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 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
…
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表示读下一行的请求数,值越大,则说明索引不正确或者写入的查询没有利用索引,需要建立索引改善。