昨天同事遇到的一個案例，這裡簡單描述一下：一個表裡面有一個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。