主要來自

? ??《高性能MySQL（第3版）》

? ??《MySQL管理之道：性能調(diào)優(yōu)、高可用與監(jiān)控（第2版）》

? ??《MySQL 8 Cookbook:Over?150 recipes for high-performance database querying and administration》

? ??以及MySQL的官方文檔

相關(guān)名稱解釋：

? ??schema：database and table structure

? ??shared lock锭环，共享鎖猴鲫，S鎖：允許其他事務(wù)讀取被鎖定的對(duì)象，其他事務(wù)也可以獲取另一個(gè)此對(duì)象的共享鎖迅腔，但是不允許寫操作牍帚。

? ??exclusive lock银伟，排它鎖伍伤，X鎖：

? ??intention lock并徘，意向鎖：作用在表上（是表級(jí)鎖），用于指明事務(wù)要獲取行鎖的類型扰魂。不同的事務(wù)可以在同一個(gè)表上獲取不同的意向鎖麦乞，但是第一個(gè)獲取了表IX鎖的事務(wù)蕴茴，會(huì)阻止其他想要獲取S鎖和X鎖的。相反路幸，如果第一個(gè)獲取了表IS鎖的事務(wù)荐开，僅阻止其他想要獲取X鎖的事務(wù)付翁。

來自《高性能MySQL（第3版）》

1简肴、MySQL的存儲(chǔ)引擎

????可是使用語句查看table的存儲(chǔ)信息：show table status like ‘tablename’;

????InnoDB引擎：被設(shè)計(jì)用來處理大量的短期（short-lived）事務(wù)，當(dāng)然在非事務(wù)性存儲(chǔ)也很流行百侧。

????MyISAM：設(shè)計(jì)簡單砰识，對(duì)整個(gè)表加鎖，不支持行鎖佣渴。

????其他引擎有Archive辫狼、Blackhole、CVS辛润、Federated膨处、Memory等。

????大多數(shù)情況下選擇InnoDB砂竖，除非要用到InnoDB不具備的特性真椿。例如，不在乎并發(fā)與擴(kuò)展乎澄，也不在乎崩潰后數(shù)據(jù)丟失突硝，卻對(duì)InnoDB的空間占用比較敏感，可選擇MyISAM置济。

2解恰、選擇存儲(chǔ)引擎的幾個(gè)需要考慮的因素。

????事務(wù)：比如無需事務(wù)浙于、且主要使用select和insert的日志型應(yīng)用护盈，則可使用MyISAM。

????備份：

????崩潰恢復(fù)：

????其他特性：如地理空間搜索羞酗、聚簇索引等等

3腐宋、關(guān)于InnoDB的幾個(gè)特點(diǎn)

????table是基于聚簇索引建立的，主鍵查詢效率非常高整慎，但是有二級(jí)索引問題脏款。

????采用MVCC支持高并發(fā)，并且實(shí)現(xiàn)了4個(gè)事務(wù)隔離級(jí)別裤园，默認(rèn)是Repeatable Read撤师。

4、死鎖與解決方法

????在資源上的相互占用導(dǎo)致死鎖拧揽。發(fā)生死鎖時(shí)剃盾，只有部分或完全回滾其中一個(gè)事務(wù)腺占，才能打破死鎖。

數(shù)據(jù)庫提供了死鎖檢查和死鎖超時(shí)痒谴。InnoDB目前處理死鎖的方式是衰伯，將持有最少行級(jí)排它鎖的事務(wù)進(jìn)行回滾。

5积蔚、關(guān)于事務(wù)日志

????存儲(chǔ)引擎只在內(nèi)存中修改數(shù)據(jù)意鲸，并不立即把數(shù)據(jù)持久化到磁盤，而是把事務(wù)日志持久化到磁盤尽爆。事務(wù)日志采用追加方式怎顾，并且磁盤是小區(qū)域的順序IO，執(zhí)行效率高漱贱。內(nèi)存中的數(shù)據(jù)在后臺(tái)擇機(jī)刷新到磁盤槐雾。如果此時(shí)宕機(jī)，重啟后引擎會(huì)恢復(fù)這部分?jǐn)?shù)據(jù)幅狮。

6募强、事務(wù)中混合使用存儲(chǔ)引擎

????事務(wù)是由存儲(chǔ)引擎實(shí)現(xiàn)的，所以如果事務(wù)中涉及不同的存儲(chǔ)引擎崇摄，那這個(gè)事務(wù)是不可靠的擎值。比如混合使用InnoDB和MyISAM，在回滾時(shí)非事務(wù)型無法撤銷配猫。

7幅恋、InnoDB的顯示和隱式鎖

????顯示鎖：Select … Lock in share mode；Select…for update泵肄。MySQL也支持Lock table和Unlock

table語句捆交。

8、InnoDB的兩階段鎖定協(xié)議

????在事務(wù)執(zhí)行過程中腐巢，可以隨時(shí)執(zhí)行鎖定品追。只有執(zhí)行Commit或Rollback時(shí)才會(huì)釋放鎖，并且所有鎖是同時(shí)釋放冯丙。

9肉瓦、多版本并發(fā)控制：MVCC

????可以認(rèn)為MVCC是行鎖的變種，它的實(shí)現(xiàn)是通過保存數(shù)據(jù)在某個(gè)時(shí)間點(diǎn)的快照實(shí)現(xiàn)的胃惜。與其相關(guān)的事務(wù)隔離級(jí)別是Repeatable Read泞莉。

10、MySQL基準(zhǔn)測(cè)試的指標(biāo)都有那些

????吞吐量：單位時(shí)間內(nèi)事務(wù)處理數(shù)船殉，主要是針對(duì)在線事務(wù)處理（OLTP）的吞吐量鲫趁，常用單位是TPS：每秒事務(wù)數(shù)，或TPM：每分鐘事務(wù)數(shù)利虫。

? ? 響應(yīng)時(shí)間或延遲：測(cè)試任務(wù)所需的整體時(shí)間挨厚，通常計(jì)算平均相應(yīng)時(shí)間堡僻，最小相應(yīng)時(shí)間、最大相應(yīng)時(shí)間及百分比疫剃。常用百分比響應(yīng)時(shí)間來描述最大相應(yīng)時(shí)間钉疫，如95%的相應(yīng)時(shí)間是5毫秒，表示95%的任務(wù)可以在5毫秒內(nèi)完成巢价。

????并發(fā)性：需要關(guān)注在并發(fā)增加時(shí)牲阁，吞吐量是否下降，響應(yīng)時(shí)間是否變長蹄溉。

????可擴(kuò)展性：簡單說就是給系統(tǒng)增加一倍資源咨油，是否可同樣增加一倍的吞吐量您炉。

11柒爵、一些測(cè)試工具

????集成式測(cè)試工具：

????????ab：一個(gè)Apache Http服務(wù)器的基準(zhǔn)測(cè)試工具。

????????http_load：類似ab

????????JMeter

????MySQL測(cè)試工具：

????????MySQLslap

????????Sql-bench

????????Super smack

????????Database Test Suite

????????Sysbench

12赚爵、關(guān)于“性能優(yōu)化”的一個(gè)前置知識(shí)：任務(wù)所需的時(shí)間

????性能就是完成任務(wù)的所需時(shí)間棉胀，即響應(yīng)時(shí)間。而非“每秒查詢次數(shù)”“cpu利用率”“可擴(kuò)展性”之類冀膝。

????執(zhí)行任務(wù)包括兩個(gè)時(shí)間：等待時(shí)間和執(zhí)行時(shí)間唁奢。當(dāng)談到優(yōu)化任務(wù)執(zhí)行時(shí)間時(shí)，需要從這兩個(gè)完全不同的角度去考慮窝剖。

????比如任務(wù)執(zhí)行時(shí)間優(yōu)化麻掸，優(yōu)化去掉一些子任務(wù)、提升子任務(wù)時(shí)間等赐纱。

13脊奋、關(guān)于選擇優(yōu)化的數(shù)據(jù)類型

????更小的通常更好：占用空間少，磁盤疙描、內(nèi)存诚隙、cpu緩存的空間。

????簡單的更好：使用內(nèi)建類型存儲(chǔ)日期或時(shí)間起胰；使用整數(shù)存儲(chǔ)IP地址久又。

????盡量避免NULL：對(duì)NuLL很難優(yōu)化，且進(jìn)行統(tǒng)計(jì)和比較都會(huì)復(fù)雜效五。也有例外地消，Null對(duì)于InnoDB列的稀疏數(shù)據(jù)有很好的空間利用率）

14、關(guān)于varchar（5）和varchar（200）

????如果存儲(chǔ)hello畏妖，那更短的列會(huì)有優(yōu)勢(shì)嗎脉执？事實(shí)證明其優(yōu)勢(shì)很大，更長的列占用更多的內(nèi)存瓜客，MySQL內(nèi)部會(huì)分配固定大小的內(nèi)存塊來保存內(nèi)部值适瓦。

15竿开、關(guān)于Alter table

? ? 【略】

16、MySQL索引有哪些類型玻熙？

????B-tree索引：大多數(shù)引擎都支持此類型索引否彩，但是又有所區(qū)別，NDB使用T-Tree嗦随，InnoDB使用B+Tree列荔。

????Hash索引：MySQL中只有Memory引擎支持Hash索引。擴(kuò)展枚尼，InnoDB支持“自適應(yīng)Hash索引“（在此不討論）贴浙。

????R-tree空間數(shù)據(jù)索引：MyISAM支持空間索引，可用作地理數(shù)據(jù)存儲(chǔ)署恍。

????全文索引：查找的是文本中的關(guān)鍵字崎溃，而不是比較索引的值。

????第三方存儲(chǔ)引擎使用的自定義索引盯质。

17袁串、索引的優(yōu)點(diǎn)

????1、減少服務(wù)器掃描的數(shù)據(jù)量呼巷。

? ? 2囱修、幫助服務(wù)器避免排序和臨時(shí)表。

????3王悍、將隨機(jī)IO變?yōu)轫樞騃O破镰。

18、高性能索引策略

????從以下方面考慮索引的使用策略：

? ??獨(dú)立的列：索引列不能是表達(dá)式的一部分压储，也不能是函數(shù)的參數(shù)鲜漩。

? ??前綴索引和索引選擇性：平衡一下前綴索引和索引選擇性兩者的關(guān)系。

? ??多列索引：這里涉及一個(gè)“索引合并“的概念渠脉，

? ??選擇合適的索引列順序：對(duì)B-tree來說宇整，最左順序使用索引原則。但是芋膘，把選擇性最好的列放置到最左是通用原則嗎鳞青？在某些特殊場(chǎng)合下，避免隨機(jī)IO和排序可能更重要一些为朋。

? ??聚簇索引：這不是索引類型臂拓，而是數(shù)據(jù)存儲(chǔ)方式。在InnoDB中习寸，聚簇索引在同一結(jié)構(gòu)中保存了B-tree索引和數(shù)據(jù)行胶惰。

? ??覆蓋索引：如果索引中已經(jīng)包含所需數(shù)據(jù)，則稱為覆蓋索引霞溪。

? ??使用索引掃描做排序：

? ??壓縮（前綴壓縮）索引：MyISAM使用前綴壓縮來減少索引的大小孵滞。

? ??冗余和重復(fù)索引：需要消除

? ??未使用的索引：排查并消除中捆。通過分析索引的使用頻率（可能需要打開某開關(guān)）

? ??索引和鎖：雖然InnoDB基于索引的行鎖開銷很小，但是依然要盡可能減少被鎖定的行數(shù)坊饶。被鎖定的行越多泄伪，鎖爭用會(huì)減少并發(fā)性。

19匿级、關(guān)于InnoDB中的聚簇索引

????InnoDB通過主鍵聚集數(shù)據(jù)蟋滴，如果沒有主鍵，InnoDB會(huì)選擇一個(gè)唯一的非空索引替代痘绎，如果沒有這樣的索引津函，InnoDB會(huì)隱式定義一個(gè)主鍵來作為聚簇索引。

? ??聚簇索引的幾個(gè)優(yōu)點(diǎn)：

????????相關(guān)的數(shù)據(jù)保存在一起孤页，減少IO次數(shù)

????????數(shù)據(jù)訪問更快尔苦，從聚簇索引中獲取數(shù)據(jù)通常比非聚簇索引快。

????????使用覆蓋索引掃描的查詢可直接使用頁節(jié)點(diǎn)中的主鍵值散庶，避免二次索引數(shù)據(jù)蕉堰。

? ??聚簇索引的缺點(diǎn)：

????????聚簇索引最大限度的提高IO密集型應(yīng)用性能，但是如果都是基于內(nèi)存的訪問悲龟，順序就不重要了。

????????插入速度嚴(yán)重依賴插入順序冰寻。

????????更新聚簇索引代價(jià)較高须教。

????????插入新行（或主鍵被更新）時(shí)，如果需要移動(dòng)行斩芭，則可能面臨“頁分裂”問題轻腺。

????????可能導(dǎo)致全表掃描變慢，尤其是行比較稀疏划乖，或頁分裂導(dǎo)致存儲(chǔ)不連續(xù)的時(shí)候贬养。

????????二級(jí)索引占用空間可能更大，因?yàn)樵诙?jí)索引包含行的主鍵列琴庵。

????????二級(jí)索引訪問需要兩次索引查找误算。

20、一些啥玩意迷殿，看看吧儿礼！

? ??關(guān)聯(lián)子查詢：？（尤其是In（select ））改造庆寺。

? ??Union的局限性：union導(dǎo)致生成臨時(shí)表蚊夫。

? ??索引合并：？

? ??等值傳遞：懦尝？例如非常大的In（）列表知纷。

? ??并行執(zhí)行：壤圃？5.5版本不支持并行執(zhí)行，8版本呢琅轧？需要考察埃唯。

? ??哈希關(guān)聯(lián)：？MySQL不支持哈希關(guān)聯(lián)鹰晨，所有關(guān)聯(lián)都是嵌套循環(huán)關(guān)聯(lián)墨叛。MariaDB實(shí)現(xiàn)了真正的哈希關(guān)聯(lián)。

? ??松散索引掃描：模蜡？MySQL不支持松散索引掃描漠趁，也就無法按照不連續(xù)的方式掃描一個(gè)索引。

? ??最大值和最小值優(yōu)化：使用索引的有序特性忍疾，放棄分組函數(shù)闯传，使用limit 1來替代。

? ??查詢優(yōu)化器提示（hint）：最好不使用這個(gè)卤妒，因?yàn)閿?shù)據(jù)庫是不斷升級(jí)的甥绿，可能導(dǎo)致一些系統(tǒng)級(jí)優(yōu)化變得無效，并且代碼維護(hù)起來也比較繁瑣则披。

21共缕、關(guān)于優(yōu)化Limit語句

????如limit 10000,20，前面10000條記錄被拋棄士复，只返回后20條图谷，這樣的代價(jià)很高≮搴椋可以從以下方面考慮：

????????盡量的使用索引覆蓋和延遲關(guān)聯(lián)便贵。

????????轉(zhuǎn)化為已知位置的查詢，通過where的某些字段的條件冗荸。

????????依據(jù)某字段（如注解承璃，某索引）的順序特性，將前面的查詢結(jié)果作為后續(xù)分頁查詢的where條件蚌本。

????????多獲取1條數(shù)據(jù)（21條）盔粹，如果存在第21條，則可以進(jìn)行“下一頁”如果不存在第21條魂毁，則無需再檢索下一頁玻佩。

????????應(yīng)用程序緩存。

22席楚、關(guān)于優(yōu)化Union

????MySQL（老版本這樣咬崔，新版本呢？）總是通過創(chuàng)建并填充臨時(shí)表的方式執(zhí)行union，并且默認(rèn)使用distinct去重垮斯，這個(gè)代價(jià)也很大郎仆。此時(shí)的優(yōu)化是使用union all，避免唯一性檢查兜蠕。

????臨時(shí)表是避免不了的（嗎扰肌？，看看新版本）

23熊杨、大數(shù)據(jù)量的擴(kuò)展性曙旭，一般有下面兩個(gè)策略

????全量掃描數(shù)據(jù)、不要任何索引：考慮順利IO和索引開銷晶府。

????索引數(shù)據(jù)桂躏，并分離熱點(diǎn)

24、MySQL中重要的緩存

? ? 以下緩存相對(duì)比較重要：

????????InnoDB緩沖池

????????InnoDB日志文件和MyISAM數(shù)據(jù)的操作系統(tǒng)緩存

????????MyISAM健緩存

????????查詢緩存

????????無法手工分配的緩存川陆，如二進(jìn)制日志和表定義文件的操作系統(tǒng)緩存

????????其他緩存相對(duì)占用空間較小

25剂习、MySQL的查詢緩存檢查

????【內(nèi)容部分來自《深入分布式緩存從原來到實(shí)踐》】

????相關(guān)的命令：

????????show variables like‘query_cache%’

????????show status like ‘Qcache%’

????Query Cache命中率= Qcache_hits/(Qcache_hits +Qcache_inserts)

26、每個(gè)連接需要多少內(nèi)存

【TODO】

27较沪、線程緩存（thread_cache_size）

????設(shè)置“線程池大小”的概念鳞绕，新連接進(jìn)入時(shí)可以從線程緩存中取一個(gè)線程，并分配給這個(gè)新連接尸曼。

????Thread_cache_size：可緩存的線程數(shù)们何。可以通過show status

????like ‘threads_created’查看以下狀態(tài)骡苞。下面還有描述垂蜗。

28、表緩存（table_cache_size）

????類似線程緩存解幽，可以理解為對(duì)表結(jié)構(gòu)（.frm文件）的緩存。這個(gè)參數(shù)對(duì)InnoDB的重要性非常小烘苹，InnoDB不依賴這個(gè)信息做很多的事情躲株。

????InnoDB有自己的表緩存：Data Dictionary。

????可以設(shè)置的大一些镣衡，沒啥影響霜定。

29、InnoDB事務(wù)日志

? ??關(guān)于日志文件大小廊鸥，有這樣兩個(gè)參數(shù)：

????????Innodb_log_file_size:默認(rèn)5M

????????Innodb_log_files_in_groups:默認(rèn)2

????默認(rèn)總共10M望浩，在實(shí)際生產(chǎn)環(huán)境中過小，可以調(diào)高到幾百兆或GB惰说。尤其是8版本中磨德，已經(jīng)進(jìn)行優(yōu)化，log文件設(shè)置的大一些沒毛病典挑！

? ??日志緩沖區(qū)參數(shù)：

????????innodb_log_buffer_size:默認(rèn)1M酥宴，相對(duì)有點(diǎn)兒小。10幾M或幾十M都正常您觉。此參數(shù)對(duì)日志IO有影響拙寡。

? ??日志刷新到log文件（即持久化）參數(shù)：

????innodb_flush_log_at_trx_commit:

????????0：每秒持久化到文件，并且刷新琳水。與commit無關(guān)肆糕。

????????1：每次commit，這個(gè)是默認(rèn)值在孝，比較安全

????????2：每秒持久化诚啃，但并不刷新。與0的區(qū)別是MySQL宕機(jī)也不會(huì)丟失數(shù)據(jù)浑玛。但是如果服務(wù)器掛了绍申，會(huì)導(dǎo)致一些事務(wù)丟失。

30顾彰、]InnoDB如何打開和刷新數(shù)據(jù)文件和日志

【TODO极阅，很重要，回頭看涨享，略】

31筋搏、InnoDB表空間

【TODO、看看新版本的定義】

32厕隧、InnoDB的雙寫緩沖

????【TODO奔脐，再看看】

????很多文件系統(tǒng)做了同樣的事情，沒必要讓InnoDB再做一遍吁讨，所以可禁用：innodb_doublewrite = 0髓迎。

32、InnoDB并發(fā)配置：innodb_thread_concurrency

????限制一次性可以有多少線程進(jìn)入內(nèi)核建丧，0表示不限制排龄。

????可參考一下公式：并發(fā)值=cpu數(shù)量*磁盤數(shù)量*2

34、MySQL的二進(jìn)制日志參數(shù)

????sync_binlog參數(shù)：

????????0：表示MySQL不刷新翎朱，交由操作系統(tǒng)自己決定什么時(shí)候持久化橄维。

????????>0：表示每多少次二進(jìn)制日志寫操作進(jìn)行一次持久化，1是個(gè)很安全的設(shè)置拴曲。

????????機(jī)制與事務(wù)日志類似争舞，但是整體開銷比事務(wù)日志要昂貴很多。設(shè)置為1澈灼，對(duì)性能損害可能很大竞川。要慎重。

35、Binlog的格式流译，需要謹(jǐn)慎設(shè)置

????有三種格式：Statement逞怨、Row、Mixed

????????Statement：記錄sql語句福澡。

????????Row：記錄數(shù)據(jù)的實(shí)際變更

????????Mixed：默認(rèn)是Statement叠赦，但在以下情況下是Row格式：

????????????NDB引擎的DML操作

????????????使用了UUID函數(shù)

????????????自增字段更新

????????????包含了Insert Delayed語句

????????????使用了用戶定義函數(shù)（UDF）

????????????使用了臨時(shí)表

? ? *Repeatable-Read隔離級(jí)別下，建議設(shè)置Row格式革砸。Read-Committed隔離級(jí)別下Mixed和Row效果一樣除秀，都是Row格式。

? ? *Row相關(guān)的另一個(gè)參數(shù)binlog_row_image = minimal算利，讓binlog只記錄影響后的行册踩，從而降低binlog增長量。

36效拭、innodb的最重要的兩個(gè)配置參數(shù)

????????innodb_buffer_pool_size

????????innodb_log_file_size：注意：在版本8暂吉，這個(gè)文件要稍微大一些，沒啥影響缎患。

37慕的、關(guān)于max_connections配置

????如果不能執(zhí)行查詢，那多打開一個(gè)連接是沒有好處的挤渔。所以遇到“太多的連接錯(cuò)誤”是一種快速且代價(jià)小的失敗方式肮街。

????可觀察“max_used_connections”和”max_used_connectios_time”來看一下歷史統(tǒng)計(jì)信息张肾。

? ? *需要警惕一下應(yīng)用停機(jī)/重啟時(shí)連接釋放和重連問題淆游，如果未釋放干凈，可能導(dǎo)致重連超限福青。

38眼刃、關(guān)于thread_cache_size配置

????跟線程數(shù)量的波動(dòng)有關(guān)绕辖，不用設(shè)置太大，因?yàn)楸３痔罂臻e線程也沒啥卵用擂红。一個(gè)相關(guān)的狀態(tài)變量是“slow_launch_threads”引镊，如果這個(gè)值比較大，則說明有些情況導(dǎo)致延遲了連接分配新線程篮条。

39、關(guān)于expire_logs_days配置

????二進(jìn)制日志過期時(shí)間吩抓。別手工rm刪除日志文件涉茧，導(dǎo)致MySQL很迷惑自己的處理能力：）。

40疹娶、關(guān)于max_allowed_packet

????允許服務(wù)器接收與發(fā)送的最大數(shù)據(jù)包伴栓，默認(rèn)值太小，但是設(shè)置的太大也有危險(xiǎn)。如果太小钳垮，在復(fù)制時(shí)可能出現(xiàn)問題惑淳，備庫不能接收主庫發(fā)過來的復(fù)制數(shù)據(jù)。

41饺窿、關(guān)于優(yōu)化排序的配置

????two-pass和single-pass排序算法

????max_length_for_sort_data：如果查詢中所需要的列和order by列總大衅缃埂（按字段定義，非實(shí)際存儲(chǔ)數(shù)據(jù)的大卸且健）绢馍，則使用two-pass算法排序。否則使用single-pass算法肠套。

????max_sort_length：當(dāng)對(duì)blob和text排序時(shí)舰涌，只使用前綴，忽略剩余的值你稚。此變量指定前綴大小瓷耙。

42、關(guān)于排序緩存（sort buffer）和讀緩存（read? buffer）需要設(shè)置嗎刁赖？

????默認(rèn)值就很好搁痛，無需配置

。乾闰。落追。。涯肩。轿钠。。

?來自《MySQL管理之道：性能調(diào)優(yōu)病苗、高可用與監(jiān)控（第2版）》

46疗垛、MySQL有哪些形式的鎖

????有以下三種級(jí)別的鎖：

????表級(jí)鎖：開銷小、加鎖快硫朦、不出現(xiàn)死鎖贷腕；粒度大，沖突概率高咬展，并發(fā)度低泽裳。MyISAM引擎屬于這種類型。

????行級(jí)鎖：

????頁面鎖：

47破婆、Per_thread_buffers優(yōu)化

????有這樣的幾個(gè)相關(guān)參數(shù)：

? ??????read_buffer_size：表的順序掃描（如全表掃描）時(shí)涮总，會(huì)臨時(shí)緩沖到這個(gè)區(qū)域，再返回給上層調(diào)用者祷舀。

? ??????read_rnd_buffer_size：與上面的順序讀取不同瀑梗，這個(gè)是隨機(jī)讀取的緩沖區(qū)烹笔。

? ??????sort_buffer_size：order by或group by字段沒用到索引，可用此參數(shù)增加每個(gè)線程的分配緩沖區(qū)來提高性能抛丽，同時(shí)出現(xiàn)“user filesort”谤职，遇到這樣的提示則需要優(yōu)化。

? ??????thread_stack：線程堆棧大小

? ??????join_buffer_size：join中的關(guān)聯(lián)字段沒索引亿鲜，會(huì)使用這個(gè)區(qū)域提高性能允蜈，也同時(shí)出現(xiàn)“using join buffer”，遇到這樣的提示就需要優(yōu)化了狡门。

? ??????binlog_cache_size：如果沒有大事務(wù)陷寝，就不用太大。

? ??????max_connections：最大連接數(shù)

? ??per_thread_buffers的內(nèi)存計(jì)算公式是：sum（上面的buffer size）* max_connections其馏。

48凤跑、Global_buffers優(yōu)化

????有這樣的幾個(gè)相關(guān)參數(shù)：

? ??????innodb_buffer_pool_size：默認(rèn)才128M，太小太小叛复，參考整個(gè)內(nèi)存的70%-80%

? ??????innodb_additional_mem_pool_size：數(shù)據(jù)字典緩存仔引。10幾M就夠。

? ??????innodb_log_buffer_size：日志緩沖區(qū)大小褐奥，與innodb_flush_log_trx_commit參數(shù)聯(lián)合使用咖耘。

? ??????key_buffer_size：MyISAM引擎參數(shù)

? ??????query_cache_size：緩存select語句和結(jié)果集大小的參數(shù)。與query_cache_type聯(lián)合使用撬码，如果頻繁的寫操作儿倒，則最好關(guān)閉這個(gè)緩存，避免緩存過于頻繁的刷新呜笑。

49夫否、關(guān)于初始化參數(shù)中的：innodb_page_size

????配置InnoDB數(shù)據(jù)頁大小，默認(rèn)16K叫胁。書中的案例：相比16k而言凰慈，8k的cpu壓力高一些，select吞吐量也高驼鹅。

50微谓、關(guān)于“謹(jǐn)慎”使用分區(qū)表功能

????分區(qū)字段必須屬于主鍵字段

????where后面的字段必須是分區(qū)字段，否則會(huì)在所有分區(qū)全部掃描一遍输钩。????

51豺型、簡要說明一下MySQL的事務(wù)實(shí)現(xiàn)

????MySQL在進(jìn)行事務(wù)處理的時(shí)候，采用日志先行的方式保證事務(wù)可快速并持久的運(yùn)行买乃，即寫數(shù)據(jù)前触创，先寫日志，過程如下：

????????開始事務(wù)時(shí)記錄該事務(wù)的LSN日志序列號(hào)

????????執(zhí)行事務(wù)時(shí)为牍，往innodb_log_buffer里插入事務(wù)日志（redo log）

????????當(dāng)事務(wù)提交時(shí)哼绑，將日志緩存中的事務(wù)日志刷新到磁盤（依賴另一個(gè)參數(shù)）。

????????除了事務(wù)日志碉咆，數(shù)據(jù)庫還會(huì)記錄一定量的撤銷日志（undo log）抖韩，用于處理回滾。

????????事務(wù)提交后疫铜，首先刷新binlog茂浮，然后再刷新redo log】枪荆【請(qǐng)看下面的問題】

52席揽、數(shù)據(jù)庫操作過程，會(huì)出現(xiàn)以下問題谓厘。也是事務(wù)隔離級(jí)別需要對(duì)付的幾個(gè)問題

? ? 更新丟失幌羞，Lost Update

????臟讀，Dirty Reads

????不可重復(fù)讀竟稳，Non-repeatable Reads

????兩次更新問題属桦，Second lost updates problem

????幻讀，Phantom Reads

53他爸、數(shù)據(jù)庫的四個(gè)事務(wù)隔離級(jí)別

????未授權(quán)讀取聂宾，Read Uncommitted

????授權(quán)讀取，Read Committed

????可重復(fù)讀诊笤，Repeatable Read

????序列化系谐，Serializable

54、關(guān)于“間隙鎖”

????間隙鎖主要是防止幻讀讨跟，用在Repeatable Read隔離級(jí)別纪他。指的是，當(dāng)對(duì)數(shù)據(jù)進(jìn)行條件查詢许赃、范圍檢索時(shí)止喷，對(duì)其范圍內(nèi)的也許并不存在的值進(jìn)行加鎖。其對(duì)高并發(fā)混聊、且范圍更新的業(yè)務(wù)有較大影響弹谁。

其實(shí)Repeatable Read的隔離級(jí)別就比較高，對(duì)高并發(fā)還是有影響的句喜。

55预愤、系統(tǒng)性能評(píng)估工具

????vmstat、sar咳胃、iostat植康、netstat、free展懈、ps销睁、top供璧、mpstat

????dstat、collectl

????淘寶的Tsar

56冻记、一些常用的性能指標(biāo)

? ??CPU：

????????cpu使用率

????????%us：應(yīng)用程序（用戶空間）所花費(fèi)的CPU百分比

????????%sy：系統(tǒng)（內(nèi)核）所花費(fèi)的CPU百分比

????????%ws：I/O等待所需的CPU時(shí)間總和

????????%id：CPU空閑百分比

????????%ni：Nice時(shí)間睡毒，花費(fèi)在執(zhí)行re-nicing（改變進(jìn)程執(zhí)行順序和優(yōu)先級(jí)）的CPU百分比。

? ??內(nèi)存指標(biāo)：

????????空閑內(nèi)存冗栗、交換空間使用

????????磁盤性能指標(biāo)

????????磁盤I/O等待

????????隊(duì)列平均長度

????????平均等待時(shí)間

????????每秒傳輸?shù)臄?shù)量

????????每秒讀寫塊的數(shù)量

????????每秒讀寫字節(jié)的數(shù)量

57演顾、高可用方案

????MMM、MHA

隅居。钠至。。胎源。棉钧。。乒融。掰盘。。赞季。

來自《MySQL 8 Cookbook:Over 150 recipes for high-performance database querying and administration》

58愧捕、獲取Database和Table的信息/Getting information about databases and tables

????這些信息來自于Information_schema，主要包括兩類信息：

????靜態(tài)表元數(shù)據(jù)申钩，static table metadata：Table_Schema次绘、Table_Name、Table_Type撒遣、Engine邮偎。

????動(dòng)態(tài)表元數(shù)據(jù)，Dynamic table metadata：AUTO_INCREMENT,

????AVG_ROW_LENGTH, and?DATA_FREE义黎。收集動(dòng)態(tài)信息是有些成本的禾进。

59、關(guān)于Common table expressions（CTE）

【忽略】

60廉涕、關(guān)于Generated columns

【忽略】

61泻云、關(guān)于Window functions

【忽略】

62、關(guān)于Repeatable Read的幾點(diǎn)知識(shí)

????當(dāng)一個(gè)事務(wù)開始時(shí)并執(zhí)行了第一個(gè)讀時(shí)狐蜕，會(huì)創(chuàng)建一個(gè)讀視圖并保持打開宠纯，直到事務(wù)結(jié)束。為了提供前后一致的查詢結(jié)果层释，InnoDB使用行版本和UNDO信息婆瓜。稱為MVCC-Multi-Version Concurrency Control。

63贡羔、Locking

【很重要的一章廉白，其中參考了MySQL official doc】

? ??MySQL有兩種類型的鎖：

????????內(nèi)部鎖Internal locking：MySQL內(nèi)部用于管理多會(huì)話導(dǎo)致的表內(nèi)容掙用个初。

????????外部鎖External locking：客戶端會(huì)話可以直接獲取表鎖，阻止其他會(huì)話訪問表蒙秒。

? ??內(nèi)部鎖主要包括兩種類型：

????????行級(jí)鎖Row-level locks：把訪問的行鎖定勃黍，允許多會(huì)話同時(shí)寫訪問，適用于多用戶晕讲、高并發(fā)、OLTP應(yīng)用程序马澈。只有InnoDB支持行級(jí)鎖瓢省。

????????表級(jí)鎖Table-level locks：MyISAM、MEMORY痊班、MERGE

? ? table使用表級(jí)鎖勤婚。適用于只讀、讀非常多涤伐、單用戶場(chǎng)景馒胆。

64、External Locking

????使用Lock table和Unlock table來控制鎖凝果，分讀鎖和寫鎖祝迂。

? ??????READ讀鎖：允許其他會(huì)話讀訪問，阻塞所有會(huì)話寫訪問器净，其他會(huì)話也可以同時(shí)獲得讀鎖型雳，讀鎖是共享鎖（shared lock）。

? ??????WRITE寫鎖：只允許當(dāng)前會(huì)話讀寫山害，阻塞其他會(huì)話讀/寫訪問纠俭，其他會(huì)話也不可以同時(shí)獲取任何鎖，寫鎖是排它鎖（exclusive

lock）浪慌。

????使用Unlock table語句或會(huì)話結(jié)束冤荆，可釋放鎖。

????語法：

????????Lock tables table_name [READ|WRITE]

????????Unlock tables

????????FLUSH TABLES WITH READ LOCK;?????? 相當(dāng)于凍結(jié)了整個(gè)數(shù)據(jù)庫权纤。

65钓简、鎖隊(duì)列Locking queue

????1、只有共享鎖可以同時(shí)作用在表上

????2妖碉、如果已有共享鎖涌庭，隨后有請(qǐng)求獲取排它鎖，那這個(gè)排它鎖會(huì)被放入一個(gè)隊(duì)列欧宜，直到共享鎖被釋放坐榆。

????3、只要鎖隊(duì)列中有鎖請(qǐng)求冗茸，那么隨后的鎖請(qǐng)求都會(huì)被放入隊(duì)列席镀。

? ? 4匹中、

????????4.1、InnoDB在進(jìn)行讀/寫表的過程中豪诲，會(huì)獲取元數(shù)據(jù)鎖顶捷。

????????4.2、如果此時(shí)第二個(gè)事務(wù)申請(qǐng)寫鎖（lock table write）屎篱，那此請(qǐng)求會(huì)被放入鎖隊(duì)列服赎。

????????4.3、如果此時(shí)有第三個(gè)事務(wù)僅僅是想讀數(shù)據(jù)交播，那也會(huì)別阻塞重虑，也會(huì)被放入鎖隊(duì)列。

上述規(guī)則還是挺有意思的秦士，總之缺厉，最好別使用lock table。因?yàn)殡S后的事務(wù)會(huì)被放入隊(duì)列隧土，而不是“直接錯(cuò)誤”提针。超級(jí)危險(xiǎn)。

66曹傀、InnoDB Locking

【很重要的一章】

67辐脖、二進(jìn)制日志Binary Logging

????數(shù)據(jù)和結(jié)構(gòu)變化都會(huì)記錄到二進(jìn)制日志中。二進(jìn)制日志可用于：

????????Replication復(fù)制：主從復(fù)制卖毁。

????????Point-in-time recovery時(shí)間點(diǎn)恢復(fù)揖曾。

????日志格式（binlog_format）有三種：Row、Statement亥啦、Mixed

68炭剪、Backup and Restoring，備份與恢復(fù)

【忽略】

69翔脱、關(guān)于復(fù)制-Replication的一些拓補(bǔ)結(jié)構(gòu)（topologies）

????主從復(fù)制master-slave replication

????鏈復(fù)制chain replication：masteràrelay masteràslave奴拦。

????主主復(fù)制master-master replication、

????多源復(fù)制multi-source replication：一個(gè)從服務(wù)可以接受來自多個(gè)主服務(wù)的數(shù)據(jù)届吁。

70错妖、關(guān)于延遲復(fù)制（delayed Replication）

????主要是防止嚴(yán)重的應(yīng)用層問題（如drop table等）被快速傳遞到從庫。

71疚沐、關(guān)于半同步復(fù)制（semi-synchronous replication）

????默認(rèn)是異步復(fù)制的暂氯，Master不關(guān)心從機(jī)是否進(jìn)行正常的復(fù)制，這可能有數(shù)據(jù)丟失風(fēng)險(xiǎn)亮蛔。

????半同步復(fù)制是Master確保至少一臺(tái)Slave已經(jīng)收到了“writes”痴施。

????全同步復(fù)制是Master保證所有Slave提交事務(wù)。

72、關(guān)于日志

????MySQL日志包括以下類型（注意：是MySQL的日志辣吃，不是存儲(chǔ)引擎的）：

????????錯(cuò)誤日志动遭，error log

????????通用查詢?nèi)罩荆琯eneral query log

????????慢查詢?nèi)罩旧竦茫瑂low query log厘惦、

????????二進(jìn)制日志，binary logs哩簿、

????????中繼日志宵蕉，relay logs

????????DDL logs

????????InnoDB相關(guān)的日志：

????????????Redo Log

????????????Undo Log

73、關(guān)于性能調(diào)整节榜，相關(guān)的概念有以下

? ??執(zhí)行計(jì)劃国裳，explain plan

? ??基準(zhǔn)查詢和服務(wù)，Benchmarking queries and server

? ??增加索引全跨，Adding indexes

? ??看不見的索引，Invisible index：如果要?jiǎng)h除索引亿遂，可以先讓它Invisible浓若，觀察一下刪除索引的影響。

? ??降序索引蛇数，Descending index：相對(duì)Ascending index（升序索引）挪钓，可定義降序索引。

? ??慢查詢分析耳舅，Analyzing slow queries using pt-query-digest

? ??優(yōu)化數(shù)據(jù)類型碌上，Optimizing datatypes

? ??移除重復(fù)和冗余索引，Removing duplicate and redundant indexes

? ??檢查索引的使用浦徊，Checking index usage

? ??控制查詢優(yōu)化器馏予，Controlling the query optimizer

? ??使用索引暗示，Using index hints

? ??使用資源分組盔性，Using resource groups

? ??使用performance_schema

? ??使用sys schema

74霞丧、關(guān)于Secondary indexes

????Primary key（clustered index）和Secondaryindexes

75、用put-query-digest分析慢查詢

????不只是慢查詢?nèi)罩久嵯悖@個(gè)工具還可以通過多種渠道收集查詢：

????????慢查詢?nèi)罩居汲ⅲ瑂low query log

????????通用查詢?nèi)罩荆琯eneral query log

????????過程列表悉尾，process list

????????二進(jìn)制日志突那，Binary log

????????TCP dump

來自《MySQL Documentation——MySQL Reference Manual》

76、關(guān)于優(yōu)化

????很多很多构眯，包括從以下方面考慮優(yōu)化愕难，每個(gè)方面都是很大的課題

? ??????SQL語句

? ??????索引，Index

? ??????數(shù)據(jù)庫結(jié)構(gòu)

? ??????引擎特定的優(yōu)化：InnoDB、MyISAM务漩、Memory等引擎

? ??????查詢執(zhí)行計(jì)劃

? ??????控制查詢優(yōu)化器

? ??????緩沖與緩存（Buffering and Caching）

? ??????鎖優(yōu)化

? ??????MySQL Server優(yōu)化

? ??????性能測(cè)試拄衰，Measuring Performance(Benchmarking)????

? ??????線程信息檢查，Examining ThreadInformation

77饵骨、關(guān)于優(yōu)化SQL Statements

????SQL語句翘悉，從使用目的上可分為兩種類型：1、數(shù)據(jù)處理居触；2妖混、系統(tǒng)監(jiān)控。因此轮洋，SQL優(yōu)化也從這兩種目的上分別進(jìn)行制市。

????SQL 語句的優(yōu)化包括很多范圍，大致如下：

? ??????優(yōu)化Select語句

? ??????優(yōu)化子查詢弊予、派生表祥楣、視圖引用、公用表表達(dá)式

? ??????優(yōu)化Information Schema查詢

? ??????優(yōu)化Performance Schema查詢

? ??????優(yōu)化數(shù)據(jù)變更語句（insert汉柒、update误褪、delete）

? ??????優(yōu)化數(shù)據(jù)庫權(quán)限

? ??????還有其他一些小地方【略】

78、關(guān)于Select優(yōu)化

????看看標(biāo)題碾褂，不詳述了：

79兽间、關(guān)于Where優(yōu)化

????一句話，去掉沒用的條件正塌；優(yōu)化條件嘀略。

????早期檢查常數(shù)表達(dá)式

????如果沒有分組函數(shù)和Group By，Having與where合并乓诽。

????構(gòu)造簡單的Where條件帜羊，以便獲得快速Where評(píng)估。并盡可能多的過濾掉行问裕。

????固定表格（no row或只有1行的表逮壁；主鍵匹配）優(yōu)先加載。

????如果order和group都來自一個(gè)表粮宛，這個(gè)表最好能作為join的第一個(gè)

????Order和group不一樣窥淆，或Order來自不同的表，那會(huì)導(dǎo)致創(chuàng)建臨時(shí)表巍杈。

80忧饭、關(guān)于Limit 查詢優(yōu)化

【不詳細(xì)說了，看原文吧筷畦，上面有類似內(nèi)容】

????分為兩部分：

? ? ? ? 需要分頁時(shí)词裤，考慮如何優(yōu)化limit刺洒。Limit作為優(yōu)化對(duì)象。

? ? ? ? 進(jìn)行一些查詢時(shí)（如order by）吼砂，可以考慮用limit去優(yōu)化它們逆航。Limit作為優(yōu)化手段。

81渔肩、重點(diǎn)看看InnoDB的大量數(shù)據(jù)加載問題：Bulk Data Loading for InnoDB Tables

????????這跟Insert大量數(shù)據(jù)的優(yōu)化有關(guān)因俐，考慮一下操作：

? ? ? ? ? ? 關(guān)閉自動(dòng)提交，否則導(dǎo)致更頻繁的flush log周偎。

????????????如果有Secondary唯一索引抹剩，先關(guān)閉唯一性檢查。

????????????如果有外鍵蓉坎，先關(guān)閉外鍵檢查澳眷。對(duì)大表來說，可節(jié)省超多的IO蛉艾。

????????????使用多行插入Insert into yourtable values （,,）钳踊，（,,），…勿侯。

????????????如果有自增列箍土，可把innodb_autoinc_lock_mode設(shè)置為2

????????????按照主鍵的順序插入

82、關(guān)于InnoDB的若干優(yōu)化

? ??表格存儲(chǔ)布局優(yōu)化：考慮使用optimize table來重組表格罐监；盡可能縮短主鍵長度；如果varchar太長考慮使用前綴索引瞒爬；不固定長度的字符串使用varchar替代char弓柱；大表考慮使用Compressed行格式，以及考慮使用全表掃描等侧但。

? ??事務(wù)管理優(yōu)化：找到事務(wù)特性的性能開銷與工作負(fù)載的平衡點(diǎn)矢空。Autocommit設(shè)置對(duì)日志刷新和只讀事務(wù)的影響；避免大數(shù)據(jù)操作后的回滾禀横，會(huì)惡化性能屁药；事務(wù)日志的刷新機(jī)制；

? ??只讀事務(wù)優(yōu)化：對(duì)只讀事務(wù)避免設(shè)置事務(wù)ID而產(chǎn)生的開銷柏锄。系統(tǒng)內(nèi)有與優(yōu)化機(jī)制酿箭。

? ??Redo日志優(yōu)化：太小會(huì)導(dǎo)致不必要的寫磁盤，最新版本下趾娃，大的Redo文件不會(huì)導(dǎo)致太長時(shí)間的恢復(fù)缭嫡，已經(jīng)優(yōu)化了。如果有大事務(wù)抬闷，要把緩沖區(qū)設(shè)置的大一些妇蛀。

? ??大量數(shù)據(jù)加載優(yōu)化：尤其是批量insert場(chǎng)景下耕突，進(jìn)行特定的設(shè)置。

? ??查詢優(yōu)化：關(guān)鍵是對(duì)index的創(chuàng)建和使用评架。

? ??DDL操作優(yōu)化：比如使用truncate table眷茁，如果有外鍵可先drop table再create。

? ??磁盤I/O優(yōu)化：通常的緩存設(shè)置纵诞；日志刷新機(jī)制等上祈；使用Linux native AIO等等；利用多個(gè)磁盤挣磨；

? ??配置優(yōu)化：很多配置項(xiàng)優(yōu)化

? ??多表的系統(tǒng)優(yōu)化：【看原文吧】

83雇逞、InnoDB的鎖

? ??共享鎖和排它鎖（Shared and Exclusive Locks）：InnoDB實(shí)現(xiàn)了標(biāo)準(zhǔn)的行級(jí)鎖，包括有兩種類型：共享鎖（S鎖）和排它鎖（X鎖）茁裙。

? ??意向鎖（Intention Locks）：InnoDB支持多種粒度鎖共存塘砸，行級(jí)鎖和表級(jí)鎖，比如使用Lock table write鎖定表晤锥。為了提供多種粒度級(jí)別上進(jìn)行實(shí)際鎖定掉蔬，InnoDB使用意向鎖（Intention Lock）。意向鎖是表級(jí)鎖矾瘾，用于指示事務(wù)稍后對(duì)于表中的行需要哪種類型的鎖（共享或獨(dú)占）女轿。意向共享鎖（IS）（Select for share）表示事務(wù)意圖獲取表的每行共享鎖。意向排它鎖（IX）（Select for update）表示事務(wù)意圖獲取表的每行排它鎖壕翩。

? ??記錄鎖（Record Locks）：對(duì)索引記錄進(jìn)行加鎖蛉迹，即使一個(gè)表沒有定義索引，InnoDB引擎也會(huì)創(chuàng)建一個(gè)隱藏的聚簇索引放妈。

? ??間隙鎖（Gap Locks）：索引記錄之間的“間隙”進(jìn)行加鎖北救，或者是第一個(gè)記錄之前，或者是最后一個(gè)記錄之后的“間隙”芜抒。所謂間隙珍策，有可能有1個(gè)值或多個(gè)值，也可能是空宅倒。

? ??下一主鍵鎖（Next-Key Locks）：記錄鎖和間隙鎖的組合攘宙。

? ??插入意向鎖（Insert Intention Locks）：是間隙鎖的一種，多個(gè)事務(wù)對(duì)同一間隙序列進(jìn)行插入時(shí)需要等待拐迁。

? ??自增鎖（AUTO-INC Locks）：表級(jí)鎖蹭劈，這個(gè)innodb_autoinc_lock_mode配置對(duì)自增機(jī)制有影響。

? ??空間索引的斷言鎖（Predicate Locks for Spatial Indexes）：【略】