MySQL性能调优(3)查询优化详解

1
2
3

1. id相同是相当于一组，执行顺序由上至下
2. id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
3. id相同又不同即两种情况同时存在，id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

1
2
3
4
5
6
7

查询的类型，主要是用于区分普通查询、联合查询、子查询等
SIMPLE：简单的select查询，查询中不包含子查询或者union
PRIMARY：查询中包含子部分，最外层查询则被标记为primary
SUBQUERY/MATERIALIZED：SUBQUERY表示在select 或 where列表中包含了子查询
MATERIALIZED表示where 后面in条件的子查询
UNION：若第二个select出现在union之后，则被标记为union
UNION RESULT：从union表获取结果的select

1
2

<unionM,N> 由ID为M,N 查询union产生的结果
<subqueyN> 由ID为N查询生产的结果

1
2
3
4
5
6
7
8

访问类型，sql查询优化中一个很重要的指标，结果值从好到坏依次是：system > const > eq_ref > ref > range > index > ALL
system：表只有一行记录（等于系统表），const类型的特例，基本不会出现，可以忽略不计
const：表示通过索引一次就找到了，const用于比较primary key 或者 unique索引
eq_ref：唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描
ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质是也是一种索引访问
range：只检索给定<font color=red>范围</font>的行，使用一个索引来选择行
index：Full Index Scan，索引全表扫描，把索引从头到尾扫一遍
ALL：Full Table Scan，遍历全表以找到匹配的行

1
2
3
4

possible_keys:查询过程中有可能用到的索引
key:实际使用的索引，如果为NULL，则没有使用索引 
rows:根据表统计信息或者索引选用情况，大致估算出找到所需的记录所需要读取的行数
filtered:它指返回结果的行占需要读到的行(rows列的值)的百分比表示返回结果的行数占需读取行数的百分比,filtered的值越大越好

1
2
3
4
5
6
7

这条语句就是把两个子查询查出来再结合
这里有个特殊情况：id等于null那个是最后执行的，因为他需要的到其他查询结果结合。
//--------------------------------
1. 这里就看id最大的4：select_type是subquery说明是子查询；type为ref非唯一性索引扫描；用到了id和address索引。
2. id为3的开始执行，select_type为union说明第二个select出现在union之后，则被标记为union；
3. id等于2和id等于1就是之前的子查询与查询；并且用到了主键索引。
4. 然后最后再把1和3合并；这里可以看出组合查询使用到了临时表。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

// 查看是否打开慢查询日志
show variables like 'slow_query_log'
// 打开慢查询日志
set global slow_query_log = on
//  保存日志位置
set global slow_query_log_file ='/var/lib/mysql/gupaoedu-slow.log'
 // 没有命中索引的都保存
set global log_queries_not_using_indexes = on
// 超过多少秒记录日志
set global long_query_time = 0.1 (秒)
//查看是否开启
show variables like 'slow_query%';

1
2
3
4
5

Time ：日志记录的时间
User@Host：执行的用户及主机
Query_time：查询耗费时间 Lock_time 锁表时间 Rows_sent发送给请求方的记录条数 Rows_examined 语句扫描的记录条数
SET timestamp 语句执行的时间点
select .... 执行的具体语句

1

mysqldumpslow -t 10 -s at /var/lib/mysql/gupaoedu-slow.log