对数据库的性能分析来说，就是要知道一个SQL在数据库中执行的有多快，从进到数据库，到出数据库，需要多长时间。
SQL执行频率
MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信
息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次：
| 1 2 3 | — session 是查看当前会话 ; — global 是查询全局数据 ; SHOW GLOBAL STATUS LIKE ‘Com_______’; |
执行结果：
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | mysql> SHOW GLOBAL STATUS LIKE ‘Com_______’; ERROR 2013 (HY000): Lost connection to MySQL server during query No connection. Trying to reconnect… Connection id: 4711 Current database: studydata +—————+——-+ | Variable_name | Value | +—————+——-+ | Com_binlog | 0 | | Com_commit | 1241 | | Com_delete | 14 | | Com_import | 0 | | Com_insert | 1027 | | Com_repair | 0 | | Com_revoke | 0 | | Com_select | 54453 | | Com_signal | 0 | | Com_update | 1759 | | Com_xa_end | 0 | +—————+——-+ 11 rows in set (0.05 sec) |
Com_delete: 删除次数Com_insert: 插入次数Com_select: 查询次数Com_update: 更新次数
我们可以在当前数据库再执行几次查询操作，然后再次查看执行频次，看看 Com_select 参数会不会变化。
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | mysql> SHOW GLOBAL STATUS LIKE ‘Com_______’; +—————+——-+ | Variable_name | Value | +—————+——-+ | Com_binlog | 0 | | Com_commit | 1241 | | Com_delete | 14 | | Com_import | 0 | | Com_insert | 1027 | | Com_repair | 0 | | Com_revoke | 0 | | Com_select | 54458 | | Com_signal | 0 | | Com_update | 1759 | | Com_xa_end | 0 | +—————+——-+ 11 rows in set (0.00 sec) |
通过上述指令，我们可以查看到当前数据库到底是以查询为主，还是以增删改为主，从而为数据库优化提供参考依据。 如果是以增删改为主，我们可以考虑不对其进行索引的优化。 如果是以查询为主，那么就要考虑对数据库的索引进行优化了。
那么通过查询SQL的执行频次，我们就能够知道当前数据库到底是增删改为主，还是查询为主。 那假如说是以查询为主，我们又该如何定位针对于那些查询语句进行优化呢？ 次数我们可以借助于慢查询日志。
接下来，我们就来介绍一下MySQL中的慢查询日志。
慢查询日志
慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。MySQL的慢查询日志默认没有开启，我们可以查看一下系统变量 slow_query_log。
| 1 2 3 4 5 6 7 | mysql> SHOW VARIABLES LIKE ‘slow_query_log’; +—————-+——-+ | Variable_name | Value | +—————-+——-+ | slow_query_log | ON | +—————-+——-+ 1 row in set, 1 warning (0.01 sec) |
可以看到我这里的慢查询日志是我已经打开过的，如果是OFF状态，要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：
| 1 2 3 4 | # 开启MySQL慢日志查询开关 slow_query_log=1 # 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志 long_query_time=2 |
配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息/var/lib/mysql/localhost-slow.log。
| 1 | systemctl restart mysqld |
然后，再次查看开关情况，慢查询日志就已经打开了。
测试
我们这里制造1000w数据模拟这个场景
导入数据
使用load data local infile导入数据，执行报错如下：
| 1 | Loading local data is disabled; this must be enabled on both the client and server sides |
属于mysql导入数据报错：local_infile服务器变量指示能否使用load data local infile命令。该变量为OFF时，禁用客户端的load data local infile命令，该变量默认为ON， 报错消失。
查看变量状态：
| 1 2 3 4 5 6 7 | mysql> show global variables like ‘local_infile’; +—————+——-+ | Variable_name | Value | +—————+——-+ | local_infile | OFF | +—————+——-+ 1 row in set, 1 warning (0.01 sec) |
解决方案：windows为my.ini文件，linux为my.cnf
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | [client] # 开启导入本地sql文件 local_infile = 1 [mysql] # 开启导入本地sql文件 local_infile = 1 [mysqld] # 开启导入本地sql文件 local_infile = 1 |
重启mysql服务
再次执行导入
| 1 2 | mysql> load data local infile ‘D:/tb_sku2.sql’ into table `tb_sku` fields terminated by ‘,’ lines terminated by ‘\n’; ERROR LOAD DATA LOCAL INFILE file request rejected due to restrictions on access. |
解决方案
| 1 2 | net stop mysql net start mysql |
使用mysql -u root -p --local-infile命令先登入MySQL
再次执行导入
已成功导入，本地测试用了5个文件，每个20w条，依次导入这5个，刚好制造好1000w数据
| 1 2 3 | mysql> load data local infile ‘D:/tb_sku1.sql’ into table `tb_sku` fields terminated by ‘,’ lines terminated by ‘\n’; Query OK, 2000000 rows affected, 1540 warnings (45.88 sec) Records: 2000000 Deleted: 0 Skipped: 0 Warnings: 1540 |
对比查询sql语句
| 1 2 3 4 5 6 7 8 9 10 11 12 | mysql>select * from tb_user; — 这条SQL执行效率比较高, 执行耗时 0.01sec 24 rows in set (0.01 sec) — 由于tb_sku表中, 预先存入了1000w的记录, count一次,耗时18.11 sec mysql> select count(*) from tb_sku; +———-+ | count(*) | +———-+ | 10000000 | +———-+ 1 row in set (18.11 sec) |
检查慢查询日志
最终我们发现，在慢查询日志中，只会记录执行时间超多我们预设时间（2s）的SQL，执行较快的SQL是不会记录的。
在windows中，我们可以通过my.ini中slow_query_log_file="DESKTOP-TPUK7BF-slow.log"配置，查看日志名称，
然后进入数据库Data保存目录找到这个文件打开即可看到慢日志记录
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe, Version: 8.0.29 (MySQL Community Server – GPL). started with: TCP Port: 3306, Named Pipe: MySQL Time Id Command Argument # Time: 2022-08-02T01:43:17.055439Z # User@Host: root[root] @ localhost [::1] Id: 9 # Query_time: 38.558903 Lock_time: 0.000002 Rows_sent: 0 Rows_examined: 0 use studydata; SET timestamp=1659404558; load data local infile ‘D:/tb_sku1.sql’ into table `tb_sku` fields terminated by ‘,’ lines terminated by ‘\n’; # Time: 2022-08-02T01:44:35.017395Z # User@Host: root[root] @ localhost [::1] Id: 9 # Query_time: 45.880232 Lock_time: 0.000002 Rows_sent: 0 Rows_examined: 0 SET timestamp=1659404629; load data local infile ‘D:/tb_sku2.sql’ into table `tb_sku` fields terminated by ‘,’ lines terminated by ‘\n’; # Time: 2022-08-02T01:47:25.934166Z # User@Host: root[root] @ localhost [::1] Id: 9 # Query_time: 43.595911 Lock_time: 0.000002 Rows_sent: 0 Rows_examined: 0 SET timestamp=1659404802; load data local infile ‘D:/tb_sku3.sql’ into table `tb_sku` fields terminated by ‘,’ lines terminated by ‘\n’; # Time: 2022-08-02T01:50:15.889409Z # User@Host: root[root] @ localhost [::1] Id: 9 # Query_time: 44.240190 Lock_time: 0.000004 Rows_sent: 0 Rows_examined: 0 SET timestamp=1659404971; load data local infile ‘D:/tb_sku4.sql’ into table `tb_sku` fields terminated by ‘,’ lines terminated by ‘\n’; # Time: 2022-08-02T01:51:40.856591Z # User@Host: root[root] @ localhost [::1] Id: 9 # Query_time: 43.998160 Lock_time: 0.000002 Rows_sent: 0 Rows_examined: 0 SET timestamp=1659405056; load data local infile ‘D:/tb_sku5.sql’ into table `tb_sku` fields terminated by ‘,’ lines terminated by ‘\n’; # Time: 2022-08-02T01:55:23.098781Z # User@Host: root[root] @ localhost [::1] Id: 9 # Query_time: 18.112484 Lock_time: 0.000002 Rows_sent: 1 Rows_examined: 0 SET timestamp=1659405304; select count(*) from tb_sku; |
那这样，通过慢查询日志，就可以定位出执行效率比较低的SQL，从而有针对性的进行优化。
profile详情
show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling
参数，能够看到当前MySQL是否支持profile操作：
| 1 | SELECT @@have_profiling; |
查询结果：
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | mysql> SELECT @@have_profiling; +——————+ | @@have_profiling | +——————+ | YES | +——————+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT @@profiling; +————-+ | @@profiling | +————-+ | 0 | +————-+ 1 row in set, 1 warning (0.00 sec) |
可以看到，当前MySQL是支持 profile操作的，但是开关是关闭的。可以通过set语句在session/global级别开启profiling：
| 1 | SET profiling = 1; |
再次查看
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | mysql> SET profiling = 1; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> SELECT @@have_profiling; +——————+ | @@have_profiling | +——————+ | YES | +——————+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT @@profiling; +————-+ | @@profiling | +————-+ | 1 | +————-+ 1 row in set, 1 warning (0.00 sec) |
开关已经打开了，接下来，我们所执行的SQL语句，都会被MySQL记录，并记录执行时间消耗到哪儿去了。 我们直接执行如下的SQL语句：
| 1 2 3 4 | select * from tb_user; select * from tb_user where id = 1; select * from tb_user where name = ‘白起’; select count(*) from tb_sku; |
执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时：
| 1 2 3 4 5 6 | — 查看每一条SQL的耗时基本情况 show profiles; — 查看指定query_id的SQL语句各个阶段的耗时情况 show profile for query query_id; — 查看指定query_id的SQL语句CPU的使用情况 show profile cpu for query query_id; |
查看每一条SQL的耗时情况:
| 1 2 3 4 5 6 7 8 9 10 11 12 | mysql> show profiles; +———-+————-+——————————————-+ | Query_ID | Duration | Query | +———-+————-+——————————————-+ | 1 | 0.00013000 | SELECT @@have_profiling | | 2 | 0.00013575 | SELECT @@profiling | | 3 | 0.00773350 | select * from tb_user | | 4 | 0.00026225 | select * from tb_user where id = 1 | | 5 | 0.00048050 | select * from tb_user where name = ‘白起’ | | 6 | 18.17035025 | select count(*) from tb_sku | +———-+————-+——————————————-+ 6 rows in set, 1 warning (0.00 sec) |
查看指定SQL各个阶段的耗时情况 :
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | mysql> show profile for query 6; +——————————–+———–+ | Status | Duration | +——————————–+———–+ | starting | 0.000055 | | Executing hook on transaction | 0.000003 | | starting | 0.000005 | | checking permissions | 0.000018 | | Opening tables | 0.001312 | | init | 0.000005 | | System lock | 0.000005 | | optimizing | 0.000005 | | statistics | 0.000009 | | preparing | 0.000012 | | executing | 18.168639 | | end | 0.000012 | | query end | 0.000004 | | waiting for handler commit | 0.000011 | | closing tables | 0.000019 | | freeing items | 0.000081 | | logging slow query | 0.000134 | | cleaning up | 0.000023 | +——————————–+———–+ 18 rows in set, 1 warning (0.00 sec) |
explain
EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。
语法:
| 1 2 | — 直接在select语句之前加上关键字 explain / desc EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ; |
如：
| 1 2 3 4 5 6 7 | mysql> explain select * from tb_user where id = 1; +—-+————-+———+————+——-+—————+———+———+——-+——+———-+——-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +—-+————-+———+————+——-+—————+———+———+——-+——+———-+——-+ | 1 | SIMPLE | tb_user | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +—-+————-+———+————+——-+—————+———+———+——-+——+———-+——-+ 1 row in set, 1 warning (0.00 sec) |
Explain 执行计划中各个字段的含义:
| 字段 | 含义 |
|---|---|
| id | select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下；id不同，值越大，越先执行)。 |
| select_type | 表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等 |
| table | 表名 |
| partitions | 指分区信息。数据库优化有分库、分表、分区；这里的分区值表的分区信息。 |
| type | 表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、 index、all 。 |
| possible_keys | 显示可能应用在这张表上的索引，一个或多个。 |
| key | 实际使用的索引，如果为NULL，则没有使用索引。 |
| key_len | 表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好 。 |
| ref | 显示索引的哪一列被使用了 |
| rows | MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。 |
| filtered | 表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好。 |
| Extra | 额外信息 |
id
通过id值可以知道sql语句执行顺序：
id不同：如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
id有相同有不同。
id相同
id 相同，执行顺序由上至下执行
id有不同
如果是子查询，id的序号会递增，id值越大优先级越高，越被执行。所以上例id=3先被执行，type=ALL做的是一个全表扫描
id有相同，有不同的
id 如果相同，可以认为是一组，从上至下顺序执行 在所有组中，id值越大，优先级越高，越先执行
select_type
根据此字段可以看到当前查询类型，select_type常用值：
SIMPLE简单的select查询，查询中不包含子查询或者UNION PRIMARY查询中若包含任何复杂的子部分，最外层查询标记SUBQUERY在SELECT或WHERE列表中包含了子查询DERIVED在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表UNION若第二个SELECT出现在UNION之后，则被标记为UNION,若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVEDUNION RESULT：从UNION表获取结果的SELECT
table
显示这一行数据是关于哪一个表查询。
derived 临时表
type
- 表示访问类型，访问类型理论上如下表，排序越靠前性能越好：1
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL - 但我们工作中常接触到：ALL,index,rang,ref,eq_ref,const,system,NULL1
2
显示查询使用类型从最好到最差依次：
system>const>eq_ref>ref>range>index>ALL - 一般来说，你的SQL语句至少得保证达到range级别，最好达到ref。
- 各类型解释：
- system :表只有一行（等于系统表）这是const类型的特列，平时不会出现，这个也可以忽略不计
const: 表示通过索引一次就找到了，const用于比较primary key或unique索引，因为只匹配一行数据，所以很快如将主键放置于where列表中，MySQL就能将该查询转换为一个常量。
eq_ref : 唯一性索引扫描，对于每个索引建，表中只有一条记录与之匹配，常见于主键或唯一索引的扫描
ref : 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问。它返回所有匹配某个单独值的行，然而它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体。(工作中能达到这个就很牛逼了)
range : 只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就在你的where语句中出现between,<,>,in等的查询，这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。
index： Full Index Scan,index与ALL区别为index类型只遍历索引树，这通常比ALL快，因为索引文件通常比数据文件小，（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读取）
all : Full Table Scan,将遍历全表以找到匹配的行。
possible_keys 和 key
用于判断是否使用索引，是否索引失效，你的sql是使用哪个索引。
| 1 2 3 4 | possible_keys 显示可能应用在这张表中的索引，一个或多个 查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。 key 实际使用的索引，如果为NULL 则没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在key列表中,优先使用普通索引，然后使用主键索引 |
当然一般情况下都是出现理论索引，再看有没有实际用到索引，但个别情况下也会出现理论上没有索引，实际上使用覆盖索引：
key_len
表示索引中使用的字节数，可通过该列计算查询中使用索引的长度，在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得到，不是通过表内检索出的。
同样查询结果，精度越小，key_len越小，查询效果越好。
ref
显示索引的哪一列被使用了，如果可能的话，是一个常量，哪些列被用于查找索引列上的值。
rows
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数
如图，可以看到建立索引和没有建立索引所扫描行数区别。
Extra
额外信息，它的显示十分重要。
Using filesort （九死一生）
通过如下改进性能：
说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，MySQL中无法利用索引完成的排序操作成为“文件排序”。(出现这个就代表SQL不好)
Using temporay (十死无生)
新建了内部临时表，保存中间结果，MySQL在对查询结果排序使用临时表，常见于order by 和分组查询 group by，因创建临时表大大降低了性能。
优化改进：
Using index
表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错！如果同时出现using where,表明索引被用来执行索引键值查找。如果没有同时出现using where ，表示索引用来读取数据而非执行查找动作。
Using where
- 使用了where过滤
Using join buffer
- 使用了连接缓存impossible where
- where子句值总是false,不能用来获取任何元组
- select查询错乱了。1
2
— 当你执行name即等于July又等于z3导致SQL混乱：
explain select * from staffs where name=’July’ and name=’z3′;
select tables optimized away
- 在没有GROUPBY 子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
dictinct
- 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。
