資料庫基礎知識詳解四：存儲過程、視圖、游標、SQL語句優化以及索引

寫在文章前：本系列文章用於部落客自己歸納複習一些基礎知識，同時也分享給可能需要的人，因為水平有限，肯定存在諸多不足以及技術性錯誤，請大佬們及時指正。

11、存儲過程

​ 存儲過程是事先經過編譯並存儲在資料庫中的一段SQL語句的集合。想要實現相應的功能時，只需要調用這個存儲過程就行了（類似於函數，輸入具有輸出參數）。

優點：

  • 預先編譯，而不需要每次運行時編譯，提高了資料庫執行效率。
  • 封裝了一系列操作，對於一些數據交互比較多的操作，相比於單獨執行SQL語句，可以減少網路通訊量。
  • 具有可復用性，減少了資料庫開發的工作量。
  • 安全性高，可以讓沒有許可權的用戶通過存儲過程間接操作資料庫。
  • 更易於維護。

缺點：

  • 可移植性差，存儲過程將應用程式綁定到了資料庫上。
  • 開發調試複雜。
  • 修改複雜，需要重新編譯，有時還需要更新程式中的程式碼以更新調用。

Drop/Delete/Truncate的區別？

Delete用來刪除表的全部或者部分數據，執行delete之後，用戶需要提交之後才會執行，會觸發表上的DELETE觸發器（包含一個OLD的虛擬表，可以只讀訪問被刪除的數據），DELETE之後表結構還在，刪除很慢，一行一行地刪，因為會記錄日誌，可以利用日誌還原數據；

Truncate刪除表中的所有數據，這個操作不能回滾，也不會觸發這個表上的觸發器。操作比DELETE快很多（直接把表drop掉，再創建一個新表，刪除的數據不能找回）。如果表中有自增（AUTO_INCREMENT）列，則重置為1。

Drop命令從資料庫中刪除表，所有的數據行，索引和約束都會被刪除。不能回滾，不會觸發觸發器。

觸發器是什麼？

觸發器（TRIGGER）是由事件（比如INSERT/UPDATE/DELETE）來觸發運行的操作（不能被直接調用，不能接收參數）。在資料庫里以獨立的對象存儲，用於保證數據完整性（比如可以檢驗或轉換數據）。

有哪些約束類型？

約束（Constraint）類型：

  • 主鍵（Primary Key）約束
  • 唯一約束（Unique）
  • 檢查約束
  • 非空約束
  • 外鍵（Foreign Key）約束

12、視圖、游標

視圖：

​ 從資料庫的基本表中通過查詢選取出來的數據組成的虛擬表（資料庫中只存放視圖的定義，而不存放視圖的數據）。可以對其進行增/刪/改/查等操作。視圖是對若干張基本表的引用，一張虛表，查詢語句執行的結果，不存儲具體的數據（基本表數據發生了改變，視圖也會跟著改變）。

可以跟基本表一樣，進行增刪改查操作(增刪改操作有條件限制，一般視圖只允許查詢操作)，對視圖的增刪改也會影響原表的數據。它就像一個窗口，透過它可以看到資料庫中自己感興趣的數據並且操作它們。好處：

  • 通過只給用戶訪問視圖的許可權，保證數據的安全性。
  • 可以通過對不同的用戶定義不同的視圖，使機密數據不出現在不應該看到這些數據的用戶視圖上。例如，Student表涉及全校15個院系學生數據，可以在其上定義15個視圖，每個視圖只包含一個院系的學生數據，並只允許每個院系的主任查詢和修改本原系學生視圖。
  • 可以通過視圖使用戶以多種角度看待同一數據，比如不同種類的用戶共享一個資料庫。（通過許可權？）
  • 簡化複雜的SQL操作（比如原來查的內容是幾張幾百列的數據表，而我們只關心其中幾個數據，那我們可以建立一個視圖，直接在視圖中查詢它們，可以提高很多效率），隱藏數據的複雜性（比如複雜的連接）。

游標（Cursor）：

​ 用於定位在查詢返回的結果集的特定行，以對特定行進行操作。使用游標可以方便地對結果集進行移動遍歷，根據需要滾動或對瀏覽/修改任意行中的數據。主要用於互動式應用。它是一段私有的SQL工作區，也就是一段記憶體區域，用於暫時存放受SQL語句影響的數據，簡單來說，就是將受影響的數據暫時放到了一個記憶體區域的虛表當中，這個虛表就是游標。

​ 游標是一種能從包括多條數據記錄的結果集中每次提取一條記錄的機制。即游標用來逐行讀取結果集。游標充當指針的作用。儘管游標能遍歷結果中的所有行，但他一次只指向一行。

​ 游標的一個常見用途就是保存查詢結果，以便以後使用。游標的結果集是由SELECT語句產生，如果處理過程需要重複使用一個記錄集，那麼創建一次游標而重複使用若干次，比重複查詢資料庫要快的多。通俗來說，游標就是能在sql的查詢結果中，顯示某一行（或某多行）數據，其查詢的結果不是數據表，而是已經查詢出來的結果集。

簡單來說：游標就是在查詢出的結果集中進行選擇性操作的工具。

13、SQL語句的優化

  • 應盡量避免在 where 子句中使用!=、<、>操作符或對欄位進行null值判斷，否則將引擎放棄使用索引而進行全表掃描；
  • 只返回必要的列：最好不要使用 SELECT * 語句；
  • 只返回必要的行：使用 LIMIT 語句來限制返回的數據；
  • 將一個大連接查詢分解成對每一個表進行一次單表查詢，然後進行關聯，這樣做的好處有：

​ 讓快取更高效。對於連接查詢，如果其中一個表發生變化，那麼整個查詢快取就無法使用。而分解後的多個查詢，即使其中一個表發生變化，對其它表的查詢快取依然可以使用。分解成多個單表查詢，這些單表查詢的快取結果更可能被其它查詢使用到，從而減少冗餘的查詢。減少鎖競爭。

14、索引

​ 索引是對資料庫表中一列或多列的值進行排序的一種結構（說明是在列上建立的），使用索引可快速訪問資料庫表中的特定資訊。如果想按特定職員的姓來查找他或她，則與在表中搜索所有的行相比，索引有助於更快地獲取資訊。索引的一個主要目的就是加快檢索表中數據，亦即能協助資訊搜索者儘快的找到符合限制條件的記錄ID的輔助數據結構。

​ 當表中有大量記錄時，若要對錶進行查詢，第一種搜索資訊方式是全表搜索，是將所有記錄一一取出，和查詢條件進行一一對比，然後返回滿足條件的記錄，這樣做會消耗大量資料庫系統時間，並造成大量磁碟I/O操作。第二種就是在表中建立索引，然後在索引中找到符合查詢條件的索引值，最後通過保存在索引中的ROWID（相當於頁碼）快速找到表中對應的記錄。

例如這樣一個查詢：select * from table1 where id=10000。如果沒有索引，必須遍歷整個表，直到ID等於10000的這一行被找到為止。有了索引之後(必須是在ID這一列上建立的索引)，即可在索引中查找。由於索引是經過某種演算法優化過的，因而查找次數要少的多。可見，索引是用來定位的。

  • （1）聚簇索引對磁碟上存放數據的物理地址重新組織以使這些數據按照指定規則排序的一種索引（數據的物理排列順序和索引排列順序一致）。按照數據存放的物理位置為順序，每張數據表只能創建一個聚簇索引（因為要改變物理存儲順序），與非聚簇索引相比查詢數據速度很快，進行修改的速度較慢。主鍵索引就是一種聚簇索引。
  • InnoDB表要求必須有聚簇索引，默認在主鍵欄位上建立聚簇索引，在沒有主鍵欄位的情況下，表的第一個NOT NULL 的唯一索引將被建立為聚簇索引，在前兩者都沒有的情況下，InnoDB將自動生成一個隱式自增id列並在此列上創建聚簇索引。
  • （2）非聚簇索引（也叫二級索引/輔助索引）只記錄邏輯順序，並不改變物理順序。通過索引記錄地址訪問表中的數據。索引的邏輯順序和表中行的物理存儲順序不同。Innodb使用的是聚簇索引，MyISam使用的是非聚簇索引。

從應用上分，主鍵索引（聚集），唯一索引（聚集/非聚集），普通索引，組合索引，單列索引和全文索引

  • （3）唯一索引（UNIQUE）：索引列的值必須唯一，允許有空值。
  • （4）主鍵索引 PRIMARY KEY：必須唯一，不允許空值（是一種特殊的唯一索引。表中只有一個，MySQL創建主鍵時默認為聚集索引，但主鍵也可以是非聚集索引）。當列添加主鍵約束時，自動添加主鍵索引。
  • （5）普通索引：用表中的普通列構建的索引，沒有任何限制，用於加速查詢。
  • （6）組合索引（複合索引）：多列值組成一個索引，專門用於組合搜索，其效率大於索引合併。
  • （7）全文索引：先定義一個詞庫，然後在文章中查找每個詞條(term)出現的頻率和位置，把這樣的頻率和位置資訊按照詞庫的順序歸納，這樣就相當於對文件建立了一個以詞庫為目錄的索引，這樣查找某個詞的時候就能很快的定位到該詞出現的位置。

索引的優點：

  • 大大加快了數據的檢索速度。
  • 顯著減少查詢中分組和排序的時間。
  • 通過創建唯一性索引，可以保證資料庫表中的某一行數據的唯一性。
  • 將隨機I/O變為了順序I/O（B+Tree 索引是有序的，會將相鄰的數據都存儲在一起）。

索引的缺點：

  • 創建和維護索引組要耗費時間，並且隨著數據量的增加所耗費的時間也會增加。
  • 索引需要佔磁碟空間，除了數據表占數據空間以外，每一個索引還要佔一定的物理空間。如果有大量的索引，索引文件可能比數據文件更快達到最大文件尺寸。
  • 當對表中的數據進行增加、刪除和修改的時候，索引也要動態維護，這樣就降低了數據的維護速度。

索引失效的情況？

  • 以「%(表示任意0個或多個字元)」開頭的LIKE語句。
  • OR條件中的每個列沒有同時使用索引。
  • 對於多列索引，必須滿足 最左匹配原則/最左前綴原則 (最左優先：多列索引col1、col2和col3，則 索引生效的情形包括 col1或col1，col2或col1，col2，col3)。
  • 如果MySQL估計全表掃描比索引快，則不使用索引（比如非常小的表）。

哪些地方適合創建索引？

  • 某列經常作為最大最小值。
  • 經常被查詢的欄位。
  • 經常用作表連接的欄位。
  • 經常出現在ORDER BY/GROUP BY/DISDINCT後面的欄位。

創建索引需要注意的：

  • 只應建立在小欄位上，而不要對大文本或圖片建立索引（一頁存儲的數據越多一次IO操作獲取的數據越大效率越高）。

  • 建立索引的欄位應該非空，在MySQL中，含有空值的列很難進行查詢優化，因為它們使得索引、索引的統計資訊以及比較運算更加複雜。應該用0、一個特殊的值或者一個空串代替NULL。

  • 選擇數據密度大（唯一值佔總數的百分比很大）的欄位作索引。

Tags:

VirMach 便宜 VPS

QNews

QNews