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類型的字段新增了一個索引，然后測試驗證時，居然發(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ù)測試驗證，就能出現(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>
其實第一次見到這種情況的時候，我還是有點震驚的，因為在我的觀念中，索引只會影響執(zhí)行計劃，不會影響查詢結(jié)果，但是現(xiàn)在的情況是索引的存在影響了SQL的查詢結(jié)果。那么為什么會出現(xiàn)這種情況呢？
首先看了一下執(zhí)行計劃，如下所示，從執(zhí)行計劃看，它既沒有走全表掃描也沒有走索引，僅僅有"message": "no matching row in const table"提示，如果僅僅分析執(zhí)行計劃，我們得不到更多的有用信息
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í)行計劃。看看其詳細執(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的詳細信息看，這個過程中發(fā)生了隱式轉(zhuǎn)換：下面這個過程就是發(fā)生了類型轉(zhuǎn)換
由于發(fā)生類型轉(zhuǎn)換過程中(字符串轉(zhuǎn)換為bit類型)遇到了數(shù)據(jù)截斷錯誤(從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的信息做的一個判斷，如有錯誤或不謹慎的地方，敬請諒解。畢竟沒有深入分析過源碼。
那么為什么沒有索引的話，SQL語句的結(jié)果就是正確的呢？ 難道沒有發(fā)生類型轉(zhuǎn)換嗎？ 難度沒有發(fā)生數(shù)據(jù)截斷錯誤嗎？那么我們就繼續(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í)行計劃先進行全表掃描，然后過濾記錄，輸出信息里面沒有value_out_of_range這類信息，似乎沒有發(fā)生數(shù)據(jù)截斷。具體步驟跟之前的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)建索引的時候也必須謹慎處理，跟開發(fā)和Support人員多協(xié)商溝通，告知他們可能出現(xiàn)這種情況，因為你可能沒法控制開發(fā)人員寫出這樣的SQL。
到此這篇關(guān)于MySQL bit類型增加索引后查詢結(jié)果不正確案例淺析的文章就介紹到這了,更多相關(guān)MySQL bit類型增加索引查詢結(jié)果不正確內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
MySQL是一款安全、跨平臺、高效的，并與PHP、Java等主流編程語言緊密結(jié)合的數(shù)據(jù)庫系統(tǒng),下面這篇文章主要給大家介紹了關(guān)于MySQL5.5版本安裝與安裝失敗詳細講解的相關(guān)資料,需要的朋友可以參考下2023-03-03
mysql的日期和時間函數(shù) 這里是一個使用日期函數(shù)的例子。下面的查詢選擇所有 date_col 值在最后 30 天內(nèi)的記錄。2008-04-04
mysql實現(xiàn)查詢最接近的記錄數(shù)據(jù)示例
這篇文章主要介紹了mysql實現(xiàn)查詢最接近的記錄數(shù)據(jù),涉及mysql查詢相關(guān)的時間轉(zhuǎn)換、排序等相關(guān)操作技巧,需要的朋友可以參考下2018-07-07
這篇文章主要介紹了MySQL?5.7版本中二進制日志（bin_log）的配置和使用,文中通過圖文及代碼介紹的非常詳細,需要的朋友可以參考下2025-02-02
這篇文章介紹了三種查看連接MYSQL數(shù)據(jù)庫的IP信息方法，方法簡單實用,需要的朋友可以參考下2015-07-07
Mysql作為一款開元的免費關(guān)系型數(shù)據(jù)庫,用戶基礎(chǔ)非常龐大,下面這篇文章主要給大家介紹了關(guān)于mysql日期類型轉(zhuǎn)換的相關(guān)資料,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下2022-08-08
這篇文章給大家分享了mysql 5.7.19免安裝版在安裝過程中遇到的一些問題，以前有mysql服務(wù)的話 需要去停掉mysql服務(wù)。具體內(nèi)容介紹大家參考下本文2017-08-08
解決Mysql?Binlog文件太大導(dǎo)致無法解析問題
這篇文章主要為大家介紹了解決Mysql?Binlog文件太大導(dǎo)致無法解析問題，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪2023-11-11
在本篇文章里小編給各位分享了是關(guān)于刪除mysql服務(wù)的具體方法，需要的朋友們可以學(xué)習(xí)下。2020-07-07
想去查看windows系統(tǒng)下,MySQL數(shù)據(jù)庫的配置文件,由于距離上一次查看時間太久,每次查看都要找很久在什么位置,所以本文給大家介紹了mysql查找配置文件位置的兩種方法,需要的朋友可以參考下2024-09-09