实战

一、关键字in对MySQL使用索引的影响

结论：in肯定会走索引，但是当IN的取值范围较大时会导致索引失效，走全表扫描。

MySQL 需要 IN查询但是很慢怎么办

我们可以使用inner join代替in，具体如下

  1. select id,name
  2. from product
  3. where supplier_id in (select id from user_supplier where user_supplier.status = 1)
  5. --使用inner join 代替
  6. select t1.id,t1.name
  7. from product t1
  8. inner join user_supplier t2 on t1.supplier_id = t2.id
  9. where t2.status = 1

二、关键字not in对MySQL使用索引的影响

not in不会使用索引，索引我们要尽量必要在 SQL 中使用not in关键字

2.1、例子

select * from a
where a.id not in (
     select id from b
)

使用left join替代not in的逻辑是，a、b表使用left join连接时，对于未满足连接条件的查询结果来说b表对应的字段为null，为null则表示a.id并不在b.id中。

select a.* from a
left join b
     on a.id=b.id
where b.id is null

三、多表查询时使用前缀可以提高查询

列名前使用表名前缀可以提高查询效率，节省了 SQL 执行器判断字段属于哪个表的操作。

SELECT e.employee_id, e.last_name, e.department_id,
FROM   employees e , departments d
WHERE  e.department_id = d.department_id;

3.1、Alibaba 规范

【强制】对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名（或 表名）进行限定。

3.2、反例

在某业务中，由于多表关联查询语句没有加表的别名（或表名）的限制，正常运行两年后，最近在某个表中增加一个同名字段，在预发布环境做数据库变更后，线上查询语句出现出 1052 异常：Column ‘name’ in field list is ambiguous。

四、使用union all代替union

  • UNION 操作符返回两个查询的结果集的并集，去除重复记录。
  • UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。

执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

五、多表查询

5.1、Alibaba 规范

【强制】超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时， 保证被关联的字段需要有索引。即使双表 join 也要注意表索引、SQL 性能。

5.2、解释说明

我们要控制连接表的数量。多表连接就相当于嵌套 for 循环一样，非常消耗资源，会让 SQL 查询性能下降得很严重，因此不要连接不必要的表。在许多 DBMS 中，也都会有最大连接表的限制。

5.3、当业务需要join的表超过三个如何处理？

我们可以将复杂的 SQL 语句拆成多个语句多次执行，以缓解 SQL 服务器的压力。尽量避免使用 SQL 服务器做逻辑计算。

六、关于count()的使用

6.1、count(*)、count(1)、count(列名)谁好呢?

其实，对于 MyISAM 引擎的表是没有区别的，这种引擎内部有一计数器在维护着行数。而 Innodb 引擎的表用count(*)、count(1)直接读行数，复杂度是O(n)，因为 Innodb 真的要去数一遍。但好于具体的count(列名)。

6.2、 能不能使用count(列名)替换count(*)

不要使用 count(列名)来替代 count()，count()是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。 count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。