mysql sql性能分析 mysql查询性能分析

目录

• 一、MySQL性能分析工具
• 二、MySQL性能优化常见问题

• SQL 优化步骤
• MySQL优化
• 数据库优化
• MySQL怎么优化全表扫描（table scan）
• MySQL 如何优化 DISTINCT

一、MySQL性能分析工具

数据库性能低时，优化过程应该从下往上考虑。

几种常用的性能分析工具：

1. 查看系统性能参数
   使用SHOW STATUS语句查询一些 MySQL 数据库服务器的性能参数和执行频率。
2. 统计SQL的查询成本
   使用 SQL 语句SHOW STATUS LIKE 'last_query_cost';查看上一条 SQL 语句的成本。
3. 慢查询日志
   使用SQL语句set global slow_query_log='ON';开启慢查询日志。
   使用慢查询分析工具 mysqldumpslow。
4. 查看SQL执行成本
   使用SQL语句show profiles;查看 SQL 执行成本。
5. EXPLAIN
   在 SQL 语句前加一个EXPLAIN，运行即可。
6. 分析优化器执行计划
   使用SQL语句select * from information_schema.optimizer_trace\G查看优化器执行计划。
7. MySQL监控分析视图
   使用MySQL监控分析视图 sys schema。

二、MySQL性能优化常见问题

SQL 优化步骤

1. 用show status命令了解各种 SQL 的执行频率。
2. 定位执行效率较低的 SQL 语句。
3. 通过Explain分析较低 SQL 的执行计划
4. 通过show profile分析 SQL。
5. 通过trace分析优化器如何选择执行计划。
6. 确定问题并采取相应的优化措施。

MySQL优化

1. SQL语句如何优化： Explain，看是否用了索引，如果没有进行索引优化、索引覆盖、索引下推。
2. 大表数据如何优化： （1）禁止不带限制条件的查询语句。（2）主从读写分离。（3）垂直拆分（分布式系统）、水平拆分。
3. 超大分页如何优化： （1）SELECT * FROM xxx WHERE xxx > 20 LIMIT 100000, 10会白加载100000条数据，可以改成SELECT * FROM xxx WHERE id IN (SELECT id FROM xxx WHERE xxx > 20 LIMIT 100000, 10)，这样用上了索引，快很多。（2）用Redis。
4. 慢查询怎么优化： （1）看是不是加载了多余的行。（2）看是不是没用上索引。（3）都不行就横向、纵向分表。
5. 查询过程中的数据访问如何优化： Explain，看是否用了索引，如果没有进行索引优化、索引覆盖、索引下推。
6. 关联查询如何优化： （1）确保ON和USING的子句中用上了索引。（2）确保GROUP BY和ORDER BY只用一个字段，这样才能用上索引。
7. 数据库结构如何优化： 空间换时间（1）反范式化，用冗余字段换时间。（2）表里使用频率低的字段分离出去。（3）经常JOIN的表建一个中间表。
8. CPU 负载过高如何优化： 可能突然有大量 session 进来，分析原因，限制连接数。】

数据库优化

1. 外键约束可能严重影响性能，甚至引起“数据库风暴”，如无必要不使用外键。
2. 索引优化（适合情况、不适合情况、失效情况、覆盖、下推）
3. 关联查询优化
4. 大表优化
5. UNION ALL要比UNION快很多，因为UNION ALL不用排序，所以如果不需要排序，就使用UNION ALL。

MySQL怎么优化全表扫描（table scan）

避免 7 种索引失效的情况。

MySQL 如何优化 DISTINCT

将DISTINCT在所有列上转换为GROUP BY，并与ORDER BY子句结合使用。