关于mysql:Mysql-实战笔记-三-实践2

五、为什么表数据删除后，表文件大小不变？

一个 InnoDB 表蕴含两局部，即：表构造定义和数据。在 MySQL 8.0 版本以前，表构造是存在以.frm 为后缀的文件里。而 MySQL 8.0 版本，则曾经容许把表构造定义放在零碎数据表中了。

表数据

表数据既能够存在共享表空间里，也能够是独自的文件。这个行为是由参数innodb_file_per_table 管制的：

1. 这个参数设置为 OFF 示意的是，表的数据放在零碎共享表空间，也就是跟数据字典放在一起；
2. 这个参数设置为 ON 示意的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。

不管应用 MySQL 的哪个版本，都将这个值设置为 ON。因为，一个表独自存储为一个文件更容易治理，而且在你不须要这个表的时候，通过 drop table 命令，零碎就会间接删除这个文件。而如果是放在共享表空间中，即便表删掉了，空间也是不会回收的。

数据删除流程

InnoDB 引擎只会把要删除的记录标记为删除。如果之后要在这个地位插入一个记录时，可能会复用这个地位。然而，磁盘文件的大小并不会放大。
如果咱们删掉了一个数据页上的所有记录，会怎么样？答案是，整个数据页就能够被复用了。然而，数据页的复用跟记录的复用是不同的。
记录的复用，只限于合乎范畴条件的数据。比方下面的这个例子，ID=400 这条记录被删除后，如果插入一个 ID 是 400 的行，能够间接复用这个空间。
而当整个页从 B+ 树外面摘掉当前，能够复用到任何地位。
如果相邻的两个数据页利用率都很小，零碎就会把这两个页上的数据合到其中一个页上，另外一个数据页就被标记为可复用。

delete 命令其实只是把记录的地位，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。也就是说，通过 delete 命令是不能回收表空间的。

如果数据是依照索引递增程序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页决裂。

重建表

通过大量增删改的表，都是可能是存在空洞的。所以，如果可能把这些空洞去掉，就能达到膨胀表空间的目标。而重建表，就能够达到这样的目标。
alter table A engine=InnoDB，用来重建表，原理是 Online DDL
重建表的流程：

1. 建设一个临时文件，扫描表 A 主键的所有数据页；
2. 用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；
3. 生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中，对应的是图中 state2 的状态；
4. 临时文件生成后，将日志文件中的操作利用到临时文件，失去一个逻辑数据上与表 A 雷同的数据文件，对应的就是图中 state3 的状态；
5. 用临时文件替换表 A 的数据文件。

alter 语句在启动的时候须要获取 MDL（元数据）写锁，然而这个写锁在真正拷贝数据之前就进化成读锁了。为什么要进化呢？为了实现 Online，MDL 读锁不会阻塞增删改操作。那为什么不罗唆间接解锁呢？为了爱护本人，禁止其余线程对这个表同时做 DDL。

Online 和 inplace

在上图中，依据表 A 重建进去的数据是放在“tmp_file”里的，这个临时文件是 InnoDB 在外部创立进去的。整个 DDL 过程都在 InnoDB 外部实现。对于 server 层来说，没有把数据移动到长期表，是一个“原地”操作，这就是“inplace”名称的起源。
以，我当初问你，如果你有一个 1TB 的表，当初磁盘间是 1.2TB，能不能做一个 inplace 的 DDL 呢？答案是不能。因为，tmp_file 也是要占用长期空间的。

optimize table、analyze table 和 alter table 区别

从 MySQL 5.6 版本开始，alter table t engine = InnoDB（也就是 recreate）默认的就是下面图 4 的流程了；
analyze table t其实不是重建表，只是对表的索引信息做从新统计，没有批改数据，这个过程中加了 MDL 读锁；
optimize table t 等于 recreate+analyze。

delete truncate drop 的区别

truncate 删除内容、开释空间但不删除表的构造，能够了解为 delete + create。
drop 删除内容和表的构造，开释空间。

六、count 为什么这么慢？

count(*) 的实现形式

MyISAM 引擎把一个表的总行数存在了磁盘上，因而执行 count(*) 的时候会间接返回这个数，效率很高 (如果加了 where 条件的话，MyISAM 表也是不能返回得这么快的)；
而 InnoDB 引擎就麻烦了，它执行 count(*) 的时候，须要把数据一行一行地从引擎外面读出来，而后累积计数。
为什么 InnoDB 不跟 MyISAM 一样，也把数字存起来呢？这是因为 即便是在同一个时刻的多个查问，因为多版本并发管制（MVCC）的起因，InnoDB 表“应该返回多少行”也是不确定的。 这和 InnoDB 的事务设计有关系，可反复读是它默认的隔离级别，在代码上就是通过多版本并发管制，也就是 MVCC 来实现的。每一行记录都要判断本人是否对这个会话可见，因而对于 count(*) 申请来说，InnoDB 只好把数据一行一行地读出顺次判断，可见的行才可能用于计算“基于这个查问”的表的总行数。

InnoDB 是索引组织表，主键索引树的叶子节点是数据，而一般索引树的叶子节点是主键值。所以，一般索引树比主键索引树小很多。对于 count(*) 这样的操作，遍历哪个索引树失去的后果逻辑上都是一样的。因而，MySQL 优化器会找到最小的那棵树来遍历。

show table status 命令显示的行数 row 不能间接应用。

本人实现一个 count(*)计数

用缓存保留计数: redis
在数据库保留计数

count(*)、count(主键 id)、count(字段) 和 count(1) 区别

1. count(主键 id)：InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。
2. count(1)：InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。

3. count(字段)：

   1. 如果这个“字段”是定义为 not null 的话，一行行地从记录外面读出这个字段，判断不能为 null，按行累加；
   2. 如果这个“字段”定义容许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。
4. count(*)，并不会把全副字段取出来，而是专门做了优化，不取值。count(*)必定不是 null，按行累加。

七、orderby 是怎么工作的？

全字段排序

MySQL 会给每个线程调配一块内存用于排序，称为 sort_buffer。
select city,name,age from t where city='杭州' order by name limit 1000

按 name 排序，可能在内存中实现，也可能须要应用内部排序，这取决于排序所需的内存和参数 sort_buffer_size，就是 MySQL 为排序开拓的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中实现。
但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。内部排序个别应用归并排序算法。将须要排序的数据分成 若干 份，每一份独自排序后存在这些临时文件中。而后把这些个有序文件再合并成一个有序的大文件。
sort_buffer_size 越小，须要分成的份数越多，number_of_tmp_files 的值就越大。

rowid 排序

如果 MySQL 认为排序的单行长度太大会怎么做呢？

全字段排序 VS rowid 排序

如果 MySQL 切实是放心 排序内存太小，会影响排序效率，才会采纳 rowid 排序算法， 这样排序过程中一次能够排序更多行，然而须要再回到原表去取数据。如果 MySQL 认为内存足够大，会优先选择全字段排序， 把须要的字段都放到 sort_buffer 中，这样排序后就会间接从内存外面返回查问后果了，不必再回到原表去取数据。这也就体现了 MySQL 的一个设计思维：如果内存够，就要多利用内存，尽量减少磁盘拜访。

能够利用笼罩索引，避免应用排序和长期表
alter table t add index city_user_age(city, name, age);

能够看到，Extra 字段外面多了“Using index”，示意的就是应用了笼罩索引，性能上会快很多。

八、如何正确地显示随机音讯？

内存长期表

select word from words order by rand() limit 3;

Extra 字段显示 Using temporary，示意的是须要应用长期表；Using filesort，示意的是须要执行排序操作。因而这个 Extra 的意思就是，须要长期表，并且须要在长期表上排序。

下面 sql 的执行流程：

1. 创立一个长期表。这个长期表应用的是 memory 引擎， 表里有两个字段，第一个字段是 double 类型，为了前面形容不便，记为字段 R，第二个字段是 varchar(64) 类型，记为字段 W。并且，这个表没有建索引。
2. 从 words 表中，按主键程序取出所有的 word 值。对于每一个 word 值，调用 rand()函数生成一个大于 0 小于 1 的随机小数，并把这个随机小数和 word 别离存入长期表的 R 和 W 字段中，到此，扫描行数是 10000。
3. 当初长期表有 10000 行数据了，接下来你要在这个没有索引的内存长期表上，依照字段 R 排序。
4. 初始化 sort_buffer。sort_buffer 中有两个字段，一个是 double 类型，另一个是整型。
5. 从内存长期表中一行一行地取出 R 值和地位信息（rowid），别离存入 sort_buffer 中的两个字段里。这个过程要对内存长期表做全表扫描，此时扫描行数减少 10000，变成了 20000。
6. 在 sort_buffer 中依据 R 的值进行排序。留神，这个过程没有波及到表操作，所以不会减少扫描行数。
7. 排序实现后，取出前三个后果的地位信息，顺次到内存长期表中取出 word 值，返回给客户端。这个过程中，拜访了表的三行数据，总扫描行数变成了 20003。

慢查问日志为：

# Query_time: 0.900376  Lock_time: 0.000347 Rows_sent: 3 Rows_examined: 20003
SET timestamp=1541402277;
select word from words order by rand() limit 3;

图中的 pos 就是地位信息（rowid），它示意的是：每个引擎用来惟一标识数据行的信息。对于有主键的 InnoDB 表来说，这个 rowid 就是主键 ID。
到这里，略微小结一下：order by rand() 应用了内存长期表，内存长期表排序的时候应用了 rowid 排序办法。

磁盘长期表

tmp_table_size 这个配置限度了内存长期表的大小，默认值是 16M。 如果长期表大小超过了 tmp_table_size，那么内存长期表就会转成磁盘长期表。磁盘长期表应用的 引擎默认是 InnoDB， 是由参数 internal_tmp_disk_storage_engine 管制的。

当应用磁盘长期表的时候，对应的就是一个没有显式索引的 InnoDB 表的排序过程。

九、为什么有些 SQL 语句逻辑雷同，性能却差别微小？

条件字段函数操作

统计产生在所有年份中 7 月份的交易记录总数。
select count(*) from tradelog where month(t_modified)=7;
t_modified 字段上有索引，然而对字段做了函数计算，就用不上索引了。为什么条件是 where t_modified='2018-7-1'的时候能够用上索引，而改成 where month(t_modified)=7 的时候就不行了？对索引字段做函数操作，可能会毁坏索引值的有序性，因而优化器就决定放弃走树搜寻性能。改为：

 select count(*) from tradelog 
 where (t_modified >= '2016-7-1' and t_modified<'2016-8-1') 
    or (t_modified >= '2017-7-1' and t_modified<'2017-8-1') 
    or (t_modified >= '2018-7-1' and t_modified<'2018-8-1');

隐式类型转换

select * from tradelog where tradeid=110717;
tradeid 的字段类型是 varchar(32)，而输出的参数却是整型，所以须要做类型转换。对于优化器来说，这个语句相当于:
select * from tradelog where CAST(tradid AS signed int) = 110717;

隐式字符编码转换

字符集不同造成的问题：tradeid 不同
select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;