联合索引最左匹配-实测

explain各个参数的含义

• id：表示查询中执行select子句或操作表的顺序，如果id相同，则执行顺序从上至下，如果是子查询，id的序号会递增，id越大则优先级越高，越先会被执行。
• select_type：有多种，常见的是simple
  • simple：表示不需要union操作或者不包含子查询的简单select查询。有连接查询时，外层的查询为simple，且只有一个。
• table：显示的查询表名，如果查询使用了别名，那么这里显示的是别名。
• type：依次从好到差：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL。
• possible_keys：查询可能使用到的索引都会在这里列出来。
• key：查询真正使用到的索引
• key_len：用于处理查询的索引长度
• ref：
  • 如果是使用的常数等值查询，这里会显示const
  • 如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段
  • 如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func。
• rows：这里是执行计划中估算的扫描行数，不是精确值。
• extra：这个列可以显示的信息非常多，有几十种，常用的有
  • using index：查询时不需要回表查询，直接通过索引就可以获取查询的数据。
  • using where：表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤
• filtered：表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。

准备条件

• 版本：mysql 5.7.28
• 共10行数据，abcd4列，建立abc的联合索引
• a列
  • varchar 取值：甲、乙、丙、丁，其中值为甲有3个
• b列
  • integer, 取值范围0-100
• c列
  • tinyInt ，取值：0、1、2
• d列
  • varchar 取值：钾、钙、钠、镁
• 建表语句

  CREATE TABLE `idx_abc` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `a` varchar(255) DEFAULT NULL,
    `b` int(255) DEFAULT NULL,
    `c` tinyint(255) DEFAULT NULL,
    `d` varchar(255) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_abc` (`a`,`b`,`c`)
  ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

• 数据截图
  

explain查询

• a：走a的索引

  EXPLAIN SELECT * FROM idx_abc WHERE a="甲";

• ab：走ab的索引

  EXPLAIN SELECT * FROM idx_abc WHERE a='甲' and b=11;

• ac：走a的索引

  EXPLAIN SELECT * FROM idx_abc WHERE a='甲' and c=2;

• bc：全表扫描

  EXPLAIN SELECT * FROM idx_abc WHERE b=11 and c=2;

• abc：走abc的索引

  EXPLAIN SELECT * FROM idx_abc WHERE a='甲' and b=11 and c=2;

• ad：走a的索引

  EXPLAIN SELECT * FROM idx_abc WHERE a='甲' and d='钠';

• abcd：走abc的索引

  EXPLAIN SELECT * FROM idx_abc WHERE a='甲' and b=11 and c=2 and d='钠';

• b or c：全表扫描

EXPLAIN SELECT * FROM idx_abc WHERE b=11;

结论

• 建立abc的联合索引，相当于建立了a、ab、ac、abc四个索引。注意是相当于，并不是真正建立
• 联合索引的第一列肯定是有序的
• 联合索引的第二列不一定是有序的，但是在第一列值相同的第二列是有序的
• 联合索引的b列和c列，是没有索引的

下一步工作

• 聚簇索引和非聚簇索引
• 覆盖索引
• mysql索引长度的意义
• filtered的值分析
• 索引设计优化