首页 > 编程笔记 > MySQL笔记 阅读：75

MySQL索引查询详解（附带实例）

MySQL 中提高性能的一个有效方式就是对数据表设计合理的索引。索引提供了高效访问数据的方法，并且可加快查询的速度，因此索引对查询的速度有着至关重要的影响。

如果查询时没有使用索引，查询语句将扫描表中的所有记录。在数据量大的情况下，这样的查询速度会很慢。如果使用索引进行查询，查询语句可以根据索引快速定位到待查询记录，从而减少查询的记录数，达到提高查询速度的目的。

【实例 1】下面是查询语句中不使用索引和使用索引的对比。
1) 首先，分析未使用索引时的查询情况，EXPLAIN 语句执行如下： 
mysql> EXPLAIN SELECT * FROM fruits WHERE f_name='apple';
+----+-------------+---------+------+-----------------+--------+----------+-------+--------+---------------+
| id | select_type | table   | type | possible_keys   | key    | key_len | ref    | rows   | Extra         |
+----+-------------+---------+------+-----------------+--------+---------+--------+--------+---------------+
|  1 | SIMPLE      | fruits  | ALL  | NULL            | NULL   | NULL    | NULL   | 15     | Using where   |
+----+-------------+---------+------+-----------------+--------+---------+--------+--------+----------------+
1 row in set (0.00 sec)
可以看到，rows 列的值是 15，说明“SELECT * FROM fruits WHERE f_name='apple';”查询语句扫描了表中的 15 条记录。

然后，在表 fruits 的 f_name 字段上加上索引。执行添加索引的语句及结果如下： 
mysql> CREATE INDEX index_name ON fruits(f_name);
Query OK, 0 rows affected (0. 04 sec)
Records: 0  Duplicates: 0  Warnings: 0

现在，分析上面的查询语句，执行的 EXPLAIN 语句及结果如下： 
mysql> EXPLAIN SELECT * FROM fruits WHERE f_name='apple';
+----+-------------+---------+------+-----------------+---------------+----------+-------+------+---------------+
| id | select_type | table   | type | possible_keys   | key           | key_len  | ref   |rows  | Extra         |
+----+-------------+---------+------+-----------------+---------------+----------+-------+------+---------------+
|  1 | SIMPLE      | fruits  | ref  | index_name      | index_name    |255       | const |    1 |Using where    |
+----+-------------+---------+------+-----------------+---------------+----------+-------+------+---------------+
1 row in set (0.00 sec)
结果显示，rows 列的值为 1，表示这个查询语句只扫描了表中的一条记录，其查询速度自然比扫描 15 条记录的快；而且 possible_keys 和 key 的值都是 index_name，说明查询时使用了 index_name 索引。

MySQL使用索引查询

虽然索引可以提高查询的速度，但并不是所有索引都会起作用。

使用索引有几种特殊情况，在这些情况下使用带有索引的字段进行查询时索引可能并没有起作用。下面重点介绍这几种特殊情况。

1) 使用LIKE关键字的查询语句

在使用 LIKE 关键字进行查询的语句中，如果匹配字符串的第一个字符为“%”，则索引不会起作用，只有“%”不在第一个位置，索引才会起作用。

【实例 2】查询语句中使用 LIKE 关键字，并且匹配的字符串中含有“%”字符，EXPLAIN 语句执行如下： 
mysql> EXPLAIN SELECT * FROM fruits WHERE f_name like '%x';
+----+-------------+---------+-------+------------------+---------------+----------+--------+------+---------------+
| id | select_type | table   | type  | possible_keys    | key           | key_len  | ref    |rows  | Extra         |
+----+-------------+---------+-------+------------------+---------------+----------+--------+------+---------------+
|  1 | SIMPLE      | fruits  | ALL   | NULL             | NULL          | NULL     | NULL   | 16   | Usingwhere    |
+----+-------------+---------+-------+------------------+---------------+----------+--------+------+---------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM fruits WHERE f_name like 'x%';
+----+-------------+---------+-------+----------------+--------------+---------+--------+-------+------------------+
| id | select_type | table   | type  | possible_keys  | key          | key_len | ref    |rows   | Extra            |
+----+-------------+---------+-------+----------------+--------------+---------+--------+-------+------------------+
| 1  | SIMPLE      | fruits  | range | index_name     | index_name   | 150     | NULL   | 4     | Usingwhere       |
+----+-------------+---------+-------+----------------+--------------+---------+--------+-------+------------------+
1 row in set (0.00 sec)
已知 f_name 字段上有索引 index_name。第一条查询语句执行后，rows 列的值为 16，表示这次查询过程中扫描了表中所有的 16 条记录；第二条查询语句执行后，rows 列的值为 4，表示这次查询过程扫描了 4 条记录。第一条查询语句中的索引没有起作用，因为第一条查询语句中 LIKE 关键字后的字符串以“%”开头，而第二条查询语句使用了索引 index_name。

2) 使用多列索引的查询语句

MySQL 可以为多个字段创建索引。一个索引可以包括 16 个字段。对于多列索引，只有查询条件中使用了这些字段中的第一个字段时索引才会被使用。

【实例 2】在表 fruits 中的 f_id、f_price 字段上创建多列索引，验证多列索引的使用情况，SQL 语句如下： 
mysql> CREATE INDEX index_id_price ON fruits(f_id, f_price);
Query OK, 0 rows affected (0.39 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> EXPLAIN SELECT * FROM fruits WHERE f_id='l2';
+----+--------------+--------+-------+------------------------------+---------------+---------+-------+------+-------+
| id | select_type  | table  | type  | possible_keys                | key           | key_len |ref    | rows | Extra |
+----+--------------+--------+-------+------------------------------+---------------+---------+-------+------+-------+
|  1 | SIMPLE       | fruits | const | PRIMARY,index_id_price       | PRIMARY       | 20      | const |   1  |       |
+----+--------------+--------+-------+------------------------------+---------------+---------+-------+------+-------+
1 row in set (0.00 sec)
　　　　　　
mysql> EXPLAIN SELECT * FROM fruits WHERE f_price=5.2;
+----+-------------+--------+------+-----------------+--------+---------+-------+------+---------------+
| id | select_type | table  | type | possible_keys   | key    | key_len | ref   | rows | Extra         |
+----+-------------+--------+------+-----------------+--------+---------+-------+------+---------------+
|  1 | SIMPLE      | fruits | ALL  | NULL            | NULL   | NULL    | NULL  |  16  | Using where   |
+----+-------------+--------+------+-----------------+--------+---------+-------+------+---------------+
1 row in set (0.00 sec)
从第一条语句的查询结果中可以看出，“f_id= 'l2'”的记录有 1 条。第一条语句共扫描了 1 条记录，并且使用了索引 index_id_price。从第二条语句的查询结果中可以看出，rows 列的值是 16，说明查询语句共扫描了 16 条记录，并且 key 列值为 NULL，说明“SELECT * FROM fruits WHERE f_price=5.2;”语句并没有使用索引。因为 f_price 字段是多列索引的第二个字段，只有查询条件中使用了 f_id 字段才会使 index_id_price 索引起作用。

3) 使用OR关键字的查询语句

查询语句的查询条件中只有 OR 关键字，并且 OR 前后的两个条件中的列都有索引时，查询中才使用索引；否则，查询将不使用索引。

【实例 3】查询语句使用 OR 关键字的情况。 
mysql> EXPLAIN SELECT * FROM fruits WHERE f_name='apple' or s_id=101 \G
*** 1. row ***
           id: 1
  select_type: SIMPLE
        table: fruits
         type: ALL
possible_keys: index_name
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16
        Extra: Using where
1 row in set (0.00 sec)
　　　　　　
mysql> EXPLAIN SELECT * FROM fruits WHERE f_name='apple' or f_id='l2' \G
*** 1. row ***
           id: 1
  select_type: SIMPLE
       table: fruits
        type: index_merge
possible_keys: PRIMARY,index_name,index_id_price
         key: index_name,PRIMARY
     key_len: 510,20
         ref: NULL
        rows: 2
       Extra: Using union(index_name,PRIMARY); Using where
1 row in set (0.00 sec)
因为 s_id 字段上没有索引，所以第一条查询语句没有使用索引，总共查询了 16 条记录；因为 f_id 字段和 f_name 字段上都有索引，所以第二条查询语句使用了索引，查询的记录数为 2 条。

相关文章