MySQL的基礎問題有哪些

2024-07-24 12:33:11

字體：大中小

來源：轉載

供稿：網友

　　本篇內容主要講解“MySQL的基礎問題有哪些”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“MySQL的基礎問題有哪些”吧!

　　常規篇

　　1、說一下數據庫的三大范式？

　　第一范式：字段原子性，第二范式：行唯一，有主鍵列，第三范式：每列和主鍵列都相關。

　　實際應用中會通過冗余少量字段來少關聯表，提升查詢效率。

　　2、只查詢一條數據，但是也執行非常慢，原因一般有哪些？

　　MySQL數據庫本身被堵住了，比如：系統或網絡資源不夠

　　SQL語句被堵住了，比如：表鎖，行鎖等，導致存儲引擎不執行對應的SQL語句

　　確實是索引使用不當，沒有走索引

　　表中數據的特點導致的，走了索引，但回表次數龐大

　　3、count(*)、count(0)、count(id)實現方式的區別？

　　對于count(*)、count(常數)、count(主鍵)形式的count函數來說，優化器可以選擇掃描成本最小的索引執行查詢，從而提升效率，它們的執行過程是一樣的。

　　而對于count(非索引列)來說，優化器選擇全表掃描，說明只能在聚集索引的葉子結點順序掃描。

　　count(二級索引列)只能選擇包含我們指定的列的索引去執行查詢，可能導致優化器選擇的索引執行的代價并不是最小。

　　MySQL的基礎問題有哪些

　　4、誤刪數據怎么辦？

　　1）如果數據量比較大,用物理備份xtrabackup。定期對數據庫進行全量備份,也可以做增量備份。

　　2）如果數據量較少,用mysqldump或者mysqldumper，再利用binlog來恢復或者搭建主從的方式來恢復數據，可以從以下幾個點來恢復：

　　DML誤操作語句：可以通過flashback，先解析binlog event,然后在進行反轉。

　　DDL語句誤操作：只能通過全量備份+應用binlog的方式來恢復數據。一旦數據量比較大,那么恢復時間就特別長。

　　rm 刪除：使用備份跨機房，或者最好是跨城市保存。

　　5、drop、truncate 和 delete 的區別

　　DELETE語句執行刪除的過程是每次從表中刪除一行，并且同時將該行的刪除操作作為事務記錄在日志中保存以便進行進行回滾操作。

　　TRUNCATE TABLE 則一次性地從表中刪除所有的數據并不把單獨的刪除操作記錄記入日志保存，刪除行是不能恢復的。并且在刪除的過程中不會激活與表有關的刪除觸發器，執行速度快。

　　drop語句將表所占用的空間全釋放掉。

　　6、MySQL大表查詢為什么不會爆內存？

　　MySQL 是“邊讀邊發的”，這就意味著，如果客戶端接收得慢，會導致 MySQL 服務端由于結果發不出去，這個事務的執行時間變長。

　　服務端并不需要保存一個完整的結果集。取數據和發數據的流程都是通過一個next_buffer來操作的。

　　內存的數據頁是在 Buffer Pool (BP) 中管理的。

　　InnoDB 管理 Buffer Pool 使用改進的 LRU 算法，是用鏈表來實現的。在 InnoDB 實現上，按照 5:3 的比例把整個 LRU 鏈表分成了 young 區域和 old 區域，確保大批量加載冷數據時不會沖掉熱點數據。

　　7、深度分頁（超大分頁）怎么處理?

　　用id優化：先找到上次分頁的最大ID,然后利用id上的索引來查詢,類似于select * from user where id>1000000 limit 100。

　　用覆蓋索引優化：Mysql的查詢完全命中索引的時候,稱為覆蓋索引,是非?？斓?因為查詢只需要在索引上進行查找,之后可以直接返回,而不用再回表拿數據.因此我們可以先查出索引的ID,然后根據Id拿數據。

　　在業務允許的情況下限制頁數

　　8、日常開發中你是怎么優化SQL的？

　　添加合適索引：對作為查詢條件和order by的字段建立索引，對于多個查詢字段的考慮建立組合索引，同時注意組合索引字段的順序,將最常用作限制條件的列放在最左邊，依次遞減，索引不宜太多，一般5個以內。

　　優化表結構：數字型字段優于字符串類型，數據類型更小通常更好，盡量使用 NOT NULL

　　優化查詢語句：分析SQl執行計劃，是否命中索引等，如果SQL很復雜，優化SQL結構，如果表數據量太大，考慮分表

　　9、MySQL 的并發連接與并發查詢什么區別？

　　在執行show processlist的結果里，看到了幾千個連接，指的是并發連接。

　　而"當前正在執行"的語句，才是并發查詢。

　　并發連接數多影響的是內存。

　　并發查詢太高對CPU不利。一個機器的CPU核數有限，線程全沖進來，上下文切換的成本就會太高。

　　需要注意的是，在線程進入鎖等待以后，并發線程計數減一，所以等行鎖或者間隙鎖時的線程是不算在計數范圍內的。也就是說進入鎖等待的線程不吃CPU，從而避免整個系統鎖死。

　　10、MySQL更新字段值為原來的值內部是怎么操作呢？

　　相同的數據時，不會做update更新。

　　不過對不同的binlog格式，處理的日志方式有所不同：

　　1）基于row模式時，server層匹配到要更新的記錄，發現新值和舊值一致，不做更新，就直接返回，也不記錄binlog。

　　2）基于 statement 或者 mixed格式時，MySQL執行 update 語句，并把更新語句記錄到binlog。

　　11、datetime和timestamp有什么區別？

　　datetime 的日期范圍是 1001——9999 年；timestamp 的時間范圍是 1970——2038 年

　　datetime 存儲時間與時區無關；timestamp 存儲時間與時區有關，顯示的值也依賴于時區

　　datetime 的存儲空間為 8 字節；timestamp 的存儲空間為 4 字節

　　datetime 的默認值為 null；timestamp 的字段默認不為空(not null)，默認值為當前時間(current_timestamp)

　　12、事務的隔離級別有哪些？

　　「讀未提交」（Read Uncommitted）最低級別，任何情況都無法保證

　　「讀已提交」（Read Committed）可避免臟讀的發生

　　「可重復讀」（Repeatable Read）可避免臟讀、不可重復讀的發生

　　「串行化」（Serializable）可避免臟讀、不可重復讀、幻讀的發生

　　Mysql默認的事務隔離級別是「可重復讀」(Repeatable Read)

　　13、在 MySQL 中有兩個 kill 命令

　　kill query + 線程 id，表示終止這個線程中正在執行的語句

　　kill connection + 線程 id，這里 connection 可缺省，表示斷開這個線程的連接

　　索引篇

　　1、索引分類有哪些？

　　根據葉子節點的內容，索引類型分為主鍵索引和非主鍵索引。

　　主鍵索引的葉子節點存的是整行數據。在 InnoDB 里，主鍵索引也被稱為聚簇索引（clustered index）。

　　非主鍵索引的葉子節點內容是主鍵的值。在 InnoDB 里，非主鍵索引也被稱為二級索引（secondary index）。

　　2、聚集索引和非聚集索引有什么區別？

　　聚集索引：聚集索引就是以主鍵創建的索引，聚集索引在葉子節點存儲的是表中的數據。MySQL的基礎問題有哪些

　　非聚集索引：非主鍵創建的索引，在葉子節點存儲的是主鍵和索引列，使用非聚集索引查詢出數據時，拿到葉子上的主鍵再去查到想要查找的數據。(拿到主鍵再查找這個過程叫做回表)。MySQL的基礎問題有哪些

　　覆蓋索引：假設所查詢的列，剛好都是索引對應的列，不用再回表查，那么這個索引列就叫覆蓋索引。

　　3、InnoDB 為什么設計B+樹，而不是B-Tree，Hash，二叉樹，紅黑樹？

　　哈希索引能夠以 O(1) 的速度處理單個數據行的增刪改查，但是面對范圍查詢或者排序時就會導致全表掃描的結果。

　　B樹可以在非葉結點中存儲數據，由于所有的節點都可能包含目標數據，我們總是要從根節點向下遍歷子樹查找滿足條件的數據行，這個特點帶來了大量的隨機 I/O，造成性能下降。

　　B+樹所有的數據行都存儲在葉節點中，而這些葉節點可以通過『指針』依次按順序連接，當我們在如下所示的 B+ 樹遍歷數據時可以直接在多個子節點之間進行跳轉，這樣能夠節省大量的磁盤 I/O 時間。

　　二叉樹：樹的高度不均勻，不能自平衡，查找效率跟數據有關（樹的高度），并且IO代價高。

　　紅黑樹：樹的高度隨著數據量增加而增加，IO代價高。

　　4、講一講聚簇索引與非聚簇索引？

　　在InnoDB里，索引B+ Tree的葉子節點存儲了整行數據的是主鍵索引，也被稱之為聚簇索引，即將數據存儲與索引放到了一塊，找到索引也就找到了數據。

　　而索引B+Tree的葉子節點存儲了主鍵的值的是非主鍵索引，也被稱之為非聚簇索引、二級索引。

　　第一次索引一般是順序IO，回表的操作屬于隨機IO。需要回表的次數越多，即隨機IO次數越多，我們就越傾向于使用全表掃描。

　　5、非聚簇索引一定會回表查詢嗎？

　　不一定，這涉及到查詢語句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再進行回表查詢。一個索引包含（覆蓋）所有需要查詢字段的值，被稱之為“覆蓋索引”。

　　6、講一講MySQL的最左前綴原則？

　　最左前綴原則就是最左優先，在創建多列索引時，要根據業務需求，where子句中使用最頻繁的一列放在最左邊。

　　MySQL會一直向右匹配直到遇到范圍查詢（>、<、between、like）就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立（a，b，c，d）順序的索引，d是用不到索引的，如果建立（a，b，d，c）的索引則都可以用到，a，b，d的順序可以任意調整。

　　=和in可以亂序，比如a = 1 and b = 2 and c = 3 建立（a，b，c）索引可以任意順序，MySQL的查詢優化器會幫你優化成索引可以識別的形式。

　　7、什么是索引下推？

　　滿足最左前綴原則的時候，最左前綴可以用于在索引中定位記錄。

　　在 MySQL 5.6 之前，只能從ID開始一個個回表。到主鍵索引上找出數據行，再對比字段值。

　　而 MySQL 5.6 引入的索引下推優化（index condition pushdown)，可以在索引遍歷過程中，對索引中包含的字段先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數。

　　8、Innodb為什么要用自增id作為主鍵？

　　如果表使用自增主鍵，那么每次插入新的記錄，記錄就會順序添加到當前索引節點的后續位置，當一頁寫滿，就會自動開辟一個新的頁。如果使用非自增主鍵（如果身份證號或學號等），由于每次插入主鍵的值近似于隨機，因此每次新紀錄都要被插到現有索引頁得中間某個位置，頻繁的移動、分頁操作造成了大量的碎片，得到了不夠緊湊的索引結構，后續不得不通過OPTIMIZE TABLE（optimize table）來重建表并優化填充頁面。

　　9、事務ACID特性的實現原理？

　　「原子性」：是使用 undo log 來實現的，如果事務執行過程中出錯或者用戶執行了rollback，系統通過undo log日志返回事務開始的狀態。

　　「持久性」：使用 redo log 來實現，只要redo log日志持久化了，當系統崩潰，即可通過redo log把數據恢復。

　　「隔離性」：通過鎖以及 MVCC,使事務相互隔離開。

　　「一致性」：通過回滾、恢復，以及并發情況下的隔離性，從而實現一致性。

　　10、MyISAM和InnoDB實現B樹索引方式的區別是什么？

　　InnoDB 存儲引擎：B+ 樹索引的葉子節點保存數據本身；

　　MyISAM 存儲引擎：B+ 樹索引的葉子節點保存數據的物理地址；

　　InnoDB，其數據文件本身就是索引文件，相比MyISAM，索引文件和數據文件是分離的，其表數據文件本身就是按B+Tree組織的一個索引結構，樹的節點data域保存了完整的數據記錄，這個索引的key是數據表的主鍵，因此InnoDB表數據文件本身就是主索引，這被稱為“聚簇索引”或者聚集索引，而其余的索引都作為輔助索引，輔助索引的data域存儲相應記錄主鍵的值而不是地址，這也是和MyISAM不同的地方。

　　11、索引有哪些分類？

　　根據葉子節點的內容，索引類型分為主鍵索引和非主鍵索引。

　　主鍵索引的葉子節點存的是整行數據。在 InnoDB 里，主鍵索引也被稱為聚簇索引（clustered index）。

　　非主鍵索引的葉子節點內容是主鍵的值。在 InnoDB 里，非主鍵索引也被稱為二級索引（secondary index）。

　　12、有哪些場景會導致索引失效？

　　背景：B+ 樹提供的這個快速定位能力，來源于同一層兄弟節點的有序性，所以說破壞了這個有序性，大概率就失效了，具體有如下幾種情況：

　　對索引使用左或者左右模糊匹配：也就是 like %xx 或者 like %xx% 這兩種方式都會造成索引失效。原因在于查詢的結果可能是「陳林、張林、周林」等之類的，所以不知道從哪個索引值開始比較，于是就只能通過全表掃描的方式來查詢。

　　對索引使用函數/對索引進行表達式計算：因為索引保存的是索引字段的原始值，而不是經過函數計算后的值，自然就沒辦法走索引了。

　　對索引隱式類型轉換：相當于用了新函數

　　WHERE 子句中的 OR：的含義就是兩個只要滿足一個即可，因此只有一個條件列是索引列是沒有意義的，只要有條件列不是索引列，就會進行全表掃描。

　　方案篇

　　1、有一個未分庫分表的系統，如何設計才可以讓系統動態切換到分庫分表上？

　　停機擴容（不推薦）

　　雙寫遷移方案：設計好擴容后的表結構方案，然后對單庫和分庫實現雙寫，觀察一周沒問題后，關閉單庫的讀流量，再觀察一段時間，持續穩定后，關閉單庫的寫流量，平滑切換到分庫分表中。

　　2、如何設計可以動態擴容縮容的分庫分表方案？

　　原理篇

　　1、一條 MySQL 語句執行步驟是什么樣的？

　　MySQL的基礎問題有哪些

　　Server層按順序執行sql的步驟為：

　　客戶端請求 -> 連接器（驗證用戶身份，給予權限） -> 查詢緩存（存在緩存則直接返回，不存在則執行后續操作）-> 分析器（對SQL進行詞法分析和語法分析操作） -> 優化器（主要對執行的sql優化選擇最優的執行方案方法） -> 執行器（執行時會先看用戶是否有執行權限，有才去使用這個引擎提供的接口）-> 去引擎層獲取數據返回（如果開啟查詢緩存則會緩存查詢結果）。

　　2、order by 排序內部原理是什么樣的？

　　MySQL會為每個線程分配一個內存（sort_buffer）用于排序該內存大小為sort_buffer_size。

　　如果排序的數據量小于sort_buffer_size，排序將會在內存中完成。

　　如果排序數據量很大，內存中無法存下這么多數據，則會使用磁盤臨時文件來輔助排序，也稱外部排序。

　　在使用外部排序時，MySQL會分成好幾份單獨的臨時文件用來存放排序后的數據，然后在將這些文件合并成一個大文件。

　　3、MVCC 實現原理？

　　MVCC(Multiversion concurrency control) 就是同一份數據保留多版本的一種方式，進而實現并發控制。在查詢的時候，通過read view和版本鏈找到對應版本的數據。

　　作用：提升并發性能。對于高并發場景，MVCC 比行級鎖開銷更小。

　　MVCC 的實現依賴于版本鏈，版本鏈是通過表的三個隱藏字段實現。

　　1）DB_TRX_ID：當前事務 id，通過事務 id 的大小判斷事務的時間順序。

　　2）DB_ROLL_PRT：回滾指針，指向當前行記錄的上一個版本，通過這個指針將數據的多個版本連接在一起構成undo log版本鏈。

　　3）DB_ROLL_ID：主鍵，如果數據表沒有主鍵，InnoDB 會自動生成主鍵。

　　4、change buffer是什么，有何作用？

　　5、MySQL是如何保證數據不丟失?

　　只要 redolog 和 binlog 保證持久化磁盤就能確保 MySQL 異常重啟后數據恢復 binlog 寫入機制。

　　redolog確保系統異常后，丟失的數據可以重做，binlog將數據進行歸檔，確保丟失的數據可以恢復。

　　事務執行前先寫redolog，事務執行過程中，先把日志寫到 binlog cache 里，事務提交的時候，再把 binlog cache 寫到 binlog 文件中。

　　6、為什么刪除了表，表文件的大小還是沒變？

　　數據項刪除之后InnoDB標記 page A 會被標記為可復用

　　delete 命令把整個表的數據刪除呢？結果就是，所有的數據頁都會被標記為可復用。但是磁盤上，文件不會變小。

　　經過大量增刪改的表，都是可能是存在空洞的。這些空洞也占空間所以，如果能夠把這些空洞去掉，就能達到收縮表空間的目的。

　　重建表，就可以達到這樣的目的?？梢允褂?alter table A engine=InnoDB 命令來重建表。

　　7、binlog三種格式對比

　　row格式的binlog記錄的操作行的主鍵id以及每個字段的真實值，所以不會出現主備操作數據不一致的情況。

　　statement：記錄的源SQL語句

　　mixed：前兩種混合，為什么還需要有mixed格式的文件，因為有些 statement 格式的 binlog 可能會導致主備不一致，所以要使用 row 格式。但 row 格式的缺點是，很占空間。MySQL 就取了個折中方案，MySQL 自己會判斷這條 SQL 語句是否可能引起主備不一致，如果有可能，就用 row 格式，否則就用 statement 格式。

　　8、MySQL加鎖規則

　　原則 1：加鎖的基本單位是 next-key lock，next-key lock 是前開后閉區間。

　　原則 2：查找過程中訪問到的對象才會加鎖

　　優化 1：索引上的等值查詢，給唯一索引加鎖的時候，next-key lock 退化為行鎖。

　　優化 2：索引上的等值查詢，向右遍歷時且最后一個值不滿足等值條件的時候，next-key lock 退化為間隙鎖

　　一個 bug：唯一索引上的范圍查詢會訪問到不滿足條件的第一個值為止。

　　9、什么是臟讀、不可重復讀、幻讀呢？

　　「臟讀」: 臟讀指的是讀到了其他事務未提交的數據，未提交意味著這些數據可能會回滾，也就是可能最終不會存到數據庫中，也就是不存在的數據。讀到了并不一定最終存在的數據，這就是臟讀。

　　「不可重復讀」: 不可重復讀指的是在一個事務內，最開始讀到的數據和事務結束前的任意時刻讀到的同一批數據出現不一致的情況。

　　「幻讀」: 幻讀，并不是說兩次讀取獲取的結果集不同，幻讀側重的方面是某一次的 select 操作得到的結果的數據狀態無法支撐后續的業務操作。更為具體一些：select 某記錄是否存在，不存在，準備插入此記錄，但執行 insert 時發現此記錄已存在，無法插入，此時就發生了幻讀。

　　10、MySQL都有哪些鎖呢?像上面那樣子進行鎖定豈不是有點阻礙并發效率了?

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

　　1）共享鎖: 又叫做讀鎖. 當用戶要進行數據的讀取時,對數據加上共享鎖.共享鎖可以同時加上多個。

　　2）排他鎖: 又叫做寫鎖. 當用戶要進行數據的寫入時,對數據加上排他鎖.排他鎖只可以加一個,他和其他的排他鎖,共享鎖都相斥。

　　鎖的粒度取決于具體的存儲引擎,InnoDB實現了行級鎖,頁級鎖,表級鎖。

　　他們的加鎖開銷從大大小,并發能力也是從大到小。

　　框架篇

　　1、Mysql 主從復制原理的是啥？

　　MySQL的基礎問題有哪些

　　Master的更新事件(update、insert、delete)會按照順序寫入bin-log中。當Slave連接到Master的后,Master機器會為Slave開啟binlog dump線程,該線程會去讀取bin-log日志。

　　Slave連接到Master后,Slave庫有一個I/O線程通過請求binlog dump thread讀取bin-log日志,然后寫入從庫的relay log日志中。

　　Slave還有一個 SQL線程,實時監控 relay-log日志內容是否有更新,解析文件中的SQL語句,在Slave數據庫中去執行。

　　2、Mysql主從復制同步方式有哪些？

　　異步復制：Mysql主從同步默認是異步復制的。就是上面三步中,只有第一步是同步的(也就是Mater寫入bin log日志),就是主庫寫入binlog日志后即可成功返回客戶端，無須等待binlog日志傳遞給從庫的過程。

　　同步復制：對于同步復制而言，Master主機將事件發送給Slave主機后會觸發一個等待，直到所有Slave節點（如果有多個Slave）返回數據復制成功的信息給Master。

　　半同步復制：對于半同步復制而言，Master主機將事件發送給Slave主機后會觸發一個等待，直到其中一個Slave節點（如果有多個Slave）返回數據復制成功的信息給Master。

　　3、Mysql主從同步延時產生原因?怎么優化？

　　主節點如果執行一個很大的事務，那么就會對主從延遲產生較大的影響

　　網絡延遲，日志較大，slave數量過多

　　主上多線程寫入，從節點只有單線程同步

　　機器性能問題，從節點是否使用了“爛機器”

　　鎖沖突問題也可能導致從機的SQL線程執行慢

　　4、Mysql主從同步延時產生原因?怎么優化？

　　大事務：將大事務分為小事務，分批更新數據

　　減少Slave的數量，不要超過5個，減少單次事務的大小

　　Mysql 5.7之后，可以使用多線程復制，使用MGR復制架構

　　在磁盤、raid卡、調度策略有問題的情況下可能會出現單個IO延遲很高的情況，可用iostat命令查看DB數據盤的IO情況，再進一步判斷

　　針對鎖問題可以通過抓去processlist以及查看information_schema下面和鎖以及事務相關的表來查看。

　　6、bin log/redo log/undo log是什么？

　　bin log是Mysql數據庫級別的文件，記錄對Mysql數據庫執行修改的所有操作，不會記錄select和show語句。

　　redo log中記錄的是要更新的數據，比如一條數據已提交成功，并不會立即同步到磁盤，而是先記錄到redo log中，等待合適的時機再刷盤，為了實現事務的持久性。

　　undo log用于數據的撤回操作，它保留了記錄修改前的內容。通過undo log可以實現事務回滾，并且可以根據undo log回溯到某個特定的版本的數據，實現MVCC。

（編輯：武林網）

上一篇：mysql中的權限有哪些

下一篇：mysql如何查詢庫中所有表