MySQL?bit類型增加索引后查詢結(jié)果不正確案例解析

更新時間：2024年06月22日 09:29:11 作者：瀟湘隱者

這篇文章主要介紹了MySQL?bit類型增加索引后查詢結(jié)果不正確案例淺析,我們先創(chuàng)建表student_attend,初始化一些數(shù)據(jù),這篇文章的測試環(huán)境為MySQL 8.0.35社區(qū)版,具體內(nèi)容介紹跟隨小編一起學(xué)習(xí)吧

昨天同事遇到的一個案例，這里簡單描述一下：一個表里面有一個bit類型的字段，同事在優(yōu)化相關(guān)SQL的過程中，給這個表的bit類型的字段新增了一個索引，然后測試驗(yàn)證時，居然發(fā)現(xiàn)SQL語句執(zhí)行結(jié)果跟不加索引不一樣。加了索引后，SQL語句沒有查詢出一條記錄，刪除索引后，SQL語句就能查詢出幾十條記錄。下面我們構(gòu)造一個簡單的例子，重現(xiàn)一下這個案例

我們先創(chuàng)建表student_attend，初始化一些數(shù)據(jù)。這篇文章的測試環(huán)境為MySQL 8.0.35社區(qū)版。

CREATE TABLE `student_attend` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '自增編號',
  `std_id` int DEFAULT NULL COMMENT '學(xué)號',
  `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;

如下所示，假設(shè)我們要查詢is_attend=1的所有學(xué)生信息，那么可以有下面三種寫法

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上創(chuàng)建索引ix_student_attend_n1,如下所示

create index ix_student_attend_n1 on student_attend(is_attend);

然后我們繼續(xù)測試驗(yàn)證，就能出現(xiàn)我前文所說的情況,如需所示，最后一個SQL，它的返回記錄數(shù)為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>

其實(shí)第一次見到這種情況的時候，我還是有點(diǎn)震驚的，因?yàn)樵谖业挠^念中，索引只會影響執(zhí)行計(jì)劃，不會影響查詢結(jié)果，但是現(xiàn)在的情況是索引的存在影響了SQL的查詢結(jié)果。那么為什么會出現(xiàn)這種情況呢？

首先看了一下執(zhí)行計(jì)劃，如下所示，從執(zhí)行計(jì)劃看，它既沒有走全表掃描也沒有走索引，僅僅有"message": "no matching row in const table"提示，如果僅僅分析執(zhí)行計(jì)劃，我們得不到更多的有用信息

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跟蹤分析一下優(yōu)化器如何選擇執(zhí)行計(jì)劃?？纯雌湓敿?xì)執(zhí)行過程，如下所示

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的詳細(xì)信息看，這個過程中發(fā)生了隱式轉(zhuǎn)換：下面這個過程就是發(fā)生了類型轉(zhuǎn)換

由于發(fā)生類型轉(zhuǎn)換過程中(字符串轉(zhuǎn)換為bit類型)遇到了數(shù)據(jù)截?cái)噱e誤(從value_out_of_range等信息就可以看出),如下截圖所示

而優(yōu)化器應(yīng)該是根據(jù)一定的邏輯判斷，得到這個值不存在索引中，從而就判斷沒有匹配的記錄，直接返回空的結(jié)果集了，根本不去走掃描全表或走索引查找等操作。

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

當(dāng)然這里僅僅是根據(jù)trace的信息做的一個判斷，如有錯誤或不謹(jǐn)慎的地方，敬請諒解。畢竟沒有深入分析過源碼。

那么為什么沒有索引的話，SQL語句的結(jié)果就是正確的呢？難道沒有發(fā)生類型轉(zhuǎn)換嗎？難度沒有發(fā)生數(shù)據(jù)截?cái)噱e誤嗎？那么我們就繼續(xù)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信息來看，似乎執(zhí)行計(jì)劃先進(jìn)行全表掃描，然后過濾記錄，輸出信息里面沒有value_out_of_range這類信息，似乎沒有發(fā)生數(shù)據(jù)截?cái)?。具體步驟跟之前的trace信息有很大不同。具體只看到了下面這些信息，但是更多信息我也看不出來。不清楚底層到底做了啥。

小結(jié)

關(guān)于bit類型的字段，我們寫SQL的時候，不要使用字符串，避免發(fā)生隱式類型轉(zhuǎn)換。正確的寫法應(yīng)該是下面這種方式

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

DBA在給bit類型創(chuàng)建索引的時候也必須謹(jǐn)慎處理，跟開發(fā)和Support人員多協(xié)商溝通，告知他們可能出現(xiàn)這種情況，因?yàn)槟憧赡軟]法控制開發(fā)人員寫出這樣的SQL。

到此這篇關(guān)于MySQL bit類型增加索引后查詢結(jié)果不正確案例淺析的文章就介紹到這了,更多相關(guān)MySQL bit類型增加索引查詢結(jié)果不正確內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

最新国产好看的视频,伊人天堂AV在线,国产Aaaaaa视频,蜜臀视频在线观看一区,人妻av色图,密臀久久久精品影片,青青视频免费观看毛片,久草在线观看视,国产三级精品色情在线

MySQL?bit類型增加索引后查詢結(jié)果不正確案例解析

小結(jié)

相關(guān)文章

最新評論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具