Java基础知识——数据库

锁：读写锁 行页表锁 乐观锁
连接：内连接 外连接 交叉连接
底层设计：HashB B与二叉
引擎：五大引擎 MYISAM和innoDB的区别
基础知识：删除 优先级 视图 约束（五大约束 完整性 断言 触发器）
主从复制：分布式 种类 策略 过程
mysql机制：server组成 日志

锁

活锁：饿死
死锁：
乐观锁：认为读多于写
但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，采取在写时先读出当前版本号，然后加锁操作(比较跟上一次的版本号，如果一样则更新)，如果失败则要重复读-比较-写的操作（类似于自旋操作）。
java 中的乐观锁基本都是通过 CAS 操作实现的，CAS 是一种更新的原子操作，比较当前值跟传入值是否一样，一样则更新，否则失败。
CAS机制当中使用了3个基本操作数：内存地址V，旧的预期值A，要修改的新值B。
如果v中存储的值==A,则成功，写入新值B否则失败不写入

缺点：

1. ABA 问题
   如果一个变量V初次读取的时候是A值，并且在准备赋值的时候检查到它仍然是A值，那我们就能说明它的值没有被其他线程修改过了吗？很明显是不能的，因为在这段时间它的值可能被改为其他值，然后又改回A，那CAS操作就会误认为它从来没有被修改过。这个问题被称为CAS操作的 "ABA"问题。
   部分乐观锁的实现是通过版本号(version)的方式来解决 ABA 问题，乐观锁每次在执行数据的修改操作时，都会带上一个版本号，一旦版本号和数据的版本号一致就可以执行修改操作并对版本号执行+1 操作，否则就执行失败。因为每次操作的版本号都会随之增加，所以不会出现 ABA 问
   题，因为版本号只会增加不会减少。
2. 循环时间长开销大

悲观锁：常用的加锁机制
互斥锁：未得到锁后即阻塞
自旋锁：循环去申请锁而不会阻塞
优点为不用切换上下文，缺点是可能长期占用cpu资源
自旋锁时间阈值：若超过自旋等待的最大时间扔没有释放锁，这时争用线程会停止自旋进入阻塞状态。
在 1.6 引入了适应性自旋锁，适应性自旋锁意味着自旋的时间不在是固定的了，而是由前一次在同一个锁上的自旋时间以及锁的拥有者的状态来决定

悲观锁按使用性质

MYISAM：没有行锁，不支持事务
表级锁：读，写锁
MyISAM 总是一次获得 SQL 语句所需要的全部锁，Dead free
非聚簇索引：如果MyISAM表在数据文件中间没有空闲块，则行始终插入数据文件的末尾。 在这种情况下，你可以自由混合并发使用MyISAM表的INSERT和SELECT语句而不需要加锁
不会死锁

InnoDB：行级锁
行锁：建立在b+数的索引上，所以只有走索引的才可以使用行锁。否则使用表锁（或next-key）
使用mvcc机制，所以普通的select不用加任何锁

锁类型：
共享锁(S锁）：对行加锁，与X不兼容
排他锁（X）：写锁，与S，X都不兼容
意向锁：获取行锁前必须取得相应的意向锁。与其他意向锁兼容，也和行锁兼容，和表锁互斥。主要用于其他lock table时候，避免其检查每一行的行锁，而是只用检查意向锁
意向排他锁IX
意向共享锁IS

为什么要意向锁？
1：对于Lock table，对全表加所，首先需要判断每一行有没有X锁。因为建立在索引上，所以对索引进行扫描，很费时间
有了意向锁，因为X必须先获得IX，所以只需要判断表上有没有IX就可以判断X，非常简单
2：select for update获取X锁，防止死锁
哪些时候加锁？
普通select 不加锁【范围select +间隙锁（解决幻读）】
insert/update/delete：X+IX
select for update：X+IX
select lock in share mod：S+IS

隐式加锁：两阶段。可以在事务的任何时候加锁，但是只能在commit/rollback的时候释放锁
显式加锁：
select for update：获取意向排他锁+排他锁，保证获取最新的数据，并且可以最先修改
select lock in share mod：获取意向共享锁+共享锁，保证获取最新的数据，但是不一定最先修改

死锁：innodb可以使用事务回滚进行释放资源。
一般来说，事务内需要修改的行都先用for update获取IX才修改

参考：
https://zhuanlan.zhihu.com/p/29150809
https://juejin.cn/post/6844903666332368909
https://yq.aliyun.com/articles/626848

悲观锁按作用范围划分

参考资料
行锁：对索引加锁，对操作属性中包含索引
开销大，加锁慢；会出现死锁；需要具体检索到某一行，当没有释放某一行而去请求另一行则可能导致死锁
锁定粒度小，发生锁冲突的概率低，并发度高

表锁：对非索引加锁，不包含索引
锁定力度大，发生锁冲突概率高，并发度最低
开销小，加锁快；不会出现死锁；一次性获得表所涉及的所有锁，要么全部得到要么全部不得到

如果操作的是索引，那么就是行锁；如果是非索引，那么就是表锁

乐观锁

时间戳，版本号

锁协议

锁协议对应四种隔离几杯
1）一级封锁协议：事务T在修改数据R之前必须先对其加写锁，直到事务结束才释放（避免丢失修改）
2）二级封锁协议：在一级封锁协议的基础上增加事务T在读取数据R之前必须先对其加读锁，读完后即可释放读锁（避免丢失修改和读脏数据）
3）三级封锁协议：在一级封锁协议的基础上增加事务T在读取数据R之前必须先对其加读锁，直到事务结束才释放（避免丢失修改、读脏数据、不可重复读）
4）两段锁协议：在对任何数据进行读写操作之前，首先要先申请并获得对该数据的封锁
加锁阶段：在该阶段可以进行加锁操作。在对任何数据进行读操作之前要申请并获得S锁，在进行写操作之前要申请并获得X锁。加锁不成功，则事务进入等待状态，直到加锁成功才继续执行。
解锁阶段：当事务释放了一个封锁以后，事务进入解锁阶段，在该阶段只能进行解锁操作不能再进行加锁操作。
两段封锁法可以这样来实现：事务执行第一条语句为加锁阶段，一直到执行ROLLBACK和COMMIT之前都是加锁阶段。ROLLBACK和COMMIT使事务进入解锁阶段，即在ROLLBACK和COMMIT模块中DBMS释放所有封锁。

Next-Key Lock的算法

行锁+Gap Lock
Gap Lock间隙锁：对间隙加锁
目的：防止幻读

案例数据

id(主键)	c（普通索引）	d（无索引）
5	5	5
10	10	10
15	15	15
20	20	20
25	25	25

有6条间隙锁，（-∞，5]（5，10]（10，15]（15，20]（20，25]（25，+supernum] （其中supernum是数据库维护的最大的值。为了保证间隙锁都是左开右闭原则。）
加锁原则是左开右闭原则

事务与隔离

1. 原子性（Atomicity）
   事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚。
2. 一致性（Consistency）
   数据库在事务执行前后都保持一致性状态。
   在一致性状态下，所有事务对一个数据的读取结果都是相同的。
3. 隔离性（Isolation）
   一个事务所做的修改在最终提交以前，对其它事务是不可见的。即事务之间不会互相影响
4. 持久性（Durability）持久化粗处

可能出现的问题
前提：T1，T2都开启事务
丢失修改：T1 和 T2 对一个数据进行修改，T1 先修改，T2 随后修改，T2 的修改覆盖了 T1 的修改。
脏读：T1 修改一个数据，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2 读取的数据是脏数据。
不可重复读：T2 读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。
幻读：T1 读取某个范围的数据，T2 在这个范围内插入/删除新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。
不可重复读关注update/delete,幻读关注insert

隔离的等级

1. 未提交读（READ UNCOMMITTED）
   事务彼此并发执行
   写会加X锁，而读不需要加S锁
   解决：丢失修改

2. 提交读（READ COMMITTED）
   一个事务只能读取已经提交的事务所做的修改。
   写会加X锁，并且持续到释放结束，读需要加S锁。所以还未提交的写是无法进行读的，返回的上一次读取的ReadView
   解决：脏读

3. 可重复读（REPEATABLE READ）
   给写的数据加行级排它锁，事务结束释放，给读的数据加行级共享锁，事务结束后释放
   解决：不可重复读
   未解决幻读：原因在于加的是行级锁。例子：
   例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作这里只是对现有的“1”加了锁，这时事务T2又对这个表中插入了一行数据项不会和之前的锁冲突，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改

4. 可串行化（SERIALIZABLE）
   事务之间必须串行执行
   解决所有

数据库默认隔离级别: mysql —repeatable,oracle,sql server —read commited

MVCC

上述的隔离级别都是悲观锁实现，效率低。实际应用是用乐观锁的MVCC实现的
(Mutil-Version Concurrency Control)，就是多版本并发控制
参考资料
系统版本号：一个递增的数字，每开始一个新的事务，系统版本号就会自动递增。
事务版本号：事务开始时的系统版本号trx_id
在MySQL中，会在表中每一条数据后面添加两个字段：
创建版本号：创建一行数据时，将当前系统版本号作为创建版本号赋值
删除版本号：删除一行数据时，将当前系统版本号作为删除版本号赋值

roll_pointer：每次对哪条聚簇索引记录有修改的时候，都会把老版本写入undo日志中。这个roll_pointer就是存了一个指针，它指向这条聚簇索引记录的上一个版本的位置，通过它来获得上一个版本的记录信息。(注意插入操作的undo日志没有这个属性，因为它没有老版本)
ReadView：begin了还未提交的事务。通过这个列表来判断记录的某个版本是否对当前事务可见。假设当前列表里的事务id为[80,100]。
1：访问的记录版本的事务id为50，这个事务在之前就提交了，所以对当前活动的事务来说是可访问的。
2：访问的记录版本的事务id为90，需要遍历ReadView列表，如果在那就说明此事务还未提交，所以版本不能被访问。如果不在那说明事务已经提交，所以版本可以被访问。
3：访问的记录版本的事务id为110，那比事务列表最大id100都大，那说明这个版本是在ReadView生成之后才发生的，所以不能被访问。

具体访问记录需要在undo日志
先找最近记录，如果最近这一条记录事务id不符合条件，不可见的话，再去找上一个版本再比较当前事务的id和这个版本事务id看能不能访问，以此类推直到返回可见的版本或者结束

如何解决隔离问题：
已提交读：每次查询都会生成一个独立的ReadView（当前读）
可重复读：第一次读的时候生成一个ReadView，之后的读都复用之前的ReadView（快照读）

参考资料

数据库

索引

创建索引：

CREATE TABLE projectfile (
	id INT AUTO_INCREMENT COMMENT '附件id',
	filesize BIGINT COMMENT '附件大小，单位Byte',
	-- 主键本身也是一种索引（注:也可以在上面的创建字段时使该字段主键自增）
        PRIMARY KEY (id),
	-- 主外键约束（注:project表中的id字段约束了此表中的projectid字段）
	FOREIGN KEY (projectid) REFERENCES project (id),
	-- 给projectid字段创建了唯一索引(注:也可以在上面的创建字段时使用unique来创建唯一索引)
	UNIQUE INDEX (projectid),
	-- 给fileuploadercode字段创建普通索引
	INDEX (fileuploadercode)
	-- 指定使用INNODB存储引擎(该引擎支持事务)、utf8字符编码
) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT '项目附件表';

联合索引：
INDEX (fileuploadercode,projectid）
建表后创建索引：

ALTER TABLE projectfile ADD INDEX (fileuploadercode, projectid);

聚集索引=主键索引（一般情况下）
普通索引=非聚集索引 是否加入unique唯一索引
全文索引
组合索引/复合索引：一个索引包含多个属性

聚集索引和非聚集索引：
参考资料
参考资料
想象一下数据为汉字（首字母，偏旁，注释）
最开始的时候顺序存储，查找效率O(n)
提高效率利用树，B+树 O(logn)
利用FCB的思想，如果树中每一个节点都是汉字的全部数据，则查找时无关内容过多，所以仅仅将首字母作为树中的节点，自然ID伴随着指向对应学生数据结构的指针。聚集索引
而如果不想用首字母找，而利用偏旁找，则建立其他索引即可非聚集索引
区别：根本在于表记录是否和索引顺序一致
1： 通过聚集索引可以查到查找数据（叶子结点就是数据结点）， 而通过非聚集索引需要两次查找，第一次查找到聚集索引的某一个叶子结点，再通过其得到数据即聚集索引是找到数据的唯一途径
2：聚集索引多利用主键，仅仅只有一个。非聚集索引利用其他属性，可以有多个
3：聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续
4：聚集索引修改慢，查找块

like：模糊查找效率低，因为用不到索引（有一些可能用到，但是仍然效率低）所以一般用搜索引擎
全文索引：
需要事先设置fulltext

create table fulltext_test (
    id int(11) NOT NULL AUTO_INCREMENT,
    FULLTEXT KEY content_tag_fulltext(content,tag)  // 创建联合全文索引列
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

之后利用match against查询

select * from fulltext_test 
    where match(content,tag) against('xxx xxx'); is there anything

对于长度小于最小搜索长度和大于最大搜索长度的词语都不会成功
效率高于like，但是可能出现精度问题

联合索引/组合索引/复合索引：最左前缀原则
即对(a,b,c)创建索引，则实际得到的是(a)(ab)(abc)

其构造图如图所示：
1：索引节点包含所有联合索引的索引值，B+树依次从最左进行构造
之所以符合最左前缀原则，可以看到先比较第一行也就是左1索引，如果相同则比较第二行即左2索引
2：叶子结点存储的是主键索引的地址

为什么要用可增列当索引：
主键——非NULL的唯一列——6字节的自增列
因为数据实际存储于B+树的叶子结点，且物理顺序等于索引的逻辑顺序。如果索引是自增列，那么依次添加到末尾结点即可，如果不是有序的，那么每次都要找到特定位置进行插入

四大范式

第一范式的关键词是列的原子性》第二范式的关键词是不能包含部分依赖》第三范式的关键词是不能包含传递依赖。
第一范式：要求数据库表的每一列都是不可分割的原子数据项。

第二范式：表中必须有主键或者联合主键，且其他列必须完全依赖（针对联合主键，不能部分依赖）
如下图，联合主键（订单号+产品号），此时订单金额和订单时间不依赖于产品好，仅仅依赖于订单号，属于部分依赖，所以需要单独列表


第三范式：任何非主属性不依赖其他非主属性，不能由传递依赖

BCNF：主属性对于码的部分与传递函数依赖理解为主属性之间在同一表中不能依赖
首先理解
非主属性
主属性
候选码，主键=主码（码=键）

比如，你有一个员工的二维关系（表） ， 大概这几个属性：
员工表：系统内标识码，身份证号，工号，姓名，出生日期，所在部门
理论上讲， 可以有三个码，：
Key1 : 系统内部标识，这个是数据库设计时，确定的唯一标识。
Key2 : 身份证号，理论上来讲，这个是全国唯一，但是经常听说有重复的。
Key3 : 工号，公司内部定的，也不应该 重复。
但是主码(PK)，你只从上述三个中选定一个，比如我们选定了 Key1，那么 Key2 , Key3 就是候选码。
再说属性，属性就是这个二维关系（这张表）的列（字段）。
主属性，就是上述三个码中所包含的这些列：系统内部标识、身份证号、工号
非主属性，就是除了这三个属性之外的其他所有属性。

联合主键（仓库名，管理员，物品名）
但是物品名却部分依赖于仓库名，并影响数量，所以修改为

连接

等值连接：2个表会先进行笛卡尔乘积运算，生成一个新表格，占据在电脑内存里，当表的数据量很大时，很耗内存，这种方法效率比较低，尽量不用。
内连接：2个表根据共同ID进行逐条匹配，不会出现笛卡尔乘积的现象，效率比较高，优先使用这种方法

内连接

select *
from A inner join B on A.id=B.nameid
关注公共部分C

等值连接

select * from A,B where A.id=B.nameid
关注公共部分C

不等值连接

主要用除了等号之外的操作符，比如：<>、>、<、>=、<=、LIKE、IN、BETWEEN…AND。

自然连接

natural join
特殊的等值连接

1. 等值连接中不要求属性值完全相同，而自然连接要求两个关系中进行比较的必须是相同的属性组（属性名可以不同），即要求必须有相同的值域。
2. 自然连接是去掉重复列的等值连接即上图中Aid和Bnameid是属性值相同的列，因为相同所以去掉其中一列

外连接

左连接

A left join B
部分A+部分C

右连接

A right join B
部分B+部分C

全连接

select *from A full JOIN B ON A.id=B.nameid
部分A+部分B+部分C

交叉连接

笛卡尔积
9*9=81列，不存在等值关系
select *from A cross JOIN B

基本语法

删除

Delete用来删除表的全部或者一部分数据行，执行delete之后，用户需要提交(commmit)或者回滚(rollback)来执行删除或者撤销删除， delete命令会触发这个表上所有的delete触发器；

Truncate删除表中的所有数据，这个操作不能回滚，也不会触发这个表上的触发器，TRUNCATE比delete更快，占用的空间更小；

Drop命令从数据库中删除表，所有的数据行，索引和权限也会被删除，所有的DML触发器也不会被触发，这个命令也不能回滚。

因此，在不再需要一张表的时候，用drop；在想删除部分数据行时候，用delete；在保留表而删除所有数据的时候用truncate。

视图

是从一个或多个表导出的虚拟的表，其内容由查询定义。具有普通表的结构，但是不实现数据存储。

例子：create view v_student as select * from student;

①简化了操作，把经常使用的数据定义为视图。
我们在使用查询时，在很多时候我们要使用聚合函数，同时还要显示其它字段的信息，可能还会需要关联到其它表，这时写的语句可能会很长，如果这个动作频繁发生的话，我们可以创建视图，这以后，我们只需要select * from view就可以啦，这样很方便。

②安全性，用户只能查询和修改能看到的数据。
因为视图是虚拟的，物理上是不存在的，只是存储了数据的集合，我们可以将基表中重要的字段信息，可以不通过视图给用户

③逻辑上的独立性，屏蔽了真实表的结构带来的影响。
视图可以使应用程序和数据库表在一定程度上独立。如果没有视图，应用一定是建立在表上的。有了视图之后，程序可以建立在视图之上，从而程序与数据库表被视图分割开来。

关键字的优先级：

select * from 表名 limit 4，3；
说明：对查询出来的结果进行过滤，只显示起始行为第5行，一共查询3行记录。
where>group by>having>order by>limit
where：大于小于等于like，无法使用聚合函数
group by ：分组。注意单个group值所select的其他值，要么只能有一个，要么聚合函数返回单值参考资料
having：可以使用聚合函数
order by：默认顺序，DESC逆序 ASC

约束

五大约束 数据完整性 断言 触发器

参考资料
主键约束
非空约束
唯一约束
默认约束
外键约束

ON DELETE(其他操作类似)
restrict(约束):
当在父表（即外键的来源表）中删除对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除。
no action:
意思同restrict.即如果存在从数据，不允许删除主数据

cascade(级联):
当在父表（即外键的来源表）中删除对应记录时，首先检查该记录是否有对应外键，如果有则也删除外键在子表（即包含外键的表）中的记录。

set null:
当在父表（即外键的来源表）中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（不过这就要求该外键允许取null）

数据完整性：
实体完整性：Primary Key
自动进行检查。包括：1)、检查码值是否唯一，如果不唯一则拒绝插入或修改；2)、检查码的各个属性是否为空，只要有一个为空则拒绝插入或修改。这样就保证了实体完整性。

检查记录中主码值是否唯一的一种方法是全盘扫描，依次判断表中每一条记录的主码值与将插入记录的主码值（或者修改的新主码值）是否相同，但这种扫描是十分耗时的。为了避免对基本表进行全表扫描，关系数据库管理系统一般都在主码上自动建立一个索引，如 B+ 树索引 ，通过索引查找基本表中是否已经存在新的主码值时不需要查看全部值，而是看特定的几个结点即可，大大提高查找效率。

参照的完整性：外键约束
用户定义的完整性
某一具体应用的数据必须满足的语义要求
可以根据应用要求定义属性上的约束条件，即属性值限制，包括：列值非空 ( not null )、列值唯一 ( unique )、检查列值是否满足条件表达式 ( check 短语)

域完整性：
域是一组具有相同数据类型的值的集合可以理解为类的思想，也就是对于不同表的性别属性，都会check（男，女），重复定义过于麻烦，所以单独定义域，则表的性别属性引用这个域即可
create domain GenderDomain char(2)
check (value in (‘男’,‘女’));

断言：更具一般性的约束，但是很影响效率
任何对断言中所涉及关系的操作都会触发关系数据库管理系统对断言的检查，任何使断言不为真值的操作都会被拒绝执行。

触发器：思想类似于面向AOP编程
行级触发器for each row 和语句级触发器for each statement：
有 100 行
语句级触发器，那么执行完 update 语句后触发动作体执行一次
行级触发器，触发动作体将执行 100 次。

触发顺序：先before后after，（多个before）谁先创建谁先执行

主从复制

参考资料

分布式架构

单点服务：当主库宕机后，集群会自动将一个从库升级为主库，继续对外提供服务
读写分离：写操作走主库，读操作走从库
此时若写操作超过主库负荷？
分库：将业务相关性比较大的表放在同一个数据库中，例如之前数据库有A，B，C，D四张表，A表和B表关系比较大，而C表和D表关系比较大，这样我们把C表和D表分离出去成为一个单独的数据库
将原有的单点写变成双点写或多点些，从而降低原有主库的写负载。
具体有垂直切分和水平切分
垂直切分：按照功能且分。例如商品数据库，用户数据库，日志数据库，不同数据库有不同的功能，根据功能分裤
水平切分：按照数据规则。如果主键为数字，则%20和%21单独分库

策略

同步策略：Master要等待所有Slave应答之后才会commit
半同步策略：Master等待至少一个Slave应答就可以commit。
异步策略：Master不需要等待Slave应答就可以提交。
延迟策略：Slave要至少落后Master指定的时间。
全新MGR（MySQL Group Replication）：必须经过组内（N / 2 + 1）结点通过才能commit
Paxos协议

类型

基于语句的复制，即Statement Based Replication（SBR）：记录每一条更改数据的sql
优点：binlog文件较小，节约I/O，性能较高。
缺点：不是所有的数据更改都会写入binlog文件中，尤其是使用MySQL中的一些特殊函数（如LOAD_FILE()、UUID()等）和一些不确定的语句操作，从而导致主从数据无法复制的问题。

基于行的复制，即Row Based Replication（RBR）：不记录sql，只记录每行数据的更改细节即数据本身
优点：详细的记录了每一行数据的更改细节，这也意味着不会由于使用一些特殊函数或其他情况导致不能复制的问题。
缺点：由于row格式记录了每一行数据的更改细节，会产生大量的binlog日志内容，性能不佳，并且会增大主从同步延迟出现的几率。

混合复制（Mixed）
一般的语句使用SBR。若遇到特殊函数则用RBR

实现过程

mysql主从复制需要三个线程，master（binlog dump thread）、slave（I/O thread 、SQL thread）。
master
binlog dump线程：当
1：主库将更新的事件类型写入到主库的binlog文件中
2：创建log dump线程通知slave有数据更新，当I/O线程请求日志内容时，会将此时的binlog名称和当前更新的位置同时传给slave的I/O线程可以理解为如果由log dump线程传输那么负担太大，所以只是提醒和传输位置，IO线程根本位置自己复制
slave
I/O线程：该线程会连接到master，向log dump线程请求一份指定binlog文件位置的副本，并将请求回来的binlog存到本地的relay log中
SQL线程：该线程检测到relay log有更新后，会读取并在本地做redo操作，将发生在主库的事件在本地重新执行一遍，来保证主从数据同步。

mysql执行过程

Server层
引擎层：插拔式
数据

连接器：用户登录数据库，进行用户的身份认证
查询缓存：执行查询语句的时候，会先查询缓存，Mysql会先校验这个sql是否执行过，以Key-Value的形式缓存在内存中
Mysql 查询不建议使用缓存，因为对于经常更新的数据来说，缓存的有效时间太短了
分析器：词法分析，一条SQL语句有多个字符串组成，首先要提取关键字语法分析，主要就是判断你输入的sql是否正确，是否符合mysql的语法。
优化器：优化器的作用就是它认为的最优的执行方案去执行（虽然有时候也不是最优），比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等
执行器：首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用引擎的接口，返回接口执行的结果
binlog归档日志：主要用于主从复制
InnoDB自带日志模块redo log：主要用于事务（重新提交或者回滚事务）

执行有两种情况：查询语句和更新语句
查询语句按照上述流程（没有binlog和redo log环节）
更新语句：
执行完上述操作后进行日志更新。
1：将操作记录于redo log，redo log进入prepare状态
2：记录binlog
3：提交，redo log 为提交状态。

这里我们可以知道，仅仅有binlog日志是没法支持日志的，而必须有redo log，且顺序必须是
redo log准备——binlog——redo log commit

1：先写redo log 直接提交，然后写 binlog：写完redo log 后，机器挂了，binlog日志没有被写入。机器重启后，这台机器会通过redo log恢复数据，但是这个时候bingog并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。
2：先写binlog，然后写redo log：假设写完了binlog，机器异常重启了，由于没有redo log，本机是无法恢复这一条记录的，但是binlog又有记录，那么和上面同样的道理，就会产生数据不一致的情况。
3：如果按正常顺序，机器在redo log准备——binlog阶段宕机
binlog不完整，回滚事务
4：binlog——redo log commit宕机

底层设计

索引主要是基于Hash表和B+树：
1：B+树效率高于Hash表
两个特点导致：索引的值和Hash值无特定关系
索引的值和Hash值无对应关系：

1. Hash索引仅仅能满足“=”,“IN”,“<=>”查询，不能使用范围查询。
2. Hash索引无法避免数据的排序操作 （order）
   由于Hash索引中存放Hash值，Hash值的大小关系并不一定和Hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算。
3. 联合索引中，Hash索引不能利用部分索引键查询。
   对于联合索引中的多个列，Hash是要么全部使用，要么全部不使用

Hash值存在冲突
4. Hash索引任何时候都不能避免表扫描
因为存在冲突即不同的值对应相同的Hash，所以还是要通过访问表中的实际数据进行比较，并得到相应的结果。
5. Hash索引遇到大量Hash值相等的情况后性能并不一定会比BTree高

发展历史：二叉树——平衡二叉树——B-树——B+树
B树效率高于二叉树
二叉树比较次数少（内存耗时少）
但是最耗费资源是磁盘IO，并且磁盘读写是按页读
二叉树(红黑树)每次只能读取一个节点，浪费资源
B树树低于二叉树，IO次数少
B+树效率高于B-树
io次数少：b+树中间节点只存索引，不存在实际的数据(一页能够有更多索引)
性能稳定：b+树数据只存在于叶子节点，查询性能稳定，即每次都到达叶子结点
范围查询简单：b+树不需要中序遍历，遍历链表即可。

存储引擎

种类

参考资料
数据库存储引擎是存储、处理和保护数据的核心服务
存储引擎主要有:

1. MyIsam , 2. InnoDB, 3. Memory 4:TokuDB

ISAM
查询的次数要远大于更新的次数
不支持事务处理，也不能够容错
如果你的硬盘崩溃了，那么数据文件就无法恢复了。如果你正在把ISAM用在关键任务应用程序里，那就必须经常备份你所有的实时数据，通过其复制特性，MYSQL能够支持这样的备份应用程序。

MYISAM
ISAM扩展格式。
提供索引和字段管理，表锁，来优化多个并发的读写操作。
MYISAM还有一些有用的扩展，例如用来修复数据库文件的MYISAMCHK工具和用来恢复浪费空间的MYISAMPACK工具（容错机制）

HEAP（Hash索引，B+树）
HEAP允许只驻留在内存里的临时表格。驻留在内存里让HEAP要比ISAM和MYISAM都快
数据是不稳定的，关机后数据丢失

InnoDB：B+树

InnoDB和MYISAM的区别

1：InnoDB提供事务支持
2：InnoDB提供行健和外健约束
3：InnoDB没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表。
4：关于索引
对于记录（姓名，ID，成绩）例如（Bob，15，34）
MYISAM
无论是主键索引还是非主键索引，索引结构为B+Tree，其中B+Tree的数据域存储的内容为实际数据的地址，都是非聚集索引。
可以看到Primary Key是ID构建的B+树，而叶子结点是指向记录的地址

InnoDB的索引结构同样也是B+Tree，但是Innodb的索引文件本身就是数据文件，即B+Tree的数据域存储的就是实际的数据，聚集索引。
可以看到Primary Key是ID构建的B+树，而叶子结点是指向记录本身

并且InnoDB的辅助索引数据域存储的也是相应记录主键的值而不是地址，所以当以辅助索引查找时，会先根据辅助索引找到主键，再根据主键索引找到实际的数据。

可以看到Second Key是年龄构建的B+树，而叶子结点是指向的是（年龄，主键（ID）），通过ID再找Primary Key

上述可以得到
Innodb不建议使用过长的主键，否则会使辅助索引变得过大。也最好使用自增主键，便于建树

全文索引的原理：倒排索引

区别：
1：MYISAM不支持事务
2：MYISAM支持全文索引，Innodb最新版本才支持

数据库的优化：
临时表：
一种是内存临时表，一种是磁盘临时表。内存临时表采用的是memory存储引擎，磁盘临时表采用的是myisam存储引擎
1：union查询:合并两个表。必须拥有相似列
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
2：子查询
select * from A where A.id in （select id from B）
3：join 包括not in、exist等
4：group by

性能优化：
1：优化索引、sql语句、分析慢查询 (避免使用无法使用索引的情况)
2：设计表的时候严格根据数据的设计规范来设计数据库(范式)，数据类型准确
3：使用缓存
4：
表分区技术：将一张表划分为不同的区上，
分表：将不同的业务分在不同的表上
垂直分表：根据不同业务进行分表
水平分表：根据数据范围等来进行分表
分库：将不同的表分在不同的数据库中
5：主从分离读写，采取主从复制把数据库的读操作和写操作分离出来

操作优化：
1：not in/in不会使用索引。利用exists替代;
like%不会走索引，用全文索引
2：union用union all代替
3：group by后自动排序，可以用not order by 代替取消

explain命令

all：不使用索引，扫描全表
例子：全校找小明，但是可能有多个小明

index：扫描全部索引
select count(*) from user

range：
range指的是有范围的索引扫描，between，and以及’>’,’<'外，in和or也是索引范围扫描

ref：
查找条件列使用了索引而且不为主键和unique。其实，意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。

const：主键或或者唯一索引

system：

再举一个例子，内层嵌套(const)返回了一个临时表，外层嵌套从临时表查询，其扫描类型也是system，也不需要走磁盘IO，速度超快。

索引失效：
1：如果条件中有or，即使其中有条件带索引也不会使用注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引）
2：对于多列索引，不是使用的第一部分（不符合最左前缀原则）
3：like查询是以%开头，
4：如果列类型是字符串，那一定要在条件中使用引号引起来，否则不会使用索引
5：如果mysql估计使用全表扫描比使用索引快，则不使用索引
6：使用 id=null不会走索引，id is null会走索引