Web程序MySQL性能瓶颈排查手册
## 耗时的各个过程
用户电脑 <--1--> 服务器网卡 <--2--> WEB程序 <--3--> 应用代码 <--4--> 数据库 <--5--> 调取数据(内存/硬盘) <--6--> 处理数据(CPU)
<--1--> 网络延时，涉及带宽、服务器TCP性能
<--2--> HTTP响应延时，HTTP处理性能(Apache/Nginx/Tomcat/IIS等)
<--3--> 代码性能
<--4--> 数据库耗时(从接到指令到返回结果的时间)
<--5--> 数据库耗时IO部分(等待内存/硬盘读写)
<--6--> 数据库耗时CPU部分(等待CPU完成计算)
## 处理方法
根据用户反馈慢的操作找到相应的实际执行的SQL语句
可以通过开启MySQL慢查询的方式找到执行的SQL语句
可以通过网页程序代码的方式找到执行的SQL语句
查看SQL语句耗时是否是用户感觉慢的主要原因
如果是，使用《Linux 性能 进程资源 CPU内存磁盘网络 sysstat.txt》判断的是IO问题还是CPU问题
然后看看是否可以通过优化方法<-5-><-6->解决
## 各个过程优化方法
<-1->
网络延时(内网 < 1ms)(外网 < 50ms)
1 查看网络配置
2 更换性能更好的网络设备
3 带宽不足加带宽
4 服务器负载及TCP优化
<-2->
WEB程序(Apache/Nginx/Tomcat/IIS等)
1 优化配置提升性能
2 换更快/支持更大并发的程序
<-3->
优化代码
<-4->
优化数据库
1 读写问题看<-5->
2 计算问题看<-6->
<-5->
频繁读写磁盘
1 数据表加索引(避免读取多余的数据)
2 增加系统内存(Linux系统会自动缓存磁盘常用文件到内存中)
3 增大数据库缓存，增加缓存命中率
4 提升磁盘性能
使用更快的磁盘，如SSD
使用更快的阵列，如RAID10
5 独享磁盘IO
新加磁盘/阵列，新磁盘/阵列放数据库文件可以有独立的磁盘IO，甚至读写最多的表单独放磁盘/阵列以独享IO
6 读写分离
使用主从数据库，主数据库(master)处理事务性增改删操作(INSERT/UPDATE/DELETE)，而从数据库(slave)处理查询操作(SELECT)
7 使用Redis加速
<-6->
CPU满负荷
1 SQL语句太复杂(优化语句)
MySQL可以通过explain自动分析语句问题
2 只使用系统中部分CPU核心造成计算能力不足
尝试修改MySQL配置以支持更多核心(MySQL默认只使用4核心)
## 参考资料
1.1 《测试网络延时》制作中...
1.4 《排查TCP问题》制作中...
2.0 《排查HTTP延时》制作中...
4.1 《MySQL SQL语句资源使用情况.txt》
5.3 《MySQL 配置优化 缓存.txt》
6.1 《MySQL 慢查询.txt》
6.2 《MySQL 配置优化 使用CPU多核心.txt》
## 性能瓶颈定位
## top 命令方式
## 查询MySQL进程号
ps -ef | grep mysql
mysql 9878 1 0 12月07 ? 00:13:12 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
root 20921 20891 0 10:01 pts/0 00:00:00 mysql -u root -px xxxxxxxx
## 每秒刷新MySQL主进程信息
top -d 1 -Hp 9878
## 再按 1 显示每个核心
## 再按 V 树形显示进程线程
top - 10:41:37 up 3 days, 34 min, 3 users, load average: 0.07, 0.08, 0.06
Threads: 32 total, 0 running, 32 sleeping, 0 stopped, 0 zombie
%Cpu0 : 0.0 us, 1.0 sy, 0.0 ni, 99.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu1 : 0.0 us, 0.0 sy, 0.0 ni,100.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu2 : 0.0 us, 0.0 sy, 0.0 ni,100.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu3 : 0.0 us, 0.0 sy, 0.0 ni,100.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 7730280 total, 153476 free, 6224396 used, 1352408 buff/cache
KiB Swap: 7995388 total, 7974140 free, 21248 used. 1226216 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
9878 mysql 20 0 7014988 5.6g 8480 S 0.0 75.5 0:00.59 mysqld
9879 mysql 20 0 7014988 5.6g 8480 S 0.0 75.5 0:00.00 `- mysqld
9880 mysql 20 0 7014988 5.6g 8480 S 0.0 75.5 0:23.25 `- mysqld
9881 mysql 20 0 7014988 5.6g 8480 S 0.0 75.5 0:23.56 `- mysqld
14211 mysql 20 0 7014988 5.6g 8480 S 0.0 75.5 5:40.64 `- mysqld
wa # 表示1秒内，CPU等待IO的时间，如果占比过大，可以考虑提升磁盘IO
si # 软中断时间，如果占比过大，可以查看是什么造成的，可能是网卡性能问题
KiB Swap # 如果SWAP使用频繁，说明内存不足，加内存
## 磁盘IO情况
## yum install iotop -y
iotop -p 9878
Total DISK READ : 0.00 B/s | Total DISK WRITE : 0.00 B/s
Actual DISK READ: 0.00 B/s | Actual DISK WRITE: 0.00 B/s
TID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND
9878 be/4 mysql 0.00 B/s 0.00 B/s 0.00 % 0.00 % mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
## 磁盘IO更详细情况《Linux 性能 进程资源 CPU内存磁盘网络 sysstat.txt》
iostat -x
rkB/s 和 wkB/s # 看磁盘读写是否达到极限
await # 平均每次IO请求等待时间(包括等待时间和处理时间，单位：毫秒)【一般系统IO响应时间应该低于5ms，如果大于10ms就比较大了】
r_await # 读
w_await # 写
## 网络IO更详细情况《Linux 性能 进程资源 CPU内存磁盘网络 sysstat.txt》
## 查看全部网卡进出正常信息
## 查看流量是否达到网卡极限
sar -n DEV 1
rxKB/S # 每秒钟接受的数据包大小，单位为KB（12000.00(rxkB/s) = 12MB/秒 (百兆网络极限)）
txKB/S # 每秒钟发送的数据包大小，单位为KB
## 参考资料
4.1 《MySQL SQL语句资源使用情况.txt》
## 分析使用的系统资源 profiling 分析查询
## 查看 profiling 状态
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 | # 默认关闭
+-------------+
## 开启 profiling
mysql> set profiling = 1;
## 查看执行过的SQL语句ID
mysql> show profiles;
+----------+------------+------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+------------------------------------------------+
| 1 | 0.00430125 | select @@profiling |
| 2 | 1.44244375 | SELECT * FROM IC where IC='400000000000001' |
+----------+------------+------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
说明
QUERY_ID # 数字语句标识符。 一个序号，指示具有相同QUERY_ID值的行的显示顺序。
DURATION # 语句执行在给定状态下保持多长时间（以秒为单位）。
Query # SQL语句
参数
ALL 显示所有的开销信息。
BLOCK IO 显示块IO开销。
CONTEXT SWITCHES 上下文切换开销。
CPU 显示CPU开销信息。
IPC 显示发送和接收开销信息。
MEMORY 显示内存开销信息。目前尚未实现
PAGE FAULTS 显示页面错误开销信息。
SOURCE 显示和Source_function，Source_file，Source_line相关的开销信息。
SWAPS 显示交换次数开销信息。
## 分析ID为2的语句详细信息
mysql> show profile all for query 2;
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
| Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function | Source_file | Source_line |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
| starting | 0.000175 | 0.000120 | 0.000046 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL |
| checking permissions | 0.000026 | 0.000017 | 0.000006 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | check_access | sql_authorization.cc | 809 |
| Opening tables | 0.000049 | 0.000036 | 0.000014 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | open_tables | sql_base.cc | 5793 |
| init | 0.000071 | 0.000051 | 0.000019 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | handle_query | sql_select.cc | 128 |
| System lock | 0.000030 | 0.000021 | 0.000008 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 330 |
| optimizing | 0.000029 | 0.000021 | 0.000008 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 158 |
| statistics | 0.000040 | 0.000030 | 0.000011 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 374 |
| preparing | 0.000034 | 0.000024 | 0.000009 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 482 |
| executing | 0.000011 | 0.000008 | 0.000003 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | exec | sql_executor.cc | 126 |
| Sending data | 1.440666 | 0.847068 | 0.473223 | 39 | 101 | 2853856 | 0 | 0 | 0 | 0 | 5 | 0 | exec | sql_executor.cc | 202 |
| end | 0.000012 | 0.000005 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | handle_query | sql_select.cc | 206 |
| query end | 0.000007 | 0.000005 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 4956 |
| closing tables | 0.000007 | 0.000006 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 5009 |
| freeing items | 0.001157 | 0.000789 | 0.000299 | 2 | 0 | 8096 | 0 | 0 | 0 | 1 | 2 | 0 | mysql_parse | sql_parse.cc | 5622 |
| logging slow query | 0.000113 | 0.000079 | 0.000029 | 0 | 0 | 0 | 8 | 0 | 0 | 0 | 0 | 0 | log_slow_do | log.cc | 1716 |
| cleaning up | 0.000017 | 0.000011 | 0.000004 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | dispatch_command | sql_parse.cc | 1931 |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
16 rows in set, 1 warning (0.00 sec)
说明
Status ## 语句执行的全过程
starting ## 语句执行开始的第一阶段。
checking permissions ## 线程正在检查服务器是否具有执行该语句所需的特权。
Opening tables ## 线程正在尝试打开表。除非有什么阻止打开的步骤，否则这应该是非常快速的过程。例如，一个ALTER TABLE或 LOCK TABLE语句可以阻止打开表，直到该语句完成为止。还值得检查您的table_open_cache值是否足够大。
init ## 出现这种情况的初始化之前 ALTER TABLE，DELETE，INSERT，SELECT，或 UPDATE语句。服务器在此状态下采取的操作包括刷新二进制日志，InnoDB日志和一些查询缓存清除操作。
System lock ## 从此线程已被调用mysql_lock_tables() ，并且线程状态尚未更新。这是一种非常普遍的状态，可能由于多种原因而发生。
optimizing ## 服务器正在对查询执行初始优化。
statistics ## 服务器正在计算统计信息以制定查询执行计划。如果线程长时间处于此状态，则服务器可能是磁盘绑定的，正在执行其他工作。
preparing ## 此状态在查询优化期间发生。
executing ## 该线程已开始执行一条语句。
Sending data ## 线程正在为一个select语句读取和处理行，并且发送数据到客户端。【从磁盘读取数据，处理数据(过滤/排序等)，发送数据】需要继续看此行后面cpu、io、网络才能分析瓶颈是读取慢还是处理慢，还是发送慢
end ## 这发生在结束，但的清理之前 ALTER TABLE， CREATE VIEW， DELETE， INSERT， SELECT，或 UPDATE语句。对于end状态，可能发生以下操作：1.更改表中的数据后删除查询缓存条目 2.将事件写入二进制日志 3.释放内存缓冲区，包括blob
query end ## 此状态在处理查询之后但在该freeing items状态之前发生 。
closing tables ## 线程正在将已更改的表数据刷新到磁盘并关闭已使用的表。这应该是一个快速的操作。如果没有，请确认您没有完整的磁盘，并且磁盘使用率不是很高。
freeing items ## 线程已执行命令。在此状态下完成的一些项目释放涉及查询缓存。此状态通常后跟cleaning up。
logging slow query ## 线程正在将一条语句写入慢速查询日志。
cleaning up ## 该线程已经处理了一条命令，并准备释放内存并重置某些状态变量。
DURATION # 语句执行在给定状态下保持多长时间（以秒为单位）。
CPU_USER, CPU_SYSTEM # 用户和系统CPU使用率，以秒为单位。
CONTEXT_VOLUNTARY, CONTEXT_INVOLUNTARY # 发生了多少自愿和非自愿上下文切换。
BLOCK_OPS_IN, BLOCK_OPS_OUT # 块输入和输出操作的数量。
MESSAGES_SENT, MESSAGES_RECEIVED # 发送和接收的通信消息数。
PAGE_FAULTS_MAJOR, PAGE_FAULTS_MINOR # 主要和次要页面错误的数量。
SWAPS # 发生了多少交换。
SOURCE_FUNCTION, SOURCE_FILE 和 SOURCE_LINE # 指示配置文件状态在源代码中的何处执行的信息。
## 重要的几项
mysql> show profile cpu, block io, ipc for query 2;
## 只看基础耗时
mysql> show profile for query 2;
## 测试完关闭 profiling（直接退出会话也可以，开启profiling只对当前会话有效）
mysql> set profiling=0
## profiling 各参数解释
参考资料 https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html
MySQL 5.7参考手册
下表描述了State 与常规查询处理而非更专门的活动（如复制）相关联的线程值。其中许多仅用于发现服务器中的错误。
After create
当线程在创建表的函数的末尾创建表（包括内部临时表）时，会发生这种情况。即使由于某些错误而无法创建表，也会使用此状态。
altering table
服务器正在执行就地服务 ALTER TABLE。
Analyzing
该线程正在计算MyISAM表键分布（例如for ANALYZE TABLE）。
checking permissions
线程正在检查服务器是否具有执行该语句所需的特权。
Checking table
线程正在执行表检查操作。
cleaning up
该线程已经处理了一条命令，并准备释放内存并重置某些状态变量。
closing tables
线程正在将已更改的表数据刷新到磁盘并关闭已使用的表。这应该是一个快速的操作。如果没有，请确认您没有完整的磁盘，并且磁盘使用率不是很高。
converting HEAP to ondisk
线程正在将内部临时表从 MEMORY表转换为磁盘表。
copy to tmp table
线程正在处理一条ALTER TABLE语句。在创建具有新结构的表之后但在将行复制到表中之前，将发生此状态。
对于处于这种状态的线程，可以使用性能架构来获取有关复制操作进度的信息。请参见 第24.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
线程正在处理ALTER TABLE ... ENABLE KEYS一个MyISAM表。
Creating sort index
线程正在处理 SELECT使用内部临时表解析的。
creating table
该线程正在创建一个表。这包括创建临时表。
Creating tmp table
该线程正在内存或磁盘上创建一个临时表。如果该表是在内存中创建的，但后来又转换为磁盘表，则该操作期间的状态为Copying to tmp table on disk。
committing alter table to storage engine
服务器已就地完成 ALTER TABLE并提交结果。
deleting from main table
服务器正在执行多表删除的第一部分。它仅从第一个表中删除，并保存要用于从其他（参考）表中删除的列和偏移量。
deleting from reference tables
服务器正在执行多表删除的第二部分，并从其他表中删除匹配的行。
discard_or_import_tablespace
线程正在处理ALTER TABLE ... DISCARD TABLESPACEorALTER TABLE ... IMPORT TABLESPACE语句。
end
这发生在结束，但的清理之前 ALTER TABLE， CREATE VIEW， DELETE， INSERT， SELECT，或 UPDATE语句。
对于end状态，可能发生以下操作：
更改表中的数据后删除查询缓存条目
将事件写入二进制日志
释放内存缓冲区，包括blob
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的每个主循环中都会检查该标志，但是在某些情况下，线程死亡仍然可能需要很短的时间。如果该线程被其他某个线程锁定，则杀死操作将在另一个线程释放其锁定后立即生效。
logging slow query
线程正在将一条语句写入慢速查询日志。
login
连接线程的初始状态，直到客户端已成功通过身份验证。
manage keys
服务器正在启用或禁用表索引。
Opening tables
线程正在尝试打开表。除非有什么阻止打开的步骤，否则这应该是非常快速的过程。例如，一个ALTER TABLE或 LOCK TABLE语句可以阻止打开表，直到该语句完成为止。还值得检查您的table_open_cache价值是否足够大。
optimizing
服务器正在对查询执行初始优化。
preparing
此状态在查询优化期间发生。
Purging old relay logs
该线程正在删除不需要的中继日志文件。
query end
此状态在处理查询之后但在该freeing items状态之前发生 。
Receiving from client
服务器正在从客户端读取数据包。Reading from net在MySQL 5.7.8之前，此状态称为。
Removing duplicates
查询的使用 SELECT DISTINCT方式使得MySQL无法在早期阶段优化独特的操作。因此，MySQL需要一个额外的阶段来删除所有重复的行，然后再将结果发送给客户端。
removing tmp table
线程在处理SELECT 语句后正在删除内部临时表。如果未创建临时表，则不使用此状态。
rename
线程正在重命名表。
rename result table
该线程正在处理一条ALTER TABLE语句，创建了新表，并对其进行了重命名以替换原始表。
Reopen tables
线程获得了该表的锁，但是在获得该锁后，该线程注意到基础表结构已更改。它释放了锁，关闭了表，并试图重新打开它。
Repair by sorting
修复代码正在使用某种排序来创建索引。
preparing for alter table
服务器正在准备执行就地 ALTER TABLE。
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
服务器正在将数据包写入客户端。Writing to net在MySQL 5.7.8之前，此状态称为。
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
从此线程已被调用mysql_lock_tables() ，并且线程状态尚未更新。这是一种非常普遍的状态，可能由于多种原因而发生。
例如，线程将要请求或正在等待表的内部或外部系统锁定。InnoDB在执行期间等待表级锁定时， 可能会发生这种情况LOCK TABLES。如果此状态是由对外部锁的请求引起的，并且您没有使用正在访问同一 表的多个mysqld服务器，则MyISAM可以使用该--skip-external-locking 选项禁用外部系统锁 。但是，默认情况下禁用外部锁定，因此此选项可能无效。对于 SHOW PROFILE，此状态表示线程正在请求锁定（不等待它）。
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
该线程收到通知，表明表的基础结构已更改，因此需要重新打开表以获取新结构。但是，要重新打开该表，它必须等待，直到所有其他线程都关闭了该表。
这张告示，如果另一个线程使用的地方 FLUSH TABLES或有问题的表下面的语句之一： ， ， ， ， ，或 。 FLUSH TABLES tbl_nameALTER TABLERENAME TABLEREPAIR TABLEANALYZE TABLEOPTIMIZE TABLE
Waiting for table flush
该线程正在执行FLUSH TABLES并正在等待所有线程关闭其表，或者该线程收到有关表的基础结构已更改的通知，并且需要重新打开表以获取新结构。但是，要重新打开该表，它必须等待，直到所有其他线程都关闭了该表。
这张告示，如果另一个线程使用的地方 FLUSH TABLES或有问题的表下面的语句之一： ， ， ， ， ，或 。 FLUSH TABLES tbl_nameALTER TABLERENAME TABLEREPAIR TABLEANALYZE TABLEOPTIMIZE TABLE
Waiting for lock_type lock
服务器正在等待THR_LOCK从元数据锁定子系统获取 锁或锁，其中 lock_type指示了锁的类型。
此状态表示正在等待 THR_LOCK：
Waiting for table level lock
这些状态指示等待元数据锁定：
Waiting for event metadata lock
Waiting for global read lock
Waiting for schema metadata lock
Waiting for stored function metadata lock
Waiting for stored procedure metadata lock
Waiting for table metadata lock
Waiting for trigger metadata lock
有关表锁定指示器的信息，请参见 第8.11.1节“内部锁定方法”。有关元数据锁定的信息，请参见第8.11.4节“元数据锁定”。要查看哪些锁阻止了锁请求，请使用第24.12.12节“性能模式锁表”中介绍的 性能模式锁表。
Waiting on cond
线程正在等待条件变为真的一般状态。没有可用的特定状态信息。
Writing to net
服务器正在将数据包写入网络。Sending to client从MySQL 5.7.8开始，此状态被称为。
5.3 《MySQL 配置优化 缓存.txt》
## MySQL 缓存参数配置
## innodb_buffer_pool_size
## 当前配置情况
mysql> show variables like '%innodb_buffer_pool%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size | 134217728 | （单位B默）认128MB 134217728/1024/1024 = 128
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 134217728 |
+-------------------------------------+----------------+
10 rows in set (0.00 sec)
## 分析InnoDB缓冲池的性能来验证当前配置的 innodb_buffer_pool_size 是否合适
## 可以使用以下公式计算InnoDB缓冲池性能：
## Performance = innodb_buffer_pool_reads / innodb_buffer_pool_read_requests * 100
## InnoDB buffer pool 命中率：
## InnoDB buffer pool 命中率 = innodb_buffer_pool_read_requests / (innodb_buffer_pool_read_requests + innodb_buffer_pool_reads ) * 100
## 此值低于99%，则可以考虑增加innodb_buffer_pool_size。
## 缓冲池命中率 = (Innodb_buffer_pool_read_requests)/(Innodb_buffer_pool_read_requests + Innodb_buffer_pool_read_ahead + Innodb_buffer_pool_reads)
## 平均每次读取的字节数 = Innodb_data_read/Innodb_data_reads
mysql> show status like 'innodb_buffer_pool_read%';
+---------------------------------------+----------+
| Variable_name | Value |
+---------------------------------------+----------+
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 227524 | （累计值）预读的页数
| Innodb_buffer_pool_read_ahead_evicted | 0 | （累计值）预读的页数，但是没有被读取就从缓冲池中被替换的页的数量，一般用来判断预读的效率。
| Innodb_buffer_pool_read_requests | 36070845 | （累计值）从缓冲池中读取的次数。
| Innodb_buffer_pool_reads | 2483 | （累计值）表示从物理磁盘读取的页数，InnoDB缓冲池无法满足的请求数。
+---------------------------------------+----------+
5 rows in set (0.00 sec)
## 2483/36070845 * 100 = 0.0068836757220408894
## InnoDB buffer pool 命中率：
36070845 / (36070845 + 2483) * 100 = 99.99311679809526
mysql> show status like 'Innodb_data_%';
+----------------------------+-------------+
| Variable_name | Value |
+----------------------------+-------------+
| Innodb_data_fsyncs | 12598 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 16728216064 | （累计值）总共读入的字节数。
| Innodb_data_reads | 1021037 | （累计值）发起读请求的次数，每次读取可能需要读取多个页。
| Innodb_data_writes | 372228 |
| Innodb_data_written | 18872196096 |
+----------------------------+-------------+
8 rows in set (0.00 sec)
## 在专用MySQL服务器上，多余的innodb_buffer内存不会有问题，
## 但是当使用共享服务器时（服务器上还跑了不是数据库的其他业务应用，如web等），可能会有性能影响。因为空闲内存对其他程序和操作系统很有用。
mysql> show engine innodb status\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2020-12-07 11:08:14 0x7fc1c4fb7700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 4 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 135 srv_active, 0 srv_shutdown, 3868 srv_idle
srv_master_thread log flush and writes: 4003
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1002
OS WAIT ARRAY INFO: signal count 2600
RW-shared spins 0, rounds 2772, OS waits 35
RW-excl spins 0, rounds 4868, OS waits 67
RW-sx spins 191, rounds 5676, OS waits 188
Spin rounds per wait: 2772.00 RW-shared, 4868.00 RW-excl, 29.72 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 3380
Purge done for trx's n:o < 3380 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421945643591504, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
230039 OS file reads, 74478 OS file writes, 2535 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 1428164261
Log flushed up to 1428164261
Pages flushed up to 1428164261
Last checkpoint at 1428164252
0 pending log flushes, 0 pending chkp writes
599 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 106973
Buffer pool size 8191
Free buffers 1 ## 表示有多少空闲buffer。如果 此值长时间都较高，则可以考虑减小InnoDB缓冲池大小。
Database pages 8189
Old database pages 3041
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 76, not young 4475520 # young：从 old 区移动到 new 区有多少个页，not young：因为 innodb_old_blocks_time 的设置而导致页没有从 old 部分启动到 new 部分的操作。
0.00 youngs/s, 0.00 non-youngs/s
Pages read 230006, created 70878, written 71348
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout # Buffer pool hit rate：表示缓冲池的命中率，通常这个值不应该小于95%，如果小于95%，则应该看看是不是由于全表扫描而导致 LRU 列表有污染。
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 8189, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=24750, Main thread ID=140470366852864, state: sleeping
Number of rows inserted 10000000, updated 0, deleted 0, read 40000008
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
##
6.1 《MySQL 慢查询.txt》
## 慢查询只计算语句执行时间，等锁时间不算，
## 实际时间 = 执行时间 + 锁等待时间
## 其中：执行时间 < long_query_time 将不计入慢查询
## 查看慢查询状态
mysql> show variables like '%slow%';
+---------------------------+-----------------------------------+
| Variable_name | Value |
+---------------------------+-----------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | OFF | # 默认关闭
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------------+-----------------------------------+
5 rows in set (0.01 sec)
## 查看慢查询配置
mysql> show variables like 'long%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 | # 多少秒才算慢，默认10秒，long_query_time 实际上界定的是实际执行时间，所以有些情况下虽然语句实际消耗的时间很长但是是因为锁等待时间较长而引起的，那么实际上这种语句也不会记录到慢查询。
+-----------------+-----------+
1 row in set (0.01 sec)
## 开启慢查询
mysql> set global slow_query_log = 1;
## 设置超过1秒的都记录慢查询
mysql> set long_query_time=1;
## 试试一条慢的
mysql> SELECT * FROM IC where IC='400000000000001';
Empty set (1.46 sec)
## 查看日志文件，数量少
cat /var/lib/mysql/localhost-slow.log
/usr/sbin/mysqld, Version: 5.7.31 (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 2020-12-04T08:15:19.108765Z
# User@Host: root[root] @ localhost [] Id: 5
# Query_time: 1.462273 Lock_time: 0.000304 Rows_sent: 0 Rows_examined: 10000000
use BIG;
SET timestamp=1607069719;
SELECT * FROM IC where IC='400000000000001';
## 查看日志文件，数量多
mysqldumpslow /var/lib/mysql/localhost-slow.log
Reading mysql slow query log from /var/lib/mysql/localhost-slow.log
Count: 1 Time=1.46s (1s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
SELECT * FROM IC where IC='S'
# 参数
-s 是表示按照何种方式排序，c、t、l、r 分别是按照 记录次数、时间、查询时间、返回的记录数 来排序，ac、at、al、ar，表示相应的倒叙；
-t 是top n的意思，即为返回前面多少条的数据；
-g 后边可以写一个正则匹配模式，大小写不敏感的；
# 示例
mysqldumpslow -s r -t 10 slow-log # 得到返回记录集最多的10个查询。
mysqldumpslow -s t -t 10 -g “left join” slow-log # 得到按照时间排序的前10条里面含有左连接的查询语句。
## 语句优化 explain
## https://www.cnblogs.com/tufujie/p/9413852.html
## https://www.cnblogs.com/tufujie/p/9648088.html
mysql> explain SELECT * FROM IC where IC='400000000000001';
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| 1 | SIMPLE | IC | NULL | ALL | NULL | NULL | NULL | NULL | 10000000 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
id ## 选择标识符
select_type ## 表示查询的类型
SIMPLE # (简单SELECT，不使用UNION或子查询等)
PRIMARY # (子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)
UNION # (UNION中的第二个或后面的SELECT语句)
DEPENDENT UNION # (UNION中的第二个或后面的SELECT语句，取决于外面的查询)
UNION RESULT # (UNION的结果，union语句中第二个select开始后面所有select)
SUBQUERY # (子查询中的第一个SELECT，结果不依赖于外部查询)
DEPENDENT SUBQUERY # (子查询中的第一个SELECT，依赖于外部查询)
DERIVED # (派生表的SELECT, FROM子句的子查询)
UNCACHEABLE SUBQUERY # (一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
table ## 输出结果集的表(显示这一行的数据是关于哪张表的，有时不是真实的表名字，可能是简称，也可能是第几步执行的结果的简称)
partitions ## 匹配的分区
type ## 表示表的连接类型
ALL # Full Table Scan，MySQL将遍历全表以找到匹配的行
index # Full Index Scan，index与ALL区别为index类型只遍历索引树
range # 只检索给定范围的行，使用一个索引来选择行
ref # 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
eq_ref # 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
const/system # 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
NULL # MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
possible_keys ## 表示查询时，可能使用的索引
key ## 表示实际使用的索引(NULL 没有选择索引)
key_len ## 索引字段的长度
ref ## 列与索引的比较(列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值)
rows ## 扫描出的行数(估算的行数)
filtered ## 按表条件过滤的行百分比
Extra ## 执行情况的描述和说明
Using where # 不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
Using temporary # 表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by
Using filesort # 当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”
Using join buffer # 改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
Impossible where # 这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。
Select tables optimized away # 这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行
No tables used # Query语句中使用from dual 或不含任何from子句
6.2 《MySQL 配置优化 使用CPU多核心.txt》
# 查看当前MySQL使用多少个核心
mysql> show variables like '%_io_threads' ;
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| innodb_read_io_threads | 4 |
| innodb_write_io_threads | 4 |
+-------------------------+-------+
2 rows in set (0.01 sec)
## 修改使用更多核心
修改配置文件，增加如下2行
innodb_read_io_threads=16
innodb_read_write_threads=16
重启MySQL
146
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
