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）：

1. slow_query_log = 1
2. slow_query_log_file = /path/to/your/slow-query.log
3. 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：

1. 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实行操持分析性