关于数据库（MySQL）存储引擎，索引，事务，锁的概念总结

关于数据库（MySQL）存储引擎，索引，事务，锁的概念总结

---

存储引擎

查看当前版本数据库支持的存储引擎：show engines;

此处主要概述MyISAM，InnoDB，MEMORY，ARCHIVE四个存储引擎。
MyISAM
1>不支持外键（可以设置，但不生效）
2>支持全文索引
3>不支持事务（但有类似事务的伪事务）
4>支持B+树的索引
5>支持表锁
6>数据存放在磁盘中

InnoDB
1>支持外键
2>不支持全文索引
3>支持事务
4>支持B+树的索引
5>支持行锁
6>数据存放在磁盘中

MEMORY
1>varchar当成char类型处理
2>不支持text（大文本）和BLOB（图片）类型的字段（当遇到时会将这两钟字段放在磁盘上让MyISAM存储引擎处理）
3>支持B树和哈希（默认）的索引
4>数据存放在内存中（适用于临时数据）

ARCHIVE
归档存储引擎：默认以10：1的比例压缩
1>不支持update和delete操作，因为太麻烦
2>支持insert和select操作
3>适合存放数据量大，访问量小的数据（日志数据）

---

索引

索引是一种数据结构，为了提高查询效率（O(logx n)x为支路数）

MyISAM：非聚集索引
数据和索引分离（B+树中，叶子结点上存放数据地址）
以MyISAM存储引擎创建表时会生成三个文件。
.frm 存放创建表的基本创建信息和基本配置信息。
.myi 存放索引
.myd 存放数据
主索引：索引值不重复的索引都可以叫做主索引（叶子结点上存放数据地址）
辅助索引：索引值可以重复的索引都可以叫做辅助索引（叶子结点上存放数据地址）

InnoDB：聚集索引
数据和索引合并（B+树中，叶子结点上存放完整数据）
主索引（只有一个）：存放数据的索引为主索引，不允许索引值重复。（叶子结点上存放完整数据）
辅助索引：索引值可以重复的索引（叶子结点存储主索引的索引值，这样的原因是因为存储完整数据会发生数据冗余，空间占用率高）

如果没有明确索引：
1>是否有主键，建立主键索引
2>是否唯一键，建立唯一索引
3>添加隐藏字段（6个字节）

建立索引 的时机？
1>经常访问的字段
2>主键字段
3>两表连接的字段
4>用于排序查询的字段
5>范围查询的字段
6>经常用于条件过滤的字段

不应该建立索引？
1>不经常查询的字段
2>字段数值重复的
3>存储字节多的，占内存的（I/O效率低）
4>修改性能大于查询性能的

索引的分类
1>普通索引
①create index index_name on tbname(field_name);
②Alter table table_name add index on(fied_name(length);
③index index_name(field_name);
2>主键索引
3>唯一索引：与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似。
4>全文索引
5>单列索引，多列索引：多个单列索引与单个多列索引的查询效果不同，因为执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。
6>组合索引(多字段作为索引值）->依次匹配原则（最左前缀原则）：平时用的SQL查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引。

索引注意事项
1>包含NULL的列（叶子结点存储为空）
2>使用短索引（提升I/O效率）
3>like模糊匹配（%在前不能使用）
4>不要在列上做运算（会产生新表）

---

事务

概念：一组特定的SQL语句的集合
四大特性：ACID
A：原子性：要么都成功，要么都失败
C：一致性：保证完整性约束
I：隔离性：消除事务间的互相影响
D：持久性：事务执行后的结构在磁盘上永久保存

I：隔离性
如果没有隔离性会发生的问题？

1>脏读：事务执行过程中获取到其他事务执行过程中的结果
解决方案：事务执行过程中不能获取到其他事务执行过程中的结果

2>不可重复读：事务执行过程中获得到其他事务不同阶段（执行前和执行后）的结果（因为update）
解决方案：如果事务进行中其他事务开启，则该事务对其他事务透明。

3>幻读：事务执行过程中获得到其他事务不同阶段（因为insert或者delete）
解决方案：间隙锁

隔离级别：通过 select @@tx_ioslation;命令进行查询
通过set tx_ioslation="隔离级别"命令进行隔离级别修改
隔离级别分为：全局隔离级别和会话隔离级别（全局和局部）
①未提交读：READ-UNCOMMITTED
出现脏读，不可重复读，幻读的问题

②已提交读: READ-COMMITTED（SQL server默认）
出现不可重复读，幻读的问题

③可重复读: REPEATABLE-READ(MySQL innodb默认）
出现幻读的问题

④可序列化（效率低）
无问题

原子性&&持久性
日志系统（日志先行，先刷新到磁盘）
redo log：重做日志
记录事务将要执行的操作（事务都执行成功）

undo log：未作日志
记录每个修改点的状态，用于回滚（事务都执行成功）

一致性
锁

---

锁机制

MyISAM（表级锁：粒度大）不容易发生死锁
读锁 -> 共享读锁 -> select 操作
写锁 -> 独占写锁 -> update，insert，delete操作
读读兼容，读写不兼容，写写不兼容

InnoDB（行级锁：粒度小/表级锁）开销大，容易思索
读锁 -> 共享锁 -> select操作不加锁，读锁需要手动加（用于特殊要求）
写锁 -> 排他锁 -> update，insert，delete操作
读读兼容，读写不兼容，写写不兼容

注意：当给一行数据手动加上写锁时，另外一个事务，还可以修改该行数据。原因：InnoDB存储引擎支持事务，每一行语句会当成一个事务，事务处理完之后，会将所有锁释放。
取消自动事务提交：set autocommit = 0;

注意：
非索引查询时，采用表锁
索引查询时，采用行锁

悲观锁
提前加锁：读锁，写锁

乐观锁
遇到问题时加锁（人为设计）
认为设计：
1.版本标识
2.时间戳

意向锁
解决行锁和表锁冲突（加锁之前加意向锁，系统加）

间隙锁
锁定数据间隙（范围查询时）