前缀索引&虚拟列索引

前缀索引&虚拟列索引

一、前缀索引

#创建前缀索引,注意：前缀索引，在排序 Order By 和 分组 Group By 操作是无法使用。
create index idx_title on film(title(10));

二、虚拟列索引

#1）创建一个虚拟列 amount_by_lk
alter table payment add column amount_by_lk int generated always as (round(amount*10));

#2）在虚拟列上创建索引
alter table payment add key idx_amount_by_lk(amount_by_lk);

#1）查看表执行计划 
mysql> explain select * from payment where round(amount*10)>100 \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: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

#2）查看表定义如下
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   |     | NULL              |                             |
| last_update  | timestamp            | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------+----------------------+------+-----+-------------------+-----------------------------+
7 rows in set (0.01 sec)

#3）创建一个虚拟列 amount_by_lk
mysql> alter table payment add column amount_by_lk int generated always as (round(amount*10));
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

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   |     | NULL              |                             |
| last_update  | timestamp            | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| amount_by_lk | int(11)              | YES  |     | NULL              | VIRTUAL GENERATED           |
+--------------+----------------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)

#4）在虚拟列上创建索引
mysql> alter table payment add key idx_amount_by_lk(amount_by_lk);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

#5）再次观察执行计划, Extra 为 Using where,表示优化器除了利用索引来加快访问之外，还需要根据索引回表查询数据
mysql> explain select * from payment where round(amount*10)>100 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: range
possible_keys: idx_amount_by_lk
          key: idx_amount_by_lk
      key_len: 5
          ref: NULL
         rows: 114
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.01 sec)