mysql SQL性能分析

1.索引-性能分析-查看执行频次
show global status like 'com_______';

2.索引-性能分析-慢查询日志
默认是超过10s的所有SQL语句的日志
mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | ON |
+----------------+-------+

cat /etc/my.cnf
配置文件里面需要配置
#慢查询配置
slow_query_log = ON
slow_query_log_file = /data/mysqldata/slowlog/slow_query.log
long_query_time = 2

3.索引-性能分析-show profiles
profile 详情
mysql> show variables like 'have_profiling';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| have_profiling | YES |
+----------------+-------+
mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES |
+------------------+
0显示暂时没有开启
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
开启profiling
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select profiling;
ERROR 1054 (42S22): Unknown column 'profiling' in 'field list'
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 1 |
+-------------+

#查看每一条SQL的耗时基本情况
mysql> SHOW PROFILES;
+----------+------------+--------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------------+
| 1 | 0.00038200 | select profiling |
| 2 | 0.00043750 | select @@profiling |
| 3 | 0.00397025 | show tables |
| 4 | 0.00087275 | select * from user |
| 5 | 0.00054875 | select * from user where name='root' |
| 6 | 0.00012400 | select * from user/G |
| 7 | 0.00086275 | SELECT * FROM USER |
+----------+------------+--------------------------------------+
7 rows in set, 1 warning (0.00 sec)

#查看指定query_id的SQL语句各个阶段的耗时情况
mysql> show profile for query 7;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000203 |
| Executing hook on transaction | 0.000013 |
| starting | 0.000019 |
| checking permissions | 0.000016 |
| Opening tables | 0.000095 |
| init | 0.000016 |
| System lock | 0.000022 |
| optimizing | 0.000014 |
| statistics | 0.000031 |
| preparing | 0.000038 |
| executing | 0.000249 |
| end | 0.000015 |
| query end | 0.000011 |
| waiting for handler commit | 0.000024 |
| closing tables | 0.000021 |
| freeing items | 0.000058 |
| cleaning up | 0.000022 |
+--------------------------------+----------+

#查看指定query_id的SQL语句CPU的使用情况
mysql> show profile cpu for query 7;
+--------------------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+--------------------------------+----------+----------+------------+
| starting | 0.000203 | 0.000117 | 0.000105 |
| Executing hook on transaction | 0.000013 | 0.000006 | 0.000006 |
| starting | 0.000019 | 0.000010 | 0.000009 |
| checking permissions | 0.000016 | 0.000008 | 0.000008 |
| Opening tables | 0.000095 | 0.000050 | 0.000045 |
| init | 0.000016 | 0.000008 | 0.000007 |
| System lock | 0.000022 | 0.000011 | 0.000010 |
| optimizing | 0.000014 | 0.000008 | 0.000007 |
| statistics | 0.000031 | 0.000016 | 0.000014 |
| preparing | 0.000038 | 0.000020 | 0.000018 |
| executing | 0.000249 | 0.000232 | 0.000000 |
| end | 0.000015 | 0.000014 | 0.000000 |
| query end | 0.000011 | 0.000012 | 0.000000 |
| waiting for handler commit | 0.000024 | 0.000023 | 0.000000 |
| closing tables | 0.000021 | 0.000021 | 0.000000 |
| freeing items | 0.000058 | 0.000058 | 0.000000 |
| cleaning up | 0.000022 | 0.000021 | 0.000000 |
+--------------------------------+----------+----------+------------+

4.索引-性能分析-explain执行计划
explain 执行计划各字段含义 可以参考deepseek
Id
select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下;id不同，值越大，越先执行)。
select type
表示SELECT的类型，常见的取值有SIMPLE(简单表，即不使用表连接或者子查询)、PRIMARY(主查询，即外层的查询)、UNION(UNION中的
第二个或者后面的查询语句)、SUBQUERY(SELECT/WHERE之后包含了子查询)等
type
表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、index、all
possible key
显示可能应用在这张表上的索引，一个或多个。
Key
实际使用的索引，如果为NULL，则没有使用索引。
Key len
表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
roWS
MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。
filtered
表示返回结果的行数占需读取行数的百分比，filtered 的值越大越好

mysql> explain select 'A';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+