mysql 疑问之 用了索引反而慢

表结构

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)

sql

取出某个时间段,收入前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);

索引情况,这里users表我们还是不加索引

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)

TRACE分析

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,

这里明显用索引代价要小于全表扫描啊..但是为什么,全表扫描的是比用索引的世界短呢