【MySQL】各个版本优化_mysql处理亿级数据-CSDN博客

本文链接：https://blog.csdn.net/weixin_43746433/article/details/104862799

文章目录

Mysql优化
- 回表概念
- - 索引覆盖
  - 索引下推 + id mrr
MySQL 千万级的大表如何优化？
- 方案一详细说明：优化现有mysql数据库
- - 设计表时要注意：
  - 索引

MySQL 5.5 特性，相对于 5.1

性能提升

InnoDB代替MyISAM成为MySQL默认的存储引擎, 具有提交、回滚和crash恢复功能、ACID兼容。行级锁(一致性的非锁定读 MVCC)。
多核扩展，能更充分地使用多核CPU: 充分利用CPU多核处理能力innodb_read_io_threads阈值：1-64innodb_write_io_threads 阈值：1-64根据数据库的读写比灵活设置，充分发挥多CPU、高性能存储设备的性能，不支持动态加载。
引入innodb_io_capacity选项，用于控制脏页刷新的数量。可以动态调整刷新脏页的数量，改善大批量更新时刷新脏页跟不上导致的性能下降问题。Default：200，跟硬盘的IOPS有关。
热数据(5/8)存活更久。
buffer pool多实例：innodb_buffer_pool_instances 参数增加。innodb_buffer_pool实例个数，大大降低buffer pool的mutex争抢过热情况。
Linux上实现异步IO。
重新支持组提交。
引入utf8mb4字符集，可用来存储emoji表情。
引入metadata locks（元数据锁）。

稳定性提升

支持半同步Replication。
增加Relay Log自我修复功能。
Crash recovery。
引入红-黑树做插入排序的中间数据结构，时间复杂度大大降低，减少恢复时间。
Thread Pool 分组排队限流。

MySQL 5.6 特性，相比于 5.5

默认参数的改变。
Back_log 排队队列。
支持全文索引。
支持online DDL create,alter,drop。
新增参数innodb_page_size可以设置page大小。
可以在建表时指定表空间位置。

create table external (x int unsigned not null primary key)data directory = '/volumes/external1/data';

整合了memcached API，可以使用API来直接访问innodb表，并非SQL（减少SQL解析、查询优化代价）。
innodb只读事务，不需要设置TRX_ID字段。
减少内部数据结构开销，减少read view。
仅仅非只读事务依然需要TRX_ID。
统计信息持久化，mysqld重启后不丢失
explain语句支持insert，update，delete，replace等DML操作的执行计划，并且支持JSON格式,在此之前，只支持SELECT操作。
子查询优化提升。

innodb 改进点

innodb表空间在线迁移(TransportableTablespaces)。
undo log可独立出系统表空间。
redo log最大可增长到512G。
innodb后台线程独立出来。

优化器改进

可以在引擎层直接过滤数据，避免二次回表。
节省BP空间，提高查询性能。

BKA

全称Batch Key Access，SQL通过辅助索引要访问表数据时候，将大量的随机访问放入缓存，交给MRR接口合并为顺序访问。

BKA（Batched Key Access）是在MySQL数据库的查询优化器层实现的。具体来说，它是在以下几个步骤中进行操作的：

查询解析和优化：当一个查询到达MySQL服务器时，首先经过解析器解析SQL语句，然后由优化器生成一个或多个执行计划。优化器决定使用BKA算法时，会将该算法应用于生成的执行计划中。

执行计划生成：优化器根据可用的索引、表的大小、JOIN类型等因素，决定使用哪种JOIN算法。如果决定使用BKA，它会在执行计划中体现。

执行引擎：一旦优化器生成了使用BKA的执行计划，执行引擎会按照这个计划执行查询。BKA算法在这个过程中，通过使用join buffer来批量获取外部表的行，并将这些行的键值批量发送到存储引擎层进行查找。

存储引擎层：BKA算法利用存储引擎提供的Multi-Range Read（MRR）接口，将收集到的键值进行排序，并按照排序后的结果顺序地从存储引擎中读取数据。这一步是在存储引擎层完成的，但它是由执行引擎层的BKA算法触发的。

数据访问：在存储引擎层，数据的物理读取操作（如随机I/O或顺序I/O）是根据MRR的结果进行的。BKA通过MRR将随机I/O操作转换为顺序I/O操作，从而提高性能。

总结来说，BKA算法是在MySQL的执行引擎层进行的，但它依赖于查询优化器的决策和存储引擎层的MRR接口来实现其优化效果。

MySQL中的Batched Key Access（BKA）是一种用于优化表连接查询的算法，它在MySQL 5.6及以后的版本中得到支持。BKA算法主要针对那些在连接表上有索引的情况，尤其是当这些索引是非主键索引时。BKA通过减少随机I/O操作，提高查询性能，它结合了Simple Nested-Loops Join（SNLJ）算法和Block Nested-Loops Join（BNL）算法的特点，并利用了Multi-Range Read（MRR）的特性。

BKA算法的工作流程大致如下：

将外部表（驱动表）中相关的列放入Join Buffer中。
构建好key后，批量传给引擎层做索引查找。
利用MRR接口，根据收到的Key和对应的ROWID进行排序，再进行数据的读取操作，将随机I/O转为顺序I/O。
返回结果集给客户端。

要启用BKA算法，需要设置optimizer_switch系统变量，将’mrr’和’mrr_cost_based’设置为’on’和’off’，并将’batched_key_access’标志设置为’on’。这是因为BKA依赖于MRR来优化I/O操作，而MRR的成本估算可能过于保守，因此需要关闭成本估算。

MRR是BKA算法的一个重要支柱，它的目的是减少磁盘的随机访问，通过将辅助索引扫描到的数据存入内存，并按照主键（RowID）排序后进行顺序回表，从而提升性能。MRR优化特别适用于范围查询，可以显著减少IO-bound类型的SQL查询语句的性能开销。

BKA算法的适用场景包括内联接、外联接和半联接操作，特别是当内部表上有非主键索引，并且连接操作需要访问内部表主键上的索引时。BKA算法通过MRR接口，批量进行索引键的匹配和主键索引上获取数据的操作，提高连接的执行效率。

在实际使用中，如果需要确保优化器使用BKA算法，可以在SQL语句中使用hints，如/*+ BKA(table) */，来显式指定使用BKA算法。此外，EXPLAIN输出中的Extra值如果包含"Using join buffer (Batched Key Access)"，则表示使用了BKA算法。

MRR

全称Multi Range Read。
在BKA算法应用之后，通过MRR接口合并随机访问为顺序访问，再去检索表数据。
变大量随机为顺序访问。在通过辅助索引检索大量数据时，性能提升明显。
磁头无需来回寻道，page只需读取一次，且较好利用了innodb线性预读功能（每次预读64个连续page）。

MySQL 5.7 特性，相比于 5.5 5.6

可用性

在线修改Buffer pool的大小。
在线设置复制的过滤规则不再需要重启MySQL，只需要停止SQLthread，修改完成以后，启动SQLthread。
Online DDL MySQL 5.7支持重命名索引和修改varchar的大小，这两项操作在之前的版本中，都需要重建索引或表。

性能

可设置SELECT操作的超时时长（max_execution_time）

默认参数的改变

默认binlog格式调整为ROW格式。
默认binlog错误后的操作调整为ABORT_SERVER。

在先前的选项下（binlog_error_action=IGNORE_ERROR），如果一个错误发生，导致无法写入binlog，mysql-server会在错误日志中记录错误并强制关闭binlog功能。这会使mysql-server在不记录binlog的模式下继续运行，导致从库无法继续获取到主库的binlog。
默认开启mysql崩溃时的binlog安全。
默认调低slave_net_timeout。

MySQL 8.0 特性，相比于 5.7

新的系统字典表

整合了存储有关数据库对象信息的事务数据字典，所有的元数据都用InnoDB引擎进行存储
安全和用户管理

新增caching_sha2_password认证插件，并且是默认的身份认证插件。性能和安全方面加强
权限支持role
新增密码历史记录功能，限制重复使用以前的密码

innodb 增强

新增INFORMATION_SCHEMA.INNODB_CACHED_INDEXES，查看每个索引缓存在InnoDB缓冲池中的索引页数

InnoDB临时表都将在共享临时表空间ibtmp1中创建

对于SELECT … FOR SHARE和SELECT … FOR UPDATE语句，InnoDB支持NOWAIT和SKIP LOCKED

innodb_undo_tablespaces的最小值为2，并且不再允许将innodb_undo_tablespaces设置为0。最小值2确保回滚段始终在撤消表空间中创建，而不是在系统表空间中创建

支持 ALTER TABLESPACE … RENAME TO 语法

新增INFORMATION_SCHEMA.INNODB_TABLESPACES_BRIEF视图

支持使用innodb_directories选项在服务器脱机时将表空间文件移动或恢复到新位置

新增innodb_dedicated_server，让InnoDB根据服务器上检测到的内存量自动配置innodb_buffer_pool_size，innodb_log_file_size，innodb_flush_method。当innodb_dedicated_server启用时，InnoDB根据服务器上检测到的内存量自动配置以下选项：

innodb_dedicated_server：自动配置缓冲池大小

alt text

innodb_log_file_size：自动配置的日志文件大小

alt text

innodb_flush_method：O_DIRECT_NO_FSYNC

MySQL 8.0更好支持文档型数据库和JSON

不可见索引，开始支持invisible index，在优化SQL的过程中可以设置索引为不可见，优化器不会利用不可见索引
支持降序索引，可以对索引定义 DESC，之前，索引可以被反序扫描，但影响性能，而降序索引就可以高效的完成
支持RANK(), LAG()、NTILE()等函数
正则表达式增强，提供了REGEXP_LIKE()，EGEXP_INSTR(), REGEXP_REPLACE(), REGEXP_SUBSTR()等函数
新增备份锁，允许在线备份期间的DML，同时防止可能导致快照不一致的操作。备份锁由LOCK INSTANCE FOR BACKUP和UNLOCK INSTANCE语法支持

默认字符集由latin1变为utf8mb4

配置文件增强

MySQL 8.0版本支持在线修改全局参数持久化，通过加上PERSIST关键字，可以将调整持久化到新的配置文件中，再次重启db还可以应用到最新的参数。对于加上 PERSIST 关键字修改参数命令，MySQL系统会生成一个包含json格式数据的 mysqld-auto.cnf 文件，比如执行：
set PERSIST binlog_expire_logs_seconds = 604800 ; #内存和json文件都修改，重启还生效
set GLOBAL binlog_expire_logs_seconds = 604800 ; #只修改内存，重启丢失
系统会在数据目录下生成一个包含如下内容的 mysqld-auto.cnf 的文件：
{ “mysql_server”: {" binlog_expire_logs_seconds ": “604800” } }
当 my.cnf 和 mysqld-auto.cnf 同时存在时，后者具有高优先级。
直方图

MySQL 8.0 版本开始支持期待已久直方图。优化器会利用column_statistics的数据，判断字段的值的分布，得到更准确的执行计划。
可以使用 ANALYZE TABLE table_name [UPDATE HISTOGRAM on col_name with N BUCKETS |DROP HISTOGRAM ON clo_name] 来收集或者删除直方图信息。
支持会话级别SET_VAR 动态调整部分参数，有利于提升语句性能

select /*+ SET_VAR(sort_buffer_size = 16M) / id from test order id ;
insert /+ SET_VAR(foreign_key_checks=OFF) */ into test(name) values(1);

InnoDB性能提升

废除buffer pool mutex, 将原来一个mutex拆分成多个，提高并发拆分LOCK_thd_list 和 LOCK_thd_remove 这两个mutex，大约可提高线程链接效率5%。

Redo Log的优化，包括允许多个用户线程并发写入log buffer，可动态修改innodb_log_buffer_size的大小。

行缓存

MySQL8.0的优化器可以估算将要读取的行数，因此可以提供给存储引擎一个合适大小的row buffer来存储需要的数据。大批量的连续数据扫描的性能将受益于更大的record buffer。
改进扫描性能

改进InnoDB范围查询的性能，可提升全表查询和范围查询 5-20%的性能。

成本模型

InnoDB缓冲区可以估算缓存区中的有多少表和索引，这可以让优化器选择访问方式时知道数据是否可以存储在内存中还是必须存储到磁盘上。

支持直接修改列名（ALTER TABLE … RENAME COLUMN old_name TO new_name）。

MySQL死锁检测过程和原理主要包括以下几个方面：

锁类型和加锁分析：MySQL有三种锁的级别：页级、表级、行级。其中表级锁开销小，加锁快，不会出现死锁；而行级锁虽然开销大，加锁慢，但会出现死锁。InnoDB存储引擎中的锁包括next Key Locks、Gap锁和Record Lock，其中Next-Key Locks是Gap锁和Record Lock的组合。

死锁产生原因：死锁通常发生在两个或多个事务试图以不同的顺序锁定资源时。例如，事务A锁定了资源X，然后请求资源Y，而事务B先锁定了资源Y，然后请求资源X，这样就会导致死锁。

死锁检测机制：MySQL使用等待图（Wait-for Graph）算法进行死锁检测。MySQL维护一个等待图，其中节点表示事务，边表示事务之间的依赖关系。通过检测等待图中是否存在环来判断是否发生了死锁。如果存在环，则说明发生了死锁，并选择一个事务作为牺牲者进行回滚，以解决死锁。

死锁预防策略：预防死锁可以通过调整事务顺序、使用乐观锁、减少事务大小、避免长时间的事务、设置合适的隔离级别等方法来实现。

死锁检测参数：InnoDB存储引擎提供了innodb_deadlock_detect参数来控制是否检测死锁。默认情况下，该参数为ON，表示启用死锁检测。从MySQL 5.7.15开始，还可以设置该参数为OFF以在高并发场景中提高性能，此时如果发生死锁，需要依赖innodb_lock_wait_timeout参数来处理。

死锁案例分析：通过具体的死锁案例，可以分析出事务之间的锁等待关系，并根据死锁日志中的信息，如事务ID、等待的锁类型、持有的锁等，来确定死锁的原因和解决方案。

死锁解决方案：解决死锁可以通过重试失败的事务、优化事务设计、设置锁超时时间、调整隔离级别、使用死锁预防策略以及建立监控和警报机制等方法。

通过上述信息，我们可以看到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)
);

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

在没有使用索引下推的情况下，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';

可以通过上述这条命令开启或关闭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;

在讲联合索引时，咱们提到过最左前缀匹配原则，也就是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.

> 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.

这里面的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;

也就是说，虽然我们的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:

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:

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)
```
另外格外注意：对于大表，排序字段务必加上案引。

大数据量分批执行

三、长期解决方案

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

4、实时数据如何同步

目前有3种方案可选擇：

1. 监听mysql binlog日志（推荐）
1. 通过mybatis的sql拦截器，获取变更语句，发送kafka
  变更消息
1. 新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