mysql中sql优化学习笔记

EXPLAIN 字段解析

列字段 含义
id 查询序号
select_type 查询类型
table 表名
type join类型
partitions 匹配的分区
possible_keys 可能选择的索引
key 实际选择的索引
key_len 索引长度
ref 与索引作比较的列
rows 大概需要检索的行数
filtered 按表条件过滤的行百分比
Extra 附加信息

ID 查询序号

一个sql中有多少个select就有多少个id(如果有子查询和关联查询，那么就会有多个id)，id值越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。

SELECT_TYPE 查询类型

Mysql的查询类型有：简单查询、复杂查询

1. SIMPLE（简单查询）
2. PRIMARY（复杂查询的最外层查询）
3. SUBQUERY（复杂查询的子查询，出现在select或者where的子句中），
4. DERIVED（复杂查询的FROM子句中的查询标记为DERIVED，MySql会将结果放在一个临时表中、也称为派生表）
5. UNION（复杂查询中出现在UNION后面的查询标记为UNION）

TABLE 表名

这一列表示解释的一行正在访问哪个表

TYPE join类型

这一列表示关联类型或访问类型。

最优到最差分别为：system > const > eq_ref > ref > range > index > ALL，一般来说，得保证查询达到range级别，最好达到ref。当这一列为null，原因是mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。

const,system: 用于primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。system是const的特例，表中只有一行数据或者是空表,且只能用于myisam和memory表。

eq_ref: primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。

ref： 相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。

range： 索引范围扫描，通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。

index： 扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，在extra中显示Using index，性能比All稍好一些。

ALL： 即全表扫描，扫描你的聚簇索引的所有叶子节点，性能最差。

PARTITIONS 匹配的分区

如果查询是基于分区表的话，会显示查询将访问的分区。(需要结合分区表,非分区表该字段为null)

POSSIBLE_KEYS 可能选择的索引

指出 MySQL 能在该表中可能使用的索引，显示的是索引的名称，多个索引用逗号隔开，如果该列是NULL，则没有相关的索引。而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。

KEY 实际选择的索引

这一列显示查询实际用到的索引，如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。

KEY_LEN 索引长度

这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。

char(n)：如果存汉字长度就是 3n 字节
varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为
varchar是变长字符串
tinyint：1字节
smallint：2字节
int：4字节
bigint：8字节
date：3字节
timestamp：4字节
datetime：8字节
如果字段允许为 NULL，需要1字节记录是否为 NULL

索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

比如有个联合索引时，可能不会使用到联合索引的全部字段，只有上了前几个，这时就可以根据key_len字段来计算出到底使用了几个字段。

REF 与索引作比较的列

表示在查询中与key列索引作比较所使用的值是什么类型。

如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func。

ROWS 大概需要检索的行数

这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。没有用上索引的情况下，会全表扫描。rows的值越小越好，说明检索的数据少。

FILTERED 按表条件过滤的行百分比

给出了一个百分比的值，这个百分比值和rows列的值一起使用，可以大概计算出这个查询的结果行数。

EXTRA 附加信息

using index : 出现这个说明mysql使用了覆盖索引，避免访问了表的数据行，效率不错。
using where ：这说明服务器在存储引擎收到行后讲进行过滤。
using temporary ：这意味着mysql对查询结果进行排序的时候使用了一张临时表
using filesort ：这个说明mysql会对数据使用一个外部的索引排序
注意当出现using temporary 和 using filesort时候说明需要优化操作。

SHOW PROFILE 解释

使用该命令可以获取一个查询在整个执行过程中各个资源消耗情况，例如 CPU，IO，SWAP……

1.检测当前mysql版本是否支持profile，具体命令如下

select @@have_profiling

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pqYytHc4-1663761059198)(C:\Users\novots\AppData\Roaming\Typora\typora-user-images\image-20220919204029117.png)]

2.如果支持还需要查看一下 profile 是否开启，命令如下：

select @@profiling

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0M3ZDTeC-1663761059198)(C:\Users\novots\AppData\Roaming\Typora\typora-user-images\image-20220919204214430.png)]

如果没有开启，可以通过 set profiling = 1 开启该配置。

测试查看信息

1)执行查询sql

SELECT * FROM `t_student` WHERE id = 15;

2)执行以下以下语句查看profile文件信息

show profiles;

profile语法

show profile [type] for query query_id;

type: {
    ALL                显示所有信息
  | BLOCK IO           显示块输入和输出操作的数量
  | CONTEXT SWITCHES   显示自愿上下文切换和非自愿上下文切换的数量
  | CPU                显示用户和系统的CPU使用时间
  | IPC                显示已发送和已接收消息（messages）的数量
  | MEMORY             -- 尚未生效
  | PAGE FAULTS        显示主要和次要页面错误的数量
  | SOURCE             显示源代码中函数名称以及该函数所在文件的名称和行号
  | SWAPS              显示SWAP数量
}

3)执行以下语句

show profile all for query 9;

行字段的含义：

+---------------------+--------------+------+-----+----------+-------+
| Field               | Type         | Null | Key | Default  | Extra |
+---------------------+--------------+------+-----+----------+-------+
| QUERY_ID            | int(20)      | NO   |     | 0        |       | # 语句ID
| STATE               | varchar(30)  | NO   |     |          |       | # 状态
| DURATION            | decimal(9,6) | NO   |     | 0.000000 |       | # 持续时间，单位s
| CPU_USER            | decimal(9,6) | YES  |     | NULL     |       | # 用户态CPU时间，单位s
| CPU_SYSTEM          | decimal(9,6) | YES  |     | NULL     |       | # 系统态CPU时间，单位s
| CONTEXT_VOLUNTARY   | int(20)      | YES  |     | NULL     |       | # 自愿上下文切换次数
| CONTEXT_INVOLUNTARY | int(20)      | YES  |     | NULL     |       | # 非自愿上下文切换次数
| BLOCK_OPS_IN        | int(20)      | YES  |     | NULL     |       | # 块输入次数
| BLOCK_OPS_OUT       | int(20)      | YES  |     | NULL     |       | # 块输出次数
| MESSAGES_SENT       | int(20)      | YES  |     | NULL     |       | # 发送的消息数量
| MESSAGES_RECEIVED   | int(20)      | YES  |     | NULL     |       | # 接收的消息数量
| PAGE_FAULTS_MAJOR   | int(20)      | YES  |     | NULL     |       | # 主要页面错误数量
| PAGE_FAULTS_MINOR   | int(20)      | YES  |     | NULL     |       | # 次要页面错误数量
| SWAPS               | int(20)      | YES  |     | NULL     |       | # 交换次数
| SOURCE_FUNCTION     | varchar(30)  | YES  |     | NULL     |       | # 源代码函数
| SOURCE_FILE         | varchar(20)  | YES  |     | NULL     |       | # 源代码文件
| SOURCE_LINE         | int(20)      | YES  |     | NULL     |       | # 源代码行数
+---------------------+--------------+------+-----+----------+-------+

列字段的含义：

* Sending data (最重要的一个过程★★★★★)
  线程正在读取和处理一条SELECT语句的行，并且将数据发送至客户端。由于在此期间会执行大量的磁盘访问（读操作），
  这个状态在一个指定查询的生命周期中经常是耗时最长的。
  这个字段才是SQL真正运行采集+相应数据的时间，而非executing；
  
  --以下按照首字母顺序依次排序
  
* After create
  这个状态当线程创建一个表（包括内部临时表）时，在这个建表功能结束时出现。即使某些错误导致建表失败，也会使用这个状态。

* Analyzing
  当计算MyISAM表索引分布时。（比如进行ANALYZE TABLE时）

* checking permissions
  这个线程检查服务器是否有具有执行该语句的所需权限。

* Checking table
  线程正在执行表检查操作。

* cleaning up
  线程处理一个命令，并正准备释放内存和重置某些状态变量。

* closing tables
  线程正在将变更的表中的数据刷新到磁盘上并正在关闭使用过的表。这应该是一个快速的操作。如果不是这样的话
  则应该检查硬盘空间是否已满或者硬盘IO是否达到瓶颈。 

* converting HEAP to MyISAM
  线程将一个内部临时表转换为磁盘上的MyISAM表。

* copy to tmp table 
  线程正在处理一个ALTER TABLE语句。这个状态发生在新的表结构已经创建之后，但是在数据被复制进入之前。

* Copying to group table
  如果一个语句有不同的ORDER BY和GROUP BY条件，数据会被复制到一个临时表中并且按组排序。

* Copying to tmp table
  线程将数据写入内存中的临时表。  正在创建临时表以存放部分查询结果

* Copying to tmp table on disk
  线程正在将数据写入磁盘中的临时表。临时表的结果集过大。所以线程将临时表由基于内存模式改为基于磁盘模式，以节省内存。
  但是这个过程会异常的缓慢！！

* Creating index
  线程正在对一个MyISAM表执行ALTER TABLE ... ENABLE KEYS语句。

* Creating sort index
  线程正在使用内部临时表处理一个SELECT 操作。

* creating table
  线程正在创建一个表，包括创建临时表。

* Creating tmp table
  线程正在创建一个临时表在内存或者磁盘上。
  如果这个表创建在内存上但是之后被转换到磁盘上，这个状态在运行Copying to tmp table on disk 的时候保持。

* deleting from main table
  线程正在执行多表删除的第一部分，只从第一个表中删除。并且保存列和偏移量用来从其他（参考）表删除。

* deleting from reference tables
  线程正在执行多表删除的第二部分，并从其他表中删除匹配的行。

* discard_or_import_tablespace
  线程正在执行ALTER TABLE ... DISCARD TABLESPACE 或 ALTER TABLE ... IMPORT TABLESPACE语句。

* end
  这个状态出现在结束时，但是在对ALTER TABLE, CREATE VIEW, DELETE, INSERT, SELECT,   或者 UPDATE 语句进行清理之前。

* executing
  该线程已开始执行一条语句。

* Execution of init_command
  线程正在执行处于init_command系统变量的值中的语句。

* freeing items
* 线程已经执行了命令。在这个状态中涉及的查询缓存可以得到一些释放。这个状态通常后面跟随cleaning up状态。

* Flushing tables
  线程正在执行FLUSH TABLES 并且等待所有线程关闭他们的表。

* FULLTEXT initialization
  服务器正在准备进行自然语言全文检索。

* init
  这个状态出现在线程初始化ALTER TABLE, DELETE, INSERT, SELECT, 或 UPDATE语句之前。
  服务器在这种状态下进行的操作，包括：刷新全日志、Innodb日志，和一些查询缓存清理操作。

* Killed
  程序对线程发送了KILL语句，并且它应该放弃下一次对KILL标记的检查。
  这个标记在每一个MySQL的主要循环中被检查，但在某些情况下，它可能需要令线程在很短的时间内死亡。
  如果这个线程被其他线程锁住了，这个KILL操作会在其他线程释放锁的瞬时执行。


* logging slow query
  这个线程正在将语句写入慢查询日志。

* NULL
  没有操作的状态。

* login
  线程连接的初始状态。直到客户端已经成功验证。

* manage keys
  服务器启用或禁用表索引。

* Opening tables, Opening table
  线程正试图打开一张表

* optimizing
  服务器执行查询的初步优化。

* preparing
 在查询优化过程中出现这个状态。

* Purging old relay logs
  线程正在移除不必要的中继日志文件。

* query end
  这个状态出现在处理一个查询之后，但是在freeing items状态之前。

* Reading from net
  服务器正在从网络阅读数据包。

* Removing duplicates
  查询正在使用SELECT DISTINCT，这种情况下MySQL不能在早期阶段优化掉一些distinct操作。
  因此，MySQL需要一个额外的阶段，在将结果发送到客户端之前删除所有重复的行。

* removing tmp table
  线程正在移除一个内置临时表，在执行一条SELECT语句之后。 如果没有临时表产生，那么这个状态不被使用。

* rename
* 线程正在重命名一张表。

* rename result table
  线程正在处理ALTER TABLE语句，创建新的表，并且重命名它来代替原有的表。

* Reopen tables
  线程获得了表锁，但是在取得表锁之后才发现该表的底层结构已经发生了变化。线程释放这个锁，关闭表，并试图重新打开该表。

* Repair by sorting
  修复代码正在使用一个分类来创建索引。

* Repair done
  线程完成一个多线程的MyISAM表的修复。

* Repair with keycache
  修复代码正在通过索引缓存一个接一个地使用创建索引。这比通过分类修复要慢很多。

* Rolling back
  线程正在回滚一个事务

* Searching rows for update
  线程正在进行第一阶段，在更新前寻找所有匹配的行。如果update正在更改用于查找相关行的索引，则必须这么做。

* setup
  线程正开始进行一个ALTER TABLE操作。

* Sorting for group
  线程正在执行一个由GROUP BY指定的排序。

* Sorting for order
  线程正在执行一个由ORDER BY指定的排序。

* Sorting index
  线程正在对索引页进行排序，为了对MyISAM表进行操作时获得更优的性能。

* Sorting result
 对于一个SELECT语句，这与创建排序索引相似，但是是对非临时表。

* statistics
  服务器计算统计去规划一个查询。如果一个线程长时间处于这个状态，这个服务器的磁盘可能在执行其他工作。

* System lock
 这个线程正在请求或者等待一个内部的或外部的系统表锁。如果这个状态是由于外部锁的请求产生的，并且你没有使用多个正在访问相同的表的mysql服务器
 
* Waiting for table level lock
  系统锁定后的下一个线程状态。线程已获得外部锁并且将请求内部表锁。

* Updating
  线程寻找更新匹配的行并进行更新。

* updating main table
  线程正在执行多表更新的第一部分，只从第一个表中更新。并且保存列和偏移量用来从其他（参考）表更新。

* updating reference tables
  线程正在执行多表更新的第二部分，并从其他表中更新匹配的行。

* User lock
  线程正在请求或等待一个GET_LOCK()调用所要求的咨询锁。对于SHOW PROFILE，这个状态意味这线程正在请求锁。（而非等待）

* User sleep
  线程调用了一个SLEEP()。

* Waiting for commit lock
  一个显式或隐式语句在提交时等待释放读锁

* Waiting for global read lock
  等待全局读锁。

* Waiting for release of readlock
  等待释放读锁。

* Waiting for tables, Waiting for table, Waiting for table flush
  线程获得一个通知，底层表结构已经发生变化，它需要重新打开表来获取新的结构。然而，重新打开表，它必须等到所有其他线程关闭这个有问题的表。
  这个通知产生通常因为另一个线程对问题表执行了FLUSH TABLES或者以下语句之一：
  FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE.

* Waiting for lock_type lock
  等待各个种类的表锁。

* Waiting on cond
 一个普通的状态，线程正在等待一个条件为真。没有特定的状态信息可用。

* Writing to net
  服务器正在写一个网络数据包。

SQL执行过程中可能导致时间慢的原因

1. Sending data (最重要的一个过程★★★★★)
  线程正在读取和处理一条SELECT语句的行，并且将数据发送至客户端。由于在此期间会执行大量
  的磁盘访问（读操作），这个状态在一个指定查询的生命周期中经常是耗时最长的。
  
  对于一个普通查询来说，这个参数过大可分为两种情况
  1. 第一种是SQL本身，比如没有建立正确的索引，索引失效等等情况，这种数据体现在CPU_user 和CPU_sysyem字段 时间过长；
  2. 第二种是相应数据量过大，导致CPU调度时上下文频繁切换。这种数据体现在CONTEXT_INVOLUNTARY和CONTEXT_VOLUNTARY字段 时间过长;
     像：外网使用Navicat连接到远程数据库中。查询一个普通的SQL，在本地MySQL执行速度很快，但是使用远程服务器的MySQL就异常的缓慢。
  这时若查询profile详情，就会发现大量相应数据传输IO导致频繁的上下文切换消耗了大量的时间。
 
2. converting HEAP to MyISAM
   原译指的是：线程将一个内部临时表转换为磁盘上的MyISAM表。
   我们实际操作中可能出现的问题就是查询结果太大了导致内存不够，往磁盘上搬。

3.Creating tmp table
  创建了临时表

4.Coping to tmp table on disk
  把内存中临时表复制到磁盘

5.locked
  加锁

------------------------------------------------ 

2，4 可以修改一下tmp_table_size和max_heap_table_size两个参数来调整

show profile 是一个即将被废弃掉的命令,

官方已经推荐使用performance_schema.profiling表来查看SQL执行情况了。

SELECT * from information_schema.profiling where query_id = #{query_id};

SLOW_QUERY_LOG 慢日志查询

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10。

通过慢查询日志，可以查找出哪些查询语句的执行效率低，以便进行优化。

如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。

1.永久开启

配置文件开启

[mysqld]
show_query_log=on/off  # 开启或关闭
log-slow-queries=dir/filename
long_query_time=n
log_output=file #储存方式

其中：
dir 参数指定慢查询日志的存储路径，如果不指定存储路径，慢查询日志将默认存储到 MySQL 数据库的数据文件夹下。
filename 参数指定日志的文件名，生成日志文件的完整名称为 filename-slow.log。 如果不指定文件名，默认文件名为 hostname-slow.log，hostname 是 MySQL 服务器的主机名。
n 参数是设定的时间值，该值的单位是秒。如果不设置long_query_time选项，默认时间为10秒。

2.临时开启

SET GLOBAL slow_query_log=on/off; # 开启或关闭
# 全局和当前session都要修改
SET GLOBAL long_query_time=1;
SET long_query_time=1;
#设置为file,慢查询日志是通过file体现的,默认是none,可以设置为table或者file,如果是table则慢查询信息会保存到mysql库下的slow_log表中
set GLOBAL log_output = file;

注意：mysql重启之后，临时开启则失效

3.查询慢查询日志的开启状态和储存的位置**

show variables like '%quer%';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Eh4Pgwde-1663761059200)(https://raw.githubusercontent.com/saypig/images/master/blogImg/20180827221503186)]

参数说明:

slow_query_log : 是否已经开启慢查询

slow_query_log_file : 慢查询日志文件路径

long_query_time : 超过多少秒的查询就写入日志

log_queries_not_using_indexes : 如果值设置为ON，则会记录所有没有利用索引的查询(性能优化时开启此项,平时不要开启)

4.慢日志分析工具

mysqldumpslow是mysql官方提供的慢日志分析工具，可以获取sql的执行情况。

mysqldumpslow -t 10  /data/mysql/mysql-slow.log  #显示出慢查询日志中最慢的10条sql

DRUID监控慢SQL

1.导入依赖包

<!--引入sql驱动依赖-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>
<!--引入mybatis-plus依赖-->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.5.2</version>
</dependency>
<!--引入druid连接池依赖-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.2.8</version>
</dependency>

2.配置sql监控

spring.datasource.url=jdbc:mysql://124.12.75.52:3306/demo_db?useUnicode=true&&characterEncoding=utf-8&&useSSL=false
spring.datasource.username=root
spring.datasource.password=root@1234
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

# 默认true 内置监控页面首页/druid/index.html
spring.datasource.druid.stat-view-servlet.enabled=true
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
#允许清空统计数据
spring.datasource.druid.stat-view-servlet.reset-enable=true
#这里为登录页面账号密码配置
spring.datasource.druid.stat-view-servlet.login-username=root
spring.datasource.druid.stat-view-servlet.login-password=123456
# IP白名单 多个逗号分隔
spring.datasource.druid.stat-view-servlet.allow=
spring.datasource.druid.stat-view-servlet.deny=
#开启监控sql
spring.datasource.druid.filter.stat.enabled=true
spring.datasource.druid.filter.stat.log-slow-sql=true
#显示并标注慢sql 默认当超过3秒显示
spring.datasource.druid.filter.stat.slow-sql-millis=3000
spring.datasource.druid.filter.stat.merge-sql=true
spring.datasource.druid.filter.wall.config.multi-statement-allow=true
mybatis-plus.mapper-locations=classpath:/mapper/*.xml
mybatis-plus.typeAliasesPackage=com.springboot.cli.domain
mybatis-plus.global-config.db-config.id-type=AUTO
mybatis-plus.global-config.db-config.logic-delete-value=-1
mybatis-plus.global-config.db-config.logic-not-delete-value=0
mybatis-plus.global-config.banner=false
mybatis-plus.configuration.map-underscore-to-camel-case=true
mybatis-plus.configuration.cache-enabled=false
mybatis-plus.configuration.jdbc-type-for-null=null

3.测试效果

1）启动项目

2）http://localhost:8080/druid/index.html druid监控界面地址 账号密码 root/123456