最左匹配(前缀)原则?
MySQL中的索引可以以一定顺序引用多列,这种索引叫作联合索引。如User表的name和city加联合索引就是(name,city),而最左前缀原则指的是,如果查询的时候查询条件精确匹配索引的左边连续一列或几列,则此列就可以被用到。如下:
select * from user where name=xx and city=xx ; // 可以命中索引
select * from user where city=xx and name=xx ; // 可以命中索引
select * from user where name=xx ; // 可以命中索引
select * from user where city=xx ; // 无法命中索引
这里需要注意的是,查询的时候如果两个条件都用上了,但是顺序不同,如 city= xx and name =xx,那么现在的查询引擎会自动优化为匹配联合索引的顺序,这样是能够命中索引的。
(由于最左前缀原则,在创建联合索引时,索引字段的顺序需要考虑字段值去重之后的个数,较多的放前面。ORDER BY子句也遵循此规则)---------------不清楚-----
注意避免冗余索引
冗余索引指的是索引的功能相同,能够命中就肯定能命中 ,那么 就是**冗余索引如(name,city )和(name )**这两个索引就是冗余索引,能够命中后者的查询肯定是能够命中前者的 在大多数情况下,都应该尽量扩展已有的索引而不是创建新索引。
(MySQLS.7 版本后,可以通过查询 sys 库的 schema_redundant_indexes 表来查看冗余索引)----具体如何操作
聚集和非聚集索引
简单概括:
- 聚集索引就是以主键创建的索引
- 非聚集索引就是以非主键创建的索引
区别:
- 聚集索引在叶子节点存储的是表中的数据
- 非聚集索引在叶子节点存储的是主键和索引列
- 使用非聚集索引查询出数据时,拿到叶子上的主键再去查到想要查找的数据。(
拿到主键再查找这个过程叫做 回表
)
非聚集索引也叫做二级索引,非聚集索引在建立的时候也未必是单列的,可以多个列来创建索引。
索引总结
索引在数据库中是一个非常重要的知识点!上面谈的其实就是索引最基本的东西,要创建出好的索引要顾及到很多的方面:
最左前缀匹配原则
。这是非常重要、非常重要、非常重要(重要的事情说三遍)的原则,MySQL会一直向右匹配直到遇到范围查询(>,<,BETWEEN,LIKE)就停止匹配。- 索引列不能参与计算,尽量保持列“干净”。比如,
FROM_UNIXTIME(create_time) = '2016-06-06'
就不能使用索引,原因很简单,B+树中存储的都是数据表中的字段值,但是进行检索时,需要把所有元素都应用函数才能比较,显然这样的代价太大。所以语句要写成 :create_time = UNIX_TIMESTAMP('2016-06-06')
。 - 尽可能的扩展索引,不要新建立索引。比如表中已经有了a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。
- 单个多列组合索引和多个单列索引的检索查询效果不同,因为在执行SQL时,MySQL只能使用一个索引,会从多个单列索引中选择一个限制最为严格的索引。
参考链接:
- https://juejin.im/post/5b55b842f265da0f9e589e79 – Java3y
- https://juejin.im/post/5d1758d06fb9a07eed351405 – SnailClimb
更多信息,请关注公众号哦o')-('o