首先查看表的索引
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访问,一次全表扫描就够了