美文网首页程序猿阵线联盟-汇总各类技术干货技术文MySQL
MySQL表的数据量大小会影响索引的选择

MySQL表的数据量大小会影响索引的选择

作者: ImClive | 来源:发表于2018-06-04 18:15 被阅读5次

    现象

    新建了一张员工表，插入了少量数据，索引中所有的字段均在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表的数据量大小会影响索引的选择

        本文链接：https://www.haomeiwen.com/subject/ibbysftx.html

        延伸阅读

        深度阅读

        • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

        栏目导航

        热点阅读

        程序猿阵线联盟-汇总各类技术干货

        技术文

        MySQL

        关于我们|服务条款|联系我们|MySQL表的数据量大小会影响索引的选择|投稿指南|网站地图|RSS订阅|排版工具|手机版

        提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

        Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

        备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

        本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

        所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！