目录

1. 查看索引

2.创建索引

3.删除索引

4.MySQL8.0索引新特性

支持降序索引

隐藏索引

5.适合创建索引的情况

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

1. 查看索引

show index from book

2.创建索引

  • 创建普通索引
Index idx_bname(book_name)
  • 创建唯一索引
UNIQUE INDEX uk_idx_cmt(COMMENT)
  • 创建主键索引
book_id INT PRIMARY KEY
  • 创建单列索引
INDEX single_idx_name(name(20))
  • 创建组合索引
INDEX single_idx_name(name(20))
  • 创建全文索引
INDEX single_idx_name(name(20))
  • 创建空间索引
SPATIAL INDEX spa_idx_geo(geo)
  • 在已经存在的表上创建索引

法1：

ALTER TABLE table_name ADD index [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]

法2：

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON table_name

3.删除索引

法1：

ALTER TABLE table_name DROP INDEX index_name;

注意：添加AUTO_INCREMENT约束字段的唯一索引不能被删除

法2：

ALTER TABLE table_name DROP INDEX index_name;

4.MySQL8.0索引新特性

支持降序索引

CREATE TABLE ts1(a int,b int,index idx_a_b(a,b desc));

隐藏索引

注意：主键不能设置

1. 创建表时直接创建

INDEX [indexname](propname1 [(length)]) INVISIBLE

2.创建表以后

在已经存在的表上创建

CREATE INDEX indexname ON tablename(propname[(length)]) INVISIBLE

 通过ALTER TABLE语句创建

ALTER TABLE tablename ADD INDEX indexname (propname [(length)]) INVISIBLE;

切换索引可见状态

ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; #切换成隐藏索引
ALTER TABLE tablename ALTER INDEX index_name VISIBLE; #切换成非隐藏索引

注意： 当索引被隐藏时，它的内容仍然是和正常索引一样实时更新的。如果一个索引需要长期被隐藏，那么可以将其删除，因为索引的存在会影响插入、更新和删除的性能。

5.适合创建索引的情况

  1. 字段的数值有唯一性的限制
  2. 频繁作为 WHERE 查询条件的字段
  3. 经常 GROUP BY 和 ORDER BY 的列
  4. UPDATE、DELETE 的 WHERE 条件列
  5. DISTINCT 字段需要创建索引
  6. 多表 JOIN 连接操作时，创建索引注意事项：首先， 连接表的数量尽量不要超过 3 张，其次， 对 WHERE 条件创建索引，最后， 对用于连接的字段创建索引
  7. 使用列的类型小的创建索引
  8. 使用字符串前缀创建索引
  9. 区分度高(散列性高)的列适合作为索引
  10. 使用最频繁的列放到联合索引的左侧
  11. 在多个字段都要创建索引的情况下，联合索引优于单值索引

注意：限制索引的数目：单张表不超过6个

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

  1. 在where中使用不到的字段，不要设置索引
  2. 数据量小的表最好不要使用索引
  3. 有大量重复数据的列上不要建立索引
  4. 避免对经常更新的表创建过多的索引
  5. 不建议用无序的值作为索引
  6. 删除不再使用或者很少使用的索引
  7. 不要定义冗余或重复的索引