MySQL（二）高性能优化及规范

置顶 folyh

已于 2023-05-05 11:17:18 修改

阅读量1.1k

点赞数 2

分类专栏：后端Java 开发学习拓展文章标签：数据库 sql 缓存

于 2023-03-22 00:23:24 首次发布

本文链接：https://blog.csdn.net/qq_45738250/article/details/129698071

版权

后端Java 同时被 2 个专栏收录

55 篇文章 4 订阅

订阅专栏

开发学习拓展

46 篇文章 1 订阅

订阅专栏

思维导图

一、数据库及表规范

1.数据库规范

1.1 命名规范

所有数据库对象名称必须使用小写字母并用下划线分割
所有数据库对象名称禁止使用 MySQL 保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来）数据库对象的命名要能做到见名识意，并且最后不要超过 32 个字符
临时库表必须以 tmp_ 为前缀并以日期为后缀，备份表必须以 bak_ 为前缀并以日期 (时间戳) 为后缀
所有存储相同数据的列名和列类型必须一致（一般作为关联列，如果查询时关联列类型不一致会自动进行数据类型隐式转换，会造成列上的索引失效，导致查询效率降低）

1.2 数据库操作行为规范

1.2.1 对于大表使用 pt-online-schema-change 修改表结构

pt-online-schema-change
把原来一个 DDL 操作，分解成多个小的批次进行。

会首先建立一个与原表结构相同的新表，并且在新表上进行表结构的修改。
然后再把原表中的数据复制到新表中，并在原表中增加一些触发器。
把原表中新增的数据也复制到新表中，在行所有数据复制完成之后，把新表命名成原表，并把原来的表删除掉。

意义

避免大表修改产生的主从延迟
避免在对表字段进行修改时进行锁表

1.2.2 禁止为程序使用的账号赋予 super 权限

当达到最大连接数限制时，还运行 1 个有 super 权限的用户连接
super 权限只能留给 DBA 处理问题的账号使用

1.2.3 对于程序连接数据库账号,遵循权限最小原则

程序使用数据库账号只能在一个 DB 下使用，不准跨库
程序使用的账号原则上不准有 drop 权限

1.2.4 程序连接不同的数据库使用不同的账号，禁止跨库查询

为数据库迁移和分库分表留出余地
降低业务耦合度
避免权限过大而产生的安全风险

2.数据库基本设计规范

2.1 所有表必须使用 InnoDB 存储引擎

InnoDB 支持事务，支持行级锁，更好的恢复性，高并发下性能更好。

2.2 数据库和表的字符集统一使用 UTF8

兼容性更好，统一字符集可以避免由于字符集转换产生的乱码，不同的字符集进行比较前需要进行转换会造成索引失效
如果数据库中有存储 emoji 表情的需要，字符集需要采用 utf8mb4 字符集。

2.3 所有表和字段都需要添加注释

2.4 尽量控制单表数据量的大小，建议控制在 500 万以内

500 万并不是 MySQL 数据库的限制，过大会造成修改表结构，备份，恢复都会有很大的问题。
可以用历史数据归档（应用于日志数据），分库分表（应用于业务数据）等手段来控制数据量大小

2.5 谨慎使用 MySQL 分区表

分区表在物理上表现为多个文件，在逻辑上表现为一个表；
谨慎选择分区键，跨分区查询效率可能更低；
建议采用物理分表的方式管理大数据。

2.6 经常一起使用的列放到一个表中

避免更多的关联操作。

2.7 禁止在表中建立预留字段

预留字段的命名很难做到见名识义。
预留字段无法确认存储的数据类型，所以无法选择合适的类型。
对预留字段类型的修改，会对表进行锁定。

2.8 禁止在数据库中存储文件（比如图片）这类大的二进制数据

在数据库中存储文件会严重影响数据库性能，消耗过多存储空间。
文件（比如图片）这类大的二进制数据通常存储于文件服务器，数据库只存储文件地址信息。

2.9 不要被数据库范式所束缚

进行查询时需要对多张表进行关联查询，有时为了提高查询效率，会降低范式的要求，在表中保存一定的冗余信息，也叫做反范式。但要注意反范式一定要适度。

2.10 禁止在线上做数据库压力测试

2.11 禁止从开发环境,测试环境直接连接生产环境数据库

二、SQL规范

1.选择合适的字段类型

存储字节越小，占用也就空间越小，性能也越好。

1.1 某些字符串可以转换成数字类型存储

比如可以将 IP 地址转换成整型数据。

MySQL 提供了两个方法来处理 ip 地址

INET_ATON() ：
插入数据前，把 ip 转为无符号整型 (4-8 位)
INET_NTOA() :
查询数据后，把整型的 ip 转为地址

1.2 非负型的数据优先使用无符号整型来存储。

如自增 ID,整型 IP，年龄

因为无符号相对于有符号可以多出一倍的存储空间

1.3 小数值类型优先使用 TINYINT 类型。

比如年龄、状态表示如 0/1

1.4 日期类型建议使用Timestamp。

因为DateTime 类型耗费空间更大且没有时区信息，
超出 TIMESTAMP 取值范围的使用 DATETIME 类型存储
避免使用字符串存储日期型的数据
1. 无法用日期函数进行计算和比较
2. 用字符串存储日期要占用更多的空间

1.5 金额字段用 decimal，避免精度丢失

1.6 尽量使用自增 id 作为主键

若主键为自增 id，每次都会将数据加在 B+树尾部（本质是双向链表），时间复杂度为 O(1)。
在写满一个数据页的时候，直接申请另一个新数据页接着写就可以了。
若主键是非自增 id，为了让新加入数据后 B+树的叶子节点还能保持有序，它就需要往叶子结点的中间找，查找过程的时间复杂度是 O(lgn)。
如果这个也被写满的话，就需要进行页分裂。页分裂操作需要加悲观锁，性能非常低。

备注： 分库分表不建议使用自增 id 作为主键，应该使用分布式 ID 比如 uuid。

数据库主键一定要自增吗？有哪些场景不建议自增？

1.7 避免使用 TEXT,BLOB 数据类型

最常见的 TEXT 类型可以存储 64k 的数据

1.7.1 建议把 BLOB 或是 TEXT 列分离到单独的扩展表中。

MySQL 内存临时表不支持 TEXT、BLOB 这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行。
而且对于这种数据，MySQL 还是要进行二次查询，会使 sql 性能变得很差。
查询时一定不要使用 select *而只需要取出必要的列，不需要 TEXT 列的数据时不要对该列进行查询。

1.7.2 TEXT 或 BLOB 类型只能使用前缀索引

因为 MySQL 对索引字段长度是有限制的，所以 TEXT 类型只能使用前缀索引
TEXT 列上是不能有默认值的

1.8 避免使用 ENUM 类型

修改 ENUM 值需要使用 ALTER 语句；
NUM 类型的 ORDER BY 操作效率低，需要额外操作；
ENUM 数据类型存在一些限制比如建议不要使用数值作为 ENUM 的枚举值。

1.9 尽可能把所有列定义为 NOT NULL

除非有特别的原因使用 NULL 值

索引 NULL 列需要额外的空间来保存，所以要占用更多的空间；
进行比较和计算时要对 NULL 值做特别的处理。

1.10 单表不要包含过多字段

如果一个表包含过多字段的话，可以考虑将其分解成多个表，必要时增加中间表进行关联。

2.常见查询优化

2.1 避免使用 SELECT *

会消耗更多的cpu
无用字段增加网络带宽资源消耗，增加数据传输时间，尤其是大字段（如
varchar、blob、text）
无法使用 MySQL 优化器覆盖索引的优化（基于 MySQL 优化器的“覆盖索
引”策略又是速度极快，效率极高，业界极为推荐的查询优化方式）

关于覆盖索引链接：
Mysql性能优化：为什么要用覆盖索引？

2.2 避免数据类型的隐式转换

隐式转换会导致索引失效如:

// 等号左边id在数据库里是数值型，而等号右边却给了字符型，就会发生隐式转换
select name,phone from customer where id = '111';

MySQL 中的隐式转换造成的索引失效

2.3 避免使用子查询，可以把子查询优化为 join 操作

转化条件
通常子查询在 in 子句中，且子查询中为简单 SQL(不包含 union、group by、order by、limit 从句) 时,才可以把子查询转化为关联查询进行优化。
子查询性能差原因

子查询的结果集无法使用索引
通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。
特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大
由于子查询会产生大量的临时表也没有索引，所以会消耗过多的 CPU 和 IO 资源，产生大量的慢查询。

3. 条件排序分组查询

3.1 WHERE 从句中禁止对列进行函数转换和计算

对列进行函数转换或计算时会导致无法使用索引

// 不推荐
where date(create_time)='20190101'

// 推荐
where create_time >= '20190101' and create_time < '20190102'

3.2 禁止使用 order by rand() 进行随机排序

order by rand() 会把表中所有符合条件的数据装载到内存中
然后在内存中对所有数据根据随机生成的值进行排序，并且可能会对每一行都生成一个随机值
如果满足条件的数据集非常大，就会消耗大量的 CPU 和 IO 及内存资源

链接：聊聊order by rand()

备注： 推荐在程序中获取一个随机值，然后从数据库中获取数据的方式。

3.3 对应同一列进行 or 判断时，使用 in 代替 or

in 的值不要超过 500 个，in 操作可以更有效的利用索引，or 大多数情况下很少能利用到索引。

4.分页优化

分页在数据量小时耗费时间短，但百万甚至千万级别耗费时间就长了。

4.1 将的分页改成子查询

// 百万数据量查询
SELECT `score`,`name` FROM `cus_order` ORDER BY `score` DESC LIMIT 1000000, 10

// 子查询:我们先查询出 limit 第一个参数对应的主键值，再根据这个主键值再去过滤并 limit，这样效
率会更快(阿里巴巴规范)
SELECT `score`,`name` FROM `cus_order` WHERE id >= (SELECT id FROM `cus_order` LIMIT 1000000,1) LIMIT 10

备注： 子查询的结果会产生一张新表会影响性能，尽量避免大量使用子查询。

4.2 延迟查询

我们先提取对应的主键，再将这个主键表与原数据表关联：

SELECT `score`,`name` FROM `cus_order` a, (SELECT id from `cus_order` ORDER BY `score` DESC LIMIT 1000000, 10) b where a.id = b.id

5.关联查询优化

5.1 尽量避免多表做 join

5.1.1 原因

Join的效率比较低，因为其使用嵌套循环（Nested Loop）来实现关联查询，三
种不同的实现效率都不是很高：

Simple Nested-Loop Join ：直接使用笛卡尔积实现 join，逐行遍历/全
表扫描，效率最低。
Block Nested-Loop Join ：利用 JOIN BUFFER 进行优化，性能有所提升。不过，如果两个表的数据过大的话，对性能的提升非常有限。
Index Nested-Loop Join ：在必要的字段上增加索引，使 Join 的过程中可以使用到这
个索引，这样可以让 Block Nested-Loop Join 转换为 Index Nested-Loop Join，性能得
到进一步提升。

5.1.2 解决

单表查询+代码上组装
可以使用：Stream lambda + mybatis plus + lombok

建议使用这种方法，因为：

单表查询更利于后续业务变化后的维护。
代码可复用性高
数据量越大效率提高越大
数据量越大Join查询时间几何倍增加，若索引设计不合理甚至拖垮数据库
减少冗余字段的查询
针对只需查询一次的数据Join会多次查询增加网络和内存开销
缓存利用率更高
针对没有变动的查询可以缓存下来利用
数据库分库分表后中间件对Join不友好

MySQL多表关联查询效率高点还是多次单表查询效率高，为什么？

数据冗余
表结构比较稳定的情况下，把一些重要的数据在表中做冗余，尽可能地避免关联查询。

5.2 尽量用 UNION ALL 代替 UNION

除非数据需要去重

UNION 会把两个结果集的所有数据放到临时表中后再进行去重操作，更耗时，更消耗 CPU资源。
UNION ALL 不会再对结果集进行去重操作，获取到的数据包含重复的项。

5.3 建议不要使用外键与级联

不适合分布式、高并发
级联更新时强阻塞，存在数据库更新风暴的风险
外键影响数据库的插入速度

6. SQL语句性能分析及策略

6.1 SQL语句性能分析

6.1.1 Show Profile 分析 SQL 执行性能

展示SQL语句的资源使用情况：CPU使用、CPU 上下文切换、IO 等待、内存使用等。

// 查询功能是否存在、打开
SHOW VARIABLES LIKE '%profiling%'
或者：
SELECT @@profiling

// 设置打开
SET @@profiling=1

// 设置展示SQL语句数量（如下设置100条）

SET @@profiling_history_size=100

// 查询所有SQL的QUERY_ID
SHOW PROFILE

// 根据QUERY_ID(如下为8)对应的SQL语句性能
SHOW PROFILE CPU,IPC, MEMORY FOR QUERY 8;

6.1.2 EXPLAIN 命令分析对应的 SELECT 语句

分析信息：

select_type ：查询的类型，常用的取值有 SIMPLE（普通查询，即没有联合查询、
子查询）、PRIMARY（主查询）、UNION（UNION 中后面的查询）、SUBQUERY（子
查询）等。
table ：表示查询涉及的表或衍生表。
type ：执行方式，判断查询是否高效的重要参考指标，结果值从差到好依次是：ALL
< index < range ~ index_merge < ref < eq_ref < const < system。
rows : SQL 要查找到结果集需要扫描读取的数据行数，原则上 rows 越少越好。

MySQL（三）高性能优化实战

MySQL 性能优化神器 Explain 使用分析

6.2 优化对性能影响较大的 SQL 语句

可以通过查询 MySQL 的慢查询日志来要找到最需要优化的 SQL 语句。

使用最频繁的语句
优化后提高最明显的语句

6.3 拆分复杂的大 SQL 为多个小 SQL

大 SQL 逻辑上比较复杂，需要占用大量 CPU 进行计算的 SQL
MySQL 中，一个 SQL 只能使用一个 CPU 进行计算
SQL 拆分后可以通过并行执行来提高处理效率

7. 优化慢SQL

7.1 开启并设置MySQL慢查询日志

# 开启慢查询日志功能
SET GLOBAL slow_query_log = 'ON';
# 慢查询日志存放位置
SET GLOBAL slow_query_log_file = '/var/lib/mysql/ranking-list-slow.log';
# 无论是否超时，未被索引的记录也会记录下来。
SET GLOBAL log_queries_not_using_indexes = 'ON';
# 慢查询阈值（秒），SQL 执行超过这个阈值将被记录在日志中。
SET SESSION long_query_time = 1;
# 慢查询仅记录扫描行数大于此参数的 SQL
SET SESSION min_examined_row_limit = 100;

备注： 设置好通过以下命令验证以下：

show variables like 'slow%'

7.2 查询MySQL慢查询日志

cat /var/lib/mysql/ranking-list-slow.log

备注： 要确保自己有对应目录的访问权限：

chmod 755 /var/lib/mysql/

7.3 慢查询日志信息说明（包含SQL语句）

Time ：被日志记录的代码在服务器上的运行时间。
User@Host ：谁执行的这段代码。
Query_time ：这段代码运行时长。
Lock_time ：执行这段代码时，锁定了多久。
Rows_sent ：慢查询返回的记录。
Rows_examined ：慢查询扫描过的行数。

8.数据库语句操作

8.1 建议使用预编译语句进行数据库操作

预编译语句可以重复使用这些计划，减少 SQL 编译所需要的时间，
还可以解决动态 SQL 所带来的 SQL 注入的问题。
只传参数，比传递 SQL 语句更高效。
相同语句可以一次解析，多次使用，提高处理效率。

简单谈一谈 Java 中的预编译

8.2 减少同数据库的交互次数

数据库更适合处理批量操作，合并多个相同的操作到一起，可以提高处理效率。

8.3 超 100 万行的批量写 (UPDATE,DELETE,INSERT) 操作,要分批多次进行操作

大批量操作可能会造成严重的主从延迟
- 大批量的写操作一般都需要执行一定长的时间
- 而只有当主库上执行完成后，才会在其他从库上执行，所以会造成主库与从库长时间的延迟情况
binlog 日志为 row 格式时会产生大量的日志
- 大批量写操作会产生大量日志
- 特别是对于 row 格式二进制数据而言，由于在 row 格式中会记录每一行数据的修改，我们一次修改的数据越多，产生的日志量也就会越多
- 日志的传输和恢复所需要的时间也就越长，这也是造成主从延迟的一个原因
避免产生大事务操作

大批量修改数据，一定是在一个事务中进行的
这就会造成表中大批量数据进行锁定，从而导致大量的阻塞，阻塞会对 MySQL 的性能产生非常大的影响
特别是长时间的阻塞会占满所有数据库的可用连接，这会使生产环境中的其他应用无法连接到数据库
因此一定要注意大批量写操作要进行分批

三、索引规范

1.索引字段创建规范

1.1 选择合适的字段创建索引

不为 NULL 的字段

索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字
段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用
0,1,true,false 这样语义较为清晰的短值或短字符作为替代。

被频繁查询的字段

我们创建索引的字段应该是查询操作非常频繁的字段。

被作为条件查询的字段

被作为 WHERE 条件查询的字段，应该被考虑建立索引。
另外还有 GROUP BY、DISTINCT 中的字段

频繁需要排序的字段

索引已经排序，这样查询可以利用索引的排序，加快排序查询时
间。

被经常频繁用于JOIN连接的字段

经常用于连接的字段可能是一些外键列，对于外键列并不
一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以
考虑建立索引，提高多表连接查询的效率。

1.2 被频繁更新的字段应该慎重建立索引

2.索引类型及其他创建规范

2.1 限制每张表上的索引数量,建议单张表索引不超过 5 个

索引可以增加查询效率，但同样也会降低插入和更新的效率，甚至有些情况下会降低查询效率。

MySQL 优化器在选择如何优化查询时，会对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划
如果同时有很多个索引都可以用于查询，就会增加 MySQL 优化器生成执行计划的时间，同样会降低查询性能

2.2 删除长期未使用的索引

删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗 MySQL 5.7 后可以通过查询 sys 库的 schema_unused_indexes 视图来查询哪些索引从未被使用

2.3 禁止使用全文索引

全文索引不适用于 OLTP 场景（联机事务处理OLTP — 增删改查事务；区别于联机分析处理OLAP — 数据仓库分析）

链接： InnoDB & MySQL 全文索引

2.4 尽可能的考虑建立联合索引而不是单列索引

禁止给表中的每一列都建立单独的索引

索引需要占用磁盘空间，可以简单理解为每个索引对应着一颗 B+树。
如果一个表的索引过多，当这个表的数据量很多后，索引占用的空间也很多；且修改索引时，耗费的时间也是较多的。
如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。

2.5 避免建立冗余索引和重复索引（增加了查询优化器生成执行计划的时间）

重复索引示例：primary key(id)、index(id)、unique index(id)
冗余索引示例：index(a,b,c)、index(a,b)、index(a)
1. 冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就
  是冗余索引。
  如（name,city ）和（name ）这两个索引就是冗余索引。能够命中前者的查询肯定是能够命中后者的。
2. 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。