如何应对并发(1) - 关于数据索引

前两天收到一个消息是这样说的，一个学生去面试，题目赫然就是从浏览器输入url到网页打开，都发生了什么。这个学生特别开心，因为订阅了我的公众号，所以对这类问题早有准备。希望他能顺利拿到心仪的offer。

参见旧文 一则经典技术面试题目的解读

书归正传，应对并发，其实从整体架构来说分很多部分，比如常见的，存储层的i/o优化，网络层负载均衡，通讯层的连接池等等，不过我这里不讲这些。不讲这些的原因第一呢，是这些我基本都不太会；第二呢，是在实践过程中发现，特别是创业公司，中小企业，一般最容易出问题，也是最难处理的，往往是数据库方面的问题。

非技术人员往往会认为，负载高了，请求多了，加服务器加硬件不就完了？ 如果是只是应用程序处理，常见的负载均衡方案很成熟，加加硬件的确可以快速，有效的分担负载，提高支撑能力；但是且慢，通常到了数据库这里，如果你前期设计不合理，或者对这类问题考虑不全，那么，加硬件，很遗憾，是没用的。

我们常说运维中要关注所谓的单点隐患，什么是单点隐患呢？就是这个点一旦崩溃，无法实现自动的灾难容错响应，从而导致全盘崩溃。一般比如说web服务器，负载均衡轮询，一台出问题了，系统会自动将负载转移到其他服务器，那么数据库可以不可以呢？其实不是不可以，但是就比较需要做好设计，否则很可能直接就死在这个环节上。而发展趋势不错的创业公司死在数据库的并发能力上的案例，可以说，比比皆是。

再插一些题外话，如果你还是学生，你有意未来往互联网技术领域发展，那么数据结构这么课特别的重要，特别的关键。我上大学的时候糊里糊涂，选中了这门课却天天翘课，工作后特别后悔。就算你不想从事技术，而只是想从事一些产品方面的工作，我个人建议有可能也认真学习一下这门课，目前不少互联网公司都希望产品经理有一点技术背景，这样和技术沟通的时候会更顺畅一些。对研发工作的跟进也会能理解更多一些。

今天的第一课，我们先要对数据索引和查询效率有个基本认识，连基本优化都做不好去讲什么架构是没意义的。

第一个问题，为什么一条查询语句，使用了数据索引会提高效率？

以及，通过一条SQL语句，能否估算出其执行开销和最佳索引选择？

熟悉数据结构同学大概知道，一般数据库的索引大概是btree，b+tree，类似这样的结构，那么现在非关系型数据库特别流行，也就是所谓的key-value数据库，最求极端效率，通常是 hash结构的数据索引。但其实这些，我认为对于我这样笨的人来说，通常，只需要理解最基本的概念就行，最基本的是什么呢？就是数据索引提供了一种有序，在有序的情况下，进行检索，二分法效率最高，n条记录中定位查询开销是 log2(N)，（hash索引效率更高，但不提供关系型查询，应用场景比较受局限）。 那么所谓的btree结构也好，或其他的类似结构也好，把握一个原则，接近二分法的查询效率，因为如果做一个完全有序的队列，那么插入，删除，修改需要做的操作开销太大了，大家可以思考一下，所以才会有人设计树形结构，兼顾查询和更新操作。理解这一点对理解整个数据查询效率和索引结构，帮助极大。

简单复习就是，查询效率的关键是有序，二分，反过来理解就是，无需遍历所有数据，即可实现快速的定位。

这里就引出了一个特别经典的题目，ip地址反查。

应用场景非常常见，你上一个什么旅游订票的网站，社区，或者上百度，该网站都希望立即知道你的地理位置，从而基于你的位置定向投放内容，比如当地酒店，或者当地的本地广告。网站一般是获取用户的ip地址，然后在ip->地区的对应表里去查询比对，通常，ip - 地区的对应表，有大约十万到数十万条记录（看地区粒度），格式是 ipstart, ipend, area 这样的数据结构。如果用纯粹的SQL查询是

select * from iparea where $ip between ipstart and ipend;

在早期mysql及大部分数据库是不支持between and 中使用索引的，据说最新版本已经提供了支持，但是最近几年没有从事技术，没有测试，不知道效率如何，那么在早期，如果数据查询，这样一条SQL,无法使用索引，就要遍历所有结果，这个开销是不能忍受的，（虽然不用1秒就可以执行出结果，但是开销依然比较大，一秒钟可以处理的查询最多几十次，而我们的要求是，一秒钟几千次！）

那么这个问题的特点是什么呢，ip地址区间表并不是经常变化的，比较固定，那么在这种情况下，其实不用数据库都可以，一个完全排好序的队列放在内存里，程序用二分法来查询，每秒种处理几千个非常轻松（这程序不用教了吧），当然，其实还有更极端效率的处理途径，这里不展开，有兴趣的同学自己思考。

此处插播一条广告，目前国内最好最权威的ip地址区间表来自于高春辉，利益相关，我超过15年的好基友，互联网传奇人物，需要定期更新ip地址区间表的建议找他购买，联系方式，去微博搜索 高春辉 。

再插播一个题外话，我在微博上说过，百度最应该购买，这是不耍流氓的情况下提升收入最快的方法，可能很多人不理解，其实百度有很多广告投放是按地区投放的，04年底 我刚进百度的时候闲着没事就给升级了一个ip地区对应表，把大量未知地区的ip定位到了已知地区，很多分地区投放的广告展现率一下子就提高了，收入自然随之增长，这玩意虽然看上去不是什么高大上的算法，但是勤更新对收入影响杠杠的。（小贴士，国际ip管理机构会不定期释放ip资源出来给新的网络设施和上网服务商，所以在最近这些年，ip地址区间表还是不断的扩充中）

第二个问题，从一个常见SQL如何确定索引的构成

以下所有案例均以mysql 为例，原因是，这个我熟悉。

非mysql可能部分语法不同，但逻辑和思路相同。

发现有一个简单问题很多人会答错，一个SQL可以用到几个索引？很多人会说是多个，其实是一个，目前一些第三方的数据引擎似乎开始支持一条SQL使用多索引了，比如我前几天看淘宝公开的那个开源数据结构的文档，从官方博客的描述中似乎有这样的提法，但是我最近确实很懒惰也脱离技术，所以没有去测试和仔细研究，这个留给有兴趣的同学吧，我还是回头说通常，我们用mysql或其他常见数据库的，一个查询只能用到一个索引；但是这里要强调的是，一个索引可以用到多个字段，也就是所谓的复合索引。

那么，按照刚才提到的，基于有序这个概念，如何理解索引的使用和效率呢？特简单，你就把索引当作是一个有序数列放在脑子里，然后思考这个SQL，这个条件子句和排序子句，能否在这个索引的连续范围内精确命中结果，也就是所谓索引命中率高，这个查询就效率高，如果无法在索引这个有序数列连续范围内精确命中，查询效率就不高。

那有人说了，索引并不是真的有序数列啊，我说的是一种模拟的思考方式，这样思考效率最高，当然，必须案例说话。

比如一个社区，我希望用户进来，就能看到本地的用户，当然，是最新在线的，否则都是死用户就无法交流了。

SQL select * from user where area='$area' order by lastlogin desc limit 30;

(这个 limit 特别重要)

稍微懂一点索引的同学都应该知道，正确的索引是area+lastlogin 复合索引，那么，我们把这个思考方式推演一下。

如果只把area当作索引会怎样，数据库会把符合这个area的所有结果拿出来，然后按照lastlogin排好序给你，这样就要遍历所有符合这个area的用户记录；

如果只把lastlogin作为索引会如何，我们想象，lastlogin是一个有序的数列，数据库会从最后一条开始往前挨条遍历，每条都去比对area是不是符合查询条件，直到数出30条，遍历结束，请注意，不是全部遍历，在这里，如果area 是个热门城市，比如上海，北京，可能遍历200次左右就出结果了，效率很快，但如果是个冷门城市，可能要遍历几千条几万条结果，甚至全部数据表遍历都凑不出符合条件的30条。这样效率就要命了。 所以用lastlogin为索引，效率存在风险。

那么两个我都建立索引呢？这个mysql只会选择一个索引，我记得不同数据库版本的选择策略都不同（实战中遇到过测试服务器用的索引很正确，线上服务器使用了错误索引，因为数据库版本不同），所以我给不出肯定的答案，但是有一点，两个索引没有意义，都不是最优解。

那么如果把lastlogin+area建立索引呢？你们设想一下，两个字段拼在一起，作为有序数列，然后数据库去查询的时候，lastlogin+area,这时候area是没用的后缀，在排序中根本体现不出他存在的意义，和单独lastlogin索引是完全一样的。

而area+lastlogin呢，把两个字段拼接然后排好序后，看这条SQL在这个数列中查询的体现，所命中的完全是连续的30条，也就是数据库只遍历30条索引记录即完成搜索，效率最好。

这段有点啰嗦，如果不理解，建议多读几遍，理解这个思路，对理解索引的效率帮助特别大，我刚工作的时候写SQL也是瞎写，对索引一知半解全靠蒙，有了这个概念后豁然开朗，从此对索引效率的认识精进了一大截，我看网上各种索引优化的教程，各种规律总结，其实你把这个认识达到了，那些规律基本上不用记，都浅显的如1+1一样。

理解如上思路，就能一并理解如下策略

A+B索引可以替代A索引，而不能替代B索引。

where key like 'keyword%' 可以用到key 索引

where key like '%keyword%' 不能用到key索引

我很笨，所以我的理解方式都是基于中学生知识基础的思路，如果您有更好的理解思路，也可以忽略本文。

第三个问题，如何评估SQL的执行开销

刚才提到一个重要的概念，就是索引中遍历的记录越少，效率越高，遍历的记录越多，效率越差。 在慢查询日志或者explain分析中，一个重要的指标是 affected rows，（好像也有别的叫法，不查证了，大家应该能知道我说的是什么），这个就是索引遍历的记录说，我以前硬翻译叫做影响结果集，我后来看其他人写的数据库文档叫索引扫描行数，概念是一样的。

那么，要强调一点，一条查询语句，其执行开销，在大多数情况下，与影响结果集，也就是索引扫描行数，呈线性相关，举两个常见经典数据优化的问题案例。

经典案例1，大翻页问题

论坛社区常见，翻页越靠后效率越低，很多论坛本身访客到没事，访客不太会翻几百页几千页，但是被搜索引擎蜘蛛抓取的时候，因为连续抓取大翻页，导致数据库崩溃，这案例太多了，很多站长为此郁闷莫名，不知所措。

案例SQL如下

按最新更新的板块第一页帖子

select * from post where boardid=$id order by lastupd desc limit 0,30;

按最新更新的板块第100页帖子

select * from post where boardid=$id order by lastupd desc limit 3000,30;

这两个SQL 看上去只有limit有区别，索引都是boardid+lastupd (不要搞错顺序，理解一下)

但第一条SQL索引只扫描30行；第二条SQL索引扫描了3030行，其效率是第一条SQL的1/100.

搜索引擎的蜘蛛抓取 大翻页就是 这样把论坛搞死的。

经典案例2，积分排行问题

比如很多小游戏提交成绩，告诉你排名全球多少名，有印象吧。

这个问题我依稀记得云风大神吐槽过，好像曾经陌陌有一款游戏在这里有非常严重的性能问题，被他狠狠BS了一把。

案例SQL如下

select count(*) from gamescore where gameid=$gameid and score>'$score' ;

索引怎么建？

gameid+score复合索引，顺序不能错，为什么，按照上面说的思路，自己思考一下。

那么这个效率怎么评估？

看结果，如果你游戏成绩特别好，前几名，前几十名，你的结果就是索引扫描行数，（如果索引都设计错了那就不要提了）。

如果你的游戏成绩很烂，几万名，几十万名，那么索引扫描了几万条，几十万条，就效率非常低了，如果有一批人同时在提交成绩，又都是这种几万名，几十万名的用户，数据库非崩溃不可，你再多服务器也白搭。

所以，常见的解决方法是，积分排行只针对最靠前的用户提供，后面只给估算或区间了。

当然，这里有个终极方案，用redis的有序数组结构，一劳永逸的解决这个问题，redis四种数据结构，各有所长，有兴趣的可以深入研究一下，今天这里不展开。

第四个常见问题，MYSQL 分析和优化的方法

刚才我说了索引扫描行数，或者说影响结果集，对查询效率的影响极大，那么有人说了，怎么证明呢？

给大家一个日常SQL分析和自我测试的方法。

首先，你一条SQL如果执行很慢，你用explain 解析一下，看看是否影响结果集很大，这是其一。

其二，对这条很慢的SQL做一个状态拆解，在mysql中是这样操作的。

set profiling=1;

执行问题SQL;

show profile for query 1;

通常，如果这个问题SQL确实是索引出了问题，也就是影响结果集，或者说索引扫描行数较多，那么他的执行状态最多的消耗就在 sending data这个状态上，这个状态不要被名字骗了，其实负载是在i/o，硬盘扫描上。

你测试的时候就可以看，影响结果集的数字，和sending data上状态的开销，是不是线性相关，对一个复杂的数据表结构，导入上百万条记录，然后用不同索引方式和不同SQL查询，利用 explain 和set profiling 这些操作反复分析SQL的影响结果集和开销构成。结合我今天说的思考方式，就可以更好理解了。

而且对于日常疑难的分析，这一招也是特别关键特别重要的。

今天啰嗦的，都是数据优化分析的基本功，其实对某些高手来说，简直都弱爆了，但是我发现大部分一线程序员，特别是从业时间不长的年轻人，并不能完全了解和认识这些。

我不是计算机科班出身，数据结构这门课也没好好上过，很多东西都是工作中慢慢琢磨出来的，如果有不严谨不准确的，万望指出，但我只自嗨的说一点，我这些招数，对大部分创业公司，中小型企业，应对百万级，千万级请求的问题而言，还是颇为管用，当然，今天只是一个开始，这一系列还将继续。