美文网首页
单列索引与联合索引

单列索引与联合索引

作者: 少博先生 | 来源:发表于2019-01-20 18:10 被阅读0次

    一般在创建数据表的时候,就会根据表的业务属性,对查询比较频繁的字段建立索引,方便提高查询速度。如果该表存在多个字段查询频繁,是该建立多个单列索引还是创建一个多列联合索引呢?

    索引

    简单来说,索引就是一个指针,指向表里的数据。索引通常与相应的表时分开保存的,目的是提高检索的性能。索引的创建与删除不会影响数据本身,但会影响数据检索的速度。索引也会占据物理存储空间,可能比表本身还大,因此创建索引也要考虑存储空间。

    索引类型

    单列索引

    如果某个字段经常在where子句作为单独的查询条件,它的单列索引最为有效。单列索引是最简单常见的索引,基于一个字段创建。

    CREATE INDEX INDEX_NAME ON TABLE_NAME(COLUMN_NAME)
    
    唯一索引

    唯一索引用于改善性能和保证数据完整性,不允许表里有重复值,其他和普通单列索引一样。允许NULL值的字段不能创建唯一索引。

    CREATE UNIQUE INDEX INDEX_NAME ON TABLE_NAME(COLUMN_NAME)
    
    联合索引

    联合索引基于一个表中的多个字段的索引。联合索引中即使字段一样,但顺序不同,也属于不同的联合索引,查询速度也不同。

    CREATE INDEX INDEX_NAME ON TABLE_NAME(COLUMN1,COLUMN2)
    

    下面主要比较一下单列索引和联合索引

    单列索引&联合索引

    联合索引
    CREATE TABLE `student` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `stu_id` varchar(20) DEFAULT NULL COMMENT '学号',
      `name` varchar(30) DEFAULT NULL COMMENT '姓名',
      `phone` varchar(30) DEFAULT NULL COMMENT '电话',
      `address` varchar(30) DEFAULT NULL COMMENT '家庭住址',
      PRIMARY KEY (`id`),
      KEY `联合索引` (`stu_id`,`name`,`phone`)
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='学生表';
    
    student表

    1、针对索引字段stu_id查询,联合索引有效

    EXPLAIN
    SELECT * from student where stu_id = '004';
    

    2、针对索引字段name查询,联合索引无效

    EXPLAIN
    SELECT * from student where name = '小军';
    

    3、针对索引字段phone查询,联合索引无效

    EXPLAIN
    SELECT * from student where phone = '14785454554';
    

    4、针对索引字段stu_id和phone查询,联合索引有效

    EXPLAIN
    SELECT * from student where stu_id = '004' AND phone = '14785454554';
    

    5、针对索引字段stu_id和name查询,联合索引有效

    EXPLAIN
    SELECT * from student where stu_id = '004' AND name = '小军';
    

    6、针对索引字段phone和name查询,联合索引无效

    EXPLAIN
    SELECT * from student where phone = '14785454554' AND name = '小军';
    

    7、针对索引字段phone、name、stu_id查询,联合索引有效

    EXPLAIN
    SELECT * from student where phone = '14785454554' AND name = '小军' AND stu_id = '004';
    

    8、针对索引字段stu_id、name查询,不是and,是or,联合索引无效

    EXPLAIN
    SELECT * from student where stu_id = '004' OR name = '小军';
    
    单列索引

    删除掉联合索引,分别给stu_id、name、phone创建单列索引

    ALTER TABLE student DROP INDEX 联合索引;
    ALTER TABLE student ADD INDEX 学号索引(stu_id);
    ALTER TABLE student ADD INDEX 姓名索引(name);
    ALTER TABLE student ADD INDEX 电话索引(phone);
    

    1、针对索引字段stu_id查询,学号索引有效

    EXPLAIN
    SELECT * from student where stu_id = '004';
    

    2、针对索引字段name查询,姓名索引有效

    EXPLAIN
    SELECT * from student where name = '小军';
    

    3、针对索引字段phone查询,电话索引有效

    EXPLAIN
    SELECT * from student where phone = '14785454554';
    

    4、针对索引字段stu_id、phone查询,学号索引有效,电话索引无效

    EXPLAIN
    SELECT * from student where stu_id = '004' AND phone = '14785454554';
    

    5、针对索引字段stu_id、phone查询,学号索引有效,电话索引无效(跟where后面的条件顺序无关)

    EXPLAIN
    SELECT * from student where phone = '14785454554' AND stu_id = '004' ;
    

    6、针对索引字段stu_id、phone、name查询,学号索引有效,电话索引无效、姓名索引无效

    EXPLAIN
    SELECT * from student where phone = '14785454554' AND name = '小军' AND 
    stu_id = '004';
    

    7、针对索引字段phone、name查询,or关系,姓名索引、电话索引均无效

    EXPLAIN
    SELECT * from student where phone = '14785454554' OR name = '小军';
    
    单列索引、联合索引同时存在
    ALTER TABLE student ADD INDEX 联合索引 (stu_id,name, phone);
    EXPLAIN
    SELECT * from student where stu_id = '001';
    
    总结

    1、创建联合索引时,要考虑列的顺序,如果使用前几列查询,联合索引有效,后几列查询,联合索引无效。
    2、联合索引使用最左前缀原则,例如A,B两个字段都会在查询中用到,但A使用的频率更高,就将A作为联合索引的第一个字段,放在最左边。
    3、当存在多个单列索引可以用时,mysql会根据查询优化策略选择其中一个单列索引,并不是每个单列索引都生效。
    4、当同时存在单列索引和联合索引,mysql会根据查询优化策略选择其中一个索引。
    5、如果where中的关系是or,索引不生效。

    相关文章

      网友评论

          本文标题:单列索引与联合索引

          本文链接:https://www.haomeiwen.com/subject/tsbxjqtx.html