MYSQL-索引的介绍及使用

索引

mysql官方对索引的定义为：索引（Index）是帮助MYSQL高效获取数据的数据结构。
提取句子主干，就可以得到索引的本质，索引是数据结构。

对于索引，我们理解成目录都可以，就像一本书的目录，当我们需要查找书中某个数据时，如果对书本不了解，我们直接找肯定会很费劲，但是如果我们先找目录，查找的速度就会提高，我们理解索引就可以这样理解。

索引的分类

• 主键索引（primary key）
  • 唯一的标识，该列中的任意一个数据都是唯一的，一张表只能有一个主键。
• 唯一索引（unique）
  • 也是唯一的标识，表明该列中的任意一个数据都是唯一的，一张表中允许存在多个唯一索引。
• 常规索引（key/index）
  • 默认的索引，可以用key或者index关键字设置。
• 全文索引（FullText）
  • 在特定的数据库引擎下才有，比如MYISAM
  • 它能快速定位数据

索引的使用

显示某张表所有的索引：

show index from 表名;

增加一个索引：

• 主键索引

-- 方式1：
CREATE TABLE `user`( 
 `id` INT COMMENT '主键id', 
 `name` VARCHAR(30) COMMENT '用户名', 
 `sex` ENUM('男','女') COMMENT '性别', 
 `phone` VARCHAR(15) COMMENT '电话号', 
 `brief` TEXT COMMENT '简介', 
 `birthday` DATE COMMENT '生日',
 PRIMARY KEY(`id`)	-- 建表时将id列添加为主键
 -- PRIMARY KEY(列名) 
) ENGINE=MYISAM CHARSET=utf8 COLLATE=utf8_general_ci;

-- 方式2：
ALTER TABLE 表名 ADD PRIMARY KEY (指定字段);

• 唯一索引

-- 方式1：
CREATE TABLE `user`( 
 `id` INT COMMENT '主键id', 
 `name` VARCHAR(30) COMMENT '用户名', 
 `sex` ENUM('男','女') COMMENT '性别', 
 `phone` VARCHAR(15) COMMENT '电话号', 
 `brief` TEXT COMMENT '简介', 
 `birthday` DATE COMMENT '生日',
 UNIQUE phone_key(`phone`)	-- 建表时为phone列添加名为phone_key的唯一索引
 -- UNIQUE 索引名(列名) 可以不指定索引名 不指定时默认为列名
) ENGINE=MYISAM CHARSET=utf8 COLLATE=utf8_general_ci;

-- 方式2：
ALTER TABLE 表名 ADD UNIQUE INDEX 索引名(列名); --可省略索引名

-- 方式3：
CREATE UNIQUE INDEX 索引名 ON 表名(列名);   --不可省略索引名

• 常规索引

-- 方式1：
CREATE TABLE `user`( 
 `id` INT COMMENT '主键id', 
 `name` VARCHAR(30) COMMENT '用户名', 
 `sex` ENUM('男','女') COMMENT '性别', 
 `phone` VARCHAR(15) COMMENT '电话号', 
 `brief` TEXT COMMENT '简介', 
 `birthday` DATE COMMENT '生日',
 KEY name_key(`name`)	-- 建表时为name列添加名为name_key的普通索引
 -- KEY 索引名(列名) 可以不指定索引名 不指定时默认为列名
) ENGINE=MYISAM CHARSET=utf8 COLLATE=utf8_general_ci;

-- 方式2：
ALTER TABLE 表名 ADD INDEX 索引名(列名);  --可省略索引名

-- 方式3：
CREATE INDEX 索引名 ON 表名(列名);  --不可省略索引名

• 全文索引

-- 方式2：
CREATE TABLE `user`( 
 `id` INT COMMENT '主键id', 
 `name` VARCHAR(30) COMMENT '用户名', 
 `sex` ENUM('男','女') COMMENT '性别', 
 `phone` VARCHAR(15) COMMENT '电话号', 
 `brief` TEXT COMMENT '简介', 
 `birthday` DATE COMMENT '生日', 
 FULLTEXT brief_key(`brief`)	-- 建表时为brief列添加名为brief_key的全文索引
 -- FULLTEXT 索引名(列名) 可以不指定索引名 不指定时默认为列名
) ENGINE=MYISAM CHARSET=utf8 COLLATE=utf8_general_ci;

--方式2：
ALTER TABLE 表名 ADD FULLTEXT 索引名(列名);  --可省略索引名

--方式3：
CREATE FULLTEXT INDEX 索引名 ON 表名(列名);  --不可省略索引名

注意：全文索引的使用，要看当前数据引擎是否支持哦！

删除索引：

• 主键索引

ALTER TABLE 表名 DROP PRIMARY KEY; 

• 唯一索引

ALTER TABLE 表名 DROP INDEX 索引名; 

• 常规索引

ALTER TABLE 表名 DROP INDEX 索引名;

• 全文索引

ALTER TABLE 表名 DROP INDEX 索引名;

我们发现除了主键删除比较特殊以外，其他的索引删除均使用通过索引名删除的方法。（当然可能还有其他删除方式，只是博主还未get到，欢迎各位大佬在评论区指教）

索引的原则

• 索引不是越多越好：索引也是占内存的，过多的索引会影响数据库的效率。
• 不要对经常变动的数据加索引：对索引列添加数据时，数据库会更新它的索引文件，也会影响效率。
• 小数据量的表不需要加索引：索引本身就占空间，对于数据量少的数据，可以避免索引以降低数据表的复杂度。
• 索引一般加在常用于查询的字段上：索引本身的作用就是为了能够快速检索数据，在常用于查询的字段上添加索引，可以大大提高检索的效率。

索引虽好，但是也不能乱用和滥用，用的恰当对整个系统的运行有着很好的效果，用的不好，也会无意降低系统的性能，我们的开发更应该从实际角度出发去考虑任何问题。