Mysql语句性能优化
SQL查询过程
- 查询缓存： 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
- 分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
- 优化器： 按照 MySQL 认为最优的方案去执行。
- 执行器： 执行语句，然后从存储引擎返回数据。 执行语句之前会先判断是否有权限，如果没有权限的话，就会报错。
- 插件式存储引擎：主要负责数据的存储和读取，采用的是插件式架构，支持 InnoDB、MyISAM、Memory 等多种存储引擎。
创建高性能索引
什么是索引
数据库表中的一种数据结构，用于加速数据检索。通过创建索引，数据库可以更快速地找到所需的数据，而不需要扫描整个表。
为什么需要索引
MySQL 索引优化是提高数据库查询性能的重要手段。索引可以显著减少数据库需要扫描的数据量，从而加快查询速度。但索引的使用和管理也需要谨慎，错误的索引策略可能会导致性能下降或增加维护成本。
索引原理
数据结构：
- B+ 树：MySQL 的默认索引结构是 B+ 树，它是一种自平衡的树数据结构。B+ 树的每个节点包含多个键值和指向子节点的指针。B+ 树的叶子节点还包含数据指针或者数据行的 ID（在聚簇索引中）。在 B+ 树中，所有叶子节点处于同一层级，这使得查找、插入和删除操作的时间复杂度为 O(log N)。
- 哈希表：用于 HASH 索引（如 MEMORY 存储引擎），哈希表通过计算哈希值来直接访问数据，但不支持范围查询。
索引类型
- 主键索引（Primary Key Index）：唯一标识表中的每一行，自动创建在主键列上。
- 唯一索引（Unique Index）：确保索引列中的所有值都是唯一的。
- 普通索引（Regular Index）：最常用的索引类型，没有唯一性限制。
- 全文索引（Full-Text Index）：用于全文搜索，如搜索包含特定单词的文本。
- 组合索引（Composite Index）：索引多个列，可以加速对这些列的组合查询。
索引优化
- 唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
- 为经常需要排序、分组和联合操作的字段建立索引。
- 为常作为查询条件的字段建立索引。
- 限制索引的数目：越多的索引，会使更新表变得很浪费时间。
- 尽量使用数据量少的索引。如果索引的值很长，那么查询的速度会受到影响。
- 尽量使用前缀来索引，如果索引字段的值很长，最好使用值的前缀来索引。
- 删除不再使用或者很少使用的索引
- 最左前缀匹配原则，非常重要的原则。
- 尽量选择区分度高的列作为索引
- 索引列不能参与计算，保持列“干净”：带函数的查询不参与索引。
- 尽量的扩展索引，不要新建索引。
慢SQL优化
抓取慢SQL
启用慢查询日志
慢查询日志可以记录所有执行时间超过指定阈值的查询。
编辑 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 的内置工具或第三方监控工具进行实时查询分析：
- MySQL Workbench：提供了图形化的慢查询分析功能。
- Grafana + Prometheus：可以结合 MySQL Exporter 监控数据库性能，包括慢查询。
EXPLAIN 命令
使用 EXPLAIN 命令来分析 SQL 的 执行计划 。执行计划是指一条 SQL 语句在经过 MySQL 查询优化器的优化会后，具体的执行方式。
EXPLAIN 并不会真的去执行相关的语句，而是通过 查询优化器 对语句进行分析，找出最优的查询方案，并显示对应的信息。
EXPLAIN 适用于 SELECT, DELETE, INSERT, REPLACE, 和 UPDATE语句，我们一般分析 SELECT 查询较多。
- id:
描述: 查询的唯一标识符。对于多表查询，id 用于区分不同的 SELECT 语句。
分析: 数字越大，执行优先级越高。相同的 id 表示这些操作可以并行执行。 - select_type:
描述: 查询类型，如简单查询、联合查询、子查询等。
常见值:
- SIMPLE: 简单查询，不包含子查询或联合查询。
- PRIMARY: 主查询（包含子查询的最外层查询）。
- SUBQUERY: 子查询。
- DERIVED: 派生表（子查询的结果作为临时表）。
- table:
描述: 执行查询时访问的表名。
分析: 确保查询访问的表是期望的，检查是否有意外的表被访问。 - type:
描述: 访问类型，表示查询 MySQL 表的方式。
常见值:
- ALL: 全表扫描（性能最差）。
- index: 索引扫描（读取索引而不是实际表数据）。
- range: 范围扫描（使用索引的范围查找）。
- ref: 通过索引查找单个记录。
- eq_ref: 对每个索引值只返回一行记录。
- const: 常量（单行查找，性能最好）。
- possible_keys:
描述: 查询可能使用的索引列表。
分析: 确保查询能够利用到索引。如果没有显示预期的索引，可能需要检查索引的设计或优化查询条件。 - key:
描述: 实际使用的索引。
分析: 确认查询是否使用了期望的索引。未使用索引可能表示索引不适用或者索引未被使用。 - key_len:
描述: 使用的索引的长度。
分析: 该值帮助确定索引的使用效率。较短的长度通常表示使用了索引的部分字段。 - ref:
描述: 显示哪个列或常量与索引一起被使用。
分析: 确保使用的是索引的前缀列。比如 const 表示对每个索引值只返回一行记录，ref 表示用某个字段值来查找记录。 - rows:
描述: MySQL 估计的要扫描的行数。
分析: 这是 MySQL 预估的数量，通常用于比较不同执行计划的成本。较大的值可能表示查询效率低。 - Extra:
描述: 额外的执行信息。
常见值:
- Using where: 使用 WHERE 子句过滤记录。
- Using index: 使用覆盖索引（只从索引中获取数据）。
- Using filesort: 使用文件排序（可能导致额外的性能开销）。
参考：
MySQL索引优化（超详细）
MYSQL执行计划分析性