什么是延遲索引？使用索引查詢(xún)出來(lái)數(shù)據(jù)，之后把查詢(xún)結(jié)果和同一張表中數(shù)據(jù)進(jìn)行連接查詢(xún)，進(jìn)而提高查詢(xún)速度!

分頁(yè)是一個(gè)很常見(jiàn)功能，select ** from tableName limit ($page - 1 ) * $n ,$n

通過(guò)一個(gè)存儲(chǔ)過(guò)程插入10000條數(shù)據(jù)進(jìn)行測(cè)試：

create table smth1 (

id int auto_increment ,

ver int(11) default null,

content varchar(1000) not null,

intro varchar(1000) not null,

primary key(id),

key idver(id,ver)

)engine = innodb default charset = utf8;

create procedure smthTest1()

begin

declare num int default 100001;

while num < 1000000 do

set num := num +1;

insert into smth1 values (num ,num,'我是*****','我是誰(shuí)');

end while ;

end;

查詢(xún)：

mysql> show profiles;

+----------+------------+----------------------------------------------+

| Query_ID | Duration | Query |

+----------+------------+----------------------------------------------+

| 1 | 0.002006 | select id ,content from smth1 limit 1000,10 |

| 2 | 0.030106 | select id ,content from smth1 limit 5000,10 |

| 3 | 0.042428 | select id ,content from smth1 limit 9000,10 |

| 4 | 0.01297225 | select id ,content from smth1 limit 10000,10 |

| 5 | 0.13077625 | select id ,content from smth1 limit 20000,10 |

可見(jiàn)隨著查詢(xún)$page 變大，時(shí)間會(huì)越來(lái)越大！

怎樣避免這種情況？

一般我們數(shù)據(jù)庫(kù)里面數(shù)據(jù)都不會(huì)直接刪除，數(shù)據(jù)時(shí)很寶貴的，不舍得刪除，另一方便能提高查詢(xún)數(shù)據(jù)

先利用索引查詢(xún)出來(lái)數(shù)據(jù)，再進(jìn)行聯(lián)合查詢(xún)不就行了

select C.id,C.content from smth1 C inner join

(

select id from smth1 where id > 1000 limit 10

) as t on C.id = t.id ;

select C.id,C.content from smth1 C inner join

(

select id from smth1 where id > 5000 limit 10

) as t on C.id = t.id ;

select C.id,C.content from smth1 C inner join

(

select id from smth1 where id > 9000 limit 10

) as t on C.id = t.id ;

select C.id,C.content from smth1 C inner join

(

select id from smth1 where id > 10000 limit 10

) as t on C.id = t.id ;

select C.id,C.content from smth1 C inner join

(

select id from smth1 where id > 20000 limit 10

) as t on C.id = t.id ;

進(jìn)行執(zhí)行計(jì)劃分析，沒(méi)有一個(gè)大于1s的

11 | 0.04538625 | select C.id,C.content from smth1 C inner join

(

select id from smth1 where id > 5000 limit 10

) as t on C.id = t.id |

| 12 | 0.023278 | select C.id,C.content from smth1 C inner join

(

select id from smth1 where id > 9000 limit 10

) as t on C.id = t.id |

| 13 | 0.02320425 | select C.id,C.content from smth1 C inner join

(

select id from smth1 where id > 10000 limit 10

) as t on C.id = t.id |

| 14 | 0.001938 | select C.id,C.content from smth1 C inner join

(

select id from smth1 where id > 20000 limit 10

) as t on C.id = t.id |

此外，還會(huì)想到用in來(lái)查詢(xún)而不是子查詢(xún)，為什么不用in，使用in會(huì)先查詢(xún)出來(lái)一條id，之后再去和下面進(jìn)行匹配，會(huì)進(jìn)行smth1進(jìn)行全表掃描！

相關(guān)標(biāo)簽：索引

本文原創(chuàng)發(fā)布php中文網(wǎng)，轉(zhuǎn)載請(qǐng)注明出處，感謝您的尊重！

總結(jié)

以上是生活随笔為你收集整理的mysql延时优化教程_Mysql优化之延迟索引和分页优化_MySQL的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。