mysql面試題_MySQL面試題全在這裡了（下）

50、MySQL 有關權限的表都有哪幾個？

MySQL 伺服器通過權限表來控制使用者對資料庫的通路，權限表存放在 MySQL 資料庫裡，由 MySQL_install_db 腳本初始化。這些權限表分别 user，db，table_priv，columns_priv 和 host。

51、列的字元串類型可以是什麼？

字元串類型是：

SET

BLOB

ENUM

CHAR

TEXT

52、MySQL 資料庫作釋出系統的存儲，一天五萬條以上的增量，預計運維三年,怎麼優化？

設計良好的資料庫結構，允許部分資料備援，盡量避免 join 查詢，提高效率。

選擇合适的表字段資料類型和存儲引擎，适當的添加索引。

MySQL 庫主從讀寫分離。

找規律分表，減少單表中的資料量提高查詢速度。

添加緩存機制，比如 memcached，apc 等。

不經常改動的頁面，生成靜态頁面。

書寫高效率的 SQL。比如 SELECT * FROM TABEL 改為 SELECT ?eld_1,?eld_2, ?eld_3 FROM TABLE.

53、鎖的優化政策

讀寫分離

分段加鎖

減少鎖持有的時間

多個線程盡量以相同的順序去擷取資源不能将鎖的粒度過于細化，不然可能會出現線程的加鎖和釋放次數過多，反而效率不如一次加一把大鎖。

54、索引的底層實作原理和優化

B+樹，經過優化的 B+樹

主要是在所有的葉子結點中增加了指向下一個葉子節點的指針，是以 InnoDB 建議為大部分表使用預設自增的主鍵作為主索引。

55、什麼情況下設定了索引但無法使用

以“%”開頭的 LIKE 語句，模糊比對

OR 語句前後沒有同時使用索引

資料類型出現隐式轉化(如 varchar 不加單引号的話可能會自動轉換為 int 型)

56、實踐中如何優化 MySQL

最好是按照以下順序優化：

SQL 語句及索引的優化

資料庫表結構的優化

系統配置的優化

硬體的優化

詳細可以檢視 阿裡 P8 架構師談：MySQL 慢查詢優化、索引優化、以及表等優化總結

57、優化資料庫的方法

選取最适用的字段屬性，盡可能減少定義字段寬度，盡量把字段設定 NOTNULL，例如’省份’、’性别’最好适用 ENUM

使用連接配接( JOIN)來代替子查詢

适用聯合(UNION)來代替手動建立的臨時表

事務處理

鎖定表、優化事務處理

适用外鍵，優化鎖定表

建立索引

優化查詢語句

58、簡單描述 MySQL 中，索引，主鍵，唯一索引，聯合索引的差別，對資料庫的性能有什麼影響(從讀寫兩方面)

索引是一種特殊的檔案(InnoDB 資料表上的索引是表空間的一個組成部分)，它們包含着對資料表裡所有記錄的引用指針。普通索引(由關鍵字 KEY 或 INDEX 定義的索引)的唯一任務是加快對資料的通路速度。

普通索引允許被索引的資料列包含重複的值。如果能确定某個資料列将隻包含彼此各不相同的值，在為這個資料列建立索引的時候就應該用關鍵字 UNIQUE 把它定義為一個唯一索引。也就是說，唯一索引可以保證資料記錄的唯一性。

主鍵，是一種特殊的唯一索引，在一張表中隻能定義一個主鍵索引，主鍵用于唯一辨別一條記錄，使用關鍵字 PRIMARY KEY 來建立。索引可以覆寫多個資料列，如像 INDEX(columnA, columnB)索引，這就是聯合索引。

索引可以極大的提高資料的查詢速度，但是會降低插入、删除、更新表的速度，因為在執行這些寫操作時，還要操作索引檔案

59、資料庫中的事務是什麼?

事務(transaction)是作為一個單元的一組有序的資料庫操作。如果組中的所有操作都成功，則認為事務成功，即使隻有一個操作失敗， 事務也不成功。如果所有操作完成，事務則送出，其修改将作用于所有其他資料庫程序。如果一個操作失敗，則事務将復原，該事務所有操作的影響都将取消。

事務特性：

原子性：即不可分割性，事務要麼全部被執行，要麼就全部不被執行。

一緻性或可串性。事務的執行使得資料庫從一種正确狀态轉換成另一種正确狀态

隔離性。在事務正确送出之前，不允許把該事務對資料的任何改變提供給任何其他事物

持久性。事務正确送出後，其結果将永久儲存在資料庫中，即使在事務送出後有了其他故障，事務的處理結果也會得到儲存。

或者這樣了解：

事務就是被綁定在一起作為一個邏輯工作單元的 SQL 語句分組，如果任何一個語句操作失敗那麼整個操作就被失敗，以後操作就會復原到操作前狀态，或者是上有個節點。為了確定要麼執行，要麼不執行，就可以使用事務。要将有組語句作為事務考慮，就需要通過 ACID 測試，即原子性，一緻性，隔離性和持久性。

60、SQL 注入漏洞産生的原因？如何防止？

SQL 注入産生的原因：程式開發過程中不注意規範書寫 sql 語句和對特殊字元進行過濾，導緻用戶端可以通過全局變量 POST 和 GET 送出一些 sql 語句正常執行。

防止 SQL 注入的方式：

開啟配置檔案中的 magic_quotes_gpc 和 magic_quotes_runtime 設定 執行 sql 語句時使用 addslashes 進行 sql 語句轉換

Sql 語句書寫盡量不要省略雙引号和單引号。

過濾掉 sql 語句中的一些關鍵詞：update、insert、delete、select、 * 。

提高資料庫表和字段的命名技巧，對一些重要的字段根據程式的特點命名，取不易被猜到的。

61、為表中得字段選擇合适得資料類型

字段類型優先級: 整形>date,time>enum,char>varchar>blob,text優先考慮數字類型，其次是日期或者二進制類型，最後是字元串類型，同級别得資料類型，應該優先選擇占用空間小的資料類型

62、存儲時期

Datatime:以 YYYY-MM-DD HH:MM:SS 格式存儲時期時間，精确到秒，占用 8 個位元組得存儲空間，datatime 類型與時區無關

Timestamp:以時間戳格式存儲，占用 4 個位元組，範圍小 1970-1-1 到 2038-1-19，顯示依賴于所指定得時區，預設在第一個列行的資料修改時可以自動得修改timestamp 列得值

Date:(生日)占用得位元組數比使用字元串.datatime.int 儲存要少，使用 date 隻需要 3 個位元組，存儲日期月份，還可以利用日期時間函數進行日期間得計算

Time:存儲時間部分得資料

注意:不要使用字元串類型來存儲日期時間資料(通常比字元串占用得儲存空間小，在進行查找過濾可以利用日期得函數)

使用 int 存儲日期時間不如使用 timestamp 類型

63、對于關系型資料庫而言，索引是相當重要的概念，請回答有關索引的幾個問題

索引的目的是什麼？

快速通路資料表中的特定資訊，提高檢索速度

建立唯一性索引，保證資料庫表中每一行資料的唯一性。加速表和表之間的連接配接

使用分組和排序子句進行資料檢索時，可以顯著減少查詢中分組和排序的時間

索引對資料庫系統的負面影響是什麼？ 負面影響：

建立索引和維護索引需要耗費時間，這個時間随着資料量的增加而增加；索引需要占用實體空間，不光是表需要占用資料空間，每個索引也需要占用實體空間；當對表進行增、删、改、的時候索引也要動态維護，這樣就降低了資料的維護速度。

為資料表建立索引的原則有哪些？

在最頻繁使用的、用以縮小查詢範圍的字段上建立索引。

在頻繁使用的、需要排序的字段上建立索引

什麼情況下不宜建立索引？

對于查詢中很少涉及的列或者重複值比較多的列，不宜建立索引。對于一些特殊的資料類型，不宜建立索引，比如文本字段(text)等

64、解釋 MySQL 外連接配接、内連接配接與自連接配接的差別

先說什麼是交叉連接配接: 交叉連接配接又叫笛卡爾積，它是指不使用任何條件，直接将一個表的所有記錄和另一個表中的所有記錄一一比對。

内連接配接 則是隻有條件的交叉連接配接，根據某個條件篩選出符合條件的記錄，不符合條件的記錄不會出現在結果集中，即内連接配接隻連接配接比對的行。

外連接配接 其結果集中不僅包含符合連接配接條件的行，而且還會包括左表、右表或兩個表中的所有資料行，這三種情況依次稱之為左外連接配接，右外連接配接，和全外連接配接。

左外連接配接，也稱左連接配接，左表為主表，左表中的所有記錄都會出現在結果集中，對于那些在右表中并沒有比對的記錄，仍然要顯示，右邊對應的那些字段值以NULL 來填充。右外連接配接，也稱右連接配接，右表為主表，右表中的所有記錄都會出現在結果集中。左連接配接和右連接配接可以互換，MySQL 目前還不支援全外連接配接。

65、Myql 中的事務復原機制概述

事務是使用者定義的一個資料庫操作序列，這些操作要麼全做要麼全不做，是一個不可分割的工作機關，事務復原是指将該事務已經完成的對資料庫的更新操作撤銷。

要同時修改資料庫中兩個不同表時，如果它們不是一個事務的話，當第一個表修改完，可能第二個表修改過程中出現了異常而沒能修改，此時就隻有第二個表依舊是未修改之前的狀态，而第一個表已經被修改完畢。而當你把它們設定為一個事務的時候，當第一個表修改完，第二表修改出現異常而沒能修改，第一個表和第二個表都要回到未修改的狀态，這就是所謂的事務復原

66、SQL 語言包括哪幾部分？每部分都有哪些操作關鍵

SQL 語言包括資料定義(DDL)、資料操縱(DML),資料控制(DCL)和資料查詢(DQL)四個部分。資料定義：

Create Table,Alter Table,Drop Table, Craete/Drop Index 等

資料操縱：Select ,insert,update,delete,

資料控制：grant,revoke 資料查詢：select

67、完整性限制包括哪些？

資料完整性(Data Integrity)是指資料的精确(Accuracy)和可靠性(Reliability)。分為以下四類：

實體完整性：規定表的每一行在表中是惟一的實體。

域完整性：是指表中的列必須滿足某種特定的資料類型限制，其中限制又包括取值範圍、精度等規定。

參照完整性：是指兩個表的主關鍵字和外關鍵字的資料應一緻，保證了表之間的資料的一緻性，防止了資料丢失或無意義的資料在資料庫中擴散。

使用者定義的完整性：不同的關系資料庫系統根據其應用環境的不同，往往還需要一些特殊的限制條件。使用者定義的完整性即是針對某個特定關系資料庫的限制條件，它反映某一具體應用必須滿足的語義要求。與表有關的限制：包括列限制(NOT NULL(非空限制))和表限制(PRIMARY KEY、foreign key、check、UNIQUE) 。

68、什麼是鎖？

答：資料庫是一個多使用者使用的共享資源。當多個使用者并發地存取資料時，在資料庫中就會産生多個事務同時存取同一資料的情況。若對并發操作不加控制就可能會讀取和存儲不正确的資料，破壞資料庫的一緻性。

加鎖是實作資料庫并發控制的一個非常重要的技術。當事務在對某個資料對象進行操作前，先向系統送出請求，對其加鎖。加鎖後事務就對該資料對象有了一定的控制，在該事務釋放鎖之前，其他的事務不能對此資料對象進行更新操作。

基本鎖類型：鎖包括行級鎖和表級鎖

69、什麼叫視圖？遊标是什麼？

視圖是一種虛拟的表，具有和實體表相同的功能。可以對視圖進行增，改，查，操作，視圖通常是有一個表或者多個表的行或列的子集。對視圖的修改不影響基本表。它使得我們擷取資料更容易，相比多表查詢。

遊标：是對查詢出來的結果集作為一個單元來有效的處理。遊标可以定在該單元中的特定行，從結果集的目前行檢索一行或多行。可以對結果集目前行做修改。一般不使用遊标，但是需要逐條處理資料的時候，遊标顯得十分重要。

70、什麼是存儲過程？用什麼來調用？

答：存儲過程是一個預編譯的 SQL 語句，優點是允許子產品化的設計，就是說隻需建立一次，以後在該程式中就可以調用多次。如果某次操作需要執行多次 SQL，使用存儲過程比單純 SQL 語句執行要快。可以用一個指令對象來調用存儲過程。

71、如何通俗地了解三個範式？

第一範式：1NF 是對屬性的原子性限制，要求屬性具有原子性，不可再分解；

第二範式：2NF 是對記錄的惟一性限制，要求記錄有惟一辨別，即實體的惟一性；

第三範式：3NF 是對字段備援性的限制，即任何字段不能由其他字段派生出來，它要求字段沒有備援。

範式化設計優缺點:

優點:

可以盡量得減少資料備援，使得更新快，體積小

缺點:對于查詢需要多個表進行關聯，減少寫得效率增加讀得效率，更難進行索引

優化

反範式化:

優點:可以減少表得關聯，可以更好得進行索引優化

缺點:資料備援以及資料異常，資料得修改需要更多的成本

72、什麼是基本表？什麼是視圖？

答：基本表是本身獨立存在的表，在 SQL 中一個關系就對應一個表。 視圖是從一個或幾個基本表導出的表。視圖本身不獨立存儲在資料庫中，是一個虛表

73、試述視圖的優點？

視圖能夠簡化使用者的操作

視圖使使用者能以多種角度看待同一資料；

視圖為資料庫提供了一定程度的邏輯獨立性；

視圖能夠對機密資料提供安全保護

74、NULL 是什麼意思

NULL 這個值表示 UNKNOWN(未知):它不表示“”(空字元串)。對 NULL 這個值的任何比較都會生産一個 NULL 值。您不能把任何值與一個NULL 值進行比較，并在邏輯上希望獲得一個答案。使用 IS NULL 來進行 NULL 判斷

75、主鍵、外鍵和索引的差別？

主鍵、外鍵和索引的差別

定義 ：

主鍵–唯一辨別一條記錄，不能有重複的，不允許為空

外鍵–表的外鍵是另一表的主鍵, 外鍵可以有重複的, 可以是空值索引–該字段沒有重複值，但可以有一個空值

作用：

主鍵–用來保證資料完整性

外鍵–用來和其他表建立聯系用的索引–是提高查詢排序的速度

個數：

主鍵–主鍵隻能有一個

外鍵–一個表可以有多個外鍵

索引–一個表可以有多個唯一索引

76、你可以用什麼來確定表格裡的字段隻接受特定範圍裡的值?

Check 限制，它在資料庫表格裡被定義，用來限制輸入該列的值。觸發器也可以被用來限制資料庫表格裡的字段能夠接受的值，但是這種辦法要求觸發器在表格裡被定義，這可能會在某些情況下影響到性能。

77、說說對 SQL 語句優化有哪些方法？(選擇幾條)

Where 子句中：where 表之間的連接配接必須寫在其他 Where 條件之前，那些可以過濾掉最大數量記錄的條件必須寫在 Where 子句的末尾.HAVING 最後。

用 EXISTS 替代 IN、用 NOT EXISTS 替代 NOT IN。

避免在索引列上使用計算

避免在索引列上使用 IS NULL 和 IS NOT NULL

對查詢進行優化，應盡量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引。

應盡量避免在 where 子句中對字段進行 null 值判斷，否則将導緻引擎放棄使用索引而進行全表掃描

應盡量避免在 where 子句中對字段進行表達式操作，這将導緻引擎放棄使用索引而進行全表掃描

78、什麼是樂觀鎖

樂觀鎖認為一個使用者讀資料的時候，别人不會去寫自己所讀的資料；悲觀鎖就剛好相反，覺得自己讀資料庫的時候，别人可能剛好在寫自己剛讀的資料，其實就是持一種比較保守的态度；時間戳就是不加鎖，通過時間戳來控制并發出現的問題。

79、什麼是悲觀鎖

悲觀鎖就是在讀取資料的時候，為了不讓别人修改自己讀取的資料，就會先對自己讀取的資料加鎖，隻有自己把資料讀完了，才允許别人修改那部分資料，或者反過來說，就是自己修改某條資料的時候，不允許别人讀取該資料，隻有等自己的整個事務送出了，才釋放自己加上的鎖，才允許其他使用者通路那部分資料。

80、什麼是時間戳

時間戳就是在資料庫表中單獨加一列時間戳，比如“TimeStamp”， 每次讀出來的時候，把該字段也讀出來，當寫回去的時候，把該字段加1，送出之前 ，跟資料庫的該字段比較一次，如果比資料庫的值大的話，就允許儲存，否則不允許儲存，這種處理方法雖然不使用資料庫系統提供的鎖機制，但是這種方法可以大大提高資料庫處理的并發量，以上悲觀鎖所說的加“鎖”，其實分為幾種鎖，分别是： 排它鎖(寫鎖) 和共享鎖(讀鎖) 。

81、什麼是行級鎖

行級鎖是一種排他鎖，防止其他事務修改此行；在使用以下語句時， Oracle 會自動應用行級鎖：

INSERT、 UPDATE、 DELETE、 SELECT … FOR UPDATE [OF columns] [WAIT n | NOWAIT];

SELECT … FOR UPDATE 語句允許使用者一次鎖定多條記錄進行更新

使用 COMMIT 或 ROLLBACK 語句釋放鎖。

82、什麼是表級鎖

表示對目前操作的整張表加鎖，它實作簡單，資源消耗較少，被大部分 MySQL 引擎支援。最常使用的 MYISAM 與 INNODB 都支援表級鎖定。表級鎖定分為表共享讀鎖(共享鎖)與表獨占寫鎖(排他鎖)。

83、什麼是頁級鎖

頁級鎖是 MySQL 中鎖定粒度介于行級鎖和表級鎖中間的一種鎖。表級鎖速度快，但沖突多，行級沖突少，但速度慢。是以取了折衷的頁級，一次鎖定相鄰的一組記錄。 BDB 支援頁級鎖

擴充連接配接：加粗樣式更多請點選這裡

部落客公衆号程式員小羊 隻發面試相關推文