2. 慢查询、索引、执行计划详解

慢查询

一、慢查询定义及作用

慢查询日志，顾名思义，就是查询慢的日志。
分析MySQL语句查询性能的方法除了使用 EXPLAIN 输出执行计划，还可以让MySQL记录下查询超过指定时间的语句，我们将超过指定时间的SQL语句查询称为“慢查询”。
该日志能为sql语句的优化带来很好的帮助。默认情况下慢查询日志是关闭的，要使用慢查询日志功能，首先要开启慢查询日志功能。
1
2
3

二、启动慢查询

配置文件地址
  windows ： D:\WorkSoftWare\DataDB\MySQL\MySQL Server 5.7\my.ini(我的本地路径)
  linux : my.cnf
  
文件内容
  log-output=FILE,TABLE
  slow-query-log=1
  slow_query_log_file="LAPTOP-D67UA1L3-slow.log"
  long_query_time=3
1
2
3
4
5
6
7
8
9

配置文件中各参数含义

1. log_output 日志存放的地方,可以是数据库表（[table]），文件（[file],或者是([file,table])）
2. slow-query-log 启动停止慢查询日志（0否/1是）
3. slow_query_log_file 指定慢查询日志的存储路径及文件（默认和数据文件放在一起）
4. long_query_time 指定记录慢查询日志SQL执行时间的阈值（单位：秒，默认10秒）
5. log_queries_not_using_indexes 是否记录未使用索引的SQL

三、慢查询记录

找到数据库的date目录,在下面可以找到LAPTOP-D67UA1L3-slow.log文件，该文件即为记录文件

D:\WorkSoftWare\DataDB\MySQL\MySQL Server 5.7\data
1

1 记录的内容

1. 查询语句
2. 数据修改语句
3. 已经回滚的sql

2 记录内容详解

1. # Time: 2020-06-22T16:08:25.667840Z
2. # User@Host: root[root] @ localhost [::1]  Id:     1
3. # Query_time: 111.353229  
4. # Lock_time: 0.000000 
5. # Rows_sent: 2794  
6. # Rows_examined: 2551
7. use gdtreiking;
8. SET timestamp=1944;
9. SELECT * from rkrm$_log_diinfo d LEFT JOIN  rkrm$_log_buzinfo b on d.menuid = b.menuid;
1
2
3
4
5
6
7
8
9

3 sql修改慢查询阈值，以及session问题

在navicat中先执行查询阈值操作,值为3秒。之后执行修改操作，设置为0秒。在查询的话值依旧为3秒。这是因为数据库的session问题。当我们新打开一个页签进行查询。我们就会发现阈值已经改变。

SHOW VARIABLES LIKE 'long_query_time';	/**查询阈值**/
SET GLOBAL long_query_time = 0;   			/**设置阈值**/
1
2

四、慢查询分析工具[mysqldumpslow]

1 简介

mysql自带工具

D:\WorkSoftWare\DataDB\MySQL\MySQL Server 5.7\bin\mysqldumpslow.pl（我的安装位置）
mysqldumpslow.pl无法直接运行，配合activeperl等慢查询工具使用，安装activeperl后运行
1
2

2 使用语法

注意：

1. 一般使用mysql的data目录的绝对路径会有特殊符号，所以可以在bin目录下使用相对路径，或者将日志文件拷贝到路径简单为目录下使用。

2. 结果展示了，但是具体参数可能无法展示出来，一般为N替代。

语法：
    perl mysqldumpslow.pl -s t -t 3 ./../data\LAPTOP-D67UA1L3-slow.log
        -s (排序字段，后可接 c,t,l,r,at,al,ar)
            c: 总次数，
            t: 总时间，
            l: 锁的时间，
            r: 总数据行，
            at: t的平均数，例如[at = 总时间/总次数]
            al: l的平均数，
            ar: r的平均数，
            -g: 后边可以写一个正则匹配模式，大小写不敏感的
        -t top 指定取前面几天作为结果输出
    
结果：
    Reading mysql slow query log from ./../data\LAPTOP-D67UA1L3-slow.log
    Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
      Time: N-N-23T12:N:N.682287Z
      # User@Host: root[root] @ localhost [::N]  Id:    N
      # Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N
      SET timestamp=N;
      SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'S' ORDER BY TABLE_SCHEMA, TABLE_TYPE
    
    Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
      Time: N-N-23T12:N:N.273833Z
      # User@Host: root[root] @ localhost [::N]  Id:    N
      # Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N
      SET timestamp=N;
      SHOW TABLE STATUS LIKE 'S'
    
    Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
      Time: N-N-23T12:N:N.749521Z
      # User@Host: root[root] @ localhost [::N]  Id:    N
      # Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N
      SET timestamp=N;
      SHOW TABLE STATUS LIKE 'S'
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

五、数据库三大范式

一、第一范式

数据库表的每一列都是不可分割的原子数据项

二、第二范式

在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）。即要求数据库表中的每个实例或记录必须可以被唯一地区分。选取一个能区分每个实体的属性或属性组，作为实体的唯一标识。

三、第三范式

在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）。简而言之，说的是实体和实体之间的联系，就是关联表，他们之间用主键连起来，又叫外键关联。

六、常用优化方式

1. 服务器硬件优化

比如固态硬盘的存储速度是机械硬盘的10倍以上。
1

2. MySql服务器优化

linux系统要比windows系统稳定，并且运行速度要快。
1

3. SQL本身优化

具体如何优化，可以参考这篇文章

 //错误例句：该SQL中的关联子查询尽可能改成关联查询
 select a.meta1, a.meta2, a.meta3, (select b.meta1 from tableb b where a.meta1 = b.meta1) as meta4 from tablea a order by a.meta2, a.meta3, a.meta4
1
2

4. 反范式设计优化

1. 反范式化是针对范式化而言的。
2. 所谓的反范式化是为了性能和读取效率的考虑，而适当的对数据库设计范式的要求进行违反。
3. 允许存在少量冗余，话句话说反范式化就是使用空间换取时间。

5. 索引优化

七、索引和执行计划

1 索引分类

1.普通索引：即一个索引只包含单列，一个表可以有多个单例索引
2.唯一索引：索引列的值必须唯一，但允许有空值
3.复合索引：即一个索引包含多个列
1
2
3

2 索引基础语法

1.查看索引：SHOW INDEX FROM TABLE_NAME
2.创建索引：CREATE [UNIQUE] INDEX indexName ON TABLE(columnname(length));
            ALTER TABLE tableName ADD [UNIQUE] INDEX  [indexName] ON (columnname(length));
        
3.删除索引：DROP INDEX [indexName] ON [tableName]
例如: 
    //总数 278414
    select count(*) from mytest
    //2.753秒
    SELECT * FROM mytest ORDER BY database_name,table_name,column_name,data_type
    //创建索引
    CREATE INDEX order_index ON mytest (database_name,table_name,column_name,data_type)
    //0.216秒
    SELECT * FROM mytest where database_name = 'DW_HQ_ERP_SC' ORDER BY database_name,table_name,column_name,data_type
    //删除索引
    DROP INDEX order_index ON mytest
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

3 检验sql是否使用了索引——执行计划

执行计划是什么？

使用Explain关键字可以模拟优化器执行SQL查询语句，从而知道是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。
语法：Explain + SQL语句
例如：Explain SELECT * FROM mytest where database_name = 'DW_HQ_ERP_SC' ORDER BY database_name,table_name,column_name,data_type
1
2
3

4 执行计划分析——是否使用了索引

5 执行计划分析——索引是否被充分利用

执行计划中对于索引是否充分使用问题使用了key_len算法。
执行计划结果字段key_len就是按照以下规则计算结果.结果其实就是指的字节数
类型: varchar(+2) char(+0)
字符编码：utf8(+3)......
字段定义长度
是否为空: 是(+1) 否(+0)
1
2
3
4
5
6

①、定义的索引:

②、元数据信息:

③、key_len算法:

6. 执行计划分析——其他信息介绍

①、select_type属性:

②、type属性:

③、key_len属性:

7 索引使用10大军规

1.尽量索引字段全值匹配

1.Explain select * from mytest where database_name = 'DW_HQ_ERP_SC'
2.Explain select * from mytest where database_name = 'DW_HQ_ERP_SC' and table_name = 'ADRC'
3.Explain select * from mytest where database_name = 'DW_HQ_ERP_SC' and table_name = 'ADRC' and column_name = 'STR_SUPPL3'
4.Explain select * from mytest where database_name = 'DW_HQ_ERP_SC' and table_name = 'ADRC' and column_name = 'STR_SUPPL3' and data_type = 'VARCHAR2'
1
2
3
4

2.最佳左前缀法则。

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
当索引顺序为：`database_name`, `table_name`, `column_name`, `data_type`
1.错误示例，直接从第二个索引字段开始:
  Explain select * from mytest where table_name = 'ADRC'
2.错误示例，跳过了table_name字段:
  Explain select * from mytest where database_name = 'DW_HQ_ERP_SC' and column_name = 'STR_SUPPL3'
3.正确示例，从索引的最左前列开始并且不跳过索引中的列:
  Explain select * from mytest where database_name = 'DW_HQ_ERP_SC' and table_name = 'ADRC' and column_name = 'STR_SUPPL3' and data_type = 'VARCHAR2'
1
2
3
4
5
6
7
8

3.不要在索引列上做任何操作。

不在索引列上做任何操作（计算、函数、（自动或手动）类型转换），会导致索引失效而转向全表扫描。
1

例如该sql语句：

检验是否使用了索引:


4.范围条件放最后

存储引擎不能使用索引中范围条件右边的列。
例如有一个如下的查询语句：
    select * from table where age > 12 and age <= 18
而这个table表的索引顺序为
    `age`, `name`, `sex`
那么元数据age作为第一个索引字段，需要进行索引优化，调整table表的索引顺序为
    `name`, `sex`, `age` 
将age属性作为索引顺序中最后一个值，即可完成优化。
1
2
3
4
5
6
7
8

8.like查询要当心

like条件使用时%应该加在匹配条件后面
1.%放在后面
    Explain select * from mytest where database_name = 'DW_HQ_ERP_SC' and table_name = 'ADRC' and column_name = 'STR_SUPPL3' and data_type like 'V%'
2.%放在前面
    Explain select * from mytest where database_name = 'DW_HQ_ERP_SC' and table_name = 'ADRC' and column_name = 'STR_SUPPL3' and data_type like '%V'
3.覆盖索引
    所查询的列都是索引中的列。这样即使like中的%在前面，也是会用到索引的
    Explain select database_name,table_name,column_name,data_type	from mytest where database_name = 'DW_HQ_ERP_SC' and table_name = 'ADRC' and column_name = 'STR_SUPPL3' and data_type like '%V'
1
2
3
4
5
6
7
8

1.%放在后面

2.%放在前面


9.字符型数据要加引号