深度分页的例子

    1. select id,m_id, name, identity_no, address, create_time, modify_time
    2. from t2
    3. force index(idx_create_time)
    4. order by create_time desc
    5. limit 1000000, 20;
    7. -- 改为：
    8. SELECT   
    9.     id, m_id, NAME, identity_no, address, create_time, modify_time 
    10. FROM t2
    11. JOIN ( SELECT id FROM t2 ORDER BY create_time desc LIMIT 1000000, 20 ) x USING ( id );
    13. 执行时间：0.742 s elapsed
    14. 效果明显。（原sql执行时长：15s+）

    改成子查询关联后，无论有没有索引都节省了大量时间

    子查询只取了create_time+id到sort buffer（相当于双路排序的做法）, 相比直接查询，省去了绝大部分字段，减少大量临时文件IO操作，因此提高查询效率。

    filesort有两种算法:双路排序和单路排序

    MySQL在4.1之前都是双路排序，之后优化改为满足条件默认单路排序，条件为：查询字段数据大小小于max_length_for_sort_data值，但改到最小值测试也没有看到变化

    双路排序:

    • 根据查询条件将row_id和排序字段取出放到sort buffer （区别1）。
    • 缓冲区满了根据排序字段执行一次排序（快排）把然后把排序后的数据写到临时文件。
    • 将所有数据取出排序后，对所有临时文件按顺序做合并（归并排序）再写回到文件，直到最后所有文件合并完成。
    • 从临时文件中读取满足分页条件的row_id，再通过row_id读取对应行数据返回（区别2）

    单路排序:

    • 根据条件将所有查询字段数据取出到sort buffer缓冲区。
    • 缓冲区满了根据排序字段执行一次排序（快排）把然后把排序后的数据写到临时文件。
    • 将所有数据取出排序后，对所有临时文件按顺序做合并（归并排序）再写回到文件，直到最后所有文件合并完成。
    • 从临时文件中读取满足分页条件所需数据返回，如果首次归并就可以取到分页数据则直接返回（浅分页）。

    单路排序问题:
    在sort_buffer中,要占用更多空间,因为 是把所有字段都取出,所以有可能取出的数据的总大小超出了sort_buffer的容量,导致每次只能取出sort_buffer容量大小的数据,进行排序(创建tmp文件,多路合并),排完再取sort_buffer容量带下,再排…,从而导致多次IO.
    本来想一次IO,反而导致了多次IO操作,得不偿失.

    单路优化策略

    • 增大sort_buffer_size参数的设置
    • 增大max_length_for_sort_data参数的设置

    提高orderby排序

    1. select *是不好的
    2. 当query字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时,会用改进后的算法-单路排序,否则用老算法,多路排序
    3. 两种算法都有可能超出sort_buffer的容量,超出之后,会创建tmp文件进行合并排序,导致多次IO.单路风险更大一点
    4. 尝试提高sort_buffer_size,这个参数是针对每个线程的
    5. 尝试提高max_length_for_sort_data

    为排序使用索引

    key a_b_c (a,b,c)
    orderby能使用索引最左前缀

    1. order by a desc, b desc, c desc

    如果where使用索引的最左前缀定义为常量,则orderby能使用索引

    1. where a = const order by b, c
    2. where a = const and b = const order by c
    3. where a = const order by b, c
    4. where a = const and b > const order by b, c

    不能使用索引

    1. order by a asc, b desc, c desc //排序不一致
    2. where g = const order by b, c //丢失a索引
    3. where a = const order by c //丢失b索引
    4. where a = const order by a, d //d不是索引一部分
    5. where a in (...) order by b, c //对于排序来说,多个相等条件也是范围查询