性能优化 - 数据库优化

SQL 性能优化

慢 SQL 常见诱因

无索引

对于以下场景，通常需要建立索引：

• 字段频繁作为查询条件
• 排序字段（避免 filesort）（在 MySQL 8.0 之前，如果不需要排序，需显示指定 "ORDER BY NULL" 来避免 filesort；默认使用内存临时表，看情况适当增大 tmp_table_size 以避免使用磁盘临时表，或指定 SQL_BIG_RESULT 来提示直接用磁盘临时表）
• 分组字段
• 关联字段，如外键
• 高并发（一般使用组合索引）
• 表数据量大

不是所有场景都需要建立索引，对于以下场景，则不应建立索引：

• 查询条件中不用的字段
• 字段唯一性差（如性别）
• 频繁更新的字段（维护索引会消耗一定的资源和时间）
• 列中包含许多重复的内容
• 列中包含大数（数值）或者 NULL 值
• 表数据量小

索引失效

• 非选择性的索引，指索引列的数据分布非常不均匀，即某个值在索引列中出现的频率非常高
• 使用函数、类型转换或表达式操作索引列，索引是建立在原始列上的，对索引列进行操作会破坏索引的有序性
• 索引列字符串编码不一致（原因同上）
• 不满足索引最左前缀规则：在复合索引中，只有满足索引最左前缀规则的查询才能充分利用索引（如果都用到了，只是顺序不一致，查询引擎会自动优化为匹配联合索引的顺序）
• 以 '%' 开头的 LIKE 查询
• 以 '%' 结尾的关联 LIKE 查询（如：'SELECT * FROM menu t1 INNER JOIN menu t2 ON t2.code LIKE CONCAT(t1.code, '%') WHERE t1.id = 1'）
• OR 查询前列有索引，OR 查询后的列没有索引（会导致所有索引都会失效，使用 UNION ALL 替代）
• ORDER BY 多个字段，且方向不一致（可以建立倒序索引）
• IS NULL、IS NOT NULL，由 MySQL 评估是否使用索引
• MySQL 评估全表扫描比使用索引快
• 不等于查询（!= 或者 <>）（MySQL 8.0 有效）
• NOT IN 查询（MySQL 8.0 有效）

选错索引

由于以下原因，MySQL 在选择索引时可能会出现选择错误索引的情况：

• 统计信息不准确：MySQL 使用统计信息来评估查询执行计划并选择最佳索引。如果统计信息不准确或过期，MySQL 可能会基于错误的数据做出索引选择。可以通过手动更新统计信息或自动化的统计信息收集任务来解决这个问题。
• 索引选择性不足：选择性是指索引列中不同值的数量与总行数的比例。如果索引选择性较低，即索引列的值分布不均匀，MySQL 可能会错误地选择了一个效果较差的索引。在这种情况下，可以考虑重新设计索引或添加更多的索引来提高选择性。
• 索引列顺序不合适：对于复合索引，MySQL 会根据查询条件中的列顺序选择索引。如果查询条件中的列顺序与索引列的顺序不匹配，MySQL 可能会选择错误的索引。此时，可以考虑重新定义索引列的顺序或创建新的索引来解决问题。
• 强制索引使用：有时，开发人员可能会使用 "FORCE INDEX" 或 "USE INDEX" 等提示来强制 MySQL 使用特定的索引。但是，这些提示可能会导致错误的索引选择，因为 MySQL 会忽略其他可能更好的索引。在使用这些提示时，需要仔细评估其对查询性能的影响。
• 查询优化器的限制：MySQL 的查询优化器负责选择最佳的执行计划。然而，由于查询优化器的复杂性，它可能无法在所有情况下做出最佳决策，从而选择了错误的索引。

要解决选择错误索引的问题，可以考虑以下方法：

• 更新统计信息以确保其准确性（使用 ANALYZE TABLE 或 OPTIMIZE TABLE，要查看统计信息则可以使用 'SHOW TABLE STATUS WHERE Name='admin_user';'）。
• 重新设计或添加索引来提高选择性。
• 调整查询语句中的列顺序以匹配索引列的顺序。
• 避免不必要的强制索引提示。
• 评估和调整查询语句以改善查询优化器的决策。

不恰当的 SQL 语句

• SELECT *

• • SELECT * 会消耗更多的 CPU
  • SELECT * 无用字段增加网络带宽资源消耗，增加数据传输时间，尤其是大字段
  • SELECT * 无法使用覆盖索引的优化
  • SELECT <字段列表> 可减少表结构变更带来的影响

• COUNT(字段)

• • 按照性能排序，COUNT(*) = COUNT(1) > COUNT(主键) > COUNT(字段)

• 在大数据表中使用 <LIMIT M,N>

• • MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后放弃前 offset 行，返回 N 行，那么当 offset 特别大的时候，效率就非常的低下。

• 对非索引字段进行过滤或排序

锁等待

锁等待是指当一个事务请求获取某个资源的锁时，但该资源已被其他事务锁定，从而导致请求的事务被阻塞，等待锁的释放。这种情况下，请求事务将进入锁等待状态，直到锁可用或超时。

INSERT、UPDATE、DELETE、SELECT … FROM … FOR UPDATE 在执行时：

• 最终（如果成功的话）会在行上添加排它锁（X 锁）；
• 在添加 X 锁之前，会先查看表上是否已有其它事务持有了意向排它锁（IX 锁）

• • 如果是，且当前操作需要获取表锁，则当前事务将一直等待，直到其它事务释放 IX 锁；
  • 否则，再查看行上是否已有其它事务持有了 X 锁

• • • 如果是，则当前事务将一直等待，直到其它事务释放 X 锁
    • 否则，添加 X 锁，同时添加 IX 锁

排它锁（X 锁）用于保证对数据的独占访问，意向排它锁（IX 锁）则用于减少锁的竞争和冲突，提高并发性能。

意向锁属于表锁，它不与行锁冲突，且任意两个意向锁之间也不会产生冲突，但是会与表锁（S 锁和 X 锁）产生冲突，兼容性如下：

兼容性	S 锁	X 锁
IS 锁	兼容	冲突
IX 锁	冲突	冲突

在未创建索引的情况下，下面语句 1（命中记录）会阻塞语句 2（不管有没有命中记录），语句 2 却不会阻塞语句 1。

1. UPDATE admin_user SET username = '333' WHERE username = '111';
2. SELECT * FROM admin_user WHERE username = '999' FOR UPDATE;

可以通过以下查询语句来查看锁的类型和状态：

SELECT * FROM performance_schema.data_locks;

慢 SQL 监控

相关系统变量

参数名	含义
slow_query_log	是否启用慢查询日志， ON 为启用，OFF 为没有启用，默认为 OFF
slow_query_log_file	指定慢查询日志文件的路径和文件名
long_query_time	执行时间超过该值才记录到慢查询日志，单位为秒，默认为 10
log_output	日志输出位置，默认为 FILE，即保存为文件。若设置为 TABLE，则将日志记录到 mysql.show_log 表中，支持设置多种格式

我们可以通过以下命令查看上述系统变量：

show variables like 'slow_query_log%';
show variables like 'long_query_time';
show variables like 'log_output';

启用慢查询

我们可以执行以下命令来启用慢查询：

set global slow_query_log = on;
set global long_query_time = 10;
set global log_output = 'FILE,TABLE';

但如果想要永久生效，则需要到配置文件中配置，否则数据库重启后，以上配置会失效。

修改配置文件 /ect/my.cnf，在 [mysqld] 段落中加入如下参数：

[mysqld]
slow_query_log='ON'
long_query_time=10
log_output='FILE,TABLE'

SQL 性能分析

EXPLAIN

EXPLAIN 是一个用于分析查询语句的关键字。它可以帮助开发人员和数据库管理员更好地了解 MySQL 执行查询的方式，以及如何优化查询性能。具体来说，EXPLAIN 可以提供以下信息：

• 查询语句的执行计划：显示 MySQL 如何执行查询语句，包括使用了哪些索引、哪些连接方式等。
• 每个表的访问方式：显示每个表在查询中的访问方式，包括全表扫描、索引扫描、范围查找等。
• 查询语句的性能指标：显示查询语句的一些性能指标，如扫描行数、排序方式、使用缓存等。

字段说明

• id：查询的标识符，如果查询包含子查询，每个子查询都有一个唯一的 id。执行顺序如下：

• • id 相同，执行顺序从上之下；
  • id 不同，执行顺序从大到小。

• select_type：查询类型。

• • SIMPLE：简单查询，不使用 UNION 和子查询；
  • PRIMARY：查询中包含任何复杂的子部分，最外层的 SELECT 被标记为 PRIMARY。
  • SUBQUERY：子查询中的第一个 SELECT 语句；
  • DEPENDENT SUBQUERY：子查询中第一个 SELECT 语句，取决于外查询；
  • UNION：UNION 查询中后面的 SELECT 语句；
  • UNION RESULT：UNION 的结果；
  • DEPENDENT UNION：UNION 查询中后面的 SELECT 语句，取决于外查询；
  • DEPENDENT UNION RESULT：DEPENDENT UNION 的结果；
  • DERIVED：派生表的 SELECT 语句（FROM 或 JOIN 子句的子查询）。有些语句 MySQL 会自动优化为 SIMPLE 类型；
  • MATERIALIZED：表示使用了临时表存储的子查询结果。

• table：输出的行所引用的表。有时不是真实的表的名字（虚拟表），虚拟表最后一位是数字，代表是 id 为多少的查询。
• type：访问表的方式。其值直接反映了 SQL 的性能是否高效。这个字段值较多，只需重点关注开发中经常用到的几个值，性能由高到低有：system、const、eq_ref、ref、ref_or_null、range、index、all。（ 通常，SQL 优化至少要达到 range 级别，要求是 ref 级别，如果可以是 const 最好）

• • system：表只有一行记录，这个是 const 的特例，一般不会出现，可以忽略；
  • const：表示使用了常量级别的索引访问。当查询条件中包含了主键或唯一索引列，并且查询条件使用了 = 进行匹配时，MySQL 会选择使用常量级别的索引访问。这种访问方式直接通过索引访问单个记录，因此无需扫描任何其他记录（避免全表扫描和索引扫描），查询速度非常快。
  • eq_ref：唯一索引扫描，表中只有一条记录与之匹配。一般是两表关联，关联条件中的字段是主键或唯一索引；
  • ref：非唯一行索引扫描，返回匹配某个单独值的所有行；
  • ref_or_null: 类似 ref，但可以搜索值为 NULL 的行;
  • range：检索给定范围的行，一般是条件查询中出现了 >、<、in、between 等查询；
  • index：遍历索引树。通常比 all 快，因为索引文件通常比数据文件小。ALL 和 index 都是读全表，但 index 是从索引中检索的，而 all 是从硬盘中检索的；
  • all：遍历全表以找到匹配的行；

• possible_keys：可能使用的索引。
• key：实际使用的索引。
• key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。一般来说，索引长度越长表示精度越高，效率偏低；长度越短，效率高，但精度就偏低。并不是真正使用索引的长度，是个预估值。
• rows：大致估算出找到所需的记录所需要读取的行数。
• filter：表示选取的行和读取的行的百分比，100 表示选取了 100%，80 表示读取了 80%。
• extra：一些重要的额外信息。

• • Using filesort：使用外部的索引排序，而不是按照表内的索引顺序进行读取。一般需要优化；
  • Using temporary：使用了临时表保存中间结果。常见于 order by 和 group by。最好优化；
  • Using index：表示 SELECT 语句中使用了覆盖索引，直接从索引中取值，而不需要回表（从磁盘中取数据）；
  • Using where：使用了 WHERE 过滤；
  • Using index condition：5.6 之后新增的，表示查询的列有非索引的列，先判断索引的条件，以减少磁盘的 IO；
  • Using join buffer：使用了连接缓冲区（连接缓冲区的作用是将连接操作需要的数据加载到内存中，然后通过比较连接条件来进行连接操作。使用连接缓冲区可以避免频繁的磁盘访问，提高连接操作的速度）；
  • impossible where：WHERE 子句的值总是 false。

Show Profile

SHOW PROFILE 是 MySQL 提供的一个用于性能分析的命令，它可以显示查询的详细执行过程和各个阶段的耗时情况。通过 SHOW PROFILE 命令，可以获取有关查询的状态、持续时间、CPU 时间、磁盘 I/O 等信息，帮助我们深入了解查询的执行性能。

使用 SHOW PROFILE 的基本步骤如下：

1. 启用查询分析器：

在执行查询之前，需要启用查询分析器。可以使用命令 SET profiling = 1; 来启用查询分析器。

1. 执行查询语句：

使用正常的 SELECT、INSERT、UPDATE 或 DELETE 语句执行查询。

1. 查看分析结果：

执行完查询后，可以使用 SHOW PROFILES 命令查看当前会话中执行的查询的列表。该命令将返回查询的 ID 和持续时间。

1. 显示查询的详细信息：

使用 SHOW PROFILE 命令并指定查询的 ID（通过 SHOW PROFILES 获取）来显示查询的详细执行信息。例如：SHOW PROFILE FOR QUERY 1;

1. 分析查询的执行过程：

SHOW PROFILE 命令将返回查询的不同阶段的执行时间和资源消耗等信息，如发送查询、解析查询、优化查询、执行查询等。

可以根据这些信息来评估查询的性能瓶颈，如是否存在较长的执行时间、高 CPU 使用率、频繁的磁盘 I/O 等。

1. 关闭查询分析器：

在完成性能分析后，可以使用命令 SET profiling = 0; 来关闭查询分析器。

需要注意的是，SHOW PROFILE 命令提供了一种简单快速的性能分析方法，但它的粒度相对较粗，无法提供像 Performance Schema 那样详细的性能数据。因此，在 MySQL 8 及更新版本中，更推荐使用 Performance Schema 进行更细粒度的性能分析和优化。

Performance Schema

Performance Schema 是 MySQL 提供的一个用于性能分析和诊断的工具。它提供了一组视图和表，用于收集和展示关于数据库服务器内部操作的性能数据。通过 Performance Schema，可以深入了解数据库服务器的运行情况，包括查询语句的执行、锁等待、I/O 操作、线程活动等，从而帮助进行性能分析、优化和故障排除。

Performance Schema 的主要特点和功能如下：

• 细粒度的性能数据收集：Performance Schema 可以收集到非常详细的性能数据，包括查询语句的执行时间、CPU 使用、磁盘 I/O、锁等待、线程活动等。它提供了多个视图和表，每个表都代表了不同类型的性能事件或对象。
• 动态配置：可以通过修改 Performance Schema 的配置选项来调整数据收集的粒度和范围。可以选择性地开启或关闭不同类型的性能事件，以满足具体的性能分析需求。
• 查询和分析性能数据：使用 SQL 查询和聚合函数，可以从 Performance Schema 的视图和表中检索和分析性能数据。可以编写自定义的查询语句，以获取关于查询语句、等待事件、锁等待、I/O 等方面的统计信息。
• 与其他工具的集成：Performance Schema 可以与其他 MySQL 工具和扩展进行集成，如 EXPLAIN、MySQL Workbench、sys schema 等。通过结合这些工具，可以更全面地分析和优化数据库性能。
• 监控和诊断：Performance Schema 可以用于实时监控数据库服务器的性能。可以设置定期查询 Performance Schema 数据，并将其与历史数据进行比较，以检测潜在的性能问题或异常情况。此外，还可以使用 Performance Schema 进行故障排除，查找导致性能问题的原因。

要使用 Performance Schema 进行更细粒度的性能分析和优化，可以按照以下步骤进行：

1. 启用 Performance Schema：

• 确保 MySQL 配置文件中的 performance_schema 参数被设置为 ON。如果没有设置，可以编辑配置文件（如 my.cnf 或 my.ini）并添加或修改该参数。
• 重启 MySQL 服务器以使配置生效。

1. 配置 Performance Schema：

• Performance Schema 的默认配置可能会收集大量的性能数据，因此可以根据需要进行适当的配置。
• 可以使用 SET 语句动态调整 Performance Schema 的配置选项，如设置收集事件的类型、数量限制等。
• 可以通过修改 MySQL 配置文件来永久更改 Performance Schema 的配置。

1. 查询 Performance Schema 数据：

• 使用 Performance Schema 提供的视图和表来查询性能数据。常用的一些视图和表包括：

• • performance_schema.events_statements_*：查询语句执行的统计信息。
  • performance_schema.events_waits_*：等待事件的统计信息。
  • performance_schema.table_io_waits_summary_by_table：按表统计的 I/O 等待信息。
  • performance_schema.mutex_instances：互斥锁实例的信息。
  • 等等，根据具体需求选择合适的视图和表进行查询。

1. 分析性能数据：

• 使用 SQL 查询和聚合函数对 Performance Schema 数据进行分析。可以根据需要编写查询语句，例如查找最耗时的查询语句、最频繁的等待事件等。
• 根据分析结果识别潜在的性能问题，如高延迟查询、频繁的锁等待等。

1. 优化性能：

• 根据性能分析结果采取相应的优化措施。例如，可以考虑优化查询语句、添加或修改索引、调整数据库配置参数等来改善性能。
• 还可以使用其他工具和技术，如 EXPLAIN 分析查询执行计划、使用索引提示来指定索引、使用缓存等来进一步优化性能。

需要注意的是，Performance Schema 的配置和使用可能会对系统性能产生一定的影响，特别是在收集大量数据时。因此，在配置和使用时要谨慎，并根据具体情况进行调整。此外，深入理解 Performance Schema 的各个视图和表的含义和用法，以及合理地使用查询语句和聚合函数，对于有效地利用 Performance Schema 进行性能分析和优化也非常重要。

SQL 性能优化

SQL 语句优化

• 优化分页查询

• • 若 count 为 0 应直接返回，避免执行后面的分页语句
  • 利用子查询优化分页查询。如可以先快速定位需要获取的 id 段，然后再关联查询：

• • • SELECT t1.* FROM 表1 as t1 , (select id from 表 1 where 条件 LIMIT 100000 , 20) as t2 where t1.id = t2.id。

• 优化 SELECT 查询

• • 只查询需要的字段
  • 利用覆盖索引避免回表查询

• 优化 COUNT 查询

• • 使用 COUNT(*) 替代 COUNT(字段)

• 避免在 WHERE 子句中对字段进行 null 值判断

• • 会导致 MySQL 放弃使用索引而进行全表扫描

• 避免复杂的关联查询

• • 考虑将其拆分成多个较简单的查询，以减少每个查询涉及的数据量，从而提高查询的执行效率

• 使用 WITH RECURSIVE 替换传统 Like 方式查询上下级

索引优化

• 使用覆盖索引
• 对于字符串类型（值较长）字段，考虑使用：

• • 前缀索引
  • 倒序存储
  • Hash 字段索引

• 优先选择长度较短的数据类型
• 重建索引

• • 重建索引：

• • • 普通索引：

• • • • ALTER TABLE table_name DROP INDEX index_name;
      • ALTER TABLE table_name ADD INDEX index_name(columns);

• • • 主键索引：

• • • • ALTER TABLE table_name engine=InnoDB;

• • 分析表：ANALYZE Table table_name;
  • 重建表：OPTIMIZE Table table_name;

高并发事务优化

• 结合业务场景，使用低级别事务隔离
• 控制事务的大小，减少锁定的资源量和时间长度
• 如果事务中需要锁多个行，把最可能造成锁冲突、最可能影响并发度的锁尽量往后放；
• 尽量按照一致的顺序来处理表记录；
• 避免行锁升级表锁

• • 通过索引条件检索数据

• 使用自增主键

• • （写入）性能最高：乱序写入会产生更多的 I/O 操作、更多的页分页操作、以及碎片空间
  • 能用于优化大数据量分页查询
  • 节省磁盘空间：自增主键一般是 int（数据库自增 ID） 或 long（雪花 ID） 类型，占用 4 或 8 位字节，而 GUID 一般占 36(32) 位字节
  • 节省流量：原因同上

数据库自增 ID 弊端：

• 可编程性较差：对于那些需要在插入数据库前就指定 ID 的场景，如赋值外键，使用数据库自增 ID 要多与数据库产生一次交互
• 可扩展性较差：易导致主键重复，如分库分表场景，又或在导入就旧数据时又有新数据插入
• 安全性较差：别人可以根据自增 ID 获取到业务增长信息，从而分析出企业经营情况

改进：使用雪花 ID（支持分布式）

使用数据库连接池

数据库连接是一种关键的、有限的、昂贵的资源。对数据库连接的管理能显著影响到整个应用程序的伸缩性和健壮性，影响到程序的性能指标。

最大连接数设置多大合适？

要确定适合的值，需要考虑以下几个因素：

• 数据库服务器的最大连接数限制

• • 查看最大连接数限制：show variables like 'max_connections';
  • 查看最高同时使用的连接数：show status like 'Max_used_connections';
  • 一般来说，最大连接数应设置为上限连接数的 85% 左右。

• 应用程序的并发需求
• 硬件资源
• 连接泄漏和空闲连接

推荐的公式：((core_count * 2) + effective_spindle_count)

• core_count：CPU 核心数
• effective_spindle_count：挂载的磁盘数量

分库分表

分库分表方式

• 垂直分库分表
• 水平分库分表

分库分表问题

• 分布式事务问题

• • 使用分布式事务框架，如 Seata

• 跨节点 JOIN 查询问题

• • 冗余表或冗余字段

• 跨节点分页查询问题

• • 使用两套数据，如 Mysql + Elasticsearch

• 全局自增主键 ID 问题

• • 使用雪花 ID

• 扩容问题

• • 预设 2 倍，按 2 倍扩容

读写分离