MySQL索引知识点总结

见贤思齐

作者：fanili，腾讯WXG后台开发工程师知其然知其所以然！本文介绍索引的数据结构、查找算法、常见的索引概念和索引失效场景。什么是索引？在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。（百度百科）索引的目的是提高查找效率，对数据表的值集合进行了排序，并按照一定数据结构进行了存储。本文将从一个案例开始，从索引的数据结构、分类、关键概念及如何使用索引提高查找效率等方面对索引知识进行总结。从一个案例开始现象业务中有个既存的历史SQL语句在运行时会导致DB服务器过载，进而导致相关服务阻塞无法及时完成。CPU监控曲线如下：图1-优化前的CPU使用率从DB的CPU使用率曲线可以看到业务运行一直处于“亚健康”状态（1），随着业务的增长随时都可能出现问题。这种问题（2）在11月11日凌晨出现，当时DBCPU一直处于100%高负荷状态，且存在大量的慢查询语句。最终以杀死进程降低DB负载、减少业务进程（3）的方式恢复业务。在11月11日下午，对该业务的SQL语句进行了优化，优化的效果如下。业务运行时的CPU使用率峰值有很大的降低（对比图2的1，2，3可见）；慢查询语句几乎在监控曲线上也无法明显观察到（对比图3的1，2，3可见）。图2-优化前后的CPU使用率图3-优化前后的慢查询数量分析表结构CREATETABLET_Mch******Stat(`FStatDate`intunsignedNOTNULLDEFAULT19700101COMMENT'统计日期',`FMerchantId`bigintunsignedNOTNULLDEFAULT0COMMENT'商户ID',`FVersion`intunsignedNOTNULLDEFAULT0COMMENT'数据版本号',`FBatch`bigintunsignedNOTNULLDEFAULT0COMMENT'统计批次',`FTradeAmount`bigintNOTNULLDEFAULT0COMMENT'交易金额'PRIMARYKEY(`FStatDate`,`FMerchantId`,`FVersion`),INDEXi_FStatDate_FVersion(`FStatDate`,`FVersion`))DEFAULTCHARSET=utf8ENGINE=InnoDB;从建表语句可以知道该表有两个索引：主键索引，是一个组合索引，由字段FStateDate、FMerchantId和FVersion组成；普通索引，是一个组合索引，由字段FStateDate和FVersion组成；优化前的SQL语句（做了部分裁剪）A：SELECTSQL_CALC_FOUND_ROWSFStatDate,FMerchantId,FVersion,FBatch,FTradeAmount,FTradeCountFROMT_Mch******Stat_1020WHEREFStatDate=20201020ANDFVersion=0ANDFMerchantId>0ORDERBYFMerchantIdASCLIMIT0,8000对该SQL进行explain得到如下结果，Extra字段的值为usingwhere，说明并没有使用到索引。优化后的SQL语句（做了部分裁剪）B：SELECTSQL_CALC_FOUND_ROWSa1.FStatDate,a1.FMerchantId,a1.FVersion,FBatch,FTradeAmount,FTradeCountFROMT_Mch******Stat_1020a1,(SELECTFStatDate,FMerchantId,FVersionFROMT_Mch******Stat_1020WHEREFStatDate=20201020ANDFVersion=0ANDFMerchantId>0ORDERBYFMerchantIdASCLIMIT0,8000)a2wherea1.FStatDate=a2.FStatDateanda1.FVersion=a2.FVersionanda1.FMerchantId=a2.FMerchantId;优化关键步骤为：新增一个子查询，select字段只有主键字段；该SQL的explain结果如下，子查询语句使用了索引，而最终在线上运行结果也证明了优化效果显著。疑问优化后的SQL语句B比原来的SQL语句A复杂的多（子查询，临时表关联等），怎么效率会提升，违反直觉？有三个疑问：SQL语句A的查询条件字段都在主键中，主键索引用到了没？SQL语句B的子查询为什么能够用到索引？前后两条语句执行流程的差异是什么？索引的数据结构在MySQL中，索引是在存储引擎层实现的，而不同的存储引擎根据其业务场景特点会有不同的实现方式。这里会先介绍我们常见的有序数组、Hash和搜索树，最后看下Innodb的引擎支持的B+树。有序数组数组是在任何一本数据结构和算法的书籍都会介绍到的一种重要的数据结构。有序数组如其字面意思，以Key的递增顺序保存数据在数组中。非常适合等值查询和范围查询。ID：1ID：2......ID:Nname2name2......nameN在ID值没有重复的情况下，上述数组按照ID的递增顺序进行保存。这个时候如果需要查询特定ID值的name，用二分法就可以快速得到，时间复杂度是O(logn)。//二分查找递归实现方式intbinary_search(constintarr[],intstart,intend,intkey){if(start>end)return-1;intmid=start+(end-start)/2;if(arr[mid]>key)returnbinary_search(arr,start,mid-1,key);elseif(arr[mid]'1990-01-14';explain结果：SQL语句Bexplainselectemp_nofromemployeeswherehire_date>'1990-01-14';explain结果：分析从前后两次explain的结果可以看到SQL语句A的extra为usingwhere，SQL语句B的extra为usingwhere;usingindex。这说明A没有使用索引，而B使用了索引。索引K中包含了查询语句所需要的字段ID的值，无需再次回到主键索引树查找，也就是“覆盖”了我们的查询需求，我们称之为覆盖索引。覆盖索引可以减少树的搜索次数，显著提升查询性能。最左匹配SQL语句Aexplainselect*fromemployeeswherehire_date>'1990-01-14'andfirst_namelike'%Hi%';SQL语句Bexplainselect*fromemployeeswherehire_date>'1990-01-14'andfirst_namelike'Hi%';分析在上述测试的SQL语句A使用了极端方式:first_namelike'%Hi%'，前后都增加模糊匹配使得SQL语句无法使用到索引；当去掉最左边的‘%’后，SQL语句B就使用了索引。最左匹配可以是字符串索引的最左N个字符，也可以是联合索引的最左M的字段。合理规划、使用最左匹配可以减少索引，从而节约磁盘空间。索引下推何为索引下推？我们先从下面这组对比测试开始，将在MySQL5.5版本和MySQL5.7版本中执行同一条SQL语句：select*fromemployeeswherehire_date>'1990-01-14'andfirst_namelike'Hi%';在MySQL5.5执行explain，extra字段的值显示没有使用索引执行查询花费时间为0.12s在MySQL5.7执行explain，extra字段的值显示使用了索引下推执行查询花费时间为0.02s索引下推explain结果中的extra字段值包含usingindexcondition，则说明使用了索引下推。索引下推功能是从5.6版本开始支持的。在5.6版本之前，i_first_name索引是没有使用上的，需要每次去主键索引表取完整的记录值进行比较。从5.6版本开始，由于索引i_first_name的存在，可以直接取索引的first_name值进行过滤，这样不符合"first_namelike'Hi%'"条件的记录就不再需要回表操作。MRR优化MySQL5.6版本开始支持Multi-RangeRead(MRR)优化，MRR优化的目的是为减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问，对于IO-bound类型的SQL查询语句可带来性能极大提升。我们先看下对比测试，以下测试语句在同一个MySQL实例下执行，执行前均进行mysql服务重启，以保证缓存此没被预热。关闭MRRSET@@optimizer_switch='mrr=off';select*fromemployeeswherehire_date>'1990-01-14'andfirst_namelike'Hi%';执行耗时未0.90s开启MRRSET@@optimizer_switch='mrr=on,mrr_cost_based=off';select*fromemployeeswherehire_date>'1990-01-14'andfirst_namelike'Hi%';分析从测试结果可以发现在mrr从关闭到开启，耗时从0.90s减少到0.03s，查询速率达到30倍的提升。常见的索引失效场景在MySQL表中建立了索引，SQL查询语句就会一定使用到索引么？不一定，存在着索引失效的场景。我们给employees表增一个组合索引，后续例子均基于此表进行分析、测试。altertableemployeesaddindexi_b_f_l(birth_date,first_name,last_name)altertableemployeesaddindexi_h(hire_date);失效场景范围查询（>,）explainselect*fromemployeeswherehire_date>'1989-06-02';查询条件类型不一致altertableemployeesaddindexi_first_name(first_name);explainselect*fromemployeeswherefirst_name=1;查询条件使用了函数explainselect*fromemployeeswhereCHAR_LENGTH(hire_date)=10;模糊查询explainselect*fromemployeeswherehire_datelike'%1995';不使用组合索引的首个字段当条件explainselect*fromemployeeswherelast_name='Kalloufi'andfirst_name='Saniya';为什么会失效？顺序读比离散读性能要好范围查询一定会导致索引失效么？并不会！稍微更改下查询条件看下explain的对比结果，可以看到新语句用到索引下推，说明索引并未失效。为什么？在不使用覆盖索引的情况下，优化器只有在数据量小的时候才会选择使用非聚集索引。受制于传统的机械磁盘特性，通过聚集索引顺序读数据行的性能会比通过非聚集索引离散读数据行要好。所以，优化器在即使有非聚集索引、但是访问数据量可能达到送记录数的20%时会选择聚集索引。当然也可以用Forceindex强制使用索引。explainselect*fromemployeeswherehire_date>'1999-06-02';无法使用B+索引快速查找B+树索引支持快速查询的基本要素是因为其索引键值是有序存储的，从左到右由小到大，这样就可以在每个层级的节点中快速查并进入下一层级，最终在叶子节点找到对应的值。使用函数会使得MySQL无法使用索引进行快速查询，因为对索引字段做函数操作会破坏索引值的有序性，所以优化器选择不使用索引。而查询条件类型不一致其实也是同样的情况，因为其使用了隐式类型转换*。模糊匹配和不使用组合索引的首字段作为查询条件均是无法快速定位索引位置从而导致无法使用索引。模糊匹配当查询条件是lwhereAike'a%'，a是A的最左前缀时是可能用上索引的（最左匹配），是否用上最终还是依赖优化器对查询数据量的评估。回到初始的案例让我们回到文章初的案例，尝试回答下当时提出的3个问题。--A语句SELECTFStatDate,FMerchantId,FVersion,FBatch,FTradeAmount,FTradeCountFROMT_Mch******Stat_1020WHEREFStatDate=20201020ANDFVersion=0ANDFMerchantId>0ORDERBYFMerchantIdASCLIMIT0,8000;--B语句SELECTSQL_CALC_FOUND_ROWSa1.FStatDate,a1.FMerchantId,a1.FVersion,FBatch,FTradeAmount,FTradeCountFROMT_Mch******Stat_1020a1,(SELECTFStatDate,FMerchantId,FVersionFROMT_Mch******Stat_1020WHEREFStatDate=20201020ANDFVersion=0ANDFMerchantId>0ORDERBYFMerchantIdASCLIMIT0,8000)a2wherea1.FStatDate=a2.FStatDateanda1.FVersion=a2.FVersionanda1.FMerchantId=a2.FMerchantId;SQL语句A的查询条件字段都在主键中，主键索引用到了没？主键索引其实是有被使用的：索引的范围查询，只是其需要逐条读取和解析所有记录才导致慢查询。SQL语句B的子查询为什么能够用到索引？前文中我们介绍了聚集索引，其索引键值就是主键。两条SQL语句的不同之处在于B语句的子查询语句的Select字段都包含在主键字段中，而A语句还有其它字段（例如FBatch和FTradeAmount等）。这种情况下只凭主键索引的键值就能满足B语句的字段要求；A语句则需要逐条取整行记录进行解析。前后两条语句执行流程的差异是什么？SQL语句A的执行过程：逐条扫描索引表并比较查询条件遇到符合查询条件的则读取整行数据返回回到a步骤，直至完成所有索引记录的比较对返回的所有符合条件的记录（完整的记录）进行排序选取前8000条数据返回SQL语句B的执行过程：逐条扫描索引表并比较查询条件遇到符合查询条件的则从索引键中取相关字段值返回回到a步骤，直至完成所有索引记录的比较对返回的所有符合条件的记录（每条记录只有3个主键）进行排序选取前8000条数据返回形成临时表关联临时表与主表，使用主键相等比较查询8000条数据对比两个SQL语句的执行过程，可以发现差异点集中在步骤2和步骤4。在步骤2中SQL语句A需要随机读取整行数据并解析非常耗资源；步骤4涉及MySQL的排序算法，这里也会对执行效率有影响，排序效果上看SQL语句B比SQL语句A好。名词解释主键索引顾名思义该类索引由表的主键组成，从左到右由小到大排序。一个Innodb存储表只有一张主键索引表（聚集索引）。普通索引最为平常的一种索引，没有特别限制。唯一索引该索引的字段不能有相同值，但允许有空值。组合索引由多列字段组合而成的索引，往往是为了提升查询效率而设置。总结在文章开始时介绍了常见的几种索引数据结构，适合静态数据的有序数组、适合KV结构的哈希索引及兼顾查询及插入性能的搜索二叉树；然后介绍了Innodb的常见索引实现方式B+树及Select语句使用B+树索引查找记录的执行过程，在这个部分我们了解了几个关键的概念，回表、覆盖索引、最左匹配、索引下推和MMR；之后还总结了索引的失效场景及背后的原因。最后，我们回到最初的案例，分析出优化前后SQL语句在使用索引的差异，进而导致执行效率的差异。本文介绍了索引的一些粗浅知识，希望能够对读者有些许帮助。作为阶段性学习的一个总结，文章对MySQL索引的相关知识基本上是浅藏辄止，日后还需多多使用和深入学习。何以解忧？唯有学习。参考书目和资料《MySQL技术内幕-InnoDB存储引擎》第二版，作者：姜承尧《MySQL实战45讲》，作者：林晓斌https://dev.mysql.com/doc/refman/8.0/en/https://zh.wikipedia.org/wiki/%E4%BA%8C%E5%85%83%E6%90%9C%E5%B0%8B%E6%A8%B9重温数据结构：理解B树、B+树特点及使用场景-Androidhttps://github.com/zhangyachen/zhangyachen.github.io/issues/117