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)。
在创建联合索引时，可以把查询比较频繁的查询条件放在最左边。
联合索引遇到范围查询(!=、>、<、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(1) DEFAULT 0 COMMENT '支付状态',
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 执行的详细信息，方便针对性地优化。
EXPLAIN结果，重点看key这个字段，key表示此次查询中确切使用到的索引.
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: 额外的信息
联合索引的查询条件
- 联合索引(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)走索引。
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------------ ---------- ------ ------------- --------- ------- ----------------- ------ -------- --------
1 SIMPLE t_index_test (NULL) ref idx_order idx_order 83 const,const,const 1 100.00 (NULL)
- 联合索引(a,b,c)，查询条件为 (a,b)
EXPLAIN SELECT * FROM t_index_test WHERE order_id='abc' AND user_id='123'
EXPLAIN 结果： 查询条件为 (a,b)走索引。
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------------ ---------- ------ ------------- --------- ------- ----------- ------ -------- --------
1 SIMPLE t_index_test (NULL) ref idx_order idx_order 81 const,const 1 100.00 (NULL)
- 联合索引(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)
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------------ ---------- ------ ------------- --------- ------- ----------- ------ -------- --------
1 SIMPLE t_index_test (NULL) ref idx_order idx_order 81 const,const 1 100.00 (NULL)
- 联合索引(a,b,c)，查询条件为 (a,c)
EXPLAIN SELECT * FROM t_index_test WHERE order_id='abc' AND pay_status='2'
EXPLAIN 结果： 查询条件为 (a,c)走索引。
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------------ ---------- ------ ------------- --------- ------- ------ ------ -------- -----------------------
1 SIMPLE t_index_test (NULL) ref idx_order idx_order 77 const 2 33.33 Using index condition
- 联合索引(a,b,c)，查询条件为 (b,c)
EXPLAIN SELECT * FROM t_index_test WHERE user_id='123' AND pay_status='2'
EXPLAIN 结果： 查询条件为 (b,c)不走索引
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------------ ---------- ------ ------------- ------ ------- ------ ------ -------- -------------
1 SIMPLE t_index_test (NULL) ALL (NULL) (NULL) (NULL) (NULL) 3 33.33 Using where
- 联合索引(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) 不走索引
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------------ ---------- ------ ------------- ------ ------- ------ ------ -------- -------------
1 SIMPLE t_index_test (NULL) ALL idx_order (NULL) (NULL) (NULL) 3 33.33 Using where
- 联合索引(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) 不走索引
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------------ ---------- ------ ------------- ------ ------- ------ ------ -------- -------------
1 SIMPLE t_index_test (NULL) ALL idx_order (NULL) (NULL) (NULL) 3 33.33 Using where
- 联合索引(a,b,c)，查询条件为 (a OR b)
EXPLAIN SELECT * FROM t_index_test WHERE order_id='abc' OR user_id='123'
EXPLAIN 结果： 查询条件为 (a OR b) 不走索引
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------------ ---------- ------ ------------- ------ ------- ------ ------ -------- -------------
1 SIMPLE t_index_test (NULL) ALL idx_order (NULL) (NULL) (NULL) 3 55.56 Using where
联合索引和多个单列索引的区别
- 创建新的数据表，设置多个单独索引
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(1) 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优化器会评估用哪个条件的索引效率最高，它会选择最佳的索引去使用。
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------------- ---------- ------ --------------------------- -------- ------- ------ ------ -------- -------------
1 SIMPLE t_index_test2 (NULL) ref order_id,user_id,pay_status order_id 77 const 1 100.00 Using where
- 多个单列索引，查询条件为 (a OR b)
EXPLAIN SELECT * FROM t_index_test2 WHERE order_id='abc' OR user_id='123'
EXPLAIN结果，不走索引。
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------------- ---------- ------ ---------------- ------ ------- ------ ------ -------- -------------
1 SIMPLE t_index_test2 (NULL) ALL order_id,user_id (NULL) (NULL) (NULL) 1 100.00 Using where
- 多个单列索引，查询条件为 (a!= , b)
EXPLAIN SELECT * FROM t_index_test2 WHERE order_id!='abc' AND user_id='123'
EXPLAIN结果，走了第二个单列索引。
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------------- ---------- ------ ---------------- ------- ------- ------ ------ -------- -------------
1 SIMPLE t_index_test2 (NULL) ref order_id,user_id user_id 4 const 1 100.00 Using where
联合索引和多个单列索引的区别: 联合索引有最左匹配原则，而多个单列索引没有。
结论：
联合索引(a,b,c)，
查询条件为 (a,b,c)走索引，查询条件为 (a,b)走索引，查询条件为 (a,c)走索引，
查询条件为 (b,c)不走索引，查询条件为 (a!= , b, c) 不走索引，查询条件为 (a , b!=, c) 不走索引， 查询条件为 (a OR b) 不走索引。
参考资料：
《Mysql技术内幕InnoDB存储引擎》
https://blog.csdn.net/Abysscarry/article/details/80792876