MySQL數據庫之大廠面試必備技能

程序那點事07:27優質科技領域創作者

概述

來源：https://www.cnblogs.com/itxiaoshen/p/15463890.html

定義

MySQL官方地址 https://www.mysql.com/ MySQL 8系列最新版本為8.0.27，5系列的最新版本5.7.35MySQL發展至今仍然是世界上最流行的關系型數據庫管理系統,使用C和C++語言編寫，並且為很多種編程語言提供API.能夠處理千萬級別的數據，市場占有量還是老大哥地位。

安裝

MySQL安裝有多種方式，可使用yum安裝，也可以從官網下載指定版本解壓安裝既可，比較簡單，安裝完完成配置文件，linux下配置文件為my.cnf，windows配置文件為my.ini。

當然還可以使用更為簡單docker容器化的安裝方式

連接客戶端

我們日常連接MySQL客戶端工具可以選擇使用Navicat For MySQL、SQLyog Community 、MySQL Workbench和phpMyAdmin(web界面操作)，具體就按個人習慣喜好去選擇，而我們本篇主要是MySQL面試部分，這裏就不太展開了，關於MySQL實戰應用部分後續我們再單獨來剖析。

MySQL版本

MySQL創立於1996年,最初是由瑞典 MySQL AB公司開發所有,在2008年被Sun公司收購,而Sun公司又在2009年被Oracle公司收購，目前MySQL屬於Oracle旗下的產品。MySQL官網提供了多種版本，我們來了解一些，但我們常用的還是社區版本。

MySQL Community Server：開源免費的社區版本，不提供技術支持。MySQL Enterprise Edition：收費企業版本或商業版本，可試用，提供技術支持。MySQL Cluster：Cluster集群版，這也是開源免費的，但相對應工具集是收費的，它由一組計算機構成，適合於分布式計算環境的高實用、高冗餘版本。它采用了NDB Cluster 存儲引擎，允許在1個 Cluster 中運行多個MySQL服務器，也即是可將幾個MySQL Server封裝成一個Server。隨數據庫容量增加，每個數據數據節點需要添加更多的內存，增加使用成本。犧牲部分sql語言特性。MySQL Cluster 自動將表分片（或分區）到不同節點上，使數據庫可以在低成本的商用硬件上橫向擴展，同時保持對應用程序完全應用透明。憑借其分布式、無共享架構，MySQL Cluster 可提供 99.999% 的可用性，確保了較強的故障恢複能力和在不停機的情況下執行預定維護的能力。MySQL Cluster 讓用戶可以在解決方案中整合關系數據庫技術和 NoSQL 技術中的最佳部分，從而降低成本、風險和複雜性。MySQL Cluster 提供實時的響應時間和吞吐量，能滿足最苛刻的 Web、電信及企業應用程序的需求。
具有跨地域複制功能的多站點集群。跨地域複制使多個集群可以分布在不同的地點，從而提高了災難恢複能力和全球 Web 服務的擴展能力。為支持持續運營，MySQL Cluster 允許向正在運行的數據庫模式中聯機添加節點和更新內容，因而能支持快速變化和高度動態的負載。優點缺點MySQL Cluster CGE：Cluster高級集群版，需付費

而官方提供另外一個工具MySQL Workbench（GUI TOOL）是Workbench專為MySQL設計的ER/數據庫建模工具，它是著名的數據庫設計工具DBDesigner4的繼任者，MySQL Workbench又分為兩個版本，分別是社區版（MySQL Workbench OSS）和商用版（MySQL Workbench SE）。

MySQL其他分支

上面說到MySQL屬於Oracle旗下的產品，由於Oracle已經有了一個商業數據庫，他們擔心MySQL作為領先的免費開源數據庫提供的功能可能太少、發布周期太慢並且可能需要支付更昂貴費用，因此許多基於MySQL其他分支也就應運而生，我們也簡單了解一下。

Percona Server：是MySQL重要的分支之一，它基於InnoDB存儲引擎的基礎上，提升了性能和易管理性，Percona XtraDB 是 InnoDB 存儲引擎的增強版,被設計用來更好的使用更新計算機硬件系統的性能,同時還包含有一些在高性能環境下的新特，可以用來更好地發揮服務器硬件上的性能,XtraDB 設計的主要目的是用以替代現在的 InnoDB。所以Percona Server也可以稱為增強的MySQL與開源的插件（plugin）的結合。由於官方版本的MySQL在一些特性的使用上有一定的局限性，需要收費。所以Percona Server就有了一定的市場占有比例，也比較受大家的歡迎。像一些常用的工具包xtrabackup、percona-toolkit等，在生產環境中是DBA的必備武器。還有像XtraDB-Cluster這種支持多點寫入的強同步高可用集群架構，真正實現實時同步的過程，解決了MySQL主從複制之間經常出現並讓人頭疼的延遲問題。而且Percona還收購了TokuDB公司，TokuDB存儲引擎非常優秀，淘寶網、阿裏雲上大量在使用這款存儲引擎。它支持數據壓縮，支持hot scheme modification，具有高擴展性和優秀的查詢插入性能。MariaDB：Mariadb是由MySQL創始人Monty創建的，主要由開源社區維護，采用GPL授權許可，是一款高度兼容的MySQL產品，MariaDB提供了MySQL提供的標准存儲引擎，即MyISAM和InnoDB；因此實際上可以將它視為MySQL的擴展集，它不僅提供MySQL提供的所有功能還提供其他功能，因此從MySQL切換到MariaDB時無需更改任何基本代碼即可。它不僅僅是MySQL的一個替代品，還創新與提高了MySQL原有的技術。它與Percona產品非常類似，既包含了Percona的XtraDB存儲引擎，還包含TokuDB存儲引擎、Spider水平分片存儲引擎等多種存儲引擎，並且還有一些複制功能上的新特性，比如基於表的並行複制、Multi-source Replication多源複制、Galera Cluster集群。MariaDB有一套Java的管理系統，可以通過投票機制來決定哪些特性和參數是我們需要的。

HeatWave

我們都知道MySQL主要是為OLTP場景設計的，OLAP一直是MySQL的短板，內置 MySQL 數據庫服務實時查詢加速器，在HeatWave出現之前，我們通常不能直接使用MySQL數據庫進行OLAP分析，因為其性能不足。於是我們往往是先將MySQL數據同步到HDFS/Hbase/Kudu等存儲系統上，再使用MR/Spark/Impala等計算引擎做計算,而HeatWave的到來意味著我們可以“少此一舉”。

HeatWave 是一種用於 Oracle MySQL 數據庫服務的大規模並行、高性能、內存中查詢加速器，可將分析和混合工作負載的 MySQL 性能提高幾個數量級。HeatWave 以一半的成本比 Amazon Redshift 快 6.5 倍，以五分之一的成本比 Snowflake 快 7 倍，以一半的成本比 Amazon Aurora 快 1400 倍。帶有 HeatWave 的 MySQL 數據庫服務是唯一使客戶能夠直接從他們的 MySQL 數據庫運行 OLTP 和 OLAP 工作負載的服務。這消除了複雜、耗時且昂貴的數據移動以及與單獨分析數據庫的集成的需要。新的 MySQL Autopilot 使用先進的機器學習技術來自動化 HeatWave，使其更易於使用並進一步提高性能和可擴展性。

HeatWave是Oracle雲上的架構，實現了分布式計算框架，HeatWave 是為 Oracle MySQL 數據庫服務開發的內存查詢加速器。它是一個大規模並行、混合、列式、查詢處理引擎，具有用於分布式查詢處理的最先進算法，可為查詢提供非常高的性能。與其他OLAP計算框架的區別是，HeatWave與MySQL結合封裝得更好。從運維角度看，首先不再需要做數據同步操作，因為數據存儲還是MySQL的InnoDB。在計算的時候把InnoDB的數據讀到節點的內存中進行計算，此時InnoDB的更新也會即時同步。HeatWave可以智能地自動配置集群大小，而不需要人為配置，不會浪費資源也不會資源不足。從使用角度來看，使用者應該是無感知的，仍然是通過原有方式(JDBC/ODBC)連接和使用，能感知到的只是OLAP查詢速度快了很多。

MySQL面試必備

將原理與實際操作經驗相結合，本章不講sql用法，主要針對的是開發人員需掌握的一些MySQL知識點，涉及索引、事務、優化等方面

說說MySQL存儲引擎？

MySQL支持存儲引擎很多種，默認已支持上面9種，5.7.35版本也是一樣，但我們常用存儲引擎主要是InnoDB和MyISAM這兩種。

MySQL InnoDB和MyISAM兩大引擎對比？

InnoDB存儲引擎：從MySQL5.5版本之後，MySQL的默認內置存儲引擎已經是InnoDB了，他的主要特點有：使用共享表空間存儲：所有的表和索引存放在同一個表空間中。使用多表空間存儲：表結構放在frm文件，數據和索引放在IBD文件中。分區表的話，每個分區對應單獨的IBD文件，分區表的定義可以查看我的其他文章。使用分區表的好處在於提升查詢效率。InnoDB是MySQL5.5之後默認的數據庫存儲引擎，支持事務，支持外鍵災難恢複性比較好；支持事務。默認的事務隔離級別為可重複度，通過MVCC（並發版本控制）來實現的。使用的鎖粒度為行級鎖，可以支持更高的並發；支持外鍵；配合一些熱備工具可以支持在線熱備份，有很成熟的在線熱備解決方案；在InnoDB中存在著緩沖管理，通過緩沖池，將索引和數據全部緩存起來，加快查詢的速度；對於InnoDB類型的表，其數據的物理組織形式是聚簇表。所有的數據按照主鍵來組織。數據和索引放在一塊，都位於B+數的葉子節點上；MyISAM存儲引擎：在5.5版本之前，MyISAM是MySQL的默認存儲引擎，該存儲引擎並發性差，不支持事務，所以使用場景比較少，主要特點為：每個MyISAM在磁盤上存儲成3個文件索引的結構是B+樹結構其文件名都和表名相同，但拓展名分別是 ：通過MYI的B+樹結構來查找記錄頁，再根據記錄頁查找記錄。並且支持全文索引、B樹索引和數據壓縮,支持數據的類型有以下三種frm文件：存儲表的定義數據,可以存放在不同的目錄,平均分布IO，獲得更快的速度MYD文件：存放表具體記錄的數據,可以存放在不同的目錄,平均分布IO，獲得更快的速度MYI文件：存儲索引,僅保存記錄所在頁的指針靜態固定長度表動態可變長表壓縮表MyISAM是MySQL5.5之前的默認存儲引擎，MyISAM既不支持事務，也不支持外鍵不支持事務；不支持外鍵，如果強行增加外鍵，不會提示錯誤，只是外鍵不其作用；對數據的查詢緩存只會緩存索引，不會像InnoDB一樣緩存數據，而且是利用操作系統本身的緩存；默認的鎖粒度為表級鎖，所以並發度很差，加鎖快，鎖沖突較少，所以不太容易發生死鎖；支持全文索引（MySQL5.6之後，InnoDB存儲引擎也對全文索引做了支持），但是MySQL的全文索引基本不會使用，對於全文索引，現在有其他成熟的解決方案，比如：ElasticSearch，Solr，Sphinx等。數據庫所在主機如果宕機，MyISAM的數據文件容易損壞，而且難恢複；查詢性能上，MyISAM的查詢效率高於InnoDB，因為InnoDB在查詢過程中，是需要維護數據緩存，而且查詢過程是先定位到行所在的數據塊，然後在從數據塊中定位到要查找的行；而MyISAM可以直接定位到數據所在的內存地址，可以直接找到數據；SELECT COUNT(*)語句，如果行數在千萬級別以上，MyISAM可以快速查出，而InnoDB查詢的特別慢，因為MyISAM將行數單獨存儲了，而InnoDB需要朱行去統計行數；所以如果使用InnoDB，而且需要查詢行數，則需要對行數進行特殊處理，如：離線查詢並緩存；

MySQL常用存儲引擎的底層原理？

這裏小編先推薦一個數據結構可視化的網站，可以幫助我們學習各種數據結構的底層原理，常見的查詢算法,順序查找,二分查找,二叉排序樹查找,哈希散列法,分塊查找,平衡多路搜索樹 B 樹（B-tree）數據結構可視化學習網站 https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

InnoDB和MyISAM這兩種引擎底層都是采用B+樹的數據結構來構建索引。

B+樹是B樹的變種，B樹是一棵多路平衡查找樹，簡單來說，B樹可以看做平衡二叉樹的進階版，它與平衡二叉樹的不同點主要在B樹的一個節點可以存放多個關鍵字，並且B樹的每個節點可以有兩個以上的子節點，而這些都取決於B樹的階數，當B樹的階數為2時，它就是一個普通的平衡二叉樹。單純從數據結構來講，B樹和平衡二叉樹在查找的時間複雜度上並沒有什麼區別，但數據結構比平衡二叉樹複雜一些，計算機讀取數據的操作中最耗時的是從磁盤中讀取數據，在大多數linux系統中，每次磁盤io會取出4k的連續數據；平衡二叉樹因為每個關鍵字都存放在獨立的節點，無法保證在磁盤中的物理存儲地址是連續的，因此，在最壞的情況下，每個關鍵字的讀取都需要進行一次磁盤IO。而B樹的每個節點可以存放多個關鍵字，每個節點的關鍵字在磁盤中的物理存儲地址都是連續的，使得每次磁盤IO都可以讀取多個關鍵字，大大減少了磁盤IO次數，使得查找時間更快。我們描述一棵B樹是需要定義它的階數，階數定義了它最多會有多少個子節點。下面是一棵3階的B樹示例圖，一棵m階的B樹簡單定義如下：

每個節點最多可以有m個子節點每個節點最多可以存放m-1個關鍵字

B+樹是B樹的變種，在B+樹中，所有的關鍵字都會保存在葉子節點中，葉子節點之間也會有指針進行連接，形成一個鏈表的形式，和B樹相比，這樣的結構方便範圍查找。比如要查詢大於3的關鍵字，我們從根節點往下遍歷，找到關鍵字為3的葉子節點之後，直接讀取3之後的葉子節點就可以了，而不用一次次的從根節點去遍歷大於3的關鍵字。當我們進行的範圍查找進行倒序操作的時候，憑借葉子節點的單向鏈表是無法實現的，因此MySQL中的B+樹結構做了一些調整，MySQL將B+樹葉子節點的單向鏈表改為雙向鏈表。而相同節點的B+樹則如下，紅色的箭頭線是MySQL特殊改造實現的。

B+降低樹的高度又能支持範圍索引，MySQL在使用Innodb引擎的時候頁大小默認是16K，一個三層B+樹如果以十幾個字節作為一個索引節點，葉子節點為800字節，那麼也可存儲兩千萬級別數據（1024*1024*20）

基於B+為底層實現，我們也明白MySQL Innodb為何推薦使用自增ID作為主鍵，不要使用UUID這種作為主鍵，因為如果是自增主鍵增加節點就能順序依次追加到後面即可，避免B+的結構出現分裂帶來性能開銷，在InnoDB存儲引擎中，主鍵索引是作為聚簇索引存在的,也就是說,主鍵索引的B+樹葉子節點上存儲了主鍵索引以及全部的數據(按照順序),如果主鍵索引是自增ID,那麼只需要不斷向後排列即可,如果是UUID,由於到來的ID與原來的大小不確定,會造成非常多的數據插入,數據移動,然後導致產生很多的內存碎片,進而造成插入性能的下降.總之,在數據量大- -些的情況下,用自增主鍵性能會好一-些。

Hash索引和B+樹所有有什麼區別？

索引是一種數據結構,可以幫助我們快速的進行數據的查找。索引的數據結構和具體存儲引擎的實現有關,在MySQL中使用較多的索引有B+樹、Hash索引。Hash索引和B+樹相比如下：

hash索引底層就是hash表,進行查找時,調用一次hash函數就可以獲取到相應的鍵值,之後進行回表查詢獲得實際數據.B+樹底層實現是多路平衡查找樹.對於每一次的查詢都是 從根節點出發,查找到葉子節點方可以獲得所查鍵值然後根據查詢判斷是否需要回表查詢數據.hash索引進行等值查詢更快(-般情況下),但是卻無法進行範圍查詢.因為在hash索引中經過hash函數建立索引之後,索引的順序與原順序無法保持-致,不能支持 範圍查詢.而B+樹的的所有節點皆遵循(左節點小於父節點,右節點大於父節點多叉樹也類似,天然支持範圍.hash索引不支持使用索引進行排序,原理同上.hash索引不支持模糊查詢以及多列索引的最左前綴匹配.原理也是因為hash函數的不可預測AAAA和AAAB的索引沒有相關性.hash索 引任何時候都避免不了回表查詢數據,而B+樹在符合某些條件(聚簇索引,覆蓋索引等)的時候可以只通過索引完成查詢.hash索引雖然在等 值查詢上較快,但是不穩定.性能不可預測,當某個鍵值存在大量重複的時候發生hash碰撞,此時效率可能極差.而B+樹的查詢效率比較穩定，對於

如何選擇合適的存儲引擎？

使用場景是否需要事務支持；是否需要支持高並發，InnoDB的並發度遠高於MyISAM；是否需要支持外鍵；是否需要支持在線熱備；高效緩沖數據，InnoDB對數據和索引都做了緩沖，而MyISAM只緩沖了索引；索引，不同存儲引擎的索引並不太一樣；在選擇存儲引擎時，應該根據應用系統的特點選擇合適的存儲引擎。對於複雜的應用系統，還可以根據實際情況選擇多種存儲引擎進行組合。以下是幾種常用的存儲引擎的使用環境。InnoDB : 是Mysql的默認存儲引擎，用於事務處理應用程序，支持外鍵。如果應用對事務的完整性有比較高的要求，在並發條件下要求數據的一致性，數據操作除了插入和查詢意外，還包含很多的更新、刪除操作，那麼InnoDB存儲引擎是比較合適的選擇。InnoDB存儲引擎除了有效的降低由於刪除和更新導致的鎖定， 還可以確保事務的完整提交和回滾，對於類似於計費系統或者財務系統等對數據准確性要求比較高的系統，InnoDB是最合適的選擇。MyISAM ： 如果應用是以讀操作和插入操作為主，只有很少的更新和刪除操作，並且對事務的完整性、並發性要求不是很高，那麼選擇這個存儲引擎是非常合適的。MEMORY：將所有數據保存在RAM中，在需要快速定位記錄和其他類似數據環境下，可以提供幾塊的訪問。MEMORY的缺陷就是對表的大小有限制，太大的表無法緩存在內存中，其次是要確保表的數據可以恢複，數據庫異常終止後表中的數據是可以恢複的。MEMORY表通常用於更新不太頻繁的小表，用以快速得到訪問結果。MERGE：用於將一系列等同的MyISAM表以邏輯方式組合在一起，並作為一個對象引用他們。MERGE表的優點在於可以突破對單個MyISAM表的大小限制，並且通過將不同的表分布在多個磁盤上，可以有效的改善MERGE表的訪問效率。這對於存儲諸如數據倉儲等VLDB環境十分合適。

什麼是聚簇索引和非聚簇索引？

在B+樹的索引中,葉子節點可能存儲了當前的key值,也可能存儲了當前的key值以及整行的數據,這就是聚簇索引和非聚簇索引.在InnoDB中,只有主鍵索引是聚簇索引,如果沒有主鍵,則挑選一個唯一鍵建立聚簇索引.如果沒有唯一鍵，則隱式的生成一個鍵來建立聚簇索引.

當查詢使用聚簇索引時,在對應的葉子節點,可以獲取到整行數據,因此不用再次進行回表查詢.

非聚簇索引一定會回表詢嗎?不一定,這涉及到查詢語句所要求的字段是否全部命中了索引,如果全部命中了索引,，那麼就不必再進行回表查詢.

舉個簡單的例子,假設我們在員工表的年齡上建立了索引,那麼當進行select age from employee where age < 20的查詢時,在索引的葉子節點上,已經包含了age信息,不會再次進行回表查詢.

說說MySQL的聯合索引？

MySQL可以使用多個字段同時建立一個索引,叫做聯合索引.在聯合索引中,如果想要 命中索引,需要按照建立索引時的字段順序挨個使用，否則無法命中索引.MySQL聯合索引底層數據結構也還是B+樹，遵循索引最左前綴原則，單列索引其實也可以看做索引列為1的聯合索引，聯合索引的底層存儲跟單列索引時類似的，聯合索引是每個樹節點中包含多個索引值，在通過索引查找記錄時，會先將聯合索引中第一個索引列與節點中第一個索引值進行匹配，匹配成功接著匹配第二個索引列和索引值，直到聯合索引的所有索引列都匹配完；如果過程中出現某一個索引列與節點相應位置的索引值不匹配的情況，則無需再匹配節點中剩餘索引列。

比如：索引包含表中每一行的last_name、first_name和dob列，即key(last_name, first_name, dob)，以下情況可以用到索引：

匹配全值：對索引中的所有列都指定具體的值。匹配最左前綴：你可以利用索引查找last name為Allen的人，僅僅使用索引中的第1列。匹配列前綴：你可以利用索引查找last name以J開始的人，這僅僅使用索引中的第1列。匹配值的範圍查詢：可以利用索引查找last name在Allen和Barrymore之間的人，僅僅使用索引中第1列。匹配部分精確而其它部分進行範圍匹配：可以利用索引查找last name為Allen，而first name以字母K開始的人。僅對索引進行查詢：如果查詢的列都位於索引中，則不需要讀取元組的值。

MySQL的binlog有幾種格式?

MySQL的binlog有三種格式,分別是statement、row、mixed。

statement模式下，記錄單元為語句.即每- 個sql造成的影響會記錄.由於sql的執行是有上下文的,因此在保存的時候需要保存相關的信息,同時還有一-些使用了函數之類的語句無法被記錄複制。row級別下,記錄單元為每一-行的改動,基本是可以全部記下來但是由於很多操作,會導致大量行的改動(比如alter table),因此這種模式的文件保存的信息太多，日志量太大。mixed. -種折中的方案,普通操作使用statement記錄,當無法使用statement的時候使用row.此外,新版的MySQL中對row級別也做了- -些優化，當表結構發生變化的時候，會記錄語句而不是逐行記錄。

MySQL如何做調優？

核心理念選擇合適的存儲引擎平衡範式與冗餘，為提高效率可以犧牲範式設計，冗餘數據。最大化利用索引，盡可能避免全表掃描，減少無效數據的查詢；不在數據庫做計算，cpu計算務必移至業務層。控制單表數據量，單表記錄控制在千萬級。控制列數量，字段數控制在20以內。拒絕3B(big)，也即是大sql，大事務，大批量。基本原則減少數據訪問：設置合理的字段類型，啟用壓縮，通過索引訪問等減少磁盤IO；返回更少的數據：只返回需要的字段和數據分頁處理 減少磁盤io及網絡io；減少交互次數：批量DML操作，函數存儲等減少數據連接次數；減少服務器CPU開銷：盡量減少數據庫排序操作以及全表查詢，減少cpu 內存占用；利用更多資源：使用表分區，可以增加並行操作，更大限度利用cpu資源字段類優化NULL字段很難查詢優化NULL字段的索引需要額外空間NULL字段的複合索引無效tinyint(1Byte)/smallint(2Byte)/mediumint(3Byte)/int(4Byte)/bigint(8Byte) bad case：int(1)/int(11)有些字符轉化為數字|：用int而不是char(15)存儲ip優先使用enum或set，例如：sex enum (‘F’, ‘M’)用好數值類型：避免使用NULL字段：索引原則選擇唯一性索引，唯一性索引的值是唯一的，可以更快速的通過該索引來確定某條記錄。為經常需要排序、分組和聯合操作的字段建立索引。為常用作為查詢條件的字段建立索引。限制索引的數目：越多的索引，會使更新表變得很浪費時間。盡量使用數據量少的索引，如果索引的值很長，那麼查詢的速度會受到影響。盡量使用前綴來索引，如果索引字段的值很長，最好使用值的前綴來索引。刪除不再使用或者很少使用的索引。最左前綴匹配原則，非常重要的原則。盡量選擇區分度高的列作為索引區分度的公式是表示字段不重複的比例。索引列不能參與計算，保持列“幹淨”：帶函數的查詢不參與索引。盡量的擴展索引，不要新建索引索引類優化，例如：謹慎合理使用索引改善查詢、減慢更新索引一定不是越多越好（能不加就不加，要加的一定得加）覆蓋記錄條數過多不適合建索引，例如“性別”字符字段必須建前綴索引不在索引做列運算innodb主鍵合理使用自增列，主鍵建立聚簇索引，主鍵不應該被修改，字符串不應該做主鍵，如果不指定主鍵，innodb會使用唯一且非空值索引代替不用外鍵，請由程序保證約束sql類優化如，例如：sql語句盡可能簡單一條sql只能在一個cpu運算大語句拆小語句，減少鎖時間一條大sql可以堵死整個庫簡單的事務事務時間盡可能短調優方法通過慢查詢找到sql語句MySQL提供了explain命令來查看語句的執行計劃,MySQL在執行某個語句之前,會將該語句過一-遍查詢優化器,之後會拿到對語句的分析,也就是執行計劃，其中包含了許多信息.可以通過其中和索引有關的信息來分析是否命中了索引,例如，possilbe_ key,key,key_ len等字段,分別說明了此語句可能會使用的索引,實際使用的索引以及使用的索引長度

MySQL如何應對數據量快速增大？

這裏先介紹下Apache Sharding Sphere ，當當網開源貢獻,已於2020年4月16日成為 Apache 軟件基金會的頂級項目是一套開源的分布式數據庫解決方案組成的生態圈，它由 JDBC、Proxy 和 Sidecar（規劃中）這 3 款既能夠獨立部署，又支持混合部署配合使用的產品組成。
它們均提供標准化的數據水平擴展、分布式事務和分布式治理等功能，可適用於如 Java 同構、異構語言、雲原生等各種多樣化的應用場景。Apache ShardingSphere 旨在充分合理地在分布式的場景下利用關系型數據庫的計算和存儲能力，而並非實現一個全新的關系型數據庫。
關系型數據庫當今依然占有巨大市場份額，是企業核心系統的基石，未來也難於撼動，我們更加注重在原有基礎上提供增量，而非顛覆。Apache ShardingSphere 5.x 版本開始致力於可插拔架構，項目的功能組件能夠靈活的以可插拔的方式進行擴展。
目前，數據分片、讀寫分離、數據加密、影子庫壓測等功能，以及 MySQL、PostgreSQL、SQLServer、Oracle 等 SQL 與協議的支持，均通過插件的方式織入項目。
開發者能夠像使用積木一樣定制屬於自己的獨特系統。Apache ShardingSphere 目前已提供數十個 SPI 作為系統的擴展點，仍在不斷增加中。

如果數據量較大基本優化已不能解決，MySQL主從架構開始包括主主、一主多從等，可以逐步考慮優化如下，這裏就不展開偏度，後續有時間我們針對這幾個單獨

讀寫分離（適合讀請求較多，寫請求較少場景）Mycat 代理層方案Apache ShardingSphere分庫分表Mycat 代理層方案Apache ShardingSphereNewSql數據庫（新一代分布式關系型數據庫解決方案）TiDB

如何設計數據庫？

需求分析階段：梳理出數據流程圖DFD概念設計階段：抽象出具體DBMS的模型E-R圖、概念數據模型、邏輯數據模型、物理數據模型數據庫設計三範式、外鍵的考慮邏輯設計階段：外鍵、索引、視圖，數據庫選型：數據存儲、檢索、安全、讀寫分離、分庫分表、數據歸檔、接入數據倉庫物理設計階段：存儲過程、觸發器、用戶自定義函數、表空間

阿裏Mysql索引規約

強制業務上具有唯一特性的字段，即使是多個字段的組合，也必須建成唯一索引。說明:不要以為唯一索引影響了 insert 速度，這個速度損耗可以忽略，但提高查找速度是明 顯的;另外，即使在應用層做了非常完善的校驗控制，只要沒有唯一索引，根據墨菲定律，必 然有髒數據產生。強制超過三個表禁止 join。需要 join 的字段，數據類型必須絕對一致;多表關聯查詢時， 保證被關聯的字段需要有索引。說明:即使雙表 join 也要注意表索引、SQL 性能。強制頁面搜索嚴禁左模糊或者全模糊，如果需要請走搜索引擎來解決。說明:索引文件具有 B-Tree 的最左前綴匹配特性，如果左邊的值未確定，那麼無法使用此索 引。推薦如果有 order by 的場景，請注意利用索引的有序性。order by 最後的字段是組合 索引的一部分，並且放在索引組合順序的最後，避免出現 file_sort 的情況，影響查詢性能。
正例:where a=? and b=? order by c; 索引:a_b_c 反例:索引中有範圍查找，那麼索引有序性無法利用，如:WHERE a>10 ORDER BY b; 索引 a_b 無法排序。推薦利用覆蓋索引來進行查詢操作，避免回表。說明:如果一本書需要知道第 11 章是什麼標題，會翻開第 11 章對應的那一頁嗎?目錄瀏覽 一下就好，這個目錄就是起到覆蓋索引的作用。
正例:能夠建立索引的種類分為主鍵索引、唯一索引、普通索引三種，而覆蓋索引只是一種查 詢的一種效果，用explain的結果，extra列會出現:using index。推薦利用延遲關聯或者子查詢優化超多分頁場景。說明:MySQL 並不是跳過 offset 行，而是取 offset+N 行，然後返回放棄前 offset 行，返回 N 行，那當 offset 特別大的時候，效率就非常的低下，要麼控制返回的總頁數，要麼對超過 特定閾值的頁數進行 SQL 改寫。正例:先快速定位需要獲取的 id 段，然後再關聯:SELECT a.* FROM 表 1 a, (select id from 表 1 where 條件 LIMIT 100000,20 ) b where a.id=b.id推薦SQL 性能優化的目標:至少要達到 range 級別，要求是 ref 級別，如果可以是 consts 最好。說明:1)consts 單表中最多只有一個匹配行(主鍵或者唯一索引)，在優化階段即可讀取到數據。
2)ref 指的是使用普通的索引(normal index)。3)range 對索引進行範圍檢索。反例:explain 表的結果，type=index，索引物理文件全掃描，速度非常慢，這個 index 級 別比較 range 還低，與全表掃描是小巫見大巫。推薦建組合索引的時候，區分度最高的在最左邊。正例:如果 where a=? and b=? ，如果 a 列的幾乎接近於唯一值，那麼只需要單建 idx_a 索引即可。
說明:存在非等號和等號混合時，在建索引時，請把等號條件的列前置。如:where c>? and d=? 那麼即使 c 的區分度更高，也必須把 d 放在索引的最前列，即索引 idx_d_c。推薦防止因字段類型不同造成的隱式轉換，導致索引失效。參考創建索引時避免有如下極端誤解:1)寧濫勿缺。認為一個查詢就需要建一個索引。2)寧缺勿濫。認為索引會消耗空間、嚴重拖慢更新和新增速度。3)抵制惟一索引。認為業務的惟一性一律需要在應用層通過“先查後插”方式解決。

MySQL同時有多個事務可能會產生什麼問題？

多事務的並發進行一般會造成以下幾個問題

髒讀: A事務讀取到了B事務未提交的內容,而B事務後面進行了回滾.不可重複讀:當設置A事務只能讀取B事務已經提交的部分，會造成在A事務內的兩次查詢,結果竟然不-樣,因為在此期間B事務進行了提交操作.幻讀: A事務讀取了一一個範圍的內容,而同時B事務在此期間插入了-條數據造成”幻覺"

說說MySQL事務隔離級別?

MySQL和其他關系型數據庫一樣有四種隔離級別，InnoDB默認使用的是可重複讀隔離級別，四種隔離級別如下:

未提交讀(READ UNCOMMITTED)這就是.上面所說的例外情況了,這個隔離級別下，其他事務可以看到本事務沒有提交的部分修改因此會造成髒讀的問題(讀取到了其他事務未提交的部分,而之後該事務進行了回滾).這個級別的性能沒有足夠大的優勢,但是又有很多的問題,因此很少使用.已提交讀(READ COMMITTED)其他事務只能讀取到本事務已經提交的部分.這個隔離級別有不可重複讀的問題,在同-一個事務內的兩次讀取,拿到的結果竟然不一樣,因為另外- -個事務對數據進行了修改.REPEATABLE READ(可重複讀)可重複讀隔離級別解決了上面不可重複讀的問題(看名字也知道),但是仍然有-個新問題,就是幻讀,當你讀取id> 10的數據行時,對涉及到的所有行加上了讀鎖,此時例外一個事務新插入了-條id=11的數據,因為是新插入的,所以不會觸發上面的鎖的排斥,那麼進行本事務進行下一次的查詢時會發現有一條id=11的數據,而 上次的查詢操作並沒有獲取到,再進行插入就會有主鍵沖突的問題.SERIALIZABLE(可串行化)這是最高的隔離級別,可以解決上面提到的所有問題,因為他強制將所以的操作串行執行,這會導致並發性能極速下降,因此也不是很常用.

說說對於MySQL鎖的理解

從鎖的類別上來講,有共享鎖和排他鎖select * from t_logs where id = 1 for update 其中增刪改操作自動上行鎖，相當於上了寫鎖（排它鎖）select * from t_logs where id = 1 lock in share mode 相當於上了讀鎖（共享鎖）共享鎖:又叫做讀鎖.可以通過lock in share mode實現，這時候只能讀不能寫，當用戶要進行數據的讀取時，對數據加上共享鎖共享鎖可以同時加上多個。排他鎖:又叫做寫鎖.它會阻塞其他的寫鎖和讀鎖。從顆粒度來區分，可以分為表鎖和行鎖、頁鎖。當用戶要進行數據的寫入時,對數據加上排他鎖排他鎖只可以加一-個 ,他和其他的排他鎖,共享鎖都相斥。鎖的粒度取決於具體的存儲引擎,InnoDB實現了行級鎖,頁級鎖,表級鎖.他們的加鎖開銷從大大小,並發能力也是從大到小。表鎖會鎖定整張表並且阻塞其他用戶對該表的所有讀寫操作，比如alter修改表結構的時候會鎖表。行鎖又可以分為樂觀鎖和悲觀鎖，悲觀鎖可以通過for update實現，樂觀鎖則通過版本號實現。間隙鎖是可重複讀級別下才會有的鎖，MVCC(多版本並發控制，實際上就是保存了數據在某個時間節點的快照）和間隙鎖,、mvcc+next-key鎖、記錄鎖或者行鎖+間隙鎖）來解決幻讀的問題,。鎖的處理機制分為樂觀鎖和悲觀鎖因為悲觀鎖是在事務執行中加鎖，當並發量高時，就有可能會對其他事務進程造成影響，造成其他事務進程執行時間過程，導致事務超時。樂觀鎖是在對數據進行檢查時才加鎖，鎖的時間會少很多，而只有鎖住數據的時候會影響其它事務。悲觀鎖，從數據開始修改時就將數據鎖住，直到更改完成才釋放鎖。樂觀鎖，直到數據修改完准備提交時才上鎖，完成後釋放。加鎖時機並發性兩種鎖各有優缺點，不可認為一種好於另一種，像樂觀鎖適用於寫比較少的情況下，即沖突真的很少發生的時候，這樣可以省去了鎖的開銷，加大了系統的整個吞吐量。但如果經常產生沖突，上層應用會不斷的進行retry，這樣反倒是降低了性能，所以這種情況下用悲觀鎖就比較合適。活鎖：使用優先級隊列，先來一直獲取不到鎖。死鎖：一次性封鎖法、順序封鎖法，事先規定順序。mysql鎖分為共享鎖和排他鎖，也叫做讀鎖和寫鎖。讀鎖是共享的，可以通過lock in share mode實現，這時候只能讀不能寫。寫鎖是排他的，它會阻塞其他的寫鎖和讀鎖。從顆粒度來區分，可以分為表鎖和行鎖兩種。表鎖會鎖定整張表並且阻塞其他用戶對該表的所有讀寫操作，比如alter修改表結構的時候會鎖表。行鎖又可以分為樂觀鎖和悲觀鎖，悲觀鎖可以通過for update實現，樂觀鎖則通過版本號實現。間隙鎖是可重複讀級別下才會有的鎖，結合MVCC和間隙鎖可以解決幻讀的問題

mysql怎麼實現可重複讀？

mysql可重複讀主要通過undo log版本鏈+ReadView 機制實現，具體如下

當 MySQL 執行寫操作之前，會把即將被修改的數據記錄到 undo log 日志裏面。只有這樣，事務要回滾的時候，即使 Buffer Pool 中的數據被修改了，依然可以從 undo log 日志中，讀取到原插入、修改、刪除之前的值，最終把值重新變回去，這就是回滾操作。

日志與日志之間通過 roll_pointer 指針連接，就形成了 undo log 版本鏈。基於 undo log 版本鏈實現的 ReadView 機制：ReadView 說白了就是一種數據結構，它主要包含這樣幾部分：

m_ids，當前有哪些事務正在執行，且還沒有提交，這些事務的 id 就會存在這裏；min_trx_id，是指 m_ids 裏最小的值；max_trx_id，是指下一個要生成的事務 id。下一個要生成的事務 id 肯定比現在所有事務的 id 都大；creator_trx_id，每開啟一個事務都會生成一個 ReadView，而 creator_trx_id 就是這個開啟的事務的 id。

ACID是什麼？

事務是一系列的操作,他們要符合ACID特性.最常見的理解就是:事務中的操作要麼全部成功,要麼全部失敗。

A=Atomicity，原子性,就是上面說的,要麼全部成功,要麼全部失敗.不可能只執行一部分操作。C=Consistency，系統(數據庫)總是從一個-致性的狀態轉移到另一 個一 致性的狀態,不會存在中間狀態。I=Isolation，隔離性:通常來說:一個事務在完全提交之前,對其他事務是不可見的.注意前面的通常來說加了紅色，意味著有例外情況.D=Durability，持久性,一旦事務提交,那麼就永遠是這樣子了,哪怕系統崩潰也不會影響到這個事務的結果

簡單說說MySQL三大日志？

binlog 用來保證可以將數據庫狀態恢複到任一時刻。MySQL 整體來看，分為 Server 層和引擎層，而 binlog 是 Server 層面的 log 文件，也就是所有執行引擎都有 binlog。redo log ：用來保證 crash-safe，InnoDB可以保證即使數據庫發生異常重啟，之前提交的記錄都不會丟失。分為prepare 和 commit兩個步驟也即是“兩階段提交”。將操作先更新到內存，再寫入 redo log，此時標記為 prepare 狀態，再寫入 binlog，此時再提交事務，將 redo log 標記為 commit 狀態。undo log 是用來保證事務需要回滾時數據狀態的回滾和 MVCC 時，記錄各版本數據信息。undo log 和 redo log 也是引擎層的 log 文件，undo log 提供了回滾和多個行版本控制（MVCC），在數據庫修改操作時，不僅記錄了 redo log，還記錄了 undo log，如果因為某些原因導致事務執行失敗回滾了，可以借助 undo log 進行回滾。

MySQL如何保證ACID？

Mysql怎麼保證一致性的？從數據庫層面，數據庫通過原子性、隔離性、持久性來保證一致性。也就是說ACID四大特性之中，C(一致性)是目的，A(原子性)、I(隔離性)、D(持久性)是手段，是為了保證一致性，數據庫提供的手段。數據庫必須要實現AID三大特性，才有可能實現一致性。例如，原子性無法保證，顯然一致性也無法保證。但是，如果你在事務裏故意寫出違反約束的代碼，一致性還是無法保證的。例如，你在轉賬的例子中，你的代碼裏故意不給B賬戶加錢，那一致性還是無法保證。因此，還必須從應用層角度考慮。從應用層面，通過代碼判斷數據庫數據是否有效，然後決定回滾還是提交數據。Mysql怎麼保證原子性的？undo log名為回滾日志，是實現原子性的關鍵，當事務回滾時能夠撤銷所有已經成功執行的sql語句，他需要記錄你要回滾的相應日志信息。例如undo log記錄了這些回滾需要的信息，當事務執行失敗或調用了rollback，導致事務需要回滾，便可以利用undo log中的信息將數據回滾到修改之前的樣子。當你delete一條數據的時候，就需要記錄這條數據的信息，回滾的時候，insert這條舊數據。當你update一條數據的時候，就需要記錄之前的舊值，回滾的時候，根據舊值執行update操作。當年insert一條數據的時候，就需要這條記錄的主鍵，回滾的時候，根據主鍵執行delete操作。利用Innodb的undo log。Mysql怎麼保證持久性的？其實好處就是將redo log進行刷盤比對數據頁刷盤效率高，具體表現如下redo log體積小，畢竟只記錄了哪一頁修改了啥，因此體積小，刷盤快。redo log是一直往末尾進行追加，屬於順序IO。效率顯然比隨機IO來的快。正如之前說的，Mysql是先把磁盤上的數據加載到內存中，在內存中對數據進行修改，再刷回磁盤上。如果此時突然宕機，內存中的數據就會丟失。那怎麼解決上面這個問題，簡單啊，事務提交前直接把數據寫入磁盤就行啊。這麼做有什麼問題？只修改一個頁面裏的一個字節，就要將整個頁面刷入磁盤，太浪費資源了。畢竟一個頁面16kb大小，你只改其中一點點東西，就要將16kb的內容刷入磁盤，聽著也不合理。畢竟一個事務裏的SQL可能牽涉到多個數據頁的修改，而這些數據頁可能不是相鄰的，也就是屬於隨機IO。顯然操作隨機IO，速度會比較慢。利用Innodb的redo log。於是決定采用redo log解決上面的問題。當做數據修改的時候，不僅在內存中操作，還會在redo log中記錄這次操作。當事務提交的時候，會將redo log日志進行刷盤(redo log一部分在內存中，一部分在磁盤上)。當數據庫宕機重啟的時候，會將redo log中的內容恢複到數據庫中，再根據undo log和binlog內容決定回滾數據還是提交數據。采用redo log的好處？Mysql怎麼保證隔離性的？

設立利用索引，減少鎖的競爭。調整sql執行順序，讓長時間持有事務操作放在前面。避免大的事務，盡量拆分為多個子事務。以固定順序訪問表和行。利用的是鎖和MVCC機制。還是拿轉賬例子來說明，有一個賬戶表如下：

MySQL如何盡可能避免死鎖

以固定的順序訪問表和行。比如對第2節兩個job批量更新的情形，簡單方法是對id列表先排序，後執行，這樣就避免了交叉等待鎖的情形；又比如對於3.1節的情形，將兩個事務的sql順序調整為一致，也能避免死鎖。大事務拆小。大事務更傾向於死鎖，如果業務允許，將大事務拆小。在同一個事務中，盡可能做到一次鎖定所需要的所有資源，減少死鎖概率。降低隔離級別。如果業務允許，將隔離級別調低也是較好的選擇，比如將隔離級別從RR調整為RC，可以避免掉很多因為gap鎖造成的死鎖。為表添加合理的索引。可以看到如果不走索引將會為表的每一行記錄添加上鎖，死鎖的概率大大增大。Innodb提供了wait-for graph算法來主動進行死鎖檢測，Innodb目前處理死鎖的方法就是將持有最少行級排他鎖的事務進行回滾。這也是相對比較簡單的死鎖回滾方式。

MySQL主從複制有幾種複制方式？

異步複制MySQL主從異步複制是最常見的複制場景。數據的完整性依賴於主庫BINLOG的不丟失，只要主庫的BINLOG不丟失，那麼就算主庫宕機了，我們還可以通過BINLOG把丟失的部分數據通過手工同步到從庫上去多線程複制在MySQL5.7中，帶來了全新的多線程複制技術，解決了當master同一個schema下的數據發生了變更，從庫不能並發應用的問題，同時也真正將binlog組提交的優勢充分發揮出來，保障了從庫並發應用Relay Log的能力。在MySQL8.0中，多線程複制又進行了技術更新，引入了writeset的概念，而在之前的版本中，如果主庫的同一個會話順序執行多個不同相關對象的事務，例如，先執行了Update A表的數據，又執行了Update B表的數據，那麼BINLOG在複制到從庫後，這兩個事務是不能並行執行的，writeset的到來，突破了這個限制。增強半同步複制

從MySQL5.5開始，引入了半同步複制，此時的技術暫且稱之為傳統的半同步複制，因該技術發展到MySQL5.7後，已經演變為增強半同步複制(也成為無損複制)。在異步複制時，主庫執行Commit提交操作並寫入BINLOG日志後即可成功返回客戶端，無需等待BINLOG日志傳送給從庫。阿裏開源Mysql數據同步工具有Otter、Canal，默認從服務器訂閱bin log同步協議，實現數據增量同步。基於binlog主從複制原理和流程如下

來源：https://www.cnblogs.com/itxiaoshen/p/15463890.html