MySQL索引语句

MySQL语句请跳转：MySQL语句

创建索引

CREATE INDEX语句

 该语句用于在已存在的表添加索引

语法

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

说明

• 索引名：指定索引名。一个表可以创建多个索引，但每个索引名在表中唯一
• 表名：指定在哪个表中创建索引
• 列名：指定要创建索引的列名。通常可以考虑将查询语句中在 JOIN 子句和 WHERE 子句里经常出现的列作为索引列，可以是一列也可以是多列，一列的称之为单列索引，多列成为组合索引
• 长度：可选项。指定使用列前的 length 个字符来创建索引。使用列的一部分创建索引有利于减小索引文件的大小，节省索引列所占的空间。在某些情况下，只能对列的前缀进行索引。索引列的长度有一个最大上限 255 个字节（MyISAM 和 InnoDB 表的最大上限为 1000 个字节），如果索引列的长度超过了这个上限，就只能用列的前缀进行索引。另外，BLOB 或 TEXT 类型的列也必须使用前缀索引
• ASC | DESC：可选项。ASC指定索引按照升序来排列,DNSC指定索引按照降序来排列，默认为ASC

例子
 给学生表的name字段设置索引,索引名为name

create index `name` on student(`name`);

CREATE TABLE语句

 用于在创建表（CREATE TABLE）时创建，在CREATE TABLE 语句下添加

语法
1、表示在创建新表的同时创建该表的索引

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

2、表示在创建新表的同时创建该表的唯一性索引

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

例子

CREATE TABLE `student`(
	`student_id` INT PRIMARY KEY AUTO_INCREMENT COMMENT '学生id',
	`name` VARCHAR(10) NOT NULL COMMENT '学生姓名',
	`sex` VARCHAR(1) COMMENT '性别',
	`age` INT COMMENT '年龄',
	`phone` VARCHAR(11) COMMENT '手机号',
	`birthday` DATETIME COMMENT '生日',
	`create_time` DATETIME NOT NULL COMMENT '创建时间',
	`update_time` DATETIME COMMENT '修改时间',
	INDEX(`name`) # 在name列上添加索引(索引名默认列名)
)ENGINE=InnoDB CHARACTER SET=utf8;

ALTER TABLE语句

 在使用 ALTER TABLE 语句修改表的同时，可以向已有的表添加索引。具体的做法是在 ALTER TABLE 语句中添加以下语法成分的某一项或几项

语法
1、在修改表的同时为该表添加索引

ADD INDEX [<索引名>] [<索引类型>] (<列名>,…)

2、在修改表的同时为该表添加唯一性索引

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

例子

ALTER TABLE student ADD INDEX index_class (class);

查看索引

语句

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

例子

 查看该表的所有索引

show index from student

查看索引图

说明

• 表名：查询索引的表名
• 数据库名：要查询的表在哪个数据库，这个可以使用数据库名.表名替代
• Table：表名
• No_unique：用于显示该索引是否是唯一索引。若不是唯一索引，则该列的值显示为 1；若是唯一索引，则该列的值显示为 0
• Key_name：索引的名称，不指定名称就是列名
• Seq_in_index：索引中的列序列号，从1开始计数
• Column_name：列名
• Collation：显示列以何种顺序存储在索引中。在 MySQL 中，升序显示值“A”，若显示为 NULL，则表示无分类
• Cardinality：显示索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL 使用该索引的机会就越大
• Sub_part：若列只是被部分编入索引，则为被编入索引的字符的数目。若整列被编入索引，则为 NULL
• Packed：指示关键字如何被压缩。若没有被压缩，则为 NULL
• Null：用于显示索引列中是否包含 NULL。若列含有 NULL，则显示为 YES
• Index_type：显示索引使用的类型和方法（BTREE、FULLTEXT、HASH、RTREE）
• Comment：显示评注
• Index_comment：显示评注

删除索引

语法

DROP INDEX <索引名> ON <表名>
或者
ALTER TABLE <表名> DROP INDEX <索引名>

 注意：如果删除的列是索引的组成部分，那么在删除该列时，也会将该列从索引中删除；如果组成索引的所有列都被删除，那么整个索引将被删除。
例子

drop index `name` on student;
或
alter table student drop index `name`;