欢迎光临BDM
一枚菜鸟码农的成仙之路

工作笔记:加速 1000 倍!记一次新手 SQL 调优的心路历程

需求

按正方形的经纬度范围求出 hotel 集合。

数据

同事发来的原始数据 t_hotel 总记录 23 万条。

查询演进

直接查询

首先模拟场景,直接上 SQL 查询:

SET @maxX = 31;
SET @minX = 30;
SET @maxY = 121;
SET @minY = 120;

SELECT
    * 
FROM
    `t_hotel` 
WHERE
    lat > @minX AND lat < @maxX 
    AND lon > @minY AND lon < @maxY;

果然,太慢了,平均耗时 23 秒。

加上联合索引

以 lat lon 建立联合索引,查询耗时平均 23 秒,没有区别!!

是不是因为范围查询的关系导致聚合索引失效 ? 虽然与 B+ 的原理不符合,但仍然改为单独索引试试!

改为单独索引

以 lat 和 lon 分别建立索引,还是平均 23 秒,没有区别!!

改SQL

网上看到 BETWEEN 的用法,MySQL 在 > 和 < 的处理上是不是和 BETWEEN 有区别?遂改成 BETWEEN 试试:

SET @maxX = 31;
SET @minX = 30;
SET @maxY = 121;
SET @minY = 120;

SELECT
    * 
FROM
    `t_hotel` 
WHERE
    lat BETWEEN @minX AND @maxX 
    AND lon BETWEEN @minY AND @maxY;

23秒 依然没有区别!!

查看语句解释

最后只能通过 EXPLAIN 工具来解决了,在 SQL 前加上 EXPLAIN,结果如下:

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t_hotel ALL lon,lat 261922 1.23 Using where

lon 和 lat 在 possible_keys 的索引列里,但是没有出现在 key 列里!为何索引无法命中呢?会不会和字段的性质有关,遂查看 DDL。

查看DDL

CREATE TABLE `t_hotel`  (
  `hotel_id` int(11) NOT NULL COMMENT '酒店的唯一标识符',
  `hotel_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '酒店名称',
  `lat` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '纬度',
  `lon` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '经度',
  INDEX `lon`(`lon`) USING BTREE,
  INDEX `lat`(`lat`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

原来字段 lat 和 lon 都是 varchar 格式的,可能和 varchar 有关,遂改为浮点型试试。

那么长度改成多少呢?查看了一下现有的数据,长度层次不齐,那就查一下最长的吧:

SELECT length(lat) l, lat from t_hotel order by l desc limit 1

结果:

l lat
16 22.2165560719879

小数点后 13 位,遂改为 float,改完后 DDL 如下:

CREATE TABLE `t_hotel`  (
  `hotel_id` int(11) NOT NULL COMMENT '酒店的唯一标识符',
  `hotel_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '酒店名称',
  `lat` float(17, 14) NOT NULL COMMENT '纬度',
  `lon` float(17, 14) NOT NULL COMMENT '经度',
  INDEX `lon`(`lon`) USING BTREE,
  INDEX `lat`(`lat`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

再次测试:5.893 秒!快了 4 倍!

重新添加上联合索引,再次查看语句解释

重新添加回 lat 和 lon 的联合索引,依然耗时近 6 秒才能查出,再次查看 EXPLAIN:

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t_hotel range lat,lon,lat_lon lon 4 28962 18.37 Using index condition; Using where

extra 里出现了 Using index condition,但 key 显示只命中了 lon 的索引,说明 lat 字段依然是 Using where 查出的。

应该是索引的顺序影响了命中,索性将 lat 和 lon 单独的索引删除,再次测试及 EXPLAIN:

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t_hotel range lat_lon lat_lon 8 45544 11.11 Using index condition

只需要 1.3 秒便可查出结果,比最初快了十倍!

EXPLAIN 的 extra 显示只有 Using index condition,完美命中索引!

覆盖索引

还可以更快嘛?记得有索引覆盖这一回事,遂将查询语句改成:

SET @maxX = 31;
SET @minX = 30;
SET @maxY = 121;
SET @minY = 120;

SELECT
    hotel_id,
    lat,
    lon 
FROM
    `t_hotel` 
WHERE
    lat BETWEEN @minX AND @maxX 
    AND lon BETWEEN @minY AND @maxY;

EXPLAIN:

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t_hotel range lat_lon lat_lon 8 45544 11.11 Using where; Using index

Using where; Using index!!!查询只耗时:0.032 秒!!比最初快 1000 倍!!简直感动了自己。

需要注意的是:虽然主键没有显式地在联合索引中声明,但默认会加在联合索引中,构成覆盖索引的效果。

填坑:varchar 的索引有何问题?

回到最开始,当 lat 和 lon 的字段类型为 varchar 时,有什么问题?

查询文档得知:在字段类型为 varchar 时,如果使用其他类型的条件,会造成隐式转换,使索引失效

破除方法:在条件上加上单引号 ‘ ‘ 包起来

将字段修改回 varchar,查询范围改为 * ,修改后的 SQL:

SET @maxX = '31';
SET @minX = '30';
SET @maxY = '121';
SET @minY = '120';

SELECT
    *
FROM
    `t_hotel` 
WHERE
    lat BETWEEN @minX AND @maxX 
    AND lon BETWEEN @minY AND @maxY;

查询时间 1.387秒!和 float 一样快!看来果然是隐式转换带来的索引失效的问题。

让我们继续对比一下 EXPLAIN:

加单引号 ” 之前

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t_hotel ALL lat_lon 237519 1.23 Using where

非常弟弟的 Using where

加单引号 ” 之后

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t_hotel range lat_lon lat_lon 164 46858 11.11 Using index condition

非常优秀的 Using index condition

目前仍然有一个疑惑。在查询范围不是 *,而变成覆盖索引的范围的情况下,无论条件加不加单引号 ”,MySQL 都会优化成下面这种覆盖索引的方式:

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t_hotel range lat_lon lat_lon 164 46858 11.11 Using where; Using index

其他思路:MySQL Geometry

可以利用 MySQL 的 Geometry,拥有非常高效而丰富的 API 进行空间的计算。

具体方法:新增一列空间坐标 Geometry 进行相关查询。

MyBatis 中只在 XML 书写 SQL,而不映射到实体类即可解决和 Java 兼容的问题。

本文遵守知识共享署名-相同方式共享 4.0 国际许可协议,未经允许不得转载暂时没有标题 » 工作笔记:加速 1000 倍!记一次新手 SQL 调优的心路历程

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

联系我们GitHub