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 |
可見隨著查詢$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 |
此外,還會想到用in來查詢而不是子查詢,為什麼不用in,使用in會先查詢出來一條id,之後再去和下面進行匹配,會進行smth1進行全表掃描!
--------------------------------------分割線 --------------------------------------
Ubuntu 14.04下安裝MySQL
《MySQL權威指南(原書第2版)》清晰中文掃描版 PDF
Ubuntu 14.04 LTS 安裝 LNMP Nginx\PHP5 (PHP-FPM)\MySQL
Ubuntu 14.04下搭建MySQL主從伺服器
Ubuntu 12.04 LTS 構建高可用分布式 MySQL 叢集
Ubuntu 12.04下原始碼安裝MySQL5.6以及Python-MySQLdb
MySQL-5.5.38通用二進位安裝
--------------------------------------分割線 --------------------------------------
本文永久更新連結地址: