mysql-如何选择索引

我们都知道mysql在执行SQL时会自动使用index，但是有时会奇怪，他就是不使用index，比如如下面的例子

1.table结构

CREATE TABLE `user` (

`id` bigint(20) NOT NULL COMMENT '主键ID',

`name` varchar(30) DEFAULT NULL COMMENT '姓名',

`age` int(11) DEFAULT NULL COMMENT '年龄',

`email` varchar(50) DEFAULT NULL COMMENT '邮箱',

`deleted` int(11) DEFAULT '0',

`createtime` datetime DEFAULT NULL,

`version` bigint(20) DEFAULT '0',

`FAB` varchar(20) DEFAULT NULL,

`specid` varchar(100) DEFAULT NULL,

`work_info` varchar(100) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `name_idx` (`name`,`age`)

) ENGINE=InnoDB

2.从上面可以看到我们name、age的联合索引，但是我们执行以下两个不同的sql会发现他们执行计划不太一样

 

 

从上面可以看出一个用了全表扫描，一个用了index，为什么会这样呢？

以mysql中有trace工具可以分析，使用方法需执行如下sql：

set session optimizer_trace="enabled=on",end_markers_in_json=on

explain select * from user where name like 'Billie'

select * FROM information_schema.OPTIMIZER_TRACE

执行最后一sql语句可以得到trace结果，从以下结果来看mysql会从几个方案中进行选择cost最低的方案进查找数据

{

"steps": [

{

"join_preparation": {

"select#": 1,

"steps": [

{

"expanded_query": "/* select#1 */ select `user`.`id` AS `id`,`user`.`name` AS `name`,`user`.`age` AS `age`,`user`.`email` AS `email`,`user`.`deleted` AS `deleted`,`user`.`createtime` AS `createtime`,`user`.`version` AS `version`,`user`.`FAB` AS `FAB`,`user`.`specid` AS `specid`,`user`.`work_info` AS `work_info` from `user` where (`user`.`name` like 'Billie')"

}

] /* steps */

} /* join_preparation */

},

{

"join_optimization": {

"select#": 1, 解析sql

"steps": [

{

"condition_processing": {

"condition": "WHERE",

"original_condition": "(`user`.`name` like 'Billie')",

"steps": [

{

"transformation": "equality_propagation",

"resulting_condition": "(`user`.`name` like 'Billie')"

},

{

"transformation": "constant_propagation",

"resulting_condition": "(`user`.`name` like 'Billie')"

},

{

"transformation": "trivial_condition_removal",

"resulting_condition": "(`user`.`name` like 'Billie')"

}

] /* steps */

} /* condition_processing */

},

{

"substitute_generated_columns": {

} /* substitute_generated_columns */

},

{

"table_dependencies": [

{

"table": "`user`",

"row_may_be_null": false,

"map_bit": 0,

"depends_on_map_bits": [

] /* depends_on_map_bits */

}

] /* table_dependencies */

},

{

"ref_optimizer_key_uses": [

] /* ref_optimizer_key_uses */

},

{

"rows_estimation": [

{

"table": "`user`",

"range_analysis": {

"table_scan": { //全表扫描的评分

"rows": 218,

"cost": 24.65

} /* table_scan */,

"potential_range_indexes": [ //潜在可用index

{

"index": "PRIMARY",

"usable": false,

"cause": "not_applicable"

},

{

"index": "name_idx",

"usable": true,

"key_parts": [

"name",

"age",

"id"

] /* key_parts */

}

] /* potential_range_indexes */,

"setup_range_conditions": [

] /* setup_range_conditions */,

"group_index_range": {

"chosen": false,

"cause": "not_group_by_or_distinct"

} /* group_index_range */,

"skip_scan_range": {

"potential_skip_scan_indexes": [

{

"index": "name_idx",

"usable": false,

"cause": "query_references_nonkey_column"

}

] /* potential_skip_scan_indexes */

} /* skip_scan_range */,

"analyzing_range_alternatives": {

"range_scan_alternatives": [ 可选择index查找数据方案

{

"index": "name_idx",

"ranges": [

"Billie <= name <= Billie"

] /* ranges */,

"index_dives_for_eq_ranges": true,

"rowid_ordered": false,

"using_mrr": false,

"index_only": false,

"rows": 1,

"cost": 0.61,

"chosen": true

}

] /* range_scan_alternatives */,

"analyzing_roworder_intersect": {

"usable": false,

"cause": "too_few_roworder_scans"

} /* analyzing_roworder_intersect */

} /* analyzing_range_alternatives */,

"chosen_range_access_summary": {

"range_access_plan": {

"type": "range_scan",

"index": "name_idx",

"rows": 1,

"ranges": [

"Billie <= name <= Billie"

] /* ranges */

} /* range_access_plan */,

"rows_for_plan": 1,

"cost_for_plan": 0.61,

"chosen": true

} /* chosen_range_access_summary */

} /* range_analysis */

}

] /* rows_estimation */

},

{

"considered_execution_plans": [ 最终考虑的方案

{

"plan_prefix": [

] /* plan_prefix */,

"table": "`user`",

"best_access_path": {

"considered_access_paths": [

{

"rows_to_scan": 1,

"filtering_effect": [

] /* filtering_effect */,

"final_filtering_effect": 1,

"access_type": "range",

"range_details": {

"used_index": "name_idx"

} /* range_details */,

"resulting_rows": 1,

"cost": 0.71,

"chosen": true

}

] /* considered_access_paths */

} /* best_access_path */,

"condition_filtering_pct": 100,

"rows_for_plan": 1,

"cost_for_plan": 0.71,

"chosen": true

}

] /* considered_execution_plans */

},

{

"attaching_conditions_to_tables": {

"original_condition": "(`user`.`name` like 'Billie')",

"attached_conditions_computation": [

] /* attached_conditions_computation */,

"attached_conditions_summary": [

{

"table": "`user`",

"attached": "(`user`.`name` like 'Billie')"

}

] /* attached_conditions_summary */

} /* attaching_conditions_to_tables */

},

{

"finalizing_table_conditions": [

{

"table": "`user`",

"original_table_condition": "(`user`.`name` like 'Billie')",

"final_table_condition ": "(`user`.`name` like 'Billie')"

}

] /* finalizing_table_conditions */

},

{

"refine_plan": [

{

"table": "`user`",

"pushed_index_condition": "(`user`.`name` like 'Billie')",

"table_condition_attached": null

}

] /* refine_plan */

}

] /* steps */

} /* join_optimization */

},

{

"join_explain": {

"select#": 1,

"steps": [

] /* steps */

} /* join_explain */

}

] /* steps */

}