兩條 SQL 的結(jié)果并不一樣，因此，如果主鍵不連續(xù)，不能使用上面描述的優(yōu)化方法。

另外如果原SQL是order by 非主鍵的字段，按照上面說(shuō)餓的方法改寫會(huì)導(dǎo)致兩條SQL的結(jié)果不一致。所以這種改寫得滿足以下兩個(gè)條件：

主鍵自增且連續(xù)結(jié)果是按照主鍵排序的案例2: 根據(jù)非主鍵字段排序的分頁(yè)查詢

再看一個(gè)根據(jù)非主鍵字段排序的分頁(yè)查詢，SQL 如下:

select * from employees ORDER BY name limit 9000,5;復(fù)制代碼

 EXPLAIN select * from employees ORDER BY name limit 90000,5;復(fù)制代碼

發(fā)現(xiàn)并沒(méi)有使用 name 字段的索引(key 字段對(duì)應(yīng)的值為 null)，具體原因上前面講過(guò) : 掃描整個(gè)索引并查找到?jīng)]索引的行(可能要遍歷多個(gè)索引樹)的成本比掃描全表的成本更高，所以優(yōu)化器放棄使用索引。 知道不走索引的原因，那么怎么優(yōu)化呢? 其實(shí)關(guān)鍵是讓排序時(shí)返回的字段盡可能少，所以可以讓排序和分頁(yè)操作先查出主鍵，然后根據(jù)主鍵查到對(duì)應(yīng)的記錄，SQL 改寫如下：

select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;復(fù)制代碼

需要的結(jié)果與原 SQL 一致，執(zhí)行時(shí)間減少了一半以上，我們?cè)賹?duì)比優(yōu)化前后sql的執(zhí)行計(jì)劃:

EXPLAIN select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;復(fù)制代碼

原 SQL 使用的是 filesort 排序，而優(yōu)化后的 SQL 使用的是索引排序。

Join關(guān)聯(lián)查詢優(yōu)化

#示例表CREATE TABLE `t1` (    `id` INT (11) NOT NULL AUTO_INCREMENT,    `a` INT (11) DEFAULT NULL,    `b` INT (11) DEFAULT NULL,
    PRIMARY KEY (`id`),    KEY `idx_a` (`a`)
) ENGINE = INNODB AUTO_INCREMENT = 10001 DEFAULT CHARSET = utf8;CREATE TABLE t2 LIKE t1;復(fù)制代碼

往t1表插入1萬(wàn)行記錄，往t2表插入100行記錄

#t1 1萬(wàn)條記錄drop procedure if exists insert_emp_t1; 
delimiter ;; 
create procedure insert_emp_t1()begin
    declare i int; 
    set i=1; 
    while(i<=10000) do
        insert into t1(a,b) values(i,i);        set i=i 1; 
    end while;end;;
delimiter ; 
call insert_emp_t1();

#t2 100條記錄drop procedure if exists insert_emp_t2; 
delimiter ;; 
create procedure insert_emp_t2()begin
    declare i int; 
    set i=1; 
    while(i<=100) do
        insert into t2(a,b) values(i,i);        set i=i 1; 
    end while;end;;
delimiter ; 
call insert_emp_t2();復(fù)制代碼

MySQL 的表關(guān)聯(lián)常見(jiàn)有兩種算法Nested-Loop Join 算法Block Nested-Loop Join 算法案例1：嵌套循環(huán)連接 Nested-Loop Join（NLJ）算法

一次一行循環(huán)地從第一張表（稱為驅(qū)動(dòng)表）中讀取行，在這行數(shù)據(jù)中取到關(guān)聯(lián)字段，根據(jù)關(guān)聯(lián)字段在另一張表（被驅(qū)動(dòng)表）里取出滿足條件的行，然后取出兩張表的結(jié)果合集。

EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;復(fù)制代碼

從執(zhí)行計(jì)劃中可以看到這些信息:

驅(qū)動(dòng)表是 t2，被驅(qū)動(dòng)表是 t1。先執(zhí)行的就是驅(qū)動(dòng)表(執(zhí)行計(jì)劃結(jié)果的id如果一樣則按從上到下順序執(zhí)行sql);優(yōu)化器一般會(huì)優(yōu)先選擇小表做驅(qū)動(dòng)表。所以使用 inner join 時(shí)，排在前面的表并不一定就是驅(qū)動(dòng)表。使用了 NLJ 算法。一般 join 語(yǔ)句中，如果執(zhí)行計(jì)劃 Extra 中未出現(xiàn) Using join buffer 則表示使用的 join 算法是 NLJ。

上面SQL的大致流程如下：

從表 t2 中讀取一行數(shù)據(jù)；從第1步的數(shù)據(jù)中，取出關(guān)鍵字字段 a，到表 t1 中查找；取出表 t1 中滿足條件的行，跟 t2 中獲取到的結(jié)果合并，作為結(jié)果返回給客戶端；重復(fù)上面 3 步。

整個(gè)過(guò)程會(huì)讀取 t2 表的所有數(shù)據(jù)（掃描100行），然后遍歷這每行數(shù)據(jù)中字段 a 的值，根據(jù) t2 表中的 a 的值索引掃描 t1 表中對(duì)應(yīng)的行（掃描 100次 t1 表的索引，1次掃描可以認(rèn)為最終只掃描 t1 表一行完整數(shù)據(jù)，也就是總共 t1 表也掃描了100行）。因此整個(gè)過(guò)程掃描了 200 行。

如果被驅(qū)動(dòng)表的關(guān)聯(lián)字段沒(méi)有索引，使用NLJ算法性能會(huì)比較低（下面有詳細(xì)解釋），MySQL 會(huì)選擇 Block Nested-Loop Join 算法。

案例2：基于塊的嵌套循環(huán)連接 Block Nested-Loop Join（BNL）算法

把驅(qū)動(dòng)表的數(shù)據(jù)讀入到 join_buffer 中，然后掃描被驅(qū)動(dòng)表，把被驅(qū)動(dòng)表每一行取出來(lái)跟 join_buffer 中的數(shù)據(jù)做對(duì)比。

EXPLAIN select * from t1 inner join t2 on t1.b= t2.b;復(fù)制代碼

Extra 中 的Using join buffer (Block Nested Loop)說(shuō)明該關(guān)聯(lián)查詢使用的是 BNL 算法。

上面sql的大致流程如下:

把 t2 的所有數(shù)據(jù)放入到 join_buffer 中把表 t1 中每一行取出來(lái)，跟 join_buffer 中的數(shù)據(jù)做對(duì)比返回滿足 join 條件的數(shù)據(jù)

整個(gè)過(guò)程對(duì)表 t1 和 t2 都做了一次全表掃描，因此掃描的總行數(shù)為10000(表 t1 的數(shù)據(jù)總量) 100(表 t2 的數(shù)據(jù)總量) = 10100。并且 join_buffer 里的數(shù)據(jù)是無(wú)序的，因此對(duì)表 t1 中的每一行，都要做 100 次判斷，所以內(nèi)存中的判斷次數(shù)是 100 * 10000= 100 萬(wàn)次。

被驅(qū)動(dòng)表的關(guān)聯(lián)字段沒(méi)索引為什么要選擇使用 BNL 算法而不使用 Nested-Loop Join 呢?

如果上面第二條sql使用 Nested-Loop Join，那么掃描行數(shù)為 100 * 10000 = 100萬(wàn)次，這個(gè)是磁盤掃描。

很顯然，用BNL磁盤掃描次數(shù)少很多，相比于磁盤掃描，BNJ 的內(nèi)存計(jì)算會(huì)快得多。

因此MySQL對(duì)于被驅(qū)動(dòng)表的關(guān)聯(lián)字段沒(méi)索引的關(guān)聯(lián)查詢，一般都會(huì)使用 BNL 算法。如果有索引一般選擇 NLJ 算法，有索引的情況下 NLJ 算法比 BNL算法性能更高。

對(duì)于關(guān)聯(lián)SQL的優(yōu)化關(guān)聯(lián)字段加索引，讓mysql做join操作時(shí)盡量選擇NLJ算法小表驅(qū)動(dòng)大表，寫多表連接sql時(shí)如果明確知道哪張表是小表可以用straight_join寫法固定連接驅(qū)動(dòng)方式，省去mysql優(yōu)化器自己判斷的時(shí)間

straight_join解釋

straight_join功能同join類似，但能讓左邊的表來(lái)驅(qū)動(dòng)右邊的表，能改變優(yōu)化器對(duì)于聯(lián)表查詢的執(zhí)行順序。

比如 : select * from t2 straight_join t1 on t2.a = t1.a; 代表制定mysql選擇 t2 表作為驅(qū)動(dòng)表。

straight_join只適用于inner join，并不適用于left join，right join。(因?yàn)閘eft join，right join已經(jīng)代表指 定了表的執(zhí)行順序)盡可能讓優(yōu)化器去判斷，因?yàn)榇蟛糠智闆r下mysql優(yōu)化器是比人要聰明的。使用straight_join一定要慎重，因 為部分情況下人為指定的執(zhí)行順序并不一定會(huì)比優(yōu)化引擎要靠譜。in 和 exsits 優(yōu)化

原則：小表驅(qū)動(dòng)大表，即小的數(shù)據(jù)集驅(qū)動(dòng)大的數(shù)據(jù)集。

in：當(dāng)B表的數(shù)據(jù)集小于A表的數(shù)據(jù)集時(shí)，in優(yōu)于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
}復(fù)制代碼

exists：當(dāng)A表的數(shù)據(jù)集小于B表的數(shù)據(jù)集時(shí)，exists優(yōu)于in

將主查詢A的數(shù)據(jù)，放到子查詢B中做條件驗(yàn)證，根據(jù)驗(yàn)證結(jié)果(true或false)來(lái)決定主查詢的數(shù)據(jù)是否保留

select * from A where exists (select 1 from B whereB.id=A.id) 
#等價(jià)于:for(select * from A){    select * from B where B.id = A.id
}

#A表與B表的ID字段應(yīng)建立索引復(fù)制代碼

EXISTS (subquery)只返回TRUE或FALSE,因此子查詢中的SELECT * 也可以用SELECT 1替換,官方說(shuō)法是實(shí)際執(zhí)行時(shí)會(huì) 忽略SELECT清單,因此沒(méi)有區(qū)別；EXISTS子查詢的實(shí)際執(zhí)行過(guò)程可能經(jīng)過(guò)了優(yōu)化而不是我們理解上的逐條對(duì)比；EXISTS子查詢往往也可以用JOIN來(lái)代替，何種最優(yōu)需要具體問(wèn)題具體分析；Count(*) 查詢優(yōu)化

臨時(shí)關(guān)閉mysql查詢緩存，為了查看sql多次執(zhí)行的真實(shí)時(shí)間。

set global query_cache_size=0;set global query_cache_type=0;復(fù)制代碼

EXPLAIN select count(1) from employees; 
EXPLAIN select count(id) from employees;EXPLAIN select count(name) from employees; 
EXPLAIN select count(*) from employees;復(fù)制代碼

四個(gè)sql的執(zhí)行計(jì)劃一樣，說(shuō)明這四個(gè)sql執(zhí)行效率應(yīng)該差不多，區(qū)別在于根據(jù)某個(gè)字段count不會(huì)統(tǒng)計(jì)字段為null值的數(shù)據(jù)行。

為什么mysql最終選擇輔助索引而不是主鍵聚集索引?

因?yàn)槎?jí)索引相對(duì)主鍵索引存儲(chǔ)數(shù)據(jù)更少，檢索性能應(yīng)該更高

常見(jiàn)的優(yōu)化方法如下：查詢MySQL自己維護(hù)的總行數(shù)show table status將總數(shù)維護(hù)到Redis里增加計(jì)數(shù)表查詢MySQL自己維護(hù)的總行數(shù)

對(duì)于myisam存儲(chǔ)引擎的表做不帶where條件的count查詢性能是很高的，因?yàn)閙yisam存儲(chǔ)引擎的表的總行數(shù)會(huì)被 mysql存儲(chǔ)在磁盤上，查詢不需要計(jì)算。

對(duì)于innodb存儲(chǔ)引擎的表mysql不會(huì)存儲(chǔ)表的總記錄行數(shù)，查詢count需要實(shí)時(shí)計(jì)算。

show table status

如果只需要知道表總行數(shù)的估計(jì)值可以用如下sql查詢，性能很高

將總數(shù)維護(hù)到Redis里

插入或刪除表數(shù)據(jù)行的時(shí)候同時(shí)維護(hù)redis里的表總行數(shù)key的計(jì)數(shù)值(用incr或decr命令)，但是這種方式可能不準(zhǔn)，很難保證表操作和redis操作的事務(wù)一致性。

增加計(jì)數(shù)表

插入或刪除表數(shù)據(jù)行的時(shí)候同時(shí)維護(hù)計(jì)數(shù)表，讓他們?cè)谕粋€(gè)事務(wù)里操作。

想了解更多編程學(xué)習(xí)，敬請(qǐng)關(guān)注php培訓(xùn)欄目！

更多關(guān)于云服務(wù)器，域名注冊(cè)，虛擬主機(jī)的問(wèn)題，請(qǐng)?jiān)L問(wèn)西部數(shù)碼官網(wǎng)：m.ps-sw.cn





猜你還會(huì)喜歡下面的內(nèi)容

• 做一個(gè)獨(dú)立站需要多少錢
• 西部數(shù)碼怎樣搶注域名
• B2C 電子商務(wù)網(wǎng)站如何做推廣和優(yōu)化
• 網(wǎng)站推廣增加外鏈最好的方法
• 新手站長(zhǎng)淺談：如何在線提升產(chǎn)品轉(zhuǎn)化率
• 網(wǎng)站策劃：九大思維理念助你策劃能力提升
• 如何高效輸出移動(dòng)app產(chǎn)品原型？
• 為什么說(shuō)產(chǎn)品思考是進(jìn)行用戶體驗(yàn)設(shè)計(jì)時(shí)所不可或缺的？
• 六套 App：構(gòu)建我的產(chǎn)品設(shè)計(jì)工作流
• 如果Vine模式重來(lái)，大概就是Biu這個(gè)樣子
• 新手入門：如何做需求分析
• 產(chǎn)品狗的日常：如何更好的去分解功能點(diǎn)
• 簡(jiǎn)單明了丨如何寫出好的PRD
• 產(chǎn)品必修課——輕松搞定UML
• 如果你是個(gè)苦逼的App測(cè)試人員，這款產(chǎn)品或許能拯救你
• 手把手教你制作實(shí)用美觀的交互設(shè)計(jì)文檔

熱門標(biāo)簽

其他問(wèn)題云服務(wù)器問(wèn)題域名及賬戶問(wèn)題企業(yè)郵局市場(chǎng)咨詢云服務(wù)器云建站/云站群/小程序虛擬主機(jī)網(wǎng)絡(luò)知識(shí)企業(yè)郵箱域名注冊(cè)域名備案域名商標(biāo)注冊(cè)云主機(jī)更多標(biāo)簽...

大家感興趣的內(nèi)容