desc payment;
+--------------+----------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------------------+------+-----+-------------------+-----------------------------+
| payment_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| customer_id | smallint(5) unsigned | NO | MUL | NULL | |
| staff_id | tinyint(3) unsigned | NO | MUL | NULL | |
| rental_id | int(11) | YES | MUL | NULL | |
| amount | decimal(5,2) | NO | | NULL | |
| payment_date | datetime | NO | MUL | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------+----------------------+------+-----+-------------------+-----------------------------+
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 | 2 | NULL | NULL | | BTREE | | |
| payment | 1 | idx_fk_customer_id | 1 | customer_id | A | 599 | NULL | NULL | | BTREE | | |
| payment | 1 | fk_payment_rental | 1 | rental_id | A | 16045 | NULL | NULL | YES | BTREE | | |
| payment | 1 | idx_payment_data_amount_last_update | 1 | payment_date | A | 15819 | NULL | NULL | | BTREE | | |
| payment | 1 | idx_payment_data_amount_last_update | 2 | amount | A | 15869 | NULL | NULL | | BTREE | | |
| payment | 1 | idx_payment_data_amount_last_update | 3 | last_update | A | 16043 | NULL | NULL | | BTREE | | |
+---------+------------+-------------------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
select * FROM payment WHERE amount = 3.98 and last_update='2016-02-15 22:12:32';
explain select sql_no_cache * FROM payment WHERE amount = 3.98 and last_update='2016-02-15 22:12:32'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: payment
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 16086
filtered: 1.00
Extra: Using where
很明显这个时候,全部扫描了,并没有用到索引
amount
和last_update
amount
索引alter table payment add index
idx_amount
(amount
);
analyze table payment\G
mysql> explain select * FROM payment WHERE amount = 3.98 and last_update='2016-02-15 22:12:32'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: payment
partitions: NULL
type: ref
possible_keys: idx_amount
key: idx_amount
key_len: 3
ref: const
rows: 8
filtered: 10.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
last_update
索引alter table paymeng drop index idx_amount;
alter table payment add index `idx_last_update` (`last_update`);
mysql> explain select * FROM payment WHERE amount = 3.98 and last_update='2016-02-15 22:12:32'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: payment
partitions: NULL
type: ref
possible_keys: idx_last_update
key: idx_last_update
key_len: 4
ref: const
rows: 1
filtered: 10.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
amount
和last_update
字段索引alter table payment add index `idx_amount` (`amount`);
mysql> explain select * FROM payment WHERE amount = 3.98 and last_update='2016-02-15 22:12:32'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: payment
partitions: NULL
type: ref
possible_keys: idx_last_update,idx_amount
key: idx_last_update
key_len: 4
ref: const
rows: 1
filtered: 5.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
大家注意了,此时
possible_keys
字段是idx_last_update
和idx_amount
说明,这两个索引都会用到,但是用到的索引却是idx_last_update
,这是因为,通过idx_last_update
索引取到的row是1,通过idx_amount
取到的row是8,所以,mysql的优化器就自动选择最优索引idx_last_update
因为上面
idx_last_update
获取到的row=1,自然成为了最优的索引,那么如果两个索引的row都不为1,我们来看一下,执行计划
我们把
last_update
时间改成了2006-02-15 22:12:32
,用来保证row不为1
select * FROM payment WHERE amount = 3.98 and last_update='2006-02-15 22:12:32';
mysql> select count(*) FROM payment WHERE amount = 3.98;
+----------+
| count(*) |
+----------+
| 8 |
mysql> select count(*) FROM payment where last_update='2006-02-15 22:12:32';
+----------+
| count(*) |
+----------+
| 142 |
+----------+
1 row in set (0.00 sec)
mysql> explain select * FROM payment WHERE amount = 3.98 and last_update='2006-02-15 22:12:32'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: payment
partitions: NULL
type: index_merge
possible_keys: idx_last_update,idx_amount
key: idx_amount,idx_last_update
key_len: 3,4
ref: NULL
rows: 1
filtered: 100.00
Extra: Using intersect(idx_amount,idx_last_update); Using where
1 row in set, 1 warning (0.00 sec)
这里我们看到索引变成了
idx_amount
和idx_last_update