SQL

Database 最佳化: SQL

指定使用索引

SELECT id
FROM data USE INDEX(type)
WHERE type=12345 AND level > 3
ORDER BY id

在下SQL语法的时候,有时某些语法使用某些索引执行效率会比较好,可是有时候MySQL没办法替我们选择一个最适合的索引,导致执行的效率很慢(slow query),所以我们必须使用USE INDEX去指定执行效率好的索引,以提升效率。

少用 JOIN,多用几次 SELECT 捞取大量资料

SELECT user.id , user.name , post.id , post.content
FROM user , post
WHERE user.id = post.user_id ......

我们在捞取使用者的文章资讯的时候,我们可能会用 JOIN 去捞取我们要的资料,这样很直觉,只是当使用者资料有10000笔,而文章有99999笔,像这种有大量资料时候的话,使用 JOIN 对于资料存取真的是恶梦,因为 JOIN 过后表示会有 10000x99999=999990000 笔资料,然后再从这麽大量的资料中去捞取 WHERE 判断式中指定的资料,在资料库伺服器记忆体不够的时候铁定会炸掉。

解决方式是我们可以分批捞取使用者的资料,以及文章的资料

SELECT user.id , user.name
FROM user
SELECT id , content , user_id
FROM post

在使用者资料资料捞取出来之后,使用迴圈将使用者存成阵列,但是「阵列的索引」是使用可以识别的「使用者编号(user.id)」当作索引值。

<?php
$user['user.id'] = array();

在捞取文章资料后,必须将捞取的文章指定给该文章的作者(使用者),而我们捞取的使用有捞取「使用者编号(user.id)」,所以可以透过这个资料,将文章存到使用者资料下的文章阵列。

<?php
$user[$post['user_id']]['post'] = $post_array;

透过分次捞取,本来的 10000x99999=999990000 笔资料,就会变成 10000+99999=109999 笔资料,记忆体消耗降低极大下,也可以达到同样的效果

后记

JOIN 在资料库几乎是必学的语法,让资料表的正规化,避免资料冗余,在查询的时候也可以透过 JOIN 组合出想要的资料,所以 JOIN 对于资料库正规化有它的用途,但资料库的发展的初期中,还没有像现在有用到巨量的资料(Google、Facebook、Twitter … etc),所以在流量&资料量小的时候,资料库在 JOIN 时的查询回应速度通常差没几毫秒 (0.01~0.05毫秒),所以都在可接受的范围,但现在大型网站一天都好几千万的存取量,假如每天有 1000 万的存取次数,而每个地方都慢了 0.05 毫秒,总共就会慢了 10,000,000 x 0.05 = 500,000 毫秒 = 500 秒,在我们要求每次回应时间最差都要在 1 ~ 2 秒内回应,若是像 Google、Facebook 这样的大型服务,要求可能都要在 0.1 秒以内,这样慢的时间可能会让使用者等到不耐烦,若 $$ 多多加机器仅能简单应付巨量级的查询,没办法解决 JOIN 所造成的查询瓶颈,所以在巨量级资料的情况下,几乎很少会使用 JOIN 的方式去查询所有的资料出来,天下武功,无坚不摧,唯快不破。

更新或删除大量范围的资料,请用主键去更新

我们可能会想要一次异动大范围的资料,若以社群网站的通知(Notification)来说,我们希望在使用者点选通知列表后,将所有的通知从未读状态变更为已读,我们可以很容易地想到用这样的 SQL 语法去进行更新:

UPDATE `notification` SET status="已读" WHERE created_at < '现在时间';

若我们要删除大量的资料时,我们也可能用这样的 SQL 语法去删除资料:

DELETE FROM `backup` WHERE created_at < '现在时间';

但是在这样的条件为范围的语法,资料库会需要一笔一笔的去比对资料是否为设定条件的范围,若为交易的资料,资料库会将资料进行锁定禁止读取,有可能锁定一些非我们要处理的资料,若处理时间过久,可能会导致捞取资料的反应时间过久。

所以通常为了避免锁定不必要资料的情况发生,我们可以试着先把资料捞取出来,再透过明确的主键去指定哪些异动的资料需要被锁定,而不影响其他的资料。

更新资料(较佳作法)

-- 先捞取资料
SELECT `notification` WHERE status="未读" AND created_at < '现在时间';

-- 指定要更新资料的主键
UPDATE `notification` SET status="已读" WHERE  id IN (1, 2, 3);

删除资料(较佳作法)

-- 先捞取资料
SELECT `backup` WHERE created_at < '现在时间';

-- 指定要删除资料的主键
DELETE FROM `backup` WHERE id IN (1, 2, 3);

在服务会有大量的人捞取资料的时候,这样可以避免不必要的资料锁定,也可以让存取速度加快喔~

参考资料