首先假设有一个资料表 news ，共有20万笔资料 ， 排序的栏位为 sort，primary 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 作者 |
- create table book(
- id int auto_increment,
- book_name varchar(255),
- price int,
- author_id int,
- sort int,
- sort_index int,
- sort_unique int,
- primary key (id),
- index (sort_index),
- unique (sort_unique)
- )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,10 | 2.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;
Reply$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] 應該怎樣處理?
Admin
Himmlms40 2016/01/19
Nice code!
Duck 2014/11/11
Great!
Rooc 2014/04/16
Awesome!