索引覆盖、索引失效以及查看索引执行计划 Myth丶恋晨 2024-03-30 12:10 13阅读 0赞 在讲述这三个主题之前,我们首先对索引的分类进行概述: 1.按照创建方式 索引包括自动创建的索引和主动创建的索引:自动的创建主键约束(PRIMARY KEY)、唯一约束(UNIQUE)、外键约束(FOREIGN KEY)时,会自动创建 对应列的索引。(即外键索引,唯一索引以及外键索引)如果一张表中不存在这些数据,则会自动创建一个普通索引(这个索引是我们不能看见的),而其他我们我们手动创建的索引同样也是普通索引 而我们在手动创建的索引中,可以使用其中的两个列创建复合索引,如下: ![d3cdc7c071334a3eae6d74e95127a445.png][] 2.按照应用场景来分: 分为聚集索引(主键索引)和非聚集索引(其他索引) 为什么主键索引被称为聚集索引? mysql在储存主键时,会储存以主键为索引的所有数据信息,而非聚集索引只会储存索引列和主键信息(因为通过索引列找到数据然后通过主键查看所有信息) 我们通过查看索引执行计划中间穿插对索引覆盖进行讲解(利用实例) **查看索引执行计划:** ![05ff46b807d94c0fb51fc73981fa076c.png][] 以下全部详细解析explain各个属性含义: 各属性含义: **id**: **查询的序列号** .**select\_type**: 查**询的类型,主要是区别普通查询和联合查询、子查询之类的复杂查询** SIMPLE:**查询中不包含子查询或者UNION,也就是单独的一条SQL 查询中若包含任何复杂的子部分**,最外层查询则被标记为:**PRIMARY 在SELECT或WHERE列表中包含了子查询,该子查询被标记为:SUBQUERY** 3**.table:** 输出的行所引用的表 type: 访问类型 从左至右,性能由差到好 ![329eb026a43a4ea3924acb170533cb0d.png][] 3.1.ALL: 扫描全表 index: 扫描全部索引树 3.2.index: 扫描全部索引树 3.3.range: 扫描部分索引,索引范围扫描,对索引的扫描开始于某一点,返回匹配值域的行,常 见于between、等的查询 3.4.ref: **使用非唯一索引或非唯一索引前缀进行的查找,不是主键或不是唯一索引** 3.5.eq\_ref:**唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯 一索引扫描** 3.6.const, system: 单表中最多有一个匹配行,查询起来非常迅速,例如根据主键或唯一索引查 询。system是const类型的特例,当查询的表只有一行的情况下, 使用system。 3.7.NULL: 不用访问表或者索引,直接就能得到结果,如: ![9f2a035f258f4ffaa8466d444580ec99.png][] **possible\_keys:**(在sql语句中可能执行哪些索引) 表示查询时可能使用的索引。如果是空的,没有相关的索引。这时要提高性能,可 通过检验WHERE子句,看是否引用某些字段,或者检查字段不是适合索引 **key:** (在sql语句中执行了哪些索引)显示MySQL实际决定使用的索引。如果没有索引被选择,是NULL key\_len: 使用到索引字段的长度 注:key\_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key\_len是根据表定义计 算而得,不是通过表内检索出的。 **ref:** 显示哪个字段或常数与key一起被使用 **rows:** 这个数表示mysql要遍历多少数据才能找到,表示MySQL根据表统计信息及索引选用情况, 估算的找到所需的记录所需要读取的行数,在innodb上可能是不准确的 **Extra:** 执行情况的说明和描述。包含不适合在其他列中显示但十分重要的额外信息。 Using index:表示使用索引,如果只有 Using index,说明他没有查询到数据表,只用索引 表就完成了这个查询,这个叫**覆盖索引。(也就是说所查询的数据都在索引表中,不需要再去主表中去查询)** Using where:表示条件查询,如果不读取表的所有数据,或不是仅仅通过索引就可以获取 所有需要的数据,则会出现 Using where。 **索引失效** 最直观的体现是在key和possible keys两列中本来应该显示使用了索引,但是事实显示并没有调用索引,索引失效的情况包括如下: 1.最左原则:类似于字典的目录,这就是一个典型的复合索引 2.判断不等:每个都要判断 3.类型转换:与原类型不符 4.like '%xxx':第一个字符都不能确定,怎么去索引中比较呢? 5.索引列运算 age + 1:改了原来的值 6.is null 或 is not null : 全表扫描了 [d3cdc7c071334a3eae6d74e95127a445.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/bfff58f2c4874913bbe1f605ac2b8dfa.png [05ff46b807d94c0fb51fc73981fa076c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/256bd1c917f14b3f851a5ca82017f6fe.png [329eb026a43a4ea3924acb170533cb0d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/a50db263ac6c4c808f3962861f68e52f.png [9f2a035f258f4ffaa8466d444580ec99.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/8941da7ef52e499ba44fc660118248ec.png
还没有评论,来说两句吧...