CREATE TABLE `palette` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`created_at` bigint(20) DEFAULT NULL,
`updated_at` bigint(20) DEFAULT NULL,
`deleted_at` bigint(20) DEFAULT NULL,
`version` int(11) DEFAULT '1',
`ratio` float DEFAULT NULL,
`r` int(11) DEFAULT NULL,
`g` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=501096 DEFAULT CHARSET=utf8mb4;select * from palette where ( ABS(r - 188) + ABS(g - 156) + ABS(b - 223) < 100)
这张表有50万条数据 后续会持续大量增长 使用上面的语句查询耗时一般都是在0.8秒左右,请问有什么比较好的优化方案吗? 谢谢各位SQL查询语句里的 188 156 223 是用户传入的颜色RGB值 100是匹配值, 用户所传入的RBG如果跟库中记录的RGB各项相减的绝对值如果小于100就算匹配
所以必须要调整查询语句分析:
用户传入的值: r = 188、g = 156、b = 223、最大允许误差 = 100
那么r+g+b的范围就是:(188+156+223)±100=567±100
可以先用r+g+b的值来缩小扫描范围,SQL语句改写成
SELECT
*
FROM
palette
WHERE
r + g + b BETWEEN 188+156+223-100 AND 188+156+223+100
AND ABS( r - 188 ) + ABS( g - 156 ) + ABS( b - 223 ) < 100到这儿会说了r+g+b也是计算,是不是也无法使用索引呢?
是的,所以还得修改表结构
如果是mysql5.7或以上版本,可以使用计算列,并建立索引
如果是mysql5.7以下版本,那就加一列rgb_sum,插入数据时维护,并建立索引最终性能,200w数据的表中,查询用时0.5s,环境是i3 7100/8g的办公机
但是没有rgb_sum 这列 效果貌似差不多
然后把查询语句改成
SELECT * FROM palette WHERE rgb BETWEEN 0 AND 99 ;