数据库索引知识总结

什么是索引？

索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。通过索引，可以让数据库不必全表扫描，直接快速访问到符合条件的记录，大大加快了查询速度。

索引的效率，优点，缺点

索引的效率取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高。反过来，如果记录的列存在大量相同的值，例如性别列，50%男，50%女，因此，对该列创建索引就没有意义。可以对一张表创建多个索引。索引的优点是提高了查询效率，缺点是在插入、更新和删除记录时，需要同时修改索引，因此，索引越多，插入、更新和删除记录的速度就越慢。

什么情况下需要/不建或少建立索引

需要 1、频繁作为查询条件的字段 2、排序的字段 3、与其他表关联的字段

不建或少建 1、表记录太少 2、经常增删改的表 3、数据重复且分布平均的表字段，比如性别字段50%男 %50女，建立索引也不会提高查询效率

为什么加索引后会使查询变快？

在MySQL中， 索引有两种分类方式：逻辑分类和物理分类。 按照逻辑分类，索引可分为： ①主键索引：一张表只能有一个主键索引，不允许重复、不允许为 NULL； ②唯一索引：数据列不允许重复，允许为 NULL 值，一张表可有多个唯一索引，但是一个唯一索引只能包含一列 ③普通索引：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入； ④全文索引：让搜索关键词更高效的一种索引。 全文索引和like + %有什么不同？ like + % 在文本比较少时是合适的，但是对于大量的文本数据检索，是不可想象的。全文索引在大量的数据面前，能比 like + % 快 N 倍 ⑤组合索引：为了提高mysql效率可建立组合索引，遵循”最左前缀”原则。

按照物理分类，索引可分为： ①聚集索引：以主键创建的索引；聚集索引的叶子节点存储的是表中的数据； ②非聚集索引：非主键创建的索引；非聚集索引在叶子节点存储的是主键和索引列；使用非聚集索引查询数据，会查询到叶子上的主键，再根据主键查到数据（这个过程叫做回表）。

在未加索引时，根据条件查询到一条数据后并不会停止查询，因为可能还会有条件相同的记录，这就是所谓的全表扫描。加上索引后，会查询到叶子上的主键和索引列，再根据主键查到数据，不会进行全表扫描所以查询速度会变快。

为什么 B+ Tree 索引会降低新增、修改、删除的速度？

①B+ Tree 是一颗平衡树，如果对这颗树新增、修改、删除的话，会破坏它的原有结构； ②我们在做数据新增、修改、删除的时候，需要花额外的时间去维护索引； ③正因为这些额外的开销，导致索引会降低新增、修改、删除的速度。

索引失效的场景

①对索引字段进行了运算或者使用了函数 ②表中字段的数据类型和查询的字段类型不一致 ③违反了索引的最左匹配原则 ④模糊匹配 LIKE’%sql%’ 模糊匹配连最开始的字符串都不确定，所以不会走索引，LIKE’sql%’这个是会走索引的。 ⑤优化器认为全表扫描更快 举个例子：如果数据有10万条，要查询的where age > 20 可能有9万多条。查询万9万条还要回表，优化器分析还不如进行全表扫描

EXPLAIN 结果中的type字段

system：系统表，少量数据，往往不需要进行磁盘IO const：常量连接 eq_ref：主键索引(primary key)或者非空唯一索引(unique not null)等值扫描 ref：非主键非唯一索引等值扫描 range：范围扫描 index：索引树扫描 ALL：全表扫描(full table scan)

type扫描方式由快到慢 system > const > eq_ref > ref > range > index > ALL