发帖
翼度科技»论坛 › 编程开发 › mysql › 查看内容

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

七谴

8

主题

8

帖子

24

积分

新手上路

Rank: 1

积分
24
显示全部楼层
昨天同事遇到的一个案例，这里简单描述一下：一个表里面有一个bit类型的字段，同事在优化相关SQL的过程中，给这个表的bit类型的字段新增了一个索引，然后测试验证时，居然发现SQL语句执行结果跟不加索引不一样。加了索引后，SQL语句没有查询出一条记录，删除索引后，SQL语句就能查询出几十条记录。下面我们构造一个简单的例子，重现一下这个案例
我们先创建表student_attend，初始化一些数据。这篇文章的测试环境为MySQL 8.0.35社区版。
  1. CREATE TABLE `student_attend` (
  2.   `id` int NOT NULL AUTO_INCREMENT COMMENT '自增编号',
  3.   `std_id` int DEFAULT NULL COMMENT '学号',
  4.   `class_id` int DEFAULT NULL COMMENT '课程编号',
  5.   `is_attend` bit(1) DEFAULT b'1' COMMENT '是否缺陷考勤',
  6.   PRIMARY KEY (`id`)
  7. ) ENGINE=InnoDB;


  10. insert into student_attend(std_id, class_id, is_attend)
  11. select 1001, 1, 1 from dual union all
  12. select 1001, 2, 0 from dual union all
  13. select 1001, 3, 1 from dual union all
  14. select 1001, 4, 1 from dual union all
  15. select 1001, 5, 1 from dual union all
  16. select 1001, 6, 0 from dual union all
  17. select 1002, 1, 1 from dual union all
  18. select 1002, 2, 1 from dual union all
  19. select 1003, 1, 0 from dual union all
  20. select 1003, 2, 0 from dual;
复制代码
如下所示，假设我们要查询is_attend=1的所有学生信息，那么可以有下面三种写法
  1. mysql> select * from student_attend where is_attend=1;
  2. +----+--------+----------+----------------------+
  3. | id | std_id | class_id | is_attend            |
  4. +----+--------+----------+----------------------+
  5. |  1 |   1001 |        1 | 0x01                 |
  6. |  3 |   1001 |        3 | 0x01                 |
  7. |  4 |   1001 |        4 | 0x01                 |
  8. |  5 |   1001 |        5 | 0x01                 |
  9. |  7 |   1002 |        1 | 0x01                 |
  10. |  8 |   1002 |        2 | 0x01                 |
  11. +----+--------+----------+----------------------+
  12. 6 rows in set (0.00 sec)

  14. mysql> select * from student_attend where is_attend=b'1';
  15. +----+--------+----------+----------------------+
  16. | id | std_id | class_id | is_attend            |
  17. +----+--------+----------+----------------------+
  18. |  1 |   1001 |        1 | 0x01                 |
  19. |  3 |   1001 |        3 | 0x01                 |
  20. |  4 |   1001 |        4 | 0x01                 |
  21. |  5 |   1001 |        5 | 0x01                 |
  22. |  7 |   1002 |        1 | 0x01                 |
  23. |  8 |   1002 |        2 | 0x01                 |
  24. +----+--------+----------+----------------------+
  25. 6 rows in set (0.00 sec)

  27. #遇到问题的SQL写法
  28. mysql> select * from student_attend where is_attend='1';
  29. +----+--------+----------+----------------------+
  30. | id | std_id | class_id | is_attend            |
  31. +----+--------+----------+----------------------+
  32. |  1 |   1001 |        1 | 0x01                 |
  33. |  3 |   1001 |        3 | 0x01                 |
  34. |  4 |   1001 |        4 | 0x01                 |
  35. |  5 |   1001 |        5 | 0x01                 |
  36. |  7 |   1002 |        1 | 0x01                 |
  37. |  8 |   1002 |        2 | 0x01                 |
  38. +----+--------+----------+----------------------+
  39. 6 rows in set (0.00 sec)

  41. mysql> 
复制代码
接下来，我们在字段is_attend上创建索引ix_student_attend_n1,如下所示
  1. create index ix_student_attend_n1 on student_attend(is_attend);
复制代码
然后我们继续测试验证，就能出现我前文所说的情况,如需所示，最后一个SQL，它的返回记录数为0.
  1. mysql> select * from student_attend where is_attend=1;
  2. +----+--------+----------+----------------------+
  3. | id | std_id | class_id | is_attend            |
  4. +----+--------+----------+----------------------+
  5. |  1 |   1001 |        1 | 0x01                 |
  6. |  3 |   1001 |        3 | 0x01                 |
  7. |  4 |   1001 |        4 | 0x01                 |
  8. |  5 |   1001 |        5 | 0x01                 |
  9. |  7 |   1002 |        1 | 0x01                 |
  10. |  8 |   1002 |        2 | 0x01                 |
  11. +----+--------+----------+----------------------+
  12. 6 rows in set (0.00 sec)

  14. mysql> select * from student_attend where is_attend=b'1';
  15. +----+--------+----------+----------------------+
  16. | id | std_id | class_id | is_attend            |
  17. +----+--------+----------+----------------------+
  18. |  1 |   1001 |        1 | 0x01                 |
  19. |  3 |   1001 |        3 | 0x01                 |
  20. |  4 |   1001 |        4 | 0x01                 |
  21. |  5 |   1001 |        5 | 0x01                 |
  22. |  7 |   1002 |        1 | 0x01                 |
  23. |  8 |   1002 |        2 | 0x01                 |
  24. +----+--------+----------+----------------------+
  25. 6 rows in set (0.00 sec)

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

  30. mysql> 
复制代码
其实第一次见到这种情况的时候，我还是有点震惊的，因为在我的观念中，索引只会影响执行计划，不会影响查询结果，但是现在的情况是索引的存在影响了SQL的查询结果。那么为什么会出现这种情况呢？
首先看了一下执行计划，如下所示，从执行计划看，它既没有走全表扫描也没有走索引，仅仅有"message": "no matching row in const table"提示，如果仅仅分析执行计划，我们得不到更多的有用信息
  1. mysql> explain
  2.     -> select * from student_attend where is_attend='1';
  3. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
  4. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |
  5. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
  6. |  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
  7. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
  8. 1 row in set, 1 warning (0.00 sec)

  10. mysql> 
  11. mysql> explain format=json
  12.     -> select * from student_attend where is_attend='1'\G
  13. *************************** 1. row ***************************
  14. EXPLAIN: {
  15.   "query_block": {
  16.     "select_id": 1,
  17.     "message": "no matching row in const table"
  18.   } /* query_block */
  19. }
  20. 1 row in set, 1 warning (0.00 sec)

  22. mysql> show warnings\G
  23. *************************** 1. row ***************************
  24.   Level: Note
  25.    Code: 1003
  26. 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')
  27. 1 row in set (0.00 sec)

  29. mysql> 
复制代码
那么我们使用trace跟踪分析一下优化器如何选择执行计划。看看其详细执行过程，如下所示
  1. mysql> SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
  2. Query OK, 0 rows affected (0.00 sec)

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

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

  10. mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE \G;
  11. *************************** 1. row ***************************
  12.                             QUERY: select * from student_attend where is_attend='1'
  13.                             TRACE: {
  14.   "steps": [
  15.     {
  16.       "join_preparation": {
  17.         "select#": 1,
  18.         "steps": [
  19.           {
  20.             "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')"
  21.           }
  22.         ] /* steps */
  23.       } /* join_preparation */
  24.     },
  25.     {
  26.       "join_optimization": {
  27.         "select#": 1,
  28.         "steps": [
  29.           {
  30.             "condition_processing": {
  31.               "condition": "WHERE",
  32.               "original_condition": "(`student_attend`.`is_attend` = '1')",
  33.               "steps": [
  34.                 {
  35.                   "transformation": "equality_propagation",
  36.                   "resulting_condition": "(`student_attend`.`is_attend` = '1')"
  37.                 },
  38.                 {
  39.                   "transformation": "constant_propagation",
  40.                   "resulting_condition": "(`student_attend`.`is_attend` = '1')"
  41.                 },
  42.                 {
  43.                   "transformation": "trivial_condition_removal",
  44.                   "resulting_condition": "(`student_attend`.`is_attend` = '1')"
  45.                 }
  46.               ] /* steps */
  47.             } /* condition_processing */
  48.           },
  49.           {
  50.             "substitute_generated_columns": {
  51.             } /* substitute_generated_columns */
  52.           },
  53.           {
  54.             "table_dependencies": [
  55.               {
  56.                 "table": "`student_attend`",
  57.                 "row_may_be_null": false,
  58.                 "map_bit": 0,
  59.                 "depends_on_map_bits": [
  60.                 ] /* depends_on_map_bits */
  61.               }
  62.             ] /* table_dependencies */
  63.           },
  64.           {
  65.             "ref_optimizer_key_uses": [
  66.               {
  67.                 "table": "`student_attend`",
  68.                 "field": "is_attend",
  69.                 "equals": "'1'",
  70.                 "null_rejecting": true
  71.               }
  72.             ] /* ref_optimizer_key_uses */
  73.           },
  74.           {
  75.             "rows_estimation": [
  76.               {
  77.                 "table": "`student_attend`",
  78.                 "range_analysis": {
  79.                   "table_scan": {
  80.                     "rows": 10,
  81.                     "cost": 3.35
  82.                   } /* table_scan */,
  83.                   "potential_range_indexes": [
  84.                     {
  85.                       "index": "PRIMARY",
  86.                       "usable": false,
  87.                       "cause": "not_applicable"
  88.                     },
  89.                     {
  90.                       "index": "ix_student_attend_n1",
  91.                       "usable": true,
  92.                       "key_parts": [
  93.                         "is_attend",
  94.                         "id"
  95.                       ] /* key_parts */
  96.                     }
  97.                   ] /* potential_range_indexes */,
  98.                   "setup_range_conditions": [
  99.                     {
  100.                       "impossible_condition": {
  101.                         "cause": "value_out_of_range"
  102.                       } /* impossible_condition */
  103.                     }
  104.                   ] /* setup_range_conditions */,
  105.                   "impossible_range": true
  106.                 } /* range_analysis */,
  107.                 "rows": 0,
  108.                 "cause": "impossible_where_condition"
  109.               }
  110.             ] /* rows_estimation */
  111.           }
  112.         ] /* steps */,
  113.         "empty_result": {
  114.           "cause": "no matching row in const table"
  115.         } /* empty_result */
  116.       } /* join_optimization */
  117.     },
  118.     {
  119.       "join_execution": {
  120.         "select#": 1,
  121.         "steps": [
  122.         ] /* steps */
  123.       } /* join_execution */
  124.     }
  125.   ] /* steps */
  126. }
  127. MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
  128.           INSUFFICIENT_PRIVILEGES: 0
  129. 1 row in set (0.00 sec)

  131. ERROR: 
  132. No query specified

  134. mysql> 
  135. mysql> SET optimizer_trace="enabled=off";
  136. Query OK, 0 rows affected (0.01 sec)
复制代码
从trace的详细信息看，这个过程中发生了隐式转换：下面这个过程就是发生了类型转换
由于发生类型转换过程中(字符串转换为bit类型)遇到了数据截断错误(从value_out_of_range等信息就可以看出),如下截图所示
而优化器应该是根据一定的逻辑判断，得到这个值不存在索引中，从而就判断没有匹配的记录，直接返回空的结果集了，根本不去走扫描全表或走索引查找等操作。
  1.         "empty_result": {
  2.           "cause": "no matching row in const table"
  3.         } /* empty_result */
复制代码
当然这里仅仅是根据trace的信息做的一个判断，如有错误或不谨慎的地方，敬请谅解。毕竟没有深入分析过源码。
那么为什么没有索引的话，SQL语句的结果就是正确的呢？ 难道没有发生类型转换吗？ 难度没有发生数据截断错误吗？那么我们就继续trace跟踪分析看看，如下所示
  1. mysql> drop index ix_student_attend_n1 on student_attend;
  2. Query OK, 0 rows affected (0.03 sec)
  3. Records: 0  Duplicates: 0  Warnings: 0

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

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

  11. mysql> select * from student_attend where is_attend='1';
  12. +----+--------+----------+----------------------+
  13. | id | std_id | class_id | is_attend            |
  14. +----+--------+----------+----------------------+
  15. |  1 |   1001 |        1 | 0x01                 |
  16. |  3 |   1001 |        3 | 0x01                 |
  17. |  4 |   1001 |        4 | 0x01                 |
  18. |  5 |   1001 |        5 | 0x01                 |
  19. |  7 |   1002 |        1 | 0x01                 |
  20. |  8 |   1002 |        2 | 0x01                 |
  21. +----+--------+----------+----------------------+
  22. 6 rows in set (0.00 sec)

  24. mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE \G;
  25. *************************** 1. row ***************************
  26.                             QUERY: select * from student_attend where is_attend='1'
  27.                             TRACE: {
  28.   "steps": [
  29.     {
  30.       "join_preparation": {
  31.         "select#": 1,
  32.         "steps": [
  33.           {
  34.             "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')"
  35.           }
  36.         ] /* steps */
  37.       } /* join_preparation */
  38.     },
  39.     {
  40.       "join_optimization": {
  41.         "select#": 1,
  42.         "steps": [
  43.           {
  44.             "condition_processing": {
  45.               "condition": "WHERE",
  46.               "original_condition": "(`student_attend`.`is_attend` = '1')",
  47.               "steps": [
  48.                 {
  49.                   "transformation": "equality_propagation",
  50.                   "resulting_condition": "(`student_attend`.`is_attend` = '1')"
  51.                 },
  52.                 {
  53.                   "transformation": "constant_propagation",
  54.                   "resulting_condition": "(`student_attend`.`is_attend` = '1')"
  55.                 },
  56.                 {
  57.                   "transformation": "trivial_condition_removal",
  58.                   "resulting_condition": "(`student_attend`.`is_attend` = '1')"
  59.                 }
  60.               ] /* steps */
  61.             } /* condition_processing */
  62.           },
  63.           {
  64.             "substitute_generated_columns": {
  65.             } /* substitute_generated_columns */
  66.           },
  67.           {
  68.             "table_dependencies": [
  69.               {
  70.                 "table": "`student_attend`",
  71.                 "row_may_be_null": false,
  72.                 "map_bit": 0,
  73.                 "depends_on_map_bits": [
  74.                 ] /* depends_on_map_bits */
  75.               }
  76.             ] /* table_dependencies */
  77.           },
  78.           {
  79.             "ref_optimizer_key_uses": [
  80.             ] /* ref_optimizer_key_uses */
  81.           },
  82.           {
  83.             "rows_estimation": [
  84.               {
  85.                 "table": "`student_attend`",
  86.                 "table_scan": {
  87.                   "rows": 10,
  88.                   "cost": 0.25
  89.                 } /* table_scan */
  90.               }
  91.             ] /* rows_estimation */
  92.           },
  93.           {
  94.             "considered_execution_plans": [
  95.               {
  96.                 "plan_prefix": [
  97.                 ] /* plan_prefix */,
  98.                 "table": "`student_attend`",
  99.                 "best_access_path": {
  100.                   "considered_access_paths": [
  101.                     {
  102.                       "rows_to_scan": 10,
  103.                       "access_type": "scan",
  104.                       "resulting_rows": 10,
  105.                       "cost": 1.25,
  106.                       "chosen": true
  107.                     }
  108.                   ] /* considered_access_paths */
  109.                 } /* best_access_path */,
  110.                 "condition_filtering_pct": 100,
  111.                 "rows_for_plan": 10,
  112.                 "cost_for_plan": 1.25,
  113.                 "chosen": true
  114.               }
  115.             ] /* considered_execution_plans */
  116.           },
  117.           {
  118.             "attaching_conditions_to_tables": {
  119.               "original_condition": "(`student_attend`.`is_attend` = '1')",
  120.               "attached_conditions_computation": [
  121.               ] /* attached_conditions_computation */,
  122.               "attached_conditions_summary": [
  123.                 {
  124.                   "table": "`student_attend`",
  125.                   "attached": "(`student_attend`.`is_attend` = '1')"
  126.                 }
  127.               ] /* attached_conditions_summary */
  128.             } /* attaching_conditions_to_tables */
  129.           },
  130.           {
  131.             "finalizing_table_conditions": [
  132.               {
  133.                 "table": "`student_attend`",
  134.                 "original_table_condition": "(`student_attend`.`is_attend` = '1')",
  135.                 "final_table_condition   ": "(`student_attend`.`is_attend` = '1')"
  136.               }
  137.             ] /* finalizing_table_conditions */
  138.           },
  139.           {
  140.             "refine_plan": [
  141.               {
  142.                 "table": "`student_attend`"
  143.               }
  144.             ] /* refine_plan */
  145.           }
  146.         ] /* steps */
  147.       } /* join_optimization */
  148.     },
  149.     {
  150.       "join_execution": {
  151.         "select#": 1,
  152.         "steps": [
  153.         ] /* steps */
  154.       } /* join_execution */
  155.     }
  156.   ] /* steps */
  157. }
  158. MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
  159.           INSUFFICIENT_PRIVILEGES: 0
  160. 1 row in set (0.00 sec)

  162. ERROR: 
  163. No query specified

  165. mysql> SET optimizer_trace="enabled=off";
  166. Query OK, 0 rows affected (0.00 sec)
复制代码
从上面trace信息来看，似乎执行计划先进行全表扫描，然后过滤记录，输出信息里面没有value_out_of_range这类信息，似乎没有发生数据截断。具体步骤跟之前的trace信息有很大不同。具体只看到了下面这些信息，但是更多信息我也看不出来。不清楚底层到底做了啥。
小结

关于bit类型的字段，我们写SQL的时候，不要使用字符串，避免发生隐式类型转换。正确的写法应该是下面这种方式
  1. select * from student_attend where is_attend=b'1';
  2. 或
  3. select * from student_attend where is_attend=1;
复制代码
DBA在给bit类型创建索引的时候也必须谨慎处理，跟开发和Support人员多协商沟通，告知他们可能出现这种情况，因为你可能没法控制开发人员写出这样的SQL。
扫描上面二维码关注我如果你真心觉得文章写得不错，而且对你有所帮助，那就不妨帮忙“推荐"一下，您的“推荐”和”打赏“将是我最大的写作动力！本文版权归作者所有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接.
来源:https://www.cnblogs.com/kerrycode/p/18260767
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！

本帖子中包含更多资源

您需要 登录 才可以下载或查看，没有账号？立即注册

x

上一篇: SQLite vs MySQL vs PostgreSQL对比总结

下一篇: 解读MySQL 8.0数据字典的初始化与启动

发表于 2024-6-21 15:37:42

举报 回复 使用道具

返回列表 发新帖

Archiver|手机版|小黑屋|翼度科技

Copyright © 2001-2020, Tencent Cloud.　Powered by Discuz! X3.4