阅读整理自《MySQL 必知必会》- 朱晓峰,详细内容请登录 极客时间 官网购买专栏。


MySQL 中的索引,就相当于图书馆的检索目录,它是帮助 MySQL 系统快速检索数据的一种存储结构。

在索引中按照查询条件,检索索引字段的值,然后快速定位数据记录的位置,这样就不需要遍历整个数据表了。

数据表中的字段越多,表中数据记录越多,速度提升越是明显

我的测试数据准备:

create table demo.trans 
(
itemnumber int,
quantity text,
price text,
transdate datetime,
cashiernumber int,
branchnumber int
);

insert into demo.trans (itemnumber, quantity, price, transdate, cashiernumber, branchnumber) values (1, 1, 1, '2022-03-15', 1, 1);
-- ......
mysql> select * from demo.trans;
+------------+----------+-------+---------------------+---------------+--------------+
| itemnumber | quantity | price | transdate           | cashiernumber | branchnumber |
+------------+----------+-------+---------------------+---------------+--------------+
|          1 | 1        | 1     | 2022-03-15 00:00:00 |             1 |            1 |
|          2 | 2        | 2     | 2022-03-15 00:00:00 |             2 |            1 |
|          3 | 3        | 3     | 2022-03-15 00:00:00 |             3 |            1 |
|          1 | 1        | 1     | 2022-03-15 00:00:00 |             1 |            2 |
|          2 | 2        | 2     | 2022-03-15 00:00:00 |             2 |            2 |
|          3 | 3        | 3     | 2022-03-15 00:00:00 |             3 |            2 |
|          1 | 1        | 1     | 2022-03-16 00:00:00 |             1 |            1 |
|          2 | 2        | 2     | 2022-03-16 00:00:00 |             2 |            1 |
|          3 | 3        | 3     | 2022-03-16 00:00:00 |             3 |            1 |
|          1 | 1        | 1     | 2022-03-17 00:00:00 |             1 |            2 |
|          2 | 2        | 2     | 2022-03-17 00:00:00 |             2 |            2 |
|          3 | 3        | 3     | 2022-03-17 00:00:00 |             3 |            2 |
+------------+----------+-------+---------------------+---------------+--------------+
12 rows in set (0.00 sec)

查看一下商品编号是 1 的商品在 2022-03-17 这一天的销售情况:

mysql> select quantity, price, transdate from demo.trans where transdate = '2022-03-17' and itemnumber = 1;
+----------+-------+---------------------+
| quantity | price | transdate           |
+----------+-------+---------------------+
| 1        | 1     | 2022-03-17 00:00:00 |
+----------+-------+---------------------+
1 row in set (0.00 sec)  -- 我这边测试数据太少了,体现不出来查询时间快慢。。。

找到一个可以看毫秒级别的方法:

set profiling=1;     -- 置为1表示开启,置为0表示关闭
-- 执行其他 sql 命令
show profiles;  -- 查看上一条命令的耗时

配置好后,先执行查询,再创建索引,最后再查询一遍。


单字段索引

MySQL 支持单字段索引和组合索引,而单字段索引比较常用。

创建单字段索引

创建单字段索引,一般有 3 种方式:

  1. 通过 CREATE 语句直接给已经存在的表创建索引,这种方式比较简单;
  2. 在创建表的同时创建索引;
  3. 通过修改表来创建索引

直接给数据表创建索引

create index 索引名 on table 表名(字段);
-- create index index_cashiernumber on demo.trans (cashiernumber);

创建表的同时创建索引

create table 表名
(
字段 数据类型,
{ index | key} 索引名(字段)
)

修改表时创建索引

alter talbe 表名 add { index | key } 索引名(字段);
--  alter table demo.trans add index index_itemnumber (itemnumber);

另外,给表设定主键约束或者唯一性约束的时候,MySQL 会自动创建主键索引或唯一性索引。

举例:

给表 demo.trans 创建索引,前后各查询一次:

select quantity, price, transdate from demo.trans where transdate = '2022-03-17' and itemnumber = 3;  -- 没有索引

create index index_trans on demo.trans(transdate);  -- 加索引

select quantity, price, transdate from demo.trans where transdate = '2022-03-17' and itemnumber = 3;  -- 通过索引查询

通过耗时,可以看出,加了索引的查询更快:

mysql> show profiles;
+----------+------------+-------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                       |
+----------+------------+-------------------------------------------------------------------------------------------------------------+
|       10 | 0.01542675 | select quantity, price, transdate from demo.trans where transdate = '2022-03-17' and itemnumber = 3         |
|       11 | 0.96726050 | create index index_trans on demo.trans(transdate)                                                           |
|       12 | 0.00101600 | select quantity, price, transdate from demo.trans where transdate = '2022-03-17' and itemnumber = 3         |
+----------+------------+-------------------------------------------------------------------------------------------------------------+

加了索引之后,比没有索引的时候,快了 15 多倍。这么大的差距,说明索引对提高查询的速度确实很有帮助。


单字段索引的作用原理

借助 MySQL 中的 EXPLAIN 这个关键字,了解索引的作用。

explain 关键字能够查看 SQL 语句的执行细节,包括表的加载顺序,表是如何连接的,以及索引使用情况等。

mysql> explain select quantity, price, transdate from demo.trans where transdate = '2022-03-17' and itemnumber = 3;
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | trans | NULL       | ref  | index_trans   | index_trans | 6       | const |    3 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
  • type=ref:表示查找条件列使用了索引而且不为主键和unique
  • rows=3:表示需要读取的记录数
  • possible_keys=index_trans:表示可以选择的索引是 index_trans
  • key=index_trans:表示实际选择的索引是 index_trans
  • extra=Using where:通过 WHERE 条件进行了筛选;
    例如 extra=Using index condition;Using where;Using MRR:这里面的信息对 SQL 语句的执行细节做了进一步的解释,包含了 3 层含义:第一个是执行时使用了索引,第二个是执行时通过 WHERE 条件进行了筛选,第三个是使用了顺序磁盘读取的策略。
选择索引字段

使用其他字段来做索引:

create index index_trans_itemnumber on demo.trans(itemnumber);
mysql> explain select quantity, price, transdate from demo.trans where transdate = '2022-03-17' and itemnumber = 3;
+----+-------------+-------+------------+------+------------------------------------+-------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys                      | key         | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+------------------------------------+-------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | trans | NULL       | ref  | index_trans,index_trans_itemnumber | index_trans | 6       | const |    3 |    33.33 | Using where |
+----+-------------+-------+------------+------+------------------------------------+-------------+---------+-------+------+----------+-------------

“possible_keys= index_trans,index_trans_itemnumber ”,就是说 MySQL 认为可以选择的索引确实有 2 个,一个是用 transdate 字段创建的索引 index_trans,另一个是用 itemnumber 字段创建的索引 index_trans_itemnumber。key= index_trans,说明 MySQL 实际选择使用的索引还是 transdate 字段创建的索引 index_trans。

所以,在选择索引字段的时候,要选择那些经常被用做筛选条件的字段。这样才能发挥索引的作用,提升检索的效率。


组合索引

在实际工作中,有时会遇到比较复杂的数据表,这种表包括的字段比较多,经常需要通过不同的字段筛选数据,特别是数据表中包含多个层级信息。比如销售流水表就包含了门店信息、收款机信息和商品信息这 3 个层级信息。门店对应多个门店里的收款机,每个收款机对应多个从这台收款机销售出去的商品。经常要把这些层次信息作为筛选条件,来进行查询。这个时候单字段的索引往往不容易发挥出索引的最大功效,可以使用组合索引。

单索引效果:

mysql> select itemnumber from demo.trans where branchnumber=2 and cashiernumber=3 and itemnumber=3;
+------------+
| itemnumber |
+------------+
|          3 |
|          3 |
+------------+

mysql> explain select itemnumber from demo.trans where branchnumber=2 and cashiernumber=3 and itemnumber=3;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | trans | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   12 |     8.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

-- 原来所有索引被删除掉,重新创建如下3个单字段索引:
mysql> create index i_itemnumber on demo.trans (itemnumber);
mysql> create index i_cashiernumber on demo.trans (cashiernumber);
mysql> create index i_branch on demo.trans (branchnumber);

mysql> select itemnumber from demo.trans where branchnumber=2 and cashiernumber=3 and itemnumber=3;
+------------+
| itemnumber |
+------------+
|          3 |
|          3 |
+------------+

mysql> explain select itemnumber from demo.trans where branchnumber=2 and cashiernumber=3 and itemnumber=3;
+----+-------------+-------+------------+------+---------------------------------------+--------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys                         | key          | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------------------------------+--------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | trans | NULL       | ref  | i_itemnumber,i_cashiernumber,i_branch | i_itemnumber | 5       | const |    4 |    16.67 | Using where |
+----+-------------+-------+------------+------+---------------------------------------+--------------+---------+-------+------+----------+-------------+

mysql> show profiles;
+----------+------------+-----------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                                 |
+----------+------------+-----------------------------------------------------------------------------------------------------------------------+
|      117 | 0.00958000 | select itemnumber from demo.trans where branchnumber=2 and cashiernumber=3 and itemnumber=3                           |
|      118 | 0.00031150 | explain select itemnumber from demo.trans where branchnumber=2 and cashiernumber=3 and itemnumber=3                   |

|      122 | 0.00045350 | select itemnumber from demo.trans where branchnumber=2 and cashiernumber=3 and itemnumber=3                           |
|      123 | 0.00055550 | explain select itemnumber from demo.trans where branchnumber=2 and cashiernumber=3 and itemnumber=3                   |
+----------+------------+-----------------------------------------------------------------------------------------------------------------------+

MySQL 有 3 个索引可以用,分别是用 branchnumber 创建的 i_branchnumber、用 cashiernumber 创建的 i_cashiernumber 和用 itemnumber 创建的 i_itemnumber。MySQL 还是选择了 i_itemnumber,实际筛选的记录数是 5。

优化器现在有 3 种索引可以用,分别是商品编号索引、门店编号索引和收款机号索引。优化器发现,商品编号索引实际搜索的记录数最少,所以最后就选择了这种索引。

如果有多个索引,而这些索引的字段同时作为筛选字段出现在查询中的时候,MySQL 会选择使用最优的索引来执行查询操作。

能不能让这几个筛选字段同时发挥作用呢?这就用到组合索引了。组合索引,就是包含多个字段的索引。MySQL 最多支持由 16 个字段组成的组合索引。


创建组合索引

创建组合索引的语法结构与创建单字段索引相同,不同的是相比单字段索引,组合索引使用了多个字段。

直接给数据表创建索引

create index 索引名 on table 表名(字段1, 字段2, ...);

创建表的同时创建索引

create table 表名
(
字段 数据类型,.
{ index | key } 索引名(字段1,字段2...)
)

修改表时创建索引

alter table 表名 add { index | key } 索引名 (字段1, 字段2, ...);

举例:

针对刚刚的查询场景,可以通过创建组合索引,发挥多个字段的筛选作用。具体做法是,创建一个由 3 个字段 branchnumber、cashiernumber、itemnumber 组成的组合索引:

mysql> create index i_branch_cashier_item ON demo.trans (branchnumber,cashiernumber,itemnumber);

mysql> select itemnumber from demo.trans where branchnumber=2 and cashiernumber=3 and itemnumber=3;
+------------+
| itemnumber |
+------------+
|          3 |
|          3 |
+------------+

mysql> explain select itemnumber from demo.trans where branchnumber=2 and cashiernumber=3 and itemnumber=3;
+----+-------------+-------+------------+------+-------------------------------------------------------------+-----------------------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys                                               | key                   | key_len | ref               | rows | filtered | Extra       |
+----+-------------+-------+------------+------+-------------------------------------------------------------+-----------------------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | trans | NULL       | ref  | i_itemnumber,i_cashiernumber,i_branch,i_branch_cashier_item | i_branch_cashier_item | 15      | const,const,const |    2 |   100.00 | Using index |
+----+-------------+-------+------------+------+-------------------------------------------------------------+-----------------------+---------+-------------------+------+----------+-------------+
mysql> show profiles;
+----------+------------+-----------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                               |
+----------+------------+-----------------------------------------------------------------------------------------------------+                                  
|      122 | 0.00045350 | select itemnumber from demo.trans where branchnumber=2 and cashiernumber=3 and itemnumber=3         |
|      123 | 0.00055550 | explain select itemnumber from demo.trans where branchnumber=2 and cashiernumber=3 and itemnumber=3 |

|      124 | 0.48831800 | create index i_branch_cashier_item ON demo.trans (branchnumber,cashiernumber,itemnumber)            |

|      125 | 0.00829500 | select itemnumber from demo.trans where branchnumber=2 and cashiernumber=3 and itemnumber=3         |
|      126 | 0.00029475 | explain select itemnumber from demo.trans where branchnumber=2 and cashiernumber=3 and itemnumber=3 |
|      127 | 0.00042275 | select itemnumber from demo.trans where branchnumber=2 and cashiernumber=3 and itemnumber=3         |  -- 多执行了一次看看
|      128 | 0.00031475 | explain select itemnumber from demo.trans where branchnumber=2 and cashiernumber=3 and itemnumber=3 |
+----------+------------+-----------------------------------------------------------------------------------------------------+

这个查询,MySQL 可以用到的索引有 4 个:i_trans_itemnumber、i_trans_branchnumber、i_trans_cashiernumber、i_branch_cashier_item,rows 只有2。


组合索引的原理

组合索引的多个字段是有序的,遵循左对齐的原则。比如创建的组合索引,排序的方式是 branchnumber、cashiernumber 和 itemnumber。因此,筛选的条件也要遵循从左向右的原则,如果中断,那么,断点后面的条件就没有办法利用索引了。

  • 假如把条件换成“cashiernumber = 1 AND itemnumber = 1”,最左边的字段 branchnumber 没有包含到条件当中,中断了,所以这个条件完全不能使用组合索引

  • 假如筛选的是一个范围,如果没有办法无法精确定位,也相当于中断。比如“branchnumber>1 AND cashiernumber=3 AND itemnumber=3”这个条件,只能用到组合索引中 branchnumber>1 的部分,后面的索引就都用不上了,此时 MySQL 没有选择组合索引,而是选择了用 itemnumber 创建的普通索引 i_itemnumber。

    mysql> explain select itemnumber from demo.trans where branchnumber>1 and cashiernumber=3 and itemnumber=3;
    +----+-------------+-------+------------+------+-------------------------------------------------------------+--------------+---------+-------+------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys                                               | key          | key_len | ref   | rows | filtered | Extra       |
    +----+-------------+-------+------------+------+-------------------------------------------------------------+--------------+---------+-------+------+----------+-------------+
    |  1 | SIMPLE      | trans | NULL       | ref  | i_itemnumber,i_cashiernumber,i_branch,i_branch_cashier_item | i_itemnumber | 5       | const |    4 |    16.67 | Using where |
    

如果只用组合索引的一部分,效果没有单字段索引那么好。


小结

索引可以非常显著地提高数据查询的速度,数据表里包含的数据越多,效果越显著。

应该选择经常被用做筛选条件的字段来创建索引,这样才能通过索引缩小实际读取数据表中数据的范围,发挥出索引的优势。

如果有多个筛选的字段,而且经常一起出现,也可以用多个字段来创建组合索引。

删除索引:

drop index 索引名 on 表名;

有的索引不能用这种方法删除,比如主键索引,你就必须通过修改表来删除索引:

alter table 表名 drop primary key;

索引能够提升查询的效率,但是建索引也是有成本的,主要有 2 个方面,一个存储空间的开销,还有一个是数据操作上的开销:

  • 存储空间的开销,是指索引需要单独占用存储空间
  • 数据操作上的开销,是指一旦数据表有变动,无论是插入一条新数据,还是删除一条旧的数据,甚至是修改数据,如果涉及索引字段,都需要对索引本身进行修改,以确保索引能够指向正确的记录。

因此,索引也不是越多越好,创建索引有存储开销和操作开销,需要综合考虑。

本内容为合法授权发布,文章内容为作者独立观点,不代表开发云立场,未经允许不得转载。

CSDN开发云