1 Mysql架构与历史
事务机制ACID
-
原子性 (atomicity)
对于一个事务来说,要么操作全部完成,要么全部失败回滚,事务中的任何一个步骤失败都会导致整体事务的失败
-
一致性 (consistency)
所有改变数据库数据的动作事务必须完成，没有事务会创建一个无效数据状态,如若事务没有提交,则不会更改数据库里面的数据
-
隔离性 (isolation)
隔离能够确保并发执行的事务能够顺序一个接一个执行，通过隔离，一个未完成事务不会影响另外一个未完成事务
-
持久性 (durability)
一旦事务被提交，它应该持久保存，不会因为和其他操作冲突而取消这个事务
隔离级别
-
未提交读 Read UnCommitted
事务可以读取未提交的数据,即脏读 (Dirty Read)
-
提交读 Read Committed
事务在提交之前对其他事务是不可见的,两次执行同样的查询,可能会得到不一样的结果,即不可重复读(nonrepeatable)
-
可重复读 Repeatable Read
默认隔离级别, 同一事务中,多次读取结果是一致的,但是无法解决幻读,即事务操作过程中,已经有其他事务提交新增了其中的部分数据,查询会出现幻行
-
可串行化 Serializable
最高隔离级别,强制事务串行执行,会在读取的每一行数据加上锁,所以可能会导致大量超时或者锁争用的问题
4 Schema与数据类型优化
范式
-
1NF
表的列的具有原子性,不可再分解
-
2NF
在满足1NF的前提下,表有主键，非主键字段依赖主键
-
3NF
在满足2NF的前提下,表中每列都与主键有直接关系，不存在传递依赖
反范式
为了提高部分查询效率或者性能, 可以适当降低范式标准,比如增加字段, 减少关联,提高查询效率
5 创建高性能的索引
索引类型
B-Tree 索引
使用Ｂ-Tree按顺序存储索引，并且叶子节点到根的距离相同，参考下图：
叶子节点的指针指向被索引的数据,各叶子节点之间相连,所以范围查询的效率会更高
B-Tree索引支持的查询原则如下所示:
- 全值匹配: 查询条件和索引中的所有列匹配
- 匹配最左前缀: 只使用索引的第一列
- 匹配列前缀: 只匹配某一列的开头部分
- 匹配范围值: 查找列的范围数据
- 精确匹配某一列并范围匹配另外一列
- 只访问索引的查询
除了按值查找之外,索引还可以用户查询中的排序操作
下面是一些关于B-Tree索引的限制:
- 如果不是按照索引的最左咧开始查找,则无法使用索引
- 不能跳过索引中的一些列,若是跳过则只能使用最左列
- 范围查询的右边列都无法使用索引查找
聚簇索引
聚簇索引并不是一种单独的索引类型,而是一种数据存储方式, InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行, 其中数据行保存在了叶子节点中
聚集的数据有一些重要的优点:
- 相关数据聚集在一起,可以减少磁盘I/O,只需要读取少量的数据页, 就可以读取全部的相关数据
- 访问数据更快,因为聚簇索引将数据行与索引保存在叶子节点,所以更快
- 使用覆盖索引扫描的查询可以直接使用页节点中的主键值
同时,聚簇索引也会有一些缺点:
- 更新代价高,相关行都会被移动到新得位置
- 插入速度依赖插入顺序,按照主键的顺序插入是向InnoDB表中插入数据速度最快的方式
- 二级索引可能比想象的更大，因为在二级索引中的叶节点包含了引用行的主键列
- 二级索引访问需要两次索引查找，而不是一次
