《丁奇-MySQL45讲-16/17》之归纳总结

16 | order by 是怎么工作的？

  • Extra字段中显示Using filesort表示需要排序。MySQL会给每个线程分配一块内存用于排序，称为sort_buffer，将满足条件的记录放入到该缓冲区中，然后根据指定字段排序。排序这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数sort_buffer_size。sort_buffer_size是MySQL为排序开辟的内存（sort_buffer）的大小，如果要排序的数据量小于sort_buffer_size，排序就在内存中完成，但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序，这种排序方式一般是归并排序算法。

  • 查看排序语句是否使用了临时文件：


/* 打开optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 

/* @a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 执行语句 */
select city, name,age from t where city='杭州' order by name limit 1000; 

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

/* @b保存Innodb_rows_read的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 计算Innodb_rows_read差值 */
select @b-@a;

这个方法是通过查看OPTIMIZER_TRACE的结果来确认的，你可以从number_of_tmp_files中看到是否使用了临时文件。

  • 归并排序算法：将数据分成多份，每一份单独排序，然后在将这些小文件合并成一个大文件。

  • 如果要返回的字段很多的话，就会导致如果sort_buffer能存放的数据就变少了，所需要的临时文件也就越大，如果MySQL认为单行的数据过大（通过max_length_for_sort_data来配置行数的字节限制），就会采用另外一种算法，只将满足条件的记录的排序字段和主键id放入到sort_buffer里，然后根据指定字段进行排序，排完序后使用id到主索引中取出剩余要返回的字段，取一个就返回一个，不占用其他额外内存。

  • Extra字段中显示Using index表示的就是使用了覆盖索引，注意，如果要查询的字段并没有包括在二级索引中，那么也不会是覆盖索引，只有查询的字段在二级索引中才会是覆盖索引

  • 小于255需要一个字节记录长度，超过255就需要两个字节。1个字节8位，最大值是255，用来记录varchar的长度，如果大于255，1个字节无法存储，因此需要2个字节（16位）。

  • 思考题：select * from t where city in ('杭州', '苏州') order by name limit 100; 这个SQL语句是否需要排序，如果避免排序

（city，name）虽然是联合索引， 对于单个city来说，name是递增的，但对于多个city来说，满足条件的name就不是递增的，所以是需要排序，按照前面说的思路，其实可以把多个city变成单个city，然后在排序最后的name取前100个即可。

17 | 如何正确地显示随机消息？

  • Extra字段显示Using temporary表示需要临时表。

  • explain执行SQL的时候不会考虑limit关键字。

  • 随机数排序的流程：

  1. 创建一个临时表。这个临时表使用的是memory引擎，表里有两个字段，第一个字段是double类型，为了后面描述方便，记为字段R，第二个字段是varchar(64) 类型，记为字段W。并且，这个表没有建索引。

  2. 从words表中，按主键顺序取出所有的word值。对于每一个word值，调用rand函数生成一个大于0小于1的随机小数，并把这个随机小数和word分别存入临时表的R和W字段中，到此，扫描行数是10000。现在临时表有10000行数据了，接下来你要在这个没有索引的内存临时表上，按照字段R排序。

  3. 初始化sort_buffer。sort_buffer中有两个字段，一个是double类型，另一个是整型。从内存临时表中一行一行地取出R值和位置信息（我后面会和你解释这里为什么是"位置信息"），分别存入sort_buffer中的两个字段里。这个过程要对内存临时表做全表扫描，此时扫描行数增加10000，变成了20000。

  4. 在sort_buffer中根据R的值进行排序。注意，这个过程没有涉及到表操作，所以不会增加扫描行数。排序完成后，取出前三个结果的位置信息，依次到内存临时表中取出word值，返回给客户端。这个过程中，访问了表的三行数据，总扫描行数变成了20003。

由于后续会采用优先级队列排序，那么先将这10000条数据放入到sort_buffer中在排序呢，可是10000 * 14 > sort_buffer_size（参看丁奇老师的文章）已经放不下了，就需要磁盘的临时文件来辅助，因为他也举例了为什么limit 1000就不能使用优先级队列排序，而是只能使用归并排序算法，所以我个人认为应该是先取3（看limit的值）条放入到sort_buffer构成最大堆，然后从临时表中依次取剩下的数据到sort_buffer进行比较，这样子sort_buffer中始终都是只有3条，也小于sort_buffer_size，符合没有使用临时文件的关系。

  • 位置信息的概念：图中的 pos 就是位置信息，你可能会觉得奇怪，这里的"位置信息"是个什么概念？在上一篇文章中，我们对InnoDB表排序的时候，明明用的还是ID字段。这时候，我们就要回到一个基本概念：MySQL 的表是用什么方法来定位"一行数据"的。在前面第4和第5篇介绍索引的文章中，有几位同学问到，如果把一个InnoDB 表的主键删掉，是不是就没有主键，就没办法回表了？其实不是的。如果你创建的表没有主键，或者把一个表的主键删掉了，它会按照表结构定义的顺序选取一个非空唯一索引作为主键，如果没有的话InnoDB会自己生成一个长度为6字节的 rowid 来作为主键。这也就是排序模式里面，rowid 名字的来历。实际上它表示的是：每个引擎用来唯一标识数据行的信息。对于有主键的InnoDB表来说，这个rowid就是主键ID；对于没有主键的InnoDB表来说，这个rowid就是由系统生成的；MEMORY引擎不是索引组织表。在这个例子里面，你可以认为它就是一个数组，因此，这个rowid其实就是数组的下标。

  • 优先队列排序：对于10000个准备排序的数据，取前几行（看limit具体值）构造成一个最大堆（根节点是最大值），然后将剩余的数据依次与根节点进行比较，如果小于根节点，则用这个数据替换掉根节点，然后重新平衡堆，接下比较下一个数据，最终堆里的数据就是10000个数据中最小的数据之一。

  • 返回的字段长度（一行） > max_length_for_sort_data -> rowid排序。

  • 返回的字段长度（一行） < max_length_for_sort_data -> 全字段排序。

  • 总数据 > sort_buffer_size -> 利用磁盘的临时文件辅助排序，根据limit x的值来判断sort_buffer是否可以容纳，若可以的话则是优先级队列排序，否则就是归并排序。

  • 总数据 < sort_buffer_size -> 直接在内存中排序。

  • MySQL处理limit Y，1的做法是按顺序一个一个地读出来，然后丢掉前Y个，接着把下一个记录作为返回结果。

  • 思考题：如何更好的随机取三条数据？


    select * from t limit @Y1, 1;
    select * from t limit @Y2, 1;
    select * from t limit @Y3, 1;

以上方式需要扫描的行数是 C + (Y1 + 1) + (Y2 + 1) + (Y3 + 1)，针对这种方式在进行优化：

  1. 首先还是扫描整张表的行数，总共有C行。

  2. 根据共有C行随机取某一行，一共取3次，分别是Y1、Y2、Y3。

  3. 在Y1、Y2、Y3中找出最大值Ymax、最小值Ymin，然后执行select * from t limit Ymin, (Ymax - Ymin + 1)。

  4. 然后根据Y1、Y2、Y2找到对应所需要的数据。

posted @ 2021-03-27 22:18  zliawk  阅读(75)  评论(0)    收藏  举报