반응형
이 쿼리 mysql 및 레일 5를 개선하는 방법
테이블이 세 개 있습니다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)
레일 5.1(이것은 큰 레일 4 레거시 데이터베이스)을 사용하고 있습니다.
다음으로 이 쿼리를 빠르게 해야 합니다.
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)
store_customer_associations: INDEX(store_id, user_id) -- "covering"
store_id첫 번째 -- 처리한다.WHERE;user_id인덱스를 터치하기만 하면 되도록 추가됩니다.
이 튜토리얼을 사용하여INDEX본인 : http://mysql.rjweb.org/doc.php/index_cookbook_mysql
쿼리 인덱스는 where part가 아니기 때문에 store_curstometer_associations의 2개의 파라미터에 대한 인덱스를 작성해야 합니다.
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)
언급URL : https://stackoverflow.com/questions/49929996/how-to-improve-this-query-mysql-and-rails-5
반응형
'source' 카테고리의 다른 글
| NumPy 어레이에서 NaN 값을 삭제하려면 어떻게 해야 합니까? (0) | 2022.11.24 |
|---|---|
| Mac용 ext-zip 설치 (0) | 2022.11.24 |
| 사용자 정의 변수를 사용하여 두 번째 데이터베이스에 동적으로 이름을 지정하여 해당 두 번째 데이터베이스와 함께 조인 테이블에 이름을 지정합니다. (0) | 2022.11.24 |
| JavaScript에서 오브젝트에 구성원을 조건부로 추가하는 방법은 무엇입니까? (0) | 2022.11.24 |
| Larabel에는 Mcrypt PHP 확장자가 필요합니다. (0) | 2022.11.24 |