Mysql索引创建删除及使用代价

一、mysql 中索引的创建删除

innodb 和 myisam 引擎会自动为主键或者带有 unique 属性的列建立索引。

如果要为其他列建立索引，就需要显示的指明。

1. 创建表的时候创建索引

create table 表名 (
    各个列的信息...,
    (key/index) 索引名 (需要被索引的单个列或多个列)
)

注意，key 和 index 任用其一即可，是同义词。

比如在创建 index_demo 表时为c2 和 c3 列添加一个联合索引：

create table index_demo(
    c1 int,
    c2 int,
    c3 char(1),
    primary key(c1),
    index idx_c2_c3 (c2, c3)
);

这里创建的索引的名称是idx_c2_c3，虽然索引名字可以任意起，但是建议以idx_为前缀，后面跟着要简历的索引的列名，多个列用下划线隔开。

2. 修改表结构时候添加索引

alter table 表名 add (key/index) 索引名 (需要被索引的单个列或多个列);

3. 修改表结构时候删除索引

alter table 表名 drop (key/index) 索引名;

现在删除掉最开始建的索引idx_c2_c3，可以这样写：

alter table index_demo drop index idx_c2_c3;

二、索引的代价

索引虽然是个好东西，但是不能肆意创建。

1. 空间上的代价

这个是很显而易见的。

• 每建立一个索引，就要为它建立一颗 b+ 树。
• 而每一颗 b+ 树的每一个节点都是一个数据页，默认会占用16kb的存储空间。
• 一颗很大的 b+ 树又是由许多数据页组成。

所以，这会占用很大一片存储空间。

2. 时间上的代价

维护顺序的耗时

每当对表中的数据进行增删改操作时，都需要修改各个 b+树 索引。

我们知道：

• b+ 树中的每层节点都是按照索引列的值从小到大的顺序组成了双向链表。
• 无论是叶子节点还是内节点，内部中的记录都是按照索引列的值的大小顺序，形成了一个单链表。

所以，当进行增删改操作时，可能会对节点和记录的排序造成破坏，那么存储引擎就需要额外的时间进行页面分裂、回收等操作，以维护节点和记录的排序。

如果建立了很多索引，可想而知每个索引对应的b+ 树都要进行相关的维护操作，这要带来多大的消耗。

执行计划分析的耗时

在执行查询语句之前，首先会生成一个执行计划。

一般来说，一条查询语句在执行的过程中最多使用一个二级索引，在生成执行计划时需要计算使用不同索引执行查询时所需的成本，最后选择最低的那个索引进行查询。

如果建立了太多的索引，可能导致在进行成本分析的时候就耗时太多，从而影响查询语句的执行性能。

小结

综上所述，在一个表中建立的索引越多，占用的存储空间就越多，在增删改记录或者生成执行计划时性能也就越差。

所以，该怎样建立又好又少的索引？

本文参考书籍： 小孩子4919 《mysql是怎样运行的》