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)