联合索引的创建原则
- 使用次数较为频繁的放在最左端。（此时甚至可为其单独建立索引）
- 联合索引存在“最左原则”。
- 最左原则：以最左边的为起点，任意连续的索引都能匹配上。
下面使用具体的例子说明
索引图
如上，在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是无效的。
