mysql使用索引优化排序!

Question

1. 首先，我要创建表标签:

   CREATE TABLE `tag` (
        `id` smallint(6) NOT NULL AUTO_INCREMENT,
        `total` int(11) DEFAULT NULL,
        `total_question` int(11) DEFAULT NULL,
        PRIMARY KEY (`id`),
        KEY `idx_sort` (`total`,`total_question`) USING BTREE
   ) ENGINE=InnoDB DEFAULT CHARSET=gb2312;
   

   mysql> explain select * from tag order by total;

   +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
   | id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
   +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
   | 1  | SIMPLE      | tag   | index | NULL          | idx_sort | 10      | NULL | 1    | Using index |
   +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
   

   使用索引排序，不使用文件排序。

2. 当我将 name 列添加到 tag 表时:

   CREATE TABLE `tag` (
     `id` smallint(6) NOT NULL AUTO_INCREMENT,
     `total` int(11) DEFAULT NULL,
     `total_question` int(11) DEFAULT NULL,
     `name` char(20) DEFAULT NULL,
     PRIMARY KEY (`id`),
     KEY `idx_sort` (`total`,`total_question`) USING BTREE
   ) ENGINE=InnoDB DEFAULT CHARSET=gb2312;
   

   mysql> explain select * from tag order by total;

   +----+-------------+-------+------+---------------+------+---------+------+------+----------------+
   | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
   +----+-------------+-------+------+---------------+------+---------+------+------+----------------+
   | 1  | SIMPLE      | tag   | ALL  | NULL          | NULL | NULL    | NULL | 1    | Using filesort |
   +----+-------------+-------+------+---------------+------+---------+------+------+----------------+
   

   使用文件排序，不使用索引。

3. 当我仅在 total 上创建索引时:

   CREATE TABLE `tag` (
     `id` smallint(6) NOT NULL AUTO_INCREMENT,
     `total` int(11) DEFAULT NULL,
     `total_question` int(11) DEFAULT NULL,
     `name` char(20) DEFAULT NULL,
     PRIMARY KEY (`id`),
     KEY `idx_sort` (`total`) USING BTREE
   ) ENGINE=InnoDB DEFAULT CHARSET=gb2312;
   

   mysql> explain select * from tag order by total;

   +----+-------------+-------+------+---------------+------+---------+------+------+----------------+
   | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
   +----+-------------+-------+------+---------------+------+---------+------+------+----------------+
   | 1  | SIMPLE      | tag   | ALL  | NULL          | NULL | NULL    | NULL | 1    | Using filesort |
   +----+-------------+-------+------+---------------+------+---------+------+------+----------------+
   

   使用 filesort 排序! 为什么？我只使用 total 列进行排序。

Answer 1

你可以运行 desc select * from tag force index (idx_sort) order by total ;你可以看到输出:

mysql> desc select * from tag force index (idx_sort) order by total ;
+----+-------------+-------+-------+---------------+----------+---------+------+------+---     ----+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows |    Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+------+---  ----+
|  1 | SIMPLE      | tag   | index | NULL          | idx_sort | 5       | NULL |    1 |           |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------+