【面試】【MySQL常見問題總結】【04】

標籤：

【常見面試問題總結目錄>>>】091 資料庫死結概念

　　多數情況下，可以認為如果一個資源被鎖定，它總會在以後某個時間被釋放。而死結發生在當多個進程訪問同一資料庫時，其中每個進程擁有的鎖都是其他進程所需的，由此造成每個進程都無法繼續下去。簡單的說，進程A等待進程B釋放他的資源，B又等待A釋放他的資源，這樣就互相等待就形成死結。
　　雖然進程在運行過程中，可能發生死結，但死結的發生也必須具備一定的條件，死結的發生必須具備以下四個必要條件。
　　1）互斥條件：指進程對所分配到的資源進行排它性使用，即在一段時間內某資源只由一個進程佔用。如果此時還有其它進程請求資源，則要求者只能等待，直至佔有資源的進程用畢釋放。
　　2）請求和保持條件：指進程已經保持至少一個資源，但又提出了新的資源請求，而該資源已被其它進程佔有，此時請求進程阻塞，但又對自己已獲得的其它資源保持不放。
　　3）不剝奪條件：指進程已獲得的資源，在未使用完之前，不能被剝奪，只能在使用完時由自己釋放。
　　4）環路等待條件：指在發生死結時，必然存在一個進程——資源的環形鏈，即進程集合{P0，P1，P2，???，Pn}中的P0正在等待一個P1佔用的資源；P1正在等待P2佔用的資源，……，Pn正在等待已被P0佔用的資源。
　　下列方法有助於最大限度地降低死結：
　　（1）按同一順序訪問對象。
　　（2）避免事務中的使用者互動。
　　（3）保持事務簡短並在一個批處理中。
　　（4）使用低隔離等級。
　　（5）使用綁定串連。

092 資料庫有幾種資料保護方式（AAA）

　　實現資料庫安全性控制的常用方法和技術有：使用者標識和鑒別；存取控制；視圖機制；審計；資料加密；

093 union和union all 的區別以及使用

　　Union因為要進行重複值掃描，所以效率低。如果合并沒有刻意要重複資料刪除行，那麼就使用Union All兩個要聯合的SQL語句 欄位個數必須一樣，而且欄位類型要“相容”（一致）；
　　union和union all的區別是,union會自動壓縮多個結果集合中的重複結果，而union all則將所有的結果全部顯示出來，不管是不是重複。
　　Union：對兩個結果集進行並集操作，不包括重複行，同時進行預設規則的排序；
　　Union All：對兩個結果集進行並集操作，包括重複行，不進行排序；
　　Intersect：對兩個結果集進行交集操作，不包括重複行，同時進行預設規則的排序；
　　Minus：對兩個結果集進行差操作，不包括重複行，同時進行預設規則的排序。
　　可以在最後一個結果集中指定Order by子句改變排序方式。

094 mysql的備份命令是什麼

　　mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql
　　　　備份MySQL資料庫為帶刪除表的格式
　　　　備份MySQL資料庫為帶刪除表的格式，能夠讓該備份覆蓋已有資料庫而不需要手動刪除原有資料庫。
　　mysqldump -–add-drop-table -uusername -ppassword databasename > backupfile.sql
　　　　直接將MySQL資料庫壓縮備份
　　mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz
　　　　備份MySQL資料庫某個(些)表
　　mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sql
　　　　同時備份多個MySQL資料庫
　　mysqldump -hhostname -uusername -ppassword –databases databasename1 databasename2 databasename3 > multibackupfile.sql
　　　　僅僅備份資料庫結構
　　mysqldump –no-data –databases databasename1 databasename2 databasename3 > structurebackupfile.sql
　　　　備份伺服器上所有資料庫
　　mysqldump –all-databases > allbackupfile.sql
　　　　還原MySQL資料庫的命令
　　mysql -hhostname -uusername -ppassword databasename < backupfile.sql
　　　　還原壓縮的MySQL資料庫
　　gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename
　　　　將資料庫轉移到新伺服器
　　mysqldump -uusername -ppassword databasename | mysql –host=... -C databasename

095 在mysql伺服器運行緩慢的情況下輸入什麼命令能緩解伺服器壓力

　　第一步 檢查系統的狀態
　　　　通過作業系統的一些工具檢查系統的狀態，比如CPU、記憶體、交換、磁碟的利用率，根據經驗或與系統正常時的狀態相比對，有時系統資料表面上看起來看空閑，這也可能不是一個正常的狀態，因為cpu可能正等待IO的完成。除此之外，還應觀注那些佔用系統資源(cpu、記憶體)的進程。
　　　　1.1　使用sar來檢查作業系統是否存在IO問題
　　　　1.2　使用vmstat監控記憶體 cpu資源
　　　　1.3　磁碟IO問題，處理方式：做raid10提高效能
　　　　1.4　網路問題，telnet一下MySQL對外開放的連接埠，如果不通的話，看看防火牆是否正確設定了。另外，看看MySQL是不是開啟了skip-networking的選項，如果開啟請關閉。
　　第二步 檢查mysql參數
　　　　2.1　max_connect_errors
　　　　2.2　connect_timeout
　　　　2.3　skip-name-resolve
　　　　2.4　slave-net-timeout=seconds
　　　　2.5　master-connect-retry
　　第三步 檢查mysql 相關狀態值
　　　　3.1　關注串連數
　　　　3.2　關注下系統鎖情況
　　　　3.3　關注慢查詢（slow query）日誌

096 怎麼匯出表結構?

　　1.匯出整個資料庫
　　　　mysqldump -u使用者名稱 -p密碼 資料庫名 > 匯出的檔案名稱
　　　　C:\Users\jack> mysqldump -uroot -pmysql sva_rec > e:\sva_rec.sql
　　2.匯出一個表，包括表結構和資料
　　　　mysqldump -u使用者名稱 -p 密碼 資料庫名 表名> 匯出的檔案名稱
　　　　C:\Users\jack> mysqldump -uroot -pmysql sva_rec date_rec_drv> e:\date_rec_drv.sql
　　3.匯出一個資料庫結構
　　　　C:\Users\jack> mysqldump -uroot -pmysql -d sva_rec > e:\sva_rec.sql
　　4.匯出一個表，只有表結構
　　　　mysqldump -u使用者名稱 -p 密碼 -d資料庫名 表名> 匯出的檔案名稱
　　　　C:\Users\jack> mysqldump -uroot -pmysql -d sva_rec date_rec_drv> e:\date_rec_drv.sql
　　5.匯入資料庫
　　　　常用source 命令
　　　　進入mysql資料庫控制台，
　　　　如mysql -u root -p
　　　　mysql>use 資料庫
　　　　然後使用source命令，後面參數為指令檔(如這裡用到的.sql)
　　　　mysql>source d:wcnc_db.sql

097 正常登入MYSQL後使用什麼命令查看其進程是否正常

　　輸入show processlist;
　　如果有SUPER許可權，則可以看到全部的線程，否則，只能看到自己發起的線程（這是指，當前對應的MySQL帳戶啟動並執行線程）。

098 mysql遠端連線命令

　　一、MySQL 串連本機資料庫，使用者名稱為“root”，密碼“123”（注意：“-p”和“123” 之間不能有空格）
　　　　C:>mysql -h localhost -u root -p123
　　二、MySQL 串連遠端資料庫（192.168.0.201），連接埠“3306”，使用者名稱為“root”，密碼“123”
　　　　C:>mysql -h 192.168.0.201 -P 3306 -u root -p123

099 mysql主從用什麼方式傳輸日誌

　　MySQL 複製基於主伺服器在二進位日誌中跟蹤所有對資料庫的更改(更新、刪除等等)。每個從伺服器從主伺服器接收主伺服器已經記錄到其二進位日誌的儲存的更新，以便從伺服器可以對其資料拷貝執行相同的更新。

100 資料庫的備份方式

　　1、完全備份，這是大多數人常用的方式，它可以備份整個資料庫，包含使用者表、系統資料表、索引、視圖和預存程序等所有資料庫物件。但它需要花費更多的時間和空間，所以，一般推薦一周做一次完全備份。
　　2、交易記錄備份，交易記錄是一個單獨的檔案，它記錄資料庫的改變，備份的時候只需要複製自上次備份以來對資料庫所做的改變，所以只需要很少的時間。為了使資料庫具有魯棒性，推薦每小時甚至更頻繁的備份交易記錄。
　　3、差異備份也叫增量備份。它是只備份資料庫一部分的另一種方法，它不使用交易記錄，相反，它使用整個資料庫的一種新映象。它比最初的完全備份小，因為它只包含自上次完全備份以來所改變的資料庫。它的優點是儲存和恢複速度快。推薦每天做一次差異備份。
　　4、檔案備份，資料庫可以由硬碟上的許多檔案構成。如果這個資料庫非常大，並且一個晚上也不能將它備份完，那麼可以使用檔案備份每晚備份資料庫的一部分。由於一般情況下資料庫不會大到必須使用多個檔案儲存體，所以這種備份不是很常用。

101 查看mysql資料庫是否支援innodb

　　查看mysql的儲存引擎：show plugins;
　　如何在mysql某個表中隨機抽取10條記錄
　　1.通過MYSQL內建的函數來操作，具體SQL代碼如下：
　　　　SELECT * FROM tablename ORDER BY RAND() LIMIT 10
　　2.不要將大量的工作給資料庫去做，這樣會導致資料庫在某一集中並發時間內鎖死並阻塞。建議通過PHP隨機產生一下1-X(總行數)之間的數字，然後將這10個隨機數字作為查詢條件，具體語句如：
　　　　SELECT * FROM tablename where ID in (2,8,4,11,12,9,3,1,33)
　　可能你還要進行重複排除，並且需要在程式中將10個值串聯並串連進入SQL語句中。

102 如何查看串連mysql的目前使用者。

　　show full processlist，在user欄位中查看有哪些使用者

103 寫出mysql怎麼修改密碼?

　　方法一: (適用於管理員或者有全域許可權的使用者重設其它使用者的密碼)
　　　　進入命令列模式
　　　　mysql -u root -p
　　　　mysql>use mysql;
　　　　mysql> UPDATE user SET password=PASSWORD(“new password”) WHERE user=’username’;
　　　　mysql> FLUSH PRIVILEGES;
　　　　mysql> quit;
　　方法二:
　　　　mysql -u root -p
　　　　mysql>use mysql;
　　　　mysql> SET PASSWORD FOR username=PASSWORD(‘new password’);
　　　　mysql> QUIT
　　方法三:
　　　　mysqladmin -u root “old password” “new password”
　　註：new password請輸入你想要設定的密碼。

104 MySQL怎麼修複損壞的表?

　　有兩種方法，一種方法使用mysql的check table和repair table 的sql語句，另一種方法是使用MySQL提供的多個myisamchk, isamchk資料檢測恢複工具。

105 簡單敘述一下MYSQL的最佳化（重點）

　　1.資料庫的設計：盡量把資料庫設計的更小的占磁碟空間.
　　　　1) 儘可能使用更小的整數類型.(mediumint就比int更合適).
　　　　2) 儘可能的定義欄位為not null,除非這個欄位需要null.
　　　　3) 如果沒有用到變長欄位的話比如varchar,那就採用固定大小的紀錄格式比如char.
　　　　4) 表的主索引應該儘可能的短.這樣的話每條紀錄都有名字標誌且更高效.
　　　　5) 只建立確實需要的索引。索引有利於檢索記錄，但是不利於快速儲存記錄。如果總是要在表的組合欄位上做搜尋，那麼就在這些欄位上建立索引。索引的第一部分必須是最常使用的欄位.如果總是需要用到很多欄位，首先就應該多複製這些欄位，使索引更好的壓縮。
　　　　6) 所有資料都得在儲存到資料庫前進行處理。
　　　　7) 所有欄位都得有預設值。
　　　　8) 在某些情況下,把一個頻繁掃描的表分成兩個速度會快好多。在對動態格式表掃描以取得相關記錄時，它可能使用更小的靜態格式表的情況下更是如此。
　　2.系統的用途
　　　　1) 盡量使用長串連.
　　　　2) explain複雜的SQL語句。
　　　　3) 如果兩個關聯表要做比較話，做比較的欄位必須類型和長度都一致.
　　　　4) LIMIT語句盡量要跟order by或者 distinct.這樣可以避免做一次full table scan.
　　　　5) 如果想要清空表的所有紀錄,建議用truncate table tablename而不是delete from tablename.
　　　　6) 能使用STORE PROCEDURE 或者 USER FUNCTION的時候.
　　　　7) 在一條insert語句中採用多重紀錄插入格式.而且使用load data infile來匯入大量資料，這比單純的insert快好多.
　　　　8) 經常OPTIMIZE TABLE 來整理片段.
　　　　9) 還有就是date 類型的資料如果頻繁要做比較的話盡量儲存在unsigned int 類型比較快。
　　3.系統的瓶頸
　　　　1) 磁碟搜尋。並行搜尋,把資料分開存放到多個磁碟中，這樣能加快搜尋時間.
　　　　2) 磁碟讀寫(IO)。可以從多個媒介中並行的讀取資料。
　　　　3) CPU周期。資料存放在主記憶體中.這樣就得增加CPU的個數來處理這些資料。
　　　　4) 記憶體頻寬。當CPU要將更多的資料存放到CPU的緩衝中來的話,記憶體的頻寬就成了瓶頸.

106 如何確定有哪些儲存引擎可用？

　　mysql> show engines; 顯示了可用的資料庫引擎的全部名單以及在當前的資料庫伺服器中是否支援這些引擎。

107 MYSQL資料庫設計資料類型選擇需要注意哪些地方?（重點）

　　VARCHAR和CHAR類型，varchar是變長的，需要額外的1-2個位元組儲存，能節約空間，可能會對效能有協助。但由於是變長，可能發生片段，如更新資料；
使用ENUM代替字串類型，資料實際儲存為整型。
　　字串類型
　　要儘可能地避免使用字串來做標識符，因為它們佔用了很多空間並且通常比整數類型要慢。特別注意不要在MYISAM表上使用字串標識符。MYISAM預設情況下為字串使用了壓縮索引（Packed Index），這使尋找更為緩慢。據測試，使用了壓縮索引的MYISAM表效能要慢6倍。
　　還要特別注意完全‘隨機’的字串，例如由MD5（）、SHA1（）、UUID（）產生的。它們產生的每一個新值都會被任意地儲存在很大的空間範圍內，這會減慢INSERT及一些SELECT查詢。
　　　　1）它們會減慢INSERT查詢，因為插入的值會被隨機地放入索引中。這會導致分頁、隨機磁碟訪問及聚集儲存引擎上的叢集索引片段。
　　　　2)它們會減慢SELECT查詢，因為邏輯上相鄰的行會分布在磁碟和記憶體中的各個地方。
　　　　3）隨機值導致緩衝對所有類型的查詢效能都很差，因為它們會使緩衝賴以工作的訪問局部性失效。如果整個資料集都變得同樣“熱”的時候，那麼把特定部分的資料緩衝到記憶體中就沒有任何的優勢了。並且如果工作集不能被裝入記憶體中，緩衝就會進行很多刷寫的工作，並且會導致很多緩衝未命中。
　　如果儲存UUID值，就應該移除其中的虛線，更好的辦法是使用UHEX（）把UUID值轉化為16位元組的數字，並把它儲存在BINARY（16）列中。

108 mysql、oracle預設連接埠號碼

　　3306、1521

109 innodb的事務與日誌的實現方式。

　　（1）有多少種日誌
　　　　錯誤記錄檔：記錄出錯資訊，也記錄一些警告資訊或者正確的資訊
　　　　慢查詢日誌：設定一個閾值，將已耗用時間超過該值的所有SQL語句都記錄到慢查詢的記錄檔中。
　　　　二進位日誌：記錄對資料庫執行更改的所有操作
　　　　查詢日誌：記錄所有對資料庫請求的資訊，不論這些請求是否得到了正確的執行。
　　（2）日誌的存放形式
　　（3）事務是如何通過日誌來實現的，說得越深入越好。
　　在Innodb儲存引擎中，交易記錄是通過redo和innodb的儲存引擎日誌緩衝（Innodb log buffer）來實現 的，當開始一個事務的時候，會記錄該事務的lsn(log sequence number)號; 當事務執行時，會往InnoDB存 儲引擎的日誌的日誌緩衝裡面插入交易記錄；當事務提交時，必須將儲存引擎的日誌緩衝寫入磁碟（通過 innodb_flush_log_at_trx_commit來控制），也就是寫資料前，需要先寫日誌。這種方式稱為“預寫記錄檔方 式”， innodb通過此方式來保證事務的完整性。也就意味著磁碟上儲存的資料頁和記憶體緩衝池上面的頁是不同步 的，是先寫入redo log，然後寫入data file，因此是一種非同步方式。
　　隔離性： 通過鎖實現
　　原子性、一致性和持久性是通過redo和undo來完成的。

【面試】【MySQL常見問題總結】【04】