索引的创建与设计原则

1.索引的分类:MySQL

---

MySQL的索引包括普通索引，唯一性索引，全文索引，单例索引，多列索引和空间索引

1. 功能逻辑上来说，索引主要分为4类：普通索引，唯一性索引，主键索引和空间索引
2. 按照物理的实现方式，索引主要分为2类：聚簇索引和非聚簇索引
3. 按照作用字段的个数：单列索引，联合索引

 

2.创建索引

---

2.1创建表时创建索引 

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

1.UNIQUE 、 FULLTEXT 和 SPATIAL 为可选参数，分别表示唯一索引、全文索引和空间索引；

2.INDEX 与 KEY 为同义词，两者的作用相同，用来指定创建索引；

3.index_name 指定索引的名称，为可选参数，如果不指定，那么 MySQL 默认 col_name 为索引名；

4.col_name 为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；

5.length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；

6.ASC 或 DESC 指定升序或者降序的索引值存储。

---

2.2索引创建

 创建普通索引:index（字段）；

 创建唯一索引:unique index 索引名（字段名）；

 创建主键索引:PRIMARY KEY(字段名)；删除主键索引:alter table 表名 drop PRIMARY KEY;

 修改主键索引:必须先删掉原索引，然后再进行ADD；

创建单例索引:INDEX 索引名（字段名（前缀长度））； 

创建组合索引:INDEX 索引名（字段1，字段2，字段3，......）； 

创建全文索引: FULLTEXT INDEX futxt_idx_info(字段名) ；

创建空间索引: SPATIAL INDEX spa_idx_geo(字段名)；

2.3 在已经存在的表上创建索引

 使用ALTER TABLE语句创建索引 

ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
[index_name] (col_name[length],...) [ASC | DESC]

 使用CREATE INDEX创建索引

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (col_name[length],...) [ASC | DESC]

---

 3.删除索引 

使用 ALTER TABLE 删除索引

 ALTER TABLE table_name DROP INDEX index_name;

使用DROP INDEX语句删除索引

 DROP INDEX index_name ON table_name;

4.索引的设计原则 

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

  ---

  5.哪些情况不适合创建索引

  1. 在 where 中使用不到的字段，不要设置索引

  2. 数据量小的表最好不要使用索引

  3. 有大量重复数据的列上不要建立索引

  4. 避免对经常更新的表创建过多的索引

  5. 不建议用无序的值作为索引

  6. 删除不再使用或者很少使用的索引

  7. 不要定义冗余或重复的索引