Mysql语句性能优化

严文文-Chris

已于 2024-08-24 20:49:35 修改

阅读量1.1k

点赞数 12

文章标签： mysql

于 2024-08-23 21:44:26 首次发布

本文链接：https://blog.csdn.net/yanwenwennihao/article/details/141475341

版权

SQL查询过程

在这里插入图片描述

数据库表中的一种数据结构，用于加速数据检索。通过创建索引，数据库可以更快速地找到所需的数据，而不需要扫描整个表。

MySQL 索引优化是提高数据库查询性能的重要手段。索引可以显著减少数据库需要扫描的数据量，从而加快查询速度。但索引的使用和管理也需要谨慎，错误的索引策略可能会导致性能下降或增加维护成本。

数据结构：

B+ 树：MySQL 的默认索引结构是 B+ 树，它是一种自平衡的树数据结构。B+ 树的每个节点包含多个键值和指向子节点的指针。B+ 树的叶子节点还包含数据指针或者数据行的 ID（在聚簇索引中）。在 B+ 树中，所有叶子节点处于同一层级，这使得查找、插入和删除操作的时间复杂度为 O(log N)。
哈希表：用于 HASH 索引（如 MEMORY 存储引擎），哈希表通过计算哈希值来直接访问数据，但不支持范围查询。

慢查询日志可以记录所有执行时间超过指定阈值的查询。
编辑 MySQL 配置文件（通常是 my.cnf 或 my.ini）：

slow_query_log = 1
slow_query_log_file = /path/to/your/slow-query.log
long_query_time = 2

slow_query_log: 启用慢查询日志（1 表示启用，0 表示禁用）。
slow_query_log_file: 指定日志文件的路径。
long_query_time: 设置慢查询的时间阈值（单位为秒），例如 2 表示记录执行时间超过 2 秒的查询。

可以直接查看配置的日志文件，或者使用工具来分析日志，例如 mysqldumpslow 或 pt-query-digest（来自 Percona Toolkit）。
使用 mysqldumpslow：

mysqldumpslow -s t -t 10 /path/to/your/slow-query.log

-s t：按时间排序。
-t 10：显示前 10 条最慢的查询

可以使用 MySQL 的内置工具或第三方监控工具进行实时查询分析：

使用 EXPLAIN 命令来分析 SQL 的执行计划。执行计划是指一条 SQL 语句在经过 MySQL 查询优化器的优化会后，具体的执行方式。

EXPLAIN 并不会真的去执行相关的语句，而是通过查询优化器对语句进行分析，找出最优的查询方案，并显示对应的信息。

EXPLAIN 适用于 SELECT, DELETE, INSERT, REPLACE, 和 UPDATE语句，我们一般分析 SELECT 查询较多。

在这里插入图片描述

id:
描述: 查询的唯一标识符。对于多表查询，id 用于区分不同的 SELECT 语句。
分析: 数字越大，执行优先级越高。相同的 id 表示这些操作可以并行执行。
select_type:
描述: 查询类型，如简单查询、联合查询、子查询等。
常见值:

possible_keys:
描述: 查询可能使用的索引列表。
分析: 确保查询能够利用到索引。如果没有显示预期的索引，可能需要检查索引的设计或优化查询条件。
key:
描述: 实际使用的索引。
分析: 确认查询是否使用了期望的索引。未使用索引可能表示索引不适用或者索引未被使用。
key_len:
描述: 使用的索引的长度。
分析: 该值帮助确定索引的使用效率。较短的长度通常表示使用了索引的部分字段。
ref:
描述: 显示哪个列或常量与索引一起被使用。
分析: 确保使用的是索引的前缀列。比如 const 表示对每个索引值只返回一行记录，ref 表示用某个字段值来查找记录。
rows:
描述: MySQL 估计的要扫描的行数。
分析: 这是 MySQL 预估的数量，通常用于比较不同执行计划的成本。较大的值可能表示查询效率低。
Extra:
描述: 额外的执行信息。
常见值: