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 查询较多。