慢查日志 慢查日志用于记录执行时间超过某个设定值的sql，可以记录用时长的sql进行针对性优化。
慢查日志默认关闭，需要手动开启。
1 2 3 4 5 6 7 8 9 10 -- 查询慢查日志是否开启 show variables like 'slow_query_log'； -- 开启日志 set global slow_query_log=on; -- 指定慢查日志存放在哪里 set global slow_query_log_file = '/Users/wt/Documents/projectLogs/Mysql-slow.log'; -- 是否把没有使用索引的sql记录到日志中 set global log_queries_not_using_indexes=on; -- 把超过多少秒的sql记录到日志中 (1秒) set long_query_time = 1;
日志内容：1 2 3 4 5 # Time: 2017-11-14T08:09:33.505020Z //执行sql的时间 # User@Host: root[root] @ localhost [127.0.0.1] Id: 8 //执行sql的主机信息 # Query_time: 0.007736 Lock_time: 0.000085 Rows_sent: 1000 Rows_examined: 1000 //sql执行信息 SET timestamp=1510646973; //sql执行时间 select * from film; //sql内容
慢查日志分析工具——Mysqldumpslow Mysqldumpslow是Mysql自带的命令行慢查日志分析工具。
1 2 Mysqldumpslow -t 3 /Users/wt/Documents/projectLogs/Mysql-slow.log //显示目标文件里的排名前3的数据
慢查日志分析工具——pt-query-digest 如何通过慢查日志发现有问题的sql
查询次数多且每次查询占用时间长的sql
通常为pt-query-digest分析的前几个查询
IO大的sql
注意pt-query-digest分析中的Rows examine项（sql扫描行数）
未命中索引的sql
注意pt-query-digest分析中Rows examine和Rows Send的对比
如何分析sql查询 使用explain查询sql的执行计划
1 2 3 4 5 6 explain select * from film; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | film | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
explain返回各列的含义：
table：显示这一列的数据关于那张表的。
type：这是重要的一列，显示连接使用了何种类型。从最好到最差连接类型为const、eq_reg、ref、range、index和ALL。
possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。
key：实际使用的索引。如果为null，则没有使用索引。
key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好。
ref：显示索引的哪一列被使用了，如果可能的话，是一个常数。
rows：Mysql认为必须检查的用来返回请求数据的行数。
extra ：需要注意的返回值：1.using filesort ：看到的这个的时候，说明查询需要优化了。Mysql需要进行额外的步骤来发现对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。2.using temporary ：这里，Mysql需要创建一个临时的表来存储结果，这通常发生在对同的列表进行order by上，而不是group by上。
如何优化sql count()和max()的优化 max()优化
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Mysql> explain select max(payment_date) from `payment` \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: payment partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 16086 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec)
可以看出，上面的查询语句使用的是全表扫描，没有使用索引，当数据量非常大的时候，IO非常高，拖慢服务器的IO效率。通常情况下，我们可以在这个payment_date上建立一个索引：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 Mysql> create index idx_paydate on payment(payment_date); Mysql> explain select max(payment_date) from `payment` \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: NULL partitions: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL filtered: NULL Extra: Select tables optimized away 1 row in set, 1 warning (0.00 sec)
这样，这次查询就是走索引了，它并不需要全表数据，因为索引是顺序排序的，不管数据量有多大，查询的速率基本上是恒定的。
count()优化
条件：在一条sql中同时查出2006年和2007年电影的数量。
1 Mysql> select count(release_year='2006' or null) as '2006',count(release_year='2007' or null) as '2007' from film;
count(*)和count(id)的区别：如果某一条数据的id为null，count(id)不会统计这条数据，而count(*)会
子查询优化 通常情况下，需要把子查询优化为join查询，但在优化的时候需要注意关联键是否有一对多的关系，要注意重复数据。
1 2 3 4 -- 子查询的方式 select * from t where t.id in (select p.tid from p); -- join方式 select * from t join p on t.id = p.tid;
当p表中有多条tid一样的数据时，t表和p表存在一对多的关系，这时候如果使用join方式查询会返回多条重复的数据，而子查询的方式不会，这时候可以使用distinct来去重：
1 select distinct * from t join p on t.id = p.tid;
group by查询 条件：查询每一个演员参演的影片的数量。用到了演员表actor和影片演员表film_actor。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 Mysql> explain select actor.`first_name`,actor.`last_name`,COUNT(*) from film_actor inner join actor using(actor_id) group by film_actor.`actor_id` \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: actor partitions: NULL type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 200 filtered: 100.00 Extra: Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: film_actor partitions: NULL type: ref possible_keys: PRIMARY,idx_fk_film_id key: PRIMARY key_len: 2 ref: sakila.actor.actor_id rows: 27 filtered: 100.00 Extra: Using index 2 rows in set, 1 warning (0.00 sec)
可以看出我们的查询语句没有where条件，那么对actor表进行了表扫描也是正常情况，还用到了临时表和文件排序。我们做如下修改：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 Mysql> explain select actor.`first_name`,actor.`last_name`,c.cnt from actor inner join (select actor_id,COUNT(*) as cnt from film_actor group by actor_id) as c using(actor_id) \G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: actor partitions: NULL type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 200 filtered: 100.00 Extra: NULL *************************** 2. row *************************** id: 1 select_type: PRIMARY table: <derived2> partitions: NULL type: ref possible_keys: <auto_key0> key: <auto_key0> key_len: 2 ref: sakila.actor.actor_id rows: 27 filtered: 100.00 Extra: NULL *************************** 3. row *************************** id: 2 select_type: DERIVED table: film_actor partitions: NULL type: index possible_keys: PRIMARY,idx_fk_film_id key: PRIMARY key_len: 4 ref: NULL rows: 5462 filtered: 100.00 Extra: Using index 3 rows in set, 1 warning (0.00 sec)
首先通过子查询查询每一个actor_id对应的影片数量，然后再跟演员表关联查询演员的基本信息。这样做，已经没有使用到临时表和排序的方式了，取而代之的是索引的操作。如果表非常大，那么节省了大量的IO。如果where条件，应该写在子查询中，而不是写在外部。
limt的优化 limit常用于分页处理，时常会伴随order by从句使用，因此大多数时候会使用filesorts，这样会造成大量的IO问题。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Mysql> explain select film_id,description from film order by title limit 50,5 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: film partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1000 filtered: 100.00 Extra: Using filesort 1 row in set, 1 warning (0.00 sec)
可以看出，sql使用了表扫描的方式，还使用了filesort，当数据量大的时候，就会出现IO问题。
优化步骤1：使用有索引的列或主键进行order by操作
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Mysql> explain select film_id,description from film order by film_id limit 50,5 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: film partitions: NULL type: index possible_keys: NULL key: PRIMARY key_len: 2 ref: NULL rows: 55 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec)
使用了索引，只扫描的55行，但是当需要的limit越来越大，需要扫描越后面的数据，IO量也会越来越大，所以需要一些别的优化。
优化步骤2：记录上次返回的主键，在下次查询的时候使用主键过滤
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Mysql> explain select film_id,description from film where film_id>55 and film_id<=60 order by film_id limit 1,5 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: film partitions: NULL type: range possible_keys: PRIMARY key: PRIMARY key_len: 2 ref: NULL rows: 5 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec)
这样，无论需要扫描多后面的数据，sql固定扫描5行。效率基本是一致的。但是，这种方式需要索引是排序的，中间不能空缺。如果有空缺，可以加一个附加的列，作为索引。