MySQL 09_MySQL性能分析及调优

一、MySQL性能分析及调优简介

1.1 数据库服务调优的目标

尽可能节省系统资源，以便系统可以提供更大负荷的服务（吞吐量更大）；合理的结构设计和参数调整，以提高用户操响应的速度（响应速度更快）；减少系统的瓶颈，提高MySQL数据库整体的性能；

1.2 数据库服务器的优化维度和步骤

如何定位调优问题：

用户的反馈（主要）；
日志分析（主要）；
服务器资源使用监控；
数据库内部状况监控；
其它；

调优的维度和步骤：

选择适合的 DBMS；
优化表设计；
优化逻辑查询；
优化物理查询；

物理查询优化是在确定了逻辑查询优化之后，采用物理优化技术（比如索引等），通过计算代价模型对各种可能的访问路径进行估算，从而找到执行方式中代价最小的作为执行计划。（重点掌握索引的创建和使用）

数据库服务器的优化步骤：可分成了观察（Show status）和行动（Action）两部分，字母S的部分代表观察（会使用相应的分析工具），字母A代表的部分是行动（对应分析可以采取的行动）：

通过观察了解数据库整体的运行状态，通过性能分析工具可以了解执行慢的SQL都有哪些，查看具体的SQL执行计划，甚至是SQL执行中的每一步的成本代价，这样才能定位问题所在，找到问题，再采取相应的行动。

调优流程图详解：

首先在 S1 部分，我们需要观察服务器的状态是否存在周期性的波动。如果存在周期性波动，有可能是周期性节点的原因，如双十一，促销活动等。这样的话，可以通过 A1 加缓存这一步骤解决，或者更改缓存失效策略。
如果缓存策略没有解决，或者不是周期性波动的原因，就需要进一步分析查询延迟和卡顿的原因。进入开启慢查询 S2 步骤，通过慢查询可用快速定位执行慢的SQL语句。我们可以通过设置 long_query_time 参数定义“慢”的阈值（阈值即边界值），如果SQL执行时间超过了阈值，则认为是慢查询。收集上来这些慢查询的SQL语句之后，我们就可以通过分析工具对慢查询日志进行分析。
在S3这一步骤中，我们就知道了执行慢的sQL，这样就可以针对性地用EXPLAIN查看对应SQL语句的执行计划，或者使用show profile查看sQL中每一个步骤的时间成本。这样我们就可以了解SQL查询慢是因为执行时间长，还是等待时间长。
如果是SQL等待时间长，就进入 A2 步骤。进行调优服务器的参数，比如适当增加数据库缓冲池等。如果是SQL执行时间长，就进入 A3 步骤。可以从三个方面进行优化调整：
- 索引设计优化。比如联合索引就比多个单个索引的查询效率要快一些。
- Join表是否过多。Join表的数据最好不要超过三张，表的数据越多，嵌套循环就越多，查询时间也就越长。
- 数据表设计优化。虽然一般设计数据表都遵循三范式，但是我们可以适当的增加数据冗余度，以空间换取时间提高数据的查询效率。
如果 A2 和 A3 都不能解决问题，就要考虑数据库自身的SQL查询性能是否已经达到了瓶颈，如果确认没有达到性能瓶颈，就需要重新检查也就是重复上述步骤。如果已经达到了性能瓶颈，进入 A4 阶段，需要考虑增加服务器，采用读写分离的架构，或者考虑对数据库进行分库分表，比如垂直分库，垂直分表和水平分表等。

以上就是数据库调优的流程思路。如果我们发现执行sQL时存在不规则延迟或卡顿的时候，就可以采用分析工具帮我们定位有问题的sQL，这三种分析工具你可以理解是sQL调优的三个步骤:慢查询、EXPLAIN和SHoWPROFILING。

1.3 查看MySQL系统性能参数

在MySQL中，可以使用SHOW STATUS语句查询一些MySQL数据库服务器的性能参数、执行频率。

SHOW STATUS语句语法如下：

1

SHOW [GLOBAL|SESSION] STATUS LIKE '参数';

一些常用的性能参数如下:

Connections：连接MySQL服务器的次数；
Uptime：MySQL服务器的上线时间；
Slow_queries：慢查询的次；
lnnodb_rows_read：select查询返回的行数；
lnnodb_rows_inserted：执行INSERT操作插入的行数；
Innodb_rows_updated：执行UPDATE操作更新的行数；
lnnodb_rows_deleted：执行DELETE操作删除的行数；
com_select：查询操作的次数；
Com_insert：插入操作的次数；对于批量插入的INSERT操作，只累加一次；
Com_update：更新操作的次数；
Com_delete：删除操作的次数；
innodb_buffer_pool_size：这个参数是Mysql数据库最重要的参数之一，表示InnoDB类型的表和索引的最大缓存。它不仅仅缓存索引数据，还会缓存表的数据。这个值越大，查询的速度就会越快。但是这个值太大会影响操作系统的性能。
key_buffer_size：表示索引缓冲区的大小。索引缓冲区是所有的线程共享。增加索引缓冲区可以得到更好处理的索引（对所有读和多重写）。当然，这个值不是越大越好，它的大小取决于内存的大小。如果这个值太大，就会导致操作系统频繁换页，也会降低系统性能。对于内存在4GB左右的服务器该参数可设置为256M或384M。
table_cache：表示同时打开的表的个数。这个值越大，能够同时打开的表的个数越多。物理内存越大，设置就越大。默认为2402，调到512-1024最佳。这个值不是越大越好，因为同时打开的表太多会影响操作系统的性能。
query_cache_size：表示查询缓冲区的大小。可以通过在MySQL控制台观察：
- 如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况，就要增加Query_cache_size的值；
- 如果Qcache_hits的值非常大，则表明查询缓冲使用非常频繁，如果该值较小反而会影响效率，那么可以考虑不用查询缓存；
- Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多。MySQL8.0之后失效。该参数需要和query_cache_type配合使用。
query_cache_type 的值是0时，所有的查询都不使用查询缓存区，但是query_cache_type=0并不会导致MySQL释放query_cache_size所配置的缓存区内存。
- 当query_cache_type=1时，所有的查询都将使用查询缓存区，除非在查询语句中指定SQL_NO_CACHE，如SELECT SQL_NO_CACHE * FROM tbl_name。
- 当query_cache_type=2时，只有在查询语句中使用SQL_CACHE关键字，查询才会使用查询缓存区。使用查询缓存区可以提高查询的速度，这种方式只适用于修改操作少且经常执行相同的查询操作的情况。
sort_buffer_size：表示每个需要进行排序的线程分配的缓冲区的大小。增加这个参数的值可以提高ORDER BY或GROUP BY操作的速度。默认数值是2 097 144字节（约2MB）。对于内存在4GB左右的服务器推荐设置为6-8M，如果有100个连接，那么实际分配的总共排序缓冲区大小为100 × 6 ＝ 600MB。
join_buffer_size = 8M：表示联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。
read_buffer_size：表示每个线程连续扫描时为扫描的每个表分配的缓冲区的大小（字节）。当线程从表中连续读取记录时需要用到这个缓冲区。SET SESSION read_buffer_size=n可以临时设置该参数的值。默认为64K，可以设置为4M。
innodb_flush_log_at_trx_commit：表示何时将缓冲区的数据写入日志文件，并且将日志文件写入磁盘中。该参数对于innoDB引擎非常重要。该参数有3个值，分别为0、1和2。该参数的默认值为1。
- 值为0时，表示每秒1次的频率将数据写入日志文件并将日志文件写入磁盘。每个事务的commit并不会触发前面的任何操作。该模式速度最快，但不太安全，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。
- 值为1时，表示每次提交事务时将数据写入日志文件并将日志文件写入磁盘进行同步。该模式是最安全的，但也是最慢的一种方式。因为每次事务提交或事务外的指令都需要把日志写入（flush）硬盘。
- 值为2时，表示每次提交事务时将数据写入日志文件，每隔1秒将日志文件写入磁盘。该模式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。
innodb_log_buffer_size：这是 InnoDB 存储引擎的事务日志所使用的缓冲区。为了提高性能，也是先将信息写入 Innodb Log Buffer 中，当满足 innodb_flush_log_trx_commit 参数所设置的相应条件（或者日志缓冲区写满）之后，才会将日志写到文件（或者同步到磁盘）中。
max_connections：表示允许连接到MySQL数据库的最大数量，默认值是 151 。如果状态变量connection_errors_max_connections 不为零，并且一直增长，则说明不断有连接请求因数据库连接数已达到允许最大值而失败，这是可以考虑增大max_connections 的值。在Linux 平台下，性能好的服务器，支持 500-1000 个连接不是难事，需要根据服务器性能进行评估设定。这个连接数不是越大越好，因为这些连接会浪费内存的资源。过多的连接可能会导致MySQL服务器僵死。
back_log：用于控制MySQL监听TCP端口时设置的积压请求栈大小。如果MySql的连接数达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源，将会报错。5.6.6 版本之前默认值为 50 ，之后的版本默认为 50 + （max_connections / 5），对于Linux系统推荐设置为小于512的整数，但最大不超过900。如果需要数据库在较短的时间内处理大量连接请求，可以考虑适当增大back_log 的值。
thread_cache_size：线程池缓存线程数量的大小，当客户端断开连接后将当前线程缓存起来，当在接到新的连接请求时快速响应无需创建新的线程。这尤其对那些使用短连接的应用程序来说可以极大的提高创建连接的效率。那么为了提高性能可以增大该参数的值。默认为60，可以设置为120。
wait_timeout：指定一个请求的最大连接时间，对于4GB左右内存的服务器可以设置为5-10。
interactive_timeout：表示服务器在关闭连接前等待行动的秒数。

1.4 统计SQL的查询成本：last_query_cost

1

SHOW STATUS LIKE 'last_query_cost';

使用场景：它对于比较开销是非常有用的，特别是有好几种查询方式可选的时候。

SQL 查询是一个动态的过程，从页加载的角度来看，可以得到以下两点结论：

位置决定效率，如果页就在数据库缓冲池中，那么效率是最高的，否则还需要从内存或者磁盘中进行读取，当然针对单个页的读取来说，如果页存在于内存中，会比在磁盘中读取效率高很多；

批量决定效率，如果从磁盘中对单一页进行随机读，那么效率是很低的（差不多10ms），而采用顺序读取的方式，批量对页进行读取，平均一页的读取效率就会提升很多，甚至要快于单个页面在内存中的随机读取；

所以说，遇到I/O并不用担心，方法找对了，效率还是很高的，首先要考虑数据存放的位置，如果是经常使用的数据就要尽量放到缓冲池中，其次可以充分利用磁盘的吞吐能力，一次性批量读取数据，这样单个页的读取效率也就得到了提升。

二、定位执行慢的SQL：慢查询日志

2.1 MySQL慢查询日志简介

MySQL的 慢查询日志，用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过 long_query_time 的值的SQL，则会被记录到慢查询日志中，long_query_time 的默认值为10，意思是运行10秒以上（不含10秒）的语句，认为是超出了最大忍耐时间值。

默认情况下，MySQL数据库没有开启慢查询日志，需要手动来设置这个参数，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。

2.2 慢查询日志相关参数介绍

开启slow_query_log：

1

set global slow_query_log='ON';

查看下慢查询日志是否开启，以及慢查询日志文件的位置：

1
2


show variables like `%slow_query_log%`;
SHOW VARIABLES LIKE '%slow%' ;     # 查询慢查询日志所在目录

修改long_query_time 阈值:

1
2
3
4
5
6


# 测试发现：设置global的方式对当前session的long_query_time失效，对新连接的客户端有效，所以可以一并 执行下述语句
mysql> set global long_query_time=1;  # 对当前session的long_query_time 无效，对新连接的客户端有效
mysql> show global variables like '%long_query_time%'; 

mysql> set long_query_time=1;   # # 对当前session的long_query_time 有效
mysql> show variables like '%long_query_time%';

查看慢查询数目：

1

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

除了上述变量，控制慢查询日志的还有一个系统变量: min_examined_row_limit，这个变量的意思是，查询扫描过的最少记录数。

这个变量和查询执行时间，共同组成了判别一个查询是否是慢查询的条件，如果查询扫描过的记录数大于等于这个变量的值，并且查询执行时间超过 long_query_time 的值，那么，这个查询就被记录到慢查询日志中; 反之，则不被记录到慢查询日志中。

1
2
3
4
5
6
7


mysql> show variables like 'min%';
+-------------------------+-------+
| Variable_namel          | Value |
+-------------------------+-------+
| min_examined_row_limit  | 0     |
+-------------------------+-------+
1 row in set,1 warning ( 0.00 sec)

min_examined_row_limit 的值默认是0，与 long_query_time=10 合在一起，表示只要查询的执行时间超过10秒，哪怕一个记录也没有扫描过，都要被记录到慢查询日志中。

也可以根据需要，通过修改 my.ini 文件，来修改查询时长，或者通过SET指令，用SQL语句修改 min_examined_row_limit 的值。

慢查询日志分析工具：mysqldumpslow 在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow:

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


# 查看mysqldumpslow的帮助信息
mysqldumpslow --help

#得到返回记录集最多的10个SQL 
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log 

#得到访问次数最多的10个SQL 
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log

#得到按照时间排序的前10条里面含有左连接的查询语句 
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log 

#另外建议在使用这些命令时结合 | 和more 使用，否则有可能出现爆屏情况 
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

关闭慢查询日志 方式1：永久性方式

1
2
3
4
5


[mysqld] 
slow_query_log=OFF
# 或
[mysqld] 
# slow_query_log=ON

方式2：临时性方式

1

SET GLOBAL slow_query_log=off;

删除慢查询日志: 使用SHOW语句显示慢查询日志信息，具体SQL语句如下:

1

SHow VARIABLES LIKE 'slow_query_log%';

从执行结果可以看出，慢查询日志的目录默认为MySQL的数据目录，在该目录下手动删除慢查询日志文件即可。

使用命令mysqladmin flush-logs 来重新生成查询日志文件，具体命令如下，执行完毕会在数据目录下重新生成慢查询日志文件:

1

mysqladmin -uroot -p flush-logs slow

Tips: 慢查询日志都是使用mysqladmin flush-logs命令来删除重建的。使用时一定要注意，一旦执行了这个命令，慢查询日志都只存在新的日志文件中，如果需要旧的查询日志，就必须事先备份。

三、查看SQL执行成本：SHOW PROFILE

3.1 SHOW PROFILE使用简介

Show Profile 是MysQL提供的可以用来分析当前会话中SQL都做了什么、执行的资源消耗情况的工具，可用于sql调优的测量，默认情况下处于关闭状态，并保存最近15次的运行结果。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23


show variables like 'profiling';
# 开启
set profiling = 'ON';

# 查看
SHOW PROFILE [type [ , type ] ... ]
	[ FOR QUERY n ]
	[LIMIT row_count [ OFFSET offset ] ]

type : {
	ALL -- 显示所有参数的开销信息
	BLOCK IO -- 显示IO的相关开销
	CONTEXT SWITCHES -- 上下文切换相关开销
	CPU -- 显示CPU相关开销信息
	IPC -- 显示发送和接收相关开销信息
	MEMORY -- 显示内存相关开销信息
	PAGE FAULTS -- 显示页面错误相关开销信息
	SOURCE -- 显示和Source_function, Source_file,Source_line相关的开销信息
	SWAPS -- 显示交换次数相关的开销信息
}

show profiles;
show profile cpu,block io for query 2;

日常开发需注意的结论:

converting HEAP to MyISAM︰查询结果太大，内存不够，数据往磁盘上搬了。
creating tmp table:创建临时表。先拷贝数据到临时表，用完后再删除临时表。
Copying to tmp table on disk:把内存中临时表复制到磁盘上，警惕!
locked 。

如果在show profile诊断结果中出现了以上4条结果中的任何一条，则sql语句需要优化。

注意：不过 SHOW PROFILE命令将被弃用，可以从information_schema 中的profiling数据表进行查看。

四、分析查询语句：EXPLAIN

4.1 EXPLAIN

定位了查询慢的SQL之后，就可以使用 EXPLAIN 或 DESCRIBE 工具做针对性的分析查询语句。DESCRIBE 语句的使用方法与 EXPLAIN 语句是一样的，并且分析结果也是一样的。

MySQL中有专门负责优化SELECT语句的优化器模块，主要功能:通过计算分析系统中收集到的统计信息，为客户端请求的Query提供它认为最优的执行计划（它认为最优的数据检索方式，但不见得是DBA认为是最优的，这部分最耗费时间)。

这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执行查询等等。

MySQL提供了 EXPLAIN语句 来帮助查看某个查询语句的具体执行计划，看懂 EXPLAIN语句 的各个输出项，可以有针对性的提升我们查询语句的性能。

Tips1:

MySQL 5.6.3 以前只能EXPLAIN SELECT；

MYSQL 5.6.3 以后就可以 EXPLAIN SELECT，UPDATE，DELETE;

Tips2:

MySQL 5.7以前的版本中，想要显示partitions需要使用explain partitions命令；想要显示 filtered 需要使用explain extended命令。

MySQL 5.7版本后，默认explain直接显示partitions和filtered中的信息。

Tips2:

EXPLAIN不考虑各种cache

EXPLAIN不能显示MySQL在执行查询时所作的优化工作

EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定文函数对查询的影响情况

部分统计信息是估算的，并非精确值

4.2 EXPLAIN的基本语法

1
2
3


EXPLAIN SELECT select_options 
# 或者
DESCRIBE SELECT select_options

EXPLAIN 语句输出的各个列的作用如下：

列名	描述
id	在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
select_type	SELECT关键字对应的那个查询的类型
table	表名
partitions	匹配的分区信息
type	针对单表的访问方法
possible_keys	可能用到的索引
key	实际上使用的索引
key_len	实际使用到的索引长度
ref	当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows	预估的需要读取的记录条数
filtered	某个表经过搜索条件过滤后剩余记录条数的百分比
Extra	一些额外的信息

table: 不论查询语句有多复杂，包含了多少个表，到最后也是需要对每个表进行单表访问的，所以MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名（有时不是真实的表名字，可能是简称）。

id: 在一个大的查询语句中每个SELECT关键字都对应一个 唯一的id

id如果相同，可以认为是一组，从上往下顺序执行
在所有组中，id值越大，优先级越高，越先执行
关注点：id的每个号码，表示一趟独立的查询,一个sql的查询趟数越少越好

select_type：一条大的查询语句里边可以包含若干个SELECT关键字，每个SELECT天键字代表着一个小的查询语句，而每个SELECT关键字的FROM子句中都可以包含若干张表(这些表用来做连接查询)，每一张表都对应着执行计划输出中的一条记录，对于在同一个SELECT关键字中的表来说，它们的id值是相同的。

MySQL为每一个SELECT关键字代表的小查询都定义了一个称之为select.type的属性，意思是只要知道了某个小查询的select_type 属性，就知道了这个小查询在整个大查询中扮演了一个什么角色，看一下select_type都能取哪些值，请看官方文档:

名称	描述
SIMPLE	Simple SELECT (not using UNION or subqueries)
PRIMARY	Outermost SELECT
UNION	Second or later SELECT statement in a UNION
UNION RESULT	Result of a UNION
SUBQUERY	First SELECT in subquery
DEPENDENT SUBQUERY	First SELECT in subquery, dependent on outer query
DEPENDENT UNION	Second or later SELECT statement in a UNION, dependent on outer query
DERIVED	Derived table
MATERIALIZED	Materialized subquery
UNCACHEABLE SUBQUERY	Asubquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNION	The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)

partitions: 代表分区表中的命中情况，非分区表，该项为NULL，一般情况下查询语句的执行计划的partitions列的值都是NULL。

type（重点）：执行计划的一条记录就代表着MySQL对某个表的执行查询时的访问方法，又称 访问类型，其中的type列就代表了这个访问方法是什么，是较为重要的一个指标。比如，看到type列的值是 ref，表明MySQL即将使用 ref访问方法来执行对s1表的查询。

完整的访问方法如下: system、const、eq.ref、ref、fulltext、ref_or_null、index_merge、unique_subquery、index_subquery、range、index、ALL。

结果值从最好到最坏依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

Tips:SQL性能优化的目标：至少要达到 range级别，要求是ref级别，最好是const级别。（阿里巴巴开发手册要求）

possible_keys和key：在EXPLAIN语句输出的执行计划中, possible_keys列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些。

一般查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。

key列表示实际用到的索引有哪些，如果为NULL，则没有使用索引。

key_len（重点）： key_len的长度计算公式：

1
2
3
4
5
6
7


varchar(10)变长字段且允许NULL = 10 * ( character set： utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段) 

varchar(10)变长字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+2(变长字段)

char(10)固定字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL) 

char(10)固定字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)

ref: 显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是 const、eq_ref、ref、ref_of_null、unique_subquery、index_subquery其中之一时，ref 列展示的就是与索引列作等值匹配的结构是什么，比如只是一个常数或者是某个列。

rows（重点）: 预估的需要读取的记录条数，值越小越好。

filtered：某个表经过搜索条件过滤后剩余记录条数的百分比。

对个单表查询来说，这个filtered列的值没有什么意义，更关注连接查询。

Extra： Extra列是用来说明一些额外信息的，包含不适合在其它列中显示但十分重要的额外信息，可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句。额外信息有如下种类：

No tables used ——当查询语句没有 from 子句时将会提示该额外信息
Impossible WHERE ——查询语句的 where 子句永远为 false 时将会提示该额外信息
Using where ——当使用全表扫描来执行对给某个表的查询，并且该语句的 where 子句中有针对该表的搜索条件时，会提示该额外信息；当使用索引访问来执行对某个表的查询，并且该语句的 where 子句中有除了该索引包含的列之外的其它搜索条件时，也会提示该额外信息
No matching min/max row ——当查询列表处有 MIN 或 MAX 聚合函数，但是并没有符合 WHERE 子句中的搜索条件的记录时
Using index ——当查询列表以及搜索条件中只包含某个索引的列，也就是在可以使用覆盖索引的情况下，将提示该信息。比方说下边这个查询中只需要用到 idx_key1 而不需要回表操作。
Using index condition ——有些搜索条件中虽然出现了索引列，但却不能使用到索引。称为索引条件下推。
Using join buffer ——在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为其分配一块名叫 join buffer 的内存块来加快查询速度，也就是我们所讲的 ‘基于块的嵌套循环算法’
Not exists ——当我们使用左（外）连接时，如果 WHERE 子句中包含要求被驱动表的某个列等于 NULL 值的搜索条件，而且那个列又是不允许存储 ‘NULL’ 值的，将提示该额外信息。
Using intersect（…） ——说明准备使用 Intersect 索引合并的方式执行查询，括号中的 … 表示需要进行索引合并的索引名称；Using union（…） ——说明准备使用 Union 索引合并的方式执行查询；Using sort_union（…），说明准备使用 Sort-Union 索引合并的方式执行查询。
Zero limit ——当 limit 子句的参数为 0 时，表示压根儿不打算从表中读取任何记录，将提示该额外信息
Using filesort ——很多情况下排序操作无法使用到索引，只能在内存中（记录较少的时候）或者磁盘中（记录较多的时候）进行排序，MySQL把这种在内存中或者磁盘上进行排序的方式统称为文件排序。如果某个查询需要文件排序的方式执行查询，将提示该信息
Using temporary ——在许多查询执行过程中，MySQL可能会借助临时表来完成一些功能，比如去重、排序之类的，比如我们在执行许多包含 DISTINCT，GROUP BY， UNION` 等子句的查询过程中，如果不能有效利用索引来完成查询，MySQL很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，将提示该信息。

4.3 EXPLAIN四种输出格式

EXPLAIN可以输出四种格式：传统格式、JSON格式、TREE格式 以及 可视化输出，用户可以根据需要选择适用于自己的格式： 1、传统格式（默认）

2、JSON格式

JSON格式：在EXPLAIN单词和真正的查询语句中间加上FORMAT=JSON，用于查看执行成本cost_info：

Column	JSON Name	Meaning
id	select_id	The SELECT identifier
select_type	None	The SELECT type
table	table_name	The table for the output row
partitions	partitions	The matching partitions
type	access_type	The join type
possible_keys	possible_keys	The possible indexes to choose
key	key	The index actually chosen
key_len	key_length	The length of the chosen key
ref	ref	The columns compared to the index
rows	rows	Estimate of rows to be examined
filtered	filtered	Percentage of rows filtered by table condition
Extra	None	Additional information

3、TREE格式

TREE格式是8.0.16版本之后引入的新格式，主要根据查询的各个部分之间的关系和各部分的执行顺序来描述如何查询。

4、可视化输出

可视化输出，可以通过MySQL Workbench可视化查看MySQL的执行计划。

4.4 SHOW WARNINGS的使用

1
2
3
4
5
6
7
8
9


mysql> EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.common_field IS NOT NULL;

# 查看优化后的执行语句
mysql> SHOW WARNINGS\G
***************************1. roW ***************************
  Level : Note
   Code : 1003
Message: /* select#1 */ select 'atguigu'.'s1'.'key1' AS 'key1', 'atguigu'.'s2'.'key1' AS 'key1' from 'atguigu'.'s1' join 'atguigu'.'s2' where (( 'atguigu'.'s1'.'key1' = 'atguigu'.'s2'.'key1') and ( 'atguigu'.'s2'.'common_field' is not null))
1 row in set (8.00 sec)

4.5 分析优化器执行计划：trace

OPTIMIZER_TRACE 是MySQL 5.6引入的一项跟踪功能，它可以跟踪优化器做出的各种决策(比如访问表的方法、各种开销计算、各种转换等)，并将跟踪结果记录到 INFORMATION_SCHEMALI.OPTIMIZER_TRACE 表中。

此功能默认关闭，开启trace，并设置格式为JSON，同时设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示：

1
2
3
4
5
6
7
8


# 开启
SET optimizer_trace="enabled=on",end_markers_in_json=on; 
# 设置大小
set optimizer_trace_max_mem_size=1000000;

# 使用
select * from student where id < 10;
select * from information_schema.optimizer_trace\G

五、MySQL监控分析视图-sys schema

5.1 Sys schema视图摘要简介

关于MySQL的性能监控和问题诊断，一般都从 performance_schema 中去获取想要的数据，在MySQL5.7.7版本中新增sys schema，它将 performance_schernla 和 information_schema 中的数据以更容易理解的方式总结归纳为视图，其目的就是为了降低查询 performance_schema 的复杂度，让DBA能够快速的定位问题。下面看看这些库中都有哪些监控表和视图，掌握了这些，在开发和运维的过程中就起到了事半功倍的效果。

Sys schema视图摘要

主机相关：以host_summary开头，主要汇总了IO延迟的信息。
Innodb相关：以innodb开头，汇总了innodb buffer信息和事务等待innodb锁的信息。
l/o相关：以io开头，汇总了等待I/O、I/o使用量情况。
内存使用情况：以memorv开头，从主机、线程、事件等角度展示内存的使用情况
连接与会话信息：processlist和session相关视图，总结了会话相关信息。
表相关：以schema_table开头的视图，展示了表的统计信息。
索引信息：统计了索引的使用情况，包含冗余索引和未使用的索引情况。
语句相关：以statement开头，包含执行全表扫描、使用临时表、排序等的语句信息。
用户相关：以user开头的视图，统计了用户使用的文件I/o、执行语句统计信息。
等待事件相关信息：以wait开头，展示等待事件的延迟情况。

5.2 Sys schema视图使用场景

索引情况：

1
2
3
4
5
6


#1. 查询冗余索引 
select * from sys.schema_redundant_indexes; 
#2. 查询未使用过的索引 
select * from sys.schema_unused_indexes; 
#3. 查询索引的使用情况 
select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted from sys.schema_index_statistics where table_schema='dbname' ;

表相关:

1
2
3
4
5
6
7


# 1. 查询表的访问量 
select table_schema,table_name,sum(io_read_requests+io_write_requests) as io from sys.schema_table_statistics group by table_schema,table_name order by io desc; 
# 2. 查询占用bufferpool较多的表 
select object_schema,object_name,allocated,data
from sys.innodb_buffer_stats_by_table order by allocated limit 10; 
# 3. 查看表的全表扫描情况 
select * from sys.statements_with_full_table_scans where db='dbname';

语句相关:

1
2
3
4
5
6
7
8


#1. 监控SQL执行的频率 
select db,exec_count,query from sys.statement_analysis order by exec_count desc; 
#2. 监控使用了排序的SQL 
select db,exec_count,first_seen,last_seen,query
from sys.statements_with_sorting limit 1; 
#3. 监控使用了临时表或者磁盘临时表的SQL 
select db,exec_count,tmp_tables,tmp_disk_tables,query
from sys.statement_analysis where tmp_tables>0 or tmp_disk_tables >0 order by (tmp_tables+tmp_disk_tables) desc;

IO相关:

1
2
3


#1. 查看消耗磁盘IO的文件 
select file,avg_read,avg_write,avg_read+avg_write as avg_io
from sys.io_global_by_file_by_bytes order by avg_read limit 10;

Innodb相关:

1
2


#1. 行锁阻塞情况 
select * from sys.innodb_lock_waits;

风险提示:

通过sys库去查询时，MySQL会消耗大量资源去收集相关信息，严重的可能会导致业务请求被阻塞，从而引起故障。建议生产上不要频繁的去查询sys或者performance_schema、information_schema来完成监控、巡检等工作。

六、数据库其它调优策略

6.1 使用 Redis或 Memcached 作为缓存

6.2 库级优化

1）读写分离

如果读和写的业务量都很大，并且它们都在同一个数据库服务器中进行操作，那么数据库的性能就会出现瓶颈，这时为了提升系统的性能，优化用户体验，可以采用读写分离的方式降低主数据库的负载，比如：用主数据库(master)完成写操作，用从数据库(slave）完成读操作。

2）数据分片（分库分表）

对数据库分库分表，当数据量级达到千万级以上时，有时候需要把一个数据库切成多份，放到不同的数据库服务器上，减少对单一数据库服务器的访问压力。

如果使用的是MySQL，就可以使用MySQL自带的分区表功能，当然也可以考虑自己做垂直拆分（分库）、水平拆分（分表)、垂直+水平拆分(分库分表)。

垂直拆分 如果数据库中的数据表过多，可以采用垂直分库的方式，将关联的数据表部署在同一个数据库上。

如果数据表中的列过多，可以采用垂直分表的方式，将一张数据表分拆成多张数据表，把经常一起使用的列放到同一张表里。

水平拆分 尽量控制单表数据宜的大小，建议控制在1000万以内。1000万并不是MsQL数据库的限制，过大会造成修改表结构、备份、恢复都会有很大的问题。此时可以用历史数据归档（应用天日志数据），水平分表（应用于业务数据）等手段来控制数据量大小。

这里主要考虑业务数据的水平分表策略。将大的数据表按照某个属性维度分拆成不同的小表，每张小表保持相同的表结构。比如可以按照年份来划分，把不向年份的数据放到不同的数据表中。

水平分表仅是解决了单一表数据过大的问题，但由于表的数据还是在风一台机器上，其实对于提升MysQL并发能力没有什么意义，所以水平拆分最好分库，从而达到分布式的目的。

水平拆分能够支持非常大的数据量存储，应用端改造也少，但分片事务难以解决，跨节点Join性能较差，逻辑复杂。《Java工程师修炼之道》的作者推荐·尽量不要对数据进行分片，因为拆分会带来逻辑、部署、运维的各种复杂度，一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。如果实在要分片，尽量选客户端分片架构，这样可以减少一次和中间件的网络I/O。

6.3 优化MySQL服务器

1、优化服务器硬件

服务器的硬件性能直接决定着MySQL数据库的性能，硬件的性能瓶颈直接决定MySQL数据库的运行速度和效率，针对性能瓶颈提高硬件配置，可以提高MySQL数据库查询、更新的速度。

配置较大的内存
配置高速磁盘系统
合理分布磁盘I/O
配置多处理器

6.4 优化数据库结构

1、拆分表：冷热数据分离

拆分表的思路是，把1个包含很多字段的表拆分成2个或者多个相对较小的表。这样做的原因是，这些表中某些字段的操作频率很高（热数据），经常要进行查询或者更新操作，而另外一些字段的使用频率却很低（冷数据），冷热数据分离，可以减小表的宽度。如果放在一个表里面，每次查询都要读取大记录，会消耗较多的资源。

MySQL限制每个表最多存储4096列，并且每一行数据的大小不能超过65535字节。表越宽，把表装载进内存缓冲池时所占用的内存也就越大，也会消耗更多的IO。冷热数据分离的目的是:

①减少磁盘lO，保证热数据的内存缓存命中率。
②更有效的利用缓存，避免读入无用的冷数据。

2、增加中间表

把需要经常联合查询的数据插入中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。

3、增加冗余字段

4、优化数据类型

优先选择符合存储需要的最小的数据类型。

情况1：对整数类型数据进行优化。

遇到整数类型的字段可以用INT 型。这样做的理由是，INT 型数据有足够大的取值范围，不用担心数据超出取值范围的问题。刚开始做项目的时候，首先要保证系统的稳定性，这样设计字段类型是可以的。但在数据量很大的时候，数据类型的定义，在很大程度上会影响到系统整体的执行效率。

对于非负型的数据（如自增ID、整型IP）来说，要优先使用无符号整型UNSIGNED来存储。因为无符号相对于有符号，同样的字节数，存储的数值范围更大。如tinyint有符号为-128-127，无符号为0-255，多出一倍的存储空间。

情况2：既可以使用文本类型也可以使用整数类型的字段，要选择使用整数类型。

跟文本类型数据相比，大整数往往占用更少的存储空间，因此，在存取和比对的时候，可以占用更少的内存空间。所以，在二者皆可用的情况下，尽量使用整数类型，这样可以提高查询的效率。如：将IP地址转换成整型数据。

情况3：避免使用TEXT、BLOB数据类型情况4：避免使用ENUM类型情况5：使用TIMESTAMP存储时间情况6：用DECIMAL代替FLOAT和DOUBLE存储精确浮点数

总之，遇到数据量大的项目时，一定要在充分了解业务需求的前提下，合理优化数据类型，这样才能充分发挥资源的效率，使系统达到最优。

6.5 优化插入记录的速度

MyISAM引擎的表：

禁用索引
禁用唯一性检查
使用批量插入
使用LOAD DATA INFILE 批量导入

InnoDB引擎的表：

禁用唯一性检查
禁用外键检查
禁止自动提交

6.6 使用非空约束

在设计字段的时候，如果业务允许，建议尽量使用非空约束

6.7 分析表、检查表与优化表

MySQL提供了分析表、检查表和优化表的语句。分析表主要是分析关键字的分布，检查表主要是检查表是否存在错误，优化表主要是消除删除或者更新造成的空间浪费。

1、分析表

1

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name[,tbl_name]…

默认的，MySQL服务会将 ANALYZE TABLE语句写到binlog中，以便在主从架构中，从服务能够同步数据。可以添加参数LOCAL 或者 NO_WRITE_TO_BINLOG取消将语句写到binlog中。

使用ANALYZE TABLE分析表的过程中，数据库系统会自动对表加一个只读锁。在分析期间，只能读取表中的记录，不能更新和插入记录。ANALYZE TABLE语句能够分析InnoDB和MyISAM类型的表，但是不能作用于视图。

ANALYZE TABLE分析后的统计结果会反应到cardinality的值，该值统计了表中某一键所在的列不重复的值的个数。该值越接近表中的总行数，则在表连接查询或者索引查询时，就越优先被优化器选择使用。

2、检查表

1

CHECK TABLE tbl_name [, tbl_name] ... [option] ... option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

MySQL中可以使用CHECK TABLE语句来检查表。CHECK TABLE语句能够检查InnoDB和MyISAM类型的表是否存在错误。CHECK TABLE语句在执行过程中也会给表加上只读锁。

3、优化表

1

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

MySQL中使用OPTIMIZE TABLE语句来优化表。但是，OPTILMIZE TABLE语句只能优化表中的VARCHAR、BLOB或TEXT类型的字段。一个表使用了这些字段的数据类型，若已经删除了表的一大部分数据，或者已经对含有可变长度行的表（含有VARCHAR、BLOB或TEXT列的表）进行了很多更新，则应使用OPTIMIZE TABLE来重新利用未使用的空间，并整理数据文件的碎片。

OPTIMIZE TABLE 语句对InnoDB和MyISAM类型的表都有效。该语句在执行过程中也会给表加上只读锁。

6.8 其它调优策略

服务器语句超时处理
创建全局通用表空间
MySQL8.0新特性：隐藏索引对调优的帮助