mysql可以创建联合索引吗

2024-07-24 12:34:22

字体：大中小

来源：转载

供稿：网友

　　什么是联合索引？联合索引的本质：最左匹配

　　两个或更多个列上的索引被称作联合索引，联合索引又叫复合索引。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表进行演示。

　　mysql可不可以创建联合索引

　　以下语句在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）查找姓氏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）查找姓氏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');
　
　　查询优化器不能在以下查询中使用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可不可以创建联合索引”的学习就结束了，希望能够解决大家的疑惑。

（编辑：武林网）

上一篇：mysql怎样取字符串前几位

下一篇：mysql中between and咋用