## 數據庫基礎知識 ### 為什么要使用數據庫 * **數據保存在內存** * 優點： 存取速度快 * 缺點： 數據不能永久保存 * **數據保存在文件** * 優點： 數據永久保存 * 缺點：1、速度比內存操作慢，頻繁的IO操作。2、查詢數據不方便 * **數據保存在數據庫** * 數據永久保存 * 使用SQL語句，查詢方便效率高。 * 管理數據方便 ### 什么是SQL？ * 結構化查詢語言(Structured Query Language)簡稱SQL，是一種數據庫查詢語言。 `作用：用于存取數據、查詢、更新和管理關系數據庫系統。` ### 什么是MySQL? * MySQL是一個關系型數據庫管理系統，由瑞典MySQL AB 公司開發，屬于 Oracle 旗下產品。MySQL 是最流行的關系型數據庫管理系統之一，在 WEB 應用方面，MySQL是最好的 RDBMS (Relational Database Management System，關系數據庫管理系統) 應用軟件之一。在Java企業級開發中非常常用，因為 MySQL 是開源免費的，并且方便擴展。 ### MySql, Oracle，Sql Service的區別 1. Sql Service只能在Windows上使用，而MySql和Oracle可以在其他系統上使用， 而且可以支持數據庫不同系統之間的移植 2. MySql開源免費的，Sql Service和Oracle要錢。 3. 我從小到大排序哈，MySql很小，Sql Service居中，Oracle最大 4. Oracle支持大并發量，大訪問量，Sql Service還行，而MySql的話壓力沒這么大，因此現在的MySql的話最好是要使用集群或者緩存來搭配使用 5. Oracle支持多用戶不同權限來進行操作，而MySql只要有登錄權限就可操作全部數據庫 6. 安裝所用的空間差別也是很大的，Mysql安裝完后才幾百M而Oracle有幾G左右，且使用的時候Oracle占用特別大的內存空間和其他機器性能。 7. 做分頁的話，MySql使用Limit，Sql Service使用top，Oracle使用row 8. Oracle沒有自動增長類型，Mysql和Sql Service一般使用自動增長類型 ### 數據庫三大范式是什么 * 第一范式：每個列都不可以再拆分。 * 第二范式：在第一范式的基礎上，非主鍵列完全依賴于主鍵，而不能是依賴于主鍵的一部分。 * 第三范式：在第二范式的基礎上，非主鍵列只依賴于主鍵，不依賴于其他非主鍵。 `在設計數據庫結構的時候，要盡量遵守三范式，如果不遵守，必須有足夠的理由。比如性能。事實上我們經常會為了性能而妥協數據庫的設計。` ### 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級別也做了一些優化，當表結構發生變化的時候，會記錄語句而不是逐行記錄。` ### 數據庫經常使用的函數 * count(\*/column)：返回行數 * sum(column)： 返回指定列中唯一值的和 * max(column)：返回指定列或表達式中的數值最大值 * min(column)：返回指定列或表達式中的數值最小值 * avg(column)：返回指定列或表達式中的數值平均值 * date（Expression）: 返回指定表達式代表的日期值 ...... ## 數據類型 ### mysql有哪些數據類型 > | 分類 | 類型名稱 | 說明 | > | --- | --- | --- | > | 整數類型 | tinyInt | 很小的整數(8位二進制) | > | 整數類型 | smallint | 小的整數(16位二進制) | > | 整數類型 | mediumint | 中等大小的整數(24位二進制) | > | 整數類型 | int(integer) | 普通大小的整數(32位二進制) | > | 小數類型 | float | 單精度浮點數 | > | 小數類型 | double | 雙精度浮點數 | > | 小數類型 | decimal(m,d) | 壓縮嚴格的定點數 | > | 日期類型 | year | YYYY 1901~2155 | > | 日期類型 | time | HH:MM:SS -838:59:59~838:59:59 | > | 日期類型 | date | YYYY-MM-DD 1000-01-01~9999-12-3 | > | 日期類型 | datetime | YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00~ 9999-12-31 23:59:59 | > | 日期類型 | timestamp | YYYY-MM-DD HH:MM:SS 19700101 00:00:01 UTC~2038-01-19 03:14:07UTC | > | 文本、二進制類型 | CHAR(M) | M為0~255之間的整數 | > | 文本、二進制類型 | VARCHAR(M) | M為0~65535之間的整數 | > | 文本、二進制類型 | TINYBLOB | 允許長度0~255字節 | > | 文本、二進制類型 | BLOB | 允許長度0~65535字節 | > | 文本、二進制類型 | MEDIUMBLOB | 允許長度0~167772150字節 | > | 文本、二進制類型 | LONGBLOB | 允許長度0~4294967295字節 | > | 文本、二進制類型 | TINYTEXT | 允許長度0~255字節 | > | 文本、二進制類型 | TEXT | 允許長度0~65535字節 | > | 文本、二進制類型 | MEDIUMTEXT | 允許長度0~167772150字節 | > | 文本、二進制類型 | LONGTEXT | 允許長度0~4294967295字節 | > | 文本、二進制類型 | VARBINARY(M) | 允許長度0~M個字節的變長字節字符串 | > | 文本、二進制類型 | BINARY(M) | 允許長度0~M個字節的定長字節字符串 | * `1、整數類型`，包括TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分別表示1字節、2字節、3字節、4字節、8字節整數。任何整數類型都可以加上UNSIGNED屬性，表示數據是無符號的，即非負整數。 `長度`：整數類型可以被指定長度，例如：INT(11)表示長度為11的INT類型。長度在大多數場景是沒有意義的，它不會限制值的合法范圍，只會影響顯示字符的個數，而且需要和UNSIGNED ZEROFILL屬性配合使用才有意義。 `例子`，假定類型設定為INT(5)，屬性為UNSIGNED ZEROFILL，如果用戶插入的數據為12的話，那么數據庫實際存儲數據為00012。 * `2、實數類型`，包括FLOAT、DOUBLE、DECIMAL。 DECIMAL可以用于存儲比BIGINT還大的整型，能存儲精確的小數。 而FLOAT和DOUBLE是有取值范圍的，并支持使用標準的浮點進行近似計算。 計算時FLOAT和DOUBLE相比DECIMAL效率更高一些，DECIMAL你可以理解成是用字符串進行處理。 * `3、字符串類型`，包括VARCHAR、CHAR、TEXT、BLOB VARCHAR用于存儲可變長字符串，它比定長類型更節省空間。 VARCHAR使用額外1或2個字節存儲字符串長度。列長度小于255字節時，使用1字節表示，否則使用2字節表示。 VARCHAR存儲的內容超出設置的長度時，內容會被截斷。 CHAR是定長的，根據定義的字符串長度分配足夠的空間。 CHAR會根據需要使用空格進行填充方便比較。 CHAR適合存儲很短的字符串，或者所有值都接近同一個長度。 CHAR存儲的內容超出設置的長度時，內容同樣會被截斷。 **使用策略：** 對于經常變更的數據來說，CHAR比VARCHAR更好，因為CHAR不容易產生碎片。 對于非常短的列，CHAR比VARCHAR在存儲空間上更有效率。 使用時要注意只分配需要的空間，更長的列排序時會消耗更多內存。 盡量避免使用TEXT/BLOB類型，查詢時會使用臨時表，導致嚴重的性能開銷。 * `4、枚舉類型（ENUM）`，把不重復的數據存儲為一個預定義的集合。 有時可以使用ENUM代替常用的字符串類型。 ENUM存儲非常緊湊，會把列表值壓縮到一個或兩個字節。 ENUM在內部存儲時，其實存的是整數。 盡量避免使用數字作為ENUM枚舉的常量，因為容易混亂。 排序是按照內部存儲的整數 * `5、日期和時間類型`，盡量使用timestamp，空間效率高于datetime， 用整數保存時間戳通常不方便處理。 如果需要存儲微妙，可以使用bigint存儲。 看到這里，這道真題是不是就比較容易回答了。 ## 引擎 ### MySQL存儲引擎MyISAM與InnoDB區別 * 存儲引擎Storage engine：MySQL中的數據、索引以及其他對象是如何存儲的，是一套文件系統的實現。 * 常用的存儲引擎有以下： * **Innodb引擎**：Innodb引擎提供了對數據庫ACID事務的支持。并且還提供了行級鎖和外鍵的約束。它的設計的目標就是處理大數據容量的數據庫系統。 * **MyIASM引擎**(原本Mysql的默認引擎)：不提供事務的支持，也不支持行級鎖和外鍵。 * **MEMORY引擎**：所有的數據都在內存中，數據的處理速度快，但是安全性不高。 **MyISAM與InnoDB區別** > | 比較 | MyISAM | Innodb | > | --- | --- | --- | > | 存儲結構 | 每張表被存放在三個文件：frm-表格定義、MYD(MYData)-數據文件、MYI(MYIndex)-索引文件 | 所有的表都保存在同一個數據文件中（也可能是多個文件，或者是獨立的表空間文件），InnoDB表的大小只受限于操作系統文件的大小，一般為2GB | > | 存儲空間 | MyISAM可被壓縮，存儲空間較小 | InnoDB的表需要更多的內存和存儲，它會在主內存中建立其專用的緩沖池用于高速緩沖數據和索引 | > | 可移植性、備份及恢復 | 由于MyISAM的數據是以文件的形式存儲，所以在跨平臺的數據轉移中會很方便。在備份和恢復時可單獨針對某個表進行操作 | 免費的方案可以是拷貝數據文件、備份 binlog，或者用 mysqldump，在數據量達到幾十G的時候就相對痛苦了 | > | 文件格式 | 數據和索引是分別存儲的，數據`.MYD`，索引`.MYI` | 數據和索引是集中存儲的，`.ibd` | > | 記錄存儲順序 | 按記錄插入順序保存 | 按主鍵大小有序插入 | > | 外鍵 | 不支持 | 支持 | > | 事務 | 不支持 | 支持 | > | 鎖支持（鎖是避免資源爭用的一個機制，MySQL鎖對用戶幾乎是透明的） | 表級鎖定 | 行級鎖定、表級鎖定，鎖定力度小并發能力高 | > | SELECT | MyISAM更優 | \-- | > | INSERT、UPDATE、DELETE | \-- | InnoDB更優 | > | select count(\*) | myisam更快，因為myisam內部維護了一個計數器，可以直接調取。 | | > | 索引的實現方式 | B+樹索引，myisam 是堆表 | B+樹索引，Innodb 是索引組織表 | > | 哈希索引 | 不支持 | 支持 | > | 全文索引 | 支持 | 不支持 | ### MyISAM索引與InnoDB索引的區別？ * InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。 * InnoDB的主鍵索引的葉子節點存儲著行數據，因此主鍵索引非常高效。 * MyISAM索引的葉子節點存儲的是行數據地址，需要再尋址一次才能得到數據。 * InnoDB非主鍵索引的葉子節點存儲的是主鍵和其他帶索引的列數據，因此查詢時做到覆蓋索引會非常高效。 ### InnoDB引擎的4大特性 * 插入緩沖（insert buffer) * 二次寫(double write) * 自適應哈希索引(ahi) * 預讀(read ahead) ### 存儲引擎選擇 * 如果沒有特別的需求，使用默認的`Innodb`即可。 * MyISAM：以讀寫插入為主的應用程序，比如博客系統、新聞門戶網站。 * Innodb：更新（刪除）操作頻率也高，或者要保證數據的完整性；并發量高，支持事務和外鍵。比如OA自動化辦公系統。 ## 索引 ### 什么是索引？ * 索引是一種特殊的文件(InnoDB數據表上的索引是表空間的一個組成部分)，它們包含著對數據表里所有記錄的引用指針。 * 索引是一種數據結構。數據庫索引，是數據庫管理系統中一個排序的數據結構，以協助快速查詢、更新數據庫表中數據。索引的實現通常使用B樹及其變種B+樹。 * 更通俗的說，索引就相當于目錄。為了方便查找書中的內容，通過對內容建立索引形成目錄。索引是一個文件，它是要占據物理空間的。 ### 索引有哪些優缺點？ **索引的優點** * 可以大大加快數據的檢索速度，這也是創建索引的最主要的原因。 * 通過使用索引，可以在查詢的過程中，使用優化隱藏器，提高系統的性能。 **索引的缺點** * 時間方面：創建索引和維護索引要耗費時間，具體地，當對表中的數據進行增加、刪除和修改的時候，索引也要動態的維護，會降低增/改/刪的執行效率； * 空間方面：索引需要占物理空間。 ### 怎么創建索引的，有什么好處，有哪些分類 1. 創建索引的語法：create index depe\_unique\_ide on depe(dept\_no) tablespace idx\_ 2. 創建索引可以增加查詢速度，唯一索引可以保證數據庫列的一致性，可以確定表與表之間的連接 3. 索引的分類： ????????????????????????邏輯分類：單列索引，復合索引，唯一索引，非唯一索引，函數索引 ????????????????????????物理分類：B數索引，反向鍵索引，位圖索引 ### 簡述有哪些索引和作用 `索引的作用：通過索引可以大大的提高數據庫的檢索速度，改善數據庫性能` 1. 唯一索引：不允許有倆行具有相同的值 2. 主鍵索引：為了保持數據庫表與表之間的關系 3. 聚集索引：表中行的物理順序與鍵值的邏輯（索引）順序相同。 4. 非聚集索引：聚集索引和非聚集索引的根本區別是表記錄的排列順序和與索引的排列順序是否一致 5. 復合索引：在創建索引時，并不是只能對一列進行創建索引，可以與主鍵一樣，講多個組合為索引 6. 全文索引： 全文索引為在字符串數據中進行復雜的詞搜索提供有效支持 ### 索引使用場景 1. 當數據多且字段值有相同的值得時候用普通索引。 2. 當字段多且字段值沒有重復的時候用唯一索引。 3. 當有多個字段名都經常被查詢的話用復合索引。 4. 普通索引不支持空值，唯一索引支持空值。 5. 但是，若是這張表增刪改多而查詢較少的話，就不要創建索引了，因為如果你給一列創建了索引，那么對該列進行增刪改的時候，都會先訪問這一列的索引， 6. 若是增，則在這一列的索引內以新填入的這個字段名的值為名創建索引的子集， 7. 若是改，則會把原來的刪掉，再添入一個以這個字段名的新值為名創建索引的子集， 8. 若是刪，則會把索引中以這個字段為名的索引的子集刪掉。 9. 所以，會對增刪改的執行減緩速度， 10. 所以，若是這張表增刪改多而查詢較少的話，就不要創建索引了。 11. 更新太頻繁地字段不適合創建索引。 12. 不會出現在where條件中的字段不該建立索引。 ### 主鍵索引與唯一索引的區別 1. 主鍵是一種約束，唯一索引是一種索引，兩者在本質上是不同的。 2. 主鍵創建后一定包含一個唯一性索引，唯一性索引并不一定就是主鍵。 3. 唯一性索引列允許空值，而主鍵列不允許為空值。 4. 主鍵列在創建時，已經默認為空值 ++ 唯一索引了。 5. 一個表最多只能創建一個主鍵，但可以創建多個唯一索引。 6. 主鍵更適合那些不容易更改的唯一標識，如自動遞增列、身份證號等。 7. 主鍵可以被其他表引用為外鍵，而唯一索引不能。 ？ ### 索引有哪幾種類型？ **主鍵索引:** 數據列不允許重復，不允許為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樹索引）  * 查詢方式： * 主鍵索引區: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鍵下以鏈表形式存儲。當然這只是簡略模擬圖。  ### 索引的基本原理 * 索引用來快速地尋找那些具有特定值的記錄。如果沒有索引，一般來說執行查詢時遍歷整張表。 * 索引的原理很簡單，就是把無序的數據變成有序的查詢 1. 把創建了索引的列的內容進行排序 2. 對排序結果生成倒排表 3. 在倒排表內容上拼上數據地址鏈 4. 在查詢的時候，先拿到倒排表內容，再取出數據地址鏈，從而拿到具體數據 ### 索引算法有哪些？ * 索引算法有 BTree算法和Hash算法 **1、BTree算法** * BTree是最常用的mysql數據庫索引算法，也是mysql默認的算法。因為它不僅可以被用在=,>,>=,<,<=和between這些比較操作符上，而且還可以用于like操作符，只要它的查詢條件是一個不以通配符開頭的常量， 例如： ~~~ -- 只要它的查詢條件是一個不以通配符開頭的常量 select * from user where name like 'jack%'; -- 如果一通配符開頭，或者沒有使用常量，則不會使用索引，例如： select * from user where name like '%jack'; 復制代碼 ~~~ **2、Hash算法** * Hash Hash索引只能用于對等比較，例如=,（相當于=）操作符。由于是一次定位數據，不像BTree索引需要從根節點到枝節點，最后才能訪問到頁節點這樣多次IO訪問，所以檢索效率遠高于BTree索引。 ### 索引設計的原則？ 1. 適合索引的列是出現在where子句中的列，或者連接子句中指定的列 2. 基數較小的類，索引效果較差，沒有必要在此列建立索引 3. 使用短索引，如果對長字符串列進行索引，應該指定一個前綴長度，這樣能夠節省大量索引空間 4. 不要過度索引。索引需要額外的磁盤空間，并降低寫操作的性能。在修改表內容的時候，索引會進行更新甚至重構，索引列越多，這個時間就會越長。所以只保持需要的索引有利于查詢即可。 ### 創建索引的原則 * 索引雖好，但也不是無限制的使用，最好符合一下幾個原則 1. 最左前綴匹配原則，組合索引非常重要的原則，mysql會一直向右匹配直到遇到范圍查詢(>、 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命令去增加索引 ~~~ ALTER TABLE table_name ADD INDEX index_name (column_list); 復制代碼 ~~~ * ALTER TABLE用來創建普通索引、UNIQUE索引或PRIMARY KEY索引。 * 其中table\_name是要增加索引的表名，column\_list指出對哪些列進行索引，多列時各列之間用逗號分隔。 * 索引名index\_name可自己命名，缺省時，MySQL將根據第一個索引列賦一個名稱。另外，ALTER TABLE允許在單個語句中更改多個表，因此可以在同時創建多個索引。 * 第三種方式：使用CREATE INDEX命令創建 ~~~ CREATE INDEX index_name ON table_name (column_list); 復制代碼 ~~~ * 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`（因為主鍵只有一個）。這里值得注意的是，如果主鍵自增長，那么不能直接執行此操作（自增長依賴于主鍵索引）：  * 需要取消自增長再行刪除： ~~~ 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官方手冊得知刪除數據的速度和創建的索引數量是成正比的。 1. 所以我們想要刪除百萬數據的時候可以先刪除索引（此時大概耗時三分多鐘） 2. 然后刪除其中無用數據（此過程需要不到兩分鐘） 3. 刪除完成后重新創建索引(此時數據較少了)創建索引也非常快，約十分鐘左右。 4. 與之前的直接刪除絕對是要快速很多，更別說萬一刪除中斷,一切刪除會回滾。那更是坑了。 ### 前綴索引 * 語法：`index(field(10))`，使用字段值的前10個字符建立索引，默認是使用字段的全部內容建立索引。 * 前提：前綴的標識度高。比如密碼就適合建立前綴索引，因為密碼幾乎各不相同。 * 實操的難度：在于前綴截取的長度。 * 我們可以利用`select count(*)/count(distinct left(password,prefixLen));`，通過從調整`prefixLen`的值（從1自增）查看不同前綴長度的一個平均匹配度，接近1時就可以了（表示一個密碼的前`prefixLen`個字符幾乎能確定唯一一條記錄） ### 什么是最左前綴原則？什么是最左匹配原則 * 顧名思義，就是最左優先，在創建多列索引時，要根據業務需求，where子句中使用最頻繁的一列放在最左邊。 * 最左前綴匹配原則，非常重要的原則，mysql會一直向右匹配直到遇到范圍查詢(>、 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樹的葉子節點各自獨立。  ### 使用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中，在聚簇索引之上創建的索引稱之為輔助索引，輔助索引訪問數據總是需要二次查找，非聚簇索引都是輔助索引，像復合索引、前綴索引、唯一索引，輔助索引葉子節點存儲的不再是行的物理位置，而是主鍵值 `何時使用聚簇索引與非聚簇索引`  ### 非聚簇索引一定會回表查詢嗎？ * 不一定，這涉及到查詢語句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再進行回表查詢。 * 舉個簡單的例子，假設我們在員工表的年齡上建立了索引，那么當進行`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規則，具體內容如下：  1. **原子性：** 事務是最小的執行單位，不允許分割。事務的原子性確保動作要么全部完成，要么完全不起作用； 2. **一致性：** 執行事務前后，數據保持一致，多個事務對同一個數據讀取的結果是相同的； 3. **隔離性：** 并發訪問數據庫時，一個用戶的事務不被其他事務所干擾，各并發事務之間數據庫是獨立的； 4. **持久性：** 一個事務被提交之后。它對數據庫中數據的改變是持久的，即使數據庫發生故障也不應該對其有任何影響。 ### 什么是臟讀？幻讀？不可重復讀？ * 臟讀(Drity Read)：某個事務已更新一份數據，另一個事務在此時讀取了同一份數據，由于某些原因，前一個RollBack了操作，則后一個事務所讀取的數據就會是不正確的。 * 不可重復讀(Non-repeatable read):在一個事務的兩次查詢之中數據不一致，這可能是兩次查詢過程中間插入了一個事務更新的原有的數據。 * 幻讀(Phantom Read):在一個事務的兩次查詢中數據筆數不一致，例如有一個事務查詢了幾列(Row)數據，而另一個事務卻在此時插入了新的幾列數據，先前的事務在接下來的查詢中，就會發現有幾列數據是它先前所沒有的。 ### 什么是事務的隔離級別？MySQL的默認隔離級別是什么？ 為了達到事務的四大特性，數據庫定義了4種不同的事務隔離級別，由低到高依次為Read uncommitted、Read committed、Repeatable read、Serializable，這四個級別可以逐個解決臟讀、不可重復讀、幻讀這幾類問題。 | 隔離級別 | 臟讀 | 不可重復讀 | 幻影讀 | | --- | --- | --- | --- | | READ-UNCOMMITTED | √ | √ | √ | | READ-COMMITTED | × | √ | √ | | REPEATABLE-READ | × | × | √ | | SERIALIZABLE | × | × | × | **SQL 標準定義了四個隔離級別：** * **READ-UNCOMMITTED(讀取未提交)：** 最低的隔離級別，允許讀取尚未提交的數據變更，**可能會導致臟讀、幻讀或不可重復讀**。 * **READ-COMMITTED(讀取已提交)：** 允許讀取并發事務已經提交的數據，**可以阻止臟讀，但是幻讀或不可重復讀仍有可能發生**。 * **REPEATABLE-READ(可重復讀)：** 對同一字段的多次讀取結果都是一致的，除非數據是被本身事務自己所修改，**可以阻止臟讀和不可重復讀，但幻讀仍有可能發生**。 * **SERIALIZABLE(可串行化)：** 最高的隔離級別，完全服從ACID的隔離級別。所有的事務依次逐個執行，這樣事務之間就完全不可能產生干擾，也就是說，**該級別可以防止臟讀、不可重復讀以及幻讀**。 **注意：** * 這里需要注意的是：Mysql 默認采用的 REPEATABLE\_READ隔離級別 Oracle 默認采用的 READ\_COMMITTED隔離級別 * 事務隔離機制的實現基于鎖機制和并發調度。其中并發調度使用的是MVVC（多版本并發控制），通過保存修改的舊版本信息來支持并發一致性讀和回滾等特性。 * 因為隔離級別越低，事務請求的鎖越少，所以大部分數據庫系統的隔離級別都是**READ-COMMITTED(讀取提交內容):**，但是你要知道的是InnoDB 存儲引擎默認使用 \*\*REPEATABLE-READ（可重讀）\*\*并不會有任何性能損失。 * InnoDB 存儲引擎在 **分布式事務** 的情況下一般會用到\*\*SERIALIZABLE(可串行化)\*\*隔離級別。 ## 鎖 ### 對MySQL的鎖了解嗎 * 當數據庫有并發事務的時候，可能會產生數據的不一致，這時候需要一些機制來保證訪問的次序，鎖機制就是這樣的一個機制。 * 就像酒店的房間，如果大家隨意進出，就會出現多人搶奪同一個房間的情況，而在房間上裝上鎖，申請到鑰匙的人才可以入住并且將房間鎖起來，其他人只有等他使用完畢才可以再次使用。 ### 從鎖的類別上分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引擎的行鎖是怎么實現的？ * 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 鎖定一個范圍，包含記錄本身 **相關知識點：** 1. innodb對于行的查詢使用next-key lock 2. Next-locking keying為了解決Phantom Problem幻讀問題 3. 當查詢的索引含有唯一屬性時，將next-key lock降級為record key 4. Gap鎖設計的目的是為了阻止多個事務將記錄插入到同一范圍內，而這會導致幻讀問題的產生 5. 有兩種方式顯式關閉gap鎖：（除了外鍵約束和唯一性檢查外，其余情況僅使用record lock） A. 將事務隔離級別設置為RC B. 將參數innodb\_locks\_unsafe\_for\_binlog設置為1 ### 什么是死鎖？怎么解決？ * 死鎖是指兩個或多個事務在同一資源上相互占用，并請求鎖定對方的資源，從而導致惡性循環的現象。 * 常見的解決死鎖的方法 * 1、如果不同程序會并發存取多個表，盡量約定以相同的順序訪問表，可以大大降低死鎖機會。 * 2、在同一個事務中，盡可能做到一次鎖定所需要的所有資源，減少死鎖產生概率； * 3、對于非常容易產生死鎖的業務部分，可以嘗試使用升級鎖定顆粒度，通過表級鎖定來減少死鎖產生的概率； `如果業務不好處理,可以用分布式事務鎖或者使用樂觀鎖` ### 數據庫的樂觀鎖和悲觀鎖是什么？怎么實現的？ * 數據庫管理系統（DBMS）中的并發控制的任務是確保在多個事務同時存取數據庫中同一數據時不破壞事務的隔離性和統一性以及數據庫的統一性。樂觀并發控制（樂觀鎖）和悲觀并發控制（悲觀鎖）是并發控制主要采用的技術手段。 * **悲觀鎖**：假定會發生并發沖突，屏蔽一切可能違反數據完整性的操作。在查詢完數據的時候就把事務鎖起來，直到提交事務。實現方式：使用數據庫中的鎖機制 ~~~ //核心SQL,主要靠for update select status from t_goods where id=1 for update; 復制代碼 ~~~ * **樂觀鎖**：假設不會發生并發沖突，只在提交操作時檢查是否違反數據完整性。在修改數據的時候把事務鎖起來，通過version的方式來進行鎖定。實現方式：樂一般會使用版本號機制或CAS算法實現。 ~~~ //核心SQL update table set x=x+1, version=version+1 where id=#{id} and version=#{version}; 復制代碼 ~~~ **兩種鎖的使用場景** * 從上面對兩種鎖的介紹，我們知道兩種鎖各有優缺點，不可認為一種好于另一種，像**樂觀鎖適用于寫比較少的情況下（多讀場景）**，即沖突真的很少發生的時候，這樣可以省去了鎖的開銷，加大了系統的整個吞吐量。 * 但如果是多寫的情況，一般會經常產生沖突，這就會導致上層應用會不斷的進行retry，這樣反倒是降低了性能，所以**一般多寫的場景下用悲觀鎖就比較合適。** ## 視圖 ### 為什么要使用視圖？什么是視圖？ * 為了提高復雜SQL語句的復用性和表操作的安全性，MySQL數據庫管理系統提供了視圖特性。所謂視圖，本質上是一種虛擬表，在物理上是不存在的，其內容與真實的表相似，包含一系列帶有名稱的列和行數據。但是，視圖并不在數據庫中以儲存的數據值形式存在。行和列數據來自定義視圖的查詢所引用基本表，并且在具體引用視圖時動態生成。 * 視圖使開發者只關心感興趣的某些特定數據和所負責的特定任務，只能看到視圖中所定義的數據，而不是視圖所引用表中的數據，從而提高了數據庫中數據的安全性。 ### 視圖有哪些特點？ **視圖的特點如下:** * 視圖的列可以來自不同的表，是表的抽象和在邏輯意義上建立的新關系。 * 視圖是由基本表(實表)產生的表(虛表)。 * 視圖的建立和刪除不影響基本表。 * 對視圖內容的更新(添加，刪除和修改)直接影響基本表。 * 當視圖來自多個基本表時，不允許添加和刪除數據。 視圖的操作包括創建視圖，查看視圖，刪除視圖和修改視圖。 ### 視圖的使用場景有哪些？ `視圖根本用途：簡化sql查詢，提高開發效率。如果說還有另外一個用途那就是兼容老的表結構。` **下面是視圖的常見使用場景：** * 重用SQL語句； * 簡化復雜的SQL操作。在編寫查詢后，可以方便的重用它而不必知道它的基本查詢細節； * 使用表的組成部分而不是整個表； * 保護數據。可以給用戶授予表的特定部分的訪問權限而不是整個表的訪問權限； * 更改數據格式和表示。視圖可返回與底層表的表示和格式不同的數據。 ### 視圖的優點 1. 查詢簡單化。視圖能簡化用戶的操作 2. 數據安全性。視圖使用戶能以多種角度看待同一數據，能夠對機密數據提供安全保護 3. 邏輯數據獨立性。視圖對重構數據庫提供了一定程度的邏輯獨立性 ### 視圖的缺點 1. 性能。數據庫必須把視圖的查詢轉化成對基本表的查詢，如果這個視圖是由一個復雜的多表查詢所定義，那么，即使是視圖的一個簡單查詢，數據庫也把它變成一個復雜的結合體，需要花費一定的時間。 2. 修改限制。當用戶試圖修改視圖的某些行時，數據庫必須把它轉化為對基本表的某些行的修改。事實上，當從視圖中插入或者刪除時，情況也是這樣。對于簡單視圖來說，這是很方便的，但是，對于比較復雜的視圖，可能是不可修改的 這些視圖有如下特征：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. 如果在一個程序系統中大量的使用存儲過程，到程序交付使用的時候隨著用戶需求的增加會導致數據結構的變化，接著就是系統的相關問題了，最后如果用戶想維護該系統可以說是很難很難、而且代價是空前的，維護起來更麻煩。 ## 觸發器 ### 什么是觸發器？觸發器的使用場景有哪些？ * 觸發器是用戶定義在關系表上的一類由事件驅動的特殊的存儲過程。觸發器是指一段代碼，當觸發某個事件時，自動執行這些代碼。 **使用場景** * 可以通過數據庫中的相關表實現級聯更改。 * 實時監控某張表中的某個字段的更改而需要做出相應的處理。 * 例如可以生成某些業務的編號。 * 注意不要濫用，否則會造成數據庫及應用程序的維護困難。 * 大家需要牢記以上基礎知識點，重點是理解數據類型CHAR和VARCHAR的差異，表存儲引擎InnoDB和MyISAM的區別。 ### 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 主要為以上操作 即對數據庫安全性完整性等有操作的，可以簡單的理解為權限控制等。 ### SQL語句的語法順序： 1. SELECT 2. FROM 3. JOIN 4. ON 5. WHERE 6. GROUP BY 7. HAVING 8. UNION 9. ORDER BY 10. LIMIT ### 超鍵、候選鍵、主鍵、外鍵分別是什么？ * 超鍵：在關系中能唯一標識元組的屬性集稱為關系模式的超鍵。一個屬性可以為作為一個超鍵，多個屬性組合在一起也可以作為一個超鍵。超鍵包含候選鍵和主鍵。 * 候選鍵：是最小超鍵，即沒有冗余元素的超鍵。 * 主鍵：數據庫表中對儲存數據對象予以唯一和完整標識的數據列或屬性的組合。一個數據列只能有一個主鍵，且主鍵的取值不能缺失，即不能為空值（Null）。 * 外鍵：在一個表中存在的另一個表的主鍵稱此表的外鍵。 ### SQL 約束有哪幾種？ > 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） ~~~ SELECT * FROM A,B(,C)或者SELECT * FROM A CROSS JOIN B (CROSS JOIN C)#沒有任何關聯條件，結果是笛卡爾積，結果集會很大，沒有意義，很少使用內連接（INNER JOIN）SELECT * FROM A,B WHERE A.id=B.id或者SELECT * FROM A INNER JOIN B ON A.id=B.id多表中同時符合某種條件的數據記錄的集合，INNER JOIN可以縮寫為JOIN 復制代碼 ~~~ **內連接分為三類** * 等值連接：ON A.id=B.id * 不等值連接：ON A.id > B.id * 自連接：SELECT \* FROM A T1 INNER JOIN A T2 ON T1.id=T2.pid **外連接（LEFT JOIN/RIGHT JOIN）** * 左外連接：LEFT OUTER JOIN, 以左表為主，先查詢出左表，按照ON后的關聯條件匹配右表，沒有匹配到的用NULL填充，可以簡寫成LEFT JOIN * 右外連接：RIGHT OUTER JOIN, 以右表為主，先查詢出右表，按照ON后的關聯條件匹配左表，沒有匹配到的用NULL填充，可以簡寫成RIGHT JOIN **聯合查詢（UNION與UNION ALL）** ~~~ SELECT * FROM A UNION SELECT * FROM B UNION ... 復制代碼 ~~~ * 就是把多個結果集集中在一起，UNION前的結果為基準，需要注意的是聯合查詢的列數要相等，相同的記錄行會合并 * 如果使用UNION ALL，不會合并重復的記錄行 * 效率 UNION 高于 UNION ALL **全連接（FULL JOIN）** ~~~ SELECT * FROM A LEFT JOIN B ON A.id=B.id UNIONSELECT * FROM A RIGHT JOIN B ON A.id=B.id 復制代碼 ~~~ * MySQL不支持全連接 * 可以使用LEFT JOIN 和UNION和RIGHT JOIN聯合使用 #### 表連接面試題 ##### 有2張表。 * 1張R、1張S，R表有ABC三列，S表有CD兩列，表中各有三條記錄 **R表** | A | B | C | | --- | --- | --- | | a1 | b1 | c1 | | a2 | b2 | c2 | | a3 | b3 | c3 | **S表** | C | D | | --- | --- | | c1 | d1 | | c2 | d2 | | c4 | d3 | ##### 1、交叉連接(笛卡爾積) * SQL ~~~ select r.*,s.* from r,s 復制代碼 ~~~ * 結果 | A | B | C | C | D | | --- | --- | --- | --- | --- | | a1 | b1 | c1 | c1 | d1 | | a2 | b2 | c2 | c1 | d1 | | a3 | b3 | c3 | c1 | d1 | | a1 | b1 | c1 | c2 | d2 | | a2 | b2 | c2 | c2 | d2 | | a3 | b3 | c3 | c2 | d2 | | a1 | b1 | c1 | c4 | d3 | | a2 | b2 | c2 | c4 | d3 | | a3 | b3 | c3 | c4 | d3 | ##### 2、內連接結果 * SQL ~~~ select r.*,s.* from r inner join s on r.c=s.c 復制代碼 ~~~ * 結果 | A | B | C | C | D | | --- | --- | --- | --- | --- | | a1 | b1 | c1 | c1 | d1 | | a2 | b2 | c2 | c2 | d2 | ##### 3、左連接結果 * SQL ~~~ select r.*,s.* from r left join s on r.c=s.c 復制代碼 ~~~ * 結果 | A | B | C | C | D | | --- | --- | --- | --- | --- | | a1 | b1 | c1 | c1 | d1 | | a2 | b2 | c2 | c2 | d2 | | a3 | b3 | c3 | | | ##### 4、右連接結果 * SQL ~~~ select r.*,s.* from r right join s on r.c=s.c 復制代碼 ~~~ * 結果 | A | B | C | C | D | | --- | --- | --- | --- | --- | | a1 | b1 | c1 | c1 | d1 | | a2 | b2 | c2 | c2 | d2 | | | | | c4 | d3 | ##### 5、全表連接的結果（MySql不支持，Oracle支持） * SQL ~~~ select r.*,s.* from r full join s on r.c=s.c 復制代碼 ~~~ * 結果 | A | B | C | C | D | | --- | --- | --- | --- | --- | | a1 | b1 | c1 | c1 | d1 | | a2 | b2 | c2 | c2 | d2 | | a3 | b3 | c3 | | | | | | | c4 | d3 | ### 什么是子查詢 1. 條件：一條SQL語句的查詢結果做為另一條查詢語句的條件或查詢結果 2. 嵌套：多條SQL語句嵌套使用，內部的SQL查詢語句稱為子查詢。 ### mysql中 in 和 exists 區別 * mysql中的in語句是把外表和內表作hash 連接，而exists語句是對外表作loop循環，每次loop循環再對內表進行查詢。一直大家都認為exists比in語句的效率要高，這種說法其實是不準確的。這個是要區分環境的。 1. 如果查詢的兩個表大小相當，那么用in和exists差別不大。 2. 如果兩個表中一個較小，一個是大表，則子查詢表大的用exists，子查詢表小的用in。 3. 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表示存儲數據的大小，即表示存儲多少個字符。 * char(10)表示存儲定長的10個字符，不足10個就用空格補齊，占用更多的存儲空間 * varchar(10)表示存儲10個變長的字符，存儲多少個就是多少個，空格也按一個字符存儲，這一點是和char(10)的空格不同的，char(10)的空格表示占位不算一個字符 ### FLOAT和DOUBLE的區別是什么？ * FLOAT類型數據可以存儲至多8位十進制數，并在內存中占4字節。 * DOUBLE類型數據可以存儲至多18位十進制數，并在內存中占8字節。 ### drop、delete與truncate的區別 * 三者都表示刪除，但是三者有一些差別： | 比較 | Delete | Truncate | Drop | | --- | --- | --- | --- | | 類型 | 屬于DML | 屬于DDL | 屬于DDL | | 回滾 | 可回滾 | 不可回滾 | 不可回滾 | | 刪除內容 | 表結構還在，刪除表的全部或者一部分數據行 | 表結構還在，刪除表中的所有數據 | 從數據庫中刪除表，所有的數據行，索引和權限也會被刪除 | | 刪除速度 | 刪除速度慢，需要逐行刪除 | 刪除速度快 | 刪除速度最快 | * 因此，在不再需要一張表的時候，用drop；在想刪除部分數據行時候，用delete；在保留表而刪除所有數據的時候用truncate。 ### UNION與UNION ALL的區別？ * 如果使用UNION ALL，不會合并重復的記錄行 * 效率 UNION 高于 UNION ALL ## SQL優化 ### 說出一些數據庫優化方面的經驗? 1. 有外鍵約束的話會影響增刪改的性能，如果應用程序可以保證數據庫的完整性那就去除外鍵 2. Sql語句全部大寫，特別是列名大寫，因為數據庫的機制是這樣的，sql語句發送到數據庫服務器，數據庫首先就會把sql編譯成大寫在執行，如果一開始就編譯成大寫就不需要了把sql編譯成大寫這個步驟了 3. 如果應用程序可以保證數據庫的完整性，可以不需要按照三大范式來設計數據庫 4. 其實可以不必要創建很多索引，索引可以加快查詢速度，但是索引會消耗磁盤空間 5. 如果是jdbc的話，使用PreparedStatement不使用Statement，來創建SQl，PreparedStatement的性能比Statement的速度要快，使用PreparedStatement對象SQL語句會預編譯在此對象中，PreparedStatement對象可以多次高效的執行 ### 怎么優化SQL查詢語句嗎 1. 對查詢進行優化，應盡量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引 2. 用索引可以提高查詢 3. SELECT子句中避免使用\*號，盡量全部大寫SQL 4. 應盡量避免在 where 子句中對字段進行 is null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描，使用 IS NOT NULL 5. where 子句中使用 or 來連接條件，也會導致引擎放棄使用索引而進行全表掃描 6. in 和 not in 也要慎用，否則會導致全表掃描 ### 你怎么知道SQL語句性能是高還是低 1. 查看SQL的執行時間 2. 使用explain關鍵字可以模擬優化器執行SQL查詢語句，從而知道MYSQL是如何處理你的SQL語句的。分析你的查詢語句或是表結構的性能瓶頸。 ### SQL的執行順序 1. FROM：將數據從硬盤加載到數據緩沖區，方便對接下來的數據進行操作。 2. WHERE：從基表或視圖中選擇滿足條件的元組。（不能使用聚合函數） 3. JOIN（如right left 右連接-------從右邊表中讀取某個元組，并且找到該元組在左邊表中對應的元組或元組集） 4. ON：join on實現多表連接查詢，推薦該種方式進行多表查詢，不使用子查詢。 5. GROUP BY：分組，一般和聚合函數一起使用。 6. HAVING：在元組的基礎上進行篩選，選出符合條件的元組。（一般與GROUP BY進行連用） 7. SELECT：查詢到得所有元組需要羅列的哪些列。 8. DISTINCT：去重的功能。 9. UNION：將多個查詢結果合并（默認去掉重復的記錄）。 10. ORDER BY：進行相應的排序。 11. LIMIT 1：顯示輸出一條數據記錄（元組） ### 如何定位及優化SQL語句的性能問題？創建的索引有沒有被使用到?或者說怎么才可以知道這條語句運行很慢的原因？ * 對于低性能的SQL語句的定位，最重要也是最有效的方法就是使用執行計劃，MySQL提供了explain命令來查看語句的執行計劃。 我們知道，不管是哪種數據庫，或者是哪種數據庫引擎，在對一條SQL語句進行執行的過程中都會做很多相關的優化，**對于查詢語句，最重要的優化方式就是使用索引**。 而**執行計劃，就是顯示數據庫引擎對于SQL語句的執行的詳細情況，其中包含了是否使用索引，使用什么索引，使用的索引的相關信息等**。  * 執行計劃包含的信息 **id** 有一組數字組成。表示一個查詢中各個子查詢的執行順序; * id相同執行順序由上至下。 * id不同，id值越大優先級越高，越先被執行。 * id為null時表示一個結果集，不需要使用它查詢，常出現在包含union等查詢語句中。 **select\_type** 每個子查詢的查詢類型，一些常見的查詢類型。 | id | select\_type | description | | --- | --- | --- | | 1 | SIMPLE | 不包含任何子查詢或union等查詢 | | 2 | PRIMARY | 包含子查詢最外層查詢就顯示為 PRIMARY | | 3 | SUBQUERY | 在select或 where字句中包含的查詢 | | 4 | DERIVED | from字句中包含的查詢 | | 5 | UNION | 出現在union后的查詢語句中 | | 6 | UNION RESULT | 從UNION中獲取結果集，例如上文的第三個例子 | * **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** 的信息非常豐富，常見的有： 1. Using index 使用覆蓋索引 2. Using where 使用了用where子句來過濾結果集 3. Using filesort 使用文件排序，使用非索引列進行排序時出現，非常消耗性能，盡量優化。 4. 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. 關掉連接，釋放資源  ### 大表數據查詢，怎么優化 1. 優化shema、sql語句+索引； 2. 第二加緩存，memcached, redis； 3. 主從復制，讀寫分離； 4. 垂直拆分，根據你模塊的耦合度，將一個大的系統分為多個小的系統，也就是分布式系統； 5. 水平切分，針對數據量大的表，這一步最麻煩，最能考驗技術水平，要選擇一個合理的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數據庫中,直接返回即可** ### mysql 分頁 * LIMIT 子句可以被用于強制 SELECT 語句返回指定的記錄數。LIMIT 接受一個或兩個數字參數。參數必須是一個整數常量。如果給定兩個參數，第一個參數指定第一個返回記錄行的偏移量，第二個參數指定返回記錄行的最大數目。初始記錄行的偏移量是 0(而不是 1) SELECT \* FROM table LIMIT 5,10; // 檢索記錄行 6-15 * 為了檢索從某一個偏移量到記錄集的結束所有的記錄行，可以指定第二個參數為 -1： SELECT \* FROM table LIMIT 95,-1; // 檢索記錄行 96-last. * 如果只給定一個參數，它表示返回最大的記錄行數目： SELECT \* FROM table LIMIT 5; //檢索前 5 個記錄行 * 換句話說，LIMIT n 等價于 LIMIT 0,n。 ### 慢查詢日志 > 用于記錄執行時間超過某個臨界值的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語句優化的一些方法 * 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=0 復制代碼 ~~~ * 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 飆升，這種情況就需要跟應用一起來分析為何連接數會激增，再做出相應的調整，比如說限制連接數等 ### 大表怎么優化？分庫分表了是怎么做的？分表分庫了有什么問題？有用到中間件么？他們的原理知道么？ 當MySQL單表記錄數過大時，數據庫的CRUD性能會明顯下降，一些常見的優化措施如下： 1. **限定數據的范圍：** 務必禁止不帶任何限制數據范圍條件的查詢語句。比如：我們當用戶在查詢訂單歷史的時候，我們可以控制在一個月的范圍內。； 2. **讀/寫分離：** 經典的數據庫拆分方案，主庫負責寫，從庫負責讀； 3. **緩存：** 使用MySQL的緩存，另外對重量級、更新少的數據可以考慮使用應用級別的緩存； **還有就是通過分庫分表的方式進行優化，主要有垂直分區、垂直分表和水平分區、水平分表** #### 1、垂直分區 * **根據數據庫里面數據表的相關性進行拆分。** 例如，用戶表中既有用戶的登錄信息又有用戶的基本信息，可以將用戶表拆分成兩個單獨的表，甚至放到單獨的庫做分庫。 * **簡單來說垂直拆分是指數據表列的拆分，把一張列比較多的表拆分為多張表。** 如下圖所示，這樣來說大家應該就更容易理解了。  * **垂直拆分的優點：** 可以使得行數據變小，在查詢時減少讀取的Block數，減少I/O次數。此外，垂直分區可以簡化表的結構，易于維護。 * **垂直拆分的缺點：** 主鍵會出現冗余，需要管理冗余列，并會引起Join操作，可以通過在應用層進行Join來解決。此外，垂直分區會讓事務變得更加復雜； #### 2、垂直分表 * 把主鍵和一些列放在一個表，然后把主鍵和另外的列放在另一個表中  **適用場景** * 1、如果一個表中某些列常用，另外一些列不常用 * 2、可以使數據行變小，一個數據頁能存儲更多數據，查詢時減少I/O次數 **缺點** * 有些分表的策略基于應用層的邏輯算法，一旦邏輯算法改變，整個分表邏輯都會改變，擴展性較差 * 對于應用層來說，邏輯算法增加開發成本 * 管理冗余列，查詢所有數據需要join操作 #### 3、水平分區 * **保持數據表結構不變，通過某種策略存儲數據分片。這樣每一片數據分散到不同的表或者庫中，達到了分布式的目的。 水平拆分可以支撐非常大的數據量。** * 水平拆分是指數據表行的拆分，表的行數超過200萬行時，就會變慢，這時可以把一張的表的數據拆成多張表來存放。舉個例子：我們可以將用戶信息表拆分成多個用戶信息表，這樣就可以避免單一表數據量過大對性能造成影響。  * 水品拆分可以支持非常大的數據量。需要注意的一點是:分表僅僅是解決了單一表數據過大的問題，但由于表的數據還是在同一臺機器上，其實對于提升MySQL并發能力沒有什么意義，所以 **水平拆分最好分庫** 。 * 水平拆分能夠 **支持非常大的數據量存儲，應用端改造也少**，但 **分片事務難以解決** ，跨界點Join性能較差，邏輯復雜。 `《Java工程師修煉之道》的作者推薦 盡量不要對數據進行分片，因為拆分會帶來邏輯、部署、運維的各種復雜度 ，一般的數據表在優化得當的情況下支撐千萬以下的數據量是沒有太大問題的。如果實在要分片，盡量選擇客戶端分片架構，這樣可以減少一次和中間件的網絡I/O。` #### 4、水平分表： * 表很大，分割后可以降低在查詢時需要讀的數據和索引的頁數，同時也降低了索引的層數，提高查詢次數  **適用場景** * 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的復制原理以及流程 * 主從復制：將主數據庫中的DDL和DML操作通過二進制日志（BINLOG）傳輸到從數據庫上，然后將這些日志重新執行（重做）；從而使得從數據庫的數據與主數據庫保持一致。 #### 主從復制的作用 1. 主數據庫出現問題，可以切換到從數據庫。 2. 可以進行數據庫層面的讀寫分離。 3. 可以在從數據庫上進行日常備份。 #### MySQL主從復制解決的問題 * 數據分布：隨意開始或停止復制，并在不同地理位置分布數據備份 * 負載均衡：降低單個服務器的壓力 * 高可用和故障切換：幫助應用程序避免單點失敗 * 升級測試：可以用更高版本的MySQL作為從庫 #### MySQL主從復制工作原理 * 在主庫上把數據更高記錄到二進制日志 * 從庫將主庫的日志復制到自己的中繼日志 * 從庫讀取中繼日志的事件，將其重放到從庫數據中 #### 基本原理流程，3個線程以及之間的關聯 * **主**：binlog線程——記錄下所有改變了數據庫數據的語句，放進master上的binlog中； * **從**：io線程——在使用start slave 之后，負責從master上拉取 binlog 內容，放進自己的relay log中； * **從**：sql執行線程——執行relay log中的語句； #### 復制過程  * Binary log：主數據庫的二進制日志 * Relay log：從服務器的中繼日志 1. master在每個事務更新數據完成之前，將該操作記錄串行地寫入到binlog文件中。 2. salve開啟一個I/O Thread，該線程在master打開一個普通連接，主要工作是binlog dump process。如果讀取的進度已經跟上了master，就進入睡眠狀態并等待master產生新的事件。I/O線程最終的目的是將這些事件寫入到中繼日志中。 3. 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 /\*!40100 WITH CONSISTENTSNAPSHOT \*/)，這樣就保證了該事務里讀到的數據都是事務事務時候的快照。之后再把表的數據讀取出來。如果加上–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命令后這些空間將被回收，并且對磁盤上的數據行進行重排（注意：是磁盤上，而非數據庫）`