老师，基于早上知道的sort_buffer是在server层，我重新理解了下rowid排序的过程，
1，执行器查看表定义，发现name、city、age字段的长度之和超过max_length_for_sort_data，所以初始化sort_buffer的时候只放入id和name字段。
2，执行器调用存储引擎的读数据接口，依次获取满足条件的数据的id和name，存入sort_buffer。
3，排序。
4，执行器根据limit条件筛选出id，再次调用引擎读数据的接口获取相应的数据，返回客户端。
整个过程实际上是被执行器拆成了两次查询，共调用两次存储层的读数据接口，所以总的扫描行数需要相加。（@b-@a=5000）

但是对于using index condition的场景，执行器只调用了一次查询接口，回表是由存储层来完成的，所以扫描行数只算一次，即只算走索引搜索的过程中扫描的行数。（@b-@a只会是4000）

不知道这么理解对不对？

1)
mysql> select * from t where city in ('杭州'," 苏州 ") order by name limit 100;
需要排序
原因是索引顺序城市、名称 与 单独按name排序的顺序不一致。

2）如果不想mysql排序
方案a
可以执行两条语句
select * from t where city = '杭州' limit 100;
select * from t where city = '苏州' limit 100;
然后把200条记录在java中排序。
方案b
分别取前100，然后在数据端对200条数据进行排序。可以sort buffer就可以完成排序了。
少了一次应用程序与数据库的网络交互
select * from (
    select * from t where city = '杭州' limit 100
    union all
  select * from t where city = '苏州' limit 100
) as tt order by name limit 100


3）对分页的优化。
  没有特别好的办法。如果业务允许不提供排序功能，不提供查询最后一页，只能一页一页的翻，基本上前几页的数据已经满足客户需求。
  为了意义不大的功能优化，可能会得不偿失。
  如果一定要优化可以 select id from t where city in ('杭州'," 苏州 ") order by name limit 10000,100
  因为有city\name索引，上面的语句走覆盖索引就可以完成，不用回表。
  最后使用 select * from t where id in (); 取得结果
  对于这个优化方法，我不好确定的是临界点，前几页直接查询就可以，最后几页使用这个优化方法。
  但是中间的页码应该怎么选择不太清楚
  

笔记:
1.MySQL会为每个线程分配一个内存（sort_buffer）用于排序该内存大小为sort_buffer_size
  1>如果排序的数据量小于sort_buffer_size，排序将会在内存中完成
  2>如果排序数据量很大，内存中无法存下这么多数据，则会使用磁盘临时文件来辅助排序，也称外部排序
  3>在使用外部排序时，MySQL会分成好几份单独的临时文件用来存放排序后的数据，然后在将这些文件合并成一个大文件


2.mysql会通过遍历索引将满足条件的数据读取到sort_buffer，并且按照排序字段进行快速排序
    1>如果查询的字段不包含在辅助索引中，需要按照辅助索引记录的主键返回聚集索引取出所需字段
      2>该方式会造成随机IO，在MySQL5.6提供了MRR的机制，会将辅助索引匹配记录的主键取出来在内存中进行排序，然后在回表
     3>按照情况建立联合索引来避免排序所带来的性能损耗，允许的情况下也可以建立覆盖索引来避免回表

全字段排序
1.通过索引将所需的字段全部读取到sort_buffer中
2.按照排序字段进行排序
3.将结果集返回给客户端


缺点：
1.造成sort_buffer中存放不下很多数据，因为除了排序字段还存放其他字段，对sort_buffer的利用效率不高
2.当所需排序数据量很大时，会有很多的临时文件，排序性能也会很差

优点：MySQL认为内存足够大时会优先选择全字段排序，因为这种方式比rowid 排序避免了一次回表操作


rowid排序
1.通过控制排序的行数据的长度来让sort_buffer中尽可能多的存放数据，max_length_for_sort_data
2.只将需要排序的字段和主键读取到sort_buffer中，并按照排序字段进行排序
3.按照排序后的顺序，取id进行回表取出想要获取的数据
4.将结果集返回给客户端

优点：更好的利用内存的sort_buffer进行排序操作，尽量减少对磁盘的访问

缺点：回表的操作是随机IO，会造成大量的随机读，不一定就比全字段排序减少对磁盘的访问


3.按照排序的结果返回客户所取行数

老师 ， 接前面 create_time的回答 。 语句确实是 select * from t order by create_time desc ;

老师是指 优化器会根据 order by create_time 来选择使用 create_time 索引么

我之前误以为优化器是根据 where 后面的字段条件来选择索引 ，所以上面那条语句没有where 的时候我就想当然地以为不会走索引 。 看来是自己跳进了一个大坑里面 😅

另 ： 我之前在本地建了张表加了20w数据 ，用explain 查了一次 ，发现走的是全表没有走索引， 老师说会走索引。我想了一下， 可能是统计的数据有误的缘故，用 analyze table重新统计 ，再次查询果然走了索引 。😁

不好意思，上个留言没打完。
问题一，在跟max_length_for_sort_data坐比较时，mysql是怎么判断一行数据的大小的？是直接根据表定义字段的大小吗？

问题二，另外这‘一行’的含义是整行数据，还是单单最终引擎层需要返回的字段(即select字段+where字段+order by字段)？

麻烦老师有空解答下，谢谢哈

感谢！针对我之前提出的疑问，我又详细的做了实验，发现一个新的问题，我感觉是个bug，希望解答
# SessionA
mysql> show variables like '%binlog_row_image%';
| Variable_name | Value |
| binlog_row_image | FULL |
mysql> create table t (id int not null primary key auto_increment,
    -> a int default null)
    -> engine=innodb;
mysql> insert into t values(1,2);
mysql> set tx_isolation = 'repeatable-read';
mysql> begin;
mysql> select * from t where id = 1;
| id | a |
| 1 | 2 |
此时在另一个SessionB执行update t set a=3 where id = 1;成功更新一条记录。通过show engine innodb status看，Log sequence number 2573458
然后在SessionA继续。。
mysql> update t set a=3 where id = 1;
Rows matched: 1 Changed: 0 Warnings: 0
Log sequence number 2573467
mysql> select * from t where id = 1;
| id | a |
| 1 | 2 |

这里与你给出的答案里的实验结果不同
可以看到redolog是记录了第二次的update的，但是select却没有看到更新后的值，于是我又换了一个平时测试用的实例，同样的步骤却得到了与你的答案相同的结果
然后我对比了2个实例的参数，发现当binlog-row-image=minimal时第二次查询结果a=3，当binlog-row-image=full时第二次查询结果a=2，而且不论哪个参数，redolog都会因为SessionA的update增长，说明redolog都做了记录，update是发生了的，但是binlog-row-image参数会影响查询结果，难以理解，我用的mysql版本是官方的5.7.13

下面是binlog-row-image = minimal的实验结果
mysql> set binlog_row_image=MINIMAL;
mysql> drop table t;
mysql> create table t (id int not null primary key auto_increment,
    -> a int default null)
    -> engine=innodb;
insert into t values(1,2);
mysql> insert into t values(1,2);
mysql> set tx_isolation = 'repeatable-read';
mysql> begin;
mysql> select * from t where id = 1;
| id | a |
| 1 | 2 |
此时在另一个SessionB执行update t set a=3 where id = 1;成功更新一条记录。
mysql> update t set a=3 where id = 1;
Rows matched: 1 Changed: 0 Warnings: 0
mysql> select * from t where id = 1;
| id | a |
| 1 | 3 |

图 14 可见性验证方式 -- 对照中

session A 的 update t set a = 3 where id = 1 and a = 3

会不会有当前读？如果当前读的话，那是不是会读到session B的更新的值？

请老师帮忙分析下。

正好有个 order by 使用场景 ， 有个页面，需要按数据插入时间倒序来查看一张记录表的信息 ，因为除了分页的参数 ， 没有其他 where 的条件 ，所以除了主键外没有其他索引 。

这时候 DBA 让我给 create_time 创建索引， 说是按照顺序排列 ，查询会增快 。这篇文章看完后 ， 让我感觉实际上创建 create_time 索引是没用的 。

因为查询本身并没有用到 create_time 索引 ，实际上查询的步骤是 ：

1. 初始化 sort_buffer 内存

2. 因为没索引 ， 所以扫出全表的数据到 sort_buffer 中

2. 如果内存够则直接内存按时间排序

3. 如果内存不够则按数据量分成不同文件分别按时间排序后整合

4. 根据数量分页查询数量 回聚集索引中用 ID 查询数据

5. 返回

所以我分析create_time索引应该不需要创建。反而增加了维护成本


问题一 ：这种无条件查列表页除了全表扫还有其他建立索引的办法么

问题二 : 如果加入 group by ， 数据该如何走

问题三 ：老师之后的文章会有讲解 bigInt(20) 、 tinyint(2) 、varchar(32) 这种后面带数字与不带数字有何区别的文章么 。 每次建字段都会考虑长度 ，但实际却不知道他有何作用

把该讲内容总结为几个问题, 大家复习的时候可以先尝试回答这些问题检查自己的掌握程度:
    1.
全字段排序的流程是怎么样的?
    2.
sort_buffer_size, number_of_tmp_files, packed_additional_fields 这几个参数各是什么含义?
    3.
rowid排序的流程是怎么样的?
    4.
通过什么方式可以减少排序对于系统的消耗?

老师你好，全字段排序那一节，我做了实验，我的排序缓存大小是1M， examined rows 是7715892，查询的三个字段都有数据，那么如果这些数据都放到缓存应该需要（4+8+11）*7715892等于160M，但是我看了都没有用到临时表，这是为什么？

CREATE TABLE `phone_call_logs` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `city_id` int(11) NOT NULL DEFAULT '11',
  `call_sender` varchar(40) DEFAULT NULL COMMENT '电话主叫号码',
  `phone_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '手机id',
  PRIMARY KEY (`id`),
  KEY `idx_city` (`city_id`)
) ENGINE=InnoDB AUTO_INCREMENT=64551193;
----------------sort_buffer_size=1M----------------------------
root:(none)> show variables like 'sort_buffer_size';
+------------------+---------+
| Variable_name | Value |
+------------------+---------+
| sort_buffer_size | 1048576 |
+------------------+---------+
1 row in set (0.00 sec)
---------------查询sql---------------------
 select city_id,phone_id,call_sender from phone_call_logs where city_id=11 order by phone_id desc limit 1000;


-----------------------执行计划结果---------------------------------------------

  "filesort_priority_queue_optimization": {
              "limit": 1000,
              "rows_estimate": 146364461,
              "row_size": 146,
              "memory_available": 1048576,
              "chosen": true
            },
            "filesort_execution": [
            ],
            "filesort_summary": {
              "rows": 1001,
              "examined_rows": 7715892,
              "number_of_tmp_files": 0,
              "sort_buffer_size": 154160,
              "sort_mode": "<sort_key, additional_fields>"

1.不会有排序，这种情况属于《高性能mysql》里提到的“in技法”，符合索引的最左原则，是2个等值查询，可以用到右边的索引列。
2.分页查询，可以用延迟关联来优化：
select * from t join
(select id from t where city in('杭州','苏州') order by name limit 10000,100) t_id
on t.id=t_id.id;

课前思考

1：如果排序的字段有索引，怎么排？

由于B+树是天然有序的，所以直接取出的数据也是天然有序的，因此依次取数就行不用再排序了。若取出的字段是覆盖索引，则连回表的动作也不必做啦！

2：如果排序的字段没索引，怎么排？

非索引字段，取出来的时候是按索引的顺序取出的，针对非索引字段无序，需要在内存中通过排序算法来排序。

3：如果数据大到内存放不下，怎么排？

取出的数据无序且体量较大，只能先放到磁盘，然后用外排序的方法排序。

4：如果排序的字段有多个，怎么排？

有多个还是一个其实思路没啥变化，如果原本就有序，则直接取出，否则只能进行排序，体量小在内存中排序，体量大则必须借助磁盘排序。

课后思考

牛逼的系统对于性能的优化都有着不懈的追求，数据库更是如此。

性能优化的原则是恒定的，就看各家怎么发挥啦！

1：就排序而言，如果数据天然有序，则不用排序，如果是覆盖索引，则不用回表，那么这种情况性能应该是最佳的。

2：对于计算机而言，内存比硬盘快多了，能在内存办的事要尽量在内存办。索引当MySQL认为查询的字段长度过大时会进行一定的优化，会只查出主键id和排序的字段，排序字段用于排序，主键id用于回表查询其他的字段，这样速度可能会快一些。

疑问？

1：sort_buffer是MySQL按线程的维度分配的专门用于排序的内存空间，且通过sort_buffer_size可以调整其大小，那么走一下极端情况，若sort_buffer_size=0，是否所有的排序都无法进行啦?若sort_buffer_size=+∞，是否只能有一个线程在同一时刻能排序?另外，sort_buffer设置后是对所有线程都一样限制嘛？其实际分配会根据实际数据量变化呢？还是固定不变?比如：MySQL分配给可用于划分sort_buffer的内存有10m，我设置为10m意味着一个5m的排序会占5m另外5m空着，还是可以跑两个5m的排序？

2：外部排序归并排序，具体怎么排序的？

1m的内存排序2m的数据，每次只能在内存中排序1m的数据，想着想着想明白了，这应该是个通用的方法，只有内存小于数据量都可以这么玩。

3：排序的内存在某线程排完序就释放了，临时文件的回收也是一样的吧？如果数据量大，磁盘不够排序也无法进行的，空间够不够这个判断时机是啥时候？

4：引擎返回数据结果是一条一条，Server返回给客户端也是一条一条嘛？文中图“结果集”是在哪里组合的，Server层？还是客户端？如果是Server层也会存在空间不够的可能吧？此时是先放入磁盘，等数据到齐，再一起发送给客户端吗？

非常感谢老师，提高了，我的认知！