ChatGPT3.5使用体验( 七 ) _索引

可以看到上面的表，是有一个 city 字段的索引。
select city,name,age from t_user_city where city='上海' order by name limit 1000;
来分析下排序的过程
全字段排序
MySQL 会给每个查询线程分配一个用于排序的内存:。
通过上面查询的栗子来看下MySQL 中是如何使用来进行排序的。
1、首先 MySQL 会给米一个查询线程分配一快大小为的排序内存，放入查询和排序的字段，所以字段city,name,age都会被放入到排序内存中；
2、查询首先使用索引 city 来确定查询的数据，然后查询到的数据都会通过查询到的主键 id 进行一次回表操作，查询到city,name,age字段，然后放入到中；
3、所有的数据都放入到排序内存之后，会根据排序字段对中的数据进行排序；
4、按照排序结果取前 1000 行返回给客户端，整个排序结束。
如果查询的数据量很大，内存中就放不下了，这时候就需要使用磁盘临时文件辅助排序。
使用的就是外部排序，一般使用归并排序。使用外部排序的时候，MySQL 会将排序的文件分成 N 份，每一份单独排序后放入到一个临时文件中，然后再把这 N 个有序文件合成一个有序的大文件。
如果超过了需要排序的数据量的大小，就是0，表示排序可以直接在内存中完成。
在排序的文件在一定额度的情况下，如果越小，那么借助于磁盘排序徐的时候，需要的临时文件也就越多，发生 I/O 的次数也就越多，性能也就越差。
可以通过下面的命令来查看一个排序语句是否使用了临时文件
/* 打开optimizer_trace，只对本线程有效 */SET optimizer_trace='enabled=on'; /* @a保存Innodb_rows_read的初始值 */select VARIABLE_VALUE into @a fromperformance_schema.session_status where variable_name = 'Innodb_rows_read';/* 执行语句 */select city,name,age from t_user_city 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;
这里来重点看下
"filesort_summary": {"memory_available": 262144,"key_size": 512,"row_size": 654,"max_rows_per_buffer": 15,"num_rows_estimate": 15,"num_rows_found": 4, // 参与排序的行"num_initial_chunks_spilled_to_disk": 0, // 表示产生了多少个文件用于外部排序，如果为0，说明没有外部排序"peak_memory_used": 32800,"sort_algorithm": "std::sort","sort_mode": ""}
MySQL 有 3 种排序模式
1、< , rowid >对应的是MySQL 4.1之前的“原始排序模式”，即 rowid 排序；
2、< ,>对应的是MySQL 4.1以后引入的“修改后排序模式”即全字段排序；
3、< , elds >是MySQL 5.7.3以后引入的进一步优化的”打包数据排序模式”，是对全字段排序的优化。对于额外字段数据类型为：CHAR、、以及可为 NULL 的固定长度数据类型，其字段值长度是可以被压缩的。例如，在无压缩的情况下，字段数据类型为(255)，当字段值只有 3 个字符时，却需要占用sort255个字符长度的内存空间大小；而在有压缩的情况下，字段值为 3 个字符，却只占用 3 个字符长度 + 2 个字节长度标记的内存空间大小；当字段值为 NULL 时，只需通过一个位掩码来标识。
rowid 排序
全字段排序会把字段放入到在或临时文件中进行排序，但是当查询的返回的字段很多，那么中要放入的字段很多，那么就意味着能够放下的条数很少了，需要生成的临时文件就会变多，排序的性能会变差。