详解MySQL索引

Database
, , ,

索引基础

索引（在MySQL中也叫作“键（key）”）是存储引擎用于快速找到记录的一种数据结构。这是索引的基本功能。

在MySQL中，存储引擎使用索引，先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。

即：MySQL先在索引上按值进行查找，然后返回所有包含该值的数据行。

索引可以包含一个或多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为MySQL只能高效地使用索引的最左前缀列。

索引使用场景

where

上图中，根据id查询记录，因为id字段仅建立了主键索引，因此此SQL执行可选的索引只有主键索引，如果有多个，最终会选一个较优的作为检索的依据。

1
2
3
4
-- 增加一个没有建立索引的字段
alter table innodb1 add sex char(1);
-- 按sex检索时可选的索引为null
EXPLAIN SELECT * from innodb1 where sex='男';

可以尝试在一个字段未建立索引时，根据该字段查询的效率，然后对该字段建立索引（alter table 表名 add index(字段名)），同样的SQL执行的效率，你会发现查询效率会有明显的提升（数据量越大越明显）。

order by

当我们使用order by将查询结果按照某个字段排序时，如果该字段没有建立索引，那么执行计划会将查询出的所有数据使用外部排序（将数据从硬盘分批读取到内存使用内部排序，最后合并排序结果），这个操作是很影响性能的，因为需要将查询涉及到的所有数据从磁盘中读到内存（如果单条数据过大或者数据量过多都会降低效率），更无论读到内存之后的排序了。

但是如果我们对该字段建立索引alter table 表名 add index(字段名)，那么由于索引本身是有序的，因此直接按照索引的顺序和映射关系逐条取出数据即可。而且如果分页的，那么只用取出索引表某个范围内的索引对应的数据，而不用像上述那取出所有数据进行排序再返回某个范围内的数据。（从磁盘取数据是最影响性能的）

join

对join语句匹配关系（on）涉及的字段建立索引能够提高效率

索引覆盖

如果要查询的字段都建立过索引，那么引擎会直接在索引表中查询而不会访问原始数据（否则只要有一个字段没有建立索引就会做全表扫描），这叫索引覆盖。因此我们需要尽可能的在select后只写必要的查询字段，以增加索引覆盖的几率。

这里值得注意的是不要想着为每个字段建立索引，因为优先使用索引的优势就在于其体积小。

索引的创建和删除

  • 第一种方式：在执行CREATE TABLE时创建索引
1
2
3
4
5
6
7
8
9
10
CREATE TABLE user_index2 (
	id INT auto_increment PRIMARY KEY,
	first_name VARCHAR (16),
	last_name VARCHAR (16),
	id_card VARCHAR (18),
	information text,
	KEY name (first_name, last_name),
	FULLTEXT KEY (information),
	UNIQUE KEY (id_card)
);
  • 第二种方式：使用ALTER TABLE命令去增加索引
1
ALTER TABLE table_name ADD INDEX index_name (column_list);

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。

索引名index_name可自己命名，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。

  • 第三种方式：使用CREATE INDEX命令创建
1
CREATE INDEX index_name ON table_name (column_list);CREATE INDEX可对表增加普通索引或UNIQUE索引。（但是，不能创建PRIMARY KEY索引）
  • 删除索引

根据索引名删除普通索引、唯一索引、全文索引：alter table 表名 drop KEY 索引名

1
2
3
alter table user_index drop KEY name;
alter table user_index drop KEY id_card;
alter table user_index drop KEY information;

删除主键索引：alter table 表名 drop primary key（因为主键只有一个）。这里值得注意的是，如果主键自增长，那么不能直接执行此操作（自增长依赖于主键索引）：

需要取消自增长再行删除：

1
2
3
4
alter table user_index
-- 重新定义字段
MODIFY id int,
drop PRIMARY KEY

但通常不会删除主键，因为设计主键一定与业务逻辑无关。

创建索引的注意事项

1.选择合适的字段创建索引：

  • 不为 NULL 的字段 ：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。
  • 被频繁查询的字段 ：我们创建索引的字段应该是查询操作非常频繁的字段。
  • 被作为条件查询的字段 ：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
  • 频繁需要排序的字段 ：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
  • 被经常频繁用于连接的字段 ：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

2.被频繁更新的字段应该慎重建立索引。

虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。 如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。

3.尽可能的考虑建立联合索引而不是单列索引。

因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。

4.注意避免冗余索引 。

冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

5.考虑在字符串类型的字段上使用前缀索引代替普通索引。

前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引。

索引的类型

在MySQL中，索引是在存储引擎层而不是服务器层实现的。所以并没有统一的索引标准：不同存储引擎的索引的工作方式不一样，也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。

下面介绍MySQL支持的索引类型及其优缺点。

主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。

唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。

可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引

可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引

普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。

可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引

可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引

全文索引： 是目前搜索引擎使用的一种关键技术。

可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引

B-Tree索引

B 树也称 B-树,全称为 多路平衡查找树 ，B+ 树是 B 树的一种变体。B 树和 B+树中的 B 是 Balanced （平衡）的意思。

目前大部分数据库系统及文件系统都采用 B-Tree 或其变种 B+Tree 作为索引结构。

B 树& B+树两者有何异同呢？

  • B 树的所有节点既存放键(key) 也存放 数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
  • B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
  • B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

在 MySQL 中，MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是，两者的实现方式不太一样。（下面的内容整理自《Java 工程师修炼之道》）

MyISAM 引擎中，B+Tree 叶节点的 data 域存放的是数据记录的地址。在索引检索的时候，首先按照 B+Tree 搜索算法搜索索引，如果指定的 Key 存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。

InnoDB 引擎中，其数据文件本身就是索引文件。相比 MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。这被称为“聚簇索引（或聚簇索引）”，而其余的索引都作为辅助索引，辅助索引的 data 域存储相应记录主键的值而不是地址，这也是和 MyISAM 不同的地方。在根据主索引搜索时，直接找到 key 所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。 因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

存储引擎以不同的方式使用B-Tree索引，性能也各不相同，各有优劣：

  • MyISAM : 使用前缀压缩技术使索引更新；通过数据的物理位置引用被索引的行
  • InnoDB : 按照原数据格式进行存储；根据主键引用被索引的行

查询流程

  • B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始搜索。

  • 根节点的槽中存放了指向根节点的指针，存储引擎根据这些指针向下层查找。

  • ‘通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际定义了子节点页中值的上限和下限。

  • 最终存储引擎要么是找到对应的值，要么该记录不存在。

  • 页子节点比较特别，它们的指针指向的是被索引的数据，而不是其他的节点页。

B-Tree对索引列是按顺序组织存储的，所以很适合查找范围数据。

注意：索引对多个值进行排序的依据是CREATE TABLE语句中定义索引时列的顺序。

可以使用B-Tree索引的查询类型

  • 全值匹配：和索引列中的所有列进行匹配
  • 匹配最左前缀：只使用索引的第一列
  • 匹配列前缀：只匹配某一列的开头部分
  • 匹配范围值：只使用索引的第一列
  • 精确匹配某一列并范围匹配另外一列
  • 只访问索引的查询：只需要访问索引而无需访问数据行

限制

  • 如果不是按照索引的最左列开始查找，则无法使用索引
  • 不能跳过索引中的列
  • 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找

这些限制都和索引列的顺序有关。也有些限制并不是B-Tree本身导致的，而是MySQL优化器和存储引擎使用索引的方式导致的

哈希索引

哈希索引（hash index）基于哈希表实现，只有精确匹配所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。

哈希索引将所有的哈希码存储在索引中，同时在哈希表保存指向每个数据行的指针。

在MySQL中，只有Memory引擎显式支持哈希索引，且支持非唯一哈希索引：如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。

哈希索引的数据结构如下

槽（slot） 值（value）
2323 指向第一行的指针
2458 指向第三行的指针

注意：每个槽的编号是顺序的，但是数据行不是。

查询流程

若有如下SQL语句

1
select lname from testhash where fname = 'peter'
  • 先计算’peter’的哈希值，并使用该值寻找对应的记录指针
  • 比较寻找到的对应指针所指向的值是否为’peter’，以确保就是要查找的行。

限制

因为索引自身只需存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的速度非常快。

然而，哈希索引也有它的限制：

  • 哈希索引只包含哈希值和指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过访问内存中的行的速度很快，索引大部分情况下这一点对性能的影响并不明显。
  • 哈希索引并不是按照索引值顺序排序的，所以也就无法用于排序。
  • 哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。
  • 哈希索引只支持等值比较匹配，包括=,IN(),<=>；也不支持任何范围查询。
  • 访问哈希索引的数据非常快，除非有很多哈希冲突（不同的索引列值却有相同的哈希值）。当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到符合条件的行。
  • 如果哈希冲突很多的化，一些索引维护操作的代价也会很高。例如当从表中删除一行时，存储引擎需要遍历对应哈希值的链表中的每一行，找到并删除对应列的引用，冲突越多，代价越大。

因为这些限制，哈希索引只适用于某些特定的场合。而一旦适合哈希索引，则它带来的性能提升将非常显著。

InnoDB引擎有一个特殊的功能叫做“自适应哈希索引（adaptive hash index）”。当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引，这样就让B-Tree索引也具有哈希索引的一些优点。

这是一个完全自动的、内部的行为，用户无法控制或者配置，若有必要可以关闭该功能。

创建自定义哈希索引

如果存储引擎不支持哈希索引，则可以模拟像InnoDB一样创建哈希索引。

思路：在B-Tree基础上创建一个伪哈希索引。这和真正的哈希索引不是一回事，因为还是使用B-Tree进行查找，但是它使用哈希值而不是键本身进行索引查找。需要做的就是在查询的WHERE子句中手动指定使用哈希函数。

例如需要存储大量的URL，并需要根据URL进行搜索查找。如果使用B-Tree来存储URL，存储的内容就会很大。正常的情况会有如下查询：

1
select id from url where url = "http://www.yumoyumo.top"

若删除原来URL列上的索引，而新增一个被索引的url_crc列，使用CRC32做哈希，就可以使用下面的方式查询：

1
select id from url where url = "http://www.yumoyumo.top" and url_crc = CRC32("http://www.yumoyumo.top")

这样做的性能会非常高，因为MySQL优化器会使用这个选择性很高而体积很小的基于url_crc列的索引来完成查找。另一种方式就是对完整的URL字符串做索引，那样会非常慢。

注意：不要使用SHA()和MD5()作为哈希函数。因为这两个函数计算出来的哈希值是非常长的字符串，会浪费大量空间，比较时也会更慢。SHA1()和MD5()是强加密函数，设计目标是最大限度消除冲突，但这里并不需要这样高的要求。简单哈希函数的冲突在一个可以接受的范围，同时又能提供更好的性能。

处理哈希冲突

因为所谓的“生日悖论”，出现哈希冲突的概率的增长速度可能比想象的要快得多。要避免冲突问题，必须要在WHERE条件中带入哈希值和对应列值。

注意：若使用如下SQL语句:

1
select id from url where url_crc = CRC32("http://www.yumoyumo.top")

一旦出现哈希冲突，有多个索引列值的哈希值相等，则该语句无法工作。

因此当使用哈希索引进行查询时，必须在WHERE子句中包含常量值：

1
select id from url where url = "http://www.yumoyumo.top" and url_crc = CRC32("http://www.yumoyumo.top")

维护哈希值

这样实现的缺陷是需要维护哈希值。可以手动维护，也可以使用触发器实现。

在创建表后创建触发器。先临时修改一下语句分隔符，这样就可以在触发器定义中使用分号。

1
2
3
4
5
6
7
8
9
10
11
12
13
DELEMITER //

CREATE TRIGGER pseudohash_crc_ins BEFORE INSERT ON pseudohash FOR EACH ROW BEGIN
SET NEW.url_crc=crc32(NEW.url);
END;
//

CREATE TRIGGER pseudohash_crc_upd BEFORE UPDATE ON pseudohash FOR EACH ROW BEGIN
SET NEW.url_crc=crc32(NEW.url);
END;
//

DELIMETER ;

索引的优缺点

索引可以让服务器快速地定位到表的指定位置。但是这并不是索引的唯一作用，到目前位置可以看到，根据索引的数据结构不同，索引也有一些其他的附加作用。

例如最常见的B-Tree索引，按照顺序存储数据，索引MySQL可以用来做ORDER BY和GROUP BY操作。因为数据是有序的，所以B-Tree也就会将相关的列值都存储在一起。最后，因为索引中存储了实际的列值，所以某些查询只使用索引就能完成全部查询。

据此，总结下来索引有如下优点：

  • 索引大大减少了服务器需要扫描的数据量。
  • 索引可以帮助服务器避免排序和临时表。
  • 索引可以将随机I/O变为顺序I/O

索引并不总是最好的工具。总的来说，只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时，索引才是有效的。

  • 对于非常小的表，大部分情况下简单的全表扫描更高效。

  • 对于中到大型的表，索引就非常有效。

  • 对应特大型的表，建立和使用索引的代价将随之增长。在这种情况下，则需要一种技术可以直接区分出查询所需要的一组数据，而不是一条一条地匹配，例如可以使用分区技术。

  • 如果表的数量非常多，可以建立一个元数据信息表，用来查询需要用到的某些特性。

    例如执行那些需要聚合多个应用分布在多个表的数据的查询，则需要记录“哪个用户的信息存储在哪个表中”的元数据，这样在查询时就可以直接忽略那些不包含指定用户信息的表。

    对于大型系统，这是一个常用的技巧。对于TB级别的数据，定位单条记录的意义不大，所以经常会使用块级别元数据技术来代替索引。

总结下来索引有如下缺点：

  • 创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
  • 索引需要使用物理文件存储，也会耗费一定空间。

索引策略

正确地创建和使用索引时实现高性能查询的基础。

独立的列

如果查询中的列不是独立的，则MySQL不会使用索引

“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数

例如下面这个查询无法使用id列的索引

1
select id from useer where id + 1 =5

因此，我们应该养成简化WHERE条件的习惯，始终将索引列单独放在比较符号的一侧。

前缀索引

应用场景

有时候需要索引很长的字符列，例如针对很长的16进制唯一ID，这会让索引变得大且慢。

一个策略是使用前面提到的模拟哈希索引，但有时候这还不够。例如使用的是打包过的解决方案，因而无法修改存储结构。

通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。

但这样也会降低索引的选择性。

  • 索引的选择性：不重复的索引值（基数，cardinality）和数据表的记录总数（#T）的比值，范围从1/#T到1之间。

索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。

唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

前缀长度选择

前缀长度选择的诀窍在于：要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）。

前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。换句话说，前缀的基数应该接近于完整列的基数。

完整列的基数：

1
SELECT COUNT(*) AS cnt,city FROM city_demo GROUP BY city ORDER BY cnt DESC LIMIT 10

列前缀的基数：

1
SELECT COUNT(*) AS cnt,LEFT(city,3) AS pref FROM city_demo GROUP BY city ORDER BY cnt DESC LIMIT 10

将SQL语句中的”LEFT(city,3)“里的“3”依次递增，直至这个前缀的选择性接近完整列的选择性。

另一种方法是计算完整列的选择性，并使前缀的选择性解决于完整列的选择性。

完整列的选择性：

1
SELECT COUNT(DISTINCT city)/COUNT(*) FROM city_demo

前缀选择性：

1
2
3
4
5
6
SELECT COUNT(DISTINCT LEFT(city,3))/COUNT(*) FROM city_demo,
	COUNT(DISTINCT LEFT(city,4))/COUNT(*) FROM city_demo,
	COUNT(DISTINCT LEFT(city,5))/COUNT(*) FROM city_demo,
	COUNT(DISTINCT LEFT(city,6))/COUNT(*) FROM city_demo,
	COUNT(DISTINCT LEFT(city,7))/COUNT(*) FROM city_demo
FROM city_demo

直至增加前缀长度后，选择性提升的幅度足够小。

注意：考虑平均选择性是不够的，需要考虑最坏情况下的选择性。如果数据分布很不均匀，则会有陷阱。

创建前缀索引

1
ALTER TABLE city_demo ADD KEY (city(7))

前缀索引优缺点

优点：

  • 使索引更小、更快

缺点：

  • MySQL无法使用前缀索引做ORDER BY和GROUP BY
  • 无法使用前缀索引做覆盖扫描

选择合适的索引列顺序

正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要。

注意：本节内容仅适用于B-Tree索引，因为其他索引不会按顺序存储数据。

在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。

所以索引可以按照升序或降序进行扫描，以满足符合列顺序的ORDER BY ，GROUP BY 和 DISTINCT 等子句的查询需求。

对于如何选择的列顺序有一个经验法则：将选择性最高的列放在索引最前列。此经验法则考虑的是全局基数和选择性，而不是某个具体查询。

注意：经验法则和推论在多数情况是有用的，但要注意不要假设平均情况下的性能也能代表特殊情况下的性能，特殊情况可能会摧毁整个应用的性能。

聚簇索引

聚簇索引并不是单独的一种索引类型，而是一种数据存储方式。

当表有聚簇索引时，它的数据行实际存放才索引的叶子页中。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引（覆盖索引可以模拟多个聚簇索引的情况）。

因为是存储引擎负责实现索引，因此不是所有的存储引擎都支持聚簇索引。

在InnoDB中，叶子页包含了行的全部数据，但是节点页只包含了索引列。

  • InnoDB根据主键聚簇索引（主键索引）
  • 若没有定义主键，则选择一个唯一的非空索引代替
  • 若没有这样的索引，则隐式定义一个主键来作为聚簇索引

InnoDB只聚集在同一个页面中的记录，包含相邻键值的页面可能会相聚甚远。

聚集主键可能对性能有帮助，但也可能导致严重的性能问题

聚簇索引的优点

  • 可以把相关的数据保存在一起。例如实现电子邮箱时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘I/O
  • 数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快
  • 使用覆盖索引扫描的查询可以直接使用页节点中的主键值

聚簇索引的缺点

  • 聚簇索引最大限度地提高了I/O密集型应用的性能，但如果数据都存放在内存中，则访问顺序就没那么重要了，聚簇索引也就没扫描优势了

  • 插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。

  • 更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置

  • 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行时，可能面临“页分裂(page split)”的问题。

    页分裂：当行的主键值要求必须将这一行插入到某个已满的页中，存储引擎会将该页分裂成两个页面来容纳该行。这会导致表占用更多的磁盘空间。

  • 聚簇索引可能会导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。

  • 二级所有（非聚簇索引）可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列。

  • 二级索引访问需要两次索引查找，而不是一次。因为二级索引中保存的”行指针”的实质：二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。这意味着通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获得对应的主键值，如何根据这个值去聚簇索引中查找对应的行。对于InnoDB，自适应哈希索引能减少这样的重复工作。

非聚簇索引

非聚簇索引即索引结构和数据分开存放的索引。

二级索引属于非聚簇索引。

非聚簇索引的叶子节点并不一定存放数据的指针，因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。

非聚簇索引的优点

更新代价比聚簇索引要小 。非聚簇索引的更新代价就没有聚簇索引那么大了，非聚簇索引的叶子节点是不存放数据的

非聚簇索引的缺点

  1. 跟聚簇索引一样，非聚簇索引也依赖于有序的数据
  2. 可能会二次查询(回表) :这应该是非聚簇索引最大的缺点了。 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

这是 MySQL 的表的文件截图:

img

聚簇索引和非聚簇索引:

img

何时使用聚簇索引与非聚簇索引

img

覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。我们知道在 InnoDB 存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回表”，也就是要通过主键再查找一次。

覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了， 而无需回表查询。

如主键索引，如果一条 SQL 需要查询主键，那么正好根据主键索引就可以查到主键。

再如普通索引，如果一条 SQL 需要查询 name，name 字段正好有索引， 那么直接根据这个索引就可以查到数据，也无需回表。

覆盖索引: img

覆盖索引的优点

  • 索引条目通常远小于数据行大小，所以如果只需要读取索引，那MySQL就会极大地减少数据访问量。这对缓存的负载非常重要，因为这种情况下响应时间大部分花费在数据拷贝上。覆盖索引对于I/O密集型的应用也有帮助，因为所有比数据更小，更容易全部放入内存中。
  • 因为索引是按照列值顺序存储的，所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少得多。
  • 一些存储引擎如MyISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。这可能会导致严重的性能问题，尤其是那些系统调用占了数据访问的最大开销的场景。
  • 由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。

注意：索引覆盖查询还有许多陷阱可能会导致无法实现优化：MySQL查询优化器会在执行查询前判断是否有一个索引能进行覆盖。

  • 一个查询从表中查询所有的列，而没有任何索引覆盖了所有的列
  • MySQL不能在索引中执行LIKE操作

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用

B-Tree 索引做覆盖索引。

最左前缀匹配原则

最左前缀匹配原则指的是，在使用联合索引时，MySQL 会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配，如果查询条件中存在与联合索引中最左侧字段相匹配的字段，则就会使用该字段过滤一批数据，直至联合索引中全部字段匹配完成，或者在执行过程中遇到范围查询，如 >、<、between 和 以%开头的like查询 等条件，才会停止匹配。

所以，我们在使用联合索引时，可以将区分度高的字段放在最左边，这也可以过滤更多数据。

使用索引的一些建议

  • 对于中到大型表索引都是非常有效的，但是特大型表的话维护开销会很大，不适合建索引
  • 避免 where 子句中对字段施加函数，这会造成无法命中索引。
  • 在使用 InnoDB 时使用与业务无关的自增主键作为主键，即使用逻辑主键，而不要使用业务主键。
  • 删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗 MySQL 5.7 可以通过查询 sys 库的 schema_unused_indexes 视图来查询哪些索引从未被使用
  • 在使用 limit offset 查询缓慢时，可以借助索引来提高性能

创建索引的原则

索引虽好，但也不是无限制的使用，最好符合一下几个原则

  • 最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

  • 较频繁作为查询条件的字段才去创建索引

  • 更新频繁字段不适合创建索引

  • 若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)

  • 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

  • 定义有外键的数据列一定要建立索引。

  • 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

  • 对于定义为text、image和bit的数据类型的列不要建立索引。