在MySQL中同时对纬度和经度数据使用DECIMAL(17,13)时，是否会对性能产生影响? [英] Does it have impact on performance when using DECIMAL(17,13) for both Latitude and Longitude data in MySQL?
问题描述
我已经在Google上进行了研究，并阅读了有关Stack Overflow的一些答案. DECIMAL是存储纬度和经度数据的不错选择.
I have done research on Google and read some answers on Stack Overflow. DECIMAL is a good choice for storing latitude and longitude data.
但是人们对于小数点前后的位数有不同的看法.
But people have different opinions about the numbers of digit before and after the decimal point.
纬度范围最大为2位数字(-90和90)，经度范围最大为3位数字(-180和180).
Latitude range can be max 2 digits (-90 and 90) and longitude range can be max 3 digits (-180 and 180).
我从Restful API得到的小数点后的位数是12位.
The numbers of digit after the decimal point that I get from a Restful API is 12 digits.
因此，数据库列可以如下.
Thus the database columns can be as following.
lat DECIMAL(14, 12)
lon DECIMAL(15, 12)
在位数增加的情况下，我可以安全地设置以下最大位数.
I feel safe to set more max digits as following, in case numbers of digit increase.
lat DECIMAL(17, 13)
lon DECIMAL(17, 13)
这样，lat和lon最多可以存储数据，直到xxxx.xxxxxxxxxxxxx，因此4 digits在小数点之前，而13 digits在小数点之后.它对MySQL的性能有重要意义吗，这是没有必要的吗?设置它们为lat range is -90 and 90和lon range is -180 and 180令我感到很愚蠢，但是我感到更加安全.但是同时我也担心性能.
This way, both lat and lon can store data up to xxxx.xxxxxxxxxxxxx, thus 4 digits before the decimal point and 13 digits after the decimal point. Does it have import on the performance in MySQL and it is not necessary? I feel stupid to set them so because lat range is -90 and 90 and lon range is -180 and 180, but I feel more safe. But at the same time I concern about the performance.
或者将它们设置为:
lat DECIMAL(14, 12)
lon DECIMAL(15, 12)
并且不必担心任何事情，因为这对于存储纬度和经度数据已经完全安全了吗?
And no need to worry about anything because this is already completely safe for storing latitude and longitude data?
推荐答案
即使12位数字也是荒谬的.我推荐以下之一:
Even 12 digits is ludicrous. I recommend one of the following:
DECIMAL(8,6)/(9,6)足以区分两个相邻的人.而且我怀疑GPS并不是那么精确.总计:两列为9个字节.
DECIMAL(8,6)/(9,6) is sufficient to distinguish two persons standing next to each other. And I suspect GPS is not that precise. Total: 9 bytes for the two columns.
DECIMAL(6,4)/(7,4)对于房屋或企业而言已足够，但没有垂直组件. 7个字节.
DECIMAL(6,4)/(7,4) is sufficient for houses or businesses, except that there is no vertical component. 7 bytes.
更多关于lat/lng精度的讨论: http://mysql.rjweb.org /doc.php/latlng#representation_choices 或 https://stackoverflow.com/a/50126941/1766831
More discussion of lat/lng precision: http://mysql.rjweb.org/doc.php/latlng#representation_choices or https://stackoverflow.com/a/50126941/1766831
至于性能，并没有太大的区别.这是要点:
As for performance, there is not a lot of difference. Here are the points:
- 更多的小数位会占用更多的磁盘(和内存缓存)空间，因此会稍微降低速度.
- 更多的小数位数表示要进行更多的计算以使用数字.同样，这只是一个小小的打击.
您提到的其他尺寸:
lat DECIMAL(17, 13)
lon DECIMAL(17, 13)
总共需要16个字节.同时，这对:
takes a total of 16 bytes. Meanwhile, this pair:
lat DECIMAL(14, 12)
lon DECIMAL(15, 12)
仅小一个字节！
这篇关于在MySQL中同时对纬度和经度数据使用DECIMAL(17,13)时，是否会对性能产生影响?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
