5. SQL编程之三剑客只能孤独求败吗?

2018-12-27 本文已影响0人 Alvinzane

这里所说的孤独求败其实有二层意思:

作为金庸名著系列里的人物, 拥有至高无上的武功,却很少在江湖中行走
作为形容词,SQL编程在percona手里做出了无人匹敌的工具,让percona觉得甚是寂寞无奈

MySQL中SQL编程中function,procedure和trigger就好比是三剑客, 在我看来, 它目前的处境就是孤独求败!

一,对SQL编程认识的常见误区

先来讲个故事:<把家都搬过去>
话说一家鞋厂派了两个业务员去某地做市场调研,他俩回来以后分别给BOSS做了汇报:

业务A说: 那真是个鸟不拉屎的地儿,那里人都不穿鞋
业务B说: 真是太好了,我要把家都搬过去

故事不长,其道理显而易见，同样的市场, 有人看到的是"困境",有人看到的是"商机", 这是眼光的问题.

吴老师在课上也讲过小马过河的故事,道理非常简单, 不要道听途说, 遇到问题或疑惑时, 应该自己去思考, 在实践中寻找答案. 当别人说不推荐使用SQL编程时, 就应该去思考:

为什么不推荐?
不推荐是不是在说不反对呢?
那什么样的场景是不推荐?
什么样的场景又鼓励呢?
什么样的场景还得必须使用呢?

其实我本没有计划写这篇文章的,但看到很多小伙伴的还是有小马心态,还是伟哥常说的那句话,好人做到底,送佛送到西吧. 接下来,我们开始一起来寻找答案吧.

二,使用SQL编程的利与弊

如果你不太了解MySQL的function,procedure及trigger, 建议你先按顺序看完之前的教程,再回来看这一章节,便能更深入的理解下面的内容.

三剑客之function

使用function的好处可以说是谁用谁知道, 在前面的教程里, 我们已经看到用function来简化select的结构,把原来的三个left join直接变成了直接select主表,回顾一下:

# 原SQL的样子:
select 
  a.order_id,       
  b.address_name  as address_name_shipper,
  c.address_name  as address_name_receiver,
  d.address_name  as address_name_notify
from t_order a
  left join t_address b on a.address_id_shipper=b.address_id
  left join t_address c on a.address_id_receiver=c.address_id
  left join t_address d on a.address_id_notify=d.address_id
where order_id = 1;

# 用function改写后的样子:
select 
  order_id,
  f_get_address_name(address_id_shipper) as address_name_shipper,
  f_get_address_name(address_id_receiver) as address_name_receiver,
  f_get_address_name(address_id_notify) as address_name_notify
from t_order 
where order_id = 1;

# 更多的例子会以后的章节中看到

上面只是一个简单的例子, 以下是我总结的一些心得:

f_get_xxx开头的function可以解决少量外表字段扩展的问题,从而消除join,当然字段数太多的话,还是老老实实join吧
f_check_xxx开头的function可以解决只做where过虑,不select字段的情况,同样可以消除join
请注意上面这句,我说的是where过虑, 带有f_check_xxx的条件不能用到索引, 只对回表以后的二次过滤
function内部的基本上通常都是按主键或唯一索引来查询的,其本身不会是slow的节点
在物流系统中用一条SQL(100多行)把全球门到门的物流费用清单查来,没有function,伟哥也办不到
当你在分析别人的SQL,最不想看到的就是一大坨的jion和一大堆表别名, 同时在编写时也极容易出错
在我看来,优化SQL不仅要关注性能, 更要关注可读性
想想加餐课中老师享的案例吧,某SQL写出几百行,执行30多个小时,真的让我长见识了
让我长见识时的不是SQL的长度, 而且SQL怎么能写得怎么烂, 别人读不懂不出, 还跑不起来

一言以蔽之,使用function的好处就是简化select,消除不必要的join,提高可读性.

有利就于弊哦, 那使用function有什么不好的地方呢?

创建function时仅仅时记录了meta信息
调用时增加了额外的function解析开销, 与Oracle中不同的是, MySQL的function是在每次执行时"编译"的
不合理的使用可能会导制索引失效,整个SQL变量
目前业内SQL优化建议工具还不支持function

有性能强迫症的同学可能要问了, 高并发怎么办法呀, 会不会拖后腿? 性能损耗是肯定是有的, 主要是看你怎么去选择了.

关于function最后再给大家留个思考题:
function适合在分库分表的场景下使用吗?

三剑客之procedure

可以把procedure理解决多个DQL和DML的混合体, 并且节省了网络的开销. 性能的优势是显而易见的,另外在团队协作层面,也有以下好处:
1. 一个团队中负责schema设计通常是更懂数据库小组
2. schema设计小组更清楚数据变化和流向,更懂得如何优化数据
3. 把核心的业务从app层面切分出来,由schema设计小组来开发procedure,人员分工更合理
4. schema设计小组和app开发小组可以利用接口,同时开发, 并可以分工测试

procedure的缺点主要就体现在debug和单元测试上面:
1. 在Oracle上面是提供了debug储存过程的api,利于专业工具是可以完成单步调度的功能,如Toad for Oracle
2. MySQL好像并不支持debug这个功能, 对于复杂逻辑的储存过程,调试起来很不方便,所以也太建议写的太复杂了
3. 在编程语言中都有单元测试框架可以保证代码的质量,而SQL编程中,单元测试只能自己动手做

三剑客之trigger

触发器的设计允许DBA可以在最低层对数据再设立一道关卡, 有很多奇葩的场景都可以用trigger来协助我们进行处理, 举一个栗子:

曾经有一个客户投诉我们的系统"不稳定", 有些关帐的数据, 过一段时间会自己改变. 且客户一再坚定的说不是他人为修改的.
* 关帐的数据的本来就锁, 除非有权限的人取消关帐后才可以修改.
* Oh, My God. 作为一个老司机, 第一直觉就是客户他自己搞错了, 但是God都说了有问题, 那不得查一下吗?
* 于是开始推测各种可能性, 把认为有可以造成这个原因的地方都处理了,OK, 升级吧, 然后通知God说问题处理好了.
* 平静几天后, God又说了问题还在存在.
* 好吧,再继续查,继续升级, God继续说还有问题, 如此反复
* 终于有一天,伟哥实在受不了,决定.... 放大招! 放大招! 放大招!
* 于是我就给某个表的某个字段,严格看管起来,并且加上一把特殊锁, 只有打电话给我才能解开,并且留了电话.
* 就这样平静的过了好久,我都几乎快忘记这件事了,突然有一天接到一个电话:
* 你是阿伟吗? 我是xx公司的开发,负责God财务系统对接模块,我在错误日志查到,系统有个问题只有你才能解决....
* 终于查出问题了, 原来跟本就不是我们系统的问题, 当初就忽略了第三方系统出错的可能性.

相信遇到这种奇葩问题的人,我不是第一个,也不会是最后一个.

trigger的好处是切入点足够多,足够灵活, 且功能强大.

非要说trigger的缺点的话,那就是会产生额外的性能开销. 这里先暂时不展开讨论.

三,SQL编程的常见场景

function的场景相对单一些, 最主要就是处理复杂SQL的时候, 用funtion来消除一些不必要的join. 提高SQL的可读性和可维护性. 可读性好了, 犯错的机率自然也小了. 对于高并发的OLTP业务中, 很少有用武之地, 在一个轻量的OLAP业务中则可以大显身手.

procedure由于它本身也尽似于编程语言, 能做出的功能非常多, 需要在性能,成本考量之间找到平衡, 成本主要来源与业务复杂度导致的degug成本和测试成本. 特别适合以下场景:

批量数据生成,如测试数据
批量数据更新
大量数据查询后聚合,如各种财务统计,结帐等等

trigger能在最底层同时拿到新旧两个版本的数据,还能获取其它表的数据(前提不能跨实例),也能发起DML,并且自动触发,可以中断事务的提交,其灵活性和功能性不言而喻, 能完成的功能就更多了, 典型的场景如下:

底层的数据冗余
数据变化监控
数据校正
业务规则的兜底保证
奇葩问题解决,可以最底层做一些变记录, 便于定位问题

四,关于性能问题

总会有一些人患有性能强迫症?! 性能恐惧症?! 我也不知道用什么词来形容比较好, 总会担心:

开binlog会变慢
开双1会变慢
半同步会变慢
开PS会变慢
用LVM会变慢
用proxy会变慢
开监控会变慢
用SQL编程会变慢

就像买电脑硬件一样,一定要买最顶配的, 要把性能追求到极致, 总要追求压测工具的得分力压群雄. 不少人因此买了各种发烧级硬件回来,实际上后来发现除了比别人多了"煮泡面"的功能外, 也没什么优越感.

以我个人的观点, 对于数据库而言, 当其未成为业务的频颈之前, 其稳定性,维护的便利性,开发的友好性远比追求性能重要, 而不必在上线前, 就开始操心性能的问题. 在现在的PCIE年代, 对于中小型公司而言, 可能更多考虑的不是如何拆分,反而是如何合并.

关于SQL编程的性能问题, 它不像binlog那样属于非0即1的选择, 它的取舍更加灵活, 比如, 在项目开发的初期, 完全可以为了开发效率和团队分工配合, 优先考虑使用SQL编程, 将项目按时甚至提前投入生产, 是首要考虑的问题. 业务爆发后, 就算真的瓶颈点出在SQL编程, 再将相关的业务逻辑放到app中处理也是很简单的事件, 之前也有提到过SQL编程一般都是由schema设计小组来完成, 所以SQL的质量更有保证, app开发的同学在移值的过程,也是一个学习的过程. 也利于团队的成长.

更何况, 合理的使用SQL编程, 综合来看, 反而更有利于提高性能, 因为在RBR他们是天然的"组提交", 有可以有效的合并IO,对复制也量种加强.
在介绍trigger章节的最后留了一个问题, 就是当trigger产生其它DML时, MySQL会跟据触发时机, 把额外的DML产生的events合并到自身表的events前后,binlog传到slave上执行时,也并不会触发从库上相同的trigger,我们可以解折一下binlog文件,一探究竟:

#mysqlbinlog -vv mysql-bin.000017 | tail -50
BEGIN
/*!*/;
# at 7836
#181220  3:09:36 server id 3306100  end_log_pos 7888     Table_map: `db_dba`.`t2` mapped to number 308
# at 7888
#181220  3:09:36 server id 3306100  end_log_pos 7948     Table_map: `db_dba`.`t2_history` mapped to number 310
# at 7948
#181220  3:09:36 server id 3306100  end_log_pos 7995     Write_rows: table id 310
# at 7995
#181220  3:09:36 server id 3306100  end_log_pos 8094     Update_rows: table id 308 flags: STMT_END_F

BINLOG '
8AcbXBN0cjIANAAAANAeAAAAADQBAAAAAAEABmRiX2RiYQACdDIABgMDDwMPAwQoACgAHg==
8AcbXBN0cjIAPAAAAAwfAAAAADYBAAAAAAEABmRiX2RiYQAKdDJfaGlzdG9yeQAGAwMDDwMPBCgA
KAA8
8AcbXB50cjIALwAAADsfAAAAADYBAAAAAAAAAgAG/9wDAAAAAwAAAAZweXRob24=
8AcbXB90cjIAYwAAAJ4fAAAAADQBAAAAAAEAAgAG///AAwAAAGECAAAKc3Nzc3Nzc3Nzc2ECAAAG
cHl0aG9uAwAAAMADAAAAYQIAAApzc3Nzc3Nzc3NzYQIAAAN0b28EAAAA
'/*!*/;
### INSERT INTO `db_dba`.`t2_history`
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2=3 /* INT meta=0 nullable=0 is_null=0 */
###   @3=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @4=NULL /* VARSTRING(40) meta=40 nullable=1 is_null=1 */
###   @5=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @6='python' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
### UPDATE `db_dba`.`t2`
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2=609 /* INT meta=0 nullable=1 is_null=0 */
###   @3='ssssssssss' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @4=609 /* INT meta=0 nullable=1 is_null=0 */
###   @5='python' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @6=3 /* INT meta=0 nullable=0 is_null=0 */
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2=609 /* INT meta=0 nullable=1 is_null=0 */
###   @3='ssssssssss' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @4=609 /* INT meta=0 nullable=1 is_null=0 */
###   @5='too' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @6=4 /* INT meta=0 nullable=0 is_null=0 */
# at 8094
#181220  3:09:36 server id 3306100  end_log_pos 8121     Xid = 160
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

可以看出对于before update,t2_history的数据产生在t2的前面, 如果换到app程层处理, 如果不显式启用事务, 则会产生两个trx逐个提交.

而对于procedure相对于app的批量提交,在通信层面就占尽了优势,就不在多说.

唯有function的使用,相比等价joinSQL多出解析过程,在性能上有所下降, 下面是两个可用于快速测试的储存过程两个功能等价的写法的耗时, 大家都自行去测试吧, 就先不给出我自己的结论了, 留给大家在论坛中讨论:

function改写join的性能损耗是利大于弊,还是弊大于利, 能在高并发中使用吗? 并讲出你的理由.

DELIMITER $$

DROP PROCEDURE IF EXISTS P_TEST_CALL_FUNCTION_TIMES$$
CREATE PROCEDURE `P_TEST_CALL_FUNCTION_TIMES`(IN I_TOTAL INT)
BEGIN
  -- 功能: 测试function的执行耗时
  
  DECLARE C_N1 INT DEFAULT 0;
  DECLARE C_ADDRESS_NAME_SHIPPER VARCHAR(50);
  
  -- 循环
  WHILE C_N1 < I_TOTAL DO
    -- 赋值, 不能写成 C_N1++
    SET C_N1 = C_N1 + 1;
    
    -- 调用测试SQL
    SELECT
      F_GET_ADDRESS_NAME(ADDRESS_ID_SHIPPER) AS ADDRESS_NAME_SHIPPER,
      F_GET_ADDRESS_NAME(ADDRESS_ID_RECEIVER) AS ADDRESS_NAME_RECEIVER,
      F_GET_ADDRESS_NAME(ADDRESS_ID_NOTIFY) AS ADDRESS_NAME_NOTIFY
    INTO
      C_ADDRESS_NAME_SHIPPER,C_ADDRESS_NAME_SHIPPER,C_ADDRESS_NAME_SHIPPER
    FROM t_order
    WHERE ORDER_ID = 1;

  END WHILE;

END$$

DROP PROCEDURE IF EXISTS `P_TEST_CALL_JOIN_TIMES`$$
CREATE PROCEDURE `P_TEST_CALL_JOIN_TIMES`(IN I_TOTAL INT)
BEGIN
  -- 功能: 测试function的执行耗时
  
  DECLARE C_N1 INT DEFAULT 0;
  DECLARE C_ADDRESS_NAME_SHIPPER VARCHAR(50);
  
  -- 循环
  WHILE C_N1 < I_TOTAL DO
    -- 赋值, 不能写成 C_N1++
    SET C_N1 = C_N1 + 1;
    
    -- 调用测试SQL
    SELECT      
      B.ADDRESS_NAME  AS ADDRESS_NAME_SHIPPER,
      C.ADDRESS_NAME  AS ADDRESS_NAME_RECEIVER,
      D.ADDRESS_NAME  AS ADDRESS_NAME_NOTIFY
    INTO
      C_ADDRESS_NAME_SHIPPER,C_ADDRESS_NAME_SHIPPER,C_ADDRESS_NAME_SHIPPER
    FROM t_order A
      LEFT JOIN t_address B ON A.ADDRESS_ID_SHIPPER=B.ADDRESS_ID
      LEFT JOIN t_address C ON A.ADDRESS_ID_RECEIVER=C.ADDRESS_ID
      LEFT JOIN t_address D ON A.ADDRESS_ID_NOTIFY=D.ADDRESS_ID
    WHERE ORDER_ID = 1;

  END WHILE;

END$$

DELIMITER ;

返回目录