索引效率的惊人体现以及索引的最左匹配原则
不加索引的查询
加上索引之后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手动)类型转换),会导致索引失效从而转向全表扫描。