YuTin's Blog

談起這話題，肯定是硬幫幫的事情，
牽扯的層面也不是單單的MySQL，
從Front-End．PHP．MySQL都有責任，
不過本次先讓我們從最核心的MySQL開始談起吧！

與SQL談到sort肯定會扯到ORDER BY sort_key，
當sort_key是有規則可循的，如create_time或update_time...等，
這些都是簡單事。

可是當sort_key的value是可以讓使用者變動的，
也就是哪天使用者失戀了，覺得麵包比愛情重要，滑動了一下手指，
就必須要修改table的sort-key欄位的value時，就開始不是簡單事哩!!
當這樣的更動每天須面對1000萬以上次的話，就成了困難事的開始。

先回到初衷，假設有個table如下：
id是primary key欄位，而sort_key是index欄位

要將排序從1->2->3->4->5改成1->3->4->2->5，可以：

UPDATE `table` SET `sort_key`=0 WHERE `id`=1;
UPDATE `table` SET `sort_key`=3 WHERE `id`=2;
UPDATE `table` SET `sort_key`=1 WHERE `id`=3;
UPDATE `table` SET `sort_key`=2 WHERE `id`=4;
UPDATE `table` SET `sort_key`=4 WHERE `id`=5;

SELECT * FROM `table` ORDER BY `sort_key` ASC 後，得到

覺得UPDATE要下5次太麻煩，也可以

SET @sort = 0;
UPDATE `table` SET `sort_key`=(@sort:=@sort+1) ORDER BY FIELD(`id`,1,3,4,2,5);

但上述兩個方式，都需要一次UPDATE 5筆資料，實在不是一個很好的解決方案，
當資料有1000萬筆的時候就需要更新1000萬筆的資料。

讓使用者每次更新排序時，只UPDATE 1筆資料or較少的資料肯定是最棒的，
以欄位sort_key出發，先談談暴力的方法，把sort_key改成如下：

要將排序從4移動到1與2中間時，可以：

SQL> SELECT `sort_key` FROM `table` WHERE `id`=1 ORDER BY `sort_key` ASC LIMIT 2
PHP> $sort_key=($sort_key_a+$sort_key_b)/2
SQL> UPDATE table SET sort_key=${sort_key} WHERE id=4

先取得1,2的sort_key也就是1000和2000，透過PHP將sort_key相加除以2，並只更新到MySQL有更動排序的該筆資料。

因此只需要更新1筆資料和做一次SELECT，
但預留的sort_key空間是有限的要避免碰撞，php必須排程在低負載的時候更新sort_key區間回到1000的連續數列，
勢必也會增加不少的負載。

竟然如此，我們就必須要瞭解

順序對資料庫沒有意義，只對使用資料的人有意義

跳脫single table的思維，現在用2個table去實作，
第一個table用來存放data，第二個table用來存放sort_key，
並加入group_id欄位，假設多數的排序需求，只更動部分資料，
如下：

要將group 1的排序從1->2->3，修改為1->3->2的時候，可以：

DELETE FROM `table_sort` WHARE `id` IN (1,2,3);
INSERT INTO `table_sort`(`id`, `sort_key`) VALUES (1, 1);
INSERT INTO `table_sort`(`id`, `sort_key`) VALUES (2, 3);
INSERT INTO `table_sort`(`id`, `sort_key`) VALUES (3, 2);
SELECT `table_data`.*
  FROM `table_data` LEFT JOIN `table_sort` ON `table_data`.`id`=`table_sort`.`id`
  WHERE `table_data`.`group_id`=1 ORDER BY `sort_key` ASC

INSERT與DELETE的速度比UPDATE快速，所以選擇以INSERT與DELETE作為操作，
當然DELETE到INSERT的轉換期間也是要被考慮到。

老問題，要更新的筆數實在太多，必須要想辦法調整成更新越少筆數越好，
把table_sort用簡單的一句表示可以看成：sort_id=>id, 1=>1, 2=>2, 3=>3
改成用PHP陣列表示就是：array(1=>1, 2=>2, 3=>3)
其實array本來就是個連續的流水號，因此可以簡化成保留id值array(1,2,2)
然後把array存進database，就變成：

要將group 1的排序，改成3(巨峰大葡萄),2(芭樂),1(香蕉)，只需要：

UPDATE `table_sort` SET `sort_key`='3,2,1' WHERE `group_id`=1

困難的問題就在怎麼把一行的sort_key與table的data做連結，
可以從MySQL與PHP兩個地方開始思考，
先來說說MySQL的做法，在MySQL的ORDER方法中可以ORDER BY FIELD，也就是：

SELECT `sort_key` FROM `table_sort` WHERE `group_id`=1;
SELECT * FROM `table_data` WHERE `group_id`=1 ORDER BY FIELD(`id`, ${sort_key});

附帶一提，此時${sort_key}就要注意sql injection的問題，
為了效能，當select不到sort_key時，可以SQL連ORDER BY FIELD都不必下，
但肯定還是有人會對ORDER BY FIELD的效能有些疑慮。

沒關係，接著來看看PHP怎麼實作與sort_key的連結，

...待續