MySQL 索引优化

jie12366
539 阅读4分钟

高性能索引原则

最左前缀匹配原则

  1. mysql 会一直向右匹配直到遇到范围查询 (>、<、between、like)就停止匹配，那么我们应该将需要用到范围查询的列放到索引列的最后面，因为如果放在前面，后面的索引将无法使用。
  2. = 和 in 可以乱序，mysql的查询优化器会帮你优化成索引可以识别的形式。根据经验是将业务中使用到的查询里最常用的列放在前面，这样可以尽量使用到该索引。
  3. Like 查询不要使用 '%s%' 这种格式，非最左前缀匹配将无法使用到索引。
/* 前缀匹配：可以使用索引 */
select * from table1 where name like 'anqu%'
/* 非最左前缀匹配：无法使用索引，将全表扫描 */
select * from table1 where name like '%anqu%'
/* 7w 行数据，上面的执行时间 0.1s, 下面的 0.3s。数据量更大的时候差距更明显 */

这里的范围查询是不包括 in 的，in 是指多个等值条件查询，不对联合索引的匹配造成影响。但是通过 explain 分析执行计划时无法区别范围查询和多个等值查询（统一显示为 range）

短索引

索引长度直接影响索引文件的大小,影响增删改的速度,并间接影响查询速度(占用内存多)。所以当要索引的字符很长时，可以只索引前面的字符，从而大大节约索引的大小。 在选择前缀索引的长度时，需要考虑索引的选择性，不能使索引的选择性降低很多。可以通过比较不同长度的选择性来确定最优的前缀长度。

例子：

/* 比较不同前缀长度的选择性 */
select  count(distinct(LEFT(rit_id, 7))) / COUNT(*) as sel1, 
        count(distinct(LEFT(rit_id, 8))) / COUNT(*) as sel2, 
        count(distinct(LEFT(rit_id, 9))) / COUNT(*) as sel3, 
        count(distinct(LEFT(rit_id, 10))) / COUNT(*) as sel3, 
        count(distinct(LEFT(rit_id, 11))) / COUNT(*) as sel4, 
        count(distinct(rit_id)) / COUNT(*) as count 
from table2

根据计算结果可从长度为 10 开始，增幅较小，已经没有什么提升空间了，故选择前缀索引长度为10. 下面来创建一个前缀索引 idx_rit：

create index idx_rit on table2 (rit_id(10))

区分度高的列

  1. 区分度的公式是 count(distinct col) / count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0。
  2. 当区分度很小的列使用的非常频繁，我们可以在联合索引中加入它。

建立索引

  1. 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
  2. 考虑建立联合索引，而不是全为独立索引。两个独立的索引比一个联合索引使用更少的空间开销，写入和查询的效率更高。当同时需要对多个字段进行查询（and 或 or 或 order by 等），就可以建立联合索引，建立 / 使用联合索引一定要时时想起最左前缀匹配原则。
  3. 参与 join 连接的字段一定要建索引。
  4. 更新非常频繁的字段不适合建立索引 (更新操作会变更B+树，重建索引) 。

其它原则

  1. 索引列不要参与计算，否则将无法使用索引。计算操作应该放到业务层处理。
  2. 负向条件查询 ( !=、<>、not in、not exists、not like 、is not null) 一般不建议使用。只有当负向条件结果集较小的时候可使用。当负向条件查询的结果集很大的时候，优化器会趋向于使用全表扫描，因为扫描非聚簇索引再回表查询的话会浪费一定的时间.
SELECT * FROM table1 WHERE rule_id is not null
  1. 如果 or 查询时，有条件没有索引或使用不了索引，那么所有的索引都不能被使用。 例子：
/* 使用全表扫描 */
Select * from table2 where name like '%?%' or id = ?

查询优化

Offset 分页查询

当 offset(偏移量) 很大时，需要浪费大量的时间去扫描到要开始查询的那个索引位置。

// MySQL 会丢弃掉查询结果中前面的 3000 条数据（这里涉及到大量的回表操作）
select * from table3 where deal_date = '2019-04-01' limit 3000, 10
// 耗时大概 1s

优化策略一般是，通过覆盖索引查询返回需要的主键，再根据这些主键从原表中获取需要的数据。覆盖索引查询节省了大量的回表时间。

select * from table3 as a inner join 
(select id from table3 where deal_date = '2019-04-01' limit 3000, 10) as b on a.id = b.id
// 耗时大概 0.08s

可以看到优化后，效率提高了 10 倍多。（但当偏移量不大时，没有优化的必要）

排序优化

尽量按索引顺序扫描来做排序，而不是通过文件排序操作。order by 子句中使用索引或者和 where 子句一起组合为一个联合索引，这些都将会使用索引扫描进行排序。

avatar
文章
阅读
粉丝