MySQL 高性能索引的知识点总结

站长

2023年03月09日 21:49 · 阅读数 11

本文是阅读《高性能MySQL》过程中所做的笔记，主要是总结关于索引的知识点，希望对大家有所帮助。

🚀 表结构设计

首先，在说到索引之前，先讨论一下表结构的设计，主要是关于数据类型的选择。

因为我们在讨论索引时，往往是为了让查询速度更快，而在此之前，表结构的设计也起了很大作用，相较于不合理的表结构设计，合理的表结构设计也能加快查询速度。

在设计表结构的列时，尽量做到：

使用能够正确存储和表示数据的最小数据类型，原因是：更小的数据类型意味着更快，它占用的磁盘、内存、CPU 空间更小，处理时间更快
使用简单的数据结构，能用整形就不要用字符串类型，因为整形没有复杂的字符集和排序规则，操作起来会更快
- 例如：一张学生表中的性别列，我们可以尽量使用整形(0-女，1-男)来表示，而不是使用 char
尽量不要存储 NULL，MySQL 会对 NULL 这样没有意义的值进行索引。最好指定列为 NOT NULL。可为 NULL 的列使得索引、索引统计、值比较变得更复杂，此外还需要更多的存储空间
- 例如，当某张表中允许存储 NULL 值，同时里面也已经存储了 NULL 值，我们在查询时还要多考虑使用 is null 过滤值，以及当心在表连接时查询出错误的连接记录。
可以使用无符号整数 unsigned int（占用4个字节）表示IP地址，而不是使用 varchar(15)，这样能在节省更多的存储空间，节省了 15-4 = 11 个字节
使用整数类型时，如果确定了列并不会存储负数，那么可以考虑使用 unsigned 修饰，这样可以存储更大的正数
对于字符串类型，每个字符串类型的列都可以指定自己的字符集和排序规则集：

`column` varchar(255) CHARACTER set {字符集} COLLATE {排序规则}

VARCHAR 用于存储可变长度的字符串，只会使用实际需要存储的空间（长度为3的字符串，占用4个字节的空间，其中1个字节存储字符串长度），通常更节省空间。虽然节省了存储空间，但是由于长度可变，在更新时可能会带了额外工作。因此，尽量让 VARCHAR 列少更新。
CHAR 字符则是存储固定长度的，适合长度几乎相同的字符串，例如 MD5 值，身份证号等等。
一张表尽量不要设计太多的列，如果查询中的列数越多，性能就越差。因此在设计的时候，设计尽可能少的列，在查询的时候，尽量只查询需要的列。
查询时，尽量减少需要连接的表

🚀 使用高性能的索引

MySQL 中最常使用的索引就是 B-tree 索引，关于 B-tree 索引，这里不过多赘述。

它适用于全键值、键值范围、键前缀查找，也使用于查询中的排序操作 order by 和分组操作 group by。如果某些值经常被访问，MySQL 会在 B-tree 的基础上在内存中创建哈希索引。

我们为什么需要索引，因为索引有如下的优点：

减少服务器需要扫描的数据量，使用了索引，数据的存储更有规律可循，服务器查找也更加方便
避免排序和创建临时表，临时表的创建和销毁需要一定的开销
将随机 I/O 变成了顺序 I/O

接下来详细说说 B-tree 索引适用的查询类型，假设有索引 key xx_index (col1, col2) ：

全键值查找：和索引中的所有列匹配，例如：where col1=x and col2=y
匹配最左前缀：只使用索引的第一列，例如：where col1 = x
匹配列前缀：匹配索引列第一列的值的开头部分，例如：where col1 like 'abc%'
匹配范围值：匹配索引列第一列，值在某个范围内，例如：where col1 between a and b
精确匹配某一列而范围匹配另一列，例如：where col1 = x and col2 like 'b%'
只访问索引的查询（覆盖索引）：例如查询语句中，只查询了索引列中包含的列，那么 MySQL 只会访问索引，而不会访问数据行。例如：select col1, col2 from table

在使用索引的时候，需要注意一下事项，假设有索引 key xx_index(c1, c2, c3)：

索引必须符合最左匹配原则，如果单独使用 c2 则索引不生效，例如 where c2 = x
通配符在前面时，索引也不生效，例如 where c1 like '%abc' 以及 where c1 like '%abc%'
如果组合索引的某一列使用了范围查询，则后面那一列的索引无法生效，例如 where c1 = 'abc' and c2 like 'a%' and c3 = 'de'，c3 列并不会走索引
- 对于 key xx_index(c1,c2,c3) 索引，相当于创建了 c1; c1,c2; c1,c2,c3三个索引

🛸 前缀索引和索引的选择性

前缀索引指的是，只对字段的前一部分字符进行索引，这样可以提升索引的性能，节省索引空间，但是会降低索引的选择性，同时也无法使用前缀索引进行排序操作和分组操作，也无法使用前缀索引做覆盖索引。

在使用前缀索引的时候，要注意选择合适的前缀来保证较高的选择性以及节省空间。

所谓索引的选择性是指，不重复的索引值数量（基数）和数据表的记录总数的比值，选择性越高查询效率越高，MySQL 查询时就能够过滤掉更多的行。

唯一索引的选择性是 1。

对于那些值很长的列，例如 BLOB、TEXT 和很长的 VARCHAR 列，必须使用前缀索引，因为 MySQL 不支持对这些列的完整内容进行索引。

怎么创建前缀索引

为了创建合适的前缀索引，我们可以对目标列的列值分布情况，对其基数进行统计，然后确定合适的前缀索引长度。

例如：

# 查看完整列的选择性
select count(distinct col)/count(*) from table;	
# 查看不同列前缀长度的选择性
select count(distinct left(col,3))/count(*) as sel3,
	count(distinct left(col,4))/count(*) as sel4,
	count(distinct left(col,5))/count(*) as sel5,
	...
	count(distinct left(col,n))/count(*) as seln
from table;

# 最后，我们选择选择性最高的前缀长度创建前缀索引。
alter table 表名 add key (目标列(前缀长度));

🛸 多列索引

在多列上“独立的”创建多个单列索引，在大部分情况下并不能提高 MySQL 的查询性能。

MySQL 有一种“索引合并”的策略，可以利用表中的多个单列索引定位到指定的行。

这种策略主要有：OR 条件的联合，AND 条件的相交，组合起来的联合和相交。

怎样知道这种策略起了作用呢？我们可以使用 explain 解释 SQL，如果结果中 extra 字段的值为 Using union/Using intersection，那么就说明这个策略起作用了。

union 是联合的意思，intersection 是相交的意思。

索引合并可以优化查询，但大多数时候，这种优化恰恰说明了表中的索引建立地很糟糕：

如果优化器选择了相交策略，通常意味着可以建立一个包含相关列的多列索引。
如果优化器选择了联合策略，我们就可以考虑使用 union 关键字改写查询。
优化器并不会把这些操作（相交、联合）计算到“查询成本”中，因此查询成本可能会被低估，导致最后的查询效率可能还不如全表扫描。

因此，当在优化慢SQL时，如果 explain 后发现有索引合并，那么就应该考虑查询语句的写法和索引建立是否有优化空间。

我们可以修改参数 optimizer_switch 关闭索引合并的功能，也可以使用 IGNORE INDEX 强制忽略某些索引，从而避免索引合并。

# 查看 optimizer_switch 的参数设置：
select @@global.optimizer_switch;
# 结果
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on
#关闭索引合并
set @@global.optimizer_switch='index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on'

# 忽略某些索引
select col1,col2,col3  from 表名 IGNORE index(索引) where col1="1"

🛸 选择合适的索引顺序

在多列索引中，索引列的顺序对查询的性能也有影响，MySQL 会根据索引列的顺序进行排序，先是根据第一列，再第二列，以此类推。

因此，如果需要排序时，可以考虑根据多列索引的定义，让 order by、group by、distinct 的目标列满足列顺序。这样 MySQL 就能利用索引进行排序，更高效地完成查询。

既然 MySQL 能够利用多列索引优化查询的排序、分组，那么在定义多列索引时，如何定义列的顺序呢。书中给到的经验法则是：将选择性高的列放到索引最前列。

🛸 聚簇索引

聚簇索引并不是一种索引类型，而是一种数据存储方式：将键值和数据行紧凑地存储在一起。

InnoDB 的聚簇索引在同一个结构中保存了 B-tree 索引和数据行。因为无法把数据行存放在两个不同的地方，所以一个表中只有一个聚簇索引。

在 InnoDB 中，主键就是聚簇索引。如果没有定义主键，InnoDB 会选择一个唯一的非空索引作为主键。如果依旧没有这样的索引，InnoDB 则会隐式地定义一个主键作为聚簇索引，但是这种隐藏主键都会依赖同一个“自增值”，可能会导致非常高的锁竞争，降低性能。

聚簇索引聚集数据的特点，能够带来以下的优点：

可以把互相关联的数据保存在一起，例如，电子邮箱应用中，可以根据主键聚集数据，从而减少磁盘 I/O 获取某个用户的全部邮件。
数据访问更快。数据和索引保存在同一个 B-tree 中，不用像二级索引一样先查找索引，再根据索引中的主键值重新查找主键索引。

排除掉主键的索引，其他索引基本上都是二级索引，二级索引保存了键值以及这个键值对应记录的主键值，查询完二级索引，还需要根据主键值去查找对应的记录。

使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

例如，定义了索引 (col1, col2)，我们执行查询 select col1, col2 from t1；

那么这个时候覆盖索引扫描就已经生效，而二级索引又包含了主键，这个时候就直接根据主键来索引数据了。

聚簇索引虽然带来了优点，但与此同时也带来了一些缺点：

如果数据全部放在了内存中，那访问的顺序就不重要了，因为聚簇索引的优点之一就是减少磁盘访问次数
插入速度严重依赖插入顺序，按照主键顺序插入是最快的方式。如果主键随机，那么插入的数据就不能被很好的组织起来。可以考虑使用 optimize table 组织表，利用未被使用的空间，这个命令会锁表。
更新聚簇索引列的代价很高，这会强制每个被更新的行移动到新的位置。
插入新行、或者主键更新就需要移动已有的数据行
可能导致全表扫描变慢，尤其是数据存储不连续的时候
二级索引可能比想象中的更大
二级索引需要两次索引查找记录（没有覆盖索引的情况下）

定义主键，可以考虑使用 AUTO_INCREMENT 自增列，保证数据行是按照顺序写入的，对于根据主键进行连接操作的性能也会更好。使用随机值作为主键则不是很好，它会使得数据插入完全随机，可能需要移动已有的其他记录，带来大量的随机 I/O，数据本身没有聚集特性。

因此，在定义主键的时候，尽量考虑使用自增列或者使用雪花算法来保证顺序插入数据。同时，也要考虑高并发下，自增主键可能会造成较高的写入竞争。

🛸 覆盖索引

只有 B-tree 索引可以应用覆盖索引。

所谓覆盖索引，前文已经有所提及，这里再做一个说明：一个查询中的查询列包含了索引定义的列。

例如：有索引 index (col1, col2, col3) ，查询语句 select col2, col3 from t1 就发生了覆盖索引。

覆盖索引可以极大地提高性能，只需要扫描索引而不需要回表查询：

索引条目通常远小于数据行数，极大地减少数据访问量
索引是按照列值顺序存储的，顺序读取要比随机读取的 I/O 次数少得多，性能更好
如果二级索引能够覆盖查询，那么就可以避免对主键索引进行二次查询

如何知道发生了覆盖索引呢，我们可以使用 explain 解释 SQL，如果结果中的 extra 的值为 "Using index"，那么就说明发生了覆盖索引。

二级索引也可以用来巧妙地利用主键完成覆盖索引，因为每一个二级索引都保存了主键值。

例如，表 t1 有主键 id，有二级索引的列为 col1，有查询

select id, col1 from t1;

这个查询也发生了覆盖索引，虽然二级索引的定义并不包含主键列 id，但是二级索引中保存了主键值。

🛸 使用索引扫描进行排序

如果 explain 的输出结果中，type 的值为 index，则说明 MySQL 使用了索引扫描进行排序。

扫描索引本身是很快的，因为只需要查完这个索引记录就去查找下一个索引记录。但是如果没有覆盖索引，则每次扫描索引记录后都需要回表查询一次，这样效率反而可能不如全表扫描。

从上文可以看出，MySQL 可以使用同一个索引既满足排序，又用于查找所需要的记录。因此，要尽可能地设计一个索引能够同时满足这两个任务的索引。

当索引列的顺序和 order by 子句的顺序完全一致，并且所有列的排序方向（正序、倒序）一样，MySQL 才能根据索引对结果进行排序。

例如：索引 index(col1, col2, col3)，查询 select col1,col2,col3 from table order by col1,col2,col3;

如果查询需要连接多张表，则只有 order by 子句中的字段全部来自于第一个表时，才能使索引排序发生。

order by 子句中，要使索引作用生效，也同样需要满足索引的最左前缀原则。但是，如果前导列是常量，那么 order by 子句中的列也可以不满足索引的最左前缀原则。

例如：索引 index(col1,col2,col3)，查询 where col1 = '常量' order by col2,col3;

一些无法使用索引排序的情况（假设存在索引 index(col1,col2,col3)）：

使用了两种不同的排序方向，例如：order by col1 asc, col2 desc;
order by 使用了索引中不存在的列
where 和 order by 中不满足索引最左前缀原则。例如，查询 where col1 = '常量' order by col3;
索引第一列是范围条件，例如：where col1 > '常量' order by col2, col3

🛸 冗余和重复索引

重复索引：相同类型、相同的列、相同的列顺序的索引。MySQL 需要单独维护重复的索引，优化器在优化查询的时候也需要逐个评估（所以不要创建太多的索引），影响性能也浪费磁盘空间

冗余索引：创建了一个组合索引的前缀索引，例如索引(A,B)，又创建了索引(A)，这个索引(A)就是冗余的索引。

表中的索引越多，插入的速度越慢，因为需要维护各个索引。增加索引会使得插入、更新、删除等操作速度变慢。

🚀 总结

接下来，我们对本文内容进行一个简单总结，希望对你有帮助：

根据 B-tree 索引有序存放的特点，如果查询时能够利用好索引，就能够同时满足排序和查询两项任务，所以定义索引时尽量让其能够满足这两项任务
查询时尽可能让覆盖索引（索引列包含要查询的列）生效，性能更好
查询时，要想让索引生效，必须符合最左前缀原则、通配符不可以用在前面
可以为长度很大的列建立前缀索引，例如长度很长的 varchar、text、blob
如果 explain 的 extra 为：Using union/Using intersection，则说明发生了索引合并。如果是联合，考虑重写查询为 union；如果是相交，考虑建立一个索引包含相关列
发生了索引合并说明表中的索引建立地很糟糕
建立多列索引时，可以考虑将选择性高的列放在前面
聚簇索引可以紧凑地存放键值和数据，可以只检索一遍索引就能拿到数据，尽量使用自增列或雪花算法定义主键
如果 explain 的 extra 为：Using index，则说明发生了覆盖索引，查询时可以利用二级索引+主键令覆盖索引生效
索引的数量不要建立太多，维护成本会变高，增删改性能也就越差