为什么MySQL不使用最佳索引 [英] Why does MySQL not use optimal indexes
问题描述
我正在尝试优化我的查询，但是，MySQL似乎在查询上使用非最佳索引，我似乎无法弄清楚出了什么问题。我的查询如下：
I'm trying to optimize my query, however, MySQL seems to be utilizing non-optimal indexes on the query and I can't seem to figure out what is wrong. My query is as follows:
SELECT SQL_CALC_FOUND_ROWS deal_ID AS ID,dealTitle AS dealSaving,
storeName AS title,deal_URL AS dealURL,dealDisclaimer,
dealType, providerName,providerLogo AS providerIMG,createDate,
latitude AS lat,longitude AS lng,'local' AS type,businessType,
address1,city,dealOriginalPrice,NULL AS dealDiscountPercent,
dealPrice,scoringBase, smallImage AS smallimage,largeImage AS image,
storeURL AS storeAlias,
exp(-power(greatest(0,
abs(69.0*DEGREES(ACOS(0.82835377099147 *
COS(RADIANS(latitude)) * COS(RADIANS(-118.4-longitude)) +
0.56020534635454*SIN(RADIANS(latitude)))))-2),
2)/(5.7707801635559)) *
scoringBase * IF(submit_ID IN (18381),
IF(businessType = 1,1.3,1.2),IF(submit_ID IN (54727),1.19, 1)
) AS distance
FROM local_deals
WHERE latitude BETWEEN 33.345362318841 AND 34.794637681159
AND longitude BETWEEN -119.61862872928 AND -117.18137127072
AND state = 'CA'
AND country = 'US'
ORDER BY distance DESC
LIMIT 48 OFFSET 0;
在表格中列出索引显示：
Listing the indexes on the table reveals:
+-------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| local_deals | 0 | PRIMARY | 1 | id | A | 193893 | NULL | NULL | | BTREE | | |
| local_deals | 0 | unique_deal_ID | 1 | deal_ID | A | 193893 | NULL | NULL | | BTREE | | |
| local_deals | 1 | deal_ID | 1 | deal_ID | A | 193893 | NULL | NULL | | BTREE | | |
| local_deals | 1 | store_ID | 1 | store_ID | A | 193893 | NULL | NULL | YES | BTREE | | |
| local_deals | 1 | storeOnline_ID | 1 | storeOnline_ID | A | 3 | NULL | NULL | YES | BTREE | | |
| local_deals | 1 | storeChain_ID | 1 | storeChain_ID | A | 117 | NULL | NULL | YES | BTREE | | |
| local_deals | 1 | userProvider_ID | 1 | userProvider_ID | A | 5 | NULL | NULL | YES | BTREE | | |
| local_deals | 1 | expirationDate | 1 | expirationDate | A | 3127 | NULL | NULL | YES | BTREE | | |
| local_deals | 1 | createDate | 1 | createDate | A | 96946 | NULL | NULL | YES | BTREE | | |
| local_deals | 1 | city | 1 | city | A | 17626 | NULL | NULL | YES | BTREE | | |
| local_deals | 1 | state | 1 | state | A | 138 | NULL | NULL | YES | BTREE | | |
| local_deals | 1 | zip | 1 | zip | A | 38778 | NULL | NULL | YES | BTREE | | |
| local_deals | 1 | country | 1 | country | A | 39 | NULL | NULL | YES | BTREE | | |
| local_deals | 1 | latitude | 1 | latitude | A | 193893 | NULL | NULL | YES | BTREE | | |
| local_deals | 1 | longitude | 1 | longitude | A | 193893 | NULL | NULL | YES | BTREE | | |
| local_deals | 1 | eventDate | 1 | eventDate | A | 4215 | NULL | NULL | YES | BTREE | | |
| local_deals | 1 | isNowDeal | 1 | isNowDeal | A | 3 | NULL | NULL | YES | BTREE | | |
| local_deals | 1 | businessType | 1 | businessType | A | 5 | NULL | NULL | YES | BTREE | | |
| local_deals | 1 | dealType | 1 | dealType | A | 5 | NULL | NULL | YES | BTREE | | |
| local_deals | 1 | submit_ID | 1 | submit_ID | A | 5 | NULL | NULL | YES | BTREE | | |
+-------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
运行解释扩展显示：
+------+-------------+-------------+------+----------------------------------+-------+---------+-------+-------+----------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------------+------+----------------------------------+-------+---------+-------+-------+----------+----------------------------------------------------+
| 1 | SIMPLE | local_deals | ref | state,country,latitude,longitude | state | 35 | const | 52472 | 100.00 | Using index condition; Using where; Using filesort |
+------+-------------+-------------+------+----------------------------------+-------+---------+-------+-------+----------+----------------------------------------------------+
表格中有大约200,000行。奇怪的是它忽略了纬度和经度索引，因为它们应该更多地过滤表格。运行查询，我删除state和country，其中命令显示以下说明：
There are around 200k rows in the table. What is strange is that it is ignoring the latitude and longitude indexes as those should filter the table more. Running a query where I remove the "state" and "country" where commands reveals the following explain:
+------+-------------+-------------+-------+--------------------+-----------+---------+------+-------+----------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------------+-------+--------------------+-----------+---------+------+-------+----------+----------------------------------------------------+
| 1 | SIMPLE | local_deals | range | latitude,longitude | longitude | 5 | NULL | 30662 | 100.00 | Using index condition; Using where; Using filesort |
+------+-------------+-------------+-------+--------------------+-----------+---------+------+-------+----------+----------------------------------------------------+
这表明经度指数会更好地将表格过滤到30,662行。我在这里错过了什么吗？如何让MySQL使用所有查询。请注意，该表是InnoDB，我使用的是MySQL 5.5。
This shows that the longitude index would better filter the table to 30,662 rows. Am I missing something here? How can I get MySQL to use all queries. Note that the table is InnoDB and I'm using MySQL 5.5.
推荐答案
查询的最佳索引是复合索引（国家，州，纬度，经度）（ country 和州可以交换）。 MySQL有很多关于多列索引的文档，这些文档是这里。
The best index for your query is a composite index on (country, state, latitude, longitude) (country and state could be swapped). MySQL has good documentation on multi-column indexes, which is here.
基本上，纬度和经度不是特别具有选择性。不幸的是，标准的B树索引只支持一个不等式，而你的查询有两个。
Basically, latitude and longitude are not particularly selective individually. Unfortunately, the standard B-tree index only supports one inequality, and your query has two.
实际上，如果你想要GIS处理，那么你应该使用空间扩展来MySQL。
Actually, if you want GIS processing, then you should use a spatial extension to MySQL.
这篇关于为什么MySQL不使用最佳索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
