首页 > 数据库 >MySQL bit类型增加索引后查询结果不正确案例浅析

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

时间:2024-06-21 16:46:57浏览次数:24  
标签:attend 0x01 浅析 select student MySQL bit id 1001

昨天同事遇到的一个案例,这里简单描述一下:一个表里面有一个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。

标签:attend,0x01,浅析,select,student,MySQL,bit,id,1001
From: https://www.cnblogs.com/kerrycode/p/18260767

相关文章

  • 对比Oracle和MySQL索引对于like的支持,MySQL一言难尽啊...
     对比Oracle和MySQL索引对于like的支持,MySQL一言难尽啊... MySQL:|Oracle:(root@localhost09:44:08)[zkm](673009)>select*fromtest;|09:52:11ZKM@test(1076......
  • MySQL索引、事务与存储引擎
    目录1.索引介绍(1)工作方式(2)创建索引的依据2.索引类型(1)普通索引(2)唯一索引(3)主键索引(4)多列组合索引(单列索引与多列索引)(5)全文索引(FULLTEXT)(6)删除和查看索引以及各字段的含义(1)删除索引(2)查看索引(3)各字段的含义3.遇到select查询语句执行速度慢该怎么办?4.事务介绍(1)事务的ACID特性(2)隔离......
  • MySQL数据库管理
    目录1.SQL语句(1)DDL:用于管理数据库对象(库、表、索引等)(2)DML:用于管理表数据(3)DQL:用于查询表数据(4)DCL:用于管理用户和权限2.创建及删除数据库和表3.修改表名和表结构4.数据表高级操作5.如何找回root密码?6.MySQL的六大约束属性7.权限管理1.SQL语句关系型数据库专用的操作管理语句,包......
  • MYSQL基础_18_MySQL8其它新特性
    第18章_MySQL8其它新特性1.MySQL8新特性概述MySQL从5.7版本直接跳跃发布了8.0版本,可见这是一个令人兴奋的里程碑版本。MySQL8版本在功能上做了显著的改进与增强,开发者对MySQL的源代码进行了重构,最突出的一点是多MySQLOptimizer优化器进行了改进。不仅在速度上得到了改......
  • MYSQL基础_17_触发器
    第17章_触发器在实际开发中,我们经常会遇到这样的情况:有2个或者多个相互关联的表,如商品信息和库存信息分别存放在2个不同的数据表中,我们在添加一条新商品记录的时候,为了保证数据的完整性,必须同时在库存表中添加一条库存记录。这样一来,我们就必须把这两个关联的操作步......
  • 结合zabbix监控mysql,让mysql性能飙升
      前段时间客户的系统突然出现mysql只读集群cpu飙升的情况,飙升到最高点的时候,甚至导致应用服务器GC,幸好应用有备份服务器,流量直接切过去,客户也无感知。但是这个只是临时的解决办法,总归要找到具体的原因,和开发同事查了两天的应用日志和mysql的慢日志,始终无法定位到具体的问题。......
  • 【Mysql】MySQL存储引擎的介绍
    文章目录前言一、存储引擎简介二、使用场景三、总结前言在处理大量数据的系统中,如何快速准确的获取所需的信息是一个重要的问题。这就像在一个巨大的图书馆中寻找一本书,如果没有目录或指南,这将是一项艰巨的任务。在数据库中,我们使用索引来解决这个问题。本文将介绍M......
  • 五天搞定Mysql基础知识-Day05
    学习目标:        1、MySQL常用内置函数        2、了解存储过程        3、了解视图        4、了解事务        5、了解索引        6、掌握基于命令行的SQL使用第一章MySQL内置函数一、字符串函数1、拼接字符串......
  • MySQL触发器基本结构
    1修改分隔符符号delimiter$$可以修改成$$//都行2创建触发器函数名称createtrigger函数名3什么样的操作触发,操作那个表after:....之后触发before:....之前触发inser:插入被触发uodate:修改被触发delete:删除被触发on表名实例:在users表被添加信息后触发af......
  • JavaWeb——Mysql的启动/登录/卸载
    目录1.Mysql服务器2.Mysql的简单使用2.1启动Mysql:2.2登录Mysql2.3退出3.连接别人的数据库4.卸载mqsql1.Mysql服务器安装了Mysql的计算机都成为Mysql服务器 2.Mysql的简单使用2.1启动Mysql:第一种方法:搜索服务,找到Mysql80,右击打开;第二种方法:输入cmd,右击命......