在生产中，为了高效查询数据库中的数据，我们经常会在表中的字段上添加索引。你考虑过吗？如何添加索引以提高索引效率。图片来自Pexels。添加尽可能多的索引是否更好？为什么添加索引后有时无法生效？有哪些类型的索引？如何判断一个指标设计的好坏？看完这篇文章，相信你会对索引的原理有一个更清晰的认识。本文将从以下几个方面介绍索引的相关知识：什么是索引及其作用？索引类型高性能索引策略索引设计指南：三星索引什么是索引及其作用？在查找某个词（如“xian”）的具体含义时，我通常会拿起新华字典查一下。可以先检查每一页从头到尾是否有“第一”字样。这种方式（对应数据库中的全表扫描）确实可以找到，但是效率无疑是很低的。相信大家都知道，比较高效的方法是先在首页的索引中查找“first”对应的页码，然后直接跳转到对应的页面进行查找，这样查询时间就大大减少了，这可以是O(1)。数据库中的索引也类似。使用索引来定位要读取的页面，大大减少了需要扫描的行数，可以大大提高效率。简而言之，索引主要有以下几个作用：上面说过，索引可以大大减少扫描的行数索引可以帮助服务器避免排序和临时表索引可以把随机IO变成顺序IO第一点已经上面说了解释，我们来看第二点和第三点，我们先看第二点，假设我们不使用索引，想象一下运行如下语句：SELECT*FROMuserorderbyagedesc;那么MySQL的流程是这样的，扫描所有的行，把所有的行加载到内存中后，通过age排序生成一个临时表，将表排序后返回对应的行给客户端。更糟糕的是，如果这个临时表的大小大于tmp_table_size的值（默认为16M），内存临时表将转换为磁盘临时表，性能会更差。如果添加了索引，则索引本身是有序的。因此，本身从磁盘读取的行数是按age排序的，不会产生临时表，所以不需要额外排序，无疑提高了性能。再来看看随机IO和顺序IO。我先解释一下这两个概念。旋转火锅相信很多人应该都吃过。服务员把菜放在回转的传送带上，等菜传给我们就可以拿到菜了。假设装一个圈需要4分钟，最短等待时间为0（即菜品就在你面前），最长等待时间为4分钟（刚好错过菜品在你面前），那么平均等待时间是2分钟。假设我们现在要取四道菜，这四道菜是随机分配在传送带上的，可以知道这四道菜的平均等待时间是8分钟（随机IO）。加在一起，等待时间只有2分钟（顺序IO）。上面提到的传送带可以类比为磁轨，磁轨上的碟子可以类比为一个扇区中的信息。一个磁盘块（block）由多个相邻的扇区组成，是操作系统读取的最小单位。这样，如果能把信息以块的形式聚集在一起，就可以大大减少磁盘IO时间。这就是顺序IO带来的性能提升。下面我们会看到B+树索引起到了这样的作用。如图：多个扇区组成一个块。如果要读取的信息都在这个block中，那么只需要一次IO读取。但是，如果信息分散分布在一个磁道的各个扇区，或者分布在不同磁道的扇区（寻道时间是随机IO的主要瓶颈），就会造成随机IO，影响性能。我们看一下随机IO的时间分布：seekTime：寻道时间，磁头移动到扇区所在磁道。RotationalLatency：第一步完成后，磁头移动到同一磁道扇区对应位置所需的时间。传输时间：将信息从磁盘读入内存的时间。其中，寻道时间占据了绝大部分时间（约占随机IO时间的40%）。随机IO和顺序IO相差一百倍左右（随机IO：10ms/page，顺序IO0.1ms/page）。可见顺序IO的性能是高的，索引带来的性能提升是很明显的！索引类型主要分为以下几类：B+树索引哈希索引①B+树索引B+树以N叉树的形式存在，有效降低了树的高度，不需要全表扫描查找数据。逐层向下搜索根节点可以快速找到我们的目标数据。每个节点的大小是一个磁盘块的大小。一次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）、全文索引等，生产中用的不是很常用，这里就不多说了。高性能索引策略不同的索引设计选择会对性能产生很大的影响。有的人可能会发现生产中明明加了索引但是没有生效。有时它会生效但不会大大提高搜索性能。对于多列联合索引，哪一列在前，哪一列在后也是有讲究的。我们来看看为什么添加索引后不生效。加了索引不生效可能有以下几种原因：①索引列是表达式的一部分，或者是函数的一部分，如下SQL:SELECTbook_idFROMBOOKWHEREbook_id+1=5;or:SELECTbook_idFROMBOOKWHERETO_DAYS(CURRENT_DATE)-TO_DAYS(gmt_create)<=10上面两个SQL虽然在book_id和gmt_create列上有索引，但是由于是表达式或函数的一部分，无法使用索引，导致全表扫描。②隐式类型转换上面两种情况相信很多人都知道索引不能生效，但是下面的隐式类型转换可能会让很多人犯迷糊。让我们看看下面的例子。假设下表：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执行Fulltablescan，为什么，tradeId的类型是varchar(32)。但是这条SQL使用tradeid作为数值类型进行比较，发生了一个不可见的转换，会把字符串隐式转换成整数，如下：mysql>SELECT*FROMtradelogWHERECAST(tradidASsignedint)=110717;这也触发了第一条规则，即：索引列不能是函数的一部分。③隐式编码转换的情况非常隐蔽。我们来看这个例子：CREATETABLE`trade_detail`(`id`int(11)NOTNULL,`tradeid`varchar(32)DEFAULTNULL,`trade_step`int(11)DEFAULTNULL,/*operationsteps*/`step_info`varchar(32)DEFAULTNULL,/*步骤信息*/PRIMARYKEY(`id`),KEY`tradeid`(`tradeid`))ENGINE=InnoDBDEFAULTCHARSET=utf8;trade_detail是交易明细，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;这样索引列才会生效。④全表扫描SELECT*FROMuserORDERBYageDESCusingorderby上面的语句给age加了索引，但还是造成了全表扫描。这是因为我们使用了SELECT*，导致查询回表。MySQL认为回表的代价比全表扫描大。所以不要选择使用索引。如果要使用age索引，我们可以改用覆盖索引：SELECTageFROMuserORDERBYageDESC或者加上限制条件（数据比较少）：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），我们可以通过伪哈希索引的形式创建索引，避免索引变大变慢。另外，其实我们也可以通过前缀索引（字符串的部分字符）的形式来达到我们的目的，那么这个前缀索引应该如何选择呢，这就涉及到一个概念，叫做索引选择性。索引选择性：唯一索引值（也称为基数、基数）与数据表中记录总数的比值。该比率越高，指数的选择性越好。唯一索引的选择性最好。比例为1。画外音：我们可以通过SHOWINDEXESFROMtable查看各个索引的基数值来评价索引设计的合理性。如何选择这个比例，我们可以分别取前3、4、5、6、7的前缀索引，然后比较选择这些前缀索引的选择性，执行如下语句：SELECTCOUNT(DISTINCTLEFT(city,3))/COUNT(*)assel3,COUNT(DISTINCTLEFT(城市,4))/COUNT(*)assel4,COUNT(DISTINCTLEFT(城市,5))/COUNT(*)assel5,COUNT(DISTINCTLEFT(城市,6))/COUNT(*)assel6,COUNT(DISTINCTLEFT(city,7))/COUNT(*)assel7FROMcity_demo结果如下：可以看出，当前缀长度为7时，索引选择性提升的比例已经非常小，也就是说应该选择city的前六个字符作为前缀索引，如下：ALTERTABLEcity_demoADDKEY(city(6))我们目前使用的是平均选择性作为索引，有时候这样是不够的，而且还必须考虑最坏情况下的选择性。以这个demo为例，可能有人会看到选择4、5的前缀索引的选择性和选择6、7的选择性相差不大，那么就要看选择4的前缀索引是否分布了,5是偶数：SELECTCOUNT(*)AScnt,LEFT(city,4)ASprefFROMcity_demoGROUPBYprefORDERBYcntDESCLIMIT5可能会出现如下结果：可以看到分布极不均匀，带有Sant和Toul的前缀索引数量非常多。两者的选择性都不理想，所以选择前缀索引时也考虑了选择性最差的情况。前缀索引虽然可以实现索引占用空间小、速度快的效果，但是它也有明显的弱点。MySQL不能对ORDERBY和GROUPBY使用前缀索引，覆盖扫描也不能使用前缀索引。前缀索引也可能增加扫描行数。数字。假设有如下表数据和要执行的SQL：SELECTid,emailFROMuserWHEREemail='zhangssxyz@xxx.com';如果我们为email整个字段设置索引，那么上表可以根据"zhangssxyz@163.com"记录记录后，查询这条记录的下一条记录，没有记录则停止扫描。此时可以看到只扫描了一行记录。如果我们使用前六个字符（即email(6)）作为前缀索引，显然需要扫描四行记录，获取到行记录后，还得返回主键索引来判断电子邮件字段的值，所以用前缀索引来评估它带来的开销。我们可能需要考虑的另一种情况是如果前缀基本相同怎么办。比如现在我们为某个城市的市民创建一个人口信息表。几个数字相同，这种情况下如何建立前缀索引。一种方式就是我们上面说的，为身份证建立哈希索引，另一种方式更巧妙，倒序存储身份证，查询如下：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.0373idCOUNT49erfromselectivity:1更高，所以customer_id应该选为第一栏。索引设计指南：SamsungIndex上面我们得出了一个索引列顺序的经验法则：将最有选择性的列放在索引的最前面。这种建立在某些场景下可能有用，但通常不如避免随机IO，而且Sorting是如此重要，这里引用一个索引设计中非常著名的准则：Samsungindex。如果一个查询满足三星级索引中所有三星级的索引条件，理论上可以认为我们设计的索引是最好的索引。什么是三星级指数？第一颗星：WHERE之后参与查询的列可以构成单列索引或联合索引。第二颗星：避免排序，即如果SQL语句中出现orderbycolumn，那么取出来的结果集已经是按列排序的了，不需要生成临时表。第三颗星：SELECT对应的列尽量是索引列，即尽量避免回表查询。所以对于下面的语句：SELECTage,name,citywhereage=xxxandname=xxxorderbyage设计的索引应该是(age,name,city)或者(name,age,city)。当然，三星指标是比较理想的标准，实际操作只能达到预期的一两星。考虑如下语句：SELECTage,name,citywhereage>=10ANDage<=20andcity=xxxorderbynamedesc假设我们分别为这三列建立了联合索引，那么显然符合第三颗星（使用覆盖索引）。如果索引为(city,age,name)，虽然第一个star满足，但是索引不能用来排序，第二个star不满足。如果索引为(city,name,age)，第二颗星满足，但是此时WHERE中年龄的搜索条件不能满足第一颗星。另外，第三颗星（尽量用覆盖指数）不能完全满足。想象一下，如果我要SELECT多列，是不是要把这多列设置成一个联合索引呢？这对于索引维护来说是一个问题，因为每次表的CURD随着索引的更新，很可能会频繁的伴随着分页和分页合并。综上所述，三星指数只是为我们建立指数提供了一个参考，指数设计应尽可能接近三星指数的标准。但在实际场景中，我们一般无法同时满足三星指标。一般我们会优先满足第三颗星（因为退桌成本比较高）。至于一星二星，要看实际成本和实际业务场景考虑。小结本文简要介绍了索引的基本原理、索引的几种类型，并分析了设计索引时应遵循的一些准则。相信大家对索引有了更深入的了解。作者：码海编辑：陶家龙来源：转载自微信公众号码海（ID：seofcode）
