网上那么多关于组合索引详解 最左侧原理 : 个人感觉99%都是讲不清楚的废话,看完 都不知道在讲什么

因此在此写一下!

原理:你使用的sql能否用到组合索引?

结论:你建立的组合索引(a,b,c,d) 无论怎么使用只要和a没有组合(最左面的那个),那么都没有使用上索引!!!!!

下面是验证:EXPLAIN 观察 type 列

  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