如何改进这个查询mysql和rails5

cgvd09ve 于 2021-06-24 发布在 Mysql

关注(0)|答案(2)|浏览(335)

我们有三张table users , stores 以及 store_customer_associations ，其中一个用户可以成为许多客户 stores .
我曾尝试在主键中放置另一个索引，但没有成功（其他也一样）。
（目前这些是我们在每个表中的索引）

mysql> SHOW INDEXES FROM store_customer_associations;
+-------+------------+-------------------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 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         |     9386515 |     NULL | NULL   |      | BTREE      |         |               |
| users |          0 | tmp_idx_users_id                    |            1 | id                   | A         |     9386515 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-------------------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
10 rows in set (0.00 sec)

mysql> SHOW INDEXES FROM store_customer_associations;
+-----------------------------+------------+-----------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                       | Non_unique | Key_name                                      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------------------+------------+-----------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| store_customer_associations |          0 | PRIMARY                                       |            1 | id          | A         |    51298761 |     NULL | NULL   |      | BTREE      |         |               |
| store_customer_associations |          1 | index_store_customer_associations_on_store_id |            1 | store_id    | A         |       50096 |     NULL | NULL   | YES  | BTREE      |         |               |
| store_customer_associations |          1 | index_store_customer_associations_on_user_id  |            1 | user_id     | A         |    25649380 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------------------------+------------+-----------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

我们使用的是Rails5.1（这是一个很大的Rails4遗留数据库）。
然后我们需要快速查询：

SELECT  COUNT(*)
    FROM  `users`
    INNER JOIN  `store_customer_associations`
         ON `users`.`id` = `store_customer_associations`.`user_id`
    WHERE  `store_customer_associations`.`store_id` = STORE_ID;

+----------+
| COUNT(*) |
+----------+
|  1997632 |
+----------+
1 row in set (6.64 sec)

mysql> EXPLAIN     SELECT  COUNT(*)
    FROM  `users`
    INNER JOIN  `store_customer_associations`
         ON `users`.`id` = `store_customer_associations`.`user_id`
    WHERE  `store_customer_associations`.`store_id` = STORE_ID;

+------+-------------+-----------------------------+--------+--------------------------------------------------------------------------------------------+-----------------------------------------------+---------+---------------------------------------------------------+---------+-------------+
| id   | select_type | table                       | type   | possible_keys                                                                              | key                                           | key_len | ref                             | rows    | Extra       |
+------+-------------+-----------------------------+--------+--------------------------------------------------------------------------------------------+-----------------------------------------------+---------+---------------------------------------------------------+---------+-------------+
|    1 | SIMPLE      | store_customer_associations | ref    | index_store_customer_associations_on_store_id,index_store_customer_associations_on_user_id | index_store_customer_associations_on_store_id | 5       | const                             | 4401812 | Using where |
|    1 | SIMPLE      | users                       | eq_ref | PRIMARY,tmp_idx_users_id                                                                   | PRIMARY                                       | 4       | trustvox_production.store_customer_associations.user_id |       1 | Using index |
+------+-------------+-----------------------------+--------+--------------------------------------------------------------------------------------------+-----------------------------------------------+---------+---------------------------------------------------------+---------+-------------+
2 rows in set (0.00 sec)

更新1

mysql> EXPLAIN     SELECT  COUNT(*)
    FROM  `users`
    INNER JOIN  `store_customer_associations`
         ON `users`.`id` = `store_customer_associations`.`user_id`
    WHERE  `store_customer_associations`.`store_id` = STORE_ID;

+------+-------------+-----------------------------+--------+-------------------------------------------------------------------------------------------------------------+-----------------------------------------------+---------+---------------------------------------------------------+---------+-------------+
| id   | select_type | table                       | type   | possible_keys                                                                                               | key                                           | key_len | ref                                              | rows    | Extra       |
+------+-------------+-----------------------------+--------+-------------------------------------------------------------------------------------------------------------+-----------------------------------------------+---------+---------------------------------------------------------+---------+-------------+
|    1 | SIMPLE      | store_customer_associations | ref    | index_store_customer_associations_on_store_id,index_store_customer_associations_on_user_id,user_id_store_id | index_store_customer_associations_on_store_id | 5       | const                                              | 4401812 | Using where |
|    1 | SIMPLE      | users                       | eq_ref | PRIMARY,tmp_idx_users_id                                                                                    | PRIMARY                                       | 4       | trustvox_production.store_customer_associations.user_id |       1 | Using index |
+------+-------------+-----------------------------+--------+-------------------------------------------------------------------------------------------------------------+-----------------------------------------------+---------+---------------------------------------------------------+---------+-------------+
2 rows in set (0.00 sec)

更新2

mysql>     SELECT  COUNT(*)
    FROM  users
    INNER JOIN  store_customer_associations
        FORCE INDEX FOR JOIN  (PRIMARY, user_id_store_id)
         ON users.id = store_customer_associations.user_id
    WHERE  store_customer_associations.store_id = STORE_ID;

+----------+
| COUNT(*) |
+----------+
|  1997632 |
+----------+
1 row in set (28.90 sec)

mysql> EXPLAIN     SELECT  COUNT(*)
    FROM  users
    INNER JOIN  store_customer_associations
        FORCE INDEX FOR JOIN  (PRIMARY, user_id_store_id)
         ON users.id = store_customer_associations.user_id
    WHERE  store_customer_associations.store_id = STORE_ID;
+------+-------------+-----------------------------+-------+--------------------------+------------------+---------+------------------------------------+---------+-------------+
| id   | select_type | table                       | type  | possible_keys            | key              | key_len | ref                                | rows    | Extra       |
+------+-------------+-----------------------------+-------+--------------------------+------------------+---------+------------------------------------+---------+-------------+
|    1 | SIMPLE      | users                       | index | PRIMARY,tmp_idx_users_id | tmp_idx_users_id | 4       | NULL                               | 8675689 | Using index |
|    1 | SIMPLE      | store_customer_associations | ref   | user_id_store_id         | user_id_store_id | 10      | trustvox_production.users.id,const |       1 | Using index |
+------+-------------+-----------------------------+-------+--------------------------+------------------+---------+------------------------------------+---------+-------------+
2 rows in set (0.00 sec)

mysql>

更新3

mysql> EXPLAIN     SELECT  COUNT(*)
    FROM  users
    INNER JOIN  
        ( SELECT  *
            FROM  store_customer_associations
            WHERE  store_id = 75856
        ) sca  ON users.id = sca.user_id;

+------+-------------+-----------------------------+--------+-------------------------------------------------------------------------------------------------------------+-----------------------------------------------+---------+---------------------------------------------------------+---------+-------------+
| id   | select_type | table                       | type   | possible_keys                                                                                               | key                                           | key_len | ref                                              | rows    | Extra       |
+------+-------------+-----------------------------+--------+-------------------------------------------------------------------------------------------------------------+-----------------------------------------------+---------+---------------------------------------------------------+---------+-------------+
|    1 | SIMPLE      | store_customer_associations | ref    | index_store_customer_associations_on_store_id,index_store_customer_associations_on_user_id,user_id_store_id | index_store_customer_associations_on_store_id | 5       | const                                              | 4401812 | Using where |
|    1 | SIMPLE      | users                       | eq_ref | PRIMARY,tmp_idx_users_id                                                                                    | PRIMARY                                       | 4       | trustvox_production.store_customer_associations.user_id |       1 | Using index |
+------+-------------+-----------------------------+--------+-------------------------------------------------------------------------------------------------------------+-----------------------------------------------+---------+---------------------------------------------------------+---------+-------------+

更新4

mysql> ALTER TABLE store_customer_associations DROP INDEX index_store_customer_associations_on_store_id;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE store_customer_associations DROP INDEX index_store_customer_associations_on_user_id;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE INDEX index_on_store_id_and_user_id ON store_customer_associations (store_id, user_id);
Query OK, 0 rows affected (45.95 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE INDEX index_store_customer_associations_on_user_id ON store_customer_associations(user_id);
Query OK, 0 rows affected (33.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE INDEX index_store_customer_associations_on_store_id ON store_customer_associations(store_id);
Query OK, 0 rows affected (38.58 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEXES FROM store_customer_associations;
+-----------------------------+------------+-----------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                       | Non_unique | Key_name                                      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------------------+------------+-----------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| store_customer_associations |          0 | PRIMARY                                       |            1 | id          | A         |    10305620 |     NULL | NULL   |      | BTREE      |         |               |
| store_customer_associations |          1 | index_on_store_id_and_user_id                 |            1 | store_id    | A         |        8244 |     NULL | NULL   | YES  | BTREE      |         |               |
| store_customer_associations |          1 | index_on_store_id_and_user_id                 |            2 | user_id     | A         |    10305620 |     NULL | NULL   | YES  | BTREE      |         |               |
| store_customer_associations |          1 | index_store_customer_associations_on_user_id  |            1 | user_id     | A         |    10305620 |     NULL | NULL   | YES  | BTREE      |         |               |
| store_customer_associations |          1 | index_store_customer_associations_on_store_id |            1 | store_id    | A         |        6424 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------------------------+------------+-----------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)

mysql performance mariadb ruby-on-rails

来源：https://stackoverflow.com/questions/49929996/how-to-improve-this-query-mysql-and-rails-5

2条答案

按热度按时间

ej83mcc01#

store_customer_associations:  INDEX(store_id, user_id)  -- "covering"
``` `store_id` 首先是处理 `WHERE` ;  `user_id` 是这样添加的，只有索引需要被触碰。
一个让你发现 `INDEX` 你自己：http://mysql.rjweb.org/doc.php/index_cookbook_mysql

赞(0）回复(0）举报 2021-06-24

sdnqo3pr2#

你需要为存储\游标表\关联的2个参数建立索引，因为在查询中索引不是因为where部分。

create index user_id_store_id on store_customer_associations (user_id, store_id)

我应该帮你
例子

mysql> show indexes from api_plexts;
+------------+------------+----------------------------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name                               | Seq_in_index | Column_name        | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+----------------------------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| api_plexts |          0 | PRIMARY                                |            1 | id                 | A         |      206318 |     NULL | NULL   |      | BTREE      |         |               |
| api_plexts |          0 | index_api_plexts_on_guid               |            1 | guid               | A         |      215475 |     NULL | NULL   | YES  | BTREE      |         |               |
| api_plexts |          1 | index_api_plexts_on_api_agent_id       |            1 | api_agent_id       | A         |        1565 |     NULL | NULL   | YES  | BTREE      |         |               |
| api_plexts |          1 | index_api_plexts_on_from_api_portal_id |            1 | from_api_portal_id | A         |       11401 |     NULL | NULL   | YES  | BTREE      |         |               |
| api_plexts |          1 | index_api_plexts_on_to_api_portal_id   |            1 | to_api_portal_id   | A         |        9566 |     NULL | NULL   | YES  | BTREE      |         |               |
+------------+------------+----------------------------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)

mysql> show indexes from api_portals;
+-------------+------------+--------------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name                             | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+--------------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| api_portals |          0 | PRIMARY                              |            1 | id              | A         |       20530 |     NULL | NULL   |      | BTREE      |         |               |
| api_portals |          0 | index_api_portals_on_guid            |            1 | guid            | A         |       19891 |     NULL | NULL   | YES  | BTREE      |         |               |
| api_portals |          1 | index_api_portals_on_owner_agent_id  |            1 | owner_agent_id  | A         |         718 |     NULL | NULL   | YES  | BTREE      |         |               |
| api_portals |          1 | index_api_portals_on_api_map_tile_id |            1 | api_map_tile_id | A         |        5480 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------------+------------+--------------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
explain select count(*) from api_portals ap JOIN api_plexts al FORCE INDEX FOR JOIN (PRIMARY) on ap.id = al.from_api_portal_id  where al.api_agent_id = 2\G;

mysql> show indexes from api_plexts;
+------------+------------+----------------------------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name                               | Seq_in_index | Column_name        | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+----------------------------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| api_plexts |          0 | PRIMARY                                |            1 | id                 | A         |      206318 |     NULL | NULL   |      | BTREE      |         |               |
| api_plexts |          0 | index_api_plexts_on_guid               |            1 | guid               | A         |      215475 |     NULL | NULL   | YES  | BTREE      |         |               |
| api_plexts |          1 | index_api_plexts_on_api_agent_id       |            1 | api_agent_id       | A         |        1565 |     NULL | NULL   | YES  | BTREE      |         |               |
| api_plexts |          1 | index_api_plexts_on_from_api_portal_id |            1 | from_api_portal_id | A         |       11401 |     NULL | NULL   | YES  | BTREE      |         |               |
| api_plexts |          1 | index_api_plexts_on_to_api_portal_id   |            1 | to_api_portal_id   | A         |        9566 |     NULL | NULL   | YES  | BTREE      |         |               |
+------------+------------+----------------------------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)

mysql> show indexes from api_portals;
+-------------+------------+--------------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name                             | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+--------------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| api_portals |          0 | PRIMARY                              |            1 | id              | A         |       20530 |     NULL | NULL   |      | BTREE      |         |               |
| api_portals |          0 | index_api_portals_on_guid            |            1 | guid            | A         |       19891 |     NULL | NULL   | YES  | BTREE      |         |               |
| api_portals |          1 | index_api_portals_on_owner_agent_id  |            1 | owner_agent_id  | A         |         718 |     NULL | NULL   | YES  | BTREE      |         |               |
| api_portals |          1 | index_api_portals_on_api_map_tile_id |            1 | api_map_tile_id | A         |        5480 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------------+------------+--------------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

mysql> explain select count(*) from api_portals ap JOIN api_plexts al on ap.id = al.from_api_portal_id where al.api_agent_id = 2;
+----+-------------+-------+------------+--------+-------------------------------------------------------------------------+----------------------------------+---------+-------------------------------+------+----------+--------------------------+
| id | select_type | table | partitions | type   | possible_keys                                                           | key                              | key_len | ref                           | rows | filtered | Extra                    |
+----+-------------+-------+------------+--------+-------------------------------------------------------------------------+----------------------------------+---------+-------------------------------+------+----------+--------------------------+
|  1 | SIMPLE      | al    | NULL       | ref    | index_api_plexts_on_api_agent_id,index_api_plexts_on_from_api_portal_id | index_api_plexts_on_api_agent_id | 5       | const                         |  271 |   100.00 | Using where              |
|  1 | SIMPLE      | ap    | NULL       | eq_ref | PRIMARY                                                                 | PRIMARY                          | 8       | ingress.al.from_api_portal_id |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+--------+-------------------------------------------------------------------------+----------------------------------+---------+-------------------------------+------+----------+--------------------------+

this is exactly your case U can see using where
mysql> create index testime on api_plexts (api_agent_id, from_api_portal_id);                                                                                                                               Query OK, 0 rows affected (3.71 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show indexes from api_plexts;
+------------+------------+----------------------------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name                               | Seq_in_index | Column_name        | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+----------------------------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| api_plexts |          0 | PRIMARY                                |            1 | id                 | A         |      200644 |     NULL | NULL   |      | BTREE      |         |               |
| api_plexts |          0 | index_api_plexts_on_guid               |            1 | guid               | A         |      209549 |     NULL | NULL   | YES  | BTREE      |         |               |
| api_plexts |          1 | index_api_plexts_on_api_agent_id       |            1 | api_agent_id       | A         |        1522 |     NULL | NULL   | YES  | BTREE      |         |               |
| api_plexts |          1 | index_api_plexts_on_from_api_portal_id |            1 | from_api_portal_id | A         |       11087 |     NULL | NULL   | YES  | BTREE      |         |               |
| api_plexts |          1 | index_api_plexts_on_to_api_portal_id   |            1 | to_api_portal_id   | A         |        9303 |     NULL | NULL   | YES  | BTREE      |         |               |
| api_plexts |          1 | testime                                |            1 | api_agent_id       | A         |        1817 |     NULL | NULL   | YES  | BTREE      |         |               |
| api_plexts |          1 | testime                                |            2 | from_api_portal_id | A         |       52277 |     NULL | NULL   | YES  | BTREE      |         |               |
+------------+------------+----------------------------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.00 sec)

VOILA!!!

mysql> explain select count(*) from api_portals ap JOIN api_plexts al on ap.id = al.from_api_portal_id where al.api_agent_id = 2\G;                          ***************************1. row***************************
           id: 1
  select_type: SIMPLE
        table: al
   partitions: NULL
         type: ref
possible_keys: index_api_plexts_on_api_agent_id,index_api_plexts_on_from_api_portal_id,testime
          key: testime
      key_len: 5
          ref: const
         rows: 271
     filtered: 100.00
        Extra: Using where; Using index

***************************2. row***************************

           id: 1
  select_type: SIMPLE
        table: ap
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: ingress.al.from_api_portal_id
         rows: 1
     filtered: 100.00
        Extra: Using where; Using index
2 rows in set, 1 warning (0.00 sec)

赞(0）回复(0）举报 2021-06-24

我来回答

如何改进这个查询mysql和rails5

2条答案

相关问题

热门标签

最新问答