Skip to content

MySQL 核心原理

· 73 min

MySQL的执行流程#

如上图所示，MySQL的架构分为两大部分：

那么执行一条select 语句，期间到底发生了什么？

1. 连接#

在执行sql之前，我们首先要启动sql服务并连接上mysql，这是最基础的步骤。连接的时候经过TCP三次握手。

我们可以通过show processlist命令来查看有多少的客户端连接了MySQL

一般我们都使用长连接，短连接需要频繁连接mysql，但是长连接累计过多也会导致服务占用的内存过大，浪费资源，很有可能导致mysql重启。所以为了解决长连接占用内存的问题，我们可以：

2. 查询缓存#

连接之后，客户端向sql服务端发送sql语句，mysql收到sql语句后会解析sql的第一个字段，然后就会判断是不是select，如果是就会查找缓存中有没有数据。

查询缓存是个比较鸡肋的功能，MySQL8.0 之后就没有了查询缓存

3. 解析sql#

sql的解析是通过解析器来完成的

解析器会做两件事情：

4. 执行sql#

mysql的执行分为三个阶段：

MySQL 一行记录是怎么存储的？#

MySQL的数据存放在哪个文件？

  • MySQL的表结构存放在 table.frm 文件

  • MySQL的表数据存放在 table.ibd 文件

mysql表文件结构#

Innodb的行格式 👈 重点#

行格式是一条数据的存储的结构

InnoDB 存储引擎设计了 4 种不同类型的行格式 Compact、Redundant、Dynamic 和 Compressed 行格式。innodb默认的行格式为Dynamic

Compact行格式#

表中的三个记录值

记录的额外信息#

  • 小于128字节‌：长度字段的最高位为0，低7位直接存储实际长度值。

  • 大于等于128字节‌：长度字段最高位为1，低7位存储后续用于表示长度的字节数量，后续字节的最高位均为1，低7位存储具体长度值。 1

例如：

记录的真实数据#

记录的真实数据前面有三个隐藏字段：

varchar(n) 中 n 最大取值为多少？（这里只考虑ascii情况下）#

mysql的一条记录除了TEXT、BLOBs类型的列，限制最大为65535字节，（包含NULL值列表和变长字段长度列表）

字符集是 ascii 的情况下1 个字符占用 1 字节

行溢出后，MySQL 是怎么处理的？#

上面我们计算过，除了TEXT、BLOB类型的数据可以存放大对象，varchar(n) 类型的数据最多存放65532大小的数据，所以如果遇到大对象，很容易发生溢出现象，compact行结构的处理方式是，记录的真实数据处只会保存该列的一部分数据，然后把剩余的数据放在溢出页中，在真实数据处留出20字节的空间，指向溢出页的位置。

Compressed 和 Dynamic 这两个行格式和 Compact 非常类似，他们采用完全的行溢出方式把所有的数据都放进溢出页，真实数据记录处只存储20个字节的数据指向溢出页

索引#

索引参考资料

什么是索引?#

索引是提升查询速度的一种数据结构

索引分类#

Innodb下的B+树索引：主键索引是聚簇索引，非主键的是二级索引，二级索引叶子节点存放的是主键值，我们在使用索引查找的时候，如果查询的二级索引，正好包含所需要的列，那么就是覆盖索引

索引组织表和堆表#

MYISM的存储是堆表。Inoodb是索引组织表

数据存储有堆表和索引组织表两种方式。

联合索引#

联合索引遵循最左匹配原则：

索引下推#

如何提高联合索引的效率#

索引失效#

模型数空运最快or

如何优化索引#

优化器是如何选择合适的索引#

MySQL的优化器默认是CBO优化器，一条 SQL 的计算成本计算如下所示：

Cost = Server Cost + Engine Cost

= CPU Cost + IO Cost

MySQL有两个表，一个是server_cost，一个是engine_cost，里面记录了对于各种成本的计算

为什么会出现不使用索引的情况呢？

创建索引的标准#

可能适用于索引的数据结构#

二分查找#

由于二分查找需要索引按照顺序线性排列，那么就会导致插入的时候需要大量移动索引，在磁盘上对数据进行操作，那么效率会大大的降低

二分查找树#

非常容易链化，导致树的高度非常高，严重影响效率

自平衡树（AVL）#

不管是红黑树还是自平衡树，他们随着插入元素的增加， 树的高度也就增加，这就导致磁盘i/o次数增加，影响效率

B树#
B+树#

回表#

回表是指根据索引查询到的主键值再去访问主键索引，从而获取完整的数据记录。

回表操作通常会导致访问两次索引，增加IO次数，影响效率

导致回表的情况#

  1. 索引不包含查询的字段

  2. 使用了非聚簇索引

  3. 使用的覆盖索引，但是超过了索引的最大长度（767字节）

如何避免出现回表#

  1. 使用聚簇索引

  2. 使用覆盖索引的时候，注意索引的长度，不要超过767字节

索引面试技巧#

问题1：什么是索引？#

索引的本质就是数据结构。从数据结构上来说，mysql 的索引主要是B+树索引，它的查询性能好，还能进行范围查询。我们也可以通过不同的角度对索引进行划分：比方说根据叶子节点是否包含数据划分为 聚簇索引和非聚簇索引；根据覆盖的列分为单列索引和联合索引

问题2：为什么使用B+树作为mysql的数据结构？#

事务#

事务的四大特性（ACID）#

原子性是undo log回滚日志来保证的

一致性是持久性+原子性+隔离性来保证

隔离性是MVCC或者锁机制来保证的

持久性是redo log 日志保证的

并发事务带来的问题#

不可重复读和幻读的区别在于：不可重复读是数据值的变化，而幻读是新增或者删除了数据

MySQL的事务隔离级别#

MySQL默认隔离级别是可重复读

快照读和当前读的区别#

MVCC（多并发版本控制）#

实现原理：Read View、隐藏字段、undo log

隐藏字段#

我们在介绍MySQL的 compact行格式 的真实数据中提到过，有两个隐藏的字段

undo log 数据版本链#

每行数据的多个版本通过 roll_pointer 串联成链表，最新版本在链头，旧版本依次向后。

Read View#

Read View 有四个字段: creator_id 、m_ids 、min_trx_id、max_trx_id

Read View最主要的作用就是判断当前事务执行快照读的时候，哪个版本对他可见。

数据对当前事务的可见性判断：

可见性判断的核心是 “事务提交状态” 和 “事务启动顺序” 的结合，其中 “是否提交” 是基础（未提交的版本一定不可见），而 “启动顺序” 则决定了已提交版本在不同隔离级别下的可见范围。

可重复读和读已提交的区别#

总结：两者的区别在于创建Read View 的时机不同

  • 可重复读：只在第一次快照读的时候创建

  • 读已提交：每次进行快照读的时候都会创建

可重复读为什么没有完全解决幻读？#
快照读如何解决幻读#

因为每次进行快照读的时候，我们都会使用第一次快照读创建的Read View 所以不会出现前后读取数据不一致的情况

当前读如何解决幻读#

MySQL除了普通的快照读，其他都是当前读，例如：update、insert、delete等语句执行前都会查询最新版本的数据，再进行下一步操作。

Innodb为了解决当前读情况下每次都会查询新版本的数据而造成的幻读问题，使用了间隙锁

当数据被事务A 插入next-key lock 后，事务B进行插入操作的时候，会加一个意向锁，从而进入等待状态，直到事务A提交之后， 事务B 才会执行

可重复读出现幻读的情况#

除了上面这一种场景会发生幻读现象之外，还有下面这个场景也会发生幻读现象。

要想避免发生幻读，就要在事务开始之后立马加上next_key lock（间隙锁和记录锁）避免其他事务插入新的数据

锁相关#

锁的原理和存储#

MySQL 中的锁（尤其是 InnoDB 引擎的锁）并非直接 “加到数据本身”，而是基于索引加锁。具体来说，锁的作用对象是索引记录（或索引间隙），通过索引关联到对应的数据行，从而间接控制对数据的访问

锁的分类#

全局锁#

MySQL全局锁加上后，数据库处于只读状态

表级锁#
表锁#

表锁可以限制当前线程和其他线程对某个表进行读写操作，表锁的粒度太大，影响并发性能

元数据锁（MDL锁）#

元数据锁是自动加上的，当一个线程对数据库表进行操作的时候，那么MySQL会自动加上元数据锁

元数据锁在事务提交之后自动释放

如果数据库中有一个长事务 A 正在执行，此时线程 B 对表结构做变更操作的时候由于一直获取不到写锁，会导致后续的select 请求被阻塞，原因是：申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现 MDL 写锁等待，会阻塞后续该表的所有 CRUD 操作。

意向锁#

作用：快速判断表内是否有记录被加锁

AUTO-INC 锁#

在插入数据的时候，会加一个表级别的AUTO-INC 锁，然后为 自增的字段赋予递增值，等插入语句执行完毕后这个锁就会被释放。

在加了AUTO-INC 锁之后，其他线程插入数据的时候都会被阻塞，从而保证自增字段的连续递增

由于插入的数据过多的时候，会导致阻塞，影响性能，在MySQL5.1.22版本开始，Innodb就提供了一个轻量级的锁，每次插入的时候，会为自增字段加上AUTO-INC锁，为该字段赋值一个自增数据后该锁就会被释放，不会到插入执行完毕后再释放。

有一个字段可以控制AUTO-INC 锁的粒度 innodb_autoinc_lock_mode

行级锁#

InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁。

Record lock#

记录锁，锁住的是一条记录，记录锁满足多个事务同时加锁，但是只能是共享锁之间叠加，独占锁不行。

共享锁独占锁
共享锁兼容不兼容
独占锁不兼容不兼容
Gap lock#

间隙锁，只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。间隙锁是兼容的，两个事务可以同时拥有相同范围的间隙锁。锁住一个范围，范围是双开区间。

Next-Key lock#

是间隙锁和记录锁的结合，可以锁住该记录和范围内的记录，有一个闭区间，由于他有记录锁，所以存在互斥关系。

插入意向锁#

一个事务在插入数据的时候需要先判断插入位置是否被加了间隙锁（next-key lock也有间隙锁），如果有就会被阻塞，于是就加一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待状态。

⭐MySQL是如何加行级锁的#

参考资料：MySQL 是怎么加锁的？我是看了这篇文章后，按自己的理解复盘总结了一下。

我们知道MySQL的行锁总共有三个：next-key、gap、record。在可重复读隔离级别下，MySQL通过加next-key锁来避免幻读，接下来我会分析MySQL是如何加锁的，以及如何通过加锁来解决幻读。

  1. 下面是我们演示的数据表：

主键索引#
等值查询存在数据#

执行完毕后，得到的结果是：表级别的意向锁、行级别退化成了记录锁，锁的是主键为 5 的记录

等值查询不存在数据#

执行完毕后，得到的结果是：表级别的意向锁、行级别的间隙锁，锁的范围是（5，10） mysql是基于索引加锁的，id = 7 这个数据不存在，只能通过锁定已有的索引（5，10）这个范围。以此来限制别的事务插入 id = 7 的情况

范围查询 #

结果是：表级别的意向锁、行级别的next-key锁，锁的范围是（5,10] (10,15] (15,20] (20,+∞] mysql是基于索引加锁的，id = 6 这个数据不存在，只能通过锁定已有的索引（5，+∞] 这个范围。以此来限制别的事务插入 id > 6 的范围

总结一下，mysql是基于索引加锁的，我们要想加锁的数据不被修改，必须找到条件值附近的索引来加锁，例如 id > 6这个条件。我们的数据中没有id = 6的数据，于是要想锁住 id > 6 这个范围，必须找到id = 6前面的那条数据，也就是id = 5。然后加锁(5,+∞]；同理，如果是锁 id < 6 的这个范围，就要找到id = 6 后面的那条数据 id = 10,然后加锁 [1,10)

二级索引#
存在值的等值查询#

我们在age字段上也加了普通的索引，我们试验一下，如果进行条件查询的时候会怎么加锁

InnoDB 的二级索引 age 实际存储的是 (age, id) 对，并按字典序排序。按照输出来说，我们锁住的是（15，20）这个组合的上下区间。也就是（(1,19),(15,20)] + ((15,20),(5,21))

不存在值的等值查询#

可以看到，我们锁的是（39，20）的间隙锁，也就是范围 ((10,22),(20,39))

范围查询#

范围查询锁的都是下一个数据的临键锁，也就是我们只需要找到这个数据的下一个数据（包含），如果是大于就加下一个数据的临键锁和正无穷那个，如果是小于，就加上下一个数据的临键锁和小于这个值的所有值的临键锁

死锁#

死锁发生的原因#

两个或者多个事务执行过程中，因争夺资源造成互相等待，导致死锁。MySQL如果监测到死锁，会选择其中一个事务回滚，打破这个僵局

死锁产生的常见的场景#
场景一：行锁顺序不一致（最常见）#

事务A：

事务B：

✅ 解决方法：所有事务按相同的顺序访问资源（如都先更新 id 小的记录）。

场景二：间隙锁或者next-key-lock#

可重复读隔离级别下，间隙锁如果出现重叠就会发生死锁，例如：间隙锁 (1,10) 和间隙锁 (5,15) 两者在重叠区域形成循环等待

场景三：没有命中索引#

如果没有索引，那么mysql会对每一条记录加上行锁，导致死锁概率增大

如何排查死锁#

使用命令来查询innodb引擎状态

这个命令会输出一个状态，里面有记录死锁相关的信息

死锁怎么解决#

死锁不能完全解决，我们只能通过一些手段降低死锁发生的概率

  1. 减少事务执行的时间（缩短锁持有时间）

    1. MySQL的锁释放时机是在事务提交之后，所以我们可以通过减少事务执行时间（缩短锁持有时间）

    2. 避免大事务，将大的事务拆分为小的事务

  2. 减少间隙锁：

    1. 如果可重复读和幻读对业务影响不大，把隔离级别调整为读已提交

  3. 给表加上合适的索引

    1. 因为不走索引MySQL会对每一行记录都加锁

  4. MySQL参数调整

    1. MySQL锁等待时间阈值参数，如果等待超过多久就会回滚

    2. 主动死锁检测，MySQL如果发现有死锁，就会回滚某一个事务

日志#

MySQL有三种日志：undo log（回滚日志）、redo log（重做日志）、binlog（归档日志）

undo log（回滚日志）#

undolog是回滚日志，保证了事务中的原子性

Buffer Pool#

为了提高读写性能， Innodb 引擎设计了一个缓存池，当读取数据的时候可以先去缓存池中查找，当修改数据的时候同样可以在缓存池中修改（被修改过的页是脏页），为了减少磁盘的I/O,不会立刻写入，而是后续由后台线程找个合适的时机写入

Buffer Pool 除了缓存「索引页」和「数据页」，还包括了 Undo 页，插入缓存、自适应哈希索引、锁信息等等

redo log（持久化）#

Redo log 记录的是“在哪个数据页的什么位置做了什么修改”，是一种物理逻辑日志（Physical-Logical Log），用于在系统崩溃后重新应用（重放）这些修改，确保已提交事务的数据不会丢失。

记录更新到写入redolog经历了什么#

一条记录的更新，会先在内存中修改数据页，同时生成 redo log 写入 redo log buffer；当事务提交时，redo log 会根据配置（innodb_flush_log_at_trx_commit）刷入磁盘的 redo log 文件，而数据页的持久化则由后台线程异步完成。

这个过程遵循 WAL（先写日志） 原则：必须先写日志，才能提交事务。

写入到 redo log 中依然需要刷新到磁盘中，这不是多此一举吗？

  • 如果不写入redo log ，得先找到数据页再修改，较为随机（数据页位置）效率很低

  • 写入到 Redo log 的数据是按顺序写入的，写入完成后事务就可以提交了。剩下的是交给操作系统处理

  • Undo log 脏页需要先找到数据的位置，再进行写入

MySQL的写磁盘操作由随机写变成了顺序写，提高了执行能力

redo log buffer 的刷盘策略#

缓存在 redo log buffer 里的 redo log 还是在内存中，它什么时候刷新到磁盘？

innodb_flush_log_at_trx_commit 参数

安全性：参数1 > 参数2 > 参数 0

性能： 参数0 > 参数2 > 参数 1

Redo log 文件满了咋办?#

Redo log 有个重做日志文件组，由两个redo log 组成

Redo log 循环写：先写第一个，再往第二个里面写。

脏页内容刷新到了磁盘后，redo log 中的数据也就无效了，可以擦除，用一个指针指向需要擦除的位置->check-point

如果 write pos 追上了checkpoint 说明空间不足了，此时MySQL会被阻塞，将蓝色部分刷新到磁盘中（脏页变成干净页），然后标记可擦除的地方，腾出空间，然后MySQL恢复，继续执行更新操作

Redo log 和undo log 的区别在哪儿？#
binlog#

Binlog 记录了MySQL数据库表结构变更和表数据的修改操作，不会记录查询。

思考：为什么有了binlog 我们还引入 redo log ？

Binlog 和 redolog 的区别#

  1. 适用的对象不同

    • binlog 是在server层，所有的存储引擎都可以调用

    • redolog 是Innodb引擎实现的日志

  2. 写入的方式不同

    • binlog 是追加写的方式，写完就新建一个写

    • redolog 是循环写的方式

  3. 文件格式不同

    • binlog 有三个格式类型：statement、row、mixed

    • redolog 是物理日志，记录了在某个地方做了什么修改

  4. 用途不同

    • binlog 用于备份恢复、主从复制

    • redolog 用于系统崩溃、停电数据恢复

如果数据库数据都被删了，能用redolog 恢复吗？

主从复制的过程#

MySQL的主从复制是依赖binlog实现的，复制的过程就是将主库的binlog传输到从库中。这个一般是异步实现的

  1. MySQL主库收到客户端事务的提交请求后，先把数据写入binlog，然后提交事务，更新存储引擎中的数据，然后返回客户端操作成功的提示

  2. 从库会创建一个专门的 I/O 线程，连接主库的 log dump线程，来接收主库的binlog日志，接收的日志存放在从库的 relay log 日志中（相当于一个缓存），然后接收完毕，返回给主库复制成功的响应。

  3. 从库会创建一个来回放（执行binglog里面的sql）binlog 日志的线程，读relay log日志，回放binlog更新存储引擎数据，实现主从一致性

在完成主从复制之后，就可以在写数据时只写主库，在读数据时只读从库，这样即使写请求会锁表或者锁记录，也不会影响读请求的执行。

从库不是越多越好，因为从库多了之后，主库连接log dump线程的I/O线程就会变多，对主库消耗大。一般是一主两从一主备

主从复制的模型还有那些？

  1. 同步复制：事务提交需要等待从库全部复制完毕后才会提交

  2. 异步复制（默认）：后台I/O线程对接从库log dump线程进行主从复制

  3. 半同步复制：事务提交只要有一个从库复制完毕后就可以提交

binlog刷盘策略#

事务执行过程中，先把日志写到 binlog cache（Server 层的 cache），事务提交的时候，再把 binlog cache 写到 binlog 文件中。

事务的两阶段提交问题#

只要事务涉及数据修改（INSERT/UPDATE/DELETE）并且同时启用了 binlog 和 InnoDB，MySQL 在每次提交事务时都会执行“两阶段提交”（Two-Phase Commit, 2PC）。关键前提：必须开启了 binlog

为什么要两阶段提交#

MySQL中redolog保证持久性，binlog用于主从复制和数据恢复。如果一个事务只写了redolog没写binlog，会导致主库有数据，从库没有，或者是崩溃恢复后前后数据不一致

系统崩溃导致主从数据不一致的问题#

我们知道，mysql在事务提交之后需要将redo log日志和 binlog 日志持久化到磁盘中，但是如果中途出现宕机，那么就会导致主从数据不一致情况

假设执行了下面的语句：update t_user set name = xsong where id = 1

mysql在持久化的过程中，如果出现“半成功”的情况，都会导致主从不一致，所以mysql为了避免这种情况使用了两阶段提交

两阶段提交的过程#

两阶段提交把事务的提交拆分为了两阶段：Prepare阶段、Commit 阶段。MySQL在开启binlog 日志的情况下，为了保证binlog 和Innodb redo log 这两个日志的一致性，使用了MySQL内部XA事务，binlog作为协调者，存储引擎是参与者

过程如下：

解决主从不一致问题#

在 MySQL 重启后会按顺序扫描 redo log 文件，碰到处于 prepare 状态的 redo log，就拿着 redo log 中的 XID 去 binlog 查看是否存在此 XID：

两阶段提交是由 binlog 写成功为事务提交的标识， binlog 写成功，就意味着能在 binlog 中查找到与 redo log 相同的 XID。

两阶段提交的问题#

  1. 磁盘 I/O 次数过多：每次事务提交都会进行两次刷盘操作，一次是 redo log刷盘，一次是binlog刷盘

  2. 锁竞争激烈：为了保证两阶段提交redo log 和binlog 的刷盘顺序，mysql使用了锁来保证原子性，在高并发的情况下，锁竞争会很激烈

两阶段提交就像数据库事务的”双保险机制”。当你执行事务提交时，MySQL会先把数据变更记录写入redo log，这时候redo log处于”prepare”状态，就像给数据变更买了第一份保险，确保即使此时数据库崩溃，重启后也能通过redo log恢复这部分变更。

接着，MySQL会将整个事务的逻辑操作记录到binlog，这相当于给数据变更办了”公证”，确保主从复制时从库能完整复现这个事务。

最后，当binlog写入成功后，MySQL才会把redo log的状态改为”commit”，完成整个事务提交。这样设计的好处是，无论在哪个阶段发生故障，都能通过这两个日志的状态对比来决定是回滚还是继续提交，从而保证主库和从库的数据一致性。我在处理主从同步问题时，经常需要通过分析这两个日志的状态来定位数据不一致的原因。

调优#

Explain#

EXPLAIN 语句用来查看某个查询语句的执行计划

执行计划输出各行详解#

type 描述了找到数据所用的扫描方式#

system

当表中只有一条记录，并且该表使用的存储引擎是MyISAM，那么type类型是system但是换成Innodb就会变成 all，例子如下：

（t表是MyISAM引擎）

（t2表是Innodb引擎）

Const

当我们根据主键或者唯一的二级索引列进行等值匹配的时候，数据所用的扫描方式是const，例如：

eq_ref

在连接查询中，如果被驱动表是通过主键或者唯一二级索引列进行等值匹配的方式进行访问的，那么被驱动表的访问方法就是eq_ref

ref

通过普通二级索引列与常量进行等值比较的时候所用的扫描类型就是ref

range

使用索引获取某些范围区间的数据时，那么就可能使用到range

index

当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是 index

possible_keys和key #

possible_keys：可能用到的索引；key ：真正使用的索引

possible_keys列中的值并不是越多越好，可能使用的索引越多，查询优化器计算查询成 本时就得花费更长时间，所以如果可以的话，尽量删除那些用不到的索引。

key_len #
filtered#
Extra#

extra列说明了一些额外的信息，接下来挑一些常见的额外信息来说明

Using index

出现该信息，说明MySQL能使用覆盖索引来避免访问表的行，不会回表，可以提升查询效率。

Using where

MySQL将在存储引擎中检索行拿到数据后，再去server层进行过滤，索引下推可以很好的避免这个问题

Using index condition

该搜索条件中出现了索引列，但是可能因为索引失效导致没有走索引

Using filesort

查询语句中的排序操作无法使用索引，只能在内存（记录少的时候）或者磁盘（记录多的时候）中进行排序，统称为“文件排序”

Using temporary

查询中使用了内部临时表

SQL优化#

SQL优化的目的：减少磁盘I/O、减少CUP消耗

优化策略：

慢SQL日志如何打开?#

如何通过慢查询日志分析 #

我通常会先配置慢查询参数，设置long_query_time阈值和log_queries_not_using_indexes选项，确保能捕获到真正有问题的SQL。日志生成后，我习惯用pt-query-digest工具分析，它能帮我快速定位执行频率高、耗时久的SQL。找到目标SQL后，会结合explain查看执行计划，重点关注type列的访问类型和key列是否正确使用索引，再检查rows列的扫描行数是否合理。如果发现全表扫描或临时表问题，就会考虑优化索引或重写SQL。对特别复杂的SQL，我还会结合show profile分析具体执行阶段的耗时分布，这样能更精准地找到性能瓶颈点。

索引创建的策略#

大部分的慢SQL都是因为没有使用到索引，走全表扫描导致速度变慢

对列建立索引的时候我们可以参考以下原则：

慢SQL的优化#
优化数据的访问#

查询性能低下的最基本的原因就是访问的数据过多，很多数据都不是我们想要的，我们想要的数据只是从大量数据筛选出来的一小部分，我们可以通过以下两种思路来优化数据的访问：

请求了不需要的列

  1. 查询不需要的记录：例如写一个新闻网站查询新闻的接口，我们的前端页面只显示10条就够了，但是我们却查询所有数据给前端，让前端来过滤数据。

解决思路：加limit限制查询的条数

解决思路：使用 select s_table.name,r_table.core 这种，而不是使用select *

扫描过多的数据

  1. 扫描行数和返回的行数：我们可以通过explain语句来查看sql执行计划，里面的rows 和 filtered 行可以表示

  2. 查看访问的类型：type列可以看到

如果我们的sql扫描了大量数据但是只返回了少量的行，我们可以使用以下办法来优化：

切分查询#

将大的查询切分为多个小的查询，例如：删除一千万条数据，我们可以分批删除，这样可以把一次性的压力分散到比较长的时间段中，可以大大减轻对服务器的影响

覆盖索引#

查询的时候，如果没有索引字段，那么考虑建立索引或者是联合索引，通过覆盖索引可以很好的避免回表现象

避免索引失效#

检查SQL语句中，有没有可能导致索引失效的语句

分解关联查询#

很多高性能的应用都会对关联查询进行分解，例子如下：

分解关联表

分解关联表的好处：

  1. 单表查询的话，可能命中查询缓存，提高效率

  2. 单个查询可以减少锁之间的竞争

  3. 在应用层做关联而不是在数据层关联，可以更容易对数据库进行拆分，更容易做到高可用和可扩展

  4. 减少冗余的记录，在应用层做关联，意味着只需要查询一次，而在数据层做关联可能会对同一个数据重复访问多次

排序优化#

如果extra中显示的内容是 filesort——文件排序，那么我们可以对排序进行优化，可以考虑对排序字段和其他字段建立联合索引，因为B+树索引是天然有序的

优化关联查询#
优化子查询#
优化分页offset x limit y#

分页的场景下，我们使用 limit 10000,2 来实现分页，这种偏移量非常大的分页十分影响性能，因为查到的数据前10000条都要被抛弃。如果使用了二级索引，那么大量无效的回表会严重影响性能

优化方式一：减少扫描次数

对于这种偏移量超级大的分页操作，我们可以通过记录上一页最后一条数据的id（max_id），然后在下一页查询的时候加上where id > max_id，这样就可以保证偏移量永远是0，查询速度始终稳定在毫秒级

优化方式二：减少回表

原理：二级索引中存在主键id，所以我们可以先查到前10000个数据的id 加偏移量20，这样我们在查询id为 10020、10021…的数据

减少锁持有的时间#

案例1：调整sql 的执行顺序

我们在执行update操作的时候，MySQL事务会加行级锁——next-key lock，这个锁是事务提交之后才会释放，如果update 和select 之间没有什么依赖关系，也就是说他们的操作互不影响的时候，一般来说把加锁的语句放在事务靠后的位置执行

案例2：分批删除

删除大量的数据表数据的时候，最好采用分批删除的方法，因为删除的时候，mysql会给删除的数据加上行锁，直到全部删除完毕之后，才会释放。锁持有的时间很长，会影响效率。

WHERE 和 HAVING 优化#

如果sql和索引都没问题，查询还是比较慢怎么办？#

高可用#

分库分表#

什么是分库分表？

分库分表就是把原本存储在单张表上的数据分成多个表，把原本储存在同一个数据库上的数据分成多个库，实现数据切分。

分库分表使用场景：

分库分表带来的问题#
分布式事务一致性#

一个大的操作被拆分为多个小的操作，多个小的操作被放到多个服务器中，如何才能让这些操作要么都成功要么都失败。如何实现分布式事务一致性呢？

2PC（两阶段提交）

基于MQ的消息投递

全局ID唯一性

Snowflake 是 Twitter 开源的分布式 ID 生成算法，由 64 位的二进制数字组成，一共分为 4 部分，下面是示意图：

缺点：强依赖机器时钟，如果出现始终回拨可能会导致生成相同id

跨库跨表关联查询问题

跨库跨表COUNT问题

跨库跨表排序问题

MySQL数据同步ES的四种方法#

同步双写#

在写入MySQL的时候，直接也同步往ES里写一份数据。

异步双写#

定时任务#

数据订阅#

MySQL通过binlog订阅实现主从同步，各路数据订阅框架比如canal就依据这个原理，将client组件伪装成从库，来实现数据订阅。