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

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

解決的問(wèn)題

很多業(yè)務(wù)有查找某個(gè)點(diǎn)附近的人，附近的商戶(hù)等等。

準(zhǔn)備

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

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)度'
);

最初方案

我們假設(shè)地球是圓的，然后使用兩點(diǎn)所在最大元圓公式來(lái)計(jì)算兩點(diǎn)之間的距離。

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

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

---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;

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

改進(jìn)方案

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

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)

然而這樣就不能使用索引了（這里不考慮建兩個(gè)索引的情況），因?yàn)閮蓚€(gè)查詢(xún)都是都是范圍的。但是我們可以使用IN()優(yōu)化，我們先新增兩個(gè)列，用來(lái)存儲(chǔ)坐標(biāo)的近似值FLOOR()，然后在查詢(xún)中使用IN()講所有點(diǎn)的整數(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ù)近似值來(lái)計(jì)算了，以下的計(jì)算可以在程序中進(jìn)行，限定了經(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);

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