欢迎您的访问
专注架构,Java,数据结构算法,Python技术分享

MySQL 索引与查询优化

文介绍一些优化 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、 1星: 可以在索引上(用 ref 或 eq_ref 方式)完成等值查询。需要取出等值谓词涉及的列作为索引开头的列以满足最左匹配原则。
2、 2星: 可以使用索引进行排序
3、 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 查询进行优化, 选择最优的驱动表。

赞(0) 打赏
版权归原创作者所有,任何形式转载请联系作者;码农code之路 » MySQL 索引与查询优化

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏