1. 全字段排序

---

    疑问1:  explain 后, 各个字段代表啥意思?

            答:     https://blog.csdn.net/bingguang1993/article/details/98313390

---

    1.sort_buffer : mysql给每个线程分配一块内存用于排序

1. using filesort 需要排序 2. 索引city 示意图

    2.执行计划: 

            1.初始化sort_buffer , 放入 name, city , age 三个字段;

            2.从索引city 找到第一个满足city = '杭州'条件的主键id ==> 图中ID_X

            3.到主键id索引取出整行, 区 name, city , age 三个字段的值, 存入sort_buffer

            4.从索引city 取下一个记录的主键id

            5.重复 3 , 4 知道city 的值不满足查询条件位置.  ==> 图中ID_Y

            6.对sort_buffer中的数据按照字段name 做快速排序

            7.取快排结果前1000行返回客户端. 

3.全字段排序

   3.参数 sort_buffer_size  ==> 为程序开辟的内存大小

            数据量 <  sort_buffer_size  ==> 在内存中完成

            数据量 > sort_buffer_size  ==> 磁盘临时文件排序

    4.有方法可以确定排序是否使用了临时文件: 

4. 全排序的 OPTIMIZER_TRACE

           1. number_of_tmp_files ==> 使用了几个临时文件  ==> 归并排序

    疑问2 : 为什么 sort_buffer_size 越小,  number_of_tmp_files  就越大?  

                难道 是总数据量 / sort_buffer_size  = number_of_tmp_files  ? 

           2.examined_rows ==> 检查行数 ==> 参与排序的行数

           3. internal_tmp_disk_storage_engine 设置成 MyISAM (为了避免干扰)

           4.因为查OPTIMIZER_TRACE 要用到临时表,如果使用innodb , 把数据从临时表取出来, 会让 innodb_rows_read 加1

       疑问3 : 什么场景会用到临时表 ?

       疑问4 : 内存临时表和磁盘临时表有什么不同 ?   

            灵光: 其实临时表就是临时文件, 因为表就是以文件形式储存的. 

2.row id排序 

    1.全字段排序缺点: 

        1如果查询要返回的字段很多, sort_buffer中要放的字段太多, 需要很多临时文件, 性能差.

        2单行长度太大 , 性能差

    2.max_length_for_sort_data  如果单行长度大于该值 则使用row id 排序(单位为char)

5. row id 排序

        3.执行计划: 

            1与全字段排序不同点: 

                1.只把 id  ,  name 放入sort_buffer中 

                2. 最后多了一次回表, 取目标name, city , age 

            因为需要查询的字段长度太大,所以使用这个方式. 机智啊! 

6. row id 的OPTIMIZER_TRACE

            2. number_of_tmp_files 变成10 , 变小了. 因为每一行 从name ,city ,age 变成了 id , name, 长度小了

3. 全字段排序  VS row id 排序 : 

    1.优先选择全字段排序, 因为少一次回表. 

设计思想: 如果内存够, 尽量使用内存, 不得已再使用磁盘. 

    2. 使用临时表的原因是 , 原来的数据是无序的. 

        ==> 如果保证从city所以中取出的行, 是按照name排序的. 就不用再排序. 

        ==> 所以可以使用联合索引

4更快的方法

    1.联合索引

7. 联合索引

    执行计划: 

        1.从索引(city, name)找到第一个满足 city = ' 杭州'条件的主键id

        2.到主键id索引取出整行, 取name, city, age 三个字段的值, 返回

        3. 从索引 (city, name) 去下一个记录主键id

        4.重复步骤2, 3 , 直到查到1000条记录, 或者是不满足city = '杭州'条件. 

8. 联合索引执行计划 9.联合索引 explain

    4.覆盖索引 ==> 性能最高, 但是要权衡索引维护代价

        ==>建立索引(city, name, age)

        执行计划: 

        1.从索引(city, name, age)找到第一个满足 city = ' 杭州'条件的主键id,  取name, city, age 三个字段的值, 返回

        2. 从索引 (city, name, age) 取下一个记录, 从索引中取出三个值.

        3.重复步骤2 , 直到查到1000条记录, 或者是不满足city = '杭州'条件. 

10. 覆盖索引执行计划 11. 覆盖索引expalin