好记性不如烂笔头，最近学习的知识零零散散，还是汇总一下比较方便。（本文大部分是摘录的~）

---

1.MySQL基本结构

2.存储引擎

2.1什么事存储引擎？常用存储引擎有哪些？

MySQL是用来保存数据的，MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者特定的功能，从而改善你的应用的整体功能。这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作表类型)。

以下分割线内部分摘自 https://www.cnblogs.com/lvjianwei/p/9880993.html

---

MyISAM:

MySQL默认数据库引擎，不支持事务、外键，速度快，用于无事务要求或者select、insert为主的场景。

会创建3个文件.frm（存储表定义），.MYD（MYData，存储数据），.MYI（MYIndex，存储引擎）。

支持3中存储格式，静态（固定长度）表，动态表，压缩表。

静态表是默认格式，固定长度速度快，但是占空间大。记录长度不够时会用空格填充，读取数据时会清除空格，存在吃尾部空格的情况。

动态表包含变长字段，记录不是固定长度，占用空间少，但是频繁更新和删除会产生随便，需要定期整理，并且在出现故障时恢复比较困难。

压缩表由mysiampack工具创建，空间小。

InnoDB:

提供了具有提交、回滚、崩溃恢复能力的事务安全，但是会占用更多空间用以保存数据和索引。

支持外键，创建外键的时候，要求父表必须有对应的索引，子表在创建索引的时候也会自动创建对应的索引。

存储表和索引有两种方式，

使用共享表空间存储，表结构保存在.frm文件中，数据和索引在innodb_data_home_dir和innodb_data_file_path定义的表空间中，可以是多个文件。

使用多表空间存储，表结构保存在.frm文件中，每个表的数据和索引单独保存在.ibd中。

（MySQL5.6之后已支持全文索引）

MEMORY:

使用存在于内存中的内容来创建表，访问速度快，默认使用HASH索引，服务关闭数据会丢失。

用于内容变化不频繁的代码表，或者作为统计操作的中间结果表。

MERGE:

一组MyISAM表的组合，这些表必须结构完全相同。

MERGE表本身没有数据，CRUD时其实操作的是内部的MyISAM表。

Drop只是删除MERGE的定义，对于内部表没有影响。

---

2.2.InnoDB 和 MyISAM 的区别

    ①InnoDB支持事务，MyISAM不支持

    ②InnoDB支持外键，MyISAM不支持

    ③InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；

    ④InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。

    ⑤InnoDB如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。MyISAM允许没有任何索引和主键的表存在，索引都是保存行的地址。

    ⑥数据和索引的组织方式不同。

        MyISAM将索引和数据分开进行存储。索引存放在.MYI文件中，数据存放在.MYD文件中。索引中保存了相应数据的地址。以表名+.MYI文件分别保存。 

        InnoDB的主键索引树的叶子节点保存主键和相应的数据。其它的索引树的叶子节点保存的是主键。也正是因为采取了这种存储方式，InnoDB才强制要求每张表都要有主键

3.InnoDB索引

3.1 索引为什么能提高查询速度

原因在于将数据从无序变成了相对有序。从全表扫描到根据关键词快速定位数据行。底层是B+树

3.2 索引的优缺点

降低增删改的速度。增删改时，B树需要重新平衡节点，耗费额外的时间。

3.3 InnoDB支持的索引类型

B树

B是balance的意思，BTree是一种平衡树，有很多变种，最常见的就是B+Tree它被MySQL广泛使用。

B树：数据存放到叶子节点和非叶子节点中。查询时，每次都在做二分查找，速度快。并且可能不需要遍历到叶子节点就能查到所需的数据了。

B+树：只有叶子节点才存放数据，非叶子节点存放的是稀疏索引。更适合用于文件系统中。并且B+树因为索引是稀疏的，在磁盘上可能只需要一个或者很少的几个block块就存完了，相当于减少了磁盘的IO次数。

B树和B+树没什么谁更快的问题，只有谁更适合使用场景的问题

Hash

将键值经过哈希算法算出哈希值。当没有大量重复键值时，通过一次哈希即可快速索引到结果。

但是哈希的缺点很明显，（1）不可有过多重复的键值，会发生严重的哈希碰撞；（2）没法根据索引排序（因为是哈希值）；（3）不支持最左匹配原则；

3.4聚簇索引和非聚簇索引

聚簇索引（一种数据结构）

聚簇索引是以主键创建的索引，叶子节点存储的是主键和对应的数据行，是通往真实数据的唯一途径；创建带有主键的表默认就是聚簇索引，如果没有主键引擎会选取带有唯一索引的列为主键，如果还没有唯一索引，引擎会创建一个row_id隐藏列作为主键来创建聚簇索引。

非聚簇索引（二级索引）

非聚簇索引是以普通字段创建的索引，叶子节点存储的是索引列和主键，查询比聚簇索引慢，因为要先查询到索引列，获取对应的主键，再根据主键回表查询到对应数据行。

3.5 索引注意事项

索引列不能是表达式的一部分，否则索引为失效。

最左匹配原则：组合索引时，InnoDB引擎会从左往右匹配，直到遇到 >,<,between,like操作符停止匹配。

3.6 索引失效状况举例

1、索引列出现在表达式或者函数中

2、索引列是字符型但值没加引号

3、索引列上使用IS NULL或者IS NOT NULL作为判断条件

4、索引列条件是不等于!=或<>

5、索引列上模糊匹配的值前面加了%

6、不满足最左前缀原则

7、条件是or。如果想让or条件生效，给or每个字段都加上索引。

8、索引检索结果集过多，优化器选择全表扫描

3.7 explain主要字段说明

• type的类型

• all: 全表遍历

• index: 全索引遍历。和all的区别在于all扫描全表，而index扫描全部的索引。

• range: 一定范围的索引扫描

• ref: 非唯一性索引扫描。常见于用唯一的索引，匹配到的行不唯一。

• eq_ref: 唯一性索引。使用唯一索引匹配到了唯一的行。

• const/system: 存储引擎对查询进行了优化，并将查询转化为常量时会出现。常见于将主键置于where条件。

• Extra的类型

• Using Temporary: 表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询。

• Using filesort: 文件排序。表示当无法使用索引排序时，使用文件排序方式。

• Using where: 表示MySQL在存储引擎收到记录后根据where条件过滤结果集。

• Using index: 表示覆盖索引。只使用索引即可获取结果，不需要回表。

• Using index Condition: MySQL5.6之后的新特性，先过滤完索引后找到所有符合索引条件的数据行，随后用where条件对数据行进行过滤。这个是索引下推功能。

4.InnoDB事务

4.1特性

事务是恢复和并发控制的基本单位，有四个特性， 原子性（A）Atomicity、隔离性（I）Isolation、持久性（D）Durability 、一致性（C）Consistency、其中一致性是目的，原子性、隔离性、持久性是手段。因此数据库必须实现AID三大特性才有可能实现一致性。

一致性：事务执行之后数据保持一致，多个事务对同一个数据读取的结果是相同的

原子性：要么全部成功然后提交，要么全部失败然后回滚。利用undo log记录需要回滚的日志信息是实现原子性的关键。

持久性：一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。具体实现原理就是在事务提交之前会将 redo log buffer中的数据持久化到硬盘中的redo logo file，这样在提交的时候，硬盘中已经有了我们修改或新增的数据，由此做到持久化。

隔离性：并发访问数据库时，各个事物间互不影响。（通过读写锁+MVCC 来实现的）

4.2并发事务带来的问题

4.2.1脏写

一个事务修改了其他事务已修改但未提交的记录。

附：脏写，是最严重的问题。四种隔离级别中，无论哪一种，都不允许脏写的情况发生。

4.2.2脏读

一个事务读到了其他事务已修改但未提交的记录。

4.2.3不可重复读

读取到数据后，随着其他食物对数据发生了修改，无法再次读取。MySQL通过三级封锁协议的第三季解决了不可重复读，在二级的基础上，要求读取数据A时必须加S锁，直到事务结束了才释放S锁。

4.2.4幻读

一个事务内读取到了别的事务插入的数据，导致前后读取不一致

eg：如果事务A先根据某些条件查出一些记录，之后事务B又向表中插入了符合这些条件的记录，事务A再次按照该条件查询时，能把事务B插入的记录也读出来

4.2.5丢失更新

一个事务覆盖了另一个事务对同一条数据的修改。MySQL通过三级封锁协议的第一级解决了丢失更新，事务T要修改数据A是必须加X锁，直到T结束才释放锁。

4.3 事务隔离级别

4.3.1 读未提交

一个事务还未提交时，他做的更改就能被其他事务读取到。

4.3.2 不可重复读（读已提交）

一个事务所做的更改，只有在提交后才会被其他事务所看到。

4.3.3 可重复读（默认）

一个事务执行过程中看到的数据总是和该事务启动时看到的数据是一致的。

4.3.4 串行

对于同一行记录，写会加写锁，读会加读锁，当出现读写所冲突时，后访问的事务必须等待前一个事务执行完成才能继续执行。

4.4 MVCC

MVCC（MultiVersion Concurrency Control）叫做多版本并发控制。

InnoDB的eMVCC，是通过在每行记录的后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存了行的过期时间，当然存储的并不是实际时间值，而是系统版本号。

他的主要实现思想是通过数据多版本来做到读写分离。从而实现不加锁读进而做到读写并行。

5.常见锁

5.1 记录锁（行锁）（Record Locks）

记录锁是索引记录上的锁。记录锁总是锁定索引记录，即使表没有定义索引。对于这种情况，InnoDB创建一个隐藏的聚集索引并使用这个索引来锁定记录。

5.2 间隙锁（Gap Locks）

RR级别多次索引范围查询时，可是实现重复读，也同时避免了幻读。虽然防止了幻读，但是不能防止插入幻影记录。Gap Lock的出现仅仅就是为了解决插入幻影行的问题。

5.3 临键锁（Next-key Locks）

是Record Lock和Gap Lock的合体。即能保护当前记录，又能保护住插入间隙。

5.4 插入意向锁（Insert Intention Locks）

其他事务在向GAP间隙锁中插入记录时，会被阻塞并且获取该间隙中的插入意向锁，当GAP锁的事务结束时，才会去插入记录。插入意向锁并不会组织其他事务继续获取该记录上的其他任何类型的锁。

5.5 共享锁/排他锁（Shared and Exclusive Locks）

共享锁是一个事务并发读取某一行记录所需要持有的锁，比如select ... in share mode；排他锁是一个事务并发更新或删除某一行记录所需要持有的锁，比如select ... for update。

5.6 意向共享锁/意向排他锁（Intention Shared and Exclusive Locks）

意图锁是表级锁，指示事务稍后需要哪种类型的锁(共享的或排他的)来锁定表中的某一行。

意图锁定协议如下:

事务在获得表中某行上的共享锁之前，必须先获得表上的IS锁或更强的锁。

在事务可以获得表中某一行上的排他锁之前，它必须首先获得表上的IX锁。

5.7 自增锁（Auto-inc Locks）

自增锁是一种特殊的表级锁，主要用于事务中插入自增字段，也就是我们最常用的自增主键id。通过innodb_autoinc_lock_mode参数可以设置自增主键的生成策略

6.log

6.1 bin log(归档日志)

内容：记录了对数据库数据和表的修改。

功能：复制和恢复。主从复制就是用这个来做。

6.2 redo log（重做日志）

redo log 会把事务在执行过程中对数据库所做的所有修改都记录下来，在之后系统崩溃重启后可以把事务所做的任何修改都恢复出来。

内容：记录了x页做了xx修改。

功能：数据库崩溃恢复（删库只能用binlog恢复。不能用redolog。因为redolog记录的页级别的修改记录。页都不见了，咋恢复。）

bin log和redo log必须保持一致，不允许出现bin log有记录但redo log没有的情况

（redo log和bin log的故事，传送门：https://www.jianshu.com/p/4bcfffb27ed5）

6.3 undo log（回滚日志）

内容：记录着记录修改前的数据，并且通过记录中的roll pointer隐藏列指向备份数据的undo log，事务失败时，好通过undo日志回滚。

功能：回滚和MVCC中的版本链。