• 技术文章 >数据库 >mysql教程

    mysql可以创建联合索引吗

    青灯夜游青灯夜游2022-06-15 16:22:42原创127

    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索引用于在以下的查询查找:

    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视频教程

    以上就是mysql可以创建联合索引吗的详细内容,更多请关注php中文网其它相关文章!

    声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn核实处理。
    专题推荐:mysql
    上一篇:mysql怎么取字符串前几位 下一篇:mysql日期用什么类型
    20期PHP线上班

    相关文章推荐

    • 【活动】充值PHP中文网VIP即送云服务器• mysql的长整型是什么• mysql怎么查询两个字段的值• mysql怎么将时分秒转换成秒数• mysql中order by怎么用• linux怎么查询mysql端口
    1/1

    PHP中文网