文章目录
前言
在SQL性能优化过程中，经常遇到的问题包括查询响应时间过长、数据库负载过高、不适当的索引策略以及查询语句的复杂性等。
SQL性能分析可以帮助我们发现潜在的性能问题，定位瓶颈所在，并提供相应的优化策略。通过深入分析SQL查询的执行计划、监控关键性能指标以及使用专业的性能分析工具，我们可以及时优化和改进数据库应用程序的性能。
一、查看库SQL类型频率
通过指定DQL语句（Data Query Language），可以查看某个数据库所有增删改查的次数。从而分析这个数据库在增删改查的操作中，哪几种操作的比例大。
使用以下命令进行查询：Com后面跟了7个下划线，表示查找Com后面跟了7个字符的属性和属性值
show global status like 'Com_______'; 下图数据可以看出，select操作明显是最多的，其次是更新操作。
二、找出慢SQL
慢SQL对数据库性能、应用程序响应时间和系统可用性都会产生很大影响，找出慢SQL是优化和改善数据库性能的开始。
1、慢查询日志
定位哪些sql语句执行效率低（默认超过10秒），会自动存储到日志中，方便分析。
查看是否开启
慢查询日志是默认关闭的，查看日志前要检查MySQL是否启用：
SHOW VARIABLES LIKE 'slow_query_log';
注意：
要执行上述查询，你至少需要具有以下权限之一：
- PROCESS：允许查看正在运行的进程。
- SUPER：允许执行高级操作，包括查看和更改服务器变量。
- SELECT：允许执行SHOW VARIABLES查询。
开启慢日志
先说为什么Mysql默认不开启慢查询日志，想必是有开发者的考量。开启日志后会造成的影响：
-
性能开销：记录每个查询的执行时间等信息需要额外的系统资源，包括磁盘IO和CPU时间。对于高负载的生产环境，可能会对数据库性能产生显著的影响。
-
磁盘空间消耗：日志不加管理会占用大量的磁盘空间，需要定期进行管理和维护。
我们可以手动启用慢查询日志，并根据实际需求进行配置和管理。
1.找到Mysql配置文件
需要进到服务器修改配置文件，通常位置是：
Linux：/etc/mysql/my.cnf
Windows：C:\ProgramData\MySQL\MySQL Server X.X\my.ini
2.修改配置文件
进入配置文件，输入以下命令查找指定配置
/[mysqld]在[mysqld]部分中添加或修改以下行（如果已存在则修改）：
slow_query_log = 1 // 启用慢查询日志long_query_time = 2 // 定义查询执行时间的阈值，单位为秒。这里设置为2秒，可以根据需要进行调整。修改完毕后保存并关闭配置文件，重启MySQL服务，以使更改生效。
3.查看慢日志路径
SHOW VARIABLES LIKE 'slow_query_log_file';
2、查看每条SQL的耗时
show profiles命令是MySQL中的一个诊断命令，用于显示执行过的语句或连接的性能分析信息。可以获取有关特定查询或连接的详细性能统计数据，以便进行性能分析和优化。
show profiles命令显示的是当前会话（当前数据库连接）中最近执行的SQL语句的性能分析信息。每次执行SQL语句后，相关的性能分析信息会被记录并保留在当前会话中，直到会话结束或重新启动。
开启profiles
profiling默认是关闭的，可以通过set语句开启
SET profiling=1;##查看是否开启SELECT @@have_profiling;操作命令
执行一系列的业务sql的操作，然后通过以下命令查看执行耗时：
1. 查看每一条sql的耗时基本情况
show profiles;
2. 查看指定Query_ID的SQL语句各个阶段的耗时
show profile for query xxxxx;
3. 查看指定Query_ID的SQL语句CPU情况
show profile cpu for query xxxxx;
三、查询SQL详细分析-explain
在查询语句前加上关键字explain，就可以监测到该语句执行过程中表的连接情况和顺序、是否用到了索引等信息。例如：
## 专门找了个复杂点的SQL举例explain select orguser.organization_name,orguser.id organization_id ,count(*) allnum from tid_alarm_detail ad right join ( select org.organization_name,org.id,user_id from tc_organization_user right join( select SUBSTRING(organization_name,5,3) organization_name ,id from tc_organization WHERE organization_name like '_年级%' and organization_name!='四年级（14期）' and is_delete =0 union select SUBSTRING(organization_name,1,3),id from tc_organization WHERE organization_name like '__期___' and is_delete=0 )org on tc_organization_user.organization_id=org.id WHERE is_delete=0 )orguser on ad.user_jifen_id = orguser.user_id and ad.orgazition=orguser.id where is_delete=0 and remark is not null AND create_time like (SELECT CONCAT(DATE_FORMAT(NOW(), '%Y-%m'),"%")) GROUP BY organization_name 查询结果如下：
explain命令输出列详解
- id: 表示查找中子句或表的顺序（id值越大越先执行；id相同的时候执行顺序从上到下）
- select_type: 查询的类型，例如简单查询、子查询、联合查询等。
- simple：简单表，没有表连接和子查询
- primary：主查询，即外层的查询
- union：union中的第二个或后面的查询
- subquery：select、where之后的子查询
- type：表示连接类型，性能由好到差：null、system、const、eq_ref、range、index、all
- ALL：全表扫描，需要遍历整个表。
- index：索引扫描，通过遍历索引树来获取数据。
- range：范围扫描，通过索引根据范围条件来获取数据。
- ref：基于非唯一索引的等值查询。
- eq_ref：基于唯一索引的等值查询。
- const：基于常量条件的查询，通常用于只返回一行结果的查询。
- system：基于系统表的查询，通常只有一行结果。
- possible_keys: 可能用到的索引，可以有多个
- key: 实际使用的索引（mysql自己选，也可以人为指定）
- key_len: 使用的索引的长度。
- ref: 表之间的连接条件。
- rows: 估计的要扫描的行数。
- filtered：返回结果的行数占扫描行数的百分比，越高表示命中率越大，越好。
- Extra:其他附加信息，如使用临时表、排序等，很多信息都是和索引相关的。
- Using index：表示查询使用了覆盖索引，即查询所需的数据可以直接从索引中获取，这样的结果是最好的。
- Using where：表示查询使用了WHERE子句进行筛选，即在检索数据之后，MySQL还需要进一步使用WHERE条件进行过滤。
- Using temporary：表示查询使用了临时表来完成某些操作，例如排序或分组操作。这可能会增加额外的内存和磁盘开销。
- Using filesort：表示查询使用了文件排序操作，即无法利用索引的顺序进行排序，需要将结果存储在临时文件中进行排序。
- Using index condition：表示查询使用了索引条件进行筛选，即通过索引来过滤数据而不需要访问实际的数据行。
- Using index for group-by：表示查询使用了索引来执行GROUP BY操作，从而避免了额外的排序操作。
- Using index for distinct：表示查询使用了索引来执行DISTINCT操作，从而避免了额外的排序和去重操作。
- Using index for order by：表示查询使用了索引来执行ORDER BY操作，从而避免了额外的排序操作。
- Using index for merge：表示查询使用了索引来执行合并操作，例如多个子查询的结果合并。
- Using sort_union()：表示查询使用了排序合并算法进行合并操作。
来源地址：https://blog.csdn.net/CharmaineXia/article/details/131572097
