优化器放弃索引mysql_mysql优化器有没有可能选择一个低效索引，给数据库带来性能问题？...

mysql> show variables like '%trace%';

+------------------------------+----------------------------------------------------------------------------+

| Variable_name | Value |

+------------------------------+----------------------------------------------------------------------------+

| optimizer_trace | enabled=off,one_line=off |

| optimizer_trace_features | greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on |

| optimizer_trace_limit | 1 |

| optimizer_trace_max_mem_size | 16384 |

| optimizer_trace_offset | -1 |

+------------------------------+----------------------------------------------------------------------------+

5 rows in set (0.00 sec)

mysql> set optimizer_trace = "enabled=on"; --打开跟踪， 只能跟踪自己session执行的，不能跟踪别人的

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test.ta where id=1;

+------+

| id |

+------+

| 1 |

+------+

1 row in set (0.00 sec)

mysql> select * from information_schema.optimizer_trace\G

*************************** 1. row ***************************

QUERY: select * from test.ta where id=1

TRACE: {

"steps": [

{

"join_preparation": {

"select#": 1,

"steps": [

{

"expanded_query": "/* select#1 */ select `test`.`ta`.`id` AS `id` from `test`.`ta` where (`test`.`ta`.`id` = 1)"

}

]

}

},

{

"join_optimization": {

"select#": 1,

"steps": [

{

"condition_processing": {

"condition": "WHERE",

"original_condition": "(`test`.`ta`.`id` = 1)",

"steps": [

{

"transformation": "equality_propagation", --转换

"resulting_condition": "multiple equal(1, `test`.`ta`.`id`)"

},

{

"transformation": "constant_propagation",

"resulting_condition": "multiple equal(1, `test`.`ta`.`id`)"

},

{

"transformation": "trivial_condition_removal",

"resulting_condition": "multiple equal(1, `test`.`ta`.`id`)"

}

]

}

},

{

"table_dependencies": [

{

"table": "`test`.`ta`",

"row_may_be_null": false,

"map_bit": 0,

"depends_on_map_bits": [

]

}

]

},

{

"ref_optimizer_key_uses": [

{

"table": "`test`.`ta`",

"field": "id",

"equals": "1",

"null_rejecting": false

}

]

},

{

"rows_estimation": [

{

"table": "`test`.`ta`",

"range_analysis": {

"table_scan": {

"rows": 9,

"cost": 4.9 --表扫描的cost

},

"potential_range_indices": [

{

"index": "idx_ta",

"usable": true,

"key_parts": [

"id"

]

}

],

"best_covering_index_scan": {

"index": "idx_ta",

"cost": 2.8107,

"chosen": true

},

"setup_range_conditions": [

],

"group_index_range": {

"chosen": false,

"cause": "not_group_by_or_distinct"

},

"analyzing_range_alternatives": {

"range_scan_alternatives": [

{

"index": "idx_ta",

"ranges": [

"1 <= id <= 1"

],

"index_dives_for_eq_ranges": true,

"rowid_ordered": true,

"using_mrr": false,

"index_only": true,

"rows": 1,

"cost": 2.21,

"chosen": true

}

],

"analyzing_roworder_intersect": {

"usable": false,

"cause": "too_few_roworder_scans"

}

},

"chosen_range_access_summary": {

"range_access_plan": {

"type": "range_scan",

"index": "idx_ta",

"rows": 1,

"ranges": [

"1 <= id <= 1"

]

},

"rows_for_plan": 1,

"cost_for_plan": 2.21,

"chosen": true

}

}

}

]

},

{

"considered_execution_plans": [

{

"plan_prefix": [

],

"table": "`test`.`ta`",

"best_access_path": {

"considered_access_paths": [

{

"access_type": "ref",

"index": "idx_ta",

"rows": 1,

"cost": 1.2,

"chosen": true --使用了这个执行计划

},

{

"access_type": "range",

"cause": "heuristic_index_cheaper",

"chosen": false

}

]

},

"cost_for_plan": 1.2,

"rows_for_plan": 1,

"chosen": true

}

]

},

{

"attaching_conditions_to_tables": {

"original_condition": "(`test`.`ta`.`id` = 1)",

"attached_conditions_computation": [

],

"attached_conditions_summary": [

{

"table": "`test`.`ta`",

"attached": null

}

]

}

},

{

"refine_plan": [

{

"table": "`test`.`ta`"

}

]

}

]

}

},

{

"join_execution": {

"select#": 1,

"steps": [

]

}

}

]

}

MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0

INSUFFICIENT_PRIVILEGES: 0

1 row in set (0.00 sec)

-- 里面包含条件的去除，查询的转换，选择最好的执行计划。

mysql> set optimizer_trace = "enabled=off"; --关闭跟踪

Query OK, 0 rows affected (0.00 sec)

mysql> show create table information_schema.optimizer_trace;

+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| OPTIMIZER_TRACE | CREATE TEMPORARY TABLE `OPTIMIZER_TRACE` ( --这是一个临时表，别的连接看不到本连接的内容，

`QUERY` longtext NOT NULL,

`TRACE` longtext NOT NULL,

`MISSING_BYTES_BEYOND_MAX_MEM_SIZE` int(20) NOT NULL DEFAULT '0',

`INSUFFICIENT_PRIVILEGES` tinyint(1) NOT NULL DEFAULT '0'

) ENGINE=MyISAM DEFAULT CHARSET=utf8 |

+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)