SQL优化器有两种,RBO(基于规则优化),CBO(基于成本优化),现在工作中一般基本使用CBO。
话不多说,直接上步骤。
一、性能监控
1. profiling
通过开启profiling,使用show profile查询剖析工具。
先写一条查询语句,我查询了整张表的信息,如图所示:
最后一行,可以看到,显示时间为 0.00sec, 说明只显示到了小数点后两位,为了更精确、清楚的看到时间,我们开启profiling
.
MySQL在5.0之后有个分析查询语句非常重要的利器,就是profiling。
mysql> set profiling = 1;
Query OK, 0 rows affected (0.00 sec)
开启后再进行查询操作,使用show profiles
查看开启profiling
后被记录分析的操作。如图:
由Duration可以看到Query_ID为1 的SQL语句的查询时间。
进行多次操作后,使用show profile
查询最近一条语句的具体执行过程分析。
通过Query_ID查看指定语句的具体执行过程分析:
eg, show profile for query 3
通过分析查询器我们可以找到SQL语句中耗时多的地方,从而进行优化。
选择类型剖析查询:
命令类型 | 解释 | 用法 |
---|---|---|
all | 显示所有性能信息 | show profile all; |
block io | 显示块IO次数 | show profile block io for query n; |
context switches | 显示上下文切换次数,被动/主动 | show profile context switches for query n; |
cpu | 显示CPU时间 | show profile cpu; |
ipc | 显示发送和接收消息的数量 | show profile ipc; |
page faults | 显示页错误数量 | show profile page faults for query n; |
source | 显示源码中的函数名称和位置 | show profile source; |
swaps | 显示swap的次数 | show profile swaps; |
2. Performance Schema
The MySQL Performance Schema is a feature for monitoring MySQL Server
execution at a low level. The Performance Schema has these
characteristics.
这是MySQL官网上对Performance Schema解释的一句话,大意是Performance Schema是一个运行在较低级别里的具有监控MySQL Server执行的功能。
具体可看:https://dev.mysql.com/doc/refman/8.0/en/performance-schema.html
用法:
通过select * from events_waits_current\G;
从表中信息可得到该表中有多少当前线程在运行以及线程运行的其它信息,用来显示每个线程的最新监视事件。
好处是Performance Schema
比show profile
功能更全,当从SQL语句执行时间等的执行过程分析中无法找到原因进行优化时,用Performance Schema
可以直接查看表中有哪些线程在运行,从每个线程的包含信息记录中找到耗时原因。
3. show processlist
使用show processlist
查看连接的线程个数,对长时间连接的、无用的线程可通过quit
进行关闭.
Id: 线程id(/session id)
User: 表示操作的用户。
Host: 表示操作的主机。
db: 表示操作的数据库。
Command: 表示命令的类型。
Time: 表示该命令执行的时间。
State: 表示命令执行的状态。
Info: 表示详细的SQL语句。