老师，基于早上知道的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） 不知道这么理解对不对？

re: 问题3:回答得也很好，需要注意的是255这个边界。小于255都需要一个字节记录长度，超过255就需要两个字节 11 月过数据库设计方案，总监现场抛了一个问题，就是关于 varchar 255 的。现在回看，木有人回答到点上，都说是历史原因。 下回再问，就可以分享这一点了。ꉂ ೭(˵¯̴͒ꇴ¯̴͒˵)౨”哇哈哈～

回答下@发条橙子同学的问题: 问题一： 1)无条件查询如果只有order by create_time,即便create_time上有索引,也不会使用到。 因为优化器认为走二级索引再去回表成本比全表扫描排序更高。 所以选择走全表扫描,然后根据老师讲的两种方式选择一种来排序 2)无条件查询但是是order by create_time limit m.如果m值较小,是可以走索引的. 因为优化器认为根据索引有序性去回表查数据,然后得到m条数据,就可以终止循环,那么成本比全表扫描小,则选择走二级索引。 即便没有二级索引,mysql针对order by limit也做了优化,采用堆排序。这部分老师明天会讲 问题二: 如果是group by a,a上不能使用索引的情况,是走rowid排序。 如果是group by limit,不能使用索引的情况,是走堆排序 如果是只有group by a,a上有索引的情况,又根据选取值不同,索引的扫描方式又有不同 select * from t group by a --走的是索引全扫描,至于这里为什么选择走索引全扫描,还需要老师解惑下 select a from t group by a --走的是索引松散扫描,也就说只需要扫描每组的第一行数据即可,不用扫描每一行的值 问题三: bigint和int加数字都不影响能存储的值。 bigint(1)和bigint(19)都能存储2^64-1范围内的值,int是2^32-1。只是有些前端会根据括号里来截取显示而已。建议不加varchar()就必须带,因为varchar()括号里的数字代表能存多少字符。假设varchar(2),就只能存两个字符,不管是中文还是英文。目前来看varchar()这个值可以设得稍稍大点,因为内存是按照实际的大小来分配内存空间的,不是按照值来预分配的。 老师我有几个问题: 1.我还是想在确认之前问的问题。一个长连接,一条sql申请了sort_buffer_size等一系列的会话级别的内存,sql成功执行完,该连接变为sleep状态。这些内存只是内容会被情况,但是占用的内存空间不会释放? 2.假设要给a值加1,执行器先找引擎取a=1的行,然后执行器给a+1,在调用接口写入a+1了数据。那么加锁不应该是在执行器第一次去取数据时，引擎层就加该加的锁？为什么要等到第二次调用写入数据时,才加锁。第一次和第二次之间,难道不会被其他事务修改吗？如果没有锁保证 3.始终没太明白堆排序是采用的什么算法使得只需要对limit的数据进行排序就可以,而不是排序所有的数据在取前m条。--不过这里期待明天的文章

刚才又测了一下，在binlog-row-image=full的情况下，第二次update是不写redolog的，说明update并没有发生 这样我就理解了，当full时，mysql需要读到在更新时读到a值，所以会判断a值不变，不需要更新，与你给出的update t set a=3 where id=1 and a=3原理相同，但binlog-row-image会影响查询结果还是会让人吃一惊

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.按照排序的结果返回客户所取行数

关于上期问题里的最后一个例子不太明白，还请老师指点一下。按说在更新操作的时候应该是当前读，那么应该能读到id=1 and a = 3的记录并修改。那么为什么再select还会查到a = 2。难道是即便update但是where条件也是快照读？但是如果这样那么幻读的问题不就不会存在了吗？（B insert了一条记录，此时A范围update后再select会把B insert的语句查出来）

不好意思，上个留言没打完。 问题一，在跟max_length_for_sort_data坐比较时，mysql是怎么判断一行数据的大小的？是直接根据表定义字段的大小吗？ 问题二，另外这‘一行’的含义是整行数据，还是单单最终引擎层需要返回的字段(即select字段+where字段+order by字段)？ 麻烦老师有空解答下，谢谢哈

老师 ， 接前面 create_time的回答 。 语句确实是 select * from t order by create_time desc ; 老师是指 优化器会根据 order by create_time 来选择使用 create_time 索引么 我之前误以为优化器是根据 where 后面的字段条件来选择索引 ，所以上面那条语句没有where 的时候我就想当然地以为不会走索引 。 看来是自己跳进了一个大坑里面 😅 另 ： 我之前在本地建了张表加了20w数据 ，用explain 查了一次 ，发现走的是全表没有走索引， 老师说会走索引。我想了一下， 可能是统计的数据有误的缘故，用 analyze table重新统计 ，再次查询果然走了索引 。😁

感谢！针对我之前提出的疑问，我又详细的做了实验，发现一个新的问题，我感觉是个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 |