資料庫基礎知識

為什麼要使用資料庫

資料儲存在記憶體

優點：存取速度快

缺點：資料不能永久儲存

資料儲存在檔案

優點：資料永久儲存

缺點：1）速度比記憶體操作慢，頻繁的IO操作。2）查詢資料不友善

資料儲存在資料庫

1）資料永久儲存

2）使用SQL語句，查詢友善效率高。

3）管理資料友善

什麼是SQL？

結構化查詢語言(Structured Query Language)簡稱SQL，是一種資料庫查詢語言。

作用：用于存取資料、查詢、更新和管理關系資料庫系統。

什麼是MySQL?

MySQL是一個關系型資料庫管理系統，由瑞典MySQL AB 公司開發，屬于 Oracle 旗下産品。MySQL 是最流行的關系型資料庫管理系統之一，在 WEB 應用方面，MySQL是最好的 RDBMS (Relational Database Management System，關系資料庫管理系統) 應用軟體之一。在Java企業級開發中非常常用，因為 MySQL 是開源免費的，并且友善擴充。

資料庫三大範式是什麼

第一範式：每個列都不可以再拆分。

第二範式：在第一範式的基礎上，非主鍵列完全依賴于主鍵，而不能是依賴于主鍵的一部分。

第三範式：在第二範式的基礎上，非主鍵列隻依賴于主鍵，不依賴于其他非主鍵。

在設計資料庫結構的時候，要盡量遵守三範式，如果不遵守，必須有足夠的理由。比如性能。事實上我們經常會為了性能而妥協資料庫的設計。

mysql有關權限的表都有哪幾個

MySQL伺服器通過權限表來控制使用者對資料庫的通路，權限表存放在mysql資料庫裡，由mysql_install_db腳本初始化。這些權限表分别user，db，table_priv，columns_priv和host。下面分别介紹一下這些表的結構和内容：

user權限表：記錄允許連接配接到伺服器的使用者帳号資訊，裡面的權限是全局級的。
db權限表：記錄各個帳号在各個資料庫上的操作權限。
table_priv權限表：記錄資料表級的操作權限。
columns_priv權限表：記錄資料列級的操作權限。
host權限表：配合db權限表對給定主機上資料庫級操作權限作更細緻的控制。這個權限表不受GRANT和REVOKE語句的影響。

MySQL的binlog有有幾種錄入格式？分别有什麼差別？

有三種格式，statement，row和mixed。

statement模式下，每一條會修改資料的sql都會記錄在binlog中。不需要記錄每一行的變化，減少了binlog日志量，節約了IO，提高性能。由于sql的執行是有上下文的，是以在儲存的時候需要儲存相關的資訊，同時還有一些使用了函數之類的語句無法被記錄複制。
row級别下，不記錄sql語句上下文相關資訊，僅儲存哪條記錄被修改。記錄單元為每一行的改動，基本是可以全部記下來但是由于很多操作，會導緻大量行的改動(比如alter table)，是以這種模式的檔案儲存的資訊太多，日志量太大。
mixed，一種折中的方案，普通操作使用statement記錄，當無法使用statement的時候使用row。

此外，新版的MySQL中對row級别也做了一些優化，當表結構發生變化的時候，會記錄語句而不是逐行記錄。

資料類型

mysql有哪些資料類型

mysql資料類型

引擎

MySQL存儲引擎MyISAM與InnoDB差別

存儲引擎Storage engine：MySQL中的資料、索引以及其他對象是如何存儲的，是一套檔案系統的實作。

常用的存儲引擎有以下：

Innodb引擎：Innodb引擎提供了對資料庫ACID事務的支援。并且還提供了行級鎖和外鍵的限制。它的設計的目标就是處理大資料容量的資料庫系統。
MyIASM引擎(原本Mysql的預設引擎)：不提供事務的支援，也不支援行級鎖和外鍵。
MEMORY引擎：所有的資料都在記憶體中，資料的處理速度快，但是安全性不高。

MyISAM與InnoDB差別

MySQL資料庫面試題資料庫基礎知識資料類型引擎索引事務視圖存儲過程與函數觸發器常用SQL語句SQL優化資料庫優化

MyISAM索引與InnoDB索引的差別？

InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
InnoDB的主鍵索引的葉子節點存儲着行資料，是以主鍵索引非常高效。
MyISAM索引的葉子節點存儲的是行資料位址，需要再尋址一次才能得到資料。
InnoDB非主鍵索引的葉子節點存儲的是主鍵和其他帶索引的列資料，是以查詢時做到覆寫索引會非常高效。

InnoDB引擎的4大特性

插入緩沖（insert buffer)

二次寫(double write)

自适應哈希索引(ahi)

預讀(read ahead)

存儲引擎選擇

如果沒有特别的需求，使用預設的Innodb即可。

MyISAM：以讀寫插入為主的應用程式，比如部落格系統、新聞門戶網站。

Innodb：更新（删除）操作頻率也高，或者要保證資料的完整性；并發量高，支援事務和外鍵。比如OA自動化辦公系統。

索引

什麼是索引？

索引是一種特殊的檔案(InnoDB資料表上的索引是表空間的一個組成部分)，它們包含着對資料表裡所有記錄的引用指針。

索引是一種資料結構。資料庫索引，是資料庫管理系統中一個排序的資料結構，以協助快速查詢、更新資料庫表中資料。索引的實作通常使用B樹及其變種B+樹。

更通俗的說，索引就相當于目錄。為了友善查找書中的内容，通過對内容建立索引形成目錄。索引是一個檔案，它是要占據實體空間的。

索引有哪些優缺點？

索引的優點

可以大大加快資料的檢索速度，這也是建立索引的最主要的原因。

通過使用索引，可以在查詢的過程中，使用優化隐藏器，提高系統的性能。

索引的缺點
時間方面：建立索引和維護索引要耗費時間，具體地，當對表中的資料進行增加、删除和修改的時候，索引也要動态的維護，會降低增/改/删的執行效率；
空間方面：索引需要占實體空間。

索引使用場景（重點）

where

MySQL資料庫面試題資料庫基礎知識資料類型引擎索引事務視圖存儲過程與函數觸發器常用SQL語句SQL優化資料庫優化

上圖中，根據id查詢記錄，因為id字段僅建立了主鍵索引，是以此SQL執行可選的索引隻有主鍵索引，如果有多個，最終會選一個較優的作為檢索的依據。

MySQL資料庫面試題資料庫基礎知識資料類型引擎索引事務視圖存儲過程與函數觸發器常用SQL語句SQL優化資料庫優化

可以嘗試在一個字段未建立索引時，根據該字段查詢的效率，然後對該字段建立索引（alter table 表名 add index(字段名)），同樣的SQL執行的效率，你會發現查詢效率會有明顯的提升（資料量越大越明顯）。

order by

當我們使用order by将查詢結果按照某個字段排序時，如果該字段沒有建立索引，那麼執行計劃會将查詢出的所有資料使用外部排序（将資料從硬碟分批讀取到記憶體使用内部排序，最後合并排序結果），這個操作是很影響性能的，因為需要将查詢涉及到的所有資料從磁盤中讀到記憶體（如果單條資料過大或者資料量過多都會降低效率），更無論讀到記憶體之後的排序了。

但是如果我們對該字段建立索引alter table 表名 add index(字段名)，那麼由于索引本身是有序的，是以直接按照索引的順序和映射關系逐條取出資料即可。而且如果分頁的，那麼隻用取出索引表某個範圍内的索引對應的資料，而不用像上述那取出所有資料進行排序再傳回某個範圍内的資料。（從磁盤取資料是最影響性能的）

join

對join語句比對關系（on）涉及的字段建立索引能夠提高效率

索引覆寫

如果要查詢的字段都建立過索引，那麼引擎會直接在索引表中查詢而不會通路原始資料（否則隻要有一個字段沒有建立索引就會做全表掃描），這叫索引覆寫。是以我們需要盡可能的在select後隻寫必要的查詢字段，以增加索引覆寫的幾率。

這裡值得注意的是不要想着為每個字段建立索引，因為優先使用索引的優勢就在于其體積小。

索引有哪幾種類型？

主鍵索引: 資料列不允許重複，不允許為NULL，一個表隻能有一個主鍵。

唯一索引: 資料列不允許重複，允許為NULL值，一個表允許多個列建立唯一索引。

可以通過 ALTER TABLE table_name ADD UNIQUE (column); 建立唯一索引
可以通過 ALTER TABLE table_name ADD UNIQUE (column1,column2); 建立唯一組合索引

普通索引: 基本的索引類型，沒有唯一性的限制，允許為NULL值。

可以通過ALTER TABLE table_name ADD INDEX index_name (column);建立普通索引
可以通過ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);建立組合索引

全文索引：是目前搜尋引擎使用的一種關鍵技術。

可以通過ALTER TABLE table_name ADD FULLTEXT (column);建立全文索引

索引的資料結構（b樹，hash）

索引的資料結構和具體存儲引擎的實作有關，在MySQL中使用較多的索引有Hash索引，B+樹索引等，而我們經常使用的InnoDB存儲引擎的預設索引實作為：B+樹索引。對于哈希索引來說，底層的資料結構就是哈希表，是以在絕大多數需求為單條記錄查詢的時候，可以選擇哈希索引，查詢性能最快；其餘大部分場景，建議選擇BTree索引。

1）B樹索引

mysql通過存儲引擎取資料，基本上90%的人用的就是InnoDB了，按照實作方式分，InnoDB的索引類型目前隻有兩種：BTREE（B樹）索引和HASH索引。B樹索引是Mysql資料庫中使用最頻繁的索引類型，基本所有存儲引擎都支援BTree索引。通常我們說的索引不出意外指的就是（B樹）索引（實際是用B+樹實作的，因為在檢視表索引時，mysql一律列印BTREE，是以簡稱為B樹索引）

MySQL資料庫面試題資料庫基礎知識資料類型引擎索引事務視圖存儲過程與函數觸發器常用SQL語句SQL優化資料庫優化

查詢方式：

主鍵索引區:PI(關聯儲存的時資料的位址)按主鍵查詢,

普通索引區:si(關聯的id的位址,然後再到達上面的位址)。是以按主鍵查詢,速度最快

B+tree性質：

1.）n棵子tree的節點包含n個關鍵字，不用來儲存資料而是儲存資料的索引。

2.）所有的葉子結點中包含了全部關鍵字的資訊，及指向含這些關鍵字記錄的指針，且葉子結點本身依關鍵字的大小自小而大順序連結。

3.）所有的非終端結點可以看成是索引部分，結點中僅含其子樹中的最大（或最小）關鍵字。

4.）B+ 樹中，資料對象的插入和删除僅在葉節點上進行。

5.）B+樹有2個頭指針，一個是樹的根節點，一個是最小關鍵碼的葉節點。

2）哈希索引

簡要說下，類似于資料結構中簡單實作的HASH表（散清單）一樣，當我們在mysql中用哈希索引時，主要就是通過Hash算法（常見的Hash算法有直接定址法、平方取中法、折疊法、除數取餘法、随機數法），将資料庫字段資料轉換成定長的Hash值，與這條資料的行指針一并存入Hash表的對應位置；如果發生Hash碰撞（兩個不同關鍵字的Hash值相同），則在對應Hash鍵下以連結清單形式存儲。當然這隻是簡略模拟圖。

MySQL資料庫面試題資料庫基礎知識資料類型引擎索引事務視圖存儲過程與函數觸發器常用SQL語句SQL優化資料庫優化

索引的基本原理

索引用來快速地尋找那些具有特定值的記錄。如果沒有索引，一般來說執行查詢時周遊整張表。

索引的原理很簡單，就是把無序的資料變成有序的查詢

把建立了索引的列的内容進行排序

對排序結果生成倒排表

在倒排表内容上拼上資料位址鍊

在查詢的時候，先拿到倒排表内容，再取出資料位址鍊，進而拿到具體資料

索引算法有哪些？

索引算法有 BTree算法和Hash算法

BTree算法

BTree是最常用的mysql資料庫索引算法，也是mysql預設的算法。因為它不僅可以被用在=,>,>=,<,<=和between這些比較操作符上，而且還可以用于like操作符，隻要它的查詢條件是一個不以通配符開頭的常量，例如：

-- 隻要它的查詢條件是一個不以通配符開頭的常量
select * from user where name like 'jack%'; 
-- 如果一通配符開頭，或者沒有使用常量，則不會使用索引，例如： 
select * from user where name like '%jack';

Hash算法

Hash Hash索引隻能用于對等比較，例如=,<=>（相當于=）操作符。由于是一次定位資料，不像BTree索引需要從根節點到枝節點，最後才能通路到頁節點這樣多次IO通路，是以檢索效率遠高于BTree索引。

索引設計的原則？

适合索引的列是出現在where子句中的列，或者連接配接子句中指定的列
基數較小的類，索引效果較差，沒有必要在此列建立索引
使用短索引，如果對長字元串列進行索引，應該指定一個字首長度，這樣能夠節省大量索引空間
不要過度索引。索引需要額外的磁盤空間，并降低寫操作的性能。在修改表内容的時候，索引會進行更新甚至重構，索引列越多，這個時間就會越長。是以隻保持需要的索引有利于查詢即可。

建立索引的原則（重中之重）

索引雖好，但也不是無限制的使用，最好符合一下幾個原則

1）最左字首比對原則，組合索引非常重要的原則，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的順序可以任意調整。

2）較頻繁作為查詢條件的字段才去建立索引

3）更新頻繁字段不适合建立索引

4）若是不能有效區分資料的列不适合做索引列(如性别，男女未知，最多也就三種，區分度實在太低)

5）盡量的擴充索引，不要建立索引。比如表中已經有a的索引，現在要加(a,b)的索引，那麼隻需要修改原來的索引即可。

6）定義有外鍵的資料列一定要建立索引。

7）對于那些查詢中很少涉及的列，重複值比較多的列不要建立索引。

8）對于定義為text、image和bit的資料類型的列不要建立索引。

建立索引的三種方式，删除索引

第一種方式：在執行CREATE TABLE時建立索引

CREATE TABLE user_index2 (
	id INT auto_increment PRIMARY KEY,
	first_name VARCHAR (16),
	last_name VARCHAR (16),
	id_card VARCHAR (18),
	information text,
	KEY name (first_name, last_name),
	FULLTEXT KEY (information),
	UNIQUE KEY (id_card)
);

第二種方式：使用ALTER TABLE指令去增加索引

LTER TABLE用來建立普通索引、UNIQUE索引或PRIMARY KEY索引。

其中table_name是要增加索引的表名，column_list指出對哪些列進行索引，多列時各列之間用逗号分隔。

索引名index_name可自己命名，預設時，MySQL将根據第一個索引列賦一個名稱。另外，ALTER TABLE允許在單個語句中更改多個表，是以可以在同時建立多個索引。

第三種方式：使用CREATE INDEX指令建立

CREATE INDEX可對表增加普通索引或UNIQUE索引。（但是，不能建立PRIMARY KEY索引）

删除索引

根據索引名删除普通索引、唯一索引、全文索引：

alter table 表名 drop KEY 索引名

alter table user_index drop KEY name;
alter table user_index drop KEY id_card;
alter table user_index drop KEY information;

删除主鍵索引：

alter table 表名 drop primary key（因為主鍵隻有一個）

。這裡值得注意的是，如果主鍵自增長，那麼不能直接執行此操作（自增長依賴于主鍵索引）：

MySQL資料庫面試題資料庫基礎知識資料類型引擎索引事務視圖存儲過程與函數觸發器常用SQL語句SQL優化資料庫優化

需要取消自增長再行删除：

alter table user_index
-- 重新定義字段
MODIFY id int,
drop PRIMARY KEY

但通常不會删除主鍵，因為設計主鍵一定與業務邏輯無關。

建立索引時需要注意什麼？

非空字段：應該指定列為NOT NULL，除非你想存儲NULL。在mysql中，含有空值的列很難進行查詢優化，因為它們使得索引、索引的統計資訊以及比較運算更加複雜。你應該用0、一個特殊的值或者一個空串代替空值；
取值離散大的字段：（變量各個取值之間的差異程度）的列放到聯合索引的前面，可以通過count()函數檢視字段的差異值，傳回值越大說明字段的唯一值越多字段的離散程度高；
索引字段越小越好：資料庫的資料存儲以頁為機關一頁存儲的資料越多一次IO操作擷取的資料越大效率越高。

使用索引查詢一定能提高查詢的性能嗎？為什麼

通常，通過索引查詢資料比全表掃描要快。但是我們也必須注意到它的代價。

-索引需要空間來存儲，也需要定期維護，每當有記錄在表中增減或索引列被修改時，索引本身也會被修改。這意味着每條記錄的INSERT，DELETE，UPDATE将為此多付出4，5 次的磁盤I/O。因為索引需要額外的存儲空間和處理，那些不必要的索引反而會使查詢反應時間變慢。使用索引查詢不一定能提高查詢性能，索引範圍查詢(INDEX RANGE SCAN)适用于兩種情況:

基于一個範圍的檢索，一般查詢傳回結果集小于表中記錄數的30%
基于非唯一性索引的檢索

百萬級别或以上的資料如何删除

關于索引：由于索引需要額外的維護成本，因為索引檔案是單獨存在的檔案,是以當我們對資料的增加,修改,删除,都會産生額外的對索引檔案的操作,這些操作需要消耗額外的IO,會降低增/改/删的執行效率。是以，在我們删除資料庫百萬級别資料的時候，查詢MySQL官方手冊得知删除資料的速度和建立的索引數量是成正比的。

是以我們想要删除百萬資料的時候可以先删除索引（此時大概耗時三分多鐘）
然後删除其中無用資料（此過程需要不到兩分鐘）
删除完成後重新建立索引(此時資料較少了)建立索引也非常快，約十分鐘左右。
與之前的直接删除絕對是要快速很多，更别說萬一删除中斷,一切删除會復原。那更是坑了。

字首索引

文法：index(field(10))，使用字段值的前10個字元建立索引，預設是使用字段的全部内容建立索引。

前提：字首的辨別度高。比如密碼就适合建立字首索引，因為密碼幾乎各不相同。

實操的難度：在于字首截取的長度。

我們可以利用select count(*)/count(distinct left(password,prefixLen));，通過從調整prefixLen的值（從1自增）檢視不同字首長度的一個平均比對度，接近1時就可以了（表示一個密碼的前prefixLen個字元幾乎能确定唯一一條記錄）

什麼是最左字首原則？什麼是最左比對原則

顧名思義，就是最左優先，在建立多列索引時，要根據業務需求，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的查詢優化器會幫你優化成索引可以識别的形式

B樹和B+樹的差別

在B樹中，你可以将鍵和值存放在内部節點和葉子節點；但在B+樹中，内部節點都是鍵，沒有值，葉子節點同時存放鍵和值。
B+樹的葉子節點有一條鍊相連，而B樹的葉子節點各自獨立。

MySQL資料庫面試題資料庫基礎知識資料類型引擎索引事務視圖存儲過程與函數觸發器常用SQL語句SQL優化資料庫優化

使用B樹的好處

B樹可以在内部節點同時存儲鍵和值，是以，把頻繁通路的資料放在靠近根節點的地方将會大大提高熱點資料的查詢效率。這種特性使得B樹在特定資料重複多次查詢的場景中更加高效。

使用B+樹的好處

由于B+樹的内部節點隻存放鍵，不存放值，是以，一次讀取，可以在記憶體頁中擷取更多的鍵，有利于更快地縮小查找範圍。 B+樹的葉節點由一條鍊相連，是以，當需要進行一次全資料周遊的時候，B+樹隻需要使用O(logN)時間找到最小的一個節點，然後通過鍊進行O(N)的順序周遊即可。而B樹則需要對樹的每一層進行周遊，這會需要更多的記憶體置換次數，是以也就需要花費更多的時間

Hash索引和B+樹所有有什麼差別或者說優劣呢?

首先要知道Hash索引和B+樹索引的底層實作原理：

hash索引底層就是hash表，進行查找時，調用一次hash函數就可以擷取到相應的鍵值，之後進行回表查詢獲得實際資料。B+樹底層實作是多路平衡查找樹。對于每一次的查詢都是從根節點出發，查找到葉子節點方可以獲得所查鍵值，然後根據查詢判斷是否需要回表查詢資料。

那麼可以看出他們有以下的不同：

hash索引進行等值查詢更快(一般情況下)，但是卻無法進行範圍查詢。

因為在hash索引中經過hash函數建立索引之後，索引的順序與原順序無法保持一緻，不能支援範圍查詢。而B+樹的的所有節點皆遵循(左節點小于父節點，右節點大于父節點，多叉樹也類似)，天然支援範圍。
hash索引不支援使用索引進行排序，原理同上。
hash索引不支援模糊查詢以及多列索引的最左字首比對。原理也是因為hash函數的不可預測。AAAA和AAAAB的索引沒有相關性。
hash索引任何時候都避免不了回表查詢資料，而B+樹在符合某些條件(聚簇索引，覆寫索引等)的時候可以隻通過索引完成查詢。
hash索引雖然在等值查詢上較快，但是不穩定。性能不可預測，當某個鍵值存在大量重複的時候，發生hash碰撞，此時效率可能極差。而B+樹的查詢效率比較穩定，對于所有的查詢都是從根節點到葉子節點，且樹的高度較低。
是以，在大多數情況下，直接選擇B+樹索引可以獲得穩定且較好的查詢速度。而不需要使用hash索引。

資料庫為什麼使用B+樹而不是B樹

B樹隻适合随機檢索，而B+樹同時支援随機檢索和順序檢索；
B+樹空間使用率更高，可減少I/O次數，磁盤讀寫代價更低。一般來說，索引本身也很大，不可能全部存儲在記憶體中，是以索引往往以索引檔案的形式存儲的磁盤上。這樣的話，索引查找過程中就要産生磁盤I/O消耗。B+樹的内部結點并沒有指向關鍵字具體資訊的指針，隻是作為索引使用，其内部結點比B樹小，盤塊能容納的結點中關鍵字數量更多，一次性讀入記憶體中可以查找的關鍵字也就越多，相對的，IO讀寫次數也就降低了。而IO讀寫次數是影響索引檢索效率的最大因素；
B+樹的查詢效率更加穩定。B樹搜尋有可能會在非葉子結點結束，越靠近根節點的記錄查找時間越短，隻要找到關鍵字即可确定記錄的存在，其性能等價于在關鍵字全集内做一次二分查找。而在B+樹中，順序檢索比較明顯，随機檢索時，任何關鍵字的查找都必須走一條從根節點到葉節點的路，所有關鍵字的查找路徑長度相同，導緻每一個關鍵字的查詢效率相當。
B-樹在提高了磁盤IO性能的同時并沒有解決元素周遊的效率低下的問題。B+樹的葉子節點使用指針順序連接配接在一起，隻要周遊葉子節點就可以實作整棵樹的周遊。而且在資料庫中基于範圍的查詢是非常頻繁的，而B樹不支援這樣的操作。
增删檔案（節點）時，效率更高。因為B+樹的葉子節點包含所有關鍵字，并以有序的連結清單結構存儲，這樣可很好提高增删效率。

B+樹在滿足聚簇索引和覆寫索引的時候不需要回表查詢資料，

在B+樹的索引中，葉子節點可能存儲了目前的key值，也可能存儲了目前的key值以及整行的資料，這就是聚簇索引和非聚簇索引。在InnoDB中，隻有主鍵索引是聚簇索引，如果沒有主鍵，則挑選一個唯一鍵建立聚簇索引。如果沒有唯一鍵，則隐式的生成一個鍵來建立聚簇索引。
當查詢使用聚簇索引時，在對應的葉子節點，可以擷取到整行資料，是以不用再次進行回表查詢。

什麼是聚簇索引？何時使用聚簇索引與非聚簇索引

聚簇索引：将資料存儲與索引放到了一塊，找到索引也就找到了資料
非聚簇索引：将資料存儲于索引分開結構，索引結構的葉子節點指向了資料的對應行，myisam通過key_buffer把索引先緩存到記憶體中，當需要通路資料時（通過索引通路資料），在記憶體中直接搜尋索引，然後通過索引找到磁盤相應資料，這也就是為什麼索引不在key buffer命中時，速度慢的原因

澄清一個概念：innodb中，在聚簇索引之上建立的索引稱之為輔助索引，輔助索引通路資料總是需要二次查找，非聚簇索引都是輔助索引，像複合索引、字首索引、唯一索引，輔助索引葉子節點存儲的不再是行的實體位置，而是主鍵值

何時使用聚簇索引與非聚簇索引

MySQL資料庫面試題資料庫基礎知識資料類型引擎索引事務視圖存儲過程與函數觸發器常用SQL語句SQL優化資料庫優化

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

**不一定，**這涉及到查詢語句所要求的字段是否全部命中了索引，如果全部命中了索引，那麼就不必再進行回表查詢。

舉個簡單的例子，假設我們在員工表的年齡上建立了索引，那麼當進行select age from employee where age < 20的查詢時，在索引的葉子節點上，已經包含了age資訊，不會再次進行回表查詢。

聯合索引是什麼？為什麼需要注意聯合索引中的順序？

MySQL可以使用多個字段同時建立一個索引，叫做聯合索引。在聯合索引中，如果想要命中索引，需要按照建立索引時的字段順序挨個使用，否則無法命中索引。

具體原因為:

MySQL使用索引時需要索引有序，假設現在建立了"name，age，school"的聯合索引，那麼索引的排序為: 先按照name排序，如果name相同，則按照age排序，如果age的值也相等，則按照school進行排序。

當進行查詢時，此時索引僅僅按照name嚴格有序，是以必須首先使用name字段進行等值查詢，之後對于比對到的列而言，其按照age字段嚴格有序，此時可以使用age字段用做索引查找，以此類推。是以在建立聯合索引的時候應該注意索引列的順序，一般情況下，将查詢需求頻繁或者字段選擇性高的列放在前面。此外可以根據特例的查詢或者表結構進行單獨的調整。

事務

什麼是資料庫事務？

事務是一個不可分割的資料庫操作序列，也是資料庫并發控制的基本機關，其執行的結果必須使資料庫從一種一緻性狀态變到另一種一緻性狀态。事務是邏輯上的一組操作，要麼都執行，要麼都不執行。

事務最經典也經常被拿出來說例子就是轉賬了。

假如小明要給小紅轉賬1000元，這個轉賬會涉及到兩個關鍵操作就是：将小明的餘額減少1000元，将小紅的餘額增加1000元。萬一在這兩個操作之間突然出現錯誤比如銀行系統崩潰，導緻小明餘額減少而小紅的餘額沒有增加，這樣就不對了。事務就是保證這兩個關鍵操作要麼都成功，要麼都要失敗。

事物的四大特性(ACID)介紹一下?

關系性資料庫需要遵循ACID規則，具體内容如下：

MySQL資料庫面試題資料庫基礎知識資料類型引擎索引事務視圖存儲過程與函數觸發器常用SQL語句SQL優化資料庫優化

原子性：事務是最小的執行機關，不允許分割。事務的原子性確定動作要麼全部完成，要麼完全不起作用；
一緻性：執行事務前後，資料保持一緻，多個事務對同一個資料讀取的結果是相同的；
隔離性：并發通路資料庫時，一個使用者的事務不被其他事務所幹擾，各并發事務之間資料庫是獨立的；
持久性：一個事務被送出之後。它對資料庫中資料的改變是持久的，即使資料庫發生故障也不應該對其有任何影響。

什麼是髒讀？幻讀？不可重複讀？

髒讀(Drity Read)(事務A讀取到事務B更新失敗的資料)：某個事務已更新一份資料，另一個事務在此時讀取了同一份資料，由于某些原因，前一個RollBack了操作，則後一個事務所讀取的資料就會是不正确的。
不可重複讀(Non-repeatable read)（事務A讀取到事務B更新的資料）:在一個事務的兩次查詢之中資料不一緻，這可能是兩次查詢過程中間插入了一個事務更新的原有的資料。
幻讀(Phantom Read)（事務A讀取到事務B插入的資料）:在一個事務的兩次查詢中資料筆數不一緻，例如有一個事務查詢了幾列(Row)資料，而另一個事務卻在此時插入了新的幾列資料，先前的事務在接下來的查詢中，就會發現有幾列資料是它先前所沒有的。

什麼是事務的隔離級别？MySQL的預設隔離級别是什麼？

為了達到事務的四大特性，資料庫定義了4種不同的事務隔離級别，由低到高依次為Read uncommitted、Read committed、Repeatable read、Serializable，這四個級别可以逐個解決髒讀、不可重複讀、幻讀這幾類問題。

MySQL資料庫面試題資料庫基礎知識資料類型引擎索引事務視圖存儲過程與函數觸發器常用SQL語句SQL優化資料庫優化

SQL 标準定義了四個隔離級别：

READ-UNCOMMITTED(讀取未送出)：最低的隔離級别，允許讀取尚未送出的資料變更，可能會導緻髒讀、幻讀或不可重複讀。
READ-COMMITTED(讀取已送出)：允許讀取并發事務已經送出的資料，可以阻止髒讀，但是幻讀或不可重複讀仍有可能發生。
REPEATABLE-READ(可重複讀)：對同一字段的多次讀取結果都是一緻的，除非資料是被本身事務自己所修改，可以阻止髒讀和不可重複讀，但幻讀仍有可能發生。
SERIALIZABLE(可串行化)：最高的隔離級别，完全服從ACID的隔離級别。所有的事務依次逐個執行，這樣事務之間就完全不可能産生幹擾，也就是說，該級别可以防止髒讀、不可重複讀以及幻讀。

這裡需要注意的是：Mysql 預設采用的 REPEATABLE_READ隔離級别 Oracle 預設采用的 READ_COMMITTED隔離級别

事務隔離機制的實作基于鎖機制和并發排程。其中并發排程使用的是MVVC（多版本并發控制），通過儲存修改的舊版本資訊來支援并發一緻性讀和復原等特性。

因為隔離級别越低，事務請求的鎖越少，是以大部分資料庫系統的隔離級别都是READ-COMMITTED(讀取送出内容):，但是你要知道的是InnoDB 存儲引擎預設使用 REPEATABLE-READ（可重讀并不會有任何性能損失)。

InnoDB 存儲引擎在分布式事務的情況下一般會用到**SERIALIZABLE(可串行化)**隔離級别。

鎖

對MySQL的鎖了解嗎

當資料庫有并發事務的時候，可能會産生資料的不一緻，這時候需要一些機制來保證通路的次序，鎖機制就是這樣的一個機制。

就像酒店的房間，如果大家随意進出，就會出現多人搶奪同一個房間的情況，而在房間上裝上鎖，申請到鑰匙的人才可以入住并且将房間鎖起來，其他人隻有等他使用完畢才可以再次使用。

隔離級别與鎖的關系

在Read Uncommitted級别下，讀取資料不需要加共享鎖，這樣就不會跟被修改的資料上的排他鎖沖突

在Read Committed級别下，讀操作需要加共享鎖，但是在語句執行完以後釋放共享鎖；

在Repeatable Read級别下，讀操作需要加共享鎖，但是在事務送出之前并不釋放共享鎖，也就是必須等待事務執行完畢以後才釋放共享鎖。

SERIALIZABLE 是限制性最強的隔離級别，因為該級别鎖定整個範圍的鍵，并一直持有鎖，直到事務完成。

按照鎖的粒度分資料庫鎖有哪些？鎖機制與InnoDB鎖算法

在關系型資料庫中，可以按照鎖的粒度把資料庫鎖分為行級鎖(INNODB引擎)、表級鎖(MYISAM引擎)和頁級鎖(BDB引擎 )。

MyISAM和InnoDB存儲引擎使用的鎖：

MyISAM采用表級鎖(table-level locking)。
InnoDB支援行級鎖(row-level locking)和表級鎖，預設為行級鎖

行級鎖，表級鎖和頁級鎖對比

行級鎖行級鎖是Mysql中鎖定粒度最細的一種鎖，表示隻針對目前操作的行進行加鎖。行級鎖能大大減少資料庫操作的沖突。其加鎖粒度最小，但加鎖的開銷也最大。行級鎖分為共享鎖和排他鎖。

特點：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的機率最低，并發度也最高。

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

特點：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發出鎖沖突的機率最高，并發度最低。

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

特點：開銷和加鎖時間界于表鎖和行鎖之間；會出現死鎖；鎖定粒度界于表鎖和行鎖之間，并發度一般

從鎖的類别上分MySQL都有哪些鎖呢？像上面那樣子進行鎖定豈不是有點阻礙并發效率了

從鎖的類别上來講，有共享鎖和排他鎖。

共享鎖: 又叫做讀鎖。當使用者要進行資料的讀取時，對資料加上共享鎖。共享鎖可以同時加上多個。

排他鎖: 又叫做寫鎖。當使用者要進行資料的寫入時，對資料加上排他鎖。排他鎖隻可以加一個，他和其他的排他鎖，共享鎖都相斥。

用上面的例子來說就是使用者的行為有兩種，一種是來看房，多個使用者一起看房是可以接受的。一種是真正的入住一晚，在這期間，無論是想入住的還是想看房的都不可以。

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

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

MySQL中InnoDB引擎的行鎖是怎麼實作的？

答：InnoDB是基于索引來完成行鎖

例: select * from tab_with_index where id = 1 for update;

for update 可以根據條件來完成行鎖鎖定，并且 id 是有索引鍵的列，如果 id 不是索引鍵那麼InnoDB将完成表鎖，并發将無從談起

InnoDB存儲引擎的鎖的算法有三種

Record lock：單個行記錄上的鎖
Gap lock：間隙鎖，鎖定一個範圍，不包括記錄本身
Next-key lock：record+gap 鎖定一個範圍，包含記錄本身

相關知識點：

innodb對于行的查詢使用next-key lock

Next-locking keying為了解決Phantom Problem幻讀問題

當查詢的索引含有唯一屬性時，将next-key lock降級為record key

Gap鎖設計的目的是為了阻止多個事務将記錄插入到同一範圍内，而這會導緻幻讀問題的産生

有兩種方式顯式關閉gap鎖：（除了外鍵限制和唯一性檢查外，其餘情況僅使用record lock） A. 将事務隔離級别設定為RC B. 将參數innodb_locks_unsafe_for_binlog設定為1

什麼是死鎖？怎麼解決？

死鎖是指兩個或多個事務在同一資源上互相占用，并請求鎖定對方的資源，進而導緻惡性循環的現象。

常見的解決死鎖的方法

1、如果不同程式會并發存取多個表，盡量約定以相同的順序通路表，可以大大降低死鎖機會。

2、在同一個事務中，盡可能做到一次鎖定所需要的所有資源，減少死鎖産生機率；

3、對于非常容易産生死鎖的業務部分，可以嘗試使用更新鎖定顆粒度，通過表級鎖定來減少死鎖産生的機率；

如果業務處理不好可以用分布式事務鎖或者使用樂觀鎖

資料庫的樂觀鎖和悲觀鎖是什麼？怎麼實作的？

資料庫管理系統（DBMS）中的并發控制的任務是確定在多個事務同時存取資料庫中同一資料時不破壞事務的隔離性和統一性以及資料庫的統一性。樂觀并發控制（樂觀鎖）和悲觀并發控制（悲觀鎖）是并發控制主要采用的技術手段。

悲觀鎖：假定會發生并發沖突，屏蔽一切可能違反資料完整性的操作。在查詢完資料的時候就把事務鎖起來，直到送出事務。實作方式：使用資料庫中的鎖機制

樂觀鎖：假設不會發生并發沖突，隻在送出操作時檢查是否違反資料完整性。在修改資料的時候把事務鎖起來，通過version的方式來進行鎖定。實作方式：樂一般會使用版本号機制或CAS算法實作。

兩種鎖的使用場景

從上面對兩種鎖的介紹，我們知道兩種鎖各有優缺點，不可認為一種好于另一種，像樂觀鎖适用于寫比較少的情況下（多讀場景），即沖突真的很少發生的時候，這樣可以省去了鎖的開銷，加大了系統的整個吞吐量。

但如果是多寫的情況，一般會經常産生沖突，這就會導緻上層應用會不斷的進行retry，這樣反倒是降低了性能，是以一般多寫的場景下用悲觀鎖就比較合适。

視圖

為什麼要使用視圖？什麼是視圖？

為了提高複雜SQL語句的複用性和表操作的安全性，MySQL資料庫管理系統提供了視圖特性。所謂視圖，本質上是一種虛拟表，在實體上是不存在的，其内容與真實的表相似，包含一系列帶有名稱的列和行資料。但是，視圖并不在資料庫中以儲存的資料值形式存在。行和列資料來自定義視圖的查詢所引用基本表，并且在具體引用視圖時動态生成。

視圖使開發者隻關心感興趣的某些特定資料和所負責的特定任務，隻能看到視圖中所定義的資料，而不是視圖所引用表中的資料，進而提高了資料庫中資料的安全性。

視圖有哪些特點？

視圖的特點如下:

視圖的列可以來自不同的表，是表的抽象和在邏輯意義上建立的新關系。
視圖是由基本表(實表)産生的表(虛表)。
視圖的建立和删除不影響基本表。
對視圖内容的更新(添加，删除和修改)直接影響基本表。
當視圖來自多個基本表時，不允許添加和删除資料。
視圖的操作包括建立視圖，檢視視圖，删除視圖和修改視圖。

視圖的使用場景有哪些？

視圖根本用途：簡化sql查詢，提高開發效率。如果說還有另外一個用途那就是相容老的表結構。

下面是視圖的常見使用場景：

重用SQL語句；
簡化複雜的SQL操作。在編寫查詢後，可以友善的重用它而不必知道它的基本查詢細節；
使用表的組成部分而不是整個表；
保護資料。可以給使用者授予表的特定部分的通路權限而不是整個表的通路權限；
更改資料格式和表示。視圖可傳回與底層表的表示和格式不同的資料。

視圖的優點

查詢簡單化。視圖能簡化使用者的操作
資料安全性。視圖使使用者能以多種角度看待同一資料，能夠對機密資料提供安全保護
邏輯資料獨立性。視圖對重構資料庫提供了一定程度的邏輯獨立性

視圖的缺點

性能。資料庫必須把視圖的查詢轉化成對基本表的查詢，如果這個視圖是由一個複雜的多表查詢所定義，那麼，即使是視圖的一個簡單查詢，資料庫也把它變成一個複雜的結合體，需要花費一定的時間。
修改限制。當使用者試圖修改視圖的某些行時，資料庫必須把它轉化為對基本表的某些行的修改。事實上，當從視圖中插入或者删除時，情況也是這樣。對于簡單視圖來說，這是很友善的，但是，對于比較複雜的視圖，可能是不可修改的

這些視圖有如下特征：1.有UNIQUE等集合操作符的視圖。2.有GROUP BY子句的視圖。3.有諸如AVG\SUM\MAX等聚合函數的視圖。 4.使用DISTINCT關鍵字的視圖。5.連接配接表的視圖（其中有些例外）

什麼是遊标？

遊标是系統為使用者開設的一個資料緩沖區，存放SQL語句的執行結果，每個遊标區都有一個名字。使用者可以通過遊标逐一擷取記錄并賦給主變量，交由主語言進一步處理。

存儲過程與函數

什麼是存儲過程？有哪些優缺點？

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

優點

1）存儲過程是預編譯過的，執行效率高。

2）存儲過程的代碼直接存放于資料庫中，通過存儲過程名直接調用，減少網絡通訊。

3）安全性高，執行存儲過程需要有一定權限的使用者。

4）存儲過程可以重複使用，減少資料庫開發人員的工作量。

缺點

1）調試麻煩，但是用 PL/SQL Developer 調試很友善！彌補這個缺點。

2）移植問題，資料庫端代碼當然是與資料庫相關的。但是如果是做工程型項目，基本不存在移植問題。

3）重新編譯問題，因為後端代碼是運作前編譯的，如果帶有引用關系的對象發生改變時，受影響的存儲過程、包将需要重新編譯（不過也可以設定成運作時刻自動編譯）。

4）如果在一個程式系統中大量的使用存儲過程，到程式傳遞使用的時候随着使用者需求的增加會導緻資料結構的變化，接着就是系統的相關問題了，最後如果使用者想維護該系統可以說是很難很難、而且代價是空前的，維護起來更麻煩。

觸發器

什麼是觸發器？觸發器的使用場景有哪些？

觸發器是使用者定義在關系表上的一類由事件驅動的特殊的存儲過程。觸發器是指一段代碼，當觸發某個事件時，自動執行這些代碼。

使用場景

可以通過資料庫中的相關表實作級聯更改。
實時監控某張表中的某個字段的更改而需要做出相應的處理。
例如可以生成某些業務的編号。

注意不要濫用，否則會造成資料庫及應用程式的維護困難。

MySQL中都有哪些觸發器？

在MySQL資料庫中有如下六種觸發器：

Before Insert

After Insert

Before Update

After Update

Before Delete

After Delete

常用SQL語句

SQL語句主要分為哪幾類

資料定義語言DDL（Data Ddefinition Language）CREATE，DROP，ALTER

主要為以上操作即對邏輯結構等有操作的，其中包括表結構，視圖和索引。

資料查詢語言DQL（Data Query Language）SELECT

這個較為好了解即查詢操作，以select關鍵字。各種簡單查詢，連接配接查詢等都屬于DQL。

資料操縱語言DML（Data Manipulation Language）INSERT，UPDATE，DELETE

主要為以上操作即對資料進行操作的，對應上面所說的查詢操作 DQL與DML共同建構了多數初級程式員常用的增删改查操作。而查詢是較為特殊的一種被劃分到DQL中。

資料控制功能DCL（Data Control Language）GRANT，REVOKE，COMMIT，ROLLBACK

主要為以上操作即對資料庫安全性完整性等有操作的，可以簡單的了解為權限控制等。

超鍵、候選鍵、主鍵、外鍵分别是什麼？

超鍵：在關系中能唯一辨別元組的屬性集稱為關系模式的超鍵。一個屬性可以為作為一個超鍵，多個屬性組合在一起也可以作為一個超鍵。超鍵包含候選鍵和主鍵。
候選鍵：是最小超鍵，即沒有備援元素的超鍵。
主鍵：資料庫表中對儲存資料對象予以唯一和完整辨別的資料列或屬性的組合。一個資料列隻能有一個主鍵，且主鍵的取值不能缺失，即不能為空值（Null）。
外鍵：在一個表中存在的另一個表的主鍵稱此表的外鍵。

SQL 限制有哪幾種？

NOT NULL: 用于控制字段的内容一定不能為空（NULL）。
UNIQUE: 控件字段内容不能重複，一個表允許有多個 Unique 限制。
PRIMARY KEY: 也是用于控件字段内容不能重複，但它在一個表隻允許出現一個。
FOREIGN KEY: 用于預防破壞表之間連接配接的動作，也能防止非法資料插入外鍵列，因為它必須是它指向的那個表中的值之一。
CHECK: 用于控制字段的值範圍。

六種關聯查詢

交叉連接配接（CROSS JOIN）

内連接配接（INNER JOIN）

外連接配接（LEFT JOIN/RIGHT JOIN）

聯合查詢（UNION與UNION ALL）

全連接配接（FULL JOIN）

交叉連接配接（CROSS JOIN）

什麼是子查詢

條件：一條SQL語句的查詢結果做為另一條查詢語句的條件或查詢結果

嵌套：多條SQL語句嵌套使用，内部的SQL查詢語句稱為子查詢。

子查詢的三種情況

子查詢是單行單列的情況：結果集是一個值，父查詢使用：=、 <、 > 等運算符

-- 查詢工資最高的員工是誰？ 
select  * from employee where salary=(select max(salary) from employee);

子查詢是多行單列的情況：結果集類似于一個數組，父查詢使用：in 運算符

-- 查詢工資最高的員工是誰？ 
select  * from employee where salary in (select max(salary) from employee);

子查詢是多行多列的情況：結果集類似于一張虛拟表，不能用于where條件，用于select子句中做為子表

-- 1) 查詢出2011年以後入職的員工資訊
-- 2) 查詢所有的部門資訊，與上面的虛拟表中的資訊比對，找出所有部門ID相等的員工。
select * from dept d,  (select * from employee where join_date > '2011-1-1') e where e.dept_id =  d.id;    

-- 使用表連接配接：
select d.*, e.* from  dept d inner join employee e on d.id = e.dept_id where e.join_date >  '2011-1-1'

mysql中 in 和 exists 差別

遵循小表驅動大表原則，小表先執行

mysql中的in語句是把外表和内表作hash 連接配接，而exists語句是對外表作loop循環，每次loop循環再對内表進行查詢。一直大家都認為exists比in語句的效率要高，這種說法其實是不準确的。這個是要區分環境的。

如果查詢的兩個表大小相當，那麼用in和exists差别不大。

如果兩個表中一個較小，一個是大表，則子查詢表大的用exists，子查詢表小的用in。

not in 和not exists：如果查詢語句使用了not in，那麼内外表都進行全表掃描，沒有用到索引；而not extsts的子查詢依然能用到表上的索引。是以無論那個表大，用not exists都比not in要快。

varchar與char的差別

char的特點

char表示定長字元串，長度是固定的；
如果插入資料的長度小于char的固定長度時，則用空格填充；
因為長度固定，是以存取速度要比varchar快很多，甚至能快50%，但正因為其長度固定，是以會占據多餘的空間，是空間換時間的做法；
對于char來說，最多能存放的字元個數為255，和編碼無關

varchar的特點

varchar表示可變長字元串，長度是可變的；
插入的資料是多長，就按照多長來存儲；
varchar在存取方面與char相反，它存取慢，因為長度不固定，但正因如此，不占據多餘的空間，是時間換空間的做法；

= 對于varchar來說，最多能存放的字元個數為65532

總之，結合性能角度（char更快）和節省磁盤空間角度（varchar更小），具體情況還需具體來設計資料庫才是妥當的做法。

varchar(50)中50的涵義

最多存放50個字元，varchar(50)和(200)存儲hello所占空間一樣，但後者在排序時會消耗更多記憶體，因為order by col采用fixed_length計算col長度(memory引擎也一樣)。在早期 MySQL 版本中， 50 代表位元組數，現在代表字元數。

int(20)中20的涵義

是指顯示字元的長度。20表示最大顯示寬度為20，但仍占4位元組存儲，存儲範圍不變；

不影響内部存儲，隻是影響帶 zerofill 定義的 int 時，前面補多少個 0，易于報表展示

mysql為什麼這麼設計

對大多數應用沒有意義，隻是規定一些工具用來顯示字元的個數；int(1)和int(20)存儲和計算均一樣；

mysql中int(10)和char(10)以及varchar(10)的差別

int(10)的10表示顯示的資料的長度，不是存儲資料的大小；chart(10)和varchar(10)的10表示存儲資料的大小，即表示存儲多少個字元。
int(10) 10位的資料長度 9999999999，占32個位元組，int型4位
char(10) 10位固定字元串，不足補空格最多10個字元
varchar(10) 10位可變字元串，不足補空格最多10個字元
char(10)表示存儲定長的10個字元，不足10個就用空格補齊，占用更多的存儲空間
varchar(10)表示存儲10個變長的字元，存儲多少個就是多少個，空格也按一個字元存儲，這一點是和char(10)的空格不同的，char(10)的空格表示占位不算一個字元

FLOAT和DOUBLE的差別是什麼？

FLOAT類型資料可以存儲至多8位十進制數，并在記憶體中占4位元組。

DOUBLE類型資料可以存儲至多18位十進制數，并在記憶體中占8位元組。

drop、delete與truncate的差別

三者都表示删除，但是三者有一些差别：

MySQL資料庫面試題資料庫基礎知識資料類型引擎索引事務視圖存儲過程與函數觸發器常用SQL語句SQL優化資料庫優化

是以，在不再需要一張表的時候，用drop；在想删除部分資料行時候，用delete；在保留表而删除所有資料的時候用truncate。

UNION與UNION ALL的差別？

如果使用UNION ALL，不會合并重複的記錄行

效率 UNION 高于 UNION ALL

SQL優化

如何定位及優化SQL語句的性能問題？建立的索引有沒有被使用到?或者說怎麼才可以知道這條語句運作很慢的原因？

對于低性能的SQL語句的定位，最重要也是最有效的方法就是使用執行計劃，MySQL提供了explain指令來檢視語句的執行計劃。我們知道，不管是哪種資料庫，或者是哪種資料庫引擎，在對一條SQL語句進行執行的過程中都會做很多相關的優化，對于查詢語句，最重要的優化方式就是使用索引。而執行計劃，就是顯示資料庫引擎對于SQL語句的執行的詳細情況，其中包含了是否使用索引，使用什麼索引，使用的索引的相關資訊等。

MySQL資料庫面試題資料庫基礎知識資料類型引擎索引事務視圖存儲過程與函數觸發器常用SQL語句SQL優化資料庫優化

執行計劃包含的資訊 id 有一組數字組成。表示一個查詢中各個子查詢的執行順序;

id相同執行順序由上至下。
id不同，id值越大優先級越高，越先被執行。
id為null時表示一個結果集，不需要使用它查詢，常出現在包含union等查詢語句中。

select_type 每個子查詢的查詢類型，一些常見的查詢類型。

MySQL資料庫面試題資料庫基礎知識資料類型引擎索引事務視圖存儲過程與函數觸發器常用SQL語句SQL優化資料庫優化

table 查詢的資料表，當從衍生表中查資料時會顯示 x 表示對應的執行計劃id partitions 表分區、表建立的時候可以指定通過那個列進行表分區。舉個例子：

create table tmp (
    id int unsigned not null AUTO_INCREMENT,
    name varchar(255),
    PRIMARY KEY (id)
) engine = innodb
partition by key (id) partitions 5;

type(非常重要，可以看到有沒有走索引) 通路類型

ALL 掃描全表資料
index 周遊索引
range 索引範圍查找
index_subquery 在子查詢中使用 ref
unique_subquery 在子查詢中使用 eq_ref
ref_or_null 對Null進行索引的優化的 ref
fulltext 使用全文索引
ref 使用非唯一索引查找資料
eq_ref 在join查詢中使用PRIMARY KEYorUNIQUE NOT NULL索引關聯。

possible_keys 可能使用的索引，注意不一定會使用。查詢涉及到的字段上若存在索引，則該索引将被列出來。當該列為 NULL時就要考慮目前的SQL是否需要優化了。

key 顯示MySQL在查詢中實際使用的索引，若沒有使用索引，顯示為NULL。

TIPS:查詢中若使用了覆寫索引(覆寫索引：索引的資料覆寫了需要查詢的所有資料)，則該索引僅出現在key清單中

key_length 索引長度

ref 表示上述表的連接配接比對條件，即哪些列或常量被用于查找索引列上的值

rows 傳回估算的結果集數目，并不是一個準确的值。

extra 的資訊非常豐富，常見的有：

Using index 使用覆寫索引
Using where 使用了用where子句來過濾結果集
Using filesort 使用檔案排序，使用非索引列進行排序時出現，非常消耗性能，盡量優化。
Using temporary 使用了臨時表 sql優化的目标可以參考阿裡開發手冊

【推薦】SQL性能優化的目标：至少要達到 range 級别，要求是ref級别，如果可以是consts最好。 
說明： 
1） consts 單表中最多隻有一個比對行（主鍵或者唯一索引），在優化階段即可讀取到資料。 
2） ref 指的是使用普通的索引（normal index）。 
3） range 對索引進行範圍檢索。 
反例：explain表的結果，type=index，索引實體檔案全掃描，速度非常慢，這個index級别比較range還低，與全表掃描是小巫見大巫。

SQL的生命周期？

1、應用伺服器與資料庫伺服器建立一個連接配接

2、資料庫程序拿到請求sql

3、解析并生成執行計劃，執行

4、讀取資料到記憶體并進行邏輯處理

5、通過步驟一的連接配接，發送結果到用戶端

6、關掉連接配接，釋放資源

MySQL資料庫面試題資料庫基礎知識資料類型引擎索引事務視圖存儲過程與函數觸發器常用SQL語句SQL優化資料庫優化

大表資料查詢，怎麼優化

優化shema、sql語句+索引；
第二加緩存，memcached, redis；
主從複制，讀寫分離；
垂直拆分，根據你子產品的耦合度，将一個大的系統分為多個小的系統，也就是分布式系統；
水準切分，針對資料量大的表，這一步最麻煩，最能考驗技術水準，要選擇一個合理的sharding key, 為了有好的查詢效率，表結構也要改動，做一定的備援，應用也要改，sql中盡量帶sharding key，将資料定位到限定的表上去查，而不是掃描全部的表；

超大分頁怎麼處理？

超大的分頁一般從兩個方向上來解決.

資料庫層面,這也是我們主要集中關注的(雖然收效沒那麼大),類似于 select * from table where age > 20 limit 1000000,10 這種查詢其實也是有可以優化的餘地的. 這條語句需要load1000000資料然後基本上全部丢棄,隻取10條當然比較慢. 當時我們可以修改為 select * from table where id in (select id from table where age > 20 limit 1000000,10) .這樣雖然也load了一百萬的資料,但是由于索引覆寫,要查詢的所有字段都在索引中,是以速度會很快. 同時如果ID連續的好,我們還可以 select * from table where id > 1000000 limit 10 ,效率也是不錯的,優化的可能性有許多種,但是核心思想都一樣,就是減少load的資料.
從需求的角度減少這種請求…主要是不做類似的需求(直接跳轉到幾百萬頁之後的具體某一頁.隻允許逐頁檢視或者按照給定的路線走,這樣可預測,可緩存)以及防止ID洩漏且連續被人惡意攻擊.

解決超大分頁,其實主要是靠緩存,可預測性的提前查到内容,緩存至redis等k-V資料庫中,直接傳回即可.

在阿裡巴巴《Java開發手冊》中,對超大分頁的解決辦法是類似于上面提到的第一種.

【推薦】利用延遲關聯或者子查詢優化超多分頁場景。 

說明：MySQL并不是跳過offset行，而是取offset+N行，然後傳回放棄前offset行，傳回N行，那當offset特别大的時候，效率就非常的低下，要麼控制傳回的總頁數，要麼對超過特定門檻值的頁數進行SQL改寫。 

正例：先快速定位需要擷取的id段，然後再關聯： 

SELECT a.* FROM 表1 a, (select id from 表1 where 條件 LIMIT 100000,20 ) b where a.id=b.id

mysql 分頁

LIMIT 子句可以被用于強制 SELECT 語句傳回指定的記錄數。LIMIT 接受一個或兩個數字參數。參數必須是一個整數常量。如果給定兩個參數，第一個參數指定第一個傳回記錄行的偏移量，第二個參數指定傳回記錄行的最大數目。初始記錄行的偏移量是 0(而不是 1)

為了檢索從某一個偏移量到記錄集的結束所有的記錄行，可以指定第二個參數為 -1：

如果隻給定一個參數，它表示傳回最大的記錄行數目：

慢查詢日志

用于記錄執行時間超過某個臨界值的SQL日志，用于快速定位慢查詢，為我們的優化做參考。

開啟慢查詢日志

配置項：slow_query_log

可以使用show variables like ‘slov_query_log’檢視是否開啟，如果狀态值為OFF，可以使用set GLOBAL slow_query_log = on來開啟，它會在datadir下産生一個xxx-slow.log的檔案。

設定臨界時間

配置項：long_query_time

檢視：show VARIABLES like ‘long_query_time’，機關秒

設定：set long_query_time=0.5

實操時應該從長時間設定到短的時間，即将最慢的SQL優化掉

檢視日志，一旦SQL超過了我們設定的臨界時間就會被記錄到xxx-slow.log中

關心過業務系統裡面的sql耗時嗎？統計過慢查詢嗎？對慢查詢都怎麼優化過？

在業務系統中，除了使用主鍵進行的查詢，其他的我都會在測試庫上測試其耗時，慢查詢的統計主要由運維在做，會定期将業務中的慢查詢回報給我們。

慢查詢的優化首先要搞明白慢的原因是什麼？是查詢條件沒有命中索引？是load了不需要的資料列？還是資料量太大？

是以優化也是針對這三個方向來的，

首先分析語句，看看是否load了額外的資料，可能是查詢了多餘的行并且抛棄掉了，可能是加載了許多結果中并不需要的列，對語句進行分析以及重寫。
分析語句的執行計劃，然後獲得其使用索引的情況，之後修改語句或者修改索引，使得語句可以盡可能的命中索引。
如果對語句的優化已經無法進行，可以考慮表中的資料量是否太大，如果是的話可以進行橫向或者縱向的分表。

為什麼要盡量設定一個主鍵？

主鍵是資料庫確定資料行在整張表唯一性的保障，即使業務上本張表沒有主鍵，也建議添加一個自增長的ID列作為主鍵。設定了主鍵之後，在後續的删改查的時候可能更加快速以及確定操作資料範圍安全。

主鍵使用自增ID還是UUID？

推薦使用自增ID，不要使用UUID。

因為在InnoDB存儲引擎中，主鍵索引是作為聚簇索引存在的，也就是說，主鍵索引的B+樹葉子節點上存儲了主鍵索引以及全部的資料(按照順序)，如果主鍵索引是自增ID，那麼隻需要不斷向後排列即可，如果是UUID，由于到來的ID與原來的大小不确定，會造成非常多的資料插入，資料移動，然後導緻産生很多的記憶體碎片，進而造成插入性能的下降。

總之，在資料量大一些的情況下，用自增主鍵性能會好一些。

關于主鍵是聚簇索引，如果沒有主鍵，InnoDB會選擇一個唯一鍵來作為聚簇索引，如果沒有唯一鍵，會生成一個隐式的主鍵。

字段為什麼要求定義為not null？

null值會占用更多的位元組，且會在程式中造成很多與預期不符的情況。

如果要存儲使用者的密碼散列，應該使用什麼字段進行存儲？

密碼散列，鹽，使用者身份證号等固定長度的字元串應該使用char而不是varchar來存儲，這樣可以節省空間且提高檢索效率。

優化查詢過程中的資料通路

通路資料太多導緻查詢性能下降
确定應用程式是否在檢索大量超過需要的資料，可能是太多行或列
确認MySQL伺服器是否在分析大量不必要的資料行

避免犯如下SQL語句錯誤

查詢不需要的資料。解決辦法：使用limit解決
多表關聯傳回全部列。解決辦法：指定列名
總是傳回全部列。解決辦法：避免使用SELECT *
重複查詢相同的資料。解決辦法：可以緩存資料，下次直接讀取緩存
是否在掃描額外的記錄。解決辦法：

使用explain進行分析，如果發現查詢需要掃描大量的資料，但隻傳回少數的行，可以通過如下技巧去優化：
使用索引覆寫掃描，把所有的列都放到索引中，這樣存儲引擎不需要回表擷取對應行就可以傳回結果。
改變資料庫和表的結構，修改資料表範式
重寫SQL語句，讓優化器可以以更優的方式執行查詢。

優化長難的查詢語句

一個複雜查詢還是多個簡單查詢
MySQL内部每秒能掃描記憶體中上百萬行資料，相比之下，響應資料給用戶端就要慢得多
使用盡可能小的查詢是好的，但是有時将一個大的查詢分解為多個小的查詢是很有必要的。
切分查詢
将一個大的查詢分為多個小的相同的查詢
一次性删除1000萬的資料要比一次删除1萬，暫停一會的方案更加損耗伺服器開銷。
分解關聯查詢，讓緩存的效率更高。
執行單個查詢可以減少鎖的競争。
在應用層做關聯更容易對資料庫進行拆分。
查詢效率會有大幅提升。
較少備援記錄的查詢。

優化特定類型的查詢語句

count(*)會忽略所有的列，直接統計所有列數，不要使用count(列名)
MyISAM中，沒有任何where條件的count(*)非常快。
當有where條件時，MyISAM的count統計不一定比其它引擎快。
可以使用explain查詢近似值，用近似值替代count(*)
增加彙總表
使用緩存

優化關聯查詢

确定ON或者USING子句中是否有索引。
確定GROUP BY和ORDER BY隻有一個表中的列，這樣MySQL才有可能使
用索引。

優化子查詢

用關聯查詢替代
優化GROUP BY和DISTINCT
這兩種查詢據可以使用索引來優化，是最有效的優化方法
關聯查詢中，使用辨別列分組的效率更高
如果不需要ORDER BY，進行GROUP BY時加ORDER BY NULL，MySQL不會再進行檔案排序。
WITH ROLLUP超級聚合，可以挪到應用程式處理

優化LIMIT分頁

LIMIT偏移量大的時候，查詢效率較低
可以記錄上次查詢的最大ID，下次查詢時直接根據該ID來查詢

優化UNION查詢

UNION ALL的效率高于UNION

優化WHERE子句

解題方法

對于此類考題，先說明如何定位低效SQL語句，然後根據SQL語句可能低效的原因做排查，先從索引着手，如果索引沒有問題，考慮以上幾個方面，資料通路的問題，長難查詢句的問題還是一些特定類型優化的問題，逐一回答。

SQL語句優化的一些方法？

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

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

select id from t where num is null
-- 可以在num上設定預設值0，確定表中num列沒有null值，然後這樣查詢：
select id from t where num=

3.應盡量避免在 where 子句中使用!=或<>操作符，否則引擎将放棄使用索引而進行全表掃描。

4.應盡量避免在 where 子句中使用or 來連接配接條件，否則将導緻引擎放棄使用索引而進行全表掃描，如：

select id from t where num=10 or num=20
-- 可以這樣查詢：
select id from t where num=10 union all select id from t where num=20

5.in 和 not in 也要慎用，否則會導緻全表掃描，如：

select id from t where num in(1,2,3) 
-- 對于連續的數值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3

6.下面的查詢也将導緻全表掃描：select id from t where name like ‘%李%’若要提高效率，可以考慮全文檢索。

7.如果在 where 子句中使用參數，也會導緻全表掃描。因為SQL隻有在運作時才會解析局部變量，但優化程式不能将通路計劃的選擇推遲到運作時；它必須在編譯時進行選擇。然而，如果在編譯時建立通路計劃，變量的值還是未知的，因而無法作為索引選擇的輸入項。如下面語句将進行全表掃描：

select id from t where num=@num
-- 可以改為強制查詢使用索引：
select id from t with(index(索引名)) where num=@num

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

select id from t where num/2=100
-- 應改為:
select id from t where num=100*2

9.應盡量避免在where子句中對字段進行函數操作，這将導緻引擎放棄使用索引而進行全表掃描。如：

select id from t where substring(name,1,3)=’abc’
-- name以abc開頭的id應改為:
select id from t where name like ‘abc%’

10.不要在 where 子句中的“=”左邊進行函數、算術運算或其他表達式運算，否則系統将可能無法正确使用索引。

資料庫優化

為什麼要優化

系統的吞吐量瓶頸往往出現在資料庫的通路速度上

随着應用程式的運作，資料庫的中的資料會越來越多，處理時間會相應變慢

資料是存放在磁盤上的，讀寫速度無法和記憶體相比

優化原則：減少系統瓶頸，減少資源占用，增加系統的反應速度。

資料庫結構優化

一個好的資料庫設計方案對于資料庫的性能往往會起到事半功倍的效果。

需要考慮資料備援、查詢和更新的速度、字段的資料類型是否合理等多方面的内容。

将字段很多的表分解成多個表

對于字段較多的表，如果有些字段的使用頻率很低，可以将這些字段分離出來形成新表。

因為當一個表的資料量很大時，會由于使用頻率低的字段的存在而變慢。

增加中間表

對于需要經常聯合查詢的表，可以建立中間表以提高查詢效率。

通過建立中間表，将需要通過聯合查詢的資料插入到中間表中，然後将原來的聯合查詢改為對中間表的查詢。

增加備援字段

設計資料表時應盡量遵循範式理論的規約，盡可能的減少備援字段，讓資料庫設計看起來精緻、優雅。但是，合理的加入備援字段可以提高查詢速度。

表的規範化程度越高，表和表之間的關系越多，需要連接配接查詢的情況也就越多，性能也就越差。

注意：

備援字段的值在一個表中修改了，就要想辦法在其他表中更新，否則就會導緻資料不一緻的問題。

MySQL資料庫cpu飙升到500%的話他怎麼處理？

當 cpu 飙升到 500%時，先用作業系統指令 top 指令觀察是不是 mysqld 占用導緻的，如果不是，找出占用高的程序，并進行相關處理。

如果是 mysqld 造成的， show processlist，看看裡面跑的 session 情況，是不是有消耗資源的 sql 在運作。找出消耗高的 sql，看看執行計劃是否準确， index 是否缺失，或者實在是資料量太大造成。

一般來說，肯定要 kill 掉這些線程(同時觀察 cpu 使用率是否下降)，等進行相應的調整(比如說加索引、改 sql、改記憶體參數)之後，再重新跑這些 SQL。

也有可能是每個 sql 消耗資源并不多，但是突然之間，有大量的 session 連進來導緻 cpu 飙升，這種情況就需要跟應用一起來分析為何連接配接數會激增，再做出相應的調整，比如說限制連接配接數等

大表怎麼優化？某個表有近千萬資料，CRUD比較慢，如何優化？分庫分表了是怎麼做的？分表分庫了有什麼問題？有用到中間件麼？他們的原理知道麼？

當MySQL單表記錄數過大時，資料庫的CRUD性能會明顯下降，一些常見的優化措施如下：

限定資料的範圍：務必禁止不帶任何限制資料範圍條件的查詢語句。比如：
我們當使用者在查詢訂單曆史的時候，我們可以控制在一個月的範圍内。；
讀/寫分離：經典的資料庫拆分方案，主庫負責寫，從庫負責讀；
緩存：使用MySQL的緩存，另外對重量級、更新少的資料可以考慮使用應用級别的緩存；
還有就是通過分庫分表的方式進行優化，主要有垂直分表和水準分表

垂直分區：

根據資料庫裡面資料表的相關性進行拆分。例如，使用者表中既有使用者的登入資訊又有使用者的基本資訊，可以将使用者表拆分成兩個單獨的表，甚至放到單獨的庫做分庫。

簡單來說垂直拆分是指資料表列的拆分，把一張列比較多的表拆分為多張表。如下圖所示，這樣來說大家應該就更容易了解了。

MySQL資料庫面試題資料庫基礎知識資料類型引擎索引事務視圖存儲過程與函數觸發器常用SQL語句SQL優化資料庫優化

垂直拆分的優點：可以使得行資料變小，在查詢時減少讀取的Block數，減少I/O次數。此外，垂直分區可以簡化表的結構，易于維護。

垂直拆分的缺點：主鍵會出現備援，需要管理備援列，并會引起Join操作，可以通過在應用層進行Join來解決。此外，垂直分區會讓事務變得更加複雜；

垂直分表

把主鍵和一些列放在一個表，然後把主鍵和另外的列放在另一個表中

MySQL資料庫面試題資料庫基礎知識資料類型引擎索引事務視圖存儲過程與函數觸發器常用SQL語句SQL優化資料庫優化

适用場景

1、如果一個表中某些列常用，另外一些列不常用

2、可以使資料行變小，一個資料頁能存儲更多資料，查詢時減少I/O次數

缺點

有些分表的政策基于應用層的邏輯算法，一旦邏輯算法改變，整個分表邏輯都會改變，擴充性較差
對于應用層來說，邏輯算法增加開發成本
管理備援列，查詢所有資料需要join操作

水準分區：

保持資料表結構不變，通過某種政策存儲資料分片。這樣每一片資料分散到不同的表或者庫中，達到了分布式的目的。水準拆分可以支撐非常大的資料量。

水準拆分是指資料表行的拆分，表的行數超過200萬行時，就會變慢，這時可以把一張的表的資料拆成多張表來存放。舉個例子：我們可以将使用者資訊表拆分成多個使用者資訊表，這樣就可以避免單一表資料量過大對性能造成影響。

MySQL資料庫面試題資料庫基礎知識資料類型引擎索引事務視圖存儲過程與函數觸發器常用SQL語句SQL優化資料庫優化

水品拆分可以支援非常大的資料量。需要注意的一點是:分表僅僅是解決了單一表資料過大的問題，但由于表的資料還是在同一台機器上，其實對于提升MySQL并發能力沒有什麼意義，是以水準拆分最好分庫。

水準拆分能夠支援非常大的資料量存儲，應用端改造也少，但分片事務難以解決，跨界點Join性能較差，邏輯複雜。

《Java工程師修煉之道》的作者推薦盡量不要對資料進行分片，因為拆分會帶來邏輯、部署、運維的各種複雜度，一般的資料表在優化得當的情況下支撐千萬以下的資料量是沒有太大問題的。如果實在要分片，盡量選擇用戶端分片架構，這樣可以減少一次和中間件的網絡I/O。

水準分表：

表很大，分割後可以降低在查詢時需要讀的資料和索引的頁數，同時也降低了索引的層數，提高查詢次數

MySQL資料庫面試題資料庫基礎知識資料類型引擎索引事務視圖存儲過程與函數觸發器常用SQL語句SQL優化資料庫優化

适用場景

1、表中的資料本身就有獨立性，例如表中分表記錄各個地區的資料或者不同時期的資料，特别是有些資料常用，有些不常用。

2、需要把資料存放在多個媒體上。

水準切分的缺點

1、給應用增加複雜度，通常查詢時需要多個表名，查詢所有資料都需UNION操作

2、在許多資料庫應用中，這種複雜度會超過它帶來的優點，查詢時會增加讀一個索引層的磁盤次數

下面補充一下資料庫分片的兩種常見方案：

用戶端代理：分片邏輯在應用端，封裝在jar包中，通過修改或者封裝JDBC層來實作。當當網的 Sharding-JDBC 、阿裡的TDDL是兩種比較常用的實作。

中間件代理：在應用和資料中間加了一個代理層。分片邏輯統一維護在中間件服務中。我們現在談的 Mycat 、360的Atlas、網易的DDB等等都是這種架構的實作。

分庫分表後面臨的問題

事務支援分庫分表後，就成了分布式事務了。如果依賴資料庫本身的分布式事務管理功能去執行事務，将付出高昂的性能代價；如果由應用程式去協助控制，形成程式邏輯上的事務，又會造成程式設計方面的負擔。
跨庫join

隻要是進行切分，跨節點Join的問題是不可避免的。但是良好的設計和切分卻可以減少此類情況的發生。解決這一問題的普遍做法是分兩次查詢實作。在第一次查詢的結果集中找出關聯資料的id,根據這些id發起第二次請求得到關聯資料。分庫分表方案産品
跨節點的count,order by,group by以及聚合函數問題這些是一類問題，因為它們都需要基于全部資料集合進行計算。多數的代理都不會自動處理合并工作。解決方案：與解決跨節點join問題的類似，分别在各個節點上得到結果後在應用程式端進行合并。和join不同的是每個結點的查詢可以并行執行，是以很多時候它的速度要比單一大表快很多。但如果結果集很大，對應用程式記憶體的消耗是一個問題。
資料遷移，容量規劃，擴容等問題來自淘寶綜合業務平台團隊，它利用對2的倍數取餘具有向前相容的特性（如對4取餘得1的數對2取餘也是1）來配置設定資料，避免了行級别的資料遷移，但是依然需要進行表級别的遷移，同時對擴容規模和分表數量都有限制。總得來說，這些方案都不是十分的理想，多多少少都存在一些缺點，這也從一個側面反映出了Sharding擴容的難度。
ID問題

一旦資料庫被切分到多個實體結點上，我們将不能再依賴資料庫自身的主鍵生成機制。一方面，某個分區資料庫自生成的ID無法保證在全局上是唯一的；另一方面，應用程式在插入資料之前需要先獲得ID,以便進行SQL路由. 一些常見的主鍵生成政策

UUID 使用UUID作主鍵是最簡單的方案，但是缺點也是非常明顯的。由于UUID非常的長，除占用大量存儲空間外，最主要的問題是在索引上，在建立索引和基于索引進行查詢時都存在性能問題。 Twitter的分布式自增ID算法Snowflake 在分布式系統中，需要生成全局UID的場合還是比較多的，twitter的snowflake解決了這種需求，實作也還是很簡單的，除去配置資訊，核心代碼就是毫秒級時間41位機器ID 10位毫秒内序列12位。

跨分片的排序分頁

般來講，分頁時需要按照指定字段進行排序。當排序字段就是分片字段的時候，我們通過分片規則可以比較容易定位到指定的分片，而當排序字段非分片字段的時候，情況就會變得比較複雜了。為了最終結果的準确性，我們需要在不同的分片節點中将資料進行排序并傳回，并将不同分片傳回的結果集進行彙總和再次排序，最後再傳回給使用者。如下圖所示：

MySQL資料庫面試題資料庫基礎知識資料類型引擎索引事務視圖存儲過程與函數觸發器常用SQL語句SQL優化資料庫優化

MySQL的複制原理以及流程

主從複制：将主資料庫中的DDL和DML操作通過二進制日志（BINLOG）傳輸到從資料庫上，然後将這些日志重新執行（重做）；進而使得從資料庫的資料與主資料庫保持一緻。

主從複制的作用

主資料庫出現問題，可以切換到從資料庫。
可以進行資料庫層面的讀寫分離。
可以在從資料庫上進行日常備份。

MySQL主從複制解決的問題

資料分布：随意開始或停止複制，并在不同地理位置分布資料備份
負載均衡：降低單個伺服器的壓力
高可用和故障切換：幫助應用程式避免單點失敗
更新測試：可以用更高版本的MySQL作為從庫

MySQL主從複制工作原理

在主庫上把資料更高記錄到二進制日志
從庫将主庫的日志複制到自己的中繼日志
從庫讀取中繼日志的事件，将其重放到從庫資料中
基本原理流程，3個線程以及之間的關聯

主：binlog線程——記錄下所有改變了資料庫資料的語句，放進master上的binlog中；

從：io線程——在使用start slave 之後，負責從master上拉取 binlog 内容，放進自己的relay log中；

從：sql執行線程——執行relay log中的語句；

複制過程

MySQL資料庫面試題資料庫基礎知識資料類型引擎索引事務視圖存儲過程與函數觸發器常用SQL語句SQL優化資料庫優化

Binary log：主資料庫的二進制日志

Relay log：從伺服器的中繼日志

第一步：master在每個事務更新資料完成之前，将該操作記錄串行地寫入到binlog檔案中。

第二步：salve開啟一個I/O Thread，該線程在master打開一個普通連接配接，主要工作是binlog dump process。如果讀取的進度已經跟上了master，就進入睡眠狀态并等待master産生新的事件。I/O線程最終的目的是将這些事件寫入到中繼日志中。

第三步：SQL Thread會讀取中繼日志，并順序執行該日志中的SQL事件，進而與主資料庫中的資料保持一緻。

讀寫分離有哪些解決方案？

讀寫分離是依賴于主從複制，而主從複制又是為讀寫分離服務的。因為主從複制要求slave不能寫隻能讀（如果對slave執行寫操作，那麼show slave status将會呈現Slave_SQL_Running=NO，此時你需要按照前面提到的手動同步一下slave）。

方案一

使用mysql-proxy代理

優點：直接實作讀寫分離和負載均衡，不用修改代碼，master和slave用一樣的帳号，mysql官方不建議實際生産中使用

缺點：降低性能，不支援事務

方案二

使用AbstractRoutingDataSource+aop+annotation在dao層決定資料源。

如果采用了mybatis，可以将讀寫分離放在ORM層，比如mybatis可以通過mybatis plugin攔截sql語句，所有的insert/update/delete都通路master庫，所有的select 都通路salve庫，這樣對于dao層都是透明。 plugin實作時可以通過注解或者分析語句是讀寫方法來標明主從庫。不過這樣依然有一個問題，也就是不支援事務，是以我們還需要重寫一下

DataSourceTransactionManager，将read-only的事務扔進讀庫，其餘的有讀有寫的扔進寫庫。

方案三

使用AbstractRoutingDataSource+aop+annotation在service層決定資料源，可以支援事務.

缺點：類内部方法通過this.xx()方式互相調用時，aop不會進行攔截，需進行特殊處理。

備份計劃，mysqldump以及xtranbackup的實作原理

(1)備份計劃

視庫的大小來定，一般來說 100G 内的庫，可以考慮使用 mysqldump 來做，因為 mysqldump更加輕巧靈活，備份時間選在業務低峰期，可以每天進行都進行全量備份(mysqldump 備份出來的檔案比較小，壓縮之後更小)。

100G 以上的庫，可以考慮用 xtranbackup 來做，備份速度明顯要比 mysqldump 要快。一般是選擇一周一個全備，其餘每天進行增量備份，備份時間為業務低峰期。

(2)備份恢複時間

實體備份恢複快，邏輯備份恢複慢

這裡跟機器，尤其是硬碟的速率有關系，以下列舉幾個僅供參考

20G的2分鐘（mysqldump）

80G的30分鐘(mysqldump)

111G的30分鐘（mysqldump)

288G的3小時（xtra)

3T的4小時（xtra)

邏輯導入時間一般是備份時間的5倍以上

(3)備份恢複失敗如何處理

首先在恢複之前就應該做足準備工作，避免恢複的時候出錯。比如說備份之後的有效性檢查、權限檢查、空間檢查等。如果萬一報錯，再根據報錯的提示來進行相應的調整。

(4)mysqldump和xtrabackup實作原理

mysqldump

mysqldump 屬于邏輯備份。加入–single-transaction 選項可以進行一緻性備份。背景程序會先設定 session 的事務隔離級别為 RR(SET SESSION TRANSACTION ISOLATION LEVELREPEATABLE READ)，之後顯式開啟一個事務(START TRANSACTION )，這樣就保證了該事務裡讀到的資料都是事務事務時候的快照。之後再把表的資料讀取出來。如果加上–master-data=1 的話，在剛開始的時候還會加一個資料庫的讀鎖(FLUSH TABLES WITH READ LOCK),等開啟事務後，再記錄下資料庫此時 binlog 的位置(showmaster status)，馬上解鎖，再讀取表的資料。等所有的資料都已經導完，就可以結束事務

Xtrabackup:

xtrabackup 屬于實體備份，直接拷貝表空間檔案，同時不斷掃描産生的 redo 日志并儲存下來。最後完成 innodb 的備份後，會做一個 flush engine logs 的操作(老版本在有 bug，在5.6 上不做此操作會丢資料)，確定所有的 redo log 都已經落盤(涉及到事務的兩階段送出

概念，因為 xtrabackup 并不拷貝 binlog，是以必須保證所有的 redo log 都落盤，否則可能會丢最後一組送出事務的資料)。這個時間點就是 innodb 完成備份的時間點，資料檔案雖然不是一緻性的，但是有這段時間的 redo 就可以讓資料檔案達到一緻性(恢複的時候做的事

情)。然後還需要 flush tables with read lock，把 myisam 等其他引擎的表給備份出來，備份完後解鎖。這樣就做到了完美的熱備。

資料表損壞的修複方式有哪些？

使用 myisamchk 來修複，具體步驟：

1）修複前将mysql服務停止。

2）打開指令行方式，然後進入到mysql的/bin目錄。

3）執行myisamchk –recover 資料庫所在路徑/*.MYI

使用repair table 或者 OPTIMIZE table指令來修複，REPAIR TABLE table_name 修複表 OPTIMIZE TABLE table_name 優化表 REPAIR TABLE 用于修複被破壞的表。 OPTIMIZE TABLE 用于回收閑置的資料庫空間，當表上的資料行被删除時，所占據的磁盤空間并沒有立即被回收，使用了OPTIMIZE TABLE指令後這些空間将被回收，并且對磁盤上的資料行進行重排（注意：是磁盤上，而非資料庫）

本文轉載自thinkwon