MySQL的事务与索引
事务
特性
- 原子性
- 一致性
- 隔离性
- 持久性
事务的启动方式：
- 显式的启动：begin或 start transaction。提交语句为commit，回滚事务语句是rollback
- set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一 个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主 动执行 commit 或 rollback 语句，或者断开连接。
第二种方法可能会导致长事务
可使用commit work and chain 命令提交并开启新事务
长事务的危害
- 长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间
- 长事务会占用锁资源
索引
索引分类
- 单一索引
- 复合索引
- 主键索引
- 唯一性索引
索引类型：
- 主键索引 （叶子节点存的是整行数据）
- 非主键索引 （叶子节点内容是主键的值）
普通索引和主键索引的区别
如果语句是 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)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件（索引字段）的记录，减少回表次数。