继上篇博客示例,在employees表里添加1000000百万数据,由于电脑太老旧,速度慢只插入200000条数据做测试。
CREATE PROCEDURE insert_emp()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE (i <= 1000000 ) DO
INSERT INTO employees(name,age,position,hire_time) VALUES(CONCAT('RICK',i),i,'manager',NOW());
set i=i+1;
END WHILE;
END
call insert_emp();
1、联合索引第一个字段范围查询可能不走索引
EXPLAIN SELECT * FROM employees WHERE name > 'Rick' AND age = 22 AND position ='manager';
从测试结果看出,没有使用索引查询,原因是mysql在底层做了分析并计算cost成本值,mysql认为全表扫描的速度可能比使用联合索引的速度还要快。所以没有使用联合索引。
2、强制走索引
EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'Rick' AND age = 22 AND position ='manager';
虽然看上去rows扫描的行数相比不走索引要少了很多,但是并不代表查询效率就高。继续做一个测试:
-- 关闭查询缓存
set global query_cache_size=0;
set global query_cache_type=0;
SELECT * FROM employees WHERE name > 'Rick';
SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'Rick';
在本台笔记本上第一条查询sql中执行时间大概0.5s,第二条查询sql执行时间大概0.7s。原因是强制走索引会回表的开销。
3、覆盖索引优化
使用覆盖索引覆盖查询字段,避免回表。
EXPLAIN SELECT name,age,position FROM employees WHERE name > 'Rick' AND age = 22 AND position ='manager';
4、in和or在数据量比较大时会走索引,数据量少时会全表扫描(employees_tmp为复制表,数据只有3行)。or的结果跟in一样
EXPLAIN SELECT * FROM employees WHERE name in ('RICK','TONY','TOM') AND age = 22 AND position ='manager';
EXPLAIN SELECT * FROM employees_tmp WHERE name in ('RICK','TONY','TOM') AND age = 22 AND position ='manager';
5、like 'abc%'一般会走索引
EXPLAIN SELECT * FROM employees WHERE name like 'abc%' AND age = 22 AND position ='manager';
like 'abc%'是走了索引的,那什么情况不会索引呢?
EXPLAIN SELECT * FROM employees WHERE name like 'Rick%' AND age = 22 AND position ='manager';
当使用like 'Rick%'时没有走索引,mysql在计算like 'Rick%'会分析大量数据的name字段都是Rick开头的,回表会浪费很多时间,所以直接走全表扫描。
like 'abc%'其实使用了索引下推,那么什么是索引下推呢?
在mysql5.6之前,like 'abc%'使用联合索引时只会匹配name字段,联合索引后面的age和position字段不会匹配,也就是说使用联合索引name字段过滤数据以后就回表查询,再进行age和position字段匹配。
在mysql5.6之后引入了索引下推,对于这种情况进行优化,在使用联合索引查询时不仅匹配name字段,还会继续匹配后面的age和position字段,减少查询数据进而减少回表次数。
如何使用mysql trace工具分析sql执行
set session optimizer_trace="enabled=on",end_markers_in_json=on; ##开启trace
select * from employees where name like 'Rick%';
SELECT * FROM information_schema.OPTIMIZER_TRACE;
set session optimizer_trace="enabled=off"; ##关闭trace
order by和group by优化示例
order by
示例一:
EXPLAIN SELECT * FROM employees WHERE name = 'Rick' AND position ='manager' ORDER BY age;
从key_len列得知联合索引只走了name字段,但是从Extra列中看出并没有使用using filesort,说明age排序走了索引。而下面sql使用position排序时,跳过了age,出现using filesort使用外部排序。
EXPLAIN SELECT * FROM employees WHERE name = 'Rick' ORDER BY position
示例二: 只使用name字段走索引,而age、position做排序,无using filesort;
EXPLAIN SELECT * FROM employees WHERE name = 'Rick' ORDER BY age,position;
当使用position、age的顺序进行排序时,出现了using filesort,说明排序没有走索引
EXPLAIN SELECT * FROM employees WHERE name = 'Rick' ORDER BY position,age;
当我们在where中新增查询条件age=18,发现Using filesort会消失;因为age为常量,相当于name和age都确定了,自然就会使用联合索引排序。
EXPLAIN SELECT * FROM employees WHERE name = 'Rick' AND age=18 ORDER BY position,age
示例三
EXPLAIN SELECT * FROM employees WHERE name = 'Rick' ORDER BY age ASC,position DESC
age升序,position倒序,出现using filesort会使用外部排序
使用in,对于排序来说,相当于使用范围查询,age和position都是无序的。
name范围查询并使用name进行排序出现using filesort,是因为查询需要的字段联合索引没有覆盖,进行优化
group by
group by在底层是使用order by先排序再进行分组,所以二者实际上优化是类似的。
Using filesort文件排序实现原理
文件排序方式有两种:单路排序和双路排序;以下面sql为例
SELECT * FROM employees WHERE name = 'Rick' ORDER BY name;
单路排序:(1)name字段走联合索引找到对应id;(2)通过id回表主键索引拿到行数据;(3)将行数据全部加载sort buffer内存中(默认1024B);(4)重复1/2/3动作直到取完所有满足条件的数据;(5)在sort buffer中通过name字段排序;(6)返回客户端。
双路排序:(1)使用联合索引取出所有满足条件的name和id;(2)通过id去主键索引取出的行数据;全部加载到sort buffer中(4)重复1/2/3(5)在sort buffer对name字段进行排序;(6)将排好序的name和id通过id回表取出所有数据返回客户端;
注意:如果字段的总长度小于1024B ,那么使用单路排序模式;大于max_length_for_sort_data 那么使用双路排序模式
索引设计原则
1、代码先行,索引后上
2、联合索引尽量覆盖条件
3、不要在小基数字段上建立索引:例如 男、女
4、长字符串我们可以采用前缀索引:对于varchar(255)的大字段,建议取前面20个字符做索引
5、where与order by冲突时优先where
#展示慢查询日志是否开启以及日志所在目录
show variables like '%slow_query_log%';
#开启慢查询日志
set global slow_query_log=1;
show variables like 'slow_query_log_file';
#查看慢查询时间
show variables like 'long_query_time%';
#设置慢查询时间
set global long_query_time=4;
#查询有多少条慢查询记录
show global status like '%Slow_queries%';