转载

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

创建表:

    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的重要性从左往右建。

问题背景 : 

当我们需要创建一个组合索引, 索引的顺序对于效率影响很大, 怎么确定索引的顺序;

解决方法 : 

我们应该依据字段的全局基数和选择性, 而不是字段的某个具体的值来确定;

 

表结构 : 

dc_listing

 

代码 : 

SELECT
  COUNT(DISTINCT accountId) / COUNT(*) AS accountId_sel,
  COUNT(DISTINCT userName) / COUNT(*) AS userName_sel,
  COUNT(*)
FROM
  dc_listing

 

结果: 

 

可以看到accountId的选择性更高, 所以我们应该讲accountId这个字段放在组合索引前面

 

存在问题 : 

对于一些特殊的值, 比如超级用户, 如果他满足大部分的查询条件, 即这个基数对这个值可以说是无效的参考.

避免这种问题, 只能从代码层面屏蔽, 区分这类数据合组, 禁止针对这类值进行这个查询;

参考 msyql高性能 5.3.4 章节, 有更详细例子

摘自:https://www.cnblogs.com/meibao/p/6204368.html?utm_source=gold_browser_extension

        https://segmentfault.com/a/1190000008666078

正文到此结束
Loading...