MySQL进阶(三)：索引失效、SQL定位及调优（慢查询日志、mysql profile、全日志）

目录

一、索引失效

问：如果就要使用like+%关键字%，而且索引不失效？

二、explain

三、定位sql

0.查询优化

1.慢查询的开启并捕获

2.explain+慢sql分析

3.mysql profiles

4.全日志（不推荐，尤其是线上环境）

---

一、索引失效

关于索引跳转链接，在使用索引时，如果避免索引失效？下面综合各种情况来总结：

• 1.全值匹配最好（即：复合索引的每个列都被作为条件使用了）
• 2.遵循最佳左前缀法则（若创建的多个列的复合索引，在sql中使用时，若仅使用该复合索引的非第一列，索引会失效；即：必须包含第一列，且中间的列不能丢失，顺序不可颠倒，否则会从断裂点后面的索引列失效）
• 3.不在索引列上做任何操作（计算、函数\(自动or手动)类型转换),会导致索引失效而转向全表扫描
• 4.存储引擎不能使用索引中范围条件右边的列（即：范围条件右边的索引列失效）
• 5.尽量使用覆盖索引（只访问索引列的的查询（索引列与查询列一致）），减少使用：select *
• 6.mysql在使用不等于（!=或<>）的时候无法使用索引，会导致全表扫描
• 7.is null,is not null也无法使用索引
• 8.like以通配符开头（%asd）mysql索引失效会变成全表扫描的操作，但是like通配符结尾（adc%）索引还是有效的；
• 9.字符串不加单引号，索引失效（原因：自动类型转换）
• 10，少用or,用它来连接时会索引失效

关于组合索引，如A表中组合索引idx（a1,a2,a3,a4）,有以下几种情况：
0.SELECT * from A WHERE a1='a1'and a2='a2'and a4>'a4' and a3='a3';
索引都会被使用
1.SELECT * from A WHERE a1='a1'and a2='a2'and a3='a3' ORDER BY a4;
索引列都会被使用，只是排序的列没有被explain统计；
2.SELECT * from A WHERE a1='a1'and a2='a2'and a3>'a3' ORDER BY a4;
排序a4索引失效，而是using filesort；原因：范围后面索引失效；
3.SELECT * from A WHERE a1='a1'and a2='a2'and a4='a4' ORDER BY a3;
索引使用了3个：explain统计2个，排序其实也使用了索引，a4失效
4.SELECT * from A WHERE a1='a1'and a2='a2' ORDER BY a4;
索引使用了2个：explain统计2个，排序a4失效，原因：索引使用顺序与组合索引创建列的顺序不可断
5.SELECT * from A WHERE a1='a1',a4='a4' ORDER BY a2,a3;
使用了a1,a2,a3，explain统计1个；a4失效，无useing filesort
6.SELECT * from A WHERE a1='a1',a4='a4' ORDER BY a3,a2;
使用了a1，explain统计1个；a3,a2,a4失效，extra显示useing filesort
7.SELECT * from A WHERE a1='a1',a2='a2' ORDER BY a2,a3;
索引不会失效
8.SELECT * from A WHERE a1='a1',a2='a2' ORDER BY a3,a2;
索引不会失效
9.SELECT * from A WHERE a1>'a1' ORDER BY a1
索引不会失效，不会有using filesort

问：如果就要使用like+%关键字%，而且索引不失效？

答：使用覆盖索引，即：查询的列都是索引或组合索引

10.关于like范围的特例：后面的不失效
SELECT * from A where a1=3 and a2 like 'aa%' and a3='a3' a1,a2,a3的索引都不失效
11.
SELECT * from A where a1=3 and a2 like '%aa' and a3='a3' a2与a3失效
12.
SELECT * from A where a1=3 and a2 like '%aa%' and a3='a3' a2与a3失效
13.
SELECT * from A where a1=3 and a2 like 'a%aa%' and a3='a3' a1,a2,a3的索引都不失效

覆盖索引：简单理解sql查询列被所创建的索引字段完全覆盖；这样性能好的原因：sql直接从索引中读取所查数据，不需要读取数据行。

另，order by与group by相较于索引，情况类似。group by基本上都需要进行排序，会有临时表。

二、explain

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈；

格式：explain + sql语句

2.能干什么？

• 表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以使用
• 哪些索引被实际使用
• 表之间的引用
• 每张表有多少行被优化器查询

结果：

+----+-------------+-------+------------+-------+---------------+------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | e     | NULL       | ref   | dept_id_fk    | dept_id_fk | 5       | const |    1 |   100.00 | Using where |
|  2 | SUBQUERY    | d     | NULL       | const | PRIMARY       | PRIMARY    | 4       | const |    1 |   100.00 | Using index |
|  3 | SUBQUERY    | dc    | NULL       | ref   | loc_id_fk     | loc_id_fk  | 5       | const |   21 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------------+---------+-------+------+----------+-------------+

注释：

• 1.id的意思：
  三种情况：
  1.1 如果是平行，返回结果中id相同：执行顺序从上到下；
  1.2 如果是子查询，返回结果中id会递增，:执行顺序是id越大越先执行；
  1.3 id相同\不同同时存在:id越大越先执行,id相同的从上到下执行
• 2.select_type即查询类型
  常见的值：
  simple:普通查询,sql中不包含子查询或union
  primary:查询中若包含任何的子查询，最外层查询则为primary
  subquery:在select或where中子查询部分
  derived:在from中子查询被标记为derived(衍生)，临时表
  union:联合查询，union关键后的sql类型为union,外层sql标记derived
  union result:联合查询结果
• table：显示访问表
• type:与sql是否优化息息相关
  all:全表扫描
  index：index与all区别为index类型只遍历索引树，（也就是虽然all和index都是读全表，但index是索引中读取的，而all是从硬盘中读取的）
  range：检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引一般就是出现在where后的between,<,>,in;
  ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上是一种索引访问。属于查找和扫描的混合体
  eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
  const：表示通过索引一次就找到了，const用于比较primary key或unique索引；
  system：表只有一行数据，可以忽略
  NULL
  从最好到最差依次是：system>const>eq_ref>ref>range>index>all
  一般来说，得保证查询至少达到range级别，最好能达到ref
• possible_keys:可能应用在这张表中的索引，一个或多个；sql中涉及的字段存在索引，就会被列出。
• keys：实际使用到的索引，可能与possible_keys不同,null即没有使用索引，若查询中使用了覆盖索引，则该索引仅出现在key列中
• key_len：显示的值为索引字段的最大可能长度，并非实际使用长度，一般越小越好，这点和keys相冲，但还是以keys为最优。
• ref:表示哪个库中的哪个索引被使用，最优是const
• rows：根据表统计信息及索引的选用情况，大致估算出找到所需记录所需要读取的行数
• extra:可以有很多值，参考性高：
  using filesort：说明mysql对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作称为“文件排序”，显示内容包含该值表示sql排序性能不好。
  using temporary:性能比using filesort更差，这表示sql产生了临时表保存中间结果，mysql在对查询结果排序时使用临时表。常见于order by和分组查询group by。
  using index:表示相应的sql操作中使用了覆盖索引（covering index），避免访问了表的数据行，效率不错。如果同时出现了using where，表明索引被用来执行索引键值的查找，如果没有同时出现using where，表明索引用来读取数据而非执行查动作

三、定位sql

0.查询优化
1.慢查询的开启并捕获
2.explain+慢sql分析
3.show profile查询sql在mysql服务器里面的执行细节和生命周期情况
4.sql数据库服务器的参数调优

0.查询优化

查询优化分3点：

• 遵循小的数据集驱动大的数据集
  select * from A where id in (select id from B)
  当B表的数据集必须小于A表的数据集时，用in优于exists
  select * from A where exists (select 1 from B where B.id=A.id)
  当A的数据集小于B的数据集时，用exists优于in.注意：A表与B表的id字段应建立索引。
• order by会不会产生using filesort?
  如A表中组合索引idx（a1,a2,a3,a4）,有以下几种情况：
  1.SELECT * from A WHERE a1>'a1' ORDER BY a1
  索引不会失效，不会有using filesort
  2.SELECT * from A ORDER BY a1 desc,a2 asc;
  索引失效，会有using filesort
  3.SELECT * from A ORDER BY a1 desc,a2 desc;
  索引不会失效
  4.SELECT * from A ORDER BY a2 
  索引失效，会有using filesort
  5.SELECT * from A ORDER BY a1,a10
  索引失效，有using filesort，原因：a10不是索引列
  6.SELECT * from A where a1 in(……) ORDER BY a2,a3
  索引失效，有using filesort，原因：对于排序来说，多个相等条件也是范围查询
• group by
  类似order by；实质是先排序后分组，遵循索引列最佳左前缀
  where高于having,能写在where限定的条件就不要去having限定了。

1.慢查询的开启并捕获

mysql的慢查询日志是mysql提供的一种日志记录，它用来记录mysql中响应时间超过阙值的语句
具体指运行时间超过long_query_time值的sql，则会被记录到慢查询日志中。

查看慢查询日志是否开启

SHOW VARIABLES LIKE '%slow_query_log%';

设置为开启,设置输出目录

set GLOBAL slow_query_log =1;
仅对当前数据库生效；如mysql重启则会失效；
若要永久生效：需要修改配置文件my.cnf（其他系统变量也是如此）复制下面内容进去：
slow_query_log=1
slow_query_log_file=/var/lib/mysql/host_name-slow.log#地址

设置输出格式file/TABLE

set global log_output=`TABLE`
可以使用select * from mysql.slow_log;查看慢日志情况；
set global log_output=`file`
可以在slow_query_log_file中查看慢查询语句；

设置阙值时间

SHOW VARIABLES LIKE '%long_query_time%';
set global long_query_time=3;
#表示sql大于3s的都会记录在slow_query_log_file中

查看慢查询的条数

show global status like '%Slow_queries%'

然后进入设置的慢查询日志目录slow_query_log_file，查看文件内容，里面会有对应慢查询sql,执行时间，时间戳；

2.explain+慢sql分析

1.通过慢查询日志，获取到日志中的慢sql语句，使用关键字explain查看sql的执行过程的属性。

2.在linux终端执行
mysqldumpslow -help，可以利用mysqldumpslow查询对应sql的执行情况

3.mysql profiles

1.查看状态

show VARIABLES LIKE '%profiling%'

2.开启

set profiling=on

3.查看profile记录的执行sql情况

show profiles;
根据上面命令查询的sql执行所消耗的时间，包含query_id，针对某一条，查询对应内部过程
show profile cpu,block io for QUERY $query_id;

如：show profile cpu,block io for QUERY 102;
你将会看到一条sql的完整的生命周期，及每一步花费的时间；

出现如下4条，表示：性能堪忧

• converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上搬了。
• creating tmp table 创建临时表
• copying to tmp table on disk 把内存中临时表复制到磁盘，危险
• locked

4.全日志（不推荐，尤其是线上环境）

万不能在生产环境启动

在mysql的my.cnf,设置
#开启
general_log=1
general_log_file=/path/logfile
#输出格式
log_output=FILE
或者：log_output=`TABLE`


************************************
命令：
set global general_log=1
set global log_output=`TABLE`

此后，sql运行记录就会记录到general_log表，可通过sql查询：select * from mysql.general_log;