MySQL索引的创建与使用

最新推荐文章于 2022-11-21 23:05:14 发布

喜只狼果冻

最新推荐文章于 2022-11-21 23:05:14 发布

阅读量234

点赞数

本文链接：https://blog.youkuaiyun.com/qq_36750352/article/details/105551372

版权

本文深入讲解MySQL中的单列索引和组合索引的创建与使用方法，包括索引的创建、修改、删除及查看，提供了丰富的示例代码，帮助读者理解如何在实际应用中有效利用索引提升数据库性能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

索引有很多，且按不同的分类方式，又有很多种分类。不同的数据库，对索引的支持情况也不尽相同。

声明：本人主要简单示例MySQL中的单列索引、组合索引的创建与使用。

索引的创建：

建表时创建：

CREATE TABLE 表名(

字段名 数据类型 [完整性约束条件],

       ……，

[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY

[索引名](字段名1 [(长度)] [ASC | DESC]) [USING 索引方法]

);

UNIQUE:可选。唯一性索引。
FULLTEXT:可选。全文索引。
SPATIAL:可选。空间索引。
INDEX和KEY:用于指定字段为索引，两者选择其中之一就可以了
索引名:可选。给创建的索引取一个新名称。
字段名1:指定索引对应的字段的名称
长度:可选。指索引的长度，必须是字符串类型才可以使用。
ASC:可选。表示升序排列。
DESC:可选。表示降序排列。
注：索引方法默认使用B+TREE。

单列索引(示例)：

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 '项目附件表';

建表后创建：

ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL]  INDEX | KEY  [索引名] (字段名1 [(长度)] [ASC | DESC]) [USING 索引方法]；

或

CREATE  [UNIQUE | FULLTEXT | SPATIAL]  INDEX  索引名 ON  表名(字段名) [USING 索引方法]；

示例一：

-- 假设建表时fileuploadercode字段没创建索引(注:同一个字段可以创建多个索引，但一般情况下意义不大)
-- 给projectfile表中的fileuploadercode创建索引
ALTER TABLE projectfile ADD UNIQUE INDEX (fileuploadercode);

示例二：

ALTER TABLE projectfile ADD INDEX (fileuploadercode, projectid);

示例三：

-- 将id列设置为主键
ALTER TABLE index_demo ADD PRIMARY KEY(id) ;
-- 将id列设置为自增
ALTER TABLE index_demo MODIFY id INT auto_increment;

查看已创建的索引：

show index from 表名;

提示:我们也可以直接使用工具查看

索引的删除：

DROP INDEX 索引名 ON 表名

或

ALTER TABLE 表名 DROP INDEX 索引名

示例一：

drop index fileuploadercode1 on projectfile;

示例二：

alter table projectfile drop index s2123;