Mysql索引、事务、隔离级别、存储结构--详解
一、4大SQL语句分类
- DDL: 数据库定义语句( create, drop, alert)
- DCL: 数据库控制语句( grant, revoke, commit, rollback)
- DML: 数据库操作语句( update, insert, delete)
- DQL: 数据库查询语句( select)
二、数据库5大约束分类
- 非空约束：not null
- 检查约束：check
- 外键约束：foreginkey
- 主键约束：primarykey
- 唯一约束：unique
三、数据库5种索引分类 【B-Tree 索引， Hash 索引， Fulltext 索引 和 R-Tree 索引 （还有 B+Tree）】
索引优点：可以根据查询要求迅速缩小查询范围，避免全表扫描；
1、主键索引
主键索引不一定是聚簇索引，但通常情况下主键索引就是聚簇索引，也可以是非聚 簇索引或者堆索引，可以和多个字段联合做主键，值不能为null。
2、单列索引
一个表中可以有多个单列索引，值可以不为空。
3、唯一索引
索引列中的值必须唯一，但是可以有null值。
4、复合索引
复合索引是多个字段上创建的索引，一个索引包含多个列，复合索引的开销比单列 索引开销更小，列入员工编号、员工、姓名可就可以建立复合索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。
5、聚集索引和非聚集索引（Clustered Index）
①聚集索引：聚簇索引是指将表中的数据行按照指定的列（通常是主键）进行排序，它会根据聚集索引的顺序来存储表中的数据，并且一个表中只能有一个聚集索引。比如字典中用拼音来查询汉字
SQL：create clustered index index_name on table_name(column_name);
使用场景：①查询结果是以该字段为排序依据的
②查询结果返回一个区间的值
③聚集索引会降低Insert和update操作额性能。
②非聚集索引：与聚集索引相反 索引顺序和物理存储顺序不一致,例如字典中按部首查询汉字。
SQL：create nonclustered index index_name on table_name(column_name);
使用场景：①查询数据量比较少的情况
②某字段中的数据唯一性比较高的情况下
四、数据库(常见的3种)搜索引擎
- Innodb引擎：现mysql默认存储引擎，Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约 束。它的设计的目标就是处理大数据容量的数据库系统。
存储方式：数据同样存储在磁盘上面(idb文件包含索引和数据)，基于B+Tree结构存储，在树 的叶子节点处存储了索引对应的数据，在通过索引进行检索时命中叶子节点 即可从叶子节点中直接读取数据。
- MyIASM引擎(原本Mysql的默认引擎)：不提供事务的支持，支持表锁但不支持行级锁和外键。 数据是通过二进制的方式存储在磁盘上（.MYD/.MYI文件），文件中索引和数据是分离存储的；
存储方式：先从索引文件中找到数据的磁盘位置，再从数据文件中找到索引对应的数据内容。
- MEMORY引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不高。
五、mysql数据库5种存储数据结构
- 二叉树: 每个节点最多能支持两个分叉
- 二叉查找树: 基于二叉树，但是二叉查找树左子树的所有节点都要小于它的根节点，右侧子
节点都要大于它的根节点 - AVL平衡二叉树: 由于二叉树可能产生"斜树"的问题，从而导致时间复杂度增加，因此引入
了平衡二叉树，具有二叉查找树的所有特点，但它的左右两个子树的高度差的绝对值不能超过1，通过左旋和右旋的机制来达到平衡。 - B-Tree: 是一种多路平衡的二叉树，既满足平衡二叉树的规则，又可以有多个子树，子树的数量取决于关键字的数量（= 根节点中关键字的数量+1）
- B+Tree和B-Tree的不同点:
①B-树的数据存储在每一个节点上, B+树的子树数量等于它的关键字的数量，而B-树是关键字数量+1
②而B+树的数据存储在每一个叶子节点上，并且通过链表的方式将所有叶子节点串联起来
平衡二叉树的高度要比B树要高，所以磁盘的IO的磁盘次数也比较多，所以数据存储结构大多数用B树或者B+树做数据存储。
MangoDB中采用B树存储数据，MangoDB中所有的节点都有Data域，无疑单次查询更快些，只要找到指定的索引就能进行访问，
Mysql作为一种关系型数据库，区间访问也是一种常见的情况，而B+树数据存储在叶子节点上并且通过指针串联在一起，这样就容易进行区间遍历或者全部遍历访问
六、ACID事务模型及数据隔离级别
START TRANSACTION; -- 开启事务
-- 若干SQL操作
UPDATE user SET name = user1' WHERE id = 1;
INSERT INTO user (id,name) VALUES (1，admin');
COMMIT; -- 提交
SET autocommit = 'NO'; -- 自动提交事务1.1 Atomicity 原子性
每一个事务中的所有操作都要像原子一样要么全部成功，要么全部失败
UNDO LOG机制：INNODB提供了UNDO LOG机制，记录数据每次的增删改操作，通常一条sql对应一个UNDO LOG,当一组操作出现异常触发回滚，反向执行UndoLog,恢复所有数据执行前状态。
1.2 Isolation 隔离性
事务执行过程中相互隔离，事物之间不能相互干扰，不能相互查看彼此未提交的数据，分为写与写隔离/写与读隔离，分别通过[锁]/[MVCC]来实现。
常见三大读写问题：
- 脏读：A事务执行过程中读取到了B事务还未提交的数据。
- 不可重复读：A事务执行过程中对同一条数据进行两次读取，但是两次读取过程中B事务修改并提交了这条数据，导致A事务两次读取数据不一致。
- 幻读：A事务执行过程中对同一集合数据进行两次读取，但是两次读取过程中B事务在集合中增加或者删除了部分数据，导致A事务两次读取到了数据不一致的行数据。
四种事务隔离级别：
读未提交：允许一个事务读取另一个还未提交的事务的数据。这种隔离级别可能会导致"脏读"、"不可重复读"和"幻读"这样的问题。
读已提交：保证了一个事务只能读取另一个已经提交的事务的数据。这种隔离级别可以防止"脏读"问题，但仍然可能出现"不可重复读"和"幻读"问题。
可重复读：保证了一个事务读取结果和别的没提交事务是一样的，查询结果不会变。
串行化：读取需要获取共享读锁，更新需要获取排他写锁，如果SQL使用WHERE语句，还会获取区间锁，这是较高的隔离级别
-- 隔离级别
SHOW VARIABLES LIKE'transaction_isolation';
-- InnoDB默认隔离级别是REPEATABLE-READ可重复读
SET transaction_isolation ='REPEATABLE-READ';
1.3Durability 持久性
事务一旦提交，事务中的所有更改就不会由外界因素干扰(断电/设备故障)而发生变化。
REDO LOG机制：数据从内存写入磁盘的同时，数据也会有Redo Log Buffer数据备份。
双写缓冲区 - 同步恢复机制：数据从内存以16KB大小数据(最小数据页)向硬盘传输刷盘时，操作系统OS以4KB大小数据记性传输，所以也有可能导致数据未完整传输。
1.4 Consistency 一致性(最终)：
由数据原子性、隔离性、持久性共同保证数据一致性。
