自定义sql语句要点、优化点

做开发工作的第一年，接触的大多都是封装好的工具，而且因为业务比较简单，所以实际编码其实很少，最常见的crud百分之八十以上都是用mybatis-plus封装好的方法实现的，又快又香，时间基本都花在了后台的前端界面上。
年中换了家公司，业务复杂了一些，而且是在老项目上进行重构，也有使用类似mybatis-plus的tkmybatis，但是业务比较复杂，基本上查询都得自己写sql。不写不知道，一写复杂的逻辑发现很多自定义sql的坑都不知道，很多可以规范或者优化的点也不知道，顿时自觉羞愧难当、当之有愧、愧不自如、如果当时…唉，无论有多少工具，熟练写sql还是非常非常重要的，复杂业务得靠双手、sql优化得靠双手、不可抗力没法用工具时还得靠双手。
遂，开坑记录…

left join和inner join

连表查询
Left join是左外关联，以左表为基础表，会返回左表所有的行，即使在右表中没有匹配的行，
left join 是 left outer join 的简写形式（right join同理）
inner join 是内关联，关联的两个表中，同时符合关联条件的数据才会被返回，join 是 inner join 的简写形式
什么时候用哪种关联呢？Sql的原则就是选取数据量表较小的来做基础表，这样的效率是最高的，如果使用数据量比较大的表来做基础表，哪怕加了索引，可能也不够快，这种情况就需要考虑使用inner join，它会自动选择数据量比较小的表作为基础表。

索引最左匹配原则

最左匹配原则是索引能够生效的原则，如果查询的条件不符合索引的最左匹配原则，则sql不会触发索引。
概括一下使用的规则：
最左优先的意思就是，以最左边的索引字段为起点，任何连续的索引都能匹配上，但是遇到范围查询【between、like、>、<】就会停止匹配。
例如我们在某个表建了一个复合索引(a,b,c,d)，那么(a)、(a,c)、(a,b,c)、(a,b,c,d)都是有效的索引，(b)、(b,c)、(b,c,d)、(c,d)这些情况都是无效的索引
例如(a = 1 and b = 2 and c > 3 and d = 4)这种情况，只能匹配到索引(a,b,c)，因为c是范围查询了，在那之后的d是无法被匹配到的。为什么？因为索引的底层原理是B+树。
比如我们现在有(a,b)复合索引，根据数据组成的索引树如下：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3sQvcXrS-1681629229462)(/upload/2021/11/1637314124(1)]-a9de3f92d3084fec9cbec85508361d9f.jpg)
仅仅只看a的索引值的话，从左到右为1,1,2,2,3,3，是有序的。而b为1,2,1,4,1,2，是无序的，但是如果在a排序的前提下再去找b，那么b也是有序的。a为1时，b的值为1,2；a的值为2时，b的值为1,4；a的值为3时，b的值为1,2。这就是为什么需要最左匹配原则，因为只有在前面的索引有序的情况下，后面的索引才能有序地匹配到。比如a>1 and b = 1是无法匹配到b索引的，因为a索引的值是一个范围 或者a = 1 and b > 1是可以匹配到(a,b)索引的，但是如果还有一个索引c，那么c是无法被匹配到的。
注意：在sql中的索引位置与实际索引位置没有关联，例如select * from test where c = 2 and a = 2;也会走索引a，因为有索引下推技术。
查看有没有走索引可以使用explain关键字分析sql语句

if标签 test=""判空踩坑

当时用mybatis的if标签判空时，一般都是

<if test="variable!= '' and variable!= null ">sql语句</if>

这种情况下当传入0的时候,sql语句是不会被执行的，因为0返回的判断结果是false，为什么是flase？0既不是’‘，也不是null，为什么会是false呢？
这是因为：mybatis是用OGNL表达式来解析的，在OGNL表达式中有这么一条准则–”If the object is a Number, its double-precision floating-point value is compared with zero; non-zero is treated as true, zero as false“。也就是0会被解析为’'，所以if标签中的sql不会被执行。

解决很简单，去掉variable!= ''即可，什么时候可以使用上面的判断呢？当确保传入的参数是String类型的时候，才能使用variable!= ''来判空。
还有一种情况

<if test="variable!= '1' and variable!= null ">sql语句</if>

这种情况下，我们传入variable = “1”，会被判断为false，不会执行sql语句，为什么呢？因为在OGNL表达式中，if中的’1’会被解析成字符类型，而我们的"1"是String类型的，char 和 一个String 是肯定不能相等的，所以if标签中的sql语句不会被执行。
怎么解决呢？将语句改为下面即可（将char改为String）

<if test="variable!= '1'.toString() and variable!= null ">sql语句</if>

或者

<if test='variable!= "1" and variable!= null '>sql语句</if>

还有好些情况，可以去了解一下OGNL表达式的解析规则。

别再用select *了

需要使用到的字段才返回啊。以前几乎都是直接使用代码插件（比如mp），使用封装的方法返回的是整个表所有的字段，一是图省事，二是觉得当下数据量少没什么影响，但是当数据量慢慢躲起来之后，才发现封装vo很有必要，或者一个界面触发大量请求的情况下也很有必要。尤其是远程字典，比如有个小区表，有十几个字段，业务需要我们做一个小区的选择下拉框以及表格渲染需要根据关联的小区编号展示名称。我们需要从数据库取出小区名称和编号两个字段来做的字典数据，如果直接使用select *，数据量少的情况下速度不会有明显区别，但是如数据量一但多起来，可能1000左右的数据都能看得到请求时间过长导致的数据渲染慢，只能通过一些加载延迟的前端操作来做交互优化，这种优化如果是因为数据量很大去做，那是合理的，但是如果实际上数据量只有一点点，却因为远程数据字典接口返回了大量不需要的字段导致加载慢（这种情况往往是因为使用了某个打码插件，比如mp），这就不应该，稍微动动手就能有很大的速度优化，何乐而不为呢？这不是sql优化，而是每个程序员必备的开发基本准则。
需要什么字段就封装一个vo，不需要的字段别返回。

count(1)、count(*)、count(col)、select 1 from table

数据表统计符合条件的行数的聚合函数count。
执行效率：count(*)=count(1)=count(0)>count(colFirst)>count(colLast)
colFirst和colLast的去别是“偏移量”，列的偏移量决定性能，列越靠后，访问的开销越大。
如果表沒有主键(Primary key), 那么count(1)比count()快；
如果有主键，那主键作为count的条件时count(主键)最快；
如果表只有一个字段，那count()是最快的；
count(*) 跟 count(1) 的结果一样，都包含对NULL值的统计，而count(col) 是不包括NULL值的统计。
当需要判断数据表里是否存在符合一定的条件的数据时，如果需要返回这条数据就用select * from table where 条件，这个最常规不用说了，那如果我只是想判断这条数据是不是存在而不需要返回这条数据，那么就可以使用select 1 from table where 条件。
比如，查询今天某人是否已经有上班或下班打卡记录：

        SELECT 1 FROM punch_record WHERE punch_type= #{punchType}
            AND user_id = #{userId}
            AND DATE_FORMAT(created_time, '%Y-%m-%d' ) = 		 
            DATE_FORMAT(now(), '%Y-%m-%d' )
            LIMIT 1

上面sql结果：存在则返回1，无则返回null。
当然，使用select count(*)也一样可以。

//DATE_FORMAT的常用格式化
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s');   结果：2021-12-02 16:01:58
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i');    结果：2021-12-02 16:01
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H');      结果：2021-12-02 16
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');       结果：2021-12-02
SELECT DATE_FORMAT(NOW(),'%H:%i:%s');     结果：16:01:58
SELECT DATE_FORMAT(NOW(),'%H');         结果：16

将某一行数据固定放在结果集的首行

将name为"合计"的一行数据放在第一行：

select * from table
order by
case when name = '合计' then 1 else 0 desc;

如果还需要按其它字段排序，再加上其它排序规则即可：

select * from table
order by
case when name = '合计' then 1 else 0 desc,
age desc;

批量更新

for循环里面使用update语句性能是很差的，数据量大一些就容易造成堵塞，不可取。
推荐使用case when：
简单用例：

UPDATE product 
    SET amount = CASE id 
        WHEN 1 THEN 77
        WHEN 2 THEN 88 
        WHEN 3 THEN 99 
    END, 
    name = CASE id 
        WHEN 1 THEN 'New name1'
        WHEN 2 THEN 'New name2'
        WHEN 3 THEN 'New name3'
    END
WHERE id IN (1,2,3)

实际用例：
开发中使用的场景大多是一个List

    <update id="batchUpdateProduct">
        UPDATE product
        SET column_name1 = CASE product_id
        <foreach collection="updateCollect" item="item" 
               index="index" open="" separator="" close="">
            WHEN #{item.productId} THEN #{item.columnName1}
        </foreach>
        else null
        END,
        column_name2 = CASE product_id
        <foreach collection="updateCollect" item="item" 
            index="index" open="" separator="" close="">
            WHEN #{item.productId} THEN #{item.columnName2}
        </foreach>
        else null
        END,
        changed_time = NOW()
        WHERE
        product_id in
        <foreach collection="updateCollect" item="item" 
            index="index" open="(" separator="," close=")">
            #{item.productId}
        </foreach>
    </update>