优化排序
我们以 MySQL 官方数据库 employees表来做示例.
employees 表结构:
image.png
数据预览:
image2.png
表中的数据量大概是 30W, 表中现只有一个主键,现有如下一个需求,查询性别为 M (男性)的员工信息,并按照出生日期排序.通常我们会书写以下 sql 语句:
SELECT * FROM employees WHERE gender="M" ORDER BY birth_date LIMIT 30;
消耗时间:
image.png
Explain 结果:
image2.png
由于两个字段都是没有索引的,所以 mysql 执行的事全表扫描,优化器需要通过索引进行回表查询数据(using where),并且进行了排序操作(using filesort),速度较慢.
可以考虑以下优化,由于性别只有男性和女性两个值,所以不考虑单独给性别添加索引,我们可以将性别和出生日期建立一个覆盖索引:
ALTER TABLE employees ADD INDEX gender_birth (gender,birth_date);
再次执行,查看消耗时间:
image3.png
Explain 结果:
image4.png
可以看出,查询速度提升了非常多.这里使用了 mysql 5.6 的新特性Index Condition Pushdown (using index condition),即在数据引擎层就可以利用索引直接判断 where 条件是否成立,无需回表判断,过滤掉大量的无用数据.并且索引中包含了 birth_date,mysql 无需对数据进行排序操作.
延迟关联
考虑如下语句:
SELECT * FROM employees WHERE gender="M" ORDER BY birth_date LIMIT 170000,30;
sql 执行时间:
image.png
这里面是一个很常见的分页业务,但是即使添加了索引,当偏移量越来越大时,查询依旧会很慢,优化这类查询的一个比较好的策略就是延迟关联,通过覆盖索引先查出所需数据的主键,再根据这些主键关联原表获得需要的数据行.
更改 sql:
SELECT * FROM employees INNER JOIN( SELECT emp_no FROM employees
WHERE gender = "M" ORDER BY birth_date LIMIT 170000,30) AS x USING (emp_no);
执行时间:
image2.png
Explain:
image3.png
网友评论