位置：首页>> 网络编程>> 数据库>> Mysql索引创建删除及使用代价

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

作者：把苹果咬哭的测试笔记　　发布时间：2024-01-16 15:32:35　

标签：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;

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

这个是很显而易见的。

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

维护顺序的耗时

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

我们知道：

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

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

执行计划分析的耗时

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

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

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

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

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

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

来源：https://blog.csdn.net/wessonlan/article/details/124812980

投稿