ToB企服应用市场:ToB评测及商务社交产业平台

标题: 【GreatSQL优化器-01】const_table [打印本页]
作者: 小小小幸运    时间: 2024-11-8 11:32
标题: 【GreatSQL优化器-01】const_table
【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_tablestmp_tables该查询语句中临时表数目JOIN::make_tmp_tables_info()下面用一个简单的例子来说明 const_table 是什么。
  1. greatsql> CREATE TABLE t1 (c1 int primary key, c2 varchar(32),date1 datetime);
  2. 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');
  4. # 这里看到以下的type类型是const，说明在优化器看来这是一张const table
  5. greatsql> EXPLAIN SELECT * FROM t1 WHERE c1=1 AND c1<10;
  6. +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
  7. | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
  8. +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
  9. |  1 | SIMPLE      | t1    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
  10. +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
  11. 1 row in set, 1 warning (3.60 sec)
  13. # 这句sql语句最后被处理成以下的语句了，可以看到条件语句被提到前面当做列，最后的条件变为where true了。
  14. greatsql> SHOW WARNINGS;
  15. +-------+------+-------------------------------------------------------------------------------------------------------------+
  16. | Level | Code | Message                                                                                                     |
  17. +-------+------+-------------------------------------------------------------------------------------------------------------+
  18. | Note  | 1003 | /* select#1 */ select '1' AS `c1`,'aaa' AS `c2`,'2021-03-25 16:44:00' AS `date1` from `db1`.`t1` where true |
  19. +-------+------+-------------------------------------------------------------------------------------------------------------+
  20. 1 row in set (0.00 sec)
复制代码
二、const_table标识

const_table的寻找和标识在函数JOIN::extract_const_tables和JOIN::extract_func_dependent_tables进行处置惩罚，这两个函数的功能各有差别。用上面的例子来说明。
  1. bool JOIN::make_join_plan() {
  2.   if (!(query_block->active_options() & OPTION_NO_CONST_TABLES)) {
  3.     // Detect tables that are const (0 or 1 row) and read their contents.
  4.     //根据表的统计信息table->file->stats.records直接寻找该值为0或者1的表，并且表的cached_table_flags必须为HA_STATS_RECORDS_IS_EXACT，只有引擎是MyISAM和memory才有这个标志
  5.     if (extract_const_tables()) return true;
  7.     // Detect tables that are functionally dependent on const values.
  8.     // 循环所有剩下的没有被标识的表，找出有索引或者有表依赖的表来标识
  9.     if (extract_func_dependent_tables()) return true;
  10.   }
  11. }
  13. bool JOIN::extract_func_dependent_tables() {
  14. do {
  15.   for (JOIN_TAB **pos = best_ref + const_tables; *pos; pos++) {
  16.     一、遍历每张出现的表，寻找表里已经创建的索引。
  17.     二、判断表有join条件的情况，本次不涉及
  18.     三、判断表有表依赖的情况，本次不涉及
  19.     四、检查表是否可以通过key读取或表仅使用 const ref。
  20.     这里需要排除以下表：
  21.     1. 全文搜索，或
  22.     2. 嵌套外连接的一部分，或
  23.     3. 半连接的一部分，或
  24.     4. 具有大开销的外连接条件。
  25.     5. 被 const 表优化的处理程序阻止。
  26.     6. 将不会被使用，通常是因为它们是流式传输的而不是物化的（请参阅 Query_expression::can_materialize_directly_into_result()）。
  27.     7. 位于完整连接的两侧
  28.     // 如果找到表有索引，这里用到的keyuse在之前JOIN::update_ref_and_keys()已经获取到。
  29.     if (eq_part.is_prefix(table->key_info[key].user_defined_key_parts)) {
  30.       // 该索引是唯一索引
  31.       if (table->key_info[key].flags & HA_NOSAME) {
  32.         if (const_ref == eq_part) {
  33.           // 标识该表是const table
  34.           mark_const_table(tab, start_keyuse);
  35.           // 改变table的状态为const
  36.           join_read_const_table(tab, positions + const_tables - 1);
  37.         }
  38.       }
  39.     }
  40.   }
  41. } 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 = 3JT_EQ_REF'=符号用在唯一索引JT_REF'=符号用在非唯一索引JT_ALL全表扫描JT_RANGE范围扫描JT_INDEX_SCAN索引扫描JT_FTFulltext索引扫描JT_REF_OR_NULL包含null值，比如"WHERE col = ... OR col IS NULLJT_INDEX_MERGE一张表实行多次范围扫描最后合并结果
  1. -- 下面的select 1表类型就是system而不是const。但是select 1这张表的数量也会计入const_tables
  2. greatsql> EXPLAIN SELECT * FROM (SELECT 1);
  3. +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
  4. | id | select_type | table      | partitions | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
  5. +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
  6. |  1 | PRIMARY     | <derived2> | NULL       | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL           |
  7. |  2 | DERIVED     | NULL       | NULL       | NULL   | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
  8. +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
  9. 2 rows in set, 1 warning (12.33 sec)
复制代码
三、实际例子说明

接下来看几个例子来说明上面的代码。
  1. greatsql> CREATE TABLE t2 (c1 int, c3 varchar(32));
  2. greatsql> CREATE index idx1 ON t2(c1);
  3. greatsql> INSERT INTO t2 VALUES (1,'aaa'),(2,'bbb');
  5. -- 非唯一索引不是const table
  6. greatsql> EXPLAIN SELECT * FROM t2 WHERE c1=1 AND c1<10;
  7. +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
  8. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
  9. +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
  10. |  1 | SIMPLE      | t2    | NULL       | ref  | idx1          | idx1 | 4       | const |    1 |   100.00 | NULL  |
  11. +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
  12. 1 row in set, 1 warning (3.84 sec)
  14. -- 改变c1为primary key，看一下结果。
  15. -- between条件也是唯一条件，因此是const table
  16. -- 这里有个约束条件：BETWEEN的上下界条件值必须一样，才会被判定为const，否则是range
  17. greatsql> EXPLAIN SELECT * FROM t1 WHERE c1 BETWEEN 1 AND 1 AND c1<10;
  18. +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
  19. | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
  20. +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
  21. |  1 | SIMPLE      | t1    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
  22. +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
  23. 1 row in set, 1 warning (0.00 sec)
  25. -- value in后面的值即使最小值和最大值相同也走的范围扫描，不被判定为const table
  26. greatsql> EXPLAIN SELECT * FROM t1 WHERE c1 IN (1,1) AND c1<10;
  27. +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  28. | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
  29. +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  30. |  1 | SIMPLE      | t1    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    1 |   100.00 | Using where |
  31. +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  32. 1 row in set, 1 warning (0.01 sec)
  34. -- 下面的表只有一行，但是由于引擎不是MyISAM或者memory而不被判定为const table
  35. greatsql> CREATE TABLE t3 AS SELECT 1 AS c1;
  36. greatsql> EXPLAIN SELECT * FROM t3 WHERE c1=1;
  37. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
  38. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
  39. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
  40. |  1 | SIMPLE      | t3    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
  41. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
  42. 1 row in set, 1 warning (1.91 sec)
  44. -- 下面的表只有一行，由于引擎是MEMORY而被判定为const table
  45. greatsql> CREATE TABLE t5 engine=memory AS SELECT 1 AS c1;
  46. greatsql> EXPLAIN SELECT * FROM t5 WHERE c1=1;
  47. +----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+
  48. | id | select_type | table | partitions | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
  49. +----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+
  50. |  1 | SIMPLE      | t5    | NULL       | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
  51. +----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+
  52. 1 row in set, 1 warning (2.70 sec)
  54. -- 两张表进行join的时候，即使这里t1的顺序在后面，因为在优化器里面被判定为const table，所以顺序被提前到第一位。
  55. greatsql> EXPLAIN SELECT t1.c1,t1.c2 FROM t2,t1 WHERE t1.c1=1 AND t1.c1<10;
  56. +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
  57. | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
  58. +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
  59. |  1 | SIMPLE      | t1    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL        |
  60. |  1 | SIMPLE      | t2    | NULL       | index | NULL          | idx1    | 4       | NULL  |    1 |   100.00 | Using index |
  61. +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
  62. 2 rows in set, 1 warning (2.70 sec)
  64. greatsql> EXPLAIN FORMAT=TREE SELECT t1.c1,t1.c2 FROM t2,t1 WHERE t1.c1=1 AND t1.c1<10;
  65. +-----------------------------------------------------+
  66. | EXPLAIN                                             |
  67. +-----------------------------------------------------+
  68. | -> Index scan on t2 using idx1  (cost=0.35 rows=1)  |
  69. +-----------------------------------------------------+
  70. 1 row in set (40.27 sec)
  72. -- 通过打印堆栈查看当前最佳的排列顺序，可以看到表t1确实被提到第一个了。
  73. Thread 56 "connection" hit Breakpoint 16, JOIN::get_best_combination (this=0x7fff14c6d0f0)
  74.     at /sql/sql_optimizer.cc:3663
  75. 3663          for (Table_ref *sj_nest : query_block->sj_nests) {
  76. (gdb) p best_ref[0]->table_ref->table_name
  77. $99 = 0x7fff14c5d660 "t1"
  78. (gdb) p best_ref[1]->table_ref->table_name
  79. $100 = 0x7fff14c5d538 "t2"
复制代码
四、总结

从上面优化器最早的步骤我们熟悉了const table的定义和判定方法，可以发现实际运用中const table是可以进步查询速率的。这里面通过唯一索引来定位查找数据是最快的就是由于被判定为const table，在后面的实践里面发现 =（等值查询）、特别情况下的 BETWEEN 的时候都可以被以为是const table，但是 IN 却不可，因此实际运用的时候尽量不要用IN条件，而多用等值条件 和 特别条件下的BETWEEN来查询，最好是只用等值条件。

Enjoy GreatSQL
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。



欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/) Powered by Discuz! X3.4