MySQL的事务与索引

• 事务
• • 特性
  • 事务的启动方式：
  • 长事务的危害
• 索引
• • 索引分类
  • 索引类型：
  • 普通索引和主键索引的区别
  • 页的内部原理
  • 页分裂
  • 页合并
  • 覆盖索引
  • 最左前缀原则
  • 索引下推

事务

特性

1. 原子性
2. 一致性
3. 隔离性
4. 持久性

事务的启动方式：

1. 显式的启动：begin或 start transaction。提交语句为commit，回滚事务语句是rollback
2. set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一 个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主 动执行 commit 或 rollback 语句，或者断开连接。

第二种方法可能会导致长事务
可使用commit work and chain 命令提交并开启新事务

长事务的危害

• 长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间
• 长事务会占用锁资源

索引

索引分类

• 单一索引
• 复合索引
• 主键索引
• 唯一性索引

索引类型：

1. 主键索引 （叶子节点存的是整行数据）
2. 非主键索引 （叶子节点内容是主键的值）

普通索引和主键索引的区别

如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵B+ 树；
如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。
可知，普通索引需要多扫描一棵主键索引树。

页的内部原理

页可以空或者充满，行记录会按照主键顺序来排序。
根据B树的特性，它可以自顶向下遍历，但也可以在各叶子节点水平遍历。因为每个叶子节点都有着一个指向包含下一条（顺序）记录的页的指针。

例如，页#5有指向页#6的指针，页#6有指向前一页（#5）的指针和后一页（#7）的指针

页分裂

页可能填充至100%，在页填满了之后，下一页会继续接管新的记录。
分裂后 上一页存 MERGE_THRESHOLD 容量的数据。在数据挪动的过程中会影响性能。除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约 50%（MERGE_THRESHOLD 参数控制）

页合并

当你删了一行记录时，实际上记录并没有被物理删除，记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。
当相邻两个页由于删除了数据，利用率小于（MERGE_THRESHOLD 默认为50%）之后，会将数据页做合并

覆盖索引

覆盖索引可以减少树的搜索次数，显著提升查询性能

最左前缀原则

使用复合索引的时候，没有使用左侧的列查找，索引失效。
对于模糊查询 以‘%’开头的会造成索引失效

索引下推

以联合索引（name，age）为例

mysql> select * from tuser where name like '张 %' and age=10 and ismale=1;

在 MySQL 5.6 之前，只能从 ID3 开始一个个回表。到主键索引上找出数据行，再对比字段值。
而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件（索引字段）的记录，减少回表次数。