前言为了在生产中高效的查询数据库中的数据，我们经常会在表中的字段上添加索引。您是否考虑过如何添加索引以使索引更高效，请考虑以下问题。添加尽可能多的索引是否更好？为什么有时候加了索引还是不生效？索引有哪些类型如何判断一个索引设计的好坏？有了更清晰的认识。本文将从以下几个方面介绍索引的相关知识。相信大家耐心看完后都会有所收获。什么是索引及其作用？索引类型高性能索引策略索引设计指南：三星索引什么是索引及其作用？你总会拿起新华字典查的。你可以查看每页从头到尾是否有“先”字。这种方式（对应数据库中的全表扫描）确实可以找到，但是效率无疑是很低的，相信大家都知道，更高效的方式是先在首页的索引中搜索“first”对应的页码，然后直接跳转到对应的页面进行搜索，这样查询时间就大大减少了，这可以是O(1)。数据库中的索引也类似。使用索引来定位要读取的页面，大大减少了需要扫描的行数，可以大大提高效率。简而言之，索引主要有以下几个作用，就是上面说的，索引可以大大减少扫描行数索引可以帮助服务器避免排序，临时表索引可以把随机IO变成顺序IO第一点上面已经解释过了，我们先来看第二点和第三点第二点，假设我们不使用索引，想象一下运行下面的语句SELECT*FROMuserorderbyagedesc;那么MySQL的流程是这样的，扫描所有的行，将所有的行加载到内存中，然后按照年龄排序，生成一个临时表，然后把这个表排序后，将对应的行返回给客户端。更糟糕的是，如果这个临时表的大小大于tmp_table_size的值（默认为16M），内存临时表将转换为磁盘临时表，性能会更差。如果加上索引，索引本身是有序的，所以从磁盘读取的行数本身是按age排序的，不会产生临时表，所以不需要额外的排序，这无疑提高了性能。再来看看随机IO和顺序IO。我先解释一下这两个概念。相信很多人应该都吃过旋转火锅。服务员把菜放在旋转的传送带上，等菜传给我们就可以拿到菜了。假设填满一圈需要4分钟，那么最短等候时间为0（即菜品就在你面前），最长等候时间为4分钟（刚好错过菜品在你面前你），则平均等待时间为2分钟。假设我们现在要拿四道菜。菜品是随机分布在传送带上的，所以可以知道这四道菜的平均等待时间是8分钟（随机IO），如果这四道菜刚好挨着，等待时间只有2分钟（顺序IO）。上面提到的传送带可以类比为磁轨，磁轨上的碟子可以类比为一个扇区中的信息。一个磁盘块（block）由多个相邻的扇区组成，是操作系统读取的最小单位。如果能把信息以块的形式聚集在一起，磁盘IO时间就可以大大减少，这就是顺序IO带来的性能提升。下面我们会看到B+树索引起到了这样的作用。如图：多个扇区组成一个块。如果要读取的信息在这个块中，则只需要一次IO读取。如果信息分散在一个磁道中的各个扇区，或者分布在磁道的不同扇区上（寻道时间是随机IO的主要瓶颈），就会造成随机IO，影响性能。我们看一下随机IO的时间分布：seekTime：寻道时间，磁头移动到扇区所在磁道RotationalLatency：第1步完成后，磁头移动到该位置所需的时间对应同一磁道扇区的TransferTimeFrom磁盘读取信息传输到内存的时间，其中寻道时间占据了绝大部分时间（约占随机IO时间的40%）。随机IO和顺序IO相差一百倍左右（随机IO：10ms/page，顺序IO0.1ms/page）。可见顺序IO的性能是高的，索引带来的性能提升是很明显的！索引类型主要分为以下几类B+树索引Hash索引B+树索引B+树索引在之前的文章中已经详细阐述过。强烈建议您再读一遍。对理解B+树很有帮助。我们简单回顾一下，B+树是以N叉树的形式存在的，这样有效的降低了树的高度，而且不需要全表扫描来查找数据。我们可以通过逐层向下搜索根节点来快速找到我们的目标数据。每个节点的大小是一个磁盘块（page），一次IO会读取一个磁盘块的所有数据（也就是磁盘预读，程序局部性原则：如果读取到某个值，很有可能因为这个值周围的数据也会被使用，所以干脆一起读内存），叶子节点之间通过指针相互连接，可以有效减少顺序遍历时的随机IO，我们也可以看出叶子节点都是按照索引的先后顺序排序的，也就是说按照索引查找或者排序都是排序的，不会在内存中形成临时表。哈希索引哈希索引基本的哈希表实现，哈希表（也称哈希表）是一种根据键码值（Keyvalue）直接访问的数据结构，它允许通过码值映射到哈希表哈希函数的转换在列表对应的位置，查找效率非常高。假设我们已经在名字上建立了哈希索引，查找过程如下图所示：对于每一行数据，存储引擎都会为所有索引列（上图中的名字列）计算一个哈希码（上图中的哈希表）位置），哈希表中的每个元素都指向数据行的指针，因为索引本身只存储对应的哈希值，所以索引的结构非常紧凑，这使得哈希索引的搜索速度非常快！当然，哈希表的劣势也很明显。它不支持区间搜索和排序，所以更多的时候哈希表是和BTree等一起使用的。在InnoDB引擎中，有一个特殊的特性叫做“自适应哈希索引”。索引，当innoDB注意到某些索引值使用频率很高时，会在内存中基于B-Tree索引创建哈希索引，这样B+树索引也具有哈希索引查找速度快的优点，这是一种完全自动的内部行为，用户无法控制或配置，但可以在必要时将其关闭。innoDB引擎本身不支持显式创建哈希索引。我们可以创建一个基于B+树的伪哈希索引，它和真正的哈希索引是不一样的。它使用哈希值而不是键这个伪哈希索引的使用场景是什么？假设我们在db中的一个表中有一个url字段。我们知道每个url的长度都很长。如果我们使用url字段来创建索引，无疑会占用大量的存储空间。如果能把url通过hash（比如CRC32）映射成4字节，再用这个hash值索引，索引占用无疑会大大缩短！但是查询的时候一定要记得同时带上url和url_crc，主要是为了避免hash冲突，导致url_crc的值可能相同SELECTidFROMurlWHEREurl="http://www.baidu.com"ANDurl_crc=CRC32("http://www.baidu.com")这样将基于url的字符串索引改为基于url_crc的整型索引，效率更高，索引占用的空间也大大减少，杀一石二鸟。当然，可能有人会说，手动维护索引太麻烦了，所以可以改进触发器的实现。除了上面提到的两个索引，还有空间索引（R-Tree）、全文索引等，生产中用的不是很常用，这里就不细说高性能索引策略了.不同的索引设计选择会对性能产生很大的影响。有的人可能会发现生产中明明加了索引却没有生效。有时它会生效但不会对搜索性能产生太大影响。对于多列联合索引，知道哪一列在前，哪一列在后也很重要。对，我们来看看为什么添加索引后不生效。添加索引可能有多种原因，但没有生效。1、索引列是表达式的一部分或函数的一部分如下SQL：SELECTbook_idFROMBOOKWHEREbook_id+1=5;orSELECTbook_idFROMBOOKWHERETO_DAYS(CURRENT_DATE)-TO_DAYS(gmt_create)<=10上面两条SQL虽然在book_id和gmt_create列上设置了索引，但是因为它们是表达式或函数的一部分，所以索引无法生效，最终导致全表扫描.2.隐式类型转换相信很多人都知道上面两种情况下索引是不能生效的，但是下面的隐式类型转换可能会绊倒很多人。我们来看下面的例子：假设有如下表：CREATETABLE`tradelog`(`id`int(11)NOTNULL,`tradeid`varchar(32)DEFAULTNULL,`operator`int(11)DEFAULTNULL,`t_modified`datetimeDEFAULTNULL,PRIMARYKEY(`id`),KEY`tradeid`(`tradeid`),KEY`t_modified`(`t_modified`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;执行SQL语句SELECT*FROMtradelogWHEREtradeid=110717;交易号tradeid上有索引，但是用EXPLAIN执行时，发现使用了全表扫描，为什么？tradeId的类型是varchar(32)，这条SQL使用tradeid作为数值类型进行比较，会发生一个不可见的转换，会将字符串隐式转换为整数，如下：mysql>SELECT*FROMtradelogWHERECAST(tradidASsignedint)=110717；这也触发了上面的第一条规则，即：索引列不能是函数的一部分。3、隐式编码转换的情况非常隐蔽。我们看这个例子CREATETABLE`trade_detail`(`id`int(11)NOTNULL,`tradeid`varchar(32)DEFAULTNULL,`trade_step`int(11)DEFAULTNULL,/*操作步骤*/`step_info`varchar(32)DEFAULTNULL,/*步骤信息*/PRIMARYKEY(`id`),KEY`tradeid`(`tradeid`))ENGINE=InnoDBDEFAULTCHARSET=utf8;trade_defail是交易明细，tradelog是操作交易明细记录，现在我们要查询id=2的交易的所有操作步骤信息，那么我们就用下面的方法SELECTd.*FROMtradelogl,trade_detaildWHEREd.tradeid=l.tradeidANDl.id=2;由于tradelog和trade_detail这两个表的字符集不同，tradelog的字符集是utf8mb4，trade_detail的字符集是utf8，而utf8mb4是utf8的超集，所以会自动把utf8转为utf8mb4.即上述语句会进行如下转换：SELECTd.*FROMtradelogl,trade_detaildWHERE(CONVERT(d.traideidUSINGutf8mb4))=l.tradeidANDl.id=2;自然地，触发了“索引列不能是函数的一部分”的规则。如何解决？第一种方案当然是把两个表的字符集改成一样的。如果业务量比较大，生产上不方便改，还有一种方案是把utf8mb4转成utf8，如下mysql>SELECTd。*FROMtradelogl,trade_detaildWHEREd.tradeid=CONVERT(l.tradeidUSINGutf8)ANDl.id=2;这样索引列才会生效。4、使用orderby导致的全表扫描SELECT*FROMuserORDERBYageDESC上面的语句在age上加了一个索引，仍然是全表扫描。这是因为我们使用了SELECT*，这导致了查询回表。MySQL考虑到回表的代价比全表扫描大，所以不选择使用索引。如果要使用age索引，我们可以改用覆盖索引：SELECTageFROMuserORDERBYageDESC或者加上limit的条件（数据比较少）SELECT*FROMuserORDERBYageDESClimit10这样就可以使用索引了。在索引列上使用函数是不可避免的。如何使用索引有时我们无法避免在索引列上使用函数，但这样做会导致全表索引。有没有更好的办法？比如我只想记录2016年到2018年7月份的交易记录总数mysql>SELECTcount(*)FROMtradelogWHEREmonth(t_modified)=7;由于索引列是函数的参数，显然不可能使用索引，我们可以对其进行改造搜索基本字段区间如下SELECTcount(*)FROMtradelogWHERE->(t_modified>='2016-7-1'ANDt_modified<'2016-8-1')or->(t_modified>='2017-7-1'ANDt_modified<'2017-8-1')or->(t_modified>='2018-7-1'ANDt_modified<'2018-8-1');前缀索引和索引选择性我们之前说过，比如在长字符串的字段（比如url），我们可以通过伪哈希索引的形式创建索引，避免索引变大变慢。另外，我们还可以采用前缀索引（字符串的一部分）的形式来创建索引，要达到我们的目的，那么这个前缀索引应该如何选择呢？这叫做索引选择性，这里涉及到一个概念，叫做索引选择性：唯一索引值（也称为基数，基数）与数据表中记录总数的比值，该比值越高，选择性越好索引中，unique索引的选择性最好，ratio为1。画外音：我们可以通过SHOWINDEXESFROMtable查看各个索引的基数值，来评估索引设计的合理性。如何选择这个比例？我们可以分别取前3、4、5、6、7个前缀索引，然后比较选择这些前缀索引的选择性，执行如下语句SELECTCOUNT(DISTINCTLEFT(city,3))/COUNT(*)assel3,COUNT(DISTINCTLEFT(city,4))/COUNT(*)assel4,COUNT(DISTINCTLEFT(city,5))/COUNT(*)assel5,COUNT(DISTINCTLEFT(city,6))/COUNT(*)assel6,COUNT(DISTINCTLEFT(city,7))/COUNT(*)assel7FROMcity_demo结果如下sel3sel4sel5sel6sel70.02390.02930.03050.03090.0310可以看出当前缀长度为7时，索引选择性提升的比例已经很小，也就是说应该选择city的前6个字符作为前缀索引，如下ALTERTABLEcity_demoADDKEY(city(6))我们目前使用平均选择性作为指标。有时这还不够，我们必须考虑最坏情况下的选择性。以这个demo为例，可能有人看到choice4和5的prefixindex与choice6和7的选择性相差不大，所以要看4和5的prefixindex分布是不是均匀分布SELECTCOUNT(*)AScnt,LEFT(city,4)ASprefFROMcity_demoGROUPBYprefORDERBYcntDESCLIMIT5cntpref可能出现以下结果305Sant200Toul90Chic20Chan可见分布极不均匀。Sant和Toul有很多前缀索引。两者的选择性都不是很理想，所以在选择前缀索引时也要考虑到最差的选择性，前缀索引虽然可以达到索引空间小、速度快的效果，但也有明显的弱点。MySQL不能用前缀索引做ORDERBY和GROUPBY，也不能用前缀索引做覆盖扫描。前缀索引也可以增加扫描线的数量。假设下表数据和要执行的SQLidemail1zhangssxyz@163.com2zhangs1@163.com3zhangs1@163.com4zhangs1@163.comSELECTid,emailFROMUserWHEREemail='zhangssxyz@xxx.com';如果我们对于电子邮件，设置了整个字段的索引。根据“zhangssxyz@163.com”查询到上表中的相关记录后，再查询这条记录的下一条记录。如果没有记录，则停止扫描。这时我们可以知道只有一行是扫描记录，如果我们用前六个字符（即email(6)）作为前缀索引，显然需要扫描四行记录，获取到行记录后，我们要回到主键索引来判断email字段的值，所以使用前缀索引需要评估它带来的开销。我们可能需要考虑的另一种情况是如果前缀基本相同怎么办。比如现在我们为某个城市的市民创建一个人口信息表。几个数字相同，这种情况下如何建立前缀索引。一种方式就是我们上面说的，为身份证建立哈希索引，另一种方式更巧妙，倒序存储身份证，查询如下：SELECTfield_listFROMtWHEREid_card=reverse('input_id_card_string');那么可以把身份证的后六位作为前缀索引，是不是很巧妙^_^其实上面说的索引选择性也适用于联合索引的设计。如果没有特殊情况，我们一般建议在建立索引时，将选择性最高的列放在最前面。例如，对于以下语句：SELECT*FROMpaymentWHEREstaff_id=xxxANDcustomer_id=xxx；单单针对这条语句，结合(staff_id,customer_id)和(customer_id,staff_id)我们应该建哪个索引呢？我们可以算一下两者的选择性。SELECTCOUNT(DISTINCTstaff_id)/COUNT(*)asstaff_id_selectivity,COUNT(DISTINCTcustomer_id)/COUNT(*)ascustomer_id_selectivity,COUNT(*)FROMpayment结果为：;staff_id_selectivity:0.0001customer_id_selectivity:0.0373COUNT(*9)fromtheselectedid_customer性能为更高，因此应选择customer_id作为第一列。索引设计指南：SamsungIndex上面我们得出了一个索引列顺序的经验法则：将最有选择性的列放在索引的最前面。这种建立在某些场景下可能有用，但通常不如避免随机IO，而且Sorting是如此重要，这里引用一个索引设计中非常著名的准则：Samsungindex。如果一个查询满足三星级索引中所有三星级的索引条件，理论上可以认为我们设计的索引是最好的索引。什么是三星级指数？第一颗星：WHERE之后参与查询的列可以构成单列索引或联合索引。该列已排序，因此无需生成临时表。第三颗星：SELECT对应的列尽量是索引列，即尽量避免回表查询。所以对于下面的语句：SELECTage,name,citywhereage=xxxandname=xxxorderbyage设计的索引应该是(age,name,city)或者(name,age,city)。当然，三星指数是一个理想的标准，实际操作中往往只能满足期望中的一两颗星，考虑如下语句：SELECTage,name,citywhereage>=10ANDage<=20andcity=xxxorderbynamedesc假设我们有为这三列建了一个联合索引，很明显满足了第三颗星（使用了覆盖索引），如果索引是(city,age,name)，虽然满足了第一颗星，但是索引不能用于排序，第二颗星不满足，如果索引是(city,name,age)，那么第二颗星满足，但是此时WHERE中年龄的搜索条件不能满足第一颗星，第三颗星（尽量使用覆盖索引）不能完全满足。想象一下，我想选择多列。这些多列是否应该设置为联合索引？这是索引维护的问题，因为一张表的每一次CURD都伴随着一次索引更新，很可能会频繁伴随分页合并。综上所述，三星级指数只是为我们建立指数提供了一个参考。指标设计应尽可能接近三星级指标的标准。但是在实际场景中，我们一般无法同时满足三星指标。一般我们会优先满足第三颗星（因为回榜成本比较高）至于第一颗和第二颗星，就看实际成本和实际业务场景考虑了。小结本文简要介绍了索引的基本原理、索引的几种类型，并分析了设计索引时应遵循的一些准则。相信大家对索引有了更深入的了解。另外，强烈建议大家学习一下附录中的几本书。本文中的许多示例在文末的参考资料中进行了总结。相信大家阅读经典书籍都会受益匪浅！本文转载自微信♂“证海”，可通过以下二维码关注。转载本文请联系码海公众号。
