?2016-2026 Matools All rights reserved,
粵ICP備17059708號(hào)
-- 示例表 CREATE TABLE `employees` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name`
varchar(24) NOT NULL DEFAULT '' COMMENT '姓名', `age` int(20) NOT NULL DEFAULT
'0' COMMENT '年齡', `position` varchar(20) NOT NULL DEFAULT '' COMMENT '職位',
`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP COMMENT '入職時(shí)間', PRIMARY KEY (`id`), KEY
`idx_name_age_position` (`name`,`age`,`position`) USING BTREE, KEY `idx_age`
(`age`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=136326 DEFAULT CHARSET=utf8
COMMENT='員工表' --創(chuàng)建100000條記錄 drop procedure if EXISTS insert_emp; delimiter ;;
create procedure insert_emp() BEGIN declare i int; set i=1; while(i < 100000)DO
INSERT INTO employees(name,age,position)
values(CONCAT('xiaoqiang',i),i,'coder'); SET i=i+1; end WHILE; end;; delimiter
; call insert_emp();
根據(jù)自增且連續(xù)的主鍵排序的分頁查詢
select * from employees LIMIT 9999 ,5;
表示從表employees
中取出從10000行開始的5行記錄?？此浦徊樵?條記錄，實(shí)際這條SQL是先讀取10005條記錄，然后拋棄前10000條記錄，然后讀到后面5條想要的數(shù)據(jù)。沒有添加單獨(dú)的order
by，表示通過主鍵排序。
因此要查詢一張大表比較靠后的數(shù)據(jù)，執(zhí)行效率是非常低的。
因?yàn)橹麈I是自增且連續(xù)的，所以可以改寫成按照主鍵查詢從第10001開始的五行數(shù)據(jù)，如下：
select * from employees WHERE id > 9999 limit 5;
可以看到兩個(gè)sql的執(zhí)行計(jì)劃，顯然改寫后的sql走了索引，而且掃描的行數(shù)大大減少，執(zhí)行效率會(huì)更高。但是，這條改寫的sql在很多場(chǎng)景下并不實(shí)用，因?yàn)楸碇锌赡苣承┯涗洷粍h除后，主鍵空缺，導(dǎo)致結(jié)果不一致。
先刪除一條記錄，然后測(cè)試下原來sql和優(yōu)化后的sql：
select * from employees LIMIT 9999 ,5;
select * from employees where id> 9999 limit 5;
兩條sql的結(jié)果不一樣，因此，如果主鍵不連續(xù)，不能使用上面描述的方法。
另外由于原來sql是order by非主鍵字段，按照上面的方法改寫sql的結(jié)果不一致。所以這種改寫得滿足以下兩個(gè)條件：
* 主鍵自增且連續(xù)
*
結(jié)果是按照主鍵排序的
根據(jù)非主鍵字段排序的分頁查詢
select * from employees order by name limit 9000, 5;
explain select * from employees order by name limit 9000, 5;
key字段對(duì)應(yīng)的值為null，發(fā)現(xiàn)并沒有使用name字段的索引。因?yàn)閽呙枵麄€(gè)索引并查找到?jīng)]有索引的行，可能要便利多個(gè)索引樹，其成本比掃描全表的成本更高，索引優(yōu)化器放棄使用索引。
優(yōu)化的關(guān)鍵是：讓排序時(shí)返回的字段盡可能的少，所以可以讓排序和分頁操作先查出主鍵，然后根據(jù)主鍵查到對(duì)應(yīng)的記錄。
改下如下：
select * from employees as e inner join(select id from employees order by name
limit 9000,5) as ed on e.id=ed.id;
可以看到結(jié)果與原來的sql結(jié)果是一致的，執(zhí)行時(shí)間減少了一般以上，再對(duì)比下執(zhí)行計(jì)劃：
原來的sql使用的是filesort排序，而優(yōu)化后的sql使用的是索引排序。
in和exists優(yōu)化
原則：小表驅(qū)動(dòng)大表，即小表的數(shù)據(jù)集驅(qū)動(dòng)大表的數(shù)據(jù)集
in：當(dāng)B表的數(shù)據(jù)集小于A表的數(shù)據(jù)集時(shí)，in由于exists
select * from A where id in(select id from B) 等價(jià)于 for(select id from B){
select * from A where A.id=B.id }
exists:當(dāng)A表的數(shù)據(jù)集小于B表的數(shù)據(jù)集時(shí)，exitsts優(yōu)于in
當(dāng)著查詢A的數(shù)據(jù)，放到子查詢B中做條件驗(yàn)證，根據(jù)驗(yàn)證結(jié)果（true或false）來決定著查詢的數(shù)據(jù)是否保留。
select * from A exists(select 1 from B where A.id=B.id) 等價(jià)于 for(select * from
A){ select * from B where A.id=B.id }
count(*)查詢優(yōu)化
explain select count(1) from employees; explain select count(id) from
employees; explain select count(name) from employees; explain select count(*)
from employees;
四個(gè)sql的執(zhí)行計(jì)劃幾乎一樣的，count(name)使用的是聯(lián)合索引， 主要區(qū)別根據(jù)某個(gè)字段做count操作不會(huì)統(tǒng)計(jì)字段為null的值的數(shù)據(jù)行。
除了count(name)的其他count操作，都是用的輔助索引而不是主鍵索引， 因?yàn)槎?jí)索引存儲(chǔ)數(shù)據(jù)更少，檢索性能更高。
還沒關(guān)注我的公眾號(hào)？
* 掃文末二維碼關(guān)注公眾號(hào)【小強(qiáng)的進(jìn)階之路】可領(lǐng)取如下:
* 學(xué)習(xí)資料： 1T視頻教程：涵蓋Javaweb前后端教學(xué)視頻、機(jī)器學(xué)習(xí)/人工智能教學(xué)視頻、Linux系統(tǒng)教程視頻、雅思考試視頻教程；
* 100多本書：包含C/C++、Java、Python三門編程語言的經(jīng)典必看圖書、LeetCode題解大全；
* 軟件工具：幾乎包括你在編程道路上的可能會(huì)用到的大部分軟件；
* 項(xiàng)目源碼：20個(gè)JavaWeb項(xiàng)目源碼。
熱門工具 換一換