mysql> show create table user\G;
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
`name` varchar(50) NOT NULL COMMENT '名字',
`age` tinyint unsigned NOT NULL COMMENT '年龄',
`sex` enum('M','W') NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> select * from user;
+----+----------+-----+-----+
| id | name | age | sex |
+----+----------+-----+-----+
| 1 | zhangsan | 16 | M |
| 2 | lisi | 13 | M |
| 3 | laoliu | 24 | W |
| 4 | jianzhe | 14 | W |
| 5 | wangwei | 21 | M |
+----+----------+-----+-----+
5 rows in set (0.03 sec)使用order by 排序
mysql> explain select * from user order by age;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.02 sec)
mysql> explain select * from user order by name;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)我们发现，这两个sql的排序方式都是Using filesort,这种排序方式呢，在数据量小时，MySQL可能会在内存中进行排序，但是，排序的数据量大时，这种排序可能会在磁盘上进行，但总的来说，这种排序方式速度并不理想，需要MySQL使用额外的方式进行排序。
mysql> explain select name from user order by name;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | index | NULL | name | 202 | NULL | 5 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
mysql> explain select name from user order by age;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.01 sec)
mysql> explain select age from user order by age;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.01 sec)从上面这两个我们可以看出，第一个查询name使用name排序，第二个同样是查询name,但他使用age排序，前者是直接使用了索引进行排序Using index，而后者却使用了Using filesort，我们可以判断，order by排序的性能不仅与待排序的字段有关，还跟要查询的字段有关。这涉及到了具体索引和非具体索引的搜索过程
