索引效率的惊人体现以及索引的最左匹配原则

最新推荐文章于 2025-03-12 21:53:13 发布

原创最新推荐文章于 2025-03-12 21:53:13 发布 · 710 阅读

0 ·

CC 4.0 BY-SA版权

阿里巴巴盒马大量招聘 hc多多提供全程面试辅导简历砸过来 laixiaoxing.lxx@alibaba-inc.com 5年内的p6 7年以上的p7 机会多多

mysql 专栏收录该内容

54 篇文章

订阅专栏

本文深入探讨了SQL查询中索引的重要性，通过一个复杂的查询案例，解析了如何利用索引提高查询效率，特别是针对organize_id、invoice_source和invoice_status字段的优化策略，以及索引最左匹配原则的应用。

不加索引的查询在这里插入图片描述

加上索引之后
在这里插入图片描述
organize_id, invoice_source, invoice_status

在这里插入图片描述

sql 如下

SELECT organize_id as 帐套id
, sum(case when auth_time BETWEEN ‘2018-11-09’ and ‘2018-11-30’ and invoice_source=2 and invoice_status=1 then tax_amount else 0 end) 进项税额
, sum(case when transfer_date BETWEEN ‘2018-11-09’ and ‘2018-11-30’ and invoice_source=4 and invoice_status=1 then tax_amount else 0 end) 进项转出税额
, sum(case when make_invoice_time BETWEEN ‘2018-11-09’ and ‘2018-11-30’ and invoice_source=1 and is_taxation=0 and invoice_status=1 then full_amount else 0 end) 销项不含税金额
, sum(case when make_invoice_time BETWEEN ‘2018-11-09’ and ‘2018-11-30’ and invoice_source=1 and is_taxation=0 and invoice_status=1 then tax_amount else 0 end) 销项税额
, sum(case when make_invoice_time BETWEEN ‘2018-11-09’ and ‘2018-11-30’ and invoice_source=1 and is_taxation=1 and invoice_status=1 then full_amount else 0 end) 简易征收销项不含税金额
, sum(case when make_invoice_time BETWEEN ‘2018-11-09’ and ‘2018-11-30’ and invoice_source=1 and is_taxation=1 and invoice_status=1 then tax_amount else 0 end) 简易征收销项税额
, sum(case when make_invoice_time BETWEEN ‘2018-11-09’ and ‘2018-11-30’ and invoice_source=1 and tax_rate=0.17 or tax_rate=0.16 and invoice_status=1 then full_amount else 0 end) 未开票直租不含税金额
, sum(case when make_invoice_time BETWEEN ‘2018-11-09’ and ‘2018-11-30’ and invoice_source=1 and tax_rate=0.17 or tax_rate=0.16 and invoice_status=1 then tax_amount else 0 end) 未开票直租税额
, sum(case when make_invoice_time BETWEEN ‘2018-11-09’ and ‘2018-11-30’ and invoice_source=1 and tax_rate=0.06 and invoice_status=1 then full_amount else 0 end) 未开票回租不含税金额
, sum(case when make_invoice_time BETWEEN ‘2018-11-09’ and ‘2018-11-30’ and invoice_source=1 and tax_rate=0.06 and invoice_status=1 then tax_amount else 0 end) 未开票回租税额
, sum(case when make_invoice_time BETWEEN ‘2018-11-09’ and ‘2018-11-30’ and invoice_source=1 and invoice_type=5 and invoice_status=1 then tax_amount else 0 end) 购置税扣除额
FROM t_invoice_detail where
organize_id in(‘0101001’,‘0101002’,‘0101003’,‘0101004’) GROUP BY organize_id

sql较为复杂，其中关键字段为organize_id 和 invoice_source以及invoice_status。
按照索引的最左匹配原则，顺序如上。

索引的最左匹配原则举例：
比如说联合索引字段(A,B,C)

查询的时候有(A) (A,B) (A，B，C) 这三种情况均生效
但是比如说B BC 这种就不生效。 AC 则A生效

总结就是带头大哥不能少，中间兄弟不能断
另外注意！
在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效从而转向全表扫描。