【Mysql】深分页问题、页分裂问题、加密/解密、执行计划

1、order by 是怎么实现的？

order by 是做排序的，具体怎么排取决于优化器的选择，如果优化器认为走索引更快，那么就会用索引排序，否则，就会使用filesort (执行计划中extra中提示：using filesort），但是能走索引排序的情况并不多，并且确定性也没有那么强，很多时候，还是走的filesort。

filesort这种排序方式中，如果需要排序的内容比较少，就会基于内存中的sort_buffer，否则就需要使用临时文件进行排序了。并且在实际排序过程中，如果字段长度并不是特别长，那么就会使用全字段排序的方式直接在sort_buffer中排序后返回结果集。如果字段长度特别长，那么就可能基于空间考虑，采用row_id排序，这样就会在排序后进行二次回表后返回结果集。

1.1 filesort 排序

如果不能使用或者优化器认为索引排序效率不高时， MySQL 会执行filesort操作以读取表中的行并对它们进行排序。

在进行排序时，MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer，它的大小是由sort_buffer_size控制的。

而根据sort_buffer_size的大小不同，会在不同的地方进行排序操作：
● 如果要排序的数据量小于 sort_buffer_size，那么排序就在内存中完成。
● 如果排序数据量大于sort_buffer_size，则需要利用磁盘临时文件辅助排序。

除了sort_buffer_size参数以外，影响排序的算法的还有一个关键参数：max_length_for_sort_data

max_length_for_sort_data是 MySQL 中控制<用于排序的行数据的长度>的一个参数，默认值是1024字节。如果单行的长度超过这个值，MySQL就认为单行太大，那么就会采用rowid 排序，否则就进行全字段排序。

1.2 全字段排序（也叫单路排序）

所谓全字段排序，就是将要查询的所有字段都放到sort_buffer中，然后再根据排序字段进行排序，排好之后直接把结果集返回给客户端。

全字段排序的好处就是只对原表进行了一次回表查询（每条记录只需要回表一次），之后的排序好以后就可以直接把需要的字段返回了。所以他的效率比较高。但是他的缺点就是，如果要查询的字段比较多，那么就会比较耗费sort_buffer的空间，使得空间中能存储的数据很少。那么如果要排序的数据量变大，就会要用到临时文件，导致整体的性能下降。

1.3 row_id 排序（也叫双路排序）

在构建sort_buffer的时候，不要把所有的要查询字段都放进去，只把排序字段的主键放进去就行了。这样就可以解决单路排序中耗费sort_buffer空间高的问题，避免使用到临时文件。缺点是要多一次回表操作。

2、MySQL的深分页问题

2.1 limit执行顺序

在一个SQL查询语句的顺序中，limit其实是在最后执行的，也就是说，在做完筛选、分组、排序等操作之后，最后进行的limit。因为它是对最终结果集的限制。所以在执行完其他所有操作后，才应用 LIMIT，从而确保查询返回的结果集已经是经过完整处理的。

还有就是，limit的查询中，如果是像 LIMIT 10000, 100 这种形式 ，他会先查询出全部数据（10000+100），然后丢弃前面的结果，再返回需要的部分。 这也是为什么深分页很慢的原因。

2.2 如何优化深分页问题

2.2.1 使用子查询和JOIN优化

假如我们这样一条SQL：

SELECT c1, c2, cn... FROM table WHERE name = "jack" LIMIT 1000000,10

可以基于子查询进行优化，如以下SQL：

SELECT c1, c2, cn...
FROM table
INNER JOIN (
    SELECT id
    FROM table
    WHERE name = "jack"
    ORDER BY id
    LIMIT 1000000, 10
) AS subquery ON table.id = subquery.id

使用一个子查询来获取限定条件下的一小部分主键id，这部分 id 对应于我们分页的目标区域。然后，使用这些 id 在主查询中获取完整的行数据。

以上SQL，在name有索引的情况下，子查询中查询id是不需要回表的。而当我们查询出我们想要的10个ID之后，基于ID查询不仅快，而且要查的数据量也很少。

2.2.2 使用子查询和ID过滤优化

如果主键ID是递增的，还可以用下面这个方式，原理其实和第一种是类似的：