Mysql面經

Select語句的執行順序

1、from 子句組裝來自不同數據源的數據；
2、where 子句基于指定的條件對記錄行進行篩選；
3、group by 子句將數據劃分為多個分組；
4、使用聚集函數進行計算；AVG() SUM() MAX() MIN() COUNT()
5、使用 having 子句篩選分組；
6、計算所有的表達式；
7、select 的字段；
8、使用 order by 對結果集進行排序。

where和having的區別

where是一個約束聲明,使用where來約束來自數據庫的數據;
where是在結果返回之前起作用的;
where中不能使用聚合函數。
having:
having是一個過濾聲明;
在查詢返回結果集以后，對查詢結果進行的過濾操作;
在having中可以使用聚合函數。
where和having的執行順序：where早于group by早于having。

count（*）和count（列名）的區別?

1、count(*)包含了所有的列，相當于行數，在統計結果的時候，不會忽略列值為空的情況；
2、count(1)在統計結果的時候也不會忽略列值為空的情況（即某個列為空時，仍進行統計）；
3、count(列名)在統計的時候會忽略列名為空（null）的情況（即某個列為空時，不統計）；

數據庫一二三范式的作用？

第一范式就是屬性不可分割，每個字段都應該是不可再拆分的。（姓名）
第二范式是在第一范式的基礎上更進一步。第二范式就是要求表中要有主鍵，表中其他字段都依賴于主鍵，因此第二范式只要記住主鍵約束就好了。
第三范式就是確保數據表中的每一列數據都和主鍵直接相關，而不能間接相關。也就是要消除傳遞依賴，方便理解，可以看做是消除冗余，因此第三范式只要記住外鍵約束就好了。

范式可以避免數據冗余，減少數據庫的空間，減輕維護數據完整性的麻煩。范式越高性能就會越差。一般在項目中，用得最多的也就是第三范式

InnoDB的Buffer Pool MySQL日志

redo log和undo log的區別
1.redo log通常是物理日志，記錄的是數據頁的物理修改，而不是某一行或某幾行修改成怎樣怎樣，它用來恢復提交后的物理數據頁(恢復數據頁，且只能恢復到最后一次提交的位置)。

2.undo用來回滾行記錄到某個版本。undo log一般是邏輯日志，根據每行記錄進行記錄。

redo log和二進制日志的區別

二進制日志是在存儲引擎的上層產生的，不管是什么存儲引擎，對數據庫進行了修改都會產生二進制日志。而redo log是innodb層產生的，只記錄該存儲引擎中表的修改。并且二進制日志先于redo log被記錄
二進制日志記錄操作的方法是邏輯性的語句。即便它是基于行格式的記錄方式，其本質也還是邏輯的SQL設置，如該行記錄的每列的值是多少。而redo log是在物理格式上的日志，它記錄的是數據庫中每個頁的修改。

（1）作用不同：redo log是用于crash recovery的，保證MySQL宕機也不會影響持久性；binlog是用于point-in-time recovery的，保證服務器可以基于時間點恢復數據，此外binlog還用于主從復制。

（2）層次不同：redo log是InnoDB存儲引擎實現的，而binlog是MySQL的服務器層(可以參考文章前面對MySQL邏輯架構的介紹)實現的，同時支持InnoDB和其他存儲引擎。

（3）內容不同：redo log是物理日志，內容基于磁盤的Page；binlog的內容是二進制的，根據binlog_format參數的不同，可能基于sql語句、基于數據本身或者二者的混合。

（4）寫入時機不同：binlog在事務提交時寫入；redo log的寫入時機相對多元：

mysql中drop、truncate和delete的區別

delete和truncate只刪除表的數據不刪除表的結構速度,一般來說: drop> truncate >delete delete語句是dml,這個操作會放到rollback segement中,事務提交之后才生效; 如果有相應的trigger,執行的時候將被觸發.
truncate,drop是ddl, 操作立即生效,原數據不放到rollback segment中,不能回滾.
操作不觸發trigger.

事務的ACID特性

acid，是指在數據庫管理系統中事務所具有的四個特性：原子性、一致性、隔離性、持久性。
原子性（Atomicity）：事務是不可分割的最小操作單元，要么全部成功，要么全部失敗。
一致性（Consistency）：事務完成時，必須使所有的數據都保持一致狀態。
隔離性（Isolation）：數據庫系統提供的隔離機制，保證事務在不受外部并發操作影響的獨立環境下運行。
持久性（Durability）：事務一旦提交或回滾，它對數據庫中的數據的改變就是永久的
而對于這四大特性，實際上分為兩個部分。其中的原子性、一致性、持久化，實際上是由InnoDB中的兩份日志來保證的，一份是redo log日志，一份是undo log日志。而持久性是通過數據庫的鎖，加上MVCC來保證的。

說一下MySQL執行一條查詢語句的內部執行?

1.首先使用登錄命令登錄 MySQL，登錄后，輸入一條查詢語句；
2.執行后，會首先查詢緩存，若緩存中有對應的數據，直接返回，若沒有，則會找分析器進行下一步操作，注意，只有在開啟查詢緩存時才會執行這一步，MySQL 8.0 版本后就沒有查詢緩存了，語句會直接走分析器；
3.分析器首先對 SQL 語句進行詞法分析，根據輸入的 select，判斷這條語句是查詢語句，并將后面的字符串識別成對應的表名與列名，隨后會對 SQL 語句進行語法分析，判斷這條語句是否符合 MySQL 的語法規則，若符合，則會進入優化器階段；
4.優化器會根據語句來選擇這條語句的具體執行方案，然后交給執行器執行；
5.執行器會判斷用戶對要使用的表有沒有執行查詢的權限，若沒有權限，就會返回沒有權限的錯誤

innodb和myisam的特點與區別？

InnoDB支持事務，MyISAM不支持，
InnoDB支持外鍵，而MyISAM不支持
InnoDB是聚集索引，使用B+Tree作為索引結構，數據文件是和（主鍵）索引綁在一起的
MyISAM是非聚集索引，索引和數據文件是分離的，索引保存的是數據文件的指針。

InnoDB的B+樹主鍵索引的葉子節點就是數據文件，輔助索引的葉子節點是主鍵的值；而MyISAM的B+樹主鍵索引和輔助索引的葉子節點都是數據文件的地址指針。

MySQL建立索引方式簡單說一下索引的優缺點

一、索引的優點
1）創建索引可以大幅提高系統性能，幫助用戶提高查詢的速度；
2）通過索引的唯一性，可以保證數據庫表中的每一行數據的唯一性；
3）可以加速表與表之間的鏈接；
4）降低查詢中分組和排序的時間。
二、索引的缺點
1）索引的存儲需要占用磁盤空間；
2）當數據的量非常巨大時，索引的創建和維護所耗費的時間也是相當大的；
3）當每次執行CRU操作時，索引也需要動態維護，降低了數據的維護速度

索引的底層實現？ B+樹

所有的數據都會出現在葉子節點。
葉子節點形成一個單向鏈表。
非葉子節點僅僅起到索引數據作用，具體的數據都是在葉子節點存放的。

MySQL索引數據結構對經典的B+Tree進行了優化。在原B+Tree的基礎上，增加一個指向相鄰葉子節點的鏈表指針，就形成了帶有順序指針的B+Tree，提高區間訪問的性能，利于排序。

聚簇索引和非聚簇索引

含義：將數據存儲與索引放一塊，索引結構的葉子節點保存了行數據，將數據與索引分開存儲，索引結構的葉子節點關聯的是對應的主鍵
特點：必須有，而且只有一個（如果不存在主鍵，將使用第一個唯一（UNIQUE）索引作為聚集索引。如果表沒有主鍵，或沒有合適的唯一索引，則InnoDB會自動生成一個rowid作為隱藏的聚集索引。）可以存在多個
聚集索引的葉子節點下掛的是這一行的數據。
二級索引的葉子節點下掛的是該字段值對應的主鍵值。

索引最左前綴/最左匹配了解嗎？

最左前綴法則中指的最左邊的列，是指在查詢時，聯合索引的最左邊的字段(即是第一個字段)必須存在。不存在會導致聯合索引不被使用。

mysql索引命中規則——最左匹配原則索引失效的情況

最左前綴法則中指的最左邊的列，是指在查詢時，聯合索引的最左邊的字段(即是第一個字段)必須存在。不存在會導致復合索引不被使用。

如何分析SQL語句的性能，要關注哪些字段？

1、查詢語句中不要使用select *
2、盡量減少子查詢，使用關聯查詢（left join,right join,inner join）替代
3、減少使用IN或者NOT IN ,使用exists，not exists或者關聯查詢語句替代
4、or 的查詢盡量用 union或者union all 代替(在確認沒有重復數據或者不用剔除重復數據時，
union all會更好)
5、應盡量避免在 where 子句中使用!=或<>操作符，否則將引擎放棄使用索引而進行全表掃描。
6、應盡量避免在 where 子句中對字段進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表
掃描，如： select id from t where num is null 可以在num上設置默認值0，確保表中num列沒有null值，然后這樣查詢： select id from t where num=0

什么是臟讀，不可重復讀和幻讀？

臟讀（Dirty read）: 當一個事務正在訪問數據并且對數據進行了修改，而這種修改還沒有提
交到數據庫中，這時另外一個事務也訪問了這個數據，然后使用了這個數據。因為這個數據是還沒有提交的數據，那么另外一個事務讀到的這個數據是“臟數據”，依據“臟數據”所做的操作可能是不正確的。
丟失修改（Lost to modify）: 指在一個事務讀取一個數據時，另外一個事務也訪問了該數
據，那么在第一個事務中修改了這個數據后，第二個事務也修改了這個數據。這樣第一個事務內的修改結果就被丟失，因此稱為丟失修改。例如：事務1讀取某表中的數據A=20，事務2也讀取A=20，事務1修改A=A-1，事務2也修改A=A-1，最終結果A=19，事務1的修改被丟失。
不可重復讀（Unrepeatableread）: 指在一個事務內多次讀同一數據。在這個事務還沒有結
束時，另一個事務也訪問該數據。那么，在第一個事務中的兩次讀數據之間，由于第二個事務的修改導致第一個事務兩次讀取的數據可能不太一樣。這就發生了在一個事務內兩次讀到的數據是不一樣的情況，因此稱為不可重復讀。
幻讀（Phantom read）: 幻讀與不可重復讀類似。它發生在一個事務（T1）讀取了幾行數
據，接著另一個并發事務（T2）插入了一些數據時。在隨后的查詢中，第一個事務（T1）就會發現多了一些原本不存在的記錄，就好像發生了幻覺一樣，所以稱為幻讀。

innodb引擎的4大特性

插入緩沖（insert buffer)；

二次寫(double write)；

自適應哈希索引(ahi)；

預讀(read ahead)。

為什么Elasticsearch比MySql的檢索快？

1）基于分詞后的全文檢索：例如select * from test where name like ‘%張三%’，對于mysql來說，因為索引失效，會進行全表檢索；對es而言分詞后，每個字都可以利用FST高速找到倒排索引的位置，并迅速獲取文檔id列表，大大的提升了性能，減少了磁盤IO。
2）精確檢索：進行精確檢索，有些時候可能mysql要快一些，當mysql的非聚合索引引用上了聚合索引，無需回表，則速度上可能更快；es還是通過FST找到倒排索引的位置比獲取文檔id列表，再根據文檔id獲取文檔并根據相關度進行排序。但是es還有個優勢，就是es即天然的分布式能夠在大量數據搜索時可以通過分片降低檢索規模，并且可以通過并行檢索提升效率，用filter時，更是可以直接跳過檢索直接走緩存。

數據的鎖的種類，加鎖的方式InnoDB鎖的有哪幾種？

MySQL 中有共享鎖和排它鎖，也就是讀鎖和寫鎖。

共享鎖：不堵塞，多個用戶可以同一時刻讀取同一個資源，相互之間沒有影響。
排它鎖：一個寫操作阻塞其他的讀鎖和寫鎖，這樣可以只允許一個用戶進行寫入，防止其他用戶讀取正在寫入的資源。
表鎖：系統開銷最小，會鎖定整張表，MyISAM 使用表鎖。
行鎖：容易出現死鎖，發生沖突概率低，并發高，InnoDB 支持行鎖（必須有索引才能實現，否則會自動鎖全表，那么就不是行鎖了）。

InnoDB怎么解決幻讀的？

READ-UNCOMMITTED(讀取未提交)：最低的隔離級別，允許讀取尚未提交的數據變更，可
能會導致臟讀、幻讀或不可重復讀。
READ-COMMITTED(讀取已提交)：允許讀取并發事務已經提交的數據，可以阻止臟讀，但是幻讀或不可重復讀仍有可能發生。
REPEATABLE-READ(可重復讀)：對同一字段的多次讀取結果都是一致的，除非數據是被本身事務自己所修改，可以阻止臟讀和不可重復讀，但幻讀仍有可能發生。
SERIALIZABLE(可串行化)：最高的隔離級別，完全服從ACID的隔離級別。所有的事務依次逐個執行，這樣事務之間就完全不可能產生干擾，也就是說，該級別可以防止臟讀、不可重復讀以及幻讀。
與 SQL 標準不同的地方在于 InnoDB 存儲引擎在 REPEATABLE-READ（可重
讀）事務隔離級別下使用的是Next-Key Lock 鎖算法，因此可以避免幻讀的產生。

可重復讀如何實現

可重復讀的核心就是一致性讀(consistent read);而事務更新數據的時候，只能用當前讀。如果當前的記錄的行鎖被其他事務占用的話，就需要進入鎖等待。

樂觀鎖和MVCC的區別？

MVCC（Multi-Version Concurrent Control），基于快照隔離機制(Snapshot Isolations)進行多版本并發控制，是一種以樂觀鎖為理論基礎的，用來解決讀-寫沖突的無鎖并發控制。也就是為事務分配單向增長的時間戳，為每個修改保存一個版本，版本與事務時間戳關聯，讀操作只讀該事務開始前的數據庫的快照，也就是說，事務開啟時看到是哪個版本就看到這個版本，這樣在讀操作不用阻塞寫操作，寫操作不用阻塞讀操作，提升性能的同時，避免了臟讀和不可重復讀

樂觀鎖（基于數據版本（ Version ）記錄機制實現并發控制）是一種基礎理論，在讀寫事務，在真正的提交之前，不加讀/寫鎖，而是先看一下數據的版本/時間戳，等到真正提交的時候再看一下版本/時間戳，如果兩次相同，說明別人期間沒有對數據進行過修改，那么就可以放心提交；如果遇到沖突，則需要回退。