MYSQL架构与SQL执行流程

一、数据库表、存储引擎介绍

数据库表：就是用来存储数据的，一个是数据内容，一个是数据的存储格式（表类型，也叫存储引擎）。

可以放到磁盘、也可以放到内存；可以使用索引也可以不适用索引；可以让数据支持修改的功能、也可以不支持修改的功能。这就是数据不同的存储格式。所以访问的方式由存储结构决定的。

分析存储引擎如何什么样的数据：找到数据的存储路径，
不同的存储引擎存储的数据格式是不同的。

二、MySQL官网介绍

MySQL官网解释innodb：支持事务、行锁、使用聚集索引。同时支持行级别的锁和表级别的锁、读写不冲突也就是支持读写的并发（MVCC）。有特殊的存储方式来存储数据。适合我们经常更新的表。

ISAM：是MySQL开发出来的时候，自带的存储引擎。InnoDB是第三方公司开发的作为一个插件集成到MySQL，后来该公司被MySQL收购了，后来MySQL被oracle收购。

后来ISAM升级为MyISAM的存储引擎，用的是最久的，应用的范围比较狭小，提供了一种表结构的锁定的功能。不支持事务，通常只用于read most。插入、查询的速度比较高，但是不支持更新的高速。

Memory:把数据放入内存中进行存储，读写更快，不需要操作磁盘，但是一旦数据库重启，会造成丢失，所以此类数据库只用于临时数据的存储。

CSV：文件解析，批量上传文件， 解析CSV比解析xls后缀的文件更快，因为是纯文本的格式。里面存储的数据，就是用逗号隔开的。比如：可以用记事本打开，解析的非常快。
通常用于数据的传输、导出导入。不支持空行，不支持索引。

Archive: 归档，不支持update、delete，只做数据的存储。比如历史数据的存储，不允许修改信息，可以放入这类的存储引擎里面。

三、MYSQL架构

根据业务选择不同的存储引擎。如果所有的存储引擎都不能满足需求，可以自己写一个存储引擎，插件式的存储引擎。

改了存储引擎，不会影响数据的访问，为什么？因为在Server层，存储引擎的访问接口是一样的，不同的存储引擎相同的API。可以自己写一个自定义的存储引擎给服务端去访问。

服务端是谁来调用存储引擎API来操作我们的数据的呢？ 执行器

通过存储引擎的API，插件式存储引擎只负责取数据，而执行器负责数据的过滤、排序等计算的操作，也是在内存中进行操作。数据通过存储引擎返回给客户端。

总结图：

连接层：和不同的编程语言进行交互，比如C JAVA PHP 等。
架构分层：

磁盘的数据加载到内存，然后才能对数据进行修改。磁盘IO相对于内存的操作，是很慢的。
预读取：局部性原理，把需要读取的数据，连通周边的数据也一同加载到内存。而不是操作一条数据，只加载一条数据，这样会浪费IO性能。
操作系统、存储引擎、文件系统等，都有预读取。
页 Page:操作系统定义的是4KB、innoDB定义的是16KB的大小。不是固定不变的，修改Mysql的源码、重装mysql服务，即可改变默认大小。最小的逻辑单位（磁盘–>内存）。
数据页：不是每次都需要读取磁盘，innoDB使用缓冲池的技术。Buffer pool
重启避免数据丢失：使用持久化的方式，记录日志文件。崩溃服务redo.log
Buffer POOL 先写到日志，再写到数据文件db file。如果直接写入到磁盘，后台程序需要刷脏，写入日志文件再写入数据文件，可以降低刷脏的频率。

随机I/O: 操作的数据分在不同的磁盘扇区，写入修改，要等扇区转到不同的位置，【磁盘转动–>寻址】
顺序I/O：效率高于随机I/O。redo.log顺序写入，不需要寻址。

Redo.log记录物理日志，数据页有什么改动。大小固定，可以通过配置文件进行配置。会出现覆盖，被覆盖的修改，同步到磁盘文件。对应(redo)Log Buffer，先写入log buffer后写入redo.log。

Ubdo.log + redo.log 统称为事务日志。
Ubdo.log:原子性、回滚的实现。逻辑格式的日志，放在共享表空间中。

1、从磁盘/内存读取数据到server。
2、Server层的执行器把拿到的数据进行修改update语句。
3、修改结果，记录到undo.log。可进行恢复、回滚。
4、修改的值，记录到redo.log。
5、InnoDB 的 bufferpool中修改。
6、事务提交。Bufferpool中的值什么时候提交给磁盘 ？后台的工作线程刷脏来决定的。

Binlog记录表结构的修改，DDL DML
默认关闭：额外记录日志文件到磁盘，带来磁盘开销。
功能1：数据恢复：对数据库全量备份，binlog记录的是从建表后所有的语句，才能从drop中挽救回来数据库表。
养成数据库备份的习惯。
Binlog二进制文件的解析，在服务端有工具。
功能2：主从同步，master-slave
保持和主服务器的同步，slave读取master的binlog，有一个I/O线程，请求主服务器。
Master：binloglump线程生成binlog文件，发送给slave
Slave：把这个binlog记录到ready log中，SQL thread将终极日志应用到从库里面。

重点1：写入内存之前，先写入bufferpool。
2:记录入redolog分为2个阶段，先记录为prepare状态，后记录到binlog后置为commit状态。
3：Server层记录binlog innodb记录redolog。

四、索引存储模型

1.二分查找

概念：折半查找，每一次我们都把候选数据缩小了一半。
适用范围：已排过序的数据，效率比较高。
分析：有序数组的等值查询、比较查询效率高。但更新数据需要挪动大量数据（改变index），因此，只适合存储静态的数据。
频繁的修改、插入数据，使用单链表，但单链表效率不够高，采用二分查找树。
2.二分查找树（BST binary search tree）
概念：左子树所有节点小于父节点，右子树所有节点大于父节点。投影到平面后，就是一个有序的线性表。

分析：可实现快速查找、插入。
有一个问题：查找耗时和树深度相关，最坏情况下，时间复杂度退化为O(n)。
插入的是有序数据，BST变成链表（“斜树”），和顺序查找没有区别，不能达到加快检索的效果。

为什么造成倾斜？左右子树相差太大，左子树没有节点–不够平衡。
为了使得左右子树相差不大，使用平衡二叉树。Balance binary search tree 或者AVL树 （AVL 是该发明人的名字）。
3.平衡二叉树（AVL Tree）

概念：左右子树深度差绝对值不能超过1。比如，左子树的深度是2，右子树的深度只能是1或3。
平衡二叉树中，一个节点的大小，是一个固定的单位。

“斜树”的解决方案：右节点下面接一个右节点，右-右型。进行左旋的操作，比如有序数组1、2、3；将2提上去。
同理，左-左型，进行右旋操作。

作为索引怎么查询数据？
索引存储什么样的内容？3块内容。
①　索引的键值。比如：where id = 1，条件查询会找到索引里面的id的这个键值。
②　数据的磁盘地址。索引的作用，就是查找数据的存放地址。
③　左右子节点的引用。（因为是二叉树）

使用树做索引，拿到一个数据就要在server层进行比较，不是的话，就在读一遍磁盘。访问一个节点就要和磁盘之间发生一次IO。InooDB操作磁盘的最小单位是页（page、一个磁盘块），大小16K（16384字节）。那么一个树的节点就是16K的大小。

若一个节点只存一个键值+数据+引用，整数类型的字段，可能只用了十几或几十个字节，远远达不到16K的容量，所以访问一个树的节点，进行一次IO的时候，浪费了大量空间。

问题原因：每个节点存储的数据太少。从索引中找到我们需要的数据，就要访问更多的节点，意味着和磁盘的交互次数就越多。

若是机器硬盘时代，每次从磁盘读取数据，需要10ms 的寻址时间，交互次数越多，消耗时间就越多。

解决方案：
①　让每个节点存储更多的数据。
②　节点上关键字越多，指针越多，意味着可以有更多的分叉（“路数”）。
因为分叉越多，树的深度就会减少（根节点是0）。树不再是二叉，而是多叉，也就是多路。（矮胖矮胖的样子）
4.多路平衡查找树（B Tree）
和AVL树一样，B树在枝节点、叶子节点存储键值、数据地址、节点引用。
特点：分叉树（路数）永远比关键字数多1。

每个节点存储2个关键字，3个指针指向3个子节点。
比如：查找15，进行了3次IO。

示例：
Max Degree （路数）是3的时候，插入数据1、2、3，在插入3 的时候，本来应该做第一个磁盘款，但是如果一个节点3个关键字，意味着有4个指针，子节点变成4路。所以这个时候需要进行分裂其实就是（B+Tree）。把中间的数据2提上去，把1、3变成2的子节点。

若删除节点，会有相反的合并操作。
分裂和合并，与AVL树 的左旋和右旋是不一样的。
继续插入4、5，B Tree又会出现分裂和合并的操作。

由此也可看出，更新索引的时候，会有大量的索引的结构调整，所以不要在频繁更新的裂伤建索引，不要更新主键。

节点的分裂、合并，其实就是InnoDB页（page）的分裂和合并。

5.B+树（加强版多路平衡查找树）
B树 的改良版 解决的问题比B tree更全面。

特点：
①　关键字的数量=路数
②　B+Tree的根节点和枝节点不会存储数据，只有叶子节点才存储数据。搜索到关键字字不会直接返回，会到最后一层的叶子节点。

例子：假设一条记录是1K，一个叶子节点（一页）可以存储16条记录。非叶子节点可以存储多少个指针？

所以：在InnoDB中B+树深度一般为1-3层，他满足千万级的数据存储。

③　B +Tree的每个叶子节点，增加了一个指向相邻叶子节点的指针。最后一个数据会指向下一个叶子节点的第一个数据，形成有序的链表结构。
④　根据左闭右开的区间[)来检索数据。

B+Tree的数据搜寻过程：
1)根据左闭右开的原则，向下检索数据，最后在叶子节点上找到需要的数据。
2)范围查询。比如22到66的查询。当找到22之后，顺着节点和指针顺序遍历，就可以一次性访问到所有的数据节点。极大地提高了区间查询效率（不需要返回上层父节点重复遍历查找）。

总结特点：
①　B Tree的变种，能解决的问题，它都能解决。两大问题：（每个节点存储更多的关键字；路数更多）
②　扫库、扫表能力更强，（全表扫描，只需要遍历叶子节点即可。不需要遍历整棵B+Tree）
③　磁盘读写能力强于B Tree（根节点和枝节点不保存数据，所以一个节点可以保存更多的关键字，一次磁盘加载的关键字更多）
④　排序能力更强（叶子节点上有下一个数据区的指针，数据形成了链表）
⑤　效率更稳定（永远在叶子节点拿到数据，IO次数是稳定的）

索引方式用的是什么？
Navicat中，创建索引，索引方式有两种。

HASH: KV方式检索数据，根据索引字段生成哈希码和指针，指针指向数据。

特点：
a.时间复杂度是O（1），查询速度快。哈希索引里面的数据，不是按顺序存储的，不能用于排序。
b.查询，根据键值计算哈希码，只能支持等值查询（=IN），不支持范围查询（> < >= <= between and）
c.字段重复值较多的话，会出现大量的哈希冲突（采用拉链法解决），效率会降低。
d.InnoDB不能显示地创建一个哈希索引。

应用范围：
因为B Tree 和 B + Tree的特性，广泛应用在文件系统和数据库中，例如Windows的HPFS文件系统，Oracel , MYSQL, SQLServer。

五、B+Tree的落地形式

InnoDB表：两个文件（.frm 和 .ibd），
MyISAM表：3个文件（.frm 和 .MYD和 .MYI）

.frm文件：MYSQL里的表结构定义的文件。用任何存储引擎都会生成。

其他两个文件怎么实现Mysql不同的存储殷勤的索引？

（一）MYISAM
.MYD文件:
D代表data，是MyISAM的数据文件，存放数据记录。（所有表数据）
.MYI文件：
I代表index，是MyISAM的索引文件，存放索引。比如在id字段上创建一个主键索引，主键索引就在这个文件里。
索引和数据是独立的文件。
MyISAM的 B+Tree里，叶子结点存储的是数据文件对应的磁盘地址。
所以从索引文件.MYI中找到键值后，会到数据文件.MYD中获取对应的数据记录。

辅助索引也在.MYI文件里。

两者存储、检索数据没区别。
（二）InooDB

.ibd存储索引和数据。
以主键为索引，来组织数据的存储。主键索引在叶子结点上，直接存储了我们的数据。

聚集索引
概念：索引键值的逻辑顺序和表数据行的物理存储顺序是一致的。
（比如：字典的目录按拼音排序的，内容也是按拼音排序的，按拼音排序的这种目录就叫聚集索引。）

InnoDB中组织数据的方式：（聚集）索引组织表（clustered index organize table）。
主键索引是聚集索引。非主键是非聚集索引。

普通索引，怎么存储、检索数据？
比如查询name=’青山’，叶子节点找到主键值，也就是id=1,然后再到主键索引的叶子结点拿到数据。

若一个表没有主键？
①　如果定义了主键，选择主键作为聚集索引。
②　如果没有显示定义主键，选一个不包含null值的唯一索引，作为主键。
③　如果没有这样的索引，选一个内置6字节长的rowID作为隐藏的聚集索引，随着行记录的写入而主键递增。
【select _rowid name from t2】

六、索引的使用原则

（一）列的离散度
Count(distint(column_name)) : count(*)，列的全部的不同值和所有数据行的比例。

数据行数相同的情况下，分子越大，列的离散度越高。

Name（重复值少、离散度高）、gender（重复值多、离散度低）两个字段。

Gender上建立索引：需要扫描的行数更多。Explain看一下执行计划。
Name上建立索引：只需扫描一行。

如果B+Tree里面的重复值太多，MySQL的优化器发现走索引跟全表扫描差不多，这个时候，就算建了索引，也不一定会走索引。

所以：建立索引，要使用离散度更高的字段。

1.联合索引
单列索引、联合索引（多条件查询的时候）
Alter table user DROP INDEX comidx_name_phone;
Alter table user add index comidx_name_phone(name,phone);

联合索引在B+Tree中是复合的数据结构。他是按照从左到右的顺序来建立搜索树的（name左、有序。Phone右、无序）

优先比较name来确定下一步应该搜索的方向，name相同在比较phone。如果没有name，不用索引【name是第一个比较因子】。

所以：建立联合索引，一定要把最常用的列，放在最左边。

什么时候使用联合索引？
①　使用两个字段，能用到联合索引。
②　使用最左边的name字段，能用到联合索引。
③　使用右边phone的字段，无法使用索引，全表扫描。
2.覆盖索引
回表：
非主键索引，先通过索引找送到主键索引，再通过主键值查出索引里面没有的数据。比基于主键索引查询，多扫描了一课索引树。这个过程就是回表，

避免了回表：在辅助索引里，不管是单列索引还是联合索引，如果select的数据列。只用从索引中能够获得，就不用从数据区中读取，这个时候使用的索引就是覆盖索引。

– 创建联合索引
Alter table user drop index comidx_name_phone;
Alter table user add index ‘comidx_name_phone’(name,phone);

Extra里面值：Using index :代表使用了覆盖索引。

好处：减少io次数，提升查询效率。

七、索引的创建和使用

改善查询性能，目标：尽量使用索引

1.创建
(1)用于where、 join (on)、group by 、order排序字段。
(2)索引的个数不要太多。—浪费空间、更新慢。
(3)过长的字段，建立前缀索引。
(4)区分度低的字段，例如性别，不要建立索引。 — 离散度态度，已造成和全表扫描。
(5)频繁更新的值，不要作为主键或索引。 – 页分裂
(6)随机无序的值，不建议作为索引，例如身份证、UUID。
(7)组合索引把散列性高（区分度高）的值放在前面
(8)创建复合索引，而不是修改单列索引

八、什么时候用不到索引？

1、索引列上使用函数（replace\SUBSTR\CONCAT\sum count avg）、表达式
计算（+ - * /）：

explain SELECT * FROM t2 where id+1 = 4;
2、字符串不加引号，出现隐式转换

ALTER TABLE user_innodb DROP INDEX comidx_name_phone;
ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);
explain SELECT * FROM user_innodb where name = 136;
explain SELECT * FROM user_innodb where name = ‘136’;

3、like 条件中前面带%

where 条件中 like abc%，like %2673%，like %888 都用不到索引吗？为什么？

explain select *from user_innodb where name like ‘wang%’;
explain select *from user_innodb where name like ‘%wang’;
过滤的开销太大导致用不到索引。这个时候可以用全文索引。

4、负向查询

NOT LIKE 不能：

explain select *from employees where last_name not like ‘wang’

!= （<>）和 NOT IN 在某些情况下可以：

explain select *from employees where emp_no not in (1)
explain select *from employees where emp_no <> 1

这个例子中，因为索引是有序的，只要从 1 之后开始顺序读取就行了。
注意跟数据库版本、数据量、数据选择度都有关系。
其实，用不用索引，最终都是优化器说了算。

优化器是基于什么的优化器？

基于 cost 开销（Cost Base Optimizer），它不是基于规则（Rule-Based Optimizer），
也不是基于语义。怎么样开销小就怎么来。

https://docs.oracle.com/cd/B10501_01/server.920/a96533/rbo.htm#38960
https://dev.mysql.com/doc/refman/5.7/en/cost-model.html

使用索引有基本原则，但是没有具体细则，没有什么情况一定用索引，什么情况一
定不用索引的规则。