Mysql复合索引的顺序和必要值

php基础

浏览数:240

2019-3-21

AD:资源代下载服务

创建表:

    CREATE TABLE article (
    id INT (10) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
    author_id INT (10) UNSIGNED NOT NULL,
    category_id INT (10) UNSIGNED NOT NULL,
    VIEW INT (10) UNSIGNED NOT NULL,
    comments INT (10) UNSIGNED NOT NULL,
    title VARBINARY (255) NOT NULL,
    content text NOT NULL
);

插入数据:

INSERT INTO article (
    author_id,
    category_id. VIEW,
    omments,
    title,
    content
)VALUES
    (1, 1, 1, 1, '1', '1'),
    (2, 2, 2, 2, '2', '2'),
    (1,1,3,3,'3','3');
                    

创建索引:

create index idx_articl_ccv on article(category_id,comments,view);

分析:
查询和排序字段正好符合索引–> 用到索引

explain select id,author_id from article where category_id = 1 and comments =1 order by view desc limit 1;

交换顺序–> 依旧使用到了索引

去除中间的条件–> 依旧使用了索引:

explain select id,author_id from article where category_id = 1  order by view desc limit 1;

去除开头条件–> 没有使用索引了:

explain select id,author_id from article where comments =1 order by view desc limit 1;

总结:

所以在复合索引中,索引第一位的column很重要,只要查询语句包含了复合索引的第一个条件,基本上就会使用到该复合索引(可能会使用其他索引)。我们在建符合索引的时候应该按照column的重要性从左往右建。