尧图网页设计 
MySQL UDF这个坑最近我又踩了一遍而且是在测绘数据落库这种不太容易被人注意的场景里。当时有一批GPS点位要和某个固定坐标做距离筛选线上库是MySQL 5.7.44数据量上了十万行之后SQL里写经度纬度差的公式怎么都别扭后来干脆自己写了一个UDF用户自定义函数编译成.so塞进MySQL一条SELECT就搞定性能还比原来快了将近一个量级。这篇就把完整的开发过程、编译部署、SQL注册和几个让我折腾到半夜的坑全部记录下来给以后写UDF的人留一份能直接参考的作业。UDF这个东西简单说就是MySQL留出来的一个扩展口子允许你用C或者C写一个函数编译成动态库然后像用内置函数sum()、now()一样在SQL里直接调用。它和存储过程的区别在于UDF是直接跑在mysqld进程里的原生代码被调用时不需要像存储过程那样在SQL引擎层反复解析、做安全上下文切换所以在做行级计算、表达式嵌入这类场景时性能和灵活性都远高于存储过程。适合看的读者是有一定C语言基础、正在被MySQL内置函数边界卡住的DBA、后端开发或者大数据工程同学。1. 项目背景一个不得不写UDF的测绘需求1.1 需求来源与问题定位需求本身不复杂。我们有一张POI表结构大概就是id、name、latitude、longitude这四列总行数在五十万左右。业务方要求是给定一个中心点把周围十公里以内的POI全部捞出来并且按距离排序。这类需求在LBS类业务里非常常见说白了就是“附近的人”“门店配送范围”那一套逻辑。按理说这是个标准的地理围栏查询不值得我去写UDF。问题出在当时这个项目运行在MySQL 5.7上而5.7里内置的地理空间函数只有一堆空间关系判断并没有一个开箱即用的“计算两点球面距离并返回公里数”的函数。虽然8.0里有ST_Distance_Sphere()但线上库的版本不是说升就能升的。何况就算有内置函数我们的业务后来还加了“高速优先”“直线距离折减系数”这类加权逻辑内置函数也满足不了。于是摆在面前的方案其实就四个第一在应用层用Java/PHP算好距离再做筛选但几十万行数据全量拉回应用层内存和网络开销都受不了第二在SQL里写一大串三角函数公式每次查询都去解析那坨复杂表达式维护成本极高第三用存储过程但它同样要经过SQL引擎的解析执行而且很难在SELECT表达式中灵活嵌入第四就是写一个UDF把距离算法沉淀成原生代码直接挂在MySQL里。我当时几乎是没有任何犹豫就选了第四条路。1.2 为什么不用存储过程、临时表或内置函数这里需要把“为什么是UDF”这个决策讲透因为很多人第一反应是“存储过程不是也能算距离吗”。先说存储过程的问题它本质上是一段SQL脚本的集合每次调用都要经过语法解析、优化、权限检查这一整套流程你在里面做循环、做计算性能完全没法和编译好的原生代码比。更关键的是存储过程不好放进SELECT的字段列表里比如你想把算出来的距离作为结果集的一个列返回用存储过程去做这件事就非常绕。临时表方案也试过先把全景数据导入临时表再在临时表上用SQL写Haversine公式计算距离。看起来可行但问题在于一是五十万行数据的临时表创建、索引维护本身就耗时二是那段SQL公式极其冗长嵌套五六层三角函数一旦字段名写错或者参数类型不匹配排查起来非常痛苦三是每次计算都要把cos、sin重复算一遍重复分配临时结果集性能瓶颈非常明显。至于内置函数当时5.7确实没有合适的。MySQL的GIS扩展有很多空间对象函数但那些是拿来做包含、相交、重叠判断的要么返回二值结果要么返回的空间对象枚举并不是直接给你一个公里数。而8.0虽然有了ST_Distance_Sphere可我们的技术栈和运维体系还锁在5.7上为了一个距离计算去升级数据库大版本风险收益完全不成比例。所以最终选择很清晰在这种“需要在表达式层面做频繁数学计算、且内置函数覆盖不到”的空白地带UDF就是最合理的补充手段。2. UDF开发的底层协议与准备工作2.1 一个UDF必须实现哪三个函数写UDF之前先把它底层的调用约定摸清楚不然写出来的.so就算编译成功加载进MySQL也大概率崩。任何一个普通的非聚合UDF在代码里最少要实现三个函数名字要跟你要注册的SQL函数名对应上。假设我们要注册的SQL函数叫distance_km那么C代码里就必须有my_bool distance_km_init(UDF_INIT *initid, UDF_ARGS *args, char *message)double distance_km(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error)void distance_km_deinit(UDF_INIT *initid)init函数是初始化钩子。MySQL在第一次执行SQL时会对每一条要调UDF的表达式调用一次init你可以在这里检查参数个数、参数类型也可以给initid分配一些跨多次调用复用的一内存比如预计算好的常量、缓存表指针这些内存要在对应的deinit里释放。如果init返回1说明初始化失败MySQL会抛弃这次执行并且把message里写的内容作为报错抛给客户端。主函数才是真正干活的。因为我们要返回的是一个浮点数所以主函数返回类型写成double。注意这里有个很容易被忽视的细节is_null和error是两个指针当你发现参数有NULL、或者计算过程中遇到异常不要直接返回一个魔法值更应该做的是把*is_null1或者*error1置位然后返回一个占位值否则MySQL会把这个返回值当成正常结果造成数据脏了还不报错。deinit函数对应释放init里分配的资源。很多基础教程根本不写deinit如果你的init函数里没申请任何内存那确实可以省略但MySQL文档明确要求UDF应该提供这个函数我个人的习惯是哪怕空实现也要写上一是规范二是将来扩展时防止遗忘。2.2 参数结构UDF_ARGS到底长什么样UDF_ARGS这个结构体是所有UDF参数处理的核心一定要吃透。它在MySQL头文件里大概是这样的有unsigned int arg_count表示参数个数有enum Item_result *arg_type数组表示每个参数的类型有char **args数组存参数的值还有unsigned long *lengths数组存每个参数的长度。这里有个坑我必须单独拎出来讲args这个字段声明的类型是char **看起来像字符串数组但实际上它里面装的是什么完全由对应的arg_type决定。如果arg_type[i]是STRING_RESULT那args[i]确实是一个以\0结尾的字符串指针但如果arg_type[i]是REAL_RESULT那args[i]其实是指向一个double类型内存的指针你要把它强转成double *再取值如果是INT_RESULT则要转成long long *。还有一个更容易踩的坑MySQL里SQL字面量写39.9042这种小数实际传给UDF的参数类型很可能不是REAL_RESULT而是DECIMAL_RESULT或STRING_RESULT。因为MySQL的表达式优化器对无后缀的小数字面量默认会建一个DECIMALItem而DECIMAL参数在UDF里又是以字符串形式传给args[i]的。所以如果一个UDF在init阶段强硬地要求所有参数必须是REAL_RESULT你拿到的可能全是初始化失败。最稳妥的做法是在主函数里根据arg_type分别转double实数类型就强转指针字符串或DECIMAL类型就走atof。后面我会在源码里给你看这个helper怎么写。2.3 编译环境搭建mysql_config和gcc环境我使用的是CentOS 7 x86_64MySQL版本5.7.44社区版编译器是系统自带的gcc的c99模式。写UDF不需要你去下载整个MySQL源码包但你必须安装对应的开发包目的是拿到两个东西MySQL的头文件以及mysql_config这个工具。头文件路径一般是/usr/include/mysql/里面至少有mysql.h、my_global.h、my_sys.h这几个文件。实际上我们可以用mysql_config --cflags把include路径打出来用mysql_config --plugindir拿到当前实例的插件目录非常方便。如果你的系统里没有mysql_config多半是缺mysql-community-devel这个包用yum装一下即可Debian系则搜索libmysqlclient-dev。有一点要提醒开发包的版本最好和线上mysqld版本一致跨大版本编译的UDF经常出现符号不兼容我在后面故障章节会专门讲。编译本身也不需要链接libmysqlclient因为UDF是运行在mysqld进程内部的需要的MySQL内部符号在加载时由mysqld自己导出。所以最精简的编译命令就是gcc -shared -fPIC -O2 -o distance_km.so distance_km.c -I/usr/include/mysql -lm但还是建议在Makefile里顺手调一下mysql_config --cflags万一以后开发包路径变了不会编译得一头雾水。3. 核心开发实现经纬度距离计算函数3.1 Haversine公式与算法选择说回这个距离函数本身。计算两个经纬度坐标之间的球面距离业界最常用的就是Haversine公式。它的核心思想是假设地球是一个球体然后根据两点的经纬度差求对应球面两点间的“大圆距离”。数学公式长这样a sin²(Δlat/2) cos(lat1)·cos(lat2)·sin²(Δlng/2) c 2·atan2(√a, √(1−a)) d R·c其中R是地球平均半径我用6371公里这个值。这个公式对千米级别的距离精度已经足够误差通常控制在0.5%以内而如果业务距离本身就只有十公里范围那这点误差完全可以接受。为什么不直接选更复杂的Vincenty公式因为那套东西迭代计算量大每行数据都要跑好几轮循环在UDF这种被频繁调用的场景里不划算。而那些更精确的椭球模型算法在这个业务里属于杀鸡用牛刀。选型原则很简单精度够用计算量最小代码可读性高。3.2 完整源码与逐段解析下面给出我实际用的完整源码注释尽量详细#include my_global.h #include my_sys.h #include mysql.h #include string.h #include math.h static double get_double_arg(UDF_ARGS *args, int i) { if (args-args[i] NULL) return 0.0; switch (args-arg_type[i]) { case REAL_RESULT: return *(double *)args-args[i]; case INT_RESULT: return (double)(*(long long *)args-args[i]); case STRING_RESULT: case DECIMAL_RESULT: return atof(args-args[i]); default: return 0.0; } } my_bool distance_km_init(UDF_INIT *initid, UDF_ARGS *args, char *message) { if (args-arg_count ! 4) { strcpy(message, distance_km() requires exactly 4 args: lat1,lng1,lat2,lng2); return 1; } for (int i 0; i 4; i) { if (args-arg_type[i] ! REAL_RESULT args-arg_type[i] ! DECIMAL_RESULT args-arg_type[i] ! STRING_RESULT) { strcpy(message, distance_km() args must be numeric or string-numeric); return 1; } } initid-max_length 20; initid-maybe_null 1; return 0; } void distance_km_deinit(UDF_INIT *initid) { /* no extra memory allocated, nothing to do */ } double distance_km(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error) { double lat1 get_double_arg(args, 0); double lng1 get_double_arg(args, 1); double lat2 get_double_arg(args, 2); double lng2 get_double_arg(args, 3); if (lat1 0.0 lng1 0.0) { *is_null 1; return 0.0; } if (lat2 0.0 lng2 0.0) { *is_null 1; return 0.0; } double dlat (lat2 - lat1) * M_PI / 180.0; double dlng (lng2 - lng1) * M_PI / 180.0; double rlat1 lat1 * M_PI / 180.0; double rlat2 lat2 * M_PI / 180.0; double a sin(dlat / 2.0) * sin(dlat / 2.0) cos(rlat1) * cos(rlat2) * sin(dlng / 2.0) * sin(dlng / 2.0); double c 2.0 * atan2(sqrt(a), sqrt(1.0 - a)); return 6371.0 * c; }这个函数的主逻辑其实很短真正的重点在get_double_arg这个helper里。我见过很多网上教程主函数一上来就写double lat1 *(double*)args-args[0]看起来简洁但实际生产环境里根本扛不住。比如客户端传过来的是39.9042这个带引号的字符串或者MySQL内部按DECIMAL存参数args-args[0]对应的就是字符串内存直接强转double指针去取值轻则拿到一个无意义数字重则触发越界读。所以按类型分流处理是写UDF的基本素养。3.3 边界条件和内存安全处理写UDF最大的风险是它运行在mysqld进程内任何一次非法内存操作都可能直接让整个数据库实例崩溃。这点和写普通后台服务完全不一样普通服务崩了进程重启拉倒mysqld崩了那是在生产事故。我处理边界条件的方法是先判空值。入口处如果传入坐标是NULL或者经纬度全是0直接置*is_null1让MySQL内部把它转成NULL字段。这里特别提一下为什么经纬度全是0也当空处理实际轨迹数据里很多设备定位失败时上报的就是(0, 0)这个坐标落在几内亚湾海域全是0的记录几乎可以断定是脏数据。内存安全方面主函数里没有动态申请内存只在栈上做计算所以目前没有释放压力。但如果你的UDF要在init里用malloc申请缓存然后通过initid-ptr保存指针一定不要忘了在deinit里free否则就是一次隐性的内存泄漏。另外所有对args-args[i]的访问都要先确认它不是NULL空值参数在UDF里就表现为NULL指针直接传给atof或强转会有不可预测的后果。4. 编译、部署与SQL侧验证4.1 Makefile与编译细节编译这一步我用一个非常简单的MakefileMYSQL_CFLAGS : $(shell mysql_config --cflags) PLUGIN_DIR : $(shell mysql_config --plugindir) all: gcc -shared -fPIC -O2 -o distance_km.so distance_km.c $(MYSQL_CFLAGS) -lm deploy: all cp distance_km.so $(PLUGIN_DIR)/ clean: rm -f distance_km.so这里有几个值得说的编译细节。-fPIC是必须的因为.so要求位置无关代码-shared表示生成共享库-O2以上建议打开UDF会被高频调用这点优化很有价值。你还必须用mysql_config --cflags而不是瞎写-I/usr/include/mysql因为不同发行版本的头文件路径差异很大甚至5.7和8.0的头文件组织都不一样。另一个是我自己踩过的坑如果你习惯用g而不是gcc编译C代码一定要在所有函数外面包一层extern C{}否则C编译器会对函数名做name mangling生成出来的符号变成_Z13distance_kmP8st_udf_initP9st_udf_argsPcS2_这种鬼样子MySQL加载时按distance_km这个名字去找符号怎么都找不到。我最早一遍就是直接用g编注册函数时报Symbol not found排查了半天才意识到是符号表被改写了。部署完成后记得验证一下.so文件属主和权限。mysqld进程通常以mysql用户运行如果.so文件root权限过强或者所在目录不可读加载就会失败这个放到下一章的故障表里细说。4.2 部署.so文件和系统权限检查编译生成了distance_km.so部署其实就两步把它拷到plugin_dir然后在MySQL里注册。先查plugin_dirSHOW VARIABLES LIKE plugin_dir;我这边输出是/usr/lib64/mysql/plugin。拷贝时建议用cp拷贝完还要检查一下mysqld进程能不能读cp distance_km.so /usr/lib64/mysql/plugin/ chmod 755 /usr/lib64/mysql/plugin/distance_km.so chown mysql:mysql /usr/lib64/mysql/plugin/distance_km.so这里特别说一句SELinux的坑。CentOS默认开启SELinux如果你把所有东西都拷进去了却在MySQL里一直报权限拒绝那多半不是Unix权限问题而是SELinux安全上下文不对。验证方法是执行ls -Z /usr/lib64/mysql/plugin/distance_km.so看它是不是lib_t或类似MySQL插件应有的上下文标签如果不是用restorecon恢复一下restorecon -v /usr/lib64/mysql/plugin/distance_km.so这一条局部的SELinux命令能救回不少人的命我身边至少有两个人卡在这里超过半天。4.3 CREATE FUNCTION注册与业务验证注册UDF的SQL非常简单但语法细节还是容易错CREATE FUNCTION distance_km RETURNS REAL SONAME distance_km.so;注意RETURNS REAL是声明返回类型REAL对应C代码里的double如果把函数的返回类型写成了INTEGERMySQL会调用一个返回long long的主函数签名你提供的是返回double的函数行为会完全不可预测。所以代码和声明必须一一对应。注册成功后立刻做一次冒烟测试SELECT distance_km(39.9042, 116.4074, 31.2304, 121.4737) AS dist_km;我这边算出来是1068.79左右这个结果用在线经纬度距离工具核对过误差在合理范围内。然后就可以放到业务SQL里了SELECT id, name, distance_km(latitude, longitude, 39.9042, 116.4074) AS dist FROM poi WHERE latitude BETWEEN 39.5 AND 40.3 AND longitude BETWEEN 116.0 AND 116.8 HAVING dist 10 ORDER BY dist;这里一个很重要的性能技巧UDF里计算的是精确距离但你千万不要对全表五十万行直接就跑WHERE distance_km(...) 10那等于把每条记录都算一遍。标准做法是先利用经纬度的线性范围也就是所谓的bounding box把这个粗略筛选作为索引友好的过滤条件让MySQL用上经纬度字段上的索引先把集合缩小到几百几千行再对剩下的行用UDF算精确距离最后用HAVING过滤。我在源码注释里就强调了这一点实践证明这条优化能让整个查询从秒级降到毫秒级。5. 常见故障、性能与使用边界5.1 我在生产环境遇到过的几个坑UDF开发最容易让人灰心的阶段就是编译和加载因为报错信息又短又不直观。我把遇到过的坑整理成一张速查表方便你对照排查现象大概率原因解决办法CREATE FUNCTION报Cant open shared libraryerrno 13权限不足或SELinux拦截检查.so属主和chmod 755用restorecon重置SELinux标签CREATE FUNCTION报Symbol not found用g编译导致C弱符号或函数名拼写不一致改用gcc编译或用extern C包裹核对SQL函数名与C函数名完全一致加载成功但一调用就报Illegal mix of collationsUDF声明返回STRING但实际返回二进制数据检查RETURNS类型字符串UDF一般加CHAR/NVARCHAR声明调用后结果异常大对DECIMAL/STRING类型的参数直接强转double指针使用文中get_double_arg的分类型转换逻辑升级MySQL大版本后UDF失效5.7编译的.so放到8.0环境内部符号或ABI变化在目标版本环境上重新编译、重新部署结果偶尔是NULL经纬度为(0,0)或NULL参数未处理init里设置maybe_null1主函数置*is_null1这里有个经验MySQL的报错信息很多时候不会给出具体原因比如权限失败它不会说“SELinux拒绝了”你能做的就是按照“是不是没拷对目录→是不是属主不对→是不是x权限缺失→是不是SELinux上下文不对”的顺序逐层排查。如果你在云服务器上开了安全软件还要看一眼有没有被库文件扫描功能误删。5.2 线程安全与并发下的注意事项UDF在MySQL里是并发执行的同一个UDF在同一时刻可能被多个线程、多个连接调用。这里有一个很多新手都搞错的概念UDF_INIT *initid并不是全局共享的MySQL会对每一个连接中的每一次表达式评估单独调用一次init所以initid指向的内存天然是线程隔离的你不用加锁去保护它。但如果你是c里定义了一个全局静态变量想在函数调用间共享缓存或者做计数器那就麻烦了。比如static double last_lat;这种多个线程同时读写会存在数据竞争轻则算错重则Crash。所以我的原则是UDF内部完全不使用可写的全局状态所有可变量都放在栈上需要缓存就挂到initid-ptr上它是每连接私有的安全且高效。内存释放顺序也要注意。如果你在init里用malloc给initid-ptr分配了一块缓存而deinit里又没有释放每次连接新建都会泄漏一块。注意不是每行调用都泄漏是每个连接周期泄漏一次短连接业务大量建立连接时会持续涨内存。这个很难靠监控发现但长时间运行后会拖垮mysqld。5.3 什么时候不该写UDF写UDF感觉很酷但它不是万能的我也见过不少被滥用UDF翻车的案例这里必须泼一点冷水。第一种不该写的情况是能用内置函数解决就别写。比如MySQL 8.0已经有ST_Distance_Sphere如果你的库可以升到8.0那就不需要再来一个自定义UDF。第二种不该写的场景函数体里做了非常重的IO操作比如在UDF里连接外部服务或者读文件这是绝对禁忌。因为UDF跑在mysqld进程里任何慢IO都会直接卡住数据库线程一次意外阻塞可能拖垮整个实例。第三种如果你只是想在应用层算一个近似的距离很多地理库已经足够没必要把这种逻辑下沉到数据库层要知道UDF的维护成本比普通代码高因为它跟数据库版本、编译器、系统环境全部耦合。还有一个容易被忽略的点尽量不要用UDF来封装加密算法或者复杂的二进制协议解析这类东西要么有现成的函数要么应该放在应用层。MySQL的UDF接口本身是可返回二进制字符串的但你要自己处理length参数和空字符问题远比应用层处理复杂而且一旦写错就是安全漏洞。写在最后这次写UDF最大的收获不是学会了一个Haversine公式而是对整个MySQL扩展机制有了更直观的理解。很多人在数据库这条路上习惯“能用就行”遇到内置函数覆盖不到的需求就上临时表、上存储过程、甚至上外挂中间件其实回头看一眼MySQL的UDF协议它可能才是你这个场景里最精准、最省事的解法。我真正的体会是UDF的价值不仅仅在于性能更在于它把计算逻辑沉淀成数据库原生能力让上层SQL变得简洁可控。最后再分享一个小技巧如果你以后要写多个UDF建议每一个UDF都单独编译成独立的.so不要全部塞进一个库。MySQL加载.so的时候是一次性把所有符号都加载进来的混合在一起虽然少几个文件但一旦某一个函数初始化崩溃整个库和里面驻留的所有UDF都会受影响隔离才是生产环境的第一原则。我是从踩坑里学到这些的希望你读完能少踩几个。 MySQL UDF实战：用C/C++扩展实现高性能经纬度距离计算
MySQL UDF这个坑最近我又踩了一遍而且是在测绘数据落库这种不太容易被人注意的场景里。当时有一批GPS点位要和某个固定坐标做距离筛选线上库是MySQL 5.7.44数据量上了十万行之后SQL里写经度纬度差的公式怎么都别扭后来干脆自己写了一个UDF用户自定义函数编译成.so塞进MySQL一条SELECT就搞定性能还比原来快了将近一个量级。这篇就把完整的开发过程、编译部署、SQL注册和几个让我折腾到半夜的坑全部记录下来给以后写UDF的人留一份能直接参考的作业。UDF这个东西简单说就是MySQL留出来的一个扩展口子允许你用C或者C写一个函数编译成动态库然后像用内置函数sum()、now()一样在SQL里直接调用。它和存储过程的区别在于UDF是直接跑在mysqld进程里的原生代码被调用时不需要像存储过程那样在SQL引擎层反复解析、做安全上下文切换所以在做行级计算、表达式嵌入这类场景时性能和灵活性都远高于存储过程。适合看的读者是有一定C语言基础、正在被MySQL内置函数边界卡住的DBA、后端开发或者大数据工程同学。1. 项目背景一个不得不写UDF的测绘需求1.1 需求来源与问题定位需求本身不复杂。我们有一张POI表结构大概就是id、name、latitude、longitude这四列总行数在五十万左右。业务方要求是给定一个中心点把周围十公里以内的POI全部捞出来并且按距离排序。这类需求在LBS类业务里非常常见说白了就是“附近的人”“门店配送范围”那一套逻辑。按理说这是个标准的地理围栏查询不值得我去写UDF。问题出在当时这个项目运行在MySQL 5.7上而5.7里内置的地理空间函数只有一堆空间关系判断并没有一个开箱即用的“计算两点球面距离并返回公里数”的函数。虽然8.0里有ST_Distance_Sphere()但线上库的版本不是说升就能升的。何况就算有内置函数我们的业务后来还加了“高速优先”“直线距离折减系数”这类加权逻辑内置函数也满足不了。于是摆在面前的方案其实就四个第一在应用层用Java/PHP算好距离再做筛选但几十万行数据全量拉回应用层内存和网络开销都受不了第二在SQL里写一大串三角函数公式每次查询都去解析那坨复杂表达式维护成本极高第三用存储过程但它同样要经过SQL引擎的解析执行而且很难在SELECT表达式中灵活嵌入第四就是写一个UDF把距离算法沉淀成原生代码直接挂在MySQL里。我当时几乎是没有任何犹豫就选了第四条路。1.2 为什么不用存储过程、临时表或内置函数这里需要把“为什么是UDF”这个决策讲透因为很多人第一反应是“存储过程不是也能算距离吗”。先说存储过程的问题它本质上是一段SQL脚本的集合每次调用都要经过语法解析、优化、权限检查这一整套流程你在里面做循环、做计算性能完全没法和编译好的原生代码比。更关键的是存储过程不好放进SELECT的字段列表里比如你想把算出来的距离作为结果集的一个列返回用存储过程去做这件事就非常绕。临时表方案也试过先把全景数据导入临时表再在临时表上用SQL写Haversine公式计算距离。看起来可行但问题在于一是五十万行数据的临时表创建、索引维护本身就耗时二是那段SQL公式极其冗长嵌套五六层三角函数一旦字段名写错或者参数类型不匹配排查起来非常痛苦三是每次计算都要把cos、sin重复算一遍重复分配临时结果集性能瓶颈非常明显。至于内置函数当时5.7确实没有合适的。MySQL的GIS扩展有很多空间对象函数但那些是拿来做包含、相交、重叠判断的要么返回二值结果要么返回的空间对象枚举并不是直接给你一个公里数。而8.0虽然有了ST_Distance_Sphere可我们的技术栈和运维体系还锁在5.7上为了一个距离计算去升级数据库大版本风险收益完全不成比例。所以最终选择很清晰在这种“需要在表达式层面做频繁数学计算、且内置函数覆盖不到”的空白地带UDF就是最合理的补充手段。2. UDF开发的底层协议与准备工作2.1 一个UDF必须实现哪三个函数写UDF之前先把它底层的调用约定摸清楚不然写出来的.so就算编译成功加载进MySQL也大概率崩。任何一个普通的非聚合UDF在代码里最少要实现三个函数名字要跟你要注册的SQL函数名对应上。假设我们要注册的SQL函数叫distance_km那么C代码里就必须有my_bool distance_km_init(UDF_INIT *initid, UDF_ARGS *args, char *message)double distance_km(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error)void distance_km_deinit(UDF_INIT *initid)init函数是初始化钩子。MySQL在第一次执行SQL时会对每一条要调UDF的表达式调用一次init你可以在这里检查参数个数、参数类型也可以给initid分配一些跨多次调用复用的一内存比如预计算好的常量、缓存表指针这些内存要在对应的deinit里释放。如果init返回1说明初始化失败MySQL会抛弃这次执行并且把message里写的内容作为报错抛给客户端。主函数才是真正干活的。因为我们要返回的是一个浮点数所以主函数返回类型写成double。注意这里有个很容易被忽视的细节is_null和error是两个指针当你发现参数有NULL、或者计算过程中遇到异常不要直接返回一个魔法值更应该做的是把*is_null1或者*error1置位然后返回一个占位值否则MySQL会把这个返回值当成正常结果造成数据脏了还不报错。deinit函数对应释放init里分配的资源。很多基础教程根本不写deinit如果你的init函数里没申请任何内存那确实可以省略但MySQL文档明确要求UDF应该提供这个函数我个人的习惯是哪怕空实现也要写上一是规范二是将来扩展时防止遗忘。2.2 参数结构UDF_ARGS到底长什么样UDF_ARGS这个结构体是所有UDF参数处理的核心一定要吃透。它在MySQL头文件里大概是这样的有unsigned int arg_count表示参数个数有enum Item_result *arg_type数组表示每个参数的类型有char **args数组存参数的值还有unsigned long *lengths数组存每个参数的长度。这里有个坑我必须单独拎出来讲args这个字段声明的类型是char **看起来像字符串数组但实际上它里面装的是什么完全由对应的arg_type决定。如果arg_type[i]是STRING_RESULT那args[i]确实是一个以\0结尾的字符串指针但如果arg_type[i]是REAL_RESULT那args[i]其实是指向一个double类型内存的指针你要把它强转成double *再取值如果是INT_RESULT则要转成long long *。还有一个更容易踩的坑MySQL里SQL字面量写39.9042这种小数实际传给UDF的参数类型很可能不是REAL_RESULT而是DECIMAL_RESULT或STRING_RESULT。因为MySQL的表达式优化器对无后缀的小数字面量默认会建一个DECIMALItem而DECIMAL参数在UDF里又是以字符串形式传给args[i]的。所以如果一个UDF在init阶段强硬地要求所有参数必须是REAL_RESULT你拿到的可能全是初始化失败。最稳妥的做法是在主函数里根据arg_type分别转double实数类型就强转指针字符串或DECIMAL类型就走atof。后面我会在源码里给你看这个helper怎么写。2.3 编译环境搭建mysql_config和gcc环境我使用的是CentOS 7 x86_64MySQL版本5.7.44社区版编译器是系统自带的gcc的c99模式。写UDF不需要你去下载整个MySQL源码包但你必须安装对应的开发包目的是拿到两个东西MySQL的头文件以及mysql_config这个工具。头文件路径一般是/usr/include/mysql/里面至少有mysql.h、my_global.h、my_sys.h这几个文件。实际上我们可以用mysql_config --cflags把include路径打出来用mysql_config --plugindir拿到当前实例的插件目录非常方便。如果你的系统里没有mysql_config多半是缺mysql-community-devel这个包用yum装一下即可Debian系则搜索libmysqlclient-dev。有一点要提醒开发包的版本最好和线上mysqld版本一致跨大版本编译的UDF经常出现符号不兼容我在后面故障章节会专门讲。编译本身也不需要链接libmysqlclient因为UDF是运行在mysqld进程内部的需要的MySQL内部符号在加载时由mysqld自己导出。所以最精简的编译命令就是gcc -shared -fPIC -O2 -o distance_km.so distance_km.c -I/usr/include/mysql -lm但还是建议在Makefile里顺手调一下mysql_config --cflags万一以后开发包路径变了不会编译得一头雾水。3. 核心开发实现经纬度距离计算函数3.1 Haversine公式与算法选择说回这个距离函数本身。计算两个经纬度坐标之间的球面距离业界最常用的就是Haversine公式。它的核心思想是假设地球是一个球体然后根据两点的经纬度差求对应球面两点间的“大圆距离”。数学公式长这样a sin²(Δlat/2) cos(lat1)·cos(lat2)·sin²(Δlng/2) c 2·atan2(√a, √(1−a)) d R·c其中R是地球平均半径我用6371公里这个值。这个公式对千米级别的距离精度已经足够误差通常控制在0.5%以内而如果业务距离本身就只有十公里范围那这点误差完全可以接受。为什么不直接选更复杂的Vincenty公式因为那套东西迭代计算量大每行数据都要跑好几轮循环在UDF这种被频繁调用的场景里不划算。而那些更精确的椭球模型算法在这个业务里属于杀鸡用牛刀。选型原则很简单精度够用计算量最小代码可读性高。3.2 完整源码与逐段解析下面给出我实际用的完整源码注释尽量详细#include my_global.h #include my_sys.h #include mysql.h #include string.h #include math.h static double get_double_arg(UDF_ARGS *args, int i) { if (args-args[i] NULL) return 0.0; switch (args-arg_type[i]) { case REAL_RESULT: return *(double *)args-args[i]; case INT_RESULT: return (double)(*(long long *)args-args[i]); case STRING_RESULT: case DECIMAL_RESULT: return atof(args-args[i]); default: return 0.0; } } my_bool distance_km_init(UDF_INIT *initid, UDF_ARGS *args, char *message) { if (args-arg_count ! 4) { strcpy(message, distance_km() requires exactly 4 args: lat1,lng1,lat2,lng2); return 1; } for (int i 0; i 4; i) { if (args-arg_type[i] ! REAL_RESULT args-arg_type[i] ! DECIMAL_RESULT args-arg_type[i] ! STRING_RESULT) { strcpy(message, distance_km() args must be numeric or string-numeric); return 1; } } initid-max_length 20; initid-maybe_null 1; return 0; } void distance_km_deinit(UDF_INIT *initid) { /* no extra memory allocated, nothing to do */ } double distance_km(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error) { double lat1 get_double_arg(args, 0); double lng1 get_double_arg(args, 1); double lat2 get_double_arg(args, 2); double lng2 get_double_arg(args, 3); if (lat1 0.0 lng1 0.0) { *is_null 1; return 0.0; } if (lat2 0.0 lng2 0.0) { *is_null 1; return 0.0; } double dlat (lat2 - lat1) * M_PI / 180.0; double dlng (lng2 - lng1) * M_PI / 180.0; double rlat1 lat1 * M_PI / 180.0; double rlat2 lat2 * M_PI / 180.0; double a sin(dlat / 2.0) * sin(dlat / 2.0) cos(rlat1) * cos(rlat2) * sin(dlng / 2.0) * sin(dlng / 2.0); double c 2.0 * atan2(sqrt(a), sqrt(1.0 - a)); return 6371.0 * c; }这个函数的主逻辑其实很短真正的重点在get_double_arg这个helper里。我见过很多网上教程主函数一上来就写double lat1 *(double*)args-args[0]看起来简洁但实际生产环境里根本扛不住。比如客户端传过来的是39.9042这个带引号的字符串或者MySQL内部按DECIMAL存参数args-args[0]对应的就是字符串内存直接强转double指针去取值轻则拿到一个无意义数字重则触发越界读。所以按类型分流处理是写UDF的基本素养。3.3 边界条件和内存安全处理写UDF最大的风险是它运行在mysqld进程内任何一次非法内存操作都可能直接让整个数据库实例崩溃。这点和写普通后台服务完全不一样普通服务崩了进程重启拉倒mysqld崩了那是在生产事故。我处理边界条件的方法是先判空值。入口处如果传入坐标是NULL或者经纬度全是0直接置*is_null1让MySQL内部把它转成NULL字段。这里特别提一下为什么经纬度全是0也当空处理实际轨迹数据里很多设备定位失败时上报的就是(0, 0)这个坐标落在几内亚湾海域全是0的记录几乎可以断定是脏数据。内存安全方面主函数里没有动态申请内存只在栈上做计算所以目前没有释放压力。但如果你的UDF要在init里用malloc申请缓存然后通过initid-ptr保存指针一定不要忘了在deinit里free否则就是一次隐性的内存泄漏。另外所有对args-args[i]的访问都要先确认它不是NULL空值参数在UDF里就表现为NULL指针直接传给atof或强转会有不可预测的后果。4. 编译、部署与SQL侧验证4.1 Makefile与编译细节编译这一步我用一个非常简单的MakefileMYSQL_CFLAGS : $(shell mysql_config --cflags) PLUGIN_DIR : $(shell mysql_config --plugindir) all: gcc -shared -fPIC -O2 -o distance_km.so distance_km.c $(MYSQL_CFLAGS) -lm deploy: all cp distance_km.so $(PLUGIN_DIR)/ clean: rm -f distance_km.so这里有几个值得说的编译细节。-fPIC是必须的因为.so要求位置无关代码-shared表示生成共享库-O2以上建议打开UDF会被高频调用这点优化很有价值。你还必须用mysql_config --cflags而不是瞎写-I/usr/include/mysql因为不同发行版本的头文件路径差异很大甚至5.7和8.0的头文件组织都不一样。另一个是我自己踩过的坑如果你习惯用g而不是gcc编译C代码一定要在所有函数外面包一层extern C{}否则C编译器会对函数名做name mangling生成出来的符号变成_Z13distance_kmP8st_udf_initP9st_udf_argsPcS2_这种鬼样子MySQL加载时按distance_km这个名字去找符号怎么都找不到。我最早一遍就是直接用g编注册函数时报Symbol not found排查了半天才意识到是符号表被改写了。部署完成后记得验证一下.so文件属主和权限。mysqld进程通常以mysql用户运行如果.so文件root权限过强或者所在目录不可读加载就会失败这个放到下一章的故障表里细说。4.2 部署.so文件和系统权限检查编译生成了distance_km.so部署其实就两步把它拷到plugin_dir然后在MySQL里注册。先查plugin_dirSHOW VARIABLES LIKE plugin_dir;我这边输出是/usr/lib64/mysql/plugin。拷贝时建议用cp拷贝完还要检查一下mysqld进程能不能读cp distance_km.so /usr/lib64/mysql/plugin/ chmod 755 /usr/lib64/mysql/plugin/distance_km.so chown mysql:mysql /usr/lib64/mysql/plugin/distance_km.so这里特别说一句SELinux的坑。CentOS默认开启SELinux如果你把所有东西都拷进去了却在MySQL里一直报权限拒绝那多半不是Unix权限问题而是SELinux安全上下文不对。验证方法是执行ls -Z /usr/lib64/mysql/plugin/distance_km.so看它是不是lib_t或类似MySQL插件应有的上下文标签如果不是用restorecon恢复一下restorecon -v /usr/lib64/mysql/plugin/distance_km.so这一条局部的SELinux命令能救回不少人的命我身边至少有两个人卡在这里超过半天。4.3 CREATE FUNCTION注册与业务验证注册UDF的SQL非常简单但语法细节还是容易错CREATE FUNCTION distance_km RETURNS REAL SONAME distance_km.so;注意RETURNS REAL是声明返回类型REAL对应C代码里的double如果把函数的返回类型写成了INTEGERMySQL会调用一个返回long long的主函数签名你提供的是返回double的函数行为会完全不可预测。所以代码和声明必须一一对应。注册成功后立刻做一次冒烟测试SELECT distance_km(39.9042, 116.4074, 31.2304, 121.4737) AS dist_km;我这边算出来是1068.79左右这个结果用在线经纬度距离工具核对过误差在合理范围内。然后就可以放到业务SQL里了SELECT id, name, distance_km(latitude, longitude, 39.9042, 116.4074) AS dist FROM poi WHERE latitude BETWEEN 39.5 AND 40.3 AND longitude BETWEEN 116.0 AND 116.8 HAVING dist 10 ORDER BY dist;这里一个很重要的性能技巧UDF里计算的是精确距离但你千万不要对全表五十万行直接就跑WHERE distance_km(...) 10那等于把每条记录都算一遍。标准做法是先利用经纬度的线性范围也就是所谓的bounding box把这个粗略筛选作为索引友好的过滤条件让MySQL用上经纬度字段上的索引先把集合缩小到几百几千行再对剩下的行用UDF算精确距离最后用HAVING过滤。我在源码注释里就强调了这一点实践证明这条优化能让整个查询从秒级降到毫秒级。5. 常见故障、性能与使用边界5.1 我在生产环境遇到过的几个坑UDF开发最容易让人灰心的阶段就是编译和加载因为报错信息又短又不直观。我把遇到过的坑整理成一张速查表方便你对照排查现象大概率原因解决办法CREATE FUNCTION报Cant open shared libraryerrno 13权限不足或SELinux拦截检查.so属主和chmod 755用restorecon重置SELinux标签CREATE FUNCTION报Symbol not found用g编译导致C弱符号或函数名拼写不一致改用gcc编译或用extern C包裹核对SQL函数名与C函数名完全一致加载成功但一调用就报Illegal mix of collationsUDF声明返回STRING但实际返回二进制数据检查RETURNS类型字符串UDF一般加CHAR/NVARCHAR声明调用后结果异常大对DECIMAL/STRING类型的参数直接强转double指针使用文中get_double_arg的分类型转换逻辑升级MySQL大版本后UDF失效5.7编译的.so放到8.0环境内部符号或ABI变化在目标版本环境上重新编译、重新部署结果偶尔是NULL经纬度为(0,0)或NULL参数未处理init里设置maybe_null1主函数置*is_null1这里有个经验MySQL的报错信息很多时候不会给出具体原因比如权限失败它不会说“SELinux拒绝了”你能做的就是按照“是不是没拷对目录→是不是属主不对→是不是x权限缺失→是不是SELinux上下文不对”的顺序逐层排查。如果你在云服务器上开了安全软件还要看一眼有没有被库文件扫描功能误删。5.2 线程安全与并发下的注意事项UDF在MySQL里是并发执行的同一个UDF在同一时刻可能被多个线程、多个连接调用。这里有一个很多新手都搞错的概念UDF_INIT *initid并不是全局共享的MySQL会对每一个连接中的每一次表达式评估单独调用一次init所以initid指向的内存天然是线程隔离的你不用加锁去保护它。但如果你是c里定义了一个全局静态变量想在函数调用间共享缓存或者做计数器那就麻烦了。比如static double last_lat;这种多个线程同时读写会存在数据竞争轻则算错重则Crash。所以我的原则是UDF内部完全不使用可写的全局状态所有可变量都放在栈上需要缓存就挂到initid-ptr上它是每连接私有的安全且高效。内存释放顺序也要注意。如果你在init里用malloc给initid-ptr分配了一块缓存而deinit里又没有释放每次连接新建都会泄漏一块。注意不是每行调用都泄漏是每个连接周期泄漏一次短连接业务大量建立连接时会持续涨内存。这个很难靠监控发现但长时间运行后会拖垮mysqld。5.3 什么时候不该写UDF写UDF感觉很酷但它不是万能的我也见过不少被滥用UDF翻车的案例这里必须泼一点冷水。第一种不该写的情况是能用内置函数解决就别写。比如MySQL 8.0已经有ST_Distance_Sphere如果你的库可以升到8.0那就不需要再来一个自定义UDF。第二种不该写的场景函数体里做了非常重的IO操作比如在UDF里连接外部服务或者读文件这是绝对禁忌。因为UDF跑在mysqld进程里任何慢IO都会直接卡住数据库线程一次意外阻塞可能拖垮整个实例。第三种如果你只是想在应用层算一个近似的距离很多地理库已经足够没必要把这种逻辑下沉到数据库层要知道UDF的维护成本比普通代码高因为它跟数据库版本、编译器、系统环境全部耦合。还有一个容易被忽略的点尽量不要用UDF来封装加密算法或者复杂的二进制协议解析这类东西要么有现成的函数要么应该放在应用层。MySQL的UDF接口本身是可返回二进制字符串的但你要自己处理length参数和空字符问题远比应用层处理复杂而且一旦写错就是安全漏洞。写在最后这次写UDF最大的收获不是学会了一个Haversine公式而是对整个MySQL扩展机制有了更直观的理解。很多人在数据库这条路上习惯“能用就行”遇到内置函数覆盖不到的需求就上临时表、上存储过程、甚至上外挂中间件其实回头看一眼MySQL的UDF协议它可能才是你这个场景里最精准、最省事的解法。我真正的体会是UDF的价值不仅仅在于性能更在于它把计算逻辑沉淀成数据库原生能力让上层SQL变得简洁可控。最后再分享一个小技巧如果你以后要写多个UDF建议每一个UDF都单独编译成独立的.so不要全部塞进一个库。MySQL加载.so的时候是一次性把所有符号都加载进来的混合在一起虽然少几个文件但一旦某一个函数初始化崩溃整个库和里面驻留的所有UDF都会受影响隔离才是生产环境的第一原则。我是从踩坑里学到这些的希望你读完能少踩几个。