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列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 |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set
添加一列后执行怎么就文件排序了,最左索引满足啊!不解!3,在一中将索引的类改下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;
也就是只索引total
然后按照total排序
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 |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
有使用了文件排序了。,我只按照total排序啊,为什么只在total列建索引就出现这样情况了!
希望高手能够回答下!谢谢了!!!
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列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 |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set
添加一列后执行怎么就文件排序了,最左索引满足啊!不解!3,在一中将索引的类改下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;
也就是只索引total
然后按照total排序
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 |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
有使用了文件排序了。,我只按照total排序啊,为什么只在total列建索引就出现这样情况了!
希望高手能够回答下!谢谢了!!!
mysql> show index from tag;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tag | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| tag | 1 | idx_sort | 1 | total | A | 0 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
添加了单列索引时
mysql> show index from tag;
+-------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tag | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| tag | 1 | idx_sort | 1 | total | A | 0 | NULL | NULL | YES | BTREE | | |
| tag | 1 | idx_sort | 2 | total_question | A | 0 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tag
type: index
possible_keys: NULL
key: idx_sort
key_len: 10
ref: NULL
rows: 1
Extra: Using index
1 row in set (0.00 sec)下面是我对你的2做的分析mysql> explain select * from tag order by total \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tag
type: index
possible_keys: NULL
key: idx_sort
key_len: 10
ref: NULL
rows: 1
Extra:
1 row in set (0.00 sec)在key 中还是选择了idx_sort
后面的表多了个name字段,用不到覆盖索引,所以会有filesort。
你把select * 换成select id,,total,total_question再看执行计划就会走索引了