数据库学习笔记

Mysql常用的存储引擎对比

• MyISAM 强调的是性能，执行更快，但不支持事务，InnoDB支持事务
• MyISAM不支持外键，InnoDB支持
• MyISAM只支持表索，InnoDB行锁表索都支持，行锁基于索引（只有通过索引查询时行锁才有效）
• 当数据量很大又不需要支持事务时最好选择MyISAM
• 对于UPDATE、DELETE、INSERT语句，InnoDB会自动给涉及数据集加排他锁，一般情况下不需要手动加锁

Mysql的存储结构

系统从磁盘读取数据到内存以块（block）为单位,Mysql数据存储在磁盘中，以页为单位，默认每页16k,系统磁盘块一般没有这么大，所以一般是使用连续的若干个块来构成页

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为10^3）。 也就是说一个深度为3的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿 条记录。

Mysql的数据库索引

• 索引会加快查询的效率，但是会降低新增、修改、删除的效率
• 哈希索引：底层的数据结构就是哈希表，在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快
• BTree索引：底层为B+树，可作为大多数情况使用

B+树的查找过程如下图

20160202204827368.png

数据库悲观锁和乐观锁

• 悲观锁

加了写锁以后，其他的事务就不能对它修改了！

select * from xxxx for update

• 乐观锁

通过给表增加版本号来实现，更新时对比版本号

update A set Name=lisi,version=version+1 where ID=#{id} and version=#{version}

常见数据库优化手段

• 读/写分离：主库负责写，从库负责读
• 增加缓存
• 字段较多的表可拆表
• 数据量大的表可分表（Sharding-JDBC ）

Mysql事务隔离级别

事务的四大基本要素（ACID）:原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation，同一时间不同事务间没有干扰）、持久性（Durability，事务完成后数据被持久化）

事务的并发问题：脏读（读到了其他事务未提交的数据）、不可重复读（事务期间多次读取的数据不一致）、幻读（一个事务改全表多条数据期间，另一进程插入数据导致全表更新结果不完整，解决幻读需锁表）

事务隔离级别	脏读	不可重复读	幻读	解释
读未提交（read-uncommitted）	是	是	是	允许读取其他事务未提交的数据
不可重复读（read-committed）	否	是	是	只读取其他事务已提交的数据
可重复读（repeatable-read）	否	否	是	select操作不更新版本号，后续重复读取的是历史版本，update/insert/delete操作时读取最新版本
串行化（serializable）	否	否	否	操作串行化、锁表，事务操作时全表不允许其他线程修改，并发极低，很少用

Mysql默认使用repeatable-read级别，有索引时，默认使用next-key锁（锁定数据及前后几条，一定程度上避免幻读）。如果检索条件没有索引，更新数据时会锁住整张表！