设为首页 加入收藏

TOP

表数据量影响MySQL索引选择(一)
2018-10-28 16:08:23 】 浏览:141
Tags:数据 影响 MySQL 索引 选择

现象


新建了一张员工表,插入了少量数据,索引中所有的字段均在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": [
                {
       
编程开发网

首页 上一页 1 2 3 4 5 6 7 下一页 尾页 1/10/10
】【打印繁体】【投稿】【收藏】 【推荐】【举报】【评论】 【关闭】 【返回顶部
上一篇MongoDB 与 MySQL,你选择谁? 下一篇MySQL ORDER BY主键id加LIMIT限制..

评论

帐  号: 密码: (新用户注册)
验 证 码:
表  情:
内  容:

array(4) { ["type"]=> int(8) ["message"]=> string(24) "Undefined variable: jobs" ["file"]=> string(32) "/mnt/wp/cppentry/do/bencandy.php" ["line"]=> int(214) }