【GreatSQL最佳化器-01】const_table
一、const_table介紹
GreatSQL的最佳化器主要用JOIN類來進行處理SQL語句的,JOIN類有以下四個table數量相關的成員變數。其中const_tables是optimize最開始就檢查並且標識的,因為這樣可以把記錄最少的表放在執行計劃的第一步,在後面的執行計劃裡面這些const tables是不參與迴圈遍歷和計算的,因此可以減少很多開銷。
計數名稱 | 說明 | 哪個函式進行累加 |
---|---|---|
tables | 該查詢語句的所有表的數量,包含物化表和臨時表 | JOIN::get_best_combination() |
primary_tables | 該查詢語句的主要表的數量,不包含物化表 | JOIN::get_best_combination() |
const_tables | 該查詢語句中只有0行或者1行的表數量 | JOIN::extract_const_tables和JOIN::extract_func_dependent_tables |
tmp_tables | 該查詢語句中臨時表數量 | JOIN::make_tmp_tables_info() |
下面用一個簡單的例子來說明 const_table 是什麼。
greatsql> CREATE TABLE t1 (c1 int primary key, c2 varchar(32),date1 datetime);
greatsql> INSERT INTO t1 VALUES (1,'aaa','2021-03-25 16:44:00.123456'),(2,'bbb','2022-03-25 16:44:00.123456'),(3,'ccc','2023-03-25 16:44:00.123456');
# 這裡看到以下的type型別是const,說明在最佳化器看來這是一張const table
greatsql> EXPLAIN SELECT * FROM t1 WHERE c1=1 AND c1<10;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (3.60 sec)
# 這句sql語句最後被處理成以下的語句了,可以看到條件語句被提到前面當做列,最後的條件變為where true了。
greatsql> SHOW WARNINGS;
+-------+------+-------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select '1' AS `c1`,'aaa' AS `c2`,'2021-03-25 16:44:00' AS `date1` from `db1`.`t1` where true |
+-------+------+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
二、const_table標識
const_table的尋找和標識在函式JOIN::extract_const_tables和JOIN::extract_func_dependent_tables進行處理,這兩個函式的功能各有不同。用上面的例子來說明。
bool JOIN::make_join_plan() {
if (!(query_block->active_options() & OPTION_NO_CONST_TABLES)) {
// Detect tables that are const (0 or 1 row) and read their contents.
//根據表的統計資訊table->file->stats.records直接尋找該值為0或者1的表,並且表的cached_table_flags必須為HA_STATS_RECORDS_IS_EXACT,只有引擎是MyISAM和memory才有這個標誌
if (extract_const_tables()) return true;
// Detect tables that are functionally dependent on const values.
// 迴圈所有剩下的沒有被標識的表,找出有索引或者有表依賴的表來標識
if (extract_func_dependent_tables()) return true;
}
}
bool JOIN::extract_func_dependent_tables() {
do {
for (JOIN_TAB **pos = best_ref + const_tables; *pos; pos++) {
一、遍歷每張出現的表,尋找表裡已經建立的索引。
二、判斷表有join條件的情況,本次不涉及
三、判斷表有表依賴的情況,本次不涉及
四、檢查表是否可以透過key讀取或表僅使用 const ref。
這裡需要排除以下表:
1. 全文搜尋,或
2. 巢狀外連線的一部分,或
3. 半連線的一部分,或
4. 具有大開銷的外連線條件。
5. 被 const 表最佳化的處理程式阻止。
6. 將不會被使用,通常是因為它們是流式傳輸的而不是物化的(請參閱 Query_expression::can_materialize_directly_into_result())。
7. 位於完整連線的兩側
// 如果找到表有索引,這裡用到的keyuse在之前JOIN::update_ref_and_keys()已經獲取到。
if (eq_part.is_prefix(table->key_info[key].user_defined_key_parts)) {
// 該索引是唯一索引
if (table->key_info[key].flags & HA_NOSAME) {
if (const_ref == eq_part) {
// 標識該表是const table
mark_const_table(tab, start_keyuse);
// 改變table的狀態為const
join_read_const_table(tab, positions + const_tables - 1);
}
}
}
}
} while ((const_table_map & found_ref) && ref_changed);
以下是實際使用的時候表掃描方式型別彙總,其中JT_CONST和JT_SYSTEM就是我們本章要說明的。JT_SYSTEM型別,這個需要跟JT_CONST區分開,JT_SYSTEM主要是擴充套件表而不是物理表,JT_SYSTEM表沒有索引。JT_SYSTEM型別也會被計入const_tables,詳情見下面。
join_type訪問方法的型別 | 說明 |
---|---|
JT_UNKNOWN | 無效 |
JT_SYSTEM | 表只有一行,比如select * from (select 1) |
JT_CONST | 表最多隻有一行滿足,比如WHERE table.pk = 3 |
JT_EQ_REF | '=符號用在唯一索引 |
JT_REF | '=符號用在非唯一索引 |
JT_ALL | 全表掃描 |
JT_RANGE | 範圍掃描 |
JT_INDEX_SCAN | 索引掃描 |
JT_FT | Fulltext索引掃描 |
JT_REF_OR_NULL | 包含null值,比如"WHERE col = ... OR col IS NULL |
JT_INDEX_MERGE | 一張表執行多次範圍掃描最後合併結果 |
-- 下面的select 1表型別就是system而不是const。但是select 1這張表的數量也會計入const_tables
greatsql> EXPLAIN SELECT * FROM (SELECT 1);
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
| 1 | PRIMARY | <derived2> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
2 rows in set, 1 warning (12.33 sec)
三、實際例子說明
接下來看幾個例子來說明上面的程式碼。
greatsql> CREATE TABLE t2 (c1 int, c3 varchar(32));
greatsql> CREATE index idx1 ON t2(c1);
greatsql> INSERT INTO t2 VALUES (1,'aaa'),(2,'bbb');
-- 非唯一索引不是const table
greatsql> EXPLAIN SELECT * FROM t2 WHERE c1=1 AND c1<10;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t2 | NULL | ref | idx1 | idx1 | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (3.84 sec)
-- 改變c1為primary key,看一下結果。
-- between條件也是唯一條件,因此是const table
-- 這裡有個約束條件:BETWEEN的上下界條件值必須一樣,才會被判定為const,否則是range
greatsql> EXPLAIN SELECT * FROM t1 WHERE c1 BETWEEN 1 AND 1 AND c1<10;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
-- value in後面的值即使最小值和最大值相同也走的範圍掃描,不被判定為const table
greatsql> EXPLAIN SELECT * FROM t1 WHERE c1 IN (1,1) AND c1<10;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 1 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
-- 下面的表只有一行,但是由於引擎不是MyISAM或者memory而不被判定為const table
greatsql> CREATE TABLE t3 AS SELECT 1 AS c1;
greatsql> EXPLAIN SELECT * FROM t3 WHERE c1=1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t3 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (1.91 sec)
-- 下面的表只有一行,由於引擎是MEMORY而被判定為const table
greatsql> CREATE TABLE t5 engine=memory AS SELECT 1 AS c1;
greatsql> EXPLAIN SELECT * FROM t5 WHERE c1=1;
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | t5 | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (2.70 sec)
-- 兩張表進行join的時候,即使這裡t1的順序在後面,因為在最佳化器裡面被判定為const table,所以順序被提前到第一位。
greatsql> EXPLAIN SELECT t1.c1,t1.c2 FROM t2,t1 WHERE t1.c1=1 AND t1.c1<10;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 1 | SIMPLE | t2 | NULL | index | NULL | idx1 | 4 | NULL | 1 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (2.70 sec)
greatsql> EXPLAIN FORMAT=TREE SELECT t1.c1,t1.c2 FROM t2,t1 WHERE t1.c1=1 AND t1.c1<10;
+-----------------------------------------------------+
| EXPLAIN |
+-----------------------------------------------------+
| -> Index scan on t2 using idx1 (cost=0.35 rows=1) |
+-----------------------------------------------------+
1 row in set (40.27 sec)
-- 透過列印堆疊檢視當前最佳的排列順序,可以看到表t1確實被提到第一個了。
Thread 56 "connection" hit Breakpoint 16, JOIN::get_best_combination (this=0x7fff14c6d0f0)
at /sql/sql_optimizer.cc:3663
3663 for (Table_ref *sj_nest : query_block->sj_nests) {
(gdb) p best_ref[0]->table_ref->table_name
$99 = 0x7fff14c5d660 "t1"
(gdb) p best_ref[1]->table_ref->table_name
$100 = 0x7fff14c5d538 "t2"
四、總結
從上面最佳化器最早的步驟我們認識了const table的定義和判定方法,可以發現實際運用中const table是可以提高查詢速度的。這裡面透過唯一索引來定位查詢資料是最快的就是因為被判定為const table,在後面的實踐裡面發現 =
(等值查詢)、特殊情況下的 BETWEEN
的時候都可以被認為是const table,但是 IN
卻不行,因此實際運用的時候儘量不要用IN
條件,而多用等值條件 和 特殊條件下的BETWEEN
來查詢,最好是隻用等值條件。
Enjoy GreatSQL 😃
關於 GreatSQL
GreatSQL是適用於金融級應用的國內自主開源資料庫,具備高效能、高可靠、高易用性、高安全等多個核心特性,可以作為MySQL或Percona Server的可選替換,用於線上生產環境,且完全免費併相容MySQL或Percona Server。
相關連結: GreatSQL社群 Gitee GitHub Bilibili
GreatSQL社群:
社群部落格有獎徵稿詳情:https://greatsql.cn/thread-100-1-1.html
技術交流群:
微信:掃碼新增
GreatSQL社群助手
微信好友,傳送驗證資訊加群
。