早在《高性能MySQL》上看到了，一直想記錄下來，我有嚴重拖延癥。。。最近公司在這個類似業(yè)務方面需要優(yōu)化，所以趕緊再仔細看一下，記錄下來，加深一下印象。

此文章參考來源《高性能MySQL》6.8.2節(jié)，有興趣可以去看一下。

解決的問題

很多業(yè)務有查找某個點附近的人，附近的商戶等等。

準備

創(chuàng)建保存用戶位置的表

CREATE TABLE locations (
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(30),
  lat FLOAT NOT NULL CONTENT '緯度',
  lon FLOAT NOT NULL CONTENT '經(jīng)度'
);

最初方案

我們假設地球是圓的，然后使用兩點所在最大元圓公式來計算兩點之間的距離。

球面距離公式是計算球面上兩點間距離的公式。設所求點A ，緯度β1 ，經(jīng)度α1 ；點B ，緯度β2 ，經(jīng)度α2。
則距離S=R·arccos[cosβ1cosβ2cos（α1-α2）+sinβ1sinβ2]，其中R為球體半徑。

以上公式R是地球的半徑，如果去掉R則是兩點之間的弧度。如果直接按照這種方法計算兩點之間的距離，則計算公式如下：

---RADIANS(X)：返回X從度轉(zhuǎn)換成弧度的值 
SELECT * FROM locations 
WHERE 3979 * ACOS(
COS(RADIANS(lat))*COS(RADIANS(38.03))*COS(RADIANS(lon)-RADIANS(-78.48)) 
+ SIN(RADIANS(lat))*SIN(38.03))<=100;

缺點：這類查詢不僅無法使用索引，而且還會非常消耗CPU時間，給數(shù)據(jù)庫帶來很大的壓力。
思考：也許我們不需要這么精確的計算，其實本身地址已經(jīng)很不精確，再加上沒有正在直接的距離。地球確切的說也不是圓的。

改進方案

如果我們不用圓周，而是用正方形，如圖(隨便看看，下圖是圓周，把那個地方改成正方形)：

image.png

-----DEGREES(X):返回X從弧度轉(zhuǎn)換為度值----
SWLECT * FROM locations 
WHERE lat BETWEEN 38.03-DEGREES(0.0253) AND 38.03 +DEGREES(0.0253) 
AND 
lon  BETEEEN -78.48-DEGREES(0.0253) AND -78.48 +DEGREES(0.0253)

然而這樣就不能使用索引了（這里不考慮建兩個索引的情況），因為兩個查詢都是都是范圍的。但是我們可以使用IN()優(yōu)化，我們先新增兩個列，用來存儲坐標的近似值FLOOR()，然后在查詢中使用IN()講所有點的整數(shù)值都放到列表中。

索引優(yōu)化

下面是我們需要新增的列和索引：

ALTER TABLE locations ADD lat_floor INT NOT NULL DEFAULT 0,
ADD lon_floor INT NOT NULL DEFAULT 0,
ADD KEY(lat_floor,lon_floor);
------更新這兩列的值
UPDATE locations SET lat_floor= FLOOR(lat), lon_floor = FLOOR(lon);

現(xiàn)在可以根據(jù)近似值來計算了，以下的計算可以在程序中進行，限定了經(jīng)緯度的范圍

-----CEILING(X)：返回的最小整數(shù)值不小于X；
-----FLOOR(X)：返回的最大整數(shù)但不大于X的值；
SELECT FLOOR(38.03-DEGREES(0.0253)) AS lat_lb,
CEILING(38.03+DEGREES(0.0253)) AS lat_ub,
FLOOR(-78.48-DEGREES(0.0253)) AS lon_lb,
CEILING(-78.48+DEGREES(0.0253)) AS lon_ub;
------結(jié)果為36 40 -80 -77

加上索引的sql

SWLECT * FROM locations 
WHERE lat BETWEEN 38.03-DEGREES(0.0253) AND 38.03 +DEGREES(0.0253) 
AND 
lon  BETEEEN -78.48-DEGREES(0.0253) AND -78.48 +DEGREES(0.0253)
AND lat_floor IN(36,37,38,39,40) AND lon_floor IN(-80,-79,-78,-77);

這樣就可以使用索引了，并且速度也很快了。如果希望精度再高些，則可以使用第一個sql再加上IN查詢就可以了。