MySQL5.6的10053，CBO如何选择执行计划

叁歲伎倆 2022-06-08 23:41 174阅读 0赞

mysql> select version();  
\+------------+  
| version()  |  
\+------------+  
| 5.6.24-log |  
\+------------+  
1 row in set (0.00 sec)  
  
mysql> create table a (c1 int);  
Query OK, 0 rows affected (0.32 sec)  
mysql> insert into a values(1);  
Query OK, 1 row affected (0.01 sec)  
mysql> insert into a values(2);  
Query OK, 1 row affected (0.00 sec)  
mysql> insert into a values(3);  
Query OK, 1 row affected (0.00 sec)  
mysql> insert into a values(4);  
Query OK, 1 row affected (0.01 sec)  
mysql> insert into a values(5);  
Query OK, 1 row affected (0.00 sec)  
mysql> insert into a values(6);  
Query OK, 1 row affected (0.01 sec)  
  
mysql> explain select \* from a where c1=6;  
\+----+-------------+-------+------+---------------+------+---------+------+------+-------------+  
| id | select\_type | table | type | possible\_keys | key  | key\_len | ref  | rows | Extra       |  
\+----+-------------+-------+------+---------------+------+---------+------+------+-------------+  
|  1 | SIMPLE      | a     | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |  
\+----+-------------+-------+------+---------------+------+---------+------+------+-------------+  
1 row in set (0.00 sec)  
  
mysql> create index ind\_a\_c1 on a(c1);  
Query OK, 0 rows affected (0.06 sec)  
Records: 0  Duplicates: 0  Warnings: 0  
  
mysql> explain select \* from a where c1=6;  
\+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+  
| id | select\_type | table | type | possible\_keys | key      | key\_len | ref   | rows | Extra       |  
\+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+  
|  1 | SIMPLE      | a     | ref  | ind\_a\_c1      | ind\_a\_c1 | 5       | const |    1 | Using index |  
\+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+

1 row in set (0.00 sec)

\#开启trace  
mysql> set optimizer\_trace='enabled=on';  
Query OK, 0 rows affected (0.00 sec)  
\#设置trace大小  
mysql> set optimizer\_trace\_max\_mem\_size=1000000;  
Query OK, 0 rows affected (0.00 sec)  
\#增加trace中注释  
mysql> set end\_markers\_in\_json=on;  
Query OK, 0 rows affected (0.00 sec)  
  
mysql> select \* from a where c1=6;  
\+------+  
| c1   |  
\+------+  
|    6 |  
\+------+  
1 row in set (0.00 sec)  
  
mysql> select \* from information\_schema.optimizer\_trace\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
                            QUERY: select \* from a where c1=6  
                            TRACE: \{  
  "steps": \[  
    \{  
      "join\_preparation": \{\#优化准备工作  
        "select\#": 1,  
        "steps": \[  
          \{  
            "expanded\_query": "/\* select\#1 \*/ select \`a\`.\`c1\` AS \`c1\` from \`a\` where (\`a\`.\`c1\` = 6)"  
          \}  
        \] /\* steps \*/  
      \} /\* join\_preparation \*/  
    \},  
    \{  
      "join\_optimization": \{\#优化的主要阶段,包括逻辑优化和物理优化两个阶段    
        "select\#": 1,  
        "steps": \[  
          \{  
            "condition\_processing": \{  
              "condition": "WHERE",  
              "original\_condition": "(\`a\`.\`c1\` = 6)",  
              "steps": \[  
                \{  
                  "transformation": "equality\_propagation",  \#逻辑优化,等式处理  
                  "resulting\_condition": "multiple equal(6, \`a\`.\`c1\`)"  
                \},  
                \{  
                  "transformation": "constant\_propagation",  
                  "resulting\_condition": "multiple equal(6, \`a\`.\`c1\`)"  
                \},  
                \{  
                  "transformation": "trivial\_condition\_removal",  
                  "resulting\_condition": "multiple equal(6, \`a\`.\`c1\`)"  
                \}  
              \] /\* steps \*/  
            \} /\* condition\_processing \*/  
          \},  
          \{  
            "table\_dependencies": \[\#逻辑优化, 找出表之间的相互依赖关系. 非直接可用的优化方式  
              \{  
                "table": "\`a\`",  
                "row\_may\_be\_null": false,  
                "map\_bit": 0,  
                "depends\_on\_map\_bits": \[  
                \] /\* depends\_on\_map\_bits \*/  
              \}  
            \] /\* table\_dependencies \*/  
          \},  
          \{  
            "ref\_optimizer\_key\_uses": \[ \#逻辑优化,找出备选的索引  
              \{  
                "table": "\`a\`",  
                "field": "c1",  
                "equals": "6",  
                "null\_rejecting": false  
              \}  
            \] /\* ref\_optimizer\_key\_uses \*/  
          \},  
          \{  
            "rows\_estimation": \[\#逻辑优化, 估算rows,单表上进行全表扫描和索引扫描的代价估算. 每个索引都估算索引扫描代价  
              \{  
                "table": "\`a\`",  
                "range\_analysis": \{  
                  "table\_scan": \{  
                    "rows": 6,  
                    "cost": 4.3  
                  \} /\* table\_scan \*/,  
                  "potential\_range\_indices": \[\#逻辑优化, 列出备选的索引  
                    \{  
                      "index": "ind\_a\_c1",  
                      "usable": true,  
                      "key\_parts": \[  
                        "c1"  
                      \] /\* key\_parts \*/  
                    \}  
                  \] /\* potential\_range\_indices \*/,  
                  "best\_covering\_index\_scan": \{  
                    "index": "ind\_a\_c1",  
                    "cost": 2.2067,  
                    "chosen": true  
                  \} /\* best\_covering\_index\_scan \*/,  
                  "setup\_range\_conditions": \[ \#逻辑优化, 如果有可下推的条件,则带条件考虑范围查询  
                  \] /\* setup\_range\_conditions \*/,  
                  "group\_index\_range": \{\#逻辑优化, 如带有GROUPBY或DISTINCT,则考虑是否有索引可优化这种操作. 并考虑带有MIN/MAX的情况   
                    "chosen": false,  
                    "cause": "not\_group\_by\_or\_distinct"  
                  \} /\* group\_index\_range \*/,  
                  "analyzing\_range\_alternatives": \{\#逻辑优化,开始计算每个索引做范围扫描的花费  
                    "range\_scan\_alternatives": \[  
                      \{  
                        "index": "ind\_a\_c1",  
                        "ranges": \[  
                          "6 <= c1 <= 6"  
                        \] /\* ranges \*/,  
                        "index\_dives\_for\_eq\_ranges": true,  
                        "rowid\_ordered": true,  
                        "using\_mrr": false,  
                        "index\_only": true,  
                        "rows": 1,  
                        "cost": 2.21,  
                        "chosen": false,  
                        "cause": "cost"  
                      \}  
                    \] /\* range\_scan\_alternatives \*/,  
                    "analyzing\_roworder\_intersect": \{  
                      "usable": false,  
                      "cause": "too\_few\_roworder\_scans"  
                    \} /\* analyzing\_roworder\_intersect \*/  
                  \} /\* analyzing\_range\_alternatives \*/  
                \} /\* range\_analysis \*/  
              \}  
            \] /\* rows\_estimation \*/  
          \},  
          \{  
            "considered\_execution\_plans": \[  
              \{  
                "plan\_prefix": \[  
                \] /\* plan\_prefix \*/,  
                "table": "\`a\`",  
                "best\_access\_path": \{  
                  "considered\_access\_paths": \[ \#物理优化计算ind\_a\_c1索引上使用ref方查找的花费,  
                    \{  
                      "access\_type": "ref",  
                      "index": "ind\_a\_c1",  
                      "rows": 1,  
                      "cost": 1.2,  
                      "chosen": true \#逻辑优化,这个索引的代价最小,被选中。  
                    \},  
                    \{  
                      "access\_type": "scan",  
                      "cost": 2.2,  
                      "rows": 6,  
                      "cause": "cost",  
                      "chosen": false  
                    \}  
                  \] /\* considered\_access\_paths \*/  
                \} /\* best\_access\_path \*/,  
                "cost\_for\_plan": 1.2,  
                "rows\_for\_plan": 1,  
                "chosen": true  
              \}  
            \] /\* considered\_execution\_plans \*/  
          \},  
          \{  
            "attaching\_conditions\_to\_tables": \{  
              "original\_condition": "(\`a\`.\`c1\` = 6)",  
              "attached\_conditions\_computation": \[  
              \] /\* attached\_conditions\_computation \*/,  
              "attached\_conditions\_summary": \[  
                \{  
                  "table": "\`a\`",  
                  "attached": null  
                \}  
              \] /\* attached\_conditions\_summary \*/  
            \} /\* attaching\_conditions\_to\_tables \*/  
          \},  
          \{  
            "refine\_plan": \[  
              \{  
                "table": "\`a\`"  
              \}  
            \] /\* 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.01 sec)