mysql的一个奇怪问题

Question

引擎是MyISAM,当需要扫描的行数大于等于15的时候,它就不使用索引而是全表扫描

mysql> desc customer;
+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| id         | bigint(20)    | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255)  | NO   | UNI | NULL    |                |
| address    | varchar(255)  | YES  |     | NULL    |                |
| linkman    | varchar(255)  | YES  |     | NULL    |                |
| mobile     | varchar(255)  | YES  |     | NULL    |                |
| phone      | varchar(255)  | YES  |     | NULL    |                |
| fax        | varchar(255)  | YES  |     | NULL    |                |
| memo       | varchar(2500) | YES  |     | NULL    |                |
| createDate | datetime      | YES  |     | NULL    |                |
+------------+---------------+------+-----+---------+----------------+

mysql> show index from customer \G
*************************** 1. row ***************************
       Table: customer
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
   Collation: A
Cardinality: 841
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 2. row ***************************
       Table: customer
  Non_unique: 0
    Key_name: name
Seq_in_index: 1
Column_name: name
   Collation: A
Cardinality: 841
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:

mysql> explain select * from customer order by id limit 15;
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | customer | ALL  | NULL          | NULL | NULL    | NULL |  841 | Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+

mysql> explain select * from customer order by id limit 14;
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | customer | index | NULL          | PRIMARY | 8       | NULL |   14 |       |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------+

mysql> explain select * from customer order by id limit 7,8;
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | customer | ALL  | NULL          | NULL | NULL    | NULL |  841 | Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+

mysql> explain select * from customer order by id limit 7,7;
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | customer | index | NULL          | PRIMARY | 8       | NULL |   14 |       |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------+

Answer 1

MyISAM每个表都有一个索引文件和数据文件，如果是通过索引来查找，会首先将索引信息载入内存然后找到对应的数据地址然后从数据文件中读取数据。如果MySQL觉得数据很少，就会直接将所有数据载入内存来操作，避免先去读取索引文件。

如果你的表中有很多数据，你会发现MySQL会尽可能使用索引。

另外如果你只查询索引信息，例如：

explain select id from customer order by id limit 15;

你会发现它会一直用索引，因为那样最快。