表資料量影響MySQL索引選擇

ImClive發表於2018-10-27

現象

新建了一張員工表，插入了少量資料，索引中所有的欄位均在where條件出現時，正確走到了idx_nap索引，但是where出現部分自左開始的索引時，卻進行全表掃描，與MySQL官方所說的最左匹配原則“相悖”。

資料背景

CREATE TABLE `staffs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT `` COMMENT `姓名`,
  `age` int(11) NOT NULL DEFAULT `0` COMMENT `年齡`,
  `pos` varchar(20) NOT NULL DEFAULT `` COMMENT `職位`,
  `add_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT `入職時間`,
  PRIMARY KEY (`id`),
  KEY `idx_nap` (`name`,`age`,`pos`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COMMENT=`員工記錄表`;

表中資料如下：
id  name    age pos     add_time
1   July    23  dev     2018-06-04 16:02:02
2   Clive   22  dev     2018-06-04 16:02:32
3   Cleva   24  test    2018-06-04 16:02:38
4   July    23  test    2018-06-04 16:12:22
5   July    23  pre     2018-06-04 16:12:37
6   Clive   22  pre     2018-06-04 16:12:48
7   July    25  dev     2018-06-04 16:30:17

Explain語句看下執行計劃

-- 全匹配走了索引
explain select * from staffs where name = `July` and age = 23 and pos = `dev`;
id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  staffs  NULL    ref idx_nap idx_nap 140 const,const,const   1   100.00  NULL

開啟優化器跟蹤優化過程

-- 左側部分匹配卻沒有走索引，全表掃描
explain select * from staffs where name = `July` and age = 23;
id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  staffs2 NULL    ALL idx_nap NULL    NULL    NULL    6   50.00   Using where

-- 開啟優化器跟蹤
set session optimizer_trace=`enabled=on`;
-- 在執行完查詢語句後，在執行以下的select語句可以檢視具體的優化器執行過程
select * from information_schema.optimizer_trace;

Trace部分的內容

{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `staffs`.`id` AS `id`,`staffs`.`name` AS `name`,`staffs`.`age` AS `age`,`staffs`.`pos` AS `pos`,`staffs`.`add_time` AS `add_time` from `staffs` where ((`staffs`.`name` = `July`) and (`staffs`.`age` = 23))"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((`staffs`.`name` = `July`) and (`staffs`.`age` = 23))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "((`staffs`.`name` = `July`) and multiple equal(23, `staffs`.`age`))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "((`staffs`.`name` = `July`) and multiple equal(23, `staffs`.`age`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "((`staffs`.`name` = `July`) and multiple equal(23, `staffs`.`age`))"
                }
              ]
            }
          },
          {
            "substitute_generated_columns": {
            }
          },
          {
            "table_dependencies": [
              {
                "table": "`staffs`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`staffs`",
                "field": "name",
                "equals": "`July`",
                "null_rejecting": false
              },
              {
                "table": "`staffs`",
                "field": "age",
                "equals": "23",
                "null_rejecting": false
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "`staffs`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 6,
                    "cost": 4.3
                  },
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_nap",
                      "usable": true,
                      "key_parts": [
                        "name",
                        "age",
                        "pos",
                        "id"
                      ]
                    }
                  ],
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "idx_nap",
                        "ranges": [
                          "July <= name <= July AND 23 <= age <= 23"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 3,
                        "cost": 4.61,
                        "chosen": false,
                        "cause": "cost"
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  }
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`staffs`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                    //可以看到這邊MySQL計算得到使用索引的成本為2.6
                      "access_type": "ref",
                      "index": "idx_nap",
                      "rows": 3,
                      "cost": 2.6,
                      "chosen": true
                    },
                    {
                    //而全表掃描計算所得的成本為2.2
                      "rows_to_scan": 6,
                      "access_type": "scan",
                      "resulting_rows": 6,
                      "cost": 2.2,
                      "chosen": true
                    }
                  ]
                },
                //因此選擇了成本更低的scan
                "condition_filtering_pct": 100,
                "rows_for_plan": 6,
                "cost_for_plan": 2.2,
                "chosen": true
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`staffs`.`age` = 23) and (`staffs`.`name` = `July`))",
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "table": "`staffs`",
                  "attached": "((`staffs`.`age` = 23) and (`staffs`.`name` = `July`))"
                }
              ]
            }
          },
          {
            "refine_plan": [
              {
                "table": "`staffs`"
              }
            ]
          }
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
}

增加表資料量

-- 接下來增大表的資料量
INSERT INTO `staffs` (`name`, `age`, `pos`, `add_time`)
VALUES
    (`July`, 25, `dev`, `2018-06-04 16:30:17`),
    (`July`, 23, `dev1`, `2018-06-04 16:02:02`),
    (`July`, 23, `dev2`, `2018-06-04 16:02:02`),
    (`July`, 23, `dev3`, `2018-06-04 16:02:02`),
    (`July`, 23, `dev4`, `2018-06-04 16:02:02`),
    (`July`, 23, `dev6`, `2018-06-04 16:02:02`),
    (`July`, 23, `dev5`, `2018-06-04 16:02:02`),
    (`July`, 23, `dev7`, `2018-06-04 16:02:02`),
    (`July`, 23, `dev8`, `2018-06-04 16:02:02`),
    (`July`, 23, `dev9`, `2018-06-04 16:02:02`),
    (`July`, 23, `dev10`, `2018-06-04 16:02:02`),
    (`Clive`, 23, `dev1`, `2018-06-04 16:02:02`),
    (`Clive`, 23, `dev2`, `2018-06-04 16:02:02`),
    (`Clive`, 23, `dev3`, `2018-06-04 16:02:02`),
    (`Clive`, 23, `dev4`, `2018-06-04 16:02:02`),
    (`Clive`, 23, `dev6`, `2018-06-04 16:02:02`),
    (`Clive`, 23, `dev5`, `2018-06-04 16:02:02`),
    (`Clive`, 23, `dev7`, `2018-06-04 16:02:02`),
    (`Clive`, 23, `dev8`, `2018-06-04 16:02:02`),
    (`Clive`, 23, `dev9`, `2018-06-04 16:02:02`),
    (`Clive`, 23, `dev10`, `2018-06-04 16:02:02`);

執行Explain

-- 再次執行同樣的查詢語句，會發現走到索引上了
explain select * from staffs where name = `July` and age = 23;
id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  staffs  NULL    ref idx_nap idx_nap 78  const,const 13  100.00  NULL

檢視新的Trace內容

-- 再看下優化器執行過程
{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `staffs`.`id` AS `id`,`staffs`.`name` AS `name`,`staffs`.`age` AS `age`,`staffs`.`pos` AS `pos`,`staffs`.`add_time` AS `add_time` from `staffs` where ((`staffs`.`name` = `July`) and (`staffs`.`age` = 23))"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((`staffs`.`name` = `July`) and (`staffs`.`age` = 23))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "((`staffs`.`name` = `July`) and multiple equal(23, `staffs`.`age`))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "((`staffs`.`name` = `July`) and multiple equal(23, `staffs`.`age`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "((`staffs`.`name` = `July`) and multiple equal(23, `staffs`.`age`))"
                }
              ]
            }
          },
          {
            "substitute_generated_columns": {
            }
          },
          {
            "table_dependencies": [
              {
                "table": "`staffs`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`staffs`",
                "field": "name",
                "equals": "`July`",
                "null_rejecting": false
              },
              {
                "table": "`staffs`",
                "field": "age",
                "equals": "23",
                "null_rejecting": false
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "`staffs`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 27,
                    "cost": 8.5
                  },
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_nap",
                      "usable": true,
                      "key_parts": [
                        "name",
                        "age",
                        "pos",
                        "id"
                      ]
                    }
                  ],
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "idx_nap",
                        "ranges": [
                          "July <= name <= July AND 23 <= age <= 23"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 13,
                        "cost": 16.61,
                        "chosen": false,
                        "cause": "cost"
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  }
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`staffs`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                    //使用索引的成本變為了5.3
                      "access_type": "ref",
                      "index": "idx_nap",
                      "rows": 13,
                      "cost": 5.3,
                      "chosen": true
                    },
                    {
                    //scan的成本變為了6.4
                      "rows_to_scan": 27,
                      "access_type": "scan",
                      "resulting_rows": 27,
                      "cost": 6.4,
                      "chosen": false
                    }
                  ]
                },
                //使用索引查詢的成本更低，因此選擇了走索引
                "condition_filtering_pct": 100,
                "rows_for_plan": 13,
                "cost_for_plan": 5.3,
                "chosen": true
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`staffs`.`age` = 23) and (`staffs`.`name` = `July`))",
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "table": "`staffs`",
                  "attached": null
                }
              ]
            }
          },
          {
            "refine_plan": [
              {
                "table": "`staffs`"
              }
            ]
          }
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
}

結論

MySQL表資料量的大小，會影響索引的選擇，具體的情況還是通過Explain和Optimizer Trace來檢視與分析。

MySQL學習之影響優化器選擇索引因素
2021-03-10
MySql優化索引
表資料的儲存對索引的影響
2014-01-14
索引
MySQL字首索引和索引選擇性
2017-09-07
MySql索引
是什麼影響了資料庫索引選型？
2018-08-29
資料庫索引
影響rest api版本選擇的因素
2018-10-06
RESTAPI
MySQL資料庫索引選擇使用B+樹
2020-04-04
MySql資料庫索引
MySQL索引選擇及規則整理
2018-09-13
MySql索引
mysql聯合索引的選擇性
2017-01-18
MySql索引
Oracle主鍵選擇對插入的影響
2014-01-01
Oracle
分割槽表的不同操作對索引的影響
2009-04-14
索引
分割槽表全域性索引與本地索引的選擇
2016-05-04
索引
pandas索引和選擇資料
2020-12-28
索引
操作分割槽表對global和local索引的影響
2015-03-01
索引
MySQL innodb如何選擇一個聚簇索引
2018-10-12
MySql索引
「性別選擇」在遊戲中產生了哪些影響？
2020-02-06
遊戲
css屬性的選擇對動畫效能的影響
2016-03-06
CSS動畫
如何選擇普通索引和唯一索引《死磕MySQL系列五》
2021-11-03
索引MySql
mysql千萬級資料量根據索引優化查詢速度
2018-05-08
MySql索引優化
【Oracle】-【COMMIT對索引的影響】-從trace看COMMIT對索引的影響
2013-07-31
OracleMIT索引
16、MySQL Case-索引key對select count(*)的影響
2021-09-23
MySql索引
面試 (MySQL 索引為啥要選擇 B+ 樹)
2019-03-21
面試MySql索引
mysql索引為啥要選擇B+樹 (下)
2019-03-17
MySql索引
mysql索引為啥要選擇B+樹 (上)
2019-03-09
MySql索引
shrink 操作對索引的影響
2010-06-25
索引
Update操作對索引的影響
2009-06-08
索引
資料列not null對索引影響一例
2010-12-27
Null索引
“一個掃描槍一張表”，韻達選擇 TDengine 應對每日億級資料量
2022-04-26
單表掃描，MySQL索引選擇不正確並詳細解析OPTIMIZER_TRACE格式
2019-06-11
MySql索引
mysql千萬級資料量根據索引最佳化查詢速度
2021-09-09
MySql索引
【OPTIMIZATION】Oracle影響優化器選擇的相關技術
2021-10-15
Oracle優化
《高效能MySQL》筆記——MySQL建表資料型別的選擇
2018-11-07
MySql筆記資料型別
Elasticsearch 中為什麼選擇倒排索引而不選擇 B 樹索引
2021-10-26
Elasticsearch索引
mysql 表資料量大量查詢慢如何優化
2021-03-08
MySql優化
oracle分割槽表的常規操作導致對索引的影響
2017-11-09
Oracle索引
索引的選擇原則
2016-08-19
索引
CNNIC：手機遊戲留存時間短社交因素影響新遊戲選擇
2014-09-12
CNN遊戲
【Oracle】-【ROWNUM與索引】-索引對ROWNUM檢索的影響
2013-08-02
Oracle索引
mysql對資料庫表建索引
2017-05-27
MySql資料庫索引

表資料量影響MySQL索引選擇

現象

資料背景

Explain語句看下執行計劃

開啟優化器跟蹤優化過程

Trace部分的內容

增加表資料量

執行Explain

檢視新的Trace內容

結論

相關文章