技術文章 » mysql可以建立聯合索引嗎

mysql可以建立聯合索引嗎

2022-06-15 18:00:54

mysql可以建立聯合索引。MySQL允許使用者建立一個最多包含16列的聯合索引，建立方法有兩個：1、建立表時建立，語法「CREATE TABLE 表名 (列名1 型別 PRIMARY KEY,列名2 型別,列名3 型別,...INDEX 索引名 (列名2,列名3...));」；2、修改表時建立，語法「CREATE INDEX 索引名 ON 表名(列名2,列名3,列名4);」。

本教學操作環境：windows7系統、mysql8版本、Dell G3電腦。

什麼是聯合索引？聯合索引的本質：最左匹配

兩個或更多個列上的索引被稱作聯合索引，聯合索引又叫複合索引。MySQL允許使用者建立一個最多包含16列的複合索引。

對於複合索引:Mysql從左到右的使用索引中的欄位，一個查詢可以只使用索引中的一部份，但只能是最左側部分。

例如索引是key index (a,b,c). 可以支援a | a,b| a,b,c 3種組合進行查詢，但不支援 b,c進行查詢 .當最左側欄位是常數參照時，索引就十分有效。

查詢優化器將複合索參照於測試索引中所有列的查詢，或者測試第一列，前兩列等的查詢。

如果在索引定義中以正確的順序指定列，則單個複合索引可以在同一個表上加速這些型別的查詢。

建立表時建立聯合索引

語法：

CREATE TABLE 表名 (
    c1 data_type PRIMARY KEY,
    c2 data_type,
    c3 data_type,
    c4 data_type,
    INDEX 索引名 (c2,c3,c4)
);

在此語法中，聯合索引由三列c2，c3和c4組成。

修改表時建立聯合索引

可以使用以下CREATE INDEX語句將複合索引新增到現有表：

CREATE INDEX 索引名 
ON 表名(c2,c3,c4);

請注意，如果您在（c1，c2，c3）上有複合索引，則您將在以下列組合之一上建立索引搜尋功能：

(c1)
(c1,c2)
(c1,c2,c3)

例如：

SELECT
    *
FROM
    table_name
WHERE
    c1 = v1;
 
 
SELECT
    *
FROM
    table_name
WHERE
    c1 = v1 AND 
    c2 = v2;
 
 
SELECT  
    *
FROM
    table_name
WHERE
    c1 = v1 AND 
    c2 = v2 AND 
    c3 = v3;

如果列不形成索引的最左字首，則查詢優化器無法使用索引執行查詢。例如，以下查詢無法使用複合進行查詢：

SELECT
    *
FROM
    table_name
WHERE
    c1 = v1 AND 
    c3 = v3;

MySQL聯合索引範例

我們將使用範例資料庫中的employees表進行演示。

1.png

以下語句在lastName和firstName列上建立聯合索引：

CREATE INDEX name 
ON employees(lastName, firstName);

  • 首先，name索引可用於指定lastName值的查詢中的查詢，因為lastName列是索引的最左字首。

  • 其次，name索引可用於指定lastName和firstName值組合的值的查詢。

name索參照於在以下的查詢查詢：

1）查詢姓氏為的員工 Patterson

SELECT 
    firstName, 
    lastName, 
    email
FROM
    employees
WHERE
    lastName = 'Patterson';

此查詢使用名稱索引，因為索引的最左邊字首（即lastName列）用於查詢。

您可以通過EXPLAIN在查詢中新增子句來驗證這一點：

EXPLAIN SELECT 
    firstName, 
    lastName, 
    email
FROM
    employees
WHERE
    lastName = 'Patterson';

這是輸出：

2.png

2）查詢姓氏Patterson和名字的員工Steve：

SELECT 
    firstName, 
    lastName, 
    email
FROM
    employees
WHERE
    lastName = 'Patterson' AND
    firstName = 'Steve';

在此查詢中，兩個lastName和firstName列都用於查詢，因此，它使用name索引。

我們來核實一下：

EXPLAIN SELECT 
    firstName, 
    lastName, 
    email
FROM
    employees
WHERE
    lastName = 'Patterson' AND
    firstName = 'Steve';

輸出是：

3.png

3）查詢姓氏Patterson和名字是Steve或的員工Mary：

SELECT 
    firstName, 
    lastName, 
    email
FROM
    employees
WHERE
    lastName = 'Patterson' AND
    (firstName = 'Steve' OR 
    firstName = 'Mary');

此查詢類似於第二個查詢，其中兩個lastName和firstName列都用於查詢。

以下語句驗證索參照法：

EXPLAIN SELECT 
    firstName, 
    lastName, 
    email
FROM
    employees
WHERE
    lastName = 'Patterson' AND
    (firstName = 'Steve' OR 
    firstName = 'Mary');

輸出是：

4.png

查詢優化器不能在以下查詢中使用name索引進行查詢，因為只使用了firstName不是索引最左字首的列：

SELECT 
    firstName, 
    lastName, 
    email
FROM
    employees
WHERE
    firstName = 'Leslie';

類似地，查詢優化器不能在以下查詢中使用名稱索引進行查詢，因為firstName或者lastName列用於查詢。

SELECT 
    firstName, 
    lastName, 
    email
FROM
    employees
WHERE
    firstName = 'Anthony' OR
    lastName = 'Steve';

【相關推薦：】

以上就是mysql可以建立聯合索引嗎的詳細內容，更多請關注TW511.COM其它相關文章！