大家好，我是小林。面试中MySQL索引相关的问题基本上都是一系列的问题，从索引的基本原理开始，然后到索引的使用场景，比如：索引底层用的是什么数据结构和算法？MySQLInnoDB为什么选择B+tree作为索引的数据结构？指数什么时候合适？什么时候不需要索引？什么情况下索引会失效？有没有办法优化索引？...今天就带大家巩固一下MySQL索引的知识点。什么是索引？当你想在一本书中查找某个知识的内容时，你会选择一页一页地查找吗？或者在书的目录里？傻子都知道时间宝贵，你当然是选择在书本目录中查找，找到后再翻到相应的页面。书中的目录起到了索引的作用，方便我们快速找到书中的内容，所以索引的设计是以空间换时间的思想。然后切换到数据库，索引的定义是一种帮助存储引擎快速获取数据的数据结构，形象地说索引就是数据所在的目录。所谓存储引擎，说白了就是如何存储数据，如何对存储的数据建立索引，如何更新和查询数据的实现方法。MySQL存储引擎包括MyISAM、InnoDB和Memory，其中InnoDB在MySQL5.5之后成为默认的存储引擎。下图是MySQL的结构图。索引和数据位于存储引擎：索引的分类，你知道索引是什么吗？聚簇索引、主键索引、二级索引、普通索引、唯一索引、哈希索引、B+树索引等等大家肯定都能说出来。那我再问你，你能把这些索引分类吗？可能大家有点懵。实际上，要对这些指标进行分类，首先需要明确这些指标的用途和实现方式，然后将具有相同特征的指标归为一类。我们可以从四个角度对指标进行分类。按“数据结构”分类：B+树索引、Hash索引、全文索引。按“物理存储”分类：聚簇索引（主键索引）、二级索引（辅助索引）。按“字段特性”分类：主键索引、唯一索引、普通索引、前缀索引。按“字段数”分类：单列索引、联合索引。接下来，根据这些角度，对各项指标的特点进行探讨。按数据结构分类从数据结构来看，常见的MySQL索引有B+Tree索引、HASH索引、Full-Text索引。每个存储引擎支持的索引类型不一定相同。我在表中总结了MySQL常见的存储引擎InnoDB、MyISAM、Memory支持的索引类型。InnoDB在MySQL5.5之后成为了MySQL默认的存储引擎，B+Tree索引类型也是MySQL存储引擎使用最广泛的索引类型。在建表时，InnoDB存储引擎会根据不同的场景选择不同的列作为索引：如果有主键，则默认使用主键作为聚簇索引的索引键（key）；如果没有主键，则选择第一个不包含唯一具有NULL值的列作为聚簇索引的索引键（key）；在以上两种都不是的情况下，InnoDB会自动生成一个隐式自增id列作为聚簇索引的索引键（key）；其他索引都属于二级索引（SecondaryIndex），也称为二级索引或非聚集索引。创建的主键索引和二级索引默认使用B+Tree索引。为了让大家了解B+Tree索引的存储和查询过程，下面我用一个简单的例子来说明B+Tree索引在存储数据上的具体实现。首先创建一张以id为主键的product表，如下：CREATETABLE`product`(`id`int(11)NOTNULL,`product_no`varchar(20)DEFAULTNULL,`name`varchar(255)DEFAULTNULL,`price`decimal(10,2)DEFAULTNULL,PRIMARYKEY(`id`)USINGBTREE)CHARACTERSET=utf8COLLATE=utf8_general_ciROW_FORMAT=Dynamic;在product表中，有这几行数据：这几行数据存储在B+Tree索引中是什么样子的？B+Tree是一棵多叉树，叶子节点只存放数据，非叶子节点只存放索引，每个节点中的数据按照主键的顺序存放。每个父节点的索引值都会出现在下层子节点的索引值中，所以在叶子节点中，包含了所有的索引值信息，每个叶子节点指向下一个叶子节点，形成一个链表。如图：主键索引B+Tree通过主键查询商品数据的过程例如，我们执行了如下查询语句，使用主键索引查询id号为5的商品。查询过程是这样的，B+Tree会从上到下逐层查找：比较5和根节点的索引数据(1,10,20)，5在1到10之间，所以按照B+Tree的查找逻辑,找到第二层的索引数据(1,4,7)；在第二层的索引数据(1,4,7)中查找，因为5在4和7之间，所以找到第三层的索引数据(4,5,6)；在叶子节点的索引数据(4,5,6)中查找，找到索引值为5的行数据。数据库的索引和数据都存储在硬盘上，我们可以把读一个节点作为磁盘I/O操作。那么上面整个查询过程一共经历了3个节点，也就是进行了3次I/O操作。B+Tree只需要3-4层高度就可以存储千万级的数据，也就是说从千万级的表中查询目标数据最多需要3-4次磁盘I/O，所以B+Tree相对于ForB-tree和二叉树，最大的优点就是查询效率非常高，因为即使在数据量很大的情况下，查询一个数据的磁盘I/O仍然保持在3-4倍。主键索引B+Tree和二级索引B+Tree通过二级索引查询商品数据的过程区别如下：主键索引B+Tree的叶子节点存储实际数据，并且所有完整的用户记录都保存在主键索引的B+Tree的叶子节点中；二级索引的B+Tree的叶子节点存储的是主键值，而不是实际数据。这里我把前面商品表中的product_no（商品代码）字段设置为二级索引，那么二级索引的B+Tree如下图，其中非叶子的key值为product_no（图中橙色部分），叶子节点存储的数据是主键值（图中绿色部分）。二级索引B+Tree如果我使用product_no二级索引查询商品，如下查询语句：select*fromproductwhereproduct_no='0002';会先在二级索引（商品代码，product_no）中查询B+Tree的索引值，找到对应的叶子节点，然后获取主键值，再通过B+Tree中的树查询对应的叶子节点主键索引，然后获取整行数据。这个过程叫做“还表”，也就是说需要查两棵B+树才能找到数据。如下图：回表但是，当查询的数据可以在二级索引的B+Tree的叶子节点中查询到时，那么就不需要去查主键索引了，比如下面查询语句：selectidfromproductwhereproduct_no='0002';这种在二级索引的B+Tree中查询结果的过程称为“覆盖索引”，即只需要查一个B+Tree就可以找到数据。为什么MySQLInnoDB选择B+tree作为索引的数据结构？B+Tree的索引原理前面已经讲过了。下面我们来回答一下B+Tree相对于B-tree、二叉树或者Hash索引结构有什么优势？1.B+TreevsBTreeB+Tree只在叶子节点存储数据，B-tree的非叶子节点也存储数据，所以B+Tree单个节点的数据量更小。在相同的磁盘I/O次数下，可以查询到更多的节点。另外，B+Tree的叶子节点使用双链表连接，适合MySQL中常见的基于范围的顺序查找，而B-tree做不到这一点。2.B+TreevsBinaryTree对于一个有N个叶子节点的B+Tree，其搜索复杂度为O(logdN)，其中d表示该节点允许的最大子节点数为d。在实际应用中，d值大于100，保证了即使数据达到千万级别，B+Tree的高度依然保持在3~4层左右，也就是说只需要一次数据查询操作tobedone目标数据经过3~4次磁盘I/O操作后即可查询。但是二叉树每个父节点的子节点个数只能是2，也就是说它的查找复杂度是O(logN)，远高于B+Tree，所以二叉树检索目标数据。磁盘I/O更高。3.B+TreevsHashHash在做等值查询时效率极高，搜索复杂度为O(1)。但是Hash表不适合范围查询，它更适合等值查询，这也是B+Tree索引比Hash表索引适用场景更广的原因。按物理存储分类从物理存储的角度，索引分为聚簇索引（主键索引）和二级索引（辅助索引）。这两个区别在上面也有提到：主键索引的B+Tree的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的B+Tree的叶子节点中；二级索引的B+TreeTree的叶子节点存储的是主键值，而不是实际数据。因此，在查询中使用二级索引。如果查询到的数据可以在二级索引中查询到，那么就不需要回表了。这个过程是一个覆盖索引。如果查询的数据不在二级索引中，会先查找二级索引，找到对应的叶子节点，得到主键值，再查找主键索引查询数据。这个过程就是回表。按字段特性分类从字段特性来看，索引分为主键索引、唯一索引、普通索引和前缀索引。主键索引主键索引是建立在主键字段上的索引。一般在建表的时候一起创建。一张表至多有一个主键索引，索引列值不允许空值。创建表时，创建主键索引的方式如下：CREATETABLEtable_name(....PRIMARYKEY(index_column_1)USINGBTREE);唯一索引唯一索引是建立在UNIQUE字段上的索引，一个表可以有多个唯一索引，索引列的值必须是唯一的，但允许空值。创建表时，创建唯一索引的方法如下：CREATETABLEtable_name(....UNIQUEKEY(index_column_1,index_column_2,...));创建表后，如果要创建唯一索引，可以使用这个命令：CREATEUNIQUEINDEXindex_nameONtable_name(index_column_1,index_column_2,...);普通索引普通索引是建立在普通字段上的索引，既不要求字段为主键也不要求字段唯一。创建表时，创建普通索引的方法如下：CREATETABLEtable_name(....INDEX(index_column_1,index_column_2,...));创建表后，如果要创建普通索引，可以使用这个命令：CREATEINDEXindex_nameONtable_name(index_column_1,index_column_2,...);前缀索引前缀索引是指建立在字符类型字段的前几个字符上的索引，而不是建立在整个字段上的索引。前缀索引可以建立在char、varchar、binary或varbinary列的字段类型上。使用前缀索引的目的是减少索引占用的存储空间，提高查询效率。创建表时，创建前缀索引的方式如下：CREATETABLEtable_name(column_list,INDEX(column_name(length)));创建表后，如果要创建前缀索引，可以使用这个命令：CREATEINDEXindex_nameONtable_name(column_name(length));按字段个数分类从字段个数来看，索引分为单列索引和联合索引（复合索引）。建立在单列上的索引称为单列索引，如主键索引；建立在多列上的索引称为联合索引；联合索引将多个字段组合成一个索引，这种索引称为联合索引。例如，product表中的product_no和name字段组合成一个联合索引(product_no,name)，创建联合索引的方法如下：CREATEINDEXindex_product_no_nameONproduct(product_no,name);B+Tree联合索引(product_no,name)示意图如下：联合索引可以看出，联合索引的非叶子节点维护两个字段的值作为B+的key值树。在联合索引中查询数据时，先通过product_no字段进行比较，如果product_no相同则通过name字段进行比较。也就是说，联合索引查询的B+Tree先按product_no排序，如果product_no相同，再按name字段排序。因此，在使用联合索引时，有一个最左匹配原则，即按照最左优先的方式进行索引匹配。比如创建一个(a,b,c)联合索引，如果查询条件为如下，则可以匹配联合索引：wherea=1;wherea=1andb=2andc=3;其中a=1和b=2；需要注意的是，由于查询优化器的存在，where子句中a字段的顺序并不重要。但是，如果查询条件是如下，因为不符合最左匹配原则，无法匹配到联合索引，联合索引就会失败：whereb=2;wherec=3;whereb=2andc=3;另外，建立联合索引时的字段顺序对索引效率也有很大的影响。字段被用于索引过滤的概率越高，被用于索引过滤的概率就越高。在实际开发工作中创建联合索引时，应将区分度高的字段排在最前面，这样区分度高的字段才更有可能被更多的SQL使用。到达。区分度是一个字段列的不同值的个数除以表中的总行数。计算公式如下：判别度计算公式。比如性别区分度很小，不适合建索引或者联合索引中排名列的靠前位置，而UUID等字段更适合做索引或者靠前的位置联合索引列。这里出一道题考大家。对于下面的SQL，如何通过索引来提高查询效率呢？select*fromproductwherestatus=1orderbycreate_timeasc有的同学会认为status单独up创建一个索引就够了。但是更好的方法是为status和create_time列创建一个联合索引，因为这样可以避免MySQL数据库中的文件排序。因为在查询的时候，如果只使用status索引，但是这条语句还需要对create_time进行排序，那么就必须使用filesort，也就是在SQL执行计划中，Extra列会出现Usingfilesort。因此，需要利用索引的有序性，在status和create_time列建立联合索引，使得根据status过滤的数据按照create_time排序，避免文件排序，提高查询效率。什么时候需要/不需要创建索引？索引最大的优点是提高了查询速度，但是索引也有缺点，比如：需要占用物理空间，数字越大，占用的空间越大；创建和维护索引需要时间，这个时间随着数据量的增加而增加；会降低增删改表的效率，因为每次增删改查索引都需要动态维护。所以，索引不是万能钥匙，也是根据场景使用的。何时应用索引？具有唯一限制的字段，例如商品代码；WHERE查询条件中经常用到的字段；GROUPBY和ORDERBY中经常使用的字段；什么时候不需要创建索引？WHERE条件、GROUPBY、ORDER对于BY中没有用到的字段，索引的值是为了快速定位。如果定位不到的字段，通常不需要创建索引。字段中有很多重复数据，不需要建立索引，比如gender字段，只有male和female。当表数据太小时，不需要创建索引；频繁更新的字段不需要创建索引，比如电商项目的用户余额，因为索引字段经常被修改，意味着需要经常重建索引；有什么办法可以优化索引吗？?下面介绍一些常见的索引优化方式：前缀索引优化；覆盖指数优化；字符建索引，那为什么要用前缀建索引呢？使用前缀索引是为了减小索引字段的大小，可以增加索引页中存储的索引值，有效提高索引的查询速度。当一些大的字符串字段作为索引时，使用前缀索引可以帮助我们减少索引项的大小。但是前缀索引有一定的局限性，例如：orderby不能使用前缀索引；前缀索引不能用作覆盖索引；覆盖索引优化覆盖索引是指SQL中查询的所有字段，在索引B+Tree的叶子结点上能找到的那些索引，可以从二级索引查询获取记录，而不是聚集索引查询，这样可以避免返回表的操作。假设我们只需要查询商品的名称和价格，有什么办法可以避免回表呢？我们可以创建一个联合索引，即“产品ID、名称、价格”作为一个联合索引。如果数据存在于索引中，则查询不会再次检索主键索引，从而避免回表。因此，使用覆盖索引的好处是不需要查询包含整行记录的所有信息，减少了大量的I/O操作。主键索引最好是自增的。我们在建表的时候，主键索引默认设置为自增。我们为什么要做这个？有什么好处？InnoDB创建的主键索引默认是聚簇索引，数据存储在B+Tree的叶子节点上。也就是说，同一个叶子节点中的每条数据都是按照主键的顺序存储的。因此，每当插入一条新的数据时，数据库都会根据主键插入到对应的叶子节点中。如果我们使用自增主键，每次插入的新数据都会按顺序添加到当前索引节点的位置，不需要移动已有数据。当页面已满时，将自动打开一个新页面。这种插入数据的方法非常有效，因为不需要重新移动数据。如果我们使用非自增主键，由于每次插入时主键的索引值都是随机的，每次插入新数据时，可能会插入到已有数据页中间的某处，这将不得不移动其他数据以满足新数据的插入，甚至需要将数据从一个页面复制到另一个页面，我们通常称这种情况为页面拆分。分页还可能造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。例如，假设一个数据页中的数据为1、3、5、9，数据页已满，现在要插入一个数据7，需要将数据页拆分为两个数据页：innodbpagesplit发生pagesplit时，需要将一页的记录移动到另一页，性能会受到影响，页空间的利用率会降低，造成存储空间的浪费。但是，如果记录是顺序插入的，比如插入数据11，则只需要打开一个新的数据页，不会出现分页：打开一个新的数据页。因此，在使用InnoDB存储引擎时，如果没有特殊的业务需求，建议使用自增字段作为主键。防止索引失效使用索引并不代表查询的时候一定会用到索引，所以我们需要清楚什么情况下会导致索引失效，避免写出使索引失效的查询语句，否则查询效率很low的。我之前写过一篇关于索引失败的文章。如果你想了解更多，可以阅读这篇文章：谁没有接触过索引故障？这里简单介绍下索引失效：当我们使用左右模糊匹配时，即like%xx或者like%xx%都会导致索引失效；当我们在查询条件中对索引列进行计算、函数和类型转换操作时，这些情况下的索引失效是因为查询过程需要扫描整个索引返回表，成本比直接全表??扫描，所以优化最终选择使用全表扫描。要正确使用联合索引，需要遵循最左匹配原则，即按照最左优先的方式匹配索引，否则索引将失效。在WHERE子句中，如果OR之前的条件列是索引列，而OR之后的条件列不是索引列，则索引无效。为了更好地利用索引，应该将索引列设置为NOTNULL约束。我上面提到的是一个常见的索引失败场景。在实际过程中，还可能出现其他索引失效场景。这时候我们就需要查看执行计划，根据执行计划显示的数据判断查询语句是否使用了索引。如下图所示，是一个没有使用索引的查询语句，是全表扫描。对于执行计划，参数为：possible_keys字段表示可能使用的索引；key字段表示实际使用的索引，如果此项为NULL，表示没有使用该索引；key_len表示索引的长度；rows表示扫描的数据行数。type表示数据扫描的类型，我们需要重点关注这个。类型字段描述查找所需数据时使用的扫描方法。常见扫描类型执行效率从低到高的顺序为：ALL（全表扫描）；索引（全索引扫描）；范围（索引范围扫描）；ref（非唯一索引扫描）；eq_ref（唯一索引扫描）；const（结果只有一个主键或唯一索引扫描）。考虑到查询效率，尽量避免全表扫描和全索引扫描。小结本期主要介绍索引的原理、分类和使用。我在下表中总结了要点
