MYSQL实战45讲-笔记

1、查询长事务执行时间

SELECT TIMEDIFF(NOW(),trx_started),S.* FROM information_schema.innodb_trx S

2、数据清理方向：清理大量数据后查询索引文件大小，重构索引文件

3、索引和回表

        1）自增主键的优势：按照顺序生成主键索引，节省主键树因为需要插入乱序数据导致的写成本。

        2）回表：二级索引树的value值是主键索引，回表指读完二级索引树后拿到ID去读主键索引树。

        3）索引建立规则：主键最好按照顺序生成；查询时优先select id以达到用覆盖索引效果，减少回表；联合索引建立时，依据最左前缀匹配原则，优先字节数少的字段在右，这样对右字段建索引时可以减少空间；

4、事务设计：如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁的申请时机尽量后放，减少锁等待。

5、数据版本和事务：

begin/start transaction，一致性视图是在第执行第一个快照读语句时创建的；

start transaction with consistent snapshot，一致性视图是在执行 start transaction with consistent

snapshot 时创建的。

对于可重复读，查询只承认在事务启动前就已经提交完成的数据；

对于读提交，查询只承认在语句启动前就已经提交完成的数据

一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：

        1）版本未提交，不可见；

        2）版本已提交，但是是在视图创建后提交的，不可见；

        3）版本已提交，而且是在视图创建前提交的，可见。

         ps:更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。

6、普通索引和change buffer ：适用于写多读少的表，写多读多不建议使用change buffer ，写多不建议使用唯一索引（需要做唯一性约束，从磁盘读所有索引）；

change buffer 参数：innodb_change_buffer_max_size

7.analyze table t 命令，可以用来重新统计索引信息。

8.既然优化器放弃了使用索引 a，说明 a 还不够合适，所以第二种方法就是，我们可以考虑修改语句，引导 MySQL 使用我们期望的索引。

比如，在这个例子里，显然把“order by b limit 1” 改成 “order by b,a limit 1” ，语义的逻辑是相同的。

我们来看看改之后的效果：

 图 10 order by b,a limit 1 执行结果

之前优化器选择使用索引 b，是因为它认为使用索引 b 可以避免排序（b 本身是索引，已经是有序的了，如果选择索引 b 的话，不需要再做排序，只需要遍历），所以即使扫描行数多，也判定为代价更小。

现在 order by b,a 这种写法，要求按照 b,a 排序，就意味着使用这两个索引都需要排序。

因此，扫描行数成了影响决策的主要条件，于是此时优化器选了只需要扫描 1000 行的索引a。

若优化器错误选择的索引其实根本没有必要存在，就删掉这个索引。