mysql不走索引

合集下载

mysql索引失效的常见原因和如何用好索引

mysql索引失效的常见原因和如何⽤好索引本篇主要介绍的是索引失效的常见原因和如何⽤好索引，跟上⼀篇正好承上启下，给有需要的朋友⼀个参考。

本⽂将从以下⼏个⽅便进⾏讲解：1.索引失效常见原因：2.索引失效常见误区：3.索引设计的⼏个建议：准备⼯作查看当前 mysql 的版本：select VERSION();查出当前版本为：8.0.21创建⼀张表 test1CREATE TABLE `test1` (`id` bigint NOT NULL,`code` varchar(30) NOT NULL,`age` int NOT NULL,`name` varchar(30) NOT NULL,`height` int NOT NULL,PRIMARY KEY (`id`),KEY `idx_code_age_name` (`code`,`age`,`name`) USING BTREE,KEY `idx_height` (`height`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8插⼊两条数据：INSERT INTO `test1`(`id`, `code`, `name`, `age`,`address`) VALUES (1, '001', '张飞', 18,'7');INSERT INTO `test1`(`id`, `code`, `name`, `age`,`address`) VALUES (2, '002', '关⽻', 19,'8');查询⼀下：select * from test1;结果：此外建⽴了两个索引：idx_code_age_name( code , age , name )联合索引和 idx_height(height )普通索引。

MySQL中索引失效的原因与解决方案

MySQL中索引失效的原因与解决方案简介MySQL是一种常用的开源关系型数据库管理系统，具有高性能、可扩展性和稳定性等优点。

在使用MySQL时，对数据的查询性能常常是用户关注的核心问题之一。

索引是提高查询效率的重要手段，但有时候会出现索引失效的情况。

本文将探讨MySQL中索引失效的原因及相应的解决方案。

一、索引失效的原因1. 不合适的数据类型在MySQL中，选择合适的数据类型对索引的有效性有很大影响。

如果在索引列上使用了不适合的数据类型，将导致索引失效。

例如，将字符串类型存储为了索引列，而同时进行了数值比较，将导致索引无效。

2. 函数或运算符的使用当在查询条件中使用了函数或运算符时，MySQL无法利用索引进行优化，从而导致索引失效。

例如，在WHERE子句中使用了字符串函数，如CONCAT和SUBSTRING，或者使用了LIKE操作符，MySQL将无法使用索引进行快速查找。

3. 未使用索引列如果在查询中未使用索引列，MySQL将无法利用索引进行优化。

有时候，由于设计不当或者编码错误，可能在查询中使用了其他列进行过滤，而未使用索引列，从而导致索引失效。

4. 数据库统计信息不准确MySQL使用统计信息来预估查询计划的代价，从而选择最优的执行方案。

如果统计信息不准确，将导致MySQL选择了不合适的执行计划，从而导致索引失效。

常见的原因包括统计信息过时、没有收集统计信息或统计信息不全面等。

5. 索引选择不当MySQL支持多种类型的索引，如B-tree索引、哈希索引等。

在索引选择中，需要考虑到数据分布的特点和查询的需求，选择合适的索引类型。

如果选择不当，将导致索引失效。

二、解决方案1. 选择合适的数据类型在创建表时，需要选择合适的数据类型。

对于需要进行数值比较的列，应选择数值类型而不是字符串类型。

这样可以确保索引在比较时有效。

2. 避免使用函数或运算符尽量不在查询条件中使用函数或运算符。

如果确实需要使用函数，可以考虑对查询条件进行重写，以便让MySQL能够使用索引进行优化。

MySQL数据库优化中常见的性能瓶颈与解决方法

MySQL数据库优化中常见的性能瓶颈与解决方法概述：在开发应用程序过程中，MySQL数据库是常见的使用的关系型数据库管理系统。

然而，在使用MySQL过程中，我们可能会遇到一些性能瓶颈，导致数据库查询速度变慢，响应时间增加。

本文将介绍一些常见的MySQL性能瓶颈，并提供相应的解决方法，帮助开发者在优化数据库性能方面取得更好的成果。

1. 索引失效索引是MySQL中用于优化查询速度的重要工具。

当索引失效时，查询语句需要扫描整个表，导致查询变慢。

常见的索引失效原因包括：- 查询条件中使用了函数，导致无法使用索引。

- 数据表中的字段类型不匹配，导致无法使用索引。

- 数据表中没有为经常查询的字段添加索引。

解决方法：- 避免在查询条件中使用函数，尽量使用原始字段。

- 确保数据表中的字段和索引的数据类型匹配。

- 根据查询频率添加索引，使用EXPLAIN语句分析查询计划，选择合适的索引策略。

2. 慢查询语句慢查询通常指耗时较长的查询语句。

慢查询语句一旦出现，会严重影响数据库性能。

常见的慢查询原因包括：- 查询语句中涉及大量的JOIN操作。

- 数据表中缺少必要的索引。

- 数据表中存在大量冗余数据。

解决方法：- 尽量避免使用复杂的JOIN语句，考虑调整数据表结构，尽量减少JOIN操作。

- 分析慢查询日志，确定慢查询语句，并为其添加合适的索引。

- 定期清理冗余数据，保持数据表的整洁。

3. 内存和磁盘IO内存和磁盘IO是MySQL性能瓶颈的另外两个重要因素。

当MySQL需要大量的内存和磁盘IO时，性能会受到极大影响。

常见的原因包括：- 数据库缓冲区设置过小，无法容纳常用的数据块。

- 存储引擎性能不佳。

解决方法：- 调整MySQL的配置文件，增加缓冲区的大小，提高数据块的访问速度。

- 根据实际需求选择合适的存储引擎，如InnoDB或MyISAM，权衡性能和适用场景。

- 避免频繁的磁盘IO操作，尽量将部分数据加载到内存中进行处理。

如何解决MySQL索引失效或无法使用的问题

如何解决MySQL索引失效或无法使用的问题MySQL索引是提高查询性能的重要手段之一，但在实际使用中，有时会遇到索引失效或无法使用的问题。

本文将探讨如何解决这些问题，提高MySQL数据库的查询效率。

一、索引失效的原因及解决方案1. 数据类型不一致导致索引失效在使用索引时，如果查询条件与索引字段的数据类型不一致，会导致索引失效。

例如，索引字段为整型，但查询条件传入的是字符型，这样就无法使用索引，而是进行全表扫描。

解决方案：保持查询条件与索引字段的数据类型一致，及时进行数据类型转换，确保能够使用索引。

2. 索引列上使用了函数在查询语句中，如果在索引列上使用函数，也会导致索引失效。

因为函数的运算会导致索引无法直接匹配，从而无法使用索引。

解决方案：尽量避免在索引列上使用函数，可以考虑将函数运算的结果存储在一个字段中，并在查询时使用该字段进行匹配。

3. 索引列上使用了LIKE查询在使用LIKE查询时，如果查询条件的模式以通配符开头（例如：%abc），那么索引也会失效。

因为MySQL的索引是基于B-Tree实现的，默认只能从左边开始匹配。

解决方案：如果需要使用LIKE查询，可以考虑将查询条件进行改写，避免通配符开头的模式，或者使用全文索引来解决。

4. 索引列上使用了OR连接条件在查询语句中，如果索引列上使用了OR连接条件，也可能导致索引失效。

因为OR连接条件会导致MySQL无法选择一个最优的索引进行匹配。

解决方案：将OR连接条件拆分为多个单个条件，使用UNION或者JOIN进行连接，以便MySQL能够更好地选择索引进行查询。

二、无法使用索引的常见情况及解决方案1. 对索引列进行了函数计算在查询语句中，如果对索引列进行了函数计算，MySQL也无法使用索引。

解决方案：将函数计算的逻辑移至查询语句的外部，或者使用虚拟列来存储计算结果，以便使用索引进行查询。

2. 对索引列进行了类型转换在查询语句中，如果对索引列进行了类型转换，MySQL也无法使用索引。

mysql索引失效的十大问题小结

mysql索引失效的⼗⼤问题⼩结⽬录背景⼀、查询条件包含or，可能导致索引失效⼆、如何字段类型是字符串，where时⼀定⽤引号括起来，否则索引失效三、like通配符可能导致索引失效。

四、联合索引，查询时的条件列不是联合索引中的第⼀个列，索引失效。

五、在索引列上使⽤mysql的内置函数，索引失效。

六、对索引列运算（如，+、-、*、/），索引失效。

七、索引字段上使⽤（！= 或者 < >，not in）时，可能会导致索引失效。

⼋、索引字段上使⽤is null， is not null，可能导致索引失效。

九、左连接查询或者右连接查询查询关联的字段编码格式不⼀样，可能导致索引失效。

⼗、mysql估计使⽤全表扫描要⽐使⽤索引快,则不使⽤索引。

总结背景最近⽣产爆出⼀条慢sql，原因是⽤了or和!=，导致索引失效。

于是，总结了索引失效的⼗⼤杂症，希望对⼤家有帮助，加油。

⼀、查询条件包含or，可能导致索引失效新建⼀个user表，它有⼀个普通索引userId，结构如下：CREATE TABLE `user` (`id` int(11) NOT NULL AUTO_INCREMENT,`userId` int(11) NOT NULL,`age` int(11) NOT NULL,`name` varchar(255) NOT NULL,PRIMARY KEY (`id`),KEY `idx_userId` (`userId`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;执⾏⼀条查询sql，它是会⾛索引的，如下图所⽰：把or条件+没有索引的age加上，并不会⾛索引，如图：分析&结论：对于or+没有索引的age这种情况，假设它⾛了userId的索引，但是⾛到age查询条件时，它还得全表扫描，也就是需要三步过程：全表扫描+索引扫描+合并如果它⼀开始就⾛全表扫描，直接⼀遍扫描就完事。

mysql中走与不走索引的情况汇集（待全量实验）

mysql中⾛与不⾛索引的情况汇集（待全量实验）说明在MySQL中，并不是你建⽴了索引，并且你在SQL中使⽤到了该列，MySQL就肯定会使⽤到那些索引的，有⼀些情况很可能在你不知不觉中，你就“成功的避开了”MySQL的所有索引。

索引列参与计算如果where条件中age列中使⽤了计算，则不会使⽤该索引。

如果需要计算，千万不要计算到索引列，想⽅设法让其计算到表达式的另⼀边去。

SELECT `sname` FROM `t_stu` WHERE `age`=20; -- 会使⽤索引SELECT `sname` FROM `t_stu` WHERE `age`+10=30; -- 不会使⽤索引！！因为所有索引列参与了计算SELECT `sname` FROM `t_stu` WHERE `age`=30-10; -- 会使⽤索引索引列使⽤了函数同样的道理，索引列使⽤了函数，⼀样会导致相同的后果SELECT `sname` FROM `stu` WHERE concat(`sname`,'abc') ='Jaskeyabc'; -- 不会使⽤索引,因为使⽤了函数运算,原理与上⾯相同SELECT `sname` FROM `stu` WHERE `sname`=concat('Jaskey','abc'); -- 会使⽤索引索引列使⽤了Like %XXXSELECT * FROM `houdunwang` WHERE `uname` LIKE '前缀%' -- ⾛索引SELECT * FROM `houdunwang` WHERE `uname` LIKE '%后缀' -- 扫描全表，不⾛索引所以当需要搜索email列中.com结尾的字符串⽽email上希望⾛索引时候,可以考虑数据库存储⼀个反向的内容reverse_emailSELECT * FROM `table` WHERE `reverse_email` LIKE REVERSE('%.com'); -- ⾛索引注：以上如果你使⽤REVERSE(email) = REVERSE(’%.com’)，⼀样得不到你想要的结果，因为你在索引列email列上使⽤了函数，MySQL不会使⽤该列索引同样的，索引列上使⽤正则表达式也不会⾛索引。

mysql索引失效原理

mysql索引失效原理MySQL索引是提高查询效率的重要手段。

但是有时候我们会遇到MySQL索引失效的情况，导致查询效率降低，甚至导致整个系统的崩溃。

那么，MySQL索引失效的原理是什么呢？首先，我们需要了解一下MySQL索引的工作原理。

MySQL索引是建立在表上的，它通过对某个或某些字段进行排序后建立一个索引表，用于快速访问这些字段。

当我们执行查询语句时，MySQL会优先检索索引表，找到符合条件的记录，再返回给我们查询结果。

那么，为什么会出现MySQL索引失效的情况呢？原因有多种，下面列举几个常见的原因。

1. 索引列没有经过SELECT中使用，导致索引失效。

例如：我们有一个students表，其中有id、name、age、gender 四个字段，我们希望查询年龄小于18岁的学生。

如果我们只在name 字段上建立了索引，那么执行以下语句时，查询将不会命中索引，导致索引失效。

SELECT * FROM students WHERE age < 18;2. 索引列进行了类型转换，导致索引失效。

例如：我们有一个students表，其中有id、name、age、gender 四个字段，我们希望查询年龄为18岁的学生。

如果我们在age字段上建立了整型索引，那么执行以下语句时，查询将不会命中索引，导致索引失效。

SELECT * FROM students WHERE age = '18';3. 索引字段的值过于分散，导致索引失效。

例如：我们有一个students表，其中有id、name、age、gender 四个字段，我们希望查询姓名为‘Tom’的学生。

如果我们在name 字段上建立了索引，但是‘Tom’这个值占据的比例较小，那么查询时会很可能不命中索引，导致索引失效。

SELECT * FROM students WHERE name = 'Tom';4. 索引列中使用了函数，导致索引失效。

MySQL中使用IN查询到底走不走索引

总结 IN通常是走索引的，当IN后面的数据在数据表中超过30%的匹配时是全表扫描，不走索引，因此IN走不走索引和后面的数据量有关系。
二、看IN后面是不是有子查询
EXPLAIN SELECT * from users WHERE is_doctor in (SELECT is_doctor from test_in_subselect);
又走索引了，所以IN查询走不走索引需要看rows的数据量，in (0,1)时查询出52万多数据量，全表才54万数据量，in (2,1)时只有1万多数据量。 EXPLAIN SELECT * from users WHERE (is_doctor=0 or is_doctor=1); 与 EXPLAIN SELECT * from users WHERE (is_doctor=2 or is_doctor=1); 也是同样的道理
博客园用户登录代码改变世界密码登录短信登录忘记登录用户名忘记密码记住我登录第三方登录/注册没有账户, 立即注册
MySQL中使用 IN查询到LAIN SELECT * from users WHERE is_doctor in (0,1);
很明显没走索引，下面再看一个sql。 EXPLAIN SELECT * from users WHERE is_doctor in (2,1);

mysql索引列为Null的走不走索引及null在统计时的问题

mysql索引列为Null的⾛不⾛索引及null在统计时的问题要尽可能地把字段定义为 NOT NULL，即使应⽤程序⽆须保存 NULL（没有值），也有许多表包含了可空列（Nullable Column）这仅仅是因为它为默认选项。

除⾮真的要保存 NULL，否则就把列定义为 NOT NULLMySQL难以优化引⽤了可空列的查询,它会使索引、索引统计和值更加复杂。

可空列需要更多的储存空间，还需要在MySQL内部进⾏特殊处理。

当可空列被索引的时候，每条记录都需要⼀个额外的字节，还可能导致 MyISAM 中固定⼤⼩的索引(例如⼀个整数列上的索引)变成可变⼤⼩的索引。

即使要在表中储存「没有值」的字段，还是有可能不使⽤ NULL 的，考虑使⽤ 0、特殊值或空字符串来代替它。

把 NULL 列改为 NOT NULL 带来的性能提升很⼩，所以除⾮确定它引⼊了问题，否则就不要把它当作优先的优化措施。

然后，如果计划对列进⾏索引，就要尽量避免把它设置为可空,虽然在mysql⾥ Null值的列也是⾛索引的mysql>SELECT1IS NULL, 1IS NOT NULL;+-----------+---------------+|1IS NULL|1IS NOT NULL|+-----------+---------------+|0|1|+-----------+---------------+1 row in setmysql>SELECT0IS NULL, 0IS NOT NULL, ''IS NULL, ''IS NOT NULL;+-----------+---------------+------------+----------------+|0IS NULL|0IS NOT NULL|''IS NULL|''IS NOT NULL|+-----------+---------------+------------+----------------+|0|1|0|1|+-----------+---------------+------------+----------------+测试CREATE TABLE `test_null` (`id` int(11) DEFAULT NULL,`mark` varchar(20) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;create procedure test_null(in num int)BEGINDECLARE i int;set i=1;while (i<num)DOif mod(i,10)!=0theninsert into test_null values (i,concat('aaa',i));elseinsert into test_null values (null,concat('aaa',i));end if;set i=i+1;END while;END;call test_null(10000);mysql>select count(*) from test_null;+----------+|count(*) |+----------+|9999|+----------+没加任何索引时mysql> explain SELECT*from test_null WHERE id is null;+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------------+| id | select_type |table| partitions | type | possible_keys |key| key_len | ref | rows | filtered | Extra |+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------------+|1| SIMPLE | test_null |NULL|ALL|NULL|NULL|NULL|NULL|10105|10| Using where|+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------------+在id上加普通索引create index idx_test_null on test_null(id);mysql> explain SELECT*from test_null WHERE id is null;+----+-------------+-----------+------------+------+---------------+---------------+---------+-------+------+----------+-----------------------+| id | select_type |table| partitions | type | possible_keys |key| key_len | ref | rows | filtered | Extra |+----+-------------+-----------+------------+------+---------------+---------------+---------+-------+------+----------+-----------------------+|1| SIMPLE | test_null |NULL| ref | idx_test_null | idx_test_null |5| const |999|100| Using index condition |+----+-------------+-----------+------------+------+---------------+---------------+---------+-------+------+----------+-----------------------+null值也是⾛索引的null在count统计时时的问题create table test (id int,val int);INSERT INTO `test` VALUES ('1', '11');INSERT INTO `test` VALUES ('1', '111');INSERT INTO `test` VALUES ('2', '2');INSERT INTO `test` VALUES ('2', '22');INSERT INTO `test` VALUES ('2', '222');1条语句统计id=1,id=2的个数⼀般错误写法select count(id=1) ,count(id=2) from test;mysql>select count(id=1) ,count(id=2) from test;+-------------+-------------+|count(id=1) |count(id=2) |+-------------+-------------+|5|5|+-------------+-------------+需要注意count只不会统计null的列，0的会统计mysql>select1or null as or1,1and null as and1 ,0and null as and0 ,0or null as null0; +------+------+------+-------+| or1 | and1 | and0 | null0 |+------+------+------+-------+|1|NULL|0|NULL|+------+------+------+-------+mysql>select id=1 ,id=2from test;+------+------+| id=1| id=2|+------+------+|1|0||1|0||0|1||0|1||0|1|+------+------+要把0值变为null,count时不计算即可mysql>select count(id=1) ,count(id=2) from test;+-------------+-------------+|count(id=1) |count(id=2) |+-------------+-------------+|5|5|+-------------+-------------+mysql>select id=1or null,id=2or null from test;+--------------+--------------+| id=1or null| id=2or null|+--------------+--------------+|1|NULL||1|NULL||NULL|1||NULL|1||NULL|1|+--------------+--------------+所以正确的写法是mysql>select count(id=1or null),count(id=2or null) from test;+---------------------+---------------------+|count(id=1or null) |count(id=2or null) |+---------------------+---------------------+|2|3|+---------------------+---------------------+1 row in set (0.00 sec)select id,count(id) from test where id in(1,2) GROUP BY id常数与null的运算DROP TABLE IF EXISTS `test1`;CREATE TABLE `test1` (`id` int(11) DEFAULT NULL,`a` int(11) DEFAULT NULL,`b` int(11) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;-- ------------------------------ Records of test1-- ----------------------------INSERT INTO `test1` VALUES ('1', '5', '1');INSERT INTO `test1` VALUES ('2', '6', null);INSERT INTO `test1` VALUES ('3', '4', '2');INSERT INTO `test1` VALUES ('4', '7', null);INSERT INTO `test1` VALUES ('5', null, null);查询 id,a-b的数量(剩余计算)错误写法mysql>SELECT id ,(a-b) as remain from test1;+------+--------+| id | remain |+------+--------+|1|4||2|NULL||3|2||4|NULL||5|NULL|+------+--------+正确写法mysql>SELECT id ,(IFNULL(a,0)-IFNULL(b,0)) as remain from test1; +------+--------+| id | remain |+------+--------+|1|4||2|6||3|2||4|7||5|0|+------+--------+。

MySQL中的索引失效原因与解决方案

MySQL中的索引失效原因与解决方案MySQL是一个开源的关系型数据库管理系统，被广泛应用于互联网应用和大型企业中。

在MySQL中，索引是提高查询性能和加快数据检索速度的重要机制。

然而，有时候我们会遇到索引失效的情况，导致查询效率低下。

本文将探讨MySQL中索引失效的原因以及相应的解决方案。

一、索引失效的原因1. 不合理的查询条件索引的作用是根据查询条件快速定位到符合条件的数据块，而如果查询条件不合理，将导致索引失效。

举个例子，假设有一张用户表，包含了"id"和"name"字段，并且"id"字段是主键，有唯一索引。

如果我们执行以下查询语句：```SELECT * FROM users WHERE name LIKE '%张三%';```这个查询将无法使用索引，因为LIKE通配符开头的模式匹配无法使用BTREE 索引。

2. 数据类型不匹配在MySQL中，如果查询条件与索引字段的数据类型不一致，将导致索引失效。

举个例子，假设有一张订单表，包含了"order_id"和"create_time"字段，并且"order_id"字段是主键，有唯一索引。

如果我们执行以下查询语句：```SELECT * FROM orders WHERE order_id = '12345';```这个查询将无法使用索引，因为查询条件中的数据类型为字符串，而索引字段的数据类型为整数。

3. 使用过多的索引虽然索引可以提高查询性能，但是过多的索引也会导致查询性能下降。

因为每次更新数据时，除了更新数据本身，还需要更新对应的索引。

如果有过多的索引，将增加更新数据的开销。

因此，在设计数据库时，需要根据实际情况选择合适的索引，避免使用过多的索引。

4. 数据分布不均匀如果数据分布不均匀，即某些索引值对应的数据量非常大，而某些索引值对应的数据量非常小，将导致索引失效。

MySQL索引失效的原因和解决方法

MySQL索引失效的原因和解决方法MySQL索引是提高查询效率的重要工具，然而在实际应用中，我们经常会遇到MySQL索引失效的情况。

本文将探讨MySQL索引失效的原因和解决方法，希望能帮助读者更好地理解和应对这一问题。

一、索引失效的原因1. 非选择性索引索引的选择性是指索引中不同值的个数与总记录数的比值。

如果索引的选择性很低，即索引中不同值的个数很少，那么查询时使用该索引很可能导致全表扫描，从而失去了使用索引的意义。

2. 使用函数或表达式作为索引列当我们在查询条件中使用函数或表达式来过滤数据时，MySQL无法使用索引进行优化。

因此，在设计表结构和查询语句时，应尽量避免使用函数或表达式作为索引列。

3. 索引列数据类型不匹配索引列的数据类型应与查询条件中的数据类型保持一致，否则MySQL无法正确匹配索引，导致索引失效。

因此，在创建索引时，要仔细选择合适的数据类型，并保持与查询条件的一致性。

4. 索引列上使用了函数当我们在查询条件中对索引列使用函数时，MySQL无法使用该索引进行优化。

因此，在查询语句中尽量避免对索引列使用函数，或者考虑使用其它方式进行优化。

二、索引失效的解决方法1. 优化查询语句当索引失效时，首先要检查并优化查询语句。

可以通过使用EXPLAIN命令来分析查询语句的执行计划，查看是否使用了索引，以及索引的选择性等信息。

根据分析结果，对查询语句进行重写或优化，以提高索引的使用效率。

2. 添加合适的索引如果索引失效是由于缺乏合适的索引所致，可以通过添加合适的索引来解决。

我们可以根据查询条件和频繁用于过滤的列来创建索引。

同时，要考虑索引的选择性，避免创建非选择性索引。

3. 避免使用函数或表达式在设计表结构和查询语句时，应尽量避免使用函数或表达式作为索引列。

如果确实需要使用函数或表达式来过滤数据，可以考虑对列进行预处理，将结果存储在新的列中，并创建相应的索引。

4. 使用覆盖索引覆盖索引是一种特殊的索引类型，它包含了查询所需的所有数据，无需再次回表读取数据。

记一次Mysql索引失效原因分析

记⼀次Mysql索引失效原因分析前⾔此⽂章包含Mysql的Where条件查询执⾏过程、范围查询使联合索引停⽌匹配、回表操作分析、常见索引失效场景、Extra分析等知识。

背景6千万数据量的数据表出现了⼀个满查询，复现sql语句发现查询并没有⾛索引⽽是⾛全表查询，找出索引失效原因。

# sql语句EXPLAIN SELECT count(*) FROM order_recipient_extend_tab WHERE start_date>'1628442000' and start_date<'1631120399' and station_id='1809' and status='2';order_recipient_extend_tab 表有6千万数据，慢查询的查询字段包括 start_date、station_id、status，按照索引设计初衷会⾛但实际上失效的索引是：联合索引字段1字段2字段3idx_date_station_driver start_date station_id driver_idWhere条件查询执⾏过程了解Mysql怎么执⾏where条件查询，能更快速清晰地洞见索引失效的原因。

此次慢查询中匹配度⾼的索引是idx_date_station_driver，分析此次慢查询中where条件查询的执⾏过程。

Mysql对where条件提取规则主要可以归纳为三⼤类：Index Key (First Key & Last Key)，Index Filter，Table Filter。

Index KeyIndex Key⽤于确定此次sql查询在索引树上的范围。

⼀个范围包括起始和终⽌，Index First Key ⽤于定位索引查询的起始范围，Index Last Key⽤于定位索引查询的终⽌范围。

Index First Key 提取规则：从索引的第⼀个字段开始，检查该字段在where条件中是否存在，若存在且条件是=、>=，则将对应的条件加⼊Index First Key之中，继续读取索引的下⼀个字段；若存在且条件是>，则将对应的条件加⼊Index First Key中，然后终⽌Index First Key的提取；若不存在，也终⽌Index First Key的提取。

MySQL数据库中的索引失效原因分析和解决

MySQL数据库中的索引失效原因分析和解决在使用MySQL数据库时，索引是提高查询效率的重要组成部分。

然而，有时候我们会发现索引并没有起到应有的作用，查询速度反而变得很慢。

那么，索引失效的原因是什么？如何解决这个问题呢？一、索引失效的原因分析1. 条件表达式中使用了函数当我们在查询条件中使用了函数时，索引就会失效。

例如，下面的查询语句中使用了函数YEAR()：SELECT * FROM table WHERE YEAR(date_column) = 2022;在这种情况下，即使date_column上有索引，MySQL也无法有效利用它，导致索引失效。

2. 条件表达式使用了"不等于"操作符在条件表达式中使用不等于(<>, !=)操作符时，索引也会失效。

因为这种情况下MySQL无法确定应该使用哪个索引。

3. 列类型不匹配如果查询条件中的列与索引列的类型不匹配，索引也会失效。

例如，索引列是字符串类型，但在查询时使用了数值类型，MySQL会放弃使用索引。

4. 使用LIKE操作符进行模糊匹配当我们使用LIKE操作符进行模糊匹配时，如果通配符在开头（如LIKE'%keyword'），索引也会失效。

因为MySQL无法利用索引来快速定位符合条件的记录。

5. 数据量过小如果表中的数据量非常小，即使使用了索引，其实对于查询性能的提升并不明显。

在这种情况下，索引可能会被认为是无效的。

二、索引失效的解决方法1. 避免在索引列上使用函数我们在使用索引时应尽量避免在索引列上使用函数。

如果有必要，我们可以将函数操作提前，将结果存入一个新的列中，并在新列上建立索引，以提高查询效率。

2. 使用合适的条件表达式在条件表达式中，我们应尽量避免使用不等于操作符。

如果确实需要使用不等于操作符，可以考虑通过拆分查询，使用UNION操作符来减少不等于操作符的使用次数，提高性能。

3. 确保查询条件与索引列类型匹配查询条件的列类型应与索引列类型匹配，以保证索引能够被正确使用。

MySQL中的索引失效分析与解决办法

MySQL中的索引失效分析与解决办法引言在数据库中，索引是提高查询效率的关键要素之一。

然而，由于索引的选择与使用并不总是正确和合适的，有时候我们可能会遇到索引失效的情况，导致查询性能变得很差。

本文将探讨MySQL中索引失效的原因，并提供一些解决办法。

一、索引失效的原因1. 索引列数据类型不适配在使用索引进行查询时，如果查询的值与索引列的数据类型不匹配，将导致索引失效。

例如，如果将一个字符串类型的查询值与一个整型索引列进行匹配，MySQL将不会使用该索引。

解决办法：确保查询值与索引列的数据类型匹配，可以使用类型转换函数进行处理，或者修改表结构，使得索引列的数据类型更适合查询需求。

2. 使用函数操作符当使用函数操作符（如LOWER，UPPER等）对索引列进行查询时，MySQL将无法使用该索引。

解决办法：尽量避免在索引列上使用函数操作符，可以通过修改查询逻辑或调整数据存储方式来解决。

3. 使用LIKE或通配符查询使用LIKE或通配符查询时，索引也可能失效。

因为通配符查询中的前缀模糊查询（如"LIKE 'abc%'"）可以使用索引，但是如果通配符位于中间或后面（如"LIKE '%abc%'"），MySQL将无法使用索引。

解决办法：考虑使用全文索引或其他更合适的查询方式，避免使用通配符查询。

4. 数据表过大或索引选择性低当数据表非常庞大且索引选择性低时，索引的效果将变得不明显甚至失效。

选择性是指索引中不同键值的数量与数据表中总记录数的比例。

解决办法：可以考虑重新设计索引，优化表结构，或者使用分区表等方式来提高索引的选择性和性能。

二、索引失效的解决办法1. 优化查询语句首先，我们可以通过分析查询语句，找出可能导致索引失效的原因，然后通过修改查询逻辑或重写查询语句来解决。

例如，尽量避免在索引列上使用函数操作符，避免使用LIKE或通配符查询等。

MySQL中的索引失效原因分析与解决方法

MySQL中的索引失效原因分析与解决方法引言在数据库中，索引是提高查询效率的重要工具。

然而，在实际应用中，我们常常会遇到索引失效的情况。

本文将探讨MySQL中索引失效的原因，并提出相应的解决方法。

一、索引失效的原因分析1. 条件不满足索引选择性索引选择性是指索引列中不重复值的比例。

如果索引列的选择性太低，即有很多重复的值，那么使用该索引进行查询将会扫描大量的数据，导致索引失效。

尤其是对于包含大量重复值的列，如性别列，使用索引几乎没有意义。

因此，在设计表结构时，应尽量选择有较高选择性的列作为索引。

2. 数据类型不匹配或长度不一致如果查询条件中的数据类型与索引列的数据类型不一致，或者长度不一致，将导致索引失效。

例如，如果索引列是整型，而查询条件中将该列用字符串类型进行比较，MySQL将无法利用索引进行优化查询。

因此，在编写SQL语句时，应确保查询条件与索引列的数据类型和长度相匹配。

3. 字符串类型的索引使用了函数如果在查询条件中对字符串类型的索引列使用了函数，如使用LEFT()、CONCAT()、LOWER()等函数进行处理，将导致索引失效。

因为MySQL无法在B-tree索引中执行函数，所以必须全量扫描索引列的数据。

因此，在设计查询条件时，尽量避免对索引列使用函数。

4. 隐式类型转换MySQL在执行查询语句时，会进行隐式类型转换以匹配索引列的数据类型。

如果查询条件中出现了隐式类型转换，将导致索引失效。

例如，索引列是整型，而查询条件中使用字符串进行比较，这时MySQL会将字符串转换为整型进行比较，从而导致索引失效。

在编写SQL时，应确保查询条件与索引列的数据类型保持一致。

5. 联合索引列顺序不当联合索引是指多个列组合形成的索引。

如果查询条件中未按照联合索引列的顺序进行查询，将导致索引失效。

例如，联合索引由列A和列B组成，查询条件中只有列B，这时无法使用该联合索引进行查询。

因此，在设计联合索引时，应根据查询条件经常出现的情况进行合理的顺序安排。

MySQL索引失效原因分析与解决方法

MySQL索引失效原因分析与解决方法引言MySQL作为目前广泛使用的关系型数据库管理系统，索引是提高查询效率的重要手段之一。

然而，虽然我们在设计表结构时会考虑使用索引来优化查询，但有时候会发现索引并没有起到预期的作用，查询仍然变得缓慢。

本文将深入探讨MySQL索引失效的原因，并提供解决方法。

1. 数据类型不匹配索引的数据类型必须与查询条件一致，否则索引将无法发挥作用。

例如，如果索引是整数类型，但查询条件中使用了带引号的字符串，MySQL就无法使用该索引。

因此，在设计表结构时，应该考虑到查询条件的数据类型，并确保索引与之匹配。

2. 列顺序不匹配在创建组合索引时，索引的列顺序也非常关键。

如果查询条件中只包含部分组合索引的列，MySQL也无法使用该索引。

因此，当设计组合索引时，应该根据查询条件的列顺序来确定索引的列顺序，将最常用的列放在前面，以发挥最佳的索引效果。

3. 索引列上使用了函数或表达式如果查询条件中的列使用了函数或表达式，MySQL无法使用索引进行优化查询。

因此，在设计表结构时，应该避免在索引列上使用函数或表达式。

如果需要使用函数或表达式，可以考虑创建虚拟列，将函数或表达式结果存储为实际列，然后在该列上创建索引。

4. 数据量过大当索引列的数据量过大时，MySQL的查询优化器可能会选择不使用索引，而是全表扫描。

这是因为使用索引需要进行额外的磁盘IO操作，而全表扫描则可以减少磁盘IO。

因此，在设计索引时，应该注意将索引列的数据量控制在合理范围内，以避免索引失效。

5. LIKE查询中使用通配符在开头在使用LIKE进行模糊查询时，如果通配符出现在查询条件的开头，MySQL无法使用索引进行优化。

例如，LIKE '%keyword'，这种查询条件无法使用索引。

解决方法是将通配符放在查询条件的末尾，例如，LIKE 'keyword%'，这样MySQL可以使用索引。

6. 索引列上存在NULL值在索引列上存在NULL值时，MySQL会忽略这些行，因此索引无法起到作用。

MySQL不走索引的情况

MySQL不⾛索引的情况1.索引列参与了计算，不⾛索引！不⾛索引情况：SELECT `username` FROM `t_user` WHERE age+10=30; ⾛索引情况，因为没有在列上运算：SELECT `username` FROM `t_user` WHERE age=30-10;2.索引列上使⽤了函数，不⾛索引！不⾛索引情况：SELECT username FROM t_user WHERE concat(username,'1') ='admin1'; ⾛索引情况，因为没有在列上使⽤函数：SELECT username FROM t_user WHERE username =concat('admin','1');3.索引列使⽤了Like %XXX或Like %XXX%，不⾛索引！不⾛索引情况：select * from user where username like '%mysql测试' select * from user where username like '%mysql测试%' ⾛索引情况: select * from user where username like 'mysql测试%' ;4.隐式转换——字符串列与数字⽐较，不⾛索引！ title字段为varchar 不⾛索引情况：select * from job j where j.`title` = 1 ⾛索引情况，因为⽐较的是字符:select * from job j where j.`title` = '1'5.尽量避免 OR 操作，只要有⼀个字段没有索引，改语句就不⾛索引，不⾛索引！ select * from t_user where username = 'mysql测试' or password ='123456' 当只有username有索引，则不⾛索引。

MySQL数据库in太多不走索引案例

MySQL数据库in太多不⾛索引案例Limiting Memory Use for Range OptimizationTo control the memory available to the range optimizer, use the system variable:A value of 0 means “no limit.”With a value greater than 0, the optimizer tracks the memory consumed when considering the range access method. If the specified limit is about to be exceeded, the range access method is abandoned and other methods, including a full table scan, are considered instead. This could be less optimal.If this happens, the following warning occurs (where N is the current value):同样的SQL语句，in ⼏百个。

在⼀台实例上⾛索引，在另⼀个实例上不⾛索引，第⼀感觉是和eq_range_index_dive_limit 之类的参数，调了⼏次之后⽆法复现问题，在看帮助⽂档时才注意到 range_optimizer_max_mem_size 参数也会引起全表扫描。

问题复现如下：mysql> show create table t1;+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| t1 | CREATE TABLE `t1` (`id` int(11) NOT NULL AUTO_INCREMENT,`od` int(11) DEFAULT NULL,`name` varchar(200) DEFAULT NULL,PRIMARY KEY (`id`),UNIQUE KEY `od` (`od`),KEY `idx_name` (`name`)) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8 |+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> select * from t1;+-----+------+------+| id | od | name |+-----+------+------+| 1 | 1 | a || 2 | 2 | b || 3 | 3 | c || 4 | 4 | d || 100 | 100 | f |+-----+------+------+5 rows in set (0.00 sec)mysql> show variables like 'range_optimizer_max_mem_size';+------------------------------+---------+| Variable_name | Value |+------------------------------+---------+| range_optimizer_max_mem_size | 8388608 |+------------------------------+---------+1 row in set (0.00 sec)mysql> explain select * from t1 where id in(1,3,5);+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+| 1 | SIMPLE | t1 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 3 | 100.00 | Using where |+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)mysql> set range_optimizer_max_mem_size=1;Query OK, 0 rows affected (0.00 sec)mysql>mysql> explain select * from t1 where id in(1,3,5);+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| 1 | SIMPLE | t1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 6 | 50.00 | Using where |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set,2 warnings (0.00 sec)。

mysql索引查询with_mysqlselectwithin子句不使用索引

mysql索引查询with_mysqlselectwithin子句不使用索
引
如果MySQL查询中使用了`WITH_MYSQLSELECTWITHIN`子句，但没有使用索引，可能有以下几种可能原因：
1. 索引不存在或没有被正确创建：确保表中有适当的索引，并且这些索引被正确创建。

可以使用`SHOW INDEX FROM tableName`语句来检查索引的存在和状态。

2.查询条件不满足索引使用的前缀长度：一些类型的索引（如前缀索引）需要满足一定长度的前缀才能被使用。

如果查询条件不满足这个长度要求，MySQL将不会使用索引。

3. 数据库统计信息不准确：MySQL使用统计信息来优化查询计划。

如果统计信息不准确或过期，可能导致MySQL选择了不合适的查询计划。

可以使用`ANALYZE TABLE tableName`命令来更新统计信息。

4.查询中使用了函数或表达式：一些函数和表达式可能会导致索引失效，使得MySQL无法使用索引来加速查询。

尽量避免在索引列上使用函数或表达式。

5.查询条件过于宽泛：如果查询条件返回的结果集非常大，MySQL可能会选择不使用索引而使用全表扫描。

可以尝试优化查询条件，缩小结果集的范围。

综上所述，可以通过正确创建适当的索引、优化查询条件、更新统计信息等方法来解决MySQL查询中`WITH_MYSQLSELECTWITHIN`子句不使用索引的问题。

MySQL中的索引失效原因和解决办法

MySQL中的索引失效原因和解决办法MySQL是目前最常用的开源关系型数据库管理系统之一，索引是MySQL中用于提高查询效率的重要机制。

然而，在使用索引的过程中，有时候会遇到索引失效的情况，这会导致查询性能下降甚至出现性能瓶颈。

本文将探讨MySQL中索引失效的原因和相应的解决办法。

一、索引失效的原因1. 数据类型不匹配在MySQL中，索引的数据类型必须与查询条件的数据类型一致，否则索引将无法生效。

例如，如果一个字段定义为整型，但在查询时用字符串类型进行检索，那么索引将无法利用。

解决办法：确保查询条件的数据类型与索引的数据类型一致，或者使用类型转换函数将查询条件转换成合适的数据类型。

2. 索引列上进行函数操作当在索引列上进行函数操作时，索引将无法使用。

例如，对于一个名为"username"的索引列进行函数操作"LOWER(username)"，那么索引将不起作用。

解决办法：避免在索引列上进行函数操作，可以通过调整查询逻辑或添加额外的列来解决。

3. LIKE查询时以通配符开头在使用LIKE进行模糊查询时，如果通配符（如%）出现在查询字符串的开头，索引也将失效。

例如，"LIKE '%keyword'"将无法使用索引。

解决办法：优化查询逻辑，考虑将LIKE查询改写成以通配符结尾的形式（如"LIKE 'keyword%'"），这样索引在查询时就能够生效。

4. 组合索引的顺序问题在使用组合索引时，索引的顺序非常重要。

如果查询的条件与组合索引的顺序不一致，索引将无法生效。

解决办法：根据查询的条件重新调整组合索引的顺序，确保查询的条件与组合索引的顺序一致。

5. 复合索引中出现范围查询当在复合索引中使用了范围查询（如BETWEEN、>、<、IN等），而范围查询的条件不是索引的最左前缀时，索引将失效，MySQL将会执行全表扫描。