实践理解mysql的联合索引

B+树

mysql索引的数据结构最常见的是B+树。
在B+树中，所有数据记录都是放在同一层的叶子节点上，并且是按键值大小顺序存放的。
有序的存放比无序的存放，查询速度更快。
B+树的中间节点只存放指向下一层节点的指针，这样能让查询更快，叶子节点存储的数据更多。
B+树的高度一般都在2-4层，也就是说查找某一键值的行记录时，最多只需要2到4次IO。

联合索引

联合索引，是指将表上的多个列作为一个索引。

索引的底层是一颗B+树，联合索引也是一颗B+树，只不过联合索引的健值数量不是一个，而是多个。

注：图片出自《Mysql技术内幕InnoDB存储引擎》
可以看到，联合索引对应的键值(a,b)，分别是 (1,1)、(1,2)、(2,1)、(2,4)、(3,1)、(3,2)，
查询条件，我们按照a排序，明显是有序的 1、1、 2、2、3、3。
查询条件，我们按照(a,b)排序，也是有序的。a明显是有序，而当a值相同时，b值也是有序的。比如(1,1)和(1,2)
查询条件，我们按照b排序，就不是有序的了。1、2、1、4、1、2。

联合索引的结构，可以类比电话簿，人名由姓和名构成，
电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。
如果知道姓，电话簿是有序的；
如果知道姓和名，电话簿也是有序的，
但如果只知道名不姓，电话簿是无序的。
这个其实就是最左匹配原则。

最左匹配原则

联合索引(a,b,c)，最左优先，从联合索引最左边的第一个字段进行查询，就会走联合索引，比如(a,b,c)、(a,b)或者(a,c)。
mysql的查询优化器会自动优化查询条件中的顺序，(b,a)相当于(a,b)。
在创建联合索引时，可以把查询比较频繁的查询条件放在最左边。
联合索引遇到(is not null、between、or)就会停止匹配，不走联合索引。
如果遇到这种情况，可以建立一个单独索引。

区分度

区分度不高的字段，不建议加索引。比如性别字段，基本只有男女两种，区分度不高，没有必要加索引。

示例

• 创建数据表：

在表上创建联合索引 idx_order(order_id, user_id, pay_status)

CREATE TABLE t_index_test (                                                                                                                   
  id INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键,自增id',                                                                              
  order_id VARCHAR(25) NOT NULL COMMENT '订单号',
  user_id INT(11) NOT NULL COMMENT '用户id',                                                                                             
  pay_status TINYINT(4) DEFAULT 0 COMMENT '1：未支付,2：支付成功,3：支付失败, 4：已下单,5：申请退款,6：退款成功,7：退款失败',
  create_time TIMESTAMP  COMMENT '更新时间', 
  PRIMARY KEY (id),                                                                                 
  INDEX idx_order(order_id, user_id, pay_status)                                                                                                                      
) ENGINE=INNODB DEFAULT CHARSET=utf8   

• 插入数据：

INSERT INTO t_index_test (order_id,user_id,pay_status)  VALUES('abc','123',2); 
INSERT INTO t_index_test (order_id,user_id,pay_status)  VALUES('abc','456',2); 
INSERT INTO t_index_test (order_id,user_id,pay_status)  VALUES('def','123',1);

EXPLAIN

首先，了解一下EXPLAIN， 它可以对 SELECT 语句进行分析，并输出 SELECT 执行的详细信息，方便针对性地优化。

select_type: SELECT 查询的类型。包括SIMPLE、PRIMARY、UNION、UNION RESULT等
table: 查询的是哪个表
partitions: 匹配的分区
type: 类型。type值为all，表示全表扫描。type值为const，说明使用了主键索引。
不同的 type 类型的性能关系如下:
ALL < index < range ~ index_merge < ref < eq_ref < const < system。
possible_keys: 此次查询中可能选用的索引
key: 此次查询中确切使用到的索引.
ref: 哪个字段或常数与 key 一起被使用
rows: 显示此查询一共扫描了多少行. 这个是一个估计值.
filtered: 表示此查询条件所过滤的数据的百分比
Extra :额外信息,比如using index表示使用覆盖索引,using where表示在存储引擎之后进行过滤,using temporary表示使用临时表,using filesort表示对结果进行外部排序。

EXPLAIN结果，重点看key这个字段，key表示此次查询中确切使用到的索引.
其次看 type，有没有走索引。
还可以看下 rows，扫描了多少行，扫描的行越少越好。
如果Extra 中，出现了 using filesort，也要注意，尽量避免。

详情见： https://segmentfault.com/a/1190000008131735

联合索引的查询条件

• 联合索引(a,b,c)，查询条件为 (a,b,c)

EXPLAIN SELECT * FROM t_index_test  WHERE order_id='abc' AND user_id='123' AND pay_status=2

EXPLAIN 结果： 查询条件为 (a,b,c)走索引。

• 联合索引(a,b,c)，查询条件为 (a,b)

EXPLAIN SELECT * FROM t_index_test  WHERE order_id='abc' AND user_id='123'

EXPLAIN 结果： 查询条件为 (a,b)走索引。

• 联合索引(a,b,c)，查询条件为 (a,c)

EXPLAIN SELECT * FROM t_index_test  WHERE order_id='abc' AND pay_status=2

EXPLAIN 结果： 查询条件为 (a,c)走索引。

• 联合索引(a,b,c)，查询条件为 (b,a)

EXPLAIN SELECT * FROM t_index_test  WHERE  user_id='123' AND order_id='abc' 

EXPLAIN 结果： 查询条件为 (b,a)走索引。 这是因为mysql会自动调节查询条件中的顺序，(b,a)相当于(a,b)

• 联合索引(a,b,c)，查询条件为 (b)

EXPLAIN SELECT * FROM t_index_test  WHERE  user_id='123' ;

EXPLAIN 结果： 查询条件为 (b)不走索引

• 联合索引(a,b,c)，查询条件为 (b,c)

EXPLAIN SELECT * FROM t_index_test  WHERE user_id='123' AND pay_status=2

EXPLAIN 结果： 查询条件为 (b,c)不走索引

• 联合索引(a,b,c)，查询条件为 (a is not null, b, c)

EXPLAIN SELECT * FROM t_index_test  WHERE order_id is not null AND user_id='123' AND pay_status=2;

EXPLAIN 结果： 查询条件为 (a is not null, b, c) 不走索引

• 联合索引(a,b,c)，查询条件为 (a OR b)

EXPLAIN SELECT * FROM t_index_test  WHERE order_id='abc' OR user_id='123' 

EXPLAIN 结果： 查询条件为 (a OR b) 不走索引

联合索引和多个单列索引的区别

• 创建新的数据表，设置多个单独索引

CREATE TABLE t_index_test2 (                                                                                                                   
  id INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键,自增id',                                                                              
  order_id VARCHAR(25) NOT NULL COMMENT '订单号',
  user_id INT(11) NOT NULL COMMENT '用户id',                                                                                             
  pay_status TINYINT(4) DEFAULT 0 COMMENT '支付状态',                                                                                                                                                                                                                                   
  create_time TIMESTAMP  COMMENT '更新时间', 
  PRIMARY KEY (id),                                                                                 
  INDEX (order_id),                                                                                                                      
  INDEX (user_id),                                                                                                                      
  INDEX (pay_status)                                                                                                                      
) ENGINE=INNODB DEFAULT CHARSET=utf8  COMMENT '使用单列的索引' 

• 多个单列索引，查询条件为 (a , b, c)

EXPLAIN SELECT * FROM t_index_test2  WHERE order_id='abc' AND user_id='123' AND pay_status=2

EXPLAIN结果，走了单列索引的其中一个。
mysql优化器的优化策略，当多个查询条件时，mysql优化器会评估用哪个条件的索引效率最高，它会选择最佳的索引去使用。

• 多个单列索引，查询条件为 (a OR b)

EXPLAIN SELECT * FROM t_index_test2  WHERE order_id='abc' OR user_id='123' 

EXPLAIN结果，不走索引。

联合索引和多个单列索引的区别: 联合索引有最左匹配原则，而多个单列索引没有。

结论：

联合索引(a,b,c)，
查询条件为 (a,b,c)走索引，查询条件为 (a,b)走索引，查询条件为 (a,c)走索引，
查询条件为 (b)不走索引，查询条件为 (b,c)不走索引，
查询条件为 (b,a)会走索引，因为 (b,a) 会被mysql优化器转化为 (a,b),
查询条件为 (a is not null, b, c) 不走索引，
查询条件为 (a OR b) 不走索引。

参考资料：

《Mysql技术内幕InnoDB存储引擎》
https://blog.csdn.net/Abysscarry/article/details/80792876