| Bug #89824 | Optimizer bug | ||
|---|---|---|---|
| Submitted: | 27 Feb 2018 9:40 | Modified: | 1 Mar 2018 1:45 |
| Reporter: | 晓刚 薛 | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.7.14 | OS: | CentOS (6.7) |
| Assigned to: | CPU Architecture: | x86 | |
| Tags: | Optimizer bug | ||
[27 Feb 2018 13:59]
MySQL Verification Team
HI! Thank you for your report. However, this does not seem to be a bug. First of all, no index is used for the outer query simply because there is not appropriate index and, much more important, nested query is dependent one. Second, you can make it faster by not using this variant of the nested query, but the one with equality expression. Third and last, since only one row is result of the nested query, you can actually rewrite this query so that nested query is obsolete.
[28 Feb 2018 3:10]
晓刚 薛
thanks,but i don`t think so. you think that no index is used for the outer query simply because there is not appropriate index. But, in fact, CREATE TABLE `t_hy_process` ( `process_id` int(11) NOT NULL DEFAULT '0', `biz_type` varchar(30) CHARACTER SET utf8 DEFAULT NULL COMMENT '业务类型', `biz_type_name` varchar(50) CHARACTER SET utf8 DEFAULT NULL COMMENT '业务类型中文', `table_name` varchar(50) CHARACTER SET utf8 DEFAULT NULL COMMENT '业务表名', `biz_guid` int(11) DEFAULT NULL, `biz_desc` varchar(50) CHARACTER SET utf8 DEFAULT NULL COMMENT '业务描述', `member_type` varchar(10) CHARACTER SET utf8 DEFAULT NULL COMMENT '会员类型', `member_type_name` varchar(20) CHARACTER SET utf8 DEFAULT NULL COMMENT '会员类型中文', `member_guid` int(11) DEFAULT NULL, `member_name` varchar(200) CHARACTER SET utf8 DEFAULT NULL COMMENT '企业会员名称', `member_user_guid` int(11) DEFAULT NULL, `member_user_name` varchar(50) CHARACTER SET utf8 DEFAULT NULL COMMENT '企业账户名称', `begin_date` varchar(20) CHARACTER SET utf8 DEFAULT NULL COMMENT '开始时间', `end_date` varchar(20) CHARACTER SET utf8 DEFAULT NULL COMMENT '结束时间', `status` varchar(2) CHARACTER SET utf8 DEFAULT NULL COMMENT '1待审核、8审核退回、9审核通过', `status_name` varchar(10) CHARACTER SET utf8 DEFAULT NULL COMMENT '状态描述', `advice` varchar(200) CHARACTER SET utf8 DEFAULT NULL COMMENT '审批意见', `ext1` varchar(20) CHARACTER SET utf8 DEFAULT NULL, `ext2` varchar(20) CHARACTER SET utf8 DEFAULT NULL, `ext3` varchar(20) CHARACTER SET utf8 DEFAULT NULL, `remark` varchar(50) CHARACTER SET utf8 DEFAULT NULL COMMENT '备注', `create_person` varchar(20) CHARACTER SET utf8 DEFAULT NULL COMMENT '创建人', `create_date` varchar(20) CHARACTER SET utf8 DEFAULT NULL COMMENT '创建时间', `modi_person` varchar(20) CHARACTER SET utf8 DEFAULT NULL COMMENT '修改人', `modi_date` varchar(20) CHARACTER SET utf8 DEFAULT NULL COMMENT '修改时间', `way` int(2) DEFAULT NULL, `is_import` int(2) DEFAULT NULL, `import_date` varchar(32) COLLATE utf8_bin DEFAULT NULL, `bussiness_status` varchar(2) COLLATE utf8_bin DEFAULT NULL COMMENT '工商信息状态 1:未提交 2:待审核 3:审核退回 4:审核通过', `legal_person_status` varchar(2) COLLATE utf8_bin DEFAULT NULL COMMENT '法人信息状态 1:未提交 2:待审核 3:审核退回 4:审核通过', `invoice_status` varchar(2) COLLATE utf8_bin DEFAULT NULL COMMENT '开票信息状态 1:未提交 2:待审核 3:审核退回 4:审核通过', PRIMARY KEY (`process_id`), KEY `biz_guid` (`biz_guid`), KEY `member_guid` (`member_guid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='流程审批表' | process_id and member_guid all created index I am very much in favor of your second and third suggestions. I think so, too. This is the lower level of developers. However, this situation is the primary key retrieval in Oracle, and no full table scans will occur.
[28 Feb 2018 13:32]
MySQL Verification Team
Hi! It is easy to see why no index can be used. Outer query does not have a condition on which index can be used, since it only has: ......... WHERE some_col IN (........); It is not a condition that can use any index. Hence, you can follow my other advices.
[1 Mar 2018 1:45]
晓刚 薛
I'm sorry, I can't agree with you. Process_id is the primary key, and member_guid has an index. A subquery gets a line of data through an index, and then gets the maximum value, which is a constant. The parent query condition is a primary key query by the constant of the primary key in. So it should be the index. The last thing I want to say is. Select * from t_hy_process WHERE process_id in. ); To change this "in" to "=", you can use the index. So I think it's a bug. So you said: "It is not a condition that can use any index." I can't accept. Of course, I did the rewriting, and it was OK. But this is an evasive strategy. I think "in" (const) does not use an index, and "=" (const) uses the index. This is a problem. So I stick to my point of view
Description: mysql> explain select * from t_hy_process WHERE process_id in ( select max(process_id) from t_hy_process WHERE biz_type in ( '3' , '4' ) and member_guid = 30116 GROUP BY member_guid ); +----+-------------+--------------+------------+------+---------------+-------------+---------+-------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+------+---------------+-------------+---------+-------+-------+----------+-------------+ | 1 | PRIMARY | t_hy_process | NULL | ALL | NULL | NULL | NULL | NULL | 92529 | 100 | Using where | | 2 | SUBQUERY | t_hy_process | NULL | ref | member_guid | member_guid | 5 | const | 1 | 20 | Using where | +----+-------------+--------------+------------+------+---------------+-------------+---------+-------+-------+----------+-------------+ 2 rows in set process_id is primary。 SUBQUERY result is max（primary），must one row。 so two querys should primary scan。 but now，full table scan。why？ How to repeat: select max(process_id) from t_hy_process WHERE biz_type in ( '3' , '4' ) and member_guid = 30116 GROUP BY member_guid; +-----------------+ | max(process_id) | +-----------------+ | 30116 | +-----------------+ 1 row in set mysql> select * from t_hy_process WHERE process_id in ( select max(process_id) from t_hy_process WHERE biz_type in ( '3' , '4' ) and member_guid = 30116 GROUP BY member_guid ); +------------+----------+---------------+------------+----------+----------+-------------+------------------+-------------+----------------------+------------------+------------------+---------------------+----------+--------+-------------+--------+------+------+------+--------+---------------+---------------------+-------------+-----------+------+-----------+---------------------+------------------+---------------------+----------------+ | process_id | biz_type | biz_type_name | table_name | biz_guid | biz_desc | member_type | member_type_name | member_guid | member_name | member_user_guid | member_user_name | begin_date | end_date | status | status_name | advice | ext1 | ext2 | ext3 | remark | create_person | create_date | modi_person | modi_date | way | is_import | import_date | bussiness_status | legal_person_status | invoice_status | +------------+----------+---------------+------------+----------+----------+-------------+------------------+-------------+----------------------+------------------+------------------+---------------------+----------+--------+-------------+--------+------+------+------+--------+---------------+---------------------+-------------+-----------+------+-----------+---------------------+------------------+---------------------+----------------+ | 30116 | 3 | NULL | NULL | 30116 | NULL | NULL | NULL | 30116 | 上海归塘贸易有限公司 | NULL | NULL | 2014-08-15 15:43:15 | NULL | 8 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 2014-08-15 15:43:15 | NULL | NULL | NULL | 1 | 2016-09-17 17:23:41 | NULL | NULL | NULL | +------------+----------+---------------+------------+----------+----------+-------------+------------------+-------------+----------------------+------------------+------------------+---------------------+----------+--------+-------------+--------+------+------+------+--------+---------------+---------------------+-------------+-----------+------+-----------+---------------------+------------------+---------------------+----------------+ 1 row in set Suggested fix: The execution plan subqueries the master key parent to query the whole table, and in fact, it is estimated to be the primary key. But the execution of the plan gave me a wrong direction. I've seen the execution plan index, and I actually walk the whole table. But I didn't see the full schedule of the execution plan, and it was actually indexed.