MySQL学习笔记：模型2

序言

《MySQL45讲》

为什么表数据删除一半，表文件大小不变？

表数据既可以存在共享表空间里，也可以是单独的文件。这个行为是由参数 innodb_file_per_table 控制的：

1. 这个参数设置为 OFF 表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；

2. 这个参数设置为 ON 表示的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。

从 MySQL 5.6.6 版本开始，它的默认值就是 ON 了。如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。

假设，我们要删掉 R4 这个记录，InnoDB 引擎只会把 R4 这个记录标记为删除。如果之后要再插入一个 ID 在 300 和 600 之间的记录时，可能会复用这个位置。但是，磁盘文件的大小并不会缩小。

我们知道InnoDB的数据是按页存在，如果一个页上的数据都被删除掉，那么整个页将会被复用。

数据页复用和记录复用是不一样的。
上面的截图而言：

记录复用 – 如果插入一个 ID 是 400 的行，可以直接复用这个空间。但如果插入的是一个 ID 是 800 的行，就不能复用这个位置了。

数据页复用：如果将数据页 page A 上的所有记录删除以后，page A 会被标记为可复用。这时候如果要插入一条 ID=50 的记录需要使用新页的时候，page A 是可以被复用的。

删除数据的过程

1. 假设，我们要删掉 R4 这个记录，InnoDB 引擎只会把 R4 这个记录标记为删除。如果之后要再插入一个 ID 在 300 和 600 之间的记录时，可能会复用这个位置。但是，磁盘文件的大小并不会缩小。

如何处理掉因为删除或者插入、更新导致的空洞呢？

方法有三个：

1. 使用 alter table A engine=InnoDB 命令来重建表。（recreate）
2. analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了 MDL 读锁；
3. optimize table t 等于 recreate+analyze。

Q：什么时候使用 alter table t engine=InnoDB 会让一个表占用的空间反而变大？
A：这个表，本身就已经没有空洞的了，比如说刚刚做过一次重建表操作。在 DDL 期间，如果刚好有外部的 DML 在执行，这期间可能会引入一些新的空洞。再具体点就是：
在重建表的时候，InnoDB 不会把整张表占满，每个页留了 1/16 给后续的更新用。也就是说，其实重建表之后不是“最”紧凑的。假如是这么一个过程：
①. 将表 t 重建一次；
②. 插入一部分数据，但是插入的这些数据，用掉了一部分的预留空间；
③. 这种情况下，再重建一次表 t，就可能会出现问题中的现象。

不同count的用法

count(主键 id)或count(字段)

InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。

①count函数，不管传什么都会判断一下；②另一个就是字段是否为null需要判断一下。

count(1)

InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。

count(*) MySQL专门优化了

不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加。

按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count(*)

对于需要经常要显示交易系统的操作记录总数的页面，需要我们自己来计算。
通常好的办法，就是用一张表来记录。假设有分布式事务，那么用Redis也行。
用MySQL的表来记录，其实就是为了保证这种需求：“要显示操作记录的总数，同时还要显示最近操作的 100 条记录。”
换句话说，多个不同的存储构成的系统，如果没有分布式事务保证精确一致的事务，那么还是都用MySQL表来记录吧！

order by

全字段排序

如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer 中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。

rowid 排序

如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。

本质就是：只有 要排序的列（即 name 字段）和主键 id。其他字段等排完顺后，再回表查询即可。

体现了 MySQL 的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。

索引知识

如何使用explain查看索引使用情况

Extra 字段显示 Using temporary，表示的是需要使用临时表；Using filesort，表示的是需要执行排序操作。
因此这个 Extra 的意思就是，需要临时表，并且需要在临时表上排序。

不走索取的情况

B+ 树提供的这个快速定位能力，来源于同一层兄弟节点的有序性。

1. 对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

mysql> select * from tradelog where tradeid=110717;
1

假设索引字段tradeid是字符串的话，那么相当于：

-- 等号左边，是索引字段或者叫字段。等号右边叫输入的参数
mysql> select * from tradelog where  CAST(tradid AS signed int) = 110717;
1
2

那么就说明对索引字段做了函数操作，因此优化器不会走索引。

查询一条SQL语句慢的原因：

1. 等 MDL 锁： 现在有一个线程正在表 t 上请求或者持有 MDL 写锁，把 select 语句堵住了。
2. 等 flush

flush tables t with read lock;

flush tables with read lock;
1
2
3
4

3. 等行锁
4. select * from t where id=1 如果另一个线程update10万次，这条SQL就会很慢，因为为了一致性，需要依次执行undo log 10万次。

show processlist 显示用户正在运行的进程：

通过查询 sys.schema_table_lock_waits 这张表，我们就可以直接找出造成阻塞的 process id，把这个连接用 kill 命令断开即可。

通过sys.innodb_lock_waits，查出是谁占着这个写锁？

mysql> select * from t sys.innodb_lock_waits where locked_table='`test`.`t`'\G
1
2

参考地址：

https://time.geekbang.org/column/article/72388