淺談MySQL資料庫面試必要掌握知識點

概述

**本人部落格網站 **IT小神 www.itxiaoshen.com

定義

MySQL官方地址 //www.mysql.com/ MySQL 8系列最新版本為8.0.27，5系列的最新版本5.7.35

MySQL發展至今仍然是世界上最流行的關係型資料庫管理系統,使用C和C++語言編寫，並且為很多種程式語言提供API.能夠處理千萬級別的數據，市場佔有量還是老大哥地位。

安裝

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

image-20211025143345414

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

image-20211025143711516

連接客戶端

我們日常連接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。
    • 優點
      • MySQL Cluster 自動將表分片（或分區）到不同節點上，使資料庫可以在低成本的商用硬體上橫向擴展，同時保持對應用程式完全應用透明。
      • 憑藉其分散式、無共享架構，MySQL Cluster 可提供 99.999% 的可用性，確保了較強的故障恢復能力和在不停機的情況下執行預定維護的能力。
      • MySQL Cluster 讓用戶可以在解決方案中整合關係資料庫技術和 NoSQL 技術中的最佳部分，從而降低成本、風險和複雜性。
      • MySQL Cluster 提供實時的響應時間和吞吐量，能滿足最苛刻的 Web、電信及企業應用程式的需求。 具有跨地域複製功能的多站點集群。
      • 跨地域複製使多個集群可以分布在不同的地點，從而提高了災難恢復能力和全球 Web 服務的擴展能力。
      • 為支援持續運營，MySQL Cluster 允許向正在運行的資料庫模式中聯機添加節點和更新內容，因而能支援快速變化和高度動態的負載。
    • 缺點
      • 隨資料庫容量增加，每個數據數據節點需要添加更多的記憶體，增加使用成本。
      • 犧牲部分sql語言特性。
  • MySQL Cluster CGE：Cluster高級集群版，需付費

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

image-20211025135600397

image-20211025135542655

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的到來意味著我們可以「少此一舉」。

image-20211025103239525

image-20211025140417835

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

image-20211025140357795

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

MySQL面試必備

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

說說MySQL存儲引擎？

image-20211025150915144

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

MySQL InnoDB和MyISAM兩大引擎對比？

  • InnoDB存儲引擎：從MySQL5.5版本之後，MySQL的默認內置存儲引擎已經是InnoDB了，他的主要特點有：

    • InnoDB是MySQL5.5之後默認的資料庫存儲引擎，支援事務，支援外鍵
      • 使用共享表空間存儲：所有的表和索引存放在同一個表空間中。
      • 使用多表空間存儲：表結構放在frm文件，數據和索引放在IBD文件中。分區表的話，每個分區對應單獨的IBD文件，分區表的定義可以查看我的其他文章。使用分區表的好處在於提升查詢效率。
    • 災難恢復性比較好；
    • 支援事務。默認的事務隔離級別為可重複度，通過MVCC（並發版本控制）來實現的。
    • 使用的鎖粒度為行級鎖，可以支援更高的並發；
    • 支援外鍵；
    • 配合一些熱備工具可以支援在線熱備份，有很成熟的在線熱備解決方案；
    • 在InnoDB中存在著緩衝管理，通過緩衝池，將索引和數據全部快取起來，加快查詢的速度；
    • 對於InnoDB類型的表，其數據的物理組織形式是聚簇表。所有的數據按照主鍵來組織。數據和索引放在一塊，都位於B+數的葉子節點上；

  • MyISAM存儲引擎：在5.5版本之前，MyISAM是MySQL的默認存儲引擎，該存儲引擎並發性差，不支援事務，所以使用場景比較少，主要特點為：

    • MyISAM是MySQL5.5之前的默認存儲引擎，MyISAM既不支援事務，也不支援外鍵
      • 每個MyISAM在磁碟上存儲成3個文件索引的結構是B+樹結構其文件名都和表名相同，但拓展名分別是 ：
        • frm文件：存儲表的定義數據,可以存放在不同的目錄,平均分布IO，獲得更快的速度
        • MYD文件：存放表具體記錄的數據,可以存放在不同的目錄,平均分布IO，獲得更快的速度
        • MYI文件：存儲索引,僅保存記錄所在頁的指針
      • 通過MYI的B+樹結構來查找記錄頁，再根據記錄頁查找記錄。並且支援全文索引、B樹索引和數據壓縮,支援數據的類型有以下三種
        • 靜態固定長度表
        • 動態可變長表
        • 壓縮表
    • 不支援事務；
    • 不支援外鍵，如果強行增加外鍵，不會提示錯誤，只是外鍵不其作用；
    • 對數據的查詢快取只會快取索引，不會像InnoDB一樣快取數據，而且是利用作業系統本身的快取；
    • 默認的鎖粒度為表級鎖，所以並發度很差，加鎖快，鎖衝突較少，所以不太容易發生死鎖；
    • 支援全文索引（MySQL5.6之後，InnoDB存儲引擎也對全文索引做了支援），但是MySQL的全文索引基本不會使用，對於全文索引，現在有其他成熟的解決方案，比如：ElasticSearch，Solr，Sphinx等。
    • 資料庫所在主機如果宕機，MyISAM的數據文件容易損壞，而且難恢復；

  • 查詢性能上，MyISAM的查詢效率高於InnoDB，因為InnoDB在查詢過程中，是需要維護數據快取，而且查詢過程是先定位到行所在的數據塊，然後在從數據塊中定位到要查找的行；而MyISAM可以直接定位到數據所在的記憶體地址，可以直接找到數據；

  • SELECT COUNT(*)語句，如果行數在千萬級別以上，MyISAM可以快速查出，而InnoDB查詢的特別慢，因為MyISAM將行數單獨存儲了，而InnoDB需要朱行去統計行數；所以如果使用InnoDB，而且需要查詢行數，則需要對行數進行特殊處理，如：離線查詢並快取；

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

這裡小編先推薦一個數據結構可視化的網站，可以幫助我們學習各種數據結構的底層原理，常見的查詢演算法,順序查找,二分查找,二叉排序樹查找,哈希散列法,分塊查找,平衡多路搜索樹 B 樹（B-tree）

數據結構可視化學習網站 //www.cs.usfca.edu/~galles/visualization/Algorithms.html

image-20211025153718960

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個關鍵字

image-20211025153854021

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

image-20211025155643394

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

image-20211025160355489

基於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+樹的查詢效率比較穩定，對於

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

image-20211025154214233

  • 使用場景是否需要事務支援；是否需要支援高並發，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的聯合索引，聯合索引的底層存儲跟單列索引時類似的，聯合索引是每個樹節點中包含多個索引值，在通過索引查找記錄時，會先將聯合索引中第一個索引列與節點中第一個索引值進行匹配，匹配成功接著匹配第二個索引列和索引值，直到聯合索引的所有索引列都匹配完；如果過程中出現某一個索引列與節點相應位置的索引值不匹配的情況，則無需再匹配節點中剩餘索引列。

image-20211025170426533

比如：索引包含表中每一行的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資源
  • 欄位類優化
    • 用好數值類型：
      • 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欄位：
      • NULL欄位很難查詢優化
      • NULL欄位的索引需要額外空間
      • 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 ShardingSphere
  • NewSql資料庫（新一代分散式關係型資料庫解決方案）
    • 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鎖的理解

image-20211025182128987

  • 從鎖的類別上來講,有共享鎖和排他鎖

    • 共享鎖:又叫做讀鎖.可以通過lock in share mode實現，這時候只能讀不能寫，當用戶要進行數據的讀取時，對數據加上共享鎖共享鎖可以同時加上多個。
      • select * from t_logs where id = 1 lock in share mode 相當於上了讀鎖（共享鎖）
    • 排他鎖:又叫做寫鎖.它會阻塞其他的寫鎖和讀鎖。從顆粒度來區分，可以分為表鎖和行鎖、頁鎖。當用戶要進行數據的寫入時,對數據加上排他鎖排他鎖只可以加一-個 ,他和其他的排他鎖,共享鎖都相斥。
      • select * from t_logs where id = 1 for update 其中增刪改操作自動上行鎖，相當於上了寫鎖（排它鎖）
    • 鎖的粒度取決於具體的存儲引擎,InnoDB實現了行級鎖,頁級鎖,表級鎖.他們的加鎖開銷從大大小,並發能力也是從大到小。
    • 表鎖會鎖定整張表並且阻塞其他用戶對該表的所有讀寫操作，比如alter修改表結構的時候會鎖表。
    • 行鎖又可以分為樂觀鎖和悲觀鎖，悲觀鎖可以通過for update實現，樂觀鎖則通過版本號實現。
    • 間隙鎖是可重複讀級別下才會有的鎖，MVCC(多版本並發控制，實際上就是保存了數據在某個時間節點的快照）和間隙鎖,、mvcc+next-key鎖、記錄鎖或者行鎖+間隙鎖）來解決幻讀的問題,。

  • 鎖的處理機制分為樂觀鎖和悲觀鎖

    • 加鎖時機
      • 悲觀鎖，從數據開始修改時就將數據鎖住，直到更改完成才釋放鎖。
      • 樂觀鎖，直到數據修改完準備提交時才上鎖，完成後釋放。
    • 並發性
      • 因為悲觀鎖是在事務執行中加鎖，當並發量高時，就有可能會對其他事務進程造成影響，造成其他事務進程執行時間過程，導致事務超時。
      • 樂觀鎖是在對數據進行檢查時才加鎖，鎖的時間會少很多，而只有鎖住數據的時候會影響其它事務。
    • 兩種鎖各有優缺點，不可認為一種好於另一種，像樂觀鎖適用於寫比較少的情況下，即衝突真的很少發生的時候，這樣可以省去了鎖的開銷，加大了系統的整個吞吐量。但如果經常產生衝突，上層應用會不斷的進行retry，這樣反倒是降低了性能，所以這種情況下用悲觀鎖就比較合適。

  • 活鎖：使用優先順序隊列，先來一直獲取不到鎖。

  • 死鎖：一次性封鎖法、順序封鎖法，事先規定順序。

  • mysql鎖分為共享鎖和排他鎖，也叫做讀鎖和寫鎖。

    讀鎖是共享的，可以通過lock in share mode實現，這時候只能讀不能寫。

    寫鎖是排他的，它會阻塞其他的寫鎖和讀鎖。從顆粒度來區分，可以分為表鎖和行鎖兩種。

    表鎖會鎖定整張表並且阻塞其他用戶對該表的所有讀寫操作，比如alter修改表結構的時候會鎖表。

    行鎖又可以分為樂觀鎖和悲觀鎖，悲觀鎖可以通過for update實現，樂觀鎖則通過版本號實現。

    間隙鎖是可重複讀級別下才會有的鎖，結合MVCC和間隙鎖可以解決幻讀的問題

mysql怎麼實現可重複讀？

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

image-20211025182600753

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

image-20211025182633274

日誌與日誌之間通過 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怎麼保證原子性的？

    • 利用Innodb的undo log。
      • undo log名為回滾日誌，是實現原子性的關鍵，當事務回滾時能夠撤銷所有已經成功執行的sql語句，他需要記錄你要回滾的相應日誌資訊。例如
        • 當你delete一條數據的時候，就需要記錄這條數據的資訊，回滾的時候，insert這條舊數據。
        • 當你update一條數據的時候，就需要記錄之前的舊值，回滾的時候，根據舊值執行update操作。
        • 當年insert一條數據的時候，就需要這條記錄的主鍵，回滾的時候，根據主鍵執行delete操作。
      • undo log記錄了這些回滾需要的資訊，當事務執行失敗或調用了rollback，導致事務需要回滾，便可以利用undo log中的資訊將數據回滾到修改之前的樣子。

  • Mysql怎麼保證持久性的？

    • 利用Innodb的redo log。
      • 正如之前說的，Mysql是先把磁碟上的數據載入到記憶體中，在記憶體中對數據進行修改，再刷回磁碟上。如果此時突然宕機，記憶體中的數據就會丟失。
      • 那怎麼解決上面這個問題，簡單啊，事務提交前直接把數據寫入磁碟就行啊。這麼做有什麼問題？
      • 只修改一個頁面里的一個位元組，就要將整個頁面刷入磁碟，太浪費資源了。畢竟一個頁面16kb大小，你只改其中一點點東西，就要將16kb的內容刷入磁碟，聽著也不合理。
      • 畢竟一個事務里的SQL可能牽涉到多個數據頁的修改，而這些數據頁可能不是相鄰的，也就是屬於隨機IO。顯然操作隨機IO，速度會比較慢。
    • 於是決定採用redo log解決上面的問題。當做數據修改的時候，不僅在記憶體中操作，還會在redo log中記錄這次操作。當事務提交的時候，會將redo log日誌進行刷盤(redo log一部分在記憶體中，一部分在磁碟上)。當資料庫宕機重啟的時候，會將redo log中的內容恢復到資料庫中，再根據undo log和binlog內容決定回滾數據還是提交數據。
    • 採用redo log的好處？
      • 其實好處就是將redo log進行刷盤比對數據頁刷盤效率高，具體表現如下
      • redo log體積小，畢竟只記錄了哪一頁修改了啥，因此體積小，刷盤快。
      • redo log是一直往末尾進行追加，屬於順序IO。效率顯然比隨機IO來的快。

  • Mysql怎麼保證隔離性的？

    image-20211025232810242

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

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日誌傳送給從庫。

    image-20211025234809060

  • 阿里開源Mysql數據同步工具有Otter、Canal，默認從伺服器訂閱bin log同步協議，實現數據增量同步。

  • 基於binlog主從複製原理和流程如下

image-20211025233936755

image-20211025234006550

Tags:

VirMach 便宜 VPS

QNews

QNews