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就算匹配
uj5u.com熱心網友回復：
可以試下分表uj5u.com熱心網友回復：
或者 講 R,G,B 組合成 int 型， 設成主鍵， 不使用自增 id 作為主鍵uj5u.com熱心網友回復：
你這個查詢，對欄位進行了計算，所以怎么設索引、設主鍵都是沒有意義的，都會全掃描所以必須要調整查詢陳述句
分析：
用戶傳入的值： 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的辦公機
uj5u.com熱心網友回復：
我也測驗200W 資料 0.5左右但是沒有rgb_sum 這列 效果貌似差不多
uj5u.com熱心網友回復：
直接改，真的不那么容易，可以新增欄位rgb，這個欄位直接存盤( ABS(r - 188) + ABS(g - 156) + ABS(b - 223)的值，并且增加這個欄位的索引。然后把查詢陳述句改成
SELECT * FROM palette WHERE rgb BETWEEN 0 AND 99 ;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/79515.html
標籤：MySQL
上一篇：求助，關于hive隨機取數的問題
下一篇：求救：公司的舊系統出問題了，急~