数据库MySQL

发表于 更新于 阅读次数： 本文字数： 111k 阅读时长 ≈ 1:41

什么是事务

事务是逻辑上的一组操作，要么都执行，要么都不执行。

事务有四大特性，即原子性，一致性，隔离性和持久性（ACID）

  • 原子性（Atomicity）：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用
  • 一致性（Consistency）：执行事务前后，数据要保持一致，多个事务对同一个数据读取的结果是相同的
  • 隔离性（Isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的
  • 持久性（Durability）：一个事务被提交之后，它对数据库中数据的改变是持久化的，即使数据库发生故障也不应该对其有任何影响。

几个特性之间的关系：

  • 只有满足一致性，事务的执行结果才是正确
  • 在无并发的情况下，事务串行执行，隔离性一定能够满足。此时只要能满足原子性，就一定能满足一致性。
  • 在并发的情况下，多个事务并行执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性。
  • 事务满足持久化是为了能应对数据库崩溃的情况

MySQL默认采用自动提交模式，也就是说，如果不显示使用 START TRANSACTION 语句来开始一个事务，那么每个查询都会被当做一个事务自动提交。

并发事务带来哪些问题

  • 脏读（Dirty read）：当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另一个事务读到的这个数据就是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
  • 丢失数据（Lost to modify）：指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。
  • 不可重复读（Unrepeatableread）：指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据不一样的情况，称为不可重复读。
  • 幻读（Phantom read）：幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入一些数据时，在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复读和幻读区别：不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除比如多次读取一条记录发现记录增多或者减少了

事务隔离级别有哪些？MySQL默认隔离级别是？

SQL标准定义了四个隔离级别：

  • READ-UNCOMMITTED(读取未提交)：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读，幻读，不可重复读。
  • READ-COMMITTED(读取已提交)：允许读取并发事务已经提交的数据，可以阻止脏读，但是仍然可能发生幻读和不可重复读
  • REPEATABLE-READ(可重复读)：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，保证一个事务不会修改已经由另一个事务读取但未提交（回滚）的数据，可以避免脏读和不可重复读，但是幻读仍然有可能发生。
  • SERIALIZABLE(可串行化)：最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读、幻读

MySQL InnoDB存储引擎默认支持的隔离级别是REPEATABLE-READ(可重复读)，可以通过SELECT @@tx_isolation命令查看。在MySQL8.0改为使用SELECt @@transaction_isolation;

注意：与SQL标准不同的地方在于，InnoDB存储引擎在REPEATABLE-READ（可重读）事务隔离级别下使用的是Next-Key Lock锁算法，因此可以避免幻读的产生，这与其他数据库系统是不同的。所以说InnoDB存储引擎的默认支持的隔离级别是REPEATABLE-READ(可重复读)已经完全保证事务的隔离性要求，即达到了SQL标准的SERIALIZABLE（可串行化）隔离级别。因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是READ-COMMITTED(提交读)，但是InnoDB存储引擎默认使用的REPEATABLE-READ(重复读)并不会有任何性能损失。

InnoDB存储引擎在分布式事务的情况下一般会用到SERIALIZABLE（可串行化）隔离级别。

锁机制与InnoDB锁算法

MySQL中提供了三种封锁粒度：行级锁、表级锁和页级锁。

三种锁对比：

  • 表级锁：MySQL中锁定粒度最大的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和InnoDB引擎都支持表级锁。表级锁总是一次获得 SQL 语句所需要的全部锁，要么全部满足，要么全部等待，所以不会产生死锁，这也正是表级锁不会出现死锁(Deadlock Free)的原因。

  • 行级锁：MySQL中锁定粒度最小的一种锁，只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突，其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

    行级锁不一定比表级锁要好：锁的粒度越细，代价越高，相比表级锁在表的头部直接加锁，行级锁还要扫描找到对应的行对其上锁，这样的代价其实是比较高的，所以表锁和行锁各有所长。

  • 页级锁：锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。页级进行了折衷，一次锁定相邻的一组记录。BDB 支持页级锁。开销和加锁时间界于表锁和行锁之间，会出现死锁。锁定粒度界于表锁和行锁之间，并发度一般。

MyISAM 和 InnoDB 存储引擎使用的锁

MyISAM

  • 采用表级锁，所以 MyISAM 的加锁相对比较开销低，但数据操作的并发性能相对就不高。但如果写操作都是尾插入，那还是可以支持一定程度的读写并发。
  • MyISAM 是一个支持读读并发，但不支持通用读写并发，写写并发的数据库引擎，所以它更适合用于读多写少的应用场合，一般工程中也用的较少。

InnoDB

支持行级锁和表级锁，默认是行级锁，具体来说，InnoDB支持的锁有：

  • 共享锁和排他锁 (Shared and Exclusive Locks)

  • 意向锁（Intention Locks）

  • 记录锁（Record Locks）

  • 间隙锁（Gap Locks）

  • 临键锁 （Next-Key Locks）

  • 插入意向锁（Insert Intention Locks）

  • 主键自增锁 (AUTO-INC Locks)

  • 空间索引断言锁（Predicate Locks for Spatial Indexes）

锁分类（按照是否可写分类）

表级锁和行级锁还可以进一步划分为共享锁（S）和排他锁（X）。

  • 共享锁（Share Locks）：又被称为读锁，其它用户可以并发读取数据，但任何事务都不能获取数据上的排他锁，直到已释放所有共享锁。若事务 T 对数据对象 A 加上 S 锁，则事务 T 只能读 A；其他事务只能再对 A 加 S 锁，而不能加 X 锁，直到 T 释放 A 上的 S 锁。这就保证了其他事务可以读 A，但在 T 释放 A 上的 S 锁之前不能对 A 做任何修改。
  • 排他锁（Exclusive lock）：又称为写锁，若事务 T 对数据对象 A 加上 X 锁，则只允许 T 读取和修改 A，其它任何事务都不能再对 A 加任何类型的锁，直到 T 释放 A 上的锁。它防止任何其它事务获取资源上的锁，直到在事务的末尾将资源上的原始锁释放为止。在更新操作(INSERT、UPDATE或DELETE)过程中始终应用排他锁。

InnoDB 行级锁算法

InnoDB存储引擎支持行级锁的算法有三种：

  • Record lock:单个行记录上的锁，对索引项加锁，而不是记录本身，锁定符合条件的行。其他事务不能修改和删除加锁项。
  • Gap lock：间隙锁，对索引项之间的“间隙”加锁。当用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁；**对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁。**锁定记录的范围（对第一条记录前的间隙或最后一条记录后的间隙加锁），不包括索引项本身。其它事务不能在锁访问内插入数据，这样就防止了别的事务新增幻影行。
  1. 间隙锁的目的是阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生,只有在 REPEATABLE READ 隔离级别下的特定操作才会取得gap lock
  2. UPDATE/DELETE/SELECT FOR UPDATE时，除了对唯一索引的唯一搜索外都会获取gap锁，也就是说主键或唯一索引的相等条件搜索不会获取间隙锁，当然如果查询条件还包含非唯一索引，那么还是会获取间隙锁
  • Next-key lock: 临键锁，record+gap 锁定索引项本身和索引范围，即Record Lock和Gap Lock的结合，是一个前开后闭的区间，结合MVCC，可解决幻读问题。例如一个索引包含以下值：10，11，13，20，那么就需要锁定以下区间：
(-∞,10]
(10,11]
(11,13]
(13,20]
(20,+supremum)

InnoDB三种锁的应用：

  1. innodb对于行的查询使用next-key lock
  2. Next-key lock是为了解决Phantom Problem幻读问题
  3. 当查询的索引含有唯一属性时，将next-key lock降级为record lock
  4. 索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，NextKeyLock 退化为间隙锁。
  5. 唯一索引上的范围查询会访问到不满足条件的第一个值为止。
  6. Gap锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生
  7. 有两种方式显式关闭gap锁：（除了外键约束和唯一性检查外，其余情况仅使用record lock）
  • 将事务隔离级别设置为RC
  • 将参数innodb_locks_unsafe_for_binlog设置为1

虽然使用行级锁具有粒度小、并发度高等特点，但是表级锁有时候也是非常必要的：

  • 事务更新大表中的大部分数据直接使用表级锁效率更高；
  • 事务比较复杂，使用行级锁很可能引起死锁导致回滚。

通过上面的分析可以看出，InnoDB 的行级锁实现特点是在索引上加锁，所以只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！

InnoDB 表级锁算法

当一个事务需要给自己需要的某个资源加锁的时候，如果遇到一个共享锁正锁定着自己需要的资源的时候，自己可以再加一个共享锁，不过不能加排他锁。但是，如果遇到自己需要锁定的资源已经被一个排他锁占有之后，则只能等待该锁定释放资源之后自己才能获取锁定资源并添加自己的锁定。而意向锁的作用就是当一个事务在需要获取资源锁定的时候，如果遇到自己需要的资源已经被排他锁占用的时候，该事务可以在需要锁定行的表上面添加一个合适的意向锁。如果自己需要一个共享锁，那么就在表上面添加一个意向共享锁。而如果自己需要的是某行（或者某些行）上面添加一个排他锁的话，则先在表上面添加一个意向排他锁。意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在。

InnoDB 另外两个表级锁：

  • 意向共享锁（IS）：表示事务准备给数据行加入共享锁，事务在一个数据行加共享锁前必须先取得该表的IS锁
  • 意向排他锁（IX）：表示事务准备给数据行加入排他锁，事务在一个数据行加排他锁前必须先取得该表的IX锁

在只存在行级锁和表级锁的情况下，事务 T 想要对 A 加 X锁，就需要先检测是否有其它事务对表 A 或者表 A 中的任意一行加了锁，那么就需要对表 A 的每一行都检测一次，这是非常耗时的。

在引入了意向锁后，如果事务 T 想要对表 A 加 X 锁，只需要先检测是否有其它事务对表 A 加了 X/IX/S/IS 锁，如果加了就表示有其它事务正在使用这个表或者表中某一行的锁，因此事务 T 加 X 锁失败。

注意：

  • 这里的意向锁是表级锁 ，表示的是一种意向，仅仅表示事务正在读或写某一行记录，在真正加行锁时才会判断是否冲突。意向锁是 InnoDB 自动加的，不需要用户干涉。
  • IX，IS 是表级锁，不会和行级的 X，S 锁发生冲突，只会和表级的 X，S 发生冲突。

当一个事务请求的锁模式与当前的锁兼容，InnoDB 就将请求的锁授予该事务；反之如果请求不兼容，则该事物就等待锁释放。

死锁和避免死锁

InnoDB的行级锁是基于索引实现的，如果查询语句没命中任何索引，那么InnoDB就会使用表级锁。此外，InnoDB的行级锁是针对索引加的锁，不针对数据记录，因此即使访问不同行的记录，如果使用了相同的索引键仍然会出现锁冲突。

在通过SELECT ... LOCK IN SHARE MODE;（加IS锁）或SELECT ... FOR UPDATE;（加IX锁）使用锁的时候，如果表没有定义任何索引，那么 InnoDB 会创建一个隐藏的聚簇索引并使用这个索引来加记录锁

不同于 MyISAM 总是一次性获得所需的全部锁，InnoDB 的锁是逐步获得的，当两个事务都需要获得对方持有的锁，导致双方都在等待，这就产生了死锁。 发生死锁后，InnoDB一般都可以检测到，并使一个事务释放锁回退，另一个则可以获取锁完成事务，我们可以采取以下方式避免死锁：

  • 通过表级锁来减少死锁产生的概率；
  • 多个程序尽量约定以相同的顺序访问表（这也是解决并发理论中哲学家就餐问题的一种思路）
  • 同一个事务尽可能做到一次锁定所需要的所有资源。

封锁协议

三级封锁协议

一级封锁协议

事务T要修改A时必须加X锁，直到T结束才释放锁

可以解决丢失修改问题，因为不能同时有两个事务对同一个数据进行修改，那么事务的修改就不会被覆盖

二级封锁协议

在一级的基础上，要求读取数据A时必须加S锁，读取完马上释放S锁

可以解决读脏数据问题，因为如果一个事务在对数据A进行修改，根据1级封锁协议，会加X锁，那么就不能再加S锁了，也就不会读入数据。

三级封锁协议

在二级的基础上，要求读取数据A时必须加S锁，直到事务结束了才能释放S锁

可以解决不可重复读的问题，因为读A时，其它事务不能对A加X锁，从而避免了在读的期间数据发生改变。

两段锁协议

加锁和解锁分为两个阶段进行

可串行化调度是指，通过并发控制，使得并发执行的事务结果与某个串行执行的事务结果相同

事务遵循两段锁协议是保证可串行化调度的充分条件，例如下面操作满足两端锁协议，它是可串行化调度的：

lock-x(A)...lock-s(B)...lock-s(C)...unlock(A)...unlock(C)...unlock(B)

但是这不是必要条件，例如以下操作不满足两段锁协议，但是它还是可串行化调度

lock-x(A)...unlock(A)...lock-s(B)...unlock(B)...lock-s(C)...unlick(C)

MySQL的InnoDB引擎采用两段锁协议，会根据隔离级别在需要的时候自动加锁，并且所有的锁都是在同一时刻被释放，这被称为隐式锁定。

怎么编写加锁语句

对于 MyISAM 引擎表级锁：MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给MyISAM表显式加锁。

LOCK TABLE 命令：

LOCK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and t2 here];
COMMIT;
UNLOCK TABLES;

InnoDB也可以使用特定的语句进行显示锁定：

共享锁：SELECT ... LOCK IN SHARE MODE;
排他锁：SELECT ... FOR UPDATE;

表优化方案

单表优化

除非单表数据未来会一直不断上涨，否则不要一开始就考虑拆分，拆分会带来逻辑、部署、运维的各种复杂度，一般以整型值为主的表在千万级以下，字符串为主的表在五百万以下是没有太大问题的。而事实上很多时候MySQL单表的性能依然有不少优化空间，甚至能正常支撑千万级以上的数据量：

字段

  • 尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED
  • VARCHAR的长度只分配真正需要的空间
  • 使用枚举或整数代替字符串类型
  • 尽量使用TIMESTAMP而非`DATETIME`~~
  • 单表不要有太多字段，建议在20以内
  • 避免使用NULL字段，很难查询优化且占用额外索引空间
  • 用整型来存IP

索引

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

查询SQL

  • 可通过开启慢查询日志来找出较慢的SQL
  • 不做列运算：SELECT id WHERE age + 1 = 10，任何对列的操作都将导致表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能将操作移至等号右边
  • sql语句尽可能简单：一条sql只能在一个cpu运算；大语句拆小语句，减少锁时间；一条大sql可以堵死整个库
  • 不用SELECT *
  • OR改写成IN：OR的效率是n级别，IN的效率是log(n)级别，in的个数建议控制在200以内
  • 不用函数和触发器，在应用程序实现
  • 避免%xxx式查询
  • 少用JOIN
  • 使用同类型进行比较，比如用'123'和'123'比，123和123比
  • 尽量避免在WHERE子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描
  • 对于连续数值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5
  • 列表数据不要拿全表，要使用LIMIT来分页，每页数量也不要太大

引擎

目前广泛使用的是MyISAM和InnoDB两种引擎：

MyISAM

MyISAM引擎是MySQL 5.1及之前版本的默认引擎，它的特点是：

  • 不支持行锁，读取时对需要读到的所有表加锁，写入时则对表加排它锁
  • 不支持事务
  • 不支持外键
  • 不支持崩溃后的安全恢复
  • 在表有读取查询的同时，支持往表中插入新纪录
  • 支持BLOB和TEXT的前500个字符索引，支持全文索引
  • 支持延迟更新索引，极大提升写入性能
  • 对于不会进行修改的表，支持压缩表，极大减少磁盘空间占用

InnoDB

InnoDB在MySQL 5.5后成为默认索引，它的特点是：

  • 支持行锁，采用MVCC来支持高并发
  • 支持事务
  • 支持外键
  • 支持崩溃后的安全恢复
  • 不支持全文索引

总体来讲，MyISAM适合SELECT密集型的表，而InnoDB适合INSERT和UPDATE密集型的表

系统调优参数

可以使用下面几个工具来做基准测试：

  • sysbench：一个模块化，跨平台以及多线程的性能测试工具
  • iibench-mysql：基于 Java 的 MySQL/Percona/MariaDB 索引进行插入性能测试工具
  • tpcc-mysql：Percona开发的TPC-C测试工具

具体的调优参数内容较多，具体可参考官方文档，这里介绍一些比较重要的参数：

  • back_log：back_log 值指出在 MySQL 暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。也就是说，如果 MySql 的连接数据达到 max_connections 时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即 back_log，如果等待连接的数量超过 back_log，将不被授予连接资源。可以从默认的 50 升至 500
  • wait_timeout：数据库连接闲置时间，闲置连接会占用内存资源。可以从默认的8小时减到半小时
  • max_user_connection: 最大连接数，默认为0无上限，最好设一个合理上限
  • thread_concurrency：并发线程数，设为CPU核数的两倍
  • skip_name_resolve：禁止对外部连接进行DNS解析，消除DNS解析时间，但需要所有远程主机用IP访问
  • key_buffer_size：索引块的缓存大小，增加会提升索引处理速度，对MyISAM表性能影响最大。对于内存4G左右，可设为256M或384M，通过查询show status like 'key_read%'，保证key_reads / key_read_requests在0.1%以下最好
  • innodb_buffer_pool_size：缓存数据块和索引块，对InnoDB表性能影响最大。通过查询show status like 'Innodb_buffer_pool_read%'，保证 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests越高越好
  • innodb_additional_mem_pool_size：InnoDB 存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小，当数据库对象非常多的时候，适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率，当过小的时候，MySQL会记录Warning信息到数据库的错误日志中，这时就需要该调整这个参数大小
  • innodb_log_buffer_size：InnoDB存储引擎的事务日志所使用的缓冲区，一般来说不建议超过32MB
  • query_cache_size：缓存MySQL中的ResultSet，也就是一条SQL语句执行的结果集，所以仅仅只能针对select语句。当某个表的数据有任何任何变化，都会导致所有引用了该表的select语句在Query Cache中的缓存数据失效。所以，当我们的数据变化非常频繁的情况下，使用Query Cache可能会得不偿失。根据命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))进行调整，一般不建议太大，256MB可能已经差不多了，大型的配置型静态数据可适当调大.
    可以通过命令show status like 'Qcache_%'查看目前系统Query catch使用大小
  • read_buffer_size：MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，可以通过增加该变量值以及内存缓冲区大小提高其性能
  • sort_buffer_size：MySql执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能，可以尝试增加sort_buffer_size变量的大小
  • read_rnd_buffer_size：MySql的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。
  • record_buffer：每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，可能想要增加该值
  • thread_cache_size：保存当前没有与连接关联但是准备为后面新的连接服务的线程，可以快速响应连接的线程请求而无需创建新的
  • table_cache：类似于thread_cache_size，但用来缓存表文件，对InnoDB效果不大，主要用于MyISAM

升级硬件

Scale up，这个不多说了，根据MySQL是CPU密集型还是I/O密集型，通过提升CPU和内存、使用SSD，都能显著提升MySQL性能

缓存

缓存可以发生在这些层次：

  • MySQL内部：在系统调优参数介绍了相关设置
  • 数据访问层：比如MyBatis针对SQL语句做缓存
  • 应用服务层：这里可以通过编程手段对缓存做到更精准的控制和更多的实现策略，这里缓存的对象是数据传输对象Data Transfer Object
  • Web层：针对web页面做缓存
  • 浏览器客户端：用户端的缓存

可以根据实际情况在一个层次或多个层次结合加入缓存。这里重点介绍下服务层的缓存实现，目前主要有两种方式：

  • 直写式（Write Through）：在数据写入数据库后，同时更新缓存，维持数据库与缓存的一致性。这也是当前大多数应用缓存框架如Spring Cache的工作方式。这种实现非常简单，同步好，但效率一般。
  • 回写式（Write Back）：当有数据要写入数据库时，只会更新缓存，然后异步批量的将缓存数据同步到数据库上。这种实现比较复杂，需要较多的应用逻辑，同时可能会产生数据库与缓存的不同步，但效率非常高。

大表优化

当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，常见的优化措施如下：

限定数据的范围

禁止不带任何限制数据范围条件的查询语句，比如：当用户在查询历史订单的时候，控制在一个月的范围内。

读/写分离

经典的数据库拆分方案，主库负责写，从库负责读

表分区

MySQL在5.1版引入的分区是一种简单的水平拆分，用户需要在建表的时候加上分区参数，对应用是透明的无需修改代码

对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成，实现分区的代码实际上是通过对一组底层表的对象封装，但对SQL层来说是一个完全封装底层的黑盒子。MySQL实现分区的方式也意味着索引也是按照分区的子表定义，没有全局索引。

用户的SQL语句是需要针对分区表做优化，SQL条件中要带上分区条件的列，从而使查询定位到少量的分区上，否则就会扫描全部分区，可以通过EXPLAIN PARTITIONS来查看某条SQL语句会落在那些分区上，从而进行SQL优化，如下图5条记录落在两个分区上：

mysql> explain partitions select count(1) from user_partition where id in (1,2,3,4,5);
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table          | partitions | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | user_partition | p1,p4      | range | PRIMARY       | PRIMARY | 8       | NULL |    5 | Using where; Using index |
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

分区的好处是：

  • 可以让单表存储更多的数据
  • 分区表的数据更容易维护，可以通过清楚整个分区批量删除大量数据，也可以增加新的分区来支持新插入的数据。另外，还可以对一个独立分区进行优化、检查、修复等操作
  • 部分查询能够从查询条件确定只落在少数分区上，速度会很快
  • 分区表的数据还可以分布在不同的物理设备上，从而高效利用多个硬件设备
  • 可以使用分区表来避免某些特殊瓶颈，例如InnoDB单个索引的互斥访问、ext3文件系统的inode锁竞争
  • 可以备份和恢复单个分区

分区的限制和缺点：

  • 一个表最多只能有1024个分区
  • 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来
  • 分区表无法使用外键约束
  • NULL值会使分区过滤无效
  • 所有分区必须使用相同的存储引擎

分区的类型：

  • RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区
  • LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择
  • HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式
  • KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值

分区适合的场景有：

  • 最适合的场景数据的时间序列性比较强，则可以按时间来分区，如下所示：
CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
    PARTITION p0 VALUES LESS THAN (1960),
    PARTITION p1 VALUES LESS THAN (1970),
    PARTITION p2 VALUES LESS THAN (1980),
    PARTITION p3 VALUES LESS THAN (1990),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

查询时加上时间范围条件效率会非常高，同时对于不需要的历史数据能很容易的批量删除。

  • 如果数据有明显的热点，而且除了这部分数据，其他数据很少被访问到，那么可以将热点数据单独放在一个分区，让这个分区的数据能够有机会都缓存在内存中，查询时只访问一个很小的分区表，能够有效使用索引和缓存

另外MySQL有一种早期的简单的分区实现 - 合并表（merge table），限制较多且缺乏优化，不建议使用，应该用新的分区机制来替代

表分区与性能

表分区并不能提升性能，分区表技术不是用于提升 MySQL 数据库的性能，而是方便数据的管理。

B+ 树的高度与数据存储量的关系如下：

B+树的高度与总记录数的关系

从表格中可以看到，B+ 树的高度为 4 能存放数十亿的数据，一次查询只需要占用 4 次 I/O，速度非常快。

但是当使用分区之后，效果就不一样了，比如上面的表 t，我们根据时间拆成每年一张表，这时，虽然 B+ 树的高度从 4 降为了 3，但是这个提升微乎其微。

除此之外，分区表还会引入新的性能问题，比如非分区列的查询。非分区列的查询，即使分区列上已经创建了索引，但因为索引是每个分区文件对应的本地索引，所以要查询每个分区。

垂直分区

根据数据库里面数据表的相关性进行拆分，例如：用户表中既有用户的登录信息，又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库中做分库。

简单来说垂直拆分是指数据表列的拆分，把一张字段比较多的表拆分成多张表。

在数据库的层面使用垂直切分将按数据库中表的密集程度部署到不同的库中，例如将原来的电商数据库垂直切分成商品数据库、用户数据库等。

垂直切分

垂直拆分的优缺点：

  • 优点：可以使得列数据变小，在查询的时候减少读取的Block数，减少I/O次数，此外，垂直分区可以简化表的结构，易于维护。
  • 缺点：主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决，此外，垂直分区会让事务变得更加复杂。

水平分区

水平切分又称为Sharding，它是将同一个表中的记录拆分到多个结构相同的表中。

当一个表的数据不断增多时，Sharding是必然的选择，它可以将数据分布到集群的不同节点上，从而缓解单个数据库的压力。

保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。水平拆分可以支撑非常大的数据量。

水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆分成多张表来存放。

水平拆分可以支持非常大的数据量。需要注意的一点是：分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以水平拆分最好分库。

水平切分

水平拆分能够支持非常大的数据量存储，应用端改造也小，但是分片事务难以解决，跨节点Join性能较差，逻辑复杂。

水平拆分的优点是:

  • 不存在单库大数据和高并发的性能瓶颈
  • 应用端改造较少
  • 提高了系统的稳定性和负载能力

缺点是：

  • 分片事务一致性难以解决
  • 跨节点Join性能差，逻辑复杂
  • 数据多次扩展难度跟维护量极大

分片原则

  • 能不分就不分，参考单表优化
  • 分片数量尽量少，分片尽量均匀分布在多个数据结点上，因为一个查询SQL跨分片越多，则总体性能越差，虽然要好于所有数据在一个分片的结果，只在必要的时候进行扩容，增加分片数量
  • 分片规则需要慎重选择做好提前规划，分片规则的选择，需要考虑数据的增长模式，数据的访问模式，分片关联性问题，以及分片扩容问题，最近的分片策略为范围分片，枚举分片，一致性Hash分片，这几种分片都有利于扩容
  • 尽量不要在一个事务中的SQL跨越多个分片，分布式事务一直是个不好处理的问题
  • 查询条件尽量优化，尽量避免Select * 的方式，大量数据结果集下，会消耗大量带宽和CPU资源，查询尽量避免返回大量结果集，并且尽量为频繁使用的查询语句建立索引。
  • 通过数据冗余和表分区赖降低跨库Join的可能

这里特别强调一下分片规则的选择问题，如果某个表的数据有明显的时间特征，比如订单、交易记录等，则他们通常比较合适用时间范围分片，因为具有时效性的数据，我们往往关注其近期的数据，查询条件中往往带有时间字段进行过滤，比较好的方案是，当前活跃的数据，采用跨度比较短的时间段进行分片，而历史性的数据，则采用比较长的跨度存储。

总体上来说，分片的选择是取决于最频繁的查询SQL的条件，因为不带任何Where语句的查询SQL，会遍历所有的分片，性能相对最差，因此这种SQL越多，对系统的影响越大，所以我们要尽量避免这种SQL的产生。

水平分区策略：

  • 哈希取模：hash(key) % N；
  • 范围：可以是 ID 范围也可以是时间范围，就是每个库一段连续的数据，这个一般是按比如时间范围来的，但是这种一般较少用，因为很容易产生热点问题，大量的流量都打在最新的数据上了。
  • 映射表：使用单独的一个数据库来存储映射关系。

按范围来分，好处在于说，扩容的时候很简单，因为只要预备好，给每个月都准备一个库就可以了，到了一个新的月份的时候，自然而然，就会写新的库了；缺点，但是大部分的请求，都是访问最新的数据，大量的流量都打在最新的数据上

hash 分发，好处在于说，可以平均分配每个库的数据量和请求压力；坏处在于说扩容起来比较麻烦，会有一个数据迁移的过程，之前的数据需要重新计算 hash 值重新分配到不同的库或表。

存在的问题：

  1. 事务问题：使用分布式事务来解决，比如 XA 接口。
  2. 连接：可以将原来的连接分解成多个单表查询，然后在用户程序中进行连接。
  3. ID 唯一性
  • 使用全局唯一 ID（GUID）:使用一种全局唯一的字符串作为主键，类似 8f55d96b-8acc-4636-8cb8-76bf8abc2f57。GUID 算法通过网卡MAC地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的，大部分编程语言都内置了 GUID 算法，可以自己预算出主键。
  • 为每个分片指定一个 ID 范围
  • 分布式 ID 生成器 (如 Twitter 的 Snowflake 算法)

《Java工程师修炼之道》的作者推荐尽量不要对数据进行分片，因为拆分会带来逻辑、部署、运维的各种复杂度，一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。如果实在要分片，尽量选择客户端分片架构，这样可以减少一次和中间件的网络I/O。

数据库分片的两种常见方案

由于水平拆分牵涉的逻辑比较复杂，当前也有了不少比较成熟的解决方案。这些方案分为两大类：客户端架构和代理架构。

  • 客户端代理：分片逻辑在应用端，封装在 jar 包中，通过修改或者封装 JDBC 层来实现。
  • 中间件代理：在应用和数据中间加一个代理层，分片逻辑统一维护在中间件服务中。
客户端架构

通过修改数据访问层，如 JDBC、Data Source、MyBatis，通过配置来管理多个数据源，直连数据库，并在模块内完成数据的分片整合，一般以 Jar 包的方式呈现

这是一个客户端架构的例子：

分库分表客户端架构

可以看到分片的实现是和应用服务器在一起的，通过修改Spring JDBC层来实现

客户端架构的优点是：

  • 应用直连数据库，降低外围系统依赖所带来的宕机风险
  • 集成成本低，无需额外运维的组件

缺点是：

  • 限于只能在数据库访问层上做文章，扩展性一般，对于比较复杂的系统可能会力不从心
  • 将分片逻辑的压力放在应用服务器上，造成额外风险
代理架构

通过独立的中间件来统一管理所有数据源和数据分片整合，后端数据库集群对前端应用程序透明，需要独立部署和运维代理组件

这是一个代理架构的例子：

Alt text

代理组件为了分流和防止单点，一般以集群形式存在，同时可能需要Zookeeper之类的服务组件来管理

代理架构的优点是：

  • 能够处理非常复杂的需求，不受数据库访问层原来实现的限制，扩展性强
  • 对于应用服务器透明且没有增加任何额外负载

缺点是：

  • 需部署和运维独立的代理中间件，成本高
  • 应用需经过代理来连接数据库，网络上多了一跳，性能有损失且有额外风险
各方案比较（时间来源2016-08-03）
出品方 架构模型 支持数据库 分库 分表 读写分离 外部依赖 是否开源 实现语言 支持语言 最后更新 Github星数
MySQL Fabric MySQL官方 代理架构 MySQL 有 有 有 无 是 python 无限制 4个月前 35
Cobar 阿里巴巴 代理架构 MySQL 有 无 无 无 是 Java 无限制 两年前 1287
Cobar Client 阿里巴巴 客户端架构 MySQL 有 无 无 无 是 Java Java 三年前 344
TDDL 淘宝 客户端架构 无限制 有 有 有 Diamond 只开源部分 Java Java 未知 519
Atlas 奇虎360 代理架构 MySQL 有 有 有 无 是 C 无限制 10个月前 1941
Heisenberg 百度熊照 代理架构 MySQL 有 有 有 无 是 Java 无限制 2个月前 197
TribeDB 个人 代理架构 MySQL 有 有 有 无 是 NodeJS 无限制 3个月前 126
ShardingJDBC 当当 客户端架构 MySQL 有 有 有 无 是 Java Java 当天 1144
Shark 个人 客户端架构 MySQL 有 有 无 无 是 Java Java 两天前 84
KingShard 个人 代理架构 MySQL 有 有 有 无 是 Golang 无限制 两天前 1836
OneProxy 平民软件 代理架构 MySQL 有 有 有 无 否 未知 无限制 未知 未知
MyCat 社区 代理架构 MySQL 有 有 有 无 是 Java 无限制 两天前 1270
Vitess Youtube 代理架构 MySQL 有 有 有 无 是 Golang 无限制 当天 3636
Mixer 个人 代理架构 MySQL 有 有 无 无 是 Golang 无限制 9个月前 472
JetPants Tumblr 客户端架构 MySQL 有 有 无 无 是 Ruby Ruby 10个月前 957
HibernateShard Hibernate 客户端架构 无限制 有 有 无 无 是 Java Java 4年前 57
MybatisShard MakerSoft 客户端架构 无限制 有 有 无 无 是 Java Java 11个月前 119
Gizzard Twitter 代理架构 无限制 有 有 无 无 是 Java 无限制 3年前 2087

如此多的方案，如何进行选择？可以按以下思路来考虑：

  1. 确定是使用代理架构还是客户端架构。中小型规模或是比较简单的场景倾向于选择客户端架构，复杂场景或大规模系统倾向选择代理架构
  2. 具体功能是否满足，比如需要跨节点ORDER BY，那么支持该功能的优先考虑
  3. 不考虑一年内没有更新的产品，说明开发停滞，甚至无人维护和技术支持
  4. 最好按大公司->社区->小公司->个人这样的出品方顺序来选择
  5. 选择口碑较好的，比如github星数、使用者数量质量和使用者反馈
  6. 开源的优先，往往项目有特殊需求可能需要改动源代码

按照上述思路，推荐以下选择：

  • 客户端架构：ShardingJDBC
  • 代理架构：MyCat 或者 Atlas

分库分表后，id 主键唯一如何处理？

数据库自增 id

这个就是说系统里每次得到一个 id，都是往一个库的一个表里插入一条没什么业务含义的数据，然后获取一个数据库自增的一个 id。拿到这个 id 之后再往对应的分库分表里去写入。

这个方案的好处就是方便简单；缺点就是单库生成自增 id，要是高并发的话，就会有瓶颈的；如果硬是要改进一下，那么就专门开一个服务出来，这个服务每次就拿到当前 id 最大值，然后自己递增几个 id，一次性返回一批 id，然后再把当前最大 id 值修改成递增几个 id 之后的一个值；但是无论如何都是基于单个数据库。

适合的场景：分库分表就俩原因，要不就是单库并发太高，要不就是单库数据量太大；除非是并发不高，但是数据量太大导致的分库分表扩容，可以用这个方案，因为可能每秒最高并发最多就几百，那么就走单独的一个库和表生成自增主键即可。

UUID

好处就是本地生成，不要基于数据库来了；不好之处就是，UUID 太长了、占用空间大，作为主键性能太差了；更重要的是，UUID 不具有有序性，会导致 B+ 树索引在写的时候有过多的随机写操作（连续的 ID 可以产生部分顺序写），还有，由于在写的时候不能产生有顺序的 append 操作，而需要进行 insert 操作，将会读取整个 B+ 树节点到内存，在插入这条记录后会将整个节点写回磁盘，这种操作在记录占用空间比较大的情况下，性能下降明显。

适合的场景：如果你是要随机生成个什么文件名、编号之类的，可以用 UUID，但是作为主键是不能用 UUID 的。

UUID.randomUUID().toString().replace(“-”, “”) -> sfsdf23423rr234sfdaf

获取系统当前时间

这个就是获取当前时间即可，但是问题是，并发很高的时候，比如一秒并发几千，会有重复的情况，这个是肯定不合适的。基本就不用考虑了。

适合的场景：一般如果用这个方案，是将当前时间跟很多其他的业务字段拼接起来，作为一个 id，如果业务上你觉得可以接受，那么也是可以的。你可以将别的业务字段值跟当前时间拼接起来，组成一个全局唯一的编号。

snowflake 算法

snowflake 算法是 twitter 开源的分布式 id 生成算法，采用 Scala 语言实现，是把一个 64 位的 long 型的 id，1 个 bit 是不用的，用其中的 41 bit 作为毫秒数，用 10 bit 作为工作机器 id，12 bit 作为序列号。

0 | 0001100 10100010 10111110 10001001 01011100 00 | 10001 | 1 1001 | 0000 00000000
  • 1 bit：不用，因为二进制里第一个 bit 为如果是 1，那么都是负数，但是我们生成的 id 都是正数，所以第一个 bit 统一都是 0。
  • 41 bit：表示的是时间戳，单位是毫秒。41 bit 可以表示的数字多达 2^41 - 1，也就是可以标识 2^41 - 1 个毫秒值，换算成年就是表示69年的时间。
  • 10 bit：记录工作机器 id，代表的是这个服务最多可以部署在 2^10 台机器上哪，也就是 1024 台机器（因为分区最多1024个）。但是 10 bit 里 5 个 bit 代表机房 id，5 个 bit 代表机器 id。意思就是最多代表 2^5 个机房（32个机房），每个机房里可以代表 2^5 个机器（32台机器）。
  • 12 bit：这个是用来记录同一个毫秒内产生的不同 id，12 bit 可以代表的最大正整数是 2^12 - 1 = 4096，也就是说可以用这个 12 bit 代表的数字来区分同一个毫秒内的 4096 个不同的 id。
public class IdWorker {

    private long workerId;
    private long datacenterId;
    private long sequence;

    public IdWorker(long workerId, long datacenterId, long sequence) {
        // sanity check for workerId
        // 这儿不就检查了一下，要求就是你传递进来的机房id和机器id不能超过32，不能小于0
        if (workerId > maxWorkerId || workerId < 0) {
            throw new IllegalArgumentException(
                    String.format("worker Id can't be greater than %d or less than 0", maxWorkerId));
        }
        if (datacenterId > maxDatacenterId || datacenterId < 0) {
            throw new IllegalArgumentException(
                    String.format("datacenter Id can't be greater than %d or less than 0", maxDatacenterId));
        }
        System.out.printf(
                "worker starting. timestamp left shift %d, datacenter id bits %d, worker id bits %d, sequence bits %d, workerid %d",
                timestampLeftShift, datacenterIdBits, workerIdBits, sequenceBits, workerId);

        this.workerId = workerId;
        this.datacenterId = datacenterId;
        this.sequence = sequence;
    }

    private long twepoch = 1288834974657L;

    private long workerIdBits = 5L;
    private long datacenterIdBits = 5L;

    // 这个是二进制运算，就是 5 bit最多只能有31个数字，也就是说机器id最多只能是32以内
    private long maxWorkerId = -1L ^ (-1L << workerIdBits);

    // 这个是一个意思，就是 5 bit最多只能有31个数字，机房id最多只能是32以内
    private long maxDatacenterId = -1L ^ (-1L << datacenterIdBits);
    private long sequenceBits = 12L;

    private long workerIdShift = sequenceBits;
    private long datacenterIdShift = sequenceBits + workerIdBits;
    private long timestampLeftShift = sequenceBits + workerIdBits + datacenterIdBits;
    private long sequenceMask = -1L ^ (-1L << sequenceBits);

    private long lastTimestamp = -1L;

    public long getWorkerId() {
        return workerId;
    }

    public long getDatacenterId() {
        return datacenterId;
    }

    public long getTimestamp() {
        return System.currentTimeMillis();
    }

    public synchronized long nextId() {
        // 这儿就是获取当前时间戳，单位是毫秒
        long timestamp = timeGen();

        if (timestamp < lastTimestamp) {
            System.err.printf("clock is moving backwards.  Rejecting requests until %d.", lastTimestamp);
            throw new RuntimeException(String.format(
                    "Clock moved backwards.  Refusing to generate id for %d milliseconds", lastTimestamp - timestamp));
        }

        if (lastTimestamp == timestamp) {
            // 这个意思是说一个毫秒内最多只能有4096个数字
            // 无论你传递多少进来，这个位运算保证始终就是在4096这个范围内，避免你自己传递个sequence超过了4096这个范围
            sequence = (sequence + 1) & sequenceMask;
            if (sequence == 0) {
                timestamp = tilNextMillis(lastTimestamp);
            }
        } else {
            sequence = 0;
        }

        // 这儿记录一下最近一次生成id的时间戳，单位是毫秒
        lastTimestamp = timestamp;

        // 这儿就是将时间戳左移，放到 41 bit那儿；
        // 将机房 id左移放到 5 bit那儿；
        // 将机器id左移放到5 bit那儿；将序号放最后12 bit；
        // 最后拼接起来成一个 64 bit的二进制数字，转换成 10 进制就是个 long 型
        return ((timestamp - twepoch) << timestampLeftShift) | (datacenterId << datacenterIdShift)
                | (workerId << workerIdShift) | sequence;
    }

    private long tilNextMillis(long lastTimestamp) {
        long timestamp = timeGen();
        while (timestamp <= lastTimestamp) {
            timestamp = timeGen();
        }
        return timestamp;
    }

    private long timeGen() {
        return System.currentTimeMillis();
    }

    // ---------------测试---------------
    public static void main(String[] args) {
        IdWorker worker = new IdWorker(1, 1, 1);
        for (int i = 0; i < 30; i++) {
            System.out.println(worker.nextId());
        }
    }
}

就是说 41 bit 是当前毫秒单位的一个时间戳，就这意思；然后 5 bit 是你传递进来的一个机房 id（但是最大只能是 32 以内），另外 5 bit 是你传递进来的机器 id（但是最大只能是 32 以内），剩下的那个 12 bit序列号，就是如果跟你上次生成 id 的时间还在一个毫秒内，那么会把顺序给你累加，最多在 4096 个序号以内。

所以利用这个工具类，自己搞一个服务，然后对每个机房的每个机器都初始化这么一个东西，刚开始这个机房的这个机器的序号就是 0。然后每次接收到一个请求，说这个机房的这个机器要生成一个 id，你就找到对应的 Worker 生成。

利用这个 snowflake 算法，可以开发自己公司的服务，甚至对于机房 id 和机器 id，反正给你预留了 5 bit + 5 bit，你换成别的有业务含义的东西也可以的。

这个 snowflake 算法相对来说还是比较靠谱的，所以要真是搞分布式 id 生成，如果是高并发啥的，那么用这个应该性能比较好，一般每秒几万并发的场景，也足够用了。

MySQL存储引擎

通过show engines可以看到MySQL提供的所有存储引擎，MySQL默认的存储引擎是InnoDB,并且在5.7版本所有的存储引擎只有InnoDB是事务性存储引擎，也就是说只有InnoDB支持事务。

InnoDB

是 MySQL 默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其它存储引擎。

实现了四个标准的隔离级别，默认级别是可重复读（REPEATABLE READ）。在可重复读隔离级别下，通过多版本并发控制（MVCC）+ Next-Key Locking 防止幻影读。

主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。

内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。

支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。（热备份即是主从复制的过程）

MyISAM

设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它。

提供了大量的特性，包括压缩表、空间数据索引等。

不支持事务。

不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入（CONCURRENT INSERT）。

可以手工或者自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据丢失，而且修复操作是非常慢的。

如果指定了 DELAY_KEY_WRITE 选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。

在 MyISAM下，主键索引和辅助键索引都属于非聚簇索引。查询不管是走主键索引，还是非主键索引，在叶子结点得到的都是目的数据的地址，还需要通过该地址，才能在数据文件中找到目的数据。

MyISAM 和 InnoDB 区别：

MyISAM是MySQL的默认数据库引擎（5.5版之前）。虽然性能极佳，而且提供了大量的特性，包括全文索引、压缩、空间函数等，但MyISAM不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。不过，5.5版本之后，MySQL引入了InnoDB（事务性数据库引擎），MySQL 5.5版本后默认的存储引擎为InnoDB。大多数时候使用的都是InnoDB存储引擎，但是在某些情况下使用MyISAM也是合适的,比如读密集的情况下。（如果不介意MyISAM崩溃恢复问题的话）

两者的对比：

  1. MyISAM只有表级锁，而InnoDB支持行级锁和表级锁，默认为行级锁
  2. MyISAM强调的是性能，每次查询具有原子性，其执行速度比InnoDB类型更快，但是不提供事务支持。但是InnoDB提供事务支持事务，外部键等高级数据库功能。具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
  3. MyISAM不支持外键，而InnoDB支持
  4. InnoDB支持MVCC，应对高并发事务，MVCC比单纯的加锁更高效；MVCC只在READ COMMITTED和REPEATABLE READ两个隔离级别下工作。因为未提交读总是读取最新的数据行，无需使用MVCC，而可串行化需要对所有读取的行都加锁，单纯使用MCVCC无法实现。MVCC可以使用乐观锁和悲观锁来实现，各个数据库中MVCC实现并不统一。
  5. MyISAM崩溃后发生损坏的概率比InnoDB高很多，而且恢复的速度也更慢
  6. MyISAM支持压缩表和空间数据索引

InnoDB 缓冲池

InnoDB缓冲池

应用系统分层架构，为了加速数据访问，会把最常访问的数据，放在缓存(cache)里，避免每次都去访问数据库。操作系统，会有缓冲池(buffer pool)机制，避免每次访问磁盘，以加速数据的访问。MySQL 作为一个存储系统，同样具有缓冲池(buffer pool)机制，以避免每次查询数据都进行磁盘IO，主要作用：

  1. 存在的意义是加速查询

  2. 缓冲池(buffer pool) 是一种常见的降低磁盘访问的机制；

  3. 缓冲池通常以页(page 16K)为单位缓存数据；

  4. 缓冲池的常见管理算法是 LRU，memcache，OS，InnoDB 都使用了这种算法；

  5. InnoDB 对普通 LRU 进行了优化：将缓冲池分为老生代和新生代，入缓冲池的页，优先进入老生代，该页被访问，才进入新生代，以解决预读失效的问题页被访问。且在老生代停留时间超过配置阈值的，才进入新生代，以解决批量数据访问，大量热数据淘汰的问题

Innodb缓冲池新老生代

预读失效：

由于预读(Read-Ahead)，提前把页放入了缓冲池，但最终 MySQL 并没有从页中读取数据，称为预读失效

缓冲池污染：

当某一个 SQL 语句，要批量扫描大量数据时，可能导致把缓冲池的所有页都替换出去，导致大量热数据被换出，MySQL 性能急剧下降，这种情况叫缓冲池污染。

解决办法：加入老生代停留时间窗口策略后，短时间内被大量加载的页，并不会立刻插入新生代头部，而是优先淘汰那些，短期内仅仅访问了一次的页。

MVCC

概念

MVCC即多版本控制: 指的是一种提高并发的技术。最早的数据库系统，只有读读之间可以并发，读写，写读，写写都要阻塞。引入多版本之后，只有写写之间相互阻塞，其他三种操作都可以并行，这样大幅度提高了InnoDB的并发度。在内部实现中，与 Postgres 在数据行上实现多版本不同，InnoDB 是在 undolog 中实现的，通过 undolog 可以找回数据的历史版本。找回的数据历史版本可以提供给用户读(按照隔离级别的定义，有些读请求只能看到比较老的数据版本)，也可以在回滚的时候覆盖数据页上的数据。在 InnoDB 内部中，会记录一个全局的活跃读写事务数组，其主要用来判断事务的可见性。

《高性能MySQL》中对MVCC的介绍：
MySQL 的大多数事务型存储引擎实现的其实都不是简单的行级锁。基于提升并发性能的考虑, 它们一般都同时实现了多版本并发控制(MVCC)。不仅是 MySQL,包括 Oracle,PostgreSQL 等其他数据库系统也都实现了 MVCC，但各自的实现机制不尽相同,因为 MVCC 没有一个统一的实现标准。可以认为 MVCC 是行级锁的一个变种,但是它在很多情况下避免了加锁操作, 因此开销更低。虽然实现机制有所不同,但大都实现了非阻塞的读操作，写操作也只锁定必要的行。
MVCC 的实现方式有多种,典型的有乐观(optimistic)并发控制和悲观(pessimistic)并发控制。
MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作。其他两个隔离级别和 MVCC 不兼容, 因为 READ UNCOMMITTED 总是读取最新的数据行,而不是符合当前事务版本的数据行。而 SERIALIZABLE 则会对所有读取的行都加锁。

MVCC的特点

一般认为MVCC有下面几个特点：

  • 每行数据都存在一个版本，每次数据更新时都更新该版本
  • 修改时 Copy 出当前版本, 然后随意修改，各个事务之间无干扰
  • 保存时比较版本号，如果成功(commit)，则覆盖原记录,失败则放弃 copy(rollback)
  • 每行都有版本号，保存时根据版本号决定是否成功，听起来含有乐观锁的味道，因为这看起来正是，在提交的时候才能知道到底能否提交成功。

而 InnoDB 实现 MVCC 的方式是:

  • 事务以排他锁的形式修改原始数据
  • 把修改前的数据存放于 undo log，通过回滚指针与主数据关联
  • 修改成功（commit）啥都不做，失败则恢复 undo log中的数据（rollback）

Innodb 的实现算不上 MVCC, 因为并没有实现核心的多版本共存,undo log 中的内容只是串行化的结果，记录了多个事务的过程,不属于多版本共存。但理想的 MVCC 是难以实现的,当事务仅修改一行记录使用理想的 MVCC 模式是没有问题的，可以通过比较版本号进行回滚, 但当事务影响到多行数据时, 理想的 MVCC 就无能为力了。
比如, 如果事务 A 执行理想的 MVCC, 修改 Row1成功, 而修改 Row2 失败,此时需要回滚 Row1，但因为 Row1没有被锁定，其数据可能又被事务 B 所修改, 如果此时回滚 Row1 的内容，则会破坏事务 B 的修改结果，导致事务 B 违反ACID。这也正是所谓的第一类更新丢失的情况。也正是因为 InnoDB 使用的 MVCC 中结合了排他锁, 不是纯的 MVCC，所以第一类更新丢失是不会出现了，一般说更新丢失都是指第二类丢失更新。

第一类更新丢失：事务 A 的事务回滚覆盖了事务 B 已提交的结果

第二类更新丢失：事务 A 的提交覆盖了事务 B 已提交的结果

InnoDB中MVCC的实现方式

InnoDB的MVCC通过在每行记录后面保存四个隐藏的字段来实现：

  • 6字节的事务ID（DB_TRX_ID）：用来标识最近一次对本行记录做修改（insert|update）的事务的标识符，即最后一次修改（insert|update）本行记录的事务id。至于 delete 操作，在 innodb 看来也不过是一次 update 操作，更新行中的一个特殊位 FLAG ，将行表示为deleted, 并非真正删除。
  • 7字节的回滚指针（DB_ROLL_PTR）：指写入回滚段（rollback segment）的undo log record（撤销日志记录），指向这条记录的上一个版本。如果一行记录被更新，则undo log record包含“重建该行记录被更新之前内容”所必须的信息
  • 6字节的DB_ROW_ID 递增ID：包含一个随着新行插入而单调递增的行ID，当由 Innodb 自动产生聚集索引时，聚集索引会包括这个行ID的值，否则这个行ID不会出现在任何索引中。
  • FLAG：一个删除 flag 隐藏字段, 既记录被更新或删除并不代表真的删除，而是删除 flag 变了。

聚簇索引：如果表中没有主键或合适的唯一索引,也就是无法生成聚簇索引的时候, InnoDB会帮我们自动生成聚集索引,但聚簇索引会使用DB_ROW_ID的值来作为主键; 如果有自己的主键或者合适的唯一索引,那么聚簇索引中也就不会包含DB_ROW_ID了。

事务对一条记录的修改，会导致该记录的 undo log 成为一条记录版本线性表(链表)，undo log 的链首就是最新的旧记录，链尾就是最早的旧记录。

read view / snapshot

read view 叫做事务快照，也有些称为 snapshot,事务快照是用来存储数据库的事务运行情况。一个事务快照的创建过程可以概括为：

  • up_limit_id：表示该SQL启动时，当前已经提交的事务号 + 1，也就是当前系统中创建最早但还未提交的事务。事务号小于 up_limit_id ，对于当前Read View都是可见的。理解起来就是创建Read View视图的时候，之前已经提交的事务对于该事务肯定是可见的。

  • low_limit_id：表示该SQL启动时，当前事务链表中最大的事务id编号+1（即未开启的事务id=当前最大事务id+1），事务号大于等于low_limit_id，对于当前Read View都是不可见的。理解起来就是在创建 Read View 视图之后创建的事务对于该事务肯定是不可见的。

  • trx_ids：为活跃事务id列表，即 Read View 初始化时当前未提交的事务列表。所以当进行RR读的时候，trx_ids 中的事务对于本事务是不可见的（除了自身事务，自身事务对于表的修改对于自己当然是可见的）。理解起来就是创建 RV 时，将当前活跃事务 ID 记录下来，后续即使他们提交对于本事务也是不可见的。

read view 主要是用来做可见性判断的, read view 快照的生成时机不同,是造成 RC,RR 两种隔离级别的不同可见性的最主要原因。

  • 在 innodb 中(默认 repeatable read 级别), 事务在 begin/start transaction 之后的第一条 select 读操作后, 会创建一个快照(read view),将当前系统中活跃的其他事务记录记录起来，后续所有的 SELECT 都是复用这个 ReadView，其它 update, delete, insert 语句和一致性读 snapshot 的建立没有关系;
  • 在innodb中(默认read committed级别),事务中每条select语句都会创建一个快照(read view);

undo-log

Undo log 是 InnoDB MVCC 事务特性的重要组成部分。当我们对记录做了变更操作时就会产生 undo 记录，Undo 记录默认被记录到系统表空间(ibdata)中，但从 5.6 开始，也可以使用独立的 Undo 表空间。

Undo 记录中存储的是老版本数据，当一个旧的事务需要读取数据时，为了能读取到老版本的数据，需要顺着 undo链找到满足其可见性的记录。当版本链很长时，通常可以认为这是个比较耗时的操作。

undo-log

大多数对数据的变更操作包括 INSERT/DELETE/UPDATE，其中 INSERT 操作在事务提交前只对当前事务可见，因此产生的Undo日志可以在事务提交后直接删除，而对于 UPDATE/DELETE 则需要维护多版本信息，在 InnoDB 里，UPDATE 和 DELETE 操作产生的 Undo 日志被归成一类，即 update_undo

另外, 在回滚段中的 undo logs 分为：insert undo log和 update undo log

  • insert undo log：事务对 insert 新记录时产生的undolog，只在事务回滚时需要, 并且在事务提交后就可以立即丢弃。
  • update undo log：事务对记录进行 delete 和 update 操作时产生的 undo log,不仅在事务回滚时需要,一致性读也需要，所以不能随便删除，只有当数据库所使用的快照中不涉及该日志记录，对应的回滚日志才会被 purge 线程删除。

可见性比较算法

所有数据行上 DB_TRX_ID 小于 up_trx_id 的记录，说明修改该行的事务在当前事务开启之前都已经提交完成，所以对当前事务来说，都是可见的。而对于 DB_TRX_ID 大于 low_trx_id 的记录，说明修改该行记录的事务在当前事务之后，所以对于当前事务来说是不可见的。

注意，ReadView 是与 SQL 绑定的，而并不是事务，所以即使在同一个事务中，每次 SQL 启动时构造的 ReadView 的 up_trx_id 和 low_trx_id 也都是不一样的，至于 DATA_TRX_ID 大于 low_trx_id 本身出现也只有当多个 SQL 并发的时候，在一个 SQL 构造完 ReadView 之后，另外一个 SQL 修改了数据后又进行了提交，对于这种情况，数据其实是不可见的。

最后，至于位于（up_trx_id, low_trx_id）中间的事务是否可见，这个需要根据不同的事务隔离级别来确定。对于RC的事务隔离级别来说，对于事务执行过程中，已经提交的事务的数据，对当前事务是可见的，也就是说下图中，当前事务运行过程中，trx1~4中任意一个事务提交，对当前事务来说都是可见的；而对于RR隔离级别来说，事务启动时，已经开始的事务链表中的事务的所有修改都是不可见的，所以在RR级别下，low_trx_id基本保持与 up_trx_id 相同的值即可。

mvcc事务id可见性

下图是 MVCC 整体原理图：

MVCC 整体原理图

当前读和快照读

通过 MVCC 机制，虽然让数据变得可重复读，但我们读到的数据可能是历史数据，不是数据库最新的数据。这种读取历史数据的方式，我们叫它快照读 (snapshot read)，而读取数据库最新版本数据的方式，叫当前读 (current read)。

当前读

像 select lock in share mode (共享锁)、select for update 、update、insert、delete (排他锁)这些操作都是一种当前读，就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。

当前读可以认为是悲观锁的具体功能实现

快照读

不加锁的 select 就是快照读，即不加锁的非阻塞读，也就是简单的 select 操作,不包括 select .. lock in share mode,select ... for update

快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读，之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC，可以认为 MVCC 是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销。

既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。

快照读就是 MVCC 思想在 MySQL 的具体非阻塞读功能实现，MVCC 的目的就是为了实现读—写冲突不加锁，提高并发读写性能，而这个读指的就是快照读。

在RR级别下，快照读是通过 MVCC（多版本控制）和undo log来实现的，当前读是通过加record lock(记录锁)和gap lock(间隙锁)实现的。

防止部分幻读

MySQL 中 InnoDB 存储引擎默认事务隔离级别是RR,是通过行排他锁+MVCC 一起实现的，不仅可以保证可重复读，还可以部分防止幻读，而非完全防止。

原因：如果事务 B 在事务 A 执行中，insert 了一条数据并提交，事务 A 再查询的时候，虽然读取的是 undo 中旧版本数据（防止了部分幻读），但是事务 A 中执行 updata 或者 delete 都是可以成功的。因为执行 update 或者 delete使用的是当前读，此时是可以读到最新的数据的。

innodb在快照读的情况下并没有真正的避免幻读,但是在当前读的情况下避免了不可重复读和幻读。

为什么要使用索引

  • 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性
  • 可以大大加快数据的检索速度（大大减少检索的数据量），这也是创建索引的最主要原因
  • 帮助服务器避免排序和临时表
  • 将随机 IO 变为顺序 IO
  • 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义

索引这么多优点，为什么不对表中的每一个列创建一个索引呢？

  1. 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
  2. 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
  3. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

使用索引的注意事项？

  1. 在经常需要搜索的列上，可以加快搜索的速度；

  2. 在经常使用在 WHERE 子句中的列上面创建索引，加快条件的判断速度。

  3. 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

  4. 对于中到大型表索引都是非常有效的，但是特大型表的话维护开销会很大，不适合建索引

  5. 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；

  6. 避免 where 子句中对字段施加函数，这会造成无法命中索引。

  7. 在使用 InnoDB 时使用与业务无关的自增主键作为主键，即使用逻辑主键，而不要使用业务主键。

  8. 将打算加索引的列设置为 NOT NULL ，否则将导致引擎放弃使用索引而进行全表扫描。

    订正，将某一列设置为 default null，where 是可以走索引，另外索引列是否设置 null 是不影响性能的。 但是，还是不建议列上允许为空。最好限制 not null，因为 null 需要更多的存储空间并且 null 值无法参与某些运算。

    《高性能MySQL》第四章如是说：And, in case you’re wondering, allowing NULL values in the index really doesn’t impact performance 。NULL 值索引查找流程见 18 题

  9. 删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗， MySQL 5.7 可以通过查询 sys 库的 chema_unused_indexes 视图来查询哪些索引从未被使用

  10. 在使用 limit offset 查询缓慢时，可以借助索引来提高性能

索引结构原理

MySQL索引使用的数据结构主要有B+Tree索引和哈希索引。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其于大部分场景，选择B+Tree索引。

B+Tree数据结构

Tree指的是Balance Tree，也就是平衡树。平衡树是一颗查找树，并且所有叶子节点位于同一层。

B+ Tree是基于B Tree和叶子节点顺序访问指针进行实现，它具有B Tree的平衡性，并且通过顺序访问指针来提高区间查询的性能。

在B+ Tree中，一个节点中的key从左到右非递减排列，如果某个指针的左右相邻key分别是keyi和keyi+1，且不为null，则该指针指向节点的所有key大于等于keyi且小于等于keyi+1。

B+Tree数据结构

在进行查找操作时，首先在根节点进行二分查找，找到一个key所在的指针，然后递归地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出key所对应的data。

插入删除操作会破坏平衡树的平衡性，因此在插入删除操作之后，需要对树进行一个分裂、合并、旋转等操作来维护平衡性。

大多数 MySQL 存储引擎的默认索引类型都是B+Tree。

因为不再需要进行全表扫描，只需要对树进行搜索即可，所以查找速度快很多。

因为B+ Tree的有序性，所以除了用于查找，还可以用于排序和分组。

可以指定多个列作为索引列，多个索引列共同组成键。

适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于最左前缀查找。如果不是按照索引列的顺序进行查找，则无法使用索引。

MySQL的BTree索引使用的是B树中的B+Tree，但对于主要的两种存储引擎，其实现方式不同：

  • MyISAM:B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。
  • InnoDB:其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

主键索引（聚簇/集索引）：

主键索引

聚集索引的优点

聚集索引的查询速度非常的快，因为整个B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。

聚集索引的缺点

  1. 依赖于有序的数据 ：因为B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或UUID这种又长又难比较的数据，插入或查找的速度肯定比较慢。
  2. 更新代价大 ： 索引列的数据被修改时，对应的索引也将会被修改， 而且聚集索引的叶子节点还存放着数据，修改代价肯定是较大的， 所以对于主键索引来说，主键一般都是不可被修改的。

辅助索引（二级索引）：

辅助索引

非聚集索引的优点

更新代价比聚集索引要小 。非聚集索引的更新代价就没有聚集索引那么大了，非聚集索引的叶子节点是不存放数据的

非聚集索引的缺点

  1. 跟聚集索引一样，非聚集索引也依赖于有序的数据
  2. 可能会二次查询(回表) :这应该是非聚集索引最大的缺点了。当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

覆盖索引（不需要回表的情况）

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，就称之为“覆盖索引”。我们知道InnoDB存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢。覆盖索引就是把要查询出的列和索引对应，不做回表操作！

覆盖索引使用实例

现在如果创建了索引(username,age)，执行下面的 sql 语句

select username , age from user where username = 'Java' and age = 22

在查询数据的时候：要查询出的列在叶子节点都存在！所以，就不用回表

选择索引和编写利用这些索引的查询的3个原则

  1. 单行访问是很慢的。特别是在机械硬盘存储中(SSD的随机I/O要快很多，不过这一点仍然成立）。如果服务器从存储中读取一个数据块只是为了获取其中一行，那么就浪费了很多工作。最好读取的块中能包含尽可能多所需要的行。使用索引可以创建位置引，用以提升效率。
  2. 按顺序访问范围数据是很快的，这有两个原因。第一，顺序I/0不需要多次磁盘寻道，所以比随机I/O要快很多（特别是对机械硬盘）。第二，如果服务器能够按需要顺序读取数据，那么就不再需要额外的排序操作，并且GROUPBY查询也无须再做排序和将行按组进行聚合计算了。
  3. 索引覆盖查询是很快的。如果一个索引包含了查询需要的所有列，那么存储引擎就不需要再回表查找行。这避免了大量的单行访问，而上面的第1点已经写明单行访问是很慢的。

索引的原理

索引就是将无序的数据变成有序：

使用索引后

找到id为8的记录的简要步骤：

索引查找

在没有用到索引的时候，需要遍历双向链表来定位对应的页，使用索引后通过“目录”就可以很快定位到对应的页上了。

采用的是二分查找法，时间复杂度近似为O(log(n)),其底层结构是B+树。

其它类型索引

哈希索引

哈希索引能以O(1)时间进行查找，但是失去了有序性：

  • 无法用于排序和分组
  • 只支持精确查找，无法用于部分查找和范围查找

InnoDB存储引擎有一个特殊的功能叫做“自适应哈希索引”，当某个索引值被使用的非常频繁的时候，会在B+ Tree索引之上再创建一个哈希索引，这样就让B+ Tree索引具有哈希索引的一些优点，比如快速的哈希查找。

全文索引

MyISAM存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。

查找条件使用MATCH AGAINST,而不是普通的WHERE

语法：

MATCH (col1,col2,...) AGAINST (expr [search_modifier])

search_modifier:
{
    IN NATURAL LANGUAGE MODE | # 自然语言全文搜索（默认）
    IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION | # 带查询扩展的自然语言全文搜索
    IN BOOLEAN MODE | # 布尔全文搜索，该搜索模式下，待搜索单词前或后的一些特定字符会有特殊的含义。
    WITH QUERY EXPANSION # 查询扩展全文搜索
}

例子：

# title和body字段已经建立全文索引
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE);

在全文索引中，实际返回的行是按与所查找内容的相关度由高到低的顺序排列的。这个相关度的值由WHERE语句中的MATCH(...)AGAINST(...)计算所得，是一个非负浮点数。该值越大表明相应的行与所查找的内容越相关，0值表明不相关。该值基于行中的单词数、行中不重复的单词数、文本集合中总单词数以及含特定单词的行数计算得出。如果直接执行以下语句，则直接返回的就是相关度：

SELECT MATCH(title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE) as score FROM articles

注意查找内容不区分大小写

拥有查询扩展的查询模式，在查询的时候会进行两次搜索。第一次用给定的搜索内容进行搜索，第二次使用给定的搜索内容结合第一次搜索返回结果中相关性非常高的一些行进行搜索，例如搜索“database”，实际上我们期望返回结果不仅仅是仅包含“database”单词的行，一些包含“MySQL”、“SQLServer”、“Oracle”、“DB2”、“RDBMS”等的行也期望被返回。这个时候查询扩展全文搜索就能大显身手。

空间数据索引

MyISAM 存储引擎支持空间数据索引（R-Tree），可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。

必须使用GIS相关的函数来维护数据。

MySQL InnoDB 的基本存储结构

MySQL的基本存储结构是页，记录都存在页里边：

mysql存储结构

插入记录

每个数据页可以组成一个双向链表，同时每个数据页中的记录又可以组成一个单向链表。

每个数据页都会为存储在它里边的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。

以其它列（非主键）作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。所以说，如果写SELECT * FROM user WHERE indexname = 'xxx'这样没有进行任何优化的sql语句，默认会这样做：

  • 定位到记录所在的页：需要遍历双向链表，找到所在的页
  • 从所在的页内中查找相应的记录：由于不是根据主键查询，只能遍历所在页的单链表。

在数据量很大的情况下，这种查询会很慢，时间复杂度为O(n)。

InnoDB B+ Tree 索引数据结构、数据组织方式（InnoDB 一棵 B+ 树可以存放多少行数据）

一棵 B+ 树能存放大概 2000 万行数据，这和 InnoDB 索引数据结构、数据组织方式有关。

计算机在存储数据的时候，有最小存储单元，这就好比我们今天进行现金的流通最小单位是一毛。在计算机中磁盘存储数据最小单元是扇区，一个扇区的大小是 512 字节，而文件系统（例如 XFS/EXT4）的最小单元是块，一个块的大小是 4k，而对于 InnoDB 存储引擎也有自己的最小储存单元——页（Page），一个页的大小是16K。

磁盘扇区、文件系统、InnoDB存储引擎各自的最小存储单元

例如，文件系统中一个文件大小只有差不多 2kb 大小，但不得不占磁盘上4KB的空间：

文件系统存储最小单位

在 MySQL 中 InnoDB 页的大小默认是16k，所以 innodb 的所有数据文件（后缀为 ibd 的文件），它的大小始终都是16384（16k）的整数倍。当然也可以通过参数设置：

mysql> show variables like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)

数据表中的数据都是存储在页中的，假设一行数据的大小是 1k，那么一个页可以存放 16 行这样的数据。

如果数据库只按这样的方式存储，那么如何查找数据就成为一个问题，因为不知道要查找的数据存在哪个页中，也不可能把所有的页遍历一遍，那样太慢了。所以就有了用 B+ 树的方式组织这些数据。

页可以用于存放数据也可以用于存放键值+指针，在 B+ 树中叶子节点存放数据（这里的数据指的可能是实际的数据，也可能是指向主键索引的指针，看索引是聚簇索引还是非聚簇索引），非叶子节点存放键值+指针。

索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而在去数据页中查找到需要的数据。

那么问题就在于通常一棵 B+ 树可以存放多少行数据？

这里先假设 B+ 树高为 2，即存在一个根节点和若干个叶子节点，那么这棵 B+ 树的存放总记录数为：根节点指针数 * 单个叶子节点记录行数。

上面已经说明单个叶子节点（页）中的记录数 = 16K/1K = 16。（这里假设一行记录的数据大小为 1k，实际上现在很多互联网业务数据记录大小通常就是 1K 左右）。

那么现在需要计算出非叶子节点（对于高度为 2 的树就是根节点）能存放多少指针，假设主键 ID 为 bigint 类型，长度为 8 字节，而指针大小在 InnoDB 源码中设置为 6 字节，这样一共 14 字节，一个页中能存放多少这样的单元，其实就代表有多少指针，即 16384/14 = 1170。那么可以算出一棵高度为 2 的 B+ 树，能存放 1170 * 16 = 18720 条这样的数据记录。

根据同样的原理可以算出一个高度为 3 的 B+ 树可以存放：1170 * 1170 * 16 = 21902400 条这样的记录。所以在 InnoDB 中 B+ 树高度一般为 1-3 层，它就能满足千万级的数据存储。在查找数据时一次页的查找代表一次 IO，所以通过主键索引查询通常只需要 1-3 次 IO 操作即可查找到数据。

查看 InnoDB 中主键索引 B+ 树的高度

在 InnoDB 的表空间文件中，约定 page number 为 3 的代表主键索引的根页，而在根页偏移量为 64 的地方存放了该 B+ 树的 page level。如果 page level为 1，树高为 2，page level 为2，则树高为 3。即 B+ 树的高度=page level + 1；

在实际操作中，可以通过 InnoDB 元数据表确认主键索引根页的 page number 为 3：

SELECT
b.name, a.name, index_id, type, a.space, a.PAGE_NO
FROM
information_schema.INNODB_SYS_INDEXES a,
information_schema.INNODB_SYS_TABLES b
WHERE
a.table_id = b.table_id AND a.space <> 0;

执行结果：

通过源数据表查索引结构

可以看出数据库 education 下的 admin_menu 表、admin_operation_log 表、admin_permissions 表主键索引根页的 page number 均为 3，而其他的二级索引 page number 为 4。

因为主键索引 B+ 树的根页在整个表空间文件中的第 3 个页开始，所以可以算出它在文件中的偏移量：16384 * 3 = 49152（16384为页大小）。

另外根据《InnoDB存储引擎》中描述在根页的 64 偏移量位置的前 2 个字节，保存了 page level 的值，因此 page level 的值在整个文件中的偏移量为：16384 * 3 + 64 = 49152 + 64 = 49216，page level 的值就在这个偏移量的前 2 个字节中。

另外，如果表的数据行数为 600多万，则B+ 树高度为 3，如果表数据行数只有15万，B+ 树高度也为 3。可以看出尽管数据量差异较大，这两个表树的高度都是 3，换句话说这两个表通过索引查询效率并没有太大差异，因为都只需要做 3 次 IO。那么如果有一张表行数是一千万，那么他的 B+ 树高度依旧是 3，查询效率仍然不会相差太大。

MySQL 索引为什么使用 B+ Tree

普通平衡树的缺点：

  • 数据量不大时，普通平衡树（AVL树，红黑树）性能极好。但是数据量巨大时，内存不够用，无法将数据全部加载到内存中，只能放到磁盘
  • 树的高度为LogN，导致磁盘IO次数过多影响效率
  • 调整树的平衡是通过旋转实现，如果不把全部数据加载进内存是无法完成旋转的

B-树的缺点：

  • 非叶子节点也存储数据，每次磁盘io数据量是固定的，每一层索引范围小
  • 数据分散在每个节点中，不支持范围查询

B+树的特有性质：

  • 非叶子节点只存储key，每一层能索引的数据更多。每次io能看到更多数据
  • 树高度低（一般为3层左右），io次数少
  • 叶子节点两两相连，符合磁盘预读特性，减少io次数
  • 范围查询支持良好。真正数据只存储在叶子节点，范围查询只需遍历叶子节点
  • 每个节点的大小设置为磁盘IO一次的大小（称为页，根据操作系统不同而定，如16k）
  • B+树中更有利于对数据扫描，可以避免B树的回溯扫描

B+ Tree 索引与红黑树比较：

红黑树等平衡数也可以用来实现索引，但是文件系统以及数据库系统普遍采用B+ Tree作为索引结构，主要有以下两个原因：

  • 更少的查找次数：平衡树查找操作的时间复杂度和树高 h 相关，O(h) = O(logdN),其中d为每个节点的出度。（出度指的是当前节点中叶子结点的个数）
    红黑树的出度为2，而B+ Tree的出度一般都非常大，所以红黑树的树高h很明显比B+ Tree大非常多，查找的次数也就更多。
  • 利用磁盘预读特性：为了减少磁盘I/O操作，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，并且只需要很短的磁盘旋转时间，速度会非常快。
    操作系统一般将内存和磁盘分割成固定大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次I/O就能完全载入一个节点。并且可以利用预读特性，相邻的节点也能够被预先载入。

Mysql的索引为什么使用B+树而不使用跳表？

B+树是多叉树结构，每个结点都是一个16k的数据页，能存放较多索引信息，所以扇出很高。三层左右就可以存储2kw左右的数据。也就是说查询一次数据，如果这些数据页都在磁盘里，那么最多需要查询三次磁盘IO。

跳表是链表结构，一条数据一个结点，如果最底层要存放2kw数据，且每次查询都要能达到二分查找的效果，2kw大概在2的24次方左右，所以，跳表大概高度在24层左右。最坏情况下，这24层数据会分散在不同的数据页里，也即是查一次数据会经历24次磁盘IO。

因此存放同样量级的数据，B+树的高度比跳表的要少，如果放在mysql数据库上来说，就是磁盘IO次数更少，因此B+树查询更快。

而针对写操作，B+树需要拆分合并索引数据页，跳表则独立插入，并根据随机函数确定层数，没有旋转和维持平衡的开销，因此跳表的写入性能会比B+树要好。

其实，mysql的存储引擎是可以换的，以前是myisam，后来才有的innodb，它们底层索引用的都是B+树。也就是说，你完全可以造一个索引为跳表的存储引擎装到mysql里。事实上，facebook造了个rocksDB的存储引擎，里面就用了跳表。直接说结论，它的写入性能确实是比innodb要好，但读性能确实比innodb要差不少。

什么是冗余，什么是冗余索引

同一信息的重复储存，叫做冗余

  • 低级冗余：字段的重复
  • 高级冗余：字段的派生：比如总额=单价*数量

形成原因：

  • 表重复
  • 属性重复
  • 元组重复

冗余的坏处：

  • 为了保证数据一致性，要维护冗余字段的成本高
  • 可能导致数据不一致

冗余索引指的是索引功能相同，能够命中就肯定能命中，那么就是冗余索引，如(name,city)和(name)这两个索引就是冗余索引，能够命中后者的查询肯定能够命中前者。

MySQL5.7之后，可以通过查询sys库中的schemal_redundant_indexes表来查看冗余索引。

MySQL如何为表字段添加索引

  1. 添加PRIMARY KEY(主键索引)
ALTER TABLE 'table_name' ADD PRIMARY KEY ('column');
  1. 添加UNIQUE(唯一索引)
ALTER TABLE 'table_name' ADD UNIQUE ('column');
  1. 添加INDEX(普通索引)
ALTER TABLE 'table_name' ADD INDEX index_name ('column');
  1. 添加FULLTEXT(全文索引)
ALTER TABLE 'table_name' ADD FULLTEXT ('column');
  1. 添加多列索引
ALTER TABLE 'table_name' ADD INDEX index_name ('column1','column2','column3');

什么是聚簇索引和非聚簇索引？

聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。

聚簇索引，实际存储的循序结构与数据存储的物理机构是一致的，所以通常来说物理顺序结构只有一种，那么一个表的聚簇索引也只能有一个，通常默认都是主键，设置了主键，系统默认就加上了聚簇索引。总而言之，聚簇索引是顺序结构与数据存储物理结构一致的一种索引，并且一个表的聚簇索引只能有唯一的一条。

非聚簇索引记录的物理顺序与逻辑顺序没有必然的联系，与数据的存储物理结构没有关系；一个表对应的非聚簇索引可以有多条，根据不同列的约束可以建立不同要求的非聚簇索引；

MyISAM中 B+Tree 叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。（MyISAM 存储引擎中 B+Tree 索引的实现）

InnoDB 的 B+ Tree 中其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。（InnoDB 存储引擎中 B+ Tree 索引的实现）

索引失效的情况

  1. 最左原则失效

    1. 模糊搜索，左模糊或全模糊都会导致索引失效，比如 '%a'和 '%a%'。但是右模糊是可以利用索引的，比如 'a%' 。

  2. 如果查询时两个表关联列类型不一致会自动进行数据类型隐式转换，会造成列上的索引失效，导致查询效率降低。如：

    SELECT name,phone FROM customer WHERE id = '111';

  3. 当语句中带有or的时候，比如：

    select * from t where name=‘sw’ or age=14

  4. 不同的字符集进行比较前需要进行转换会造成索引失效

  5. not in 也通常会使索引失效（看需要扫描的数据量成本）。

  6. 对字段进行了函数操作

  7. 对字段的做了运算，如:SELECT * FROM t WHRER c-1 = 1000;

  8. 使用索引的成本比走全表扫描的成本更高（由 MySQL 优化器决定），例如捞全表超过 50% 的数据；

NULL 值索引查找流程

注意下面这个说法是不正确的！

MySQL的WHERE子句中包含 IS NULL、IS NOT NULL、!= 这些条件时便不能使用索引查询，只能使用全表扫描。

假如有个表s1，结构如下：

CREATE TABLE s1 (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 VARCHAR(100),
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    KEY idx_key2 (key2),
    KEY idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

这个表里有10000条记录：

mysql> SELECT COUNT(*) FROM s1;
+----------+
| COUNT(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.00 sec)

下边直接贴几个图：

null值字段走索引查询1

null值字段走索引查询2

null值字段走索引查询3

上边几个查询语句的WHERE子句中用了IS NULL、IS NOT NULL、!=这些条件，但是从它们的执行计划中可以看出来，这些语句都采用了相应的二级索引执行查询，而不是使用所谓的全表扫描。

NULL值是怎么在记录中存储的

在MySQL中，每一条记录都有它固定的格式，以InnoDB存储引擎的Compact行格式为例，来看一下NULL值是怎样存储的。在Compact行格式下，一条记录是由下边这几个部分构成的：

null值在记录中的存储

新建一个称之为record_format_demo的表：

CREATE TABLE record_format_demo (
     c1 VARCHAR(10),
     c2 VARCHAR(10) NOT NULL,
     c3 CHAR(10),
     c4 VARCHAR(10)
 ) CHARSET=ascii ROW_FORMAT=COMPACT;

这里重点是NULL值是如何存储在记录中的，所以重点看一下行格式的NULL值列表部分。存储NULL值的过程如下：

  1. 首先统计表中允许存储NULL的列有哪些。

    前边说过，主键列、被NOT NULL修饰的列都是不可以存储NULL值的，所以在统计的时候不会把这些列算进去。比方说表record_format_demo的3个列c1、c3、c4都是允许存储NULL值的，而c2列是被NOT NULL修饰，不允许存储NULL值。

  2. 如果表中没有允许存储NULL的列，则NULL值列表也不存在了，否则将每个允许存储NULL的列对应一个二进制位，二进制位按照列的顺序逆序排列，二进制位表示的意义如下：

    • 二进制位的值为1时，代表该列的值为NULL。
    • 二进制位的值为0时，代表该列的值不为NULL。

    因为表record_format_demo有3个值允许为NULL的列，所以这3个列和二进制位的对应关系就是这样：

    null列与二进制位对应关系

    这里需要强调，二进制位按照列的顺序逆序排列，所以第一个列c1和最后一个二进制位对应。

  3. 设计InnoDB的大叔规定NULL值列表必须用整数个字节的位表示，如果使用的二进制位个数不是整数个字节，则在字节的高位补0。

    表record_format_demo只有3个值允许为NULL的列，对应3个二进制位，不足一个字节，所以在字节的高位补0，效果就是这样：

    null列与二进制位对应关系2

    以此类推，如果一个表中有9个允许为NULL，那这个记录的NULL值列表部分就需要2个字节来表示了。

假设现在向record_format_demo表中插入一条记录：

INSERT INTO record_format_demo(c1, c2, c3, c4)
    VALUES('eeee', 'fff', NULL, NULL);

这条记录的c1、c3、c4这3个列中c3和c4的值都为NULL，所以这3个列对应的二进制位的情况就是：

null列与二进制位对应关系3

所以这记录的NULL值列表用十六进制表示就是：0x06。

键值为NULL的记录是怎么在B+树中存放的

对于InnoDB存储引擎来说，记录都是存储在页面中的（一个页面默认是16KB大小），这些页面可以作为B+树的节点而组成一个索引，

按规定，一条记录的主键值不允许存储NULL值，所以下边语句中的WHERE子句结果肯定为FALSE：

SELECT * FROM tbl_name WHERE primary_key IS NULL;

像这样的语句优化器自己就能判定出WHERE子句必定为NULL，所以压根儿不会去执行它，看下图（Extra信息提示WHERE子句不成立）：

where子句必定为null优化情况

对于二级索引来说，索引列的值可能为NULL。那对于索引列值为NULL的二级索引记录来说，它们被放在B+树的最左边。比方说有如下查询语句：

SELECT * FROM s1 WHERE key1 IS NULL;

那它的查询示意图就如下所示：

null值索引查询流程

从图中可以看出，对于s1表的二级索引idx_key1来说，值为NULL的二级索引记录都被放在了B+树的最左边，这是因为设计InnoDB的大叔有这样的规定：

We define the SQL null to be the smallest possible value of a field.

也就是说他们把SQL中的NULL值认为是列中最小的值。

在通过二级索引idx_key1对应的B+树快速定位到叶子节点中符合条件的最左边的那条记录后，也就是本例中id值为521的那条记录之后，就可以顺着每条记录都有的next_record属性沿着由记录组成的单向链表去获取记录了，直到某条记录的key1列不为NULL。

小贴士： 通过B+树快速定位到叶子节点的记录的过程是靠一个所谓的页目录（Page Directory）做到的

使不使用索引的依据到底是什么？

那既然IS NULL、IS NOT NULL、!=这些条件都可能使用到索引，那到底什么时候索引，什么时候采用全表扫描呢？

答案很简单：成本。当然，关于如何定量的计算使用某个索引执行查询的成本比较复杂，在这里只定性的分析一下。对于使用二级索引进行查询来说，成本组成主要有两个方面：

  • 读取二级索引记录的成本
  • 将二级索引记录执行回表操作，也就是到聚簇索引中找到完整的用户记录的操作所付出的成本。

很显然，要扫描的二级索引记录条数越多，那么需要执行的回表操作的次数也就越多，达到了某个比例时，使用二级索引执行查询的成本也就超过了全表扫描的成本（举一个极端的例子，比方说要扫描的全部的二级索引记录，那就要对每条记录执行一遍回表操作，自然不如直接扫描聚簇索引来的快）。

所以MySQL优化器在真正执行查询之前，对于每个可能使用到的索引来说，都会预先计算一下需要扫描的二级索引记录的数量，比方说对于下边这个查询：

SELECT * FROM s1 WHERE key1 IS NULL;

优化器会分析出此查询只需要查找key1值为NULL的记录，然后访问一下二级索引idx_key1，看一下值为NULL的记录有多少（如果符合条件的二级索引记录数量较少，那么统计结果是精确的，如果太多的话，会采用一定的手段计算一个模糊的值，当然算法也比较麻烦），这种在查询真正执行前优化器就率先访问索引来计算需要扫描的索引记录数量的方式称之为index dive。当然，对于某些查询，比方说WHERE子句中有IN条件，并且IN条件中包含许多参数的话，比方说这样：

SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c', ... , 'zzzzzzz');

这样的话需要统计的key1值所在的区间就太多了，这样就不能采用index dive的方式去真正的访问二级索引idx_key1，而是需要采用之前在背地里产生的一些统计数据去估算匹配的二级索引记录有多少条（很显然根据统计数据去估算记录条数比index dive的方式精确性差了很多）。

反正不论采用index dive还是依据统计数据估算，最终要得到一个需要扫描的二级索引记录条数，如果这个条数占整个记录条数的比例特别大，那么就趋向于使用全表扫描执行查询，否则趋向于使用这个索引执行查询。

理解了这个也就好理解为什么在WHERE子句中出现IS NULL、IS NOT NULL、!=这些条件仍然可以使用索引，本质上都是优化器去计算一下对应的二级索引数量占所有记录数量的比值而已。

可以看到，MySQL中决定使不使用某个索引执行查询的依据很简单：就是成本够不够小。而不是是否在WHERE子句中用了IS NULL、IS NOT NULL、!=这些条件。

一条SQL语句执行很慢的原因

需要分两种情况讨论：

  1. 大多数情况是正常的，只是偶尔会出现很慢的情况
  2. 在数据量不变的情况下，这条SQL语句一直以来都执行的很慢

偶尔很慢的情况

在这种情况下，这条SQL语句本身是没什么问题的，而是其它原因导致的，主要有以下几个原因：

1. 数据库在刷新脏页

当要往数据库插入一条数据、或者更新一条数据的时候，数据库会在内存中把对应的字段数据更新，但是更新之后，这些更新的字段并不会马上同步持久化到磁盘中去，而是把这些更新的记录写入到redo log日记中去，等到空闲的时候，再通过redo log里的日记把最新的数据同步到磁盘中去。

当内存数据页跟磁盘数据页内容不一致的时候，称这个内存页为“脏页”。当内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”

以下4中场景会出现刷脏页的情况：

  • redolog写满了：redo log里的容量是有限的，如果数据库一直很忙，更新又很频繁，这个时候redo log很快就会被写满，这个时候就没办法等到空闲的时候再把数据同步到磁盘，只能暂停其他操作，全身心来把数据同步到磁盘中去，而这个时候，就会导致平时正常的SQL语句突然执行的很慢，所以说，数据库在同步数据到磁盘的时候，就有可能导致SQL语句执行的很慢了。
  • 内存不够用了：如果一次查询较多的数据，恰好碰到所查数据页不在内存中，需要申请内存，而此时恰好内存不足的时候就需要淘汰一部分内存数据页，如果是干净页，就直接释放，如果恰好是脏页就需要刷脏页。
  • MySQL认为系统“空闲”的时候
  • MySQL正常关闭的时候：这个时候，MysQL会把内存的脏页都flush到磁盘上，这样下次MySQL启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

2. 拿不到锁

要执行的这条语句涉及到的表，刚好有其他进程在用，并且加锁了，这时候该线程拿不到锁，只能慢慢等待释放锁。或者，表没有加锁，但是使用到的某一行被加锁了，这个时候也会造成阻塞。

如果要判断是否真的在等待锁，可以用show processlist这个命令来查看当前的状态。

针对一直都很慢的情况

如果在数据量一样大的情况下，这条SQL语句每次执行都很慢，就有可能是SQL语句写的有问题。有可能有以下问题：

例如有以下表：

CREATE TABLE 't' (
 'id' int(11) NOT NULL,
 'c' int(11) DEFAULT NULL,
 'd' int(11) DEFAULT NULL,
 PRIMARY KEY('id')
) ENGINE=InnoDB;

1. 没有用到索引

例如执行以下查询语句：

SELECT * FROM t WHERE 100<c AND c<100000;

字段没有索引

这个时候因为c字段上没有索引，所以只能走全表扫描，这就会导致这条语句执行很慢。

字段有索引但是没有用到

这时候如果给c加上了索引，然后执行以下查询：

SELECT * FROM t WHRER c-1 = 1000;

这时，由于在字段的左边做了运算，在查询的时候，就不会用上索引。正确的查询应该如下：

SELECT * FROM t WHERE c = 1000+1;

函数操作导致没有用上索引

如果在查询的时候，对字段进行了函数操作，也是会导致没有用上索引，如：

SELECT * FROM t WHERE pow(c,2)=1000;

2. 数据库自己选错了索引

主键索引和非主键索引是有区别的，主键索引存放的值是整行字段的数据，而非主键索引上存放的是主键字段的值。

例如下面的查询语句：

SELECT * FROM t WHERE 100<c AND c>10000;

如果走c这个字段的索引的话，最后会查询到对应主键的值，然后再根据主键的值走主键索引，查询到整行数据返回。

但是，就算c字段上有索引，系统也并不一定会走c这个字段上的索引，而是有可能会直接扫描全表，找出所有符合100<c AND c<1000的数据。

原因：

系统在执行这条语句的时候，会进行预测：究竟是走c索引扫描的行数少，还是直接扫描全表的扫描行行数少，显然，扫描行数越少越好，因为这意味着IO操作的次数越少。

如果进行全表扫描的话，扫描的次数就是这个表的总行数n，如果走索引扫描的话，通过索引c找到主键后，还要再通过主键索引来找整行的数据，也就是说需要走两次索引，而且，我们也不知道100<c AND c<10000这个条件的数据多少行，如果整个表的全部数据都符合，那么这时候如果走c索引不仅要扫面行数n，还要每行数据走两次索引。

系统的预测主要是通过索引的区分度来判断(区分度=列中不同值的数量/列的总行数)，一个索引上不同的值越多，意味着出现相同数值的索引越少，意味着索引的区分度越高。索引的区分度又叫基数，即区分度越高，基数越大，意味着符合100<c AND c>10000这个条件的行数越少。

所以，一个索引的基数越大，意味着走索引查询越有优势。

系统并不会遍历全部来获得一个索引的基数，其代价太大，索引系统是通过遍历部分数据，也就是通过采样的方式来预测索引的基数的，采样就有可能出现失误的情况，也即是说，c这个索引的基数有可能是很大的，但是采样的时候，把这个索引的基数预测成很小，系统就不走c索引了，而直接走全部扫描。

也就是说，由于统计的失误，有可能导致系统没有走索引而是走了全表扫描

系统判断是否走索引，扫描行数的预测其实只是原因之一，这条查询语句是否需要使用使用临时表、是否需要排序等也是会影响系统的选择的。

可以通过强制走索引的方式来查询：

SELECT * FROM t FORCE INDEX(a) WHERE 100<c AND c<10000;

也可以通过：

SHOW INDEX FROM t;

来查询索引的基数和实际是否符合，如果和实际很不符合，可以重新来统计索引的基数：

ANALYZE TABLE t;

既然会预测错索引的基数，也就意味着，当我们查询语句有多个索引的时候，系统可能会选错索引。

什么是最左前缀原则

在 mysql 建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，示例：
对列col1、列col2和列col3建一个联合索引

KEY test_col1_col2_col3 on test(col1,col2,col3);

联合索引 test_col1_col2_col3 实际上相当于建立了(col1)、(col1,col2)、(col1,col2,col3)三个索引。

SELECT * FROM test WHERE col1=“1” AND clo2=“2” AND clo4=“4”

上面这个查询语句执行时会依照最左前缀匹配原则，检索时会使用索引(col1,col2)进行数据匹配。

MySQL中索引可以以一定顺序引用多列，这种索引叫做联合索引。如User表的name和city加联合索引就是(name,city)。而最左前缀原则指的是：如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。如以下例子：

SELECT * FROM user WHERE name = xx AND city = xx; //可以命中索引
SELECT * FROM user WHERE name = xx; //可以命中索引
SELECT * FROM user WHERE city = xx; //无法命中索引

需要注意的是，查询的时候如果两个条件都用上了，但是顺序不同，如city == xx AND name = xx,那么查询的时候会自动优化为匹配联合索引的顺序，能够命中索引。

由于最左前缀原则，在创建联合索引的时候，索引字段的顺序需要考虑字段值去重之后的个数，区分度较高的放前面。ORDERBY语句也遵循此规则。

为什么要使用联合索引

  • 减少开销。建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！
  • 覆盖索引。对联合索引(col1,col2,col3)，如果有如下的 sql: select col1,col2,col3 from test where col1=1 and col2=2。那么 MySQL 可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机 io 操作。减少 io 操作，特别的随机 io 其实是 dba 主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。
  • 效率高。索引列越多，通过索引筛选出的数据越少。有 1000W 条数据的表，有如下 sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出 10% 的数据，如果只有单值索引，那么通过该索引能筛选出 1000W x 10%=100w 条数据，然后再回表从 100w 条数据中找到符合 col2=2 and col3= 3 的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出 1000w10% 10% *10%=1w，效率提升可想而知。

联合索引失效

对于联合索引(col1,col2,col3)，查询语句 SELECT * FROM test WHERE col2=2;是否能够触发索引？

实际上是会触发索引的，原因：

使用 explain 进行性能分析：

EXPLAIN SELECT * FROM test WHERE col2=2;
EXPLAIN SELECT * FROM test WHERE col1=1;

观察上述两个 explain 结果中的 type 字段。查询中分别是：

type: index
type: ref
  • index：这种类型表示 mysql 会对整个该索引进行扫描。要想用到这种类型的索引，对这个索引并无特别要求，只要是索引，或者某个联合索引的一部分，mysql 都可能会采用 index 类型的方式扫描。但是呢，缺点是效率不高，mysql 会从索引中的第一个数据一个个的查找到最后一个数据，直到找到符合判断条件的某个索引。所以，上述语句会触发索引。但是，只遍历索引树，通常比All快。因为，索引文件通常比数据文件小，也就是虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘读的。
  • ref：这种类型表示 mysql 会根据特定的算法快速查找到某个符合条件的索引，而不是会对索引中每一个数据都进行一一的扫描判断，也就是所谓平常理解的使用索引查询会更快的取出数据。而要想实现这种查找，索引却是有要求的，要实现这种能快速查找的算法，索引就要满足特定的数据结构。简单说，也就是索引字段的数据必须是有序的，才能实现这种类型的查找，才能利用到索引。

最左前缀原则原因

b+ 树的数据项是复合的数据结构，比如 (name,age,sex) 的时候，b+ 树是按照从左到右的顺序来建立搜索树的，比如当 (张三,20,F) 这样的数据来检索的时候，b+ 树会优先比较 name 来确定下一步的所搜方向，如果 name 相同再依次比较 age 和 sex，最后得到检索的数据；但当 (20,F) 这样的没有 name 的数据来的时候，b+ 树就不知道第一步该查哪个节点，因为建立搜索树的时候 name 就是第一个比较因子，必须要先根据 name 来搜索才能知道下一步去哪里查询

比如当 (张三, F) 这样的数据来检索时，b+ 树可以用 name 来指定搜索方向，但下一个字段 age 的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是 F 的数据了， 这个是非常重要的性质，即索引的最左匹配特性。（这种情况无法用到联合索引）

索引下推

索引下推（INDEX CONDITION PUSHDOWN，简称 ICP）是 MySQL 5.6 发布后针对扫描二级索引的一项优化改进。

总的来说是通过把索引过滤条件下推到存储引擎，来减少 MySQL 存储引擎访问基表的次数以及 MySQL 服务层访问存储引擎的次数。

ICP 适用于 MYISAM 和 INNODB。

索引下推主要跟 MySQL 的服务层和存储引擎层相关：

  • MySQL 服务层：也就是 SERVER 层，用来解析 SQL 的语法、语义、生成查询计划、接管从 MySQL 存储引擎层上推的数据进行二次过滤等等。
  • MySQL 存储引擎层：按照 MySQL 服务层下发的请求，通过索引或者全表扫描等方式把数据上传到 MySQL 服务层。
  • MySQL 索引扫描：根据指定索引过滤条件（比如 where id = 1) ，遍历索引找到索引键对应的主键值后回表过滤剩余过滤条件。
  • MySQL 索引过滤：通过索引扫描并且基于索引进行二次条件过滤后再回表。

ICP 就是把以上索引扫描和索引过滤合并在一起处理，过滤后的记录数据下推到存储引擎后的一种索引优化策略。

在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件 。

在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。

这样做的优点如下：

1.减少了回表的操作次数。

2.减少了上传到 MySQL SERVER 层的数据。

ICP 默认开启，可通过优化器开关参数关闭 ICP：optimizer_switch='index_condition_pushdown=off'或者是在 SQL 层面通过 HINT 来关闭。

具体例子

不使用 ICP的过程

MySQL 存储引擎层只把满足索引键值对应的整行表记录一条一条取出，并且上传给 MySQL 服务层。

MySQL 服务层对接收到的数据，使用 SQL 语句后面的 where 条件过滤，直到处理完最后一行记录，再一起返回给客户端。

mysql 索引下推-1

由于这里 r2 为模糊查询，会导致索引失效，此时存储引擎只通过 r1 进行查询，之后再将查询出来的数据（主键id）返回给服务层，服务层根据剩余条件进行回表过滤（查聚簇索引）。

使用 ICP 的过程

MySQL 存储引擎层，先根据过滤条件中包含的索引键确定索引记区间，再在这个区间的记录上使用包含索引键的其他过滤条件进行过滤，之后规避掉不满足的索引记录，只根据满足条件的索引记录回表取回数据上传到 MySQL 服务层。

MySQL 服务层对接收到的数据，使用 where 子句中不包含索引列的过滤条件做最后的过滤，然后返回数据给客户端。

mysql 索引下推-2

数据库命名规范

  1. 所有数据库对象名称必须使用小写字母并用下划线分割
  2. 所有数据库对象名称禁止使用MySQL保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来）
  3. 数据库对象的命名要能做到见名识意，并且最后不要超过 32 个字符
  4. 临时库表必须以tmp_为前缀并以日期为后缀，备份表必须以bak_为前缀并以**日期(时间戳)**为后缀
  5. 所有存储相同数据的列名和列类型必须一致（一般作为关联列，如果查询时关联列类型不一致会自动进行数据类型隐式转换，会造成列上的索引失效，导致查询效率降低）

数据库基本设计规范和优化

所有表必须使用Innodb存储引擎

没有特殊要求（即Innodb无法满足的功能如：列存储，存储空间数据等）的情况下，所有表必须使用Innodb存储引擎（MySQL5.5之前默认使用MyIsam，5.6以后默认的为Innodb）。

Innodb支持事务，支持行级锁，更好的恢复性，高并发下性能更好。

数据库和表的字符集统一使用UTF-8

兼容性更好，统一字符集可以避免由于字符集转换产生的乱码，不同的字符集进行比较前需要进行转换会造成索引失效，如果数据库中有存储emoji表情的需要，字符集需要采用utf8mb4字符集

所有表和字段都需要添加注释

使用comment从句添加表和列的备注，从一开始就进行数据字典的维护

尽量控制单表数据量的大小,建议控制在 500 万以内。

500 万并不是 MySQL 数据库的限制，过大会造成修改表结构，备份，恢复都会有很大的问题。

可以用历史数据归档（应用于日志数据），分库分表（应用于业务数据）等手段来控制数据量大小

谨慎使用 MySQL 分区表

分区表在物理上表现为多个文件，在逻辑上表现为一个表；

谨慎选择分区键，跨分区查询效率可能更低；

建议采用物理分表的方式管理大数据。

尽量做到冷热数据分离,减小表的宽度

MySQL 限制每个表最多存储 4096 列，并且每一行数据的大小不能超过 65535 字节。

减少磁盘 IO,保证热数据的内存缓存命中率（表越宽，把表装载进内存缓冲池时所占用的内存也就越大,也会消耗更多的 IO）；

更有效的利用缓存，避免读入无用的冷数据；

经常一起使用的列放到一个表中（避免更多的关联操作）。

禁止在表中建立预留字段

预留字段的命名很难做到见名识义。

预留字段无法确认存储的数据类型，所以无法选择合适的类型。

对预留字段类型的修改，会对表进行锁定。

禁止在数据库中存储图片,文件等大的二进制数据

通常文件很大，会短时间内造成数据量快速增长，数据库进行数据库读取时，通常会进行大量的随机 IO 操作，文件很大时，IO 操作很耗时。

通常存储于文件服务器，数据库只存储文件地址信息

禁止在线上做数据库压力测试

禁止从开发环境,测试环境直接连接生产环境数据库

优化

  • 索引优化：避免索引失效，遵循相关设计规范
  • 参数优化:
innodb_buffer_size            缓冲区大小
innodb_buffer_pool_instance   缓冲池实例个数
innodb_old_blocks_pct         读取的页放入缓冲区LRU的位置，默认37%
innodb_old_blocks_time        读取的页等待多久才放入LRU
innodb_log_buffer_size        undo日志缓冲区大小，默认8M
innodb_page_size              每一页的大小
max_connections               最大连接数
key_buffer_size
innodb_thread_concurrency     最大并发线程数
thread_cache_size             缓存的最大线程数
tmp_table_size                超过该值的用硬盘临时表，低于改值的直接放内存
query_cache_limit             超过此大小的查询将不缓存
query_cache_min_res_unit      缓存块的最小大小
query_cache_size              查询缓存大小
innodb_log_buffer_size        日志缓冲大小
slow_query_log = ON           开启慢查询
long_query_time = 3           超过3s的为慢查询
innodb_flush_log_at_trx_commit重做日志从缓冲刷新到磁盘的策略：0表示不记录redo日志
  • 主从优化：通过配置主库和从库，主库负责读取删改，从库负责只读，做到读写分离，并根据读写要求的不同配置不同的系统参数
  • 分库分表：对大表进行优化

数据库字段设计规范

优先选择符合存储需要的最小的数据类型

原因：列的字段越大，建立索引时所需要的空间也就越大，这样一页中所能存储的索引节点的数量也就越少，在遍历时所需要的 IO 次数也就越多，索引的性能也就越差。

方法：

将字符串转换成数字类型存储,如:将 IP 地址转换成整形数据

MySQL 提供了两个方法来处理 ip 地址

  • inet_aton: 把 ip 转为无符号整型 (4-8 位)
  • inet_ntoa: 把整型的 ip 转为地址

插入数据前，先用inet_aton把 ip 地址转为整型，可以节省空间，显示数据时，使用inet_ntoa把整型的 ip 地址转为地址显示即可。

对于非负型的数据 (如自增 ID,整型 IP) 来说,要优先使用无符号整型来存储

原因：无符号相对于有符号可以多出一倍的存储空间

SIGNED INT -2147483648~2147483647
UNSIGNED INT 0~4294967295

VARCHAR(N) 中的 N 代表的是字符数，而不是字节数，使用 UTF8 存储 255 个汉字Varchar(255)=765个字节。过大的长度会消耗更多的内存。

避免使用TEXT,BLOB数据类型，最常见的TEXT类型可以存储64k的数据

建议把 BLOB 或是 TEXT 列分离到单独的扩展表中

MySQL 内存临时表不支持TEXT、BLOB这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行。而且对于这种数据，MySQL 还是要进行二次查询，会使 sql 性能变得很差，但是不是说一定不能使用这样的数据类型。

如果一定要使用，建议把BLOB或是TEXT列分离到单独的扩展表中，查询时一定不要使用select *而只需要取出必要的列，不需要TEXT列的数据时不要对该列进行查询。

TEXT 或 BLOB 类型只能使用前缀索引

因为MySQL 对索引字段长度是有限制的，所以 TEXT 类型只能使用前缀索引，并且 TEXT 列上是不能有默认值的。

前缀索引：使用字符串的前几个字符作为索引，例如以下语句： 

ALTER TABLE 'table_name' ADD INDEX 'index_name' ('column'(8)) USING BTREE;

前缀索引缺点：MySQL中无法使用前缀索引进行ORDER BY和GROUP BY，也无法用来进行覆盖扫描

避免使用ENUM类型

修改 ENUM 值需要使用ALTER语句

ENUM 类型的ORDER BY操作效率低，需要额外操作

禁止使用数值作为 ENUM 的枚举值

尽可能把所有列定义为NOT NULL

索引 NULL 列需要额外的空间保存，所以要占用更多的空间。

进行比较和计算时对 NULL 值要做特别的处理

使用 TIMESTAMP（4个字节）或 DATETIME类型（8个字节）存储时间

TIMESTAMP 存储的时间范围是1970-01-01 00:00:01 ~ 2038-01-19 03:14:07

TIMESTAMP 占用4字节与 INT 相同，但比 INT 可读性高

超出 TIMESTAMP 取值范围的使用 DATETIME 存储数据

MySQL 提供了FROM_UNIXTIME()函数把 UNIX 时间戳转换为日期，并提供了UNIX_TIMESTAMP() 函数把日期转换为 UNIX 时间戳。

默认情况下，如果插入时没有指定 TIMESTAMP 列的值，会将这个值设置为当前时间。

应该尽量使用 TIMESTAMP，因为它比 DATETIME 空间效率更高。

使用字符串存储时间的缺点：

  • 无法用日期函数进行计算和比较
  • 用字符串存储日期要占用更多的空间

同财务相关的金额类数据必须使用 decimal 类型

  • 非精准浮点：float,double
  • 精准浮点：decimal

Decimal 类型为精准浮点数，在计算时不会丢失精度

占用空间由定义的宽度决定，每 4 个字节可以存储 9 位数字，并且小数点要占用一个字节

可用于存储比bigint更大的整型数据

VARCHAR 和 CHAR

在不能确定字段需要多少字符时使用 VARCHAR 类型可以大大地节约磁盘空间、提高存储效率。但如果确切知道字符串长度,比如就在50~55之间,那就用 CHAR 因为 CHAR 类型由于本身定长的特性使其性能要高于 VARCHAR。如uuid，MD5

索引设计规范

限制每张表上的索引数量,建议单张表索引不超过 5 个

索引并不是越多越好，索引可以提高效率同样可以降低效率。

索引可以增加查询效率，但同样也会降低插入和更新的效率，甚至有些情况下会降低查询效率，过多索引不仅增加磁盘空间，而且更新插入数据都要动态维护索引，

因为 MySQL 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加 MySQL 优化器生成执行计划的时间，同样会降低查询性能。

禁止给表中的每一列都建立单独的索引

  • 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度
  • 索引需要占用物理空间，除了数据表占据数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大
  • 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

5.6版本之前，一个sql只能使用到一个表中的一个索引，5.6以后，虽然有了合并索引的优化方式，但是还是远远没有使用一个联合索引的查询方式好。

每个Innodb表必须有个主键

Innodb是一种索引组织表：数据的存储的逻辑顺序和索引的顺序是相同的。每个表都可以有多个索引，但是表的存储顺序只能有一种。

Innodb是按照主键索引的顺序来组织表的：

  • 不要使用更新频繁的列作为主键，不适用多列主键（相当于联合索引）
  • 不要使用 UUID，MD5，HASH，字符串列作为主键（无法保证数据的顺序增长）
  • 主键建议使用自增ID值

常见索引列建议

  • 出现在SELECT,UPDATE,DELETE语句的WHERE从句中的列
  • 包含在ORDER BY,GROUP BY,DISTINCT中的字段
  • 不要将符合1和2中的字段的列都建立一个索引，通常将1、2中的字段建立联合索引效果更好
  • 多表join的关联列

如果选择索引的顺序

建立索引的目的是：希望通过索引进行数据查找，减少随机IO，增加查询性能，索引能过滤出越少的数据，则从磁盘中读入的数据就越少。

  • 区分度最高的放在联合索引的最左侧
  • 尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越小，一页能存储的数据量越大，IO性能也就越好）
  • 使用最频繁的列放到联合索引的左侧（这样可以比较少的建立一些索引）

避免建立冗余索引和重复索引（增加了查询优化器生成执行计划的时间）

  • 重复索引示例：primary key(id),index(id),unique index(id)
  • 冗余索引示例：index(a,b,c),index(a,b),index(a)

对于频繁的查询优先考虑使用覆盖索引

覆盖索引：就是包含了所有查询字段（where,select,order by,group by包含的字段）的索引

覆盖索引的好处：

  • 避免Innodb表进行索引的二次查询：Innodb是以聚集索引的顺序来存储的，对于Innodb来说，二级索引在叶子节点中所保存的是行的主键信息，如果是用二级索引查询数据的话，在查找到相应的键值后，还要通过主键进行二次查询才能获取我们真实需要的数据。而在覆盖索引中，二级索引的键值中可以获取所有的数据，避免了对主键的二次查询，减少了IO操作，提升了查询效率。
    例如：创建了索引(username,age),在查询数据的时候如下，则查询出的列在叶子节点都存在，所以就不用进行二次查询，即“回表”。
SELECT username,age FROM user WHERE username = 'java' and age = '22'
  • 可以把随机IO变成顺序IO加快查询效率：由于覆盖索引是按键值的顺序存储的，对于IO密集型的范围查找来说，对比随机从磁盘读取每一行的数据IO要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取IO转变成索引查找的顺序IO。

索引SET规范

尽量避免使用外键约束

  • 不建议使用外键约束（foreign key），但一定要在表与表之间的关联键上建立索引
  • 外键可用于保证数据的参照完整性，但建议在业务端实现
  • 外键会影响父表和子表的写操作从而降低性能

使用索引的注意事项

  • 在经常需要搜索的列上，可以加快搜索的速度
  • 在经常使用WHERE子句中的列上面创建索引，加快条件的判断速度
  • 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的顺序，加快排序查询时间
  • 对于中到大型表索引都是非常有效的，但是特大型表的话维护开销会很大，不适合建索引
  • 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度
  • 避免在WHERE子句中对字段施加函数，这会造成无法命中索引
  • 在使用InnoDB时使用与业务无关的自增主键作为主键，即使用逻辑主键，而不要使用业务主键。
  • 将打算加索引的列设置为NOT NULL，否则将会导致引擎放弃使用索引而进行全表扫描
  • 删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗。MySQL5.7可以通过查询sys库的chema_unused_indexes视图来查询哪些索引从未被使用
  • 在使用limit offset查询缓存时，可以借助索引来提高性能
  • 数据量很少的表不要建索引，全表查询效率比遍历索引可能还快

数据库SQL开发规范

建议使用预编译语句进行数据库操作

预编译语句可以重复使用这些计划，减少SQL编译所需要的时间，还可以解决动态SQL所带来的SQL注入问题

只传参数，比传递SQL语句更高效

相同的语句可以一次解析，多次使用，提高处理效率。

避免数据类型的隐式转换

隐式转换会导致索引失效：

SELECT name,phone FROM customer WHERE id = '111';

充分利用表上已经存在的索引

避免使用双%号的查询条件，如：a like '%123%',(如果无前置%,只有后置%,是可以用到列上的索引的)

一个SQL只能利用到复合索引中的一列进行范围查询。如：有a，b，c列的联合索引，在查询条件中有a列的范围查询，则在b，c列上的索引将不会被用到。

在定义联合索引时，如果a列要用到范围查找的话，就要把a列放到联合索引的右侧，使用left join或者not exists来优化not in操作，因为not in也通常会使索引失效。

数据库设计时，应该要对以后扩展进行考虑

程序连接不同的数据库使用不同的账号，禁止跨库查询

  • 为数据库迁移和分库分表留出余地
  • 降低业务耦合度
  • 避免权限过大而产生的安全风险

禁止使用 SELECT * 必须使用 SELECT <字段列表> 查询

原因：

  • 消耗更多的 CPU 和 IO 以网络带宽资源
  • 无法使用覆盖索引
  • 可减少表结构变更带来的影响

禁止使用不含字段列表的INSERT语句

如：

insert into t values('a','b','c');

应使用：

insert into t(c1,c2,c3) values('a','b','c');

避免使用子查询，可以把子查询优化为join操作

通常子查询在in子句中，且子查询中为简单SQL（不包含union,group by,order by,limit从句）时，才可以把子查询转化为关联查询进行优化。

子查询性能差的原因：

子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表，都不会存在索引，所以查询性能会受到一定的影响。特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

由于子查询会产生大量的临时表也没有索引，所以会消耗过多的CPU和IO资源，产生大量的慢查询

避免使用JOIN关联太多的表

对于MySQL来说，是存在关联缓存的，缓存的大小可以由join_buffer_size参数进行设置。

在MySQL中，对于同一个SQL多关联（join）一个表，就会多分配一个关联缓存，如果在一个SQL中关联的表越多，所占用的内存也就越大。

如果程序中大量的使用了多表关联的操作，同时join_buffer_size设置的也合理的情况下，就容易造成服务器内存溢出的情况，就会影响到服务器数据库性能的稳定性。

同时对于关联操作来说，会产生临时表操作，影响查询效率，MySQL最多允许关联61个表，建议不超过5个。

减少同数据库的交互次数

数据库更适合处理批量操作，合并多个相同的操作到一起，可以提高处理效率。

对应同一列进行 or 判断时，使用 in 代替 or

in 的值不要超过 500 个，in 操作可以更有效的利用索引，or 大多数情况下很少能利用到索引。

禁止使用 order by rand() 进行随机排序

order by rand()会把表中所有符合条件的数据装载到内存中，然后在内存中对所有数据根据随机生成的值进行排序，并且可能会对每一行都生成一个随机值，如果满足条件的数据集非常大，就会消耗大量的 CPU 和 IO 及内存资源。

WHERE 从句中禁止对列进行函数转换和计算

对列进行函数转换或计算时会导致无法使用索引

不推荐：

where date(create_time)='20190101'

推荐：

where create_time >= '20190101' and create_time < '20190102'

在明显不会有重复值时使用 UNION ALL 而不是 UNION

  • UNION会把两个结果集的所有数据放到临时表中后再进行去重操作
  • UNION ALL不会再对结果集进行去重操作

拆分复杂的大 SQL 为多个小 SQL

  • 大 SQL 逻辑上比较复杂，需要占用大量 CPU 进行计算的 SQL
  • MySQL 中，一个 SQL 只能使用一个 CPU 进行计算
  • SQL 拆分后可以通过并行执行来提高处理效率

数据库操作行为规范

超100万行的批量写（UPDATE，DELETE，INSERT）操作，要分批多次进行操作

大批量操作可能会造成严重的主从延迟

主从环境中,大批量操作可能会造成严重的主从延迟，大批量的写操作一般都需要执行一定长的时间， 而只有当主库上执行完成后，才会在其他从库上执行，所以会造成主库与从库长时间的延迟情况

binlog 日志为 row 格式时会产生大量的日志

大批量写操作会产生大量日志，特别是对于row格式二进制数据而言，由于在row格式中会记录每一行数据的修改，我们一次修改的数据越多，产生的日志量也就会越多，日志的传输和恢复所需要的时间也就越长，这也是造成主从延迟的一个原因

避免产生大事务操作

大批量修改数据，一定是在一个事务中进行的，这就会造成表中大批量数据进行锁定，从而导致大量的阻塞，阻塞会对 MySQL 的性能产生非常大的影响。

对于大表使用pt-online-schema-change修改表结构

  • 避免大表修改产生的主从延迟
  • 避免在对表字段进行修改时进行锁表

    pt-online-schema-change是用于管理维护MySQL的小工具

对大表数据结构的修改一定要谨慎，会造成严重的锁表操作，尤其是生产环境，是不能容忍的。

pt-online-schema-change它会首先建立一个与原表结构相同的新表，并且在新表上进行表结构的修改，然后再把原表中的数据复制到新表中，并在原表中增加一些触发器。把原表中新增的数据也复制到新表中，在行所有数据复制完成之后，把新表命名成原表，并把原来的表删除掉。把原来一个 DDL 操作，分解成多个小的批次进行。

禁止为程序使用的账号赋予super权限

  • 当达到最大连接数限制时，还运行一个有super权限的用户连接
  • super权限只能留给DBA处理问题的账号使用

对于程序连接数据库账号，遵循权限最小原则

  • 程序使用数据库账号只能在一个DB下使用，不准垮库
  • 程序使用的账号原则上不准有drop权限

一条sql语句在MySQL中如何执行

在MySQL中，有以下基本组件：

  • 连接器：身份认证和权限相关（登录MySQL的时候）
  • 查询缓存：执行查询语句的时候，会先查询缓存（MySQL8.0版本后移除，因为这个功能不太实用）
  • 分析器：如果没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
  • 优化器：按照 MySQL 认为最优的方案去执行
  • 执行器：执行语句，然后从存储引擎返回数据

MySQL组件

简单来说MySQL主要分为 Server 层和存储引擎层：

  • Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binlog 日志模块。
  • 存储引擎：主要负责数据的存储和读取，采用可以替换的插件式架构，支持InnoDB,MyISAM,Memory等多个存储引擎，其中InnoDB引擎自有日志模块redolog。现在最常用的存储引擎是InnoDB，它从 MySQL 5.5.5 版本开始就被当做默认存储引擎了。

Server 层基本组件功能

连接器

连接器主要和身份认证和权限相关的功能相关，就好比一个级别很高的门卫一样。

主要负责用户登录数据库，进行用户的身份认证，包括校验账户密码，权限等操作，如果用户账户密码已通过，连接器会到权限表中查询该用户的所有权限，之后在这个连接里的权限逻辑判断都是会依赖此时读取到的权限数据，也就是说，后续只要这个连接不断开，即时管理员修改了该用户的权限，该用户也是不受影响的

查询缓存（MySQL8.0版本后移除）

查询缓存主要用来缓存我们所执行的 SELECT 语句以及该语句的结果集。

连接建立后，执行查询语句的时候，会先查询缓存，MySQL 会先校验这个 sql 是否执行过，以 Key-Value 的形式缓存在内存中，Key 是查询预计，Value 是结果集。如果缓存 key 被命中，就会直接返回给客户端，如果没有命中，就会执行后续的操作，完成后也会把结果缓存起来，方便下一次调用。当然在真正执行缓存查询的时候还是会校验用户的权限，是否有该表的查询条件。

MySQL 查询不建议使用缓存，因为查询缓存失效在实际业务场景中可能会非常频繁，假如对一个表更新的话，这个表上的所有的查询缓存都会被清空。对于不经常更新的数据来说，使用缓存还是可以的。

所以，一般在大多数情况下都是不推荐去使用查询缓存的。

MySQL 8.0 版本后删除了缓存的功能，官方也是认为该功能在实际的应用场景比较少，所以干脆直接删掉了。

分析器

MySQL 没有命中缓存，那么就会进入分析器，分析器主要是用来分析 SQL 语句是来干嘛的，分析器也会分为几步：

  • 第一步，词法分析：一条 SQL 语句由多个字符串组成。首先提取关键词，比如：SELECT, 然后提出查询的表，提出字段名，提出查询条件等等。做完这些操作后，就会进入第二步。
  • 第二步，语法分析：主要就是判断输入的 sql 是否正确，是否符合 MySQL 的语法。

完成了这 2 步之后，MySQL 就准备开始执行了，但是如何执行，怎么执行的结果最好，就需要到优化器中去判断。

优化器

优化器的作用就是它认为的最优的执行方案去执行（有时候可能也不是最优），比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。

可以说，经过了优化器之后可以说这个语句具体该如何执行就已经定下来。

执行器

当选择了执行方案后，MySQL就准备开始执行了，首先，执行前会检验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用引擎的接口，返回接口执行的结果。

具体语句分析

sql 语句可以分为两种，一种是查询，一种是更新（增加，更新，删除）。

查询语句

例如下面语句：

SELECT * FROM tb_student A WHERE A.age = 18 AND A.name = '张三';

这条语句的执行流程是：

  • 先检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限，在 MySQL8.0 版本以前，会先查询缓存，以这条 sql 语句为key在内存中查询是否有结果，如果有直接缓存，如果没有，执行下一步。
  • 通过分析器进行词法分析，提取 sql 语句的关键元素，比如提取上面这个语句是查询 select，提取需要查询的表名为 tb_student,需要查询所有的列，查询条件是这个表的 id='1'。然后判断这个 sql 语句是否有语法错误，比如关键词是否正确等等，如果检查没问题就执行下一步。
  • 接下来就是优化器进行确认执行方案，上面的 sql 语句，可以有两种执行方案：
a. 先查询学生表中姓名为“张三”的学生，然后判断是否年龄是18
b. 先找出学生中年龄18岁的学生，然后再查询姓名为“张三”的学生

优化器根据自己的优化算法进行选择执行效率最好的一个方案（优化器认为，有时候不一定最好）。那么确认了执行计划后就准备开始执行了。

  • 进行权限校验，如果没有权限就会返回错误信息，如果有权限就会调用数据库引擎接口，返回引擎的执行结果。

更新语句

对于以下 sql 语句：

UPDATE tb_student A SET A.age = 19 WHERE A.name = '张三';

这条语句也基本会沿着上一个查询的流程走，只不过在执行更新的时候要记录日志，这就会引入日志模块了。MySQL 自带的日志模块是 binlog(归档日志)，所有的存储引擎都可以使用。而 InnoDB 引擎还自带了一个日志模块 redo log(重做日志),下面以 InnoDB 模式进行流程说明：

  • 先查询到张三这一条数据，如果有缓存，就会用到缓存
  • 然后拿到查询的语句，把 age 改为19，然后再调用引擎 API 接口，写入这一行数据，InnoDB 引擎把数据保存在内存中，同时记录 redo log,此时 redo log进入 prepare 状态，然后告诉执行器，执行完成了，随时可以提交
  • 执行器收到通知后记录 binlog,然后调用引擎接口，提交 redo log 为提交状态
  • 更新完成

为什么要用两个日志模块？

这是因为最开始 MySQL 并没与 InnoDB 引擎( InnoDB 引擎是其他公司以插件形式插入 MySQL 的) ，MySQL 自带的引擎是 MyISAM，但是 redo log 是 InnoDB 引擎特有的，其他存储引擎都没有，这就导致会没有 crash-safe 的能力(crash-safe 的能力指的是即使数据库发生异常重启，之前提交的记录都不会丢失)，binlog 日志只能用来归档，而 InnoDB 引擎就是通过 redo log 来支持事务的。

为什么要先写redo log，再写bin log，最后再写一个redo log？

  • 假设先写 redo log 直接提交，然后写 binlog：如果写完 redo log 之后，机器挂了，binlog 日志没有被写入，那么机器重启后，这台机器会通过 redo log 恢复数据，但是这个时候 bin log 并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据
  • 假设先写 bin log,然后写 redo log：写完了 binlog，如果这时候机器异常重启了，由于没有 redo log，本机是无法恢复这一条数据的，但是 bin log 又有记录，那么就同样会产生数据不一致的情况。

如果采用 redo log 两个阶段提交的方式就不一样了，写完了 bin log 之后，然后再提交 redo log 就可以防止出现上述的问题，从而保证了数据的一致性。

假设 redo log 处于预提交状态，bin log 也已经写完了，这个时候如果发生了异常重启，就需要依赖于 MySQL 的处理机制了，MySQL 的处理过程如下：

  • 判断 redo log 是否完整，如果判断是完整的，就立即提交
  • 如果 redo log 只是预提交但不是 commit 状态，这个时候就会去判断 binlog 是否完整，如果完整就提交 redo log,不完整就回滚事务。

这就解决了数据一致性问题。

函数依赖

记A->B表示A函数决定B函数，也就是说B函数依赖于A函数。

如果{A1,A2,...,An}是关系的一个或多个属性的集合，该集合函数决定了关系的其他所有属性并且是最小的，那么该集合就称为键码。

对于A->B,如果能找到A的真子集A’，使得A'->B,那么A->B就是部分函数依赖，否则就是完全函数依赖。

对于A->B,B->C,则A->C是一个传递函数依赖。

三大范式

三大范式是用来优化数据库数据存储方式的三大规范。

高级别范式的依赖于低级别的范式，1NF是最低级别的范式。

  • 第一范式（1NF）：当关系模式 R 的所有属性都不能再分解为更基本的数据单位时，称 R 是满足第一范式的，简记为 1NF。第一范式强调的是列的原子性，即列不能够再分成其它几列。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。
  • 第二范式（2NF）：如果关系模式 R 满足第一范式，并且 R 的所有非主属性都完全依赖于 R 的每一个候选关键属性，称 R 满足第二范式，简记为2NF。也就是说，要求表中的所有列，都必须依赖于主键，而不能有任何一列与主键没有关系，也就是说一个表只描述一件事情。
    例如：订单表只描述订单相关的信息，所以所有字段都必须与订单id相关，产品表只描述产品相关的信息，所以所有字段都必须与产品id相关；因此不能在一张表中同时出现订单信息与产品信息。
  • 第三范式（3NF）：在第二范式的基础上更上一层，目标是确保每列都和主键列直接相关,而不是间接相关(另外非主键列必须直接依赖于主键，不能存在传递依赖)，要求字段不能有冗余，任何字段不能由其他字段派生。
    例如：订单表中需要有客户相关信息，在分离出客户表之后，订单表中只需要有一个用户 id 即可（外键），而不能有其他的客户信息。因为其他的客户信息直接关联于用户 id，而不是直接与订单 id 直接相关。
  • BC范式（BCNF）：主属性不依赖于主属性。在第三范式的基础上，数据库表中如果不存在任何字段对任一候选关键字段的传递函数依赖则符合 BCNF 范式。或者指在第三范式的基础上进一步消除主属性对于码的部分函数依赖和传递依赖。
  • 第四范式（4NF）：要求把同一表内的多对多关系删除
  • 第五范式（5NF）：从最终结构建立原始结构

如果不符合范式的关系，就会产生很多异常，主要有以下四种异常：

id Sname Sdept Mname Cname Grade
1 学生-1 学院-1 院长-1 课程-1 90
2 学生-2 学院-2 院长-2 课程-2 80
3 学生-2 学院-2 院长-2 课程-1 100
4 学生-3 学院-2 院长-2 课程-2 95
  • 冗余数据：例如学生-2出现了两次
  • 修改异常：修改了一个记录中的信息，但是另一个记录中相同的信息却没有修改。
  • 删除异常：删除了一个信息，那么也会丢失其他信息。例如删除了课程-1需要删除第一行和第三行，那么学生-1的信息就会丢失。
  • 插入异常：例如想要插入一个学生的信息，如果这个学生还没选课，那么就无法插入。

查询性能优化

使用Explain进行分析

Explain用来分析SELECT查询语句，使用方法就是在查询语句前面使用explain修饰，开发人员可以通过分析Explain结果来优化查询语句

返回结果中比较重要的字段有：

  • select_type：查询类型，有简单查询、联合查询、子查询
  • key：使用的索引
  • rows：扫描的行数

优化数据访问

1. 减少请求的数据量

  • 只返回必要的列：最好不要使用SELECT *语句
  • 只返回必要的行：使用LIMIT语句来限制返回的数据
  • 缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别在要查询的数据经常被重复查询时，缓存带来的查询性能提升将会是非常明显的。

2. 减少服务器端扫描的行数

最有效的方式是使用索引来覆盖查询

重构查询方式

1. 切分大查询

一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。

DELETE FROM messages WHERE create < DATA_SUB(NOW(),INTERVAL 3 MONTH);

rows_affected = 0;
do{
    rows_affected = do_query(
    "DELETE FROM messages WHERE create < DATE_SUB(NOW(),INTERVAL 3 MONTH) LIMIT 10000")
}while rows_affected > 0;

2. 分解大连接查询

将一个大连接查询分解成对每一个表进行一次单表查询，然后在应用程序中进行关联，这样做的好处有：

  • 让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。
  • 分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询。
  • 减少锁竞争；
  • 在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可伸缩。
  • 查询本身效率也可能会有所提升。

Explain 命令解析

Explain 是 MySQL 提供的 sql 分析工具，使用explain可以在不查询表的情况下，模拟优化器执行SQL查询语句，返回优化器选择执行最有效查询的一组操作，即“执行计划”，从而知道MySQL怎么处理QL语句的，分析查询语句和表结构的性能瓶颈。

使用explain很简单，就是在书写的SQL语句加一个单词 explain，然后将 explain + SQL执行后会出现一个表，这个表会告诉你MySQL优化器是怎样执行你的SQL的。

就比如执行下面一句语句：

EXPLAIN SELECT * FROM student

MySQL会给你反馈下面一个信息：

    id  select_type  table    partitions  type    possible_keys  key       key_len  ref       rows  filtered  Extra        
------  -----------  -------  ----------  ------  -------------  --------  -------  ------  ------  --------  -------------
     1  SIMPLE       student  (NULL)      index   (NULL)         name_age  68       (NULL)      30    100.00  Using index

下面对每一个信息单独分析。

id

该语句的唯一标识，包含一组数字，表示查询中执行 select 子句或操作表的顺序。如果explain的结果包括多个id值，则数字越大越先执行；而对于相同id的行，则表示从上往下依次执行。

select_type

表示查询类型，有以下几种取值范围：

查询类型 作用
SIMPLE 简单查询（未使用UNION或子查询）
PRIMARY 当存在子查询时，最外面的查询被标记为主查询
UNION 在UNION中的第二个和随后的SELECT被标记为UNION。如果UNION被FROM子句中的子查询包含，那么它的第一个SELECT会被标记为DERIVED。
DEPENDENT UNION UNION中的第二个或后面的查询，依赖了外面的查询
UNION RESULT 连接几个表查询后的结果
SUBQUERY 子查询中的第一个 SELECT
DEPENDENT SUBQUERY 子查询中的第一个 SELECT，依赖了外面的查询
DERIVED 用来表示包含在FROM子句的子查询中的SELECT，MySQL会递归执行并将结果放到一个临时表中。MySQL内部将其称为是Derived table（派生表），因为该临时表是从子查询派生出来的
DEPENDENT DERIVED 派生表，依赖了其他的表
MATERIALIZED 物化子查询
UNCACHEABLE SUBQUERY 子查询，结果无法缓存，必须针对外部查询的每一行重新评估
UNCACHEABLE UNION UNION属于UNCACHEABLE SUBQUERY的第二个或后面的查询

table

表示当前这一行正在访问哪张表，如果SQL定义了别名，则展示表的别名。

通过 id + table 这两个字段可以完全判断出每一条SQL语句的执行顺序和表的查询顺序。

先看id后看table，id和table在SQL执行判断过程中的关系就像是足球联赛的积分榜，首先一个联赛的球队排名应该先看积分，积分越高的球队排名越靠前，当两支或多只球队的积分一样高怎么办呢？那就看净胜球，净胜球越多的球队，排在前面。而在explain中可以把id看作是球队积分，table当作是净胜球。

partitions

当前查询匹配记录的分区。对于未分区的表，返回null

type（重点）

type 表示表的连接查询，是分析 sql 执行效率的重要信息，下面按照查询性能由好到坏分析每一个 type。

查询性能：

NULL > system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL

null

null 表示MySQL能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引

比如：

EXPLAIN SELECT 5*7
    id  select_type  table   partitions  type    possible_keys  key     
------  -----------  ------  ----------  ------  -------------  ------ 
     1  SIMPLE       (NULL)  (NULL)      (NULL)  (NULL)         (NULL)

此外，存在这样一种情况，大家都知道索引是将数据在B+Tree中进行排序了，所以查询速率才这么高，那么B+树的最边上的叶子节点要么是最大值要么是最小值。当要查询最大值或者最小值时，MySQL会直接到索引的叶子节点上直接拿，所以不用访问表或者索引。

EXPLAIN SELECT MAX(id) FROM student
    id  select_type  table   partitions  type    possible_keys  key    
------  -----------  ------  ----------  ------  -------------  ------ 
     1  SIMPLE       (NULL)  (NULL)      (NULL)  (NULL)         (NULL)

但是，要记住，NULL的前提是已经建立了索引。

system

当表只有一行记录（等于系统表）时的类型，这是const类型的特列。

const

针对主键或唯一索引的等值查询扫描, 最多只返回一行数据。表示通过索引一次就找到了，const用于比较primary key或uique索引，因为只匹配一行数据，所以很快，如主键置于where列表中，MySQL就能将该查询转换为一个常量。

简单来说，const是直接按主键或唯一键读取。

在下面的例子中，tbl_name 的查询 type 就是 const：

SELECT * FROM tbl_name WHERE primary_key=1;

SELECT * FROM tbl_name
  WHERE primary_key_part1=1 AND primary_key_part2=2;

eq_ref

用于联表查询的情况，按联表的主键或唯一键联合查询。

多表join时，对于来自前面表的每一行，在当前表中只能找到一行。这可能是除了system和const之外最好的类型。当主键或唯一非NULL索引的所有字段都被用作join联接时会使用此类型。

在下面的例子中，ref_table 将会使用到 eq_ref 连接类型：

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

ref

可以用于单表扫描或者连接。如果是连接的话，驱动表的一条记录能够在被驱动表中通过非唯一（主键）属性所在索引中匹配多行数据，或者是在单表查询的时候通过非唯一（主键）属性所在索引中查到一行数据。

ref 可用于使用 =或者<=> 运算符比较的索引列。

SELECT * FROM ref_table WHERE key_column=expr;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

fulltext

连接是使用FULLTEXT 索引执行的。

ref_or_null

这种连接类型类似于 ref，但 MySQL 会额外搜索包含NULL值的行。这种连接类型优化最常用于解析子查询。在以下示例中，MySQL 可以使用 ref_or_null 连接来处理ref_table：

SELECT * FROM ref_table
  WHERE key_column=expr OR key_column IS NULL;

index_merge

表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在range之前，但是实际上由于要读取多个索引，性能可能大部分时间都不如range。

unique_subquery

该类型和eq_ref类似，但是使用了IN查询，且子查询是主键或者唯一索引。

value IN (SELECT primary_key FROM single_table WHERE some_expr)

index_subquery

和unique_subquery类似，只是子查询使用的是非唯一索引。

value IN (SELECT key_column FROM single_table WHERE some_expr)

range

索引范围查询，常见于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等运算符的查询中。

SELECT * FROM tbl_name
  WHERE key_column = 10;

SELECT * FROM tbl_name
  WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name
  WHERE key_column IN (10,20,30);

SELECT * FROM tbl_name
  WHERE key_part1 = 10 AND key_part2 IN (10,20,30);

index

index只遍历索引树。

这有两种方式：

  • 如果索引是查询的覆盖索引并且可以用于满足表中所需的所有数据，则仅扫描索引树。在这种情况下，该Extra列显示Using index。这种通常比All快。因为，索引文件通常比数据文件小，也就是虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘读的。
  • 按索引的顺序来查找数据行，执行了全表扫描。此时，explain的Extra列的结果不会出现Uses index。

all

全表扫描。

possible_keys

这个表里面存在且可能会被使用的索引，可能会在这个字段下面出现，但是一般都以key为准。

key

实际使用的索引，如果 MySQL 决定使用其中一个possible_keys 索引来查找行，则该索引被列为键值。如果为null,则没有使用索引，否则会显示使用了哪些索引，查询中若使用了覆盖索引（查询的列刚好是索引），则该索引仅出现在key列表。

key_len

索引使用的字节数。由于存储格式，当字段允许为NULL时，key_len比不允许为空时大1字节。

ref

表示将哪个字段或常量和 key 列所使用的字段进行比较。

如果 ref 是一个函数，则使用的值是函数的结果。要想查看是哪个函数，可在EXPLAIN语句之后紧跟一个SHOW WARNING语句。

rows

rows是根据表的统计信息和索引的选用情况，优化器大概帮你估算出你执行这行函数所需要查询的行数。对于 innoDB 来说，这个值并不是准确值，而是一个估算的值。

filtered

Filter是查询的行数与总行数的比值。其实作用与rows差不多，都是数值越小，效率越高。

Extra

此列包含有关 MySQL 如何解析查询的附加信息。

  • Using filesort 表示当SQL中有一个地方需要对一些数据进行排序的时候，优化器找不到能够使用的索引，所以只能使用外部的索引排序，外部排序就不断的在磁盘和内存中交换数据，这样就摆脱不了很多次磁盘IO，以至于SQL执行的效率很低。
  • Using tempporary 表示在对MySQL查询结果进行排序时，使用了临时表,这样的查询效率是比外部排序更低的，常见于order by和group by。
  • Using index 表示使用了索引。
  • Using where 使用了where但是好像没啥用。
  • Using join buffer 表明使用了连接缓存，比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。
  • impossible where 筛选条件没能筛选出任何东西
  • distinct 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

CHAR和VARCHAR的区别

字符串主要有CHAR和VARCHAR两种，一种是定长的，一种是变长的。

VARCHAR这种变长类型能够节省空间，因为只需要存储必要的内容。但是在执行UPDATE时可能会时行变得比原来长，当超出一个页所能容纳的大小时，就需要执行额外的操作。MyISAM会将行拆成不同的片段存储，而InnoDB则需要分裂页来使行放入页内。

进行存储和检索时，会保留VARCHAR末尾的空格，而会删除CHAR末尾的空格。

MySQL的日志类型

MySQL 中有七种日志文件，分别是：重做日志（redo log）、回滚日志（undo log）、二进制日志（binlog）、错误日志（errorlog）、慢查询日志（slow query log）、一般查询日志（general log），中继日志（relay log）。

其中重做日志和回滚日志与事务操作息息相关，二进制日志也与事务操作有一定的关系。

redo log

记录更新时，InnoDB 引擎就会先把记录写到 RedoLog 里面，并更新内存。同时，InnoDB 引擎会在空闲时将这个操作记录更新到磁盘里面。

写入这一行数据，InnoDB 引擎把数据保存在内存中，同时记录 redo log,此时 redo log进入 prepare 状态，然后告诉执行器，执行完成了，随时可以提交

如果更新太多 RedoLog 处理不了的时候，需先将 RedoLog 部分数据写到磁盘，然后擦除 RedoLog 部分数据。RedoLog 类似转盘。

作用：确保事务的持久性。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启 mysql 服务的时候，根据 redo log 进行重做，从而达到事务的持久性这一特性。

内容：物理格式的日志，记录的是物理数据页面的修改的信息，其 redo log 是顺序写入redo log file 的物理文件中去的。

什么时候产生：事务开始之后就产生 redo log，redo log 的落盘并不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入 redo log 文件中。

什么时候释放：当对应事务的脏页写入到磁盘之后，redo log 的使命也就完成了，重做日志占用的空间就可以重用（被覆盖）。

对应的物理文件：默认情况下，对应的物理文件位于数据库的 data 目录下的 ib_logfile1&ib_logfile2

  • innodb_log_group_home_dir：指定日志文件组所在的路径，默认./ ，表示在数据库的数据目录下。
  • innodb_log_files_in_group：指定重做日志文件组中文件的数量，默认 2

关于文件的大小和数量，由一下两个参数配置

  • innodb_log_file_size：重做日志文件的大小。
  • innodb_mirrored_log_groups：指定了日志镜像文件组的数量，默认 1

很重要一点，redo log 是什么时候写盘的？前面说了是在事务开始之后逐步写盘的。

之所以说重做日志是在事务开始之后逐步写入重做日志文件，而不一定是事务提交才写入重做日志缓存，原因就是，重做日志有一个缓存区 Innodb_log_buffer，Innodb_log_buffer 的默认大小为8M，Innodb 存储引擎先将重做日志写入 innodb_log_buffer中。

然后会通过以下三种方式将 innodb 日志缓冲区的日志刷新到磁盘:

  1. Master Thread 每秒一次执行刷新 Innodb_log_buffer 到重做日志文件。
  2. 每个事务提交时会将重做日志刷新到重做日志文件。
  3. 当重做日志缓存可用空间少于一半时，重做日志缓存被刷新到重做日志文件

由此可以看出，重做日志通过不止一种方式写入到磁盘，尤其是对于第一种方式，Innodb_log_buffer 到重做日志文件是 Master Thread 线程的定时任务。

因此重做日志的写盘，并不一定是随着事务的提交才写入重做日志文件的，而是随着事务的开始，逐步开始的。

另外引用《MySQL技术内幕 Innodb 存储引擎》（page37）上的原话：

即使某个事务还没有提交，Innodb 存储引擎仍然每秒会将重做日志缓存刷新到重做日志文件。

这一点是必须要知道的，因为这可以很好地解释再大的事务的提交（commit）的时间也是很短暂的。

redo log 的 write pos 和 checkpoint

write pos ：是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。

check point：是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

write pos 和 check point 之间的是文件还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示文件满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把checkpoint 推进一下。

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

回滚日志（undo log）

作用：保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读

内容：逻辑格式的日志，在执行 undo 的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，这一点是不同于 redo log 的。

什么时候产生：事务开始之前，将当前的版本生成 undo log，undo 也会产生 redo 来保证 undo log 的可靠性

什么时候释放：当事务提交之后，undo log 并不能立马被删除，而是放入待清理的链表，由 purge 线程判断是否由其他事务在使用 undo 段中表的上一个事务之前的版本信息，决定是否可以清理undo log的日志空间。

另外, 在回滚段中的 undo logs 分为: insert undo log 和 update undo log

  • insert undo log：事务对 insert 新记录时产生的 undolog,只在事务回滚时需要, 并且在事务提交后就可以立即丢弃。
  • update undo log：事务对记录进行 delete 和 update 操作时产生的 undo log,不仅在事务回滚时需要，一致性读也需要，所以不能随便删除，只有当数据库所使用的快照中不涉及该日志记录，对应的回滚日志才会被 purge 线程删除。

对应的物理文件：MySQL5.6之前，undo 表空间位于共享表空间的回滚段中，共享表空间的默认的名称是 ibdata，位于数据文件目录中。

MySQL5.6之后，undo 表空间可以配置成独立的文件，但是提前需要在配置文件中配置，完成数据库初始化后生效且不可改变 undo log 文件的个数

如果初始化数据库之前没有进行相关配置，那么就无法配置成独立的表空间了。

关于 MySQL5.7 之后的独立undo 表空间配置参数如下

  • innodb_undo_directory = /data/undospace/ –undo : 独立表空间的存放目录
  • innodb_undo_logs = 128:回滚段为128KB
  • innodb_undo_tablespaces = 4: 指定有4个undo log文件

如果 undo 使用的共享表空间，这个共享表空间中又不仅仅是存储了 undo 的信息，共享表空间的默认为与 MySQL 的数据目录下面，其属性由参数 innodb_data_file_path 配置。

undo 是在事务开始之前保存的被修改数据的一个版本，产生 undo 日志的时候，同样会伴随类似于保护事务持久化机制的 redolog 的产生。

默认情况下 undo 文件是保持在共享表空间的，也即 ibdatafile 文件中，当数据库中发生一些大的事务性操作的时候，要生成大量的 undo 信息，全部保存在共享表空间中的。

因此共享表空间可能会变的很大，默认情况下，也就是 undo 日志使用共享表空间的时候，被“撑大”的共享表空间是不会也不能自动收缩的。

因此，mysql5.7 之后的“独立 undo 表空间”的配置就显得很有必要了。

二进制日志（binlog）

BinLog 是记录所有数据库表结构变更（例如create、alter table）以及表数据修改(insert、update、delete)的二进制日志，主从数据库同步用到的都是 BinLog 文件。

作用：用于复制，在主从复制中，从库利用主库上的 binlog 进行重播，实现主从同步；用于数据库的基于时间点的还原；

内容：逻辑格式的日志，可以简单认为就是执行过的事务中的 sql 语句。但又不完全是 sql 语句这么简单，而是包括了执行的 sql 语句（增删改）反向的信息，也就意味着 delete 对应着 delete 本身和其反向的 insert；update 对应着 update 执行前后的版本的信息；insert 对应着 delete 和 insert 本身的信息。

因此可以基于 binlog 做到类似于 oracle 的闪回功能，其实都是依赖于 binlog 中的日志记录。

什么时候产生：事务提交的时候，一次性将事务中的 sql 语句（一个事务可能对应多个 sql 语句）按照一定的格式记录到 binlog 中。这里与 redo log 很明显的差异就是 redo log 并不一定是在事务提交的时候刷新到磁盘，redo log 是在事务开始之后就开始逐步写入磁盘。

因此对于事务的提交，即便是较大的事务，提交（commit）都是很快的，但是在开启了 bin_log 的情况下，对于较大事务的提交，可能会变得比较慢一些。这是因为 binlog 是在事务提交的时候一次性写入的造成的，这些可以通过测试验证。

什么时候释放：binlog 的默认是保持时间由参数 expire_logs_days 配置，也就是说对于非活动的日志文件，在生成时间超过 expire_logs_days 配置的天数之后，会被自动删除。

对应的物理文件：配置文件的路径为 log_bin_basename，binlog 日志文件按照指定大小，当日志文件达到指定的最大的大小之后，进行滚动更新，生成新的日志文件。

对于每个 binlog 日志文件，通过一个统一的 index 文件来组织。

binlog 使用

需要修改my.cnf或my.ini配置文件，在 [mysqld] 下面增加 log_bin=mysql_bin_log 表示存放 binlog 的位置，重启MySQL服务。

binlog 信息查询 binlog 开启后，可以在配置文件中查看其位置信息，也可以在myslq命令行中查看：

show variables like '%log_bin%';
+---------------------------------+-------------------------------------+
| Variable_name                   | Value                               |
+---------------------------------+-------------------------------------+
| log_bin                         | ON                                  |
| log_bin_basename                | /var/lib/mysql/3306/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/3306/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                 |
| log_bin_use_v1_row_events       | OFF                                 |
| sql_log_bin                     | ON                                  |
+---------------------------------+-------------------------------------+

binlog 文件开启 binlog 后，会在数据目录（默认）生产 host-bin.n（具体binlog信息）文件及 host-bin.index 索引文件（记录 binlog 文件列表）。

当 binlog 日志写满( binlog 大小 max_binlog_size，默认1G)，者数据库重启才会生产新文件，但是也可通过手工进行切换让其重新生成新的文件（flush logs）；另外，如果正使用大的事务，由于一个事务不能横跨两个文件，因此也可能在binlog文件未满的情况下刷新文件：

mysql> show binary logs; //查看binlog文件列表,
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       177 |
| mysql-bin.000003 |  10343266 |
| mysql-bin.000004 |  10485660 |
| mysql-bin.000005 |     53177 |
| mysql-bin.000006 |      2177 |
| mysql-bin.000007 |      1383 |
+------------------+-----------+

查看binlog的状态：show master status，可查看当前二进制日志文件的状态信息，显示正在写入的二进制文件，及当前position


mysql> show master status;
 +------------------+----------+--------------+------------------+-------------------+
 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 +------------------+----------+--------------+------------------+-------------------+
 | mysql-bin.000007 |      120 |              |                  |                   |
 +------------------+----------+--------------+------------------+-------------------+

reset master 清空 binlog 日志文件

binlog内容

默认情况下 binlog 日志是二进制格式，无法直接查看。

可使用两种方式进行查看：

方法一：通过 mysqlbinlog 查看。

mysqlbinlog 是 mysql 官方提供的一个 binlog 查看工具，在使用时，可以通过 –read-from-remote-server 参数从远程服务器读取二进制日志，还可使用 --start-position --stop-position、--start-time= --stop-time 精确解析binlog 日志:

mysqlbinlog: /usr/bin/mysqlbinlog  mysql-bin.000007

截取位置1190-1352 binlog 如下：

***************************************************************************************
# at 1190   //事件的起点
#171223 21:56:26 server id 123  end_log_pos 1190 CRC32 0xf75c94a7 	Intvar
SET INSERT_ID=2/*!*/;
#171223 21:56:26 server id 123  end_log_pos 1352 CRC32 0xefa42fea 	Query	thread_id=4	exec_time=0	error_code=0
SET TIMESTAMP=1514123786/*!*/;              //开始事务的时间起点 (每个at即为一个event)
insert into tb_person  set name="name__2", address="beijing", sex="man", other="nothing"  //sql语句
/*!*/;
# at 1352
#171223 21:56:26 server id 123  end_log_pos 1383 CRC32 0x72c565d3 	Xid = 5 //执行时间，及位置戳，Xid:事件指示提交的XA事务
***************************************************************************************

方式二：使用 show binlog events命令

使用方法如下：

 SHOW BINLOG EVENTS
        [IN 'log_name'] //要查询的binlog文件名
        [FROM pos]  
        [LIMIT [offset,] row_count]

结果如下：

    1190-135如下：mysql> show binlog events in 'mysql-bin.000007' from 1190 limit 2\G
    *************************** 13. row ***************************
       Log_name: mysql-bin.000007
            Pos: 1190
     Event_type: Query  //事件类型
      Server_id: 123
    End_log_pos: 1352   //结束pose点，下个事件的起点
           Info: use `test`; insert into tb_person  set name="name__2", address="beijing", sex="man", other="nothing"
    *************************** 14. row ***************************
       Log_name: mysql-bin.000007
            Pos: 1352
     Event_type: Xid
      Server_id: 123
    End_log_pos: 1383
           Info: COMMIT /* xid=51 */

binlog 和 redolog 区别

bin log 的作用之一是还原数据库的，这与 redo log 很类似，很多人混淆过，但是两者有本质的不同：

  • 作用不同：redo log 是保证事务的持久性的，是事务层面的，binlog 作为还原的功能，是数据库层面的（当然也可以精确到事务层面的），虽然都有还原的意思，但是其保护数据的层次是不一样的。
  • 内容不同：redo log 是物理日志，是数据页面的修改之后的物理记录，记录的是在某个数据页上做了什么修改；binlog 是逻辑日志，可以简单认为记录的就是sql语句
  • redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。追加写是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

另外，两者日志产生的时间，可以释放的时间，在可释放的情况下清理机制，都是完全不同的。

恢复数据时候的效率，基于物理日志的 redo log 恢复数据的效率要高于语句逻辑日志的 binlog。

关于事务提交时，redo log 和 binlog 的写入顺序，为了保证主从复制时候的主从一致（当然也包括使用 binlog 进行基于时间点还原的情况），是要严格一致的，MySQL 通过两阶段提交过程来完成事务的一致性的，也即 redo log 和 binlog 的一致性的，理论上是先写 redo log，再写 binlog，两个日志都提交成功（刷入磁盘），事务才算真正的完成。

BinLog日志文件的三种模式

STATEMENT 模式

内容：binlog 只会记录可能引起数据变更的 sql 语句

优势：该模式下，因为没有记录实际的数据，所以日志量和 IO 都消耗很低，性能是最优的。

劣势：但有些操作并不是确定的，比如 uuid() 函数会随机产生唯一标识，当依赖 binlog 回放时，该操作生成的数据与原数据必然是不同的，此时可能造成无法预料的后果。

ROW 模式

内容：在该模式下，binlog 会记录每次操作的源数据与修改后的目标数据，StreamSets 就要求该模式。

优势：可以绝对精准的还原，从而保证了数据的安全与可靠，并且复制和数据恢复过程可以是并发进行的

劣势：缺点在于 binlog 体积会非常大，同时，对于修改记录多、字段长度大的操作来说，记录时性能消耗会很严重。阅读的时候也需要特殊指令来进行读取数据。

MIXED 模式

内容：是对上述 STATEMENT 跟 ROW 两种模式的混合使用。

细节：对于绝大部分操作，都使用 STATEMENT 来进行 binlog 的记录，只有以下操作使用 ROW 来实现：表的存储引擎为 NDB，使用了 uuid() 等不确定函数，使用了 insert delay 语句，使用了临时表。

主从复制

主从复制

主要涉及三个线程：binlog线程、I/O 线程和SQL线程

  • 主节点 bin log dump 线程：负责将主服务器上的数据更改写入二进制日志（Binary log）中。当从节点连接主节点时，主节点会创建一个 log dump 线程，用于发送 bin-log 的内容。在读取 bin-log 中的操作时，此线程会对主节点上的 bin-log 加锁，当读取完成，甚至在发送给从节点之前，锁会被释放。
  • 从节点 I/O 线程：负责从主服务器上读取二进制日志，并写入从服务器的中继日志（Relay log）。当从节点上执行 start slave 命令之后，从节点会创建一个 I/O 线程用来连接主节点，请求主库中更新的 bin-log。I/O 线程接收到主节点 binlog dump 进程发来的更新之后，保存在本地 relay-log 中。
  • 从节点 SQL 线程：负责读取中级日志，解析出主服务器已经执行的数据更改并在从服务器中重放(Replay)。SQL线程负责读取 relay log 中的内容，解析成具体的操作并执行，最终保证主从数据的一致性。

主从复制

对于每一个主从连接，都需要三个进程来完成。当主节点有多个从节点时，主节点会为每一个当前连接的从节点建一个binary log dump 进程，而每个从节点都有自己的 I/O 进程，SQL 进程。从节点用两个线程将从主库拉取更新和执行分成独立的任务，这样在执行同步数据任务的时候，不会降低读操作的性能。比如，如果从节点没有运行，此时 I/O 进程可以很快从主节点获取更新，尽管 SQL 进程还没有执行。如果在 SQL 进程执行之前从节点服务停止，至少 I/O 进程已经从主节点拉取到了最新的变更并且保存在本地 relay 日志中，当服务再次起来之后，就可以完成数据的同步。

要实施复制，首先必须打开 Master 端的 binary log（bin-log） 功能，否则无法实现。

因为整个复制过程实际上就是 Slave 从 Master 端获取该日志然后再在自己身上完全顺序的执行日志中所记录的各种操作。

复制的基本过程如下：

  1. 从节点上的I/O 进程连接主节点，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容；
  2. 主节点接收到来自从节点的 I/O 请求后，通过负责复制的 I/O 进程根据请求信息读取指定日志指定位置之后的日志信息，返回给从节点。返回信息中除了日志所包含的信息之外，还包括本次返回的信息的 bin-log file 的以及 bin-log position；从节点的 I/O 进程接收到内容后，将接收到的日志内容更新到本机的 relay log 中，并将读取到的 binary log 文件名和位置保存到 master-info 文件中，以便在下一次读取的时候能够清楚的告诉 Master “我需要从某 bin-log 的哪个位置开始往后的日志内容，请发给我”；如果从节点的请求不带请求位置参数，则主节点就会从第一个日志文件中的第一个事件一个一个发送给从节点。
  3. Slave 的 SQL 线程检测到 relay-log 中新增加了内容后，会将 relay-log 的内容解析成在主节点上实际执行过的操作，并在本数据库中执行。

MySQL 主从复制的模式

MySQL 主从复制默认是异步的模式。并且复制的时候是有并行复制能力的。MySQL 增删改操作会全部记录在 binary log 中，当 slave 节点连接 master 时，会主动从 master 处获取最新的 bin log 文件。在 MySQL 8.0 中，MySQL 的复制可以分为以下几种方式：

MySQL复制类型

异步模式（mysql async-mode）

异步模式如下图所示，这种模式下，主节点不会主动 push bin log 到从节点，这样有可能导致 failover 的情况下，也许从节点没有即时地将最新的 bin log 同步到本地。

异步模式

在异步复制（async replication）中，Master 不用关心 Slave 是否接收到二进制日志，所以 Master 与 Slave 没有任何的依赖关系。可以认为 Master 和 Slave 是分别独自工作的两台服务器，数据最终会通过二进制日志达到一致。

异步复制的性能最好，因为它对数据库本身几乎没有任何开销，除非主从延迟非常大，Dump Thread 需要读取大量二进制日志文件。

如果业务对于数据一致性要求不高，当发生故障时，能容忍数据的丢失，甚至大量的丢失，推荐用异步复制，这样性能最好（比如像微博这样的业务，虽然它对性能的要求极高，但对于数据丢失，通常可以容忍）。但往往核心业务系统最关心的就是数据安全，比如监控业务、告警系统。

半同步模式(mysql semi-sync)

这种模式下主节点需要接收到其中 N 台从节点的返回信息，才会 commit；否则需要等待直到超时时间然后切换成异步模式再提交；这样做的目的可以使主从数据库的数据延迟缩小，可以提高数据安全性，确保了事务提交后，binlog 至少传输到了 N 个从节点上，不能保证从节点将此事务更新到 db 中。性能上会有一定的降低，响应时间会变长。如下图所示：

半同步模式

半同步模式不是 mysql 内置的，从 mysql 5.5 开始集成，需要 master 和 slave 安装插件开启半同步模式，相关设置如下：

plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enabled = 1
rpl-semi-sync-slave-enabled = 1
rpl_semi_sync_master_wait_no_slave = 1

上面的配置中：

  • 第 1 行要求数据库启动时安装半同步插件；

  • 第 2、3 行表示分别启用半同步 Master 和半同步 Slave 插件；

  • 第 4 行表示半同步复制过程中，提交的事务必须至少有一个 Slave 接收到二进制日志。

在半同步复制中，上述的同步机制称为有损半同步复制，它是 MySQL 5.7 版本前的半同步复制机制，这种半同步复制在Master 发生宕机时，Slave 会丢失最后一批提交的数据，若这时 Slave 提升（Failover）为Master，可能会发生已经提交的事情不见了，发生了回滚的情况。

而 MySQL 5.7 的无损半同步复制解决了这个问题，其原理如下图所示：

无损半同步复制原理

从上图可以看到，无损半同步复制 WAIT ACK 发生在事务提交之前，这样即便 Slave 没有收到二进制日志，但是 Master 宕机了，由于最后一个事务还没有提交，所以本身这个数据对外也不可见，不存在丢失的问题。

所以，对于任何有数据一致性要求的业务，如电商的核心订单业务、银行、保险、证券等与资金密切相关的业务，务必使用无损半同步复制。这样数据才是安全的、有保障的、即使发生宕机，从机也有一份完整的数据。

全同步模式

全同步模式是指主节点和从节点全部执行了 commit 并确认才会向客户端返回成功。

多源复制

无论是异步复制还是半同步复制，都是 1 个 Master 对应 N 个 Slave。其实 MySQL 也支持 N 个 Master 对应 1 个 Slave，这种架构就称之为多源复制。

多源复制允许在不同 MySQL 实例上的数据同步到 1 台 MySQL 实例上，方便在 1 台 Slave 服务器上进行一些统计查询，如常见的 OLAP 业务查询。

多源复制的架构如下所示：

多源复制

上图显示了订单库、库存库、供应商库，通过多源复制同步到了一台 MySQL 实例上，接着就可以通过 MySQL 8.0 提供的复杂 SQL 能力，对业务进行深度的数据分析和挖掘。

延迟复制

前面介绍的复制架构，Slave 在接收二进制日志后会尽可能快地回放日志，这样是为了避免主从之间出现延迟。而延迟复制却允许Slave 延迟回放接收到的二进制日志，为了避免主服务器上的误操作，马上又同步到了从服务器，导致数据完全丢失。

可以通过以下命令设置延迟复制：

CHANGE MASTER TO master_delay = 3600

这样就人为设置了 Slave 落后 Master 服务器1个小时。

延迟复制在数据库的备份架构设计中非常常见，比如可以设置一个延迟一天的延迟备机，这样本质上说，用户可以有 1 份 24 小时前的快照。

那么当线上发生误操作，如 DROP TABLE、DROP DATABASE 这样灾难性的命令时，用户有一个 24 小时前的快照，数据可以快速恢复。

对金融行业来说，延迟复制是你备份设计中，必须考虑的一个架构部分。

复制方式

MySQL 主从复制有三种方式：基于 SQL 语句的复制（statement-based replication，SBR），基于行的复制（row-based replication，RBR)，混合模式复制（mixed-based replication,MBR)。对应的 binlog 文件的格式也有三种：STATEMENT,ROW,MIXED。

  • Statement-base Replication (SBR) 就是记录 sql 语句在 bin log 中，Mysql 5.1.4 及之前的版本都是使用的这种复制格式。优点是只需要记录会修改数据的 sql 语句到 binlog 中，减少了 binlog 日志量，节约 I/O，提高性能。缺点是在某些情况下，会导致主从节点中数据不一致（比如sleep(),now()等）。
  • Row-based Relication (RBR) 是 mysql master 将 SQL 语句分解为基于 Row 更改的语句并记录在 bin log 中，也就是只记录哪条数据被修改了，修改成什么样。优点是不会出现某些特定情况下的存储过程、或者函数、或者 trigger 的调用或者触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是修改 table 的时候会让日志暴增,同时增加 bin log 同步时间。也不能通过 bin log 解析获取执行过的 sql 语句，只能看到发生的 data 变更。
  • Mixed-format Replication(MBR)，MySQL NDB cluster 7.3 和7.4 使用的MBR。是以上两种模式的混合，对于一般的复制使用 STATEMENT 模式保存到 binlog，对于 STATEMENT 模式无法复制的操作则使用 ROW 模式来保存，MySQL 会根据执行的 SQL 语句选择日志保存方式。

GTID复制模式

在传统的复制里面，当发生故障，需要主从切换，需要找到 binlog 和 pos 点，然后将主节点指向新的主节点，相对来说比较麻烦，也容易出错。在MySQL 5.6里面，不用再找 binlog 和 pos 点，只需要知道主节点的 ip，端口，以及账号密码就行，因为复制是自动的，MySQL会通过内部机制 GTID 自动找点同步。

在原来基于日志的复制中, 从库需要告知主库要从哪个偏移量进行增量同步, 如果指定错误会造成数据的遗漏, 从而造成数据的不一致.

而基于GTID的复制中，从库会告知主库已经执行的事务的GTID的值，然后主库会将所有未执行的事务的GTID的列表返回给从库，并且可以保证同一个事务只在指定的从库执行一次。

通过全局的事务 ID确定从库要执行的事务的方式，代替了以前需要用bin-log和pos点确定从库要执行的事务的方式。

基于GTID复制实现的工作原理

  1. 主节点更新数据时，会在事务前产生 GTID，一起记录到 binlog 日志中。
  2. 从节点的 I/O 线程将变更的 bin log，写入到本地的 relay log 中。
  3. SQL线程从 relay log 中获取 GTID，然后对比本地 binlog 是否有记录（所以MySQL从节点必须要开启 binary log）。
  4. 如果有记录，说明该 GTID 的事务已经执行，从节点会忽略；如果没有记录，从节点就会从 relay log 中执行该 GTID 的事务，并记录到 bin log。
  5. 在解析过程中会判断是否有主键，如果没有就用二级索引，如果有就用全部扫描。

传统模式与 GTID 对比：

  • 日志：传统的方式，默认的方式。依赖二进制日志，根据日志的偏移量。事务不断提交，二进制日志的偏移量也会不断的变化。需要从库告诉主库，自己明确复制到了偏移量的什么位置。
  • GTID: 全局事务ID，在一个集群内的一个GTID是唯一的， GTID= source_id:transcation_id，source_id为那一台机器上的，slave增量复制还未同步的GTID即可。

主从复制延迟

产生延迟原因？

  • 主节点如果执行一个很大的事务(更新千万行语句，总之执行很长时间的事务)，那么就会对主从延迟产生较大的影响
  • 网络延迟，日志较大，slave 数量过多。
  • 主上多线程写入，从节点只有单线程恢复

处理办法：

  • 大事务：将大事务分为小事务，分批更新数据。
  • 减少Slave的数量，不要超过5个，减少单次事务的大小。
  • MySQL 5.7之后，可以使用多线程复制（基于库），在MySQL 5.6以前的版本，slave 的复制是单线程的。一个事件一个事件的读取应用。而 master 是并发写入的，所以延时是避免不了的。唯一有效的方法是把多个库放在多台 slave，这样又有点浪费服务器。在 MySQL 5.6 里面，可以把多个表放在多个库，这样就可以使用多线程复制。

读写分离

读写分离是主从复制的一种应用架构，主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作

读写分离能提高性能的原因在于：

  • 主从服务器负责各自的读和写，极大程度缓解了锁的争用；
  • 从服务器可以使用MyISAM，提升查询性能以及节约系统开销
  • 增加冗余，提高可用性

读写分离常用代理方式实现，代理服务器接收应用层传来的读写请求，然后决定发到哪个服务器

读写分离

由于主从之间需要数据同步，所以在数据上就会存在一定延时，在这延时窗口期内，从库的读只能读到一个旧数据。

解决办法

允许一定的延迟

如果业务对于数据一致性要求不高，可以忍受一定的延迟，那可以不管这种场景。

数据同步写方案

主从数据同步方案，一般都是采用的异步方式同步给备库。可以将其修改为同步方案，主从同步完成，主库上的写才能返回。不过，由于主库写需要等待主从完成，写请求的时延将会增加，吞吐量将会降低。

这一点对于现在在线业务，可能无法接受。

选择性强制读主

对于需要强一致的场景，我们可以将其的读请求都操作主库，这样读写都在主库，就没有不一致的情况。但是这种情况下相当于是主备架构，浪费了一个数据库的资源，同时增加了主库压力。

中间件选择路由

这种方案需要使用一个中间件，所有数据库操作都先发到中间件，由中间件再分发到相应的数据库。

中间件路由选择

这时流程如下：

  1. 写请求，中间件将会发到主库，同时记录一下此时写请求的 key（操作表加主键等）
  2. 读请求，如果此时 key 存在，将会路由到主库
  3. 一定时间后（经验值），中间件认为主从同步完成，删除这个 key，后续读将会读从库

这种方案，可以保持数据读写的一致，但是系统架构增加了一个中间件，整体复杂度变高，业务开发也变得复杂，学习成本也比较高。

通过缓存路由

这种方案与中间件的方案流程比较类似，不过改造成本相对较低，不需要增加任何中间件。

通过缓存路由

这时流程如下:

  1. 写请求发往主库，同时缓存记录操作的 key，缓存的失效时间设置为主从的延时

  2. 读请求首先判断缓存是否存在

    • 若存在，代表刚发生过写操作，读请求操作主库
    • 若不存在，代表近期没发生写操作，读请求操作从库

这种方案相对中间件的方案成本较低，但是此时又引入一个缓存组件，所有读写之间就又多了一步缓存操作。

事务的实现原理？（ACID实现原理）

事务就有 ACID 特性，保证 ACID 特性就相当于实现了事务。

原子性

利用 Innodb 的undo log。

undo log 名为回滚日志，是实现原子性的关键，当事务回滚时能够撤销所有已经成功执行的 sql 语句，需要记录要回滚的相应日志信息。

例如：

  1. 当 delete 一条数据的时候，就需要记录这条数据的信息，回滚的时候，insert 这条旧数据
  2. 当 update 一条数据的时候，就需要记录之前的旧值，回滚的时候，根据旧值执行 update 操作
  3. 当 insert 一条数据的时候，就需要这条记录的主键，回滚的时候，根据主键执行 delete 操作

undo log 记录了这些回滚需要的信息，当事务执行失败或调用了 rollback，导致事务需要回滚，便可以利用 undo log 中的信息将数据回滚到修改之前的样子。

一致性

从数据库层面，数据库通过原子性、隔离性、持久性来保证一致性。也就是说 ACID 四大特性之中，C(一致性)是目的，A(原子性)、I(隔离性)、D(持久性)是手段，是为了保证一致性，数据库提供的手段。数据库必须要实现 AID 三大特性，才有可能实现一致性。例如，原子性无法保证，显然一致性也无法保证。

但是，如果你在事务里故意写出违反约束的代码，一致性还是无法保证的。例如，你在转账的例子中，你的代码里故意不给 B 账户加钱，那一致性还是无法保证。因此，还必须从应用层角度考虑。

从应用层面，通过代码判断数据库数据是否有效，然后决定回滚还是提交数据

隔离性

利用的是锁和MVCC机制。一个行记录数据有多个版本对快照数据，这些快照数据在 undo log 中。

如果一个事务读取的行正在做 DELELE 或者 UPDATE 操作，读取操作不会等行上的锁释放，而是读取该行的快照版本。

由于MVCC机制在可重复读(Repeateable Read)和读已提交(Read Commited)的MVCC表现形式不同。
但是有一点说明一下，在事务隔离级别为读已提交(Read Commited)时，一个事务能够读到另一个事务已经提交的数据，是不满足隔离性的。但是当事务隔离级别为可重复读(Repeateable Read)中，是满足隔离性的。

持久性

是利用Innodb的redo log。

Mysql是先把磁盘上的数据加载到内存中，在内存中对数据进行修改，再刷回磁盘上。如果此时突然宕机，内存中的数据就会丢失。

怎么解决这个问题？

简单啊，事务提交前直接把数据写入磁盘就行啊。

这么做有什么问题？

只修改一个页面里的一个字节，就要将整个页面刷入磁盘，太浪费资源了。毕竟一个页面16kb大小，你只改其中一点点东西，就要将16kb的内容刷入磁盘，听着也不合理。

毕竟一个事务里的SQL可能牵涉到多个数据页的修改，而这些数据页可能不是相邻的，也就是属于随机IO。显然操作随机IO，速度会比较慢。

于是，决定采用 redo log 解决上面的问题。当做数据修改的时候，不仅在内存中操作，还会在 redo log 中记录这次操作。当事务提交的时候，会将 redo log 日志进行刷盘 (redo log一部分在内存中，一部分在磁盘上)。当数据库宕机重启的时候，会将 redo log 中的内容恢复到数据库中，再根据 undo log 和 binlog 内容决定回滚数据还是提交数据。

采用redo log的好处？

其实好处就是将 redo log 进行刷盘比对数据页刷盘效率高，具体表现如下

  • redo log 体积小，毕竟只记录了哪一页修改了啥，因此体积小，刷盘快。
  • redo log 是一直往末尾进行追加，属于顺序IO。效率显然比随机IO来的快。

数据库宕机恢复过程

相关概念

  • lsn: 可以理解为数据库从创建以来产生的 redo 日志量，这个值越大，说明数据库的更新越多，也可以理解为更新的时刻。此外，每个数据页上也有一个 lsn，表示最后被修改时的 lsn，值越大表示越晚被修改。比如，数据页 A 的 lsn 为100，数据页 B 的 lsn 为200，checkpoint lsn 为150，系统 lsn 为300，表示当前系统已经更新到300，小于 150 的数据页已经被刷到磁盘上，因此数据页 A 的最新数据一定在磁盘上，而数据页 B 则不一定，有可能还在内存中。
  • 检查点: 英文名为 checkpoint。数据库为了提高性能，数据页在内存修改后并不是每次都会刷到磁盘上。checkpoint 之前的数据页保证一定落盘了，这样之前的日志就没有用了(由于InnoDB redolog 日志循环使用，这时这部分日志就可以被覆盖)，checkpoint 之后的数据页有可能落盘，也有可能没有落盘，所以 checkpoint 之后的日志在崩溃恢复的时候还是需要被使用的。InnoDB 会依据脏页的刷新情况，定期推进 checkpoint，从而减少数据库崩溃恢复的时间。检查点的信息在第一个日志文件的头部。

恢复过程

InnoDB 的数据恢复过程需要 redo log、binlog、undo log 等参与。把 InnoDB 的恢复过程主要划分为两个阶段：第一阶段主要依赖于 redo log 的恢复；而第二阶段，需要 binlog 和 undo log 的共同参与。

简单来说，MySQL 的处理过程如下：

  • 判断 redo log 是否完整，如果判断是完整的，就立即提交
  • 如果 redo log 只是预提交但不是 commit 状态，这个时候就会去判断 binlog 是否完整，如果完整就提交 redo log,不完整就回滚事务。

第一阶段

第一阶段，数据库启动后，InnoDB会通过 redo log 找到最近一次 checkpoint 的位置，然后根据 checkpoint 相对应的 LSN 开始，获取需要重做的日志，接着解析获取的日志并且保存到一个哈希表中，最后通过遍历哈希表中的redo log 信息，读取相关页进行恢复。

InnoDB的 checkpoint 信息保存在日志文件中，即 ib_logfile0 的开始2048个字节中，checkpoint 有两个，交替更新。

checkpoint 信息分别保存在 ib_logfile0 的 512 字节和 1536 字节处，每个 checkpoint 默认大小为 512 字节，InnoDB 的 checkpoint 主要由3部分信息组成：

  • checkpoint no：主要保存的是 checkpoint 号，因为InnoDB有两个 checkpoint，通过 checkpoint 号来判断哪个 checkpoint 更新。
  • checkpoint lsn：主要记录了产生该 checkpoint 时 flush 的LSN，确保在该 LSN 前面的数据页都已经落盘，不再需要通过 redo log 进行恢复。
  • checkpoint offset：主要记录了该 checkpoint 产生时，redo log 在 ib_logfile 中的偏移量，通过该 offset 位置就可以找到需要恢复的 redo log 开始位置。

checkpoint结构

通过以上 checkpoint 的信息，我们可以简单得到需要恢复的 redo log 的位置，然后通过顺序扫描该 redo log 来读取数据，比如我们通过 checkpoint 定位到开始恢复的redo log 位置在ib_logfile1 中的某个位置，那么整个 redo log 扫描的过程可能是这样的：

  1. 从 ib_logfile1 的指定位置开始读取 redo log，每次读取 4 * page_size 的大小，这里我们默认页面大小为 16K，所以每次读取 64K 的 redo log 到缓存中，redo log 每条记录（block）的大小为 512 字节。
  2. 读取到缓存中的 redo log 通过解析、验证等一系列过程后，把 redo log 的内容部分保存到用于恢复的缓存 recv_sys->buf，保存到恢复缓存中的每条信息主要包含两部分：（space，offset）组成的位置信息和具体 redo log 的内容，我们称之为body。
  3. 同时保存在恢复缓存中的 redo 信息会根据（space，offset）计算一个哈希值后保存到一个哈希表（recv_sys->addr_hash）中，相同哈希值、不同（space，offset）用链表存储，相同的（space，offset）用列表保存，可能部分事务比较大，redo 信息一个 block 不能保存，所以，每个 body 中可以用链表链接多 body 的值。

redo log 被保存到哈希表中之后，InnoDB 就可以开始进行数据恢复，只需要轮询哈希表中的每个节点获取redo信息，根据（space，offset）读取指定页面后进行日志覆盖。

InnoDB为了保证恢复的速度，做了几点优化：

  • 优化1：在根据（space，offset）读取数据页信息到 buffer pool 的时候，InnoDB 不是只读取一张页面，而是读取相邻的 32 张页面到 buffer pool。这里有个假设，InnoDB认为，如果一张页面被修改了，那么其周围的一些页面很有可能也被修改了，所以一次性连续读入32张页面可以避免后续再重新读取。
  • 优化2：在MySQL5.7版本以前，InnoDB恢复时需要依赖数据字典，因为InnoDB根本不知道某个具体的 space 对应的ibd文件是哪个，这些信息都是数据字典维护的。而且在恢复前，需要把所有的表空间全部打开，如果库中有数以万计的表，把所有表打开一遍，整个过程就会很慢。那么MySQL5.7在这上面做了哪些改进呢？其实很简单，针对上面的问题，InnoDB在 redo log 中增加了两种 redo log 的类型来解决。MLOG_FILE_NAME 用于记录在 checkpoint 之后，所有被修改过的信息（space，filepath）；MLOG_CHECKPOINT 则用于标志 MLOG_FILE_NAME 的结束。

上面两种redo log类型的添加，完美解决了前面遗留的问题，redo log 中保存了后续需要恢复的 space 和 filepath 对。所以，在恢复的时候，只需要从 checkpoint 的位置一直往后扫描到 MLOG_CHECKPOINT 的位置，这样就能获取到需要恢复的 space 和 filepath。在恢复过程中，只需要打开这些ibd文件即可。当然由于 space 和 filepath 的对应关系通过 redo 存了下来，恢复的时候也不再依赖数据字典。

这里需要强调的是 MLOG_CHECKPOINT 在每个 checkpoint 点中最多存在一次，如果出现多次 MLOG_CHECKPOINT 类型的日志，则说明 redo 已经损坏，InnoDB 会报错。

最多存在一次，那么会不会有不存在的情况？

答案是肯定的，在每次checkpoint过后，如果没有发生数据更新，那么 MLOG_CHECKPOINT 就不会被记录。所以只要查找下 redo log 最新一个 checkpoint 后的 MLOG_CHECKPOINT 是否存在，就能判定上次MySQL是否正常关机。

5.7版本的MySQL在InnoDB进行恢复的时候，也正是这样做的，MySQL5.7在进行恢复的时候，一般情况下需要进行最多3次的redo log扫描：

  1. 首先对redo log的扫描，主要是为了查找 MLOG_CHECKPOINT，这里并不进行 redo log的解析。如果没有找到 MLOG_CHECKPOINT，则说明 InnoDB 不需要进行 recovery，后面的两次扫描可以省略；如果找到了 MLOG_CHECKPOINT，则获取 MLOG_FILE_NAME 到指定列表，后续只需打开该链表中的表空间即可。
  2. 下一步的扫描是在第一次找到 MLOG_CHECKPOINT 基础之上进行的，该次扫描会把 redo log 解析到哈希表中，如果扫描完整个文件，哈希表还没有被填满，则不需要第三次扫描，直接进行recovery就结束。
  3. 最后是在第二次基础上进行的，第二次扫描把哈希表填满后，还有redo log 剩余，则需要循环进行扫描，哈希表满后立即进行 recovery，直到所有的redo log 被 apply 完为止。

redo log全部被解析并且apply完成，整个 InnoDB recovery 的第一阶段也就结束了，在该阶段中，所有已经被记录到redo log但是没有完成数据刷盘的记录都被重新落盘。

然而，InnoDB单靠 redo log 的恢复是不够的，这样还是有可能会丢失数据(或者说造成主从数据不一致)。

因为在事务提交过程中，写 binlog 和写 redo log 提交是两个过程，写 binlog 在前而 redo 提交在后，如果 MySQL 写完 binlog 后，在 redo 提交之前发生了宕机，这样就会出现问题：binlog 中已经包含了该条记录，而 redo 没有持久化。binlog 已经落盘就意味着 slave 上可以 apply 该条数据，redo 没有持久化则代表了 master 上该条数据并没有落盘，也不能通过 redo 进行恢复。

这样就造成了主从数据的不一致，换句话说主上丢失了部分数据，那么MySQL又是如何保证在这样的情况下，数据还是一致的？这就需要进行第二阶段恢复。

第二阶段 binlog 和 undo log 共同参与

该阶段的恢复中，也被划分成两部分：第一部分，根据 binlog 获取所有可能没有提交事务的 xid 列表；第二部分，根据 undo 中的信息构造所有未提交事务链表，最后通过上面两部分协调判断事务是否可以提交。

MySQL 在第二阶段恢复的时候，先会去读取最后一个 binlog 文件的所有 event 信息，然后把 xid 保存到一个列表中，然后进行第二部分的恢复，如下：

InnoDB 当前版本有 128 个回滚段，每个回滚段中保存了 undo log 的位置指针，通过扫描 undo 日志，可以构造出还未被提交的事务链表（存在于 insert_undo_list 和 update_undo_lsit 中的事务都是未被提交的），所以通过起始页（0，5）下的solt 信息可以定位到回滚段，然后根据回滚段下的 undo 的 slot 定位到 undo 页，把所有的 undo 信息构建一个 undo_list，然后通过 undo_list 再创建未提交事务链表 trx_sys->trx_list。

基于上面两步，已经构建了 xid 列表和未提交事务列表，那么在这些未提交事务列表中的事务，哪些需要被提交？哪些又该回滚？

判断条件很简单：凡是 xid 在通过 binlog 构建的xid列表中存在的事务，都需要被提交。换句话说，所有已经记录 binlog 的事务，需要被提交，而剩下那些没有记录 binlog 的事务，则需要被回滚。

MySQL 外连接查询（左外连接和右外连接）

内连接

关键字：inner join on

语句：select * from a_table a inner join b_table b on a.a_id = b.b_id;

说明：组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分。

左(外)连接

关键字：left join on / left outer join on

语句：select * from a_table a left join b_table bon a.a_id = b.b_id;

说明：left join 是 left outer join 的简写，它的全称是左外连接，是外连接中的一种。左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。

右（外）连接

关键字：right join on / right outer join on

语句：select * from a_table a right outer join b_table b on a.a_id = b.b_id;

说明：right join 是 right outer join 的简写，它的全称是右外连接，是外连接中的一种。与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。

各种关联查询结果如图：

7种join

MySQL目前不支持全外连接。

不使用事务的话会产生死锁吗？

会。

事务产生的死锁是由于：

一个用户A 访问表 A（锁住了表 A），然后又访问表 B；另一个用户 B 访问表 B（锁住了表 B），然后企图访问表 A；这时用户 A 由于用户 B 已经锁住表B，它必须等待用户 B 释放表 B 才能继续，同样用户 B 要等用户 A 释放表 A 才能继续，这就死锁就产生了。

并发修改也可能会产生：

用户 A 查询一条纪录，然后修改该条纪录。

这时用户 B 修改该条纪录。

由于此时用户 A 的事务里锁的性质由查询的共享锁企图上升到独占锁，而用户 B 里的独占锁由于 A 有共享锁存在，所以必须等 A 释放掉共享锁，而 A 由于 B 的独占锁而无法上升的独占锁也就不可能释放共享锁，于是出现了死锁。这种死锁由于比较隐蔽，但在稍大点的项目中经常发生。

数据库乐观锁悲观锁具体是什么，写一个典型的乐观锁SQL语句，有什么需要注意的问题？

  • 乐观锁：每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。乐观锁适用于读多写少的应用场景，这样可以提高吞吐量。它假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。
  • 悲观锁：每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会 block 直到它拿到锁。它假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。

乐观锁一般来说有以下2种方式：

  • 使用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 version 字段来实现。当读取数据时，将 version 字段的值一同读出，数据每更新一次，对此 version 值加一。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的 version 值进行比对，如果数据库表当前版本号与第一次取出来的 version 值相等，则予以更新，否则认为是过期数据。
  • 使用时间戳（timestamp）。乐观锁定的第二种实现方式和第一种差不多，同样是在需要乐观锁控制的 table 中增加一个字段，名称无所谓，字段类型使用时间戳（timestamp）, 和上面的 version 类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突。

悲观锁和乐观锁选用：

  1. 悲观锁使用了排他锁，当程序独占锁时，其他程序就连查询都是不允许的，导致吞吐较低。如果在查询较多的情况下，可使用乐观锁。
  2. 乐观锁更新有可能会失败，甚至是更新几次都失败，这是有风险的。所以如果写入较频繁，对吞吐要求不高，可使用悲观锁。

也就是一句话：读用乐观锁，写用悲观锁。

如何设计才可以让系统从未分库分表动态切换到分库分表上？

停机迁移方案

有一个实现比较简单的方案，大家伙儿凌晨 12 点开始运维，网站或者 app 挂个公告，说 0 点到早上 6 点进行运维，无法访问。

接着到 0 点停机，系统停掉，没有流量写入了，此时老的单库单表数据库静止了。然后之前得写好一个导数据的一次性工具，此时直接跑起来，然后将单库单表的数据哗哗哗读出来，写到分库分表里面去。

导数完了之后，就 ok 了，修改系统的数据库连接配置啥的，包括可能代码和 SQL 也许有修改，那你就用最新的代码，然后直接启动连到新的分库分表上去。

验证一下就 OK 了。

停机迁移方案

双写迁移方案

简单来说，就是在线上系统里面，之前所有写库的地方，增删改操作，除了对老库增删改，都加上对新库的增删改，这就是所谓的双写，同时写俩库，老库和新库。

然后系统部署之后，新库数据差太远，用之前说的导数工具，跑起来读老库数据写新库，写的时候要根据 gmt_modified 这类字段判断这条数据最后修改的时间，除非是读出来的数据在新库里没有，或者是比新库的数据新才会写。简单来说，就是不允许用老数据覆盖新数据。

导完一轮之后，有可能数据还是存在不一致，那么就程序自动做一轮校验，比对新老库每个表的每条数据，接着如果有不一样的，就针对那些不一样的，从老库读数据再次写。反复循环，直到两个库每个表的数据都完全一致为止。

接着当数据完全一致了，就 ok 了，基于仅仅使用分库分表的最新代码，重新部署一次，不就仅仅基于分库分表在操作了么，还没有几个小时的停机时间，很稳。所以现在基本数据迁移之类的，都是这么干的。

双写迁移方案

DELETE 和 DROP 区别

delete 删除表的数据，但保留表的结构。
drop 直接把整个表删除，执行完 drop 之后，表不存在。

exist 和 in 区别

exists和in的执行过程：

in

sql示例：

select * from tabA where tabA.x in (select x from tabB where y>0 );
  • 执行过程：

（1）执行tabB表的子查询，得到结果集B，可以使用到 tabB 表的索引 y；

（2）执行tabA表的查询，查询条件是tabA.x在结果集B里面，可以使用到tabA表的索引 x。

IN() 查询是从缓存中取数据

exists

sql示例：

select from tabA where exists (select from tabB where y>0);
  • 执行过程:

（1）先将tabA表所有记录取到。

（2）逐行针对 tabA 表的记录，去关联 tabB 表，判断 tabB 表的子查询是否有返回数据，(5.5之后的版本) 使用Block Nested Loop(Block 嵌套循环)。

（3）如果子查询有返回数据，则将tabA当前记录返回到结果集。

tabA相当于取全表数据遍历，tabB可以使用到索引。

EXISTS()查询是将主查询的结果集放到子查询中做验证，根据验证结果是true或false来决定主查询数据结果是否得以保存。

执行原理

exists的执行原理：

对外表做loop循环，每次loop循环再对内表（子查询）进行查询，那么因为对内表的查询使用的索引（内表效率高，故可用大表），而外表有多大都需要遍历，不可避免（尽量用小表），故内表大的使用exists，可加快效率；

in的执行原理：

是把外表和内表做连接，先查询内表，再把内表结果与外表匹配，对外表使用索引（外表效率高，可用大表），而内表多大都需要查询，不可避免，故外表大的使用in，可加快效率。

总结来说有 4 点：

  1. in 查询时首先查询子查询的表，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选。

  2. 子查询使用 exists，会先进行主查询，将查询到的每行数据循环带入子查询校验是否存在，过滤出整体的返回数据。

  3. 两表大小相当，in 和 exists 差别不大。内表大，用 exists 效率较高；内表小，用 in 效率较高。

  4. 查询用 not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。not exists 都比 not in 要快。

数据库中视图的应用场景，数据库数据改变视图中的数据是否会改变

视图是基于 SQL 语句的结果集的可视化的表。

视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。视图并不在数据库中以存储的数据值集形式存在，而是存在于实际引用的数据库表中，视图的构成可以是单表查询，多表联合查询，分组查询以及计算(表达式)查询等。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。

视图应用场景：

  • 权限控制的时候。当用户需要查询未授权的数据表且又需要部分数据表的部分列进行逻辑处理，不希望用户访问表中某些含敏感信息的列。
  • 关键信息来源于多个复杂关联表，可以创建视图提取我们需要的信息，简化操作；
  • 简化用户操作，视图不仅可以简化用户对数据的理解，也可以简化他们的操作。

数据库中只存放了视图的定义，而没有存放视图中的数据，这些数据存放在原来的表中。

使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。

视图中的数据依赖于原来表中的数据，一旦表中数据发生改变，显示在视图中的数据也会发生改变。

当用户试图修改视图的某些信息时，数据库必须把它转化为对基本表的某些信息的修改，对于单表的视图来说，这是很方便的。但是，对于比较复杂的视图，可能是不可修改的。

MySQL 数据库机器配置规划

一般 Java 应用系统部署在 4 核 8G 的机器上，每秒抗 500 左右的并发量是 ok 的，数据库至少选用 8 核 16 G 以上的机器，一般每秒可以扛一两千并发请求。

MySQL 压力测试

测试指标

  • QPS：Query Per Second，每秒处理请求数
  • TPS：Transaction Per Second，每秒处理事务数
  • IOPS：机器每秒执行随机 IO 并发数
  • 吞吐量：机器磁盘存储每秒可以读取多少字节的数据量
  • latency：往磁盘写一条数据的延迟
  • CPU 负载
  • 网络负载
  • 内存负载

基于sysbench构造测试表和测试数据

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_read_write --db-ps-mode=disable prepare

上面构造了一个 sysbench 命令，给他加入了很多的参数，参数意思如下：

  • --db-driver=mysql：这个很简单，就是说他基于 mysql 的驱动去连接 mysql 数据库，要是oracle，或者 sqlserver，那自然就是其他的数据库的驱动了
  • --time=300：这个就是说连续访问300秒
  • --threads=10：这个就是说用10个线程模拟并发访问
  • --report-interval=1：这个就是说每隔1秒输出一下压测情况
  • --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user：这一大串，就是说连接到哪台机器的哪个端口上的MySQL库，他的用户名和密码是什么
  • --mysql-db=test_db --tables=20 --table_size=1000000：这一串的意思，就是说在test_db这个库里，构造20个测试表，每个测试表里构造100万条测试数据，测试表的名字会是类似于sbtest1，sbtest2这个样子的
  • oltp_read_write：这个就是说，执行 oltp 数据库的读写测试
  • --db-ps-mode=disable：这个就是禁止ps模式

最后有一个prepare，意思是参照这个命令的设置去构造出来我们需要的数据库里的数据，他会自动创建20个测试表，每个表里创建100万条测试数据，所以这个工具是非常的方便的。

对数据库进行全方位测试

测试数据库的综合读写 TPS，使用的是 oltp_read_write 模式（命令中最后不是 prepare，是 run 了，就是运行压测）：

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 
--mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user 
--mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 
oltp_read_write --db-ps-mode=disable run

测试数据库的只读性能，使用的是 oltp_read_only 模式（命令中的 oltp_read_write 已经变为 oltp_read_only 了）：

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 
--mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user 
--mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 
oltp_read_only --db-ps-mode=disable run

测试数据库的删除性能，使用的是 oltp_delete 模式：

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 
--mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user 
--mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 
oltp_delete --db-ps-mode=disable run

测试数据库的更新索引字段的性能，使用的是 oltp_update_index 模式：

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 
--mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user 
--mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 
oltp_update_index --db-ps-mode=disable run

测试数据库的更新非索引字段的性能，使用的是 oltp_update_non_index 模式：

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 
--mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user 
--mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 
oltp_update_non_index --db-ps-mode=disable run

测试数据库的更新非索引字段的性能，使用的是 oltp_update_non_index 模式：

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 
--mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user 
--mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 
oltp_update_non_index --db-ps-mode=disable run

测试数据库的插入性能，使用的是 oltp_insert 模式：

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 
--mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user 
--mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 
oltp_insert --db-ps-mode=disable run

测试数据库的写入性能，使用的是 oltp_write_only 模式：

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 
--mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user 
--mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 
oltp_write_only --db-ps-mode=disable run

使用上面的命令，sysbench工具会根据你的指令构造出各种各样的 SQL 语句去更新或者查询你的 20 张测试表里的数据，同时监测出你的数据库的压测性能指标，最后完成压测之后，可以执行下面的 cleanup 命令，清理数据。

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 
--mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user 
--mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 
oltp_read_write --db-ps-mode=disable cleanup

压测结果分析

按照上面的命令，是让他每隔 1 秒都会输出一次压测报告的，此时他每隔一秒会输出类似下面的一段东西：

[ 22s ] thds: 10 tps: 380.99 qps: 7312.66 (r/w/o: 5132.99/1155.86/1321.35) lat (ms, 95%): 21.33 err/s: 0.00 reconn/s: 0.00

首先他说的这是第22s输出的一段压测统计报告，然后是其他的一些统计字段：

  • thds: 10，这个意思就是有10个线程在压测
  • tps: 380.99，这个意思就是每秒执行了380.99个事务
  • qps: 7610.20，这个意思就是每秒可以执行7610.20个请求
  • (r/w/o: 5132.99/1155.86/1321.35)，这个意思就是说，在每秒7610.20个请求中，有5132.99个请求是读请求，1155.86个请求是写请求，1321.35个请求是其他的请求，就是对QPS进行了拆解
  • lat (ms, 95%): 21.33，这个意思就是说，95%的请求的延迟都在21.33毫秒以下
  • err/s: 0.00 reconn/s: 0.00，这两个的意思就是说，每秒有0个请求是失败的，发生了0次网络重连

这个压测结果会根据每个人的机器的性能不同有很大的差距，你要是机器性能特别高，那你可以开很多的并发线程去压测，比如 100 个线程，此时可能会发现数据库每秒的 TPS 有上千个，如果你的机器性能很低，可能压测出来你的 TPS 才二三十个，QPS 才几百个，这都是有可能的。

另外在完成压测之后，最后会显示一个总的压测报告，解释写在下面：

SQL statistics:

queries performed:

read: 1480084 // 这就是说在300s的压测期间执行了148万多次的读请求

write: 298457 // 这是说在压测期间执行了29万多次的写请求

other: 325436 // 这是说在压测期间执行了30万多次的其他请求

total: 2103977 // 这是说一共执行了210万多次的请求

// 这是说一共执行了10万多个事务，每秒执行350多个事务

transactions: 105180( 350.6 per sec. )

// 这是说一共执行了210万多次的请求，每秒执行7000+请求

queries: 2103977 ( 7013.26 per sec. )

ignored errors: 0 (0.00 per sec.)

reconnects: 0 (0.00 per sec.)


// 下面就是说，一共执行了300s的压测，执行了10万+的事务

General staticstics:

total time: 300.0052s

total number of events: 105180


Latency (ms):

min: 4.32 // 请求中延迟最小的是4.32ms

avg: 13.42 // 所有请求平均延迟是13.42ms

max: 45.56 // 延迟最大的请求是45.56ms

95th percentile: 21.33 // 95%的请求延迟都在21.33ms以内

编写 sql 语句

  1. 统计数据表中某一列出现的次数，并按从大到小排列，sql 语句怎么写？
SELECT name,count(name) FROM t_table GROUP BY name;
  1. 给你一整个小学的数据，一条命令返回每个年级每个班的总人数
SELECT COUNT(number),`class`, `grade` FROM student GROUP BY class,grade;

count（*），count（字段名），count（1） 区别？

执行效果上：  

  • count(*) 包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL  
  • count(1) 包括了忽略所有列，用 1 代表代码行，在统计结果的时候，不会忽略列值为NULL  
  • count(列名) 只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是指空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。

执行效率上：  

  • 列名为主键，count(列名) 会比 count(1)快  
  • 列名不为主键，count(1) 会比 count(列名) 快  
  • 如果表多个列并且没有主键，则 count（1） 的执行效率优于 count（*）  
  • 如果有主键，则 select count（主键）的执行效率是最优的  
  • 如果表只有一个字段，则 select count（*）最优。

Varchar 的最大长度以及超长问题

4.0版本以下，varchar(20)，指的是20字节，如果存放UTF8汉字时，只能存6个（每个汉字3字节）

5.0版本以上，varchar(20)，指的是20字符，无论存放的是数字、字母还是UTF8汉字（每个汉字3字节），都可以存放20个，最大大小是65532字节

varchar 最多能存储 65535 个字节的数据。varchar 的最大长度受限于最大行长度（max row size，65535 bytes）。65535 并不是一个很精确的上限，可以继续缩小这个上限。65535 个字节包括所有字段的长度，变长字段的长度标识（每个变长字段额外使用 1 或者 2 个字节记录实际数据长度）、NULL 标识位的累计。

NULL 标识位，如果 varchar 字段定义中带有 default null 允许列空,则需要需要 1bit 来标识，每 8 个 bits 的标识组成一个字段。一张表中存在 N 个 varchar 字段，那么需要（N+7）/8 （取整） bytes 存储所有的 NULL 标识位。

因为varchar类型存储变长字段的字符类型，其存储时需要在前缀长度列表加上实际存储的字符，当存储的字符串长度小于255字节时，其需要 1 字节的空间，当大于 255 字节时，需要 2 字节的空间。

如果数据表只有一个 varchar 字段且该字段 DEFAULT NULL 并且大于 255 字节，那么该 varchar 字段的最大长度为 65532 个字节，即 65535-2-1=65532 byte。

所以具有以下规则：

编码长度限制：

  • 字符类型若为gbk，每个字符最多占 2个字节，最大长度不能超过32766;
  • 字符类型若为utf8，每个字符最多占 3 个字节，最大长度不能超过21845。
  • 若定义的时候超过上述限制，则 varchar 字段会被强行转为 text 类型，并产生 warning。

行长度限制：

导致实际应用中 varchar 长度限制的是一个行定义的长度。 MySQL 要求一个行的定义长度不能超过 65535。若定义的表长度超过这个值，则提示

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs。

内存临时表的分配/Group By 语句优化

假设现在有一张用户浏览好友记录的明细表 t_user_view，该表的表结构如下：

CREATE TABLE `t_user_view` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户id',
  `viewed_user_id` bigint(20) DEFAULT NULL COMMENT '被查看用户id',
  `viewed_user_sex` tinyint(1) DEFAULT NULL COMMENT '被查看用户性别',
  `viewed_user_age` int(5) DEFAULT NULL COMMENT '被查看用户年龄',
  `create_time` datetime(3) DEFAULT CURRENT_TIMESTAMP(3),
  `update_time` datetime(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_viewed_user` (`user_id`,`viewed_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

现在有一个需求是这样子：对用户过去查看好友的性别和年龄进行统计，按照年龄进行分组得到统计结果。依据该结果，给用户推荐计数最高的某个性别及年龄的好友。

现在结合上面的表结构和表记录，以user_id=1的用户为例，分组统计该用户查看的年龄在18 ~ 22之间的女性用户的数量，sql 如下：

SELECT viewed_user_age as age, count(*) as num FROM t_user_view WHERE user_id = 1 AND viewed_user_age BETWEEN 18 AND 22 AND viewed_user_sex = 1 GROUP BY viewed_user_age

最后可以得到一个类似下表的结果：

age num
18 2
19 1
20 3

可见：

  • 该用户查看年龄为18的女性用户数为2
  • 该用户查看年龄为19的女性用户数为1
  • 该用户查看年龄为20的女性用户数为3

所以，user_id=1 的用户对年龄为 20 的女性用户更感兴趣，可以更多推荐20岁的女性用户给他。

针对上面的 sql，如果此时，t_user_view 这张表的记录数达到千万规模，这条SQL的查询效率会直线下降，为什么呢？原因跟 MySQL 的内存分配有关，通过 Explain 查看：

EXPLAIN SELECT viewed_user_age as age, count(*) as num FROM t_user_view WHERE user_id = 1 AND viewed_user_age BETWEEN 18 AND 22 AND viewed_user_sex = 1 GROUP BY viewed_user_age

group by sql explain结果

在 Extra 这一列中出现了三个 Using，这3个 Using 代表了 groupBy 语句分别经历了3个执行阶段：

  1. Using where：通过搜索可能的idx_user_viewed_user索引树定位到满足部分条件的viewed_user_id，然后，回表继续查找满足其他条件的记录
  2. Using temporary：使用临时表暂存待 groupBy 分组及统计字段信息
  3. Using filesort：使用 sort_buffer 对分组字段进行排序

这里首先需要了解一下临时表。

临时表

上面这条包含groupBy语句的SQL，其中包含一个分组字段viewed_user_age和一个统计字段count(*)，这两个字段是这条SQL中统计所需的部分，如果要做这样一个统计和分组，并把结果固化下来，肯定是需要一个内存或磁盘区域落下第一次统计的结果，然后，以这个结果做下一次的统计，因此，像这种存储中间结果，并以此结果做进一步处理的区域，MySQL叫它临时表。

刚刚提到既可以将中间结果落在内存，也可以将这个结果落在磁盘，因此，在 MySQL 中就出现了两种临时表：内存临时表和磁盘临时表。

内存临时表

什么是内存临时表？在早期数据量不是很大的时候，以存储分组及统计字段为例，那么，基本上内存就可以完全存放下分组及统计字段对应的所有值，这个存放大小由 tmp_table_size 参数决定。这时候，这个存放值的内存区域，MySQL就叫它内存临时表。

此时，或许觉得MySQL将中间结果存放在内存临时表，性能已经有了保障，内存频繁的存取会产生碎片，为此，MySQL设计了一套新的内存分配和释放机制，可以减少甚至避免临时表内存碎片，提升内存临时表的利用率。

MySQL 的进程内存分配使用的是用户态的内存分配器：ptmalloc和tcmalloc，无论是哪个分配器，它的作用就是避免用户进程频繁向Linux内核申请内存空间，造成CPU在用户态和内核态之间频繁切换，从而影响内存存取的效率。用它们就可以解决内存利用率的问题，为什么MySQL还要自己搞一套？

或许MySQL的作者觉得无论哪个内存分配器，它的实现都过于复杂，这些复杂性会影响MySQL对于内存处理的性能，因此，MySQL自身又实现了一套内存分配机制：MEM_ROOT。它的内存处理机制相对比较简单，内存临时表的分配就是采用这样一种方式。

下面分析一下分组统计是如何使用MEM_ROOT内存分配和释放机制的？

MEM_ROOT

先看看MEM_ROOT的结构，MEM_ROOT设计比较简单，主要包含这几部分，如下图：

MEM_ROOT结构

  • free：一个单向链表，链表中每一个单元叫block，block中存放的是空闲的内存区，每个block包含3个元素：
    • left：block中剩余的内存大小
    • size：block对应内存的大小
    • next：指向下一个block的指针

如上图，free所在的行就是一个free链表，链表中每个箭头相连的部分就是block，block中有left和size，每个block之间的箭头就是next指针

  • used：一个单向链表，链表中每一个单元叫block，block中存放已使用的内存区，同样，每个block包含上面3个元素

  • min_malloc：控制一个 block 剩余空间还有多少的时候从free链表移除，加入到used链表中

  • block_size：block对应内存的大小

  • block_num：MEM_ROOT 管理的block数量

  • first_block_usage：free链表中第一个block不满足申请空间大小的次数

  • pre_alloc：当释放整个MEM_ROOT的时候可以通过参数控制，选择保留pre_alloc指向的block

以上面的分组统计SQL为例，看一下MEM_ROOT是如何分配内存的？

分配

MEM_ROOT 分配

具体的步骤如下：

初始化MEM_ROOT，见上图：

min_malloc = 32
block_num = 4
first_block_usage = 0
pre_alloc = 0
block_size = 1000
err_handler = 0
free = 0
used = 0

申请内存，见上图：

由于初始化MEM_ROOT时，free = 0，说明free链表不存在，故向Linux内核申请4个大小为1000/4=250的block，构造一个free链表，如上图，链表中包含4个block ，结合前面free链表结构的说明，每个block中size为250，left也为250

分配内存，见上图：

(1) 遍历free链表，从free链表头部取出第一个block，如上图向下的箭头

(2) 从取出的block中划分220大小的内存区，如上图向右的箭头上面-220，block中的left从250变成30

(3) 将划分的220大小的内存区分配给SQL中的groupby字段viewed_user_age和统计字段count(*)，用于后面的统计分组数据收集到该内存区

(4) 由于第(2)步中，分配后的block中的left变成30，30 < 32，即小于第(1)步中初始化的min_malloc，所以，结合上面min_malloc的含义的讲解，该block将插入used链表尾部，如上图底部，由于used链表在第(1)步初始化时为0，所以，该block插入used链表的尾部，即插入头部。

释放

再来看下上述的 SQL 如何释放内存临时表：

MEM_ROOT释放

如上图，MEM_ROOT释放内存的过程如下：

  1. 遍历used链表中，找到需要释放的block，如上图，block(30,250)为之前已分配给分组统计用的block
  2. 将block(30,250)中的left + 220，即30 + 220 = 250，释放该block已使用的220大小的内存区，得到释放后的block(250,250)
  3. 将block(250,250)插入free链表尾部，如上图曲线箭头部分

通过MEM_ROOT内存分配和释放的，我们发现MEM_ROOT的内存管理方式是在每个Block上连续分配，内部碎片基本在每个Block的尾部，由min_malloc成员变量控制，但是min_malloc的值是在代码中写死的，有点不够灵活。所以，对一个block来说，当left小于min_malloc，从其申请的内存越大，那么block中的left值越小，那么，该block的内存利用率越高，碎片越少，反之，碎片越多。这个写死是MySQL的内存分配的一个缺陷。

磁盘临时表

当分组及统计字段对应的所有值大小超过tmp_table_size决定的值，那么，MySQL将使用磁盘来存储这些值。这个存放值的磁盘区域，MySQL叫它磁盘临时表。

我们都知道磁盘存取的性能一定比内存存取的性能差很多，因为会产生磁盘IO，所以，一旦分组及统计字段不得不写入磁盘，那性能相对是很差的，所以，尽量调大参数tmp_table_size，使得组及统计字段可以在内存临时表中处理。

执行过程

无论是使用内存临时表，还是磁盘临时表，临时表对组及统计字段的处理的方式都是一样的。下面就结合上面临时表的概念，详细讲讲这条SQL的执行过程，见下图：

group by 语句执行

  1. 创建临时表temporary，表里有两个字段viewed_user_age和count(*)，主键是viewed_user_age，如上图，倒数第二个框temporary表示临时表，框中包含两个字段viewed_user_age和count(*)，框内就是这两个字段对应的值，其中viewed_user_age就是这张临时表的主键

  2. 扫描表辅助索引树idx_user_viewed_user，依次取出叶子节点上的id值，即从索引树叶子节点中取到表的主键id。如上图中的idx_user_viewed_user框就是索引树，框右侧的箭头表示取到表的主键id

  3. 根据主键id到聚簇索引cluster_index的叶子节点中查找记录，即扫描cluster_index叶子节点：

    (1) 得到一条记录，然后取到记录中的viewed_user_age字段值。如上图，cluster_index框，框中最右边的一列就是viewed_user_age字段的值

    (2) 如果临时表中没有主键为viewed_user_age的行，就插入一条记录 (viewed_user_age, 1)。如上图的temporary框，其左侧箭头表示将cluster_index框中的viewed_user_age字段值写入temporary临时表

    (3) 如果临时表中有主键为viewed_user_age的行，就将viewed_user_age这一行的count(*)值加 1。如上图的temporary框

  4. 遍历完成后，再根据字段viewed_user_age在sort_buffer中做排序，得到结果集返回给客户端。如上图中的最右边的箭头，表示将temporary框中的viewed_user_age和count(*)的值写入sort_buffer，然后，在sort_buffer中按viewed_user_age字段进行排序

通过SQL的执行过程，可以发现该过程经历了4个部分：idx_user_viewed_user、cluster_index、temporary和sort_buffer，对比上面explain的结果，其中前2个就对应结果中的Using where，temporary对应的是Using temporary，sort_buffer对应的是Using filesort。

优化方案

有什么办法优化这条SQL呢？

既然这条SQL执行需要经历4个部分，那么，可不可以去掉最后两部分呢，即去掉temporary和sort_buffer？

答案是可以的，只要给SQL中的表t_user_view添加如下索引：

ALTER TABLE `t_user_view` ADD INDEX `idx_user_age_sex` (`user_id`, `viewed_user_age`, `viewed_user_sex`);

新增索引，避免临时表对分组字段的统计，及sort_buffer对分组和统计字段排序，因为数据已经索引树里，就不用再去回表了。

当然，如果实在无法避免使用临时表，那么，尽量调大tmp_table_size，避免使用磁盘临时表统计分组字段。

如何高效的实现大量数据插入

批处理：批量提交 SQL 语句可以降低网络传输和处理开销，减少与数据库交互的次数。

  • 在循环插入时带有适当的等待时间和批处理大小，从而避免内存占用过高等问题：

  • 设置适当的批处理大小：批处理大小指在一次插入操作中插入多少行数据。如果批处理大小太小，插入操作的频率将很高，而如果批处理大小太大，可能会导致内存占用过高。通常，建议将批处理大小设置为1000-5000行，这将减少插入操作的频率并降低内存占用。

  • 采用适当的等待时间：等待时间指在批处理操作之间等待的时间量。等待时间过短可能会导致内存占用过高，而等待时间过长则可能会延迟插入操作的速度。通常，建议将等待时间设置为几秒钟到几十秒钟之间，这将使操作变得平滑且避免出现内存占用过高等问题。

  • 可以考虑使用一些内存优化的技巧，例如使用内存数据库或使用游标方式插入数据，以减少内存占用。

  • 总的来说，选择适当的批处理大小和等待时间可以帮助平稳地进行插入操作，避免出现内存占用过高等问题。

索引： 在大量数据插入前暂时去掉索引，最后再打上，这样可以大大减少写入时候的更新索引的时间。

数据库连接池：使用数据库连接池可以减少数据库连接建立和关闭的开销，提高性能。在没有使用数据库连接池的情况，记得在finally中关闭相关连接。

数据库参数调整：增加MySQL数据库缓冲区大小、配置高性能的磁盘和I/O等。

参考内容

主要参考以来两篇博客以及相关博客推荐，因找的博客比较多，没注意记录，最后好多忘了在哪2333，如果有侵权，请及时联系我，非常抱歉。
https://github.com/Snailclimb/JavaGuide

https://github.com/CyC2018/CS-Notes

MySQL 乐观锁与悲观锁

MySQL悲观锁&乐观锁

数据库的3大范式和5大约束

中华石杉–互联网Java进阶面试训练营

MySQL的视图讲解

MySQL优化笔记（七）–视图应用详解

廖雪峰的官方网站

MySQL InnoDB MVCC 机制的原理及实现

付费专栏：从零开始带你成为MySQL实战优化高手

count(1)、count(*)与count(列名)的执行区别

MySQL中varchar最大长度是多少？

MySQL大表优化方案

MySQL中IS NULL、IS NOT NULL、!=不能用索引？胡扯

MySQL锁总结

一文理解Mysql MVCC

MySQL InnoDB MVCC实现

InnoDB一棵B+树可以存放多少行数据？

顺丰快递：请签收MySQL灵魂十连

告诉面试官，我能优化groupBy，而且知道得很深！

MySQL 8.0 Reference Manual-8.8 Understanding the Query Execution Plan

MySQL explain 应用详解(吐血整理🤩)

全网最全 | MySQL EXPLAIN 完全解读

MySQL中的IN与EXISTS

腾讯工程师带你深入解析 MySQL binlog

MySQL Binlog日志

【MySQL】主从复制实现原理详解

第26期：索引设计（索引下推）

Mysql性能优化：什么是索引下推？

MySQL 的索引为什么使用 B+ 树而不使用跳表？

13 秒插入 30 万条数据，这才是批量插入正确的姿势！