php - 如何在mysql order by 已经加了索引的时候 排序，避免全表扫描

Question

首先来一个数据表
CREATE TABLE `student` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `age` int(11) NOT NULL,
  `sex` enum(0,1) NOT NULL DEFAULT 0,
  `date` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`date`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
比如 我想要取得一个数据表的最后十条数据
select * from students order by date desc limit 10;

我在date上加了索引，但是explain 一下这个sql语句还是 type=ALL全表扫描，
现在的问题就是数据量太大大时候，全表扫描很慢，如何利用索引，加快查询速度？谢谢你们

Answer 1

我扫了一遍。应该是用到了索引了。你先用explain看一下输出结果。然后再来判断一下。

Answer 2

用到索引了的呀.

MariaDB [test]> show create table student1;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                       |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student1 | CREATE TABLE `student1` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `age` int(11) NOT NULL,
  `sex` enum('0','1') NOT NULL DEFAULT '0',
  `date` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> explain select * from student1 order by date desc limit 10;
+------+-------------+----------+-------+---------------+------------+---------+------+------+-------+
| id   | select_type | table    | type  | possible_keys | key        | key_len | ref  | rows | Extra |
+------+-------------+----------+-------+---------------+------------+---------+------+------+-------+
|    1 | SIMPLE      | student1 | index | NULL          | index_name | 4       | NULL |    1 |       |
+------+-------------+----------+-------+---------------+------------+---------+------+------+-------+
1 row in set (0.00 sec)

MariaDB [test]>