MySQL性能调优指南

kryiea2024年1月1日

性能调优

📎高性能mysql第三版.pdf

explain 查看执行计划

(十七)SQL优化篇：如何成为一位写优质SQL语句的绝顶高手！ - 掘金

EXPLAIN是MySQL中一个非常有用的命令，用于分析SQL语句的执行计划。

通过执行计划，我们可以了解SQL语句是如何被优化器执行的，包括是否使用了索引、表的扫描方式、数据的读取顺序等信息。

以下是EXPLAIN命令输出中type、key、extra这三个字段的含义及如何通过它们来判断SQL语句是否使用了索引。

type：这是一个重要的字段，表示MySQL决定如何查找表中的行的方法，或者说是join类型。
type的值从最好到最差依次是：system > const > eq_ref > ref > range > index > ALL。
system和const通常表示已经使用了索引，并且查询非常快；
eq_ref和ref表示通过索引查找记录，但需要对多个记录进行比较；
range表示使用了索引范围扫描；
index表示全索引扫描；
ALL表示全表扫描，通常是最慢的。
key：这个字段显示了MySQL实际从表中使用的索引。
如果key是NULL，表示没有使用索引。
如果这个字段非空，说明查询使用了索引，字段的值就是被使用的索引的名称。
extra：这个字段包含不适合在其他字段显示的额外信息。
如果看到Using index，表示查询只使用了索引中的信息，没有读取实际的行数据，这通常意味着效率很高。
如果看到Using where，表示MySQL服务器将存储引擎返回的行进行了额外的过滤，可能是因为索引返回的行比实际需要的行多。
Using filesort和Using temporary通常表示查询效率不高，可能需要优化。

通过查看这三个字段的值，你可以判断出SQL语句是否有效地使用了索引。

如果type字段显示的是ALL或者index，并且key字段是NULL，或者extra字段出现了Using filesort或Using temporary，那么可能需要考虑添加或优化索引以提高查询效率。

索引调优

独立的列

换成：actor_id = 5 -1 ，就是独立的列

前级索引和索引选择性

用字段的前几个字符作为索引

具体是几个字符，需要去测试：增加字符数量时，效率提升的幅度是否也大，是，则继续增大，一直到提升幅度很小，就不再增加

前缀索引可以通过使用字段值的前几个字符来减少索引大小并提升查询效率。
选择性是衡量索引效果的一个重要指标，选择性高的索引有助于更快地定位记录。

联合索引

通过将多个列组合成一个索引来优化特定查询。
重要的是要根据查询模式和列的选择性来选择索引列的顺序。

选择合适的索引列顺序

在创建联合索引时，将最常用于查询条件且选择性最高的列放在前面。
正确的列顺序可以最大化索引的效率。

聚族索引

也称为主键索引，是按照每个表的主键构建的，它决定了表中数据的物理排序，基于 B+树，所以主键是逻辑上相邻的（也是聚簇的意思）。
聚簇索引可以加速主键查找，但是每个表只能有一个聚簇索引。

覆盖索引

当索引包含所有需要查询的字段时，查询可以直接使用索引而无需回表，这称为覆盖索引。
覆盖索引可以显著减少数据访问次数，提高查询性能。

使用索引扫描来做排序

如果查询的ORDER BY子句与索引列对应，可以直接利用索引顺序进行排序，避免额外排序开销。

压缩(前缀压缩)索引

对于有共同前缀的字符串，可以使用前缀压缩来节省索引空间。
压缩索引尤其在有大量重复前缀的情况下有效，可以提升索引扫描的效率。

元余和重复索引

定期审查索引，去除不必要的或重复的索引，这可以减少维护成本并提高写操作的性能。

未使用的索引

使用数据库的监控工具来识别和删除长时间未被查询使用的索引。
这可以帮助释放存储空间，减少维护开销。

索引和锁

在事务型数据库中，索引可以影响锁的粒度和类型，从而影响并发性能。
适当的索引策略可以减少锁冲突，提高事务处理速度。

SQL 优化

如何定位一条查询慢 SQL 语句

是否请求了不需要的数据

误区：mysql 只返回需要的数据

MySQL 是先返回全部结果集再进行计算

挖坑：多表关联时返回全部列

挖坑：总是取出全部列

别- -

挖坑：重复查询相同数据

是否扫描了额外的记录

三个衡量指标

响应时间

没有一致的公式，事先估计一下查询需要哪些索引，执行计划是什么等等来估计一下上限

扫描的行数和返回的行数

如何对慢查询 SQL 语进行优化

(十七)SQL优化篇：如何成为一位写优质SQL语句的绝顶高手！ - 掘金

SQL优化思路+经典案例分析 - 掘金

查询时尽量不要使用*

连表查询时尽量不要关联太多表

多表查询时一定要以小驱大

不要使用like左模糊和全模糊查询

查询时尽量不要对字段做空值判断

不要在条件查询=前对字段做任何运算

!=、!<>、not in、not like、or...要慎用

必要情况下可以强制指定索引

避免频繁创建、销毁临时表

尽量将大事务拆分为小事务执行

从业务设计层面减少大量数据返回的情况

尽量避免深分页的情况出现

SQL务必要写完整，不要使用缩写法

基于联合索引查询时请务必确保字段的顺序性

客户端的一些操作可以批量化完成

明确仅返回一条数据的语句可以使用limit 1

利用慢查询日志

一个复杂查询 or 多个简单查询

将外链接转成内连接

使用等价变化规则

优化 count()、min()、max()

优化 where 子句

使用合适的逻辑运算符，以避免在索引列上使用函数或计算

限制使用 having 子句

having 通常有关于过滤聚合函数的结果，在聚合函数之后进行过滤，可能非常慢

尽量在 where 中过滤数据

适当分页

只查询用户当前需要查看的数据

使用参数化查询

防止 sql 注入攻击

避免数据类型转换

避免隐式转换：字符和整形之间

分页场景如何优化

MySQL 性能优化

(十六)MySQL调优篇：单机数据库如何在高并发场景下健步如飞？ - 掘金

在做性能优化时，你应该要清楚系统的性能瓶颈在哪儿

到底是要调哪个位置？是线程模型？或是CPU调度？还是内存回收？亦是磁盘IO速率？

针对不同层面有不同的优化方案

MySQL 调优的五个维度

客户端与连接层的优化：调整客户端DB连接池的参数和DB连接层的参数。
MySQL结构的优化：合理的设计库表结构，表中字段根据业务选择合适的数据类型、索引。
MySQL参数优化：调整参数的默认值，根据业务将各类参数调整到合适的大小。
整体架构优化：引入中间件减轻数据库压力，优化MySQL架构提高可用性。
编码层优化：根据库表结构、索引结构优化业务SQL语句，提高索引命中率。

通常情况下，带来的性能收益排序为④ > ② > ⑤ > ③ > ①，不过带来的性能收益越大，也就意味着成本会更高

连接层优化

数据库连接数

情景：单库

数据库连接数过多

一个用户请求最终会分配一条线程处理，一个 32 核心的 CPU，数据库连接数配置 300 个，最多只能有 32 个线程同时工作，以时间片调度的模式工作，不同核心在不同线程之间反复切换执行，由于线程数远超核心数，线程的上下文切换开销远远大于线程执行开销

客户端连接池 and 服务端连接池

对比而言，无需关心 mysql 连接池，重点是客户端连接池的连接数。

在客户端做了连接限制，服务端自然也就被限制

客户端连接池和服务端连接池配置一样的最大连接数可以吗？

不行，有时候通过终端工具远程连接 MySQL，如果一致，你不就连不上了

应该配置为多少呢

PostgreSQL提供的计算公式：最大连接数 = (CPU核心数 * 2) + 有效磁盘数

有效磁盘数：SSD 固态硬盘

偶发高峰类业务的连接数配置

情景：单库

最大连接数按公式

常驻连接数可以配置成 CPU 核数 + 1

分库分表情况下的连接数配置

情景：分库分表、读写分离、双主双写

结构优化

表结构、字段结构、索引结构

表结构

字段数量不能太多，一张表最多最多 30 个字段
设计表结构时，正常情况下，应该遵循数据库三范式
实时性不高的，建立中间表，比如游戏战力排名，每日定时统计一次

字段结构

在保证足够使用的范围内，选择最小数据类型，因为它们会占用更少的磁盘、内存和CPU缓存，同时在处理速度也会更快。
尽量避免索引字段值为NULL，定义字段时应尽可能使用NOT NULL关键字，因为字段空值过多会影响索引性能。
在条件允许的情况下，尽量使用最简单的类型代替复杂的类型，如IP的存储可以使用int而并非varchar，因为简单的数据类型，操作时通常需要的CPU资源更少

索引结构

索引字段尽量选择多个，节省磁盘资源
充分利用索引覆盖
值较长的字段，尽量建立前缀索引，而不使用完整字段值
经常做模糊查询的字段，建立全文索引来代替。基于普通索引做 like 查询会导致索引失效
某些不会做范围查询的字段建立索引，选用 hash 结构代替 B+Tree

参数优化

提供给用户控制的参数都有几百个，所以想要真正的做好参数优化，必须要对MySQL真正的熟悉才行

调整 Inno DB 缓冲区

比例控制在机器内存的 70%~75%
官方建议每个缓冲区空间必须大于 1GB

调整工作线程的缓冲区

调整临时表空间

调整空闲线程的存活时间

架构优化与 SQL 优化

引入中间件解决读压力

在应用程序和数据库之间架设一个 redis 缓存

引入消息中间件解决写压力

MQ 消息中间件做削峰填谷

主从读写分离

架设了 MQ、Redis 后，必须走 mysql 执行的请求依旧超出单机 MySQL 承载范围，依旧会频繁宕机

架构优化方案，分别是指三种：主从架构、双主架构、分库分表架构

典型主从架构

主节点数据变更后，从节点会基于 binlog 日志去同步。

问题：这种模式下会存在些许数据不一致

双主双写+热备份

注意每张表的主键要设置好，如果主键是自增类型，要手动设置自增步长起始值。

节点1：[1、3、5、7、9、11、13、15、17、19.....]

节点2：[2、4、6、8、10、12、14、16、18、20.....]

确保主键唯一性

分库分表

根据业务属性的不同，会创建不同的数据库，然后由不同的业务连接不同的数据库，各自之间数据分开存储，节点之间数据不会同步，以这种方式来部署MySQL，即提高了数据库的整体吞吐量和并发能力，同时也不存在之前的存储容量的木桶问题。