MySQL性能优化方法和实践

目录

前置准备

实验用表

CREATE TABLE `user` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` tinyint unsigned DEFAULT '0',
  `gender` enum('man','woman') DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name_age` (`name`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=20006 DEFAULT CHARSET=utf8;

然后，往数据库批量插入几百万条数据，用于测试

硬件优化

  • 硬盘：使用磁盘阵列，高性能的固态硬盘，提高硬盘的读写性能。
  • 网络：提高带宽，降低网络延迟
  • 内存：保证服务器运行有充足的内存

查询缓存

查询缓存已经在MySQL 8中移除。另外，ORM框架基本基本都提供了必要的缓存机制，项目还可以本地缓存或分布式缓存，完全可以替代MySQL自带的查询缓存功能。

开启查询缓存后，同样的查询条件和数据的情况下，会直接从缓存中返回结果。

查询条件：查询本身、查询的数据库、客户端协议版本好等。

查询缓存不命中的情况

  1. 查询在任何字符上的不同，如：空格、注释的任何不同，都会导致缓存不命中
  2. 如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、MySQL库中的系统表，查询结果不会被缓存
  3. (**)缓存建立后，MySQL查询缓存系统会跟踪查询中涉及的每张表，如果这些表发生变化（表数据或表结构），那么这张表相关的所有缓存数据都会失效。

查询缓存的代价

只要对表执行写入操作，就会导致当前表的所有缓存失效。如果查询缓存占用大量的内存，缓存失效操作会导致系统僵死。缓存失效是通过全局锁实现的，所有当前表的查询、缓存失效操作都会等待全局锁的释放。

使用MySQL的查询缓存需要考虑缓存的代价和对性能的提升。

表结构设计

数据类型

  • 在确保字段的存储范围的情况下，使用最小的数据类型。
  • 选择更简单的类型。如选择MySQL内建的日期类型，而不是字符串；整型比字符串操作代价更低
  • 避免NULL值。可以为NULL的字段会使用更多的存储空间，创建索引成本更高。

整数类型

  • 有多种整数类型：TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT
  • 通过添加UNSIGNED属性，变为无符号整数
  • 指定宽度：为整数指定宽度，并不会影响值的取值范围，仅影响部分MySQL客户端显示字符的个数

实数类型

  • DECIMAL和NUMERIC使用字符串的形式存储，所以不会丢失精度，可以用于保存货币数据
  • FLOAT和DOUBLE使用二进制存储，会丢失精度

字符串类型

  • CHAR：
    • 存储定长的、短字符串
    • 如果插入的数据长度小于char的固定长度时，则用空格填充；
    • 存区速度要比varchar更快
    • char最多能存放255个字符
  • VARCHAR：
    • 不定长的、长字符串。
    • 因为长度不固定，存取速度慢
    • VARCHAR使用1到2个字节保存字符串长度，所以字符串允许的最大长度为65535

(**)BLOB和TEXT —— 存储大的数据

  • BLOB和TEXT是用于存储很大的数据的字符串类型。
  • BLOB存储二进制数据，BLOB没有字符集；TEXT存储大字符串，有字符集
  • 索引：无法对BLOB和TEXT直接建立索引

DATETIME和TIMESTAMP —— 日期类型

MySQL数据库时间类型数据存储建议：https://javaguide.cn/database/mysql/some-thoughts-on-database-storage-time.html#_5-总结

  • DATETIME：
    • 精度秒
    • 范围：1001年到9999年
    • 8个字节，范围大
    • 时区无关
  • TIMESTAMP：
    • 精度秒
    • 从1970-01-01 00:00:00~2038年
    • 4个字节，范围小
    • 时区相关：会根据时区变化，而自动变化

示例

引用自MySQL数据库时间类型数据存储建议

查看数据：

select date_time,time_stamp from time_zone_test;

结果：

+---------------------+---------------------+
| date_time           | time_stamp          |
+---------------------+---------------------+
| 2020-01-11 09:53:32 | 2020-01-11 09:53:32 |
+---------------------+---------------------+

现在我们运行

修改当前会话的时区:

set time_zone='+8:00';

再次查看数据：

+---------------------+---------------------+
| date_time           | time_stamp          |
+---------------------+---------------------+
| 2020-01-11 09:53:32 | 2020-01-11 17:53:32 |
+---------------------+---------------------+

范式和反范式

范式：

  • 优点：

    • 重复数据少
    • 更新操作修改的数据少，速度快

  • 缺点：

    • 复杂查询需要关联多张表

反范式：

  • 优点：
    • 通过冗余的数据，可以避免一些跨库跨表的查询，提高查询性能
  • 缺点：
    • 需要维护多份数据，使更新数据的逻辑更加复杂

索引优化

参考：

索引：用于快速查找数据的数据结构。

索引的优缺点

  • 优点：提高检索效率
  • 缺点：
    • 时间：索引的创建和维护是需要消耗时间的，记录的插入、删除和更新都需要同时维护索引
    • 空间：索引数据需要占用空间

索引分类

  • B+树索引：适用于顺序查找，范围查找
  • 哈希索引：适用于等值查找
  • 全文索引：适用于长文本中关键字的搜索

B+树索引

B+树数据结构

参考：

B+树是一棵多路、平衡、查找树。

  • 多路：B+树的扇出不同于二叉树，可以大于2
  • 平衡：每一个叶子页到根的距离相同
  • 值的存储顺序：值是按大小顺序存储在叶子页上，并且叶子页之间使用指针连接在一起

B+树索引

B+树索引是B+树在数据库中的实现。

  • 高扇出：B+树索引的树高度往往在24层，这样可以**控制磁盘IO的次数**在24次。

B+树索引分类

索引可以分为：

  • 聚集索引：叶子节点存放完整的表记录，即索引结构和表数据一起存放，一张表只有一个聚集索引
  • 非聚集索引（或辅助索引）：叶子节点存放指向表记录的指针。索引结构和表数据分开存放，一张表可以有多个非聚集索引。

不同的存储引擎B+树索引的使用方式也不同：

  • InnoDB中的主键索引是聚集索引；其它列的索引为非聚集索引
  • MyISAM中所有的索引都是非聚集索引

独立列的索引

  • 索引列不能是表达式的一部分，或函数的参数，索引不生效
  • LIKE操作：通配符在右边，索引生效；以通配符开头的LIKE比较，索引不生效

索引列是表达式一部分的示例

mysql> EXPLAIN SELECT * FROM user WHERE id =10 ;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

#执行计算，但索引列不是表达式的一部分，索引生效
mysql> EXPLAIN SELECT * FROM user WHERE id =10+1 ;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

#索引列是表达式的一部分，索引不生效
mysql> EXPLAIN SELECT * FROM user WHERE id + 1 = 11 ;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 19652 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+

LIKE示例

mysql> EXPLAIN SELECT * FROM user WHERE name LIKE 'test%';
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | user  | NULL       | range | index_name_age | index_name_age | 152     | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+

# 通配符在左，不走索引
mysql> EXPLAIN SELECT * FROM user WHERE name LIKE '%test';
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 19652 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+

前缀索引 —— 优化长字符串索引列

可以为长字符串创建前缀索引，减小索引的空间占用，提高查找效率。

长字符串优化的方式有两种：

  1. 前缀索引
  2. 自建哈希属性列

前缀索引的优缺点

  • 优点：
    • 索引更小，查询更高效
  • 缺点：
    • 无法在GROUP BY和ORDER BY子句中使用索引
    • 无法用于覆盖扫描

前缀索引的建立注意事项

  1. 前缀索引的选择性要和完整列的选择性基本一致
  2. 前缀索引的值分布要和完整列的值分布基本一致

组合索引

尽可能使用组合索引代替单个索引。

组合索引：在多个属性列上创建的索引。

索引列的组合顺序：

  • 首先，按最左列排序
  • 然后，第二列，以此类推

最左前缀原则

  • 如果不是从组合索引的第一列匹配，无法使用组合索引
  • 不能跳过组合索引中的列：必须按组合索引中属性列的顺序匹配，如果跳过其中的索引列，会导致后续的条件匹配无法使用索引
  • 范围查询之后无法使用索引：如果存在属性列的范围查询，后续的属性列的匹配，无法使用索引
WHERE和ORDER BY
-- name做等值匹配，age做排序，走索引
mysql> EXPLAIN SELECT * FROM user WHERE name='test' ORDER BY age;
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ref  | index_name_age | index_name_age | 152     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------+

-- name做LIKE的右通配符匹配，age做排序，走索引
mysql> EXPLAIN SELECT * FROM user WHERE name LIKE 'test%' ORDER BY age;
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | user  | NULL       | range | index_name_age | index_name_age | 152     | NULL |    1 |   100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+---------------------------------------+
ORDER BY和LIMIT
  1. ORDER BY中字段需要满足索引的最左前缀原则
  2. 优化器会根据LIMIT指定的记录数选择走索引，或者全表扫描
  3. ORDER BY中的字段的排序顺序不同，也不走索引
-- name是有索引的，LIMIT为10，走索引
mysql> EXPLAIN SELECT * FROM user ORDER BY name LIMIT 10;
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key            | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | index | NULL          | index_name_age | 154     | NULL |   10 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-------+

-- name和age是有索引的，LIMIT为10，走索引
mysql> EXPLAIN SELECT * FROM user ORDER BY name,age LIMIT 10;
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key            | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | index | NULL          | index_name_age | 154     | NULL |   10 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-------+

-- name和age是有索引的，LIMIT为10，但，排序顺序不同，不走索引
> EXPLAIN SELECT * FROM user ORDER BY name,age desc LIMIT 10;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 19652 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+

-- name有索引，但没有指定LIMIT变为全表扫描
mysql> EXPLAIN SELECT * FROM user ORDER BY name;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 19652 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+

-- name有索引，LIMIT为100，不走索引
mysql> EXPLAIN SELECT * FROM user ORDER BY name LIMIT 100;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 19652 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+

-- name和age有索引，LIMIT为100，不走索引
mysql> EXPLAIN SELECT * FROM user ORDER BY name,age LIMIT 100;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 19652 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+

--通过子查询实现延迟关联，然后，根据主键索引做等值的匹配
mysql> EXPLAIN SELECT * FROM user ,(SELECT id FROM user ORDER BY name LIMIT 100) name_id WHERE user.id = name_id.id;
+----+-------------+------------+------------+--------+---------------+----------------+---------+------------+------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key            | key_len | ref        | rows | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+----------------+---------+------------+------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL          | NULL           | NULL    | NULL       |  100 |   100.00 | NULL        |
|  1 | PRIMARY     | user       | NULL       | eq_ref | PRIMARY       | PRIMARY        | 4       | name_id.id |    1 |   100.00 | NULL        |
|  2 | DERIVED     | user       | NULL       | index  | NULL          | index_name_age | 154     | NULL       |  100 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+----------------+---------+------------+------+----------+-------------+

组合索引建立的注意事项

  • 通常，将属性列选择性最高的列放到最前面
  • 需要考虑属性列的值分布
  • 可以根据查询频率调整索引类顺序

覆盖索引

覆盖索引：在辅助索引中就可以获取到查询所需的全部数据（包括WHERE子句所需的条件和SELECT子句返回的结果），无需回表到具体的记录中获取。如：index_a_b(a,b)可以覆盖SELECT a,b FROM t WHERE a = 1 AND b =2这个语句。

示例

mysql> EXPLAIN SELECT name,age FROM user WHERE name='test' and age = 11;
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ref  | index_name_age | index_name_age | 154     | const,const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+-------------+

可以看到EXPLAIN的执行结果，Extra为Using index，key为index_name_age，说明索引覆盖了。

其它索引内容补充

使用OR操作符，它前面和后面的字段都需要加索引，否则索引失效

-- id和name都有索引，可以看到type=index_merge，即索引合并
mysql> EXPLAIN SELECT * FROM user WHERE id=10000 OR name = 'test';
+----+-------------+-------+------------+-------------+------------------------+------------------------+---------+------+------+----------+-------------------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys          | key                    | key_len | ref  | rows | filtered | Extra                                                 |
+----+-------------+-------+------------+-------------+------------------------+------------------------+---------+------+------+----------+-------------------------------------------------------+
|  1 | SIMPLE      | user  | NULL       | index_merge | PRIMARY,index_name_age | index_name_age,PRIMARY | 152,4   | NULL |    2 |   100.00 | Using sort_union(index_name_age,PRIMARY); Using where |
+----+-------------+-------+------------+-------------+------------------------+------------------------+---------+------+------+----------+-------------------------------------------------------+

-- address没有索引，查询变成了全表查询
mysql> EXPLAIN SELECT * FROM user WHERE id=10000 OR name = 'test' OR address = 'aa';
+----+-------------+-------+------------+------+------------------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys          | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+------------------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | PRIMARY,index_name_age | NULL | NULL    | NULL | 19652 |    19.00 | Using where |
+----+-------------+-------+------------+------+------------------------+------+---------+------+-------+----------+-------------+

IN关键字

  • IN的字段如果有索引，走索引
  • NOT IN，不走索引，会变成全表扫描
-- id字段为主键索引
mysql> EXPLAIN SELECT * FROM user WHERE id IN (1,10000,1000);
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    3 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

mysql> EXPLAIN SELECT * FROM user WHERE id NOT IN (1,10000,1000);
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL | 19652 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+

-- name字段有索引index_name_age
mysql> EXPLAIN SELECT * FROM user WHERE name IN ('aaa');
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ref  | index_name_age | index_name_age | 152     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------+

mysql> EXPLAIN SELECT * FROM user WHERE name NOT IN ('aaa');
+----+-------------+-------+------------+------+----------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys  | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+----------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | index_name_age | NULL | NULL    | NULL | 19652 |    50.01 | Using where |
+----+-------------+-------+------------+------+----------------+------+---------+------+-------+----------+-------------+

哈希索引

MySQL是不直接支持哈希索引的，InnoDB内部提供的一个“自适应哈希索引（adaptive hash index）”的功能，当某些索引值被频繁使用时，InnoDB会在内存中基于B-Tree索引之上再创建一个哈希索引。

哈希索引的优缺点

  • 优点：
    • 等值查找速度非常快
  • 缺点：
    • 哈希索引中，哈希值并不是按照属性列的顺序存储的，无法排序和范围查找
    • 不支持LIKE操作符的模糊查询
    • 需要考虑属性列的选择性，避免哈希冲突

哈希索引的使用

为长字符串创建哈希索引，压缩索引空间，提高等值查找效率。

哈希索引的维护：通过插入和更新记录时，通过触发器维护哈希索引。

全文索引 —— 长文本中关键字的查询

MySQL和PostgreSQL都提供了全文索引的实现，但是对于中文的支持都做的不好，如果需要全文检索，可以引入专门的搜索中间件，如Elasticsearch。

全文索引适用于长文本中关键字查询的应用场景。如果该场景使用B+树索引，会变成全表扫描，效率低下。

EXPLAIN —— 查看MySQL的执行计划

《explain | 索引优化的这把绝世好剑，你真的会用吗？》

用于查看SQL语句的执行计划，是SQL优化的利器！

查询优化

参考：

查询的执行过程

查询的执行过程：

  1. 客户端和服务端的连接器建立连接
  2. 客户端发送一条查询请求
  3. 服务端检查查询缓存（MySQL 8将查询缓存移除了），如果命中了缓存，则立刻返回缓存中的结果
  4. 分析器执行词法分析和语法分析
  5. 优化器生成执行计划
  6. 执行器执行生成的执行计划，调用存储引擎的API来执行查询
  7. MySQL将查询结果返回客户端

慢查询优化

EXPLAIN的使用：https://mp.weixin.qq.com/s?__biz=MzkwNjMwMTgzMQ==&mid=2247490262&idx=1&sn=a67f610afa984ecca130a54a3be453ab&chksm=c0ebc23ef79c4b2869dea998e413c5cbea6aeeea01ee74efc7c1a5fc228baa7beca215adf3ea&token=751314179&lang=zh_CN&scene=21#wechat_redirect

  1. 通过查看慢查询日志，找出可能存在慢查询的sql
  2. 通过EXPLAIN分析sql的执行计划，EXPLAIN非常重要，可以帮助分析MySQL的执行计划！重点关注：
    • key：使用的索引
    • key_len：判断索引是否充分使用
    • type：查看索引的类型
    • Extra：查看附加信息
  3. 优化sql，再次通过EXPLAIN查看。

避免SELECT *

除非有必要，否则避免使用SELECT *查询数据。SELECT *会查询表中所有的列数据，如果WHERE条件匹配的不是主键索引，会导致回表查询。

如：

SELECT * FROM user WHERE name LIKE 'a%';

尽量利用覆盖索引的写法：

SELECT id,name FROM user WHERE name LIKE 'a%';

这种写法，只要对表user创建了index(name)即可实现索引覆盖，从而避免回表。

批量插入代替循环插入

  • 循环插入会存在多次网络IO，多次数据库请求，消耗资源；
  • 批量插入仅一次网络IO，一次数据库请求；而且插入效率会比循环插入高很多

循环插入

for(User user: userList){
   userMapper.insert(user):
}

insert into user(name,age,gender) 
values('test',18,'man');

批量插入

userMapper.insertBatch(userList):

insert into user(name,age,gender) 
values('test',18,'man'),('test1',19,'woman'),('test2',20,'man');

批量插入的数据量不宜过大，超过500条时，需要分批，避免服务长时间阻塞。

切分大的查询

大的查询会占用大量的资源，可能导致服务器阻塞，接口超时。可以通过LIMIT切分大查询为多个小查询，分多次返回结果。

示例

这种方式称为书签方式，也称为增量查询。

大查询：

SELECT * FROM user;

切分为多个小查询

SELECT * FROM user WHERE id > #{lastId} LIMIT 10000;

lastId：每次查询完成后，保存本次查询最大的id，供下一次查询时使用

使用COUNT(*)代替COUNT(列名)或COUNT(常量)

COUNT(*)会统计值为NULL的记录，COUNT(列名)则会跳过列值为NULL的记录

优化大偏移量的LIMIT分页查询（主键有序）

在主键有序递增的前提下，如要查询id为[10000,10009]范围的数据。使用LIMIT做限制将导致全表扫描。MySQL会先扫描从前往后扫描10009条数据，然后取出10条数据返回，非常浪费资源。

示例

mysql> EXPLAIN SELECT user.* FROM user LIMIT 10000,10;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 19652 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+

通过主键过滤行：

mysql> EXPLAIN SELECT user.* FROM user id>10000 LIMIT 10;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '>10000 LIMIT 10' at line 1
mysql> EXPLAIN SELECT user.* FROM user WHERE id>10000 LIMIT 10;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 9826 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

mysql> EXPLAIN SELECT user.* FROM user WHERE id BETWEEN 10000 AND 10010 ;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    6 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

优化大偏移量的LIMIT分页查询

参考：

对于大偏移量的LIMIT分页查询，如果不能充分利用索引，将导致全表扫描，将结果集存在内存中的临时表，做完排序，再获取所需的记录，非常消耗内存和时间。

有两种优化方法：

  • 延迟关联：
    1. 通过索引覆盖的方式，一次性获取所有的主键id存入临时表；
    2. 主表通过主键索引关联临时表，获取最终的结果集
  • 书签方式：如果是针对主键索引查询记录的，可以记忆上次查询主键的最大值作为书签，那么翻页时，直接使用书签+LIMIT的方式保证性能。

延迟关联示例

在有500万条数据的user表中，使用gender（选择性很低，有建立对应的索引）作为过滤条件，取第1000000条开始的10条数据：

未优化前：

mysql> SELECT  * FROM USER WHERE gender = 'man' LIMIT 1000000,10;
+---------+--------+-----+--------+--------------------+-------------+
| id      | name   | age | gender | address            | address_crc |
+---------+--------+-----+--------+--------------------+-------------+
| 2009987 | Ysgwnm |  93 | man    | 杭州市西湖区 |  4236428381 |
| 2009989 | Dejbco |  11 | man    | 安徽省黄山市       |  1795666272 |
| 2009992 | Nolbwq |  54 | man    | 杭州市西湖区 |  4236428381 |
| 2009993 | Frwsvp |  21 | man    | 敦煌               |   242143909 |
| 2009995 | Cdizbc |  16 | man    | 敦煌               |   242143909 |
| 2009998 | Gefhme |  91 | man    | 安徽省黄山市       |  1795666272 |
| 2009999 | Kqtpyn |  40 | man    | 杭州市西湖区 |  4236428381 |
| 2010002 | Chlovp |  71 | man    | 杭州市西湖区 |  4236428381 |
| 2010004 | Fzctet |   3 | man    | 杭州市西湖区 |  4236428381 |
| 2010007 | Yjheyg |  67 | man    | 安徽省黄山市       |  1795666272 |
+---------+--------+-----+--------+--------------------+-------------+
10 rows in set (4.33 sec)

查询过程分析：

  1. 服务器根据根据gender值和LIMIT在gender索引查询出一条对应的主键
  2. 根据主键回表查询记录
  3. 因为是10条记录，存储引擎共会执行10次上述操作，所以性能低。

延迟关联策略

mysql> SELECT user.* FROM user ,(SELECT id FROM user WHERE gender='man' LIMIT 1000000,10) as x WHERE user.id=x.id;
+---------+--------+-----+--------+--------------------+-------------+
| id      | name   | age | gender | address            | address_crc |
+---------+--------+-----+--------+--------------------+-------------+
| 2009987 | Ysgwnm |  93 | man    | 杭州市西湖区 |  4236428381 |
| 2009989 | Dejbco |  11 | man    | 安徽省黄山市       |  1795666272 |
| 2009992 | Nolbwq |  54 | man    | 杭州市西湖区 |  4236428381 |
| 2009993 | Frwsvp |  21 | man    | 敦煌               |   242143909 |
| 2009995 | Cdizbc |  16 | man    | 敦煌               |   242143909 |
| 2009998 | Gefhme |  91 | man    | 安徽省黄山市       |  1795666272 |
| 2009999 | Kqtpyn |  40 | man    | 杭州市西湖区 |  4236428381 |
| 2010002 | Chlovp |  71 | man    | 杭州市西湖区 |  4236428381 |
| 2010004 | Fzctet |   3 | man    | 杭州市西湖区 |  4236428381 |
| 2010007 | Yjheyg |  67 | man    | 安徽省黄山市       |  1795666272 |
+---------+--------+-----+--------+--------------------+-------------+
10 rows in set (0.30 sec)

整个查询性能为原来的13倍。

延迟关联过程分析：

  1. 通过子查询存储引擎一次将10条主键Id都查出，存入临时表，放置在表x
  2. 再通过表x中的主键关联原表获取所需的行

MySQL 5.6引入了MRR（Multi-Range Read）的机制，优化查询过程，延迟关联等优化方式可能也用不着。

优化子查询

子查询的执行

FROM子句的子查询：

  1. 先执行子查询，并将结果集放到一个临时表
  2. 将这个临时表当作一个普通表对待。

子查询的优化

因为子查询会将查询结果存放到临时表，会有一些额外的性能消耗。多数场景下，尽量使用关联查询代替子查询。

优化关联查询

关联查询的执行（嵌套循环关联）

  1. MySQL在一个表中循环取出单条数据
  2. 再嵌套循环到下一个表中寻找匹配的行；
  3. 依次嵌套执行，直到找到表中匹配的行为止。

关联查询的优化

根据阿里巴巴开发者手册的规定，关联表的数量不应该超过3个。

优化器多数时可以自动选择出成本最小的关联顺序，无需开发人员指定；也可以使用STRAIGHT_JOIN指定所需关联顺序。

  • 小表驱动大表：用小的结果集驱动大的结果集，从而减少外层循环的数据量
  • 在被关联表上创建索引。如：A INNER JOIN B ON A.id =B.id，那么需要在B.id上创建索引
  • 增加冗余字段，避免表关联

小表驱动大表的思考

考虑表数据的存储是按页连续存储到磁盘的，如果大表在外循环小表在内循环，那么外循环次数多，每次访问小表都没法使磁盘页满载，就会导致磁盘IO次数多。而大表在内循环的话，MySQL一次磁盘IO都能满载，就能降低总的磁盘IO数，从而提升查询效率。

优化UNION查询

UNION查询的执行

  1. 先将一系列的单表查询结果放到一个临时表
  2. 合并查询结果：读取临时表中的数据完成UNION查询，写入到临时表，如果不是UNION ALL，会执行唯一性检查，由于临时表没有索引，会非常的耗时
  3. 返回临时表的数据

UNION查询的优化

  1. 将WHERE、LIMIT和ORDER BY等条件尽量内推到子查询，最大可能的利用索引，减少临时表中的数据量
  2. 除非有必要MySQL消除重复行，否则使用UNION ALL代替UNION，避免MySQL对整个临时表执行唯一性检查（代价很大）

主从复制

参考：

主从复制：将一个服务器的数据同步到多台服务器。

主从复制原理

复制的概要流程：

  1. 主库把数据的更改记录到二进制日志（Binary Log）
  2. 备库将主库上的二进制日志复制到自己的中继日志（Relay Log）
  3. 备库读取中继日志中的事件，将其重放到备库数据。

过程细节

  • 主库二进制日志的写入：在每次准备提交事务前，主库将数据更新的事件记录到二进制日志中。在二进制日志记录完成后，主库通知存储引擎提交事务。
  • 主库会启动二进制转储（binlog dump）线程：负责读取主库上二进制日志中的事件
  • 备库会启动两个线程：
    1. IO线程：负责跟主库建立客户端连接，读取二进制日志事件，写入中继日志
    2. SQL线程：从中继日志中读取事件，并在从库上执行，实现备库数据的更新
  • 备库同步主库数据的过程是串行的，在高并发场景下，从库的数据一定会比主库慢一些，即有延迟，会慢几十毫秒，甚至几百毫秒。

主从复制的应用场景

  1. 负载均衡：在读多写少的应用中，实现读性能的扩展，无法扩展写性能
  2. 高可用：主库宕机，通过从库实现故障恢复

拓扑结构

  1. 一主多从：适用于读多写少的场景
  2. 主库-分发主库-从库：由分发主库实现主库数据的分发，降低主库的负载；从库数据更新的延迟增加

MySQL主从库 + Java读写分离

参考：

通过Spring的AbstractRoutingDataSource + MyBatis的Interceptor实现。

基本实现步骤：

  1. Interceptor拦截Sql语句，根据Sql类型，选择读库或者写库，放置到ThreadLocal中
  2. AbstractRoutingDataSource通过ThreadLocal中的值，选择读库或写库，执行SQL语句

主从复制延迟优化

参考：

查看从库时延的命令：

SHOW SLAVE STATUS;

主从延迟解决方案：

  • 分库：单库的QPS大概几千，通过将一个主库拆分成多个主库，每个主库的并发就降低了，此时主从延时就可以忽略不急
  • 并行复制：从库开启多个线程，并行读取relay log中不同库的日志，然后并行重放不同库的日志。在主库QPS达到2000的高并发场景，仍然没有用，因为瓶颈在于主库，而不是从库。
  • 半同步复制（sime-sync）：不推荐使用，性能太差！主库写入binlog后，就会立即将数据同步到从库，从库将日志写入本地的relay log之后，返回一个ack给主库，主库接收至少一个从库的ack才认为完成写操作。

TODO 分库分表

参考：

针对分布式的、高并发的场景，分库分表是对数据库读写能力横向扩展的有效手段。

参考

部分图片来自于引用文章

posted on 2022-08-11 20:30  DaydayupLiu  阅读(72)  评论(0)  编辑  收藏  举报