网上那么多关于组合索引详解 最左侧原理 : 个人感觉99
因此在此写一下!
原理:你使用的sql能否用到组合索引?
结论:你建立的组合索引(a,b,c,d) 无论怎么使用只要和a没有组合(最左面的那个),那么都没有使用上索引!!!!!
下面是验证:EXPLAIN 观察 type 列
相关连接:[https://www.jianshu.com/p/ea3fc71fdc45(https://www.jianshu.com/p/ea3fc71fdc45)
select table_name,
DATA_LENGTH as tablesData,
INDEX_LENGTH as indexData
from information_schema.tables
where table_schema='test'
ORDER BY indexData desc;
-- 索引 ON `index` (a, b, c);
EXPLAIN
SELECT *
FROM `index` WHERE a='a';-- type=ref ref=const rows =1 true
EXPLAIN
SELECT *
FROM `index` WHERE b='b'; -- type=ALL rows=3 false
EXPLAIN
SELECT *
FROM `index` WHERE c='c'; -- type=ALL rows=3 false
EXPLAIN
SELECT *
FROM `index` WHERE a='a' AND b='b'; -- type=ref ref=const rows =1 true
EXPLAIN
SELECT *
FROM `index` WHERE b='b' AND a='a';-- type=ref ref=const rows =1 true
EXPLAIN
SELECT *
FROM `index` WHERE a='a' AND c='c';-- type=ref ref=const rows =1 true
EXPLAIN
SELECT *
FROM `index` WHERE c='c' AND a='a';-- type=ref ref=const rows =1 true
EXPLAIN
SELECT *
FROM `index` WHERE c='c' AND b='b';-- type=ref ref=const rows =1 false
EXPLAIN
SELECT *
FROM `index` WHERE b='b' AND c='c';-- type=ref ref=const rows =1 false
EXPLAIN
SELECT *
FROM `index` WHERE a='a' AND b='b' AND c='c';-- type=ref ref=const rows =1 true
EXPLAIN
SELECT *
FROM `index` WHERE a='a' AND c='c' AND b='b';-- type=ref ref=const rows =1 true
EXPLAIN
SELECT *
FROM `index` WHERE b='b' AND c='c' AND a='a';-- type=ref ref=const rows =1 true
EXPLAIN
SELECT *
FROM `index` WHERE b='b' AND a='a' AND c='c';-- type=ref ref=const rows =1 true
EXPLAIN
SELECT *
FROM `index` WHERE c='c' AND b='b' AND a='a';-- type=ref ref=const rows =1 true
EXPLAIN
SELECT *
FROM `index` WHERE c='c' AND a='a' AND b='b';-- type=ref ref=const rows =1 true