mysql> desc user;

+--------+-------------+------+-----+---------+----------------+ | Field  | Type        | Null | Key | Default | Extra          | +--------+-------------+------+-----+---------+----------------+ | id     | int(11)     | NO   | PRI | NULL    | auto_increment | | userid | varchar(30) | NO   | MUL | NULL    |                | | server | varchar(10) | NO   |     | NULL    |                | | date   | datetime    | NO   |     | NULL    |                | +--------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> show index from user; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | user  |          0 | PRIMARY  |            1 | id          | A         |           6 |     NULL | NULL   |      | BTREE      |         |               | | user  |          1 | userid   |            1 | userid      | A         |           6 |     NULL | NULL   |      | BTREE      |         |               | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec)  

mysql> explain select * from user order by userid;   

+----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ |  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using filesort | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ 1 row in set (0.00 sec)

没有用到索引，改之：

mysql> explain select id,userid from user order by userid;          

+----+-------------+-------+-------+---------------+--------+---------+------+------+-------------+ | id | select_type | table | type  | possible_keys | key    | key_len | ref  | rows | Extra       | +----+-------------+-------+-------+---------------+--------+---------+------+------+-------------+ |  1 | SIMPLE      | user  | index | NULL          | userid | 32      | NULL |    6 | Using index | +----+-------------+-------+-------+---------------+--------+---------+------+------+-------------+ 1 row in set (0.00 sec)

已用到索引。

 

本文转自 liang3391 51CTO博客，原文链接:http://blog.51cto.com/liang3391/827115