MySQL执行状态查看与分析_查看mysql运行状态

 当mysql出现性能问题时，一般会查看mysql的执行状态，执行命令： show processlist

各列的含义

state 状态值

参考官网：MySQL :: MySQL 5.7 Reference Manual :: 8.14.3 General Thread States

1、Sleep

连接资源未释放，如果是通过连接池连接，sleep状态应该保持在一定数据范围内。

2、Copy to tmp table

索引及现有结构无法涵盖查询条件时，会建立一个临时表来满足查询要求，产生巨大的i/o压力Copy to tmp table通常与连表查询有关，建议减少关联查询或者深入优化查询语句，如果出现此状态的语句执行时间过长，会严重影响其他操作，此时可以kill掉该操作。

3、Sending data

Sending data并不是发送数据，是从物理磁盘获取数据的进程，如果影响结果集较多，那么就需要从不同的磁盘碎片去抽取数据，如果sending data连接过多，通常是某查询的影响结果集过大，也就是查询的索引项不够优化

4、Storing result to query cache

如果频繁出现此状态，使用set profiling分析，如果存在资源开销在SQL整体开销的比例过大，则说明query cache碎片较多，使用flush query cache可即时清理，Query cache参数可适当酌情设置

MySQL数据库是常见的两个瓶颈是CPU和I/O的瓶颈，CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候。磁盘I/O瓶颈发生在装入数据远大于内存容量的时候，如果应用分布在网络上，那么查询量相当大的时候那么平瓶颈就会出现在网络上，可以用mpstat, iostat, sar和vmstat来查看系统的性能状态。

除了服务器硬件的性能瓶颈，对于MySQL系统本身，我们可以使用工具来优化数据库的性能，通常有三种：使用索引，使用EXPLAIN分析查询以及调整MySQL的内部配置。

在优化MySQL时，通常需要对数据库进行分析，常见的分析手段有慢查询日志，EXPLAIN 分析查询，profiling分析以及show命令查询系统状态及系统变量，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。

利用上面知识寻找MySQL中Varchar(20)和varchar(200)区别是什么？

数据准备：

CREATE TABLE `user_info_varchar20` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(20)   NOT NULL COMMENT '用户名',
  `age` tinyint(4)  NOT NULL DEFAULT 0 COMMENT '年龄',
  `sex` tinyint(2) NOT NULL DEFAULT 0 COMMENT '性别 0：男 1： 女',
  `create_time` datetime NOT NULL DEFAULT NOW() COMMENT '创建时间',
  `udpate_time` datetime NOT NULL DEFAULT NOW() COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) COMMENT 'name索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户账户';
 
CREATE TABLE `user_info_varchar200` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(200)   NOT NULL COMMENT '用户名',
  `age` tinyint(4)  NOT NULL DEFAULT 0 COMMENT '年龄',
  `sex` tinyint(2) NOT NULL DEFAULT 0 COMMENT '性别 0：男 1： 女',
  `create_time` datetime NOT NULL DEFAULT NOW() COMMENT '创建时间',
  `udpate_time` datetime NOT NULL DEFAULT NOW() COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) COMMENT 'name索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户账户';

每张表插入100w数据：

CREATE DEFINER=`root`@`localhost` PROCEDURE `insertTestData2`(IN total INT)
BEGIN
    DECLARE i INT DEFAULT 1;
		DECLARE batch_size INT DEFAULT 500;
		DECLARE var_offset INT DEFAULT 1;
		DECLARE var_limit INT;
		DECLARE var_batch_values TEXT;
		DECLARE var_age TINYINT;
		#DECLARE var_sex TINYINT;
		
		WHILE i < total+1 DO
		
			SET var_limit=LEAST(var_offset+batch_size, total+1);
			
			SET var_batch_values='';
		  WHILE i < var_limit  DO
			
				set var_age=FLOOR(RAND() * (100 - 18) + 18);
				#set var_sex=IF(RAND() < 0.5, 0, 1);
				
				IF i = (var_limit-1) THEN
						#SET var_batch_values = CONCAT(var_batch_values, CONCAT('(\'', CONCAT('测试用户', i), '\', ', var_age, ', ', var_sex ,', NOW(), NOW())'));
						SET var_batch_values = CONCAT(var_batch_values, CONCAT('(\'', CONCAT('测试用户', i), '\', ', var_age,', 0, NOW(), NOW())'));
				ELSE
				    #SET var_batch_values = CONCAT(var_batch_values, CONCAT('(\'', CONCAT('测试用户', i), '\', ', var_age, ', ', var_sex ,', NOW(), NOW()),'));
						SET var_batch_values = CONCAT(var_batch_values, CONCAT('(\'', CONCAT('测试用户', i), '\', ', var_age,', 0, NOW(), NOW()),'));
				END IF;
				SET i=i+1;
			END WHILE;
			
			SET @sql = CONCAT('INSERT INTO user_info_varchar20(name, age, sex, create_time, udpate_time) VALUES ', var_batch_values);
			PREPARE stmt FROM @sql;
      EXECUTE stmt;
			
			SET @sql = CONCAT('INSERT INTO user_info_varchar200(name, age, sex, create_time, udpate_time) VALUES ', var_batch_values);
			PREPARE stmt FROM @sql;
      EXECUTE stmt;
			
			SET var_offset=var_limit;
		END WHILE;
END

存储空间对比：

SELECT
    table_schema AS "数据库",
    table_name AS "表名",
    table_rows AS "记录数",
    TRUNCATE ( data_length / 1024 / 1024, 4)  AS "数据容量（MB）",
    TRUNCATE ( index_length / 1024 / 1024, 4)  AS "索引容量（MB）" 
FROM
    information_schema.TABLES 
WHERE
    table_schema = 'custom_db' 
and (TABLE_NAME = 'user_info_varchar20' or TABLE_NAME = 'user_info_varchar200')
ORDER BY
    data_length DESC,
    index_length DESC;

从MySQL统计表里面看二者差不多（统计的是个大概值），或者“ANALYZE TABLE tableName”更新统计信息也可以通过对比IBD文件（里面包含索引数据以及表的行数据）。

方式1:ANALYZE TABLE tableName

ANALYZE TABLE user_info_varchar20;
ANALYZE TABLE user_info_varchar200;
 
SELECT
    table_schema AS "数据库",
    table_name AS "表名",
    table_rows AS "记录数",
    TRUNCATE ( data_length / 1024 / 1024, 4)  AS "数据容量（MB）",
    TRUNCATE ( index_length / 1024 / 1024, 4)  AS "索引容量（MB）" 
FROM
    information_schema.TABLES 
WHERE
    table_schema = 'custom_db' 
and (TABLE_NAME = 'user_info_varchar20' or TABLE_NAME = 'user_info_varchar200')
ORDER BY
    data_length DESC,
    index_length DESC;

方式2： 对比IBD文件

查询性能对比

 测试前开启MySQL的profiles

 show variables like '%profiling%';

 set profiling = 1;  开启

执行测试SQL

-- 条件为索引字段查询		
select * from user_info_varchar20 where name = '测试用户800000';
select * from user_info_varchar200 where name = '测试用户800000';
 
-- 排序字段为索引同时搜索字段为索引覆盖(深分页)
select name from user_info_varchar20 order by name LIMIT 999995, 5;
select name from user_info_varchar200 order by name LIMIT 999995, 5;
 
 
-- 排序字段为索引同时搜索字段为普通字段（深分页）
select * from user_info_varchar20 order by name LIMIT 999995, 5;
select * from user_info_varchar200 order by name LIMIT 999995, 5;

执行命令show profiles，查看查询执行效率

 通过对比发现varchar(20)和varcha(200)基本差不多，但是在“排序字段为索引同时搜索字段为普通字段（深分页）”这种情况二者时间差距很大。

 
-- 索引排序同时深分页
select * from user_info_varchar20 order by name LIMIT 999995, 5;
select * from user_info_varchar200 order by name LIMIT 999995, 5;

show profile 命令继续分析

 翻看最上面state字段值说明，可知，二者在处理select查询字段时情况不一样，但为什么差距这么大呢？

 二者不出意外的话应该都使用了filesort排序，查询执行计划

二者都是查询字段未完全包含在排序字段中，所以是Using filesort，这个好理解，但是执行时间为啥会差距这么大呢？

开启优化分析器，继续分析，命令如下：

/* 打开optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 
 
/* @a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @a from performance_schema.session_status where variable_name = 'Innodb_rows_read';
 
/* 执行语句 */
select name,age from user_info_varchar200 order by name LIMIT 999995, 5;
 
/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * from information_schema.OPTIMIZER_TRACE;
 
/* @a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';
 
/* 计算Innodb_rows_read差值 */
SELECT @b-@a;

 表<user_info_varchar20>执行结果

  

表<user_info_varchar200>执行结果

 经过对比，二者的排序缓冲区（sort_buffer_size）大小为1048560(1M),在进行归并排序时，varchar20表用了75个临时表，而varchar200用到了419个临时表，临时文件【恰当叫法应该是数据块，因为底层只会生成两个临时文件chunk和数据块文件】越多(IO次数就越多)，归并排序性能越差。所以原因找到了！！！（间接说明varchar(20)与varchar(200)在排序缓冲区占用内存大小不一样，换种说法就是搜索引擎为两种长度在缓冲区分配的内存不一样,再细节的原因需要了解MySQL底层fileSort，顺便提一句是fileSort的sortKey字节长度不一样引起的）。