慢查询sql索引优化
模拟大量数据表、脚本
explain执行计划属性含义
1，id值越大越先执行，id相同顺序执行
2，select_type:simple、primary、subquery、derived衍生、union、union result
| select_type | 含义 | sql |
|---|---|---|
| simple | 简单的select查询，查询中不包含子查询或者union | explain select * from employees join dept_emp on employees.emp_no= dept_emp.emp_no where employees.emp_no=10; |
| primary | 子查询中，最外层的查询 | explain SELECT * FROM employees WHERE employees.emp_no = (SELECT MAX(emp_no) FROM employees); |
| subquery | 子查询 | explain SELECT * FROM employees WHERE employees.emp_no = (SELECT MAX(emp_no) FROM employees); |
| derived | from列表中包含了子查询，mysql会递归执行这些子查询，把结果放在临时表中 | explain select * from employees join (SELECT MAX(emp_no) t1 FROM employees) temp on employees.emp_no= temp.t1; |
| union | 查询中包含union表示并集并且去重，union all不去重 | explain select * from employees where first_name = ‘xyz’ union select * from employees where last_name = ‘xyz’; |
| union result | union去重结果 | explain select * from employees where first_name = ‘xyz’ union select * from employees where last_name = ‘xyz’; |
3，type访问类型
| type | 含义 | 举例 |
|---|---|---|
| null | mysql不访问任何表，索引，直接返回结果 | explain select now(); |
| system | 系统表，少量数据，不需要磁盘io，mysql5.7显示all | |
| const | 命中主键primary key或者唯一unique索引；被连接部分是一个常量const值 | explain select * from employees where emp_no=3; |
| eq_ref | 前表的每一行，后表只有一行被扫描。1join查询，2命中主键或非空唯一索引，3等值连接 | explain select * from dept,emp where emp.id=dept.id; |
| ref | 非唯一性索引扫描，返回匹配某个单独值的所有行。 | |
| range | where之后出现between，<，>，in等操作 | explain select * from employees where emp_no<=3; |
| index | 需要扫描索引上全部数据。 | explain select emp_no,first_name from employees; |
| all | 全表扫描 |
4，extra额外的执行信息
| extra | 含义 | 举例 |
|---|---|---|
| using filesort | 排序字段为非索引字段，或者组合索引中非左索引倒序 | explain select emp_no,first_name from employees order by birth_date desc; |
| using temporary | 需要建立临时表暂存中间结果，常见于order by和group by | explain select count(*),last_name from employees group by last_name; |
| using index | 二级索引字段和主键满足查询要求，不需要回表，效率好 | explain select count(*),first_name from employees group by first_name; |
show profiles
MySQL提供可以用来分析当前会话中SQL语句执行的资源消耗情况。
MySQL调优–03—show profiles 和 show processlist
索引优化
1，组合索引
1.1，组合索引，只要字段匹配成功即可，与字段顺序无关
1.2，组合索引，最左匹配
1.3，组合索引中，非最左字段使用范围查询，会导致组合索引中范围字段后边的所有字段索引不生效。
2，不要在索引列上进行运算操作，索引将失效
explain select * from table where substring(name，3,2)='科技'
3，字符串不加单引号，导致索引失效。因为mysql优化器会进行类型转换
4，尽量使用覆盖索引，避免select *，会导致回表
5，or前后需要都使用索引，引起索引合并，索引才能生效。如果一侧使用索引会导致索引失效。多表查询中哪怕or两边都带有索引也会导致索引失效。遇到or尽量用union或者union all代替。
6，以%开头的like模糊查询，导致索引失效。
7，索引列数值重复概率大，mysql以为使用索引比全表扫描慢，会不使用索引。
8，is null或者is not null会根据值null或者非null比例判断是否值得使用索引。
9，普通索引中，in走索引，not in索引失效。主键索引in和not in都走索引。
10，多个普通索引字段，只会生效一个，其他索引会失效。如果条件中包含几个字段，尽量使用组合索引。
sql优化
order by优化：
1，查询字段尽量使用索引字段和主键，减少回表。
2，order by排序字段有多个时，尽量要么同时递增，要么同时递减。
3，排序前后顺序尽量和组合索引字段顺序一致。
4，如果必须使用到filesort时，max_length_for_sort_data大小和query语句取出的字段总大小，来判断一次扫描还是两次扫描，可以适当提高sort_buffer_size和max_length_for_sort_data系统变量，来增大排序区的大小，提高排序效率。
子查询优化：
子查询会生成临时表，效率低于连接join查询。
limit优化：
比如 limit 100000,10，mysql需要排序前100010记录，仅仅返回最后10条记录，其他记录丢弃，查询排序代价非常大。
方式1：索引上完成排序分页操作，最后根据主键关联回原表所需要的其他列数据。
select * from employees join(select emp_no from employees
where first_name like 'x%' order by birth_date desc
limit 100000,10) as tempwhere employees.emp_no=temp.emp_no;
方式2：只适用于主键自增。
select * from employees
where first_name like 'x%' and emp_no>100000 order by emp_no asc
limit 10;
sql优化 涉及到索引失效和慢查询
对慢查询的优化：
在业务系统中,除了使用主键进行的查询,其他的我都会在测试库上测试其耗时,慢查询的统计主要由运维在做,会定期将业务中的慢查询反馈给我们.
慢查询的优化首先要搞明白慢的原因是什么? 是查询条件没有命中索引?是load了不需要的数据列?还是数据量太大?
所以优化也是针对这三个方向来的,
首先分析语句,看看是否load了额外的数据,可能是查询了多余的行并且抛弃掉了,可能是加载了许多结果中并不需要的列,对语句进行分析以及重写.
MySQL提供了explain命令来查看语句的执行计划,MySQL在执行某个语句之前,会将该语句过一遍查询优化器,之后会拿到对语句的分析,也就是执行计划,其中包含了许多信息. 可以通过其中和索引有关的信息来分析是否命中了索引,例如possilbe_key,key,key_len等字段,分别说明了此语句可能会使用的索引,实际使用的索引以及使用的索引长度.分析语句的执行计划,然后获得其使用索引的情况,之后修改语句或者修改索引,使得语句可以尽可能的命中索引.
如果对语句的优化已经无法进行,可以考虑表中的数据量是否太大,如果是的话可以进行横向或者纵向的分表.