MySQL 索引知识点总结.docx

《MySQL 索引知识点总结.docx》由会员分享，可在线阅读，更多相关《MySQL 索引知识点总结.docx（22页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、MySQL索引知识点总结fanili腾讯WXG后台开发工程师知其然知其所以然本文介绍索引的数据构造、查找算法、常见的索引概念以及索引失效场景。什么是索引在关系数据库中索引是一种单独的、物理的对数据库表中一列或者多列的值进展排序的一种存储构造它是某个表中一列或者假设干列值的集合以及相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录可以根据目录中的页码快速找到所需的内容。百度百科索引的目的是进步查找效率对数据表的值集合进展了排序并按照一定数据构造进展了存储。本文将从一个案例开场从索引的数据构造、分类、关键概念及怎样使用索引进步查找效率等方面对索引知识进展总结。从一个案例

2、开场现象业务中有个既存的历史SQL语句在运行时会导致DB效劳器过载进而导致相关效劳阻塞无法及时完成。CPU监控曲线如下图1-优化前的CPU使用率从DB的CPU使用率曲线可以看到业务运行一直处于“亚安康状态1随着业务的增长随时都可能出现问题。这种问题2在11月11日凌晨出现当时DBCPU一直处于100%高负荷状态且存在大量的慢查询语句。最终以杀死进程降低DB负载、减少业务进程3的方式恢复业务。在11月11日下午对该业务的SQL语句进展了优化优化的效果如下。业务运行时的CPU使用率峰值有很大的降低比照图2的123可见慢查询语句几乎在监控曲线上也无法明显观察到比照图3的123可见。图2-优化前后的C

3、PU使用率图3-优化前后的慢查询数量分析表构造CREATETABLET_Mch*Stat(FStatDateintunsignedNOTNULLDEFAULT19700101COMMENT统计日期,FMerchantIdbigintunsignedNOTNULLDEFAULT0COMMENT商户ID,FVersionintunsignedNOTNULLDEFAULT0COMMENT数据版本号,FBatchbigintunsignedNOTNULLDEFAULT0COMMENT统计批次,FTradeAmountbigintNOTNULLDEFAULT0COMMENT交易金额PRIMARYKEY(

4、FStatDate,FMerchantId,FVersion),INDEXi_FStatDate_FVersion(FStatDate,FVersion)DEFAULTCHARSETutf8ENGINEInnoDB;从建表语句可以知道该表有两个索引主键索引是一个组合索引由字段FStateDate、FMerchantId以及FVersion组成普通索引是一个组合索引由字段FStateDate以及FVersion组成优化前的SQL语句做了局部裁剪ASELECTSQL_CALC_FOUND_ROWSFStatDate,FMerchantId,FVersion,FBatch,FTradeAmount,

5、FTradeCountFROMT_Mch*Stat_1020WHEREFStatDate20202020ANDFVersion0ANDFMerchantId0ORDERBYFMerchantIdASCLIMIT0,8000对该SQL进展explain得到如下结果Extra字段的值为usingwhere讲明并没有使用到索引。优化后的SQL语句做了局部裁剪BSELECTSQL_CALC_FOUND_ROWSa1.FStatDate,a1.FMerchantId,a1.FVersion,FBatch,FTradeAmount,FTradeCountFROMT_Mch*Stat_1020a1,(SEL

6、ECTFStatDate,FMerchantId,FVersionFROMT_Mch*Stat_1020WHEREFStatDate20202020ANDFVersion0ANDFMerchantId0ORDERBYFMerchantIdASCLIMIT0,8000)a2wherea1.FStatDatea2.FStatDateanda1.FVersiona2.FVersionanda1.FMerchantIda2.FMerchantId;优化关键步骤为新增一个子查询select字段只有主键字段该SQL的explain结果如下子查询语句使用了索引而最终在线上运行结果也证明了优化效果显著。疑问优

7、化后的SQL语句B比原来的SQL语句A复杂的多子查询临时表关联等怎么效率会提升违背直觉有三个疑问SQL语句A的查询条件字段都在主键中主键索引用到了没SQL语句B的子查询为什么可以用到索引前后两条语句执行流程的差异是什么索引的数据构造在MySQL中索引是在存储引擎层实现的而不同的存储引擎根据其业务场景特点会有不同的实现方式。这里会先介绍我们常见的有序数组、Hash以及搜索树最后看下Innodb的引擎支持的B树。有序数组数组是在任何一本数据构造以及算法的书籍都会介绍到的一种重要的数据构造。有序数组如其字面意思以Key的递增顺序保存数据在数组中。非常合适等值查询以及范围查询。ID1ID2.ID:Nn

8、ame2name2.nameN在ID值没有重复的情况下上述数组按照ID的递增顺序进展保存。这个时候假如需要查询特定ID值的name用二分法就可以快速得到时间复杂度是O(logn)。/二分查找递归实现方式intbinary_search(constintarr,intstart,intend,intkey)if(startend)return-1;intmidstart(end-start)/2;if(arrmidkey)returnbinary_search(arr,start,mid-1,key);elseif(arrmidkey)returnbinary_search(arr,mid1,e

9、nd,key);elsereturnmid;有序数组的优点很明显同样其缺点也很明显。其只合适静态数据如遇到有数据新增插入那么就会需要数据挪动新申请空间、拷贝数据以及释放空间等动作这将非常消耗资源。Hash哈希表是一种以键-值K-V存储数据的构造我们只需要输入键K就可以找到对应的值V。哈希的思路是用特定的哈希函数将K换算到数组中的位置然后将值V放到数组的这个位置。假如遇到不同的K计算出一样的位置那么在这个位置拉出一个链表依次存放。哈希表适用于等值查询的场景对应范围查询那么无能为力。二叉搜索树二叉搜索树也称为二叉查找树、有序二叉树或者排序二叉树是指一颗空树或具有以下性质的二叉树假设任意节点的左子树

10、不空那么左子树上所有节点的值均小于它的根节点的值假设任意节点的右子树不空那么右子树上所有节点的值均大于或者等于它的根节点的值任意节点的左、右子树也分别为二叉查找树二叉搜索树相比于其它数据构造的优势在于查找、插入的时间复杂度较低为O(logn)。为了维持O(logn)的查询复杂度需要保持这棵树是平衡二叉树。二叉搜索树的查找算法假设b是空树那么搜索失败否那么假设x等于b的根节点的值那么查找成功否那么假设x小于b的根节点的值那么搜索左子树否那么查找右子树。相对于有序数组以及Hash二叉搜索树在查找以及插入两端的表现都非常不错。后续基于此不断的优化开展出N叉树等。B树Innodb存储引擎支持B树索引、

11、全文索引以及哈希索引。其中Innodb存储引擎支持的哈希索引是自适应的Innodb存储引擎会根据表的使用情况自动为表生成哈希索引不能人为干预。B树索引是关系型数据库中最常见的一种索引也将是本文的主角。数据构造在前文简单介绍了有序数组以及二叉搜索树对二分查找法以及二叉树有了根本解析。B树的定义相对复杂在理解索引工作机制上无须深化、只需理解数据组织形式以及查找算法即可。我们可以简单的认为B树是一种N叉树以及有序数组的结合体。例如B树的3个优点:层级更低IO次数更少每次都需要查询到叶子节点查询性能稳定叶子节点形成有序链表范围查询方便操作算法查找由根节点自顶向下遍历树根据别离值在要查找的一边的指针在节

12、点内使用二分查找来确定位置。插入删除注插入以及删除两个表格内容来自?MySQL技术内幕-InnoDB存储引擎?填充因子innodb_fill_factor索引构建期间填充的每个B-tree页面上的空间百分比其余空间保存给将来索引增长。从插入以及删除操作中可以看到填充因子的值会影响到数据页的split以及merge的频率。将值设置小些可以减少split以及merge的频率但是索引相对会占用更多的磁盘空间反之那么会增加split以及merge的频率但是可以减少占用磁盘空间。Innodb对于聚集索引默认会预留1/16的空间保证后续的插入以及晋级索引。InnodbB树索引前文介绍了索引的根本数据构造如

13、今开场我们从Innodb的角度解析怎样使用B树构建索引索引怎样工作以及怎样使用索引提升查找效率。聚集索引以及非聚集索引数据库中的B树索引可以分为聚集索引以及非聚集索引。聚集索引以及非聚集索引的不同点在于叶子节点是否是完好行数据。Innodb存储引擎表是索引组织表即表中的数据按照主键顺序存放。聚集索引就是按照每张表的主键构造一棵B树叶子节点存放的是表的完好行记录。非聚集索引的叶子节点不包含行记录的全部数据。Innodb存储引擎的非聚集索引的叶子节点的内容为主键索引值。假设数据表没有主键聚集索引是怎么建立的在没有主键时Innodb会给数据表的每条记录生成一个6个字节长度的RowId字段会以此建立聚

14、集索引。Select语句查找记录的经过下面例子将展示索引数据的组织形式及Select语句查询数据的经过。建表语句createtableT(IDintprimarykey,kintNOTNULLDEFAULT0,svarchar(16)NOTNULLDEFAULT,indexk(k)engineInnoDBDEFAULTCHARSETutf8;insertintoTvalues(100,1,aa),(200,2,bb),(300,3,cc),(500,5,ee),(600,6,ff),(700,7,gg索引构造示意左边是以主键ID建立起的聚集索引其叶子节点存储了完好的表记录信息右边是以普通字段K

15、建立的普通索引其叶子节点的值是主键ID。Select语句执行经过select*fromTwherekbetween3and5;执行流程如下在K索引树上找到k3的记录获得ID300再到ID索引树上查找ID300对应的R3在k索引树取下一个值k5获得ID500再回到ID索引树查到ID500对应的R4在k索引树取下一个值k6不知足条件循环完毕。上述查找记录的经过中引入了一个重要的概念回表即回到主键索引树搜索的经过。防止回表操作是提升SQL查询效率的常规思路及重要方法。那么怎样防止回表注该例子来自?MySQL实战45讲?覆盖索引MySQL5.7,建表语句CREATETABLEemployees(emp

16、_noint(11)NOTNULL,birth_datedateNOTNULL,first_namevarchar(14)NOTNULL,last_namevarchar(16)NOTNULL,genderenum(M,F)NOTNULL,hire_datedateNOTNULL,PRIMARYKEY(emp_no),KEYi_first_name(first_name),KEYi_hire_date(hire_date)ENGINEInnoDBDEFAULTCHARSETutf8;SQL语句Aexplainselect*fromemployeeswherehire_date1990-01-1

17、4explain结果SQL语句Bexplainselectemp_nofromemployeeswherehire_date1990-01-14explain结果分析从前后两次explain的结果可以看到SQL语句A的extra为usingwhereSQL语句B的extra为usingwhere;usingindex。这讲明A没有使用索引而B使用了索引。索引K中包含了查询语句所需要的字段ID的值无需再次回到主键索引树查找也就是“覆盖了我们的查询需求我们称之为覆盖索引。覆盖索引可以减少树的搜索次数显著提升查询性能。最左匹配SQL语句Aexplainselect*fromemployeeswher

18、ehire_date1990-01-14andfirst_namelike%Hi%SQL语句Bexplainselect*fromemployeeswherehire_date1990-01-14andfirst_namelikeHi%分析在上述测试的SQL语句A使用了极端方式:first_namelike%Hi%前后都增加模糊匹配使得SQL语句无法使用到索引当去掉最左边的%后SQL语句B就使用了索引。最左匹配可以是字符串索引的最左N个字符可以以是结合索引的最左M的字段。合理规划、使用最左匹配可以减少索引进而节约磁盘空间。索引下推何为索引下推我们先从下面这组比照测试开场将在MySQL5.5版本

19、以及MySQL5.7版本中执行同一条SQL语句select*fromemployeeswherehire_date1990-01-14andfirst_namelikeHi%在MySQL5.5执行explainextra字段的值显示没有使用索引执行查询花费时间为0.12s在MySQL5.7执行explainextra字段的值显示使用了索引下推执行查询花费时间为0.02s索引下推explain结果中的extra字段值包含usingindexcondition那么讲明使用了索引下推。索引下推功能是从5.6版本开场支持的。在5.6版本之前i_first_name索引是没有使用上的需要每次去主键索引表

20、取完好的记录值进展比拟。从5.6版本开场由于索引i_first_name的存在可以直接取索引的first_name值进展过滤这样不符合first_namelikeHi%条件的记录就不再需要回表操作。MRR优化MySQL5.6版本开场支持Multi-RangeRead(MRR)优化MRR优化的目的是为减少磁盘的随机访问并且将随机访问转化为较为顺序的数据访问对于IO-bound类型的SQL查询语句可带来性能极大提升。我们先看下比照测试以下测试语句在同一个MySQL实例下执行执行前均进展mysql效劳重启以保证缓存此没被预热。关闭MRRSEToptimizer_switchmrroffselect*

21、fromemployeeswherehire_date1990-01-14andfirst_namelikeHi%执行耗时未0.90s开启MRRSEToptimizer_switchmrron,mrr_cost_basedoffselect*fromemployeeswherehire_date1990-01-14andfirst_namelikeHi%分析从测试结果可以发如今mrr从关闭到开启耗时从0.90s减少到0.03s查询速率到达30倍的提升。常见的索引失效场景在MySQL表中建立了索引SQL查询语句就会一定使用到索引么不一定存在着索引失效的场景。我们给employees表增一个组合索

22、引后续例子均基于此表进展分析、测试。altertableemployeesaddindexi_b_f_l(birth_date,first_name,last_name)altertableemployeesaddindexi_h(hire_date);失效场景范围查询,explainselect*fromemployeeswherehire_date1989-06-02查询条件类型不一致altertableemployeesaddindexi_first_name(first_name);explainselect*fromemployeeswherefirst_name查询条件使用了函数e

23、xplainselect*fromemployeeswhereCHAR_LENGTH(hire_date)10;模糊查询explainselect*fromemployeeswherehire_datelike%1995不使用组合索引的首个字段当条件explainselect*fromemployeeswherelast_nameKalloufiandfirst_nameSaniya为什么会失效顺序读比离散读性能要好范围查询一定会导致索引失效么并不会略微更改下查询条件看下explain的比照结果可以看到新语句用到索引下推讲明索引并未失效。为什么在不使用覆盖索引的情况下优化器只有在数据量小的时候

24、才会选择使用非聚集索引。受制于传统的机械磁盘特性通过聚集索引顺序读数据行的性能会比通过非聚集索引离散读数据行要好。所以优化器在即使有非聚集索引、但是访问数据量可能到达送记录数的20%时会选择聚集索引。当然可以以用Forceindex强迫使用索引。explainselect*fromemployeeswherehire_date1999-06-02无法使用B索引快速查找B树索引支持快速查询的根本要素是因为其索引键值是有序存储的从左到右由小到大这样就可以在每个层级的节点中快速查并进入下一层级最终在叶子节点找到对应的值。使用函数会使得MySQL无法使用索引进展快速查询因为对索引字段做函数操作会破坏索

25、引值的有序性所以优化器选择不使用索引。而查询条件类型不一致其实也是同样的情况因为其使用了隐式类型转换*。模糊匹配以及不使用组合索引的首字段作为查询条件均是无法快速定位索引位置进而导致无法使用索引。模糊匹配当查询条件是lwhereAikea%a是A的最左前缀时是可能用上索引的最左匹配是否用上最终还是依赖优化器对查询数据量的评估。回到初始的案例让我们回到文章初的案例尝试答复下当时提出的3个问题。-A语句SELECTFStatDate,FMerchantId,FVersion,FBatch,FTradeAmount,FTradeCountFROMT_Mch*Stat_1020WHEREFStatDa

26、te20202020ANDFVersion0ANDFMerchantId0ORDERBYFMerchantIdASCLIMIT0,8000;-B语句SELECTSQL_CALC_FOUND_ROWSa1.FStatDate,a1.FMerchantId,a1.FVersion,FBatch,FTradeAmount,FTradeCountFROMT_Mch*Stat_1020a1,(SELECTFStatDate,FMerchantId,FVersionFROMT_Mch*Stat_1020WHEREFStatDate20202020ANDFVersion0ANDFMerchantId0ORD

27、ERBYFMerchantIdASCLIMIT0,8000)a2wherea1.FStatDatea2.FStatDateanda1.FVersiona2.FVersionanda1.FMerchantIda2.FMerchantId;SQL语句A的查询条件字段都在主键中主键索引用到了没主键索引其实是有被使用的索引的范围查询只是其需要逐条读取以及解析所有记录才导致慢查询。SQL语句B的子查询为什么可以用到索引前文中我们介绍了聚集索引其索引键值就是主键。两条SQL语句的不同之处在于B语句的子查询语句的Select字段都包含在主键字段中而A语句还有其它字段例如FBatch以及FTradeAmoun

28、t等。这种情况下只凭主键索引的键值就能知足B语句的字段要求A语句那么需要逐条取整行记录进展解析。前后两条语句执行流程的差异是什么SQL语句A的执行经过逐条扫描索引表并比拟查询条件遇到符合查询条件的那么读取整行数据返回回到a步骤直至完成所有索引记录的比拟对返回的所有符合条件的记录完好的记录进展排序选取前8000条数据返回SQL语句B的执行经过逐条扫描索引表并比拟查询条件遇到符合查询条件的那么从索引键中取相关字段值返回回到a步骤直至完成所有索引记录的比拟对返回的所有符合条件的记录每条记录只有3个主键进展排序选取前8000条数据返回形成临时表关联临时表与主表使用主键相等比拟查询8000条数据比照两个

29、SQL语句的执行经过可以发现差异点集中在步骤2以及步骤4。在步骤2中SQL语句A需要随机读取整行数据并解析非常耗资源步骤4涉及MySQL的排序算法这里也会对执行效率有影响排序效果上看SQL语句B比SQL语句A好。名词解释主键索引顾名思义该类索引由表的主键组成从左到右由小到大排序。一个Innodb存储表只有一张主键索引表聚集索引。普通索引最为平常的一种索引没有十分限制。唯一索引该索引的字段不能有一样值但允许有空值。组合索引由多列字段组合而成的索引往往是为了提升查询效率而设置。总结在文章开场时介绍了常见的几种索引数据构造合适静态数据的有序数组、合适KV构造的哈希索引及兼顾查询及插入性能的搜索二叉树

30、然后介绍了Innodb的常见索引实现方式B树及Select语句使用B树索引查找记录的执行经过在这个局部我们解析了几个关键的概念回表、覆盖索引、最左匹配、索引下推以及MMR之后还总结了索引的失效场景及背后的原因。最后我们回到最初的案例分析出优化前后SQL语句在使用索引的差异进而导致执行效率的差异。本文介绍了索引的一些粗浅知识祈望可以对读者有些许帮助。作为阶段性学习的一个总结文章对MySQL索引的相关知识根本上是浅藏辄止日后还需多多使用以及深化学习。何以解忧唯有学习。参考书目以及资料?MySQL技术内幕-InnoDB存储引擎?第二版姜承尧?MySQL实战45讲?林晓斌s:/dev.mysql/doc/refman/8.0/en/s:/zh.wikipedia.org/wiki/%E4%BA%8C%E5%85%83%E6%90%9C%E5%B0%8B%E6%A8%B9s:/github/zhangyachen/zhangyachen.github.io/issues/117