本文探讨了MySQL中能够有效利用索引的多种场景，包括全值匹配、范围查询、最左前缀原则等，并举例说明了如何进行索引优化。同时，也列举了即使存在索引但无法使用的常见情况，如%开头的LIKE查询、数据类型隐式转换等，旨在帮助读者理解索引使用的重要性与限制。
首先查看表的索引
mysql> show index from payment;
+---------+------------+--------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+--------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| payment | 0 | PRIMARY | 1 | payment_id | A | 16086 | NULL | NULL | | BTREE | | |
| payment | 1 | idx_fk_staff_id | 1 | staff_id | A | 4 | NULL | NULL | | BTREE | | |
| payment | 1 | idx_fk_customer_id | 1 | customer_id | A | 1237 | NULL | NULL | | BTREE | | |
| payment | 1 | fk_payment_rental | 1 | rental_id | A | 16086 | NULL | NULL | YES | BTREE | | |
| payment | 1 | idx_payment_date | 1 | payment_date | A | 16086 | NULL | NULL | | BTREE | | |
| payment | 1 | idx_payment_date | 2 | amount | A | 16086 | NULL | NULL | | BTREE | | |
| payment | 1 | idx_payment_date | 3 | last_update | A | 16086 | NULL | NULL | | BTREE | | |
+---------+------------+--------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.00 sec)
mysql> show index from actor
-> ;
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| actor | 0 | PRIMARY | 1 | actor_id | A | 200 | NULL | NULL | | BTREE | | |
| actor | 1 | idx_actor_last_name | 1 | last_name | A | 200 | NULL | NULL | | BTREE | | |
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
1：匹配全值
对索引中具体的列指定具体的值
mysql>explain select * from rental where rental_date='2005-05-25 17:22:10' andinventory_id=373 and customer_id=343\G;
*************************** 1. row***************************
id: 1
select_type: SIMPLE
table: rental
type: const
possible_keys:rental_date,idx_fk_inventory_id,idx_fk_customer_id
key: rental_date
key_len: 10
ref: const,const,const
rows: 1
Extra: NULL
1 row in set (0.00 sec)
ERROR:
No query specified
2：匹配值的范围查询
mysql>explain select * from rental where customer_id>=373 and customer_id<400\G;
*************************** 1. row***************************
id: 1
select_type: SIMPLE
table: rental
type: range
possible_keys: idx_fk_customer_id
key: idx_fk_customer_id
key_len: 2
ref: NULL
rows: 717
Extra: Using index condition
1 row in set (0.01 sec)
ERROR:
No query specified
3：匹配最左前缀（做左匹配原则可以算是MySQL中B-TREE索引使用的首要原则）
mysql>alter table payment add index idx_payment_date(payment_date,amount,last_update);
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
如果对第一个和第三个可以使用索引
mysql>explain select * from payment where payment_date='2006-02-14 15:16:03' andlast_update='2006-02-15 22:12:32'\G;
*************************** 1. row***************************
id: 1
select_type: SIMPLE
table: payment
type: ref
possible_keys: idx_payment_date
key: idx_payment_date
key_len: 5
ref: const
rows: 182
Extra: Using index condition
1 row in set (0.00 sec)
ERROR:
No query specified
但是对第二个和第三个就不可以使用索引
mysql>explain select * from payment where amount=3.98 and last_update='2006-02-1522:12:32'\G;
*************************** 1. row***************************
id: 1
select_type: SIMPLE
table: payment
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 16086
Extra: Using where
1 row in set (0.00 sec)
ERROR:
No query specified
4：仅仅对索引进行查询
当查询的列都在索引的字段中时，查询效率更高
mysql>explain select *from payment where payment_date='2006-02-14 15:16:03' andamount=3.98\G;
*************************** 1. row***************************
id: 1
select_type: SIMPLE
table: payment
type: ref
possible_keys: idx_payment_date
key: idx_payment_date
key_len: 8
ref: const,const
rows: 8
Extra: NULL
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>explain select last_update from payment where payment_date='2006-02-1415:16:03' and amount=3.98\G;
*************************** 1. row***************************
id: 1
select_type: SIMPLE
table: payment
type: ref
possible_keys: idx_payment_date
key: idx_payment_date
key_len: 8
ref: const,const
rows: 8
Extra: Using index
1 row in set (0.00 sec)
ERROR:
No query specified
可以发现第二次查询extra部分变成了Using index 也就意味着，现在直接访问索引足够获取到所需的数据，不需要通过索引回表，Using index也就是平常说的覆盖索引扫描。至访问必须访问的数据，减少不必要的数据访问能够提高效率。
5：匹配列前缀
mysql>explain select title from film_text where title like 'AFRICAN%'\G;
*************************** 1. row***************************
id: 1
select_type: SIMPLE
table: film_text
type: range
possible_keys:idx_title_desc_part,idx_title_description
key: idx_title_desc_part
key_len: 32
ref: NULL
rows: 1
Extra: Using where
1 row in set (0.00 sec)
ERROR:
No query specified
EXTRA值为Using where 表示优化器需要通过索引回表查询数据。
6：能够实现索引匹配部分精确而其他部分进行范围匹配
mysql> explain select inventory_id from rentalwhere rental_date='2006-02-14 15:16:03' and customer_id>=300 and customer_id<=400\G;
*************************** 1. row***************************
id: 1
select_type: SIMPLE
table: rental
type: ref
possible_keys:rental_date,idx_fk_customer_id
key: rental_date
key_len: 5
ref: const
rows: 181
Extra: Using where; Using index
1 row in set (0.00 sec)
ERROR:
No query specified
usingindex表示查询使用了覆盖索引扫描
7：如果列名是索引，那么使用column_name is null就会使用索引
mysql>explain select * from payment where rental_id is null \G;
*************************** 1. row***************************
id: 1
select_type: SIMPLE
table: payment
type: ref
possible_keys: fk_payment_rental
key: fk_payment_rental
key_len: 5
ref: const
rows: 5
Extra: Using index condition
1 row in set (0.00 sec)
ERROR:
No query specified
8：MySQL5.6引入Index ConditionPushdown（IPC）特性，进一步优化查询。
mysql> explain select * from rental whererental_date='2006-02-14 15:16:03' and customer_id>=300 andcustomer_id<=400\G;
*************************** 1. row***************************
id: 1
select_type: SIMPLE
table: rental
type: ref
possible_keys:rental_date,idx_fk_customer_id
key: rental_date
key_len: 5
ref: const
rows: 181
Extra: Using index condition
1 row in set (0.00 sec)
ERROR:
No query specified
Using index condition 就表示MySQL使用了ICP来进一步优化查询，在检索的时候，把条件customer_id的过滤操作下推到存储引擎层来完成，这样能够降低不必要的IO访问。
MySQL中存在索引但是不能使用索引的典型场景
1：以%开头的LIKE查询不能够利用B-Tree索引
mysql>explain select * from actor where last_name like '%NI%'\G;
*************************** 1. row***************************
id: 1
select_type: SIMPLE
table: actor
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 200
Extra: Using where
1 row in set (0.00 sec)
ERROR:
No query specified
因为是B-Tree索引的结构，所以以%开头查询自然没法利用索引，这种情况一般推荐使用全文索引来解决类似问题。或者考虑利用InnoDB的表都是聚簇表的特点，采用轻量级别的解决方式：一般情况下，索引都会比表小，扫描索引要比扫描表更快，而InnoDB表上二级索引idx_last_name实际上存储字段last_name还有主键actor_id，那么理想的访问方式是首先扫描二级索引idx_last_name获得满足条件last_name like %IN%的主键actor_id列表，之后根据主键去检索记录，避开全表扫描演员表actor产生的大量IO。
mysql>explain select * from (select actor_id from actor where last_name like '%NI%')a ,actor b where a.actor_id=b.actor_id\G;
*************************** 1. row***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 200
Extra: NULL
*************************** 2. row***************************
id: 1
select_type: PRIMARY
table: b
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: a.actor_id
rows: 1
Extra: NULL
*************************** 3. row***************************
id: 2
select_type: DERIVED
table: actor
type: index
possible_keys: NULL
key: idx_actor_last_name
key_len: 137
ref: NULL
rows: 200
Extra: Using where; Using index
3 rows in set (0.00 sec)
ERROR:
No query specified
通过执行计划可以看到，内层查询的Using index 表示索引覆盖扫描，之后的主键join操作演员表actor中最终查询结果，理论上是能够比全表扫描更快一些。
2：数据类型出现隐式转换的时候也不会使用索引
mysql>explain select * from actor where last_name=1\G;
*************************** 1. row***************************
id: 1
select_type: SIMPLE
table: actor
type: ALL
possible_keys: idx_actor_last_name
key: NULL
key_len: NULL
ref: NULL
rows: 200
Extra: Using where
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>explain select * from actor where last_name='1'\G;
*************************** 1. row***************************
id: 1
select_type: SIMPLE
table: actor
type: ref
possible_keys: idx_actor_last_name
key: idx_actor_last_name
key_len: 137
ref: const
rows: 1
Extra: Using index condition
1 row in set (0.00 sec)
ERROR:
No query specified
我们可以看到前者没有加引号，即便是此列有索引，使用错误的数据类型导致没有使用索引。
3：符合索引情况下，不满足最左原则不会使用符合索引
mysql>explain select * from payment where amount=3.89 and last_update='2006-02-1522:12:32'\G;
*************************** 1. row***************************
id: 1
select_type: SIMPLE
table: payment
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 16086
Extra: Using where
1 row in set (0.00 sec)
ERROR:
No query specified
4：如果MySQL估计使用索引比全表扫描更慢，则不使用索引。
mysql>explain select * from film_text where title like 'S%'\G;
*************************** 1. row***************************
id: 1
select_type: SIMPLE
table: film_text
type: ALL
possible_keys: idx_title_desc_part,idx_title_description
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
1 row in set (0.00 sec)
ERROR:
No query specified
查看执行计划
mysql>select * from information_schema.optimizer_trace\G;
*************************** 1. row***************************
....
"table_scan": {
"rows": 1000,
"cost": 234.42
} /* table_scan */,
]/* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr":false,
"index_only":false,
"rows": 999,
"cost":1199.8,
"chosen":false,
"cause":"cost"
}
] /*range_scan_alternatives */,
从上面2个红体字可以看成，全表扫描需要访问记录"rows": 1000,为1000条代价计算为"cost": 234.42
而使用索引则需要查询"rows": 999,条，代价为"cost":1199.8,
所以MySQL使用全表扫描来进行查询。
5：用or分割卡id条件，如果or钱的条件中的列有索引，而后面的列没有索引，那么就不会使用索引。
mysql>explain select * from payment where customer_id=203 or amount=3.96\G
*************************** 1. row***************************
id: 1
select_type: SIMPLE
table: payment
type: ALL
possible_keys: idx_fk_customer_id
key: NULL
key_len: NULL
ref: NULL
rows: 16086
Extra: Using where
1 row in set (0.00 sec)
因为or后面的条件没有索引，那么后面的查询肯定要走全表扫描，存在全表扫描的情况下，就没必要多一次搜音扫描而增加I/O访问，一次全表扫描就够了
2604
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
