SQL优化：执行计划出错之重复索引干扰

Contents

1. 本篇为执行计划错误系列第二篇，第一篇地址
2. 背景
3. 最后总结
4. 详细的执行代价评估

本篇为执行计划错误系列第二篇，第一篇地址

背景

MySQL版本

社区版： MySQL 5.6.27 InnoDB

表结构

| test_table | CREATE TABLE test_table (
TagId int(11) NOT NULL AUTO_INCREMENT COMMENT ‘’,
TagType int(11) DEFAULT NULL COMMENT ‘’,
SubType int(11) DEFAULT NULL COMMENT ‘’,
CommId int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘’,
TagFlag int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘’,
TagName varchar(255) DEFAULT NULL COMMENT ‘’,
OrderId int(11) DEFAULT ‘0’ COMMENT ‘’,
Unum int(10) NOT NULL DEFAULT ‘0’ COMMENT ‘’,
IsBest int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘’,
BrokerId int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘’,
AddDate int(11) DEFAULT NULL COMMENT ‘’,
UpdateDate int(11) DEFAULT NULL COMMENT ‘’,
updatetime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
tmpnum int(10) DEFAULT ‘0’ COMMENT ‘’,
cityid int(11) DEFAULT ‘0’ COMMENT ‘’,
PRIMARY KEY (TagId),
KEY idx_4 (IsBest,TagFlag,CommId),
KEY idx_1 (TagType,TagName,CommId),
KEY idxnew (UpdateDate),
KEY idx_lc (TagType,TagName,TagId)
KEY idx_lc_2 (TagName,TagType,TagId),
) ENGINE=InnoDB AUTO_INCREMENT=19387169 DEFAULT CHARSET=utf8 |

index 选择性

+-----------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table           | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test_table      |          0 | PRIMARY    |            1 | TagId       | A         |     1128048 |     NULL | NULL   |      | BTREE      |         |               |
| test_table      |          1 | idx_4      |            1 | IsBest      | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| test_table      |          1 | idx_4      |            2 | TagFlag     | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
| test_table      |          1 | idx_4      |            3 | CommId      | A         |       56402 |     NULL | NULL   |      | BTREE      |         |               |
| test_table      |          1 | idx_1      |            1 | TagType     | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| test_table      |          1 | idx_1      |            2 | TagName     | A         |     1128048 |     NULL | NULL   | YES  | BTREE      |         |               |
| test_table      |          1 | idx_1      |            3 | TagId       | A         |     1128048 |     NULL | NULL   |      | BTREE      |         |               |
| test_table      |          1 | idxnew     |            1 | UpdateDate  | A         |     1128048 |     NULL | NULL   | YES  | BTREE      |         |               |
| test_table      |          1 | idx_lc     |            1 | TagType     | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| test_table      |          1 | idx_lc     |            2 | TagName     | A         |     1128048 |     NULL | NULL   | YES  | BTREE      |         |               |
| test_table      |          1 | idx_lc     |            3 | TagId       | A         |     1128048 |     NULL | NULL   |      | BTREE      |         |               |
| test_table      |          1 | idx_lc_2   |            1 | TagName     | A         |     1128048 |     NULL | NULL   | YES  | BTREE      |         |               |
| test_table      |          1 | idx_lc_2   |            2 | TagType     | A         |     1128048 |     NULL | NULL   | YES  | BTREE      |         |               |
| test_table      |          1 | idx_lc_2   |            3 | TagId       | A         |     1128048 |     NULL | NULL   |      | BTREE      |         |               |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+-----------------+

问题SQL

select * from test_db.test_table where TagType = ‘1’ and TagName = ‘采光充足’ order by TagId limit 1;

问题再哪里？

执行计划

root:test_db> explain select * from test_db.test_table where TagType = 1 and TagName = ‘采光充足’ order by TagId limit 1;
+——+——————-+————————-+———-+———————-+————-+————-+———+———+——————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+——+——————-+————————-+———-+———————-+————-+————-+———+———+——————-+
| 1 | SIMPLE | test_table | index | idx_1,idx_lc,idx_lc_2 | PRIMARY | 4 | NULL | 386 | Using where |
+——+——————-+————————-+———-+———————-+————-+————-+———+———+——————-+
1 row in set (0.00 sec)

Time: 160313 0:07:16
User@Host: [] @ [] Id: 7123914939
Query_time: 0.246356 Lock_time: 0.000036 Rows_sent: 1 Rows_examined: 470135
SET timestamp=1457798836;
select TagId,TagType,SubType,CommId,TagFlag,TagName,OrderId,Unum,IsBest,BrokerId,AddDate,UpdateDate,updatetime,tmpnum,cityid from test_table where TagType = 1 and TagName = ‘采光充足’ order by TagId ASC limit 1 ;

痛点分析

1）primary ， type=index，表示全索引扫描。

2) 按照索引原理，这样的SQL语句应该使用的正确的索引是：idx_lc， type=ref。

root:test_db> explain select * from test_db.test_table force index(idx_lc) where TagType = 1 and TagName = ‘采光充足’ order by TagId limit 1;

+----+-------------+-----------------+------+---------------+--------+---------+-------------+------+------------------------------------+
| id | select_type | table           | type | possible_keys | key    | key_len | ref         | rows | Extra                              |
+----+-------------+-----------------+------+---------------+--------+---------+-------------+------+------------------------------------+
|  1 | SIMPLE      | test_table | ref  | idx_lc        | idx_lc | 773     | const,const | 2930 | Using index condition; Using where |
+----+-------------+-----------------+------+---------------+--------+---------+-------------+------+------------------------------------+
1 row in set (0.00 sec)

3）到这里，肯定大部分人问，既然force index可以解决问题，为啥不让开发修改SQL为force index呢?

a) 能不让开发修改，就不让开发修改，这样成本最低。

b）况且，这根本就不是SQL语句本身的问题，为什么要修改SQL呢？

c）如果以后不小心删掉了这个索引，那么force index的语句就会报错，会导致业务中断。

以上种种原因，force index 始终不是最终的积极方案。

那么问题来了

使用idx_lc代价最小，速度最快。为什么MySQL不用呢？

继续分析

如果观察仔细的小伙伴们会发现，以上的索引有很多是重复的

PRIMARY KEY (TagId),
KEY idx_4 (IsBest,TagFlag,CommId),
KEY idx_1 (TagType,TagName,CommId),
KEY idxnew (UpdateDate),
KEY idx_lc (TagType,TagName,TagId)
KEY idx_lc_2 (TagName,TagType,TagId),

possibles key 的选择问题

为什么在 idx_1,idx_lc,idx_lc_2 中却选择的 primary key 呢？

我试着删掉一个索引试试看

root:test_db> alter table test_table drop index idx_1;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
root:test_db> explain select * from test_db.test_table force index(idx_lc) where TagType = 1 and TagName = ‘采光充足’ order by TagId limit 1;
+——+——————-+————————-+———+———————-+————+————-+——————-+———+——————————————————+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+——+——————-+————————-+———+———————-+————+————-+——————-+———+——————————————————+
| 1 | SIMPLE | test_table | ref | idx_lc,idx_lc_2 | idx_lc | 773 | const,const | 2930 | Using index condition; Using where |
+——+——————-+————————-+———+———————-+————+————-+——————-+———+——————————————————+
1 row in set (0.00 sec)

这样，执行语句奇迹般的好了。让我开始对index 的顺序问题产生怀疑

ok，我在重建idx_1索引

PRIMARY KEY (TagId),
KEY idx_4 (IsBest,TagFlag,CommId),
KEY idxnew (UpdateDate),
KEY idx_lc (TagType,TagName,TagId)
KEY idx_lc_2 (TagName,TagType,TagId),
KEY idx_1 (TagType,TagName,CommId),

root:test_db>  explain select * from test_db.test_table force index(idx_lc) where `TagType` = 1  and `TagName` = '采光充足'       order by TagId limit   1;  
+----+-------------+-----------------+------+---------------+--------+---------+-------------+------+------------------------------------+  
| id | select_type | table           | type | possible_keys | key    | key_len |   ref         | rows | Extra                              |
+----+-------------+-----------------+------+---------------+--------+---------+-------------+------+------------------------------------+  
|  1 | SIMPLE      | test_table | ref  | idx_lc,idx_lc_2,idx_1        | idx_lc | 773     | const,const | 2930 | Using index condition; Using where |  
+----+-------------+-----------------+------+---------------+--------+---------+-------------+------+------------------------------------+  
1 row in set (0.00 sec)

我再将idx_lc 删掉后再重建呢？

PRIMARY KEY (TagId),
KEY idx_4 (IsBest,TagFlag,CommId),
KEY idxnew (UpdateDate),
KEY idx_lc_2 (TagType,TagName,TagId)
KEY idx_1 (TagType,TagName,CommId),
KEY idx_lc (TagType,TagName,TagId)

root:test_db>  explain select * from test_db.test_table force index(idx_lc) where `TagType` = 1  and `TagName` = '采光充足'       order by TagId limit   1;
+----+-------------+-----------------+------+---------------+--------+---------+-------------+------+------------------------------------+
| id | select_type | table           | type | possible_keys | key    | key_len | ref         | rows | Extra                              |
+----+-------------+-----------------+------+---------------+--------+---------+-------------+------+------------------------------------+
|  1 | SIMPLE      | test_table | ref  | idx_lc_2,idx_1,idx_lc        | idx_lc | 773     | const,const | 2930 | Using index condition; Using where |
+----+-------------+-----------------+------+---------------+--------+---------+-------------+------+------------------------------------+
1 row in set (0.00 sec)

最后的优化建议：

1 2	删除掉 idx_1 干扰索引。

疑问

MySQL不至于这么傻吧？应该不是根据RBO哇，说好的CBO呢？

所以，我又在test库中创新创建了一张表，表结构和原来一样，数据完全导入到过来

use test;
create table lc_test like xx_db.test_table;
insert into lc_test select * from xx_db.test_table;
Query OK, 1137914 rows affected (25.30 sec)
Records: 1137914  Duplicates: 0  Warnings: 0
| lc_test | CREATE TABLE `lc_test` (
  `TagId` int(11) NOT NULL AUTO_INCREMENT COMMENT '',
  `TagType` int(11) DEFAULT NULL COMMENT '',
  `SubType` int(11) DEFAULT NULL COMMENT '',
  `CommId` int(11) NOT NULL DEFAULT '0' COMMENT '',
  `TagFlag` int(11) NOT NULL DEFAULT '0' COMMENT '',
  `TagName` varchar(255) DEFAULT NULL COMMENT '',
  `OrderId` int(11) DEFAULT '0' COMMENT '',
  `Unum` int(10) NOT NULL DEFAULT '0' COMMENT '',
  `IsBest` int(11) NOT NULL DEFAULT '0' COMMENT '0',
  `BrokerId` int(11) NOT NULL DEFAULT '0' COMMENT '',
  `AddDate` int(11) DEFAULT NULL COMMENT '',
  `UpdateDate` int(11) DEFAULT NULL COMMENT '',
  `updatetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `tmpnum` int(10) DEFAULT '0' COMMENT '',
  `cityid` int(11) DEFAULT '0' COMMENT '',
  PRIMARY KEY (`TagId`),
  KEY `idx_4` (`IsBest`,`TagFlag`,`CommId`),
  KEY `idx_1` (`TagType`,`TagName`,`CommId`),
  KEY `idxnew` (`UpdateDate`),
  KEY `idx_lc` (`TagType`,`TagName`,`TagId`),
  KEY `idx_lc_2` (`TagName`,`TagType`,`TagId`)
) ENGINE=InnoDB AUTO_INCREMENT=19387105 DEFAULT CHARSET=utf8 |
root:test> explain select * from lc_test  where `TagType` = 1  and `TagName` = '采光充足'       order by TagId limit   1;
+----+-------------+---------+------+-----------------------+--------+---------+-------------+------+------------------------------------+
| id | select_type | table   | type | possible_keys         | key    | key_len | ref         | rows | Extra                              |
+----+-------------+---------+------+-----------------------+--------+---------+-------------+------+------------------------------------+
|  1 | SIMPLE      | lc_test | ref  | idx_1,idx_lc,idx_lc_2 | idx_lc | 773     | const,const | 2928 | Using index condition; Using where |
+----+-------------+---------+------+-----------------------+--------+---------+-------------+------+------------------------------------+
1 row in set (0.00 sec)
这里，虽然顺序是  idx_1,idx_lc,idx_lc_2， 但是它选择了 idx_lc 正确的索引。
这又说明，并不是我们想象的按照索引顺序选择。

最后总结

从上述测试来看，MySQL执行计划应该是 CBO + CRO的结合体？待确认
一般情况下，MySQL可以很好的选择执行计划，但是收到重复索引，数据分布等的干扰后，可能不准确
我们能做的就是
- 等待官方优化器的改进
- 尽量避开干扰项，如：主动删除重复性索引等

详细的执行代价评估

错误的选择

{\
  "steps": [\
    {\
      "join_preparation": {\
        "select#": 1,\
        "steps": [\
          {\
            "expanded_query": "/* select#1 */ select `test_table`.`TagId` AS `TagId`,`test_table`.`TagType` AS `TagType`,`test_table`.`SubType` AS `SubType`,`test_table`.`CommId` AS `CommId`,`test_table`.`TagFlag` AS `TagFlag`,`test_table`.`TagName` AS `TagName`,`test_table`.`OrderId` AS `OrderId`,`test_table`.`Unum` AS `Unum`,`test_table`.`IsBest` AS `IsBest`,`test_table`.`BrokerId` AS `BrokerId`,`test_table`.`AddDate` AS `AddDate`,`test_table`.`UpdateDate` AS `UpdateDate`,`test_table`.`updatetime` AS `updatetime`,`test_table`.`tmpnum` AS `tmpnum`,`test_table`.`cityid` AS `cityid` from `test_table` where ((`test_table`.`TagType` = 1) and (`test_table`.`TagName` = '采光充足')) order by `test_table`.`TagId` limit 1"\
          }\
        ]\
      }\
    },\
    {\
      "join_optimization": {\
        "select#": 1,\
        "steps": [\
          {\
            "condition_processing": {\
              "condition": "WHERE",\
              "original_condition": "((`test_table`.`TagType` = 1) and (`test_table`.`TagName` = '采光充足'))",\
              "steps": [\
                {\
                  "transformation": "equality_propagation",\
                  "resulting_condition": "((`test_table`.`TagName` = '采光充足') and multiple equal(1, `test_table`.`TagType`))"\
                },\
                {\
                  "transformation": "constant_propagation",\
                  "resulting_condition": "((`test_table`.`TagName` = '采光充足') and multiple equal(1, `test_table`.`TagType`))"\
                },\
                {\
                  "transformation": "trivial_condition_removal",\
                  "resulting_condition": "((`test_table`.`TagName` = '采光充足') and multiple equal(1, `test_table`.`TagType`))"\
                }\
              ]\
            }\
          },\
          {\
            "table_dependencies": [\
              {\
                "table": "`test_table`",\
                "row_may_be_null": false,\
                "map_bit": 0,\
                "depends_on_map_bits": [\
                ]\
              }\
            ]\
          },\
          {\
            "ref_optimizer_key_uses": [\
              {\
                "table": "`test_table`",\
                "field": "TagType",\
                "equals": "1",\
                "null_rejecting": false\
              },\
              {\
                "table": "`test_table`",\
                "field": "TagName",\
                "equals": "'采光充足'",\
                "null_rejecting": false\
              },\
              {\
                "table": "`test_table`",\
                "field": "TagType",\
                "equals": "1",\
                "null_rejecting": false\
              },\
              {\
                "table": "`test_table`",\
                "field": "TagName",\
                "equals": "'采光充足'",\
                "null_rejecting": false\
              },\
              {\
                "table": "`test_table`",\
                "field": "TagName",\
                "equals": "'采光充足'",\
                "null_rejecting": false\
              },\
              {\
                "table": "`test_table`",\
                "field": "TagType",\
                "equals": "1",\
                "null_rejecting": false\
              }\
            ]\
          },\
          {\
            "rows_estimation": [\
              {\
                "table": "`test_table`",\
                "range_analysis": {\
                  "table_scan": {\
                    "rows": 1132201,\
                    "cost": 233202\
                  },\
                  "potential_range_indices": [\
                    {\
                      "index": "PRIMARY",\
                      "usable": false,\
                      "cause": "not_applicable"\
                    },\
                    {\
                      "index": "idx_4",\
                      "usable": false,\
                      "cause": "not_applicable"\
                    },\
                    {\
                      "index": "idx_1",\
                      "usable": true,\
                      "key_parts": [\
                        "TagType",\
                        "TagName",\
                        "CommId",\
                        "TagId"\
                      ]\
                    },\
                    {\
                      "index": "idxnew",\
                      "usable": false,\
                      "cause": "not_applicable"\
                    },\
                    {\
                      "index": "idx_lc",\
                      "usable": true,\
                      "key_parts": [\
                        "TagType",\
                        "TagName",\
                        "TagId"\
                      ]\
                    },\
                    {\
                      "index": "idx_lc_2",\
                      "usable": true,\
                      "key_parts": [\
                        "TagName",\
                        "TagType",\
                        "TagId"\
                      ]\
                    }\
                  ],\
                  "setup_range_conditions": [\
                  ],\
                  "group_index_range": {\
                    "chosen": false,\
                    "cause": "not_group_by_or_distinct"\
                  },\
                  "analyzing_range_alternatives": {\
                    "range_scan_alternatives": [\
                      {\
                        "index": "idx_1",\
                        "ranges": [\
                          "1 <= TagType <= 1 AND 采光充足 <= TagName <= 采光充足"\
                        ],\
                        "index_dives_for_eq_ranges": true,\
                        "rowid_ordered": false,\
                        "using_mrr": false,\
                        "index_only": false,\
                        "rows": 2924,\
                        "cost": 3509.8,\
                        "chosen": true\
                      },\
                      {\
                        "index": "idx_lc",\
                        "ranges": [\
                          "1 <= TagType <= 1 AND 采光充足 <= TagName <= 采光充足"\
                        ],\
                        "index_dives_for_eq_ranges": true,\
                        "rowid_ordered": true,\
                        "using_mrr": false,\
                        "index_only": false,\
                        "rows": 2924,\
                        "cost": 3509.8,\
                        "chosen": false,\
                        "cause": "cost"\
                      },\
                      {\
                        "index": "idx_lc_2",\
                        "ranges": [\
                          "采光充足 <= TagName <= 采光充足 AND 1 <= TagType <= 1"\
                        ],\
                        "index_dives_for_eq_ranges": true,\
                        "rowid_ordered": true,\
                        "using_mrr": false,\
                        "index_only": false,\
                        "rows": 2924,\
                        "cost": 3509.8,\
                        "chosen": false,\
                        "cause": "cost"\
                      }\
                    ],\
                    "analyzing_roworder_intersect": {\
                      "intersecting_indices": [\
                        {\
                          "index": "idx_lc",\
                          "index_scan_cost": 266.73,\
                          "cumulated_index_scan_cost": 266.73,\
                          "disk_sweep_cost": 2771.5,\
                          "cumulated_total_cost": 3038.2,\
                          "usable": true,\
                          "matching_rows_now": 2924,\
                          "isect_covering_with_this_index": false,\
                          "chosen": true\
                        },\
                        {\
                          "index": "idx_lc_2",\
                          "cumulated_total_cost": 3038.2,\
                          "usable": false,\
                          "cause": "does_not_reduce_cost_of_intersect"\
                        }\
                      ],\
                      "clustered_pk": {\
                        "clustered_pk_added_to_intersect": false,\
                        "cause": "no_clustered_pk_index"\
                      },\
                      "chosen": false,\
                      "cause": "too_few_indexes_to_merge"\
                    }\
                  },\
                  "chosen_range_access_summary": {\
                    "range_access_plan": {\
                      "type": "range_scan",\
                      "index": "idx_1",\
                      "rows": 2924,\
                      "ranges": [\
                        "1 <= TagType <= 1 AND 采光充足 <= TagName <= 采光充足"\
                      ]\
                    },\
                    "rows_for_plan": 2924,\
                    "cost_for_plan": 3509.8,\
                    "chosen": true\
                  }\
                }\
              }\
            ]\
          },\
          {\
            "considered_execution_plans": [\
              {\
                "plan_prefix": [\
                ],\
                "table": "`test_table`",\
                "best_access_path": {\
                  "considered_access_paths": [\
                    {\
                      "access_type": "ref",\
                      "index": "idx_1",\
                      "rows": 2924,\
                      "cost": 3508.8,\
                      "chosen": true\
                    },\
                    {\
                      "access_type": "ref",\
                      "index": "idx_lc",\
                      "rows": 2924,\
                      "cost": 3508.8,\
                      "chosen": false\
                    },\
                    {\
                      "access_type": "ref",\
                      "index": "idx_lc_2",\
                      "rows": 2924,\
                      "cost": 3508.8,\
                      "chosen": false\
                    },\
                    {\
                      "access_type": "range",\
                      "cause": "heuristic_index_cheaper",\
                      "chosen": false\
                    }\
                  ]\
                },\
                "cost_for_plan": 3508.8,\
                "rows_for_plan": 2924,\
                "chosen": true\
              }\
            ]\
          },\
          {\
            "attaching_conditions_to_tables": {\
              "original_condition": "((`test_table`.`TagType` = 1) and (`test_table`.`TagName` = '采光充足'))",\
              "attached_conditions_computation": [\
              ],\
              "attached_conditions_summary": [\
                {\
                  "table": "`test_table`",\
                  "attached": "(`test_table`.`TagName` = '采光充足')"\
                }\
              ]\
            }\
          },\
          {\
            "clause_processing": {\
              "clause": "ORDER BY",\
              "original_clause": "`test_table`.`TagId`",\
              "items": [\
                {\
                  "item": "`test_table`.`TagId`"\
                }\
              ],\
              "resulting_clause_is_simple": true,\
              "resulting_clause": "`test_table`.`TagId`"\
            }\
          },\
          {\
            "refine_plan": [\
              {\
                "table": "`test_table`",\
                "pushed_index_condition": "(`test_table`.`TagName` = '采光充足')",\
                "table_condition_attached": null\
              }\
            ]\
          },\
          {\
            "added_back_ref_condition": "((`test_table`.`TagType` <=> 1) and (`test_table`.`TagName` <=> '采光充足'))"\
          },\
          {\
            "reconsidering_access_paths_for_index_ordering": {\
              "clause": "ORDER BY",\
              "index_order_summary": {\
                "table": "`test_table`",\
                "index_provides_order": true,\
                "order_direction": "asc",\
                "disabled_pushed_condition_on_old_index": true,\
                "index": "PRIMARY",\
                "plan_changed": true,\
                "access_type": "index_scan"\
              }\
            }\
          }\
        ]\
      }\
    },\
    {\
      "join_execution": {\
        "select#": 1,\
        "steps": [\
        ]\
      }\
    }\
  ]\
}

正确的选择

{\
  "steps": [\
    {\
      "join_preparation": {\
        "select#": 1,\
        "steps": [\
          {\
            "expanded_query": "/* select#1 */ select `test_table`.`TagId` AS `TagId`,`test_table`.`TagType` AS `TagType`,`test_table`.`SubType` AS `SubType`,`test_table`.`CommId` AS `CommId`,`test_table`.`TagFlag` AS `TagFlag`,`test_table`.`TagName` AS `TagName`,`test_table`.`OrderId` AS `OrderId`,`test_table`.`Unum` AS `Unum`,`test_table`.`IsBest` AS `IsBest`,`test_table`.`BrokerId` AS `BrokerId`,`test_table`.`AddDate` AS `AddDate`,`test_table`.`UpdateDate` AS `UpdateDate`,`test_table`.`updatetime` AS `updatetime`,`test_table`.`tmpnum` AS `tmpnum`,`test_table`.`cityid` AS `cityid` from `test_table` FORCE INDEX (`idx_lc`) where ((`test_table`.`TagType` = 1) and (`test_table`.`TagName` = '采光充足')) order by `test_table`.`TagId` limit 1"\
          }\
        ]\
      }\
    },\
    {\
      "join_optimization": {\
        "select#": 1,\
        "steps": [\
          {\
            "condition_processing": {\
              "condition": "WHERE",\
              "original_condition": "((`test_table`.`TagType` = 1) and (`test_table`.`TagName` = '采光充足'))",\
              "steps": [\
                {\
                  "transformation": "equality_propagation",\
                  "resulting_condition": "((`test_table`.`TagName` = '采光充足') and multiple equal(1, `test_table`.`TagType`))"\
                },\
                {\
                  "transformation": "constant_propagation",\
                  "resulting_condition": "((`test_table`.`TagName` = '采光充足') and multiple equal(1, `test_table`.`TagType`))"\
                },\
                {\
                  "transformation": "trivial_condition_removal",\
                  "resulting_condition": "((`test_table`.`TagName` = '采光充足') and multiple equal(1, `test_table`.`TagType`))"\
                }\
              ]\
            }\
          },\
          {\
            "table_dependencies": [\
              {\
                "table": "`test_table` FORCE INDEX (`idx_lc`)",\
                "row_may_be_null": false,\
                "map_bit": 0,\
                "depends_on_map_bits": [\
                ]\
              }\
            ]\
          },\
          {\
            "ref_optimizer_key_uses": [\
              {\
                "table": "`test_table` FORCE INDEX (`idx_lc`)",\
                "field": "TagType",\
                "equals": "1",\
                "null_rejecting": false\
              },\
              {\
                "table": "`test_table` FORCE INDEX (`idx_lc`)",\
                "field": "TagName",\
                "equals": "'采光充足'",\
                "null_rejecting": false\
              }\
            ]\
          },\
          {\
            "rows_estimation": [\
              {\
                "table": "`test_table` FORCE INDEX (`idx_lc`)",\
                "range_analysis": {\
                  "table_scan": {\
                    "rows": 1131547,\
                    "cost": 2e308\
                  },\
                  "potential_range_indices": [\
                    {\
                      "index": "PRIMARY",\
                      "usable": false,\
                      "cause": "not_applicable"\
                    },\
                    {\
                      "index": "idx_4",\
                      "usable": false,\
                      "cause": "not_applicable"\
                    },\
                    {\
                      "index": "idxnew",\
                      "usable": false,\
                      "cause": "not_applicable"\
                    },\
                    {\
                      "index": "idx_lc",\
                      "usable": true,\
                      "key_parts": [\
                        "TagType",\
                        "TagName",\
                        "TagId"\
                      ]\
                    }\
                  ],\
                  "setup_range_conditions": [\
                  ],\
                  "group_index_range": {\
                    "chosen": false,\
                    "cause": "not_group_by_or_distinct"\
                  },\
                  "analyzing_range_alternatives": {\
                    "range_scan_alternatives": [\
                      {\
                        "index": "idx_lc",\
                        "ranges": [\
                          "1 <= TagType <= 1 AND 采光充足 <= TagName <= 采光充足"\
                        ],\
                        "index_dives_for_eq_ranges": true,\
                        "rowid_ordered": true,\
                        "using_mrr": false,\
                        "index_only": false,\
                        "rows": 2931,\
                        "cost": 3518.2,\
                        "chosen": true\
                      }\
                    ],\
                    "analyzing_roworder_intersect": {\
                      "usable": false,\
                      "cause": "too_few_roworder_scans"\
                    }\
                  },\
                  "chosen_range_access_summary": {\
                    "range_access_plan": {\
                      "type": "range_scan",\
                      "index": "idx_lc",\
                      "rows": 2931,\
                      "ranges": [\
                        "1 <= TagType <= 1 AND 采光充足 <= TagName <= 采光充足"\
                      ]\
                    },\
                    "rows_for_plan": 2931,\
                    "cost_for_plan": 3518.2,\
                    "chosen": true\
                  }\
                }\
              }\
            ]\
          },\
          {\
            "considered_execution_plans": [\
              {\
                "plan_prefix": [\
                ],\
                "table": "`test_table` FORCE INDEX (`idx_lc`)",\
                "best_access_path": {\
                  "considered_access_paths": [\
                    {\
                      "access_type": "ref",\
                      "index": "idx_lc",\
                      "rows": 2931,\
                      "cost": 3517.2,\
                      "chosen": true\
                    },\
                    {\
                      "access_type": "range",\
                      "cause": "heuristic_index_cheaper",\
                      "chosen": false\
                    }\
                  ]\
                },\
                "cost_for_plan": 3517.2,\
                "rows_for_plan": 2931,\
                "chosen": true\
              }\
            ]\
          },\
          {\
            "attaching_conditions_to_tables": {\
              "original_condition": "((`test_table`.`TagType` = 1) and (`test_table`.`TagName` = '采光充足'))",\
              "attached_conditions_computation": [\
              ],\
              "attached_conditions_summary": [\
                {\
                  "table": "`test_table` FORCE INDEX (`idx_lc`)",\
                  "attached": "(`test_table`.`TagName` = '采光充足')"\
                }\
              ]\
            }\
          },\
          {\
            "clause_processing": {\
              "clause": "ORDER BY",\
              "original_clause": "`test_table`.`TagId`",\
              "items": [\
                {\
                  "item": "`test_table`.`TagId`"\
                }\
              ],\
              "resulting_clause_is_simple": true,\
              "resulting_clause": "`test_table`.`TagId`"\
            }\
          },\
          {\
            "refine_plan": [\
              {\
                "table": "`test_table` FORCE INDEX (`idx_lc`)",\
                "pushed_index_condition": "(`test_table`.`TagName` = '采光充足')",\
                "table_condition_attached": null\
              }\
            ]\
          },\
          {\
            "added_back_ref_condition": "((`test_table`.`TagType` <=> 1) and (`test_table`.`TagName` <=> '采光充足'))"\
          },\
          {\
            "reconsidering_access_paths_for_index_ordering": {\
              "clause": "ORDER BY",\
              "index_order_summary": {\
                "table": "`test_table` FORCE INDEX (`idx_lc`)",\
                "index_provides_order": true,\
                "order_direction": "asc",\
                "index": "idx_lc",\
                "plan_changed": false\
              }\
            }\
          }\
        ]\
      }\
    },\
    {\
      "join_execution": {\
        "select#": 1,\
        "steps": [\
        ]\
      }\
    }\
  ]\
}