mysql索引专栏合集

1年前

多个索引mysql优化器的索引选择

表结构

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      |         |               |
+---------+------------+-------------------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

实验Sql

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,我们来看一下,执行计划

Sql

我们把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