Mysql高级-索引

---

Mysql索引

索引（Index）是帮助MySQL高效获取数据的数据结构。

优点

（1）类似大学图书馆建书目索引，提高数据检索的效率，降低 数据库的IO成本 ，这也是创建索引最主
要的原因。
（2）通过创建唯一索引，可以保证数据库表中每一行 数据的唯一性 。
（3）对于有依赖关系的子表和父表联合查询时，可以提高查询速度。
（4）在使用分组和排序子句进行数据查询时，可以显著 减少查询中分组和排序的时间 ，降低了CPU的消耗

缺点

（1）创建索引和维护索引要 耗费时间 ，并且随着数据量的增加，所耗费的时间也会增加。
（2）索引需要占 磁盘空间 ，除了数据表占数据空间之外，每一个索引还要占一定的物理空间 。
（3）虽然索引大大提高了查询速度，同时却会 降低更新表的速度 。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

• 因此，选择使用索引时，需要综合考虑索引的优点和缺点。

底层实现B+树

聚簇（聚集）和非聚簇（非聚集）索引

索引按照物理实现方式，索引可以分为 2 种：聚簇（聚集）和非聚簇（非聚集）索引。我们也把非聚集
索引称为二级索引或者辅助索引。

聚簇索引

聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分；

非聚簇索引（回表）

辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，仍然需要到 聚簇索引 中再查一遍，这个过程称为 回表。

创表创建索引

创建普通索引

INDEX(a)

创建唯一索引

UNIQUE INDEX id(id)

创建主键索引

PRIMARY KEY(id)

创建单列索引

INDEX name(name(20))

创建组合索引

INDEX idx(id,name,age)

创建全文索引

FULLTEXT INDEX info(info)

已存在的表上创建索引

使用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]

删除索引

使用ALTER TABLE删除索引

ALTER TABLE table_name DROP INDEX index_name;

使用DROP INDEX语句删除索引

DROP INDEX index_name ON table_name;

适合创建索引

①字段的数值有唯一性的限制

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源：Alibaba）
说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。

②频繁作为 WHERE 查询条件的字段

某个字段在SELECT语句的 WHERE
条件中经常被使用到，那么就需要给这个字段创建索引了。尤其是在数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。
比如student_info数据表（含100万条数据），假设我们想要查询 student_id=123110 的用户信息。

③经常 GROUP BY 和 ORDER BY 的列

索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者 使用 ORDER BY 对数据进行排序的时候，就需要 对分组或者排序的字段进行索引 。如果待排序的列有多 个，那么可以在这些列上建立 组合索引 。

④UPDATE、DELETE 的 WHERE 条件列

对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作，如果对 WHERE
字段创建了索引，就能大幅提升效率。原理是因为我们需要先根据 WHERE
条件列检索出来这条记录，然后再对它进行更新或删除。如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。

⑤DISTINCT 字段需要创建索引

有时候我们需要对某个字段进行去重，使用 DISTINCT，那么对这个字段创建索引，也会提升查询效率

⑥多表 JOIN 连接操作

⑦使用列的类型小的创建索引

⑧使用字符串前缀创建索引

⑨区分度高(散列性高)的列适合作为索引

⑩使用最频繁的列放到联合索引的左侧

这样也可以较少的建立一些索引。同时，由于"最左前缀原则"，可以增加联合索引的使用率。

11在多个字段都要创建索引的情况下，联合索引优于单值索引

不适合创建索引

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

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

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

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

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

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

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