序言
《MySQL45讲》
为什么表数据删除一半,表文件大小不变?
表数据既可以存在共享表空间里,也可以是单独的文件。这个行为是由参数 innodb_file_per_table
控制的:
-
这个参数设置为 OFF 表示的是,表的数据放在系统共享表空间,也就是跟数据字典放在一起;
-
这个参数设置为 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 是可以被复用的。
删除数据的过程
- 假设,我们要删掉 R4 这个记录,InnoDB 引擎只会把 R4 这个记录标记为删除。如果之后要再插入一个 ID 在 300 和 600 之间的记录时,可能会复用这个位置。但是,磁盘文件的大小并不会缩小。
如何处理掉因为删除或者插入、更新导致的空洞呢?
方法有三个:
- 使用 alter table A engine=InnoDB 命令来重建表。(recreate)
- analyze table t 其实不是重建表,只是对表的索引信息做重新统计,没有修改数据,这个过程中加了 MDL 读锁;
- 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+ 树提供的这个快速定位能力,来源于同一层兄弟节点的有序性。
- 对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能。
mysql> select * from tradelog where tradeid=110717;
假设索引字段tradeid是字符串的话,那么相当于:
-- 等号左边,是索引字段或者叫字段。等号右边叫输入的参数
mysql> select * from tradelog where CAST(tradid AS signed int) = 110717;
那么就说明对索引字段做了函数操作,因此优化器不会走索引。
查询一条SQL语句慢的原因:
- 等 MDL 锁: 现在有一个线程正在表 t 上请求或者持有 MDL 写锁,把 select 语句堵住了。
- 等 flush
flush tables t with read lock;
flush tables with read lock;
- 等行锁
- 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
参考地址: