Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2275301
  • 博文数量: 295
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 2185
  • 用 户 组: 普通用户
  • 注册时间: 2014-03-31 14:30
个人简介

自己慢慢积累。

文章分类

全部博文(295)

分类: Mysql/postgreSQL

2018-07-09 11:40:23

转自:
https://www.cnblogs.com/katec/p/9276746.html
专注MySQL、Python。
QQ讨论群:中国MySql第一社区 457697103


MySQL是自动会选择它认为好的执行划,但是MySQL毕竟是程序,还没有达到像人类思考这么智能,还是通过一些按部就班的算法实现最优执行计划(基于cost)的选择。下面就是一个真实的案例,带你来看看MySQL也有失误的时候,这种情况不在少数。


注意:一下分析是在MySQl5.6.16版本下,其它版本未验证。


表结构:
CREATE TABLE `test_tab` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `token` varchar(100) NOT NULL,
  `user_id` int(10) NOT NULL DEFAULT '0',
  `a` int(10) NOT NULL DEFAULT '0',
  `b` int(10) NOT NULL,
  `t_logo` varchar(255) NOT NULL DEFAULT '',
  `t_name` varchar(50) NOT NULL,
  `t_contact_name` varchar(20) NOT NULL,
  `z` varchar(20) NOT NULL,
  `c` varchar(30) DEFAULT NULL,
  `d` varchar(100) NOT NULL DEFAULT '0',
  `e` varchar(100) NOT NULL DEFAULT '0',
  `t_province` varchar(50) DEFAULT NULL,
  `f` varchar(50) DEFAULT NULL,
  `t_district` varchar(50) DEFAULT NULL,
  `g` varchar(100) NOT NULL,
  `t_info` text NOT NULL,
  `h` char(1) NOT NULL DEFAULT '0',
  `i` tinyint(3) DEFAULT '0',
  `j` decimal(10,2) NOT NULL DEFAULT '0.00' ,
  `t_add_time` int(10) NOT NULL DEFAULT '0' ,
  `t_update_time` int(10) NOT NULL DEFAULT '0' ',
  `t_begin_time` int(10) NOT NULL DEFAULT '0' ,
  `t_end_time` int(10) NOT NULL DEFAULT '0' ,
  `k` char(1) NOT NULL DEFAULT '1' ,
  `t_is_check` char(1) NOT NULL DEFAULT '0' ,
  `l` int(10) NOT NULL DEFAULT '0',
  `u` int(10) NOT NULL DEFAULT '0',
  `is_delete` tinyint(1) unsigned NOT NULL DEFAULT '0' ,
  `p` varchar(50) DEFAULT NULL,
  `sort` int(11) NOT NULL DEFAULT '999',
  PRIMARY KEY (`id`),
  KEY `z` (`z`),
  KEY `t_name` (`t_name`),
  KEY `token` (`token`,`sort`),
  KEY `idx_0` (`token`,`user_id`,`is_delete`),
  KEY `idx_doc_time` (`t_add_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 


表记录数:


select count(*) from user.`test_tab`;
+----------+
| count(*) |
+----------+
|  2865011 |
+----------+


执行sql:
SELECT `id`,`t_name` FROM user.`test_tab` WHERE `token` = 'xx_8cilc73a22hm' AND `t_is_check` = 1 AND `is_delete` = 0 ORDER BY id desc LIMIT 5;
+---------+--------------------------------+
| id      | t_name                         |
+---------+--------------------------------+
| 2983295 | 《管理之道》                   |
| 2983289 | 《气功是怎么炼成丹》            |
| 2925294 | 小推车配送                     |
| 2925292 | 特色小拉面                     |
| 1101709 | 惠世本源                       |
+---------+--------------------------------+
rows in set (1.25 sec)     --可以看到这个sql花了1.25秒查询出来了结果, 这是不可接受的速度


我们来看看执行计划:
explain SELECT `id`,`t_name` FROM user.`test_tab` WHERE `token` = 'xx_8cilc73a22hm' AND `t_is_check` = 1 AND `is_delete` = 0 ORDER BY id desc LIMIT 5
+----+-------------+--------------------+-------+-----------------+---------+---------+------+------+-------------+
| id | select_type | table              | type  | possible_keys   | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------------------+-------+-----------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | test_tab           | index | token,idx_0     | PRIMARY | 4       | NULL | 1185 | Using where |
+----+-------------+--------------------+-------+-----------------+---------+---------+------+------+-------------+
说明:执行计划看着很正常,使用了主键索引,没啥大问题,为什么速度这么慢呢?因为主键字段没有范围条件限制,要把所有数据读出来,所以慢了!!!




我们换个索引看看执行效率:
SELECT `id`,`t_name` FROM user.`test_tab` force index(token) WHERE `token` = 'xx_8cilc73a22hm' AND `t_is_check` = 1 AND `is_delete` = 0 ORDER BY id desc LIMIT 5;
+---------+--------------------------------+
| id      | t_name                         |
+---------+--------------------------------+
| 2983295 | 《管理之道》                   |
| 2983289 | 《气功是怎么炼成丹》            |
| 2925294 | 小推车配送                     |
| 2925292 | 特色小拉面                     |
| 1101709 | 惠世本源                       |
+---------+--------------------------------+
rows in set (0.03 sec)   --速度这么快,比走主键索引快多了


看下执行计划:


explain SELECT `id`,`t_name` FROM user.`test_tab` force index(token) WHERE `token` = 'xx_8cilc73a22hm' AND `t_is_check` = 1 AND `is_delete` = 0 ORDER BY id desc LIMIT 5
+----+-------------+--------------------+------+---------------+-------+---------+-------+-------+----------------------------------------------------+
| id | select_type | table              | type | possible_keys | key   | key_len | ref   | rows  | Extra                                              |
+----+-------------+--------------------+------+---------------+-------+---------+-------+-------+----------------------------------------------------+
|  1 | SIMPLE      | test_tab           | ref  | token         | token | 302     | const | 11506 | Using index condition; Using where; Using filesort |
+----+-------------+--------------------+------+---------------+-------+---------+-------+-------+----------------------------------------------------+
说明:看着执行计划有Using filesort为什么速度会快呢?看看Using index condition这个原来使用了索引下推,也就是说从从存储引擎获得的数据只有5行,然后再进行排序,所以速度很快,哈哈!!这也就是为什么快的原因了。




那为什么MySQL没有选择这个执行token索引呢?
下面我们看看具体原因,如果要看详细执行计划,我们之前是不是说过要看什么呢?optimizer_trace!!回答正确。


set optimizer_trace=1;
sql语句
set
optimizer_trace=0;
select * from information_schema.optimizer_trace;


下面就是我们要看的走主键索引慢的详细的执行计划:




| SELECT `id`,`t_name` FROM user.`test_tab` WHERE `token` = 'xx_8cilc73a22hm' AND `t_is_check` = 1 AND `is_delete` = 0 ORDER BY id desc LIMIT 5 | {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `user`.`test_tab`.`id` AS `id`,`user`.`test_tab`.`t_name` AS `t_name` from `user`.`test_tab` where ((`user`.`test_tab`.`token` = 'xx_8cilc73a22hm') and (`user`.`test_tab`.`t_is_check` = 1) and (`user`.`test_tab`.`is_delete` = 0)) order by `user`.`test_tab`.`id` desc limit 5"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((`user`.`test_tab`.`token` = 'xx_8cilc73a22hm') and (`user`.`test_tab`.`t_is_check` = 1) and (`user`.`test_tab`.`is_delete` = 0))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "((`user`.`test_tab`.`token` = 'xx_8cilc73a22hm') and (`user`.`test_tab`.`t_is_check` = 1) and multiple equal(0, `user`.`test_tab`.`is_delete`))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "((`user`.`test_tab`.`token` = 'xx_8cilc73a22hm') and (`user`.`test_tab`.`t_is_check` = 1) and multiple equal(0, `user`.`test_tab`.`is_delete`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "((`user`.`test_tab`.`token` = 'xx_8cilc73a22hm') and (`user`.`test_tab`.`t_is_check` = 1) and multiple equal(0, `user`.`test_tab`.`is_delete`))"
                }
              ]
            }
          },
          {
            "table_dependencies": [
              {
                "table": "`user`.`test_tab`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`user`.`test_tab`",
                "field": "token",
                "equals": "'xx_8cilc73a22hm'",
                "null_rejecting": false
              },
              {
                "table": "`user`.`test_tab`",
                "field": "token",
                "equals": "'xx_8cilc73a22hm'",
                "null_rejecting": false
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "`user`.`test_tab`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 2597778,
                    "cost": 572230
                  },
                  "potential_range_indices": [   
                  {
                      "index": "PRIMARY",
                      "usable": false,      --可以看到根据范围条件选择,主键索引是false的,也就是范围条件选择主键是不好,那为啥实际为啥还使用主键呢?往下看。。。
                     "cause": "not_applicable"
                    },
                    {
                      "index": "z",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "t_name",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "token",
                      "usable": true,      --范围查询意向选择token索引。这没有问题啊!!!再往下看
                      "key_parts": [
                        "token",
                        "sort",
                        "id"
                      ]
                    },
                    {
                      "index": "idx_0",
                      "usable": true,
                      "key_parts": [
                        "token",
                        "user_id",
                        "is_delete",
                        "id"
                      ]
                    },
                    {
                      "index": "idx_doc_time",
                      "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": "token",
                        "ranges": [
                          "xx_8cilc73a22hm <= token <= xx_8cilc73a22hm"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 11506,
                        "cost": 13808,
                        "chosen": true
                      },
                      {
                        "index": "idx_0",
                        "ranges": [
                          "xx_8cilc73a22hm <= token <= xx_8cilc73a22hm"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 10960,
                        "cost": 13153,
                        "chosen": true
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  },
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "idx_0",
                      "rows": 10960,
                      "ranges": [
                        "xx_8cilc73a22hm <= token <= xx_8cilc73a22hm"
                      ]
                    },
                    "rows_for_plan": 10960,
                    "cost_for_plan": 13153,
                    "chosen": true
                  }
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`user`.`test_tab`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "token",
                      "rows": 11506,
                      "cost": 13807,
                      "chosen": true
                    },
                    {
                      "access_type": "ref",
                      "index": "idx_0",
                      "rows": 10960,
                      "cost": 13152,
                      "chosen": true
                    },
                    {
                      "access_type": "range",
                      "cause": "heuristic_index_cheaper",
                      "chosen": false
                    }
                  ]
                },
                "cost_for_plan": 13152,
                "rows_for_plan": 10960,
                "chosen": true
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`user`.`test_tab`.`is_delete` = 0) and (`user`.`test_tab`.`token` = 'xx_8cilc73a22hm') and (`user`.`test_tab`.`t_is_check` = 1))",
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "table": "`user`.`test_tab`",
                  "attached": "((`user`.`test_tab`.`is_delete` = 0) and (`user`.`test_tab`.`token` = 'xx_8cilc73a22hm') and (`user`.`test_tab`.`t_is_check` = 1))"
                }
              ]
            }
          },
          {
            "clause_processing": {
              "clause": "ORDER BY",
              "original_clause": "`user`.`test_tab`.`id` desc",
              "items": [
                {
                  "item": "`user`.`test_tab`.`id`"
                }
              ],
              "resulting_clause_is_simple": true,
              "resulting_clause": "`user`.`test_tab`.`id` desc"
            }
          },
          {
            "refine_plan": [
              {
                "table": "`user`.`test_tab`",
                "pushed_index_condition": "((`user`.`test_tab`.`is_delete` = 0) and (`user`.`test_tab`.`token` = 'xx_8cilc73a22hm'))",
                "table_condition_attached": "(`user`.`test_tab`.`t_is_check` = 1)"
              }
            ]
          },
          {
            "added_back_ref_condition": "((`user`.`test_tab`.`token` <=> 'xx_8cilc73a22hm') and (`user`.`test_tab`.`t_is_check` = 1))"
          },
          {
            "reconsidering_access_paths_for_index_ordering": {
              "clause": "ORDER BY",
              "index_order_summary": {
                "table": "`user`.`test_tab`",
                "index_provides_order": true,                  --关键来了,在判断排序的时候发现主键字段id能够排序,所以MySQL认为排序是花费很大的操作,使用这个主键字段是有序的,不用排序了,就使用它吧!!!!!我靠这不就是错了吗。虽然排序消耗时间,但是你为啥不判断下排序结果集大小啊,扫描所有数据(2865011)和排序5行(这里是排序1000多行还是5行不确定,就当mysql最优5行吧)数据哪个消耗更低?
                "order_direction": "desc",
                "disabled_pushed_condition_on_old_index": true,
                "index": "PRIMARY",
                "plan_changed": true,
                "access_type": "index_scan"
              }
            }
          }
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]







总结下:
1.MySQL在进行分析的时候会把等值、范围的执行消耗都分析出来。
2.在最后选择上选择了不用排序的主键索引。


为什么会选择错误?我推测原因:可能下索引条件下推还没有完善到执行计划中,没有判断索引下推的情况。




给我们的启发:不要完全相信MySQL,需要自己去验证,发现问题,MySQL不是十全十美的,还需要有很多改善!!!!!!
阅读(1395) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~