前言本文是美团一位大佬写的，还不错拿出来和大家分享下,代码中嵌套在html中sql语句是java框架的写法，理解其sql要执行的语句即可。
背景mysql凭借着出色的性能、低廉的成本、丰富的资源，已经成为绝大多数互联网公司的首选关系型数据库。虽然性能出色，但所谓“好马配好鞍”，如何能够更好的使用它，已经成为开发工程师的必修课，我们经常会从职位描述上看到诸如“精通mysql”、“sql语句优化”、“了解数据库原理”等要求。我们知道一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，所以查询语句的优化显然是重中之重。
本人从13年7月份起，一直在美团核心业务系统部做慢查询的优化工作，共计十余个系统，累计解决和积累了上百个慢查询案例。随着业务的复杂性提升，遇到的问题千奇百怪，五花八门，匪夷所思。本文旨在以开发工程师的角度来解释数据库索引的原理和如何优化慢查询。
<span class="hljs-keyword">select</span>    <span class="hljs-keyword">count</span>(*) <span class="hljs-keyword">from</span>   task <span class="hljs-keyword">where</span>   <span class="hljs-keyword">status</span>=<span class="hljs-number">2</span>    <span class="hljs-keyword">and</span> operator_id=<span class="hljs-number">20839</span>    <span class="hljs-keyword">and</span> operate_time><span class="hljs-number">1371169729</span>    <span class="hljs-keyword">and</span> operate_time、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段……这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。但如果是1千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是lgn，具有不错的查询性能。但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的，数据库实现比较复杂，数据保存在磁盘上，而为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。
磁盘io与预读前面提到了访问磁盘，那么这里先简单介绍一下磁盘io和预读，磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。那么访问一次磁盘的时间，即一次磁盘io的时间约等于5+4.17 = 9ms左右，听起来还挺不错的，但要知道一台500 -mips的机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，换句话说执行一次io的时间可以执行40万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难。下图是计算机硬件延迟的对比图，供大家参考：
various-system-software-hardware-latencies
考虑到磁盘io是非常高昂的操作，计算机操作系统做了一些优化，当一次io时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次io读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次io，这个理论对于索引的数据结构设计非常有帮助。
索引的数据结构前面讲了生活中索引的例子，索引的基本原理，数据库的复杂性，又讲了操作系统的相关知识，目的就是让大家了解，任何一种数据结构都不是凭空产生的，一定会有它的背景和使用场景，我们现在总结一下，我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘io次数控制在一个很小的数量级，最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b+树应运而生。
详解b+树
b+树如上图，是一颗b+树，关于b+树的定义可以参见b+树，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针p1、p2、p3，p1表示小于17的磁盘块，p2表示在17和35之间的磁盘块，p3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。
b+树的查找过程如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次io，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的p2指针，内存时间因为非常短（相比磁盘的io）可以忽略不计，通过磁盘块1的p2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次io，29在26和30之间，锁定磁盘块3的p2指针，通过指针加载磁盘块8到内存，发生第三次io，同时内存中做二分查找找到29，结束查询，总计三次io。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次io，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次io，那么总共需要百万次的io，显然成本非常非常高。
b+树性质通过上面的分析，我们知道io次数取决于b+数的高度h，假设当前数据表的数据为n，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)n，当数据量n一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。
当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,f)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,f)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,f)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是f的数据了， 这个是非常重要的性质，即索引的最左匹配特性。
关于mysql索引原理是比较枯燥的东西，大家只需要有一个感性的认识，并不需要理解得非常透彻和深入。我们回头来看看一开始我们说的慢查询，了解完索引原理之后，大家是不是有什么想法呢？先总结一下索引的几大基本原则：
建索引的几大原则最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。回到开始的慢查询根据最左匹配原则，最开始的sql语句的索引应该是status、operator_id、type、operate_time的联合索引；其中status、operator_id、type的顺序可以颠倒，所以我才会说，把这个表的所有相关查询都找到，会综合分析； 比如还有如下查询：
<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> task <span class="hljs-keyword">where</span> <span class="hljs-keyword">status</span> = <span class="hljs-number">0</span> <span class="hljs-keyword">and</span> <span class="hljs-keyword">type</span> = <span class="hljs-number">12</span> <span class="hljs-keyword">limit</span> <span class="hljs-number">10</span>;
<span class="hljs-keyword">select</span> <span class="hljs-keyword">count</span>(*) <span class="hljs-keyword">from</span> task <span class="hljs-keyword">where</span> <span class="hljs-keyword">status</span> = <span class="hljs-number">0</span> ;
那么索引建立成(status,type,operator_id,operate_time)就是非常正确的，因为可以覆盖到所有情况。这个就是利用了索引的最左匹配的原则
查询优化神器 – explain命令关于explain命令相信大家并不陌生，具体用法和字段含义可以参考官网explain-output，这里需要强调rows是核心指标，绝大部分rows小的语句执行一定很快（有例外，下面会讲到）。所以优化语句基本上都是在优化rows。
慢查询优化基本步骤先运行看看是否真的很慢，注意设置sql_no_cachewhere条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）order by limit 形式的sql语句让排序的表优先查了解业务方使用场景加索引时参照建索引的几大原则观察结果，不符合预期继续从0分析几个慢查询案例下面几个例子详细解释了如何分析和优化慢查询。
复杂语句写法很多情况下，我们写sql只是为了实现功能，这只是第一步，不同的语句书写方式对于效率往往有本质的差别，这要求我们对mysql的执行计划和索引原则有非常清楚的认识，请看下面的语句：
<span class="hljs-keyword">select</span>   <span class="hljs-keyword">distinct</span> cert.emp_id <span class="hljs-keyword">from</span>   cm_log cl <span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span>   (      <span class="hljs-keyword">select</span>         emp.id <span class="hljs-keyword">as</span> emp_id,         emp_cert.id <span class="hljs-keyword">as</span> cert_id       <span class="hljs-keyword">from</span>         employee emp       <span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span>         emp_certificate emp_cert             <span class="hljs-keyword">on</span> emp.id = emp_cert.emp_id       <span class="hljs-keyword">where</span>         emp.is_deleted=<span class="hljs-number">0</span>   ) cert       <span class="hljs-keyword">on</span> (         cl.ref_table=<span class="hljs-string">'employee'</span>          <span class="hljs-keyword">and</span> cl.ref_oid= cert.emp_id      )       <span class="hljs-keyword">or</span> (         cl.ref_table=<span class="hljs-string">'empcertificate'</span>          <span class="hljs-keyword">and</span> cl.ref_oid= cert.cert_id      ) <span class="hljs-keyword">where</span>   cl.last_upd_date >=<span class="hljs-string">'2013-11-07 15:03:00'</span>    <span class="hljs-keyword">and</span> cl.last_upd_date=<span class="hljs-string">'2013-11-07 15:03:00'</span>    <span class="hljs-keyword">and</span> cl.last_upd_date=<span class="hljs-string">'2013-11-07 15:03:00'</span>    <span class="hljs-keyword">and</span> cl.last_upd_date= <span class="hljs-number">2875</span>          <span class="hljs-keyword">and&llt;/span> oei.node_right = <span class="hljs-number">2875</span>          <span class="hljs-keyword">and</span> oei.node_right = 2875and oei.node_right = <span class="hljs-number">2875</span>                                          <span class="hljs-keyword">and</span> oei.node_right <= <span class="hljs-number">2875</span>                                          <span class="hljs-keyword">and</span> oei.org_category = - <span class="hljs-number">1</span>                         <span class="hljs-keyword">where</span>            c.id = cb.contact_id                 )           <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span>      c.created_time <span class="hljs-keyword">desc</span>  <span class="hljs-keyword">limit</span> <span class="hljs-number">0</span> ,      <span class="hljs-number">10</span>;empty <span class="hljs-keyword">set</span> (<span class="hljs-number">2</span> <span class="hljs-keyword">min</span> <span class="hljs-number">18.99</span> sec)
2 min 18.99 sec！比之前的情况还糟糕很多。由于mysql的nested loop机制，遇到这种情况，基本是无法优化的。这条语句最终也只能交给应用系统去优化自己的逻辑了。 通过这个例子我们可以看到，并不是所有语句都能优化，而往往我们优化时，由于sql用例回归时落掉一些极端情况，会造成比原来还严重的后果。所以，第一：不要指望所有语句都能通过sql优化，第二：不要过于自信，只针对具体case来优化，而忽略了更复杂的情况。
慢查询的案例就分析到这儿，以上只是一些比较典型的案例。我们在优化过程中遇到过超过1000行，涉及到16个表join的“垃圾sql”，也遇到过线上线下数据库差异导致应用直接被慢查询拖死，也遇到过varchar等值比较没有写单引号，还遇到过笛卡尔积查询直接把从库搞死。再多的案例其实也只是一些经验的积累，如果我们熟悉查询优化器、索引的内部原理，那么分析这些案例就变得特别简单了。
本文以一个慢查询案例引入了mysql索引原理、优化慢查询的一些方法论;并针对遇到的典型案例做了详细的分析。其实做了这么长时间的语句优化后才发现，任何数据库层面的优化都抵不上应用系统的优化，同样是mysql，可以用来支撑google/facebook/taobao应用，但可能连你的个人网站都撑不住。套用最近比较流行的话：“查询容易，优化不易，且写且珍惜！”
参考文献：
1.《高性能mysql》
2.《数据结构与算法分析》
更多mysql相关技术文章，请访问mysql教程栏目进行学习！
以上就是mysql索引原理以及优化的详细内容。