本文介绍了MySQL中的各种索引类型，包括B-tree、HASH、R-tree和Full-text索引，并详细探讨了索引的使用场景及如何高效利用索引进行查询。同时，也列举了一些即使存在索引也无法使用的常见情况。
1、索引的存储分类
- B-tree索引：最常见的索引类型
- HASH索引：只有Memory引擎支持
- R-tree索引：空间索引MyISAM的一个特殊索引类型
- Full-text索引：全文索引也是MyISAM，InnoDB从mysql5.6版本提供全文检索的支持
B-tree索引和Hash索引比较：
Hash索引相对简单，只有Memory引擎支持Hash索引，Hash索引适用于Key-value查询，通过Hash索引要比通过B-tree索引查询更迅速；Hash索引不适合范围查询，例如<、>、<=..这类。
2、Mysql如何适用索引
Mysql中能够使用索引的典型场景
（1）匹配全值
对索引中具体的列指定具体的值：
继续使用sakila作为数据库，重命名租赁表rental上的索引rental_date为idx_rental_date;
可以看到优化器选择了复合索引idx_rental_date。
（2）匹配值的范围查询
（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 specifiedEXTRA值为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 specifiedusingindex表示查询使用了覆盖索引扫描
（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…
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。
（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)
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> update film_text set title = concat('s',title);
Query OK, 1000 rows affected (0.08 sec)
Rows matched: 1000 Changed: 1000 Warnings: 0
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（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访问，一次全表扫描就够了。
3、查看索引使用情况
如果索引正在工作，Headler_read_key的值很高，这个值代表了一个行被索引值读的次数，很低的值表明增加索引得到的性能改善不高，因为索引并不经常使用。
最后一行Handler_read_rnd_next表示读下一行的请求数，值越大，则说明索引不正确或者写入的查询没有利用索引，需要建立索引改善。
扫一扫
3057
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
