復現MySQL的索引選擇失誤以及透過OPTIMIZER_TRACE分析過程

starmoon1900發表於2022-12-01

復現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批次插入資料。

資料分佈，保證資料無傾斜，索引資料均勻：

org_first_id和org_second_id欄位都是在1-100間隨機分佈
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	Null	Index_type
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 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

PRIMARY，範圍是"120306 < account_id"
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：

透過配置、distinct org_first_id等方式，將org_first_id的範圍固定下來，並快取
改寫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，最終仍然會變慢。

其他方式：

資料歸檔。建立歷史表、大資料抽數歸檔冷資料。
引入專門的OLAP系統，不在OLTP系統做複雜的業務查詢。引入ES、hive、HBASE等元件，專業的事交給專業的人去做。

其他

開啟optimizer_trace，只對本執行緒有效。建議使用命令列視窗，直連db。透過Navicat等客戶端，可能會記錄失敗。
一般optimizer_trace只在root使用者下才能使用
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();
                }
            }
        }
    }
}

MySQL 崩潰恢復過程分析
2022-11-29
MySql
MySQL 5.6.26 透過frm & ibd 恢復資料過程
2016-07-13
MySql
MySQL恢復過程
2023-01-03
MySql
單表掃描，MySQL索引選擇不正確並詳細解析OPTIMIZER_TRACE格式
2019-06-11
MySql索引
CRM系統透過特性如何選擇？
2023-04-23
【MySQL】表索引損壞致Crash及修復過程一則
2017-09-07
MySql索引
儲存過程誤刪除的恢復
2013-10-15
儲存過程
【RMAN】rm -rf 誤操作的恢復過程
2016-01-30
MySQL 透過 binlog 恢復資料
2018-12-04
MySql
mysql 透過idb 恢復檔案
2024-03-13
MySql
MySQL字首索引和索引選擇性
2017-09-07
MySql索引
MySQL • 原始碼分析 • SHUTDOWN過程
2017-12-13
MySql原始碼
mysql 儲存過程，以及mybatis如何呼叫
2016-08-30
MySql儲存過程MyBatis
mysql 5.5 索引建設（表rebuild）過程中的系統崩潰的恢復
2011-07-06
MySql索引Rebuild
mysql 誤刪除表內資料，透過binlog日誌恢復
2018-04-02
MySql
資料庫所有檔案丟失後透過RMAN實現恢復
2013-03-30
資料庫
【Mysql】如何透過binlog恢復資料
2023-03-09
MySql
如何透過SQLyog分析MySQL資料庫
2020-05-05
MySql資料庫
死鎖問題排查過程-間隙鎖的復現以及解決
2022-07-10
mysql聯合索引的選擇性
2017-01-18
MySql索引
恢復MySQL資料庫建立儲存過程是遇到錯誤
2021-09-09
MySql資料庫儲存過程
Druid.io系列4：索引過程分析
2018-04-30
UI索引
oracle索引核心過程
2024-01-17
Oracle索引
MYSQL 連線登入過程分析
2018-10-17
MySql
通過誤操作帶來的grub選單丟失薦
2007-10-26
MySQL的session過程
2019-06-04
MySqlSession
【資料庫資料恢復】透過恢復NDF檔案修復資料庫的資料恢復過程
2023-04-27
資料庫資料恢復
MySQL 5.6.26 通過frm & ibd 恢復資料過程
2016-07-15
MySql
css 選擇器優先順序的計算過程
2016-03-17
CSS
MySQL伺服器連線過程分析
2015-03-16
MySql伺服器
透過oracle的指令碼研究其建庫過程
2014-08-27
Oracle指令碼
MySQL儲存過程詳解 mysql 儲存過程
2016-07-26
MySql儲存過程
移動索引的儲存過程
2011-02-23
索引儲存過程
透過 OKR 進行專案過程管理
2019-02-25
OKR
遷移過程中出現的open failed錯誤
2009-02-23
AI
MongoDB的選舉過程
2018-12-27
MongoDB
MySQL索引選擇及規則整理
2018-09-13
MySql索引
crontab失敗的解決過程
2016-06-23

復現MySQL的索引選擇失誤以及透過OPTIMIZER_TRACE分析過程

復現MySQL的索引選擇失誤以及透過OPTIMIZER_TRACE分析過程

一、構造資料（生成150萬資料）

1. 構造資料

二、透過explain驗證語句的索引使用

檢視錶的基本情況

常規的查詢

透過改變查詢條件，引導MySQL最佳化器，選擇錯誤的索引、規則

三、復現索引選擇劣化、並嘗試分析OPTIMIZER_TRACE

四、如何最佳化？

思考，改寫SQL是最佳解決方案嗎？

其他

附錄1 OPTIMIZER_TRACE原始資訊1

附錄2 OPTIMIZER_TRACE原始資訊2

附錄3 java構造資料

相關文章