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

从整体架构来说分很多部分，比如常见的，存储层的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地址区间表并不是经常变化的，比较固定，那么在这种情况下，其实不用数据库都可以，一个完全排好序的队列放在内存里，程序用二分法来查询，每秒种处理几千个非常轻松（这程序不用教了吧），当然，其实还有更极端效率的处理途径，这里不展开，有兴趣的同学自己思考。

---

第二个问题，从一个常见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的影响结果集和开销构成。结合我今天说的思考方式，就可以更好理解了。