MySQL優(yōu)化框架

1. SQL語句優(yōu)化
2. 索引優(yōu)化
3. 數(shù)據(jù)庫結(jié)構(gòu)優(yōu)化
4. InnoDB表優(yōu)化
5. MyISAM表優(yōu)化
6. Memory表優(yōu)化
7. 理解查詢執(zhí)行計劃
8. 緩沖和緩存
9. 鎖優(yōu)化
10. MySQL服務(wù)器優(yōu)化
11. 性能評估
12. MySQL優(yōu)化內(nèi)幕

MySQL優(yōu)化需要在三個不同層次上協(xié)調(diào)進行：MySQL級別算凿、OS級別和硬件級別揽碘。MySQL級別的優(yōu)化包括表優(yōu)化、查詢優(yōu)化和MySQL服務(wù)器配置優(yōu)化等，而MySQL的各種數(shù)據(jù)結(jié)構(gòu)又最終作用于OS直至硬件設(shè)備哥纫，因此還需要了解每種結(jié)構(gòu)對OS級別的資源的需要并最終導(dǎo)致的CPU和I/O操作等，并在此基礎(chǔ)上將CPU及I/O操作需要盡量降低以提升其效率。

一蛀骇、 數(shù)據(jù)庫層面的優(yōu)化著眼點

1厌秒、是否正確設(shè)定了表結(jié)構(gòu)的相關(guān)屬性，尤其是每個字段的字段類型是否為最佳擅憔。同時鸵闪，是否為特定類型的工作組織使用了合適的表及表字段也將影響系統(tǒng)性能，比如暑诸，數(shù)據(jù)頻繁更新的場景應(yīng)該使用較多的表而每張表有著較少字段的結(jié)構(gòu)蚌讼，而復(fù)雜數(shù)據(jù)查詢或分析的場景應(yīng)該使用較少的表而每張表較多字段的結(jié)構(gòu)等。
2个榕、是否為高效進行查詢創(chuàng)建了合適的索引篡石。
3、是否為每張表選用了合適的存儲引擎西采，并有效利用了選用的存儲引擎本身的優(yōu)勢和特性凰萨。
4、是否基于存儲引擎為表選用了合適的行格式(row format)械馆。例如胖眷，壓縮表在讀寫操作中會降低I/O操作需求并占用較少的磁盤空間，InnoDB支持讀寫應(yīng)用場景中使用壓縮表霹崎，但MyISAM僅能在讀環(huán)境中使用壓縮表珊搀。
5、是否使用了合適的鎖策略尾菇，如在并發(fā)操作場景中使用共享鎖食棕，而對較高優(yōu)先級的需求使用獨占鎖等。同時错沽，還應(yīng)該考慮存儲引擎所支持的鎖類型簿晓。
6、是否為InnoDB的緩沖池千埃、MyISAM的鍵緩存以及MySQL查詢緩存設(shè)定了合適大小的內(nèi)存空間憔儿，以便能夠存儲頻繁訪問的數(shù)據(jù)且又不會引起頁面換出。

二放可、 操作系統(tǒng)和硬件級別的優(yōu)化著眼點：

1谒臼、是否為實際的工作負載選定了合適的CPU，如對于CPU密集型的應(yīng)用場景要使用更快速度的CPU甚至更多數(shù)量的CPU耀里，為有著更多查詢的場景使用更多的CPU等蜈缤。基于多核以及超線程(hyperthreading)技術(shù)冯挎，現(xiàn)代的CPU架構(gòu)越來越復(fù)雜底哥、性能也越來越強了，但MySQL對多CPU架構(gòu)的并行計算能力的利用仍然是有著不太盡如人意之處，尤其是較老的版本如MySQL 5.1之前的版本甚至無法發(fā)揮多CPU的優(yōu)勢趾徽。不過续滋，通常需要實現(xiàn)的CPU性能提升目標有兩類：低遲延和高吞吐量。低延遲需要更快速度的CPU孵奶，因為單個查詢只能使用一顆疲酌；而需要同時運行許多查詢的場景，多CPU更能提供更好的吞吐能力了袁，然而其能否奏效還依賴于實際工作場景朗恳，因為MySQL尚不能高效的運行于多CPU，并且其對CPU數(shù)量的支持也有著限制载绿。一般來說僻肖，較新的版本可以支持16至24顆CPU甚至更多。
2卢鹦、是否有著合適大小的物理內(nèi)存臀脏，并通過合理的配置平衡內(nèi)存和磁盤資源，降低甚至避免磁盤I/O〖阶裕現(xiàn)代的程序設(shè)計為提高性能通常都會基于局部性原理使用到緩存技術(shù)揉稚，這對于頻繁操作數(shù)據(jù)的數(shù)據(jù)庫系統(tǒng)來說尤其如此——有著良好設(shè)計的數(shù)據(jù)庫緩存通常比針對通用任務(wù)的操作系統(tǒng)的緩存效率更高。緩存可以有效地延遲寫入熬粗、優(yōu)化寫入搀玖，但并能消除寫入，并綜合考慮存儲空間的可擴展性等驻呐，為業(yè)務(wù)選擇合理的外部存儲設(shè)備也是非常重要的工作灌诅。
3、是否選擇了合適的網(wǎng)絡(luò)設(shè)備并正確地配置了網(wǎng)絡(luò)對整體系統(tǒng)系統(tǒng)也有著重大影響含末。延遲和帶寬是網(wǎng)絡(luò)連接的限制性因素猜拾，而常見的網(wǎng)絡(luò)問題如丟包等，即是很小的丟包率也會贊成性能的顯著下降佣盒。而更重要的還有按需調(diào)整系統(tǒng)中關(guān)網(wǎng)絡(luò)方面的設(shè)置挎袜，以高效處理大量的連接和小查詢。
4、是否基于操作系統(tǒng)選擇了適用的文件系統(tǒng)。實際測試表明大部分文件系統(tǒng)的性能都非常接近绝骚，因此，為了性能而苦選文件系統(tǒng)并不劃算全景。但考慮到文件系統(tǒng)的修復(fù)能力，應(yīng)該使用日志文件系統(tǒng)如ext3牵囤、ext4爸黄、XFS等滞伟。同時，關(guān)閉文件系統(tǒng)的某些特性如訪問時間和預(yù)讀行為馆纳，并選擇合理的磁盤調(diào)度器通常都會給性能提升帶來幫助诗良。
5汹桦、MySQL為響應(yīng)每個用戶連接使用一個單獨的線程鲁驶，再加內(nèi)部使用的線程、特殊目的線程以及其它任何由存儲引擎創(chuàng)建的線程等舞骆，MySQL需要對這些大量線程進行有效管理钥弯。Linux系統(tǒng)上的NPTL線程庫更為輕量級也更有效率。MySQL 5.5引入了線程池插件督禽，但其效用尚不明朗脆霎。

三、 使用InnoDB存儲引擎最佳實踐：

1狈惫、基于MySQL查詢語句中最常用的字段或字段組合創(chuàng)建主鍵睛蛛，如果沒有合適的主鍵也最好使用AUTO_INCRMENT類型的某字段為主鍵。
2胧谈、根據(jù)需要考慮使用多表查詢忆肾，將這些表通過外鍵建立約束關(guān)系。
3菱肖、關(guān)閉autocommit客冈。
4、使用事務(wù)(START TRANSACTION和COMMIT語句)組合相關(guān)的修改操作或一個整體的工作單元稳强，當然也不應(yīng)該創(chuàng)建過大的執(zhí)行單元场仲。
5、停止使用LOCK TABLES語句退疫，InnoDB可以高效地處理來自多個會話的并發(fā)讀寫請求渠缕。如果需要在一系列的行上獲取獨占訪問權(quán)限，可以使用SELECT ... FOR UPDATE鎖定僅需要更新的行褒繁。
6褐健、啟用innodb_file_per_table選項，將各表的數(shù)據(jù)和索引分別進行存放澜汤。
7蚜迅、評估數(shù)據(jù)和訪問模式是否能從InnoDB的表壓縮功能中受益(在創(chuàng)建表時使用ROW_FORMAT=COMPRESSED選項)，如果可以俊抵，則應(yīng)該啟用壓縮功能谁不。

四、 SQL語句優(yōu)化

EXPLAIN語句解析：

id:SELECT語句的標識符徽诲，一般為數(shù)字刹帕，表示對應(yīng)的SELECT語句在原始語句中的位置吵血。沒有子查詢或聯(lián)合的整個查詢只有一個SELECT語句，因此其id通常為1偷溺。在聯(lián)合或子查詢語句中蹋辅，內(nèi)層的SELECT語句通常按它們在原始語句中的次序進行編號。但UNION操作通常最后會有一個id為NULL的行挫掏，因為UNION的結(jié)果通常保存至臨時表中侦另，而MySQL需要到此臨時表中取得結(jié)果。

select_type:
即SELECT類型尉共，有如下值列表：
SIMPLE：簡單查詢褒傅，即沒有使用聯(lián)合或子查詢；
PRIMARY：UNION的最外圍的查詢或者最先進行的查詢袄友；
UNION：相對于PRIMARY殿托，為聯(lián)合查詢的第二個及以后的查詢；
DEPENDENT UNION：與UNION相同剧蚣，但其位于聯(lián)合子查詢中(即UNION查詢本身是子查詢)支竹；
UNION RESULT：UNION的執(zhí)行結(jié)果；
SUBQUERY：非從屬子查詢鸠按，優(yōu)化器通常認為其只需要運行一次礼搁；
DEPENDENT SUBQUERY：從屬子查詢，優(yōu)化器認為需要為外圍的查詢的每一行運行一次待诅，如用于IN操作符中的子查詢叹坦；
DERIVED：用于FROM子句的子查詢，即派生表查詢卑雁；

table:
輸出信息所關(guān)系到的表的表名募书，也有可能會顯示為如下格式：
<unionM,N>：id為M和N的查詢執(zhí)行聯(lián)合查詢后的結(jié)果；
<derivedN>：id為N的查詢執(zhí)行的結(jié)果集测蹲；

type:
MySQL官方手冊中解釋type的作用為“type of join(聯(lián)結(jié)的類型)”莹捡，但其更確切的意思應(yīng)該是“記錄(record)訪問類型”，因為其主要目的在于展示MySQL在表中找到所需行的方式扣甲。通常有如下所示的記錄訪問類型：
system: 表中僅有一行篮赢，是const類型的一種特殊情況；
const：表中至多有一個匹配的行琉挖，該行僅在查詢開始時讀取一次启泣，因此，該行此字段中的值可以被優(yōu)化器看作是個常量(constant)示辈；當基于PRIMARY KEY或UNIQUE NOT NULL字段查詢寥茫，且與某常量進行等值比較時其類型就為const，其執(zhí)行速度非撤椋快纱耻；
eq_ref：類似于const芭梯，表中至多有一個匹配的行，但比較的數(shù)值不是某常量弄喘，而是來自于其它表玖喘；ed_ref出現(xiàn)在PRIMARY KEY或UNIQUE NOT NULL類型的索引完全用于聯(lián)結(jié)操作中進行等值(=)比較時；這是除了system和const之外最好的訪問類型蘑志；
ref：查詢時的索引類型不是PRIMARY KEY或UNIQUE NOT NULL導(dǎo)致匹配到的行可能不惟一累奈，或者僅能用到索引的左前綴而非全部時的訪問類型；ref可被用于基于索引的字段進行=或<=>操作卖漫；
fulltext：用于FULLTEXT索引中用純文本匹配的方法來檢索記錄费尽。
ref_or_null：類似于ref赠群，但可以額外搜索NULL值羊始；
index_merge：使用“索引合并優(yōu)化”的記錄訪問類型，相應(yīng)地查描，其key字段(EXPLAIN的輸出結(jié)果)中會出現(xiàn)用到的多個索引突委，key_len字段中會出現(xiàn)被使用索引的最長長度列表；將多個“范圍掃描(range scan)”獲取到的行進行合并成一個結(jié)果集的操作即索引合并(index merge)冬三。
unique_subquery：用于IN比較操作符中的子查詢中進行的“鍵值惟一”的訪問類型場景中匀油，如 value IN (SELECT primary_key FROM single_table WHERE some_expr)；
index_subquery：類似于unique_subquery勾笆，但子查詢中鍵值不惟一敌蚜；
range：帶有范圍限制的索引掃描，而非全索引掃描窝爪，它開始于索引里的某一點弛车，返回匹配那個值的范圍的行；相應(yīng)地蒲每，其key字段(EXPLAIN的輸出結(jié)果)中會輸出所用到的索引纷跛，key_len字段中會包含用到的索引的最長部分的長度；range通常用于將索引與常量進行=邀杏、<>贫奠、>、>=望蜡、<唤崭、<=、IS NULL脖律、<=>谢肾、BETWEEN或IN()類的比較操作中；
index：同全表掃描(ALL)状您，只不過是按照索引的次序進行而不行的次序勒叠；其優(yōu)點是避免了排序兜挨，但是要承擔按索引次序讀取整個表的開銷，這意味著若是按隨機次序訪問行眯分，代價將非常大拌汇；
ALL：“全表掃描”的方式查找所需要的行，如果第一張表的查詢類型(EXPLAIN的輸出結(jié)果)為const弊决，其性能可能不算太壞噪舀，而第一張表的查詢類型為其它結(jié)果時，其性能通常會非常差飘诗；

Extra:
Using where：MySQL服務(wù)器將在存儲引擎收到數(shù)據(jù)后進行“后過濾(post-filter)”以限定發(fā)送給下張表或客戶端的行与倡；如果WHERE條件中使用了索引列，其讀取索引時就由存儲引擎檢查昆稿，因此纺座，并非所有帶有WHERE子句的查詢都會顯示“Using where”；
Using index：表示所需要的數(shù)據(jù)從索引就能夠全部獲取到溉潭，從而不再需要從表中查詢獲取所需要數(shù)據(jù)净响，這意味著MySQL將使用覆蓋索引；但如果同時還出現(xiàn)了Using where喳瓣，則表示索引將被用于查找特定的鍵值馋贤；
Using index for group-by：類似于Using index，它表示MySQL可僅通過索引中的數(shù)據(jù)完成GROUP BY或DISTINCT類的查詢畏陕；
Using filesort：表示MySQL會對結(jié)果使用一個外部索引排序配乓，而不是從表里按索引次序來讀取行；

五惠毁、mysql緩存和緩沖

持久性存儲
可以緩存
1.緩存表的結(jié)構(gòu)文件犹芹，.frm 緩存元數(shù)據(jù)。
MYISAM:
.frm 表結(jié)構(gòu)
.MYI 索引
.MYD 表數(shù)據(jù)
InnoDB：
.frm 表結(jié)構(gòu)
.ibd 表空間 索引仁讨，表數(shù)據(jù)

六羽莺、 鎖優(yōu)化：

加鎖,讀鎖，寫鎖
鎖的力度：
表鎖
行鎖

MYISAM表鎖
InnoDB:行鎖

如何選擇鎖的類型?

MySQL服務(wù)器優(yōu)化
緩存 洞豁，查詢緩存

物理優(yōu)化
CPU盐固，內(nèi)存，磁盤丈挟，網(wǎng)絡(luò)

七刁卜、 索引優(yōu)化

聚集索引
非聚集索引

主索引
輔助索引

稠密索引
稀疏索引

稠密索引：
每一個值的變化都有對應(yīng)的索引。

稀疏索引：
索引和值的變化不是一一對應(yīng)關(guān)系曙咽。

注意：主索引也得是稠密索引
輔助索引可以使用稠密或稀疏索引蛔趴。

聚集索引必須是稠密索引

多級索引：
為了降低索引查詢數(shù)據(jù)量。
二級索引
但是會產(chǎn)生多次IO

B+樹（多級索引）
Hash索引
空間索引
全文索引

從根到每一個葉子節(jié)點的路徑都是等長的例朱。
平衡樹索引
Balance Tree

索引：加速查詢
索引：降低寫入速度

表頻繁更新孝情，索引也要更新

插入鱼蝉，刪除，更新等性能的影響

hash
key-value
hash碼

age：hash索引
1：hash
key（hash）--->value
注意：主鍵不能使用hash索引

桶：
性能比較箫荡，IO少
靜態(tài)hash
不適合進行數(shù)據(jù)運算的魁亦，做等值查詢比較好用。

InnoDB：自適應(yīng)hash索引

覆蓋索引：索引使用方法
students:
id,name,age,salary
name,age:組合索引
左
查詢和搜索鍵做到索引里羔挡。

B樹索引的使用場景：
適用全鍵值洁奈，鍵值范圍或鍵值前綴查找。

name：
ling huchong
zhang wuji
zhang sanfeng
chen xuanfeng
chen yanzong

select * from where name like 'chen%';

B樹局限性：
如果不是從最左前綴開始绞灼，索引沒用
where name like '%u%'
不能跳過索引中的列利术。
where name like 'chen%' and salary>3000

存儲引擎不能優(yōu)化訪問任何第一個范圍條件右邊的列。

hash索引：
等值條件比較,只支持使用,IN(),<>進行的條件比較低矮。

缺陷：
無法使用索引排序
不支持部分鍵匹配

InnoDB：主索引（聚集索引）印叁，輔助索引
要用到兩次索引

聚集索引
索引和實際數(shù)據(jù)保存在一起的數(shù)據(jù)。

索引：指針
索引必須載入內(nèi)存

非聚集索引：索引和數(shù)據(jù)不保存在一起商佛。

MYISAM：是非聚集的
InnoDB：是聚集索引

輔助索引是指向主索引的喉钢。
一張表只能有一個索引姆打，聚集索引只有一個良姆。
輔助索引是指向索引的，所以要執(zhí)行兩次索引幔戏。
什么字段查詢最多是

八玛追、 MYISAM的調(diào)優(yōu)參數(shù)

key_buffer_size 鍵緩沖大小
加速查詢操作
concurreat_insert
空隙插入
delay_key_write
延時鍵寫入 異步

九、 InnoDB性能參數(shù)：

innodb_buffer_pool_size
緩存索引和數(shù)據(jù)(使用大內(nèi)存頁)
innodb_flush_log_at_trx_commit
事務(wù)提交
innodb_log_file_size:
事務(wù)日志大小

key：value 
select語句的hash碼：語句的查詢結(jié)果：

select name from student where age=30;
select name from student where age=30;
select name student whwere 
盡量使用

query_alloc_block_size 
query_cache_limit 
query_cache_min_res_unit 
query_cache_size bash
query_cache_type 
query_cache_type 
query_cache_type 


explain 
顯示語句執(zhí)行計劃：
select_type 

select name from student union select name from tutors;

說明：
mysql官方文檔中闲延，關(guān)于explain的執(zhí)行計劃痊剖。

架構(gòu)師
自動化運維（python）
DBA（mysql，Oracle）