面试中MySQL 索引相关的问题基本都是一系列问题都是先从索引的基本原理再到索引的使用场景比如索引底层使用了什么数据结构和算法为什么 MySQL InnoDB 选择 Btree 作为索引的数据结构什么时候适用索引什么时候不需要创建索引什么情况下索引会失效有什么优化索引的方法.....今天就带大家夯实 MySQL 索引的知识点。#什么是索引当你想查阅书中某个知识的内容你会选择一页一页的找呢还是在书的目录去找呢傻瓜都知道时间是宝贵的当然是选择在书的目录去找找到后再翻到对应的页。书中的目录就是充当索引的角色方便我们快速查找书中的内容所以索引是以空间换时间的设计思想。那换到数据库中索引的定义就是帮助存储引擎快速获取数据的一种数据结构形象的说就是索引是数据的目录。所谓的存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。MySQL 存储引擎有 MyISAM 、InnoDB、Memory其中 InnoDB 是在 MySQL 5.5 之后成为默认的存储引擎。下图是 MySQL 的结构图索引和数据就是位于存储引擎中#索引的分类你知道索引有哪些吗大家肯定都能霹雳啪啦地说出聚簇索引、主键索引、二级索引、普通索引、唯一索引、hash索引、B树索引等等。然后再问你你能将这些索引分一下类吗可能大家就有点模糊了。其实要对这些索引进行分类要清楚这些索引的使用和实现方式然后再针对有相同特点的索引归为一类。我们可以按照四个角度来分类索引。按「数据结构」分类Btree索引、Hash索引、Full-text索引。按「物理存储」分类聚簇索引主键索引、二级索引辅助索引。按「字段特性」分类主键索引、唯一索引、普通索引、前缀索引。按「字段个数」分类单列索引、联合索引。接下来按照这些角度来说说各类索引的特点。#按数据结构分类从数据结构的角度来看MySQL 常见索引有 BTree 索引、HASH 索引、Full-Text 索引。每一种存储引擎支持的索引类型不一定相同我在表中总结了 MySQL 常见的存储引擎 InnoDB、MyISAM 和 Memory 分别支持的索引类型。InnoDB 是在 MySQL 5.5 之后成为默认的 MySQL 存储引擎BTree 索引类型也是 MySQL 存储引擎采用最多的索引类型。在创建表时InnoDB 存储引擎会根据不同的场景选择不同的列作为索引如果有主键默认会使用主键作为聚簇索引的索引键key如果没有主键就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键key在上面两个都没有的情况下InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键key其它索引都属于辅助索引Secondary Index也被称为二级索引或非聚簇索引。创建的主键索引和二级索引默认使用的是 BTree 索引。为了让大家理解 BTree 索引的存储和查询的过程接下来我通过一个简单例子说明一下 BTree 索引在存储数据中的具体实现。先创建一张商品表id 为主键如下CREATE TABLE product ( id int(11) NOT NULL, product_no varchar(20) DEFAULT NULL, name varchar(255) DEFAULT NULL, price decimal(10, 2) DEFAULT NULL, PRIMARY KEY (id) USING BTREE ) CHARACTER SET utf8 COLLATE utf8_general_ci ROW_FORMAT Dynamic;商品表里有这些行数据这些行数据存储在 BTree 索引时是长什么样子的BTree 是一种多叉树叶子节点才存放数据非叶子节点只存放索引而且每个节点里的数据是按主键顺序存放的。每一层父节点的索引值都会出现在下层子节点的索引值中因此在叶子节点中包括了所有的索引值信息并且每一个叶子节点都有两个指针分别指向下一个叶子节点和上一个叶子节点形成一个双向链表。主键索引的 BTree 如图所示图中叶子节点之间我画了单向链表但是实际上是双向链表原图我找不到了修改不了偷个懒我不重画了大家脑补成双向链表就行#通过主键查询商品数据的过程比如我们执行了下面这条查询语句select * from product where id 5;这条语句使用了主键索引查询 id 号为 5 的商品。查询过程是这样的BTree 会自顶向下逐层进行查找将 5 与根节点的索引数据 (11020) 比较5 在 1 和 10 之间所以根据 BTree的搜索逻辑找到第二层的索引数据 (147)在第二层的索引数据 (147)中进行查找因为 5 在 4 和 7 之间所以找到第三层的索引数据456在叶子节点的索引数据456中进行查找然后我们找到了索引值为 5 的行数据。数据库的索引和数据都是存储在硬盘的我们可以把读取一个节点当作一次磁盘 I/O 操作。那么上面的整个查询过程一共经历了 3 个节点也就是进行了 3 次 I/O 操作。BTree 存储千万级的数据只需要 3-4 层高度就可以满足这意味着从千万级的表查询目标数据最多需要 3-4 次磁盘 I/O所以BTree 相比于 B 树和二叉树来说最大的优势在于查询效率很高因为即使在数据量很大的情况查询一个数据的磁盘 I/O 依然维持在 3-4次。#通过二级索引查询商品数据的过程主键索引的 BTree 和二级索引的 BTree 区别如下主键索引的 BTree 的叶子节点存放的是实际数据所有完整的用户记录都存放在主键索引的 BTree 的叶子节点里二级索引的 BTree 的叶子节点存放的是主键值而不是实际数据。我这里将前面的商品表中的 product_no 商品编码字段设置为二级索引那么二级索引的 BTree 如下图图中叶子节点之间我画了单向链表但是实际上是双向链表原图我找不到了修改不了偷个懒我不重画了大家脑补成双向链表就行。其中非叶子的 key 值是 product_no图中橙色部分叶子节点存储的数据是主键值图中绿色部分。如果我用 product_no 二级索引查询商品如下查询语句select * from product where product_no 0002;会先检二级索引中的 BTree 的索引值商品编码product_no找到对应的叶子节点然后获取主键值然后再通过主键索引中的 BTree 树查询到对应的叶子节点然后获取整行数据。这个过程叫「回表」也就是说要查两个 BTree 才能查到数据。如下图图中叶子节点之间我画了单向链表但是实际上是双向链表原图我找不到了修改不了偷个懒我不重画了大家脑补成双向链表就行不过当查询的数据是能在二级索引的 BTree 的叶子节点里查询到这时就不用再查主键索引查比如下面这条查询语句select id from product where product_no 0002;这种在二级索引的 BTree 就能查询到结果的过程就叫作「覆盖索引」也就是只需要查一个 BTree 就能找到数据。#为什么 MySQL InnoDB 选择 Btree 作为索引的数据结构前面已经讲了 BTree 的索引原理现在就来回答一下 BTree 相比于 B 树、二叉树或 Hash 索引结构的优势在哪儿之前我也专门写过一篇文章想详细了解的可以看这篇「女朋友问我为什么 MySQL 喜欢 B 树我笑着画了 20 张图 (opens new window)」这里就简单做个比对。1、BTree vs B TreeBTree 只在叶子节点存储数据而 B 树 的非叶子节点也要存储数据所以 BTree 的单个节点的数据量更小在相同的磁盘 I/O 次数下就能查询更多的节点。另外BTree 叶子节点采用的是双链表连接适合 MySQL 中常见的基于范围的顺序查找而 B 树无法做到这一点。2、BTree vs 二叉树对于有 N 个叶子节点的 BTree其搜索复杂度为O(logdN)其中 d 表示节点允许的最大子节点个数为 d 个。在实际的应用当中 d 值是大于100的这样就保证了即使数据达到千万级别时BTree 的高度依然维持在 3~4 层左右也就是说一次数据查询操作只需要做 3~4 次的磁盘 I/O 操作就能查询到目标数据。而二叉树的每个父节点的儿子节点个数只能是 2 个意味着其搜索复杂度为O(logN)这已经比 BTree 高出不少因此二叉树检索到目标数据所经历的磁盘 I/O 次数要更多。3、BTree vs HashHash 在做等值查询的时候效率贼快搜索复杂度为 O(1)。但是 Hash 表不适合做范围查询它更适合做等值的查询这也是 BTree 索引要比 Hash 表索引有着更广泛的适用场景的原因。#按物理存储分类从物理存储的角度来看索引分为聚簇索引主键索引、二级索引辅助索引。这两个区别在前面也提到了主键索引的 BTree 的叶子节点存放的是实际数据所有完整的用户记录都存放在主键索引的 BTree 的叶子节点里二级索引的 BTree 的叶子节点存放的是主键值而不是实际数据。所以在查询时使用了二级索引如果查询的数据能在二级索引里查询的到那么就不需要回表这个过程就是覆盖索引。如果查询的数据不在二级索引里就会先检索二级索引找到对应的叶子节点获取到主键值后然后再检索主键索引就能查询到数据了这个过程就是回表。#按字段特性分类从字段特性的角度来看索引分为主键索引、唯一索引、普通索引、前缀索引。#主键索引主键索引就是建立在主键字段上的索引通常在创建表的时候一起创建一张表最多只有一个主键索引索引列的值不允许有空值。在创建表时创建主键索引的方式如下CREATE TABLE table_name ( .... PRIMARY KEY (index_column_1) USING BTREE );#唯一索引唯一索引建立在 UNIQUE 字段上的索引一张表可以有多个唯一索引索引列的值必须唯一但是允许有空值。在创建表时创建唯一索引的方式如下CREATE TABLE table_name ( .... UNIQUE KEY(index_column_1,index_column_2,...) );建表后如果要创建唯一索引可以使用这面这条命令CREATE UNIQUE INDEX index_name ON table_name(index_column_1,index_column_2,...);#普通索引普通索引就是建立在普通字段上的索引既不要求字段为主键也不要求字段为 UNIQUE。在创建表时创建普通索引的方式如下CREATE TABLE table_name ( .... INDEX(index_column_1,index_column_2,...) );建表后如果要创建普通索引可以使用这面这条命令CREATE INDEX index_name ON table_name(index_column_1,index_column_2,...);#前缀索引前缀索引是指对字符类型字段的前几个字符建立的索引而不是在整个字段上建立的索引前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。使用前缀索引的目的是为了减少索引占用的存储空间提升查询效率。在创建表时创建前缀索引的方式如下CREATE TABLE table_name( column_list, INDEX(column_name(length)) );建表后如果要创建前缀索引可以使用这面这条命令CREATE INDEX index_name ON table_name(column_name(length));#按字段个数分类从字段个数的角度来看索引分为单列索引、联合索引复合索引。建立在单列上的索引称为单列索引比如主键索引建立在多列上的索引称为联合索引#联合索引通过将多个字段组合成一个索引该索引就被称为联合索引。比如将商品表中的 product_no 和 name 字段组合成联合索引(product_no, name)创建联合索引的方式如下CREATE INDEX index_product_no_name ON product(product_no, name);联合索引(product_no, name)的 BTree 示意图如下图中叶子节点之间我画了单向链表但是实际上是双向链表原图我找不到了修改不了偷个懒我不重画了大家脑补成双向链表就行。可以看到联合索引的非叶子节点用两个字段的值作为 BTree 的 key 值。当在联合索引查询数据时先按 product_no 字段比较在 product_no 相同的情况下再按 name 字段比较。也就是说联合索引查询的 BTree 是先按 product_no 进行排序然后再 product_no 相同的情况再按 name 字段排序。因此使用联合索引时存在最左匹配原则也就是按照最左优先的方式进行索引的匹配。在使用联合索引进行查询的时候如果不遵循「最左匹配原则」联合索引会失效这样就无法利用到索引快速查询的特性了。比如如果创建了一个(a, b, c)联合索引如果查询条件是以下这几种就可以匹配上联合索引where a1where a1 and b2 and c3where a1 and b2需要注意的是因为有查询优化器所以 a 字段在 where 子句的顺序并不重要。但是如果查询条件是以下这几种因为不符合最左匹配原则所以就无法匹配上联合索引联合索引就会失效:where b2where c3where b2 and c3上面这些查询条件之所以会失效是因为(a, b, c)联合索引是先按 a 排序在 a 相同的情况再按 b 排序在 b 相同的情况再按 c 排序。所以b 和 c 是全局无序局部相对有序的这样在没有遵循最左匹配原则的情况下是无法利用到索引的。我这里举联合索引ab的例子该联合索引的 B Tree 如下图中叶子节点之间我画了单向链表但是实际上是双向链表原图我找不到了修改不了偷个懒我不重画了大家脑补成双向链表就行。可以看到a 是全局有序的1, 2, 2, 3, 4, 5, 6, 7 ,8而 b 是全局是无序的12782381052。因此直接执行where b 2这种查询条件没有办法利用联合索引的利用索引的前提是索引里的 key 是有序的。只有在 a 相同的情况才b 才是有序的比如 a 等于 2 的时候b 的值为78这时就是有序的这个有序状态是局部的因此执行where a 2 and b 7是 a 和 b 字段能用到联合索引的也就是联合索引生效了。#联合索引范围查询联合索引有一些特殊情况并不是查询过程使用了联合索引查询就代表联合索引中的所有字段都用到了联合索引进行索引查询也就是可能存在部分字段用到联合索引的 BTree部分字段没有用到联合索引的 BTree 的情况。这种特殊情况就发生在范围查询。联合索引的最左匹配原则会一直向右匹配直到遇到「范围查询」就会停止匹配。也就是范围查询的字段可以用到联合索引但是在范围查询字段的后面的字段无法用到联合索引。范围查询有很多种那到底是哪些范围查询会导致联合索引的最左匹配原则会停止匹配呢接下来举例几个范围查例子。Q1:select * from t_table where a 1 and b 2联合索引a, b哪一个字段用到了联合索引的 BTree由于联合索引二级索引是先按照 a 字段的值排序的所以符合 a 1 条件的二级索引记录肯定是相邻于是在进行索引扫描的时候可以定位到符合 a 1 条件的第一条记录然后沿着记录所在的链表向后扫描直到某条记录不符合 a 1 条件位置。所以 a 字段可以在联合索引的 BTree 中进行索引查询。但是在符合 a 1 条件的二级索引记录的范围里b 字段的值是无序的。比如前面图的联合索引的 B Tree 里下面这三条记录的 a 字段的值都符合 a 1 查询条件而 b 字段的值是无序的a 字段值为 5 的记录该记录的 b 字段值为 8a 字段值为 6 的记录该记录的 b 字段值为 10a 字段值为 7 的记录该记录的 b 字段值为 5因此我们不能根据查询条件 b 2 来进一步减少需要扫描的记录数量b 字段无法利用联合索引进行索引查询的意思。所以在执行 Q1 这条查询语句的时候对应的扫描区间是 (2, ∞)形成该扫描区间的边界条件是 a 1与 b 2 无关。因此Q1 这条查询语句只有 a 字段用到了联合索引进行索引查询而 b 字段并没有使用到联合索引。我们也可以在执行计划中的 key_len 知道这一点在使用联合索引进行查询的时候通过 key_len 我们可以知道优化器具体使用了多少个字段的搜索条件来形成扫描区间的边界条件。举例个例子 a 和 b 都是 int 类型且不为 NULL 的字段那么 Q1 这条查询语句执行计划如下可以看到 key_len 为 4 字节如果字段允许为 NULL就在字段类型占用的字节数上加 1也就是 5 字节说明只有 a 字段用到了联合索引进行索引查询而且可以看到即使 b 字段没用到联合索引key 为 idx_a_b说明 Q1 查询语句使用了 idx_a_b 联合索引。通过 Q1 查询语句我们可以知道a 字段使用了 进行范围查询联合索引的最左匹配原则在遇到 a 字段的范围查询 后就停止匹配了因此 b 字段并没有使用到联合索引。Q2:select * from t_table where a 1 and b 2联合索引a, b哪一个字段用到了联合索引的 BTreeQ2 和 Q1 的查询语句很像唯一的区别就是 a 字段的查询条件「大于等于」。由于联合索引二级索引是先按照 a 字段的值排序的所以符合 1 条件的二级索引记录肯定是相邻于是在进行索引扫描的时候可以定位到符合 1 条件的第一条记录然后沿着记录所在的链表向后扫描直到某条记录不符合 a 1 条件位置。所以 a 字段可以在联合索引的 BTree 中进行索引查询。虽然在符合 a 1 条件的二级索引记录的范围里b 字段的值是「无序」的但是对于符合 a 1 的二级索引记录的范围里b 字段的值是「有序」的因为对于联合索引是先按照 a 字段的值排序然后在 a 字段的值相同的情况下再按照 b 字段的值进行排序。从这我们就可以分析得出只有 a1 and b2 走了联合索引然后后续的 a1 其实还是不走联合索引。于是在确定需要扫描的二级索引的范围时当二级索引记录的 a 字段值为 1 时可以通过 b 2 条件减少需要扫描的二级索引记录范围b 字段可以利用联合索引进行索引查询的意思。也就是说从符合 a 1 and b 2 条件的第一条记录开始扫描而不需要从第一个 a 字段值为 1 的记录开始扫描。所以Q2 这条查询语句 a 和 b 字段都用到了联合索引进行索引查询。我们也可以在执行计划中的 key_len 知道这一点。执行计划如下可以看到 key_len 为 8 字节说明优化器使用了 2 个字段的查询条件来形成扫描区间的边界条件也就是 a 和 b 字段都用到了联合索引进行索引查询。通过 Q2 查询语句我们可以知道虽然 a 字段使用了 进行范围查询但是联合索引的最左匹配原则并没有在遇到 a 字段的范围查询 后就停止匹配了b 字段还是可以用到了联合索引的。TIP读者问题如果MySQL表有联合索引ab为什么MySQL的 select * from table where a2 and b1不走b索引而 select * from table where a2 and b1 能走b索引回答执行where a2 and b1 会被拆成两个sqlwhere a2 and b1 这一条 b 不能走索引where a2 and b1 这一条 ab都能走索引为什么 where a2 and b1 这一条 b 不能走索引举个例子a1 的情况下有这些记录a2b8a2b9a3b2a3b3a1 的查询并不是按 a 2 、a 3这样去扫描索引的而是 a 走了索引找到了第一个大于1的记录也就是a2b8开始接着就会根据叶子节点间的链表往后扫描记录。这时候 b 并不是有序的b8b9b2b3无法利用二分查找的特性快速查找到b。Q3:SELECT * FROM t_table WHERE a BETWEEN 2 AND 8 AND b 2联合索引a, b哪一个字段用到了联合索引的 BTreeQ3 查询条件中a BETWEEN 2 AND 8的意思是查询 a 字段的值在 2 和 8 之间的记录。不同的数据库对 BETWEEN ... AND 处理方式是有差异的。在 MySQL 中BETWEEN 包含了 value1 和 value2 边界值类似于 and 。而有的数据库则不包含 value1 和 value2 边界值类似于 and 。这里我们只讨论 MySQL。由于 MySQL 的 BETWEEN 包含 value1 和 value2 边界值所以类似于 Q2 查询语句因此Q3 这条查询语句 a 和 b 字段都用到了联合索引进行索引查询。我们也可以在执行计划中的 key_len 知道这一点。执行计划如下可以看到 key_len 为 8 字节说明优化器使用了 2 个字段的查询条件来形成扫描区间的边界条件也就是 a 和 b 字段都用到了联合索引进行索引查询。通过 Q3 查询语句我们可以知道虽然 a 字段使用了 BETWEEN 进行范围查询但是联合索引的最左匹配原则并没有在遇到 a 字段的范围查询 BETWEEN后就停止匹配了b 字段还是可以用到了联合索引的。Q4:SELECT * FROM t_user WHERE name like j% and age 22联合索引name, age哪一个字段用到了联合索引的 BTree由于联合索引二级索引是先按照 name 字段的值排序的所以前缀为 ‘j’ 的 name 字段的二级索引记录都是相邻的 于是在进行索引扫描的时候可以定位到符合前缀为 ‘j’ 的 name 字段的第一条记录然后沿着记录所在的链表向后扫描直到某条记录的 name 前缀不为 ‘j’ 为止。所以 a 字段可以在联合索引的 BTree 中进行索引查询形成的扫描区间是[j,k)。注意 j 是闭区间。如下图虽然在符合前缀为 ‘j’ 的 name 字段的二级索引记录的范围里age 字段的值是「无序」的但是对于符合 name j 的二级索引记录的范围里age字段的值是「有序」的因为对于联合索引是先按照 name 字段的值排序然后在 name 字段的值相同的情况下再按照 age 字段的值进行排序。于是在确定需要扫描的二级索引的范围时当二级索引记录的 name 字段值为 ‘j’ 时可以通过 age 22 条件减少需要扫描的二级索引记录范围age 字段可以利用联合索引进行索引查询的意思。也就是说从符合name j and age 22条件的第一条记录时开始扫描而不需要从第一个 name 为 j 的记录开始扫描 。如下图的右边所以Q4 这条查询语句 a 和 b 字段都用到了联合索引进行索引查询。我们也可以在执行计划中的 key_len 知道这一点。本次例子中name 字段的类型是 varchar(30) 且不为 NULL数据库表使用了 utf8mb4 字符集一个字符集为 utf8mb4 的字符是 4 个字节因此 name 字段的实际数据最多占用的存储空间长度是 120 字节30 x 4然后因为 name 是变长类型的字段需要再加 2 字节用于存储该字段实际数据的长度值也就是 name 的 key_len 为 122。age 字段的类型是 int 且不为 NULLkey_len 为 4。TIP可能有的同学对于「因为 name 是变长类型的字段需要再加 2 字节」这句话有疑问。之前这篇文章 (opens new window)说「如果变长字段允许存储的最大字节数小于等于 255 字节就会用 1 字节表示变长字段的长度」而这里为什么是 2 字节key_len 的显示比较特殊行格式是由 innodb存 储引擎实现的而执行计划是在server 层生成的所以它不会去问 innodb 存储引擎可变字段的长度占用多少字节而是不管三七二十一都使用 2 字节表示可变字段的长度。毕竟 key_len 的目的只是为了告诉你索引查询中用了哪些索引字段而不是为了准确告诉这个字段占用多少字节空间。Q4 查询语句的执行计划如下可以看到 key_len 为 126 字节name 的 key_len 为 122age 的 key_len 为 4说明优化器使用了 2 个字段的查询条件来形成扫描区间的边界条件也就是 name 和 age 字段都用到了联合索引进行索引查询。通过 Q4 查询语句我们可以知道虽然 name 字段使用了 like 前缀匹配进行范围查询但是联合索引的最左匹配原则并没有在遇到 name 字段的范围查询 like j%后就停止匹配了age 字段还是可以用到了联合索引的。综上所示联合索引的最左匹配原则在遇到范围查询如 、的时候就会停止匹配也就是范围查询的字段可以用到联合索引但是在范围查询字段的后面的字段无法用到联合索引。注意对于 、、BETWEEN、like 前缀匹配的范围查询并不会停止匹配前面我也用了四个例子说明了。#索引下推现在我们知道对于联合索引a, b在执行select * from table where a 1 and b 2语句的时候只有 a 字段能用到索引那在联合索引的 BTree 找到第一个满足条件的主键值ID 为 2后还需要判断其他条件是否满足看 b 是否等于 2那是在联合索引里判断还是回主键索引去判断呢在 MySQL 5.6 之前只能从 ID2 主键值开始一个个回表到「主键索引」上找出数据行再对比 b 字段值。而 MySQL 5.6 引入的索引下推优化index condition pushdown)可以在联合索引遍历过程中对联合索引中包含的字段先做判断直接过滤掉不满足条件的记录减少回表次数。当你的查询语句的执行计划里出现了 Extra 为Using index condition那么说明使用了索引下推的优化。#索引区分度另外建立联合索引时的字段顺序对索引效率也有很大影响。越靠前的字段被用于索引过滤的概率越高实际开发工作中建立联合索引时要把区分度大的字段排在前面这样区分度大的字段越有可能被更多的 SQL 使用到。区分度就是某个字段 column 不同值的个数「除以」表的总行数计算公式如下比如性别的区分度就很小不适合建立索引或不适合排在联合索引列的靠前的位置而 UUID 这类字段就比较适合做索引或排在联合索引列的靠前的位置。因为如果索引的区分度很小假设字段的值分布均匀那么无论搜索哪个值都可能得到一半的数据。在这些情况下还不如不要索引因为 MySQL 还有一个查询优化器查询优化器发现某个值出现在表的数据行中的百分比惯用的百分比界线是30%很高的时候它一般会忽略索引进行全表扫描。#联合索引进行排序这里出一个题目针对针对下面这条 SQL你怎么通过索引来提高查询效率呢select * from order where status 1 order by create_time asc有的同学会认为单独给 status 建立一个索引就可以了。但是更好的方式给 status 和 create_time 列建立一个联合索引因为这样可以避免 MySQL 数据库发生文件排序。因为在查询时如果只用到 status 的索引但是这条语句还要对 create_time 排序这时就要用文件排序 filesort也就是在 SQL 执行计划中Extra 列会出现 Using filesort。所以要利用索引的有序性在 status 和 create_time 列建立联合索引这样根据 status 筛选后的数据就是按照 create_time 排好序的避免在文件排序提高了查询效率。#什么时候需要 / 不需要创建索引索引最大的好处是提高查询速度但是索引也是有缺点的比如需要占用物理空间数量越大占用空间越大创建索引和维护索引要耗费时间这种时间随着数据量的增加而增大会降低表的增删改的效率因为每次增删改索引B 树为了维护索引有序性都需要进行动态维护。所以索引不是万能钥匙它也是根据场景来使用的。#什么时候适用索引字段有唯一性限制的比如商品编码经常用于WHERE查询条件的字段这样能够提高整个表的查询速度如果查询条件不是一个字段可以建立联合索引。经常用于GROUP BY和ORDER BY的字段这样在查询的时候就不需要再去做一次排序了因为我们都已经知道了建立索引之后在 BTree 中的记录都是排序好的。#什么时候不需要创建索引WHERE条件GROUP BYORDER BY里用不到的字段索引的价值是快速定位如果起不到定位的字段通常是不需要创建索引的因为索引是会占用物理空间的。字段中存在大量重复数据不需要创建索引比如性别字段只有男女如果数据库表中男女的记录分布均匀那么无论搜索哪个值都可能得到一半的数据。在这些情况下还不如不要索引因为 MySQL 还有一个查询优化器查询优化器发现某个值出现在表的数据行中的百分比很高的时候它一般会忽略索引进行全表扫描。表数据太少的时候不需要创建索引经常更新的字段不用创建索引比如不要对电商项目的用户余额建立索引因为索引字段频繁修改由于要维护 BTree的有序性那么就需要频繁的重建索引这个过程是会影响数据库性能的。#有什么优化索引的方法这里说一下几种常见优化索引的方法前缀索引优化覆盖索引优化主键索引最好是自增的防止索引失效#前缀索引优化前缀索引顾名思义就是使用某个字段中字符串的前几个字符建立索引那我们为什么需要使用前缀来建立索引呢使用前缀索引是为了减小索引字段大小可以增加一个索引页中存储的索引值有效提高索引的查询速度。在一些大字符串的字段作为索引时使用前缀索引可以帮助我们减小索引项的大小。不过前缀索引有一定的局限性例如order by 就无法使用前缀索引无法把前缀索引用作覆盖索引#覆盖索引优化覆盖索引是指 SQL 中 query 的所有字段在索引 BTree 的叶子节点上都能找得到的那些索引从二级索引中查询得到记录而不需要通过聚簇索引查询获得可以避免回表的操作。假设我们只需要查询商品的名称、价格有什么方式可以避免回表呢我们可以建立一个联合索引即「商品ID、名称、价格」作为一个联合索引。如果索引中存在这些数据查询将不会再次检索主键索引从而避免回表。所以使用覆盖索引的好处就是不需要查询出包含整行记录的所有信息也就减少了大量的 I/O 操作。#主键索引最好是自增的我们在建表的时候都会默认将主键索引设置为自增的具体为什么要这样做呢又什么好处InnoDB 创建主键索引默认为聚簇索引数据被存放在了 BTree 的叶子节点上。也就是说同一个叶子节点内的各个数据是按主键顺序存放的因此每当有一条新的数据插入时数据库会根据主键将其插入到对应的叶子节点中。如果我们使用自增主键那么每次插入的新数据就会按顺序添加到当前索引节点的位置不需要移动已有的数据当页面写满就会自动开辟一个新页面。因为每次插入一条新记录都是追加操作不需要重新移动数据因此这种插入数据的方法效率非常高。如果我们使用非自增主键由于每次插入主键的索引值都是随机的因此每次插入新的数据时就可能会插入到现有数据页中间的某个位置这将不得不移动其它数据来满足新数据的插入甚至需要从一个页面复制数据到另外一个页面我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片导致索引结构不紧凑从而影响查询效率。举个例子假设某个数据页中的数据是1、3、5、9且数据页满了现在准备插入一个数据7则需要把数据页分割为两个数据页出现页分裂时需要将一个页的记录移动到另外一个页性能会受到影响同时页空间的利用率下降造成存储空间的浪费。而如果记录是顺序插入的例如插入数据11则只需开辟新的数据页也就不会发生页分裂因此在使用 InnoDB 存储引擎时如果没有特别的业务需求建议使用自增字段作为主键。另外主键字段的长度不要太大因为主键字段长度越小意味着二级索引的叶子节点越小二级索引的叶子节点存放的数据是主键值这样二级索引占用的空间也就越小。#索引最好设置为 NOT NULL为了更好的利用索引索引列要设置为 NOT NULL 约束。有两个原因第一原因索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂更加难以优化因为可为 NULL 的列会使索引、索引统计和值比较都更复杂比如进行索引统计时count 会省略值为NULL 的行。第二个原因NULL 值是一个没意义的值但是它会占用物理空间所以会带来的存储空间的问题因为 InnoDB 存储记录的时候如果表中存在允许为 NULL 的字段那么行格式 (opens new window)中至少会用 1 字节空间存储 NULL 值列表如下图的紫色部分#防止索引失效用上了索引并不意味着查询的时候会使用到索引所以我们心里要清楚有哪些情况会导致索引失效从而避免写出索引失效的查询语句否则这样的查询效率是很低的。我之前写过索引失效的文章想详细了解的可以去看这篇文章谁还没碰过索引失效呢?(opens new window)这里简单说一下发生索引失效的情况当我们使用左或者左右模糊匹配的时候也就是like %xx或者like %xx%这两种方式都会造成索引失效当我们在查询条件中对索引列做了计算、函数、类型转换操作这些情况下都会造成索引失效联合索引要能正确使用需要遵循最左匹配原则也就是按照最左优先的方式进行索引的匹配否则就会导致索引失效。在 WHERE 子句中如果在 OR 前的条件列是索引列而在 OR 后的条件列不是索引列那么索引会失效。我上面说的是常见的索引失效场景实际过程中可能会出现其他的索引失效场景这时我们就需要查看执行计划通过执行计划显示的数据判断查询语句是否使用了索引。如下图就是一个没有使用索引并且是一个全表扫描的查询语句。对于执行计划参数有possible_keys 字段表示可能用到的索引key 字段表示实际用的索引如果这一项为 NULL说明没有使用索引key_len 表示索引的长度rows 表示扫描的数据行数。type 表示数据扫描类型我们需要重点看这个。type 字段就是描述了找到所需数据时使用的扫描方式是什么常见扫描类型的执行效率从低到高的顺序为All全表扫描index全索引扫描range索引范围扫描ref非唯一索引扫描eq_ref唯一索引扫描const结果只有一条的主键或唯一索引扫描。在这些情况里all 是最坏的情况因为采用了全表扫描的方式。index 和 all 差不多只不过 index 对索引表进行全扫描这样做的好处是不再需要对数据进行排序但是开销依然很大。所以要尽量避免全表扫描和全索引扫描。range 表示采用了索引范围扫描一般在 where 子句中使用 、、in、between 等关键词只检索给定范围的行属于范围查找。从这一级别开始索引的作用会越来越明显因此我们需要尽量让 SQL 查询可以使用到 range 这一级别及以上的 type 访问方式。ref 类型表示采用了非唯一索引或者是唯一索引的非唯一性前缀返回数据返回可能是多条。因为虽然使用了索引但该索引列的值并不唯一有重复。这样即使使用索引快速查找到了第一条数据仍然不能停止要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表因为索引是有序的即便有重复值也是在一个非常小的范围内扫描。eq_ref 类型是使用主键或唯一索引时产生的访问方式通常使用在多表联查中。比如对两张表进行联查关联条件是两张表的 user_id 相等且 user_id 是唯一索引那么使用 EXPLAIN 进行执行计划查看的时候type 就会显示 eq_ref。const 类型表示使用了主键或者唯一索引与常量值进行比较比如 select name from product where id1。需要说明的是 const 类型和 eq_ref 都使用了主键或唯一索引不过这两个类型有所区别const 是与常量进行比较查询效率会更快而 eq_ref 通常用于多表联查中。除了关注 type我们也要关注 extra 显示的结果。这里说几个重要的参考指标Using filesort 当查询语句中包含 group by 操作而且无法利用索引完成排序操作的时候 这时不得不选择相应的排序算法进行甚至可能会通过文件排序效率是很低的所以要避免这种问题的出现。Using temporary使了用临时表保存中间结果MySQL 在对查询结果排序时使用临时表常见于排序 order by 和分组查询 group by。效率低要避免这种问题的出现。Using index所需数据只需在索引即可全部获得不须要再到表中取数据也就是使用了覆盖索引避免了回表操作效率不错。#总结这次主要介绍了索引的原理、分类和使用。我把重点总结在了下面这个表格完