本文是关于 MySQL 中索引、查询优化等的 Tips。

1. 索引篇

MySQL 的索引是一颗以 B+树为主的数据结构,叶子节点增加了下个节点的指针。

  • MyISAM 索引,叶子节点存放的是数据记录的地址,非聚集索引。
  • InnoDB 索引,叶子节点本身就是数据文件,是聚集索引。
    • 数据文件本身就是主键索引,所以必须要有主键。尽量使用一个自增字段作为主键。
    • 辅助索引叶子存储的是主键,辅助索引搜索需要检索两遍索引,先找主键,再找记录。
  • 更多:http://blog.codinglabs.org/articles/theory-of-mysql-index.html

MySQL 使用索引的情况

  • where 子句
  • MIN, MAX 函数
  • ORDER BY, GROUP BY 子句

1.0 建立索引的原则

  • 最左匹配(联合索引),mysql 会从左至右匹配,直到遇到范围(>,<,between,like)停止,例如 a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d 是用不到索引的

  • =和 in 的写作顺序可以乱序,mysql 会执行优化

  • 在区分度高的列建立索引,Selectivity = count(distinct col)/count(*) ,Selectivity 越接近 1 越好。例如性别列不适合建立索引。

  • 注意字符索引的长度,结合区分度,选择合适的字段长度建立索引

  • 索引列不要参与计算,不能使用函数和表达式

  • 在现有基础上扩展索引,避免新增索引

  • InnoDB 上永远使用自增长的字段作为主键,物理存储按照顺序存放,减少了磁盘开销

1.1 负向条件查询不能使用索引

select * from `table` where id not in (1,2) and status != 0

idstatus将无法使用索引。同样not exists等都不可以。

尽可能将这些查询改为正向条件,如id in (3, 4)

1.2 联合索引与最左前缀

有联合索引< a, b, c >,进行精确匹配时(in 或=),索引按照最左前缀原则。组合索引区分度最高的在左边。

# 能用到全部索引的常规写法
select * from `table` where a = '' and b= '' and c in (1,2);

# 也能用到全部索引的常规写法,mysql对查询条件自动优化,与in和=后的写作顺序无关
select * from `table` where b= '' and a = '' and c in (1,2);

# 只能用到a
select * from `table` where a = '' and c in (1,2);

# 无法使用索引
select * from `table` where c in (1,2);

# 范围列只会用到一个索引,范围后的列无法使用索引,只能用到a
select * from `table` where a > 100 and b < 1000;

1.3 查询条件中的函数或表达式不能使用索引

select * from `table` where YEAR(date) < = '2017'

如果索引有此列, 也可能是索引全扫描。

SELECT * fromtablewhere (b+1)>2 and left(c,2) ='a111'

1.4 前导模糊不能使用索引

select * from `table` where desc like '%XX'

请使用搜索引擎代替。

1.5 使用 limit

如果明确知道只有一条结果返回,limit 1 能够提高效率

select * from `table` where login_name=? limit 1

你知道只有一条结果,但数据库并不知道,明确告诉它,让它主动停止游标移动

1.6 少量的数据不应该索引

小于 k 级别的数据,索引提供的效率提升并不明显,反而增加了维护索引的代价。

2. 优化查询

2.1 慢查询

  1. 打开慢查询日志
  2. 确定 where 后哪个字段查询记录最少,区分度最高
  3. explain 查看执行计划
  4. order by limit 让排序的表优先查
  5. 根据业务场景,优化索引

2.2 避免使用自动类型转换

select * from `table` where col = 4;
select * from `table` where col = '4';

以上两条都能执行成功,但是请确定真正的类型与列一致。

类型转换可能导致索引失效。

2.3 尽量把数据列设为 NOT NULL

  • 处理更快
  • 查询简单,不需要监测是否 NULL

2.4 使用尽量“小”的数据结构

  • 数据文件变小,减少磁盘开销
  • 索引变小

2.5 永远只返回需要的列

不要 select \* ,可以 count(\*),why not:

  • 不能使用聚集索引,要查两次
  • 增加了 IO,网络开销

2.6 把一个大的查询拆分成小查询

虽然 MySQL 能查询百万行数据,但是大量数据发送给客户端要慢得多。

把关联查询分解,在内存中过滤也能提高效率:

  • 缓存的效率更高:缓存多事对单表进行的。
  • 单个查询的锁竞争减小。
  • 在应用层关联,可以对数据方便拆分。
  • 查询本身的效率也可能更高:IN()代替关联查询,可以让 mysql 按照 ID 顺序查询,可能比随即的关联要更高效。
  • 可以减少冗余记录的查询:在应用层关联,只针对小量数据,数据库中只需要查询一次,而关联查询是笛卡尔积,可能被关联多次。
  • 在应用中实现了哈希关联,而不是 MySQL 的嵌套关联。

2.7 mysql 查询的过程

MySQL 执行一个查询的过程:

  1. 客户端发送一条查询给服务器
  2. 服务器先检查查询缓存,如果命中缓存,则立刻返回存储在缓存中的结果,否则进入下一阶段。
  3. 服务器进行 sql 解析,预处理,再由优化器生成对应的执行计划。
  4. MySQL 根据优化器生成的执行计划,调用存储引擎的 API 来执行查询。
  5. 讲结果返回给客户端。

2.8 索引优化的级别

  • consts 单表中只有一行匹配(主键或唯一索引),在优化阶段即可读取数据
  • ref 使用普通的索引(normal index)
  • range 对索引范围检索

优化要达到的目标 :至少是 range < 要求 ref < 最好 consts

2.9 使用覆盖索引

如果一个索引包含所有需要查询的字段的值,我们称之为“覆盖索引”。在 explain 的 extra 列可以看到“Using index”的信息。

2.10 使用延迟关联或子查询优化分页

MySQL 查询 offset 时,先查询 offset+N,然后放弃前 offset 行,导致 offset 很大时效率低下。可以先对数据进行过滤,然后再进行关联查询。

select a.* 
from `table` a, (select id from `table` where cond limit 10000, 20) b
where a.id=b.id

内部使用了覆盖索引,外部使用关联查询。

2.11 优化关联查询

  • 确保 on 或 using 子句的列上有索引。只需要在关联顺序中的第二个表的相应的列上创建索引。例如表 A 和表 B 用列 C 关联,如果优化器的关联顺序是 B、A,那么就不需要在 B 表的对应列上建上索引,没有用到的索引只会带来额外的负担。

  • 确保任何的 Group BY 和 ORDER BY 中的表达式只涉及到一个表中的列,这样才能用索引优化

参考资料

  • 《高性能 MySQL》
  • 《MySQL 技术内幕》