MySQL（二）高性能优化及规范_优化mysql5.7数据库

思维导图

一、数据库及表规范

1.数据库规范

1.1 命名规范

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

1.2 数据库操作行为规范

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

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

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

• 意义

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

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';
1
2

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

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

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

• 子查询性能差原因

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

3. 条件排序分组查询

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

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

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

// 推荐
where create_time >= '20190101' and create_time < '20190102'
1
2
3
4
5

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

1
2
3
4
5
6
7

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

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
1

相关详细链接如下：

面试官：一千万数据，怎么快速查询？

【得物技术】MySQL 深分页优化

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 解决

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

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

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

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

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

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;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

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 越少越好。
1
2
3
4
5
6
7

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;
1
2
3
4
5
6
7
8
9
10

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

show variables like 'slow%'
1

7.2 查询MySQL慢查询日志

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

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

chmod 755 /var/lib/mysql/
1

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

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

8.数据库语句操作

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

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

简单谈一谈 Java 中的预编译

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

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

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

1. 大批量操作可能会造成严重的主从延迟

   • 大批量的写操作一般都需要执行一定长的时间
   • 而只有当主库上执行完成后，才会在其他从库上执行，所以会造成主库与从库长时间的延迟情况

2. binlog 日志为 row 格式时会产生大量的日志

   • 大批量写操作会产生大量日志
   • 特别是对于 row 格式二进制数据而言，由于在 row 格式中会记录每一行数据的修改，我们一次修改的数据越多，产生的日志量也就会越多
   • 日志的传输和恢复所需要的时间也就越长，这也是造成主从延迟的一个原因

3. 避免产生大事务操作

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

三、索引规范

1.索引字段创建规范

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

1. 不为 NULL 的字段

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

2. 被频繁查询的字段

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

3. 被作为条件查询的字段

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

4. 频繁需要排序的字段

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

5. 被经常频繁用于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. 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

2.6 尽量避免使用外键约束

• 不建议使用外键约束（foreign key），但一定要在表与表之间的关联键上建立索引
• 外键可用于保证数据的参照完整性，但建议在业务端实现
• 外键会影响父表和子表的写操作从而降低性能

2.7 每个 InnoDB 表必须有个主键

InnoDB 是一种索引组织表：数据的存储的逻辑顺序和索引的顺序是相同的。每个表都可以有多个索引，但是表的存储顺序只能有一种。

InnoDB 是按照主键索引的顺序来组织表的

• 不要使用更新频繁的列作为主键，不适用多列主键（相当于联合索引）
• 不要使用 UUID,MD5,HASH,字符串列作为主键（无法保证数据的顺序增长）
• 主键建议使用自增 ID 值

2.8 如何选择索引列的顺序

建立索引的目的是：希望通过索引进行数据查找，减少随机 IO，增加查询性能 ，索引能过滤出越少的数据，则从磁盘中读入的数据也就越少。

• 区分度最高的放在联合索引的最左侧（区分度=列中不同值的数量/列的总行数）
• 尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越小，一页能存储的数据量越大，IO 性能也就越好）
• 使用最频繁的列放到联合索引的左侧（这样可以比较少的建立一些索引）

3.正确使用索引

正确使用索引可以大大加快数据的检索速度（大大减少检索的数据量）

3.1 避免索引失效

1. 索引失效也是慢查询的主要原因之一，常见的导致索引失效的情况有下面这些：
2. 使用 SELECT * 进行查询;
3. 创建了组合索引，但查询条件未准守最左匹配原则;
4. 在索引列上进行计算、函数、类型转换等操作;
5. 以 % 开头的 LIKE 查询比如 like ‘%abc’; ;
6. 查询条件中使用 or，且 or 的前后条件中有一个列没有索引，涉及的索引都不会被使用到;
7. 发生隐式转换

备注： MySQL隐式转换造成索引失效

3.2 对于频繁的查询优先考虑使用覆盖索引

覆盖索引：就是包含了所有查询字段 (where,select,order by,group by 包含的字段) 的索引

覆盖索引的好处：

• 避免 InnoDB 表进行索引的二次查询
• 可以把随机 IO 变成顺序 IO 加快查询效率

MySQL 覆盖索引详解

3.3 考虑在字符串类型的字段上使用前缀索引代替普通索引

前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引替代普通索引

关于索引见我另一篇文章：
数据库及缓存之MySQL索引

上一篇跳转—MySQL（一）常见知识点                下一篇跳转—MySQL（三）之高性能优化实战

本篇文章主要参考链接如下：

参考链接1-JavaGuide

---

持续更新中…

随心所往，看见未来。Follow your heart，see light！

欢迎点赞、关注、留言，一起学习、交流！