學習來源: codedata - MySQL 超新手入門（20）效率

1. 索引
   1. 索引的種類
   2. 建立需要的索引
   3. 建立部份內容的索引
2. 判斷條件的設定
3. EXPLAIN與查詢敘述
4. 資料維護
5. LIMIT子句
6. 使用暫時表格
7. 儲存引擎

索引

_索引的種類

1. 主索引: 值不可以重複，而且不可以儲存「NULL」值
2. 唯一索引: 「不可重複索引」，值不可以重複，允許儲存「NULL」值
3. 非唯一索引: 增加查詢與維護資料效率的索引，值可以重複，可以儲存「NULL」值
4. 「FULLTEXT」索引: 「全文檢索」，提高搜尋大量文字的效率，只能用在「CHAR」、「VARCHAR」與「TEXT」型態的欄位，儲存引擎必須是「MyISAM」
5. 「SPATIAL」索引: 「SPATIAL」型態欄位專用，儲存引擎必須是「MyISAM」

_建立需要的索引

1. 如果很常使用這個欄位，執行條件的判斷(WHERE, ORDER BY, GROUP BY, ...)，就應該為它建立索引
2. 為了增加效率而建立索引的話，考慮下列幾點
   1. 最重要的是，不要建立沒有必要的索引，如: 資料量不大 的情況
   2. 索引的欄位儘量不要有「NULL」值
   3. 雖然某個欄位很常使用在「WHERE」、「ORDER BY」或「GROUP BY」子句中，也不一定要建立索引。例如性別欄位的值只有兩種(使用ENUM(‘M’, ‘F’)型態)
   4. 主索引鍵與 唯一索引的效率會比 非唯一索引好

_建立部份內容的索引

1. 情況，一個 test的表格，有個 address欄位，設計255的長度，需要為地址欄位建立非唯一欄位

   CREATE TABLE test (
     ...,
     address VARCHAR(255)
   )
   CREATE INDEX addr_index ON test (address)
   

2. 地址欄位的長度有255個字元，這樣的索引是比較沒有效率的
3. 如果比較經常執行的條件判斷，是類似「某某縣某某市」的話，建立部份內容的索引就好

   CREATE INDEX addrindex ON test ( address (6) )
   

   1. 使用 address的前六個字元建立非唯一索引
4. 建立索引的欄位值不應該有太多重複的值，先「分析」，欄位是不是 有很多重複的資料

   SELECT COUNT(*), COUNT(DISTINCT address)
   FROM   test;
   

5. 或是「分析」，前六個字元 是否很多重複

   SELECT COUNT(*), COUNT( DISTINCT LEFT(address, 6) )
   FROM   test
   

6. 確認地址欄位的前六個字元，如果有很多重複的資料，可以增加字元的數量後再查詢，直到可接受的重複數量後，再使用這個數量，建立部份內容的索引

判斷條件的設定

1. 使用「WHERE」子句設定查詢條件，儘量使用索引來增加查詢的效率
2. 在索引欄位使用函式或運算式，因為他訪在函式中處理，所以還是沒有使用索引
3. 在算數運算中使用字串值，MySQL會自動幫你轉換為數字，不過處理每一筆資料，都要執行一次轉換，很沒有效率，請避免
4. 結合查詢是一種很沒有效率的查詢，通常會幫結合條件中的欄位建立索引
5. 使用字串樣式執行條件判斷的話，不一定會使用索引

   SELECT * FROM country WHERE name LIKE 'ta%'
   SELECT * FROM country WHERE name LIKE '%ta'
   SELECT * FROM country WHERE name LIKE '_ta%'
   

   1. 字串樣式前面是明確的值，會使用索引，如 第一行
   2. 第二跟 第三行不會使用索引
6. 一些索引可能會包含多個欄位，依照索引欄位的順序來決定是否使用索引

   CREATE TABLE countrylanguage (
     CountryCode char(3) NOT NULL DEFAULT '',
     Language char(30) NOT NULL DEFAULT '',
     ..., 
     PRIMARY KEY (CountryCode, Language)
   )
   

   1. Language欄位在後面，如果查詢條件只有使用Language欄位的話，這個索引就不會生效

EXPLAIN 與查詢敘述

1. MySQL 提供「EXMPLIN」: 分析一個查詢敘述，不可以使用在「SELECT」以外的敘述
2. 「type」:
   1. ALL: 表示這個查詢發生「full table scan」，資料庫在這個表格從第一筆讀到最後一筆，才可以判斷要傳回哪些資料
   2. const: 表示只讀取一筆資料，在條件中使用 主牽引或 唯一牽引
   3. index:
3. 「possible_keys」: MySQL用來找到資料所使用的牽引
   1. NULL: 沒有使用牽引
   2. key名稱: 使用牽引的名稱
4. 「select_type」: 主查詢 與 子查詢

資料維護

1. 在「UPDATE」和「DELETE」敘述中使用「WHERE」子句設定條件時，跟查詢時候該注意的地方都一樣，除了儘量使用索引來增加執行的效率，也要避免不必要的資料轉換
2. 想要執行一個 UPDATE/DELETE 的敘述，把他轉換為查詢數續數後再使用「EXMPLIN」分析

   DELETE FROM ocuntry WHERE Code = 'YES'
   

   1. 轉換

      EXPLAIN
      SELECT * FROM country WHERE Code = 'YES'
      

   2. 一次要新增多筆資料，使用這樣的方式新增資料會是比較有效率的

      INSERT INTO cmdev.emp VALUES
      (8001, 'SIMON', 'MANAGER', 7369, '2001-02-03', 3300, NULL, 50), 
      (8002, 'Kelly', 'RNGINEER', 7370, '2003-02-03', 2300, NULL, 50),  
      (8003, 'JACK', 'PROGRAMMER', 7381, '2002-02-03', 2000, NULL, 50)   
      

LIMIT子句

1. 這個查詢敘述只有傳回五筆資料，可是資料庫總共讀取了105筆資料，這樣的查詢會是比較沒有效率的

   SELECT * FROM country LIMIT 100, 5
   

   1. 可以使用索引與「ORDER BY」子句來增加效率

      SELECT * FROM country ORDER BY Code LIMIT 5
      

使用暫時表格

1. 改善查詢的複雜度與效率
2. 在查詢工作中，很常使用第一個查詢的結果，再加上不同的條件或結合，你就可以考慮使用下列的敘述:
   1. 先建立好一個暫時的表格

      CREATE TABLE countrycapital
      SELECT   Continent, co.Code, co.Name COuntryName,
               ci.Name CaptitalName, ci.Population CaptitalPop
      FROM     co.Captial = ci.ID
      WHERE    co.Captial = ci.ID
      ORDER BY Continent, CountryName
      

   2. 加入其它的條件就變得簡單多

      SELECT *
      FROM  countrycapital
      WHERE CapitalPop < 5000
      

   3. 再結合另外一個表格的話，也會比較容易

      SELECT cc.*, cola.Language OfficalLanguage
      FROM   countrycapital cc, countrylanguage cola
      WHERE  cc.Code = cola.CountryCode AND
             CapitalPop < 5000 AND IsOfficial = 'T'
      

3. 把一個查詢放在「FROM」子句中，每次執行不同條件的查詢，資料庫都要重新執行子查詢敘述；先建立暫時的表格，再使用暫時表格執行查詢的作法會是比較有效率的

儲存引擎

1. 允許多個用戶端同時使用的資料庫管理系統，在多用戶端的的運作環境下，資料庫就使用「鎖定、Locking」來避免資料的混亂
2. 儲存引擎「MyISAM」
   1. 「table-level」的鎖定方式
   2. 適合使用在查詢工作非常多，資料維護比較少的資料庫，這樣的資料庫運作起來的效率會比較好
3. 儲存引擎「InnoDB」
   1. 「row-level」的鎖定方式
   2. 適合使用在查詢與資料維護工作都差不多的資料庫，這樣的資料庫運作起來的效率會比較好