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

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. 空间上的代价

这个是很显而易见的。

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

2. 时间上的代价

维护顺序的耗时

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

我们知道：

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

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

执行计划分析的耗时

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

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

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

小结

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

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

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

更多关于Mysql索引创建删除的资料请关注脚本之家其它相关文章！

欢迎分享，转载请注明来源：内存溢出

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