MySql优化及慢查询

Sql读取顺序

代码编写的程序：

select->distinct->from->join->on->where->group by->having->order by->limit

MySql读取顺序：

from->on->join->where->group by->having->select->distinct->order by->limit

先对多表进行关联，根据条件找出复合的记录

在复合的记录基础上，进行where条件过滤

对筛选的记录进行分组

对分组后的顺序用having操作进行过滤，过滤出满足条件的数据

对取出的记录进行排序

再按照分页条件取出要显示的数据

explain关键字的使用

explain关键字可以模拟优化器执行sql语句，可以根据explain分析结果和MySql底层数据结构优化sql。

EXPLAIN SELECT * FROM `sys_user` u,`sys_dept` d WHERE u.dept_id = d.dept_id

id：select查询的序号，表示select操作表的顺序，id相同的按顺序走，不同的序号大的先执行。

select_type：查询类型，主要区别普通查询，联合查询和子查询。

1. simple：简单select查询，不包含子查询或联合查询。
2. PRIMARY：主键查询
3. SUBQUERY：where条件包含了子查询。
4. DERIVED：from的表中包含子查询，被标记为derived（衍生），把子查询的结果存在临时表中

table：显示这一行数据是来自那张表的。

partitions：如果查询是基于分区的话，会显示查询访问的分区。

type：访问类型，按性能从低到高依次为：

1. ALL：全表扫描，一定要优化
2. index：它和ALL都是全表扫描，但index是从索引中读取表，ALL是从硬盘中读取
3. range：只检索给定范围的行，key列显示使用了哪个索引。适用于between and或in等查询
4. ref：非唯一性索引扫描，本质上也是一种索引访问
5. eq-ref：唯一性索引扫描，对于每一个索引键，只有一条记录与之匹配
6. const：通过索引一次就找到了，常见于primary或unique索引查找
7. system：表中只有一行记录（系统表），很少出现
8. NULL：不需要访问表

一般达到range或ref，没达到就要优化。

possible_keys：显示可能应用在这张表中的 索引，一个或多个，查询涉及的字段若建立了索引会列出来，但不一定会使用。

key：显示实际用到哪些索引，如果没有使用正确的索引，则需要优化。

key_len：索引使用的字节数，显示的值为索引字段的最大可能长度。

ref：索引是否被引用到，用到了哪些索引

rows：根据表统计信息及索引使用情况，估算所需读取的记录行数，太大需要优化

filtered：满足查询条件记录数量的比例，越高越好。

Extra：

1. Using filesort：文件排序，表示mysql无法利用索引完成排序操作，需要优化。
2. Using temporary：使用了临时表中间结果，常见于order by和group by，需要优化。
3. Using index：如果同时出现了Using where，表明索引用来执行索引键值的查找，如果没有用到，说明索引用来读取数据而非执行查找。
4. Using where：使用了Where过滤。
5. Using join buffer：使用了连接缓存
6. impossible where：where子句的值总为false，不能用来获取记录。

Sql及索引优化技巧

排序字段加索引：使用explain命令分析sql时得到type的值为index，表示该查询sql使用了索引。如果order by字段没有索引，type的值变为ALL，即全表索引，应对查询进行优化，尽量避免全表扫描，考虑在where及order by上建立索引。

where条件中or两边的字段没有索引时尽量少用or。or两边的字段中，如果有一个不是索引字段，会造成查询不走索引的情况。

区分in和exists：子查询的数据量少可以用in，如果外层查询数据少，而内层查询的数据大，则用exists，即小表驱动大表，注意返回的是true或fals。

不建议使用%前缀模糊查询，会导致索引失效，进行全表扫描，可以使用后缀模糊查询。

避免在where子句中对字段进行表达式操作，会导致索引失效。

对于联合索引来说，如果存在范围查询，比如between，>、<等条件时，会造成后面的索引字段失效，复合索引应该把常用的索引字段写前面。

使用JOIN优化：被驱动表的字段应作为on的限制字段，利用小表驱动大表。

A left join B，A表驱动B表，B left join A，B表驱动A表，inner join时，mySql会自动找出数据少的表作为驱动表。

慢查询

MySql的慢查询是MySql提供的一种日志，它用来记录在MySql中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的Sql语句。

开启日志

 MySql默认不会开启慢查询日志（OFF状态），需要手动设置这个参数，如果不是调优需要的话，不建议开启慢查询日志，开启后会拖慢服务器性能。

进入MySql命令端执行以下命令，查看日志是否开启：

show variables like '%slow_query_log%';

 执行下面的命令开启慢查询日志：

set global slow_query_log=1;

 这样修改只对当前数据库有效，重启后会恢复默认值，如果要永久生效，必须修改MySql配置文件，在my.cnf下添加以下内容：

slow_query_log=1
slow_query_log_file=日志存放路径

设置阈值

执行下面的命令查看阈值（默认是10）：

show variables like '%long_query_time%';

 执行下面的命令设置阈值：

set global long_query_time=5;

分析日志

 使用mysqldumpslow命令对慢查询日志进行分析，参数如下：

mysqldumpslow -s r -t 10 /var/lib/mysql/192-slow.log

#也可以加正则表达式
mysqldumpslow -s t -g 'left join' /var/lib/mysql/192-slow.log

#也可以使用more回车翻页查看
mysqldumpslow -s c /var/lib/mysql/192-slow.log|more

show profiles是mysql提供的可以用于分析当前会话中sql语句执行的资源消耗情况的工具，可以用于sql调优做参考，默认关闭，保存近15次运行结果。

查看当前版本是否支持：

show variables like '%profiling%';

 打开profile：

set profiling = on;

执行Sql诊断：

show profile cpu,block io for query 5;

当出现以下数据，说明要优化了：

converting HEAP to MyISAM：查询结果太大，内存放不下，需要写到磁盘里。

creating tmp tables：创建临时表，先copy数据到临时表，用完再删除。

copying to tmp table on disk：把内存中的临时表复制到磁盘上。

locked：被锁定。

最近面试不顺，因此下定决心写博客好好复习，祝我们都能上岸，加油！