MySQL索引创建及使用

一.索引的作用

        数据库索引好比是一本书前面的目录，能提升数据库的查询效率.

二.索引的类型

1.普通索引 : index

        这是最基本的索引，它没有任何限制, 允许在定义索引的列中插入重复值和空值.

2.主键索引 ：primary 

        主键列中的每个值是非空唯一的， 一个主键将自动创建主键索引

3.唯一索引： unique  

        与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须是唯一的，创建方法和普通索引类似。

4.复合索引

平时用的SQL查询，语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立复合索引。
例如对表中针对title和time建立一个组合索引：

ALTER TABLE article ADD INDEX index_title_time (title(50),time(10))

5.全文索引 ：fulltext

        支持值的全文查找，允许重复值和空值（但是一般不用）

6.空间索引 ：  spatial

        对空间数据类型的列建立的索引 

三、创建方式

• 直接创建索引

示例1 ：普通索引

CREATE INDEX index_name ON table(column(length))

示例2： 唯一索引

CREATE UNIQUE INDEX index_name ON table(column(length))

• 修改表结构的方式添加索引

示例1 ：普通索引

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

示例2： 唯一索引

ALTER TABLE table_name ADD UNIQUE INDEX index_name ON (column(length))

• 创建表的时候同时创建索引
• 单列索引(示例)：

CREATE TABLE projectfile (
	id INT AUTO_INCREMENT COMMENT '附件id',
	fileuploadercode VARCHAR(128) COMMENT '附件上传者code',
	projectid INT COMMENT '项目id;此列受project表中的id列约束',
	filename VARCHAR (512) COMMENT '附件名',
	fileurl VARCHAR (512) COMMENT '附件下载地址',
	filesize BIGINT COMMENT '附件大小，单位Byte',
	-- 主键本身也是一种索引（注:也可以在上面的创建字段时使该字段主键自增）
        PRIMARY KEY (id),
	-- 主外键约束（注:project表中的id字段约束了此表中的projectid字段）
	FOREIGN KEY (projectid) REFERENCES project (id),
	-- 给projectid字段创建了唯一索引(注:也可以在上面的创建字段时使用unique来创建唯一索引)
	UNIQUE INDEX (projectid),
	-- 给fileuploadercode字段创建普通索引
	INDEX (fileuploadercode)
	-- 指定使用INNODB存储引擎(该引擎支持事务)、utf8字符编码
) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT '项目附件表';

复合索引(示例)：

CREATE TABLE projectfile (
	id INT AUTO_INCREMENT COMMENT '附件id',
	fileuploadercode VARCHAR(128) COMMENT '附件上传者code',
	projectid INT COMMENT '项目id;此列受project表中的id列约束',
	filename VARCHAR (512) COMMENT '附件名',
	fileurl VARCHAR (512) COMMENT '附件下载地址',
	filesize BIGINT COMMENT '附件大小，单位Byte',
	-- 主键本身也是一种索引（注:也可以在上面的创建字段时使该字段主键自增）
        PRIMARY KEY (id),
        -- 创建组合索引
	INDEX (fileuploadercode,projectid)
	-- 指定使用INNODB存储引擎(该引擎支持事务)、utf8字符编码
) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT '项目附件表';

​​四、索引的删除

方法一：DROP INDEX 索引名 ON 表名

示例：drop index fileuploadercode1 on projectfile;

方法二：ALTER TABLE 表名 DROP INDEX 索引名
示例：alter table projectfile drop index s2123;

五、查看已创建的索引

show index from 表名;