【MySQL】数据库优化_mysql处理亿级数据

ORDER BY 的实现与优化

在MySQL中，ORDERBY的实现有如下两种类型：

• 一种是通过有序索引而直接取得有序的数据，这样不用进行任何排序操作即可得到满足客户端要求的有序数据返回给客户端；（索引覆盖、索引下推）

• 另外一种则需要通过MySQL的排序算法将存储引擎中返回的数据进行排序然后再将排序后的数据返回给客户端。

索引覆盖

一句话概述：就是要查询的列，在使用的索引中已经包含，被所使用的索引覆盖，这种情况称之为索引覆盖。

索引下推

索引下推是MySQL5.6版本以后引入的一种优化机制, 那什么又叫做索引下推呢？也就是将Server层筛选数据的工作，下推到引擎层处理。

MySQL索引下推（Index Condition Pushdown，简称ICP）是MySQL在5.6及以上版本中引入的一个优化器特性，用于在存储引擎层面减少回表次数，进而提高查询性能。

当你的查询中使用了复合索引，但在过滤条件中并不是使用索引的第一个字段，或者不是一个范围查询时，MySQL优化器可以利用索引下推来进一步减少回表次数。简单来说，就是让存储引擎在索引中过滤掉尽可能多的行，而不是仅仅停留在索引叶子节点上。

索引下推在MySQL5.6版本之后是默认开启的，可以通过命令set optimizer_switch='index_condition_pushdown=off|on';命令来手动管理。

CREATE TABLE users (
    id INT NOT NULL,
    name VARCHAR(20),
    age INT,
    INDEX idx_name_age (name, age)
);
1
2
3
4
5
6

SELECT * FROM users WHERE name LIKE 'Alice%' AND age > 30;
1

在没有使用索引下推的情况下，MySQL会先扫描索引idx_name_age，找到所有匹配name LIKE 'Alice%'的行后，再回表检查这些行的age是否大于30。

如果启用了索引下推，MySQL会在索引树上直接过滤掉age不大于30的行，减少回表的次数。

在实际的查询中，你可以通过EXPLAIN FORMAT=TREE查看查询计划来确认是否使用了索引下推。

MRR(Multi-Range Read)机制

Multi-Range Read简称为MRR机制，这也是和索引下推一同在MySQL5.6版本中引入的性能优化措施，那什么叫做MRR优化呢？

一般来说，在实际业务中我们应当尽量通过索引覆盖的特性，减少回表操作以降低IO次数，但在很多时候往往又不得不做回表才能查询到数据，但回表显然会导致产生大量磁盘IO，同时更严重的一点是：还会产生大量的离散IO，下面举个例子来理解。

而MRR机制就主要是解决这个问题的，针对于辅助索引的回表查询，减少离散IO，并且将随机IO转换为顺序IO，从而提高查询效率。

那MRR机制具体是怎么做的呢？MRR机制中，对于辅助索引中查询出的ID，会将其放到缓冲区的read_rnd_buffer中，然后等全部的索引检索工作完成后，或者缓冲区中的数据达到read_rnd_buffer_size大小时，此时MySQL会对缓冲区中的数据排序，从而得到一个有序的ID集合：rest_sort，最终再根据顺序IO去聚簇/主键索引中回表查询数据。

SET @@optimizer_switch='mrr=on|off,mrr_cost_based=on|off';
1

可以通过上述这条命令开启或关闭MRR机制，MySQL5.6及以后的版本是默认开启的。

Index Skip Scan索引跳跃式扫描

MySQL一定是遵循最左前缀匹配的，这句话在mysql8以前是正确的，没有任何毛病。但是在MySQL 8.0中，就不一定了。

CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2));
INSERT INTO t1 VALUES
(1,1), (1,2), (1,3), (1,4), (1,5),
(2,1), (2,2), (2,3), (2,4), (2,5);
INSERT INTO t1 SELECT f1, f2 + 5 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 10 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 20 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 40 FROM t1;
ANALYZE TABLE t1;

EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40;

1
2
3
4
5
6
7
8
9
10
11
12

在讲联合索引时，咱们提到过最左前缀匹配原则，也就是SQL的查询条件中必须要包含联合索引的第一个字段，这样才能命中联合索引查询，但实际上这条规则也并不是100%遵循的。因为在MySQL8.x版本中加入了一个新的优化机制，也就是索引跳跃式扫描，这种机制使得咱们即使查询条件中，没有使用联合索引的第一个字段，也依旧可以使用联合索引，看起来就像跳过了联合索引中的第一个字段一样，这也是跳跃扫描的名称由来。

> select version() 

version() |
----------+
5.6.40-log|

1 row(s) fetched.

> EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40

id|select_type|table|type |possible_keys|key    |key_len|ref|rows|Extra                   |
--+-----------+-----+-----+-------------+-------+-------+---+----+------------------------+
 1|SIMPLE     |t1   |index|             |PRIMARY|8      |   | 160|Using where; Using index|

1 row(s) fetched.

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

> select VERSION() 

VERSION()|
---------+
8.0.29   |

1 row(s) fetched.

> EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40

id|select_type|table|partitions|type |possible_keys|key    |key_len|ref|rows|filtered|Extra                                 |
--+-----------+-----+----------+-----+-------------+-------+-------+---+----+--------+--------------------------------------+
 1|SIMPLE     |t1   |          |range|PRIMARY      |PRIMARY|8      |   |  53|   100.0|Using where; Using index for skip scan|

1 row(s) fetched.

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

这里面的type指的是扫描方式，range表示的是范围扫描，index表示的是索引树扫描，通常情况下，range要比index快得多。
从rows上也能看得出来，使用index的扫描方式共扫描了160行，而使用range的扫描方式只扫描了16行。

MySQL 8.0中的扫描方式可以更快，主要是因为Using index for skip scan 表示他用到了索引跳跃扫描的技术。

索引跳跃扫描优化原理

mysql8.013后通过优化器帮我们加了联合索引，如下

SELECT f1, f2 FROM t1 WHERE f2 = 40;

执行的最终SQL:
SELECT f1, f2 FROM t1 WHERE f1 =1 and f2 = 40
UNION
SELECT f1, f2 FROM t1 WHERE f1 =2 and f2 = 40;

1
2
3
4
5
6
7

也就是说，虽然我们的SQL中，没有遵循最左前缀原则，只使用了f2作为查询条件，但是经过MySQL 8.0的优化以后，还是通过索引跳跃扫描的方式用到了索引了。

反之查询效率慢些。
故，我们不能依赖他这个优化，建立索引的时候，还是优先把区分度高的，查询频繁的字段放到联合索引的左边。

MySQL亿级大表怎么优化

一、线上事故回顾

1、事故现象

某一天，晚上凌晨12点30左右，突然收到线上某个mysal业务库实例CPU告警(使用率想升到70%)，除
此之外，还收到大量的慢SQL告警（大于1s的，超过100条）

2、排查事故思路

研发人员看到告營后，第一时问打开电脑，通过堡垒机，开始运程访问内网，主要排查思路如下：

• 2.1 根据经验初步预判數据库CPU蔬高的原因
  晚上12点30分钟左右，一般是业务低峰期，系統的流量理论上是也是低峰期，应用负载QPS比较低
  mysal应该是空闲状态比较合理，但是这个时候数据库的CPU出现突刺，从日第的20%鼠高到70%，有
  持续不断增加的趋势，大概率是有跑批的任务在大批量处理业务数据。

• 2.2 通过云监控观察数据库是否有大量慢SQL
  晚上12点30分钟左右，超过1s以以上的SQL有100多条，部分SQL执行时间超过20s，而且慢SQL产生的
  时间非常集中，此外，还有部分SQL处于lock状态，mysql CPU线程非常繁忙。

• 2.3 通过explain分析慢SQL性能
  从云监控系统导出top20的慢SQL语句，通过explain执行计划进行分析，看下SQL区分度比较高的查询
  宇段是否命中素引（看两个指标：扫描记录行数、命中素引的type：结果值从好到差依次是：system > const >eq_ref > ref > range > index > ALL)

  • system：表只有一行记录，这个是const的特例，一般不会出现，可以忽略
  • const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。
  • eq_ref：唯一性索引扫描，表中只有一条记录与之匹配。一般是两表关联，关联条件中的字段是主键或唯一索引。
  • ref：非唯一行索引扫描，返回匹配某个单独值的所有行
  • range：检索给定范围的行，一般条件查询中出现了>、<、in、between等查询
  • index：遍历索引树。通常比ALL快，因为索引文件通常比数据文件小。all和index都是读全表，但index是从索引中检索的，而all是从硬盘中检索的。
  • all：遍历全表以找到匹配的行

• 2.4 通过慢SQL分析反查代码是哪块业务处理不当引起的
  从云监控系统中导出top20的慢SQL语句，分析慢SQL，反查连接这个数据库对应应用的业务代码，可以快速定位是什么业务引起的。

3、初步结论

什么原因导致的

二、紧急处理方案

1、指导原则

尽可能保证mysal业务库实例CPU不被打挂，然后考虑优化SQL语句或者优化程序代码。

2、处理措施

2.1 找到锁表的慢SQL线程，执行kill id

--步骤1：使用root账户，查看当前正在运行的线程
SHOW FULL PROCESSLIST
--步發2：找到Lock状态的线程ID：9874。 command为waitting的就是锁住的表，info为执行某：
--步發3：执行kill命令，停掉1ock的线程
kill 9874:
1
2
3
4
5

2.2 找到耗时久的慢SQL线程，执行kill id

-- 步骤1：查询执行时间超过10s的线程，然后拼接成ki11语句
select
concat('kill '. id. ':') as 慢SQL
from information schema.processlist
where command != 'Sleep'
and time > 10
order by time desc;
-- 步骤2：得到步骤1的返回结果
慢SOL
kill 9874:
kill 9879;
kill 9983:
- 步骤3：执行步骤2返回的绪果
kill 9874:
kill 9879;
kill 9983:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

2.3 再次观察mysql cpu， 看看是否有明显的下降趋势

完成1，2步骤后，再次通过云监控系统观察mysal业务库安例CPU使用率，看看是否有明显的下降。如果mysal CPU没有明显的下降，这个时候就需要通过explain执行计划分析慢SQL，看下是否缺少素引或者命中的索引字段区分度太低，导致扫描的行数太多。

2.4 通过explain执行计划分析慢SQL

2.4.1优先考虑对大表加素引(成本低，见效快）

通过explain执行计划，分析当前慢SQL是否缺乏泰引或者命中的索引字段区分度太低，导致扫描的行数太多(百万或千万、甚至亿级别），我们一般在业务低峰期(一般是晚上12点以后)，通过在线DDL，给亿级大表添加索引或者更换索引(对于千万甚至亿级数据量的大表变更宇段或素引，一般大公司的做法是通过建新表、数据copy，rename、数据打平的方式进行平滑处理）

PS：如果表数据量在百万以下，可以不用等业务低峰期操作，直接通过在线DDL添加或者更换索(先建新索引，再删除素引)，可能会出现短暂的锁表，

2.4.2 优化慢SQL

2.4.2.1 检查索引是否失效

检查索引宇段是否存在：类型转换、函数计算、全模糊查询、not in、 or、素引项存在null值

2.4.2.2 检查高频查询字段是否建立联合索引

联合素引遵守最左匹配原则，(a,b,c)， ab，ac, abc字段查询走索引，bc字段查询不走素引
比如：客户表：customer_info 宇段：id, user_id,mobile,real_name,level,last_login_date

全模糊搜索 改为前缀模糊搜索

深度分页

业界通用的解决方案：游标法(每次查询找到表中主键id最大的值，作为参数向下传递）

这里提供一个案例，后面会提供相应的实现代码

```sql
SELECT
C.*
FROM
t staff c
WHERE
c.delete_flag = 0 and id > #(maxId)
order by id limit #(pageSize)
```
另外格外注意：对于大表，排序字段务必加上案引。
1
2
3
4
5
6
7
8
9
10
11
12
13
14

大数据量分批执行

三、长期解决方案

3.1 数据库扩容

3.2 长期治理慢SQL

• 每天9点30定时发送整理后的慢SQL邮件(发送人：各组leader、核心骨干）
• 各组leader根据慢SQL影响面、业务优先级，制定个长期的慢SQL治理计划，分迭代逐步偿还这些技术债务，一般的慢SQL建议不要超过1个选代，遇到紧急情況走hotfix版本修复。
• 每个月底组织各组leader召开一次复盘会，梳理当前进展及未来的计划，各组分享慢SQL优化经验，互相借鉴，沉淀技术文档，避免以后踩同样的坑，

3.3 单库迁移到分片库

亿级大表单库读写能力弱，当业务量突增，系统风险很高，库容易被打挂，所以互联网公司用的比较多的方案是采用分片库代替单库应对几倍基至几十倍的突发流量，使用分片库以后，我们需要做哪些工作呢？

• 数据迁移
  基于binlog，通过canal+kafka+数据处理应用，完成亿级大表单库到分片库的平滑迁移。
• 分库后的数据查询、插入、更新、删除
  • 分库中问件架构选型：市面上用的比较多的分库分表中问件：mycat和shardingsphere，从成本、性能、稳定性、可维护性这几个方面综合考感，我们优先选择shardingsphere。
  • 应用接入：目前Java语言开发的应用大部分都是基于springboot开发，集成shardingsphere很方
    便，网上有很多集成示例，这里不过多介绍。

3.4 夯实底盘监控与异常实时告警

• 云数据库所在的主机监控告警
  网络、磁盘
• 云数据库自身各项指标监控告警
  cpu、内存使用率、磁盘、连接数、平均RT
• 慢SQL监控
• 分片库热点数据傾斜监控告警
  比如：大客户下单，某个大客户1次推送2w+订单，以客户userID为分片键，2W+QPS，瞬间特单库写爆。

所以一定要做好热点数据写入或更新的底盘监控，第一时间发现。

四、扩展高频面试题与分析

• 问题1：假设白天业务高峰期出现mysal cpu告營，但是没有慢SQL，那我们的解决方案是？
  答案：通过监控看到数撼库的连接数较平时大量增加，但是没有明显的慢SQL，这个时候大概率是我们的业务流量突增，遇到这种情况，我们第一时间要找运维对业务的数据库实例进行在线平滑扩容（目前大公司的数据库或中间件基础都是部署在云上，基于k8s进行扩容几乎秒级生效），例如：数据库的硬件配置从2c 4G扩容到4c 8G.
• 问题2：假设上了分片库以后，有大客户单次写入TPS几w+订单，那我们如何保证系统不被打垮呢？
  答案：1）上层入口做好限流 2）数据层增加拦截器，单次写入或更新数据量超过1000条，改为分批次提交事务
  3） 底层提供热点数据写人或更新的监控告警。

MySQL亿级大表单库如何平滑迁移到分库分表？

1、如何保证不影响业务，平滑过渡(包括异常回滚）

• 1. 线上正常业务采用库双写方案，完成切换后，下线旧库，平滑过渡。
• 2. 旧库下线后，离线报表调整到读新库，保障业务不受影响。

4、实时数据如何同步

目前有3种方案可选擇：

• 1. 监听mysql binlog日志 （推荐）
• 2. 通过mybatis的sql拦截器，获取变更语句，发送kafka
     变更消息
• 3. 新1旧库双写，方泰1无侵入性，比较可靠，优先考虑，如果没有开放binlog，预先考虑方案3。

方案1-监听mysql Binlog日志（推荐）
通过Flink CDC或Canal监听binlog 数据，写入kafka，应用侧消费完成后，更新新库。

5、数据一致性如何保证

通过对账程序自动对账，同时配置人工告營，防止对账失败，人工介入。具体步骤如下：

• 1）考虑到数据量很大，我们通过大数据平台hive，创建对账任务，每天下半夜的凌晨1点对账新库、旧库不一致，写入异常数据到差异表。同时配置人工告警，通知相关的研发人员。
• 2）通过ETL同步差异表数据到mysql库差异表（一般差异表数据量比较小）
• 3） 应用侧通过定时任务定时读取mysql库差异表，更新新库。

落地与实现

Mysql优化

回表概念

也就是说，基于非主键索引的查询需要多扫描一棵索引树

当所要查找的字段不在非主键索引树上时，需要通过叶子节点的主键值去主键索引上获取对应的行数据，这个过程称为回表操作。

（使用聚集索引（主键或第一个唯一索引）就不会回表，普通索引就会回表。）

索引覆盖

单列索升级为联合索引(name, sex)后，索引叶子节点存储了主键id，name，sex，都能够命中索引覆盖，无需回表。

索引下推 + id mrr

索引下推：
MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表字数。

mrri: 回表顺序读

MySQL 千万级的大表如何优化？

方案一详细说明：优化现有mysql数据库

1.数据库设计和表创建时就要考虑性能
2.sql的编写需要注意优化
3.分区
4.分表
5.分库

设计表时要注意：

• 表字段避免null值出现，null值很难查询优化且占用额外的索引空间，推荐默认数字0代替null。
• 尽量使用INT而非BIGINT，如果非负则加上UNSIGNED（这样数值容量会扩大一倍），当然能使用TINYINT、SMALLINT、MEDIUM_INT更好。
• 使用枚举或整数代替字符串类型
• 尽量使用TIMESTAMP而非DATETIME
• 单表不要有太多字段，建议在20以内
• 用整型来存IP

索引

• 索引并不是越多越好，要根据查询有针对性的创建，考虑在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描
• 应尽量避免在WHERE子句中对字段进行NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描
• 值分布很稀少的字段不适合建索引，例如"性别"这种只有两三个值的字段
• 字符字段只建前缀索引
• 字符字段最好不要做主键
• 不用外键，由程序保证约束
• 尽量不用UNIQUE，由程序保证约束
• 使用多列索引时主意顺序和查询条件保持一致，同时删除不必要的单列索引

MySQL中使用 TIMESTAMP 而不是 DATETIME 的原因：TIMESTAMP空间消耗少，操作方便（不像DAETIME要操作整数值）。