반응형
지역 검색 쿼리 최적화
현재 user_geo_places 테이블에 사용자 위치를 저장하고 있습니다.나는 그들의 과거 위치를 포함한 모든 최신 위치를 저장한다.
내가 필요로 하는 공통적인 일
- 카테고리별로 마지막 위치만 조회하다
- 인근 사용자에게 지정된 위치에 대한 쿼리
- 거리순으로 결과를 정렬하다
- 지정된 위치에서 각 결과 기준의 거리를 가져옵니다.
테이블
+------------+---------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------------------+-------+
| id | varchar(36) | NO | | NULL | |
| user_id | varchar(36) | NO | MUL | NULL | |
| deleted_at | timestamp | YES | | NULL | |
| created_at | timestamp | NO | | 0000-00-00 00:00:00 | |
| updated_at | timestamp | NO | MUL | 0000-00-00 00:00:00 | |
| latitude | double(25,20) | NO | MUL | NULL | |
| longitude | double(25,20) | NO | MUL | NULL | |
| category | varchar(36) | YES | | NULL | |
| status | int(11) | YES | | 1 | |
+------------+---------------+------+-----+---------------------+-------+
이것은 나의 색인입니다.
+-----------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user_geo_places | 1 | latitude | 1 | latitude | A | 1894347 | NULL | NULL | | BTREE | | |
| user_geo_places | 1 | longitude | 1 | longitude | A | 1894347 | NULL | NULL | | BTREE | | |
| user_geo_places | 1 | updated_at | 1 | updated_at | A | 18 | NULL | NULL | | BTREE | | |
| user_geo_places | 1 | user_id | 1 | user_id | A | 1894347 | NULL | NULL | | BTREE | | |
+-----------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
각 사용자의 최신 위치 가져오기
Select user_geo_places.*
from user_geo_places
left join user_geo_places b
ON (user_geo_places.user_id = b.user_id
and user_geo_places.created_at < b.created_at )
where b.created_at is NULL
and user_geo_places.category = 'plcs'
결과:
+--------------------------------------+--------------------------------------+------------+---------------------+---------------------+-------------------------+--------------------------+----------+---------+
| id | user_id | deleted_at | created_at | updated_at | latitude | longitude | category | status |
+--------------------------------------+--------------------------------------+------------+---------------------+---------------------+-------------------------+--------------------------+----------+---------+
| 00019a37-e790-11e6-8469-5404a66ff99a | e20e7777-e788-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.48057242434525200000 | -74.44768883329601000000 | plcs | 1 |
| 0006162a-e790-11e6-8469-5404a66ff99a | e20e7aef-e772-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.79672692417793640000 | -74.64056815173530000000 | plcs | 1 |
| 000617f7-e790-11e6-8469-5404a66ff99a | e20ec3c5-e775-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.81266001687632900000 | -74.14159565990940000000 | plcs | 1 |
| 00061914-e790-11e6-8469-5404a66ff99a | e20edec3-e785-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.26999827965162600000 | -74.92520444926359000000 | plcs | 1 |
| 00061a1d-e790-11e6-8469-5404a66ff99a | e20eefec-e780-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.81602743809672800000 | -74.30452387342650000000 | plcs | 1 |
| 00061b21-e790-11e6-8469-5404a66ff99a | e20f3219-e766-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.07453708357600200000 | -74.45911382833413000000 | plcs | 1 |
| 00061c1e-e790-11e6-8469-5404a66ff99a | e20f7922-e786-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.07195792167629800000 | -74.98244815411275000000 | plcs | 1 |
| 00061d0e-e790-11e6-8469-5404a66ff99a | e20fd68b-e77e-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.69636703626845700000 | -74.53449074973770000000 | plcs | 1 |
| 00061e01-e790-11e6-8469-5404a66ff99a | e20fe033-e76b-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.58335267061679900000 | -74.31329113460453000000 | plcs | 1 |
| 00061ef4-e790-11e6-8469-5404a66ff99a | e2101c1b-e776-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.81639769190586900000 | -74.14211508644942000000 | plcs | 1 |
+--------------------------------------+--------------------------------------+------------+---------------------+---------------------+-------------------------+--------------------------+----------+---------+
10 rows in set (0.00 sec)
설명:
+------+-------------+-----------------+------+---------------+---------+---------+-------------------------------------+---------+-------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------------+------+---------------+---------+---------+-------------------------------------+---------+-------------------------+
| 1 | SIMPLE | user_geo_places | ALL | NULL | NULL | NULL | NULL | 1894347 | Using where |
| 1 | SIMPLE | b | ref | user_id | user_id | 110 | user_geo_places.user_id | 1 | Using where; Not exists |
+------+-------------+-----------------+------+---------------+---------+---------+-------------------------------------+---------+-------------------------+
2 rows in set (0.00 sec)
사용자의 위치를 거리별로 가져오려면
Select user_geo_places.*, (6371 * acos(cos(radians(40.3987545691419)) *
cos(radians(user_geo_places.latitude)) *
cos(radians(user_geo_places.longitude)-
radians(-74.70559604904))+sin(radians(40.3987545691419)) *
sin(radians(user_geo_places.latitude)))) as distance
from user_geo_places
left join user_geo_places b
ON (user_geo_places.user_id = b.user_id
and user_geo_places.created_at < b.created_at )
where b.created_at is NULL
and user_geo_places.category = 'plcs'
having distance <= 10
order by distance
limit 10;
결과:
+--------------------------------------+--------------------------------------+------------+---------------------+---------------------+-------------------------+---------------------------+----------+---------+---------------------+
| id | user_id | deleted_at | created_at | updated_at | latitude | longitude | category | status | distance |
+--------------------------------------+--------------------------------------+------------+---------------------+---------------------+-------------------------+---------------------------+----------+---------+---------------------+
| c4d8e37b-e78f-11e6-8469-5404a66ff99a | 52616262-e78d-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.39875468090060600000 | -74.70637827898038000000 | plcs | 1 | 0.08625581062811027 |
| f4457454-e78f-11e6-8469-5404a66ff99a | cff6b247-e76e-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.39847620893127900000 | -74.70646391688517000000 | plcs | 1 | 0.10058008395499662 |
| bc0dbef2-e78f-11e6-8469-5404a66ff99a | 29ea9f29-e76a-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.39796618688662200000 | -74.70656721545996000000 | plcs | 1 | 0.13839511964323015 |
| c5949373-e78f-11e6-8469-5404a66ff99a | 53e7b25c-e778-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.39938782472106400000 | -74.70438668849356000000 | plcs | 1 | 0.15080387665201841 |
| c9771f58-e78f-11e6-8469-5404a66ff99a | 647afd7e-e76b-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.40025693587982800000 | -74.70498255799058000000 | plcs | 1 | 0.18023303176011723 |
| d1d0d5fe-e78f-11e6-8469-5404a66ff99a | 83e6d50e-e78c-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.39976791050263500000 | -74.70698894253651000000 | plcs | 1 | 0.19049204594530353 |
| c8592196-e78f-11e6-8469-5404a66ff99a | 5ef475ae-e789-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.39691713303040500000 | -74.70603059763650000000 | plcs | 1 | 0.20985736972660576 |
| b855003d-e78f-11e6-8469-5404a66ff99a | 18f63baa-e766-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.40058719124699700000 | -74.70495101688891000000 | plcs | 1 | 0.21583472659797534 |
| 0247ffb0-e790-11e6-8469-5404a66ff99a | e534712d-e77b-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.40040703062006000000 | -74.70668734489631000000 | plcs | 1 | 0.21964310635910547 |
| ef647fab-e78f-11e6-8469-5404a66ff99a | c981f563-e765-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.40036358721588200000 | -74.70425795922452000000 | plcs | 1 | 0.2319081044464142 |
+--------------------------------------+--------------------------------------+------------+---------------------+---------------------+-------------------------+--------------------------+----------+---------+---------------------+
10 rows in set (14.90 sec)
젠장, 15초?웹 쿼리로서는 매우 느립니다.
설명:
+------+-------------+-----------------+------+---------------+---------+---------+-------------------------------------+---------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------------+------+---------------+---------+---------+-------------------------------------+---------+-----------------------------+
| 1 | SIMPLE | user_geo_places | ALL | NULL | NULL | NULL | NULL | 1894347 | Using where; Using filesort |
| 1 | SIMPLE | b | ref | user_id | user_id | 110 | user_geo_places.user_id | 1 | Using where; Not exists |
+------+-------------+-----------------+------+---------------+---------+---------+-------------------------------------+---------+-----------------------------+
2 rows in set (0.00 sec)
전체 데이터베이스를 스캔하고 싶지 않습니다.10km 부근에 있는 사용자만 있으면 됩니다.
Select user_geo_places.*, (6371 * acos(cos(radians(40.3987545691419)) *
cos(radians(user_geo_places.latitude)) *
cos(radians(user_geo_places.longitude)-
radians(-74.70559604904))+sin(radians(40.3987545691419)) *
sin(radians(user_geo_places.latitude)))) as distance
from user_geo_places
left join user_geo_places b ON (user_geo_places.user_id = b.user_id
and user_geo_places.created_at < b.created_at
)
where b.created_at is NULL
and user_geo_places.category = 'plcs'
and user_geo_places.longitude
between -74.70559604904 - 10 / abs(cos(radians( 40.3987545691419 )) * 111.045)
AND -74.70559604904 + 10 / abs(cos(radians( 40.3987545691419 )) * 111.045)
and user_geo_places.latitude
between 40.3987545691419 - ( 10 / 111.045 )
AND 40.3987545691419 + ( 10 / 111.045 )
having distance <= 10
order by distance
limit 10;
결과:
+--------------------------------------+--------------------------------------+------------+---------------------+---------------------+------------------------+--------------------------+----------+---------+---------------------+
| id | user_id | deleted_at | created_at | updated_at | latitude | longitude | category | status | distance |
+--------------------------------------+--------------------------------------+------------+---------------------+---------------------+------------------------+--------------------------+----------+---------+---------------------+
| c4d8e37b-e78f-11e6-8469-5404a66ff99a | 52616262-e78d-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.39875468090060600000 | -74.70637827898038000000 | plcs | 1 | 0.08625581062811027 |
| f4457454-e78f-11e6-8469-5404a66ff99a | cff6b247-e76e-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.39847620893127900000 | -74.70646391688517000000 | plcs | 1 | 0.10058008395499662 |
| bc0dbef2-e78f-11e6-8469-5404a66ff99a | 29ea9f29-e76a-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.39796618688662200000 | -74.70656721545996000000 | plcs | 1 | 0.13839511964323015 |
| c5949373-e78f-11e6-8469-5404a66ff99a | 53e7b25c-e778-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.39938782472106400000 | -74.70438668849356000000 | plcs | 1 | 0.15080387665201841 |
| c9771f58-e78f-11e6-8469-5404a66ff99a | 647afd7e-e76b-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.40025693587982800000 | -74.70498255799058000000 | plcs | 1 | 0.18023303176011723 |
| d1d0d5fe-e78f-11e6-8469-5404a66ff99a | 83e6d50e-e78c-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.39976791050263500000 | -74.70698894253651000000 | plcs | 1 | 0.19049204594530353 |
| c8592196-e78f-11e6-8469-5404a66ff99a | 5ef475ae-e789-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.39691713303040500000 | -74.70603059763650000000 | plcs | 1 | 0.20985736972660576 |
| b855003d-e78f-11e6-8469-5404a66ff99a | 18f63baa-e766-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.40058719124699700000 | -74.70495101688891000000 | plcs | 1 | 0.21583472659797534 |
| 0247ffb0-e790-11e6-8469-5404a66ff99a | e534712d-e77b-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.40040703062006000000 | -74.70668734489631000000 | plcs | 1 | 0.21964310635910547 |
| ef647fab-e78f-11e6-8469-5404a66ff99a | c981f563-e765-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.40036358721588200000 | -74.70425795922452000000 | plcs | 1 | 0.2319081044464142 |
+--------------------------------------+--------------------------------------+------------+---------------------+---------------------+------------------------+--------------------------+----------+---------+---------------------+
10 rows in set (2 min 53.63 sec)
진짜? 2분 53.63초?끔찍해.
설명:
+------+-------------+-----------------+-------+--------------------+----------+---------+-------------------------------------+--------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------------+-------+--------------------+----------+---------+-------------------------------------+--------+----------------------------------------------------+
| 1 | SIMPLE | user_geo_places | range | latitude,longitude | latitude | 8 | NULL | 683684 | Using index condition; Using where; Using filesort |
| 1 | SIMPLE | b | ref | user_id | user_id | 110 | user_geo_places.user_id | 1 | Using where; Not exists |
+------+-------------+-----------------+-------+--------------------+----------+---------+-------------------------------------+--------+----------------------------------------------------+
2 rows in set (0.01 sec)
아마도 계산 때문인 것 같아요.그 중 몇 개를 옮겨서 변수로 설정해 봅시다.
Set @lon1= -74.70559604904 - 10 / abs(cos(radians( 40.3987545691419 )) * 111.045) ;
Set @lon2= -74.70559604904 + 10 / abs(cos(radians( 40.3987545691419 )) * 111.045) ;
Set @lat1= 40.3987545691419 - ( 10 / 111.045 );
Set @lat2= 40.3987545691419 + ( 10 / 111.045 );
Select
user_geo_places.*,
(6371 * acos(cos(radians(40.3987545691419)) * cos(radians(user_geo_places.latitude)) * cos(radians(user_geo_places.longitude)-radians(-74.70559604904))+sin(radians(40.3987545691419)) * sin(radians(user_geo_places.latitude)))) as distance
from user_geo_places
left join user_geo_places b
on (user_geo_places.user_id = b.user_id and user_geo_places.created_at < b.created_at)
where b.created_at is NULL
and user_geo_places.category = 'plcs'
and user_geo_places.longitude between @lon1 AND @lon2
and user_geo_places.latitude between @lat1 AND @lat2
having distance <= 10
order by distance
limit 10;
결과:
+--------------------------------------+--------------------------------------+------------+---------------------+---------------------+------------------------+--------------------------+----------+---------+---------------------+
| id | user_id | deleted_at | created_at | updated_at | latitude | longitude | category | status | distance |
+--------------------------------------+--------------------------------------+------------+---------------------+---------------------+------------------------+--------------------------+----------+---------+---------------------+
| c4d8e37b-e78f-11e6-8469-5404a66ff99a | 52616262-e78d-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.39875468090060600000 | -74.70637827898038000000 | plcs | 1 | 0.08625581062811027 |
| f4457454-e78f-11e6-8469-5404a66ff99a | cff6b247-e76e-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.39847620893127900000 | -74.70646391688517000000 | plcs | 1 | 0.10058008395499662 |
| bc0dbef2-e78f-11e6-8469-5404a66ff99a | 29ea9f29-e76a-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.39796618688662200000 | -74.70656721545996000000 | plcs | 1 | 0.13839511964323015 |
| c5949373-e78f-11e6-8469-5404a66ff99a | 53e7b25c-e778-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.39938782472106400000 | -74.70438668849356000000 | plcs | 1 | 0.15080387665201841 |
| c9771f58-e78f-11e6-8469-5404a66ff99a | 647afd7e-e76b-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.40025693587982800000 | -74.70498255799058000000 | plcs | 1 | 0.18023303176011723 |
| d1d0d5fe-e78f-11e6-8469-5404a66ff99a | 83e6d50e-e78c-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.39976791050263500000 | -74.70698894253651000000 | plcs | 1 | 0.19049204594530353 |
| c8592196-e78f-11e6-8469-5404a66ff99a | 5ef475ae-e789-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.39691713303040500000 | -74.70603059763650000000 | plcs | 1 | 0.20985736972660576 |
| b855003d-e78f-11e6-8469-5404a66ff99a | 18f63baa-e766-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.40058719124699700000 | -74.70495101688891000000 | plcs | 1 | 0.21583472659797534 |
| 0247ffb0-e790-11e6-8469-5404a66ff99a | e534712d-e77b-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.40040703062006000000 | -74.70668734489631000000 | plcs | 1 | 0.21964310635910547 |
| ef647fab-e78f-11e6-8469-5404a66ff99a | c981f563-e765-11e6-8469-5404a66ff99a | NULL | 2017-01-31 16:31:48 | 2017-01-31 16:31:48 | 40.40036358721588200000 | -74.70425795922452000000 | plcs | 1 | 0.2319081044464142 |
+--------------------------------------+--------------------------------------+------------+---------------------+---------------------+------------------------+--------------------------+----------+---------+---------------------+
10 rows in set (2 min 56.29 sec)
야, 너 진짜 느리다.더 이상 어떻게 해야 할지 모르겠어 전문가들에게 물어볼 시간이야
현재의.내 테이블에는 180만 개의 더미 데이터가 저장되어 있습니다.내 질문은 여전히 매우 느리다.이 테이블에는 카테고리별로 위치가 저장되므로 이 위치에 대한 다른 카테고리가 있습니다.이 문제를 해결하고 개선할 좋은 방법이 있나요?
내가 이걸 고칠 수 없다면.모든 데이터를 테이블별로 카테고리별로 이동할 예정입니다.그들의 과거 위치 때문에 일이 여전히 더디게 진행된다면.그 기능을 제거할 수도 있지만, 저는 이것을 꼭 이루고 싶었습니다.누군가 나를 도와줄 수 있기를 바란다
편집 1:
Show Create Table :
+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user_geo_places | CREATE TABLE `user_geo_places` (
`id` varchar(36) NOT NULL,
`user_id` varchar(36) NOT NULL,
`deleted_at` timestamp NULL DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`latitude` double(25,20) NOT NULL,
`longitude` double(25,20) NOT NULL,
`category` varchar(36) DEFAULT NULL,
`status` int(11) DEFAULT '1',
KEY `latitude` (`latitude`),
KEY `longitude` (`longitude`),
KEY `updated_at` (`updated_at`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
+--------------------------------+----------------------+
| Variable_name | Value |
+--------------------------------+----------------------+
| aria_pagecache_age_threshold | 300 |
| aria_pagecache_buffer_size | 134217728 |
| aria_pagecache_division_limit | 100 |
| aria_pagecache_file_hash_size | 512 |
| binlog_cache_size | 32768 |
| binlog_stmt_cache_size | 32768 |
| have_query_cache | YES |
| host_cache_size | 279 |
| innodb_disable_sort_file_cache | OFF |
| innodb_ft_cache_size | 8000000 |
| innodb_ft_result_cache_limit | 2000000000 |
| innodb_ft_total_cache_size | 640000000 |
| join_cache_level | 2 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| key_cache_file_hash_size | 512 |
| key_cache_segments | 0 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| metadata_locks_cache_size | 1024 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_strip_comments | OFF |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
| stored_program_cache | 256 |
| table_definition_cache | 400 |
| table_open_cache | 431 |
| thread_cache_size | 0 |
+--------------------------------+----------------------+
서버 사양
4 코어
8 GB RAM
마리아DB 10.1.20
사용되는 엔진: InnoDB
총 행 수: 1,894,326
innodb_module_pool_size : 134,217,728
현재 98%의 레코드는 모두 뉴욕에 있습니다.
SQL_NO_CACHE 실행
Select SQL_NO_CACHE
user_geo_places.*
from user_geo_places
left join user_geo_places b
on (user_geo_places.user_id = b.user_id and user_geo_places.created_at < b.created_at)
where b.created_at is NULL and user_geo_places.category = 'plcs'
limit 10
10 rows in set (0.00 - 0.07 sec)
덧붙여서, 컬럼의 상태는 무효가 되어 폐기됩니다.
테이블을 축소하는 것이 도움이 될 것이다.
double(25,20)12바이트가 걸리고 분자 폭 정도의 정밀도가 있어요UUIDs포장할 수 있다BINARY(16)(16바이트) 대신VARCHAR(36)(37 바이트).- 정상화하는 것이 유용할 수 있습니다.
category2바이트로SMALLINT UNSIGNED. INT항상 4바이트입니다.1바이트를 사용합니다.TINYINT같은 깃발에 대해서status.
없이.SHOW CREATE TABLE인덱스를 볼 수 없습니다.특히 단일 컬럼인덱스 또는 복합인덱스 중 어느 쪽이든 상관없습니다.
- 카테고리별 최신 정보는 다음과 같은 이점을 얻을 수 있습니다.
INDEX(category, created_at) - 'nearby'와 'distance by'는 특히 어렵습니다. 여기를 참조하십시오.
"현재 상태"와 "이력"을 두 개의 테이블로 분할하는 것이 종종 유용합니다.전자는 엔티티당 하나의 행만을 가지며, 후자는 (잠재적으로) 많은 행이 있다.이것에 의해, 「최신」에 관한 조회가 큰폭으로 심플화됩니다., 이 조작에 의해서, 「」를 .status비활성 엔트리를 "현재 상태"에서 제거하고 "이력"에 남겨두면 됩니다.「 」의 status (현재는) 쿼리의 다른 퍼포먼스 킬러일 수 있습니다.
마치...UUIDs는 "type 1"입니다.또, 그것들을 축소하는 것에 가세해, 비트를 시간순으로 정렬할 수도 있습니다.또한 '참조 위치'를 개선하여 일부 쿼리의 성능을 향상시킵니다.여기를 참조해 주세요.
테이블은 어떤 엔진을 사용하나요?그 엔진의 캐시는 얼마나 크죠?RAM 용량이 얼마나 됩니까?
LEFT JOIN「」가 필요합니다.INDEX(user_id, created_at)(물론 이력을 분리하면 이 기능은 더 이상 관련이 없습니다.)
표의 "현재 상태"는 몇 퍼센트입니까?
나는 몇 가지 제안을 했다; 어느 것이 가장 유익할지는 말하기 어렵다.
바운딩 박스를 들고 달리는 게 왜 이렇게 느리죠?여러 가지를 조합한 것 같습니다.
- 뉴욕의 20km는 많은 지역을 차지한다.테이블 1/3 정도?
- 인덱스를 사용할 때와 단순히 테이블을 스캔할 때 Optimizer가 항상 "올바른" 것은 아닙니다.이번에는 후자가 더 빨랐을 것이다.
- 캐싱의 문제가 있을 수 있습니다.
언급URL : https://stackoverflow.com/questions/41976042/optimizing-geo-search-query
반응형
'programing' 카테고리의 다른 글
| 설정 변수 텍스트 칼럼 폭 printf에. (0) | 2022.09.30 |
|---|---|
| 출발지가 Access-Control-Allow-Origin에서 허용되지 않습니다. (0) | 2022.09.30 |
| TypeError: 문자열 형식 지정 중 일부 인수가 변환되지 않았습니다. (0) | 2022.09.30 |
| Node.js가 내부적으로 스레드에 의존하고 있을 때 본질적으로 더 빠른 방법은 무엇입니까? (0) | 2022.09.30 |
| Mockito를 사용한 정적 메서드 조롱 (0) | 2022.09.30 |