mysql索引使用基礎

楚景然發表於2024-05-07
MySql索引

1.建立&刪除

MySQL可以透過CREATE、ALTER、DDL三種方式建立一個索引。

在MySQL中，使用CREATE INDEX語句可以建立索引。具體語法如下：

CREATE INDEX indexName ON tableName (columnName(length) [ASC|DESC]);
 
其中，indexName是索引的名稱，tableName是要在其上建立索引的表名，columnName是要建立索引的列名，length是可選引數，用於指定索引的長度，ASC表示升序排序，DESC表示降序排序。

例如，如果要在名為students的表上為name列建立一個名為index_name的索引，可以使用以下語句：

CREATE INDEX index_name ON students (name);
 
如果要在名為employees的表上為age列建立一個名為index_age的索引，並按照年齡降序排序，可以使用以下語句：

CREATE INDEX index_age ON employees (age DESC);

在MySQL中，使用ALTER TABLE語句可以新增索引。具體語法如下：
ALTER TABLE tableName ADD INDEX indexName(columnName(length) [ASC|DESC]);

其中，tableName是要在其上新增索引的表名，indexName是索引的名稱，columnName是要建立索引的列名，length是可選引數，用於指定索引的長度，ASC表示升序排序，DESC表示降序排序。

例如，如果要在名為students的表上為name列建立一個名為index_name的索引，可以使用以下語句：
ALTER TABLE students ADD INDEX index_name(name);

    
如果要在名為employees的表上為age列建立一個名為index_age的索引，並按照年齡降序排序，可以使用以下語句：
ALTER TABLE employees ADD INDEX index_age(age DESC);

在MySQL中，使用CREATE TABLE語句可以建立表。具體語法如下：
CREATE TABLE tableName(
  columnName1 dataType NOT NULL,
  columnName2 dataType,
  ....,
  INDEX indexName (columnName(length))
);

    
其中，tableName是要建立的表名，columnName是列名，dataType是資料型別，NOT NULL表示該列不能為空，indexName是索引的名稱，columnName(length)是要建立索引的列名和長度。

例如，如果要建立一個名為students的表，包含id、name和age三個列，併為name列建立一個名為index_name的索引，可以使用以下語句：
CREATE TABLE students(
  id INT(8) NOT NULL,
  name VARCHAR(50),
  age INT(3),
  INDEX index_name (name)
);

mysql中沒有修改索引語句，只能刪除然後再重新新增

在MySQL中，使用DROP INDEX語句可以刪除索引。具體語法如下：
DROP INDEX indexName ON tableName;
  
其中，indexName是要刪除的索引名稱，tableName是要在其上刪除索引的表名。
例如，如果要從名為students的表中刪除名為index_name的索引，可以使用以下語句：
DROP INDEX index_name ON students;

　　

2.索引本質

資料庫索引的本質是一種資料結構，用於提高資料庫查詢的效能。

索引的工作原理基於為資料庫表中的一列或多列建立一個額外的資料結構，這個資料結構允許快速地定位到儲存資料的位置，從而加快查詢速度。具體來說，索引通常包含兩列：索引欄位和指向原記錄

指標。索引表按照索引欄位的值進行排序，這樣在執行查詢時，資料庫管理系統(DBMS)可以採用高效的查詢演算法（如二分查詢）來快速定位到所需的記錄。

以下是索引的具體優點和缺點：

優勢：

  1. 提高檢索速度：索引可以顯著加快資料的查詢速度，尤其是當表中資料量較大時更為明顯。透過索引，資料庫能夠快速定位到所需資料，避免全表掃描。
  2. 保證資料唯一性：唯一索引確保了資料庫表中每一行資料的唯一性，這對於維護資料的完整性非常重要。
  3. 加速連線操作：在涉及多表連線的查詢中，有索引的列可以加速表與表之間的連線操作。
  4. 減少排序和分組時間：在使用ORDER BY或GROUP BY子句進行資料檢索時，索引可以減少查詢中的排序和分組時間。
  5. 最佳化效能：正確建立和使用索引是實現高效能查詢的基礎，有助於提高整個系統的效能。

劣勢：

  1. 佔用空間：每個索引都會佔用一定的物理空間，如果建立聚簇索引，所需的空間會更大。
  2. 維護成本：建立和維護索引需要耗費時間，這種時間隨著資料量的增加而增加。
  3. 降低資料維護速度：在進行INSERT、UPDATE、DELETE等操作時，由於索引需要動態維護，這些操作的速度會相應降低。
  4. 查詢最佳化器選擇：有時候，即使存在索引，查詢最佳化器也可能會選擇不使用索引，特別是在評估索引帶來的效能提升不足以彌補其維護成本時。

總的來說，索引雖然在空間和更新操作上有所犧牲，但換來的是查詢效率的大幅提升。在設計資料庫時，合理使用索引是最佳化效能的重要手段之一。

3.索引區分

從表欄位的層次來看，索引又可以分為單列索引和多列索引，這兩個稱呼也比較好理解，單列索引是指索引是基於一個欄位建立的，多列索引則是指由多個欄位組合建立的索引。

3.1.單列索引

在資料庫中，單列索引可以根據其約束條件和用途分為幾種型別，每種型別都有其特定的特點。以下是常見的單列索引型別及其特點：

  1. 普通索引（Regular Index）：

    • 特點：允許列中有重複值和空值。
    • 目的：加速對指定列的查詢。

  2. 唯一索引（Unique Index）：

    • 特點：不允許列中有重複值，但可以有空值。
    • 目的：加速查詢，並保證列中值的唯一性。

  3. 主鍵索引（Primary Key Index）：

    • 特點：不允許列中有重複值或空值。
    • 目的：加速查詢，並強制表的每一行都唯一。

  4. 全文索引（Fulltext Index）：

    • 特點：用於全文搜尋，支援基於文字內容的查詢，通常用於VARCHAR或TEXT型別的列。
    • 目的：加速全文搜尋。

建立索引的SQL語句示例（以MySQL為例）：

-- 建立普通索引
CREATE INDEX index_name ON table_name(column_name);

-- 建立唯一索引
CREATE UNIQUE INDEX unique_index_name ON table_name(column_name);

-- 建立主鍵索引
ALTER TABLE table_name
ADD PRIMARY KEY (column_name);

-- 建立全文索引（僅適用於支援全文索引的儲存引擎，如InnoDB）
ALTER TABLE table_name
ADD FULLTEXT INDEX fulltext_index_name(column_name);

建立索引的SQL程式碼示例：

-- 假設我們有一個名為 `users` 的表，包含 `id`, `username`, `email` 列

-- 建立普通索引
CREATE INDEX idx_users_username ON users(username);

-- 建立唯一索引，以確保每個使用者有唯一的電子郵件地址
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- 設定 `id` 為主鍵，自動建立主鍵索引
ALTER TABLE users
ADD PRIMARY KEY (id);

-- 如果表支援全文搜尋，可以建立一個全文索引來最佳化對 `content` 列的搜尋
CREATE FULLTEXT INDEX ft_users_content ON users(content);

3.2.多列索引

多列索引是一種在多個列上建立的索引，它允許根據多個列的值進行查詢。多列索引可以提供更高效的查詢效能，特別是在需要對多個列進行篩選或排序的情況下。

以下是常見的多列索引型別及其特點：

  1. 聯合索引（Composite Index）：

    • 特點：基於多個列的值建立一個索引。
    • 目的：加速涉及多個列的查詢操作。

  2. 覆蓋索引（Covering Index）：

    • 特點：包含查詢所需的所有資料的索引。
    • 目的：避免額外的資料訪問，提高查詢效能。

  3. 字首索引（Prefix Index）：

    • 特點：只包含指定列的前N個字元的索引。
    • 目的：減少索引的大小，提高查詢效能。

  4. 全文索引（Fulltext Index）：

    • 特點：用於全文搜尋，支援基於文字內容的查詢，通常用於VARCHAR或TEXT型別的列。
    • 目的：加速全文搜尋。

建立多列索引的SQL語句示例（以MySQL為例）：

-- 建立聯合索引
CREATE INDEX index_name ON table_name(column1, column2);

-- 建立覆蓋索引
CREATE INDEX index_name ON table_name(column1, column2, ..., columnN);

-- 建立字首索引
CREATE INDEX index_name ON table_name(column_name(N));

-- 建立全文索引（僅適用於支援全文索引的儲存引擎，如InnoDB）
ALTER TABLE table_name
ADD FULLTEXT INDEX fulltext_index_name(column_name);

建立多列索引的SQL程式碼示例：

-- 假設我們有一個名為 `orders` 的表，包含 `customer_id`, `order_date`, `total_amount` 列

-- 建立聯合索引，以便根據客戶ID和訂單日期進行查詢
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- 建立覆蓋索引，以便根據客戶ID、訂單日期和總金額進行查詢，而無需額外訪問表資料
CREATE INDEX idx_orders_covering ON orders(customer_id, order_date, total_amount);

-- 建立字首索引，以便根據客戶ID的前5個字元進行查詢
CREATE INDEX idx_orders_customer_prefix ON orders(customer_id(5));

-- 如果表支援全文搜尋，可以建立一個全文索引來最佳化對 `description` 列的搜尋
CREATE FULLTEXT INDEX ft_orders_description ON orders(description);

請注意，建立索引時需要考慮表中資料的特點和查詢模式。不當的索引可能會導致效能問題，例如，頻繁更新的列不適合建立索引，因為它會導致索引維護的成本增加。此外，索引雖然可以提高查詢速度，但也會佔用額外的儲存空間，並在插入、更新和刪除操作時增加開銷。因此，在建立索引前應進行仔細的規劃和考慮。

4.索引注意點

4.1.建立索引時需要遵守以下原則

  1. 選擇唯一性索引：唯一性索引可以更快速地透過該索引來確定某條記錄，因為唯一性索引的值是唯一的。
  2. 為經常需要排序、分組和聯合操作的欄位建立索引：如果某個欄位經常用來做查詢條件，那麼該欄位的查詢速度會影響整個表的查詢速度。因此，為這樣的欄位建立索引，可以提高整個表的查詢速度。
  3. 最左字首匹配原則：在使用SQL語句時，如果WHERE部分的條件不符合最左匹配原則，可能導致索引失效，或者不能完全發揮建立的索引的功效。
  4. =和in可以亂序：比如a = 1 and b = 2 and c = 3建立 (a,b,c)索引可以任意順序，MySQL的查詢最佳化器會幫你最佳化成索引可以識別的形式。
  5. 儘量選擇區分度高的列作為索引：區分度的公式是：count (distinct col)/count (*)。區分度越高，索引的效果越好。
  6. 不要建立過多的索引：因為索引本身會佔用儲存空間。
  7. 考慮欄位值長度較短的欄位建立索引：如果欄位值太長，會降低索引的效率。

4.2.聯合索引的最左字首原則

聯合索引的最左字首原則是指在使用聯合索引進行查詢時，查詢條件需要遵循索引中列的順序，從左到右進行匹配。

最左字首原則是資料庫最佳化中的一個重要概念，它要求在使用聯合索引時，查詢條件必須包含索引列的最左端開始的一個或多個連續的列。這個原則確保了資料庫能夠有效地使用索引來加速查詢。以下是

一些關鍵點：

  • 查詢條件的順序：查詢條件中的列必須按照聯合索引中列的順序出現。例如，如果有一個聯合索引(a, b, c)，那麼查詢條件中必須首先包含列a，然後是列b，最後是列c。
  • 部分使用索引：如果查詢條件只涉及到聯合索引中的部分列，那麼只有這些列的索引會被利用。例如，如果查詢條件只包含列a和列b，那麼只有這兩部分的索引會被用於查詢。
  • 範圍查詢的影響：當遇到範圍查詢（如大於、小於、BETWEEN等）時，最左字首原則會停止匹配。這意味著，如果範圍查詢出現在聯合索引的某個列上，那麼該列右側的所有列都無法使用索引。
  • 調整索引順序：根據業務需求和查詢模式，可以調整聯合索引中列的順序，以最佳化查詢效能。例如，如果查詢經常涉及列a、列b和列d，但很少涉及列c，則可以考慮建立(a, b, d, c)的聯合索引。

以下是一個具體的示例來說明最左字首原則的應用：

假設有一個表users，其包含id, username, email等欄位，並且在這個表上建立了一個聯合索引idx_username_email（包含username和email兩個欄位）。

CREATE INDEX idx_username_email ON users(username, email);

根據最左字首原則，以下幾種查詢方式是符合最左字首原則的：

1.使用username和email進行查詢，能夠完全利用索引：

SELECT * FROM users WHERE username = '張三' AND email = 'zhangsan@example.com';

2.只使用username進行查詢，能夠部分利用索引：

SELECT * FROM users WHERE username = '張三';

3.使用username進行範圍查詢，只能利用到第一列的索引：

SELECT * FROM users WHERE username LIKE '張%';

而以下查詢方式則不符合最左字首原則，可能導致索引失效：

1.只使用email進行查詢，沒有利用到最左側的username：

SELECT * FROM users WHERE email = 'zhangsan@example.com';

2.使用username進行範圍查詢後，又使用了email進行等於查詢，但因為範圍查詢的存在，email的索引將不會生效：

SELECT * FROM users WHERE username > '張三' AND email = 'zhangsan@example.com';

4.3.索引失效

在MySQL中，即使為表的欄位建立了索引，也可能由於查詢語句的寫法或資料的特性導致索引失效。以下是一些可能導致索引失效的情況，以及相應的SQL示例：

  1. 使用OR運算子： 如果查詢條件中使用了OR連線不同欄位，即使部分條件有索引，索引也可能不會使用。

    SELECT * FROM users WHERE last_name = 'Smith' OR age = 30;

    如果last_name有索引，而age沒有，last_name上的索引可能不會使用。

  2. 不符合最左字首原則： 對於聯合索引，查詢條件必須包含聯合索引的最左邊列，否則索引可能失效。

    SELECT * FROM users WHERE age = 30;

    如果有一個聯合索引(last_name, age)，這個查詢就不會使用索引。

  3. LIKE以萬用字元開頭： 如果使用LIKE運算子且模式以萬用字元'%'開頭，如LIKE '%abc'，索引通常不會生效。

    SELECT * FROM users WHERE last_name LIKE '%abc';

    即使last_name上有索引，這種查詢也不會使用索引。

  4. 型別轉換： 如果查詢條件中對索引列進行了隱式的型別轉換，可能會導致索引失效。

    SELECT * FROM users WHERE age = '30';

    如果age是整數型別，這裡的字串'30'需要進行型別轉換，可能會導致索引失效。

  5. 索引列進行計算或使用函式： 如果在查詢條件中對索引列使用了函式或進行了計算，索引可能會失效。

    SELECT * FROM users WHERE YEAR(birthdate) = 1990;

    如果birthdate欄位有索引，使用YEAR函式可能會導致索引失效。

  6. 全表掃描更快時： 在某些情況下，即使有索引，MySQL最佳化器可能會選擇全表掃描，因為它認為這樣更快，尤其是在資料量較小的時候。

  7. 頻繁更新的欄位： 經常更新的欄位不適合作為索引，因為每次更新都會導致索引重新構建，這會消耗大量的資源。

  8. 索引選擇性差： 如果一個索引唯一性很差，即很多行都具有相同的索引值，那麼使用索引的效果將不明顯，因為MySQL需要在這些具有相同索引值的行中進行進一步的查詢。

  9. 使用了不等於運算子： 使用不等於（<>）運算子時，索引效果通常不如等值比較。

    SELECT * FROM users WHERE age <> 30;

    即使age欄位有索引，這種查詢也可能不會使用索引。

  10. 複合索引未用左列欄位： 在使用複合索引時，如果沒有使用到最左側的列，那麼整個複合索引都可能不會被使用。

    SELECT * FROM users WHERE age = 30;

    如果有一個複合索引(last_name, age)，這個查詢就不會使用索引。

4.3.索引正確使用姿勢

在MySQL中，正確使用索引可以顯著提高查詢效率。以下是一些正確使用索引的SQL示例和最佳實踐：

  1. 建立和使用主鍵索引： 主鍵索引是唯一的，它確保了資料的唯一性，並且MySQL會自動為主鍵建立索引。

    CREATE TABLE users ( id INT AUTO_INCREMENT, username VARCHAR(50), PRIMARY KEY (id) );

  2. 建立和使用唯一索引： 唯一索引保證列中的每一個值都是唯一的，這有助於加快查詢速度。

    CREATE UNIQUE INDEX idx_username ON users (username);

  3. 建立和使用普通索引： 普通索引（非唯一索引）可以幫助加速查詢操作。

    CREATE INDEX idx_email ON users (email);

  4. 使用複合索引： 複合索引可以在多個列上建立，有助於多列條件的快速查詢。

    CREATE INDEX idx_name_age ON users (last_name, age);

  5. 避免使用OR運算子： 如果查詢條件中使用了OR連線不同欄位，即使部分條件有索引，索引也可能不會使用。應儘量避免使用OR，或者將OR條件拆分成多個查詢。

  6. 避免在索引列上進行計算： 不要在索引列上使用函式或進行計算，這會導致索引失效。

  7. 使用LIMIT限制結果集： 使用LIMIT可以減少返回的資料量，這樣即使沒有索引，也能減少查詢時間。

  8. 選擇合適的索引型別： 根據儲存引擎的不同，選擇最合適的索引型別。例如，InnoDB儲存引擎使用B+Tree索引資料結構。

  9. 分析查詢語句： 使用EXPLAIN命令來分析查詢語句，確保索引被正確使用。

  10. 避免全表掃描： 儘量減少全表掃描的情況，因為全表掃描會忽略索引，導致查詢效率降低。

4.4.提高查詢效率的重要最佳化策略

MySQL提高查詢效率的重要最佳化策略包括庫表結構最佳化、索引最佳化和查詢最佳化。以下是一些具體的最佳化措施：

  1. 庫表結構最佳化：
    • 避免使用SELECT *，只查詢需要的列，減少資料傳輸量。
    • 為欄位選擇合適的資料型別，以減少儲存空間並提高處理速度。
    • 合理設計表結構，避免過度複雜的關聯關係，以提高查詢效率。
  2. 索引最佳化：
    • 建立合適的索引，例如B樹索引或雜湊索引，以提高查詢速度。
    • 理解索引的工作原理，避免在大量重複值的列上建立索引，因為這樣會導致索引效率降低。
    • 使用複合索引時，遵循最左字首原則，即查詢條件應包含複合索引的最左側列。
  3. 查詢最佳化：
    • 使用EXPLAIN分析查詢語句，瞭解MySQL是如何處理語句的，從而進行查詢最佳化器的最佳化。
    • 避免在WHERE子句中使用函式或進行計算，因為這會阻止使用索引。
    • 對於大資料集，考慮分頁查詢最佳化，但要注意LIMIT語句可能導致的效能問題。
    • 合理使用連線和子查詢，避免不必要的資料關聯和複雜的查詢結構。
    • 在適當的情況下，考慮使用索引覆蓋，這樣可以直接從索引中獲取所需資料，而無需訪問資料表的行。

      • 索引覆蓋指的是查詢所需的所有列都包含在索引中，因此不需要回表查詢資料行。這通常透過建立包含所有需要查詢欄位的複合索引來實現。例如：

        • 　　　 
          建立一個包含name和age欄位的複合索引。
SQL查詢使用這兩個欄位作為過濾條件。
CREATE TABLE user ( id INT PRIMARY KEY, name VARCHAR(20), age INT, INDEX name_age (name, age) ) ENGINE=InnoDB; SELECT id, name FROM user WHERE name = 'shenjian' AND age = 30;

在這個例子中，如果EXPLAIN的結果中Extra欄位顯示為Using index，則表示觸發了索引覆蓋
    • 利用索引下推技術，儘早使用索引進行資料篩選，減少不必要的資料讀取。
      • 索引下推是MySQL 5.6引入的一個特性，它允許在查詢執行過程中，將過濾條件下推到儲存引擎層，減少“回表查詢”的次數。這意味著，即使某些記錄不滿足查詢條件，也可以在索引層被排除，而不是在所有記錄都被讀取之後。例如： 
        • 假設有一個包含name、age和sex欄位的表。
SQL查詢使用了這三個欄位作為過濾條件。
SELECT * FROM tuser WHERE name = 'zou' AND age = 10 AND sex = 1;
這個查詢可以透過索引下推來最佳化，因為即使某些記錄不滿足所有過濾條件，也可以在索引層被排除。

相關文章