mysql实战45讲--- 16-19

16 | “order by”是怎么工作的？

假设部分表定义：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

假设按照下面的sql查询并排序。

select city,name,age from t where city='杭州' order by name limit 1000  ;

全字段排序

explain

Extra中"Using filesort"表示排序，mysql会给每个线程分配一个块内存（sort_buffer）用来排序。
city索引示意图：

sql执行过程：

     1. 初始化sort_buffer，确定放入name、city、age 这三个字段；
     2. 从city索引找到第一个city='杭州’的主键id，图中的ID_X；
     3. 根据id去聚集索引取这三个字段，放到sort_buffer；
     4. 在从city索引取下一个；
     5. 重复3、4查询所有的值；
     6. 在sort_buffer按name快速排序；
     7. 按照排序结果取前1000行返回给客户端。
如果sort_buffer太小，内存放不下排序的数据，则需要使用外部排序，利用磁盘临时文件辅助排序。这取决于排序所需内存和参数 sort_buffer_size。
下面方法可以确定排序是否使用临时文件：

/* 打开 optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 
/* @a 保存 Innodb_rows_read 的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';
/* 执行语句 */
select city, name,age from t where city='杭州' order by name limit 1000; 
/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G
/* @b 保存 Innodb_rows_read 的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';
/* 计算 Innodb_rows_read 差值 */
select @b-@a;

通过查看 OPTIMIZER_TRACE，number_of_tmp_files表示排序使用的临时文件数，外部排序一般使用归并排序算法。
rows表示满足city='杭州’有4000条，examined_rows=4000表示4000行参与排序。
sort_mode packed_additional_fields表示排序过程字符串做了“紧凑”处理。name字段定义varchar(16)，排序过程中按照实际长度分配空间。
最后一个查询语句 select @b-@a返回结果是 4000，表示只扫描了4000行。

这边老师把internal_tmp_disk_storage_engine 设置成MyISAM，否则，select @b-@a结果为 4001。因为innodb把数据从临时表取出来时，会让Innodb_rows_read 的值加 1。

 

rowid 排序

如果排序的单行长度太大mysql会使用另一种算法。

SET max_length_for_sort_data = 16;

city、name、age 这三个字段的定义总长度是 36 > max_length_for_sort_data，所以会使用别的算法。
该算法和全字段排序的差别：

    1. sort_buffer只会确定放入name 和 id字段，所以只会取这两个字段。
    2. 最后根据name排完序，会根据id字段去原表取city、name 和 age 三个字段返回给客户端。

需要注意，不做合并操作，而是直接将原表查到的字段返回给客户端。
和上述过程对比：

examined_rows和rows没有变化，但select @b-@a会变成5000。因为排完序需要去原表再取1000行。

 

全字段排序 VS rowid 排序

对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择。
假设从city索引上取出来的行天然按照name递增排序，就不需要再进行排序了。
所以可以建一个city和name的联合索引：

alter table t add index city_user(city, name);

整个查询流程就变成了：

      1. 从索引(city, name)找到第一个city='杭州’的主键id；
      2. 到聚集索引取name、city、age三个字段，作为结果集一部分直接返回；
      3. 从索引(city, name)取下一个。
      4. 重复2、3，直到查到1000条记录，或不满足city='杭州’时结束。
explian:

没有"Using filesort"。
使用覆盖索引：

alter table t add index city_user_age(city, name, age);

但维护索引是有代价的，所以需要权衡。

小结

mysql> select * from t where city in ('杭州'," 苏州 ") order by name limit 100;

上述sql需要排序，因为name不是递增的。
可以将sql拆分成两条，最后通过程序内存取前100条。
进一步，如果需要分页，“limit 10000,100”，则可以使用下面的思想：

select * from t where city=" 杭州 " order by name limit 10100; 
select * from t where city=" 苏州 " order by name limit 10100。

根据，name排序，然后取10001~10100，但这样返回的数据量较大，所以可以改成：

select id,name from t where city=" 杭州 " order by name limit 10100; 
select id,name from t where city=" 苏州 " order by name limit 10100。

根据，name排序，然后取10001~10100，然后在通过id查询100条数据。

另外

评论区大神多，特别是@某、人，看到好多次了。下面是他的回答：
问题一 ：这种无条件查列表页除了全表扫还有其他建立索引的办法么
   1)无条件查询如果只有order by create_time,即便create_time上有索引,也不会使用到。
      因为优化器认为走二级索引再去回表成本比全表扫描排序更高。
      所以选择走全表扫描,然后根据老师讲的两种方式选择一种来排序
   2)无条件查询但是是order by create_time limit m.如果m值较小,是可以走索引的.
      因为优化器认为根据索引有序性去回表查数据,然后得到m条数据,就可以终止循环,那么成本比全表扫描小,则选择走二级索引。
      即便没有二级索引,mysql针对order by limit也做了优化,采用堆排序。这部分老师明天会讲
问题二 : 如果加入 group by ， 数据该如何走
    如果是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(20) 、 tinyint(2) 、varchar(32) 这种后面带数字与不带数字有何区别的文章么 。 每次建 .   字段都会考虑长度 ，但实际却不知道他有何作用
    bigint和int加数字都不影响能存储的值。
    bigint(1)和bigint(19)都能存储2^64-1范围内的值,int是 2^32-1。只是有些前端会根据括号里来截取显示而已。建议不加varchar() .   就必须带,因为varchar()括号里的数字代表能存多少字符。假设varchar(2),就只能存两个字符,不管是中文还是英文。目前来看 .       varchar()这个值可以设得稍稍大点,因为内存是按照实际的大小来分配内存空间的,不是按照值来预分配的。
 

17 | 如何正确地显示随机消息？

mysql> CREATE TABLE `words` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `word` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=0;
  while i<10000 do
    insert into words(word) values(concat(char(97+(i div 1000)), char(97+(i % 1000 div 100)), char(97+(i % 100 div 10)), char(97+(i % 10))));
    set i=i+1;
  end while;
end;;
delimiter ;

call idata();

需求：每次随机获取三个word；

内存临时表

mysql> select word from words order by rand() limit 3;

explain:

这个 Extra 的意思就是，需要临时表，并且需要在临时表上排序。
上一篇文章的一个结论：对于 InnoDB 表来说，执行全字段排序会减少磁盘访问，因此会被优先选择。
**对于内存表，回表过程只是简单地根据数据行的位置，直接访问内存得到数据，根本不会导致多访问磁盘。**所以，MySQL 这时就会选择 rowid 排序。
上述sql的执行流程：

    1. 创建一个memory引擎的临时表，第一个字段double类型，假设字段为R，第二个字段varchar(64)，记为字段W。并且这个表没有索引。
    2. 从 words 表中，按主键顺序取出所有的 word 值。对于每一个 word 值，调用 rand() 函数生成一个大于 0 小于 1 的随机小数，并把这个随机小数和 word分别存入临时表的 R 和 W 字段中，到此，扫描行数是 10000。
    3. 接着在没有索引的内存临时表上，按字段R排序。
    4. 初始化sort_buffer。sort_buffer和临时表一直两个字段。
    5. 临时表全表扫描去取R值和位置信息（稍后解释），放入sort_buffer两个字段，此时扫描行数增加10000，变成20000。
    6. 在sort_buffer对R值排序。
    7. 排序完成取前三行，总扫描行数变成20003行。
通过慢查询日志（slow log）可以看到

# Query_time: 0.900376  Lock_time: 0.000347 Rows_sent: 3 Rows_examined: 20003
SET timestamp=1541402277;
select word from words order by rand() limit 3;

流程图如下，图中的pos就是位置信息，类似主键id：

磁盘临时表

tmp_table_size限制了内存临时表的大小，默认16M。如果内存大于tmp_table_size，则会转成磁盘临时表。
磁盘临时表使用的引擎默认是 InnoDB，由参数 internal_tmp_disk_storage_engine 控制。
复现：

set tmp_table_size=1024;
set sort_buffer_size=32768;
set max_length_for_sort_data=16;
/* 打开 optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 
/* 执行语句 */
select word from words order by rand() limit 3;
/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

部分PTIMIZER_TRACE 的结果如下：

由于max_length_for_sort_data 设置成 16，所以参与排序的是R字段和row_id字段组成的行。
R字段8个字节，rowid是6个字节，总行数10000，这样总共140000字节，超过sort_buffer_size，但没有使用临时文件。
是因为MySQL 5.6 版本引入的一个新的排序算法，即：优先队列排序算法。
因为sql只需要去R值最小的3个rowid，所以不需要将所有的数据排序，所以没有使用临时文件（归并排序算法）。

优先级队列算法执行流程如下：

    1. 先取前三行，构造成一个堆。
    2. 取下一行(R’,rowid’)，跟当前堆最大的R比较，如果 R’小于 R，把这个 (R,rowid)从堆中去掉，换成 (R’,rowid’)；
    3. 重复第 2 步，直到第 10000 个 (R’,rowid’) 完成比较。
上图OPTIMIZER_TRACE 结果中，filesort_priority_queue_optimization 这个部分的chosen=true，就表示使用了优先队列排序算法。

select city,name,age from t where city='杭州' order by name limit 1000;

这句sql没有使用优先队列排序算法，因为limit 1000堆大小超过了sort_buffer_size 大小。

随机排序方法

随机选取一个word值。

mysql> select max(id),min(id) into @M,@N from t ;
set @X= floor((@M-@N+1)*rand() + @N);
select * from t where id >= @X limit 1;

取 max(id) 和 min(id) 都是不需要扫描索引，而第三步的 select 也可以用索引快速定位，可以认为就只扫描了3行。
但id中间可能有空洞，所以不同行概率不一样。
所以，为了得到严格随机的结果，你可以用下面这个流程:

mysql> select count(*) into @C from t;
set @Y = floor(@C * rand());
set @sql = concat("select * from t limit ", @Y, ",1");
prepare stmt from @sql;
execute stmt;
DEALLOCATE prepare stmt;

MySQL 处理 limit Y,1 的做法就是按顺序一个一个地读出来，丢掉前 Y 个，然后把下一个记录作为返回结果，此这一步需要扫描 Y+1 行。
再加上，第一步扫描的 C 行，总共需要扫描 C+Y+1 行，执行代价比第一个随机算法的代价要高。
另外一个思路：

mysql> select count(*) into @C from t;
set @Y1 = floor(@C * rand());
set @Y2 = floor(@C * rand());
set @Y3 = floor(@C * rand());
select * from t limit @Y1，1； // 在应用代码里面取 Y1、Y2、Y3 值，拼出 SQL 后执行
select * from t limit @Y2，1；
select * from t limit @Y3，1；

 

18 | 为什么这些SQL语句逻辑相同，性能却差异巨大？

案例一：条件字段函数操作

不要对字段进行计算。

select * from tradelog where id + 1 = 10000;
select count(*) from tradelog where month(t_modified)=7;

案例二：隐式类型转换

数据类型与字段类型不同的，将导致全表扫描。

//判断mysql怎么进行数据类型转换，下面的字符串会转成数字，返回1
select “10” > 9;

案例三：隐式字符编码转换

两张表编码格式不一致也会导致全表查询。

 

19 | 为什么我只查一行的语句，也执行这么慢？

本节表结构和数据。

mysql> CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into t values(i,i)
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

第一类：查询长时间不返回

等MDL锁

show processlist;
//或下面sql，可以找出pid（设置 performance_schema=on，相比于设置为 off 会有 10% 左右的性能损失）
select blocking_pid from sys.schema_table_lock_waits;

等flush

//该sql可以查询到当前状态
select * from information_schema.processlist where id= 'pid';

如果查到如下图所示，则表示有线程正要对表进行flush操作。

MySQL 里面对表做 flush 操作的用法，一般有以下两个：

flush tables t with read lock;
flush tables with read lock;

等行锁

 

select * from t sys.innodb_lock_waits where locked_table=table_name \G

KILL pid 断开连接，隐含逻辑自动回滚这个连接里面正在执行的线程，释放行锁。

 

第二类：查询慢

select * from t where c=50000 limit 1;

如果字段c上没有索引，这个语句只能走id主键顺序扫描，需要扫描5万行。
扫描一行却很慢的语句

session B执行100万次后，生成了100万个undo log，所以第一个select 快照读要将undo log执行100万次回到快照的版本。而第二个select当前读。

最后，老师提了for update加锁，关于各隔离级别加锁情况参考链接，这个大哥写的可以。