本文是关于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 user 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 order where YEAR(date) < = '2017'

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

SELECT * FROM table where (b+1)>2 and left(c,2) =’a111’

1.4 前导模糊不能使用索引

select * from order where desc like '%XX'

请使用搜索引擎代替。

1.5 使用limit

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

select * from user 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 table1 a, (select id from table1 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技术内幕》