MySQL中有哪些情况下数据库索引会失效详析

前言

要想分析MySQL查询语句中的相关信息，如是全表查询还是部分查询，就要用到explain.

索引的优点

  • 大大减少了服务器需要扫描的数据量
  • 可以帮助服务器避免排序或减少使用临时表排序
  • 索引可以随机I/O变为顺序I/O

索引的缺点

  • 需要占用磁盘空间，因此冗余低效的索引将占用大量的磁盘空间
  • 降低DML性能，对于数据的任意增删改都需要调整对应的索引，甚至出现索引分裂
  • 索引会产生相应的碎片，产生维护开销

一、explain

用法：explain +查询语句。

id：查询语句的序列号，上面图片中只有一个select 语句，所以只会显示一个序列号。如果有嵌套查询，如下

select_type：表示查询类型，有以下几种

　　simple:简单的 select （没有使用 union或子查询）

　　primary:最外层的 select。

　　union:第二层，在select 之后使用了 union。

　　dependent union:union 语句中的第二个select，依赖于外部子查询

　　subquery:子查询中的第一个 select

　　dependent subquery:子查询中的第一个 subquery依赖于外部的子查询

　　derived:派生表 select（from子句中的子查询）

table：查询的表、结果集

type：全称为"join type"，意为连接类型。通俗的讲就是mysql查找引擎找到满足SQL条件的数据的方式。其值为：

  • system：系统表，表中只有一行数据
  • const：读常量，最多只会有一条记录匹配，由于是常量，实际上只须要读一次。
  • eq_ref：最多只会有一条匹配结果，一般是通过主键或唯一键索引来访问。
  • ref：对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取
  • fulltext：进行全文索引检索。
  • ref_or_null：与ref的唯一区别就是在使用索引引用的查询之外再增加一个空值的查询。
  • index_merge：查询中同时使用两个（或更多）索引，然后对索引结果进行合并，再读取表数据。
  • unique_subquery：子查询中的返回结果字段组合是主键或者唯一约束。
  • index_subquery：子查询中的返回结果字段组合是一个索引（或索引组合），但不是一个主键或唯一索引。
  • rang：索引范围扫描。
  • index：全索引扫描。
  • all：全表扫描。

　　性能从上到下依次降低。

possible_keys：可能用到的索引

key：使用的索引

ref：ref列显示使用哪个列或常数与key一起从表中选择行。

rows：显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。

Extra：额外的信息

  • Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。
  • Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
  • range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。
  • Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。
  • Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。
  • Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。
  • Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。
  • Using sort_union(...), Using union(...), Using intersect(...):这些函数说明如何为index_merge联接类型合并索引扫描。
  • Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。

二、数据库不使用索引的情况

下面举的例子中，GudiNo、StoreId列都有单独的索引。

2.1、like查询已 '%...'开头，以'xxx%'结尾会继续使用索引。

下图中第一句使用的%，没有使用索引，从rows为224147，使用索引rows为1。

　　　　

2.2 where语句中使用 <>和 !=

2.3 where语句中使用 or，但是没有把or中所有字段加上索引。

这种情况，如果需要使用索引需要将or中所有的字段都加上索引。

2.4 where语句中对字段表达式操作

2.5 where语句中使用Not In

看了别人写的文章，有说“应尽量避免在where 子句中对字段进行null 值判断，否则将导致引擎放弃使用索引而进行全表扫描”，实测没有全表扫描。

"对于多列索引，不是使用的第一部分，则不会使用索引",实测即使多索引，没有使用第一部分，也会命中索引，没有全表扫描。

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对我们的支持。

赞 (0)

相关推荐

  • mysql索引失效的五种情况分析

    mysql索引失效的五种情况分析

    索引并不是时时都会生效的,比如以下几种情况,将导致索引失效: 如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因) 注意:要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引 2.对于多列索引,不是使用的第一部分,则不会使用索引 3.like查询是以%开头 4.如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引 5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引 此外,查看索引的使用情况 show status lik

  • 这种sql写法真的会导致索引失效吗

    前言 网上经常能看到一些文章总结在 mysql 中不能命中索引的各种情况,其中有一种说法就是指使用了 or 的语句都不能命中索引. 这种说法其实是不够正确的,正确的结论应该是,从 mysql5.0 后,如果在 or 连接的字段上都有独立的索引的话,是可以命中索引的,这里就是用到了 index_merge 特性. 在 mysql5.0 版本以前一条 sql 只能选择使用一个索引,而且如果 sql 中使用了 or 关键字,那么已有的索引就会失效,会走全表扫描.因为无论走哪个索引,mysql 都不能一

  • Mysql索引会失效的几种情况分析

    Mysql索引会失效的几种情况分析

    索引并不是时时都会生效的,比如以下几种情况,将导致索引失效: 1.如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因) 注意:要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引 2.对于多列索引,不是使用的第一部分,则不会使用索引 3.like查询是以%开头 4.如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引 5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引 此外,查看索引的使用情况show status li

  • mysql的in会不会让索引失效?

    mysql的in会让索引失效吗?不会! 看结果: mysql> desc select * from tb_province where name in ('lily3', 'lily2', 'lily1'); +----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | id | select_t

  • MySQL中有哪些情况下数据库索引会失效详析

    前言 要想分析MySQL查询语句中的相关信息,如是全表查询还是部分查询,就要用到explain. 索引的优点 大大减少了服务器需要扫描的数据量 可以帮助服务器避免排序或减少使用临时表排序 索引可以随机I/O变为顺序I/O 索引的缺点 需要占用磁盘空间,因此冗余低效的索引将占用大量的磁盘空间 降低DML性能,对于数据的任意增删改都需要调整对应的索引,甚至出现索引分裂 索引会产生相应的碎片,产生维护开销 一.explain 用法:explain +查询语句. id:查询语句的序列号,上面图片中只有一

  • Django如何在不停机的情况下创建索引

    该框架在管理数据库更改方面非常强大和有用,但是该框架提供的灵活性受到了一定的限制.为了理解Django迁移的局限性,你将处理一个众所周知的问题:在不停机的情况下,在Django中创建一个索引. 在本教程中,你将学习: Django如何以及何时生成新的迁移: 如何检查Django生成的执行迁移的命令: 如何安全地修改迁移以满足你的需求. 本中级教程是为已经熟悉Django迁移(Migration)的读者设计的. 在Django迁移中创建索引的问题 当应用程序存储的数据增长时,通常需要进行的一个常见

  • 浅谈Mysql在什么情况下会使用内部临时表

    浅谈Mysql在什么情况下会使用内部临时表

    union执行 为了便于分析,使用一下sql来进行举例 CREATE TABLE t1 ( id INT PRIMARY KEY, a INT, b INT, INDEX ( a ) ); delimiter ;; CREATE PROCEDURE idata ( ) BEGIN DECLARE i INT; SET i = 1; WHILE ( i <= 1000 ) DO INSERT INTO t1 VALUES ( i, i, i ); SET i = i + 1; END WHILE;

  • MySQL数据库索引以及失效场景详解

    MySQL数据库索引以及失效场景详解

    目录 1. MySQL索引概述 1.1 索引的概念 1.2 索引的特点 1.3 索引的分类 1.4 索引的使用场景 2. 索引失效场景 2.1 索引失效9种场景 2.2 索引失效场景总结 3. 索引失效验证 3.1 全值匹配 3.2 最佳左前缀 3.3 索引计算 3.4 索引范围:索引列上不能有范围查询 3.5 索引覆盖:尽量使用覆盖索引 3.6 不等: 使用不等于(!= 或者 <>)的时候 3.7 null:字段的is not null 与is null 3.8 like:like的前后模糊

  • MongoDB中哪几种情况下的索引选择策略

    MongoDB中哪几种情况下的索引选择策略

    目录 一.MongoDB如何选择索引 二.数据准备 三.正则对index的使用 四.$or从句对索引的利用 五.sort对索引的利用 六.搜索数据对索引命中的影响 总结 一.MongoDB如何选择索引 如果我们在Collection建了5个index,那么当我们查询的时候,MongoDB会根据查询语句的筛选条件.sort排序等来定位可以使用的index作为候选索引:然后MongoDB会创建对应数量的查询计划,并分别使用不同线程执行查询计划,最终会选择一个执行最快的index:但是这个选择也不是一

  • Mysql普通索引与唯一索引的选择详析

    Mysql普通索引与唯一索引的选择详析

    假设一个用户管理系统,每个人注册都有一个唯一的手机号,而且业务代码已经保证了不会写入两个重复的手机号.如果用户管理系统需要按照手机号查姓名,就会执行类似这样的 SQL 语句: select name from users where mobile = '15202124529'; 通常会考虑在 mobile 字段上建索引.由于手机号字段相对较大,通常基本不会把手机号当做主键,那么现在就有两个选择: 1.  给 id_card 字段创建唯一索引 2.  创建一个普通索引 如果业务代码已经保证了不会

  • Mysql中tinyint(1)和tinyint(4)的区别详析

    Mysql中tinyint(1)和tinyint(4)的区别详析

    目录 1.varchar(M)和数值类型tinyint(M) 的区别 2测试 总结 1. varchar(M)和数值类型tinyint(M) 的区别 字符串类型:varchar(M)而言,M是字段中可以存储的最大字符串,也就是说字段长度.根据设置,当你插入的数值超过字段设置的长度时,很有可能会收到错误提示,如果没有收到提示,插入的数据也有可能被自动的截断以适应该字段的预定义长度.所有像varchar(5)表示其存储的字符串长度不能超过5. 数值列类型:其长度修饰符表示最大宽度,与该字段物理存储没

  • Mysql两种情况下更新字段中部分数据的方法

    Mysql更新字段中部分数据第一种情况:  复制代码 代码如下: update tab set A = concat(substring(A,1,3),'bbb'); 从A的1个字符开始取3个字符,加上'bbb',再写入a中,如果A原始值为'123aaa',那么更新之后为'123bbb'了. 如果我们要将字段A值中的包含的1,替换成2呢? 例如:a=2211,现在要替换成2222,就是把1换成2 SQl语句这么写: 复制代码 代码如下: update table set a=REPLACE(a,

  • MySQL中的回表和索引覆盖示例详解

    MySQL中的回表和索引覆盖示例详解

    目录 索引类型 索引结构 非聚簇索引查询 索引覆盖 总结 索引类型 聚簇索引: 叶子节点存储的是行记录,每个表必须要有至少一个聚簇索引.使用聚簇索引查询会很快,因为可以直接定位到行记录 普通索引:二级索引,除聚簇索引外的索引,即非聚簇索引.普通索引叶子节点存储的是主键(聚簇索引)的值. 聚簇索引递推规则: 如果表设置了主键,则主键就是聚簇索引 如果表没有主键,则会默认第一个NOT NULL,且唯一(UNIQUE)的列作为聚簇索引 以上都没有,则会默认创建一个隐藏的row_id作为聚簇索引 索引结

  • MySQL慢查日志的开启方式与存储格式详析

    MySQL慢查日志的开启方式与存储格式详析

    在开发项目中,我们可以通过MySQL的慢查询日志对有效率问题的SQL进行监控. 下面一些命令是开启日志: 查找慢查询日志文件的目录 show variables like 'slow_query_log' 设置慢日志文件目录文件 set global slow_query_log_file='home/mysql/sql_log/mysql_slow.log' 是否把没有使用索引的sql记录在日志中 set global log_queries_not_using_indexes=on 设置超过

随机推荐