分析慢查询
分析慢查询是数据库性能优化的关键一环，一个慢查询可能导致应用响应延迟，用户体验下降，甚至拖垮整个系统。
开启慢查询日志
-
Mysql
-
在
my.cnf(或my.ini) 配置中设置：slow_query_log = 1 # 开启慢查询日志 slow_query_log_file = /var/log/mysql/mysql-slow.log # 日志文件路径 long_query_time = 1 # 慢查询阈值，单位秒。表示查询时间超过1秒的记录。 log_queries_not_using_indexes = 1 # 记录没有使用索引的查询 (可选，但非常有用) -
修改配置后重启 MySQL 服务。
-
也可以在运行时通过 SQL 命令设置，但重启后会失效：
SET GLOBAL slow_query_log = 'ON'; SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log'; SET GLOBAL long_query_time = 1; SET GLOBAL log_queries_not_using_indexes = 'ON';
-
定位慢查询语句
读取和分析慢查询日志是第一步
- 手动查看日志
- 直接打开日志文件查看，可以按时间或者执行时间排序
- 使用日志分析工具
- MySQL:
mysqldumpslow是 MySQL 官方提供的一个命令行工具，用于汇总和分析慢查询日志。mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log：按总耗时排序，显示前10条慢查询。mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log：按查询次数排序。mysqldumpslow -s r -t 10 /var/log/mysql/mysql-slow.log：按返回行数排序。
- Percona Toolkit (pt-query-digest): 更强大、更专业的慢查询日志分析工具，可以生成详细的报告，包括查询模板、执行次数、平均/最大/最小执行时间、锁等待时间、扫描行数等。
pt-query-digest /var/log/mysql/mysql-slow.log
- MySQL:
理解EXPLAIN计划
一旦定位到具体的慢查询语句，下一步就是使用数据库的 EXPLAIN (或 EXPLAIN ANALYZE、DESCRIBE) 命令来分析其执行计划。这是分析慢查询的核心步骤。
-
什么是执行计划？ 执行计划是数据库优化器为 SQL 查询生成的执行步骤蓝图。它展示了数据库如何访问表（全表扫描、索引扫描）、如何连接表（嵌套循环、哈希连接、合并连接）、如何排序、如何聚合等。
-
如何使用
EXPLAIN- 在慢查询语句前加上
EXPLAIN关键字 - mysql： explain select * from users where age > 30;
- 在慢查询语句前加上
-
执行计划的关键指标和解读
MySQL
EXPLAIN输出的重要列：id: select 查询的序列号，越大越优先执行。select_type: 查询类型（SIMPLE, PRIMARY, SUBQUERY, UNION等）。table: 正在访问的表。partitions: 匹配记录所在的分区。type: 连接类型/访问类型，这是最重要的指标之一，反映了表是如何被访问的。system: 表只有一行，是const类型的一个特例。const: 通过主键或唯一索引访问一行，非常快。eq_ref: 主键或唯一索引等值查找，用于多表连接，非常快。ref: 非唯一索引查找，返回匹配多行。range: 索引范围扫描，比全索引扫描好。index: 全索引扫描，比全表扫描快，但仍要扫描整个索引。all: 全表扫描，通常是性能瓶颈，应尽量避免。
possible_keys: 可能使用的索引。key: 实际使用的索引。key_len: 使用的索引的长度。ref: 哪些列或常量被用于查找索引列上的值。rows: 估算的扫描行数，越少越好。filtered: 表条件过滤出的行百分比。Extra: 额外信息，非常重要，指示了优化器执行查询的额外细节。Using filesort: 需要额外排序，通常可以优化。Using temporary: 需要使用临时表，通常可以优化。Using index: 覆盖索引，非常高效。Using where: 使用了 WHERE 子句过滤数据。Using join buffer: 使用了连接缓冲区。Using index condition: 索引条件下推，MySQL 5.6+ 特性。
优化策略
- sql优化
- 索引优化
- 数据库结构优化
- 字段类型优化：选择最合适，最小的数据类型。如果一个字段只存储0-255，用
TINYINT比INT更好。 - 范式与反范式： 适当进行反范式设计（数据冗余）以减少 JOIN 操作，但要权衡数据一致性。
- 分区表 (Partitioning)： 将大表分成更小的、可管理的部分，提高查询效率，尤其是在涉及时间范围查询时。
- 字段类型优化：选择最合适，最小的数据类型。如果一个字段只存储0-255，用
- 硬件和系统配置优化：
- 增加内存：提升缓存命中率，减少磁盘 I/O。
- 使用更快的存储设备：SSD 替代 HDD。
- 优化数据库配置参数：调整缓存大小 (如 MySQL 的
innodb_buffer_pool_size)、线程池、连接数等。 - 升级 CPU。
慢查询分析流程图：
+-------------------+ +---------------------+ +---------------------+
| 1. 开启慢查询日志 |----->| 2. 定位慢查询语句 |----->| 3. 分析 EXPLAIN 计划|
| - 设置阈值 | | - 查看日志 | | - 关键指标解读 |
| - 配置持久化 | | - 使用分析工具 | | - 查找瓶颈 |
+-------------------+ +---------------------+ +---------------------+
| |
| V
| +---------------------+ +-------------------+
+--------------------------->| 5. 持续监控和迭代 |<-----| 4. 应用优化策略 |
| - 性能监控 | | - 索引优化 |
| - 定期审查 | | - SQL语句优化 |
| - 灰度/测试 | | - 结构/配置优化 |
+---------------------+ +-------------------+
mysql优化，如何优化索引
对索引使用左或者左右模糊匹配
在mysql中，like模糊查询可能会导致性能问题，提别是当使用通配符%开头时，因为通常会导致全表扫描，也就是like %xx 或者 like %xxxx%这两种方式会造成索引失效
-
前缀匹配：一般使用`like 'prefix%' 的形式，这种情况mysql能够利用索引
-
对于一定需要匹配后缀的情况（即
%sufix）,可以创建一个辅助列存储反转字符串，并基于此列进行前缀匹配ALTER TABLE users ADD reversed_username VARCHAR(255); UPDATE users SET reversed_username = REVERSE(username); CREATE INDEX idx_reversed_username ON users(reversed_username);
计算（使用函数）
索引保存的是索引字段的原始值，而不是经过计算后的值，所以不能走索引
函数计算和表达式计算都不能走索引
//函数计算
select * from t_user where length(name)=6;
//表达式计算
select * from t_user where id + 1 = 10;
类型转换
数值类型禁止加引号，字符串类型必须加引号
- 如果索引字段是字符串类型，但是在条件查询中，输入的参数是整型的话，那么这条语句会走全表扫描
- 但是如果索引字段是整型类型，查询条件中的输入参数即是字符串，是不会导致索引失效，还是可以走索引扫描
原因是，在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较；如果索引字段是整型类型，查询条件中的输入参数是字符串，会自动转换成整型，所以索引不会失效。而索引字段是字符串，而输入的是整型，由于是字符串转数字，而索引不是整型类型，所以索引失效了。
联合索引非最左匹配
联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引匹配，否则就会导致索引失效
原因是，在联合索引的情况下，数据是按照索引第一列排序，第一列数据相同时才会按照第二列排序。
比如，如果创建了一个 (a, b, c) 联合索引，如果查询条件是以下这几种，就可以匹配上联合索引：
- where a=1；
- where a=1 and b=2 and c=3；
- where a=1 and b=2；
因为有查询优化器，所以 a 字段在 where 子句的顺序并不重要。
但是，如果查询条件是以下这几种，因为不符合最左匹配原则，所以就无法匹配上联合索引，联合索引就会失效:
- where b=2；
- where c=3；
- where b=2 and c=3；
对于where a = 1 and c = 0 这个语句，前面的a = 1是会走索引的，后面的c不走索引。
where条件的顺序影响索引使用吗
where条件的顺序可能会影响索引使用，但是，这种影响主要取决于mysql查询优化器的工作方式，而不是直接由where子句中条件的书写顺序决定的。
- mysql的查询优化器通常会尝试重新排序where条件以
最优化索引的使用 - 对于符合索引，需要遵循
最左前缀原则 - 虽然where条件的顺序不影响索引的使用，但是将索引列的条件放在前面会使查询计划更易读和理解
不应使用or
如果在or前的条件列是索引列，而在or后的条件列不是索引列，那么索引列会失效。
只有一个条件列是索引列是没有意义的，只要有条件列不是索引列，就会进行全表扫描
in
尽量使用IN代替OR，但是IN包含的值不应过多，应少于1000个
IN通常是走索引的，当in后面的数据在数据表中超过30%的匹配时是全表扫描，不会走索引
其实就是 Mysql优化器会根据当前表的情况选择最优解。 Mysql优化器认为走全表扫描 比 走索引+回表快 那就不会走索引
不等于、不包含不能用到索引的快速搜索
在索引上，避免使用NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等
not in一定不走索引吗？
答案是不一定。Mysql优化器会根据当前表的情况选择最优解。
主要在于如果 MySQL 认为 全表扫描 比 走索引+回表效率高， 那么他会选择全表扫描。
大表查询慢怎么优化？
某个表有近千万条数据，查询比较慢，如何优化？
当mysql单表记录数过大时，数据库性能会明显下降，一些常见的优化措施如下：
- 合理建立索引：在合适的字段上建立索引，例如在where和order by命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描
- 索引优化：sql优化，索引要符合最左匹配原则
- 建立分区：对关键字段建立水平分区，比如时间字段，若查询条件往往通过时间范围来进行查询，能提升不少性能
- 利用缓存：利用Redis缓存热点数据，提高查询效率
- 限定数据的范围。比如：用户在查询历史信息的时候，可以控制在一个月的时间范围内
- 读写分离。经典的数据库拆分方案，主库负责写，从库负责读
- 通过分库分表的方式进行优化，主要有垂直拆分和水平拆分
- 数据异构到es
- 冷热数据分离。几个月之前不常用的数据放到冷库中，最新的数据比较新的数据放到热库中
- 升级数据库类型，换一种能兼容MySQL的数据库（OceanBase、TiDB等）
什么时候索引失效反而提升效率
- 小表查询： 对于非常小的表，MySQL可能会选择全表扫描（忽略索引），因为全表扫描的开销可能比通过索引逐行查找还要低。在这种情况下，索引失效不会损害性能，反而简化了查询。
- 读取大部分或所有行： 当一个查询返回表中很大比例的行（如 30% 或更多）时，使用索引查找可能会耗时更多，因为数据库必须跳回主数据页以读取完整记录。全表扫描可能更有效，因为它可以逐行顺序读取数据。
- 低选择性索引： 如果索引列的选择性非常低，例如一个布尔型字段，许多行有相同的值，那么依赖索引可能会产生不必要的开销。全表扫描可以避免索引的搜索和回表开销。
- 频繁更新的表： 对于包含大量更新操作的表，索引的维护成本可能相对较高。尤其是在频繁更新索引列时，通过避免使用或减少复杂的索引可以减轻写操作的负担。
- 复杂查询的优化选择： 对于复杂的多表联接查询，优化器有时可以选择执行计划中不使用某个索引（或部分失效）以提高整体联接和计算效率。
- 数据分布与优化器误判： 在某些特定情况下，如果索引导致MySQL错误地估计数据分布或行数，手动禁用索引或提示优化器使用不同策略可能提升性能。
浙公网安备 33010602011771号