索引 | newrain-zh博客

type

status

date

slug

summary

什么是索引

一种提高查询效率的数据结构

索引的优缺点

优点：相对于B树 IO次数更少范围查询性能更优

缺点：在于维护成本，过多的索引在DELETE、UPDATE操作时会带来性能的消耗。

索引结构

索引的分类

唯一索引、普通索引、全文索引、组合索引

主键索引

InnoDB表的主键生成策略: 优先使用用户自定义的主键，如果用户没有自定义主键，则选择一个不允许存储为NULL值的UNIQUE键做为主键；如果连不允许存储为NULL值的UNIQUE键都没有定义，InnoDB会为表默认添加有一个名为row_id的隐藏列作为主键。

主键索引也就是聚簇索引。

唯一索引

普通索引

非主键非唯一

建立的普通索引的列一般做为重复率不高且在where条件频繁的。

如何判断重复也是有办法有做到的。

show index from [table_name] 中查看表的索引。看Cardinality（基数）一般这个基数大于80%就是适合作为普通索引。这个基数统计算法是用HyperLogLog

聚簇索引和非聚簇索引

聚簇索引

索引和数据是放在一起的,找到索引也就是找到了数据（索引即数据，数据即索引），表数据按照索引的顺序来存储的，也就是说索引项的顺序与表中记录的物理顺序一致。

使用记录仪主键值的大小进行记录和页的排序，包括三个方面含义：

页内记录是按照主键的大小顺序排成一个单向链表。
各个存放用户记录的页页是根据页中用户记录的主键大小顺序排成一个双向链表。
存放目录想记录的页分为不同的层次，在同一层次中的页页是根据页中目录项记录的主键大小顺序排序成一个双向链表。

B+树的叶子节点存储的是完整的用户记录。所谓完整的记录中存储了所有列的值（包括隐藏列）。

我们把具有这两种特性的 B+树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点处。这种聚簇索引并不需要我们在 MySQL中显示的用 INDEX语句去创建，InnoDB储存引擎会自动的为我们创建聚簇索引。

非聚簇索引

聚簇索引只能在搜索条件为主键时才能发挥作用。如果想要用其他列作为搜索条件，如何提高查找效率？

解决的办法是再建立几颗 B+树，不同的 B+树采用不同的排序规则。例如使用c2 列作为 B+树，

和聚簇索引对比有以下不同点

使用记录c2列的大小进行记录和页的排序，这包括了三个方面的含义：

页内的记录都是按照 c2列的大小排序排成一个单向链表。

各个存放用户记录的页页是根据 c2 列大小顺序排序成一个双向链表。

存放目录项记录的页分为不同的层次，在同一个层次中的页页是根据页中目录项记录的c2列大小排序成一个双向链表。

B+树的叶子节点存储的并不是完整的用户记录，而只是 c2列+主键 两个列的值

目录项记录中不再是主键+页号的搭配而是变成了 c2列+页号+主键的搭配。

为什么非聚簇索引是目录项是索引列+页号+主键？

非聚簇索引不是唯一的，如果有多个重复的列值，那就不知道写入的数据应该写在哪个目录页里，所以在目录页上记录了索引列对应的主键，这样写入的相同的索引列值，再根据主键来判断就知道应该写入的具体位置。

将数据存储与索引分开，叶节点包含索引字段值及指向数据页数据行的逻辑指针，其行数量与数据表中保持一致。

非聚簇索引不保存所有的记录，只会存储主键，所以如果查询数据的完整记录，在叶子节点查询到主键值然后再去聚簇索引中再查找一遍完整的用户记录。这个过程也被称为回表。

这种按非主键列建立的 B+树需要一次回表操作才可以得到的完整的用户记录，所以这种 B+树也被称为二级索引（secondary index）或者辅助索引。

联合索引

同时可以用多个列的大小作为排序规则，也就是同时为多个列建立索引。例如我们想让 B+树按照 c2和c3 列的大小进行排序。这里的意思是：

先把各个记录和页按照 c2列进行排序

在记录 c2列相同的情况下，采用 c3 列进行排序。

这样构成的一颗 B+树：

每条目录项是由 c2、c3、页号这三个部分组成，各条记录先按照 c2 列的值进行排序，如果记录的 c2列相同，则按c3 列的值进行排序。

B+树的叶子节点处用户记录由 c2、c3和主键组成

如何使用索引

索引的代价

空间上的代价

每建立一个索引都要为它建立一个 B+树，每一颗 B+树的每一个节点都是一个数据页，一个页默认占用 16kb的存储空间

时间上的代价

每次对表中的数据进行增、删、改操作，都需要修改 B+树。B+树每层的节点都是按照索引列的值从小到大的顺序排序组成的双向链表。无论是目录项记录还是叶子节点记录都是按照索引列的值从小到大的顺序的形成一个单向链表。而增、删、改都会节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录唯一、页面分裂、回收的操作来维护号节点和记录的排序。如果我们建立了多个索引，每个索引对应的 B+树都要进行相关的维护操作。

总之一个表上的索引越多，就会占用更多的存储空间，在增、删、改记录的时候，性能越差。

索引匹配原则

全值匹配

如果我们的搜索条件中的列和索引列一致的话，这种情况就称之为全值匹配。

最左匹配原则

在联合索引中，查询语句如果想要使用索引必须使用最左边列。假如有个c1、c2、c3的联合索引，那索引的排序规则是先根据c1排序、c1相同在根据c2排序，c1、c2都相同再根据，c3排序。

如果查询语句不使用c1列,那么是无法使用索引的。因为c1列值不同的情况下，c2 是无序的。

如果查询语句只使用了 c1、c3 列，那么只会用到 c1 列的索引。情况同上。

匹配列前缀

如果一个字符串型的索引列，这些字符串的前 N 个字符也就是前缀都是排好序的。

当执行 WHERE username LIKE 'a%' 时，B+Tree 可以快速定位到以 a 开头的索引条目，并顺序扫描后续符合前缀的行，无需全表扫描。

B+Tree 的排序特性决定了 只有从最左端开始的前缀才能高效匹配。

类似 LIKE '%a' 或 LIKE '%a%' 的查询无法利用索引，因为索引无法逆向或中间匹配。

场景	示例查询	是否利用索引
左前缀匹配	`LIKE 'abc%'`	✅ 是
固定前缀查询	`WHERE username = 'alice'`	✅ 是
范围查询	`WHERE username BETWEEN 'a' AND 'c'`	✅ 是
中间或后缀通配符	`LIKE '%bc%'` 或 `LIKE '%bc'`	❌ 否

范围匹配

所有记录都是按照索引列的值从小到大的顺序排好序的，所以很方便我们查找索引列的某个范围内的值。

联合索引进行范围查找的时候，如果对多个列同时进行范围查找的话，只有对索引最左边的那个列进行范围查找才会用到 B+树索引。

上述语句可以分为两个部分：

通过条件name > 'Asa' AND name < 'Barlow' 来对name进行范围，查找的结果可能有多条name值不同的记录，

对这些name值不同的记录继续通过birthday > '1980-01-01'条件继续过滤。

这样子对于联合索引idx_name_birthday_phone_number来说，只能用到name列的部分，而用不到birthday列的部分，因为只有name值相同的情况下才能用birthday列的值进行排序，而这个查询中通过name进行范围查找的记录中可能并不是按照birthday列进行排序的，所以在搜索条件中继续以birthday列进行查找时是用不到这个B+树索引的。

精确匹配某一列并范围匹配另外一列

对于同一个联合索引来说，虽然对多个列进行范围查找值能用到最左边那个索引列，但是如果左边的列是精确查找，则右边的列可以范围查找。例如

这个查询条件可以分为 3 个部分：

name=’Ashburn’,对 name列进行精确查找，当然可以使用 B+树索引了。

birthday > '1980-01-01' AND birthday < '2000-12-31'，由于name列是精确查找，所以通过name = 'Ashburn'条件查找后得到的结果的name值都是相同的，它们会再按照birthday的值进行排序。所以此时对birthday列进行范围查找是可以用到B+树索引的。

phone_number > '15100000000'，通过birthday的范围查找的记录的birthday的值可能不同，所以这个条件无法再利用B+树索引了，只能遍历上一步查询得到的记录。

同理，下面的查询也是可能用到这个idx_name_birthday_phone_number联合索引的：

排序

如果 ORDER BY字句用到了索引列，就有可能省去在内存或文件中排序的步骤。例如:

该 SQL 语句要求先按 name 排序，name 相同则按 birthday来排序，如果 birthday 相同则按 phone_number 来排序。如果name, birthday, phone_number 三列已经建立联合索引，那么 B+树上本身就是按照这个顺序排好顺序的直接从索引提取索引即可。

ORDER BY的字句后边的列页必须遵循按照索引列的顺序给出，如果给出的是 ORDER BY birthday、phone_number 那也是用不了 B+树索引的（最左匹配原则）。

同理： ORDER BY name、ORDER BY name，birthday 这种匹配索引左边的列的形式可以使用部分 B+树索引。当联合索引左边的列值为常量，也可以使用后边的列进行排序，比如这样：

不可以使用索引进行排序的几种情况

ASC、DESC 混用

对使用联合索引进行排序的场景，要求各个排序列的排序顺序是一致的，也就是各个列要么是 ASC，要么是 DESC。

例如：

ORDER BY name, birthday LIMIT 10

这种情况直接从索引的最左边开始往右读10行记录就可以了。

ORDER BY name DESC, birthday DESC LIMIT 10

这种情况直接从索引的最右边开始往左读10行记录就可以了。

假如有以下语句：

先从索引最左边确定name 列的最小值，然后找到 name列等于该值的所有记录，然后从 name 列等于该值的最右边的那条值开始往左找 10 条记录

如果name列等于最小值的记录不足 10 条，再继续往右找值第二小的记录，重复上面过程，直到找到 10 条记录为止。

这样的查找过程不能高效的使用索引，而要采取更复杂的算法从索引中取数据。所以MySQL的设计者直接加入了这条规则。

WHERE子句中出现非排序使用到的索引列

如果 WHERE 字句出现了非排序用到的索引列，那么排序依然是无法使用索引的。

这个查询是将符合搜索条件的country=’China’提取出来然后再进行排序，是使用不到索引的。

排序列包含非同一个索引的列

有时候用来排序的多个列不是一个索引里的，这种情况也不能使用索引进行排序。

name、country 不属于一个联合索引中的列。也就是说在两颗 B+树中。

排序使用了复杂表达式

要想使用索引进行排序操作，必须保证索引以单独列的形式出现，而不是修饰过的形式。

分组（Group By）

分组使用索引的情况同排序一致。

MySQL索引优化策略

索引条件下推（Index Condition Pushdown）

简称：ICP,在5.6版本以后会有该优化策略。主要是用于辅助索引的优化。

什么是ICP

查询数据时，二级索引的查询条件在引擎层判断下，减少回表的动作称之为ICP。也就是把查询中与被使用索引有关的查询条件下推到存储引擎中判断，而不是返回到 server 层再判断。

索引条件下推 只是为了减少回表次数，也就是减少读取完整的聚簇索引记录的次数，从而减少 IO 操作。

如何知道mysql使用了ICP

使用Explain命令查看语句执行计划，在Extra列输出Using index condition表示mysql执行了ICP

开启/关闭方式

开启和关闭方式:

示例

假设我们有一个简单的商品表 products，其中包含商品的ID（id），名称（name），以及价格（price）。在该表中建立了一个包含`name`和`category`的复合索引。

不使用索引下推的查询

如果我们想要查询名称包含"Table"且类别为"Furniture"的商品，但只对name字段使用索引：

在没有索引下推的情况下，MySQL会首先使用name索引找到所有名称包含"Table"的行。然后，MySQL会根据找到的行ID去表中检查这些行，过滤出类别为"Furniture"的行。

使用索引下推的查询

当启用索引下推时，MySQL可以在扫描name索引的同时，检查索引条目中的category字段是否等于"Furniture"。这样，只有同时满足name和category条件的行ID会被取出用于后续的表访问，减少了访问表数据的次数。

索引覆盖

根据name的值到 name索引的B+树上获取对应的叶子节点的数据，该叶子节点包含了全部要查询的字段。此时就叫做索引覆盖。

上述语句执行过程为 name索引的B+树上获取对应的叶子节点数据即可。

回表

当辅助索引不能覆盖查询的列时，需要通过找到的主键再去聚簇索引查询数据，这个过程称之为回表。

上述语句的执行过程为先去name的索引的B+树上去查询到叶子节点的id值。然后再根据id值去查询到B+树整条记录，这种查询方式为回表。

在执行计划中是无法通过执行计划来判断是否有回表的。如果出现了useing index，那100%是没有出现回表

索引失效情况

一般来说对于是否使用索引的根本在于优化器去扫描索引的记录占用全部记录的比例，如果占比较低说明需要扫描记录比较少，成本比较低，那就可以使用索引去查询，否则则使用全表扫描的策略。

常见的索引失效原因

不满足最左前缀原则

索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

范围索引没有放到最后

使用了select *

索引列上有计算

索引列上使用了函数

字符类型没有加引号

如果索引列出现了隐式类型转换，则 MySQL 不会使用索引。常见的情况是在 SQL 的 WHERE 条件中字段类型为字符串，其值为数值，如果没有加引号那么 MySQL 不会使用索引。

用is null 和 is not null 没注意字段是否允许为空(这条是存在问题的)

like查询左边有%

% 在前，就代表，我前面的内容不确定，索引树从左到右都是有顺序的，无法确认前面的内容，就无法比较，所以会全表扫描。

MySQL 不能在索引中执行 LIKE 操作，这是底层存储引擎 API 的限制，最左匹配的 LIKE 比较会被转换为简单的比较操作，但如果是以通配符开头的 LIKE 查询，存储引擎就无法做比较。这种情况下 MySQL 只能提取数据行的值而不是索引值来做比较。

使用or关键字没有注意

如果 WHERE 条件中含有 OR，除非 OR 前使用了索引列而 OR 之后是非索引列，索引会失效。

字符类型没有加引号

如果查询中的列不是独立的，则 MySQL 不会使用索引。独立的列是指索引列不能是表达式的一部分，也不能是函数的参数。

对于多个范围条件查询，MySQL 无法使用第一个范围列后面的其他索引列，对于多个等值查询则没有这种限制。

如果 MySQL 判断全表扫描比使用索引查询更快，则不会使用索引。

执行计划

Explain

使用Explain select * xxxx 可以查询出mysql的执行计划

什么叫执行计划

对于一个查询来说，有时候可以通过不同的索引或者全表扫描来执行它，MySQL优化器会通过事先生成的统计数据，或者少量访问B+树索引的方式来分析使用各个索引时都需要扫描多少条记录，然后计算使用不同索引的查询成本，最后选择成本最低的那个来执行查询。

统计数据

InnoDB默认是以表的单位来收集和存储统计数据的，意味着我们可以把某些表的统计数据存储在磁盘上，也可以把另一些表的统计存储在内存中。

如何指定表的统计存储的位置？

我们可以在创建和修改表的时候通过制定STATS_PERSISTENT属性来指明该表的统计数据存储方式:

STATS_PERSISTENT=1 该表的统计数据永久存储在磁盘上

STATS_PERSISTENT=2 该表的统计临时存储在内存中

表在未指定STATS_PERSISTENT属性那默认采用系统变量innodb_stats_persistent值作为该属性的值

统计方式

对于IN的语句（是否是针对所有查询还是只是IN查询）

index dive

如果IN中的参数较少，那么将率先访问存储引擎，看一下每个区间的范围的数据有多少，这种在查询真正执行前优化器就率先访问索引计算需要需要扫描的索引记录的方式称之为索引

index statistic

如果IN对应的参数很多，那将不采用index dive方式，因为耗费的时间太多，而是采用表的统计数据去估算匹配的二级索引有多少条。

index statistic根据统计数据去估算扫描的数量要比index dive精确性差了很多。

如何控制统计方式

系统变量: eq_range_index_dive_limit

如何查看: SHOW VARIABLES LIKE 'eq_range_index_dive_limit';

5.7.3以后该数值为200,意味着当范围区间小于等于200时使用index dive

大于200时则采用index statistic方式统计。

注意：在MySQL 5.7.3以及之前的版本中，eq_range_index_dive_limit的默认值为10。所以如果大家采用的是5.7.3以及之前的版本的话，很容易采用索引统计数据而不是 index dive的方式来计算查询成本。当你的查询中使用到了IN查询，但是却实际没有用到索引，就应该考虑一下是不是由于 eq_range_index_dive_limit 值太小导致的。

在成本分析的代码中，range和index、all是被分到一类里的，ref是亲儿子，单独分析了一波。不过我们也可以看到，设计MySQL的大叔在计算range访问方法的代价时，直接认为每次回表都需要进行一次页面I/O

使用查询优化的准则:

MySQL查询优化器决策是否使用某个索引执行查询时的依据是使用该索引的成本是否足够低，而成本很大程度上取决于需要扫描的二级索引记录数量占表中所有记录数量的比例。

对null值的处理

`innodb_stats_method` 参数

在单点扫描区间特别多，或者连接查询涉及两表的等值匹配条件时，我们都无法确定要扫描的记录数量是多少，因此只能通过统计数据的平均值来计算一个单点扫描区间的记录数量。

不过统计不重复值的数量时，NULL非常的麻烦，在MySQL中，任何与NULL值进行比较表达式得到的值都是NULL，

有些人认为每个NULL都是唯一独立的，有些人认为NULL没有意义，可以看作是重复的，为了适配这两种情况，MySQL提供了 innodb_stats_method 系统变量，有三个候选值：

nulls_equal：所有NULL值都是相等的，该值也是默认值，如果NULL值特别多，查询优化器将认为该列重复值个数特多，会倾向于不使用该索引。

nulls_unequal：所有NULL值都不相等，如果NULL值特别多，会让优化器认为该列重复值个数特少，倾向使用该索引。

nulls_ignored：直接忽略NULL值。

PS: 在5.7.22中，基于内存的统计 nulls_unequal 与 nulls_ignored 效果一样，基于磁盘统计，无论设置为什么，都会以 nulls_equal 去处理。