MySQL的一些指令，函数以及关键字

这个里面我准备记录一些比较有意思的MySQL的指令和函数，当然使用函数的时候我们要注意，会不会因为函数导致不走索引，走全表扫描的情况。

因为对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。如果不是索引字段的话其实还好，毕竟MySQL里面会有不少buffer，比如sort buffer这些，实际上是在内存上操作（当然太多了也会走磁盘），其实也挺快的。

一 指令
1 show processlist
有时候会出现长时间查询不返回结果，我们想知道到底是什么情况。可以通过show processlist来查看。

这里要注意的是id指的是pid，我们发现阻塞的进程直接kill pid就可以。还有就是show processlist 内容会提示我们关于阻塞的信息，比如：Waiting for table metadata lock 就是在等MDL锁。

2 explain
在分析慢SQL的时候，经常会用到ecplain查看执行计划，判断是否走了索引，是否是全表扫描。

二 函数
1 IFNULL（XXX，xxx）
我们对于一些NULL值其实在SQL里进行处理赋值，可以没必要到代码里面去遍历。

举个例子比如我们希望对某个字段求和，如果是null的话怪怪的，可以将这个null值赋值为0.

     SELECT
            abaod.id,
            abaod.res_id,
            abaod.res_name,
            abaod.res_code,
            abaod.back_apply_order_id,
            abaod.back_apply_order_no,
            abaod.category_id,
            abaod.category_name,
            abaod.brand,
            abaod.model,
            abaod.batch_id,
            ab.batch_no,
            abaod.out_storage_id,
            abaod.out_storage_detail_id,
            abaod.apply_count,
            abaod.out_storage_id,
            abaod.out_storage_detail_id,
            aos.out_storage_code,
            IFNULL(SUM(abod.count),0)	AS back_count,
            (abaod.apply_count - IFNULL(SUM(abod.count),0)) as could_back_count
        FROM
            ams_back_apply_order_detail abaod
                LEFT JOIN ams_back_apply_order abao ON abao.id = abaod.back_apply_order_id AND abao.data_status = 1
                LEFT JOIN ams_back_order_detail abod ON abod.back_apply_order_detail_id = abaod.id
                LEFT JOIN ams_batch ab ON abaod.batch_id = ab.id
                LEFT JOIN ams_back_order abo ON abo.id = abaod.back_apply_order_id AND abo.data_status = 1 AND abo.back_order_status IN (1,2)
                LEFT JOIN ams_out_storage aos ON aos.id = abaod.out_storage_id
GROUP BY abaod.id                

三 关键字

1 GROUP BY 和 ORDER BY顺序
这俩是有顺序的，GROUP BY 在ORDER BY前面。其实很好理解就是先分组组合获得结果集，才能利用sort buffer对现有的的结果集进行排序。

注意：group by 比order by先执行，order by不会对group by 内部进行排序，如果group by后只有一条记录，那么order by 将无效。要查出group by中最大的或最小的某一字段使用 max或min函数。

2 WHERE 和 HAVING
实际上二者都是直接后面接条件，但使用场景是不同的，甚至可以这么说两者彼此之间的场景是互斥的。

WHERE 可以说最经常用的查询条件，但是有一个问题，WHERE中不能使用聚合函数的。

而针对这个聚合函数的条件过滤，就有了HAVING，可以说有HAVING一定有GROUP聚合函数出现，反之不行，因为有时候使用聚合函数不需要过滤结果集。

Where是一个约束声明，是在查询结果集返回之前约束来自数据库的数据，且Where中不能使用聚合函数。
Having是一个过滤声明，是在查询结果集返回以后对查询结果进行的过滤操作，在Having中可以使用聚合函数。

参照下面的代码：

SELECT out_storage_detail_id , (a.out_count - SUM(back_count)) AS can_back_count,SUM(back_count) back_count FROM 
(
SELECT  b.id AS out_storage_detail_id ,b.count as out_count, (CASE WHEN d.id IS NULL THEN 0 ELSE c.apply_count END) AS back_count FROM ams_out_storage a 
LEFT JOIN ams_out_storage_detail b ON a.id = b.out_storage_id 
LEFT JOIN ams_back_apply_order_detail c ON c.out_storage_detail_id = b.id AND c.data_status = 1 
LEFT JOIN ams_back_apply_order d ON c.back_apply_order_id = d.id AND d.data_status = 1 AND d.back_apply_order_status IN (1,6)
WHERE a.data_status = 1 AND b.data_status = 1    AND  a.merchant_id = 372 AND a.employee_id = 7818
UNION ALL 
SELECT  b.id AS out_storage_detail_id ,b.count as out_count, (CASE WHEN d.id IS NULL THEN 0 ELSE c.count END) AS back_count FROM ams_out_storage a 
LEFT JOIN ams_out_storage_detail b ON a.id = b.out_storage_id 
LEFT JOIN ams_back_order_detail c ON c.out_storage_detail_id = b.id AND c.data_status = 1 
LEFT JOIN ams_back_order d ON c.back_order_id = d.id AND d.data_status = 1 AND d.back_order_status IN (1,2)
WHERE a.data_status = 1 AND b.data_status = 1   AND  a.merchant_id = 372 AND a.employee_id = 7818  
) a

GROUP BY out_storage_detail_id
HAVING can_back_count > 0

Order by back_count desc
LIMIT 10

3 LEFT JOIN的ON条件失效
有时候使用LEFT JOIN总感觉ON后面的条件只有一个起作用，别的条件都没起作用，没有达到设想中的效果。

举个例子

A    LEFT JOIN B ON 条件1 AND 条件2

A在左边A是主表，这个SQL会得到一个什么结果。会得到一个笛卡尔积，根据ON后面的条件的笛卡尔积。A作为主表无论是否在ON后面的条件匹配到，都会完整的展示出来，只不过后面连接的的B表内容，匹配不到都为空。

所以我们要注意以下一些情况。

（1）ON筛选主表条件无效
因为是根据ON条件匹配的一个笛卡尔积，那ON后面的条件只能是筛选空值B表，如果的你的SQL在ON后面去对主表做过滤，实际上这个对A的过滤条件是无效的，是无法筛选过滤A的数据， 如下SQL

A   LEFT JOIN B ON  A.id = B.a_id AND A.name = "ZP"

那我一定要筛选A.name = "ZP"的数据怎么办，你可以用where来约束筛选主表。

（2）ON条件导致结果集数目变大
因为是笛卡尔积的形式，由于关联条件导致变多，举个例子

order  LEFT JOIN order_detail ON  order.id = order.detail_id 

一个order可能对应多个order_detail,笛卡尔积会出现 m*n的情况，m是order条目数，n是order_detail建立连接的数目。

这种情况你可以使用聚合函数或者设置条件来解决，但是你要清楚这个“积”的形式。其实最大的问题是多表关联产生 abc*d这种数目“平白无故”增多的情况，不要出现重复求和（差）的情况。

（3）返回条件为null要考虑
因为主表不管怎样都会全量返回，ON连接没匹配的条件对应后面的主表关联副表的字段为null，我们在做where条件的时候不要把人家给过滤掉。

4 ON和WHERE的区别
ON和WHERE实际上面已经说了一部分，实际上WHERE在INNER JOIN的情况下和ON是等价的，因为INNER是两边匹配的数据展示，可以去搜索LEFT JOIN ，RIGHT JOIN，INNER JOIN很多文章都会贴图表示彼此间的区别。

join默认为inner join，当为内部连接时，on和where的作用你可以看做是一样的。