mysql中groupby会用到索引吗_Mysql中索引的使用问题，尤其是排序中索引使用的规则是什么？...

谢谢 @胡说八道 的邀请

看到这个问题的第一感觉像是order by limit的bug

我也自己试了下，mysql5.7.17 过程如下：

create table myuser (tid int primary key auto_increment, id varchar(80) unique key , first_name varchar(10) ,last_name varchar(10), age int );

mysql> insert into myuser (id) (select concat(TABLE_SCHEMA,TABLE_NAME) a from information_schema.tables);

Query OK, 310 rows affected (0.07 sec)

Records: 310 Duplicates: 0 Warnings: 0

mysql> insert into myuser (id) (select concat(TABLE_NAME,TABLE_SCHEMA,'100') a from information_schema.tables);

Query OK, 310 rows affected (0.06 sec)

Records: 310 Duplicates: 0 Warnings: 0

mysql> explain select * from myuser where id like 't%' order by tid limit 5;

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

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

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

| 1 | SIMPLE | myuser | NULL | index | id | PRIMARY | 4 | NULL | 55 | 9.03 | Using where |

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

1 row in set, 1 warning (0.00 sec)

mysql> explain select * from myuser where id like 'te%' order by tid limit 5;

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

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

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

| 1 | SIMPLE | myuser | NULL | range | id | id | 83 | NULL | 20 | 100.00 | Using index condition; Using filesort |

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

1 row in set, 1 warning (0.00 sec)

这看起来和楼主的情况是一样的。

然后我对比两种情况下的optimizer_trace,

mysql> select * from information_schema.optimizer_trace\Gmysql> select * from information_schema.optimizer_trace\G

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

QUERY: select * from myuser where | QUERY: select * from myuser where

TRACE: { TRACE: {

"steps": [ "steps": [

{ {

"join_preparation": { "join_preparation": {

"select#": 1, "select#": 1,

"steps": [ "steps": [

{ {

"expanded_query": "/* select#1 */ select `myuser` | "expanded_query": "/* select#1 */ select `myuser`

} }

] ]

} }

}, },

{ {

"join_optimization": { "join_optimization": {

"select#": 1, "select#": 1,

"steps": [ "steps": [

{ {

"condition_processing": { "condition_processing": {

"condition": "WHERE", "condition": "WHERE",

"original_condition": "(`myuser`.`id` like 't%' | "original_condition": "(`myuser`.`id` like 'te%

"steps": [ "steps": [

{ {

"transformation": "equality_propagation", "transformation": "equality_propagation",

"resulting_condition": "(`myuser`.`id` like | "resulting_condition": "(`myuser`.`id` like

}, },

{ {

"transformation": "constant_propagation", "transformation": "constant_propagation",

"resulting_condition": "(`myuser`.`id` like | "resulting_condition": "(`myuser`.`id` like

}, },

{ {

"transformation": "trivial_condition_remova "transformation": "trivial_condition_remova

"resulting_condition": "(`myuser`.`id` like | "resulting_condition": "(`myuser`.`id` like

} }

] ]

} }

}, },

{ {

"substitute_generated_columns": { "substitute_generated_columns": {

} }

}, },

{ {

"table_dependencies": [ "table_dependencies": [

{ {

"table": "`myuser`", "table": "`myuser`",

"row_may_be_null": false, "row_may_be_null": false,

"map_bit": 0, "map_bit": 0,

"depends_on_map_bits": [ "depends_on_map_bits": [

] ]

} }

] ]

}, },

{ {

"ref_optimizer_key_uses": [ "ref_optimizer_key_uses": [

] ]

}, },

{ {

"rows_estimation": [ "rows_estimation": [

{ {

"table": "`myuser`", "table": "`myuser`",

"range_analysis": { "range_analysis": {

"table_scan": { "table_scan": {

"rows": 620, "rows": 620,

"cost": 130.1 "cost": 130.1

}, },

"potential_range_indexes": [ "potential_range_indexes": [

{ {

"index": "PRIMARY", "index": "PRIMARY",

"usable": false, "usable": false,

"cause": "not_applicable" "cause": "not_applicable"

}, },

{ {

"index": "id", "index": "id",

"usable": true, "usable": true,

"key_parts": [ "key_parts": [

"id" "id"

] ]

} }

], ],

"setup_range_conditions": [ "setup_range_conditions": [

], ],

"group_index_range": { "group_index_range": {

"chosen": false, "chosen": false,

"cause": "not_group_by_or_distinct" "cause": "not_group_by_or_distinct"

}, },

"analyzing_range_alternatives": { "analyzing_range_alternatives": {

"range_scan_alternatives": [ "range_scan_alternatives": [

{ {

"index": "id", "index": "id",

"ranges": [ "ranges": [

"t\u0000\u0000\u0000\u0000\u0000\u0 | "te\u0000\u0000\u0000\u0000\u0000\u

], ],

"index_dives_for_eq_ranges": true, "index_dives_for_eq_ranges": true,

"rowid_ordered": false, "rowid_ordered": false,

"using_mrr": false, "using_mrr": false,

"index_only": false, "index_only": false,

"rows": 56, | "rows": 20,

"cost": 68.21, | "cost": 25.01,

"chosen": true "chosen": true

} }

], ],

"analyzing_roworder_intersect": { "analyzing_roworder_intersect": {

"usable": false, "usable": false,

"cause": "too_few_roworder_scans" "cause": "too_few_roworder_scans"

} }

}, },

"chosen_range_access_summary": { "chosen_range_access_summary": {

"range_access_plan": { "range_access_plan": {

"type": "range_scan", "type": "range_scan",

"index": "id", "index": "id",

"rows": 56, | "rows": 20,

"ranges": [ "ranges": [

"t\u0000\u0000\u0000\u0000\u0000\u000 | "te\u0000\u0000\u0000\u0000\u0000\u00

] ]

}, },

"rows_for_plan": 56, | "rows_for_plan": 20,

"cost_for_plan": 68.21, | "cost_for_plan": 25.01,

"chosen": true "chosen": true

} }

} }

} }

] ]

}, },

{ {

"considered_execution_plans": [ "considered_execution_plans": [

{ {

"plan_prefix": [ "plan_prefix": [

], ],

"table": "`myuser`", "table": "`myuser`",

"best_access_path": { "best_access_path": {

"considered_access_paths": [ "considered_access_paths": [

{ {

"rows_to_scan": 56, | "rows_to_scan": 20,

"access_type": "range", "access_type": "range",

"range_details": { "range_details": {

"used_index": "id" "used_index": "id"

}, },

"resulting_rows": 56, | "resulting_rows": 20,

"cost": 79.41, | "cost": 29.01,

"chosen": true "chosen": true

} }

] ]

}, },

"condition_filtering_pct": 100, "condition_filtering_pct": 100,

"rows_for_plan": 56, | "rows_for_plan": 20,

"cost_for_plan": 79.41, | "cost_for_plan": 29.01,

"chosen": true "chosen": true

} }

] ]

}, },

{ {

"attaching_conditions_to_tables": { "attaching_conditions_to_tables": {

"original_condition": "(`myuser`.`id` like 't%' | "original_condition": "(`myuser`.`id` like 'te%

"attached_conditions_computation": [ "attached_conditions_computation": [

{ {

"table": "`myuser`", "table": "`myuser`",

"rechecking_index_usage": { "rechecking_index_usage": {

"recheck_reason": "low_limit", "recheck_reason": "low_limit",

"limit": 5, "limit": 5,

"row_estimate": 56 | "row_estimate": 20

} }

} }

], ],

"attached_conditions_summary": [ "attached_conditions_summary": [

{ {

"table": "`myuser`", "table": "`myuser`",

"attached": "(`myuser`.`id` like 't%')" | "attached": "(`myuser`.`id` like 'te%')"

} }

] ]

} }

}, },

{ {

"clause_processing": { "clause_processing": {

"clause": "ORDER BY", "clause": "ORDER BY",

"original_clause": "`myuser`.`tid`", "original_clause": "`myuser`.`tid`",

"items": [ "items": [

{ {

"item": "`myuser`.`tid`" "item": "`myuser`.`tid`"

} }

], ],

"resulting_clause_is_simple": true, "resulting_clause_is_simple": true,

"resulting_clause": "`myuser`.`tid`" "resulting_clause": "`myuser`.`tid`"

} }

}, },

{ {

"reconsidering_access_paths_for_index_ordering": "reconsidering_access_paths_for_index_ordering":

"clause": "ORDER BY", "clause": "ORDER BY",

"index_order_summary": { "index_order_summary": {

"table": "`myuser`", "table": "`myuser`",

"index_provides_order": true, | "index_provides_order": false,

"order_direction": "asc", | "order_direction": "undefined",

"index": "PRIMARY", | "index": "id",

"plan_changed": true, | "plan_changed": false

"access_type": "index" <

} }

} }

}, },

{ {

"refine_plan": [ "refine_plan": [

{ {

"table": "`myuser`" | "table": "`myuser`",

> "pushed_index_condition": "(`myuser`.`id` lik

> "table_condition_attached": null

} }

] ]

} }

▽

}

] ]

} }

}, },

{ {

"join_execution": { "join_execution": {

"select#": 1, "select#": 1,

"steps": [ "steps": [

> {

> "filesort_information": [

> {

> "direction": "asc",

> "table": "`myuser`",

> "field": "tid"

> }

> ],

> "filesort_priority_queue_optimization": {

> "limit": 5,

> "rows_estimate": 3276,

> "row_size": 116,

> "memory_available": 262144,

> "chosen": true

> },

> "filesort_execution": [

> ],

> "filesort_summary": {

> "rows": 6,

> "examined_rows": 20,

> "number_of_tmp_files": 0,

> "sort_buffer_size": 744,

> "sort_mode": ""

> }

> }

] ]

} }

} }

] ]

}}

MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0

INSUFFICIENT_PRIVILEGES: 0 INSUFFICIENT_PRIVILEGES: 0

1 row in set (0.00 sec)1 row in set (0.00 sec)

<

mysql> <

左边是走primary的，右是是走id索引的。

在considered_execution_plans时看起来一些都是正常的，都是要走id索引的，

到了reconsidering_access_paths_for_index_ordering时 左边选择了primary索引。 右边还是走了id索引。

试了另一种情况

mysql> explain select * from myuser where id like 't%' order by tid limit 10;

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

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

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

| 1 | SIMPLE | myuser | NULL | range | id | id | 83 | NULL | 56 | 100.00 | Using index condition; Using filesort |

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

1 row in set, 1 warning (0.00 sec)

这个是上面对比部分走primary的条件一样，不同的是把limit 5 变成了limit 10，变了之后走了id的索引，对比optimizer_trace 也是reconsidering_access_paths_for_index_ordering时变的不同(结果就不再列出来了)。

搞到这里，我也是一脸黑线，这没能解释明白出现这个情况的原因。

这个问题要想解释的明白，还需要再花些精力去debug各种情况，然后对比他们的异同。 等我debug。

QQ 273002188 欢迎一起学习

QQ 群 236941212

oracle,mysql,PG 相互交流