阿里P8架构师谈：MySQL数据库的索引原理、与慢SQL优化的5大原则

MySQL具有良好的性能、低成本和丰富的资源，已成为大多数互联网企业首选的关系数据库。

虽然性能优良，但“马配鞍”已成为开发工程师的必修课，如何才能很好地使用它。 “熟悉MySQL”、“优化SQL语句”、“了解数据库原理”等要求在职位描述中很常见。

在一般的APP应用系统中，读写的比率为10:1左右，并且插入操作和一般的更新操作很少发生性能问题，知道是遇到最多，最容易发生问题，还是复杂的查询操作，所以查询语句的

本文旨在从开发工程师的角度说明数据库索引的原理和低速查询的优化方法。

MySQL索引原理1 .索引的目的

的目的是提高查询的效率，可以与词典类比。 查找单词“mysql”时，必须将其定位为m个字符，并从下到上找到y个字符以找到其馀的sql。

如果没有索引，你可能需要看一遍所有的单词才能找到你想要的单词。 如果我想找到以m开头的单词呢？ 还是从ze开始的单词？ 没有索引，你觉得这件事完全做不到吗？

2 .索引原理不仅仅是词典，车站的车次、书籍目录等等，在生活中也随处可见索引的例子。

这些原理都是一样的，一边不断缩小想要获得数据的范围筛选最终想要的结果，一边将随机事件变成顺序事件，也就是说我们总是用同样的查找方式锁定数据。

数据库也是相同的，但它显然很复杂，因为它不仅面临等价查询，而且还面临范围查询(，between，in )、模糊查询( blady query )、并行查询( or )等。

数据库应该用什么方法应对所有问题呢？ 让我们回想一下词典的例子。 可以将数据分段查询吗？ 最简单的是1000条数据，1到100为第1段，101到200为第2段，201到300为第3段.像这样调查第250条数据，只要寻找第3段就可以了，90%的无效数据会一下子被去除

但是，如果是一千万的记录的话，分成几个段落比较好吗？ 有一点算法基础的同学会想出了一个搜索树，其平均复杂度为lgN，具有很好的查询性能。

但是，这里忽略了一个重要的问题。 复杂度模型每次都是基于同样的运营成本考虑的。 数据库的实现很复杂，数据存储在磁盘上，但为了提高性能，每次都可以将一些数据读取到内存中进行计算。 我们知道访问磁盘的成本约为访问内存的10万倍，所以简单的搜索树很难满足复杂的APP应用场景。

3 .磁盘I/o和预读

前面已经提到了对磁盘的访问，这里简单介绍了磁盘IO和预读。 磁盘读取数据依靠机械运动，每次读取数据所需的时间分为寻道时间、旋转延迟和传输时间三部分。 寻道时间是指磁臂移动到指定磁道所需的时间，主流磁盘通常不超过5ms。 旋转延迟是常见的光盘转速。 例如，一个盘旋转7200圈，表示每分钟可以旋转7200圈。 这意味着它每秒可以旋转120次。 旋转延迟为1/120/2=4.17ms。 传输时间是从磁盘读取数据或向磁盘写入数据的时间，通常为0.0毫秒，相对于前两个时间可以忽略。

一次访问磁盘的时间，也就是一次磁盘IO的时间约为5 4.17=9ms左右，听起来还不错，但要知道500 -MIPS的机器每秒可以执行5亿条指令。 因为命令依赖于电的性质，换言之一次IO的执行时间可以执行40万条命令。 数据库往往可以运行10万百万乃至千万级的数据。 每次9毫秒的时间，显然是灾难

下图比较了计算机的硬件延迟，以供参考。

考虑到磁盘I/o操作非常昂贵，计算机操作系统经过优化，可以在单个I/o过程中将当前磁盘地址的数据以及相邻的数据写入内存缓冲区。 因为局部预读原理表明，当计算机访问一个地址的数据时，相邻的数据也会立即被访问。

每次I/o读取的数据称为页面。

具体的一页有多少数据与操作系统有关，一般为4k或8k。 也就是说，当我们读取一页内的数据时，实际上IO只发生了一次。 这个理论对索引的数据结构设计非常有用。

4 .索引的数据结构

到目前为止，我们已经讨论了生活中的索引示例、索引的基本原理、数据库的复杂性和操作系统方面的知识。 这样做的目的是要理解，任何数据结构都不是凭空建立的，一定有其背景和使用场景。 在这里，我们总结一下这个数据结构能做什么。 实际上，每次检索数据时，都需要将磁盘的IO次数控制在很小的范围内，使其为常数级

那么，考虑一下高度可控的多重搜索树能否满足需求。 就这样，b树诞生了。

5.B树详细信息

上图是b树。 有关b树的定义，请参见b树。 在此，说明几个重要点。 浅蓝色的区块称为磁盘区块。 每个磁盘块都包含一些深蓝色数据项和黄色指针。 例如，可以看到磁盘块1包含数据项17和35，指针P1、P2、P3和P1包含少于17个磁盘块，而P2包含少于17和17个磁盘块

实际数据存在于叶节点3、5、9、10、13、15、28、29、36、60、75、79、90、99中。

除叶以外的节点中不保存实际的数据，只保存表示检索方向的数据项，例如17、35实际上不存在于数据表中。

6.b树搜索过程

如图所示，在查找数据项29时，首先将磁盘块1从磁盘加载到内存中。 此时，发生1次I/o，在存储器内通过二分查找确认29在17到35之间，锁定磁盘块1的P2指针。 内存时间非常短，(与磁盘I/o相比)不可忽略。 如果从磁盘块1的P2指针磁盘地址将磁盘块3加载到内存中，则会发生第二次I/o。 在2926和30之间，如果锁定磁盘块3的P2指针，通过指针将磁盘块8装载到存储器中，则会发生第三次IO，同时在存储器中进行二分查找以找到29，结束查询，共计发生三次IO。

实际上，三层的b树可以表示数百万的数据。 如果数百万个数据搜索只需要三次IO，则性能提升将是巨大的。 如果没有索引，每个数据项发生一次IO，则总共需要100万次IO，这显然非常昂贵。

7.b树的性质

1 .上述分析表明，IO次数取决于b数的高度h。 设当前的数据表的数据为n，每一个盘块的数据项的个数为m，则有h=(m 1 ) n，在数据量n一定的情况下，m越大，h越小。 m=磁盘块大小/数据项大小，磁盘块大小(即页面大小)是固定的，数据项占用的空间越小，数据项越多，树的高度越低。

因此，每个数据项或索引字段都应该尽可能小。 例如，int为4个字节，比bigint8字节少一半。

因此，b树要求在叶节点而不是内层节点上配置实际的数据，如果配置在内层节点上，磁盘块的数据项会大幅降低，树会变高。

如果数据项为1，则退化为路线表。

2 .在b树数据项为复合数据结构，例如( name，age，sex )的情况下，b数从左开始按顺序制作搜索树。 例如，在检索到(张三、20、f )这样的数据的情况下，b树优先比较name来确定下一个搜索方向，如果name相同，则依次比较age和下一个搜索方向，但在如( 20、f )这样来了没有name的数据的情况下，b树优先比较下的搜索方向因为，在制作检索树时，name是最初的比较因子，所以如果不首先根据name进行检索的话，下一步就不知道要询问哪里。

例如，用(张三，f )这样的数据进行搜索时，b树可以用name指定搜索方向，但由于缺少了以下字段age，所以只能找到所有名字等于张三的数据，然后匹配性别为f的数据。 这是非常重要的性质，索引的最左匹配特性。

慢查询优化对于MySQL索引的原理是比较枯燥的，大家只有一个感性，不需要非常透彻深入的理解。

让我们看看一开始说的慢速查询。 了解索引原理后，大家在想什么吗？ 首先总结索引的几个基本原则

编制索引的几个原则

1 .最左前缀匹配原则

是非常重要的原则。 mysql将停止匹配，直到遇到范围查询(、between和like )。 例如，如果创建a=1 and b=2 and c 3 and d=4)、a、b、c和d )的索引，则d不能用于索引。 )、a、b、b

2.=和in可以改变顺序

例如，可以按任何顺序创建a=1andb=2andc=3(a、b、c )索引。 mysql的查询优化程序将索引优化为可识别的形式

3 .尽量选择分区高的列作为索引

区分度表达式为count(distinctcol )/count(* ) *，表示字段不重叠的百分比。 比例越大，扫描的记录越少，唯一关键点的区分度为1。 另一方面，部分状态、性别字段在大数据之前的区分度可能会为0。 那个比例有什么经验值吗？ 你可能会问。 根据使用场景的不同，很难确定此值，但通常需要join的字段为0.1或更高，即平均每条扫描10条记录

4 .索引列不能参与计算，保持列“干净”

例如，from_UNIXtime(create_time ) ( 2014-05-29 )不能用于索引。 原因很简单，b树中存储的是数据表中的字段值，但如果要进行搜索，则必须对所有元素应用函数进行比较，显然成本太高。

因此，语句应该写成create _ time=UNIX _ timestamp ( 2014-05-29 ) )。

5 .尽量扩展索引，不创建新索引。

例如，如果在表中已经有a的索引，且要添加现在( a，b )的索引的情况下，只要修改原索引即可

查询优化神器- explain命令

你可能不知道explain命令，具体使用方法和字段的含义请参考官方网站的explain-output。 在此，必须强调rows是一个核心指标。 大多数rows小语句会立即执行(有例外，请参见下文)。

所以，优化语句基本上是在优化rows。

注意低速查询优化的基本步骤SQL_NO_CACHE的设置，首先执行它以确定是否真的很慢

1 .检查1.where条件表，锁定最小返回记录表。

这意味着您将查询语句中的所有where应用于表中返回的记录最少的表并开始检查，然后对单个表中的每个字段分别执行查询，以查看哪个字段具有最高的区分度

2.explain查看执行计划，是否与1预期一致(锁定记录数少的表后再查) ) ) ) ) ) )。

3.order by limit格式的sql语句优先检查已排序的表

4 .了解业务端使用场景

5 .参考编制索引的若干原则编制索引

6 .继续按预期从0开始分析观察结果

几种慢查询的情况

以下几个示例详细介绍了如何分析和优化慢速查询

复杂句子的写法

很多时候，我们只是为了实现功能而写SQL。 这只是第一步。 根据语句的写法不同，效率往往有本质的差异，需要对mysql的执行计划和索引原则有非常明确的认识。 请看下面的句子

selectdistinctcert.EMP _ idfromcm _ logclinnerjoin ( select EMP.idas EMP _ id， EMP _ cert.idas cert _ idfromemployeempleftjoinemp _ certificate EMP _ certonemp.id=EMP _ cert.EMP _ idwhereemp.idwheremp P_id ) or ) cl.ref _ table=’ EMP certificate ‘ were cl.last _ upd _ date=’ 2013-11-0715:03:00 ‘ and cl.land cl.land 0 .请首先尝试运行。 53条记录为1.87秒，没有使用聚合语句，所以很慢

53rowsinset(1.87sec )1.explain

请参阅————————-|id|select _ type|type|possible _————– -中的内容1|primary|cl|range|cm _ log _ cls _ id，idx _ last _ PD _ date|idx _ date using temporary||1| primary usingjoinbuffer|||2|EMP|null|null|using where||2| derived|EMP _ cert 请参阅meituan org.EMP.id|1|using index|——-请参阅—–执行计划然后，查找表扫描了63727条记录，分为两部分。 derived表示结构表，也就是不存在的表，可以简单理解为是形成了一个句子的结果集，后面的数字表示句子的ID。

derived2表示ID=2的查询构建了虚拟表，返回了63727条记录。

让我们看看ID=2的语句写了什么返回了这么大量的数据。 首先扫描所有表中的employee表13317条记录，然后根据索引emp_certificate_empid关联emp_certificate表。 rows=1表示每个关系只锁定了一条记录。

获得后，根据规则与cm_log的379条记录进行关联。

您可以看到，运行过程中数据过多，大多数返回的数据在cm_log中不可用。 因为cm_log只锁定了379条记录。

怎么优化？ 我知道执行后也必须做cm_log和join，可以做cm_log和join吗？ 仔细分析该语句，其基本思想是，如果cm_log的ref_table是EmpCertificate，则关联emp_certificate表，如果ref_table是Employee，则关联Employee表，并且我们将此关联Employee表

如果原始语句中没有distinct且不需要加重，则可以直接使用union all。 使用union时必须很重，因此会影响SQL的性能。

优化后的语句如下

select EMP.idfromcm _ logclinnerjoinemployeemponcl.ref _ table=’ employee ‘ and cl.ref _ oid=EMP.idwherecl.la ssssssecl _ deleted=0union select EMP.idfromcm _ logclinner MP certificate ‘ and cl.ref _ oid=EC.idinnerjoinemployeeemponemp.id – 0816:00:00 ‘ and EMP.is _ deleted=04 .不需要知道业务场景，只需要改造后的语句和改造前的语句使结果一致即可

5 .现有索引已满足，不需要编制索引

6 .用改造后的语句进行实验，10ms下降了近200倍！

请参阅————id|select _ type|type|possible _ keys|key|key _ len|ref | cm_log_cls_id，idx _ last _ upd _ date|idx _ last _ upd _ date|8|null|379|using where|||1|primate idx _ last _ upd _ date|idx _ last _ upd _ date|8|null|379|using where||2| union|EC|eq _ ref|primast|8 union|equref|primary using where|| null|union result|all|null|null|null

这个例子的目的是推翻我们对列划分度的认识。 一般认为，区分度越高的列越容易确定记录越少，但在特殊情况下，这一理论存在局限性

select * from stage _ poispwheresp.accurate _ result=1and ( sp.sync _ status=0orsp.sync _ status=2orsp.sync ) 951条数据是6.22秒，真的很慢

951Rowsinset(6.22sec ) explain在先，rows达到361万，type=ALL表示为全表扫描

请参阅——— key|key _ len|ref|rows | extra——————————-extra ———————————– – ———————————– – ———————————– –

尽量让explain的rows接近951

看看accurate_result=1的记录数

选择计数( *，accurate _ resultfromstage _ poigroupbyaccurate _ result； ————| count ( accurate _ result )———| accurate ( result2—– result2)

让我们看看sync_status字段的情况

selectcount(*，sync _ statusfromstage _ poigroupbysync _ status； —————-|count () ( ) )|sync_status|—-) |

将问题分析到这里，似乎得出了这个表无法优化的结论。 两列的区分度很低，即使建立索引也只能适应这种情况，很难进行普遍的优化。 例如，如果sync _ status 0，3平均分布，则锁定记录也是百万电平

4 .与业务方沟通，查看使用场景。

业务方就是这样使用这个SQL语句的。 每5分钟扫描满足条件的数据，处理完成后将sync_status字段设置为1。 5分钟满足条件的记录数并不多，只有1000个左右。

了解业务端的使用场景后，优化这个SQL就变得简单了。 因为业务方保证了数据的不均衡性，所以建立索引可以过滤大多数不需要的数据

5 .根据索引规则，使用以下语句创建索引

altertablestage _ poiaddindexidx _ ACC _ status ( accurate _ result，sync_status； 6 .观察预期结果，发现仅200ms，速度快30倍以上。

让我们再回顾一下分析952rowsinset(0.20sec )问题的过程。 单表查询相对优化。 在大多数情况下，只需根据规则对where条件中的字段进行索引即可，但如果只是这样的“无脑”优化，显然会有区分度非常低的列，甚至对不应该索引的列也进行索引

因此，调整SQL的使用场景在第四阶段非常重要。 只有了解这个业务场景，才能更好地帮助您分析和优化查询语句。

慢速询问的案例分析到此为止了。 以上是典型的例子。

既有在优化过程中遇到过包含1000多行、16个表join的“垃圾SQL”，也有在线数据库差异导致APP应用直接被慢速查询拖死的，也有varchar等值比较编写单引号

很多情况实际上只是经验的积累。 如果您熟悉查询优化程序、索引的内部原理，分析这些案例就特别容易了。

阿里P8架构师谈系列：关注如何获取资料转发，即可获得私信关键词【架构】！ 重要的事情说三遍，转发、转发，然后发私信，终于可以拿到了哦。