MySQL数据库的性能分析 ---图书《软件性能测试分析与调优实践之路》-手稿节选

1  、MySQL数据库的性能监控

1.1、如何查看MySQL数据库的连接数

连接数是指用户已经创建多少个连接，也就是MySQL中通过执行 SHOW  PROCESSLIST命令输出结果中运行着的线程个数的详情，如图所示。

SHOW PROCESSLIST默认情况下只显示前100条记录的详情，如果超过100条后需要显示所有，可以通过执行SHOW  FULL  PROCESSLIST命令，如图所示。

 

show variables like 'max_connections'可以查询数据库中可以支持的最大连接数，如图所示。

本文作者：张永清， 转载请注明： https://www.cnblogs.com/laoqing/p/16880718.html  来源于博客园 ,本文摘选自《软件性能测试分析与调优实践之路》

 show global status like 'max_used_connections'可以查询当前已经使用过的最大连接数，如图所示。

 1.2、如何查看MySQL数据库当前运行的事务与锁

事务是对数据库执行一种带有原子性、一致性、隔离性、持久性的数据操作，在MySQL中如果需要使用事务，那么数据存储时必须选用MySQL的innodb引擎，使用innodb引擎后，在MySQL系统数据库information_schema的innodb_trx表中记录了数据库当前正在运行的事务。

innodb_trx表中包含的常用字段说明如表所示。

表 innodb_trx表中包含的常用字段说明

字段	描述
trx_id	事务ID
trx_state	事务的状态，一般包括RUNNING、LOCK WAIT、ROLLING BACK 和 COMMITTING着几种不同的状态
trx_started	事务开始运行的时间
trx_requested_lock_id	事务需要等待的但已经被别的程序锁定的资源id，一般可以和INNODB_LOCKS表关联在一起获取更多的被锁定的资源的详细信息
trx_wait_started	事务开始等待时间
trx_mysql_thread_id	事务对应的MySQL线程id
trx_query	事务正在执行的SQL语句
trx_operation_state	事务操作的状态
trx_tables_in_use	事务使用到的数据库表的数量
trx_tables_locked	事务锁定的数据库表的数量
trx_rows_locked	事务锁定的数据记录行数
trx_rows_modified	事务更改的数据记录行数
trx_unique_checks	事务是否打开唯一性检查的标识
trx_foreign_key_checks	事务是否打开外键检查的标识
trx_isolation_level	事务隔离级别，一般分为Read Uncommitted(未提交读取)、Read Committed（已提交读取）、Repeatable Read（可重复读取）、Serializable（序列化）四种不同的级别
trx_weight	事务的权重
trx_lock_memory_bytes	事务锁住的内存大小，单位为 字节
trx_concurrency_tickets	事务并发票数
trx_last_foreign_key_error	事务最后一次的外键检查的错误信息

MySQL系统数据库information_schema的 innodb_locks表中记录了innodb数据库引擎当前产生的锁的情况， innodb_locks表中包含的常用字段说明如表所示。

字段	描述（本文作者：张永清， 转载请注明： https://www.cnblogs.com/laoqing/p/16880718.html  来源于博客园 ,本文摘选自《软件性能测试分析与调优实践之路》）
lock_id	锁的id
lock_trx_id	拥有锁的事务 ID。可以和 INNODB_TRX 表关联查询得到事务的详细信息
lock_mode	锁的模式，锁的模式一般包含： 行级锁：包括S（共享锁）、X（排它锁）、IS（意向共享锁）、IX（意向排它锁）。 表级锁：包括S_GAP（共享间隙锁）、X_GAP（排它间隙锁）、IS_GAP（意向共享间隙锁）、IX_GAP（意向排它间隙锁） 和 AUTO_INC（自动递增锁）
lock_type	锁的类型。包括RECORD（行级锁）和TABLE（表级锁）
lock_table	当前被锁定的或者包含锁定记录的表的名称
lock_index	当 LOCK_TYPE为RECORD 时，表示锁定的索引的名称，否则直接返回NULL
lock_space	当 LOCK_TYPE为RECORD 时，表示锁定行的表空间 ID，否则直接返回NULL
lock_page	当 LOCK_TYPE为RECORD时，表示锁定记录行的页数，否则直接返回NULL
lock_rec	当 LOCK_TYPE为RECORD时，表示锁定的数据行的数量
lock_data	当 LOCK_TYPE为RECORD时，表示锁定记录行的主键

MySQL系统数据库information_schema的innodb_lock_waits表中记录了innodb数据库引擎当前运行的数据库事务等待锁的情况，innodb_lock_waits表中包含的常用字段说明如表所示。本文作者：张永清， 转载请注明： https://www.cnblogs.com/laoqing/p/16880718.html  来源于博客园 ,本文摘选自《软件性能测试分析与调优实践之路》

表innodb_lock_waits表中包含的常用字段说明

字段	描述
requesting_trx_id	请求事务的 ID
Requested_lock_id	事务所等待的锁定的 ID。可以和 INNODB_LOCKS 表关联查询
Blocking_trx_id	阻塞事务的 ID
Blocking_lock_id	阻塞了另一事务的运行的事务的锁的 ID

在数据库中出现死锁时，经常需要通过查询innodb_trx、innodb_locks、innodb_lock_waits这三张表来找出在执行什么事务操作时导致了死锁，例如执行如下SQL可以列出数据库中所有事务的等待和锁定记录。

--本文作者：张永清， 转载请注明： https://www.cnblogs.com/laoqing/p/16880718.html  
--来源于博客园 ,本文摘选自《软件性能测试分析与调优实践之路》
SELECT 
  r.trx_isolation_level,/*事务隔离级别*/
  r.trx_id AS waiting_trx_id,/*正处于等待中的事务id*/
  r.trx_mysql_thread_id AS waiting_trx_thread, /*正处于等待中的数据库线程id*/
  r.trx_state AS waiting_trx_state, /*正处于等待中的事务的状态*/
  lr.lock_mode AS waiting_trx_lock_mode,/*正处于等待中的事务的锁定模式*/
  lr.lock_type AS waiting_trx_lock_type,/*正处于等待中的事务的锁定类型*/
  lr.lock_table AS waiting_trx_lock_table,/*正处于等待中的事务将锁定的表*/
  lr.lock_index AS waiting_trx_lock_index,/*正处于等待中的事务将锁定的索引*/
  r.trx_query AS waiting_trx_SQL,/*正处于等待中的事务将执行的SQL*/
  b.trx_id AS blocking_trx_id,/*正处于锁定中的事务id*/
  b.trx_mysql_thread_id AS blocking_trx_thread,/*正处于锁定中的线程id*/
  b.trx_state AS blocking_trx_state,/*正处于锁定中的事务的状态*/
  lb.lock_mode AS blocking_trx_lock_mode,/*正处于锁定中的事务的锁定模式*/
  lb.lock_type AS blocking_trx_lock_type,/*正处于锁定中的事务的锁定类型*/
  lb.lock_table AS blocking_trx_lock_table,/*正处于锁定中的事务已经锁定的表*/
  lb.lock_index AS blocking_trx_lock_index,/*正处于锁定中的事务已经锁定的索引*/
  b.trx_query AS blocking_sql /*正处于锁定中的事务在执行的SQL*/
FROM
  information_schema.innodb_lock_waits wt 
  INNER JOIN information_schema.innodb_trx b 
    ON b.trx_id = wt.blocking_trx_id 
  INNER JOIN information_schema.innodb_trx r 
    ON r.trx_id = wt.requesting_trx_id 
  INNER JOIN information_schema.innodb_locks lb 
    ON lb.lock_trx_id = wt.blocking_trx_id 
  INNER JOIN information_schema.innodb_locks lr 
    ON lr.lock_trx_id = wt.requesting_trx_id;

1.3、MySQL中数据库表的监控

•  1、查看数据库中当前打开了哪些表: show OPEN TABLES ，如图所示。另外还可以通过show OPEN TABLES where In_use > 0过滤出当前已经被锁定的表。

•  2、查看数据库中表的状态：SHOW STATUS LIKE  '%table%'，如图所示。需要特别注意的是Table_locks_waited  指的是不能立即获取表级锁而需要等待的次数，如果等待的次数非常大则说明可能存在锁争抢的情况，如果是频繁的出现锁争抢则对应用程序的并发性能影响很大。

 

• 3、查看数据库中锁的信息：SHOW STATUS LIKE '%lock%'，如图所示。

 

• 4、查看数据库中的表被扫描的情况：show global status like 'handler_read%'，如图所示，查询的结果数据也可以用来评估数据库中索引的使用情况。查询的结果数据说明如表所示。

 

查询结果项	描述
Handler_read_first	从索引中读取第一项的次数，如果该值非常高，表明服务器正在执行大量的全索引扫描，该值一般不宜太高
Handler_read_key	基于键读取数据行的请求数，该值如果越高则表明大量的查询都走了索引。如果越低，表示索引的利用很低，该值一般越高越好
Handler_read_last	读取索引中最后一个键的请求数
Handler_read_next	按键顺序读取下一行的请求数，如果查询都走了索引，那么该值将不断递增
Handler_read_prev	按键顺序读取前一行的请求数（倒序读取数据），一般用于评估执行ORDER BY … DESC的次数
Handler_read_rnd	基于固定位置读取数据行的请求数，如果正在执行大量的需要对查询结果进行排序的查询，则此值很高。如果该值很高，则可能存在很多查询需要做整表扫描或者查询时一些表的关联连接没有正确使用主键或者索引
Handler_read_rnd_deleted	从数据库数据文件中读取被删除记录行的请求数
Handler_read_rnd_next	从数据库数据文件中读取下一行的请求数，如果SQL语句执行大量表扫描，则此值很高。如果该值很高，一般说明表没有正确添加索引或者SQL语句没有走索引来查询

1.4、性能测试时MySQL中其他常用监控 

• 1、查看每秒事务的提交数：show global status like 'com_commit'，如图所示

 

• 2、查看每秒事务的回滚数：show global status like 'com_rollback'，如图所示

 

•  3、查看线程的运行情况：show global status like 'threads_%'，如图所示。

查询结果描述说明如表所示。

表查询结果描述说明

查询结果项	描述
Threads_cached	线程缓存中的线程数
Threads_connected	已经建立连接的线程数
Threads_created	已经创建的线程数
Threads_running	正在运行中的线程数

• 4、查看数据库建立过的连接总数（包括连接中以及已经断开的连接）：show global status like 'Connections'，如图所示。

•  5、查看innodb引擎缓存命中情况：show global status like 'innodb_buffer_pool_read%'，如图所示。

• 6、 查看join操作时全表扫描的次数：show global status like 'select_full_join'，如图所示，该值一般可以表示SQL语句中的join操作没有走索引的次数，如果值非常大，那可能是SQL语句中的join操作存在性能问题。

• 7、 查看SQL中排序使用情况：show global status like 'sort%'，如图所示

• 8、 查看SQL查询缓存的命中情况：show global status like 'qcache%'，如图所示。

如果需要查询数据库查询缓存的设置，可以通过show variables like 'query_cache%'进行查看，如图所示。

备注：MySQL数据库中大部分的运行状态都可以通过show status和show global status来查看，二者的区别在于前者是查询当前的运行状态，后者是查询全局的运行状态，也就是数据库开始启动运行到现在为止的状态。

2、MySQL数据库的性能定位

2.1、慢SQL

慢SQL 一般指查询很慢的SQL语句，在MySQL数据库中，可以通过慢查询来查看所有执行超时的SQL语句，在默认情况下，一般慢SQL 是关闭的，可以通过执行show variables like 'slow_query%'来查看数据库是否开启了慢查询，如图所示。

从图中看到slow_query_log的值为OFF表示未开启，可以通过执行 set global slow_query_log=1; 或者 set global slow_query_log=ON;来临时开启慢查询，如图所示。

如果需要永久开启，需要修改/etc/my.cnf配置文件，在[mysqld]处加入如下配置，然后重启数据库即可生效，如图所示。

slow_query_log=ON

slow_query_log_file=/var/lib/mysql/localhost-slow.log

 

修改完成重启数据库后，再次执行show variables like 'slow_query%'，发现慢查询已经被开启，如图所示。

 

通过执行show variables like 'long_query%';可以查询慢查询的记录时间，如图所示，默认是10秒，可以通过执行set long_query_time=需要修改的时长;来修改慢查询的记录时间。

 

通过执行show status like 'slow_queries';可以查看慢查询发生的次数，如图所示。

从慢查询日志中，我们也可以看到慢查询发生的详细信息，如图所示，慢查询日志中会记录每次慢查询发生的时间、执行查询时的数据库用户、线程id、查询执行的SQL语句等信息。

本文作者：张永清， 转载请注明： https://www.cnblogs.com/laoqing/p/16880718.html  来源于博客园 ,本文摘选自《软件性能测试分析与调优实践之路》

在获取到慢查询的SQL语句后，就可以借助数据库的执行计划来对慢查询的SQL语句做进一步分析了。

 2.2、执行计划

在MySQL中使用explain关键字可以模拟查看数据库是如何来执行SQL查询语句，也就是常说的查看一条SQL语句在数据库中的执行计划，如图6-2-8所示就是执行EXPLAIN

本文作者：张永清， 转载请注明： https://www.cnblogs.com/laoqing/p/16880718.html  来源于博客园 ,本文摘选自《软件性能测试分析与调优实践之路》

SELECT * FROM  test.test 后返回的SELECT * FROM  test.test查询的执行计划。

 

查询结果返回的字段说明如表所示 

表查询结果返回的字段说明

字段	说明
id	查询的顺序编号，表示查询中执行的顺序，id的值越大执行的优先级越高，如果id相同，则从上往下执行
select_type	查询类型，常见查询类型如下： SIMPLE：表示简单查询方式，SQL语句中一般不会不使用UNION和子查询等 PRIMARY：表示当查询中包含子查询时，最外层的查询语句则被标记为primary 。 UNION：查询语句中如果在UNION关键字之后出现了第二个SELECT，则被标记为UNION。 UNION RESULT：表示查询中有多个查询结果集做UNION操作。 DEPENDENT UNION：表示子查询中存在UNION操作时，从UNION之后的第二个及之后的SELECT语句都是DEPENDENT UNION。 DEPENDENT SUBQUERY：子查询中UNION 中第一个SELECT查询为DEPENDENT SUBQUERY。 SUBQUERY：子查询内层查询的第一个SELECT。 DERIVED：查询语句中如果from子句的子查询中出现了union关键字则外层select查询将被标记为DERIVED。 MATERIALIZED：表示子查询被物化。 UNCACHEABLE SUBQUERY：表示查询结果集无法缓存的子查询，需要逐次查询。 UNCACHEABLE UNION：表示子查询不可被物化，需要逐次运行
Table	查询涉及的表名或者表的别名 本文作者：张永清， 转载请注明： https://www.cnblogs.com/laoqing/p/16880718.html  来源于博客园 ,本文摘选自《软件性能测试分析与调优实践之路》
Type	表示表连接的类型，包括的类型如下所示，如下的这些类型的性能从高到低的顺序是：null→system→const→eq-ref→ref→fulltext→ref_or_null→index_merge→unique_subquery→index_subquery→range→index→ALL null：表示不访问任何的表 system：表示表中只有一条记录，相当于系统表，一般可以认为是const类型的特例。 const：表示主键或者唯一索引的常量查询，表中最多只有1行记录符合查询要求，通常const使用到主键或者唯一索引进行定值查询、常量查询，查询的速度非常快。 eq_ref：表示join 查询过程中，关联条件字段走主键或者唯一索引，出来的行数不止一行。eq_ref是一种查询性能很高的 join 操作。 ref：表示非聚集索引的常量查询 fulltext：表示查询的过程中，使用到了 fulltext 索引。 ref_or_null：跟ref查询类似，在ref的查询基础上会多家一个null值的条件查询 index merg：表示索引联合查询， unique subquery：表示查询走主键的子查询 index subquery：表示查询走非聚集索引的子查询 range：表示查询走索引范围的查询，一般包括：=、<>、>、>=、<、<=、IS NULL、BETWEEN、IN、<=> 等范围。 index：表示通过索引做扫描查询。 All：表示全表扫描，性能最差
possible_keys	查询时预计可能会使用的索引，这里说的索引只是可能会用到，实际查询不一定会用到。
Key	实际查询时真实使用的索引
key_len	使用的索引长度
Ref	关联信息
Rows	查询时扫描的数据记录行数
Extra	表示查询特性的使用情况，常用的查询特性如下所示。 Using index：表示使用了索引 Using index conditio：表示使用了索引做过滤 Using MRR：表示使用了索引做内部排序 Using where：表示使用了where条件 Using temporary：表示使用了临时表。 Using filesort：表示使用文件排序，一般指无法利用索引来完成的排序

本文作者：张永清， 转载请注明： https://www.cnblogs.com/laoqing/p/16880718.html  来源于博客园 ,本文摘选自《软件性能测试分析与调优实践之路》