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 连接操作
⑦使用列的类型小的创建索引
⑧使用字符串前缀创建索引
⑨区分度高(散列性高)的列适合作为索引
⑩使用最频繁的列放到联合索引的左侧
这样也可以较少的建立一些索引。同时,由于"最左前缀原则",可以增加联合索引的使用率。