一：簡介

主要想寫一下常見的一些操作的原理按声，比如order by、group by、join仆潮、union等等；

二：排序 order by

1.對數(shù)據(jù)進行排序是一種常見的操作遣臼，用于將更關(guān)心的數(shù)據(jù)放到最前面性置；排序若是用的不夠好，也常帶來很差的效率揍堰，比如filesort等鹏浅；當(dāng)然在這里索引也是起到了至關(guān)重要的作用，比如排序時屏歹，排序的字段是索引隐砸、覆蓋索引等情況；
2.排序的三種算法蝙眶，排序字段為col季希，查詢字段為cola、colb、colc：
? ?1）將符合條件的數(shù)據(jù)胖眷，組合成(primary,col)的樣式武通，然后放入sort buffer中去排序，若是sort buffer不能放下珊搀，則使用file sort進行歸并排序冶忱；最后再根據(jù)排序后的(primary,col)，去DB里面找(cola,colb,colc)境析；
? ?2）優(yōu)化排序：將符合條件的數(shù)據(jù)組織成(cola,colb,colc,col)的形式囚枪，然后再根據(jù)col進行排序；
? ?3）針對limit進行優(yōu)化：5.6 版本針對 Order by limit M劳淆，N 進行優(yōu)化链沼，采用堆來進行優(yōu)化；
3.排序的優(yōu)化：索引
? ?在查詢的SQL中沛鸵，效率問題很難離開索引括勺，這里利用索引來優(yōu)化的方式有兩種：
? ?1）排序字段(col1,col2,col3)是一個索引，并且順序保持最左匹配曲掰，這樣就可以利用索引的有序性疾捍；
? ?2）覆蓋索引，select的字段(cola,colb,colc)是一個索引栏妖，這樣排序后就不用再次回表乱豆；
4.?sort_buffer_size參數(shù)：
? ?該參數(shù)是排序Buffer的大小，由上面可知吊趾，當(dāng)Buffer小于需要排序的內(nèi)容時宛裕，就會文件排序；但該參數(shù)是connection級別的參數(shù)论泛，也就是說增大該參數(shù)后揩尸，所有的連接的buffer都會增大，這會很耗內(nèi)存屁奏；
5.總結(jié)
? ?order by的情況有很多岩榆，優(yōu)化的思路也很多，比如調(diào)參了袁、減少不必要的參數(shù)朗恳、索引湿颅、排序的順序ASC载绿、DESC等，在這里只能簡要介紹下油航，如果需要調(diào)參崭庸，還需要更細的去看；

三：Group By

1.含義：按照規(guī)則對數(shù)據(jù)進行分組，并且常會和聚合函數(shù)怕享、having一起使用执赡；
? ?性能：受索引的影響也很大，如果不能使用索引的話函筋，只能使用全表掃描進行分組了沙合；

SELECT column, group_function,...FROM table
[WHERE condition]
GROUP BY group_by_expression
[HAVING group_condition];

2.having 、where跌帐、order by首懈、group by 、select的順序和限制
? ?掌握了執(zhí)行的流程谨敛，這些就會順其自然究履；
? ?1）where 語句是最先執(zhí)行的，用在過濾原始數(shù)據(jù)上面脸狸；這里再多說一下最仑，對于組合索引(sid,cid)，select?sid,cid from xxx而言, order by update_time的type是ALL炊甲，order by sid泥彤，type是index；就是說mysql引擎會根據(jù)語句的情況選擇蜜葱，會找到一個包含select全景、order by的記錄；
? ?2）其次執(zhí)行 group by語句牵囤，即根據(jù)對應(yīng)的字段進行分組爸黄；
? ?3）利用having進行過濾，這里過濾的緯度是 組 揭鳞，將不合適的組過濾掉炕贵；
? ?4）select，從當(dāng)前的記錄集temp中選出對應(yīng)的column野崇，或者做一些聚合計算称开；
? ?5）由于在第一步中，選擇出來的記錄集temp中是有order by信息的乓梨，雖然在第四步?jīng)]有展示鳖轰，這時候再根據(jù)字段排序；
3.使用group by時select列的限制
? ? ? 1）在5.7版本之前扶镀，select后面可以跟任何合法的列蕴侣；
? ? ? 2）在oracle、sqlserver臭觉、db2和5.7之后昆雀，分組中select后面的列只能有2種：出現(xiàn)在group by后面的列辱志、使用聚合函數(shù)的列；
? ? ? 3）ONLY_FULL_GROUP_BY表示嚴格限制group by的列狞膘。
? ? ? 4）如果不嚴格限制的問題：對應(yīng)的數(shù)據(jù)是第一條數(shù)據(jù)揩懒，某種意義上是隨機的，所以選擇出來的字段的值也沒有意義挽封；
3.group by的三種形式：松散的索引已球、緊湊的索引、全表掃描
? ? Group by的過程辅愿，其實是一個找到所有元素和悦，然后進行分組的一個過程；那么就需要獲得column的所有數(shù)據(jù)渠缕，然后再進行分組鸽素，再進行聚合等操作；所以索引很關(guān)鍵亦鳞，分為用到索引馍忽、全表(臨時表)兩種，用到索引又可以細分：
? ? 1）松散的索引：是指分組時燕差，可以直接利用索引遭笋，并且利用索引的排序，只訪問索引中滿足條件的徒探，而不需要訪問索引中的所有值瓦呼；有點類似于explain中的ref類型；
? ? ? ? 要使用松散索引有一些條件测暗，感興趣的可以查找相關(guān)資料：見【參考】2
? ? 2）緊湊的索引：可以是全索引掃描或者是索引范圍掃描央串，這種情況下用不到最左前綴的索引；
? ? 3）全表：這種情況下碗啄，可能會用到temporary或者filesort质和，所以會比較耗時，比較耗內(nèi)存

四：distinct

? ? 1）功能：distinct是在查找的時候稚字，某一列只存在一個值饲宿，這里和Group by有些共同之處，都是將其根據(jù)col來分組胆描，distinct只取唯一的瘫想，group by會做一些聚合動作；
? ? 2）實現(xiàn)：distinct因為只需要取col字段昌讲，它會將全部內(nèi)容存儲在Hash結(jié)構(gòu)里面国夜，最后計算key的個數(shù)，是以空間換時間的方式剧蚣，時間復(fù)雜度為支竹；
? ? 3）Group by：因為Group是需要做聚合的，所以它需要先進行排序鸠按，然后再進行分組礼搁；
? ? 4）count distinct 、count group by效率對比：對不同的字段類型目尖，效率各有優(yōu)劣：首先數(shù)據(jù)越是離散馒吴，DISTINCT需要消耗的空間越大，效率也就越低瑟曲，此時GROUP BY的空間優(yōu)勢就顯現(xiàn)了饮戳；相反，數(shù)據(jù)越是集中洞拨，DISTINCT空間占用變小扯罐，時間優(yōu)勢就顯現(xiàn)出來了

五：or、in烦衣、union

? ? 1. or用來求多個條件的合集歹河，如 select id from student where grade > 90 or grade < 60;
? ? 2. union也可用來求多個條件的合集，其中union會對結(jié)果進行去重花吟，union all不去重秸歧；
? ? 3. in也算是一種多條件的合集，比如select id from student where grade in (99,100);
? ? 4. or使用索引的情況衅澈，在Mysql的index merged出現(xiàn)之前键菱，是不會使用索引的，所以在之前or的性能會比較差今布，我在項目中就遇到過一個or查詢7秒鐘的驚人SQL经备，改成union all之后，縮短到了100ms部默；
? ? ? 但是引入了index merged之后弄喘，會優(yōu)先考慮索引，并在索引之后對條件進行過濾甩牺；
? ? 5. union 對于索引字段蘑志，是可以使用索引的，所以對于一些or不能使用索引的情況下贬派，可以考慮使用union急但；
? ? 6. 對于簡單的in查詢，mysql是會走索引查詢的搞乏，但是in條件過多的時候波桩，索引就會失效，當(dāng)然因為mysql版本眾多请敦，也不斷的優(yōu)化镐躲，實際還是要以explain的結(jié)果為準(zhǔn)储玫；

六：join

? ?1. join是數(shù)據(jù)庫表與表直接，進行連接取值的一種方式萤皂，分為inner 撒穷、outer、full join裆熙；
? ?2. join和inner join是內(nèi)連接端礼，是取兩個表的交集；
? ?3.outer分為left和right： left join是取兩個表的交集之后入录，加上左表剩余的數(shù)據(jù)蛤奥，right與left類同；
? ?4.full join全連接僚稿，mysql不支持凡桥，在join的基礎(chǔ)上增加2表剩余的數(shù)據(jù)，缺少的用null補充蚀同；
? ?5.連接操作的語法：

select <row_list> from <left_table>
?<inner|left|right> join <right_table>
?on <join condition> where <where_condition>

? ?6. 這幾種 join 的操作唬血，都是在笛卡爾積的基礎(chǔ)上，進行擴充和篩選的：
? ? ? 1）from ：對left_table和right_table進行笛卡爾積唤崭，生成vtable1拷恨，行數(shù)left*right；
? ? ? 2）on：根據(jù) on 的條件谢肾，對vtable1進行過濾腕侄，然后生成vtable2；
? ? ? 3）join：根據(jù)join類型芦疏，對vtable2進行改造冕杠，inner不需要，outer需要將處理酸茴，比如left join需要將不存在于vtable2中的記錄分预，插入vtable2中，生成vtable3薪捍，其他字段補null笼痹；
? ? ? 4）where：對vtable3進行過濾，生成vtable4酪穿；
? ?7.原理：join的實現(xiàn)是用Nested Loop Join算法凳干，通常以前表為驅(qū)動表，來作為過濾條件到下一個表的查詢數(shù)據(jù)被济；
? ?8.優(yōu)化：
? ? ? 1）驅(qū)動表要選擇數(shù)據(jù)量小的表救赐；
? ? ? 2）對驅(qū)動表和被驅(qū)動表的字段，加上相應(yīng)的索引只磷；
? ? ? 3）如果被驅(qū)動表不能加索引经磅，考慮加大join buffer size

七：exists泌绣、in、not exists预厌、not in

? ? ? 1.exists和in都是集合查詢的一種阿迈，用來判斷某一行是都在集合之中；他們的實現(xiàn)原理是對表A的數(shù)據(jù)遍歷配乓，然后再去表B中去判斷是否滿足條件，它們的區(qū)別就在于怎么去表B惠毁；
? ? ? 2. 用法：select column from tableA where id in(exists) (select id from tableB);
? ? ? 3. in的場景：tableB的數(shù)據(jù)較小犹芹，在對表A遍歷之后，對將表B的被人加載到內(nèi)存鞠绰，所以如果tableB太大的話腰埂，是很難加載到內(nèi)存的；
? ? ? 4. exists場景：tableA的數(shù)據(jù)較小蜈膨，因為遍歷A之后屿笼，對表B進行exists，并不對exists進行內(nèi)存加載翁巍；所以當(dāng)tableA較大的時候驴一，比較耗時；
? ? ? 5. not in 灶壶、not exists肝断，盡量選擇 not exists，因為 not in 很少走索引驰凛；

八：not胸懈、 != 、< 恰响、> 趣钱、between

? ? ?對于這些操作，也可能會使用多音胚宦，關(guān)鍵還是看數(shù)據(jù)量等因素首有，mysql會去選擇，而不是不會使用索引枢劝；

九：limit?

? ? ?1.limit的常用用法為 limit offset rows绞灼，用來分頁操作；
? ? ?2.limit的原理：因為數(shù)據(jù)在磁盤上的存儲是隨機的呈野，所以去磁盤讀取數(shù)據(jù)是隨機讀取的：
? ? ? ? ?1）從數(shù)據(jù)庫表中讀取M條數(shù)據(jù)低矮，放入數(shù)據(jù)集中；(M主要受數(shù)據(jù)頁等因素影響)
? ? ? ? ?2）重復(fù)第一步被冒，直到數(shù)據(jù)集中存在? offset + rows 條數(shù)據(jù)军掂；
? ? ? ? ?3）根據(jù)offset拋棄前offset條數(shù)據(jù)轮蜕；
? ? ? ? ?4）返回剩下的rows條數(shù)據(jù)；
? ? ?3.limit的性能：由上可知蝗锥，limit的性能受制于?offset + rows的數(shù)量跃洛，當(dāng)offset越大，性能越差终议；
? ? ?4.性能優(yōu)化：主要思路有下面幾種：對于 select id from table limit 10000汇竭，10
? ? ? ? ?1）減少 offset的數(shù)目，比如穴张，select id from table where id >= 10000 limit 10;
? ? ? ? ?2)? 對于非主鍵的细燎，利用索引和子查詢，先利用查出一部分主鍵皂甘，然后利用主鍵去查找剩下的數(shù)據(jù)玻驻，比如：
select * from table where id in (select id from table where (name = xxx )) limit 10000 ,10;
? ? ? ? ?3）利用exists、join等對上述的in進行優(yōu)化偿枕，解決in在數(shù)據(jù)量大的時候的效率問題璧瞬；

十：主鍵

? ?1. mysql的主鍵在記錄的存儲是非常重要的，因為mysql在磁盤上的存儲是聚簇索引渐夸；
? ?2. Innodb對于不存在主鍵的處理辦法：會使用第一個非空的唯一索引作為聚簇索引嗤锉，如果沒有的話，Innodb會生成一個不可見的名為ROW_ID的列名為GEN_CLUSTER_INDEX的聚簇索引墓塌，它為一個6字節(jié)的自增數(shù)值档冬；
? ? ? ?在沒有聚簇索引的情況下， 會共享全局的序列桃纯，導(dǎo)致效率低下酷誓，所以一些DBA都會要求要有一個自增的Id作為聚簇索引；
? ?3.自增Id：Innodb是在內(nèi)存中存放下一個id态坦，只有在重啟的時候盐数，才重新進行計算；MyIsam是將下一個Id放在物理文件中伞梯；

十一：子查詢和join

1. 一個常見的優(yōu)化原則是：使用join來代替子查詢玫氢，連接的效率要好于子查詢；
2.子查詢：select column from table where id in (select goods_id from goods)
3.子查詢效率慢的原因：子查詢會創(chuàng)建谜诫、銷毀臨時表漾峡；
4.但事情也不是絕對的，類似于in和exist喻旷，join使用笛卡爾積生逸，然后進行過濾，而子查詢是對內(nèi)存中的數(shù)據(jù)進行遍歷，如果子查詢的數(shù)量很少槽袄，還是不錯的烙无；
5.原則上，對可用join替換的遍尺，盡量用join替換子查詢截酷；

十一：count(*)、count(1)乾戏、count(column)

? ? 1迂苛、count(1) and count(*)
? ?從執(zhí)行計劃上來說,兩者是一致的，我這里的type都是index鼓择；但是count(*)是SQL的標(biāo)準(zhǔn)三幻，也是做了優(yōu)化的，所以應(yīng)優(yōu)先選擇count(*)惯退；
? ?他們在語義上應(yīng)該是一致的赌髓，應(yīng)該都會選擇一個開銷比較小的索引从藤，來計算數(shù)量催跪；
? ? 2、count(1)夷野、 count(column)
? ? ?它們的區(qū)別是count(1)會統(tǒng)計表中所有的記錄數(shù)懊蒸，包含字段為null的記錄，而count(column)只記錄在表中出現(xiàn)的非null的次數(shù)悯搔；
? ? 他們的效率其實在column是索引的情況下骑丸，差距很小，因為使用索引妒貌，都是內(nèi)存類的操作通危，在非索引的情況下，差距就大了

參考文章：

1.https://database.51cto.com/art/201710/555357.htm
2.http://www.reibang.com/p/e5d962b13f44
3.https://juejin.im/post/5a449d3a518825519408eb1e
4.https://my.oschina.net/hosee/blog/1142926
5.http://www.reibang.com/p/6864abb4d885
6.http://www.reibang.com/p/efecd0b66c55