MySQL 索引与查询优化

2022-12-06 20:08:44

本文介绍一些优化 MySQL 索引设计和查询的建议。在进行优化工作前，请务必了解MySQL EXPLAIN命令: 查看执行计划

索引

索引在逻辑上是指从索引列(关键字)到数据的映射，通过索引可以快速的由关键字查找到数据记录。顺序查找复杂度为O(n), 树状索引查找复杂度为O(logn), 哈希索引为O(1)。

MySQL中的索引一般是指BTree索引， InnoDB存储引擎使用B+树来实现BTree索引。

BTree索引保持数据之间的顺序，可以极大的加快精确搜索(=, in)、范围搜索(<,>), 去重(DISTINCT), 排序(ORDER BY) 和聚合(GROUP BY)。

总结来说使用索引有三个优点:

极大减少了要扫描的数据量
减少排序和临时表
将随机IO变为顺序IO

因为写入数据时需要为新行建立索引，所以索引会减慢写入速度。请尽量避免创建无用的索引。

索引只能用于独立的列

示例:

SELECT * FROM `user` WHERE `id`=5; -- 可以使用索引

SELECT * FROM `user` WHERE `id` + 1 = 5; -- 索引列作为表达式一部分时无法使用索引

SELECT * FROM `user` WHERE MD5(first_name)='MD5'; -- 索引列作为函数参数时无法使用索引

最左匹配原则

BTree索引具有最左匹配性质, 即只能按照索引列的顺序自左向右搜索，不能跳过索引列。

联合索引中存在范围查询(<, >, like, between) 会导致后面的索引列失效。

定义表和索引:

CREATE TABLE `user` (

    `id` INT,

    `first_name` VARCHAR(16),

    `middle_name` VARCHAR(16),

    `last_name` VARCHAR(16),

    PRIMARY KEY (`id`),

    KEY `idx_name` (`first_name`, `middle_name`, `last_name`)

);

示例：

SELECT * FROM `user` WHERE `first_name`='a'; -- 可以使用 idx_name 索引

SELECT * FROM `user` WHERE `first_name`='a' AND `middle_name`='b'; -- 可以使用 idx_name 索引

SELECT * FROM `user` WHERE `first_name`='a' AND `middle_name`='b' AND `last_name`='c'; -- 可以使用 idx_name 索引

SELECT * FROM `user` WHERE `middle_name`='b'; -- 不能使用 idx_name 索引

SELECT * FROM `user` WHERE `middle_name`='b' AND `last_name`='c'; -- 不能使用 idx_name 索引

SELECT * FROM `user` WHERE `first_name`='a' AND `last_name`='c'; -- 不能使用 idx_name 索引

上文中说的"可以使用索引"是指可以用ref,eq_ref 或 range方式进行查询。

使用 EXPLAIN 命令查看3个不能使用索引示例的执行计划，可以发现 type 字段为 index, 这是在索引树上进行顺序查找。虽然性能优于全表扫描, 但比 ref 和 range 查询来说要慢很多。

索引列为字符串等类型时, 可以使用索引列的前缀字符串进行模糊查询

select * from user where first_name = 'abc' AND middle_name like 'de%';

这条语句的类型的为 range, 即在索引列上进行范围查询。

将联合索引理解为: 将索引列(关键字)按顺序拼接, 把拼接后的关键字与数据建立映射。最左匹配即是使用关键字前缀缩小搜索范围。

联合索引

在进行多列搜索时有一条经验法则: 首先使用选择性高的列进行搜索。

我们可以将选择性定义为 count(distinct ) / count(*), 也就是说满足条件的数据越少，则条件的选择性越高。

假设用户名name比性别gender选择性高, 那么查询应该写作WHERE name='finley' AND gender='M'而不是WHERE gender='M' AND name='finley'。

实际上两条语句是等效的, 当存在多个查询条件时 MySQL 优化器会根据索引和选择性决定最优的过滤顺序。

为每一个列单独建立索引，并不能有效支持多列查询

CREATE TABLE `user` (

    `id` INT,

    `first_name` VARCHAR(16),

    `middle_name` VARCHAR(16),

    `last_name` VARCHAR(16),

    PRIMARY KEY (`id`),

    KEY `idx_first_name` (`first_name`),

    KEY `idx_middle_name` (`middle_name`)

);

查询语句:

select * from user where first_name = 'a' AND middle_name = 'bc';

查看查询计划:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	user	ALL	idx_first_name,idx_middle_name	NULL	NULL	NULL	4	100.00	Using where

根据最左匹配法则和优先使用高选择性列的经验法则，可以得出一条建议:

对于需要进行多列查询的表，应建立包含所有参与查询列的联合索引, 索引的顺序应按照列的选择性从强到弱排列

一些关于索引的建议

通常在使用索引检索到数据之后，需要访问磁盘上数据表文件读取所需要的列，这种操作称为"回表"。

若索引中包含查询的所有列，则不需要回表操作直接从索引文件中读取数据即可, 这种索引称为覆盖索引。

在查询时尽量减少"SELECT *"只查询需要的行, 条件允许时尽量建立覆盖索引。

《数据库索引设计与优化》一书中提出了判断最佳索引的"三星索引"概念:

1星: 可以在索引上(用 ref 或 eq_ref 方式)完成等值查询。需要取出等值谓词涉及的列作为索引开头的列以满足最左匹配原则。
2星: 可以使用索引进行排序
3星: 索引中包含要查询的所有列，不需要回表

MySQL 在索引包含 null 的列时需要额外的开销, 尽量避免允许索引列上存在 null。

除非有非常严格的一致性要求，否则应避免使用外键。

关于主键:

避免使用字符串类型作为主键
使用MD5、UUID等随机的主键可能导致更多的磁盘随机读写，但一般不会有太大的性能问题
auto_increment 使用锁机制实现，可能影响写入性能。

在查询较多且业务允许的情况下, 推荐使用自增主键。

不知道放哪儿好的两条建议:

BLOB 用于存储较大的二进制串，TEXT 用于存储较大的字符串；它们不能被索引；
ip地址是32位无符号整数，使用 INT UNSIGNED 存储ip地址而不是字符串。INET_ATON(), INET_NTOA()可以转换数字和字符串两种格式

查询

一些关于查询的建议

尽量避免使用 != 或 not in
条件允许时避免使用 join 查询, 可以先分别查询然后在应用程序内存中关联
避免在where语句中进行 is null 判断, 这可能导致MySQL放弃使用索引而进行全表扫描
条件允许时使用 union all 而非 union, 避免 union 不必要的去重操作
必要时使用 union (all) 代替 or 条件

小表驱动大表

MySQL在执行多表查询时可以采用Nest Loop Join算法，即选择数据集较小的一张表(数据集)作为驱动表, 遍历驱动表中所有记录并连接另一张表中符合条件的记录。

在使用 JOIN 进行查询时 MySQL 会自动选择数据集较小的一张表作为驱动表。

LEFT JOIN 强制左表作为驱动表, RIGHT JOIN 则强制选择右表作为驱动表。

MySQL 的 STRAIGHT_JOIN 结果与 INNER JOIN 相同, 但强制使用左表作为驱动表, 可用来分析选择不同驱动表的效果。

在业务允许的情况下, 让 MySQL 自行决定驱动表。

在使用 IN 进行多表查询时一般会把 IN 内部的嵌套循环作为驱动表, 应尽量减少IN数据集的大小。实际上, MySQL 也会对 IN 和 EXISTS 查询进行优化, 选择最优的驱动表。

码农公寓

索引