使用索引優化查詢

以下查詢正在查找距離硬編碼緯度/對數 5 到 10 公里（實際上是另一個城鎮）的所有城鎮。我有 37010 個城鎮。

我正在使用 Symfony 2，它通過 Doctrine “創建”一個查詢。這個查詢被我的 MariaDB 伺服器標記為“不使用索引”。我不知道發生了什麼，因為：（1）這是查詢：

SELECT
   v0_.id AS id0,
   v0_.nom AS nom1,
   v0_.url AS url2,
   v0_.cp AS cp3,
   v0_.insee AS insee4,
   ROUND(
       6371 *
       ACOS(COS(RADIANS(50.58907000)) *
       COS(RADIANS(v0_.lat)) *
       COS(RADIANS(v0_.lng) -
       RADIANS(3.16710500)) +
       SIN(RADIANS(50.58907000)) *
       SIN(RADIANS(v0_.lat))), 2
   ) AS sclr5
   FROM ville v0_
   HAVING sclr5 > 4 AND sclr5 <= 10
   ORDER BY sclr5 ASC LIMIT 20 OFFSET 0;

這是時間：

# User@Host: x[x] @ localhost []
# Thread_id: 1514  Schema: mydatabase  QC_hit: No
# Query_time: 0.071503  Lock_time: 0.000137  Rows_sent: 20  Rows_examined: 37030

這是表格：

MariaDB [mydatabase]> desc ville;
+-----------------+---------------------+------+-----+---------+----------------+
| Field           | Type                | Null | Key | Default | Extra          |
+-----------------+---------------------+------+-----+---------+----------------+
| id              | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| id_origine      | bigint(20) unsigned | YES  | MUL | NULL    |                |
| date_v_creation | datetime            | YES  | MUL | NULL    |                |
| date_v_debut    | datetime            | YES  | MUL | NULL    |                |
| date_v_fin      | datetime            | YES  | MUL | NULL    |                |
| article         | varchar(4)          | YES  |     |         |                |
| nom             | varchar(150)        | NO   | MUL |         |                |
| url             | varchar(150)        | NO   | MUL |         |                |
| cp              | varchar(10)         | NO   | MUL | NULL    |                |
| insee           | varchar(10)         | NO   |     |         |                |
| id_region       | bigint(20) unsigned | NO   | MUL | NULL    |                |
| id_departement  | bigint(20) unsigned | NO   | MUL | NULL    |                |
| lat             | decimal(15,8)       | NO   | MUL | NULL    |                |
| lng             | decimal(15,8)       | NO   |     | NULL    |                |
| sound           | varchar(252)        | YES  | MUL | NULL    |                |
+-----------------+---------------------+------+-----+---------+----------------+
15 rows in set (0.01 sec)

MariaDB [mydatabase]>

如果我對此查詢進行解釋，我會得到：

+------+-------------+-------+------+---------------+------+---------+------+-------+----------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra          |
+------+-------------+-------+------+---------------+------+---------+------+-------+----------------+
|    1 | SIMPLE      | v0_   | ALL  | NULL          | NULL | NULL    | NULL | 36510 | Using filesort |
+------+-------------+-------+------+---------------+------+---------+------+-------+----------------+

一組中的 1 行（0.00 秒）

我有 37010 個城鎮，MariaBN 告訴我Rows_examined: 37030。我不明白。無論如何，您將如何優化它？

問題是您僅通過該大型計算進行過濾，因此別無選擇，只能進行全表掃描並為每一行計算該公式。

您可以通過在 lat 和 long 列上添加索引來限制考慮的行數，並按近似平方以及精確的 crow-flies 計算進行過濾。這樣，查詢執行器應該能夠使用該索引來查找那些在正方形的較大區域中的那些（“n1 和 n2 之間的 x 和 n3 和 n4 之間的 y”應該使用該索引（索引查找後跟部分掃描)) 然後只需要查找和讀取完整的行並對這幾個匹配項進行計算和排序以找到最終的過濾/有限集。

如果您有機會考慮在項目中此時更改數據庫後端（例如帶有 PostGIS 擴展的 postgres）支持用於處理幾何數據的特殊類型和索引（甚至有時本機支持基於緯度/經度的計算），儘管這種變化如果這是您的應用程序中唯一會使用它的部分，則可能是矯枉過正。

引用自：https://dba.stackexchange.com/questions/62547