MySQL8.0性能优化（实践）

一台几年前的旧笔记本电脑的虚拟系统运行环境，作为本次实践的运行工具，仅供参考。

案例环境：Linux、Docker、MySQLCommunity8.0.31、InnoDB。

过早的MySQL版本不一定适用本章内容，仅围绕 InnoDB 引擎的阐述。

一、索引

1.1 索引的管理

-- create 方式创建
create [unique] index {index_name} on {tab_name}({col_name}[(length)]);
-- alter表 方式创建
alter {tab_name} add [unique] index {index_name} on ({col_name}[(length)]);
-- 创建组合索引
create index {index_name} on ({col_name1}[(length)], {col_name2}[(length)], {col_name3}[(length)]);

-- unique：唯一索引
-- col_name：一列为单列索引；逗号隔开的多列为组合索引
-- length：字段中前几个字符有效，避免无限长度（通常能够明显区分值即可的长度；如：员工表的Email，@后面都一样）


-- 查看表中的索引
show index from {tab_name};

-- 删除索引
drop index {index_name} on {tab_name};

1.2 索引创建的场景

过多查询的表，过少写入的表。

数据量过大导致的查询效率慢。

经常作为条件查询的列。

批量的重复值，不适合创建索引；比如<业务状态>列

值过少重复的列，适合创建索引；比如<usercode>、<email>列

1.3 理想的索引特征

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

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

1.4 非主键索引

where全部为and时，无所谓位置，都会命中索引（当多个条件中有索引的时候，并且关系是and的时候，会自动匹配索引区分度高的）

where后面为 or 时，索引列依影响数据范围越精确按序靠前写。

1.5 索引的使用

使用原则：

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

-- 按现有数据，计算哪个列最精确；越精确的列，位置越靠前优先。
 select sum(depno=28), sum(username like 'Sol%'), sum(position='dev') from tab_emp;
 +---------------+---------------------------+---------------------+
 | sum(depno=28) | sum(username like 'Sol%') | sum(position='dev') |
 +---------------+---------------------------+---------------------+
 | 366551        | 3                         | 109                 |
 +---------------+---------------------------+---------------------+
-- 由此得出：username列的范围最精确，应该放到where后的首位；不在组合索引的列放到最后。

-- 如下组合索引的创建方式：
create index {index_name} on {tab_name}(username,position,depno);
-- 如下组合索引的查询方式：
select username,position,depno from tab_emp where username like 'Sol%' and position='dev' and depno=106 and age<27

1.5.1 使用索引查询

这里准备两张两千万相同表数据，测试效果如下图：

MySQL8.0性能优化（实践）

1.5.2 组合索引的使用

表创建的组合索引，如下图：

MySQL8.0性能优化（实践）

两千万数据表，组合索引查询效果，如下图：

MySQL8.0性能优化（实践）

总结：组合索引所包含的列，尽量在where, order中写全，非索引列或过少的组合索引列可能不会产生索引效果。

1.5.3 高性能分页查询

通常MySQL分页时用到的limit，当limit值过大时，查询效果会很慢。

当如 limit 9000000,10 时，需要先查询出900万数据，再抛掉900万数据；这900万的过程可否省略？

假如：每次查询一页时，把当前页的最后一条数据的重要栏位都做记录，并标识是第几页；当查询它的下页时，拿它的最后一条数据的重要栏位作为追加的查询条件，如何呢...??

下图示例：usercode 为主要的索引及排序字段，上页的最后一条作为追加条件，再往下取5条，效果有了显著提升。(排序列重复数据呢?) 当然适用于类似code、time等这样重复数据较少的列。

MySQL8.0性能优化（实践）

1.6 索引覆盖，避免回表查询

当查询的列中包含了非索引列，系统相当于扫描了两遍数据，如果能只扫描了一遍，也提高了查询效率。

回表查询的过程：

先按已有索引查询到数据，得出此数据的主键值
再按主键值，再次检索出具体的数据，获取其它列的值

查询涉及到的列都为组合索引列时，包括：select、where、order、group等，索引覆盖(索引下推)，避免回表查询。

避免使用*，以避免回表查询；不常用的查询列或text类型的列，尽量以单独的扩展表存放。

通常列表数据需要的列并不多，查询的时候可以考虑为索引列；通常详细信息时涵盖的列多，可通过主键单独查询。

1.7 命中索引

1.7.1 无效索引

列类型转换可能会导致索引无效；如：

字符转数值，会导致索引无效
数值转字符，不影响索引。

不建议类型的转换，尽量按原类型查询。

条件中的函数导致索引无效；索引列不能用在函数内。如：where abs(Id) > 200

条件中的表达式导致索引无效；如：where (Id + 1) > 200

避免单列索引与组合索引的重复列；在组合索引中的列，去除单列索引。

全模糊查询导致索引无效；匹配开头不会影响索引，如 'Sol%'；全模糊或'%Sol'时无效。

1.7.2 Explain

显示执行过程，查看是否命中索引

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

在通常情况下，能不能命中索引，取决于索引列的值重复程度；如果是极少重复的值，就很容易命中索引。如果类似于状态或类型的值，重复程度很高，就很难命中索引，这是MySQL自动取舍的结果。

比如：没有索引的列-电话号码，有索引的列-部门，那么很难命中部门索引，因为MySQL认为[电话号码]更精确；或者使用force强行命中，通常MySQL的自动取舍是最有效的。

1.8 查询总结

避免使用*，以避免回表查询。

不常用的查询列或text类型的列，尽量以单独的扩展表存放。

条件避免使用函数。

条件避免过多的or，建议使用in()/union代替，in中的数据不可以极端海量，至少个数小于1000比较稳妥。

避免子查询，子查询的结果集是临时表不支持索引、或结果集过大、或重复扫描子表；以join代替子查询，尽量以inner join代替最为妥当。

避免使用'%Sol%'查询，或以'Sol%'代替。

二、表分区

表分区也就是把一张物理表的数据文件分成若干个数据文件存储，使得单个数据文件的量有限，有助于避免全表扫描数据，提升查询性能。

那，跨区查询的性能影响有多大，从整体看，表分区还是带来了不少的性能提升。

如果表中有主键列，分区列必须是主键列之一。比如：又有自增主键，又想按年份分区，那主键就是组合索引咯。(id+date)

2.1 分区的种类

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

-- 表创建 HASH 分区12个
CREATE TABLE clients (
    id INT,
    fname VARCHAR(30),
    lname VARCHAR(30),
    signed DATE
)
PARTITION BY HASH(MONTH(signed))
PARTITIONS 12;

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

-- 表创建12个 KEY 分区
CREATE TABLE clients_lk (
    id INT,
    fname VARCHAR(30),
    lname VARCHAR(30),
    signed DATE
)
PARTITION BY LINEAR KEY(signed)
PARTITIONS 12;

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

-- 按年份创建范围分区
CREATE TABLE tr (
    id INT,
    name VARCHAR(50),
    purchased DATE
)
PARTITION BY RANGE(YEAR(purchased)) (
    PARTITION p0 VALUES LESS THAN (1990),
    PARTITION p1 VALUES LESS THAN (1995),
    PARTITION p2 VALUES LESS THAN (2000)
);

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

-- LIST 分组包含方式
CREATE TABLE tt (
    id INT,
    data INT
)
PARTITION BY LIST(data) (
    PARTITION p0 VALUES IN (5, 10, 15),
    PARTITION p1 VALUES IN (6, 12, 18)
);

2.2 分区的管理

新增 HASH/KEY 分区

-- 将原来的 12 个分区合并为 8 个分区
ALTER TABLE clients COALESCE PARTITION 4;
-- 在原有的基础上增加 6 个分区
ALTER TABLE clients ADD PARTITION PARTITIONS 6;

新增 RANGE/LIST 分区

-- RANGE 追加分区
ALTER TABLE tr ADD PARTITION (PARTITION p3 VALUES LESS THAN (2010));
-- LIST 追加新分区（不可包含已存在的值）
ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21));

变更 RANGE/LIST 分区

-- RANGE 拆分原有分区（重组分区）
ALTER TABLE tr REORGANIZE PARTITION p0 INTO (
        PARTITION n0 VALUES LESS THAN (1980),
        PARTITION n1 VALUES LESS THAN (1990)
);
-- RANGE 合并相邻分区
ALTER TABLE tt REORGANIZE PARTITION s1,s2 INTO (
    PARTITION s0 VALUES LESS THAN (1980)
);
-- LIST 重组原有分区
ALTER TABLE tt REORGANIZE PARTITION p1,np INTO (
    PARTITION p1 VALUES IN (6, 18),
    PARTITION np VALUES in (4, 8, 12)
);

删除指定分区

-- 丢掉指定分区及其数据
ALTER TABLE {TABLE_NAME} DROP PARTITION p2,p3;
-- 删除指定分区，保留数据
ALTER TABLE {TABLE_NAME} TRUNCATE PARTITION p2;
-- 删除表全部分区，保留数据
ALTER TABLE {TABLE_NAME} REMOVE PARTITIONING;

分区详细信息

-- 查询指定分区的数据
SELECT * FROM tr PARTITION (p2);
-- 查询各分区详细
SELECT * FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tt';
-- 查看某个分区的状态
ALTER TABLE tr ANALYZE PARTITION p3;

修复分区

-- 检查分区是否损坏
ALTER TABLE tr CHECK PARTITION p1;
-- 修复分区
ALTER TABLE tr REPAIR PARTITION p1, p2;
-- 优化分区，整理分区碎片
ALTER TABLE tr OPTIMIZE PARTITION p0, p1;
-- 当前分区数据，重建分区
ALTER TABLE tr REBUILD PARTITION p0, p1;

三、查询综合测试

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

MySQL8.0性能优化（实践）

数据量大了，查询慢；加索引了，数据量越大，写入越慢；

还是物理分表好呀~

四、SQL服务参数优化

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

4.1 Connections

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

4.2 缓冲区 Buffer

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

4.3 Sort merge passes

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

4.4 I/O 线程数

[mysqld]
# 异步I/O子系统
# default = NO
innodb_use_native_aio = NO
# 读数据线程数
# default = 4
innodb_read_io_threads = 32
# 写入数据线程数
# default = 4
innodb_write_io_threads = 32

4.5 Capacity 容量

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

4.6 Open cache

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

五、写入综合测试

测试目的：

经过【四、SQL服务参数优化】的配置后，分别测试空表状态批量写入200万和500万数据的耗时。

测试场景：

一台几年前的破笔记本，创建的虚拟机4C8G，Docker + MySQL8.0.31。

桌面应用以36个线程写入随机数据。

批量写入脚本：INSERT INTO TABLE ... VALUES (...),(...),(...) 的方式，INSERT 每次1000条。

表结构：聚集索引 + 两列的非聚集索引 + 一组三列的组合索引；(参照 1.5.2)

+------------+--------------+------+-----+-------------------+-------------------+
| Field      | Type         | Null | Key | Default           | Extra             |
+------------+--------------+------+-----+-------------------+-------------------+
| id         | bigint       | NO   | PRI | NULL              | auto_increment    |
| usercode   | varchar(32)  | YES  | MUL | NULL              |                   |
| title      | varchar(128) | YES  |     | NULL              |                   |
| age        | int          | YES  | MUL | NULL              |                   |
| gender     | char(1)      | YES  |     | 男                |                   |
| phone      | char(11)     | YES  |     | NULL              |                   |
| job        | varchar(32)  | YES  |     | NULL              |                   |
| department | varchar(32)  | YES  |     | NULL              |                   |
| createtime | datetime     | NO   | PRI | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+--------------+------+-----+-------------------+-------------------+

测试结果：

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

经过逐步优化：

200万数据写入耗时从 9分4秒，提升到 5分50秒；(无表分区)

500万数据写入耗时从 41分33秒，提升到 6分50秒。(有表分区)

秒客网

MySQL8.0性能优化（实践）

一、索引

1.1 索引的管理

1.2 索引创建的场景

1.3 理想的索引特征

1.4 非主键索引

1.5 索引的使用

1.5.1 使用索引查询

1.5.2 组合索引的使用

1.5.3 高性能分页查询

1.6 索引覆盖，避免回表查询

1.7 命中索引

1.7.1 无效索引

1.7.2 Explain

1.8 查询总结

二、表分区

2.1 分区的种类

2.2 分区的管理

三、查询综合测试

四、SQL服务参数优化

4.1 Connections

4.2 缓冲区 Buffer

4.3 Sort merge passes

4.4 I/O 线程数

4.5 Capacity 容量

4.6 Open cache

五、写入综合测试

相关文章