mysql慢查询优化

索引和慢查询

1. 怎样判断是否为慢查询
   MySQL判断一条语句是否为慢查询语句，主要依据SQL语句的执行时间，它把当前语句的执
   行时间跟 long_query_time 参数做比较，如果语句的执行时间 > long_query_time，就会把
   这条执行语句记录到慢查询日志里面。long_query_time 参数的默认值是 10s，该参数值可
   以根据自己的业务需要进行调整。
2. 如果判断慢查询是否应用了索引
   SQL语句是否使用了索引，可根据SQL语句执行过程中有没有用到表的索引，可通过 explain
   命令分析查看，检查结果中的 key 值，是否为NULL。
3. 应用了索引一定会变快吗？

select * from user where id>0;

虽然使用了索引，但是还是从主键索引的最左边的叶节点开始向右扫描整个索引树，进行了全表扫描，此时索引就失去了意义。而像 select * from user where id = 5; 这样的语句，才是我们平时说的使用了索引。它表示的意思是，我们使用了索引的快速搜索功能，并且有效地减少了扫描行数。查询是否使用索引，只是表示一个SQL语句的执行过程；而是否为慢查询，是由它执行的时间决定的，也就是说是否使用了索引和是否是慢查询两者之间没有必然的联系。我们在使用索引时，不要只关注是否起作用，应该关心索引是否减少了查询扫描的数据行数，如果扫描行数减少了，效率才会得到提升。对于一个大表，不止要创建索引，还要考虑索引过滤性，过滤性好，执行速度才会快。

如果提供索引查询效率

假如有一个8000万记录的用户表，通过age='18’索引过滤后，还需要定位5000万，SQL执行速度也
不会很快。其实这个问题涉及到索引的过滤性，比如1万条记录利用索引过滤后定位10条、100条、1000条，那他们过滤性是不同的。索引过滤性与索引字段、表的数据量、表设计结构都有关系。如下案例:
表:user
字段: id,name.sex.age

select * from user where age=16 and name like '李%'; 此时查询就会进行全部扫描

如果对上述的sql进行优化呢?

1. 追加name索引

alter table user add index(name);

2. 追加name、age索引

alter table user add index(age,name);

慢查询原因:

1. 全表扫描：explain分析type属性all
2. 全索引扫描：explain分析type属性index
3. 频繁的回表查询开销：尽量少用select *，使用覆盖索引.

分页查询优化

• 一般分页
  般的分页查询使用简单的 limit 子句就可以实现。limit格式如下：

SELECT * FROM 表名 LIMIT [offset,] size

1. 第一个参数指定第一个返回记录行的偏移量，注意从0开始；
2. 第二个参数指定返回记录行的最大数目；
3. 如果只给定一个参数，它表示返回最大的记录行数目；

• 如果偏移量固定，返回记录量对执行时间有什么影响？

select * from user limit 10000,1; 
select * from user limit 10000,10;
select * from user limit 10000,100;
select * from user limit 10000,1000;
select * from user limit 10000,10000;

结果：在查询记录时，返回记录量低于100条，查询时间基本没有变化，差距不大。随着查询记录
量越大，所花费的时间也会越来越多。

• 如果查询偏移量变化，返回记录数固定对执行时间有什么影响？

select * from user limit 1,100; 
select * from user limit 10,100;
select * from user limit 100,100; 
select * from user limit 1000,100; 
select * from user limit 10000,100;

结果：在查询记录时，如果查询记录量相同，偏移量超过100后就开始随着偏移量增大，查询时间
急剧的增加。（这种分页查询机制，每次都会从数据库第一条记录开始扫描，越往后查询越慢，而
且查询的数据越多，也会拖慢总查询速度。）

分页查询优化方案

1. 利用覆盖索引优化

select * from user limit 10000,100; 
select id from user limit 10000,100; 先查询id，在通过id查询数据

2. 利用子查询优化

select * from user limit 10000,100; 
select * from user where id>= (select id from user limit 10000,1) limit 100; 

原因：使用了id做主键比较(id>=)，并且子查询使用了覆盖索引进行优化。