Mysql優化之延遲索引和分頁優化

Mysql優化之延遲索引和分頁優化

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

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

通過一個存儲過程插入10000條數據進行測試：

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,'我是*****','我是誰');
	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 |

怎樣避免這種情況？

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

先利用索引查詢出來數據，再進行聯合查詢不就行了

 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 |