Mysql中明明创建了a字段的索引_数据库

当前位置：网站建设 > 技术支持

Mysql中明明创建了a字段的索引_数据库

资料来源：网络整理时间：2023/3/5 17:05:06 共计：3562 浏览

Mysql中明明创建了a字段的索引？

在MySQL数据库中，的确存在一些场景会导致存储引擎放弃使用索引而进行全表扫描，下面我们将从索引失效的原因以及如何避免索引失效两个方面回答这个问题，希望对您有所帮助。

MySQL中一条查询SQL是如何被执行的？

如上图，我们可以看到一条Mysql查询语句，从被客户端下发到调用存储引擎读取数据，返回结果，经历了连接器、分析器、优化器、执行器。我们以下面SQL为例，简单说明下在各个环节中分别做了哪些事情。

如上SQL，实现了查询Score表中学号为 9527 同学的Math(数学)成绩，下面我们分析下这个语句的执行流程：

step1 连接器：首先会检查该该查询SQL语句是否有权限，如果无权限，则直接返回错误信息，如果有权限，在MySQL 8.0 版本之前，会先查询缓存，以这条SQL语句作为KEY在内存中进行查询，如果有结果则直接将历史查询结果返回，如果没有，执行下一步。

step2 分析器：在分析器中会进行词法分析与语法分析，通过分析器词法分析，提取SQL语句中关键字，比如，提取SQL语句中的SELECT、WHERE，提取查询的表名是 CourseInfo，提取查询的字段是StudentId、Score，提取查询条件是 Course 等于 'Math' 且 StudentId 等于 9527。

然后再通过再语法分析判断在该SQL 语句是否有语法错误，比如，关键词是否正确、StudentId、Score字段是否存在于CourseInfo表中等等，若检查通过，则继续执行下一步。

step3 优化器：优化器会通过自己的分析算法确认执行方案，上面的SQL语句，有两种执行方案，如下：

方案一：首先，查询课程是Math的所有学生的成绩。然后，再查询其学号是9527的成绩。方案二：首先，查询学号是9527的所有科目的成绩。然后，再查询其科目是Math的成绩。

因此，优化器会根据它的优化算法分析它所认为执行效率最高的一个方案（优化器认为不一定是最好。同时如果如优化器分析使用索引扫描比全表扫描效率低时，会放弃使用索引而选择全表扫描，一般数据量极少时，可能不会走索引）。

step4 执行器：首先，进行权限校验，如果没有权限则会返回错误信息，如果有权限就会调用数据库存储引擎的查询接口，返回引擎的查询结果。

放弃使用索引而选择全表扫描

除了上面提到的当优化器分析使用索引扫描比全表扫描效率低时，优化器会放弃使用索引而选择全表扫描，还有哪些原因会导致放弃索引而选择全表扫描呢？

因索引失效，导致全表扫描的可能原因有以下几点：

WHERE子句中对索引列进行计算、函数、类型转换等操作。WHERE子句中对索引列使用不等于，如!= 或者 <>。WHERE子句中对索引列使用 IS NULL ，IS NOT NULL。WHERE子句中对索引列使用模糊查询LIKE并以通配符开头如，%ab。WHERE子句中对索引列使用OR来连接条件。WHERE子句中对索引列使用IN和NOT IN 。WHERE子句中对索引列使用隐式类型转换，如字段 age类型为 int，WHERE条件中却使用varchar类型，如，age= '17'。复合索引未遵循最佳左前缀原则或者存在断点。索引被禁用，开启索使用 ALTER TABLE TESTOPS ENABLE KEYS 。如何避免索引失效

避免在 WHERE 子句中使用 != 或 <> 不等于操作符

在 WHERE 子句中使用 != 或者 <> 操作符，将导致引擎放弃使用索引而进行全表扫描。MySQL 仅有对以下操作符才会使用索引：<，>，<=，>=，=，BETWEEN，IN，以及使用LIKE 时的后缀模糊查询 % 。

避免在WHERE 子句中对索引列使用%前缀模糊查询

WHERE 子句中使用 %前缀模糊查询，将导致引擎放弃使用索引而进行全表扫描。解决 %前缀模糊查询时索引失效的方法是添加覆盖索引（仅访问索引的查询，查询列都是索引，只需扫描索引而无须回表）。

避免在WHERE 子句中对索引列使用 OR 来连接条件

在 WHERE 子句中使用 OR 来连接条件，将导致引擎放弃使用索引而进行全表扫描。使用 OR 的字句可以分解为多个查询，并且通过 UNION 连接多个查询的结果。他们的速度只同是否使用索引有关，若查询需要时能够用到复合索引，使用 UNION ALL 执行的效率更高。

我们在实际SQL设计时尽量UNION ALL 代替 UNION ，UNION 和 UNION ALL 的区别主要是UNION 需要将结果集合并后并进行唯一性过滤操作，涉及到排序，产生大量的CPU运算，增加资源消耗及延迟。当然，使用UNION ALL的前提条件是两个结果集没有重复数据，或对是否存在重复数据无要求。

避免在WHERE 子句中对索引列使用 IN 和 NOT IN

在 WHERE 子句中使用 IN 和 NOT IN ，将导致引擎放弃使用索引而进行全表扫描。在SQL设计时对于连续的数值，可以使用 BETWEEN…AND… 尽量避免使用 IN。除此之外，一般可使用 EXISTS 代替 IN 。若需要使用 IN，在 IN 后面值的列表中，应按照值的分布数量降序排列，以减少判断的次数。

尝试使用BETWEEN AND 替换 IN，示例如下。

我们使用 EXISTS 来替代 IN，用 NOT EXISTS 来替代 NOT IN，无论哪种情况 NOT IN效率都是最低的。

除此之外，我们可以尝试使用LEFT JOIN 替换 IN。

避免在WHERE 子句中对索引列使用计算、函数、类型转换等操作

在 WHERE 子句中对 “=” 左边的字段进行函数、算术运算及其他表达式运算，将导致引擎放弃使用索引而进行全表扫描，可以将表达式运算移至“=”右边。

避免在WHERE 子句中对索引列进行 NULL 值判断

在 WHERE 子句中对字段进行 NULL 值判断，将导致引擎放弃使用索引而进行全表扫描。创建表时 NULL 是默认值，但大多数时候应该使用 NOT NULL，或者使用一个默认值，如使用0 作为默认值。

例如，性别字段，使用1表示男，2表示女，0表示未知，或者是当用户没有选择，默认值设置为0（大部分编程语言的数字类型的默认值0）。

如果字段允许为空，可能会有以下问题：查询条件中必须处理为空的情况，否则将会出现一些很奇怪的问题，比如 NOT IN、!= 等负向条件查询在有 NULL 值的时候返回永远为空结果，查询容易易出错。在部分数据库中将导致索引失效。可空列需要更多存储空间，导致空间变大。

凡事没有绝对的，使用默认值的思路一定程度可以解决很大一部分可为空的问题，但不是所有的都需这样做，具体还是需要根据具体业务进行分析。

避免在WHERE 子句中对索引列进行隐式类型转换

WHERE 子句中对索引列进行隐式类型转换（条件中字段赋值与字段定义类型不匹配），将导致引擎放弃使用索引而进行全表扫描。当我们对不同类型的值进行比较的时候，为了使得这些数值可比较，MySQL数据库会做一些隐式转化（Implicit type conversion）。

SQL查询语句的条件中字段赋值与字段定义类型不匹配是一种常见的错误用法。

如上，字段 account 字段的定义为 varchar 类型，而在 WHERE 条件中 account 字段值是数字型，数据类型不匹配，此时是没法直接进行比较的，需要进行类型转换。MySQL的策略是将表中 account 字段全部转换为数字型之后再比较，因此引发函数作用于字段，使得索引失效，导致全表扫描，正确的写法如下：

版权说明：
本网站凡注明“广州京杭原创”的皆为本站原创文章，如需转载请注明出处！
本网转载皆注明出处，遵循行业规范，如发现作品内容版权或其它问题的，请与我们联系处理！
欢迎扫描右侧微信二维码与我们联系。

·上一条：mysql查询所有季度,有哪些数据可视化做的比较好的公司_数据库 | ·下一条：mysql合并数据,最好的软件开发工具是什么_数据库