mysql索引实战优化

继上篇博客示例，在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

6、基于慢sql查询做优化

#展示慢查询日志是否开启以及日志所在目录
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%';