6、创建索引时应该注意什么

索引的创建

索引的分类

• 普通索引：允许被索引的数据列包含重复的值
  INDEX(year_publication)
• 唯一索引：可以保证数据记录的唯一性（可以有多个null值）
  UNIQUE INDEX uk_idx_id(id)
• 主键索引：是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条记录，使用关键字Primary key来创建（不能有null值）
  PRIMARY KEY(id)
• 单列索引
  INDEX single_idx_name(name(20))
• 复合索引：索引可以覆盖多个数据列
  INDEX multi_idx(id,name,age)
• 全文索引：通过建立倒排索引，可以极大的提升检索效率，如果需要全文索引的是大量数据，建议先添加数据，再创建索引。
  FULLTEXT INDEX futxt_idx_info(info)
• 空间索引：空间索引创建中，要求空间类型的字段必须为非空。
  SPATIAL INDEX spa_idx_geo(geo)

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

1. 使用ALTER TABLE语句创建索引 ALTER TABLE语句创建索引的基本语法如下：
   ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name[length],...) [ASC | DESC]

2. 使用CREATE INDEX创建索引 CREATE INDEX语句可以在已经存在的表上添加索引，在MySQL中，CREATE INDEX被映射到一个ALTER TABLE语句上，基本语法结构为：
   CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON table_name (col_name[length],...) [ASC | DESC]

索引的设计原则

哪些情况适合创建索引

• 字段的数值有唯一性的限制
  业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。
• 频繁作为WHERE查询条件的字段
  尤其是在数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。
• 经常GROUP BY和ORDER BY的列
  索引就是让数据按照某种顺序进行存储或检索，order by和group by进行排序时可以利用索引中已经排好的序。
• UPDATE、DELETE的WHERE条件列
  对数据按照某个条件进行查询后再进行UPDATE或DELETE的操作，如果对WHERE字段创建了索引，就能大幅提升效率。
• DISTINCT字段需要创建索引
  因为索引会对数据按照某种顺序进行排序，所以在去重的时候也会快很多。
• 多表JOIN连接操作时，对用于连接的字段创建索引，连接字段的类型必须一致，如果不一致会出现索引失效
• 使用列的类型小的创建索引
  列的类型小，可以在一个也中存储多个索引，查询速度更快
• 使用字符串前缀创建索引
  使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本
一般对字符串类型数据截取长度为20来作为索引，这时区分度会高达90%以上
• 区分度高(散列性高)的列适合作为索引
• 使用最频繁的列放到联合索引的左侧
• 在多个字段都要创建索引的情况下，联合索引优于单值索引，查询速度快

哪些情况不适合创建索引

• 在where中使用不到的字段，不要设置索引
• 数据量小的表最好不要使用索引
• 有大量重复数据的列上不要建立索引
• 避免对经常更新的表创建过多的索引
  经常更新表中的数据还需要更新索引
• 不建议用无序的值作为索引
  无序的值可能会出现页分裂，因为索引会按照某种顺序进行排序
• 删除不再使用或者很少使用的索引
• 不要定义冗余或重复的索引