-
技術性
1.加鎖lock
2.多行并發cuontdownlatch
3.最基本的代碼快,以及運行的結果與驗證邏輯和目的查看全部 -
1.搶紅包的設計目的是?
2.搶紅包的代碼并發與分類塊的群體如何區分?大數據分析的出用戶手機的人群角色?
3.搶紅包為了營造氣氛和收割用戶互聯網社交與用戶未來角色與精準營銷而設計金額大???
4.老年用戶與年輕用戶之間的互動,家庭成員之間關于網絡使用的正確打開方式的模塊會帶來更好的春晚的團聚和良好的國風家風?
5.免費始終是免費,可付費也不見得這么多年春晚有搶紅包環節是市場營銷、頭部冠名贊助商、互聯網大咖品牌贊助商、實體品牌贊助商有將廣告文案、營銷與紅包讓利及真正的老舍下的身心健康真正運用到實際,哪怕是無窮接近到實際也好,但從無。這也是這個免費課程無法帶來更深層次的教育營銷和真正應用到各行各業的營銷課程。下沉的教育營銷和公益營銷都夠不著………查看全部 -
學習數據
查看全部 -
Mysql
1.一條SQL查詢語句是如何執行的?mysql的服務架構
整體結構:MySQL Server架構自頂向下大致可以分網絡連接層、服務層、存儲引擎層和系統文件層。
網絡連接層:也就是各種數據庫連接客戶端,如 常見的 Java、C、Python、.NET等,它們通過各自API技術與MySQL建立連接。
服務層:是MySQL Server的核心,主要包含 連接器、查詢緩存、分析器、優化器、執行器等。
存儲引擎層:各種存儲引擎,其架構模式是插件式的,存儲引擎層負責數據的存儲和提取,MySQL 5.5.5 版本InnoDB開始成為了默認存儲引擎。
系統文件層:各種系統文件,比如日志文件,比如 binlog? undo.log redo.log?
上面我們提到mysql的服務層劃分:
第一步:連接器:負責管理鏈接,用戶賬戶權限驗證? ?
第二步:查詢緩存:查詢緩存,命中直接返回?
連接建立完成后,你就可以執行sql查詢語句了,執行邏輯就會來到第二步:查詢緩存。
MySQL 拿到一個查詢請求后,會先到查詢緩存看看,之前是不是執行過這條語句。?
如果查詢命中緩存,MySQL 不需要執行后面的復雜操作,就可以直接返回結果,這個效率會很高。
但是:不建議使用查詢緩存,原因如下:
因為查詢緩存往往弊大于利。查詢緩存的失效非常頻繁,只要有對一個表的更新,這個表上所有的查詢緩存都會被清空。
對于你確定要使用查詢緩存的語句,可以用 SQL_CACHE 顯式指定,像下面這個語句一樣:
mysql> select SQL_CACHE * from T where ID=10;
同時:需要注意的是,MySQL 8.0 版本直接將查詢緩存的整塊功能刪掉了,也就是說 8.0 開始徹底沒有這個功能了。
查詢緩存如何設置:
have_query_cache 表示當前mysql版本是否支持查詢緩存。
query_cache_type 0 時表示關閉,1時表示打開,2表示只要select 中明確指定SQL_CACHE才緩存。?
query_cache_limit 表示單個結果集所被允許緩存的最大值。?
query_cache_min_res_unit 每個被緩存的結果集要占用的最小內存。?
query_cache_size 用于查詢緩存的內存大小。
qcache_free_memory 查詢緩存目前剩余空間大小。
qcache_hits 查詢緩存的命中次數。?
qcache_inserts 查詢緩存插入的次數。
第三步:分析器:詞法分析 語法分析
如果沒有命中查詢緩存,就要開始真正執行語句了。首先,MySQL 需要知道你要做什么,因此需要對 SQL 語句做解析,生成"解析樹"。
預處理器根據一些MySQL規則進一步檢查“解析樹”是否合法;
分析器先會做“詞法分析”。你輸入的是由多個字符串和空格組成的一條 SQL 語句,MySQL 需要識別出里面的字符串分別是什么,代表什么。
做完了這些識別以后,就要做“語法分析”。根據詞法分析的結果,語法分析器會根據語法規則,判斷你輸入的這個sql語句是否滿足 MySQL 語法。
?
第四步:執行優化器
經過了分析器,MySQL 就知道你要做什么了。在開始執行之前,還要先經過優化器的處理。
優化器是 決定使用哪個索引; 決定各個表的連接順序。?
原則是:盡可能掃描少的數據庫行紀錄
第五步:執行器: 操作存儲引擎,獲取結果并返回
MySQL 通過分析器知道了你要做什么,通過優化器知道了該怎么做,于是就進入了執行器階段,開始執行語句。
開始執行的時候,先檢驗權限
如果操作表的權限,就打開表繼續執行。打開表的時候,執行器就會根據表的存儲引擎,去使用這個引擎提供的接口。
?
2.日志系統:一條SQL更新語句是如何執行的??
關鍵詞: WAL write ahead logging 先寫日志、 undolog redolog 、 binlog、 兩階段提交? innodb存儲引擎
如果要將 ID=2 這一行的值加 1,SQL 語句就會這么寫:
mysql> update T set c=c+1 where ID=2;
該過程是如何執行的呢?
首先,可以確定的說,查詢語句的那一套流程,更新語句也是同樣會走一遍。
1.你執行語句前要先連接數據庫,這是連接器的工作。
2.前面我們說過,在一個表上有更新的時候,跟這個表有關的查詢緩存會失效,所以這條語句就會把表 T 上所有緩存結果都清空。這也就是我們一般不建議使用查詢緩存的原因。
3.接下來,分析器會通過詞法解析和語法解析知道這是一條更新語句。
4.優化器決定要使用 ID 這個索引。
5.然后,執行器負責具體執行,找到這一行,然后更新。
更新流程還涉及兩個重要的日志模塊:redo log(重做日志)和 binlog(歸檔日志)
客戶端執行DDL語句(create)/ DML語句(insert,update,delete)/DCL語句(grant,revoke),數據庫服務端執行的時候會涉及到 undolog(撤銷日志) redolog(重做日志) 和 binlog (歸檔日志)
show engines:查看當前數據庫支持的引擎信息,在5.5版本之前默認采用MyISAM存儲引擎,從5.5開始采用InnoDB存儲引
常見存儲引擎有:
InnoDB:支持事務,具有提交,回滾和崩潰恢復能力,事務安全
MyISAM:不支持事務和外鍵,訪問速度快
Memory:利用內存創建表,訪問速度非常快,因為數據在內存,而且默認使用Hash索引,但是一旦關閉,數據就會丟失
了解 redolog(重做日志) 和 binlog (歸檔日志) 之前我們先詳細學習下Innodb存儲引擎的架構模型
? 分析下 Innodb 存儲引擎:
Innodb存儲引擎: 它支持外鍵,擅長處理事務,具有自動崩潰恢復的特性;
InnoDB引擎架構圖:主要分為內存結構和磁盤結構兩大部分
內存結構:
內存結構主要包括Buffer Pool、Change Buffer、Adaptive Hash Index和Log Buffer四大組件
Buffer Pool:緩沖池,以緩存頁page作為基本單位,大小默認為16k, 緩存頁之間通過鏈表進行鏈接
作用:在InnoDB訪問表記錄和索引時會在Page頁中緩存,以后使用可以減少磁盤IO操作,提升效率
page頁的類型分為三種:
free page :空閑page,未被使用
clean page:被使用page,數據沒有被修改過
dirty page:臟頁,被使用page,數據被修改過,頁中數據和磁盤的數據產生了不一致
page頁通過三種鏈表來進行維護
free list :表示空閑緩沖區,管理free page
flush list:表示需要刷新到磁盤的緩沖區,管理dirty page,內部page按修改時間排序。臟頁即存在于flush鏈表,也在LRU鏈表中,但是兩種互不影響,LRU鏈表負責管理page的可用性和釋放,而flush鏈表負責管理臟頁的刷盤操作。
lru list? :表示正在使用的緩沖區,管理clean page和dirty page,緩沖區以midpoint為基點,前面鏈表稱為new列表區,存放經常訪問的數據,占63%;后面的鏈表稱為old列表區,存放使用較少數據,占37%。
Change Buffer:
寫緩存區:在進行DML操作時,如果緩存池中沒有其相應的Page數據,并不會立刻將磁盤頁加載到緩沖池,而是在寫緩沖區記錄緩沖變更,等未來數據被讀取時,再將數據合并恢復到BP中。
當更新一條記錄時,該記錄在BufferPool存在,直接在BufferPool修改,一次內存操作。
如果該記錄在BufferPool不存在(沒有命中),會直接在ChangeBuffer進行一次內存操作,不用再去磁盤查詢數據,避免一次磁盤IO。
當下次查詢記錄時,會先進性磁盤讀取,然后再從ChangeBuffer中讀取信息合并,最終載入BufferPool中。
?
Adaptive Hash Index:自適應哈希索引,用于優化對BP數據的查詢
InnoDB存儲引擎會自動根據訪問的頻率和模式來為某些頁建立哈希索引。
Log Buffer: 日志緩沖區,用來保存要寫入磁盤上log文件(Redo/Undo)的數據
? ? ?LogBuffer主要是用于記錄InnoDB引擎日志,在DML操作時會產生Redo和Undo日志
磁盤結構:
表空間:存放表結構和數據(InnoDB使用聚集索引(聚簇索引),索引和記錄在一起存儲,既緩存索引,也緩存記錄。)
數組字典:InnoDB數據字典由內部系統表組成,這些表包含用于查找表、索引和表字段等對象的元數據
redo日志:用于在崩潰恢復期間更正不完整事務寫入的數據。MySQL以循環方式寫入重做日志文件,記錄InnoDB中所有對Buffer Pool修改的日志。
undo日志:撤消日志是在事務開始之前保存的被修改數據的備份,用于例外情況時回滾事務
3.詳細解析日志文件 undo.log redo.log binlog作用和功能?
undo.log撤銷日志:
Undo Log:數據庫事務開始之前,會將要修改的記錄存放到 Undo 日志里,當事務回滾時或者數據庫崩潰時,可以利用 Undo 日志,撤銷未提交事務對數據庫產生的影響
Undo Log產生和銷毀:
Undo Log在事務開始前產生;
事務在提交時,并不會立刻刪除undolog,innodb會將該事務對應的undo log放入到刪除列表中,后面會通過后臺線程purge thread進行回收處理。
Undo Log屬于邏輯日志,記錄一個變化過程。例如執行一個delete,undolog會記錄一個insert;執行一個update,undolog會記錄一個相反的update。
Undo Log存儲:undo log采用段的方式管理和記錄。在innodb數據文件中包含一種rollback segment回滾段,內部包含1024個undo log segment。
Undo Log作用:
實現事務原子性:事務處理過程中,如果出現了錯誤或者用戶執行了 ROLLBACK 語句,MySQL 可以利用 Undo Log 中的備份將數據恢復到事務開始之前的狀態
實現MVCC多版本控制:事務未提交之前,Undo Log保存了未提交之前的版本數據,Undo Log 中的數據可作為數據舊版本快照供其他并發事務進行快照讀
redo.log重做日志:
redo.log:以恢復操作為目的,在數據庫發生意外時重現操作。防止在發生故障的時間點,尚有臟頁未寫入表的 IBD 文件中,在重啟 MySQL服務的時候,根據Redolog進行重做,從而達到事務的未入磁盤數據進行持久化這一特性。
? ? ?redo.Log 的生成和釋放:?
隨著事務操作的執行,就會生成Redo Log,在事務提交時會將產生Redo Log寫入Log Buffer,并不是隨著事務的提交就立刻寫入磁盤文件。(兩階段提交)
等事務操作的臟頁寫入到磁盤之后,Redo Log 的使命也就完成了,Redo Log占用的空間就可以重用(被覆蓋寫入)。
redo.log的寫入機制:循環順序寫
屬于innodb,4個文件共4GB大小,環形,磁盤地址有序,負責事務。crash-safe能力。?
Mysql數據庫的寫前日志(Write Ahead Log, WAL)機制,也就是說,在實際將數據記錄表中之前,先把修改的數據記到日志文件中,以便故障時進行恢復。
binlog歸檔日志:
屬于mysql server層,無大小限制,會無限創建binlog文件,負責歸檔恢復。
binlog是記錄所有數據庫表結構變更以及表數據修改的二進制日志,不會記錄SELECT和SHOW這類操作。
binlog日志是以事件形式(Log Event)記錄,還包含語句所執行的消耗時間。
應用場景:
主從復制:在主庫中開啟Binlog功能,這樣主庫就可以把Binlog傳遞給從庫,從庫拿到Binlog后實現數據恢復達到主從數據一致性。
數據恢復:通過mysqlbinlog工具來恢復數據。
redo 和undo 兩種日志的相同和差異:
1redo log 是 InnoDB 引擎特有的;binlog 是 MySQL 的 Server 層實現的,所有引擎都可以使用。
2redo log 是物理日志,記錄的是“在某個數據頁上做了什么修改”;binlog 是邏輯日志,記錄的是這個語句的原始邏輯,比如“給 ID=2 這一行的 c 字段加 1 ”。
3redo log 是循環寫的,空間固定會用完;binlog 是可以追加寫入的。“追加寫”是指 binlog 文件寫到一定大小后會切換到下一個,并不會覆蓋以前的日志。
4.數據庫的兩階段提交:為了保證binlog和redolog兩個日志之間的一致性
兩階段提交過程:
1.server拿到數據對數據修改,把修改結果發給引擎。?
2.引擎記錄redo log狀態為 prepare,之后告訴server可以提交事務。?
3.server接到通知后記錄binlog,之后調引擎接口提交事務。?
4.引擎接到提交事務的通知,將redo log改為commit狀態。
引擎修改兩次redo log的狀態的操作叫兩階段提交。?
為什么日志需要“兩階段提交”。這里不妨用反證法來進行解釋。? 崩潰恢復? 和? 數據主從同步
由于 redo log 和 binlog 是兩個獨立的邏輯,如果不用兩階段提交,要么就是先寫完 redo log 再寫 binlog,或者采用反過來的順序。我們看看這兩種方式會有什么問題
仍然用前面的 update 語句來做例子。假設當前 ID=2 的行,字段 c 的值是 0,再假設執行 update 語句過程中c=1 ,在寫完第一個日志后,第二個日志還沒有寫完期間發生了 crash,會出現什么情況呢?
第一種情況:先寫 redo log 后寫 binlog。假設在 redo log 寫完,binlog 還沒有寫完的時候,MySQL 進程異常重啟。
? ?由于我們前面說過的,redo log 寫完之后,系統即使崩潰,仍然能夠把數據恢復回來,所以恢復后這一行 c 的值是 1。
? ? ? ? ? ?但是由于 binlog 沒寫完就 crash 了,這時候 binlog 里面就沒有記錄這個語句。因此,之后備份日志的時候,存起來的 binlog 里面就沒有這條語句。
? ? ? ? ? ?然后你會發現,如果需要用這個 binlog 來恢復臨時庫的話,由于這個語句的 binlog 丟失,這個臨時庫就會少了這一次更新,恢復出來的這一行 c 的值就是 0,與原庫的值不同。
第二種情況:先寫 binlog 后寫 redo log。如果在 binlog 寫完之后 crash,由于 redo log 還沒寫,崩潰恢復以后這個事務無效,所以這一行 c 的值是 0。
? ?但是 binlog 里面已經記錄了"把 c 從 0 改成 1"這個日志。所以,在之后用 binlog 來恢復的時候就多了一個事務出來,恢復出來的這一行 c 的值就是 1,與原庫的值不同。
可以看到,如果不使用“兩階段提交”,那么數據庫的狀態就有可能和用它的日志恢復出來的庫的狀態不一致。
5.詳解事務的ACID特性以及實現原理? 和 事務隔離級別 以及 多版本控制MVCC
事務:簡單來說,事務就是要保證一組數據庫操作,要么全部成功,要么全部失敗。在 MySQL 中,事務支持是在引擎層實現的;
提到事務,你肯定會想到 ACID,那它們是如何實現的呢?(只有Innodb存儲引擎支持事務)
原子性(Atomicity): 事務是一個原子操作單元,其對數據的修改,要么全都執行,要么全都不執行。
事務的原子性是通過WAL(先寫日志再刷磁盤)實現。最終落地到 undo日志實現
隔離性(Isolation):指的是一個事務的執行不能被其他事務干擾,即一個事務內部的操作及使用的數據對其他的并發事務是隔離的。
InnoDB 支持的隔離性有 4 種,隔離性從低到高分別為:讀未提交、讀已提交、可重復讀、串行化。?
底層是通過對 鎖 和 多版本控制(MVCC)技術的封裝,從而實現不通的隔離級別
持久性:指的是一個事務一旦提交,它對數據庫中數據的改變就應該是永久性的,后續的操作或故障不應該對其有任何影響,不會丟失。
redo log在系統宕機重啟之類的情況時,可以修復數據,從而保障事務的持久性。
MySQL的持久性也與WAL技術相關,redo log在系統Crash重啟之類的情況時,可以修復數據,從而保障事務的持久性。
通過原子性可以保證邏輯上的持久性,通過存儲引擎的數據刷盤可以保證物理上的持久性。
一致性(Consistency): 指的是事務開始之前和事務結束之后,數據庫的完整性限制未被破壞。一致性包括兩方面的內容,分別是約束一致性和數據一致性。
一致性也可以理解為數據的完整性。數據的完整性是通過原子性、隔離性、持久性來保證的,而這3個特性又是通過 Redo/Undo 來保證的
?
6.如果沒有事務隔離會產生什么問題?
當數據庫上有多個事務同時操作同一條記錄的時候,就可能出現臟讀(dirty read)、不可重復讀(non-repeatable read)、幻讀(phantom read)的問題
更新丟失
當兩個或多個事務選擇同一行,然后基于最初選定的值更新該行時,由于每個事務都不知道其他事務的存在,就會發生丟失更新問題:最后的更新覆蓋了由其他事務所做的更新。
當兩個或多個事務更新同一行記錄,會產生更新丟失現象??梢苑譃榛貪L覆蓋和提交覆蓋。
回滾覆蓋:一個事務回滾操作,把其他事務已提交的數據給覆蓋了。
提交覆蓋:一個事務提交操作,把其他事務已提交的數據給覆蓋了。
臟讀: 讀到其他事務未提交的數據; ? (一個事務讀取了其它事務update后未提交的數據,注意是針對其它事務的修改未提交的操作 )
不可重復讀: 前后讀取的同一條記錄內容不一致; (一個事務讀取了其它事務update或delete后已提交的數據,注意是針對其它事務修改或刪除已提交的操作 )
幻讀: 前后讀取的記錄數量不一致。 ? (一個事務讀取了其它事務新insert已經提交的數據。注意是針對其它事務新增已提交的數據)
7.mysql的隔離級別有哪些? 隔離級別是如何實現的?隔離級別如何設置?
在談隔離級別之前,你首先要知道,隔離得越嚴實,數據庫執行效率就會越低。因此很多時候,我們都要在二者之間尋找一個平衡點。
SQL標準的事務隔離級別包括:
讀未提交 : 一個事務還沒提交時,它做的變更就能被別的事務看到。(別人改數據的事務尚未提交,我在我的事務中也能讀到)
讀已提交 : 一個事務提交之后,它做的變更才會被其他事務看到。? (別人改數據的事務已經提交,我在我的事務中才能讀到)
可重復讀**: 一個事務執行過程中看到的數據,總是跟這個事務在啟動時看到的數據是一致的。(別人改數據的事務已經提交了,但該事務比我的事務開始的晚,我在我的事務中也不去讀)
串行化: 顧名思義是對于同一行記錄,當出現讀寫鎖沖突的時候,后訪問的事務必須等前一個事務執行完成,才能繼續執行;(我的事務尚未提交,別人就別想改數據)
數據庫對四種隔離級別的實現,是依靠視圖來實現的:
1、讀未提交:沒有視圖的概念,直接返回記錄的最新值;?
2、讀已提交:每次執行sql語句之前創建視圖;?
3、可重復讀:每次創建事務的時候創建視圖;?
4、串行化:通過加鎖來避免并行訪問
事務設置隔離級別的相關命令:
mysql>? show variables like 'transaction_isolation';查看事務隔離級別
set tx_isolation='各個隔離級別';// 設置事務隔離級別
8.什么是索引?以及索引的作用?常用的索引有哪些類型?
索引一種數據結構,通常采用B+樹實現;為了提升查詢速率;類似于新華字典中的字典目錄,索引是存儲在文件中的,因此需要占據物理空間。因此并不是索引越多越好
索引可以分為以下幾種:
從應用層次劃分:普通索引:沒有任何限制、? 唯一索引:唯一可為空、 主鍵索引:唯一且非空、 復合索引:遵循最左前綴原則
從數據存儲和索引鍵值邏輯關系劃分:非聚集索引(非聚簇索引,數據和索引分開存放 ,比如mysame)、聚集索引(聚簇索引 比如 innodb引擎將數據和索引存儲在一個表中)
從索引鍵值類型劃分:主鍵索引、輔助索引(二級索引)
從索引存儲數據結構劃分:B+樹索引、Hash索引、FULLTEXT全文索引等
9.索引采用了哪些數據結構?常用的有三種:哈希表? 有序數組? ?B+樹?
哈希表:
哈希表是一種鍵值存儲數據的結構,我們只要輸入待查找的鍵即 key,就可以找到其對應的值即 Value。
如果產生哈希沖突,使用拉鏈法接入,在節點下面掛一個數組或鏈表
查找一個元素的過程:
處理步驟就是:首先,將 key值 通過哈希函數算出 N;如果存在鏈表,按順序遍歷,找到目標記錄。
哈希表只適用于等值查詢,其區間查詢效率很低;
該種數據結構主要應用在 Memory 存儲引擎 和 innodb存儲引擎的自適應hash索引中
有序數組:
基于排序的數組實現;
有序數組能夠解決hash函數不能滿足支持快速范圍查詢。查找速度為log(n),但缺陷也很明顯,針對插入和刪除場景,需要挪動后面的整個記錄,代價太高。
可以結合二分法就可以快速得到某個等值查詢,這個時間復雜度是 O(log(N))。
有序數組適用于靜態搜索引擎。
B+樹(為什么非要采用B+樹)
為什么數據庫存儲使用b+樹 而不是二叉樹,因為二叉樹樹高過高,每次查詢都需要訪問過多節點,即訪問數據塊過多,而從磁盤隨機讀取數據塊過于耗時。
MySQL的存儲結構
表存儲結構
單位:表>段>區>頁>行
在數據庫中, 不論讀一行,還是讀多行,都是將這些行所在的頁進行加載。也就是說存儲空間的基本單位是頁。
一個頁就是一棵樹B+樹的節點,數據庫I/O操作的最小單位是頁,與數據庫相關的內容都會存儲在頁的結構里
10.數據庫三大范式
第一范式:每一列都是不可以再才拆分的
第二范式:基于第一范式,非主鍵字段完全依賴主鍵字段,而不是依賴于主鍵字段的一部分
第三范式:在第二范式的基礎上,非主鍵列只依賴于主鍵,不依賴于其他非主鍵。
在設計數據庫結構的時候,要盡量遵守三范式,如果不遵守,必須有足夠的理由。比如性能。事實上我們經常會為了性能而妥協數據庫的設計
11.binlog的應用場景? Binlog 日志文件的錄入方式,錄入方式優缺點對比?
Binlog是記錄所有數據庫表結構變更以及表數據修改的二進制日志,不會記錄SELECT和SHOW這類操作。
Binlog日志是以事件形式(Log Event)記錄,還包含語句所執行的消耗時間。開啟Binlog日志有以下兩個最重要的使用場景。
主從復制:在主庫中開啟Binlog功能,這樣主庫就可以把Binlog傳遞給從庫,從庫拿到Binlog后實現數據恢復達到主從數據一致性。
數據恢復:通過mysqlbinlog工具來恢復數據。
binlog的日志文件錄入方式有三種: row statment? mixed?
ROW(row-based replication, RBR):日志中會記錄每一行數據被修改的情況,? 修改記錄復制
優點:能清楚記錄每一個行數據的修改細節,能完全實現主從數據同步和數據的恢復。
缺點:批量操作,會產生大量的日志,或者修改表結構,尤其是alter table會讓日志暴漲。
STATMENT(statement-based replication, SBR):記錄每一條修改數據的SQL語句到master的Binlog中,簡稱SQL語句復制。
優點:日志量小,減少磁盤IO,提升存儲和恢復速度
缺點:在某些情況下會導致主從數據不一致,比如last_insert_id()、now()等函數。 無法記錄數據庫函數
MIXED(mixed-based replication, MBR):以上兩種模式的混合使用,一般會使用STATEMENT模式保存binlog,對于STATEMENT模式無法復制的操作使用ROW模式保存binlog,MySQL會根據執行的SQL語句選擇寫入模式。
12.分析下mysql的存儲引擎有哪些?優缺點?以及Innodb和mysiam的區別?存儲引擎選擇?
通過 show engines 可以查看當前數據庫所支持的引擎信息;主要應用的是 innodb myisam , 在mysql版本5.5默認采用myisam存儲引擎,5.6.版本之后采用innodb存儲引擎
innodb 和 myisam 的區別
鎖機制
InnoDB默認支持行級鎖,鎖定指定記錄?;谒饕齺砑渔i實現。
MyISAM默認支持表級鎖,鎖定整張表。
索引結構
InnoDB使用聚集索引(聚簇索引),索引和記錄在一起存儲,既緩存索引,也緩存記錄。
MyISAM使用非聚集索引(非聚簇索引),索引和記錄分開。
并發處理能力
MyISAM使用表鎖,會導致寫操作并發率低,讀之間并不阻塞,讀寫阻塞。
InnoDB讀寫阻塞可以與隔離級別有關,可以采用多版本并發控制(MVCC)來支持高并發
存儲文件
InnoDB表對應兩個文件,一個.frm表結構文件,一個.ibd數據文件。InnoDB表最大支持64TB;
MyISAM表對應三個文件,一個.frm表結構文件,一個MYD表數據文件,一個.MYI索引文件。從MySQL5.0開始默認限制是256TB。
如何選擇存儲引擎:
如果沒有特別的需求,使用默認的Innodb即可。
MyISAM:以讀寫插入為主的應用程序,比如博客系統、新聞門戶網站。
Innodb:更新(刪除)操作頻率也高,或者要保證數據的完整性;并發量高,支持事務和外鍵。比如OA自動化辦公系統。
13.詳解索引為什么是B+tree數據結構,而不采用B tree
B+樹索引:首先B+樹具備以下特性:
1.非葉子節點不存儲data數據,只存儲索引值,這樣便于存儲更多的索引值
2.葉子節點包含了所有的索引值和data數據,也就是說 一顆樹就是帶有索引的完整表記錄
3.葉子節點用指針連接,提高區間的訪問性能
相比B樹,B+樹進行范圍查找時,只需要查找定位兩個節點的索引值,然后利用葉子節點的指針進行遍歷即可。而B樹需要遍歷范圍內所有的節點和數據,顯然B+Tree效率高。
14.什么情況下會導致索引失效? 查詢范圍不明確導致索引失效
1. 使用like 左邊包含 %
2. 不滿足最左前綴原則
3. 在索引列上進行計算或者使用函數
4. 使用了select * ,會導致全表查詢
5. 查詢時字段類型不同,如? 某個表中的code 字段 varchar類型 添加了索引? 查詢時 code='101'可以命中索引,但是code=101就會導致全表掃描status
6. 錯誤的使用 or關鍵字,注意:如果使用了or關鍵字,那么它前面和后面的字段都要加索引,不然所有的索引都會失效,例如:where id=1 or service_code ='test02' or? status =1; 前兩個字段有索引,但是status沒有索引
7. 使用 order by 關鍵字容易導致索引失效
如果order by語句中沒有加where或limit關鍵字,該sql語句將不會走索引。
對不同的索引做order by
聯合索引不滿足最左匹配原則
如果order by后面有一個聯合索引的多個字段,它們具有不同的排序規則也會導致索引失效
8. 使用表中的列進行對比
9. 錯誤的使用 not in 或者 not exists
15.索引分析和優化?如何對數據庫sql語句進行索引分析與優化?explain 命令是如何執行的?
我們可以通過explian命令來查詢sql語句的執行情況,例如 explian select 語句
explian命令有一些關鍵信息 如 select_type? type? possiable_key key? rows? extra?
詳細解析
select_type 表示sql語句查詢類型: 例如,SIMPLE : 表示查詢語句不包含子查詢或union;SUBQUERY:SELECT子查詢語句
type: 表示存儲引擎查詢數據時采用的方式,通過它可以判斷出查詢是全表掃描還是基于索引的部分掃描。
all: 全表查詢
index:表示基于索引的全表掃描,先掃描索引再掃描全表數據。
range:表示使用索引范圍查詢。使用>、>=、<、<=、in等等。
ref:表示使用非唯一索引進行單值查詢。
eq_ref:一般情況下出現在多表join查詢,表示前面表的每一個記錄,都只能匹配后面表的一行結果。
const:表示使用主鍵或唯一索引做等值查詢,常量查詢。
NULL:表示不用訪問表,速度最快。
possible_key:可能會使用到的索引
key:命中的索引,使用的索引
rows:估算SQL要查詢到結果需要掃描多少行記錄。這個值越小越好
extra:查詢時的擴展信息
Using where :表示查詢需要通過索引回表查詢數據。
Using index:表示查詢需要通過索引,索引就可以滿足所需數據。
Using filesort:表示查詢出來的結果需要額外排序,數據量小在內存,大的話在磁盤,因此有Using filesort建議優化。
Using temprorary:查詢使用到了臨時表,一般出現于去重、分組等操作。
16.什么是回表查詢(重點)?
回表查詢:InnoDB索引有聚簇索引和輔助索引。聚簇索引的葉子節點存儲行記錄,InnoDB必須要有,且只有一個。
輔助索引的葉子節點存儲的是主鍵值和索引字段值,通過輔助索引無法直接定位行記錄,通常情況下,需要掃碼兩遍索引樹。
先通過輔助索引定位主鍵值,然后再通過聚簇索引定位行記錄,這就叫做回表查詢,它的性能比掃一遍索引樹低。
總結:通過索引查詢主鍵值,然后再去聚簇索引查詢記錄信息
17.Mysql慢查詢實戰+SQL優化,慢查詢日志分析?如何查看慢查詢日志?如何優化慢查詢日志
注意點:切記在生產環境上開啟慢查詢,會影響數據庫性能 variables slow_query_log
1.首先查看慢查詢是否開啟: SHOW VARIABLES LIKE 'slow_query_log%'
2.開啟慢查詢并設置慢查詢: 如果為OFF,則通過命令 SET global slow_query_log = ON 開啟 并設置 long_query_time = 10 指定慢查詢的閾值
3.查看慢查詢sql文件:MySQL 提供了一個慢查詢日志分析工具mysqldumpslow,可以通過該工具分析慢查詢日志內容,主要看執行時長,鎖表時間和sql語句等關鍵字段
4.通過explian命令分析慢查詢sql
5.進行sql語句優化,并監控性能
?
18.事務并發控制的實現方案有哪些? 說下 MVCC 以及 mysql的鎖機制? ? ****
事務并發會導致更新丟失,臟讀,不可重復讀,幻讀問題,為了避免上述問題,我們可以通過以下方案解決:
1.通過排隊的方法:就是完全順序執行所有事務的數據庫操作,不需要加鎖,簡單的說就是全局排隊;
2.排他鎖:如果事務之間涉及到相同的數據項時,會使用排他鎖,先進入的事務獨占數據項以后,其他事務被阻塞,等待前面的事務釋放鎖。
3.讀寫鎖:讀寫鎖區分讀操作和寫操作,讓讀和讀之間不加鎖,但是讀寫或寫讀或寫寫需要加鎖
4.通過MVCC機制實現讀讀,讀寫,寫讀不加鎖,通過讀取副本(實現讀寫不加鎖操作),但是寫寫仍然需要加鎖;
所謂的MVCC是什么呢?多版本控制,基于 Copy on Write機制實現(先寫日志再刷入磁盤)
在事務A開始寫操作的時候會copy一個記錄的副本(記錄在undo.log日志中),其他事務B讀操作會讀取這個記錄副本,因此不會影響其他事務對此記錄的讀取,實現寫和讀并行。
MVCC實現原理:
MVCC的實現原理主要依賴于記錄中的三個隱藏字段,undolog, read view來實現的。
在 MVCC 并發控制中,讀操作可以分為兩類: 快照讀(Snapshot Read)與當前讀 (Current Read)。
快照讀:讀取的是記錄的快照版本(有可能是歷史版本),不用加鎖。(select)
當前讀:讀取的是記錄的最新版本,并且當前讀返回的記錄,都會加鎖,保證其他事務不會再并發修改這條記錄。(select... for update 或lock in share mode,insert/delete/update)
每個事務都會包含三個隱藏字段:隱含ID、事務號Tid和回滾指針Roll_PT,每次寫更新都會在事務ID上增加1
19.鎖可以有哪些分類?鎖機制是如何實現得?樂觀鎖,悲觀鎖是如何實現的?
在 MySQL中鎖有很多不同的分類。根據不通的劃分規則:
根據鎖的粒度可分為表級鎖、行級鎖和頁級鎖。
表級鎖:每次操作鎖住整張表。鎖定粒度大,發生鎖沖突的概率最高,并發度最低。應用在MyISAM、InnoDB、BDB 等存儲引擎中。
行級鎖:每次操作鎖住一行數據。鎖定粒度最小,發生鎖沖突的概率最低,并發度最高。應用在InnoDB 存儲引擎中。
頁級鎖:每次鎖定相鄰的一組記錄,鎖定粒度界于表鎖和行鎖之間,開銷和加鎖時間界于表鎖和行鎖之間,并發度一般。應用在BDB 存儲引擎中。
根據鎖的操作行為分為讀鎖和寫鎖,從操作的類型可分為讀鎖和寫鎖。
讀鎖(S鎖):共享鎖,針對同一份數據,多個讀操作可以同時進行而不會互相影響。
寫鎖(X鎖):排他鎖,當前寫操作沒有完成前,它會阻斷其他寫鎖和讀鎖。
根據鎖的的實現方式分為悲觀鎖和樂觀鎖從操作的性能可分為樂觀鎖和悲觀鎖。
樂觀鎖:一般的實現方式是對記錄數據版本進行比對,在數據更新提交的時候才會進行沖突檢測,如果發現沖突了,則提示錯誤信息。
悲觀鎖:在對一條數據修改的時候,為了避免同時被其他人修改,在修改數據之前先鎖定,再修改的控制方式。共享鎖和排他鎖是悲觀鎖的不同實現,但都屬于悲觀范疇。
從廣義上來講,前面提到的行鎖、表鎖、讀鎖、寫鎖、共享鎖、排他鎖等,這些都屬于悲觀鎖范疇。
?
20.什么情況下會產生死鎖?如何避免死鎖? 如何解決死鎖?? https://blog.csdn.net/qq_34107571/article/details/78001309
死鎖的標準定義:是指兩個或兩個以上的進程在執行過程中,因爭奪資源而造成的一種互相等待的現象,若無外力作用,它們都將無法推進下去。
死鎖產生必須要具備以下四個條件:
互斥條件:某資源在一段時間內只由一個進程占用
相互等待條件:指在發生死鎖時,必然存在一個進程和資源的環形鏈,比如 A進程正在等待一個B進程占用的資源;B進程又正在等待A進程占用的資源
不可剝奪條件:指進程已經獲取的資源,在未使用完之前,只能由自身釋放
請求和保持:指進程已經保持至少一個資源,但又提出了新的資源請求,而該資源已被其它進程占有,此時請求進程阻塞,但又對自己已獲得的其它資源保持不放
以下幾種情況下會出現死鎖:
情況一:
如果在事務中執行了一條沒有索引條件的查詢,引發全表掃描,把行級鎖上升為全表記錄鎖定(等價于表級鎖),多個這樣的事務執行后,就很容易產生死鎖和阻塞
解決方案:
SQL語句中不要使用太復雜的關聯多表的查詢;
使用explain“執行計劃"對SQL語句進行分析,對于有全表掃描和全表鎖定的SQL語句,建立相應的索引進行優化。
情況二:
兩個事務分別想拿到對方持有的鎖,互相等待,于是產生死鎖。
解決方案:
在同一個事務中,盡可能做到一次鎖定所需要的所有資源
按照id對資源排序,然后按順序進行處理
四、 如何預防死鎖
如何避免死鎖?為此我們在開發的過程中需要遵循如下原則:
1.編寫應用程序避免長事務,讓進程持有鎖的時間盡可能短
2.避免并發的執行 修改數據的語句。
3.要求每一個事務一次就將所有要使用到的數據全部加鎖,否則就不允許執行。
4.預先規定一個加鎖順序,所有的事務都必須按照這個順序對數據執行封鎖。如不同的過程在事務內部對對象的更新執行順序應盡量保證一致。
5.使用盡可能低的隔離級別。
6.數據存儲空間離散法。該方法是指采用各種手段,將邏輯上在一個表中的數據分散的若干離散的空間上去,以便改善對表的訪問性能。主要通過將大表按行或者列分解為若干小表,或者按照不同的用戶群兩種方法實現。
??
怎么查詢死鎖: https://www.cnblogs.com/jpfss/p/11491526.html
1、查詢進程id,然后kill id? show processlist? ?kill id?
2、查詢是否鎖表 : show OPEN TABLES where In_use > 0;
3、在5.5中,information_schema 庫中增加了三個關于鎖的表(innoDB引擎):
innodb_trx? ? ? ? ?## 當前運行的所有事務
innodb_locks? ? ? ?## 當前出現的鎖
innodb_lock_waits? ## 鎖等待的對應關系
4、通過show engine innodb status\G命令查看近期死鎖日志信息。查看死鎖日志
21.Mysql 主從之間是怎么同步數據的? 主從復制的用途?如何解決主從復制延遲問題?
主從復制主要應用于:
故障切換:用于故障切換(高可用)
數據備份:避免由于數據宕機影響業務(高可用)
讀寫分離:提供查詢服務(讀擴展)
主從復制通過以下三個步驟完成數據同步:
1.主庫將數據庫的變更操作記錄到Binlog日志文件中
2.從庫讀取主庫中的Binlog日志文件信息寫入到從庫的Relay Log中繼日志中
3.從庫讀取中繼日志信息在從庫中進行Replay,更新從庫數據信息
在上述三個過程中,涉及了Master的BinlogDump Thread和? ?Slave的I/O Thread、SQL Thread,它們的作用如下:
Master服務器對數據庫更改操作記錄在Binlog中,BinlogDump Thread接到寫入請求后,讀取Binlog信息推送給Slave的I/O Thread。
Slave的I/O Thread將讀取到的Binlog信息寫入到本地Relay Log中。
Slave的SQL Thread檢測到Relay Log的變更請求,解析relay log中內容在從庫上執行
22.mysql主從復制存在的問題,如何解決主從復制延遲問題?
mysql主從復制存在的問題:
主庫宕機后,數據可能丟失
從庫只有一個SQL Thread,主庫寫壓力大,復制很可能延時
解決方法:
半同步復制---解決數據丟失的問題
半同步復制: 為了提升數據安全,MySQL讓Master在某一個時間點等待Slave節點的 ACK(Acknowledgecharacter)消息,接收到ACK消息后才進行事務提交。
并行復制----解決從庫復制延遲的問題
并行復制在從庫中有兩個線程IO Thread和SQL Thread,都是單線程模式工作,因此有了延遲問題,我們可以采用多線程機制來加強,減少從庫復制延遲。(IO Thread多線程意義不大,主要指的是SQL Thread多線程)
在MySQL的5.6、5.7、8.0版本上,都是基于上述SQL Thread多線程思想,不斷優化,減少復制延遲。
23.數據庫如何實現分庫分表?什么是分庫分表?分庫分表的策略是什么?
存在的問題:
業務越來越大,單表數據超出了數據庫支持的容量
Tps過大,十幾萬并發量,傳統的架構(一主多從),主庫容量肯定無法滿足這么高的Tps
拆分的模式:水平拆分 垂直拆分 都屬于物理空間的拆分。
垂直拆分:由于表數量多導致的單個庫大。將表拆分到多個庫中。
水平拆分:由于表記錄多導致的單個庫大。將表記錄拆分到多個表中。
??
面試八股文: https://blog.csdn.net/o9109003234/article/details/121026489
?
Mysql的集群架構
Mysql的集群方案有以下三種:? 主從架構? 雙主架構? 分庫分表
主從架構(如何實現,如何保證數據一致性)
主從模式:是指數據可以從一個主數據庫復制到一個或多個從節數據庫。MySQL 默認采用異步復制方式
主從架構的作用:
讀寫分離,提供查詢服務(讀擴展)
數據備份,避免影響業務(高可用)
實時災備,用于故障切換(高可用)
主從復制整體分為以下三個步驟:
1.主庫將數據庫的變更操作記錄到Binlog日志文件中
2.從庫讀取主庫中的Binlog日志文件信息寫入到從庫的 Relay Log中繼日志中
3.從庫讀取中繼日志信息在從庫中進行Replay(數據恢復),更新從庫數據信息
在上述三個過程中,涉及了三個線程, Master的BinlogDump Thread和Slave的I/O Thread、SQL Thread,它們的作用如下:
BinlogDump Thread(binlog日志推送線程) : 當Master服務器對數據庫更改操作記錄在Binlog中,該線程接到寫入請求后,讀取Binlog信息推送給Slave的I/O Thread。
I/O Thread(從庫IO線程): 該線程讀取到的Binlog信息寫入到本地Relay Log中。
SQL Thread(從庫sql線程): Slave的SQL Thread檢測到Relay Log的變更請求,解析relay log中內容在從庫上執行。
上述過程都是異步操作,俗稱異步復制,存在數據延遲現象。
主從復制存在的問題(也就是異步操作導致的問題):
主庫宕機,可能導致數據丟失問題:
數據延遲問題:從庫只有一個SQL Thread,主庫寫壓力大,復制很可能延時
如何主從復制存在的上述問題:
半同步復制---解決數據丟失的問題
為了提升數據安全,MySQL可以讓Master在某一個時間點等待Slave節點的 ACK消息,接收到ACK消息后才進行事務提交,這也是半同步復制的基礎,
MySQL從5.5版本開始引入了半同步復制機制來降低數據丟失的概率。
介紹半同步復制之前先快速過一下 MySQL 事務寫入碰到主從復制時的完整過程,主庫事務寫入分為 4個步驟:
InnoDB Redo File Write (Prepare Write)? 兩階段提交中的prepare階段
Binlog File Flush & Sync to Binlog File 兩階段提交中的寫入binlog階段
InnoDB Redo File Commit(Commit Write) 兩階段提交中的寫入commit階段
Send Binlog to Slave: 主庫通過binlogDump thread 推送binlog日志到從庫
當Master不需要關注Slave是否接受到Binlog Event時,即為傳統的主從復制。
當Master需要在第三步等待Slave返回ACK時,即為 after-commit,半同步復制(MySQL 5.5引入)。
當Master需要在第二步等待 Slave 返回 ACK 時,即為 after-sync,增強半同步(MySQL 5.7引入)
半同步復制:主庫等待從庫寫入 relay log 并返回 ACK 后才進行Engine Commit。
并行復制----解決從庫復制延遲的問題,通過增加 SQL Thread線程數實現?
復制延遲的根本原因:
在從庫中有兩個線程IO Thread和SQL Thread,都是單線程模式工作,因此有了延遲問題;
我們可以采用多線程機制來加強,減少從庫復制延遲。(IO Thread多線程意義不大,主要指的是SQL Thread多線程:解析redaylog中的內容,并在從庫上執行)
在MySQL的5.6、5.7、8.0版本上,都是基于上述SQL Thread多線程思想,不斷優化,減少復制延遲。
上面說主從復制的根本目的是為了實現 讀寫分離:
在讀寫分離的應用場景下, 可以在從庫追加多個索引來優化查詢,主庫這些索引可以不加,用于提升寫效率。
為了避免主從同步出現的延遲性問題出現,我們也可以使用以下方案:
寫后立刻讀
在寫入數據庫后,某個時間段內讀操作就去主庫,之后讀操作訪問從庫。
二次查詢
先去從庫讀取數據,找不到時就去主庫進行數據讀取。該操作容易將讀壓力返還給主庫,
根據業務特殊處理
根據業務特點和重要程度進行調整,比如重要的,實時性要求高的業務數據讀寫可以放在主庫。對于次要的業務,實時性要求不高可以進行讀寫分離,查詢時去從庫查詢。
?
查看全部 -
call
查看全部 -
j
查看全部 -
1
查看全部 -
ccc
查看全部 -
并發
查看全部 -
現場是CPU調度的基本單位也是執行的基本單位查看全部
-
一個進程可以有很多個線程查看全部
舉報