MySQL邏輯架構(gòu)整體分為三層，最上層為客戶端層，并非MySQL所獨有，諸如：連接處理、授權(quán)認(rèn)證、安全等功能均在這一層處理。
MySQL大多數(shù)核心服務(wù)均在中間這一層，包括查詢解析、分析、優(yōu)化、緩存、內(nèi)置函數(shù)(比如：時間、數(shù)學(xué)、加密等函數(shù))。所有的跨存儲引擎的功能也在這一層實現(xiàn)：存儲過程、觸發(fā)器、視圖等。
最下層為存儲引擎，其負(fù)責(zé)MySQL中的數(shù)據(jù)存儲和提取。和Linux下的文件系統(tǒng)類似，每種存儲引擎都有其優(yōu)勢和劣勢。中間的服務(wù)層通過API與存儲引擎通信，這些API接口屏蔽了不同存儲引擎間的差異。
MySQL查詢過程
客戶端/服務(wù)端通信協(xié)議
在任一時刻，要么是服務(wù)器向客戶端發(fā)送數(shù)據(jù)，要么是客戶端向服務(wù)器發(fā)送數(shù)據(jù)，這兩個動作不能同時發(fā)生。一旦一端開始發(fā)送消息，另一端要接收完整個消息才能響應(yīng)它。
客戶端用一個單獨的數(shù)據(jù)包將查詢請求發(fā)送給服務(wù)器，所以當(dāng)查詢語句很長的時候，需要設(shè)置max_allowed_packet參數(shù)。但是需要注意的是，如果查詢實在是太大，服務(wù)端會拒絕接收更多數(shù)據(jù)并拋出異常。
與之相反的是，服務(wù)器響應(yīng)給用戶的數(shù)據(jù)通常會很多，由多個數(shù)據(jù)包組成。但是當(dāng)服務(wù)器響應(yīng)客戶端請求時，客戶端必須完整的接收整個返回結(jié)果。因而在開發(fā)中，盡量保持查詢簡單且只返回必需的數(shù)據(jù)，減小通信間數(shù)據(jù)包的大小和數(shù)量是一個非常好的習(xí)慣，這也是查詢中盡量避免使用SELECT *以及加上LIMIT限制的原因之一。
查詢緩存
查詢時會先去緩存讀取數(shù)據(jù)，如果有有效緩存數(shù)據(jù)則會直接取緩存而不是真的去查。
如果兩個查詢實質(zhì)上需要查找的東西一樣，那最好使用完全相同的SQL語句，在緩存有效的情況下能夠大大加速查詢速度。
如果查詢中包含任何用戶自定義函數(shù)、存儲函數(shù)、用戶變量、臨時表、mysql庫中的系統(tǒng)表，其查詢結(jié)果
都不會被緩存。
既然是緩存，就會失效，那查詢緩存何時失效呢？MySQL的查詢緩存系統(tǒng)會跟蹤查詢中涉及的每個表，如果這些表（數(shù)據(jù)或結(jié)構(gòu)）發(fā)生變化，那么和這張表相關(guān)的所有緩存數(shù)據(jù)都將失效。正因為如此，在任何的寫操作時，MySQL必須將對應(yīng)表的所有緩存都設(shè)置為失效。如果查詢緩存非常大或者碎片很多，這個操作就可能帶來很大的系統(tǒng)消耗，甚至導(dǎo)致系統(tǒng)僵死一會兒。而且查詢緩存對系統(tǒng)的額外消耗也不僅僅在寫操作，讀操作也不例外
任何的查詢語句在開始之前都必須經(jīng)過檢查，即使這條SQL語句永遠(yuǎn)不會命中緩存
如果查詢結(jié)果可以被緩存，那么執(zhí)行完成后，會將結(jié)果存入緩存，也會帶來額外的系統(tǒng)消耗
基于此，我們要知道并不是什么情況下查詢緩存都會提高系統(tǒng)性能，緩存和失效都會帶來額外消耗，只有當(dāng)緩存帶來的資源節(jié)約大于其本身消耗的資源時，才會給系統(tǒng)帶來性能提升。如果系統(tǒng)確實存在一些性能問題，可以嘗試打開查詢緩存，并在數(shù)據(jù)庫設(shè)計上做一些優(yōu)化
用多個小表代替一個大表，注意不要過度設(shè)計
批量插入代替循環(huán)單條插入
理控制緩存空間大小，一般來說其大小設(shè)置為幾十兆比較合適
可以通過
SQL_CACHE和SQL_NO_CACHE來控制某個查詢語句是否需要進行緩存不要輕易打開查詢緩存，特別是寫密集型應(yīng)用。如果你實在是忍不住，可以將
query_cache_type設(shè)置為DEMAND，這時只有加入SQL_CACHE的查詢才會走緩存，其他查詢則不會，這樣可以非常自由地控制哪些查詢需要被緩存
語法解析和預(yù)處理
MySQL通過關(guān)鍵字將SQL語句進行解析，并生成一顆對應(yīng)的解析樹。這個過程解析器主要通過語法規(guī)則來驗證和解析。比如SQL中是否使用了錯誤的關(guān)鍵字或者關(guān)鍵字的順序是否正確等等。預(yù)處理則會根據(jù)MySQL規(guī)則進一步檢查解析樹是否合法。
查詢優(yōu)化
經(jīng)過前面的步驟生成的語法樹被認(rèn)為是合法的了，并且由優(yōu)化器將其轉(zhuǎn)化成查詢計劃。
MySQL使用基于成本的優(yōu)化器，它嘗試預(yù)測一個查詢使用某種執(zhí)行計劃時的成本，并選擇其中成本最小的一個。在MySQL可以通過查詢當(dāng)前會話的last_query_cost的值來得到其計算當(dāng)前查詢的成本。
查詢執(zhí)行引擎
在完成解析和優(yōu)化階段以后，MySQL會生成對應(yīng)的執(zhí)行計劃，查詢執(zhí)行引擎根據(jù)執(zhí)行計劃給出的指令逐步執(zhí)行得出結(jié)果。
返回結(jié)果給客戶端
結(jié)果集返回客戶端是一個增量且逐步返回的過程。有可能MySQL在生成第一條結(jié)果時，就開始向客戶端逐步返回結(jié)果集了。這樣服務(wù)端就無須存儲太多結(jié)果而消耗過多內(nèi)存，也可以讓客戶端第一時間獲得返回結(jié)果。
如果查詢緩存被打開且這個查詢可以被緩存，MySQL也會將結(jié)果存放到緩存中。
MySQL優(yōu)化匯總
謹(jǐn)慎使用select *
會讓優(yōu)化器無法完成索引覆蓋掃描這類優(yōu)化，而且還會增加額外的I/O、內(nèi)存和CPU的消耗
當(dāng)然合理地使用能夠提高代碼的復(fù)用性，要根據(jù)實際情況使用
SELECT * 和 SELECT 所有列，兩者差別幾乎可忽略。
是否掃描的太多額外的記錄
如果需要掃描上千上萬條數(shù)據(jù)，但最后只返回幾條結(jié)果，則考慮優(yōu)化
看看能否改表結(jié)構(gòu)。例如使用匯總表
看看獲取數(shù)據(jù)結(jié)果的方式是否最優(yōu)，獲取路勁是否已經(jīng)是最短
使用覆蓋索引，把所有需要的列都放到索引中，以減少返回表中對應(yīng)行中取數(shù)據(jù)的步驟
LIMIT 1可以避免全表掃描，找到對應(yīng)結(jié)果就不會再繼續(xù)掃描了
切分某些SQL語句
MySQL從設(shè)計上讓連接和斷開都很輕量，在一般服務(wù)器上可以支持每秒超過10萬的查詢，所以在某些場景下可以將一個大的查詢語句切分成多個小的查詢語句
對于全量數(shù)據(jù)查詢變成分頁。假如一張表中有數(shù)千萬條數(shù)據(jù)，一次select all，肯定是不行的。可以換成一次取一部分，把一次的壓力分?jǐn)?/p>
刪除大量舊數(shù)據(jù)的時候，不要一個大的語句一次性清完，推薦 一次刪一萬條。如果用一個大的語句一次性完成的話，可能需要一次鎖住大量數(shù)據(jù)，占滿大量日志事務(wù)，讓Mysql停在那兒了，為避免這種情況發(fā)生，最好一次性刪除一萬條左右的數(shù)據(jù)，然后每次刪完暫停一會兒再操作，將服務(wù)器上的一次性壓力分散
慎用join操作
推薦先從一張表查出id列表，然后對另一張表使用where in查詢
讓應(yīng)用的緩存(redis、memcache等)更高效。例如在第一張表中查詢出部分
id了，如果命中了緩存，就可以省去一條where in語句了更容易應(yīng)對業(yè)務(wù)的發(fā)展，方便對數(shù)據(jù)庫進行拆分，更容易做到高性能和高擴展
對
where in中的id進行升序排序后，查詢效率比join的隨機關(guān)聯(lián)更高效減少多余的查詢。在應(yīng)用層中兩次查詢，意味著對某條記錄應(yīng)用只需要查詢一次，而使用
join可能需要重復(fù)的掃描訪問一部分?jǐn)?shù)據(jù)單張表查詢可以減少鎖的競爭
假如非用不可，可以采用以下方式來優(yōu)化確保
ON或者using子句中的列上有索引確保任何的
group by和order by中的表達(dá)式只涉及到一個表中的列
OR改寫成IN
OR的效率是n級別，IN的效率是log(n)級別，in的個數(shù)建議控制在200以內(nèi)
在性能要求比較高的場景中，杜絕查詢中使用臨時表
MySQL的臨時表是沒有任何索引的，使用臨時表一般都意味著性能比較低,因此在對性能要求比較高的場景中，最好不要使用帶有臨時表的操作
未帶索引的字段上的
group by操作UNION查詢查詢語句中的子查詢
部分
order by操作，例如distinct函數(shù)和order by一起使用且distinct和order by同一個字段。再例如某些情況下group by和order by字段不同
具體是否用到臨時表，可以通過explain來查看，查看Extra列的結(jié)果，如果出現(xiàn)Using temporary則需要注意
在夠用的前提下選擇占用空間最小的數(shù)據(jù)結(jié)構(gòu)
TIMESTAMP使用4個字節(jié)存儲空間，DATETIME使用8個字節(jié)存儲空間。因而，TIMESTAMP只能表示1970 - 2038年通常來講，沒有太大的必要使用
DECIMAL數(shù)據(jù)類型。即使是在需要存儲財務(wù)數(shù)據(jù)時，仍然可以使用BIGINT。比如需要精確到萬分之一，那么可以將數(shù)據(jù)乘以一百萬然后使用BIGINT存儲。這樣可以避免浮點數(shù)計算不準(zhǔn)確和DECIMAL精確計算代價高的問題對整形設(shè)置寬度不會影響占用的空間
大多數(shù)情況下沒有使用枚舉類型的必要，其中一個缺點是枚舉的字符串列表是固定的，添加和刪除字符串（枚舉選項）必須使用
ALTER TABLE（如果只只是在列表末尾追加元素，不需要重建表）schema的列不要太多。原因是存儲引擎的API工作時需要在服務(wù)器層和存儲引擎層之間通過行緩沖格式拷貝數(shù)據(jù)，然后在服務(wù)器層將緩沖內(nèi)容解碼成各個列，這個轉(zhuǎn)換過程的代價是非常高的。如果列太多而實際使用的列又很少的話，有可能會導(dǎo)致CPU占用過高
大表
ALTER TABLE非常耗時，MySQL執(zhí)行大部分修改表結(jié)果操作的方法是用新的結(jié)構(gòu)創(chuàng)建一個張空表，從舊表中查出所有的數(shù)據(jù)插入新表，然后再刪除舊表。尤其當(dāng)內(nèi)存不足而表又很大，而且還有很大索引的情況下，耗時更久
NOT NULL不會帶來多少性能提升
應(yīng)當(dāng)用程序來保證非空
索引相關(guān)優(yōu)化
使用表達(dá)式或是函數(shù)來表示參數(shù)會導(dǎo)致SQL無法使用索引
如果列很長，通?？梢运饕_始的部分字符，這樣可以有效節(jié)約索引空間，從而提高索引效率
如果篩選條件中有多列有索引，應(yīng)當(dāng)把篩選性強的條件放在前面
如果篩選條件中有很多列索引，各個索引的結(jié)果互相合并也會帶來CPU壓力
避免同時使用多個范圍條件，多于一個的范圍條件會導(dǎo)致MySQL無法使用索引
如果你要查詢的列都有索引，那就只取這幾個列，查詢效率會很高
不要在同一列上按照相同的順序創(chuàng)建的相同類型的索引
大多數(shù)情況下建議擴展已有索引而不是新建索引
定期刪除一些長時間未使用過的索引
應(yīng)盡量避免在
WHERE子句中對字段進行NULL值判斷，否則將導(dǎo)致引擎放棄使用索引而進行全表掃描盡量不用
UNIQUE，由程序保證約束
特定類型查詢優(yōu)化
COUNT()查詢
如果指定了列，則會統(tǒng)計列值的數(shù)量，統(tǒng)計列值數(shù)量時不會計算值為
NULL的列COUNT(*)會直接統(tǒng)計行數(shù)，性能比統(tǒng)計列值數(shù)量要好如果業(yè)務(wù)不要求很精確的count值可以使用
EXPLAIN來近似，執(zhí)行EXPLAIN并不需要真正地去執(zhí)行查詢，所以成本非常低
關(guān)聯(lián)查詢
當(dāng)前MySQL關(guān)聯(lián)執(zhí)行的策略非常簡單，它對任何的關(guān)聯(lián)都執(zhí)行嵌套循環(huán)關(guān)聯(lián)操作，即先在一個表中循環(huán)取出單條數(shù)據(jù)，然后在嵌套循環(huán)到下一個表中尋找匹配的行，依次下去，直到找到所有表中匹配的行為為止。然后根據(jù)各個表匹配的行，返回查詢中需要的各個列。
在大數(shù)據(jù)場景下，表與表之間通過一個冗余字段來關(guān)聯(lián)，要比直接使用JOIN有更好的性能
盡量確保
ON和USING字句中的列上有索引確保任何的
GROUP BY和ORDER BY中的表達(dá)式只涉及到一個表中的列，這樣MySQL才有可能使用索引來優(yōu)化在關(guān)聯(lián)查詢的第二張表的關(guān)聯(lián)列上建立索引，第一張表的關(guān)聯(lián)列不必
LIMIT分頁
當(dāng)偏移量非常大的時候，比如：LIMIT 10000 20這樣的查詢，MySQL需要查詢10020條記錄然后只返回20條記錄，前面的10000條都將被拋棄，這樣的代價非常高。
分兩次查詢
先用覆蓋索引查出真正需要的id，再查這幾個id的所有字段，能夠大大減少回表次數(shù)，只回表真正需要的行
SELECT * FROM `operation_log` LIMIT 34000, 10;
從上面改成下面，此優(yōu)化在3.4w數(shù)據(jù)量的情況下已經(jīng)能夠有明顯性能差距
SELECT * FROM `operation_log` as a, (SELECT id FROM operation_log LIMIT 34000, 10) as b WHERE a.id=b.id;
利用betweenand和主鍵索引
利用主鍵自增id，我們?nèi)绻懒朔猪摰纳线吔?，可以寫?/p>
select xxx,xxx from table_name where id between xxxxx and xxxx;
直接從指定位置開始掃描
如果可以使用書簽記錄上次取數(shù)據(jù)的位置，那么下次就可以直接從該書簽記錄的位置開始掃描，這樣就可以避免使用OFFSET，比如
SELECT id FROM t LIMIT 10000, 10;
改為：
SELECT id FROM t WHERE id > 10000 LIMIT 10;
其他優(yōu)化的辦法還包括使用預(yù)先計算的匯總表，或者關(guān)聯(lián)到一個冗余表，冗余表中只包含主鍵列和需要做排序的列。
UNION去重合并/UNION ALL不去重合并
MySQL處理UNION的策略是先創(chuàng)建臨時表，然后再把各個查詢結(jié)果插入到臨時表中，最后再來做查詢。因此很多優(yōu)化策略在UNION查詢中都沒有辦法很好的時候。經(jīng)常需要手動將WHERE、LIMIT、ORDER BY等字句“下推”到各個子查詢中，以便優(yōu)化器可以充分利用這些條件先優(yōu)化。
除非確實需要服務(wù)器去重，否則就一定要使用UNION ALL，如果沒有ALL關(guān)鍵字，MySQL會給臨時表加上DISTINCT選項，這會導(dǎo)致整個臨時表的數(shù)據(jù)做唯一性檢查，這樣做的代價非常高。當(dāng)然即使使用ALL關(guān)鍵字，MySQL總是將結(jié)果放入臨時表，然后再讀出，再返回給客戶端。雖然很多時候沒有這個必要，比如有時候可以直接把每個子查詢的結(jié)果返回給客戶端。
其他
SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM titles; 計算某列的選擇性，值越大選擇性越強
主鍵選用自增ID和UUID各有利弊
自增 ID 很容易會被暴力破解，數(shù)據(jù)遷移的時候，特別是發(fā)生表格合并這種操作的時候，會不可避免地存在沖突。UUID 則能夠保證唯一性，徹底避免沖突
自增字段的長度較 UUID 小很多，這會對檢索的性能有較大影響。Innodb 引擎進行數(shù)據(jù)檢索時，也是先根據(jù)索引找到主鍵，然后根據(jù)主鍵找到記錄；這樣在主鍵長度短的情況下，會有較好的讀性能
自增 ID 并且高并發(fā)的情況下，競爭自增鎖會降低數(shù)據(jù)庫的吞吐能力。UUID 則能夠在應(yīng)用層生成 UUID，提高數(shù)據(jù)庫的吞吐能力
InnoDB 中表數(shù)據(jù)是按照主鍵順序存放的，在寫入數(shù)據(jù)時候如果發(fā)生了隨機 IO，那么就會頻繁地移動磁盤塊。當(dāng)數(shù)據(jù)量大的時候，寫的短板將非常明顯。自增 ID 中新增的數(shù)據(jù)可以默認(rèn)按序排列，對于性能有很大的提升；UUID 的主鍵之間沒有順序規(guī)律
配置項調(diào)優(yōu)
1、innodb_buffer_pool_size=500M
太小，嚴(yán)重影響數(shù)據(jù)庫性能。服務(wù)器共500G內(nèi)存，但只給mysql緩沖池分配了500M，非常影響數(shù)據(jù)庫性能，且造成資源浪費。建議設(shè)置為服務(wù)器內(nèi)存的60%。
2、expire_logs_days=7
太短，只能保留7天的binlog，只能恢復(fù)7天內(nèi)的任意數(shù)據(jù)。建議設(shè)置為參數(shù)文件里被覆蓋的90天的設(shè)置。
3、long_query_time=10
太長，建議設(shè)置為2秒，讓慢查詢?nèi)罩居涗浉嗟穆樵儭?/p>
4、transaction-isolation = read-committed
建議注釋掉，使用數(shù)據(jù)庫默認(rèn)的事務(wù)隔離級別
5、innodb_lock_wait_timeout = 5
設(shè)置得太小，會導(dǎo)致事務(wù)因鎖等待超過5秒，就被回滾。建議和云門戶設(shè)置得保持一致，云門戶大小為120。
6、autocommit = 0
建議改為mysql默認(rèn)的自動提交(autocommit=1)，提升性能，方便日常操作。
其他
varchar存儲時會按實際內(nèi)容大小來存儲，在實際內(nèi)容不變的情況下，字段長度上限的變化并不會真的影響占用空間，但還是建議以實際使用情況來設(shè)置字段長度上限，因為mysql建立索引時，如果沒有限制索引長度，則會默認(rèn)按字段長度建立索引，所以字段長度余越短，索引占用的空間也越小，另外，所有列長度加起來不能超過65535bytes
MySQL的Innodb引擎表索引字段長度的限制為767字節(jié)，因此對于多字節(jié)字符集的大字段或者多字段組合，創(chuàng)建索引時會出現(xiàn)1709錯誤，可參考https://help.aliyun.com/knowledge_detail/41707.html解決
