MySQL 是一种开源的关系型数据库，也是目前 Java 开发中非常常用的关系型数据库。其默认端口号是 3306。
语句是如何在 MySQL 中执行的
graph LR
A(连接器)-->B(查询缓存)-->C(分析器)-->D(优化器)-->E(执行器)
- 连接器：身份认证和权限相关(登录 MySQL 的时候)。
- 查询缓存：执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除）。
- 分析器：没有命中缓存的话，SQL 语句就会经过分析器，分析器就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
- 优化器：按照 MySQL 认为最优的方案去执行。
- 执行器：执行语句，然后从存储引擎返回数据。
存储引擎
MySQL 最常用的两种存储引擎是 MyISAM 和 InnoDB ，在5.7版本的所有存储引擎中只用 InnoDB是支持事务的。MyISAM 在 5.5 版本之前都是 MySQL 的默认数据库引擎，但是 5.5 版本之后 MySQL 加入了 InnoDB 引擎，并且将其设为默认引擎。这两者主要有以下区别：
| MyISAM | InnoDB | |
|---|---|---|
| 行级锁 | 不支持，只支持表级锁 | 支持表级锁及行级锁 |
| 事务及崩溃后的安全恢复 | 不支持，但是速度更快 | 支持 |
| 外键 | 不支持 | 支持 |
| MVCC* | 不支持 | 支持 |
| 索引类型 | 非聚簇索引 | 聚簇索引 |
*MVCC：Multi-Version Concurrency Control，即多版本并发控制，就是要做到维持一个数据的多个版本，使得在不加锁的情况下读写操作没有冲突。
索引
1. B+树
MySQL 使用B+树作为存储的数据结构，B-树是一种多路自平衡搜索树，而B+树相对于B-树的特点是：
- 所有关键字存储在叶子节点出现。非叶子节点只存了索引值，只在最后一行才存放了行记录，这样极大地减小了索引大小。
- 为所有叶子结点增加了一个链指针。加强了区间访问性，可使用范围区间查找。
2. 聚簇索引与非聚簇索引
2.1 聚簇索引
-
聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。
-
聚簇索引就是按照每张表的主键构造一颗B+树，叶子节点中存放的是包含主键在内的整条行记录数据。这个特性决定了表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。
-
如果使用聚簇索引，则通过其他辅助索引进行搜索时，辅助索引B+树叶子节点存放的是主键值，再通过主键值的聚簇索引B+树找到数据行。
-
聚簇索引会改变表中行的物理顺序，数据行按照一定的顺序排列，并且自动维护这个顺序。
2.2非聚簇索引
在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行。
2.3 聚簇索引的优势
-
由于行数据和聚簇索引的叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了缓存中，再次访问时，会在内存中完成访问，不必访问磁盘。
-
辅助索引的叶子节点，存储主键值，而不是数据的存放地址。因此当行数据发生变化时，可以避免对辅助索引的维护工作，只需要维护聚簇索引树就好了；另一个好处是，因为辅助索引存放的是主键值，减少了辅助索引占用的存储空间大小。
-
因为MyISAM的主索引并非聚簇索引，那么他的数据的物理地址必然是凌乱的，拿到这些物理地址，按照合适的算法进行I/O读取，于是开始不停的寻道不停的旋转。聚簇索引则只需一次I/O。
不过，如果涉及到大数据量的排序、全表扫描、计数之类的操作的话，还是MyISAM占优势些，因为索引所占空间小，这些操作是需要在内存中完成的。
2.4 聚簇索引主键使用自增id
聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，它会不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但如果是自增的，那就简单了，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。
事务
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
1. 事务的四大特性
一般来说，事务是必须满足4个条件（ACID）：
1.1 原子性（Atomicity）
一个事务中的所有操作是不可被再分割的最小执行单位，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
1.2 一致性（Consistency）
在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的 预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。(比如：A向B 转账，不可能A扣了钱，B却没有收到）
1.3 隔离性（Isolation）
数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由 于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读 提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。（比如：A正 在从一张银行卡里面取钱，在A取钱的过程中，B不能向这张银行卡打钱）
1.4 持久性（Durability）
事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
2. 事务的实现原理
mysql 每执行一条语句记录一条日志:
- start transaction，先记个日志，真正执行执行。
UPDATE user set balance = balance - 200 where id = 1，先记个日志，真正执行。 如果此时断电了，当然不能继续执行了，过了一会来电了，启动 mysql 会检查日志，发现有个事 务没有执行完毕，没有commit，就会安装反向的操作把他回滚了。UPDATE user set balance = balance + 200 where id = 2，先记个日志，真正执行。- 如
commit，记个记录，执行，结束了，日志就能删除了。如果rollback，就会按照日志反向操作，回滚。
3. 隔离性
3.1 隔离性的四个隔离级别与并发问题
- 读未提交（read uncommitted）
- 读已提交（read committed）
- 可重复读（repeatable read）
- 串行化（serializable）
| 读未提交 | 读已提交 | 可重复读 | 串行化 | |
|---|---|---|---|---|
| 脏读 | √ | × | × | × |
| 不可重复读 | √ | √ | × | × |
| 幻读 | √ | √ | √ | × |
3.2 读未提交
- 事物A和事物B，事物A未提交的数据，事物B可以读取到
- 这里B读取到的数据叫做“脏数据”，叫脏读（Dirty read）
- 这种隔离级别最低，这种级别一般是在理论上存在，数据库隔离级别一般都高于该级别
3.3 读已提交
- 能读到别的事物已经提交的数据
- A事务在本次事务中，有其他事务提交了，所以A对自己操作过的数据，进行了多次读取发现数据不一致，即不可重复读（Unrepeatable read）
3.4 可重复读
-
A事务在本次事务中对未操作的数据进行多次查询，发现第一次没有，第二次出现了，就像幻觉一样。或者第一次有而第二次没有，即幻读（Phantom read）。
-
针对
delete和insert。
3.5 串行化
- 事务A和事务B，事务A在操作数据库时，事务B只能排队等待
- 这种隔离级别很少使用，吞吐量太低，用户体验差
- 这种级别可以避免幻读，每一次读取的都是数据库中真实存在数据，事务A与事务B串行，而不并发。
- 别的地方一用这个数据就不能修改删除，直到别的地方提交
这里需要注意的是:与 SQL 标准不同的地方在于 InnoDB 存储引擎在可重读（repeatable read）事务隔离级别下使用的是 Next-Key Lock 锁算法，因此可以避免幻读的产生，这与其他数据库系统（如 SQL Server）是不同的。所以说 InnoDB存储引擎的默认支持的隔离级别是可重读（repeatable read）已经可以完全保证事务的隔离性要求，即达到了SQL标准的串行化（serializable）隔离级别。因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是读已提交（read committed），但是你要知道的是 InnoDB 存储引擎默认使用可重读（repeatable read）并不会有任何性能损失。
锁
数据库锁机制简单来说，就是数据库为了保证数据的一致性，使各种共享资源在被访问时变得有序而设计的一种规则。MySQL 的锁机制比较简单最著的特点是不同的存储引擎支持不同的锁机制。 InnoDB 支持行锁，(有时也会升级为表锁）MyISAM 只支持表锁。
- 表锁的特点就是开销小、加锁快，不会出现死锁。锁粒度大，发生锁冲突的概率小，并发度相对 低。
- 行锁的特点就是开销大、加锁慢，会出现死锁。锁粒度小，发生锁冲突的概率高，并发度高。
1. InnoDB 存储引擎的三种锁算法
- 记录锁（Record Lock）：记录锁加在索引上，是标准的单个行记录的行级锁
- 间隙锁（Gap Lock）：锁定范围，不包含记录本身
- Next-Key Lock：记录锁 + 间隙锁，当InnoDB扫描索引时会先对索引加上记录锁，再对索引两边加上间隙锁。能够锁定一个范围，包含记录本身。
InnoDB 对于行查询使用 Next-Key Lock ，其中间隙锁能够解决幻读问题，因为单记录锁只能够锁住当前行，但是不能防止新增和删除其他行。
大表优化
当 MySQL 单表数据过多时，数据库 CRUD 性能明显下降，以下是一些常见的优化措施：
1. 限定数据范围
禁止不带任何限制范围的查询语句。比如：在查询历史订单时，限制在一个月范围内。
2. 读写分离
主从分离，主库读，从库写。
3. 垂直拆分
将数据表列进行拆分，把一张列较多的表拆分为多张表。
- 优点：使得每一条行数据变小，在查询时减小读取硬盘块的个数，减少I/O次数。同时还能够简化表结构，使其变得易于维护。
- 缺点：主键变得冗余，并且会引起
join操作，可以通过应用层的联结来解决。并且垂直拆分还会让事务变得复杂。
4. 水平拆分
保持表结构不变，但是将表存储的数据分片，存放到不同的表或库中，达到分布式的目的。
分表只能够解决单一表过大的问题，但是表还是存储在同一台机器上，所以对于并发能力提升并没有什么意义，所以水平分表最好分库。
常见分片方案：
- 客户端代理：分片逻辑在应用端，封装在 jar 包里，通过修改或封装 JDBC 层实现。
- 中间件代理：在应用和数据中间加代理层。分片逻辑统一维护在中间件服务中。
引用：
