mysql之索引

mysql索引

---

---

一、索引的声名和使用

1.1索引的分类

MYSQL的索引的包括普通索引，唯一索引，全文索引，单列索引，多列索引和空间索引。
若按照物理实现方式，索引可以分为两种：聚簇索引和非聚簇索引

1.普通索引

创建普通索引时，可以创建在任何数据类型中，只用于提高查询效率。

2.唯一性索引

使用UNIQUE参数可以设置索引为唯一性索引，在创建唯一性索引时允许 有空值。在一张数据表中可以创建多个唯一性索引。

3.主键索引（聚簇索引）

主键索引是在唯一性索引的基础上增加了非空约束（NOT　ＮＵＬＬ＋UNIQUE）,最多只有一个主键索引。

4.单列索引

在表中的单个字段上创建索引,只根据该字段进行索引。单列索引可以是普通索引,唯一性索引和全文索引。

5.多列(组合,联合)索引

多列索引是在多个字段上组合上创建一个索引,在查询时间只有使用了这些组合中的第一个字段时才生效。

6.全文索引

使用FULLTEXT可以设置索引为全文索引,全文索引非常适合大型数据集.

7.空间索引

使用SPATIAL可以设置为空间索引

1.2 创建索引

创建表时在CREAT TABLE 中指定索引列
使用ALTER TABLE在已经存在的表上创建索引
使用CREAT INDEX在已经存在的表上添加索引

CREATE TABLE table_name[col_name,data_type]
[UNIQUE | FULLTEXT | SPATIAL][INDEX | KEY][index_name](col_name[length]) [ASC|DESC]

[UNIQUE | FULLTEXT | SPATIAL] 为可选参数,分别表示唯一索引,全文索引和空间索引
[INDEX | KEY] 作用相同,用来指定创建索引;
index_name指定索引名称,为可选参数;
col_name为需要创建索引的字段列,该列必须从数据表中定义的多个列选择;
length为可选参数,表示索引的长度(只有字符串可以指定)
[ASC|DESC]为指定升序或者降序的索引值存储

查看索引

SHOW CREATE TABLE 表名\G;

SHOW INDEX FROM 表名;

1.创建普通索引

CREATE TABLE student(
stu_id INT,
stu_name VARCHAR(100),
#声名索引
INDEX IDS_STU(stu_name)
);

若表已经创建成功

ALTER TABLE student ADD INDEX IDS_STU(stu_name);

或

CREATE INDEX IDS_STU ON student(stu_name);

2.创建唯一索引

CREATE TABLE student(
stu_id INT,
stu_name VARCHAR(100),
info VARCHAR(100)
#声名索引
UNIQUE INDEX UK_INFO(info)
);

若表已经创建成功

ALTER TABLE student ADD UNIQUE INDEX UK_INFO(info);

或

CREATE UNIQUE INDEX UK_INFO ON student(info);

3.创建主键索引

通过定义主键约束的方式定义主键索引

CREATE TABLE student(
stu_id INT PRIMARY KEY AUTO_INCREMENT,
stu_name VARCHAR(100),
info VARCHAR(100)
);

4.创建单列索引

CREATE TABLE student(
stu_id INT ,
stu_name VARCHAR(100),
info VARCHAR(100)
#声名索引
UNIQUE INDEX IDX_SNAME(stu_name)
);

若表已经创建成功

ALTER TABLE student ADD UNIQUE INDEX IDX_SNAME(stu_name)

或

CREATE UNIQUE INDEX IDX_SNAME ON student(stu_name);

5.创建联合索引

CREATE TABLE student(
stu_id INT ,
stu_name VARCHAR(100),
info VARCHAR(100)
#声名索引
INDEX MUL_ID_NAME(stu_id,stu_name)
);

若表已经创建成功

ALTER TABLE student ADD INDEX MUL_ID_NAME(stu_id,stu_name)

或

CREATE INDEX MUL_ID_NAME ON student(stu_id,stu_name);

1.3 删除索引

1.使用ALTER TABLE删除索引

基本格式

ALTER TABLE tbale_name DROP INDEX index_name;

2.使用DROP INDEX 删除索引

基本格式

DROP INDEX index_name ON table_name;

二、索引的设计原则

1.适合创建索引的情况

• 字段的数值有唯一性限制的
• 频繁作为WHERE查询的字段
• 经常GROUP BY 和 ORDER BY 的列
• UPDATE、DELETE的WHERE的条件列
• DISTINCT字段需要创建索引
• 多表JOIN连接操作时,连接的表不要超过三张,对WHERE条件创建索引,对用于连接的字段创建索引;
• 使用列的类型小的创建索引
• 使用字符串的前缀创建索引
• 区分度高的列适合作为索引
• 使用最频繁的列放到联合索引的左侧
• 在多个字段都要创建索引的情况下,联合索引优于单值索引

2.不适合创建索引的情况

• 在where中使用不到的字段
• 数据量较小的表
• 有大量重复数据的列上
• 经常更新的数据表避免创建过多的数据列
• 不建议用无序的值作为索引
• 删除不再使用或者很少使用的索引
• 不要定义冗余或者重复的索引