mysql(创建/查看/修改删除)索引(含实战练习)

本博文源于mysql基础，主要针对索引进行学习与练习。包含索引的创建、查看、修改与删除。

为什么要使用索引

索引是Mysql中一种十分重要的数据对象。常用于实现数据的快速检索。

索引的分类

索引根据物理和逻辑进行分类。根据物理分类：

• B-树索引：可以进行全键值、键值范围和键值前缀查询
• 哈希索引：哈希索引最大的特点是访问速度快。但是不能使用哈希索引进行排序

根据逻辑分为：

• 普通索引是最基本的索引类型，通常使用的关键字是INDEX和KEY
• 唯一性索引是不允许索引具有相同索引值的索引。创建唯一性索引的目的往往不是为了提高访问速度，而是为了避免数据出现重复。
• 空间索引主要用于地理空间数据类型GEOMETRY
• 全文索引只能在varchar和text类型的列上创建

索引的使用原则和注意事项

索引缺点

• 创建和维护索引要耗费时间
• 索引需要动态维护
• 对于那些只有很少数据值的列不应该创建索引，不能明显加快速度。

索引使用场景

• 经常需要搜索的列上建立索引
• 在作为主键的列上创建索引
• 在经常使用表连接的列上创建索引
• 在经常使用where子句的列上创建索引
• 在经常需要排序的列上创建索引

都可以加快搜索速度。

创建索引

基本语法

使用CREATE INDEX语句

这条语句可以在一个已有的表上创建索引

CREATE <索引名> ON <表名> (<列名> [<长度>] [ASC | DESC])

使用CREATE TABLE语句

索引可以在创建表的同时创建.

• 如果创建新表的同时创建表的主键，需要这样做：

CONSTRAINT PRIMARY KEY [索引类型] (<列名>,...)

• 如果创建新表的同时创建表的索引，需要这样做：

KEY | INDEX [<索引名>] [<索引类型>] (<列名>,...)

• 如果创建新表的同时创建表的唯一性索引，需要这样做：

UNIQUE [INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,...)

• 如果创建新表的同时创建表的外键性索引，需要这样做：

FOREIGN KEY <索引名> <列名>

使用ALTER TABLE语句

这个可以在修改表的同时，添加索引

• 修改表的同时为该表添加索引，可以这样做

ADD INDEX [<索引名>] [<索引类型>] [<列名>,...]

• 修改表的同时为该表添加主键索引

ADD PRIMARY KEY [<索引类型>] （<列名>,...）

• 修改表的同时为该表添加唯一性索引

ADD UNIQUE [INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,...)

• 修改表的同时为表添加外键索引

ADD FOREIGN KEY [<索引名>] (<列名>,...)

创建一般索引

例子：创建一个表tb_stu_info,在该表的height字段创建一般索引

 create table tb_stu_info(id int not null,name char(45) default null,
 dept_id int default null,age int default null,height int default null,index(height));

创建唯一索引

例子：创建一个表tb_stu_info2,在该表的id字段上使用unique关键字创建唯一索引

 create table tb_stu_info2(id int not null,name char(45) default null,
 dept_id int default null,age int default null,
 height int default null,unique index(height));

查看索引

SHOW INDEX FROM <表名> [ FROM <数据库名>]

例子：使用SHOW INDEX语句查看表tb_stu_info2的索引信息

 show index from tb_stu_info2\G;

修改和删除索引

修改索引可以通过删除索引原索引，再根据需要创建一个同名的索引，从而实现修改索引的操作。

基本语法

使用DROP INDEX语句

DROP INDEX <索引名> ON <表名>

使用ALTER TABLE语句

根据ALTER TABLE语句的语法可知，该语句也可以用于删除索引。具体使用方法是将ALTER TABLE语句的语法中部分指定为以下子句的某一项

• DROP PRIMARY KEY:表示删除的主键。一个表中只有一个主键，主键也是一个索引
• DROP INDEX index_name:表示删除名称为index_name的索引
• DROP FOREIGN KEY fk_symbol:表示删除外键

删除索引

例子：使用drop index删除表tb_stu_info中的索引

 drop index height on tb_stu_info;

例子：使用alter table删除表tb_stu_info2中的索引

alter table tb_stu_info2 drop index height;