資料庫基礎知識為什麼要使用資料庫？數據保存在記憶體優點：存取速度快缺點：數據不能永久保存數據保存在文件優點：數據永久保存缺點：速度比記憶體操作慢，頻繁的IO操作。查詢數據不方便數據保存在資料庫數據永久保存使用SQL語句，查詢方便效率高。管理數據方便什麼是SQL? 結構化查詢語 ...

資料庫基礎知識

為什麼要使用資料庫？
- 數據保存在記憶體
  - 優點：存取速度快
  - 缺點：數據不能永久保存
- 數據保存在文件
  - 優點：數據永久保存
  - 缺點：
    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存儲引擎？
- 常用的存儲引擎有以下：
  - Innodb引擎：Innodb引擎提供了對資料庫ACID事務的支持。並且還提供了行級鎖和外鍵的約束。它的設計的目標就是處理大數據容量的資料庫系統。
  - MyIASM引擎(原本Mysql的預設引擎)：不提供事務的支持，也不支持行級鎖和外鍵。
  - MEMORY引擎：所有的數據都在記憶體中，數據的處理速度快，但是安全性不高。
MyISAM與InnoDB區別
InnoDB引擎的4大特性
- 插入緩衝（insert buffer)
- 二次寫(double write)
- 自適應哈希索引(ahi)
- 預讀(read ahead)
存儲引擎選擇
- 如果沒有特別的需求，使用預設的Innodb即可。
- MyISAM：以讀寫插入為主的應用程式，比如博客系統、新聞門戶網站。
- Innodb：更新（刪除）操作頻率也高，或者要保證數據的完整性；併發量高，支持事務和外鍵。比如OA自動化辦公系統。

索引

什麼是索引？
- 索引是一種特殊的文件(InnoDB數據表上的索引是表空間的一個組成部分)，它們包含著對數據表裡所有記錄的引用指針。
- 索引是一種數據結構。資料庫索引，是資料庫管理系統中一個排序的數據結構，以協助快速查詢、更新資料庫表中數據。索引的實現通常使用B樹及其變種B+樹。
- 更通俗的說，索引就相當於目錄。為了方便查找書中的內容，通過對內容建立索引形成目錄。索引是一個文件，它是要占據物理空間的。
索引有哪些優缺點？
- 索引的優點
  - 可以大大加快數據的檢索速度，這也是創建索引的最主要的原因。
  - 通過使用索引，可以在查詢的過程中，使用優化隱藏器，提高系統的性能。
- 索引的缺點
  - 時間方面：創建索引和維護索引要耗費時間，具體地，當對錶中的數據進行增加、刪除和修改的時候，索引也要動態的維護，會降低增/改/刪的執行效率；
  - 空間方面：索引需要占物理空間。
索引有哪幾種類型？
- 主鍵索引：數據列不允許重覆，不允許為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索引。
索引演算法有哪些？
- 索引演算法有BTree演算法和Hash演算法
  - BTree演算法：BTree是最常用的mysql資料庫索引演算法，也是mysql預設的演算法。它不僅可以用於=，>，>=，<，<=和between這些比較操作符上，而且還可以用於like操作符，只要它的查詢條件是一個不以通配符開頭的常量。
  - 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的數據類型的列不要建立索引。
創建索引的三種方式，刪除索引
創建索引時需要註意什麼？
- 非空欄位：應該指定列為NOT NULL，除非你想存儲NULL。在MySQL中，含有空值的列很難進行查詢優化，因為它們使得索引、索引的統計信息以及比較運算更加複雜。你應該用0、一個特殊的值或者一個空串代替空值；
- 取值離散大的欄位：（變數各個取值之間的差異程度）的列放到聯合索引的前面，可以通過count()函數查看欄位的差異值，返回值越大說明欄位的唯一值越多欄位的離散程度高；
- 索引欄位越小越好：資料庫的數據存儲以頁為單位一頁存儲的數據越多一次IO操作獲取的數據越大效率越高。
使用索引查詢一定能提高查詢的性能嗎？為什麼？
- 通常，通過索引查詢數據比全表掃描要快。但是我們也必須註意到它的代價。
- 索引需要空間來存儲，也需要定期維護，每當有記錄在表中增減或索引列被修改時，索引本身也會被修改。這意味著每條記錄的INSERT，DELETE，UPDATE將為此多付出4，5 次的磁碟I/O。
- 因為索引需要額外的存儲空間和處理，那些不必要的索引反而會使查詢反應時間變慢。使用索引查詢不一定能提高查詢性能，索引範圍查詢(INDEX RANGE SCAN)適用於兩種情況:
  - 基於一個範圍的檢索，一般查詢返回結果集小於表中記錄數的30%
  - 基於非唯一性索引的檢索