。

利用宏观的监控工具和微观的日志分析可以帮我们快速找到调优的思路和方式。

当我们遇到数据库调优问题的时候#xff0c;该如何思考呢#xff1…第09章_性能分析工具的使用

响应时间更快,

。

利用宏观的监控工具和微观的日志分析可以帮我们快速找到调优的思路和方式。

当我们遇到数据库调优问题的时候该如何思考呢这里把思考的流程整理成下面这张图。

观察Show

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

详细解释一下这张图

Connections连接MySQL服务器的次数。

UptimeMySQL服务器的上线时间。

Slow_queries慢查询的次数。

Innodb_rows_readSelect查询返回的行数Innodb_rows_inserted执行INSERT操作插入的行数Innodb_rows_updated执行UPDATE操作更新的

行数Innodb_rows_deleted执行DELETE操作删除的行数Com_select查询操作的次数。

Com_insert插入操作的次数。

对于批量插入的

INSERT

Connections;若查询服务器工作时间则可以执行如下语句:

SHOW

Uptime;若查询MySQL服务器的慢查询次数则可以执行如下语句:

SHOW

Slow_queries;慢查询次数参数可以结合慢查询日志找出慢查询语句然后针对慢查询语句进行表结构优化或者查询语句优化。

SHOW

一条SQL查询语句在执行前需要查询执行计划如果存在多种执行计划的话MySQL会计算每个执行计划所需要的成本从中选择成本最小的一个作为最终执行的执行计划。

如果我们想要查看某条SQL语句的查询成本可以在执行完这条SQL语句之后通过查看当前会话中的last_query_cost变量值来得到当前查询的成本。

它通常也是我们评价一个查询的执行效率的一个常用指标。

这个查询成本对应的是SQL

student_info

---------------------------如果我们想要查询

900001

----------------------------你能看到页的数量是刚才的

SQL

基本上一样就是因为采用了顺序读取的方式将页面一次性加载到缓冲池中然后再进行查找。

虽然

没有增加多少查询时间

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

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

缓冲池

中进行读取当然针对单个页的读取来说如果页存在于内存中会比在磁盘中读取效率高很多。

批量决定效率。

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

所以说遇到I/O并不用担心方法找对了效率还是很高的。

我们首先要考虑数据存放的位置如果是进程使用的数据就要尽量放到缓冲池中其次我们可以充分利用磁盘的吞吐能力一次性批量读取数据这样单个页的读取效率也就得到了提升。

SQL慢查询日志

slow_query_logOFF我们可以把慢查询日志打开注意设置变量值的时候需要使用

global否则会报错

slow_query_logON;然后我们再来查看下慢查询日志是否开启以及慢查询日志文件的位置

/var/lib/mysql/atguigu02-slow.log

long_query_time

%long_query_time%;这里如果我们想把时间缩短比如设置为

#测试发现设置global的方式对当前session的long_query_time失效。

对新连接的客户端有效。

所以可以一并

执行下述语句

%long_query_time%;补充配置文件中一并设置参数

my.cnf

slow_query_log_file/var/lib/mysql/atguigu-low.log

long_query_time3

设置慢查询的阈值为3秒超出此设定值的SQL即被记录到慢查询日志

log_outputFILE如果不指定存储路径慢查询日志默认存储到MySQL数据库的数据文件夹下。

如果不指定文件名默认文件名为hostname_slow.log。

4.2

log_bin_trust_function_creators

创建函数假如报错

DETERMINISTIC......命令开启允许创建函数设置

set

log_bin_trust_function_creators1;

随机产生字符串同上一章

abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ;DECLARE

return_str

CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1RAND()*52),1));SET

1;END

VALUES((STARTi),rand_string(6),rand_num(10,100),rand_num(10,1000));UNTIL

max_numEND

insert_stu1(100001,4000000);4.4

测试及分析

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

stuno

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

3523633

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

row

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

stuno

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

1154002

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

rows

sec)从上面的结果可以看出来查询学生编号为“3455655”的学生信息花费时间为2.09秒。

查询学生姓名为

“oQmLUr”的学生信息花费时间为2.39秒。

已经达到了秒的数量级说明目前查询效率是比较低的下面

show

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

mysqldumpslow

查询时间al:平均锁定时间ar:平均返回记录数at:平均查询时间

默认方式ac:平均查询次数

/var/lib/mysql/atguigu01-slow.log[rootbogon

~]#

/var/lib/mysql/atguigu01-slow.logReading

mysql

/var/lib/mysql/atguigu01-slow.log

Count:

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

mysqldumpslow

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

mysqldumpslow

/var/lib/mysql/atguigu-slow.log#另外建议在使用这些命令时结合

和more

/var/lib/mysql/atguigu-slow.log

more4.6

slow_query_logOFF或者把slow_query_log一项注释掉

[mysqld]

slow_query_logoff;2重启MySQL服务使用SHOW语句查询慢查询日志功能信息具体SQL语句如下。

SHOW

slow_query_log%;从执行结果可以看出慢查询日志的在该目录下

手动删除慢查询日志文件

来重新生成查询日志文件具体命令如下执行完毕会在数据目录下重新生成慢查询日志文件。

mysqladmin

flush-logs命令来删除重建的。

使用时一定要注意一旦执行了这个命令慢查询日志都只存在新的日志文件中如果需要旧的查询日志就必须事先备份。

SQL

调优的测量。

默认情况下处于关闭状态并保存最近15次的运行结果。

mysql

SOURCE显示和Source_functionSource_file

converting

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

information_schema

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

官网介绍

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

版本情况

命令。

在5.7版本后默认explain直接显示partitions和

6.2

select_options如果我们想看看某个查询的执行计划的话可以在具体的查询语句前边加一个

EXPLAIN

在这里把它们都列出来知识为了描述一个轮廓让大家有一个大致的印象。

6.3

log_bin_trust_function_creators

set

log_bin_trust_function_creators1;

创建函数

abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ;DECLARE

return_str

CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1RAND()*52),1));SET

1;END

5),rand_string1(6),rand_string1(10),rand_string1(5),rand_string1(10),rand_string1(10));UNTIL

max_numEND

5),rand_string1(6),rand_string1(10),rand_string1(5),rand_string1(10),rand_string1(10));UNTIL

max_numEND

insert_s1(10001,10000);s2表数据的添加加入1万条记录

CALL

以MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法该条记录的table列代表着该

mysql

输出中只有一条记录其中的table列的值为s1表明这条记录是用来说明对s1表的单表访问方法的。

mysql

s2;可以看出这个连接查询的执行计划中有两条记录这两条记录的table列分别是s1和s2这两条记录用来分别说明对s1表和s2表的访问方法是什么。

SELECT

a;但是下边两种情况下在一条查询语句中会出现多个SELECT关键字

mysql

a;对于连接查询来说一个SELECT关键字后边的FROM字句中可以跟随多个表所以在连接查询的执行计划中每个表都会对应一条记录但是这些记录的id值都是相同的比如

mysql

s2;可以看到上述连接查询中参与连接的s1和s2表分别对应一条记录但是这两条记录对应的id都是1。

这里需要大家记住的是在连接查询的执行计划中每个表都会对应一条记录这些记录的id列的值是相同的出现在前边的表表示驱动表出现在后面的表表示被驱动表。

所以从上边的EXPLAIN输出中我们可以看到查询优化器准备让s1表作为驱动表让s2表作为被驱动表来执行查询。

对于包含子查询的查询语句来说就可能涉及多个SELECT关键字所以在**包含子查询的查询语句的执行计划中每个SELECT关键字都会对应一个唯一的id值比如这样

mysql

查询优化器可能对涉及子查询的查询语句进行重写转变为多表查询的操作。

mysql

a);可以看到虽然我们的查询语句是一个子查询但是执行计划中s1和s2表对应的记录的id值全部是1这就表明查询优化器将子查询转换为了连接查询。

对于包含UNION子句的查询语句来说每个SELECT关键字对应一个id值也是没错的不过还是有点儿特别的东西比方说下边的查询

Union去重

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

select_type

查询语句中不包含UNION或者子查询的查询都算作是SIMPLE类型比方说下边这个单表查询select_type的值就是SIMPLE:

mysql

ALL或者子查询的大查询来说它是由几个小查询组成的其中最左边的那个查询的select_type的值就是PRIMARY,比方说

mysql

s1对应的是执行计划中的第一条记录它的select_type的值就是PRIMARY。

UNION

ALL的大查询来说它是由几个小查询组成的其中除了最左边的那个小查询意外其余的小查询的select_type值就是UNION可以对比上一个例子的效果。

UNION

选择使用临时表来完成UNION查询的去重工作针对该临时表的查询的select_type就是UNION

RESULT,

如果包含子查询的查询语句不能够转为对应的semi-join的形式并且该子查询是不相关子查询并且查询优化器决定采用将该子查询物化的方案来执行该子查询时该子查询的第一个SELECT关键字代表的那个查询的select_type就是SUBQUERY比如下边这个查询

mysql

1;从执行计划中可以看出id为2的记录就代表子查询的执行方式它的select_type是DERIVED,

说明该子查询是以物化的方式执行的。

id为1的记录代表外层查询大家注意看它的table列显示的是derived2表示该查询时针对将派生表物化之后的表进行查询的。

MATERIALIZED

当查询优化器在执行包含子查询的语句时选择将子查询物化之后的外层查询进行连接查询时该子查询对应的select_type属性就是DERIVED比如下边这个查询

mysql

代表分区表中的命中情况非分区表该项为NULL。

一般情况下我们的额查询语句的执行计划的partitions列的值为NULL。

https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html如果想详细了解可以如下方式测试。

创建分区表

创建分区表

id200;查询id大于200200100p1分区的记录查看执行计划partitions是p1符合我们的分区规则

type

列就表明了这个访问方法是啥是较为重要的一个指标。

比如看到type列的值是ref表明MySQL即将使用ref访问方法来执行对s1表的查询。

system

当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的比如MyISAM、Memory那么对该表的访问方法就是system。

比方说我们新建一个MyISAM表并为其插入一条记录

mysql

测试可以把表改成使用InnoDB存储引擎试试看执行计划的type列是什么。

ALL

const

当我们根据主键或者唯一二级索引列与常数进行等值匹配时对单表的访问方法就是const,

mysql

在连接查询时如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的如果该主键或者唯一二级索引是联合索引的话所有的索引列都必须进行等值比较。

则对该被驱动表的访问方法就是eq_ref比方说

mysql

s2.id;从执行计划的结果中可以看出MySQL打算将s2作为驱动表s1作为被驱动表重点关注s1的访问

eq_ref

当通过普通的二级索引列与常量进行等值匹配时来查询某个表那么对该表的访问方法就可能是ref比方说下边这个查询

mysql

当对普通二级索引进行等值匹配查询该索引列的值也可以是NULL值时那么对该表的访问方法就可能是ref_or_null比如说

mysql

一般情况下对于某个表的查询只能使用到一个索引但单表访问方法时在某些场景下可以使用Interseation、union、Sort-Union这三种索引合并的方式来执行查询。

我们看一下执行计划中是怎么体现MySQL使用索引合并的方式来对某个表执行查询的

mysql

类似于两表连接中被驱动表的eq_ref访问方法unique_subquery是针对在一些包含IN子查询的查询语句中如果查询优化器决定将IN子查询转换为EXISTS子查询而且子查询可以使用到主键进行等值匹配的话那么该子查询执行计划的type列的值就是unique_subquery比如下边的这个查询语句

mysql

当我们可以使用索引覆盖但需要扫描全部的索引记录时该表的访问方法就是index比如这样

mysql

一个列这两个列又恰好包含在idx_key_part这个索引中可是搜索条件key_part3不能直接使用该索引进行ref和range方式的访问只能扫描整个idx_key_part索引的记录所以查询计划的type列的值就是index。

再一次强调对于使用InnoDB存储引擎的表来说二级索引的记录只包含索引列和主键列的值而聚簇索引中包含用户定义的全部列以及一些隐藏列所以扫描二级索引的代价比直接全表扫描也就是扫描聚簇索引的代价更低一些。

ALL

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

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

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

比方说下面这个查询

mysql

a;上述执行计划的possible_keys列的值是idx_key1,

idx_key3两个索引然后key列的值是idx_key3表示经过查询优化器计算使用不同索引的成本后最后决定采用idx_key3。

key_len

帮你检查是否充分的利用了索引值越大越好主要针对于联合索引有一定的参考意义。

mysql

10126;key2上有一个唯一性约束是否为NULL占用一个字节那么就是5个字节

mysql

一个字符占3个字节100*3是否为NULL占用一个字节varchar的长度信息占两个字节。

mysql

b;联合索引中可以比较key_len606的好于key_len303

character

setutf83,gbk2,latin11)1(NULL)2(变长字段)varchar(10)变长字段且不允许NULL

character

setutf83,gbk2,latin11)2(变长字段)char(10)固定字段且允许NULL

character

setutf83,gbk2,latin11)1(NULL)char(10)固定字段且不允许NULL

character

a;可以看到ref列的值是const表明在使用idx_key1索引执行查询时与key1列作等值匹配的对象是一个常数当然有时候更复杂一点:

mysql

如果使用的是索引执行的单表扫描那么计算时需要估计出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。

mysql

a;对于单表查询来说这个filtered的值没有什么意义我们更关注在连接查询中驱动表对应的执行计划记录的filtered值它决定了被驱动表要执行的次数

(即:

a;从执行计划中可以看出来查询优化器打算把s1作为驱动表s2当做被驱动表。

我们可以看到驱动表s1表的执行计划的rows列为9688filtered列为10.00这意味着驱动表s1的扇出值就是9688

10.00%

顾名思义Extra列是用来说明一些额外信息的包含不适合在其他列中显示但十分重要的额外信息。

我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句。

MySQL提供的额外信息有好几十个我们就不一个一个介绍了所以我们只挑选比较重要的额外信息介绍给大家。

tables

当查询语句的WHERE子句永远为FALSE时将会提示该额外信息

mysql

当查询列表处有MIN或者MAX聚合函数但是并没有符合WHERE子句中的搜索条件的记录时。

mysql

当我们的查询列表以及搜索条件中只包含属于某个索引的列也就是在可以使用覆盖索引的情况下在Extra列将会提示该额外信息。

比方说下边这个查询中只需要用到idx_key1而不需要回表操作:

mysql

有些搜索条件中虽然出现了索引列但却不能使用到索引比如下边这个查询

SELECT

在连接查询执行过程中当被驱动表不能有效的利用索引加快访问速度MySQL一般会为其分配一块名叫join

buffer的内存块来加快查询速度也就是我们所讲的基于块的嵌套循环算法。

mysql

当我们使用左(外)连接时如果WHERE子句中包含要求被驱动表的某个列等于NULL值的搜索条件而且那个列是不允许存储NULL值的那么在该表的执行计划的Extra列就会提示这个信息

mysql

intersect(...)提示说明准备使用Intersect索引合并的方式执行查询括号中的...表示需要进行索引合并的索引名称

如果出现Using

union(...)提示说明准备使用Union索引合并的方式执行查询;

如果出现Using

sort_union(...)提示说明准备使用Sort-Union索引合并的方式执行查询。

mysql

当我们的LIMIT子句的参数为0时表示压根儿不打算从表中读取任何记录将会提示该额外信息

mysql

10;需要注意的是如果查询中需要使用filesort的方式进行排序的记录非常多那么这个过程是很耗费性能的我们最好想办法将使用文件排序的执行方式改为索引进行排序。

Using

temporary并不是一个好的征兆因为建立与维护临时表要付出很大的成本的所以我们最好能使用索引来替代掉使用临时表比方说下边这个包含GROUP

mysql

EXPLAIN不考虑各种CacheEXPLAIN不能显示MySQL在执行查询时所作的优化工作EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况部分统计信息是估算的并非精确值

7.1

这里谈谈EXPLAIN的输出格式。

EXPLAIN可以输出四种格式

传统格式

第1种格式中介绍的EXPLAIN语句输出中缺少了一个衡量执行好坏的重要属性

成本。

而JSON格式是四种格式里面输出信息最详尽的格式里面包含了执行的成本信息。

FORMATJSON

....EXPLAIN的Column与JSON的对应关系(来源于MySQL

5.7文档)

这样我们就可以得到一个json格式的执行计划里面包含该计划花费的成本。

比如这样

mysql

rows和filter都是我们前边介绍执行计划的输出列在JSON格式的执行计划中rows

相当于rows_examined_per_scanfiltered名称不变。

eval_cost

Workbench可视化查看MySQL的执行计划。

通过点击Workbench的放大镜图标即可生成可视化的查询计划。

全表扫描

显示使用的索引。

还要注意的是每个表格的框上方是每个表访问所发现的行数的估计值以及访问该表的成本。

7.2

在我们使用EXPLAIN语句查看了某个查询的执行计划后紧接着还可以使用SHOW

WARNINGS语句查看与这个查询的执行计划有关的一些扩展信息比如这样

mysql

***************************Level:

NoteCode:

WARNINGS展示出来的信息有三个字段分别是Level、Code、Message。

我们最常见的就是Code为1003的信息当Code值为1003时Message字段展示的信息类似于查询优化器将我们的查询语句重写后的语句。

比如我们上边的查询本来是一个左(外)连接查询但是有一个s2.common_field

NOT

NULL的条件这就会导致查询优化器把左(外)连接查询优化为内连接查询从SHOW

WARNINGS的Message字段也可以看出来原本的LEFE

但是大家一定要注意我们说Message字段展示的信息类似于查询优化器将我们的查询语句重写后的语句并不是等价于也就是说Message字段展示的信息并不是标准的查询语句在很多情况下并不能直接拿到黑框框中运行它只能作为帮助我们理解MySQL将如何执行查询语句的一个参考依据而已。

分析优化器执行计划trace

optimizer_traceenabledon,end_markers_in_jsonon;

set

optimizer_trace_max_mem_size1000000;开启后可分析如下语句

SELECTINSERTREPLACEUPDATEDELETEEXPLAINSETDECLARECASEIFRETURNCALL

测试执行如下SQL语句

information_schema.optimizer_trace

select

information_schema.optimizer_trace\G***************************

row

equality_propagation,resulting_condition:

(student.id

constant_propagation,resulting_condition:

(student.id

trivial_condition_removal,resulting_condition:

(student.id

*/},{substitute_generated_columns:

false,map_bit:

query_references_nonkey_column}]

*/}

*/,analyzing_range_alternatives:

//分析范围选项range_scan_alternatives:

[{index:

*/,analyzing_roworder_intersect:

{usable:

*/,chosen_range_access_summary:

{type:

*/},{considered_execution_plans:

plan_prefix

//最佳访问路径considered_access_paths:

[{rows_to_scan:

*/},{attaching_conditions_to_tables:

(student.id

10),attached_conditions_computation:

attached_conditions_computation

*/,attached_conditions_summary:

//附加条件概要{table:

*/},{finalizing_table_conditions:

[{table:

student,original_table_condition:

(student.id

MISSING_BYTES_BEYOND_MAX_MEM_SIZE:

//第4部分执行跟踪语句的用户是否有查看对象的权限。

当不具有权限时该列信息为1且TRACE字段为空一般在

调用带有SQL

主机相关以host_summary开头主要汇总了IO延迟的信息。

Innodb相关以innodb开头汇总了innodb

buffer信息和事务等待innodb锁的信息。

I/o相关以io开头汇总了等待I/O、I/O使用量情况。

内存使用情况以memory开头从主机、线程、事件等角度展示内存的使用情况连接与会话信息processlist和session相关视图总结了会话相关信息。

表相关以schema_table开头的视图展示了表的统计信息。

索引信息统计了索引的使用情况包含冗余索引和未使用的索引情况。

语句相关以statement开头包含执行全表扫描、使用临时表、排序等的语句信息。

用户相关以user开头的视图统计了用户使用的文件I/O、执行语句统计信息。

等待事件相关信息以wait开头展示等待事件的延迟情况。

9.2

index_name,rows_selected,rows_inserted,rows_updated,rows_deleted

from

table_schema,table_name,sum(io_read_requestsio_write_requests)

from

object_schema,object_name,allocated,data

from

sys.innodb_buffer_stats_by_table

order

sys.statements_with_full_table_scans

where

db,exec_count,first_seen,last_seen,query

from

db,exec_count,tmp_tables,tmp_disk_tables,query

from

file,avg_read,avg_write,avg_readavg_write

avg_io

查询是数据库中最频繁的操作提高查询速度可以有效地提高MySQL数据库的性能。

通过对查询语句的分析可以了解查询语句的执行情况找出查询语句执行的瓶颈从而优化查询语句。