月度归档： 2018 年 10 月

背景

项目中，遇到一个统计需求，从某张表中按照条件分别统计。刚开始想到用union all的写法，太臃肿，后来使用count(distinct case when)解决此问题

count

数据统计中，count出现最频繁

最简单的用法

select count(*) from table where ....

select count(distinct xx) from table where ...

但最简单的用法也有其深刻的地方，比如这里其实有3种写法，count(1)、count(*)、count(字段)，它们有什么区别呢？

• count(1) 和 count(*)

count(1)和count(*)差别不大，使用count(*)时，sql会帮你自动优化，指定到最快的字段。所以推荐使用count(*)

• count(*) 和 count(字段)

count(*)会统计所有行数，count(字段)不会统计null值

count(case when)

条件统计，即对某个table分条件统计，比如表test_db，有一个字段user_id(可能重复), gender(man、women)，需要统计man和women的人数

可以使用where分别统计

select count(distinct user_id) as man_cnt from test_db where gender = 'man'

select count(distinct user_id) as women_cnt from test_db where gender = 'women'

也可以使用按条件统计

select count(distinct case gender = 'man' then user_id end) as man_cnt

, count(distinct case gender = 'women' then user_id end) as woman_cnt

from test_db

背景

sql中表示不等于关系时，有两种写法!=、<>，抱着刨根问底的精神，看看这两个符号到底有什么不同

标准答案

https://stackoverflow.com/questions/18015422/what-is-difference-between-and-in-sql-server

搬运过来

这两个符号没有任何区别，你使用哪个都一样。

Microsoft的文档中，这两个符号的意义相同，都是 Not Equal To

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/not-equal-to-transact-sql-exclamation?view=sql-server-2017

但，<>是ANSI 99 SQL标准中的定义，!=不是。所以，不是所有的DB引擎都支持!=。

最终推荐使用<>。

背景

项目使用mysql数据库，某张表数据量2000W左右，比较大，在组合查询时发生超时，需要优化。

优化

查询优化常用方法就是建立索引

查询语句样例：查询在20181001-20181010时间范围内A=a且B=b的数据

select * from table where A = 'a' and B = 'b' and Date > '20181001' and Date < '20181010'

按照查询建立了一个组合索引 idx_query(A, B, Date)，查询仍然报超时。然后开始研究索引原理，最后更改索引中列的顺序为idx_query(B,A,Date)解决。

---------------------------------------------------------------------------------------------

上面只是简单的项目背景，下面从原理细细解读索引

索引原理

首先，出问题以后再建立索引，是一种不好的习惯。排查、定位、解决问题会耗费很多开发时间和精力。

当然，不充分思考就给大多数列建立单列索引，给所有查询建立联合索引，是一种更坏的习惯。毕竟删除索引风险远高于添加索引。

使用索引需要成本：

• 空间成本：索引是占空间的，大表的联合索引占用空间不可忽略
• 时间成本：使用不对的索引会浪费时间
• 更新成本：增删改操作只要跟索引有关系，就需要更新索引

索引适合在返回大表中很小一部分数据时使用，很小当然越小越好，5%以下还可以，1%以下更好，如果表的数据量非常大，千万级别，返回行数要小到0.1%以下

从很大表中用联合条件查询出很小的数据，等价于count(distinct 联合条件) 的值很大，这个值被称为Cardinality，索引适合度=Cardinality/表记录数。

索引误区

多个单列索引(如idx_A, idx_B)不等同于联合索引(idx_A_B)

两个联合索引里的列顺序不同，这两个联合索引也不等价，适用场景不同

联合索引idx_A_B能够覆盖的场景

• A = ？ and B = ？
• A = ？ and B in (?, ?, ?)
• A = ?
• A in (?,?,?)

覆盖场景可以根据索引存储结构(B+Tree)推导出。

上面罗列的几种覆盖场景，效率由上至下越来越低，因为扫描行数越来越大

联合条件中，最左侧的索引列很重要，最好使用Cardinality最大的列。

总结

项目中遇到的问题，最终根据索引原理中 最左侧索引列使用Cardinality最大列 解决。