ToB企服应用市场:ToB评测及商务社交产业平台

标题: MySQL8.0性能优化（实践） [打印本页]

---

作者: 西河刘卡车医    时间: 2023-2-3 17:47
标题: MySQL8.0性能优化（实践）

一台几年前的旧笔记本电脑的虚拟系统运行环境，作为本次实践的运行工具，仅供参考。
案例环境：Linux、Docker、MySQLCommunity8.0.31、InnoDB。
过早的MySQL版本不一定适用本章内容，仅围绕 InnoDB 引擎的阐述。

一、索引

1.1 索引的管理

1. -- create 方式创建
2. create [unique] index {index_name} on {tab_name}({col_name}[(length)]);
3. -- alter表 方式创建
4. alter {tab_name} add [unique] index {index_name} on ({col_name}[(length)]);
5. -- 创建组合索引
6. create index {index_name} on ({col_name1}[(length)], {col_name2}[(length)], {col_name3}[(length)]);
8. -- unique：唯一索引
9. -- col_name：一列为单列索引；逗号隔开的多列为组合索引
10. -- length：字段中前几个字符有效，避免无限长度（通常能够明显区分值即可的长度；如：员工表的Email，@后面都一样）
13. -- 查看表中的索引
14. show index from {tab_name};
16. -- 删除索引
17. drop index {index_name} on {tab_name};

复制代码

1.2 索引创建的场景

过多查询的表，过少写入的表。
数据量过大导致的查询效率慢。
经常作为条件查询的列。
批量的重复值，不适合创建索引；比如列
值过少重复的列，适合创建索引；比如、列
1.3 理想的索引特征

• 尽量能够覆盖常用字段
  
• 字段值区分度高
  
• 字段长度小（合适的长度，不是越小越好，至少能足够区分每个值）
  
• 相对低频的写入操作，以及高频的查询操作的表和字段上建立索引
  

通过非聚集索引检索记录的时候，需要2次操作，先在非聚集索引中检索出主键，然后再到聚集索引中检索出主键对应的记录，这个过程叫做回表，比聚集索引多了一次操作。
1.4 非主键索引

where全部为and时，无所谓位置，都会命中索引（当多个条件中有索引的时候，并且关系是and的时候，会自动匹配索引区分度高的）
where后面为 or时，索引列 依影响数据范围越精确 按序靠前写。
1.5 索引的使用

使用原则：

• 按条件后面涉及到的列，创建出组合索引
  
• 越精确的条件，就排在条件的顺序首位，最左匹配原则
  

最左原则示例：

1. -- 创建组合索引前，先看下列的数据情况，找出数据重复最少的列
2. -- 计算哪个列最精确；重复值越少、越精确的列，位置顺序越靠左
3. select sum(depno=28), sum(username like 'Sol%'), sum(position='dev') from tab_emp;
4. +---------------+---------------------------+---------------------+
5. | sum(depno=28) | sum(username like 'Sol%') | sum(position='dev') |
6. +---------------+---------------------------+---------------------+
7. | 366551        | 3                         | 109                 |
8. +---------------+---------------------------+---------------------+
9. -- 由此得出：username列的范围最精确，应该放到index/where后的首位；不在组合索引的列放到最后。
11. -- 如下组合索引的创建方式：(依精确次序)
12. create index {index_name} on {tab_name}(username,position,depno);
13. -- 如下组合索引的查询方式：(依精确次序)
14. select username,position,depno from tab_emp where username like 'Sol%' and position='dev' and depno=106 and age<27;

复制代码

在通常情况下，能不能命中索引，取决于索引列的值重复程度；如果是极少重复的值，就很容易命中索引。如果类似于状态或类型的值，重复程度很高，就很难命中索引，这是MySQL自动取舍的结果。
比如：没有索引的列-电话号码，有索引的列-部门，那么很难命中部门索引，因为MySQL认为[电话号码]更精确；或者使用force强行命中，通常MySQL的自动取舍是最有效的。
1.8 查询总结

避免使用*，以避免回表查询。
不常用的查询列或text类型的列，尽量以单独的扩展表存放。
条件避免使用函数。
条件避免过多的or，建议使用in()/union代替，in中的数据不可以极端海量，至少个数小于1000比较稳妥。
避免子查询，子查询的结果集是临时表不支持索引、或结果集过大、或重复扫描子表；以join代替子查询，尽量以inner join代替最为妥当。
避免使用'%Sol%'查询，或以'Sol%'代替。
二、表分区

表分区也就是把一张物理表的数据文件分成若干个数据文件存储，使得单个数据文件的量有限，有助于避免全表扫描数据，提升查询性能。
那，跨区查询的性能影响有多大，从整体看，表分区还是带来了不少的性能提升。
如果表中有主键列，分区列必须是主键列之一。比如：又有自增主键，又想按年份分区，那主键就是组合索引咯。(id+date)
2.1 分区的种类

HASH：按算法，平均分配到各分区

1. mysql> explain select * from tab_emp where uname='Sol';
2. -- 可能用到的索引、实际用到的索引、扫描了的行数
3. +----+-------------+---------+-------+---------------+---------------+---------+-------+------+-----------------------+
4. | id | select_type | table   | type  | possible_keys | key           | key_len | ref   | rows | Extra                 |
5. +----+-------------+---------+-------+---------------+---------------+---------+-------+------+-----------------------+
6. |  1 | SIMPLE      | tab_emp | range | idx_emp_uname | idx_emp_uname | 4       | const |    1 | Using index condition |
7. +----+-------------+---------+-------+---------------+---------------+---------+-------+------+-----------------------+

复制代码

KEY：按算法，无序不等的分配到各分区

1. -- 表创建 HASH 分区12个
2. CREATE TABLE clients (
3.     id INT,
4.     fname VARCHAR(30),
5.     signed DATE
6. )
7. PARTITION BY HASH(MONTH(signed))
8. PARTITIONS 12;

复制代码

RANGE：按划定的范围将数据存放到符合的分区

1. -- 表创建12个 KEY 分区
2. CREATE TABLE clients_lk (
3.     id INT,
4.     fname VARCHAR(30),
5.     signed DATE
6. )
7. PARTITION BY LINEAR KEY(signed)
8. PARTITIONS 12;

复制代码

LIST：按定义的一组包含值将数据存放到符合的分区

1. -- 按年份创建范围分区
2. CREATE TABLE tr (
3.     id INT,
4.     fname VARCHAR(50),
5.     purchased DATE
6. )
7. PARTITION BY RANGE(YEAR(purchased)) (
8.     PARTITION p0 VALUES LESS THAN (1990),
9.     PARTITION p1 VALUES LESS THAN (1995),
10.     PARTITION p2 VALUES LESS THAN (2000)
11. );

复制代码

2.2 分区的管理

追加分区

1. -- LIST 分组包含方式
2. CREATE TABLE tt (
3.     id INT,
4.     data INT
5. )
6. PARTITION BY LIST(data) (
7.     PARTITION p0 VALUES IN (5, 10, 15),
8.     PARTITION p1 VALUES IN (6, 12, 18)
9. );

复制代码

变更重组分区

1. -- ## 新增 HASH/KEY 分区
2. -- 将原来的 12 个分区合并为 8 个分区
3. ALTER TABLE clients COALESCE PARTITION 4;
4. -- 在原有的基础上增加 6 个分区
5. ALTER TABLE clients ADD PARTITION PARTITIONS 6;
7. -- ## 新增 RANGE/LIST 分区
8. -- RANGE 追加分区
9. ALTER TABLE tr ADD PARTITION (PARTITION p3 VALUES LESS THAN (2010));
10. -- LIST 追加新分区（不可包含已存在的值）
11. ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21));

复制代码

分区详细、分区检查

1. -- RANGE 拆分原有分区（重组分区）
2. ALTER TABLE tr REORGANIZE PARTITION p0 INTO (
3.     PARTITION n0 VALUES LESS THAN (1980),
4.     PARTITION n1 VALUES LESS THAN (1990)
5. );
6. -- RANGE 合并相邻分区
7. ALTER TABLE tt REORGANIZE PARTITION s1,s2 INTO (
8.     PARTITION s0 VALUES LESS THAN (1980)
9. );
10. -- LIST 重组原有分区
11. ALTER TABLE tt REORGANIZE PARTITION p1,np INTO (
12.     PARTITION p1 VALUES IN (6, 18),
13.     PARTITION np VALUES in (4, 8, 12)
14. );
15. -- 丢掉指定分区及其数据
16. ALTER TABLE {TABLE_NAME} DROP PARTITION p2,p3;
17. -- 删除指定分区，保留数据
18. ALTER TABLE {TABLE_NAME} TRUNCATE PARTITION p2;
19. -- 删除表全部分区，保留数据
20. ALTER TABLE {TABLE_NAME} REMOVE PARTITIONING;

复制代码

三、查询综合测试

2000万相同数据、相同表结构，相同的查询方式，测试效果如下图：(仅供参考)

数据量大了，查询慢；加索引了，数据量越大，写入越慢；
还是物理分表好呀~
四、SQL服务参数优化

仅列出了点官方认可的稳定性良好的可靠的参数，以 InnoDB 为主。
4.1 Connections

1. -- 查询指定分区的数据
2. SELECT * FROM tr PARTITION (p2);
3. -- 查询各分区详细
4. SELECT * FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tt';
5. -- 查看某个分区的状态
6. ALTER TABLE tr ANALYZE PARTITION p3;
7. -- 检查分区是否损坏
8. ALTER TABLE tr CHECK PARTITION p1;
9. -- 修复分区
10. ALTER TABLE tr REPAIR PARTITION p1, p2;
11. -- 优化分区，整理分区碎片
12. ALTER TABLE tr OPTIMIZE PARTITION p0, p1;
13. -- 当前分区数据，重建分区
14. ALTER TABLE tr REBUILD PARTITION p0, p1;

复制代码

4.2 缓冲区 Buffer

1. [mysqld]
2. # 保持在缓存中的可用连接线程
3. # default = -1（无）
4. thread_cache_size = 16
5. # 最大的连接线程数(关系型数据库)
6. # default = 151
7. max_connections = 1000
8. # 最大的连接线程数(文档型/KV型)
9. # default = 100
10. #mysqlx_max_connections = 700

复制代码

4.3 Sort merge passes

1. [mysqld]
2. # 缓冲区单位大小；default = 128M
3. innodb_buffer_pool_size = 128M
4. # 缓冲区总大小，内存的70%，单位大小的倍数
5. # default = 128M
6. innodb_buffer_pool_size = 6G
7. # 以上两个参数的设定，MySQL会自动改变 innodb_buffer_pool_instances 的值

复制代码

4.4 I/O 线程数

1. [mysqld]
2. # 优化 order/group/distinct/join 的性能
3. # SHOW GLOBAL STATUS 中的 Sort_merge_passes 过多就增加设置
4. # default = 1K
5. max_sort_length = 8K
6. # default = 256K
7. sort_buffer_size = 2M
8. # 通常别太大，海量join时大
9. # default = 256K
10. #join_buffer_size = 128M

复制代码

4.5 Capacity 容量

1. [mysqld]
2. # 异步I/O子系统
3. # default = NO
4. innodb_use_native_aio = NO
5. # 读数据线程数
6. # default = 4
7. innodb_read_io_threads = 32
8. # 写入数据线程数
9. # default = 4
10. innodb_write_io_threads = 32

复制代码

4.6 Open cache

1. [mysqld]
2. # default = 200
3. innodb_io_capacity = 1000
4. # default = 2000
5. innodb_io_capacity_max = 2500
6. # 数据日志容量值越大，恢复数据越慢
7. # default = 100M
8. innodb_redo_log_capacity = 1G
9. # 数据刷新到磁盘的方式
10. # < 有些同学说用 O_DSYNC 方式，在写入时，有很大提升。可官网说：
11. # < InnoDB does not use O_DSYNC directly because there have been problems with it on many varieties of Unix.
12. # < 也就是少部分系统可以稳定使用，或者已经过系统验证确认。
13. # < 个人认为，默认值最可靠
14. # innodb_flush_method = fsync

复制代码

五、写入综合测试

测试目的：
经过【四、SQL服务参数优化】的配置后，分别测试空表状态批量写入200万和500万数据的耗时。
测试场景：
一台几年前的破笔记本，创建的虚拟机4C8G，Docker + MySQL8.0.31。
桌面应用以36个线程写入随机数据。
批量写入脚本：INSERT INTO TABLE ... VALUES (...),(...),(...) 的方式，INSERT 每次1000条。
表结构：聚集索引 + 两列的非聚集索引 + 一组三列的组合索引；(参照 1.5.2)

1. [mysqld]
2. # default = 5000
3. open_files_limit = 10000
4. # 计算公式：MAX((open_files_limit-10-max_connections)/2,400)
5. # default = 4000
6. table_open_cache = 4495
7. # 超过16核的硬件，肯定要增加，以发挥出最大性能
8. # default = 16
9. table_open_cache_instances = 32

复制代码

测试结果：
逐步追加MySQL服务参数配置+表分区，最终有了成倍的性能提升；每次测试后的日志记录了优化的递进过程；
如下图：(日志不够细，懂就行)

经过逐步优化：
  200万数据写入耗时从 9分4秒，提升到 5分50秒；(无表分区)
  500万数据写入耗时从 41分33秒，提升到 6分50秒。(有表分区)

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

---

欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)

Powered by Discuz! X3.4