MySQL实践

基础架构

客户端

连接器： 管理连接，权限验证

查询缓存： 命中则直接返回结果

分析器： 词法分析，语法分析

优化器： 执行计划生成，索引选择

执行器： 操作引擎，返回结果

存储引擎： 存储数据，提供读写接口

一条查询语句的执行过程一般是经过连接器、分析器、优化器、执行器等功能模块，最后到达存储引擎

在一个表上有更新的时候，跟这个表有关的查询缓存会失效，所以这条语句就会把表 T 上所有缓存结果都清空。这也就是一般不建议使用查询缓存的原因

日志系统

更新流程还涉及两个重要的日志模块：redo log（重做日志）和 binlog（归档日志）

redo log

在MySQL里，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新， 整个过程 IO 成本、查找成本都很高；

WAL的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘

当有一条记录需要更新的时候，InnoDB引擎就会先把记录写到redo log里面，并更新内存， 这个时候更新就算完成了；

同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面， 而这个更新往往是在系统比较空闲的时候做

InnoDB 的redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB， 总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写

write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。 checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失， 这个能力称为 crash-safe。

binlog

这两种日志有以下三点不同。

1.redo log是InnoDB引擎特有的； binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。

2.redo log 是物理日志，记录的是“在某个数据页上做了什么修改”； binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。

3.redo log 是循环写的，空间固定会用完； binlog 是可以追加写入的。 “追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

redo log 用于保证 crash-safe 能力。 innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候， 表示每次事务的 redo log 都直接持久化到磁盘。 这个参数建议设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失。

sync_binlog 这个参数设置成 1 的时候， 表示每次事务的 binlog 都持久化到磁盘。 这个参数也建议设置成 1，这样可以保证 MySQL 异常重启之后binlog不丢失。

binlog能不能去掉?

不能

一个原因是，redolog只有InnoDB有，别的引擎没有。

另一个原因是，redolog是循环写的，不持久保存，binlog的“归档”这个功能，redolog是不具备的。

索引相关

InnoDB 的索引模型

在 MySQL 中，索引是在存储引擎层实现的，所以并没有统一的索引标准， 即不同存储引擎的索引的工作方式并不一样。

每一个表是好几棵B+树, 树结点的key值就是某一行的主键，value是该行的其他数据。新建索引就是新增一个B+树，查询不走索引就是遍历主B+树。

• B+ 树

InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。 每一个索引在 InnoDB 里面对应一棵 B+ 树。

聚簇索引（clustered index）–主键索引

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。

非主键索引

非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

• 基于主键索引和普通索引的查询有什么区别？

如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树； 如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树， 得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。

覆盖索引

如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值， 而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。 也就是说，在这个查询里面，索引 k 已经“覆盖了”查询需求，称为覆盖索引

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

最左前缀原则

索引项是按照索引定义里面出现的字段顺序排序的

索引下推(index condition pushdown)

例如： mysql> select * from tuser where name like '张%' and age=10 and ismale=1;

在 MySQL 5.6 之前，只能一个个回表。到主键索引上找出数据行，再对比字段值。

MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

普通索引和唯一索引

这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以，建议尽量选择普通索引。

两种索引对查询语句和更新语句的性能影响来进行分析

查询过程

select id from T where k=5;

• 普通索引

查找到满足条件的第一个记录 (5,500) 后，需要查找下一个记录， 直到碰到第一个不满足 k=5 条件的记录。

• 唯一索引

由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索

这个不同带来的性能差距–微乎其微。 如果 k=5 这个记录刚好是这个数据页的最后一个记录，那么要取下一个记录， 必须读取下一个数据页，这个操作会稍微复杂一些。 对于整型字段，一个数据页可以放近千个 key，因此出现这种情况的概率会很低。 所以，计算平均性能差异时，仍可以认为这个操作成本对于现在的 CPU 来说可以忽略不计。

更新过程

1.第一种情况是，这个记录要更新的目标页在内存中。

这时，InnoDB 的处理流程如下： 对于唯一索引来说，找到 3 和 5 之间的位置，判断到没有冲突，插入这个值，语句执行结束； 对于普通索引来说，找到 3 和 5 之间的位置，插入这个值，语句执行结束。 这样看来，普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微小的 CPU 时间。

2.第二种情况是，这个记录要更新的目标页不在内存中。

这时，InnoDB 的处理流程如下：

对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；

对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。

将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。 change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

change buffer

A special data structure that records changes to pages in secondary indexes. These values could result from SQL INSERT, UPDATE, or DELETE statements (DML). The set of features involving the change buffer is known collectively as change buffering, consisting of insert buffering, delete buffering, and purge buffering.

change buffer 只限于用在普通索引的场景下，而不适用于唯一索引

redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写）， 而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。

怎么选择索引，为什么会选错索引

怎么给字符串加索引

order by

事务相关

ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）

Isolation

隔离性与隔离级别

隔离级别

• 读未提交

是指，一个事务还没提交时，它做的变更就能被别的事务看到。

• 读提交

是指，一个事务提交之后，它做的变更才会被其他事务看到。

• 可重复读

是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。 当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。

• 串行化

顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。 当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

show variables like ‘transaction_isolation’;

show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.03 sec)

事务的启动方式

1.显式启动事务语句， begin 或 start transaction。 配套的提交语句是 commit，回滚语句是 rollback。

2.set autocommit=0，这个命令会将这个线程的自动提交关掉。 意味着如果只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。 这个事务持续存在直到主动执行 commit 或 rollback 语句，或者断开连接。

建议总是使用 set autocommit=1, 通过显式语句的方式来启动事务。

可以在 information_schema 库的 innodb_trx 这个表中查询长事务， 比如下面这个语句，用于查找持续时间超过 60s 的事务： select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

事务的启动时机

begin/start transaction 命令并不是一个事务的起点， 在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动。 如果想要马上启动一个事务，可以使用 start transaction with consistent snapshot 这个命令

实践：

CREATE TABLE `t` ( `id` int(11) NOT NULL, `k` int(11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
insert into t(id, k) values(1,1),(2,2);

事务A：

1
mysql> start transaction with consistent snapshot;
Query OK, 0 rows affected (0.00 sec)
mysql> 
mysql> 
mysql> 
5
mysql> select k from t where id=1;
+------+
| k    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

事务B：

2
mysql> start transaction with consistent snapshot;
Query OK, 0 rows affected (0.00 sec)
mysql> 
4
mysql> update t set k=k+1 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select k from t where id=1;
+------+
| k    |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

mysql> 
6
mysql> commit;
Query OK, 0 rows affected (0.07 sec)

事务C：

3
mysql> update t set k=k+1 where id=1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

视图

在 MySQL 里，有两个“视图”的概念：

view

它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。 创建视图的语法是 create view … ， 而它的查询方法与表一样。

MVCC一致性读视图

另一个是 InnoDB 在实现 MVCC 时用到的一致性读视图， 即 consistent read view，用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现。

row trx_id

锁相关

数据库锁设计的初衷是处理并发问题。 作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。 而锁就是用来实现这些访问规则的重要数据结构。

根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类

全局锁

全局锁就是对整个数据库实例加锁。 MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。 当需要让整个库处于只读状态的时候，可以使用这个命令， 之后其他线程的以下语句会被阻塞： 数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

全局锁的典型使用场景是，做全库逻辑备份。也就是把整库每个表都 select 出来存成文本。

• mysqldump

官方自带的逻辑备份工具是 mysqldump。 当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务， 来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。

有了这个功能，为什么还需要 FTWRL 呢？一致性读是好，但前提是引擎要支持这个隔离级别。 比如，对于 MyISAM 这种不支持事务的引擎，如果备份过程中有更新，总是只能取到最新的数据， 那么就破坏了备份的一致性。这时，就需要使用 FTWRL 命令了。

• set global readonly=true

既然要全库只读，为什么不使用 set global readonly=true 的方式呢？

确实 readonly 方式也可以让全库进入只读状态，但还是建议用 FTWRL 方式， 主要有两个原因： 一是，在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。 因此，修改 global 变量的方式影响面更大，不建议使用。

二是，在异常处理机制上有差异。 如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁， 整个库回到可以正常更新的状态。 而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态， 这样会导致整个库长时间处于不可写状态，风险较高。

表级锁

表锁一般是在数据库引擎不支持行锁的时候才会被用到的

MySQL 里面表级别的锁有两种： 一种是表锁， 一种是元数据锁（meta data lock，MDL)。

表锁

表锁的语法是 lock tables … read/write lock tables t1 read, t2 write;

在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。而对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大。

MDL（metadata lock)

MDL 会直到事务提交才释放，在做表结构变更的时候，一定要小心不要导致锁住线上查询和更新。

MDL 不需要显式使用，在访问一个表的时候会被自动加上。 MDL 的作用是，保证读写的正确性。 可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

在 MySQL 5.5 版本中引入了 MDL， 当对一个表做增删改查操作的时候，加 MDL 读锁； 当要对表做结构变更操作的时候，加 MDL 写锁。

• 读锁之间不互斥，因此可以有多个线程同时对一张表增删改查。

• 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。

因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ... 

行锁

MySQL 的行锁是在引擎层由各个引擎自己实现的。 但并不是所有的引擎都支持行锁，比如 MyISAM 引擎就不支持行锁。 不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行， 这就会影响到业务并发度。 InnoDB 是支持行锁的，这也是 MyISAM 被 InnoDB 替代的重要原因之一。

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。 这个就是两阶段锁协议。

两阶段协议

如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁的申请时机尽量往后放。

调整语句顺序并不能完全避免死锁。所以引入了死锁和死锁检测的概念

死锁和死锁检测

当出现死锁以后，有两种策略： 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。

另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务， 让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

在 InnoDB 中，innodb_lock_wait_timeout 的默认值是 50s， 意味着如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过 50s 才会超时退出， 然后其他线程才有可能继续执行。 对于在线服务来说，这个等待时间往往是无法接受的。

如果是事务都要更新同一行的场景呢？

每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁， 这是一个时间复杂度是 O(n) 的操作。 假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的。 虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU 资源。 因此，CPU利用率很高，但是每秒却执行不了几个事务。

怎么解决由这种热点行更新导致的性能问题呢？

问题的症结在于，死锁检测要耗费大量的 CPU 资源。

1.一种头痛医头的方法，如果能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。 但是这种操作本身带有一定的风险，因为业务设计的时候一般不会把死锁当做一个严重错误， 毕竟出现死锁了，就回滚，然后通过业务重试一般就没问题了，这是业务无损的。

而关掉死锁检测意味着可能会出现大量的超时，这是业务有损的。

2.控制并发度。

根据上面的分析，如果并发能够控制住，比如同一行同时最多只有 10 个线程在更新， 那么死锁检测的成本很低，就不会出现这个问题。 一个直接的想法就是，在客户端做并发控制。 但是，这个方法不太可行，因为客户端很多。 有 600 个客户端，这样即使每个客户端控制到只有 5 个并发线程，汇总到数据库服务端以后， 峰值并发数也可能要达到 3000。

因此，这个并发控制要做在数据库服务端。 如果有中间件，可以考虑在中间件实现；

如果团队有能修改 MySQL 源码的人，也可以做在 MySQL 里面。 基本思路就是，对于相同行的更新，在进入引擎之前排队。 这样在 InnoDB 内部就不会有大量的死锁检测工作了。

问题

如果要删除一个表里面的前 10000 行数据， 有以下三种方法可以做到：
第一种，直接执行 delete from T limit 10000;
第二种，在一个连接中循环执行 20 次 delete from T limit 500;
第三种，在 20 个连接中同时执行 delete from T limit 500。

方案一，事务相对较长，则占用锁的时间较长，会导致其他客户端等待资源时间较长。 方案二，串行化执行，将相对长的事务分成多次相对短的事务， 则每次事务占用锁的时间相对较短，其他客户端在等待相应资源的时间也较短。 这样的操作，同时也意味着将资源分片使用（每次执行使用不同片段的资源），可以提高并发性。 方案三，人为自己制造锁竞争，加剧并发量。 方案二相对比较好，具体还要结合实际业务场景。

在一个连接中循环执行 20 次 delete from T limit 500。

确实是这样的，第二种方式是相对较好的。

第一种方式（即：直接执行 delete from T limit 10000）里面，单个语句占用时间长， 锁的时间也比较长；而且大事务还会导致主从延迟。

第三种方式（即：在 20 个连接中同时执行 delete from T limit 500），会人为造成锁冲突。

回顾

1.两阶段锁的概念是什么? 对事务使用有什么帮助?
2.死锁的概念是什么? 举例说明出现死锁的情况.
3.死锁的处理策略有哪两种?
4.等待超时处理死锁的机制什么?有什么局限?
5.死锁检测处理死锁的机制是什么? 有什么局限?
6.有哪些思路可以解决热点更新导致的并发问题?

主备相关

流程

start–>

undolog–>

data–>

redolog–>

binlog–>

主备

一个事务日志同步的完整过程：

1.在备库 B 上通过 change master 命令，设置主库 A 的 IP、端口、用户名、密码，以及要从哪个位置开始请求 binlog，这个位置包含文件名和日志偏移量。

2.在备库 B 上执行 start slave 命令，这时候备库会启动两个线程，io_thread 和 sql_thread。其中 io_thread 负责与主库建立连接。

3.主库 A 校验完用户名、密码后，开始按照备库 B 传过来的位置，从本地读取 binlog，发给 B。

4.备库 B 拿到 binlog 后，写到本地文件，称为中转日志（relay log）。

5.sql_thread 读取中转日志，解析出日志里的命令，并执行。

双主

如果设置了双 M 结构， 1.从节点 A 更新的事务，binlog 里面记的都是 A 的 server id；

2.传到节点 B 执行一次以后，节点 B 生成的 binlog 的 server id 也是 A 的 server id；

3.再传回给节点 A，A 判断到这个 server id 与自己的相同，就不会再处理这个日志。所以，死循环在这里就断掉了。

备库设置为只读-readOnly

1.有时候一些运营类的查询语句会被放到备库上去查，设置为只读可以防止误操作；

2.防止切换逻辑有 bug，比如切换过程中出现双写，造成主备不一致；

3.可以用 readonly 状态，来判断节点的角色。

备库设置成只读了，还怎么跟主库保持同步更新呢？

readonly 设置对超级 (super) 权限用户是无效的，而用于同步更新的线程，就拥有超级权限。

主备延迟

1.主库 A 执行完成一个事务，写入 binlog，记为 T1;

2.之后传给备库 B，备库 B 接收完这个 binlog 的时刻记为 T2;

3.备库 B 执行完成这个事务，记为 T3。

主备延迟，就是同一个事务，在备库执行完成的时间和主库执行完成的时间之间的差值

show slave status 显示 seconds_behind_master，用于表示当前备库延迟了多少秒

主备延迟最直接的表现是，备库消费中转日志（relay log）的速度，比主库生产 binlog 的速度要慢

主备延迟的来源

1.有些部署条件下，备库所在机器的性能要比主库所在的机器性能差; 多个备库部署在同一台机器上，大量的查询会导致io资源的竞争;

解决办法:配置”双1“，redo log和binlog都只write fs page cache

2.备库的压力大，产生的原因大量的查询操作在备库操作，耗费了大量的cpu，导致同步延迟;

解决办法:使用一主多从，多个从减少备的查询压力

3.大事务，因为如果一个大的事务的dml操作导致执行时间过长，将其事务binlog发送给备库，备库也需执行那么长时间，导致主备延迟;

解决办法:尽量减少大事务，比如delete操作，使用limit分批删除，可以防止大事务也可以减少锁的范围。

4.大表的ddl，会导致主库将其ddl binlog发送给备库，备库解析中转日志，同步，后续的dml binlog发送过来，需等待ddl的mdl写锁释放，导致主备延迟

读写分离

过期读问题

1.强制走主库方案；

对于必须要拿到最新结果的请求，强制将其发到主库上； 比如，在一个交易平台上，卖家发布商品以后，马上要返回主页面，看商品是否发布成功。 那么，这个请求需要拿到最新的结果，就必须走主库。

对于可以读到旧数据的请求，才将其发到从库上； 在这个交易平台上，买家来逛商铺页面，就算晚几秒看到最新发布的商品，也是可以接受的。 那么，这类请求就可以走从库。

2.sleep 方案；

主库更新后，读从库之前先 sleep 一下

3.判断主备无延迟方案；

每次从库执行查询请求前，先判断 seconds_behind_master 是否已经等于 0。如果还不等于 0 ，那就必须等到这个参数变为 0 才能执行查询请求。

对比位点确保主备无延迟：Master_Log_File 和 Read_Master_Log_Pos，表示的是读到的主库的最新位点； Relay_Master_Log_File 和 Exec_Master_Log_Pos，表示的是备库执行的最新位点。 如果 Master_Log_File 和 Relay_Master_Log_File、Read_Master_Log_Pos 和 Exec_Master_Log_Pos 这两组值完全相同， 就表示接收到的日志已经同步完成。

对比 GTID 集合确保主备无延迟： Auto_Position=1 ，表示这对主备关系使用了 GTID 协议。 Retrieved_Gtid_Set，是备库收到的所有日志的 GTID 集合； Executed_Gtid_Set，是备库所有已经执行完成的 GTID 集合。 如果这两个集合相同，也表示备库接收到的日志都已经同步完成。

4.配合 semi-sync 方案；

半同步复制，也就是 semi-sync replication

semi-sync的设计： 事务提交的时候，主库把 binlog 发给从库；

从库收到 binlog 以后，发回给主库一个 ack，表示收到了；

主库收到这个 ack 以后，才能给客户端返回“事务完成”的确认。

如果启用了 semi-sync，就表示所有给客户端发送过确认的事务，都确保了备库已经收到了这个日志。

semi-sync+ 位点判断的方案，只对一主一备的场景是成立的

semi-sync 配合判断主备无延迟的方案，存在两个问题：

一主多从的时候，在某些从库执行查询请求会存在过期读的现象；

在持续延迟的情况下，可能出现过度等待的问题。

5.等主库位点方案；

select master_pos_wait(file, pos[, timeout]);

这条命令的逻辑如下： 它是在从库执行的；

参数 file 和 pos 指的是主库上的文件名和位置；

timeout 可选，设置为正整数 N 表示这个函数最多等待 N 秒。

这个命令正常返回的结果是一个正整数 M，表示从命令开始执行，到应用完 file 和 pos 表示的 binlog 位置，执行了多少事务。

除了正常返回一个正整数 M 外，这条命令还会返回一些其他结果，包括：

如果执行期间，备库同步线程发生异常，则返回 NULL；

如果等待超过 N 秒，就返回 -1；

如果刚开始执行的时候，就发现已经执行过这个位置了，则返回 0。

6.等 GTID 方案。

select wait_for_executed_gtid_set(gtid_set, 1);

这条命令的逻辑是：

等待，直到这个库执行的事务中包含传入的 gtid_set，返回 0；

超时返回 1。

MySQL 5.7.6 版本开始，允许在执行完更新类事务后，把这个事务的 GTID 返回给客户端，这样等 GTID 的方案就可以减少一次查询。

等 GTID 的执行流程：

事务更新完成后，从返回包直接获取这个事务的 GTID，记为 gtid1；

选定一个从库执行查询语句；

在从库上执行 select wait_for_executed_gtid_set(gtid1, 1)；

如果返回值是 0，则在这个从库执行查询语句；

否则，到主库执行查询语句。

将参数 session_track_gtids 设置为 OWN_GTID，然后通过 API 接口 mysql_session_track_get_first 从返回包解析出 GTID 的值

7.总结

在实际应用中，这几个方案是可以混合使用的。

比如，先在客户端对请求做分类，区分哪些请求可以接受过期读，而哪些请求完全不能接受过期读；

然后，对于不能接受过期读的语句，再使用等 GTID 或等位点的方案。

分区表

1.分区表创建：

CREATE TABLE `t` (
  `ftime` datetime NOT NULL,
  `c` int(11) DEFAULT NULL,
  KEY (`ftime`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE (YEAR(ftime))
(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
 PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
 PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

insert into t values('2017-4-1',1),('2018-4-1',1);

session A:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where ftime='2017-5-1' for update;
Empty set (0.01 sec)

session B:

mysql> insert into t values('2018-2-1',1);
Query OK, 1 row affected (0.10 sec)

mysql> insert into t values('2017-2-1',1);
Query OK, 1 row affected (0.04 sec)

mysql> insert into t values('2017-12-1',1);

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

2.手动分表

按照年份来划分，分别创建普通表 t_2017、t_2018、t_2019 等等。

手工分表的逻辑，也是找到需要更新的所有分表，然后依次执行更新。

在性能上，这和分区表并没有实质的差别。

分区表和手工分表

分区表和手工分表，一个是由 server 层来决定使用哪个分区，一个是由应用层代码来决定使用哪个分表。 因此，从引擎层看，这两种方式也是没有差别的。

区别，主要是在 server 层上。分区策略每当第一次访问一个分区表的时候，MySQL 需要把所有的分区都访问一遍。 一个典型的报错情况是这样的：如果一个分区表的分区很多，比如超过了 1000 个，而 MySQL 启动的时候， open_files_limit 参数使用的是默认值 1024，那么就会在访问这个表的时候，由于需要打开所有的文件， 导致打开表文件的个数超过了上限而报错。

server层

MySQL 在第一次打开分区表的时候，需要访问所有的分区；

在 server 层，认为这是同一张表，因此所有分区共用同一个 MDL 锁；

在引擎层，认为这是不同的表，因此 MDL 锁之后的执行过程，会根据分区表规则，只访问必要的分区。

分区表的优势

对业务透明，相对于用户分表来说，使用分区表的业务代码更简洁。

分区表可以很方便的清理历史数据。可以直接通过 alter table t drop partition …这个语法删掉分区，从而删掉过期的历史数据。 效果跟 drop 普通表类似。与使用 delete 语句删除数据相比，优势是速度快、对系统影响小。

注意

分区并不是越细越好。单表或者单分区的数据一千万行，只要没有特别大的索引，对于现在的硬件能力来说都已经是小表了。

分区也不要提前预留太多，在使用之前预先创建即可。 比如，如果是按月分区，每年年底时再把下一年度的 12 个新分区创建上即可。 对于没有数据的历史分区，要及时的 drop 掉。

操作相关

mysql的正常的优化方案（步骤）如下：

1.代码中sql语句优化

2.数据库字段优化，索引优化

3.加缓存，redis/memcache等

4.主从，读写分离

5.分区表

6.垂直拆分，解耦模块

7.水平切分

MySQL man help

For information about MySQL products and services, visit:
   http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
   http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
   https://shop.mysql.com/

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
resetconnection(\x) Clean session context.

For server side help, type 'help contents'

refs

https://dev.mysql.com/doc/refman/5.7/en/c-api-functions.html