<高性能Mysql>读书笔记

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索引支持的查询原则如下所示:

• 全值匹配: 查询条件和索引中的所有列匹配
• 匹配最左前缀: 只使用索引的第一列
• 匹配列前缀: 只匹配某一列的开头部分
• 匹配范围值: 查找列的范围数据
• 精确匹配某一列并范围匹配另外一列
• 只访问索引的查询

除了按值查找之外,索引还可以用户查询中的排序操作
下面是一些关于B-Tree索引的限制:

• 如果不是按照索引的最左咧开始查找,则无法使用索引
• 不能跳过索引中的一些列,若是跳过则只能使用最左列
• 范围查询的右边列都无法使用索引查找

聚簇索引

聚簇索引并不是一种单独的索引类型,而是一种数据存储方式, InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行, 其中数据行保存在了叶子节点中
聚集的数据有一些重要的优点:

• 相关数据聚集在一起,可以减少磁盘I/O,只需要读取少量的数据页, 就可以读取全部的相关数据
• 访问数据更快,因为聚簇索引将数据行与索引保存在叶子节点,所以更快
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值

同时,聚簇索引也会有一些缺点:

• 更新代价高,相关行都会被移动到新得位置
• 插入速度依赖插入顺序,按照主键的顺序插入是向InnoDB表中插入数据速度最快的方式
• 二级索引可能比想象的更大，因为在二级索引中的叶节点包含了引用行的主键列
• 二级索引访问需要两次索引查找，而不是一次