我的编程空间,编程开发者的网络收藏夹
学习永远不晚

MySQL索引 索引分类 最左前缀原则 覆盖索引 索引下推 联合索引顺序 - G

短信预约 信息系统项目管理师 报名、考试、查分时间动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号立即预约

请填写图片验证码后获取短信验证码

看不清楚,换张图片

免费获取短信验证码

MySQL索引 索引分类 最左前缀原则 覆盖索引 索引下推 联合索引顺序 - G

MySQL索引 索引分类 最左前缀原则 覆盖索引 索引下推 联合索引顺序 - G

 

What"s Index ?

索引就是帮助RDBMS高效获取数据的数据结构。

索引可以让我们避免一行一行进行全表扫描。它的价值就是可以帮助你对数据进行快速定位。

 

索引分类

按照功能逻辑来分

  • 普通索引 INDEX(col_name)或者key index_name(col_name) 没有任何约束,一张表可以有多个普通索引
  • 唯一索引 UNIQUE INDEX index_name(col_name) 在不同索引上增加了唯一约束,允许多个ull值
  • 主键索引 PRIMARY KEY (col_name) 主键约束=UNIQUE+NOT NULL,一张表只能有一个主键索引---该性质由主键索引的物理实现方式决定---数据在文件中只能按照一种顺序进行存储
  • 外键索引 CONSTRAINT foreign_key_name FOREIGN KEY(col_name) REFREENCES table_name(col2_name)
  • 全文索引 FULLTEXT INDEX index_name(col_name)...)ENGINE=MyISAM; MySQL自带的全文索引仅支持英文,一般我们使用专门的全文搜索引擎Elasticsearch或者solr。

 

按照物理实现方式来分(也是按照叶子结点的内容来分)索引模型见此working

  • 聚集索引(InnnoDB主键索引/ clustered index),叶子节点存放的是整行数据。一张表只能有一个聚集(InnnoDB主键)索引。查询时,尽量选用主键进行查询,这样可以减少回表次数,提高效率
  • 非聚集索引(InnnoDB非主键索引/二级索引/辅助索引/second index),叶子节点存放的是主键的值,为了找到数据,它单独维护了一个索引表(树),先在索引表(树)中查询主键值,再到主键索引上查找对应的真实数据,这个过程称为回表
  • 区别:
    • 聚集索引叶子节点存储整行数据,非聚集索引存储的是主键值---是数据位置,非聚集索引不会影响数据表的物理存储顺序。(而聚集索引决定物理存储结构)
    • 一张表只能有一个聚集索引,但可以有多个非聚集索引
    • 主键索引的查询效率高,但对数据的增删改的效率比非主键索引低

但是请格外注意:主键索引不一定是聚集索引

因为聚集索引决定了数据库物理存储结构,而主键只决定数据库逻辑组织方式

在InnoDB中,主键是一个聚集索引,但当一个表没有主键或者没有索引,innodb也会有对应的处理规则。而在MyISAM引擎中,主键不是聚集索引。一般按照innodb与机具集索引的关系进行讨论。

 

按照字段个数来分

  • 单一索引
  • 联合(组合)索引

 

最左前缀原则

创建联合索引时我们要注意联合索引的字段顺序

因为B+树的结构,所以有最左前缀原则/最左匹配原则,意思就是,对于联合索引,只要查询条件与联合索引从左到右部分字段顺序相匹配,该次查询就可以利用联合索引进行加速。

例如现有联合索引(x,y,z)

如果查询条件是WHERE x=1 AND y=2 AND z=3;那么就是对该联合索引的完全匹配。

如果查询条件是WHERE x=1 AND y=2;那么就是利用了该联合索引(符合最左匹配原则)

当然,WHRER x=1;可以以利用该联合索引

如果是WHERE y=2 AND z=3;或者 WHERE z=3;等,就无法利用联合索引(x,y,z)

 

如何安排联合索引的字段顺序

  • 原则一:精简索引数量

    如果联合索引的顺序可以让我们少维护一个索引,那么这个联合索引顺序就该被优先考虑

    比如说现在你有两个字段

    那么只需要建立(a,b)+(b)两个索引即可。而不是(a)+(b)+(a+b)

    因为最左前缀原则给予了联合索引复用能力

    当然,如果仅有根据字段a来进行索引的要求,b字段的单一索引可以不创建。如无必要,勿增实体

  • 原则二:精简索引占用的空间

    那么在a,b两个字段,中我们应该选用哪个字段来做单独的索引(b)呢

    这时候就需要考虑空间了,一般将字段长度比较小的当做这里的单一索引(b)。

    比如说有name和age两个字段,name字段比age大,如果我们使用(age,name)+(name)的形式,所占用的空间就比(name,age)+(age)的要大

 

覆盖索引

用自己的话总结,覆盖索引就是不需要回表的查询

翻译过来就是, 要的查询结果就是主键值且查询条件就是非主键索引字段,即在索引树上的查询结果就是我们需要的结果。

索引覆盖可以显著减少树的搜索次数,显著提升查询性能。是常用的性能优化手段。

例如,现有主键id,有已经建立了普通索引的字段col(int),我们查询SELECT id from test where col between 6 and 8;

执行该语句时,引擎内部在索引树上读取了三条记录,但因为对于MySQL的Server层来说,该语句就是拿到了符合条件的两个记录,因此MySQL认为扫描行数是2

 

索引下推

MySQL5.6版本引入索引下推,就是在只能利用部分联合索引时,对剩余联合索引的字段(不符和最左匹配原则的字段)进行先判断,先过滤,通过先过滤来减少回表的次数。

例如有索引(name,age),然后执行SELECT * FORM student WHERE name like "高%" and age=21;

6后,MySQL就会先利用联合索引找出所有姓高的名字,然后仅对age=21的主键值进行回表,而不是找出所有姓高的同学之后,对他们的所有主键值都进行回表。

 

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

MySQL索引 索引分类 最左前缀原则 覆盖索引 索引下推 联合索引顺序 - G

下载Word文档到电脑,方便收藏和打印~

下载Word文档

猜你喜欢

MySQL索引 索引分类 最左前缀原则 覆盖索引 索引下推 联合索引顺序 - G

MySQL索引 索引分类 最左前缀原则 覆盖索引 索引下推 联合索引顺序 What"s Index ?索引就是帮助RDBMS高效获取数据的数据结构。索引可以让我们避免一行一行进行全表扫描。它的价值就是可以帮助你对数据进行快速定位。 索引分类按照功能逻辑来分普通
MySQL索引 索引分类 最左前缀原则 覆盖索引 索引下推 联合索引顺序 - G
2018-09-14

MySQL联合索引遵循最左前缀匹配原则

面试官: 我看你的简历上写着精通mysql,问你个简单的问题,MySQL联合索引有什么特性?心想,这还不简单,这不是问到我手心里了吗?听我给你背一遍八股文!我: MySQL联合索引遵循最左前缀匹配原则,即最左优先,查询的时候会优先匹配
2022-08-16

Mysql索引的最左前缀原则是什么

这篇文章主要介绍了Mysql索引的最左前缀原则是什么的相关知识,内容详细易懂,操作简单快捷,具有一定借鉴价值,相信大家阅读完这篇Mysql索引的最左前缀原则是什么文章都会有所收获,下面我们一起来看看吧。前言之所以有这个最左前缀索引归根结底是
2023-06-29

最左前缀有手就会,那索引下推呢?

联合索引的最左前缀原则属于面试高频题,想必大部分同学都知道一些,但是,那些不符合最左前缀的部分,会怎么样呢(索引下推)?

验证Mysql中联合索引的最左匹配原则详情

这篇文章主要介绍了验证Mysql中联合索引的最左匹配原则详情,文章围绕主题展开详细的内容介绍,具有一定的参考价值,需要的小伙伴可以参考一下
2022-11-13

编程热搜

目录