联合索引的创建原则

• 使用次数较为频繁的放在最左端。（此时甚至可为其单独建立索引）
• 联合索引存在“最左原则”。
• 最左原则：以最左边的为起点，任意连续的索引都能匹配上。

下面使用具体的例子说明

索引图

如上，在asset_borrow_order表中添加如上的联合索引。

EXPLAIN SELECT * from asset_borrow_order WHERE user_phone = '13002398278' and created_at ='2019-02-14 17:26:49' and user_id = 895;
EXPLAIN SELECT * from asset_borrow_order WHERE user_phone = '13002398278' and user_id = 895 and created_at ='2019-02-14 17:26:49' ;

差异图

我们在这里调换顺序，发现联合索引仍然生效。

EXPLAIN SELECT * from asset_borrow_order WHERE user_phone = '13002398278' and created_at ='2019-02-14 17:26:49';  -- 1
EXPLAIN SELECT * from asset_borrow_order WHERE user_phone = '13002398278' and user_id =895 ; -- 2

EXPLAIN SELECT * from asset_borrow_order WHERE created_at = '2019-02-14 17:26:49' and user_id =895 ; -- 3
EXPLAIN SELECT * from asset_borrow_order WHERE user_id =895 and created_at = '2019-02-14 17:26:49'; -- 4

上述是联合索引其中的两个，通过分析我们得知1和2 是生效的，3和4索引是失效的。

EXPLAIN SELECT * from asset_borrow_order WHERE user_phone = '13002398278'; -- 5
EXPLAIN SELECT * from asset_borrow_order WHERE user_id =895 ; -- 6
EXPLAIN SELECT * from asset_borrow_order WHERE created_at = '2019-02-14 17:26:49'; -- 7

上述联合索引其中的一个，通过分析仅有5是生效的，6和7是无效的。