MySQL优化查询（二） 索引对查询速度的影响
MySQL中提高性能的一个有效方式就是对数据表设计合理的索引。索引提供了高效访问数据的方法，并且可加快查询的速度，因此，索引对查询的速度有着至关重要的影响。使用索引可以快速地定位表中的某条记录，从而提高数据库查询的速度、提高数据库的性能。
如果查询时没有使用索引，查询语句将扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢。如果使用索引进行查询，查询语句可以根据索引快速定位到待查询记录，从而减少查询的记录数，达到提高查询速度的目的。
下面是查询语句中不使用索引和使用索引的对比。首先，分析未使用索引时的查询情况，EXPLAIN语句执行如下：
mysql> explain select * from article where title = 'yellow';+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+| 1 | SIMPLE | article | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | Using where |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)
可以看到，rows列的值是10，说明“explain select * from article where title = 'yellow'”这个查询语句扫描了表中的10条记录。
然后，在article表的title字段上加上索引，再分析上面的查询语句。执行的EXPLAIN语句及结果如下：
mysql> alter table article add index index_title(`title`);Query OK, 0 rows affected (0.05 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> explain select * from article where title = 'yellow';+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+| 1 | SIMPLE | article | NULL | ref | index_title | index_title | 768 | const | 1 | 100.00 | Using index |+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)
结果显示，rows列的值为1，表示这个查询语句只扫描了表中的一条记录，其查询速度自然比扫描10条记录快；而且possible_keys和key的值都是index_title，说明查询时使用了index_title索引。
使用索引查询
索引可以提高查询的速度，但并不是使用带有索引的字段查询时索引都会起作用。本小节将向读者介绍索引的使用。
使用索引有几种特殊情况，在这些情况下有可能使用带有索引的字段查询时索引并没有起作用。下面重点介绍这几种特殊情况。
1．使用LIKE关键字的查询语句
在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不会起作用。只有“%”不在第一个位置，索引才会起作用。下面将举例说明。
查询语句中使用LIKE关键字，并且匹配的字符串中含有“%”字符，EXPLAIN语句执行如下：
mysql> explain select * from article where title like '%y';+----+-------------+---------+------------+-------+---------------+----------------+---------+------+------+----------+--------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+-------+---------------+----------------+---------+------+------+----------+--------------------------+| 1 | SIMPLE | article | NULL | index | NULL | index_title | 768 | NULL | 10 | 11.11 | Using where; Using index |+----+-------------+---------+------------+-------+---------------+----------------+---------+------+------+----------+--------------------------+1 row in set, 1 warning (0.00 sec)mysql> explain select * from article where title like 'y%';+----+-------------+---------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+| 1 | SIMPLE | article | NULL | range | index_title | index_title | 768 | NULL | 1 | 100.00 | Using where; Using index |+----+-------------+---------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+1 row in set, 1 warning (0.00 sec)
已知title字段上有索引index_title。第1个查询语句执行后，rows列的值为10，表示这次查询过程中扫描了表中所有的10条记录；第2个查询语句执行后，rows列的值为1，表示这次查询过程扫描了1条记录。第1个查询语句中的索引没有起作用，因为第1个查询语句中LIKE关键字后的字符串以“%”开头，而第2个查询语句使用了索引index_title。
2．使用多列索引的查询语句
MySQL可以为多个字段创建索引。一个索引可以包括16个字段。对于多列索引，只有查询条件中使用了这些字段中的第1个字段时索引才会被使用。
【例16.4】在表article表中id和count字段上创建多列索引，验证多列索引的使用情况。
mysql> create index index_id_count on article(`id`, `count`);Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> explain select * from article where id = 10;+----+-------------+---------+------------+-------+------------------------+---------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+-------+------------------------+---------+---------+-------+------+----------+-------+| 1 | SIMPLE | article | NULL | const | PRIMARY,index_id_count | PRIMARY | 4 | const | 1 | 100.00 | NULL |+----+-------------+---------+------------+-------+------------------------+---------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec)mysql> explain select * from article where count = 10;+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+| 1 | SIMPLE | article | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | Using where |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)
explain select * from article where title = 'yellow' or cid = 10\G1. row ***************************id: 1select_type: SIMPLEtable: articlepartitions: NULLtype: ALLpossible_keys: index_titlekey: NULLkey_len: NULLref: NULLrows: 10filtered: 19.00Extra: Using where1 row in set, 1 warning (0.00 sec)explain select * from article where title = 'yellow' or id = 10\G1. row ***************************id: 1select_type: SIMPLEtable: articlepartitions: NULLtype: index_mergepossible_keys: PRIMARY,index_id_count,index_titlekey: index_title,PRIMARYkey_len: 768,4ref: NULLrows: 2filtered: 100.00Extra: Using union(index_title,PRIMARY); Using where1 row in set, 1 warning (0.00 sec)