2011
Mar
23

這篇文章將介紹因為 order by 與 limit offset,而造成 Mysql 資料庫搜尋時間過久的問題 - Slow Query。

首先假設有一個資料表 news ,共有20萬筆資料 , 排序的欄位為 sortprimary key 為 id

這時當我們要查詢第19999頁的資料時,會用這樣的語法

select * from news order By sort DESC limit 199990,10;

結果 Mysql 資料庫卻跑了2秒鐘,這真的太嚇人了

接著我們用語法

explain select * from news order By sort DESC limit 10000,10;

發現 possible_keys = null , extra = Using filesort 所以在order By sort時,mysql使用 filesort ,並沒有用index方式排序,不過這並不是最大的問題。

再試看看不加排序的語法:

select * from news limit 10000,10;

執行0.5秒,也就是說拿掉排序後,SQL 語法的執行時間就正常了,也就是說當資料量過大時,排序會影響到SQL的效能。

我們再試試下列兩句語法:

select * from news order By sort DESC limit 199990,10; 執行2秒

select id from news order By sort DESC limit 199990,10; 執行0.5秒

從這裡我們可以看出來 MySql 在排序的時間,讀取的欄位也會影響排序的效能,而且影響的程度,遠大於 Index。

最後我們使用下列的語法,來解決排序而造成搜尋時間過久的問題,方法是用 inner join 的方式,先 select primary key ,再用 primary key 去取得其它欄位的資料。

select * from news as t inner join ( select id from news order by sort DESC limit 140000,10 ) as k where t.id=k.id

Mysql performance 測試

現在再做一些詳細的測試,來比較各種狀況的SQL語法。

假設資料表 : book 欄位如下,並且資料表中有20萬筆資料。

book_name 書名price 價格author_id 作者
DB schema
  1. create table book(
  2. id int auto_increment,
  3. book_name varchar(255),
  4. price int,
  5. author_id int,
  6. sort int,
  7. sort_index int,
  8. sort_unique int,
  9. primary key (id),
  10. index (sort_index),
  11. unique (sort_unique)
  12. )engine=MyISAM DEFAULT CHARSET=utf8 ;




搜尋欄位SQL語法執行時間(秒)
抓最前10筆資料
並指定排序sort
select * from book order by sort ASC limit 0,10 0.07
抓最後10筆資料
並指定排序sort
select * from book order by sort ASC limit 199990,102.16
抓book最後10筆資料,
以sort為排序,只取欄位ID
select id from book order by sort limit 199990,10 0.1
抓最後10筆資料,
以sort(index)為排序
select * from book order by sort_index ASC limit 199990,10 1.85
抓最後10筆資料,
以sort(require)為排序
select * from book order by sort_unique ASC limit 199990,10 1.775
抓最後10筆資料,
以sort為排序,並優先取得id
(優化語法)
select * from `book` as t inner join (select id from book order by sort ASC limit 199990,10) as b on t.id=b.id order by t.sort ASC 0.13
抓最後10筆資料,
以id為排序,並優先取得id
(優化語法)
select * from `book` as t inner join (select id from book order by id ASC limit 199990,10) as b on t.id=b.id order by t.id ASC 0.065
排序id抓最後10筆資料,取欄位id,author_id book_name有文字後 select id,book_name,author_id from book order by id limit 199990,10
有讀取文字時,速度會特別慢
1.8

橘色的SQL語法,代表執行速度過慢,應修改為優化後的語法,結論是:如果資料量很大,又加上 select 的欄位有文字,以及使用 order 排序時,SQL執行時間就會爆表 ( 含文字的欄位,資料越長,速度就越慢)。

這裡有一篇文章,在說明 limit offset 過大時,為什麼會造成 slow query 。


目前回應 Comments(5 comments)

  • HIM 2016/03/25

    謝謝幫忙:D
    關於SQL 漏洞,有些看不懂
    例如: $name = preg_replace("/['"]+/" , '' ,$name); 我只放在backend 的login_submit 足夠防護嗎?

    其他的不知道那個合適和放在那裡作防護>"

  • HIM 2016/03/24

    $rowsPerPage =2;

    $pageNum = 1;

    if(isset($_GET['page']))
    {
    $pageNum = $_GET['page'];
    }

    $offset = ($pageNum - 1) * $rowsPerPage;

    $link = mysqli_connect( "localhost", "root", "password" ) or die(mysqli_error($link));
    mysqli_query("SET names 'UTF8' ");
    mysqli_select_db( "test" );
    $sql = "SELECT * FROM news WHERE ORDER BY id DESC LIMIT $offset, $rowsPerPage";

    if ( $search != "" )
    {
    $sql = $sql . " WHERE subject LIKE '%$search%' OR description LIKE '%$search%' ";
    }

    $result = mysqli_query( $sql, $link) or die ("Error Query [".$sql."]");
    while ($line = mysqli_fetch_array($result, MYSQL_ASSOC)) {

    如果加了search function 出現了Error Query [SELECT * FROM news WHERE ORDER BY id DESC LIMIT 0, 2] 應該怎樣處理?

    Reply

    Admin

    不管有沒有 search ,你的 Query statement 都是錯的,改成以下的方式。
     
    $sql = "SELECT * FROM news ORDER BY id DESC LIMIT $offset, $rowsPerPage";
     
    if ( $search != "" )
    {
       $sql = "SELECT * FROM news WHERE subject LIKE '%$search%' OR description LIKE '%$search%' ORDER BY id DESC LIMIT $offset, $rowsPerPage";
    }
     
     
    另外你的 SQL 語法有漏洞 ,請參考我的另一篇文章 SQL Injection

  • Himmlms40 2016/01/19

    Nice code!

  • Duck 2014/11/11

    Great!

  • Rooc 2014/04/16

    Awesome!

回應 (Leave a comment)