Mysql 最佳化之延遲索引和分頁最佳化

標籤：blog   io   ar   使用   sp   資料   on   2014   log   

什麼是延遲索引？使用索引查詢出來資料，之後把查詢結果和同一張表中資料進行串連查詢，進而提高查詢速度!

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

通過一個預存程序進行測試：

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 doset num := num +1;insert into smth1 values (num ,num,'我是步迎飛','我是誰');end while ;end;

查詢：

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 |

可見隨著查詢$page 變大，時間會越來越大！

怎樣避免這種情況？

一般我們資料庫裡面資料都不會直接刪除，資料時很寶貴的，不捨得刪除，另一方便能提高查詢資料

先利用索引查詢出來資料，再進行聯集查詢不就行了

 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 ;

進行效率分析，沒有一個大於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 |

Mysql 最佳化之延遲索引和分頁最佳化