MySQL bit类型增加索引后查询结果不正确案例浅析

mysql,bit · 浏览次数 : 8

小编点评

本文通过一个实际案例，探讨了MySQL中bit类型字段与索引的关系及其对SQL查询结果的影响。文章首先介绍了案例背景，然后详细描述了测试环境和相关SQL语句。接着，文章分析了执行计划、trace跟踪以及类型转换等问题，并给出了相应的建议。 1. **案例背景**： - 案例描述了一个表`student_attend`中的bit类型字段`is_attend`。 - 同事在优化SQL时为该字段新增了索引，但测试后发现SQL语句执行结果与不加索引时不一致。 - 删除索引后，SQL语句能正确查询出记录。 2. **测试环境与SQL语句**： - 文章创建了表并初始化了一些数据。 - 提供了两种不同的SQL查询方式，一种使用了字符串，另一种使用了bit类型。 - 测试结果显示，使用bit类型的查询能够正常返回结果，而使用字符串的查询返回了空结果集。 3. **执行计划与trace分析**： - 分析了执行计划，发现没有走全表扫描也没有走索引。 - 使用trace跟踪分析，发现发生了隐式类型转换，导致SQL语句返回空结果集。 4. **类型转换问题**： - 文章指出，在使用bit类型的字段时，应避免使用字符串，以减少隐式类型转换的风险。 - 正确的写法是使用bit类型的值，如`is_attend=b'1'`或`is_attend=1`。 5. **索引创建建议**： - 文章建议DBA在创建bit类型字段的索引时，要谨慎处理，与开发和支持人员多沟通。 - 提醒开发人员注意潜在的问题，以避免类似的错误发生。 总的来说，本文通过一个具体的案例，展示了bit类型字段与索引在MySQL中的交互问题，强调了在编写SQL语句时要注意类型转换问题，并提出了避免使用字符串类型以提高查询效率的建议。

正文

昨天同事遇到的一个案例，这里简单描述一下：一个表里面有一个bit类型的字段，同事在优化相关SQL的过程中，给这个表的bit类型的字段新增了一个索引，然后测试验证 时，居然发现SQL语句执行结果跟不加索引不一样。加了索引后，SQL语句没有查询出一条记录，删除索引后，SQL语句就能查询出几十条记录。下面我们构造一个简单 的例子，重现一下这个案例

我们先创建表student_attend，初始化一些数据。这篇文章的测试环境为MySQL 8.0.35社区版。

CREATE TABLE `student_attend` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '自增编号',
  `std_id` int DEFAULT NULL COMMENT '学号',
  `class_id` int DEFAULT NULL COMMENT '课程编号',
  `is_attend` bit(1) DEFAULT b'1' COMMENT '是否缺陷考勤',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;


insert into student_attend(std_id, class_id, is_attend)
select 1001, 1, 1 from dual union all
select 1001, 2, 0 from dual union all
select 1001, 3, 1 from dual union all
select 1001, 4, 1 from dual union all
select 1001, 5, 1 from dual union all
select 1001, 6, 0 from dual union all
select 1002, 1, 1 from dual union all
select 1002, 2, 1 from dual union all
select 1003, 1, 0 from dual union all
select 1003, 2, 0 from dual;

如下所示，假设我们要查询is_attend=1的所有学生信息，那么可以有下面三种写法

mysql> select * from student_attend where is_attend=1;
+----+--------+----------+----------------------+
| id | std_id | class_id | is_attend            |
+----+--------+----------+----------------------+
|  1 |   1001 |        1 | 0x01                 |
|  3 |   1001 |        3 | 0x01                 |
|  4 |   1001 |        4 | 0x01                 |
|  5 |   1001 |        5 | 0x01                 |
|  7 |   1002 |        1 | 0x01                 |
|  8 |   1002 |        2 | 0x01                 |
+----+--------+----------+----------------------+
6 rows in set (0.00 sec)

mysql> select * from student_attend where is_attend=b'1';
+----+--------+----------+----------------------+
| id | std_id | class_id | is_attend            |
+----+--------+----------+----------------------+
|  1 |   1001 |        1 | 0x01                 |
|  3 |   1001 |        3 | 0x01                 |
|  4 |   1001 |        4 | 0x01                 |
|  5 |   1001 |        5 | 0x01                 |
|  7 |   1002 |        1 | 0x01                 |
|  8 |   1002 |        2 | 0x01                 |
+----+--------+----------+----------------------+
6 rows in set (0.00 sec)

#遇到问题的SQL写法
mysql> select * from student_attend where is_attend='1';
+----+--------+----------+----------------------+
| id | std_id | class_id | is_attend            |
+----+--------+----------+----------------------+
|  1 |   1001 |        1 | 0x01                 |
|  3 |   1001 |        3 | 0x01                 |
|  4 |   1001 |        4 | 0x01                 |
|  5 |   1001 |        5 | 0x01                 |
|  7 |   1002 |        1 | 0x01                 |
|  8 |   1002 |        2 | 0x01                 |
+----+--------+----------+----------------------+
6 rows in set (0.00 sec)

mysql> 

接下来，我们在字段is_attend上创建索引ix_student_attend_n1,如下所示

create index ix_student_attend_n1 on student_attend(is_attend);

然后我们继续测试验证，就能出现我前文所说的情况,如需所示，最后一个SQL，它的返回记录数为0.

mysql> select * from student_attend where is_attend=1;
+----+--------+----------+----------------------+
| id | std_id | class_id | is_attend            |
+----+--------+----------+----------------------+
|  1 |   1001 |        1 | 0x01                 |
|  3 |   1001 |        3 | 0x01                 |
|  4 |   1001 |        4 | 0x01                 |
|  5 |   1001 |        5 | 0x01                 |
|  7 |   1002 |        1 | 0x01                 |
|  8 |   1002 |        2 | 0x01                 |
+----+--------+----------+----------------------+
6 rows in set (0.00 sec)

mysql> select * from student_attend where is_attend=b'1';
+----+--------+----------+----------------------+
| id | std_id | class_id | is_attend            |
+----+--------+----------+----------------------+
|  1 |   1001 |        1 | 0x01                 |
|  3 |   1001 |        3 | 0x01                 |
|  4 |   1001 |        4 | 0x01                 |
|  5 |   1001 |        5 | 0x01                 |
|  7 |   1002 |        1 | 0x01                 |
|  8 |   1002 |        2 | 0x01                 |
+----+--------+----------+----------------------+
6 rows in set (0.00 sec)

mysql> select * from student_attend where is_attend='1';
Empty set (0.00 sec)

mysql> 

其实第一次见到这种情况的时候，我还是有点震惊的，因为在我的观念中，索引只会影响执行计划，不会影响查询结果，但是现在的情况是 索引的存在影响了SQL的查询结果。那么为什么会出现这种情况呢？

首先看了一下执行计划，如下所示，从执行计划看，它既没有走全表扫描也没有走索引，仅仅有"message": "no matching row in const table"提示，如果仅仅分析 执行计划，我们得不到更多的有用信息

mysql> explain
    -> select * from student_attend where is_attend='1';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> 
mysql> explain format=json
    -> select * from student_attend where is_attend='1'\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "message": "no matching row in const table"
  } /* query_block */
}
1 row in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `kerry`.`student_attend`.`id` AS `id`,`kerry`.`student_attend`.`std_id` AS `std_id`,`kerry`.`student_attend`.`class_id` AS `class_id`,`kerry`.`student_attend`.`is_attend` AS `is_attend` from `kerry`.`student_attend` where (`kerry`.`student_attend`.`is_attend` = '1')
1 row in set (0.00 sec)

mysql> 

那么我们使用trace跟踪分析一下优化器如何选择执行计划。看看其详细执行过程，如下所示


mysql> SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
Query OK, 0 rows affected (0.00 sec)

mysql> SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student_attend where is_attend='1';
Empty set (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE \G;
*************************** 1. row ***************************
                            QUERY: select * from student_attend where is_attend='1'
                            TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `student_attend`.`id` AS `id`,`student_attend`.`std_id` AS `std_id`,`student_attend`.`class_id` AS `class_id`,`student_attend`.`is_attend` AS `is_attend` from `student_attend` where (`student_attend`.`is_attend` = '1')"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(`student_attend`.`is_attend` = '1')",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(`student_attend`.`is_attend` = '1')"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(`student_attend`.`is_attend` = '1')"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(`student_attend`.`is_attend` = '1')"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`student_attend`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`student_attend`",
                "field": "is_attend",
                "equals": "'1'",
                "null_rejecting": true
              }
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [
              {
                "table": "`student_attend`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 10,
                    "cost": 3.35
                  } /* table_scan */,
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "ix_student_attend_n1",
                      "usable": true,
                      "key_parts": [
                        "is_attend",
                        "id"
                      ] /* key_parts */
                    }
                  ] /* potential_range_indexes */,
                  "setup_range_conditions": [
                    {
                      "impossible_condition": {
                        "cause": "value_out_of_range"
                      } /* impossible_condition */
                    }
                  ] /* setup_range_conditions */,
                  "impossible_range": true
                } /* range_analysis */,
                "rows": 0,
                "cause": "impossible_where_condition"
              }
            ] /* rows_estimation */
          }
        ] /* steps */,
        "empty_result": {
          "cause": "no matching row in const table"
        } /* empty_result */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
          INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> 
mysql> SET optimizer_trace="enabled=off";
Query OK, 0 rows affected (0.01 sec)

从trace的详细信息看，这个过程中发生了隐式转换：下面这个过程就是发生了类型转换

由于发生类型转换过程中(字符串转换为bit类型)遇到了数据截断错误(从value_out_of_range等信息就可以看出),如下截图所示

而优化器应该是根据一定的逻辑判断，得到这个值不存在索引中，从而就判断没有匹配的记录，直接返回空的结果集了，根本不去走扫描全表或走索引查找等操作。

        "empty_result": {
          "cause": "no matching row in const table"
        } /* empty_result */

当然这里仅仅是根据trace的信息做的一个判断，如有错误或不谨慎的地方，敬请谅解。毕竟没有深入分析过源码。

那么为什么没有索引的话，SQL语句的结果就是正确的呢？ 难道没有发生类型转换吗？ 难度没有发生数据截断错误吗？那么我们就继续trace跟踪分析看看，如下所示

mysql> drop index ix_student_attend_n1 on student_attend;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
Query OK, 0 rows affected (0.00 sec)

mysql> SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student_attend where is_attend='1';
+----+--------+----------+----------------------+
| id | std_id | class_id | is_attend            |
+----+--------+----------+----------------------+
|  1 |   1001 |        1 | 0x01                 |
|  3 |   1001 |        3 | 0x01                 |
|  4 |   1001 |        4 | 0x01                 |
|  5 |   1001 |        5 | 0x01                 |
|  7 |   1002 |        1 | 0x01                 |
|  8 |   1002 |        2 | 0x01                 |
+----+--------+----------+----------------------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE \G;
*************************** 1. row ***************************
                            QUERY: select * from student_attend where is_attend='1'
                            TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `student_attend`.`id` AS `id`,`student_attend`.`std_id` AS `std_id`,`student_attend`.`class_id` AS `class_id`,`student_attend`.`is_attend` AS `is_attend` from `student_attend` where (`student_attend`.`is_attend` = '1')"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(`student_attend`.`is_attend` = '1')",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(`student_attend`.`is_attend` = '1')"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(`student_attend`.`is_attend` = '1')"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(`student_attend`.`is_attend` = '1')"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`student_attend`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [
              {
                "table": "`student_attend`",
                "table_scan": {
                  "rows": 10,
                  "cost": 0.25
                } /* table_scan */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`student_attend`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 10,
                      "access_type": "scan",
                      "resulting_rows": 10,
                      "cost": 1.25,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 10,
                "cost_for_plan": 1.25,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "(`student_attend`.`is_attend` = '1')",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`student_attend`",
                  "attached": "(`student_attend`.`is_attend` = '1')"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "finalizing_table_conditions": [
              {
                "table": "`student_attend`",
                "original_table_condition": "(`student_attend`.`is_attend` = '1')",
                "final_table_condition   ": "(`student_attend`.`is_attend` = '1')"
              }
            ] /* finalizing_table_conditions */
          },
          {
            "refine_plan": [
              {
                "table": "`student_attend`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
          INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> SET optimizer_trace="enabled=off";
Query OK, 0 rows affected (0.00 sec)

从上面trace信息来看，似乎执行计划先进行全表扫描，然后过滤记录，输出信息里面没有value_out_of_range这类信息，似乎没有发生数据截断。具体步骤跟之前的trace信息有很大不同。具体只看到了下面这些信息，但是更多信息我也看不出来。不清楚底层到底做了啥。

小结

关于bit类型的字段，我们写SQL的时候，不要使用字符串，避免发生隐式类型转换。正确的写法应该是下面这种方式

select * from student_attend where is_attend=b'1';
或
select * from student_attend where is_attend=1;

DBA在给bit类型创建索引的时候也必须谨慎处理，跟开发和Support人员多协商沟通，告知他们可能出现这种情况，因为你可能没法控制开发人员写出这样的SQL。

mysql bit

与MySQL bit类型增加索引后查询结果不正确案例浅析相似的内容：

MySQL bit类型增加索引后查询结果不正确案例浅析

昨天同事遇到的一个案例，这里简单描述一下：一个表里面有一个bit类型的字段，同事在优化相关SQL的过程中，给这个表的bit类型的字段新增了一个索引，然后测试验证 时，居然发现SQL语句执行结果跟不加索引不一样。加了索引后，SQL语句没有查询出一条记录，删除索引后，SQL语句就能查询出几十条记录。下面

mysql bit
8
[转帖]Oracle、SQL Server、MySQL数据类型对比

Oracle、SQL Server、MySQL数据类型对比 - 知乎 (zhihu.com) 1，标准SQL数据类型 BINARY 每个字符占一个字节 任何类型的数据都可存储在这种类型的字段中。不需数据转换（例如，转换到文本数据）。数据输入二进制字段的方式决定了它的输出方式。BIT 1 个字节0 和

oracle sql server mysql
0
数据特征采样在 MySQL 同步一致性校验中的实践

作者：vivo 互联网存储研发团队 - Shang Yongxing 本文介绍了当前DTS应用中，MySQL数据同步使用到的数据一致性校验工具，并对它的实现思路进行分享。 一、背景 在 MySQL 的使用过程中，经常会因为如集群拆分、数据传输、数据聚合等原因产生流动和数据复制。而在通常的数据复制过程

mysql
27
小白也能懂的Mysql数据库索引详解

一文让你彻底了解：主键索引/二级索引，聚簇索引/非聚簇索引，回表/索引覆盖，索引下推，联合索引/最左联合匹配，前缀索引，explain

mysql
65
mysql查看用户的过期时间

1. mysql查看用户的过期时间的方法 在MySQL中，用户的过期时间（也称为账户过期日期）是一个可选项，用于确定某个MySQL用户账户何时到期。但是，值得注意的是，并非所有的MySQL安装或版本都支持直接设置用户账户的过期时间。特别是，标准的MySQL用户表（如mysql.user）并没有一个专

mysql
0
【冷启动#1】实用的MySQL基础

简单安装一下MySQL Windows下（5.7.x） 本体安装 1、首先先下载安装包，名字如下： mysql-5.7.19-winx64.zip 2、配置环境变量，将解压之后的bin目录添加一下 3、在解压目录下创建my.ini文件，内容如下： [ client ] port=3306 defau

mysql
0
小白也能懂的Mysql数据库索引详解

核心概念 主键索引/二级索引 聚簇索引/非聚簇索引 回表/索引覆盖 索引下推 联合索引/最左联合匹配 前缀索引 explain 一、[索引定义] 1.索引定义 在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法

mysql
8
解读MySQL 8.0数据字典的初始化与启动

本文分享自华为云社区《MySQL全文索引源码剖析之Insert语句执行过程》，作者：GaussDB 数据库。 本文主要介绍MySQL 8.0数据字典的基本概念和数据字典的初始化与启动加载的主要流程。 MySQL 8.0数据字典简介 数据字典（Data Dictionary, DD）用来存储数据库内部

mysql
0
MySQL入门到实战详细教程

MySQL介绍 MySQL是一个开源的关系型数据库管理系统（RDBMS），由瑞典 MySQL AB 公司开发，属于 Oracle 旗下产品，它广泛应用于各种Web应用程序和网站，MySQL使用结构化查询语言（SQL）进行数据的管理和操作。 MySQL主要特点 开源免费：MySQL是一个开源项目，可以

mysql
0
《软件性能测试分析与调优实践之路》第二版-手稿节选-Mysql数据库性能定位与分析

在做MySQL数据的性能定位前，需要先知道MySQL查询时数据库内部的执行过程。只有弄清SQL的执行过程，才能对执行过程中的每一步的性能做定位分析。如图6-2-1所示。 图6-2-1 从图中可以看到，当查询出数据以后，会将数据先返回给执行器，此时执行器先将结果写到查询缓存里面，这样在下次查询相同的数

mysql
2
# 热门排行
微软 New Bing AI 申请与使用保姆级教程（免魔法） ChatGPT API使用介绍 ChatGPT开发实战 一篇带你了解如何使用纯前端类Excel表格构建现金流量表 手把手教你玩转 Excel 数据透视表 为什么 C# 可能是最好的第一编程语言 .NET 入门到高级路线 提高工作效率的神器：基于前端表格实现Chrome Excel扩展插件 React + Springboot + Quartz，从0实现Excel报表自动化 用Echarts实现前端表格引用从属关系可视化
© PmDaddy. 2023 PmDaddy教程网 All rights reserved.