where 中使用 case when 无法使用索引

最新推荐文章于 2024-08-06 12:05:05 发布
最新推荐文章于 2024-08-06 12:05:05 发布
阅读量3.4k 收藏 2
点赞数 1
分类专栏： Mysql 文章标签： where case when case when case when 不使用索引
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/shaochenshuo/article/details/115658670
版权
本文分析了一种可能导致数据库CPU使用率飙升的SQL问题，通过实例展示了casewhen在where子句中的使用如何导致全表扫描，即使有索引也无法利用。作者通过测试不同limit条件，揭示了函数应用对索引的影响以及为何扫描范围超出预期。
摘要由CSDN通过智能技术生成

1.版本

1)操作系统

#cat /proc/version
Linux version 4.14.76-1.el7.rx.x86_64 (root@8dfc8db5e7914181ad0cf0484d9be73a) (gcc version 4.8.5 20150623 (Red Hat 4.8.5-28) (GCC)) #1 SMP Thu Oct 18 09:52:06 +06 2018

2)mysql数据库版本

> select version();
+-----------+
| version() |
+-----------+
| 8.0.19    |
+-----------+
1 row in set (0.00 sec)

 

2.问题描述

    一个朋友的数据库经常出现cpu使用率彪高的现象(正常时他的数据库cpu使用率在20%左右，异常时上升到 50%-60%)，通过排查发现一类可疑的sql(在 where 条件中使用了 case when)，他的原始sql我就不发了。我在测试环境写了一个更能看出问题的sql如下

select * from test_user where case when '01' between '00' and '11' then id<10 else id<5 end;

1. 表结构及统计信息如下:
[root@127.0.0.1][test_shao]> show create table test_user;
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                                                                                                           |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_user | CREATE TABLE `test_user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_name` varchar(80) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `email` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `tel` varchar(30) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[root@127.0.0.1][test_shao]> show table status like 'test_user';
+-----------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name      | Engine | Version | Row_format | Rows     | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment |
+-----------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| test_user | InnoDB |      10 | Dynamic    | 99363553 |             99 |  9852420096 |               0 |            0 |   7340032 |      100000001 | 2021-02-02 15:53:23 | NULL        | NULL       | utf8mb4_general_ci |     NULL |                |         |
+-----------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.07 sec)


2.第一类 select 测试
[root@127.0.0.1][test_shao]> explain select * from test_user where case when '01' between '00' and '11' then id<10 else id<5 end;
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | test_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99363553 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
#我们看到查询并不会走 id 列的主键索引，走的是全表扫描

[root@127.0.0.1][test_shao]> select * from test_user where case when '01' between '00' and '11' then id<10 else id<5 end;
+----+-------------+--------------------------+---------------------+-------------+
| id | user_name   | email                    | create_time         | tel         |
+----+-------------+--------------------------+---------------------+-------------+
|  1 | user_test_1 | user_test_1xxxxxx@qq.com | 2021-02-02 15:54:56 | 15955827468 |
|  2 | user_test_2 | user_test_2xxxxxx@qq.com | 2021-02-02 15:54:56 | 15957465968 |
|  3 | user_test_3 | user_test_3xxxxxx@qq.com | 2021-02-02 15:54:56 | 15919847440 |
|  4 | user_test_4 | user_test_4xxxxxx@qq.com | 2021-02-02 15:54:56 | 15926839297 |
|  5 | user_test_5 | user_test_5xxxxxx@qq.com | 2021-02-02 15:54:56 | 15974654170 |
|  6 | user_test_6 | user_test_6xxxxxx@qq.com | 2021-02-02 15:54:56 | 15992752962 |
|  7 | user_test_7 | user_test_7xxxxxx@qq.com | 2021-02-02 15:54:56 | 15939802303 |
|  8 | user_test_8 | user_test_8xxxxxx@qq.com | 2021-02-02 15:54:56 | 15920752632 |
|  9 | user_test_9 | user_test_9xxxxxx@qq.com | 2021-02-02 15:54:56 | 15984356256 |
+----+-------------+--------------------------+---------------------+-------------+
9 rows in set (1 min 58.28 sec)

该查询的慢日志如下：
# User@Host: root[root] @  [127.0.0.1]  Id:    17
# Query_time: 118.227921  Lock_time: 0.001371 Rows_sent: 9  Rows_examined: 100000000
SET timestamp=1618278221;
select * from test_user where case when '01' between '00' and '11' then id<10 else id<5 end;

#从慢日志中我们可以看出来该sql扫描了表中所有的记录(Rows_examined: 100000000)，最终返回9条记录(id<10)



3.第二类 select 测试 
[root@127.0.0.1][test_shao]> explain select * from test_user where case when '01' between '00' and '11' then id<10 else id<5 end limit 20;
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | test_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99363553 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
#该sql依然是全表扫描

[root@127.0.0.1][test_shao]> select * from test_user where case when '01' between '00' and '11' then id<10 else id<5 end limit 20;
+----+-------------+--------------------------+---------------------+-------------+
| id | user_name   | email                    | create_time         | tel         |
+----+-------------+--------------------------+---------------------+-------------+
|  1 | user_test_1 | user_test_1xxxxxx@qq.com | 2021-02-02 15:54:56 | 15955827468 |
|  2 | user_test_2 | user_test_2xxxxxx@qq.com | 2021-02-02 15:54:56 | 15957465968 |
|  3 | user_test_3 | user_test_3xxxxxx@qq.com | 2021-02-02 15:54:56 | 15919847440 |
|  4 | user_test_4 | user_test_4xxxxxx@qq.com | 2021-02-02 15:54:56 | 15926839297 |
|  5 | user_test_5 | user_test_5xxxxxx@qq.com | 2021-02-02 15:54:56 | 15974654170 |
|  6 | user_test_6 | user_test_6xxxxxx@qq.com | 2021-02-02 15:54:56 | 15992752962 |
|  7 | user_test_7 | user_test_7xxxxxx@qq.com | 2021-02-02 15:54:56 | 15939802303 |
|  8 | user_test_8 | user_test_8xxxxxx@qq.com | 2021-02-02 15:54:56 | 15920752632 |
|  9 | user_test_9 | user_test_9xxxxxx@qq.com | 2021-02-02 15:54:56 | 15984356256 |
+----+-------------+--------------------------+---------------------+-------------+
9 rows in set (1 min 58.02 sec)
#执行时间同未加 limit 限制条件的sql 也差不多

该sql的慢日志如下：
# User@Host: root[root] @  [127.0.0.1]  Id:    19
# Query_time: 118.015081  Lock_time: 0.000325 Rows_sent: 9  Rows_examined: 100000000
SET timestamp=1618280076;

#从慢日志中我们可以看出，该sql也是扫描了整张表中的记录，最后返回了9条记录


3.第三类 select 测试(修改 limit限制的值)
[root@127.0.0.1][test_shao]> explain select * from test_user where case when '01' between '00' and '11' then id<10 else id<5 end limit 9;
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | test_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99363553 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
#执行计划我们看到同上面两个sql一致

[root@127.0.0.1][test_shao]> select * from test_user where case when '01' between '00' and '11' then id<10 else id<5 end limit 9;
+----+-------------+--------------------------+---------------------+-------------+
| id | user_name   | email                    | create_time         | tel         |
+----+-------------+--------------------------+---------------------+-------------+
|  1 | user_test_1 | user_test_1xxxxxx@qq.com | 2021-02-02 15:54:56 | 15955827468 |
|  2 | user_test_2 | user_test_2xxxxxx@qq.com | 2021-02-02 15:54:56 | 15957465968 |
|  3 | user_test_3 | user_test_3xxxxxx@qq.com | 2021-02-02 15:54:56 | 15919847440 |
|  4 | user_test_4 | user_test_4xxxxxx@qq.com | 2021-02-02 15:54:56 | 15926839297 |
|  5 | user_test_5 | user_test_5xxxxxx@qq.com | 2021-02-02 15:54:56 | 15974654170 |
|  6 | user_test_6 | user_test_6xxxxxx@qq.com | 2021-02-02 15:54:56 | 15992752962 |
|  7 | user_test_7 | user_test_7xxxxxx@qq.com | 2021-02-02 15:54:56 | 15939802303 |
|  8 | user_test_8 | user_test_8xxxxxx@qq.com | 2021-02-02 15:54:56 | 15920752632 |
|  9 | user_test_9 | user_test_9xxxxxx@qq.com | 2021-02-02 15:54:56 | 15984356256 |
+----+-------------+--------------------------+---------------------+-------------+
9 rows in set (0.00 sec)
#但是sql执行起来却很快

该sql的慢查询日志如下：
# User@Host: root[root] @  [127.0.0.1]  Id:    20
# Query_time: 0.000758  Lock_time: 0.000294 Rows_sent: 9  Rows_examined: 9
SET timestamp=1618281582;
select * from test_user where case when '01' between '00' and '11' then id<10 else id<5 end limit 9;

#从该sql的慢查日志中我们看到该sql最终只扫描了9条记录(limit 9)

NOTE1:从上面的例子中我们看到，对于上面的sql 不加limit 限制，或者加的limit限制 大于9时，sql都是扫描整张表的数据。为什么没有使用主键索引索引？因为我们对索引列使用了函数，所以导致索引列失效(case when 函数)

NOTE2:全表扫描走索引链表的最小端往最大端扫描时扫描到id>=10之后还在继续往后扫描，并未终止扫描(上面测试中未加limit以及limit 大于10的情况)，这是为什么？确实在一直往下扫描，没搞清楚为什么

NOTE3:这时使用 use index() 或者 force index() 都无法让如下sql走主键索引

 

渔夫数据库笔记
关注
专栏目录
MySQL中使用case when 语句实现多条件查询的方法
12-15
在本文中，我们将深入探讨如何使用 `CASE WHEN` 语句来实现多条件查询，特别是在处理数据表 `DemoTable` 中的 `condition1` 至 `condition5` 这五个字段时。 首先，我们要理解 `CASE WHEN` 的基本语法。`CASE WHEN`...
在where条件中使用CASE WHEN 语句
weixin_30514745的博客
06-15 5357
CREATE TABLE TB_Test_Report ( id int identity, stateid int, userid int, username varchar(64) ) go INSERT INTO TB_Test_Report (stateid,userid,username) VALUES (20,1,'a') INSERT INTO TB...
where后面case-when的用法
热门推荐
Min_Monk的博客
12-11 1万+
准备测试数据，建表语句 drop table test_table; create table test_table ( col_a NUMBER, col_b NUMBER, col_c VARCHAR2(5) ) insert into test_table values(2, 1, '>'); insert into test...
MYSQL中where 中 使用CASE WHEN
最新发布
RoadLong的博客
08-06 803
MYSQL中where嵌套使用CASE WHEN
where语句后跟case语句
qq_43985303的博客
02-08 3896
补充：当使用 B.name = 时，将不会出现以上问题，也就是如下写法也正确。正确写法：将B.name in 放到CASE END 中。在where后面使用case when的说明。
MySQL在where后面使用case when
u011250186的博客
03-11 1万+
在MySQL中，在where后面使用case when的说明 报错语句 select * from A, B where B.name in CASE when A.id = 0 then ('aaa', 'bbb') when A.id = 1 then ('bbb', 'ccc') when A.id = 2 then ('ccc', 'ddd') END 正确写法：将B.name in 放到CASE END 中 select * from A, B where ...
case when中的条件不能用上索引
03-16 3392
今日在对一报表数据源作优化时无论任何都不能让其走索引.数据源部分语句如下:select cityCode,servtype,1,channel,b.day_time, sum(case when to_number(...
C#开发和使用中的23个技巧
02-29
在 SQL Server 中使用 `LIKE` 操作符进行模糊查询时，可以使用通配符 `%` 来匹配任意数量的字符。 **示例 SQL：** ```sql SELECT * FROM myTable WHERE hao LIKE '%' + @hao + '%'; ``` ### 6. Winforms TextBox ...
SQL实验：使用索引，视图和批量
07-18
根据提供的实验报告书内容，我们可以总结出以下几个关键的知识点： ### 1....以上内容涵盖了实验报告中提到的所有知识点，包括索引的创建、视图的创建和使用、视图中的数据更新以及批量处理的实现。
详解sqlserver查询表索引
12-15
首先，我们可以使用以下SQL查询来获取SQL Server中的表索引信息： ```sql SELECT 索引名称=a.name, 表名=c.name, 索引字段名=d.name, 索引字段位置=d.colid FROM sysindexes a JOIN sysindexkeys b ON a.id=b.id ...
复杂SQL查询语句的使用
05-30
CASE WHEN允许我们在SQL查询中创建条件逻辑，根据不同的条件返回不同的结果。这对于创建复杂的计算列或筛选非常有用。 9. **临时表和表变量** 临时表和表变量可以在查询过程中存储中间结果，尤其在处理大型复杂...
Day6 | 数据库操作-索引补充、非空表达式、CASE WHEN函数
MineData
06-27 426
背景介绍本篇针对索引进行展开介绍，以及补充对非空表达式和CASE WHEN函数的介绍。索引复习一下唯一索引&主键索引唯一索引要求作为索引的字段列值唯一；主键索引是特殊的唯一索引，...
oracle case when索引 空值索引 位图索引
u010653311的专栏
07-19 2422
T1表里面有200W条数据，其中ID=2只有10条记录，查询ID=2时就可以用到case when索引。 建索引可以这样： CREATE INDEX I_1 ON T1(CASE WHEN ID>1 THEN ID ELSE NULL END);   或者类似CASE ID WHEN 2 THEN 2...或者不需要大于小于，可以用DECODE 查询的SQL也要改： SELECT * F
MySQL进阶【四】—— MySQL索引优化实战
shehuinidaye的博客
09-24 529
示例表 CREATE TABLE `employees` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名', `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄', `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位', `hire_time` timestamp NO
where 条件 in 判断语句中使用case when
u013276512的博客
09-25 5920
实战干货 case when 一般用在 select 后面或者where条件的=后面，如： select case when '${unit}'='单' then order_num else quantity_num from a where project = case when '${type}'='T1' then 'P1' else 'P2' 如果type是一个特殊值时，需要使用where条件in逻辑筛选数据，那就会有下面的写法： where project in (case when '$
where条件中使用case when是什么鬼？
xiaokanfuchen86的博客
08-27 3151
case when
测一测自己的Sql能力之MYSQL的函数会造成索引失败
永远热泪盈眶 坚持输出
08-14 577
本片详细说明了MYSQL的函数会造成索引失败的注意要点，并给出具体操作实例，如有出入还望指正。欢迎大家关注【可为编程】，成长，进步，编程，技术、掌握更多知识！
sql示例：case when作为where 条件
还在活水泥的未来包工头
06-02 1万+
上述代码中，使用了多个CASE WHEN语句在WHERE条件中进行组合，其中condition_1、condition_2和condition_3表示需要满足的条件。上述代码中，CASE WHEN语句在WHERE条件中作为一个条件，其中condition_1和condition_2表示需要满足的条件。需要注意的是，使用CASE WHEN语句进行条件过滤时，应根据具体的需求来选择使用在WHERE条件中还是SELECT语句中，以及如何与其他条件进行组合。CASE WHEN语句在WHERE条件中作为条件之一。
mysql where后面跟case when
08-26
你可以在MySQL的WHERE子句中使用CASE WHEN语句来进行条件判断和筛选。下面是一个示例： ``` SELECT * FROM your_table WHERE CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE result3 END; ``` 在上面的示例中，你可以根据不同的条件进行筛选。如果condition1为真，则使用result1进行筛选；如果condition2为真，则使用result2进行筛选；否则，使用result3进行筛选。根据你的具体需求，你可以根据条件编写不同的逻辑。 请注意，在WHERE子句中使用CASE WHEN语句可能会影响查询的性能。在处理大量数据时，请确保适当地使用索引来提高查询效率。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

渔夫数据库笔记

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值