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

概述

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

定義

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

MySQL發展至今仍然是世界上最流行的關系型資料庫管理系統,使用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。
- 優點
  - 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）。

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了，他的主要特點有：
- 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）

資料結構可視化學習網站 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資源
字段類優化
- 用好數值類型：
  - 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
分庫分表
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鎖的了解

從鎖的類别上來講,有共享鎖和排他鎖
- 共享鎖:又叫做讀鎖.可以通過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實作，這時候隻能讀不能寫。

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

間隙鎖是可重複讀級别下才會有的鎖，結合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怎麼保證原子性的？
- 利用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怎麼保證隔離性的？

淺談MySQL資料庫面試必要掌握知識點
- 利用的是鎖和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日志傳送給從庫。
淺談MySQL資料庫面試必要掌握知識點
阿裡開源Mysql資料同步工具有Otter、Canal，預設從伺服器訂閱bin log同步協定，實作資料增量同步。
基于binlog主從複制原理和流程如下

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

概述

定義

安裝