MYSQL优化—索引优化（高性能MySQL（第3版））

最新推荐文章于 2022-02-08 19:27:19 发布

luslin1711

最新推荐文章于 2022-02-08 19:27:19 发布

阅读量249

点赞数

分类专栏：后端 mysql 文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/luslin1711/article/details/106529954

版权

后端同时被 2 个专栏收录

28 篇文章

订阅专栏

mysql

9 篇文章

订阅专栏

本文深入探讨MySQL索引优化策略，包括索引优点、索引匹配类型、B-Tree索引限制、高性能索引构建、多列索引设计、索引顺序选择、覆盖索引应用、索引扫描排序、冗余索引识别及索引与锁的关系。通过实例解析，提供实用的索引设计指南。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

学习《高性能MySQL（第3版）》

索引的优点

大大减少了服务器需要扫描的数据量
帮助服务器避免排序和临时表
将随机I/O变为顺序I/O

评价一个索引是否适合某个查询的“三星系统”：

索引将相关的记录放到一起则获得一星
如果索引中的数据顺序和查找中的排序顺序一致则获得二星
如果索引中的列包含了查询中需要的全部列则获得三星

索引匹配

例如如下表

create table people (
	last_name varchar(50) not null,
	first_name varchar(50) not null,
	dob			  date 			 not null,
	gender		  enum('m' ,'f') not null,
	key(last_name, first_name, dob)
)

B-Tree 索引的查询类型对如下类型的查询类型有效：

全值匹配
和索引中的列全部匹配，查找姓名为Cuba Allen、出生于 1960-01-01 的人

mysql> explain SELECT * FROM tests.people where  last_name = "Allen" and first_name = "Cuba" and dob = "1960-01-01";
+----+-------------+--------+------------+------+------------------------------+------------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys                | key                          | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------------------+------------------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | people | NULL       | ref  | idx_last_name_first_name_dob | idx_last_name_first_name_dob | 277     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+------------------------------+------------------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

匹配最左前缀
查找所用姓为Allen 的人

mysql> explain SELECT * FROM tests.people where  last_name = "Allen";
+----+-------------+--------+------------+------+------------------------------+------------------------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys                | key                          | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------------------+------------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | people | NULL       | ref  | idx_last_name_first_name_dob | idx_last_name_first_name_dob | 137     | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+------------------------------+------------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

匹配列前缀
可以匹配某一列的值的开头部分，比如查找所有以A开头的姓的人

mysql> explain SELECT * FROM tests.people where  last_name like "A%";
+----+-------------+--------+------------+-------+------------------------------+------------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys                | key                          | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+------------------------------+------------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | people | NULL       | range | idx_last_name_first_name_dob | idx_last_name_first_name_dob | 137     | NULL |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+------------------------------+------------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

匹配范围值
查找姓在Allen 和Barrymore 之间的人

mysql> explain SELECT * FROM tests.people where  last_name > "Allen" and last_name < "Barrymore";
+----+-------------+--------+------------+-------+------------------------------+------------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys                | key                          | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+------------------------------+------------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | people | NULL       | range | idx_last_name_first_name_dob | idx_last_name_first_name_dob | 137     | NULL |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+------------------------------+------------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

精确匹配某一列并范围匹配另一列

mysql> explain SELECT * FROM tests.people where  last_name = "Allen" and first_name < "Ba" and first_name > "Aa";
+----+-------------+--------+------------+-------+------------------------------+------------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys                | key                          | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+------------------------------+------------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | people | NULL       | range | idx_last_name_first_name_dob | idx_last_name_first_name_dob | 274     | NULL |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+------------------------------+------------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

下面是一些关于B-Tree 索引的限制：

如果不是按照索引的最左列开始查找，则无法使用索引。

mysql> explain SELECT * FROM tests.people where first_name = "Barrymore" ;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | people | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

不能跳过索引中的列。也就是说，前面的索引无法用于查找姓为Smith并且在某个特定日期出生的人。如果不指定first_name, 则mysql 只能使用索引的第一列

mysql> explain SELECT * FROM tests.people where last_name= "Allen" and dob = "1970-01-01" ;
+----+-------------+--------+------------+------+------------------------------+------------------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys                | key                          | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+------------------------------+------------------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | people | NULL       | ref  | idx_last_name_first_name_dob | idx_last_name_first_name_dob | 137     | const |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+------+------------------------------+------------------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引优化查询

mysql> explain SELECT * FROM tests.people where last_name= "Allen" and first_name < "Cannel" and dob = "1970-01-01" ;
+----+-------------+--------+------------+-------+------------------------------+------------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys                | key                          | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+------------------------------+------------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | people | NULL       | range | idx_last_name_first_name_dob | idx_last_name_first_name_dob | 274     | NULL |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+------------------------------+------------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

高性能索引

1 独立的列

如果查询中的列不是独立的，则mysql 就不会使用索引。 “独立的列”指索引列不能是表达式的一部分，也不能是函数的参数：

作为函数参数

mysql>  explain SELECT * FROM tests.actor where to_days(date)   > to_days(current_date);
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | actor | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 899708 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.17 sec)

作为表达式一部分

mysql> explain select * from tests.actor where id +1 < 9000000;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | actor | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 899708 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec

2 前缀索引和索引选择性

有时候需要索引很长的字符列，这会让索引变得大且慢。一个策略是模拟哈希索引。但有时候这样做还不够，还可以做些什么？
通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这也会降低索引的选择性。索引的选择性是指不重复的索引值和数据表记录的总数的比值。索引的选择性越高则查询效率越高，因为选择性高的索引可以让mysql在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好。

一般情况下某个列的前缀的选择性也是足够高的，足以满足查询性能。对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为mysql不允许索引这些列的完整长度。

诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长。前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。

为了决定前缀的合适长度，创建一张表进行测试：

CREATE TABLE `city_demo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `city` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=900001 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

首先，查看数据分布：

mysql> select count(*) as cnt, city from city_demo group by city order by cnt desc limit 10;
+-----+------------+
| cnt | city       |
+-----+------------+
|   2 | ujxytdhtgm |
|   2 | qinmgagsct |
|   2 | nefuxymqsd |
|   2 | ovuqrtfjtk |
|   2 | nmnidwoyfd |
|   2 | pfkdewlcpx |
|   2 | ifryoaecwk |
|   2 | uhppxxbmxc |
|   2 | bwhdmkivmj |
|   2 | ocivyybmff |
+-----+------------+
10 rows in set (1.34 sec)

然后，查找出现最频繁的城市，从3个字母开始：

mysql> select count(*) as cnt, left(city, 3) as pre_city  from city_demo group by city order by cnt desc limit 10;
+-----+----------+
| cnt | pre_city |
+-----+----------+
|   2 | ujx      |
|   2 | qin      |
|   2 | nef      |
|   2 | ovu      |
|   2 | nmn      |
|   2 | pfk      |
|   2 | ifr      |
|   2 | uhp      |
|   2 | bwh      |
|   2 | oci      |
+-----+----------+
10 rows in set (1.58 sec)

mysql> select count(*) as cnt, left(city, 3) as pre_city  from city_demo group by pre_city order by cnt desc limit 10;
+-----+----------+
| cnt | pre_city |
+-----+----------+
|  88 | efm      |
|  87 | wul      |
|  86 | jsv      |
|  86 | lpp      |
|  85 | ltd      |
|  85 | yme      |
|  85 | yxj      |
|  85 | iph      |
|  85 | dwq      |
|  84 | mtt      |
+-----+----------+
10 rows in set (0.90 sec)

...
...

mysql> select count(*) as cnt, left(city, 6) as pre_city  from city_demo group by pre_city order by cnt desc limit 10;
+-----+----------+
| cnt | pre_city |
+-----+----------+
|   3 | iytqfu   |
|   2 | ewcqep   |
|   2 | vswxtw   |
|   2 | ksswok   |
|   2 | lplema   |
|   2 | abcpql   |
|   2 | qvunqf   |
|   2 | dmuqru   |
|   2 | lhdmkc   |
|   2 | ocivyy   |
+-----+----------+
10 rows in set (1.45 sec)

mysql> select count(*) as cnt, left(city, 7) as pre_city  from city_demo group by pre_city order by cnt desc limit 10;
+-----+----------+
| cnt | pre_city |
+-----+----------+
|   2 | gdjhbcv  |
|   2 | uhppxxb  |
|   2 | hnwhsrc  |
|   2 | pfkdewl  |
|   2 | xjajjhb  |
|   2 | gmrepiv  |
|   2 | ovuqrtf  |
|   2 | ujxytdh  |
|   2 | bwhdmki  |
|   2 | ocivyyb  |
+-----+----------+
10 rows in set (1.38 sec)

这时看到，前缀长度接近6,7时，可能比较合适。

计算合适的前缀长度的另一个办法就是计算完整列的选择性，并使前缀的选择性接近与完整列的选择性：

mysql> select count(distinct city)/count(*) from city_demo;
+-------------------------------+
| count(distinct city)/count(*) |
+-------------------------------+
|                        0.9998 |
+-------------------------------+
1 row in set (1.05 sec)

通常来说，例子中如果前缀的选择性能够接近0.9998，基本可以使用了。下面查询不同前缀长度的选择性：

mysql> select count(distinct left(city,3))/count(*) as sel3, count(distinct left(city,4))/count(*) as sel4, count(distinct left(city,5))/count(*) as sel5, count(distinct left(city,6))/count(*) as sel6, count(distinct left(city,7))/count(*) as sel7, count(distinct left(city,8))/count(*) as sel8 from city_demo;
+--------+--------+--------+--------+--------+--------+
| sel3   | sel4   | sel5   | sel6   | sel7   | sel8   |
+--------+--------+--------+--------+--------+--------+
| 0.0174 | 0.3911 | 0.9550 | 0.9979 | 0.9997 | 0.9998 |
+--------+--------+--------+--------+--------+--------+
1 row in set (6.40 sec)

当长度大于7的时候，再增加前缀长度，选择性提升已经非常小了。

只看平均选择性是不够的，也有例外的情况，需要考虑最坏情况下的选择性。如果数据分布不均匀，很肯能会有陷阱。

创建前缀索引：

mysql> alter table city_demo add key (city(6));
Query OK, 0 rows affected (8.69 sec)
Records: 0  Duplicates: 0  Warnings: 0

前缀索引是一种能使索引更小、更快的有效办法，但另一方面也有缺点：mysql无法使用前缀索引做order by 和 group by，也无法使用前缀索引做覆盖扫描

mysql> explain SELECT * FROM tests.city_demo where city = "ubymdailsi";
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | city_demo | NULL       | ref  | city          | city | 21      | const |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain SELECT * FROM tests.city_demo where city > "ubymdailsi" ;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | city_demo | NULL       | ALL  | city          | NULL | NULL    | NULL | 898424 |    39.19 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

3 多列索引

常见的错误是为每个列创建独立的索引，或者按照错误的顺序创建多列索引。
例如

CREATE TABLE `actor` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` datetime NOT NULL,
  `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_date` (`date` DESC),
  KEY `idx_name` (`name`),
  KEY `idx_date_name` (`date` DESC,`name`)
) ENGINE=InnoDB AUTO_INCREMENT=2313085 DEFAULT CHARSET=utf8 COLLATE=utf8_bin |

这种索引策略，一般是由于人们听到一些专家诸如“把where条件里面的列都建上索引”这样模糊的建议导致的。实际上这个建议是非常错误的。这样一来，最好的情况下也只是“一星”索引，其性能比起真正最优的索引可能差几个数量级。有时无法设计一个“三星”索引，那不如忽略掉where子句，集中精力优化索引列的顺序，或者创建一个全覆盖索引。
在mysql5.0以后，查询可以使用多个单列的索引进行扫描，例如：

mysql> explain select * from actor where id = 2304983 or name = "xviyjivgenat";
+----+-------------+-------+------------+-------------+--------------------------------+------------------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys                  | key              | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+-------+------------+-------------+--------------------------------+------------------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | actor | NULL       | index_merge | PRIMARY,idx_name,idx_name_date | PRIMARY,idx_name | 4,303   | NULL |    2 |   100.00 | Using union(PRIMARY,idx_name); Using where |
+----+-------------+-------+------------+-------------+--------------------------------+------------------+---------+------+------+----------+--------------------------------------------+
1 row in set, 1 warning (0.01 sec)

可以看到mysql使用了两个索引并使用union操作。
索引合并策略有时候是一种优化结果，但实际上更多时候说明了表上的索引建的很糟糕：

当出现服务器对多个索引做相交操作时（通常有多个and条件），通常意味着需要一个包含所有相关列的多个索引，而不是多个独立的单个索引。
当服务器需要对多个索引做联合操作时（通常多个or条件），通常需要耗费大量cpu和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回大量数据时。
更重要的是，优化器不会把这些计算到“查询成本”中，优化器只关心随机页面读取。这会导致查询的成本被低估，导致该执行计划还不如直接全表扫描、这样做不但会消耗更多的cpu和内存资源，还可能会影响查询的并发性，但如果是单独运行这样的查询则往往会忽略对并发性的影响。通常还不如像在mysql4.1或更早的时代一样，将查询改为union方式更好。

如果在explain中看到有索引合并，应好好检查一下查询和表的结构，看是不是已经是最优化。也可以通过参数optimizer_waitch来关闭索引合并功能。也可以使用ignore_index提示优化器忽略掉某些索引。

4 选择合适的索引顺序

正确的索引顺序依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要.

在一个多列B-Tree 索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次第二列。所以索引可以按照升序或降序进行扫描，以满足精确符合列顺序的ORDER BY、GROUP BY 和 DISTINCT等子句的查询需求

所以多列索引的顺序至关重要。它决定了一个索引是否能成为一个真正的“三星索引”。

当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。这时候索引作用只是用于优化where条件的查找。在这种情况下，这样设计的索引确实能够最快的过滤出所需要的行，对于where子句中只使用了索引部分前缀的查询来说选择性也更高。

然而性能不只依赖于所有索引列的选择性，也和查询条件的具体值有关，也就是和值的分布有关。可能需要根据那些运行频率最高的查询来调整索引列顺序，让这种情况下索引的选择性最高
例子：

mysql> select * from payment where staff_id = 20 and customer_id = 60;

是应该建立索引（staff_id, customer_id）还是应该颠倒一下顺序？可以跑一些查询条件来确定在这个表中值的分布，并确定哪个列的选择性更高。

mysql> select sum(staff_id=20), sum(customer_id=60) from payment;
+------------------+---------------------+
| sum(staff_id=20) | sum(customer_id=60) |
+------------------+---------------------+
|            10016 |                1100 |
+------------------+---------------------+
1 row in set (0.02 sec)

根据经验，应该将customer_id放到前面，因为对应条件的customer_id数量更小。

这里要注意。查询的结果非常依赖选定的具体值，这样可能对其他一些条件的查询不公平，服务器的整体性能可能变的更糟糕，或者其他某些查询的运行不如预期。

mysql> select count(distinct staff_id)/ count(*) as staff_id_selectivity, count(distinct customer_id)/ count(*) as customer_id_selectivity from payment;
+----------------------+-------------------------+
| staff_id_selectivity | customer_id_selectivity |
+----------------------+-------------------------+
|               0.0557 |                  0.0083 |
+----------------------+-------------------------+
1 row in set (0.01 sec)

customer_id 的选择性更高，所以将其作为索引的第一列

mysql> alter table payment add key(customer_id, staff_id);
Query OK, 0 rows affected (2.38 sec)
Records: 0  Duplicates: 0  Warnings: 0

5 覆盖索引

通常大家都会根据查询的where条件来创建合适的索引，不过，这只是索引优化的一个方面。设计优秀的索引应该考虑到整个查询，而不单单是where条件部分。索引确实是一种查询数据的高效方式，但mysql 也可以直接使用索引来获取列数据。这样就不必在去读取数据行。

如果一个索引包含（或者说覆盖）所需要查询的字段的值，我们就称为覆盖索引。

覆盖索引是非常有用的工具，能极大地提升性能。

索引条目通常远小于数据行大小，所以如果只需要读取索引，那么mysql就会极大地减少数据访问量。这对缓存的负载非常重要，因为这种情况下响应时间大部分花费在数据拷贝上。覆盖索引对I/O密集型的应用也有帮助，因为索引比数据更小，更容易全部放入内存中
因为索引是按照列值顺序存储的（至少在每个单页上如此），所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少的多
一些存储引擎如MyISAM在内存中只缓存索引，数据访问则严重依赖于操作系统来缓存，因此要访问数据需要一次系统调用。这可能会导致严重的性能问题，尤其是那些系统调用占了数据访问中最大开销的场景
由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值。所以如果二级主键能够覆盖查询。则可以避免对主键索引的二次查询。

当发起一个被索引覆盖的查询（也叫作索引覆盖查询）时，在explain的extra列可以看到“using index”的信息。例如

mysql> explain select customer_id, staff_id from payment;
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key         | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | payment | NULL       | index | NULL          | customer_id | 8       | NULL | 12027 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

索引覆盖查询还有很多陷阱可能会导致无法实现优化。mysql查询优化器会在执行查询前判断是否有一个索引能进行覆盖。如果索引没有覆盖了where条件中的字段，或者不是整个查询涉及的字段。mysql 5.5 和更早的版本也总是会回表获取数据行，尽管并不需要这一行且最终会被过滤掉。

6 使用索引扫描来做排序

MySQL有两种方式可以生成有序结果：通过排序操作，或者按索引顺序扫描；如果explain出来的type列的值为“index”，则说明mysql 使用了索引扫描来做排序。
扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需要的全部列，那就不得不每扫描一条记录就回表查询一次。这基本上都是随机I/O，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢，尤其是I/O密集型的工作负载时。

mysql可以使用同一索引既满足排序，又用于查找行。因此，如果可能，设计索引时应该尽可能地同时满足这两种任务。

只有当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，mysql才能使用索引来对结果排序。如果查询需要关联多张表，则只有当order by 子句引用的字段全部为第一个表时，才能使用索引排序。order by 子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求，否则，mysql都需要执行排序操作，而无法利用索引排序

7 冗余和重复索引

mysql允许在相同列上创建多个索引，无论是有意的还是无意的。mysql需要单独维护重复的索引，并且优化器在优化查询的时候也需要逐个地进行考虑，这会影响性能。

重复索引是指在相同的列上按照相同的顺序创建相同类型的索引。应该避免这样创建重复索引，发现后也应立即删除。

有时会在不经意键创建重复索引：

create table test (
	id int not null primary key,
	a  int not null,
	b  int not null,
	unique(id),
	index(id)
) engine = innodb;

一个经验不足的用户可能是想创建一个主键，先加上唯一限制，然后再加上索引以供查询调用。事实上，mysql的唯一限制和主键都是通过索引实现的，因此，上面的做法实际上在相同的列上创建了三个重复的索引。通常没有理由这样做，除非是在同一列上创建不同类型的索引来满足不同的查询需求（比如key(col)和fulltext key(col)）。

冗余索引和重复索引有一些不同。如果创建了索引（a,b），再创建索引(a)就是冗余索引，因为这只是前一个索引的前缀索引。但如果再创建索引（b，a）则不是冗余索引。

8 索引和锁

索引可以让查询锁定更少的行。从两方面讲这对性能有好处：

虽然innodb的行锁效率很高，内存使用也很少，但是锁定行的时候仍会带来额外开销；
锁定超过需要的行会增加锁争用并减少并发性；

innodb只有在访问行的时候才会对其加锁，而索引能够减少innodb访问的行数，从而减少锁的数量。但这只有当innodb在存储引擎层能够过滤掉所有不需要的行时才有效。如果无法过滤掉无效的行，那么在innodb检索到数据并返回给服务器层以后，mysql服务器才能使用where子句。这时已经无法避免锁定行了。
例如：

mysql> set autocommit=0;
mysql> begin;
mysql> select customer_id from payment where customer_id < 5 and customer_id <> 1 for update;
+-------------+
| customer_id |
+-------------+
|           2 |
|           3 |
|           4 |
+-------------+

这条查询会返回2~4之间的行，但实际上获取了1-4之间的排它锁。innodb会锁住第一行，因为mysql为该查询选择的执行计划是索引范围扫描

mysql> explain select customer_id from payment where customer_id < 5 and customer_id <> 1 for update;
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | payment | NULL       | range | customer_id   | customer_id | 4       | NULL |  288 |   100.00 | Using where; Using index |
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

换句话说，底层存储引擎的操作是“从索引的开头开始获取满足条件customer_id < 5的记录”，服务器并没有告诉innodb可以过滤掉第一行的where条件。注意到Extra中的“Using where”。这表明mysql服务器将存储引擎返回行以后再应用where条件过滤。

下面的第二个查询就能证明第一行确实已经被锁定，尽管第一个查询结果并没有第一行。保持第一个链接打开，然后开启第二个链接

mysql> set autocommit=0;
mysql> begin;
mysql>  select customer_id from payment where customer_id =1  for update;

这个查询将会挂起，直到第一个事务释放第一行的锁