首页 > 数据库 >复现MySQL的索引选择失误以及通过OPTIMIZER_TRACE分析过程

复现MySQL的索引选择失误以及通过OPTIMIZER_TRACE分析过程

时间：2022-12-01 17:59:45浏览次数：50  
标签：account OPTIMIZER TRACE 09 tm MySQL org id first

复现MySQL的索引选择失误以及通过OPTIMIZER_TRACE分析过程

验证环境：MySQL 5.7.39 windows-pc

一、构造数据（生成150万数据）

构建一张账户表，带有一级部门id和二级部门id，并且建立有索引。比较典型的业务场景，根据部门id进行各类查询。

CREATE TABLE `TM_ACCOUNT` (
  `account_id` bigint(20)  NOT null ,
  `name` varchar(32) DEFAULT '',
  `address` varchar(32) DEFAULT '',
  `org_first_id` int(10) DEFAULT 0,
  `org_second_id` int(10) DEFAULT 0,
  `biz_date` date DEFAULT null,
  `last_modify_dt` datetime DEFAULT null,
  PRIMARY KEY (`account_id`),
  KEY IDX_org_id_combine(org_first_id,org_second_id),
  KEY IDX_last_modify_dt_org_first_id_name(last_modify_dt,org_first_id,org_second_id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

1. 构造数据

此处直接通过jdbc批量插入数据。

数据分布，保证数据无倾斜，索引数据均匀：

  1. org_first_id和org_second_id字段都是在1-100间随机分布
  2. last_modify_dt在25天间随机分布

代码可以直接使用，详情见附件3

二、通过explain验证语句的索引使用

查看表的基本情况

show index from TM_ACCOUNT ;  -- 看索引

执行结果，可以看到org_first_id/org_second_id的区分度，都很不错。

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
tm_account 0 PRIMARY 1 account_id A 1408599 BTREE
tm_account 1 IDX_org_id_combine 1 org_first_id A 101 YES BTREE
tm_account 1 IDX_org_id_combine 2 org_second_id A 10611 YES BTREE
tm_account 1 IDX_last_modify_dt_org_first_id_name 1 last_modify_dt A 24 YES BTREE
tm_account 1 IDX_last_modify_dt_org_first_id_name 2 org_first_id A 2497 YES BTREE
tm_account 1 IDX_last_modify_dt_org_first_id_name 3 org_second_id A 251724 YES BTREE 
show table status like '%TM_ACCOUNT%'; -- 看表状态，有数据大小、索引大小、大概行数

可看到使用了InnoDB引擎，大概行数是1408599，实际行数是1500000整。

Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
tm_account InnoDB 10 Dynamic 1408599 83 118128640 0 128253952 7340032 2022-09-13 10:49:36 utf8mb4_general_ci

常规的查询

explain SELECT * from TM_ACCOUNT where ACCOUNT_ID = '10'; -- 典型的主键字段查询，非常快，type=const

explain SELECT * from TM_ACCOUNT where ACCOUNT_ID = '10'; -- 典型的主键字段查询，非常快，type=const

explain SELECT * from TM_ACCOUNT where NAME = 'name-11'; -- 典型的非索引字段查询，全表扫描

explain SELECT * from TM_ACCOUNT where ADDRESS = 'QR3xHEOpaLAVNFCtAKXY'; -- 典型的非索引字段查询，全表扫描

explain SELECT * from TM_ACCOUNT where LAST_MODIFY_DT = '2100-09-13 00:00:00' and ACCOUNT_ID > 100 LIMIT 2; -- 典型的范围查询，扫描索引。单速度也很快

通过改变查询条件，引导MySQL优化器，选择错误的索引、规则

下面通过3个SQL查询的结果对比，来复现MySQL优化器如何选错优化场景。（这里不讨论为何不换种写法，直接规避劣化SQL。往往出现这类SQL时，一是业务场景复杂，二是开发时数据量少并未发现，在生产环境才能出现）

-- SQL-1 
explain
SELECT * from TM_ACCOUNT where org_first_id >= 99 and org_second_id in (1,2,3,60) and BIZ_DATE  in ('2100-09-01','2100-09-02')and ACCOUNT_ID > '120306' order by ACCOUNT_ID  desc LIMIT 5000;

查询结果：可见使用了IDX_org_id_combine索引，并用到索引范围扫描、回表查询、临时文件排序。不算是一个很好的查询语句，但实际业务中的查询条件，只会更复杂。直接查询耗时140ms。

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE TM_ACCOUNT range PRIMARY,IDX_org_id_combine IDX_org_id_combine 18 33942 4.0 Using index condition; Using where; Using filesort 
-- SQL-2 坏案例-全表扫描； 
explain
SELECT * from TM_ACCOUNT where org_first_id >= 90 and org_second_id in (1,2,3,60) and BIZ_DATE  in ('2100-09-01','2100-09-02') and ACCOUNT_ID > '120306' order by ACCOUNT_ID desc   LIMIT 5000;

查询结果：改变org_first_id条件，扩大查询范围，结果变成了主键索引的大范围扫描，预估扫描行数70万行，几乎是表总数的一半。直接查询耗时3900ms。

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE TM_ACCOUNT range PRIMARY,IDX_org_id_combine PRIMARY 8 704299 1.68 Using where 
-- SQL-3 与SQL-1基本相同，但limit数量减少。
explain  
SELECT * from TM_ACCOUNT where org_first_id >= 99 and org_second_id in (1,2,3,60) and BIZ_DATE  in ('2100-09-01','2100-09-02') and ACCOUNT_ID > '120306' order by ACCOUNT_ID desc   LIMIT 500;

查询结果：与SQL-1基本相同，但limit数量减少，即查询条件范围缩小，劣化成主键大范围扫描。 直接查询耗时1210ms。

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE TM_ACCOUNT range PRIMARY,IDX_org_id_combine PRIMARY 8 704299 0.19 Using where

三、复现索引选择劣化、并尝试分析OPTIMIZER_TRACE

执行相关命令，获取OPTIMIZER_TRACE过程。

/* 打开optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 
#你的sql
-- select ......; 
SELECT * from TM_ACCOUNT where org_first_id >= 90 and org_second_id in (1,2,3,60) and BIZ_DATE  in ('2100-09-01','2100-09-02') and ACCOUNT_ID > '120306' order by ACCOUNT_ID desc   LIMIT 5000;
#查看优化器追踪链
select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
#关闭优化器追踪
SET optimizer_trace='enabled=off';

关键过程：通过对潜在查询方式的预估，分别对PRIMARY/IDX_org_id_combine的开销进行评估，这里开销并不仅看扫描行数，还会看排序等情况。可以看到虽然走主键索引的行数更多，但总开销更小。由此可知在【预估】过程，误导了整个优化器。

共有2个潜在选项，分别标出了rowid是否排序、行数rows、预估开销cost

  1. PRIMARY，范围是"120306 < account_id"
  2. IDX_org_id_combine，范围是"90 <= org_first_id"

截取部分OPTIMIZER_TRACE结果，完整json参考附录1

    // 分析可供选择的范围条件
    "analyzing_range_alternatives": {
        "range_scan_alternatives": [
        {
            "index": "PRIMARY",
            "ranges": [
            "120306 < account_id"
            ],
            "index_dives_for_eq_ranges": true,
            "rowid_ordered": true,
            "using_mrr": false,
            "index_only": false,
            "rows": 704299,
            "cost": 141880,
            "chosen": true
        },
        {
            "index": "IDX_org_id_combine",
            "ranges": [
            "90 <= org_first_id"
            ],
            "index_dives_for_eq_ranges": true,
            "rowid_ordered": false,
            "using_mrr": false,
            "index_only": false,
            "rows": 295138,
            "cost": 354167,
            "chosen": false,
            "cause": "cost"
        }
        ],
        "analyzing_roworder_intersect": {
        "usable": false,
        "cause": "too_few_roworder_scans"
        }
    },
    // 最终选择的路径
    "chosen_range_access_summary": {
        "range_access_plan": {
        "type": "range_scan",
        "index": "PRIMARY",
        "rows": 704299,
        "ranges": [
            "120306 < account_id"
        ]
        },
        "rows_for_plan": 704299,
        "cost_for_plan": 141880,
        "chosen": true
    }

这里怀疑是order by ACCOUNT_ID影响了优化器选择，但通测试发现，即使移除了'order by ACCOUNT_ID desc LIMIT 5000'，explain结果仍然是走PRIMARY索引。由此可见，还有些隐藏的信息，OPTIMIZER_TRACE没有展示全。这里暂不深入讨论。

explain
SELECT * from TM_ACCOUNT where org_first_id >= 90 and org_second_id in (1,2,3,60) and BIZ_DATE  in ('2100-09-01','2100-09-02') and ACCOUNT_ID > '120306' ;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE TM_ACCOUNT range PRIMARY,IDX_org_id_combine PRIMARY 8 704299 1.68 Using where

结果：实际查询耗时912ms。在【## 附录2 OPTIMIZER_TRACE原始信息2】中也能看到选择实际索引，仍然是PRIMARY，与explain结果一致。

四、如何优化？

改写SQL：

  1. 通过配置、distinct org_first_id等方式，将org_first_id的范围固定下来，并缓存
  2. 改写SQL，将org_first_id >= 90 改写为 org_first_id IN (xxxxx)

下面来看效果

explain
SELECT * from TM_ACCOUNT where org_first_id in ('90','91','92','93','94','95','96','97','98','99') and org_second_id in (1,2,3,60) and BIZ_DATE  in ('2100-09-01','2100-09-02') and ACCOUNT_ID > '120306' order by ACCOUNT_ID desc   LIMIT 5000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE TM_ACCOUNT range PRIMARY,IDX_org_id_combine IDX_org_id_combine 18 5543 20.0 Using index condition; Using where; Using filesort

结果：实际查询耗时59ms。explain结果可看到虽然也用了IDX_org_id_combine索引，但仍然是range查询、回表、filesort，好在扫描行数较少，最终耗时很小。

思考，改写SQL是最佳解决方案吗？

随着数据量的增大，无论多么简单的SQL，最终仍然会变慢。

其他方式：

  1. 数据归档。 建立历史表、大数据抽数归档冷数据。
  2. 引入专门的OLAP系统，不在OLTP系统做复杂的业务查询。引入ES、hive、HBASE等组件，专业的事交给专业的人去做。

其他

  1. 打开optimizer_trace，只对本线程有效。建议使用命令行窗口，直连db。通过Navicat等客户端，可能会记录失败。
  2. 一般optimizer_trace只在root用户下才能使用
  3. mariadb直到10.4版本才有Optimizer Trace， 之前的版本执行'SET optimizer_trace='enabled=on'; '会返回错误 。官网链接https://mariadb.com/resources/blog/optimizer-trace-in-mariadb-server-10-4/

附录1 OPTIMIZER_TRACE原始信息1

以下语句的执行优化过程
SELECT * from TM_ACCOUNT where org_first_id >= 90 and org_second_id in (1,2,3,60) and BIZ_DATE in ('2100-09-01','2100-09-02') and ACCOUNT_ID > '120306' order by ACCOUNT_ID desc LIMIT 5000;

{
    "steps": [
      {
        "join_preparation": {
          "select#": 1,
          "steps": [
            {
              "IN_uses_bisection": true
            },
            {
              "IN_uses_bisection": true
            },
            {
              "expanded_query": "/* select#1 */ select `tm_account`.`account_id` AS `account_id`,`tm_account`.`name` AS `name`,`tm_account`.`address` AS `address`,`tm_account`.`org_first_id` AS `org_first_id`,`tm_account`.`org_second_id` AS `org_second_id`,`tm_account`.`biz_date` AS `biz_date`,`tm_account`.`last_modify_dt` AS `last_modify_dt` from `tm_account` where ((`tm_account`.`org_first_id` >= 90) and (`tm_account`.`org_second_id` in (1,2,3,60)) and (`tm_account`.`biz_date` in ('2100-09-01','2100-09-02')) and (`tm_account`.`account_id` > 120306)) order by `tm_account`.`account_id` desc limit 5000"
            }
          ]
        }
      },
      {
        "join_optimization": {
          "select#": 1,
          "steps": [
            {
              "condition_processing": {
                "condition": "WHERE",
                "original_condition": "((`tm_account`.`org_first_id` >= 90) and (`tm_account`.`org_second_id` in (1,2,3,60)) and (`tm_account`.`biz_date` in ('2100-09-01','2100-09-02')) and (`tm_account`.`account_id` > 120306))",
                "steps": [
                  {
                    "transformation": "equality_propagation",
                    "resulting_condition": "((`tm_account`.`org_first_id` >= 90) and (`tm_account`.`org_second_id` in (1,2,3,60)) and (`tm_account`.`biz_date` in ('2100-09-01','2100-09-02')) and (`tm_account`.`account_id` > 120306))"
                  },
                  {
                    "transformation": "constant_propagation",
                    "resulting_condition": "((`tm_account`.`org_first_id` >= 90) and (`tm_account`.`org_second_id` in (1,2,3,60)) and (`tm_account`.`biz_date` in ('2100-09-01','2100-09-02')) and (`tm_account`.`account_id` > 120306))"
                  },
                  {
                    "transformation": "trivial_condition_removal",
                    "resulting_condition": "((`tm_account`.`org_first_id` >= 90) and (`tm_account`.`org_second_id` in (1,2,3,60)) and (`tm_account`.`biz_date` in ('2100-09-01','2100-09-02')) and (`tm_account`.`account_id` > 120306))"
                  }
                ]
              }
            },
            {
              "substitute_generated_columns": {}
            },
            {
              "table_dependencies": [
                {
                  "table": "`tm_account`",
                  "row_may_be_null": false,
                  "map_bit": 0,
                  "depends_on_map_bits": []
                }
              ]
            },
            {
              "ref_optimizer_key_uses": []
            },
            {
                // 行数预估
              "rows_estimation": [
                {
                  "table": "`tm_account`",
                  "range_analysis": {
                    "table_scan": {
                      "rows": 1408599,
                      "cost": 288932
                    },
                    "potential_range_indexes": [
                      {
                        "index": "PRIMARY",
                        "usable": true,
                        "key_parts": [
                          "account_id"
                        ]
                      },
                      {
                        "index": "IDX_org_id_combine",
                        "usable": true,
                        "key_parts": [
                          "org_first_id",
                          "org_second_id",
                          "account_id"
                        ]
                      },
                      {
                        "index": "IDX_last_modify_dt_org_first_id_name",
                        "usable": false,
                        "cause": "not_applicable" // 直接标明不适用
                      }
                    ],
                    "setup_range_conditions": [],
                    "group_index_range": {
                      "chosen": false,
                      "cause": "not_group_by_or_distinct"
                    },
                    // 分析可供选择的范围条件
                    "analyzing_range_alternatives": {
                      "range_scan_alternatives": [
                        {
                          "index": "PRIMARY",
                          "ranges": [
                            "120306 < account_id"
                          ],
                          "index_dives_for_eq_ranges": true,
                          "rowid_ordered": true,
                          "using_mrr": false,
                          "index_only": false,
                          "rows": 704299,
                          "cost": 141880,
                          "chosen": true
                        },
                        {
                          "index": "IDX_org_id_combine",
                          "ranges": [
                            "90 <= org_first_id"
                          ],
                          "index_dives_for_eq_ranges": true,
                          "rowid_ordered": false,
                          "using_mrr": false,
                          "index_only": false,
                          "rows": 295138,
                          "cost": 354167,
                          "chosen": false,
                          "cause": "cost"
                        }
                      ],
                      "analyzing_roworder_intersect": {
                        "usable": false,
                        "cause": "too_few_roworder_scans"
                      }
                    },
                    "chosen_range_access_summary": {
                      "range_access_plan": {
                        "type": "range_scan",
                        "index": "PRIMARY",
                        "rows": 704299,
                        "ranges": [
                          "120306 < account_id"
                        ]
                      },
                      "rows_for_plan": 704299,
                      "cost_for_plan": 141880,
                      "chosen": true
                    }
                  }
                }
              ]
            },
            {
              "considered_execution_plans": [
                {
                  "plan_prefix": [],
                  "table": "`tm_account`",
                  "best_access_path": {
                    "considered_access_paths": [
                      {
                        "rows_to_scan": 704299,
                        "access_type": "range",
                        "range_details": {
                          "used_index": "PRIMARY"
                        },
                        "resulting_rows": 11806,
                        "cost": 282740,
                        "chosen": true
                      }
                    ]
                  },
                  "condition_filtering_pct": 100,
                  "rows_for_plan": 11806,
                  "cost_for_plan": 282740,
                  "chosen": true
                }
              ]
            },
            {
              "attaching_conditions_to_tables": {
                "original_condition": "((`tm_account`.`org_first_id` >= 90) and (`tm_account`.`org_second_id` in (1,2,3,60)) and (`tm_account`.`biz_date` in ('2100-09-01','2100-09-02')) and (`tm_account`.`account_id` > 120306))",
                "attached_conditions_computation": [
                  {
                    "table": "`tm_account`",
                    "rechecking_index_usage": {
                      "recheck_reason": "low_limit",
                      "limit": 5000,
                      "row_estimate": 11806,
                      "range_analysis": {
                        "table_scan": {
                          "rows": 1408599,
                          "cost": 1690000
                        },
                        "potential_range_indexes": [
                          {
                            "index": "PRIMARY",
                            "usable": true,
                            "key_parts": [
                              "account_id"
                            ]
                          },
                          {
                            "index": "IDX_org_id_combine",
                            "usable": false,
                            "cause": "not_applicable"
                          },
                          {
                            "index": "IDX_last_modify_dt_org_first_id_name",
                            "usable": false,
                            "cause": "not_applicable"
                          }
                        ],
                        "setup_range_conditions": [],
                        "group_index_range": {
                          "chosen": false,
                          "cause": "cannot_do_reverse_ordering"
                        },
                        "analyzing_range_alternatives": {
                          "range_scan_alternatives": [
                            {
                              "index": "PRIMARY",
                              "ranges": [
                                "120306 < account_id"
                              ],
                              "index_dives_for_eq_ranges": true,
                              "rowid_ordered": true,
                              "using_mrr": false,
                              "index_only": false,
                              "rows": 704299,
                              "cost": 141880,
                              "chosen": true
                            }
                          ]
                        },
                        "chosen_range_access_summary": {
                          "range_access_plan": {
                            "type": "range_scan",
                            "index": "PRIMARY",
                            "rows": 704299,
                            "ranges": [
                              "120306 < account_id"
                            ]
                          },
                          "rows_for_plan": 704299,
                          "cost_for_plan": 141880,
                          "chosen": true
                        }
                      }
                    }
                  }
                ],
                "attached_conditions_summary": [
                  {
                    "table": "`tm_account`",
                    "attached": "((`tm_account`.`org_first_id` >= 90) and (`tm_account`.`org_second_id` in (1,2,3,60)) and (`tm_account`.`biz_date` in ('2100-09-01','2100-09-02')) and (`tm_account`.`account_id` > 120306))"
                  }
                ]
              }
            },
            {
              "clause_processing": {
                "clause": "ORDER BY",
                "original_clause": "`tm_account`.`account_id` desc",
                "items": [
                  {
                    "item": "`tm_account`.`account_id`"
                  }
                ],
                "resulting_clause_is_simple": true,
                "resulting_clause": "`tm_account`.`account_id` desc"
              }
            },
            {
              "reconsidering_access_paths_for_index_ordering": {
                "clause": "ORDER BY",
                "steps": [],
                "index_order_summary": {
                  "table": "`tm_account`",
                  "index_provides_order": true,
                  "order_direction": "desc",
                  "index": "PRIMARY",
                  "plan_changed": false
                }
              }
            },
            {
              "refine_plan": [
                {
                  "table": "`tm_account`"
                }
              ]
            }
          ]
        }
      },
      {
        "join_execution": {
          "select#": 1,
          "steps": []
        }
      }
    ]
  }

附录2 OPTIMIZER_TRACE原始信息2

SELECT * from TM_ACCOUNT where org_first_id >= 90 and org_second_id in (1,2,3,60) and BIZ_DATE in ('2100-09-01','2100-09-02') and ACCOUNT_ID > '120306' ;


{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "IN_uses_bisection": true
          },
          {
            "IN_uses_bisection": true
          },
          {
            "expanded_query": "/* select#1 */ select `tm_account`.`account_id` AS `account_id`,`tm_account`.`name` AS `name`,`tm_account`.`address` AS `address`,`tm_account`.`org_first_id` AS `org_first_id`,`tm_account`.`org_second_id` AS `org_second_id`,`tm_account`.`biz_date` AS `biz_date`,`tm_account`.`last_modify_dt` AS `last_modify_dt` from `tm_account` where ((`tm_account`.`org_first_id` >= 90) and (`tm_account`.`org_second_id` in (1,2,3,60)) and (`tm_account`.`biz_date` in ('2100-09-01','2100-09-02')) and (`tm_account`.`account_id` > 120306))"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((`tm_account`.`org_first_id` >= 90) and (`tm_account`.`org_second_id` in (1,2,3,60)) and (`tm_account`.`biz_date` in ('2100-09-01','2100-09-02')) and (`tm_account`.`account_id` > 120306))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "((`tm_account`.`org_first_id` >= 90) and (`tm_account`.`org_second_id` in (1,2,3,60)) and (`tm_account`.`biz_date` in ('2100-09-01','2100-09-02')) and (`tm_account`.`account_id` > 120306))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "((`tm_account`.`org_first_id` >= 90) and (`tm_account`.`org_second_id` in (1,2,3,60)) and (`tm_account`.`biz_date` in ('2100-09-01','2100-09-02')) and (`tm_account`.`account_id` > 120306))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "((`tm_account`.`org_first_id` >= 90) and (`tm_account`.`org_second_id` in (1,2,3,60)) and (`tm_account`.`biz_date` in ('2100-09-01','2100-09-02')) and (`tm_account`.`account_id` > 120306))"
                }
              ]
            }
          },
          {
            "substitute_generated_columns": {}
          },
          {
            "table_dependencies": [
              {
                "table": "`tm_account`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": []
              }
            ]
          },
          {
            "ref_optimizer_key_uses": []
          },
          {
            "rows_estimation": [
              {
                "table": "`tm_account`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 1408599,
                    "cost": 288932
                  },
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": true,
                      "key_parts": [
                        "account_id"
                      ]
                    },
                    {
                      "index": "IDX_org_id_combine",
                      "usable": true,
                      "key_parts": [
                        "org_first_id",
                        "org_second_id",
                        "account_id"
                      ]
                    },
                    {
                      "index": "IDX_last_modify_dt_org_first_id_name",
                      "usable": false,
                      "cause": "not_applicable"
                    }
                  ],
                  "setup_range_conditions": [],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "PRIMARY",
                        "ranges": [
                          "120306 < account_id"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 704299,
                        "cost": 141880,
                        "chosen": true
                      },
                      {
                        "index": "IDX_org_id_combine",
                        "ranges": [
                          "90 <= org_first_id"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 295138,
                        "cost": 354167,
                        "chosen": false,
                        "cause": "cost"
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  },
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "PRIMARY",
                      "rows": 704299,
                      "ranges": [
                        "120306 < account_id"
                      ]
                    },
                    "rows_for_plan": 704299,
                    "cost_for_plan": 141880,
                    "chosen": true
                  }
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [],
                "table": "`tm_account`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 704299,
                      "access_type": "range",
                      "range_details": {
                        "used_index": "PRIMARY"
                      },
                      "resulting_rows": 704299,
                      "cost": 282740,
                      "chosen": true
                    }
                  ]
                },
                "condition_filtering_pct": 100,
                "rows_for_plan": 704299,
                "cost_for_plan": 282740,
                "chosen": true
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`tm_account`.`org_first_id` >= 90) and (`tm_account`.`org_second_id` in (1,2,3,60)) and (`tm_account`.`biz_date` in ('2100-09-01','2100-09-02')) and (`tm_account`.`account_id` > 120306))",
              "attached_conditions_computation": [],
              "attached_conditions_summary": [
                {
                  "table": "`tm_account`",
                  "attached": "((`tm_account`.`org_first_id` >= 90) and (`tm_account`.`org_second_id` in (1,2,3,60)) and (`tm_account`.`biz_date` in ('2100-09-01','2100-09-02')) and (`tm_account`.`account_id` > 120306))"
                }
              ]
            }
          },
          {
            "refine_plan": [
              {
                "table": "`tm_account`"
              }
            ]
          }
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": []
      }
    }
  ]
}

附录3 java构造数据


public final class JdbcUtils {

    private static String url = "jdbc:mysql://localhost:3306/xxxx?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8";
    private static String user = "root";
    private static String password = "123";

    private JdbcUtils() {

    }

    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new ExceptionInInitializerError(e);
        }
    }


    public static void main(String args[]) {
        insertBatch();
    }

    public static void insertBatch() {

        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            String sql = "INSERT into TM_ACCOUNT values(?,?,?,?,?,?,?);";

            // 1. 获取链接，预处理语句
            conn = getConnection();
            conn.setAutoCommit(false);
            pst = conn.prepareStatement(sql);

            // 2. 开始插入，总插入150万
            Random random = new Random();
            int a_id_start = 1;
            for (int i = 0; i < 5 * 150; i++) {
                // 每2000条执行一次批量插入
                for (int loop = 0; loop < 2000; loop++) {

                    a_id_start++;
                    pst.setInt(1, a_id_start);
                    pst.setString(2, "name-" + a_id_start);
                    pst.setString(3, RandomString.make(20));
                    pst.setInt(4, random.nextInt(100));
                    pst.setInt(5, random.nextInt(100));
                    pst.setDate(6, new Date(200, 8, random.nextInt(25) + 1));
                    pst.setDate(7, new Date(200, 8, random.nextInt(25) + 1));
                    pst.addBatch();
                }
                pst.executeBatch();
                conn.commit();
                System.out.println(" done ！！！！！！" + i);
            }
            
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            free(rs, pst, conn);
        }
    }

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, user, password);
    }

    public static void free(ResultSet rs, Statement st, Connection conn) {
        try {
            if (rs != null)
                rs.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (st != null)
                    st.close();
            } catch (Exception e2) {
                e2.printStackTrace();
            } finally {
                try {
                    conn.close();
                } catch (Exception e3) {
                    e3.printStackTrace();
                }
            }
        }
    }
}

标签：account,OPTIMIZER,TRACE,09,tm,MySQL,org,id,first
From： https://www.cnblogs.com/starmoon1994/p/16942181.html

相关文章

  • MySQL优化常用命令
    showvariableslike'transaction_isolation';查看事务隔离级别/*查询长事务（超过60秒）*/select*frominformation_schema.innodb_trxwhereTIME_TO_SEC(timediff(n......
  • 第16节-MySQL锁与事务
    1、事务的介绍1、事务是一组有着内在逻辑联系的SQL命令。2、支持事务的数据库系统要么执行一个事务里的所有SQL命令，要么把它们当作整体全部放弃。3、事务永远不会只完......
  • MySQL经典面试题及答案
    MySQL经典面试题及答案，每道都是认真筛选出的大厂高频面试题，助力大家能找到满意的工作！下载链接：全部MySQL面试题及答案PDF其他互联网大厂面试题1：阿里巴巴Java面试题2：阿里......
  • MySQL删除数据的三种方式：delete、drop、truncate的区别
    本篇主要讨论MySQL删除数据的三种方式：delete、drop、truncate的区别当行数据批量delete时，InnoDB如何处理自增ID的目录参考来源：1、建表2、插入数据3、删除数据deletetru......
  • 高新能MySQL闲杂笔记
    https://blog.csdn.net/welongfor/article/details/86622790在这里插入图片描述......
  • mysql索引，事务，视图，存储过程，存储引擎
    一，索引1.概念索引：提供指向存储在表的指定列中的数据值的指针，然后根据指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。索引就......
  • mysql间隙锁加锁11个规则
        间隙锁是在可重复读隔离级别下才会生效的：next-keylock实际上是由间隙锁加行锁实现的，如果切换到读提交隔离级别(read-committed)的话，就好理解了，过程中去掉......
  • Docker 安装 MySQL
    1、查看可用的MySQL版本访问MySQL镜像库地址：https://hub.docker.com/_/mysql?tab=tags 。可以通过Sortby查看其他版本的MySQL，默认是最新版本 mysql:latest 。......
  • MYSQL IN 是否走索引?
    准备工作CREATETABLEt(idINTNOTNULLAUTO_INCREMENT,key1VARCHAR(100),common_fieldVARCHAR(100),PRIMARYKEY(id),KEYidx_key1(ke......
  • MySQL 间隙锁导致的死锁场景分析
    实际业务场景在我们使用mysql的时候，如果不注意间隙锁容易引起死锁，最近分析一个业务场景就是间隙锁导致的死锁，业务抽象如下：系统有一个批量新增业务资源的功能，实现逻辑如下......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99