索引的MariaDB SQL性能问题

Question

我们有一个在MariaDB mariadb-5.5.37-1.el7_0.x86_64上查找IP地理位置的表，如下所示：

创建表ip2location_db24 ( id int(11)非空AUTO_INCREMENT， ip_from int(10)无符号默认空， ip_to int(10)无符号默认空， country_code char(2)整理utf8_bin默认NULL， country_name varchar(64)整理utf8_bin默认NULL， region_name varchar(128)整理utf8_bin默认NULL， city_name varchar(128)整理utf8_bin默认NULL， latitude双默认值为空， longitude双默认值为空， zip_code varchar(30)整理utf8_bin默认NULL， time_zone varchar(8)整理utf8_bin默认NULL， isp varchar(255)整理utf8_bin默认NULL， domain varchar(128)整理utf8_bin默认NULL， net_speed varchar(8)整理utf8_bin默认NULL， idd_code varchar(5)整理utf8_bin默认NULL， area_code varchar(30)整理utf8_bin默认NULL， weather_station_code varchar(10)整理utf8_bin默认NULL， weather_station_name varchar(128)整理utf8_bin默认NULL， mcc varchar(256)整理utf8_bin默认NULL， mnc varchar(256)整理utf8_bin默认NULL， mobile_brand varchar(128)整理utf8_bin默认NULL， elevation int(10)默认为空， usage_type varchar(11)整理utf8_bin默认NULL， 主键(id)， KEY idx_ip_from (ip_from) KEY idx_latitude (latitude) KEY idx_longitude (longitude) KEY idx_ip_from_to_2 (ip_to，ip_from) ) ENGINE=InnoDB AUTO_INCREMENT=9541211默认CHARSET=utf8 COLLATE=utf8_bin

ip_from和ip_to列定义了每个地理位置的起始和结束边界。

我们在这张表上有大约一千万份记录。

当查询给定IP的geo位置时，我们发现服务器在以下SQL中存在严重的性能问题：

从ip2location_db24中选择*，其中ip_to >=1908980838和ip_from <=1908980838限制为1；*行* id: 5475739 ip_from: 1908932608 ip_to: 1909063679 country_code: CN country_name:中国 region_name:山西 city_name:太原 纬度: 37.86944 经度: 112.56028 zip_code：- time_zone：+08:00 isp:中国联通陕西省网络 域: CHINAUNICOM.COM net_speed: DSL idd_code: 86 area_code: 0351 weather_station_code: CHXX0129 weather_station_name:太原 管理协委会: 460 跨国公司: 01/06 mobile_brand:中国联通 海拔: 787 usage_type: ISP/MOB 一排设置(15.08秒)

但是，当使用以下等价SQL进行查询时，其速度非常快。

从ip2location_db24 ip_from <=1908980838命令中选择* ip_from限制限制1 \G *行* id: 5475739 ip_from: 1908932608 ip_to: 1909063679 country_code: CN country_name:中国 region_name:山西 city_name:太原 纬度: 37.86944 经度: 112.56028 zip_code：- time_zone：+08:00 isp:中国联通陕西省网络 域: CHINAUNICOM.COM net_speed: DSL idd_code: 86 area_code: 0351 weather_station_code: CHXX0129 weather_station_name:太原 管理协委会: 460 跨国公司: 01/06 mobile_brand:中国联通 海拔: 787 usage_type: ISP/MOB 一排设定(0.00秒)

问题是，当我们签出执行计划时，两个查询对ip_from列的索引使用相同的范围扫描。但这两个SQL的性能远非如此。有谁知道原因吗？

为了提供更多信息，我们还测试了其输出列被索引完全覆盖的查询。

MariaDB ip2location从ip2location_db24中选择ip_from、ip_to，其中ip_to >=1908980838和ip_from <=1908980838限制为1； + ip_from \x{e76f} ip_to \x{e76f} + 1908932608 x 1909063679 x + 一排设置(0.01秒)

注意，上面的查询SQL速度非常快。但是，当查询没有索引覆盖的任何额外列时，需要难以置信的长时间：

MariaDB ip2location从ip2location_db24中选择ip_from、ip_to、country_code，其中ip_to >=1908980838和ip_from <=1908980838限制为1； + ip_from，ip_to，country_code，country_code + \x{e76f} 1908932608 \ 1909063679 CN = + 一排设置(10.15秒)

Answer 1

select * from ip2location_db24 where ip_to >=1908980838 and ip_from <=1908980838 limit 1; 

比较慢，因为比较两列。

select * from ip2location_db24 where ip_from <=1908980838 order by ip_from desc limit 1 

因为只比较了一个索引列，所以速度很快。

Answer 2

尝试force index，如MariaDB手册https://mariadb.com/kb/en/mariadb/documentation/optimization-and-tuning/query-optimizations/how-to-force-query-plans/#dont-use-a-particular-index部分所述

select ip_from,ip_to,country_code
from ip2location_db24 force index (idx_ip_from_to_2)
where ip_to >=1908980838 and ip_from <=1908980838
limit 1;