史上最強(qiáng)MySQL參數(shù)調(diào)優(yōu)及數(shù)據(jù)庫鎖實(shí)踐
1.2.1 避免對數(shù)據(jù)進(jìn)行重復(fù)檢索
對于訪問數(shù)據(jù)庫來說，建立連接的代價(jià)是比較昂貴的，因?yàn)槲覀冾l繁的創(chuàng)建關(guān)閉連接，是比較耗費(fèi)資源的，我們有必要建立數(shù)據(jù)庫連接池，以提高訪問的性能。
1.2.1 避免對數(shù)據(jù)進(jìn)行重復(fù)檢索
在編寫應(yīng)用代碼時(shí)，需要能夠理清對數(shù)據(jù)庫的訪問邏輯。能夠一次連接就獲取到結(jié)果的，就不用兩次連接，這樣可以大大減少對數(shù)據(jù)無用的重復(fù)請求。
比如，需要獲取書籍的id和name字段，則查詢?nèi)缦?：
select id，name from tb_book;
之后，在業(yè)務(wù)邏輯中有需要獲取到書籍狀態(tài)信息，則查詢?nèi)缦?：
select id,status from tb_book;
在應(yīng)用中，我們可以在應(yīng)用中增加緩存層來達(dá)到減輕數(shù)據(jù)庫負(fù)擔(dān)的目的。緩存層有很多種，也很有很多實(shí)現(xiàn)方式，只要能達(dá)到數(shù)據(jù)庫的負(fù)擔(dān)又能滿足應(yīng)用需求就可以。
因此可以部分?jǐn)?shù)據(jù)從數(shù)據(jù)庫中抽取出來放到應(yīng)用端以文本方式存儲，或者使用框架（Mybatis，Hibernate）提供的一級緩存/二級緩存，或者使用redis數(shù)據(jù)庫來緩存數(shù)據(jù)。
負(fù)載均衡是應(yīng)用中使用非常普遍的一種優(yōu)化方法，它的機(jī)制就是利用某種均衡算法，將固定的負(fù)載量分布到不同的服務(wù)器上，以此來降低單臺服務(wù)器的負(fù)載，達(dá)到優(yōu)化的效果。
通過MySQL的主從復(fù)制，實(shí)現(xiàn)讀寫分離，使增刪改操作走主節(jié)點(diǎn)，查詢操作走從節(jié)點(diǎn)，從而可以降低單臺服務(wù)器的讀寫壓力。
分布式數(shù)據(jù)庫架構(gòu)適合大數(shù)據(jù)量、負(fù)載高的情況，它有良好的拓展性和高可用性。通過在多臺服務(wù)器之間分布數(shù)據(jù)，可以實(shí)現(xiàn)在多臺服務(wù)器之間的負(fù)載均衡，提高訪問效率。
開啟MySQL的查詢緩存，當(dāng)執(zhí)行完全相同的SQL語句的時(shí)候，服務(wù)器就會直接從緩存中讀取結(jié)果，當(dāng)數(shù)據(jù)被修改，之前的緩存會失效，修改比較頻繁的表不適合做查詢緩存。
客戶端發(fā)送一條查詢給服務(wù)器；
服務(wù)器先回檢查查詢緩存，如果命中了緩存，則立即返回存儲在緩存中的結(jié)果。否則進(jìn)入下一階段；
服務(wù)器端進(jìn)行SQL解析、預(yù)處理，再由優(yōu)化器生成對應(yīng)的執(zhí)行計(jì)劃；
執(zhí)行計(jì)劃查詢執(zhí)行引擎調(diào)用存儲引擎接口獲取數(shù)據(jù)并返回結(jié)果，以及更新緩存。
1.查看當(dāng)前的MySQL數(shù)據(jù)庫是否支持查詢緩存 ：
SHOW VARIABLES LIKE ‘have_query_cache’;
2.查看當(dāng)前MySQL是否開啟了查詢緩存 ：
SHOW VARIABLES LIKE ‘query_cache_type’;
3.查看查詢緩存的占用大小 ：
SHOW VARIABLES LIKE ‘query_cache_size’;
4.查看查詢緩存的狀態(tài)變量 ：
SHOW STATUS LIKE ‘Qcache%’;
各個(gè)變量的含義如下 :
MySQL的查詢緩存默認(rèn)是關(guān)閉的，需要手動配置參數(shù)query cache type，來開啟查詢緩存。query cache type該參數(shù)的可取值有三個(gè) ：
1）OFF或0 ：查詢緩存功能關(guān)閉；
2）ON或1 ：查詢緩存功能打開，SELECT的結(jié)果符合緩存條件即會緩存，否則，不予緩存，顯示指定SQL_NO_CACHE，不予緩存
3）DEMAND或2 ：查詢緩存功能按需進(jìn)行，顯示指定SQL_CACHE的SELECT語句才會緩存；其他均不予緩存。
在/usr/my.cnf配置中，增加以下配置 ：
開啟mysql的查詢緩存
query_cache_type=1
配置完畢之后，重啟服務(wù)即可生效；
然后就可以在命令行執(zhí)行SQL語句進(jìn)行驗(yàn)證，執(zhí)行一條比較耗時(shí)的SQL語句，然后再多執(zhí)行幾次，查看后面幾次的執(zhí)行時(shí)間；獲取通過查看查詢緩存的緩存命中數(shù)，來判定是否走查詢緩存。
可以在SELECT語句中指定兩個(gè)與查詢緩存相關(guān)的選項(xiàng) ：
SQL_CACHE : 如果查詢結(jié)果是可緩存的，并且query_cache_type系統(tǒng)變量的值為ON或DEMAND，則緩存查詢結(jié)果。
SQL_NO_CACHE : 服務(wù)器不使用查詢緩存。它既不查詢緩存，也不檢查是否已緩存，也不緩存查詢結(jié)果。
例子 ：
1）SQL語句不一致的情況，要想命中查詢緩存，查詢的SQL語句必須一致。
2）當(dāng)查詢語句中有一些不確定的時(shí)，則不會緩存。如 ：now(),current_date(),curdate(),curtime(),rand(),uuid(),database().
3）不使用任何表查詢語句。
select ‘A’;
4）查詢MySQL，information_schema 或 performance_schema數(shù)據(jù)庫中的表時(shí)，不會走查詢緩存。
select * from information_schema.engines;
5）在存儲的函數(shù)，觸發(fā)器或事件的主體內(nèi)執(zhí)行的查詢。
6）如果表更改，則使用該表的所有高速緩存查詢都將變?yōu)闊o效并從高速緩存中刪除。這包括使用MERGE映射到已更改表的表的查詢。一個(gè)表可以被許多類型的語句，如被改變INSERT，UPDATE，TRUNCATE TABLE，ALTER TABLE，DROP TABLE，或 DROP DATABASE。
1）將盡量多的內(nèi)存分配給MySQL做緩存，但要給操作系統(tǒng)和其他程序預(yù)留足夠內(nèi)存。
2）MyISAM存儲引擎的數(shù)據(jù)文件讀取依賴于操作系統(tǒng)自身的IO緩存，因此，如果有MySQL表，就要預(yù)留更多的內(nèi)存給操作系統(tǒng)做IO緩存。
3）排序區(qū)、連接區(qū)等緩存是分配給每個(gè)數(shù)據(jù)庫會后（session）專用的，其默認(rèn)值的設(shè)置要根據(jù)最大連接數(shù)合理分配，如果設(shè)置太大，不但浪費(fèi)資源，而且在并發(fā)連接較高時(shí)會導(dǎo)致物理內(nèi)存耗盡。
myisam存儲引擎使用key_buffer緩存索引塊，加速myisam索引的讀寫速度。對于myisam表的數(shù)據(jù)塊，mysql沒有特別的緩存機(jī)制，完全依賴于操作系統(tǒng)的IO緩存。
key_buffer_size
key_buffer_size決定MyISAM索引塊緩存區(qū)的大小，直接影響到MyISAM表的存取效率。可以在MySQL參數(shù)文件中設(shè)置key_buffer_size的值，對于一般MyISAM數(shù)據(jù)塊，建議至少將1/4可用內(nèi)存分配給key_buffer_size。
在/usr/my.cnf中做如下配置 ：
key_buffer_size=512M
read _buffer_size
如果需要經(jīng)常順序掃描myisam表，可以通過增大read_buffer_size的值來改善性能。但需要注意的是read_buffer_size是每個(gè)session獨(dú)占的，如果默認(rèn)值設(shè)置太大，就會造成內(nèi)存浪費(fèi)。
read_rnd_buffer_size
對于需要做排序的myisam表的查詢，如帶有order by子句的sql，適當(dāng)增加read_buffer_size的值，可以改善此類的sql性能。但需要注意的是read_rnd_buffer_size是每個(gè)session獨(dú)占的，如果默認(rèn)值設(shè)置太大，就會造成內(nèi)存浪費(fèi)。
innoDB用一塊內(nèi)存區(qū)做IO緩存池，該緩存池不僅用來緩存innodb的索引塊，而且也用來緩存innodb的數(shù)據(jù)塊。
innodb_buffer_pool_size
該變量決定了innodb存儲引擎表數(shù)據(jù)和索引數(shù)據(jù)的最大緩存區(qū)大小。在保證操作系統(tǒng)及其他程序有足夠內(nèi)存可用的情況下，innodb_buffer_pool_size的值越大，緩存命中率越高，訪問InnoDB表需要的磁盤I/O就越少，性能也就越高。
innodb_buffer_pool_size=512M
innodb_log_buffer_size
決定innodb重做日志緩存的大小，對于可能產(chǎn)生大量更新記錄的大事務(wù)，增加innodb_log_buffer_size的大小，可用避免innodb在事物提交前就執(zhí)行不必要的日志寫入磁盤操作。
innodb_log_buffer_size=10M
從實(shí)現(xiàn)上來說，MySQL Server是多線程結(jié)構(gòu)，包括后臺線程和客戶服務(wù)線程。多線程可用有效利用服務(wù)器資源，提供數(shù)據(jù)庫的并發(fā)性能。
在MySQL中，控制并發(fā)連接和線程的主要參數(shù)包括max_connections、back_log、thread_cache_siez、table_open_cache。
采用max_connections控制允許連接到MySQL數(shù)據(jù)庫的最大數(shù)量，默認(rèn)值時(shí)151.如果狀態(tài)變量connection_errors_max_connections不為零，并且一直增長，則說明不斷有連接請求因數(shù)據(jù)庫連接數(shù)已達(dá)到允許最大值而失敗，這是可以考慮增大max_connections的值。
MySQL最大可支持的連接數(shù)，取決于很多因素，包括給定操作系統(tǒng)平臺的線程庫的質(zhì)量、內(nèi)存大小、每個(gè)連接的負(fù)荷、CPU的處理速度，期望的響應(yīng)時(shí)間等。在Linux平臺下，性能好的服務(wù)器，支持500-1000個(gè)連接不是難事，需要根據(jù)服務(wù)器性能進(jìn)行評估設(shè)定。
back_log參數(shù)控制MySQL監(jiān)聽TCP端口時(shí)設(shè)置的積壓請求棧大小。如果MySQL的連接數(shù)達(dá)到max_connections時(shí)，新來的請求將會被存在堆棧中，以等待某一連接釋放資源，該堆棧的數(shù)量即back_log，如果等待連接的數(shù)量超過back_log，將不被授予連接資源，將會報(bào)錯(cuò)。5.6.6版本之前默認(rèn)值為50，之后的版本默認(rèn)為50+（max_connections / 5），最大不超過900。
如果需要數(shù)據(jù)庫在較短的時(shí)間內(nèi)處理大量連接請求，可以考慮適當(dāng)增大back_log的值。
該參數(shù)用來控制所有SQL語句執(zhí)行線程可打開表緩存的數(shù)量，而在執(zhí)行SQL語句時(shí)，每一個(gè)SQL執(zhí)行線程至少要打開1個(gè)表緩存。該參數(shù)的值應(yīng)該根據(jù)設(shè)置的最大連接數(shù)mac_connections以及每個(gè)連接執(zhí)行關(guān)聯(lián)查詢中涉及的表的最大數(shù)量來設(shè)定 ：
max_connections x N ;
為了加快連接數(shù)據(jù)庫的速度，MySQL會緩存一定數(shù)量的客戶服務(wù)線程以備重用，通過參數(shù)thread_cache_size可控制MySQL緩存客戶服務(wù)線程的數(shù)量。
該參數(shù)是用來設(shè)置InnoDB事務(wù)等待行鎖的時(shí)間，默認(rèn)值是50ms，可以根據(jù)需要進(jìn)行動態(tài)設(shè)置。對于需要快速反饋的業(yè)務(wù)系統(tǒng)來說，可以將行鎖的等待時(shí)間調(diào)小，以避免事務(wù)長時(shí)間掛起；對于后臺運(yùn)行的批量處理程序來說，可以將行鎖的等待時(shí)間調(diào)大，以避免發(fā)生大的回滾操作。
鎖是計(jì)算機(jī)協(xié)調(diào)多個(gè)進(jìn)程或線程并發(fā)訪問某一資源的機(jī)制。
在數(shù)據(jù)庫中，除傳統(tǒng)的計(jì)算資源（如CPU、RAM、I/O等）的爭用之外，數(shù)據(jù)也是一種供許多用戶共享的資源。如何保證數(shù)據(jù)并發(fā)訪問的一致性、有效性是所有數(shù)據(jù)庫必須解決的一個(gè)問題，鎖沖突也是影響數(shù)據(jù)庫并發(fā)訪問性能的一個(gè)重要因素。從這個(gè)角度來說，鎖對數(shù)據(jù)庫而言顯得尤其重要，也更加復(fù)雜。
從對數(shù)據(jù)操作的粒度分：
1）表鎖 ：操作時(shí)，會鎖定整個(gè)表。
2）行鎖 ：操作時(shí)，會鎖定當(dāng)前操作行。
從對數(shù)據(jù)操作的類型分 ：
1）讀鎖（共享鎖） ：針對同一份數(shù)據(jù)，多個(gè)讀操作可以同時(shí)進(jìn)行而不會互相影響（只能讀不能寫，寫必須先釋放鎖）。
2）寫鎖（排它鎖） ：當(dāng)前操作沒有完成之前，它會阻斷其他寫鎖和讀鎖（只能在當(dāng)前操作釋放寫鎖，其他操作才可以進(jìn)行）。
相對其他數(shù)據(jù)庫而言，MySQL的鎖機(jī)制比較簡單，其最顯著的特點(diǎn)是不同的存儲引擎不同的鎖機(jī)制。下表中羅列出了各存儲引擎對鎖的支持情況 ：
MySQL這三種鎖的特性可大致歸納如下 ：
表級鎖 ：偏向MyISAM存儲引擎，開銷小，加鎖快；不會出現(xiàn)死鎖；鎖定粒度大，發(fā)生鎖沖突的概率最高，并發(fā)度最低。
行級鎖 ：偏向InnoDB存儲引擎，開銷大，加鎖慢；會出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度也最高。
頁面鎖 ：開銷和加鎖時(shí)間界于表鎖和行鎖之間；會出現(xiàn)死鎖；鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般。
從上述特點(diǎn)可見，很難籠統(tǒng)地說那種鎖更好，只能就具體應(yīng)用的特點(diǎn)來說那種鎖更合適！僅從鎖的角度來說 ：表級鎖更適合于以查詢?yōu)橹?，只有少量按索引條件更新數(shù)據(jù)的應(yīng)用，如web應(yīng)用；而行級鎖則更適合于大量按索引條件并發(fā)更新少量不同數(shù)據(jù)，同時(shí)又并查詢的應(yīng)用，如一些在線事物處理（OLTP）系統(tǒng)。
MyISAM存儲引擎只支持表鎖，這也是MySQL開始幾個(gè)版本中唯一支持的鎖類型。
MyISAM在執(zhí)行查詢語句（SELECT）前，會自動給涉及的所有表加讀鎖，在執(zhí)行更新操作（UPDATE、DELETE、INSERT等）前，會自動給涉及的表加寫鎖，這個(gè)過程并不需要用戶干預(yù)，因此，用戶一般不需要直接用LOCK TABLE命令給MyISAM表顯示加鎖。
顯示加表鎖語法：
加讀鎖 ：lock table table_name read;
加寫鎖 ：lock table table_name write;
unlock tables : 解鎖
鎖模式的相互兼容性如表中所示 ：
由上表可見 ：
1）對MyISAM表的讀操作，不會阻塞其他用戶對同一表的讀請求，但是會阻塞對同一表的寫請求；
2）對MyISAM表的寫操作，則會阻塞其他用戶對同一表的讀和寫操作；
簡而言之，就是讀鎖會阻塞寫，但是不會阻塞讀。而寫鎖，則既會阻塞讀，又會阻塞寫。
此外，MyISAM的讀寫鎖調(diào)度是寫優(yōu)先，這也是MyISAM不適合做寫為主的表的存儲引擎的原因。因?yàn)閷戞i后，其他線程不能做任何操作，大量的更新會使查詢很難得到鎖，從而造成用于阻塞。
show open tables;
In_user : 表當(dāng)前被查詢使用的次數(shù)。如果該數(shù)為零，則表是打開的，但是當(dāng)前沒有被使用。
Name_locked : 表名稱是否被鎖定。名稱鎖定用于取消表或?qū)Ρ磉M(jìn)行重命名等操作。
show status like ‘Table_locks%’;
Table_locks_immediate : 指的是能夠立即獲得表級鎖的次數(shù)，每立即獲取鎖，值加1。
Table_locks_waited : 指的是不能立即獲取表級鎖而需要等待的次數(shù)，每等待一次，該值加1，此值高說明存在著較為嚴(yán)重的表級鎖爭用情況。
行鎖特點(diǎn) ：偏向InnoDB存儲引擎，開銷大，加鎖慢；會出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度也最高。
InnoDB與MyISAM的最大不同有兩點(diǎn) ：一是支持事務(wù)；二是采用了行級鎖。
事務(wù)及其ACID屬性
事務(wù)是由一組SQL語句組成的邏輯處理單元。
事務(wù)具有以下四個(gè)特性，簡稱為事務(wù)ACID屬性。
并發(fā)事務(wù)處理帶來的問題
事務(wù)隔離級別
為了解決上述提到的事務(wù)并發(fā)問題，數(shù)據(jù)庫提供一定的事務(wù)隔離機(jī)制來解決這個(gè)問題。數(shù)據(jù)庫的事務(wù)隔離越嚴(yán)格，并發(fā)副作用越小，但付出的代價(jià)也就越大，因?yàn)槭聞?wù)隔離實(shí)質(zhì)上就是使用事務(wù)在一定程度上“串行化”進(jìn)行，這顯然與“并發(fā)”是矛盾的。
數(shù)據(jù)庫的隔離級別有四個(gè)，由低到高依次為Read uncommitted、Read committed、Repeatable read、Serializable，這四個(gè)級別可以逐個(gè)解決臟寫、臟讀、不可重復(fù)讀、幻讀這幾類問題。
MySQL的數(shù)據(jù)庫的默認(rèn)隔離級別Repeatable read ,查看方式 ：
InnoDB實(shí)現(xiàn)了以下兩種類型的行鎖。
共享鎖（S）：又稱為讀鎖，簡稱S鎖，共享鎖就是多個(gè)事務(wù)對于同一數(shù)據(jù)可以共享一把鎖，都能訪問到數(shù)據(jù)，但是只能讀不能修改。
排他鎖（X）：又稱為寫鎖，簡稱X鎖，排他鎖就是不能與其他鎖并存，入一個(gè)事務(wù)獲取了一個(gè)數(shù)據(jù)行的排他鎖，其他事務(wù)就不能再獲取該行的其他鎖，包括共享鎖和排他鎖，但是獲取排他鎖的事務(wù)是可以對數(shù)據(jù)進(jìn)行讀取和修改。
對于update、delete和insert語句，InnoDB會自動給涉及數(shù)據(jù)集加排他鎖（X）；
對于普通SELECT語句，InnoDB不會加任何鎖；
可以通過以下語句顯示給記錄集加共享鎖或排他鎖。
共享鎖（S）：select * from table_name where LOCK IN SHARE MODE
排他鎖（X）：select * from table_name where … FOR UPDATE
如果不通過索引條件檢索數(shù)據(jù)，那么InnoDB將對表中的所有記錄加鎖，實(shí)際效果更表鎖一樣。
查看當(dāng)前表的索引 : show index from test_innodb_lock;
由于執(zhí)行更新時(shí)，name字段本來為varchar類型，我們是作為數(shù)組類型使用，存在類型轉(zhuǎn)換，索引失效，最終行鎖變?yōu)楸礞i。
當(dāng)我們用范圍條件，而不是使用相等條件檢索數(shù)據(jù)，并請求共享或排他鎖時(shí)，InnoDB會給符合條件的已有數(shù)據(jù)進(jìn)行加鎖；對于鍵值在條件返回內(nèi)但并不存在的記錄，叫做“間隙（GAP）”，InnoDB也會對這個(gè)“間隙”加鎖，這種鎖機(jī)制就是所謂的間隙鎖（Next-Key鎖）。
show status like ‘innodb_row_lock%’;
Innodb_row_lock_current_waits : 當(dāng)前正在等待鎖定的數(shù)量
Innodb_row_lock_time : 從系統(tǒng)啟動到現(xiàn)在鎖定總時(shí)間長度
Innodb_row_lock_time_avg : 每次等待所花平均時(shí)長
Innodb_row_lock_time_max : 從系統(tǒng)啟動到現(xiàn)在等待最長的一次所花的時(shí)間
Innodb_row_lock_waits : 系統(tǒng)啟動后到現(xiàn)在總共等待的次數(shù)
當(dāng)?shù)却拇螖?shù)很高，而且每次等待的時(shí)長也不小的時(shí)候，我們就需要分析系統(tǒng)中為什么會有如此多的等待，然后根據(jù)分析結(jié)果訂制優(yōu)化計(jì)劃。
InnoDB存儲引擎由于實(shí)現(xiàn)了行級鎖定，雖然在鎖定機(jī)制的實(shí)現(xiàn)方面帶來了性能損耗可能比表鎖會更高一些，但是在整體并發(fā)處理能力方面要遠(yuǎn)遠(yuǎn)優(yōu)于MyISAM的表鎖的。當(dāng)系統(tǒng)并發(fā)量較高時(shí)，InnoDB的整體性能和MyISAM相比就會有比較明顯的優(yōu)勢。
但是，InnoDB的行級鎖同樣也有其脆弱的一面，當(dāng)我們使用不當(dāng)?shù)臅r(shí)候，可能會讓InnoDB的整體性能表現(xiàn)不僅不能比MyISAM高，甚至可能會更差。
優(yōu)化建議 ：
盡可能讓所有數(shù)據(jù)檢索都能通過索引來完成，避免無索引行鎖升級為表鎖。
合理設(shè)計(jì)索引，盡量縮小鎖的范圍。
盡可能減少索引條件，及索引范圍，避免間隙鎖。
盡量控制事務(wù)大小，減少鎖定資源量和時(shí)間長度。
盡可使用低級別事務(wù)隔離（但是需要業(yè)務(wù)層面滿足需求）。
編寫順序
執(zhí)行順序
正則表達(dá)式（Regular Expression）是指一個(gè)用來描述或者匹配一系列符合某個(gè)句法規(guī)則的字符串的單個(gè)字符串。
數(shù)字函數(shù)
字符串函數(shù)
日期函數(shù)
聚合函數(shù)
