Mysql常用总结
这里做一些MySql日常开发中经常用到的知识点进行总结，记录一下方便回忆以及查看。
具体架构
知道了具体的架构我们才能更好的使用该组件。老马🐴说过一句话:“没有基础研究的应用创新，就像沙滩上建高楼，越高越危险。应用创新的大厦每向高建一寸，基础研究的地基就要向深挖一尺。我们不能抱有任何侥幸心理，一定要投入更多资源去探索、去尝试。” 一切都得从最基础的出发。
Mysql主要分为两层
- Server层：覆盖的组件如图所示，所有跨存储引擎功能大部分都在这一层实现，例如存储过程、触发器、视图等。
- 存储引擎层负责数据的存储和提取。其架构模式是属于插件化的，可以使用InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用最热门的就是InnoDB了。
Innodb存储
Innodb存储引擎表是索引组织表，即表中的数据按照主键顺序存放。聚集索引就是按照每张表的主键构造一棵B+树，叶子节点存放的是表的完整行记录。非聚集索引的叶子节点不包含行记录的全部数据。Innodb存储引擎的非聚集索引的叶子节点的内容为主键索引值。这里内容也比较多，以后再展开分析。
InnoDB 使用 Redo Log 来保证数据的一致性和可持久性，它采用 WAL 机制，即先写日志再写数据。具体来说，InnoDB 进行写操作时，先将数据操作记录在 log buffer 中，然后将 log buffer 中的数据刷到磁盘 log file 中，后续数据再落到数据 ibd 文件这一步骤由 checkpoint 来保证。
其大致内存存储结构为
Buffer Pool：缓冲池，一种常见的降低磁盘访问的机制以，页(page)为单位缓存数据，采用类LRU的算法来管理。使用了新老代的方式来避免热数据被大量淘汰。
Change Buffer：主要节省的则是随机读磁盘的 IO 消耗
Log Buffer：重做日志缓冲
其大致磁盘存储结构为
表结构——.frm文件：
与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等。
独享表空间——.ibd文件：
独享表空间存储方式，存储nnoDB表的数据和索引，并且每个表一个ibd文件
共享表空间——.ibdata文件：
共享表空间存储方式，存储nnoDB表的数据和索引，所有表共同使用一个ibdata文件
具体实现查看书籍《MySQL技术内幕》
InnoDB索引
索引对优化SQL慢查询有着很大的帮助，索引是数据库表中一列或多列的值进行排序的一种数据结构，使用索引可快速访问数据库表中的特定信息。
Innodb存储引擎支持B+树索引、全文索引和哈希索引。其中Innodb存储引擎支持的哈希索引是自适应的，Innodb存储引擎会根据表的使用情况自动为表生成哈希索引，不能人为干预。相比于MyISAM索引，使用一个b+树的叶子节点来保存真实数据的地址，主索引表和辅助索引表都是差不多的，Innodb索引采用的是主索引（clustered index）在b+树的叶子节点保存表内的所有真实数据，而辅助索引在b+数的叶子节点保存的是数据的主键，当我们用辅助索引（secondary index）去查表的时候，是找到该数据所在的主键key然后再去主索引的b+树中直接找到真实的数据。这个过程称为回表。
虽然索引可用来加快查询，但是这也增加了我们在插入和更新数据的时候维护整个索引的代价。所以在我们真正开发的时候需要权衡利弊。
说到索引，必须提的一个神器就是explain，MySQL的查询优化器会基于成本和优化规则生成一个"执行计划"，这个执行计划就是MySQL接下来会执行的查询方式，比如是否要走索引查询，走哪个索引，多表连接时连接顺序是怎样的。MySQL提供了"Explain"关键字，让我们可以查看一条SQL的执行计划，它是优化查询具体语句效率和查找慢查询问题的利器。
redo log和undo log、bin log
redo log是InnoDB独有的它属于物理日志，直接记录了在数据页上做了什么修改。它可以用来确保数据库的持久性，就是写入进Mysql了就会被永久的保存下来，其实一开始写入是写入在内存里的，内存再用redo log一页一页的写入磁盘，以确保性能。可以用它来恢复数据，这里就用到了checkpoint，即是当前要擦除并更新到数据文件的位置的位置，还有一个write pos(当前记录的位置)，新数据是从这里开始写，一边写一边后移。有三种情况的时候我们就可以把数据从redo log 写进数据文件。1. 系统空闲时 2. redo log没有空闲时 3. MySQL正常关闭时。
更新操作失败并回滚的情况是很常见的，所以需要特别关注这种情况，Undo Log 就是用来解决这个问题。Undo Log 记录的是当一个更新操作失败需要回滚时，应该进行哪些反向操作。即当你 insert 一条记录时，Undo log中会记录一条对应的 delete 记录，反之亦然。
binlog是Server层级别的所有引擎都可以使用，用于记录数据库执行的写入性操作(不包括查询)信息，是逻辑日志。以二进制的形式保存在磁盘中。binlog 是 mysql的逻辑日志，并且由 Server 层进行记录，使用任何存储引擎的 mysql 数据库都会记录 binlog 日志。它还可以用来做到高可用，实现多副本或跨区跨地域容灾，Binlog 支持 Statement，Row 和 Mixed 三种模式。其中， Row 模式会记录每行数据的修改操作，相比 Statement 模式，它能保证主从复制的正确性。
事务
下面说说事物，事物是用来处理一一系列多个操作的一个操作流水，事务是逻辑上的一组操作，要么都执行，要么都不执行。
事务最经典也经常被拿出来说例子就是转账了。假如小明要给小红转账1000元，这个转账会涉及到两个关键操作就是：将小明的余额减少1000元，将小红的余额增加1000元。万一在这两个操作之间突然出现错误比如银行系统崩溃，导致小明余额减少而小红的余额没有增加，这样就不对了。事务就是保证这两个关键操作要么都成功，要么都要失败。
事务的四大特性：
-
原子性 多数据操作 要么全部完成要么全部是失败。
-
一致性 事务应确保数据库的状态从一个一致状态转变为另一个一致状态，一致状态的含义是数据库中的数据应满足完整性约束。
-
隔离性 多个事务同时执行，防止多个事务并发执行引起交叉执行数据不一致，该操作受隔离级别影响。
-
持久性 事务处理完，数据的修改是永久的。
并发事务带来的问题
在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对统一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题。
- 脏读（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）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。
不可重复度和幻读区别：
不可重复读的重点是修改，幻读的重点在于新增或者删除。
例1（同样的条件, 你读取过的数据, 再次读取出来发现值不一样了 ）：事务1中的A先生读取自己的工资为 1000的操作还没完成，事务2中的B先生就修改了A的工资为2000，导 致A再读自己的工资时工资变为 2000；这就是不可重复读。
例2（同样的条件, 第1次和第2次读出来的记录数不一样 ）：假某工资单表中工资大于3000的有4人，事务1读取了所有工资大于3000的人，共查到4条记录，这时事务2 又插入了一条工资大于3000的记录，事务1再次读取时查到的记录就变为了5条，这样就导致了幻读。
事务隔离级别
SQL 标准定义了四个隔离级别：
- READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
- READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
- REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
- SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。
| 隔离级别 | 脏读 | 不可重复读 | 幻影读 |
|---|---|---|---|
| READ-UNCOMMITTED | √ | √ | √ |
| READ-COMMITTED | × | √ | √ |
| REPEATABLE-READ | × | × | √ |
| SERIALIZABLE | × | × | × |
MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。我们可以通过SELECT @@tx_isolation;命令来查看,MySQL 8.0 该命令改为SELECT @@transaction_isolation;
mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
这里需要注意的是：与 SQL 标准不同的地方在于InnoDB 存储引擎在 REPEATABLE-READ（可重读） 事务隔离级别下，允许应用使用 Next-Key Lock 锁算法来避免幻读的产生。这与其他数据库系统(如 SQL Server)是不同的。所以说虽然 InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读） ，但是可以通过应用加锁读（例如 select * from table for update 语句）来保证不会产生幻读，而这个加锁度使用到的机制就是 Next-Key Lock 锁算法。从而达到了 SQL 标准的 SERIALIZABLE(可串行化) 隔离级别。
因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是READ-COMMITTED(读取提交内容):，但是你要知道的是InnoDB 存储引擎默认使用 REPEATABLE-READ（可重读） 并不会有任何性能损失。
InnoDB 存储引擎在 分布式事务 的情况下一般会用到SERIALIZABLE(可串行化) 隔离级别。
实际情况演示
在下面我会使用 2 个命令行mysql ，模拟多线程（多事务）对同一份数据的脏读问题。
MySQL 命令行的默认配置中事务都是自动提交的，即执行SQL语句后就会马上执行 COMMIT 操作。如果要显式地开启一个事务需要使用命令：START TARNSACTION。
我们可以通过下面的命令来设置隔离级别。
SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE]
我们再来看一下我们在下面实际操作中使用到的一些并发控制语句:
START TARNSACTION|BEGIN：显式地开启一个事务。COMMIT：提交事务，使得对数据库做的所有修改成为永久性。ROLLBACK：回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。
脏读(读未提交)
避免脏读(读已提交)
不可重复读
还是刚才上面的读已提交的图，虽然避免了读未提交，但是却出现了，一个事务还没有结束，就发生了 不可重复读问题。
可重复读
防止幻读(可重复读)
一个事务对数据库进行操作，这种操作的范围是数据库的全部行，然后第二个事务也在对这个数据库操作，这种操作可以是插入一行记录或删除一行记录，那么第一个是事务就会觉得自己出现了幻觉，怎么还有没有处理的记录呢? 或者 怎么多处理了一行记录呢?
幻读和不可重复读有些相似之处 ，但是不可重复读的重点是修改，幻读的重点在于新增或者删除。
锁机制
数据库锁机制是为了保证数据的一致性而使各种共享资源在被并发访问时有序所设计的一种规则。MySQL数据库由于存在多种数据库引擎，每种存储引擎针对的应用场景特点不一样，对应的锁定机制也不一样。
InnoDB存储引擎支持：行级锁、表级锁，但主要使用行级锁，当无法通过索引键加锁就会升级为表锁定。
1.行级锁
特点：锁定对象颗粒度最小，发生锁资源争用的概率最小，能够给予应用程序尽可能大的并发处理能力
问题：由于锁定资源颗粒度最小，每次获取和释放锁需要做的事情，带来的消耗更大，也最容易发生死锁
InnoDB
2.表级锁
特点：锁定对象颗粒度最大的锁定机制，实现逻辑简单，获取和释放锁的的速度最快，由于一次锁定整张表，可以避免死锁的问题
问题：锁资源争抢的概率最高，影响并发度
视图
视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。
视图的最常见的应用之一是隐藏复杂的SQL，也就是利用视图简化复杂的联结。还有用视图重新格式化检索出的数据。以及用视图过滤不想要的数据等。基本上都是用来简化查询操作的。
其他不常用功能
存储过程：这个是数据库语句的面向对象的设计，可以加速我们对数据库的一些操作。但是使用的人比较少，而且还有很多公司的开发手册，直接禁用了存储过程。这里提出几个原因：
- SQl有标准，也就是说兼容大部分，存储过程没有标准导致了导致各厂商数据库语法差异大，有强绑定行，迁移上有很大的困难；
- 早期使用存储过程，在带宽资源等都紧张的情况，是一个比较优的选择；
- 现在的大部分系统设计都遵循胖前端，瘦后端，DB尽只用于存储数据和提供基本的读写能力，业务逻辑都上提到应用层实现，这样导致了存储过程用的越来越少；
触发器：触发器功能是在mysql5.0.2版本中引入的，triggername.TRN文件存储触发器的定义，触发器可以看作是一种特殊的存储过程，触发器是特定事件触发的（Insert、Update、Delete），触发的时间在事件发生的前后（Before、after），mysql5.7版本以前同一个数据表对于一个事件只能添加一个触发器；5.7.2版本以后可以在同一个表的同一个动作上创建多个触发器.其语法如下（具体请参考https://dev.mysql.com/doc/refman/5.7/en/create-trigger.html）
这个是用来执行多个表中的数据链接用的，加入你想在一个表中操作的同时，也会触发另一个表也会自动执行一些操作，这个时候就可以用到触发器。但是这个功能也存在一些问题，了解了触发器的原理以后，我们就会发现其实触发器极其不适合表更新操作量大的场景，数据量非常大了之后请求执行后由于触发器的基于行触发给db造成上百倍的压力。网上搜索大家也都不建议在生产环境中使用触发器，能够使用存储过程不用触发器，谈触色变，看来大家都中毒颇深。
优化相关
发现问题：使用Explain分析SQL语句执行计划。
解决问题：最关键：索引，避免全表扫描。
从其他层面进行优化：分库分表(量到100万以上需做)、使用集群（主-从）增加可用性，读写分离分摊主库的压力、增加业务的缓存层(提高查询效率)、使用连接池(连接复用，避免频繁的建立、关闭连接减少并发量)。
主从数据同步：关键点是Binlog，可以用binlog来复制数据保持主从同步。
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
