1 Mysql 分頁(yè)查詢(xún)sql 執(zhí)行原理疆液？
2，千萬(wàn)級(jí)數(shù)據(jù)mysql 分頁(yè)查詢(xún)?nèi)绾蝺?yōu)化
3陕贮，Mysql表連接底層實(shí)現(xiàn)原理
·
4堕油，nested_Loop Join(NLJ)與Block Nested-Loop(BNL)連表算法的區(qū)別
5，in/exist/count(*)count(1) count(列名)查詢(xún)優(yōu)化
6肮之，EXPLAIN 分析sql語(yǔ)句 type 最低滿(mǎn)足什么級(jí)別
7掉缺，為什么阿里巴巴官方手冊(cè)不推薦使用存儲(chǔ)過(guò)程。
8戈擒，阿里官方手冊(cè)mysql 索引優(yōu)化總結(jié)》
分頁(yè)查詢(xún)優(yōu)化：
select * from employees limit 10000,10;
select * from employees limit 100000,10;
select * from employees limit 1000000,10;
select * from employees limit 3000000,10;
select * from employees limit 3000000,10;
從表中只查詢(xún)10條數(shù)據(jù)眶明，實(shí)際上底層讀取了300000條數(shù)據(jù)，然后讀取最后10條
這種分頁(yè)查詢(xún)采用全表掃描的方式筐高，查詢(xún)效率極低搜囱。
1，根據(jù)主鍵且連續(xù)的主鍵排序的分頁(yè)查詢(xún)：
EXPLAIN select * from employees where id>3000003 limit 10;
原理： 根據(jù)主鍵索引id排除< 3000003 柑土，取后10條數(shù)據(jù)避免全表掃描
缺點(diǎn)： 如果主鍵id不連續(xù) 蜀肘，可能無(wú)法實(shí)現(xiàn)效果。
2稽屏，非主鍵索引方式分頁(yè)優(yōu)化：
select * from employees order by id limit 100000,10;
SELECT * from employees e inner join (
select id from employees a order by a.id limit 100000,10 ) ed on e.id=ed.id
使用復(fù)合索引嵌套子查詢(xún)扮宠，效率可以提高一半。
阿里巴巴手冊(cè)：
數(shù)據(jù)量超過(guò)500萬(wàn)或者大于2g的時(shí)候建議分表
count(*) 優(yōu)化：
臨時(shí)關(guān)閉mysql 的查詢(xún)緩存狐榔，為了查看sql多次執(zhí)行的真實(shí)時(shí)間坛增。
set global query_cache_size=0;
set global query_cache_type=0;
1,count(field) 不包含字段值為null 的值获雕；
2，count() 包含字段為null 的值收捣；
3届案，select() 與select(1) 在InnnDB 中性能沒(méi)有任何區(qū)別，處理方式相同罢艾。
5.6版本：
1, select count(1) from employees; 使用輔助聯(lián)合索引計(jì)數(shù)
2楣颠， select count(id) from employees; 使用輔助索引計(jì)數(shù)
3， select count(name) from employees; 使用輔助索引計(jì)數(shù)
4昆婿， select count（） from employees; 使用輔助索引計(jì)數(shù)
5球碉， select count(id) from employees force index (PRIMARY) 使用主鍵索引；
從效率上看： count(1) ==count()>count(name)>count(id)
因?yàn)橹麈I索引的id 對(duì)應(yīng)的葉子節(jié)點(diǎn)中存放data 數(shù)據(jù)仓蛆，加載內(nèi)存中計(jì)數(shù)的時(shí)候比較慢睁冬。 推薦使用 count(*)；
在mysql 5.7 count(*) 會(huì)選擇聚集索引看疙，進(jìn)行一次內(nèi)部handler函數(shù)調(diào)用豆拨，即可快速獲得該表總數(shù)，執(zhí)行計(jì)劃Extra 顯示 select tables optimized away SELECT 操作已經(jīng)優(yōu)化到不能再優(yōu)化
如果聚集索引比較大(或者說(shuō)表數(shù)據(jù)量比較大)能庆。沒(méi)有完全加載到buffer pool 中的話(huà),MYSQL5.7的查詢(xún)方式有可能反而更慢施禾， 還不如原先放方式 MYSQL5.6
EXPLAIN select count(1) from employees;
EXPLAIN select count(id) from employees;
EXPLAIN select count(name) from employees;
EXPLAIN select count(*) from employees;
EXPLAIN select count(id) from employees force index(PRIMARY) 強(qiáng)制使用聚集索引
聚集索引和非聚集索引的區(qū)別：
聚集索引： 就是主鍵id 非聚集索引就是自定義的userName 字段
其中聚集索引 clustered index(id) , 非聚集索引index (UserName)
InnoDB 表中在沒(méi)有默認(rèn)主鍵的情況下會(huì)生成一個(gè)6 byte 空間的自增長(zhǎng)主鍵，可以用
select _rowid from table 查詢(xún)的是對(duì)應(yīng)的主鍵值 select _rowid from employees
常見(jiàn)的優(yōu)化方案：
1搁胆，對(duì)于 myisam 存儲(chǔ)引擎的表做不帶where 條件的count 查詢(xún)性能是很高的弥搞，因?yàn)閙ysiam 存儲(chǔ)的表總行會(huì)被mysql 存儲(chǔ)在磁盤(pán)上，查詢(xún)不需要操作
2 show table status
3, 使用Redis set key 記錄表總數(shù)渠旁。
Join 關(guān)聯(lián)表查詢(xún)
EXPLAIN select * from mayikt_1 left join mayikt_2 on mayikt_1.t1= mayikt_2.t1;
EXPLAIN select * from mayikt_2 left join mayikt_1 on mayikt_1.t1= mayikt_2.t1;
EXPLAIN select * from mayikt_2 right join mayikt_1 on mayikt_1.t1= mayikt_2.t1;
Nested-Loop Join(NLJ) 嵌套循環(huán)連接 算法
Block Nested-Loop Join (BNL) 基于塊的嵌套循環(huán)連接 算法
Nested-Loop Join 嵌套循環(huán)連接 算法（主鍵關(guān)聯(lián)查詢(xún)）
EXPLAIN select * from mayikt_1 inner join mayikt_2 on mayikt_1.t1= mayikt_2.t1;
全表掃描查詢(xún)到mayikt_2 表中索引的數(shù)據(jù)攀例，在根據(jù) mayikt2 中的t1 索引字段數(shù)據(jù)查詢(xún)mayikt1 索引字段中的數(shù)據(jù)，總共掃描2000行
驅(qū)動(dòng)表 mayikt2(小表)被驅(qū)動(dòng)表： mayikt_1(大表)
優(yōu)化器一般會(huì)選擇小表做驅(qū)動(dòng)表顾腊，所以使用 inner join 時(shí) 粤铭，排在后面的表并不一定
Block Nested-Loop Join 基于塊的嵌套循環(huán)連接 算法（非索引關(guān)聯(lián)查詢(xún)）
關(guān)聯(lián)查詢(xún)使用的是 BNL 算法：
EXPLAIN select * from mayikt_2 straight_join mayikt_1 on mayikt_1.t2= mayikt_2.t2;
原理：
1.將mayikt2表中所有的數(shù)據(jù)放入到j(luò)oin_buffer 中
2.在讀取mayikt1表中每行數(shù)據(jù)與join_buffer中數(shù)據(jù)實(shí)現(xiàn)匹配關(guān)聯(lián)
3.最后在返回查詢(xún)的數(shù)據(jù)
1000*32869次 查詢(xún)效率極低。
有索引是情況下 NLJ 算法比BNL 算法性能要高杂靶；
對(duì)于關(guān)聯(lián)sql的優(yōu)化：
1梆惯， 超過(guò)三個(gè)表禁止join, 需要join 的字段 ，數(shù)據(jù)類(lèi)型必須一致吗垮，多表關(guān)聯(lián)查詢(xún)時(shí)垛吗，保證被關(guān)聯(lián)的字段需要有索引，盡量使用NLJ 算法抱既；
2职烧， 小表驅(qū)動(dòng)大表 ，寫(xiě)多表連接sql 時(shí)如果明確知道那張表是小表可以用 straight_join 寫(xiě)法固定方式 防泵，省去mysql 優(yōu)化器自己判斷時(shí)間蚀之。
EXPLAIN select * from mayikt_2 straight_join mayikt_1 on mayikt_1.t2= mayikt_2.t2;
in 和exist 優(yōu)化：
也是需要遵循原則；
原則： 小表驅(qū)動(dòng)大表捷泞，即小的數(shù)據(jù)集驅(qū)動(dòng)大的數(shù)據(jù)集
如果 mayikt_b 中的數(shù)據(jù)小于mayikt_a 的數(shù)據(jù)可以使用in
select * from mayikt_a where id in (select id from mayikt_b)
底層類(lèi)似于這樣實(shí)現(xiàn)
for(select id from mayikt_B){
select * from mayikt_a where mayikt_a.id = mayikt_b.id
}
如果mayikt_a中的數(shù)據(jù)小于mayikt_b數(shù)據(jù)可以使用exists
select * from mayikt_a where exists (select id from mayikt_b where mayikt_b.id = mayikt_a.id)
for(select * from mayikt_a){
select * from mayikt_a where mayikt_b.id = mayikt_a.id
}
1 mysql 索引優(yōu)化原則：
2 核心點(diǎn)： B+樹(shù)足删，優(yōu)化原則： 常見(jiàn)方式，分頁(yè)锁右，排序失受，連表， count(*)
3 MYSQL 索引底層采用B+樹(shù)咏瑟； 減少磁盤(pán)IO操作拂到，支持高效的范圍查詢(xún)：
4 優(yōu)化原則：
核心： 先定位慢查詢(xún)，在通過(guò)慢查詢(xún)?nèi)罩疚募? 分析sql 語(yǔ)句码泞；
分析sql 語(yǔ)句工具： ExPLAIN/trace 工具
EXPLAIN type 滿(mǎn)足級(jí)別： type最低 滿(mǎn)足 range 范圍查詢(xún)級(jí)別
防止索引失效兄旬，避免全表掃描；
5余寥， 優(yōu)化方案：
常見(jiàn)方案：
常見(jiàn)方式领铐，分頁(yè)，排序宋舷，連表绪撵，count(*)
1，必須遵循最佳左前綴原則 防止索引失效
2祝蝠，盡量使用覆蓋索引 查詢(xún)列都是加上索引 音诈，減少select *
3，is null 绎狭，is not null 索引會(huì)失效细溅，空值用專(zhuān)門(mén)特定的常量值定義； dfe
4坟岔， like 模糊查詢(xún)遵循最佳左前綴原則 谒兄，使用復(fù)合索引模糊查詢(xún)
5，排序相關(guān)： 最佳左前綴原則社付，避免 filesort
6, 分頁(yè)查詢(xún) 承疲， 根據(jù) where id 條件過(guò)濾 offset 或者使用子查詢(xún)先定位id， 在查詢(xún)效率可以提高一半鸥咖，但是如果數(shù)據(jù)量大于500萬(wàn)的情況下建議使用分表燕鸽。
7，連表查詢(xún) ： 小表驅(qū)動(dòng)大表數(shù)據(jù)避免全表掃描啼辣，超過(guò)三張表禁止使用join
8啊研，count(*) 查詢(xún)優(yōu)化 ，輔助索引count 比主鍵索引count
效率高； mysql5.7 主鍵id count mysql5.6 輔助索引 count
