索引+删除成本+mysql,MySQL常用的 5种索引的创建删除以及区别

MySQL常用的 5种索引的创建删除以及区别

MySQL 索引

索引类似于图书的目录索引，可以提高数据检索的效率，降低数据库的IO成

MySQL官方的解释：

索引(Index)是帮助MySQL高效获取数据的数据结构。

可以理解为：索引是一个排好序的数据结构。

主键索引

唯一索引

普通索引

全文索引

组合索引

主键索引

以主键作为索引，即主引，要求不可以重复，不允许为空值。

创建方式

建表时指定主索引

–方式1：直接指定为主键

create table test(

`id` int not null auto_increment primary key ,

`username` varchar(20) not null,

`password` varchar(20) not null

)engine=innodb default charset=utf8mb4

–方式2：先创列，最后再声明主键

create table `test`(

`id` int(11) not null ,

`username` varchar(20) not null,

`password` varchar(20) not null,

primary key (`id`) USING BTREE

)engine=innodb default charset=utf8mb4;

–创建之后的表结构

desc test;

+———-+————-+——+—–+———+—————-+

+———-+————-+——+—–+———+—————-+

| id | int(11) | NO | PRI | NULL | auto_increment |

+———-+————-+——+—–+———+—————-+

建表之后再添加主键

–建表时不指索引

create table `test`(

`id` int(11) not null,

`username` varchar(20) not null,

`password` varchar(20) not null

)engine=innodb default charset=utf8mb4;

–建表后修改列，其他索引也可以先建表后添加。

alter table test modify id int primary key auto_increment;

删除主键

注意，主键索引比较有牌面，不能一步到位的删除(其他类型的索引随便删)

我们先把主键索引所在的列摘除索引信息，然后再删除主键

–先摘除primary key

alter table test modify id int;

–删除primary key

alter table test drop primary key;

唯一索引

用来建立索引的列，值必须为唯一的，但是可以为NULL

–建表时直接指定唯一索引

create table `test`(

`id` int(11) not null,

`username` varchar(20) not null unique,

`password` varchar(20) not null

)engine=innodb default charset=utf8mb4;

–建表时先创建列，然后知道唯一索引

create table `test`(

`id` int(11) not null,

`username` varchar(20) not null,

`password` varchar(20) not null，

unique key `index_name` (`name`)

)engine=innodb default charset=utf8mb4;

–建表之后的

desc test;

+———-+————-+——+—–+———+——-+

+———-+————-+——+—–+———+——-+

| id | int(11) | NO | | NULL | |

+———-+————-+——+—–+———+——-+

–建表后修改列，其他索引也可以先建表后添加。

alter table test add unique index_username(username);

–删除唯一索引，根据当前索引名去进行删除

alter table test drop index index_username;

思考一下，如果一个表中有多个NULL，那么唯一索引会怎么样

普通索引

用表中的普通列构建索引，没有任何限制。

基本操作

–创建表时，直接创建普通索引

create table `test`(

`id` int(11) not null,

`username` varchar(20) not null,

`password` varchar(20) not null,

key `index_username` (`username`)

)engine=innodb default charset=utf8mb4;

–创建之后的表结构

desc test;

+———-+————-+——+—–+———+——-+

+———-+————-+——+—–+———+——-+

| id | int(11) | NO | | NULL | |

+———-+————-+——+—–+———+——-+

–添加索引

alter table test add index index_username(username);

–删除索引

drop index index_username on test;

全文索引(了解)

用大文本对象的列构建的索引

注意：

MySQL 5.6之前的版本，全文索引只能用于MyISAM存储引擎

5.6及之后的版本，MyISAM和InnoDB均支持全文索引

MySQL8之前，全文索引只对英文有用，MySQL8开始支持中文索引

全文索引创建速度比其他索引慢，相应的查询效率也比其他索引慢

用法

–添加一个text大文本的列

alter table test add `text` text not null;

–将之设置为全文索引

alter table test add fulltext index ft_index(text);

–设置全文索引之后的表结构，我们看到他和普通索引标识一样，只是用大文本作为索引的普通主键

+———-+————-+——+—–+———+——-+

+———-+————-+——+—–+———+——-+

| id | int(11) | NO | | NULL | |

+———-+————-+——+—–+———+——-+

组合索引

用多个列组成构建的索引，这多个列中的值不允许为空

–创建表

create table `test`(

`id` int(11) not null,

`username` varchar(20) not null,

`password` varchar(20) not null

)engine=innodb default charset=utf8mb4;

–添加索引

alter table test add index index_x(username,password);

–删除索引

alter table test drop index index_x;

–创建之后的表结构

desc test;

+———-+————-+——+—–+———+——-+

+———-+————-+——+—–+———+——-+

| id | int(11) | NO | | NULL | |

+———-+————-+——+—–+———+——-+

我们看到我们明明指定了两个字段，为什么只有一个字段有Key?

–删除之前创建的组合索引

alter table test drop index index_x;

–创建一个组合索引

alter table test add index index_x(password,username);

–查看表结构

desc test;

+———-+————-+——+—–+———+——-+

+———-+————-+——+—–+———+——-+

| id | int(11) | NO | | NULL | |

+———-+————-+——+—–+———+——-+

我们看到，貌似只有第一个传入组合索引的值是有key标识的，这里我们需要了解一下组合索引的一些规则

最左原则

当我们使用组合索引时，需要按照传入的顺序，进行查找才能开启索引，这个顺序是从左往右，不可以跳值，必须从第一个开始

—添加索引

alter table user add index in_x(emial,phone,uname);

观察这个图你应该就明白了

本文主要介绍了MySQL索引的分类，其中主键索引，唯一索引应用的最多，全文索引应用的最少。相对而言，查询速度依次是主键索引>唯一索引>普通索引>=组合索引>全文索引。

我们数据库都会设置一个主键，当然也不是强制性的，不过已经是行内标准了。有的时候我们并不能通过主键去查询。比如我们的主键是比较长的工作编号，我们通过姓名来查询员工信息，一般情况下是惟一的，这时候我们可以给这个姓名添加一个索引。

当我们要查询的内容很多，比如订单，订单时间，地点等信息可能会有很多类。当我们要查询具体的订单时可以根据订单号(唯一主键)来查询，如果要查询一系列订单(一定的特征区间内)，我们可以建立组合索引。

索引虽好，索引能加速查询效率，但是索引会降低增删效率。因为索引是数据结构，实际上现在一般是一种树的结构或者哈希表的结构，也是有顺序的，虽然查找加快了，但是为了维护顺序，也会有一些系统开销。

索引是一个庞大的数据结构，一个1.2G的数据库文件，如果我们对一个列添加索引，文件大小可能会达到1.5甚至1.7G的大小，这已经非常占用系统资源了，所以一般情况下，除非深思熟虑的考虑，否则不要盲目的添加索引。

关于多个索引，如果我们的查询涉及多个列的信息，而这多个列都要建立索引，应该使用组合索引而不是多个普通索引。举个例子：订单地址，订单时间，可能我们在一些情况下已经可以找到我们的详细信息，这时候订单地点，等其他索引看似也参与了查询过程，实际上根本没用上，浪费了大量的磁盘资源。而组合索引先查询最左边的，如果没有得到详细结果，再查下一个直到找到为止，而且一个组合索引的磁盘开销是小于多个普通索引的

关于索引的优化，我们看一下篇博文。

MySQL常用的 5种索引的创建删除以及区别相关教程

你可能也喜欢