当mysql出现性能问题时，一般会查看mysql的执行状态，执行命令： show processlist
各列的含义
| 列名 | 含义 |
|---|---|
| id | 一个标识，你要kill一个语句的时候使用，例如 mysql> kill 207 |
| user | 显示当前用户，如果不是root，这个命令就只显示你权限范围内的sql语句 |
| host | 显示这个语句是从哪个ip 的哪个端口上发出的，可用来追踪出问题语句的用户 |
| db | 显示这个进程目前连接的是哪个数据库 |
| command | 显示当前连接的执行的命令，一般就是休眠（sleep），查询（query），连接（connect） |
| time | 此这个状态持续的时间，单位是秒 |
| state | 显示使用当前连接的sql语句的状态，很重要的列，state只是语句执行中的某一个状态，例如查询，需要经过copying to tmp table，Sorting result，Sending data等状态才可以完成 |
| info | 显示这个sql语句，因为长度有限，所以长的sql语句就显示不全，但是一个判断问题语句的重要依据 |
state 状态值
参考官网：MySQL :: MySQL 5.7 Reference Manual :: 8.14.3 General Thread States
| 状态值 | 说明 |
|---|---|
| After create | 当线程在创建表的函数结束时创建表（包括内部临时表）时，会发生这种情况。即使由于某些错误而无法创建表，也会使用此状态。 |
| altering table | 服务器正在执行就地 ALTER TABLE。 |
| Analyzing | 线程正在计算 MyISAM 表的键分布（例如，对于 ANALYZE TABLE ）。 |
| checking permissions | 线程正在检查服务器是否具有执行语句所需的特权。 |
| Checking table | 线程正在执行表检查操作。 |
| cleaning up | 线程已处理了一个命令，并准备释放内存和重置某些状态变量。 |
| committing alter table to storage engine | 服务器已完成就地 ALTER TABLE 并正在提交结果。 |
| closing tables | 该线程正在将已更改的表数据刷新到磁盘并关闭已使用的表。这应该是一个快速的操作。如果没有，请验证磁盘是否已满，并且磁盘使用率是否很高。 |
| converting HEAP to ondisk | 线程正在将内部临时表从 MEMORY 表转换为磁盘上的表。 |
| copy to tmp table | 线程正在处理 对于处于此状态的线程，性能架构可用于获取有关复制操作进度的信息。请参见第25.12.5节“性能架构阶段事件表”。 |
| Copying to group table | 如果语句具有不同的 ORDER BY 和 GROUP BY 条件，则按组对行进行排序并将其复制到临时表中。 |
| Copying to tmp table | 服务器正在复制到内存中的临时表。 |
| Copying to tmp table on disk | 服务器正在复制到磁盘上的临时表。临时结果集变得太大（请参见第8.4.4节"MySQL中的内部临时表使用"）。因此，线程将临时表从内存中的格式更改为基于磁盘的格式以节省内存。 |
| 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 | 这发生在结束时，但在清除 对于
|
| executing | 线程已开始执行语句。 |
| Execution of init_command | 线程正在执行 init_command 系统变量值中的语句。 |
| freeing items | 线程已执行命令。在此状态下执行的某些项释放涉及查询缓存。这个状态通常跟在 cleaning up 后面。 |
| FULLTEXT initialization | 服务器正准备执行自然语言全文搜索。 |
| init | 这发生在初始化 ALTER TABLE 、 DELETE 、 INSERT 、 SELECT 或 UPDATE 语句之前。服务器在此状态下执行的操作包括刷新二进制日志、 InnoDB 日志和一些查询缓存清理操作。 |
| Killed | 有人向线程发送了一个 KILL 语句，它应该在下次检查kill标志时中止。在MySQL中的每个主要循环中都检查该标志，但在某些情况下，线程可能仍然需要很短的时间才能死亡。如果线程被其他线程锁定，则kill在其他线程释放其锁后立即生效。 |
| logging slow query | 线程正在向慢速查询日志写入语句。 |
| login | 连接线程在客户端成功通过身份验证之前的初始状态。 |
| manage keys | 服务器正在启用或禁用表索引。 |
| Opening tables | 线程正在尝试打开一个表。这应该是非常快的程序，除非有什么阻止打开。例如， ALTER TABLE 或 LOCK TABLE 语句可以在语句完成之前阻止打开表。同样值得检查的是，你的 table_open_cache 值是否足够大。 |
| optimizing | 服务器正在对查询执行初始优化。 |
| preparing | 此状态发生在查询优化期间。 |
| preparing for alter table | 服务器正在准备执行就地 ALTER TABLE 。 |
| Purging old relay logs | 线程正在删除不需要的中继日志文件。 |
| query end | 此状态发生在处理查询之后，但在 freeing items 状态之前。 |
| Receiving from client | 服务器正在阅读来自客户端的数据包。这个状态在MySQL 5.7.8之前被称为 Reading from net 。 |
| Removing duplicates | 该查询使用 SELECT DISTINCT 的方式使得MySQL无法在早期阶段优化不同的操作。因此，MySQL需要一个额外的阶段来删除所有重复的行，然后再将结果发送给客户端。 |
| removing tmp table | 线程在处理了一个 SELECT 语句后删除了一个内部临时表。如果未创建临时表，则不使用此状态。 |
| rename | 线程正在重命名表。 |
| rename result table | 线程正在处理 ALTER TABLE 语句，已创建新表，并将其重命名以替换原始表。 |
| Reopen tables | 线程获得了表的锁，但在获得锁后注意到底层表结构发生了变化。它已经释放了锁，关闭了表，并试图重新打开它。 |
| Repair by sorting | 修复代码正在使用排序来创建索引。 |
| Repair done | 线程已完成 MyISAM 表的多线程修复。 |
| Repair with keycache | 修复代码使用通过密钥缓存逐个创建密钥。这比 Repair by sorting 慢得多。 |
| Rolling back | 线程正在回滚事务。 |
| Saving state | 对于 MyISAM 表操作（如修复或分析），线程将新表状态保存到 .MYI 文件头。状态包括诸如行数、 AUTO_INCREMENT 计数器和键分布等信息。 |
| Searching rows for update | 线程执行第一阶段，在更新行之前查找所有匹配的行。如果 UPDATE 正在更改用于查找相关行的索引，则必须执行此操作。 |
| Sending data | 线程正在阅读和处理 SELECT 语句的行，并将数据发送到客户端。由于在此状态期间发生的操作往往会执行大量的磁盘访问（读取），因此它通常是给定查询的生命周期中运行时间最长的状态。 |
| Sending to client | 服务器正在向客户端写入数据包。这个状态在MySQL 5.7.8之前被称为 Writing to net 。 |
| setup | 线程正在开始 ALTER TABLE 操作。 |
| Sorting for group | 线程正在进行排序以满足 GROUP BY 。 |
| Sorting for order | 线程正在进行排序以满足 ORDER BY 。 |
| Sorting index | 该线程正在对索引页进行排序，以便在 MyISAM 表优化操作期间进行更有效的访问。 |
| Sorting result | 对于 SELECT 语句，这与 Creating sort index 类似，但适用于非临时表。 |
| starting | 语句执行开始时的第一阶段。 |
| statistics | 服务器正在计算统计信息以制定查询执行计划。如果线程长时间处于这种状态，则服务器可能正在磁盘绑定中执行其他工作。 |
| System lock | 该线程已调用 例如，线程将请求或等待表的内部或外部系统锁。当 |
| update | 线程准备开始更新表。 |
| Updating | 线程正在搜索要更新的行并正在更新它们。 |
| updating main table | 服务器正在执行多表更新的第一部分。它只更新第一个表，并保存用于更新其他（引用）表的列和偏移量。 |
| updating reference tables | 服务器正在执行多表更新的第二部分，并更新其他表中匹配的行。 |
| User lock | 线程将请求或正在等待通过 GET_LOCK() 调用请求的咨询锁。对于 SHOW PROFILE ，这个状态意味着线程正在请求锁（而不是等待锁）。 |
| User sleep | 该线程已调用 SLEEP() 调用。 |
| Waiting for commit lock | FLUSH TABLES WITH READ LOCK 正在等待提交锁。 |
| Waiting for global read lock | FLUSH TABLES WITH READ LOCK 正在等待全局读锁或正在设置全局 read_only 系统变量。 |
| Waiting for tables | 线程收到一个通知，表示表的基础结构已更改，需要重新打开表以获取新结构。但是，要重新打开表，它必须等到所有其他线程都关闭了有问题的表。 如果另一个线程在相关表上使用了 |
| Waiting for table flush | 线程正在执行 如果另一个线程在相关表上使用了 |
Waiting for lock_type lock | 服务器正在等待获取 此状态表示等待
这些状态表示等待元数据锁定：
有关表锁定指示器的信息，请参见第8.11.1节“内部锁定方法”。有关元数据锁定的信息，请参见第8.11.4节“元数据锁定”。若要查看哪些锁正在阻塞锁请求，请使用第25.12.12节“性能架构锁表”中描述的性能架构锁表。 |
| Waiting on cond | 一种泛型状态，在这种状态下，线程正在等待条件变为真。没有可用的特定状态信息。 |
| Writing to net | 服务器正在向网络写入数据包。从MySQL 5.7.8开始，这个状态被称为 Sending to client 。 |
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数据：
DELIMITER $$
CREATE PROCEDURE insertTestData(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;
WHILE i <= total DO
SET var_limit=var_offset+batch_size;
SET var_batch_values='';
WHILE i < var_limit DO
IF i = (var_limit-1) THEN
SET var_batch_values = CONCAT(var_batch_values, CONCAT('(\'', CONCAT('测试用户', i), '\', 0, 0, NOW(), NOW())'));
ELSE
SET var_batch_values = CONCAT(var_batch_values, CONCAT('(\'', CONCAT('测试用户', i), '\', 0, 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$$
DELIMITER;
CALL insertTestData(1000000);存储空间对比：
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个临时表，临时文件越多，归并排序性能越差。所以原因找到了！！！（间接说明varchar(20)与varchar(200)在排序缓冲区占用内存大小不一样，换种说法就是搜索引擎为两种长度在缓冲区分配的内存不一样）。
