发帖
翼度科技»论坛 › 编程开发 › mysql › 查看内容

【GreatSQL优化器-02】索引和Sargable谓词

无刺鱼米饭

4

主题

4

帖子

12

积分

新手上路

Rank: 1

积分
12
显示全部楼层
【GreatSQL优化器-02】索引和Sargable谓词

一、Sargable谓词介绍


GreatSQL的优化器在有过滤条件的时候，需要先把条件按照是否有索引来进行区分，可以用索引来加速查询的条件称为Sargable，其中 arge 来源于 Search Argument（搜索参数）的首字母拼成的"SARG"。GreatSQL用keyuse_array索引数组和Sargables数组来储存Sargable谓词，其中Sargable数组是对keyuse_array的补充使用，比如``a, =,  SELECT * FROM t1 where c1 in (SELECT cc1 FROM t2) and  c1 >c2;+----+------+---------------------+| c1 | c2   | date1               |+----+------+---------------------+|  2 |    1 | 2022-03-26 16:44:00 |+----+------+---------------------+> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;| SELECT * FROM t1 where c1 in (SELECT cc1 FROM t2) and  c1 >c2 | {  "steps": [    {// 1、sql语句转换成更快执行的语句      "join_preparatiON": {        "SELECT#": 1,        "steps": [          {            "join_preparatiON": {              "SELECT#": 2,              "steps": [                {                  "expanded_query": "/* SELECT#2 */ SELECT `t2`.`cc1` FROM `t2`"                }              ]            }          },          {            "expanded_query": "/* SELECT#1 */ SELECT `t1`.`c1` AS `c1`,`t1`.`c2` AS `c2`,`t1`.`date1` AS `date1` FROM `t1` where (`t1`.`c1` in (/* SELECT#2 */ SELECT `t2`.`cc1` FROM `t2`) and (`t1`.`c1` > `t1`.`c2`))"          },          {            "transformatiON": {              "SELECT#": 2,              "FROM": "IN (SELECT)",              "to": "semijoin",              "chosen": true,              "transformatiON_to_semi_join": {                "subquery_predicate": "`t1`.`c1` in (/* SELECT#2 */ SELECT `t2`.`cc1` FROM `t2`)",                "embedded in": "WHERE",                "semi-join cONditiON": "(`t1`.`c1` = `t2`.`cc1`)",                "decorrelated_predicates": [                  {                    "outer": "`t1`.`c1`",                    "inner": "`t2`.`cc1`"                  }                ]              }            }          },          {            "transformatiONs_to_nested_joins": {              "transformatiONs": [                "semijoin"              ],// 这个sql语句被转换成以下最终的语句，可以发现最后以semi join的形式查询的。              "expanded_query": "/* SELECT#1 */ SELECT `t1`.`c1` AS `c1`,`t1`.`c2` AS `c2`,`t1`.`date1` AS `date1` FROM `t1` semi join (`t2`) where ((`t1`.`c1` > `t1`.`c2`) and (`t1`.`c1` = `t2`.`cc1`))"            }          }        ]      }    },    {// 2、优化器执行计划生成      "join_optimizatiON": {        "SELECT#": 1,        "steps": [          {            "cONditiON_processing": {              "cONditiON": "WHERE",              "original_cONditiON": "((`t1`.`c1` > `t1`.`c2`) and (`t1`.`c1` = `t2`.`cc1`))",              "steps": [                {                  "transformatiON": "equality_propagatiON",                  "resulting_cONditiON": "((`t1`.`c1` > `t1`.`c2`) and multiple equal(`t1`.`c1`, `t2`.`cc1`))"                },                {                  "transformatiON": "cONstant_propagatiON",                  "resulting_cONditiON": "((`t1`.`c1` > `t1`.`c2`) and multiple equal(`t1`.`c1`, `t2`.`cc1`))"                },                {                  "transformatiON": "trivial_cONditiON_removal",                  "resulting_cONditiON": "((`t1`.`c1` > `t1`.`c2`) and multiple equal(`t1`.`c1`, `t2`.`cc1`))"                }              ]            }          },          {            "substitute_generated_columns": {            }          },          {// 表依赖，因为是两张表做join，因此这里显示了2张表            "TABLE_dependencies": [              {                "TABLE": "`t1`",                "row_may_be_null": false,                "map_bit": 0,                "depends_ON_map_bits": [                ]              },              {                "TABLE": "`t2`",                "row_may_be_null": false,                "map_bit": 1,                "depends_ON_map_bits": [                ]              }            ]          },          {            "ref_optimizer_key_uses": [ //这里就是keyuse_array的结果，实际用到了两个索引，c1和cc2的唯一索引              {                "TABLE": "`t1`",                "field": "c1",                "equals": "`t2`.`cc1`",                "null_rejecting": true              },              {                "TABLE": "`t2`",                "field": "cc1",                "equals": "`t1`.`c1`",                "null_rejecting": true              }            ]          },      // 通过查看系统表可以查看到keyuse_array信息，但是Sargables数组信息没有显示。// 这个通过debug代码发现过程中提取出了一个谓词组，就是c1>c2,field值为cc1列，arg_value值为c2列，num_VALUES为所有Sargable谓词数量，这里为1。// 这个Sargable数组信息在后面函数update_sargable_FROM_cONst补充检查是否可以使用这里面的cc1索引加速查询。struct SARGABLE_PARAM {  Field *field;     // t2的cc1列  Item **arg_value; // t1的c2列  uINT num_VALUES;  // 值为1，因为数组只有一个值};[/code]二、update_ref_and_keys代码执行过程

update_ref_and_keys函数里面通过add_key_fields把查询sql的cond条件包含的索引信息添加到Key_use_array，注意只有等于的条件才会通过add_key_field添加key_field。cond条件分为两种：FUNC_ITEM和COND_ITEM，其中and_level用于在merge_key_fields时候把用不到的key_field删掉。
条件类型说明FUNC_ITEM只由一个条件组成COND_ITEM由若干个 AND 和 OR 连接起来的条件，包含Item_cond_or和Item_cond_and两种 COND_AND_FUNC：同一个and条件的and_level不变 COND_OR_FUNC：处理前and_level需要自增实际代码执行过程：
  1. CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT,date1 DATETIME);
  2. INSERT INTO t1 VALUES (1,10,'2021-03-25 16:44:00.123456'),(2,1,'2022-03-26 16:44:00.123456'),(3,4,'2023-03-27 16:44:00.123456');
  3. CREATE TABLE t2 (cc1 INT PRIMARY KEY, cc2 INT);
  4. INSERT INTO t2 VALUES (1,3),(2,1),(3,2),(4,3);
  5. CREATE INDEX idx1 ON t1(c2);
  6. CREATE INDEX idx2 ON t1(c2,date1);
  7. CREATE INDEX idx2_1 ON t2(cc2);
  9. SET optimizer_trace = 'enabled=ON' ;
  10. greatsql> SELECT * FROM t1 where c1 in (SELECT cc1 FROM t2) and  c1 >c2;
  11. +----+------+---------------------+
  12. | c1 | c2   | date1               |
  13. +----+------+---------------------+
  14. |  2 |    1 | 2022-03-26 16:44:00 |
  15. +----+------+---------------------+
  16. > SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
  17. | SELECT * FROM t1 where c1 in (SELECT cc1 FROM t2) and  c1 >c2 | {
  18.   "steps": [
  19.     {// 1、sql语句转换成更快执行的语句
  20.       "join_preparatiON": {
  21.         "SELECT#": 1,
  22.         "steps": [
  23.           {
  24.             "join_preparatiON": {
  25.               "SELECT#": 2,
  26.               "steps": [
  27.                 {
  28.                   "expanded_query": "/* SELECT#2 */ SELECT `t2`.`cc1` FROM `t2`"
  29.                 }
  30.               ]
  31.             }
  32.           },
  33.           {
  34.             "expanded_query": "/* SELECT#1 */ SELECT `t1`.`c1` AS `c1`,`t1`.`c2` AS `c2`,`t1`.`date1` AS `date1` FROM `t1` where (`t1`.`c1` in (/* SELECT#2 */ SELECT `t2`.`cc1` FROM `t2`) and (`t1`.`c1` > `t1`.`c2`))"
  35.           },
  36.           {
  37.             "transformatiON": {
  38.               "SELECT#": 2,
  39.               "FROM": "IN (SELECT)",
  40.               "to": "semijoin",
  41.               "chosen": true,
  42.               "transformatiON_to_semi_join": {
  43.                 "subquery_predicate": "`t1`.`c1` in (/* SELECT#2 */ SELECT `t2`.`cc1` FROM `t2`)",
  44.                 "embedded in": "WHERE",
  45.                 "semi-join cONditiON": "(`t1`.`c1` = `t2`.`cc1`)",
  46.                 "decorrelated_predicates": [
  47.                   {
  48.                     "outer": "`t1`.`c1`",
  49.                     "inner": "`t2`.`cc1`"
  50.                   }
  51.                 ]
  52.               }
  53.             }
  54.           },
  55.           {
  56.             "transformatiONs_to_nested_joins": {
  57.               "transformatiONs": [
  58.                 "semijoin"
  59.               ],// 这个sql语句被转换成以下最终的语句，可以发现最后以semi join的形式查询的。
  60.               "expanded_query": "/* SELECT#1 */ SELECT `t1`.`c1` AS `c1`,`t1`.`c2` AS `c2`,`t1`.`date1` AS `date1` FROM `t1` semi join (`t2`) where ((`t1`.`c1` > `t1`.`c2`) and (`t1`.`c1` = `t2`.`cc1`))"
  61.             }
  62.           }
  63.         ]
  64.       }
  65.     },
  66.     {// 2、优化器执行计划生成
  67.       "join_optimizatiON": {
  68.         "SELECT#": 1,
  69.         "steps": [
  70.           {
  71.             "cONditiON_processing": {
  72.               "cONditiON": "WHERE",
  73.               "original_cONditiON": "((`t1`.`c1` > `t1`.`c2`) and (`t1`.`c1` = `t2`.`cc1`))",
  74.               "steps": [
  75.                 {
  76.                   "transformatiON": "equality_propagatiON",
  77.                   "resulting_cONditiON": "((`t1`.`c1` > `t1`.`c2`) and multiple equal(`t1`.`c1`, `t2`.`cc1`))"
  78.                 },
  79.                 {
  80.                   "transformatiON": "cONstant_propagatiON",
  81.                   "resulting_cONditiON": "((`t1`.`c1` > `t1`.`c2`) and multiple equal(`t1`.`c1`, `t2`.`cc1`))"
  82.                 },
  83.                 {
  84.                   "transformatiON": "trivial_cONditiON_removal",
  85.                   "resulting_cONditiON": "((`t1`.`c1` > `t1`.`c2`) and multiple equal(`t1`.`c1`, `t2`.`cc1`))"
  86.                 }
  87.               ]
  88.             }
  89.           },
  90.           {
  91.             "substitute_generated_columns": {
  92.             }
  93.           },
  94.           {// 表依赖，因为是两张表做join，因此这里显示了2张表
  95.             "TABLE_dependencies": [
  96.               {
  97.                 "TABLE": "`t1`",
  98.                 "row_may_be_null": false,
  99.                 "map_bit": 0,
  100.                 "depends_ON_map_bits": [
  101.                 ]
  102.               },
  103.               {
  104.                 "TABLE": "`t2`",
  105.                 "row_may_be_null": false,
  106.                 "map_bit": 1,
  107.                 "depends_ON_map_bits": [
  108.                 ]
  109.               }
  110.             ]
  111.           },
  112.           {
  113.             "ref_optimizer_key_uses": [ //这里就是keyuse_array的结果，实际用到了两个索引，c1和cc2的唯一索引
  114.               {
  115.                 "TABLE": "`t1`",
  116.                 "field": "c1",
  117.                 "equals": "`t2`.`cc1`",
  118.                 "null_rejecting": true
  119.               },
  120.               {
  121.                 "TABLE": "`t2`",
  122.                 "field": "cc1",
  123.                 "equals": "`t1`.`c1`",
  124.                 "null_rejecting": true
  125.               }
  126.             ]
  127.           },
  128.       
  129. // 通过查看系统表可以查看到keyuse_array信息，但是Sargables数组信息没有显示。
  130. // 这个通过debug代码发现过程中提取出了一个谓词组，就是c1>c2,field值为cc1列，arg_value值为c2列，num_VALUES为所有Sargable谓词数量，这里为1。
  131. // 这个Sargable数组信息在后面函数update_sargable_FROM_cONst补充检查是否可以使用这里面的cc1索引加速查询。
  132. struct SARGABLE_PARAM {
  133.   Field *field;     // t2的cc1列
  134.   Item **arg_value; // t1的c2列
  135.   uINT num_VALUES;  // 值为1，因为数组只有一个值
  136. };
复制代码
函数的SELECT_optimize属性见下表。
函数的查询优化类型涉及函数对应索引操作OPTIMIZE_NONE无OPTIMIZE_KEY 、between 、IN函数Item_func::BETWEEN : 把between转换为a>1 and a对于 c1=cc1：        ->判断c1=5是否可以合并          -> c1相等。可以合并                  ->将 c1=5 的Key_field删除，剩下c1=cc1，注意这里返回的end="cc1=c1"->对于 cc1=c1：        ->判断 c1=5 是否可以合并          ->cc1不等于c1，不能合并->将所有没有被合并的 Key_field 去掉最终剩下2个 Key_field：Key_field(c1=cc1, and_level=1, optimize=0, null_rejecting=true)Key_field(cc1=c1, and_level=1, optimize=0, null_rejecting=true)最后因为通过merge_key_fields算出来的field==end，因此不加入keyuse_array，注意只有or条件才会执行merge_key_fields。这里条件如果去掉or t1.c1=5这两个key_field就会加入keyuse_array于是看到如下的trace，这里面的access_type全是scan方式，说明没有用索引提升查询性能。            "considered_execution_plans": [              {                "plan_prefix": [                ],                "TABLE": "`t1`",                "best_access_path": {                  "cONsidered_access_paths": [                    {                      "rows_to_scan": 4,                      "filtering_effect": [                      ],                      "final_filtering_effect": 1,                      "access_type": "scan", 这里t1的扫描方式是索引扫描                      "resulting_rows": 4,                      "cost": 0.65,                      "chosen": true                    }                  ]                },                "rest_of_plan": [                  {                    "plan_prefix": [                      "`t1`"                    ],                    "TABLE": "`t2`",                    "best_access_path": {                      "cONsidered_access_paths": [                        {                          "rows_to_scan": 5,                          "filtering_effect": [                          ],                          "final_filtering_effect": 1,                          "access_type": "scan", 这里t2的扫描方式是索引扫描                          "using_join_cache": true,                          "buffers_needed": 1,                          "resulting_rows": 5,                          "cost": 2.25005,                          "chosen": true                        }                      ]                    },-- 下面的结果中，type=index，表明选择了索引扫描，跟上面算出来的结论一致greatsql> EXPLAIN SELECT * FROM t1 join t2 ON t1.c1=t2.cc1 and t2.cc1=t1.c1 or t1.c1=5;+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+---------------------------------------------------------+| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra                                                   |+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+---------------------------------------------------------+|  1 | SIMPLE      | t1    | NULL       | index | PRIMARY       | idx2   | 11      | NULL |    4 |   100.00 | Using index                                             |   这里选择了索引扫描，跟上面算出来的结论一致|  1 | SIMPLE      | t2    | NULL       | index | PRIMARY       | idx2_1 | 5       | NULL |    5 |   100.00 | Using where; Using index; Using join buffer (hash join) |+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+---------------------------------------------------------+例子2：SELECT * FROM t1 join t2 ON t1.c1=t2.cc1 and t1.c2 Filter: ((t2.cc1 = t1.c1) or (t1.c1 = 5))  (cost=2.90 rows=20)    -> Inner hash join (no cONditiON)  (cost=2.90 rows=20)        -> INDEX scan ON t2 using idx2_1  (cost=0.19 rows=5)        -> Hash            -> INDEX scan ON t1 using idx2  (cost=0.65 rows=4) |+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+greatsql> EXPLAIN FORMAT=TREE SELECT * FROM t1 join t2 ON t1.c1=t2.cc1 and t1.c1 Nested loop inner join  (cost=1.91 rows=3)    -> Filter: (t1.c1 < 5)  (cost=0.86 rows=3)        -> INDEX range scan ON t1 using PRIMARY over (c1 < 5)  (cost=0.86 rows=3)    -> Single-row INDEX lookup ON t2 using PRIMARY (cc1=t1.c1)  (cost=0.28 rows=1) |+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+[/code]附录：join_type表扫描方式
[table]JT_UNKNOWNJT_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一张表执行多次范围扫描最后合并结果四、总结

从上面优化器最早的步骤我们认识了Sargable谓词的定义和判定方法，如果查询用到了Sargable谓词是可以进行eq_ref扫描方式的，有效提高了查询效率。通过实际例子发现，在做多表连接的时候用OR条件会降低执行效率，同时用唯一索引列作为连接条件的话会提高效率。因此实际写查询sql的时候，尽量用唯一索引作为连接条件，少用OR条件进行过滤。

Enjoy GreatSQL
来源:https://www.cnblogs.com/greatsql/p/18547608
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！

上一篇: 5.7 与 8.0 对相同文件的 LOAD DATA 语句结果不同

下一篇: 第三十六讲：什么时候会使用内部临时表？

发表于 2024-11-15 21:46:25

举报 回复 使用道具

返回列表 发新帖

Archiver|手机版|小黑屋|翼度科技

Copyright © 2001-2020, Tencent Cloud.　Powered by Discuz! X3.4