users表mysql> desc users;
+----------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(45) | NO | | | |
| email | varchar(64) | NO | | | |
| password | varchar(64) | NO | | | |
| remember_token | varchar(64) | NO | | | |
| created_at | int(10) unsigned | NO | | 0 | |
| updated_at | int(10) unsigned | NO | | 0 | |
| type | tinyint(3) unsigned | NO | | 0 | |
| is_boss | tinyint(3) unsigned | NO | | 0 | |
+----------------+---------------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)income表mysql> desc income;
+------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| user_id | int(10) unsigned | NO | | 0 | |
| type | tinyint(3) unsigned | NO | | 0 | |
| status | tinyint(3) unsigned | NO | | 0 | |
| amount | int(10) unsigned | NO | | 0 | |
| created_at | int(10) unsigned | NO | | 0 | |
| done_at | int(10) unsigned | NO | MUL | 0 | |
| updated_at | int(10) unsigned | NO | | 0 | |
+------------+---------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)取出某个时间段,收入前20的用户的
select `users`.`id`, `users`.`name`, `income`.`user_id`, SUM(amount) AS amount from `users`
inner join `income` on `income`.`user_id` = `users`.`id`
where `income`.`done_at` >= 1490544000 and `income`.`done_at` <= 1490716800
group by `income`.`user_id`
order by `amount`
desc limit 20;users索引情况mysql> show index from users;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users | 0 | PRIMARY | 1 | id | A | 961527 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)income索引情况mysql> show index from income;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| income | 0 | PRIMARY | 1 | id | A | 1486419 | NULL | NULL | | BTREE | | |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)mysql> select sql_no_cache `users`.`id`, `users`.`name`, `income`.`user_id`, SUM(amount) AS amount from `users` inner join `income` on `income`.`user_id` = `users`.`id` where `income`.`done_at` >= 1490544000 and `income`.`done_at` <= 1490716800 group by `income`.`user_id` order by `amount` desc limit 20;
+--------+-----------------------------+---------+--------+
| id | name | user_id | amount |
+--------+-----------------------------+---------+--------+
| 151482 | Dr. Nadia Ritchie | 151482 | 316561 |
| 212461 | Leonie Erdman | 212461 | 281850 |
| 222560 | Dr. Karli Crooks MD | 222560 | 281483 |
| 99560 | Mr. Izaiah Jaskolski | 99560 | 277674 |
| 231545 | Prof. Lottie Quigley | 231545 | 273545 |
| 520949 | Devyn Hettinger | 520949 | 265047 |
| 26074 | Sibyl Beier | 26074 | 263244 |
| 13709 | Koby Hermann | 13709 | 257014 |
| 11036 | Mr. Camryn Hilpert I | 11036 | 255400 |
| 143098 | Miss Leatha Witting | 143098 | 251395 |
| 174490 | Estelle VonRueden | 174490 | 250005 |
| 186245 | Devante Hoppe | 186245 | 249586 |
| 75809 | Prof. Eudora Wehner | 75809 | 245268 |
| 18766 | Hailey Corwin | 18766 | 241733 |
| 158053 | Elmo Dare Jr. | 158053 | 239168 |
| 130210 | Lilian Casper | 130210 | 239100 |
| 44063 | Adrain Lockman | 44063 | 238509 |
| 759415 | Prof. Joshuah Ankunding DVM | 759415 | 238478 |
| 216163 | Ruth Reilly | 216163 | 237894 |
| 37976 | Prof. Zane Cassin | 37976 | 237293 |
+--------+-----------------------------+---------+--------+
20 rows in set (2.56 sec)mysql> explain select sql_no_cache `users`.`id`, `users`.`name`, `income`.`user_id`, SUM(amount) AS amount from `users` inner join `income` on `income`.`user_id` = `users`.`id` where `income`.`done_at` >= 1490544000 and `income`.`done_at` <= 1490716800 group by `income`.`user_id` order by `amount` desc limit 20;
+----+-------------+--------+------------+--------+---------------+---------+---------+---------------------------+---------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+--------+---------------+---------+---------+---------------------------+---------+----------+----------------------------------------------+
| 1 | SIMPLE | income | NULL | ALL | NULL | NULL | NULL | NULL | 1584731 | 11.11 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | users | NULL | eq_ref | PRIMARY | PRIMARY | 4 | studymysql.income.user_id | 1 | 100.00 | NULL |
+----+-------------+--------+------------+--------+---------------+---------+---------+---------------------------+---------+----------+----------------------------------------------+
2 rows in set, 1 warning (0.00 sec)income索引ALTER TABLE `studymysql`.`income`
ADD INDEX `idx_done_at` (`done_at` ASC);mysql> show index from income; +--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| income | 0 | PRIMARY | 1 | id | A | 1486419 | NULL | NULL | | BTREE | | |
| income | 1 | idx_done_at | 1 | done_at | A | 1194316 | NULL | NULL | | BTREE | | |
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)mysql> select sql_no_cache `users`.`id`, `users`.`name`, `income`.`user_id`, SUM(amount) AS amount from `users` inner join `income` on `income`.`user_id` = `users`.`id` where `income`.`done_at` >= 1490544000 and `income`.`done_at` <= 1490716800 group by `income`.`user_id` order by `amount` desc limit 20;
+--------+-----------------------------+---------+--------+
| id | name | user_id | amount |
+--------+-----------------------------+---------+--------+
| 151482 | Dr. Nadia Ritchie | 151482 | 316561 |
| 212461 | Leonie Erdman | 212461 | 281850 |
| 222560 | Dr. Karli Crooks MD | 222560 | 281483 |
| 99560 | Mr. Izaiah Jaskolski | 99560 | 277674 |
| 231545 | Prof. Lottie Quigley | 231545 | 273545 |
| 520949 | Devyn Hettinger | 520949 | 265047 |
| 26074 | Sibyl Beier | 26074 | 263244 |
| 13709 | Koby Hermann | 13709 | 257014 |
| 11036 | Mr. Camryn Hilpert I | 11036 | 255400 |
| 143098 | Miss Leatha Witting | 143098 | 251395 |
| 174490 | Estelle VonRueden | 174490 | 250005 |
| 186245 | Devante Hoppe | 186245 | 249586 |
| 75809 | Prof. Eudora Wehner | 75809 | 245268 |
| 18766 | Hailey Corwin | 18766 | 241733 |
| 158053 | Elmo Dare Jr. | 158053 | 239168 |
| 130210 | Lilian Casper | 130210 | 239100 |
| 44063 | Adrain Lockman | 44063 | 238509 |
| 759415 | Prof. Joshuah Ankunding DVM | 759415 | 238478 |
| 216163 | Ruth Reilly | 216163 | 237894 |
| 37976 | Prof. Zane Cassin | 37976 | 237293 |
+--------+-----------------------------+---------+--------+
20 rows in set (3.33 sec)mysql> explain select sql_no_cache `users`.`id`, `users`.`name`, `income`.`user_id`, SUM(amount) AS amount from `users` inner join `income` on `income`.`user_id` = `users`.`id` where `income`.`done_at` >= 1490544000 and `income`.`done_at` <= 1490716800 group by `income`.`user_id` order by `amount` desc limit 20;
+----+-------------+--------+------------+--------+---------------+-------------+---------+---------------------------+--------+----------+--------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+--------+---------------+-------------+---------+---------------------------+--------+----------+--------------------------------------------------------+
| 1 | SIMPLE | income | NULL | range | idx_done_at | idx_done_at | 4 | NULL | 190994 | 100.00 | Using index condition; Using temporary; Using filesort |
| 1 | SIMPLE | users | NULL | eq_ref | PRIMARY | PRIMARY | 4 | studymysql.income.user_id | 1 | 100.00 | NULL |
+----+-------------+--------+------------+--------+---------------+-------------+---------+---------------------------+--------+----------+--------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G
*************************** 1. row ***************************
QUERY: select sql_no_cache `users`.`id`, `users`.`name`, `income`.`user_id`, SUM(amount) AS amount from `users` inner join `income` on `income`.`user_id` = `users`.`id` where `income`.`done_at` >= 1490544000 and `income`.`done_at` <= 1490716800 group by `income`.`user_id` order by `amount` desc limit 20
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select sql_no_cache `users`.`id` AS `id`,`users`.`name` AS `name`,`income`.`user_id` AS `user_id`,sum(`income`.`amount`) AS `amount` from (`users` join `income` on((`income`.`user_id` = `users`.`id`))) where ((`income`.`done_at` >= 1490544000) and (`income`.`done_at` <= 1490716800)) group by `income`.`user_id` order by `amount` desc limit 20"
},
{
"transformations_to_nested_joins": {
"transformations": [
"JOIN_condition_to_WHERE",
"parenthesis_removal"
] /* transformations */,
"expanded_query": "/* select#1 */ select sql_no_cache `users`.`id` AS `id`,`users`.`name` AS `name`,`income`.`user_id` AS `user_id`,sum(`income`.`amount`) AS `amount` from `users` join `income` where ((`income`.`done_at` >= 1490544000) and (`income`.`done_at` <= 1490716800) and (`income`.`user_id` = `users`.`id`)) group by `income`.`user_id` order by `amount` desc limit 20"
} /* transformations_to_nested_joins */
},
{
"functional_dependencies_of_GROUP_columns": {
"all_columns_of_table_map_bits": [
0
] /* all_columns_of_table_map_bits */,
"columns": [
"studymysql.income.user_id",
"studymysql.users.id"
] /* columns */
} /* functional_dependencies_of_GROUP_columns */
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`income`.`done_at` >= 1490544000) and (`income`.`done_at` <= 1490716800) and (`income`.`user_id` = `users`.`id`))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((`income`.`done_at` >= 1490544000) and (`income`.`done_at` <= 1490716800) and multiple equal(`income`.`user_id`, `users`.`id`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "((`income`.`done_at` >= 1490544000) and (`income`.`done_at` <= 1490716800) and multiple equal(`income`.`user_id`, `users`.`id`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((`income`.`done_at` >= 1490544000) and (`income`.`done_at` <= 1490716800) and multiple equal(`income`.`user_id`, `users`.`id`))"
}
] /* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": {
} /* substitute_generated_columns */
},
{
"table_dependencies": [
{
"table": "`users`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
},
{
"table": "`income`",
"row_may_be_null": false,
"map_bit": 1,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
{
"table": "`users`",
"field": "id",
"equals": "`income`.`user_id`",
"null_rejecting": false
}
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [
{
"table": "`users`",
"table_scan": {
"rows": 1023461,
"cost": 7528
} /* table_scan */
},
{
"table": "`income`",
"range_analysis": {
"table_scan": {
"rows": 1584731,
"cost": 321338
} /* table_scan */,
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_done_at",
"usable": true,
"key_parts": [
"done_at",
"id"
] /* key_parts */
}
] /* potential_range_indexes */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_single_table"
} /* group_index_range */,
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx_done_at",
"ranges": [
"1490544000 <= done_at <= 1490716800"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 190994,
"cost": 229194,
"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": "idx_done_at",
"rows": 190994,
"ranges": [
"1490544000 <= done_at <= 1490716800"
] /* ranges */
} /* range_access_plan */,
"rows_for_plan": 190994,
"cost_for_plan": 229194,
"chosen": true
} /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`income`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 190994,
"access_type": "range",
"range_details": {
"used_index": "idx_done_at"
} /* range_details */,
"resulting_rows": 190994,
"cost": 267393,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 190994,
"cost_for_plan": 267393,
"rest_of_plan": [
{
"plan_prefix": [
"`income`"
] /* plan_prefix */,
"table": "`users`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 229193,
"chosen": true,
"cause": "clustered_pk_chosen_by_heuristics"
},
{
"rows_to_scan": 1023461,
"access_type": "scan",
"using_join_cache": true,
"buffers_needed": 9,
"resulting_rows": 1.02e6,
"cost": 3.9e10,
"chosen": false
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 190994,
"cost_for_plan": 496585,
"chosen": true
}
] /* rest_of_plan */
},
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`users`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"usable": false,
"chosen": false
},
{
"rows_to_scan": 1023461,
"access_type": "scan",
"resulting_rows": 1.02e6,
"cost": 212220,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 1.02e6,
"cost_for_plan": 212220,
"rest_of_plan": [
{
"plan_prefix": [
"`users`"
] /* plan_prefix */,
"table": "`income`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 190994,
"access_type": "range",
"range_details": {
"used_index": "idx_done_at"
} /* range_details */,
"resulting_rows": 190994,
"cost": 2.7e11,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 10,
"rows_for_plan": 2e10,
"cost_for_plan": 2.7e11,
"pruned_by_cost": true
}
] /* rest_of_plan */
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`users`.`id` = `income`.`user_id`) and (`income`.`done_at` >= 1490544000) and (`income`.`done_at` <= 1490716800))",
"attached_conditions_computation": [
{
"table": "`income`",
"rechecking_index_usage": {
"recheck_reason": "low_limit",
"limit": 20,
"row_estimate": 190994
} /* rechecking_index_usage */
}
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`income`",
"attached": "((`income`.`done_at` >= 1490544000) and (`income`.`done_at` <= 1490716800))"
},
{
"table": "`users`",
"attached": null
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"clause_processing": {
"clause": "ORDER BY",
"original_clause": "`amount` desc",
"items": [
{
"item": "sum(`income`.`amount`)"
}
] /* items */,
"resulting_clause_is_simple": false,
"resulting_clause": "`amount` desc"
} /* clause_processing */
},
{
"clause_processing": {
"clause": "GROUP BY",
"original_clause": "`income`.`user_id`",
"items": [
{
"item": "`income`.`user_id`"
}
] /* items */,
"resulting_clause_is_simple": true,
"resulting_clause": "`income`.`user_id`"
} /* clause_processing */
},
{
"refine_plan": [
{
"table": "`income`",
"pushed_index_condition": "((`income`.`done_at` >= 1490544000) and (`income`.`done_at` <= 1490716800))",
"table_condition_attached": null
},
{
"table": "`users`"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": {
"select#": 1,
"steps": [
{
"creating_tmp_table": {
"tmp_table_info": {
"table": "intermediate_tmp_table",
"row_length": 160,
"key_length": 4,
"unique_constraint": false,
"location": "memory (heap)",
"row_limit_estimate": 104857
} /* tmp_table_info */
} /* creating_tmp_table */
},
{
"converting_tmp_table_to_ondisk": {
"cause": "memory_table_size_exceeded",
"tmp_table_info": {
"table": "intermediate_tmp_table",
"row_length": 160,
"key_length": 4,
"unique_constraint": false,
"location": "disk (InnoDB)",
"record_format": "packed"
} /* tmp_table_info */
} /* converting_tmp_table_to_ondisk */
},
{
"filesort_information": [
{
"direction": "desc",
"table": "intermediate_tmp_table",
"field": "amount"
}
] /* filesort_information */,
"filesort_priority_queue_optimization": {
"limit": 20,
"rows_estimate": 885,
"row_size": 176,
"memory_available": 262144,
"chosen": true
} /* filesort_priority_queue_optimization */,
"filesort_execution": [
] /* filesort_execution */,
"filesort_summary": {
"rows": 21,
"examined_rows": 95969,
"number_of_tmp_files": 0,
"sort_buffer_size": 3864,
"sort_mode": "<sort_key, additional_fields>"
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
}
] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)user全表扫描代价 "table": "`users`",
"table_scan": {
"rows": 1023461,
"cost": 7528
.....income全表扫描代价"table": "`income`",
"range_analysis": {
"table_scan": {
"rows": 1584731,
"cost": 321338
......idx_done_at索引income代价"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx_done_at",
"ranges": [
"1490544000 <= done_at <= 1490716800"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 190994,
"cost": 229194,这里明显用索引代价要小于全表扫描啊..但是为什么,全表扫描的是比用索引的世界短呢