sql 使用游标进行逐条判断 如何回滚到insert前的状态_拂晓一刻|MySQL基本规范及效率使用...

点击上方蓝字关注我们吧！

  

1

前言

掌握好了MySQL的基本规范时，在工作中我们会更加得心应手的去使用它。本文会简单介绍一下MySQL的规范和注意点。

2

正文

1. explain查看执行计划。

我们日常开发写SQL的时候，要尽量养成这个好习惯呀：写完SQL后，用explain分析一下，尤其注意走不走索引。

explain select * from user where id =1 or age =20;

2、limit函数。

当我们执行delete或者update语句时，尽量加上limit函数。limit函数主要有这些好处：数据量过大容易把CPU装载满、提高SQL的执行效率、可以避免长事务、降低写错SQL的代价。

• 「降低写错SQL的代价」, 当你在执行SQL的时候，假如不加limit，执行的时候一个「不小心手抖」，可能会把数据全部删掉了，加上limit 200，就不一样了。删错也只是丢失200条数据，我们可以通过binlog日志快速恢复。
• 「SQL效率提高」，当你在SQL行中，加上limit 1，如果第一条就命中目标return， 没有limit的话，还会继续执行扫描表。
• 「避免长事务」，delete执行时,如果执行字段加上索引，MySQL会将所有相关的行加写锁和间隙锁，所有执行相关行会被锁住，如果删除数量大，会直接影响相关业务无法使用。
• 「数据量过大容易把CPU载满」 ,如果你的删除数据量很大时，不加 limit限制一下记录数，很容易把cpu打满，导致越删越慢。举个栗子：

delete from user where age > 18 limit 400;

3. 设计创建数据表时，表和字段都添加相应的中文注释。

当我们设计数据库表的时候，所有表和字段都添加相应的注释，后面更容易维护和工作交接。

「正确书写格式：」

CREATE TABLE USER ( id INT, NAME VARCHAR ( 10 ), age VARCHAR ( 10 ), PASSWORD VARCHAR ( 15 ), birthday date );CREATE TABLE `BENEFIT_PROFIT` (  `ID` INT ( 11 ) NOT NULL AUTO_INCREMENT COMMENT '主键Id',  `NAME` VARCHAR ( 255 ) DEFAULT NULL COMMENT '分润单名',  `BALANCE` INT ( 11 ) DEFAULT NULL COMMENT '分润余额',  `CREATE_TIME` datetime NOT NULL COMMENT '创建时间',  `UPDATE_TIME` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',  PRIMARY KEY ( `ID` ),  KEY `idx_name` ( `NAME` ) USING BTREE ) ENGINE = INNODB AUTO_INCREMENT = 1570068 DEFAULT CHARSET = utf8 ROW_FORMAT = REDUNDANT COMMENT = '分润表';

「错误书写格式：」

4. SQL语句的书写格式，关键字大小要保持一致，使用缩进。

这样会使你的SQL看起来更加的优雅~ 便于直观理解。

SELECT  bit.NAME,  sum( bit.BALANCE ) FROM  BENEFIT_PROFIT bit WHERE  bit.NAME = '待分润单据' GROUP BY  bit.NAME;

「正确书写格式：」

「错误书写格式：」

5. INSERT语句标明对应的字段名称。

INSERT INTO BENEFIT_PROFIT ( ID, NAME, BALANCE, CREATE_TIME )VALUES  ( '1', '待分润单据', '200000', '2020-12-07 00:00:00' );

「正确书写格式：」

「错误书写格式：」

6. 变更SQL操作前先在测试环境执行，并且写出明详细的操作步骤以及回滚方案，并在上生产前review一下。

• 变更SQL操作先在测试环境执行测试，避免影响有语法错误就放到生产上。
• 变更SQL操作需要写明详细操作步骤，尤其有依赖关系的时候，如：先修改表结构再补充对应的数据。
• 变更SQL操作要有回滚方案，并在上生产之前，review一下对应要变更的SQL。

7.设计创建数据库表时，加上这三个字段：主键，create_time,update_time。

「正确书写格式：」

CREATE TABLE USER ( id INT, NAME VARCHAR ( 10 ), age VARCHAR ( 10 ), PASSWORD VARCHAR ( 15 ), birthday date );CREATE TABLE `BENEFIT_PROFIT` (  `ID` INT ( 11 ) NOT NULL AUTO_INCREMENT COMMENT '主键Id',  `NAME` VARCHAR ( 255 ) DEFAULT NULL COMMENT '分润单名',  `BALANCE` INT ( 11 ) DEFAULT NULL COMMENT '分润余额',  `CREATE_TIME` datetime NOT NULL COMMENT '创建时间',  `UPDATE_TIME` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',  PRIMARY KEY ( `ID` ),  KEY `idx_name` ( `NAME` ) USING BTREE ) ENGINE = INNODB AUTO_INCREMENT = 1570068 DEFAULT CHARSET = utf8 ROW_FORMAT = REDUNDANT COMMENT = '分润表';

「错误书写格式：」

• 主键一般都要加上的，没有主键的表是没有灵魂的。
• 创建时间和更新时间的，详细审计、跟踪记录，都是有用的。

8. 写完 SQL语句后，记得检查where,order by,group by后面的列，多表关联的列是否已加上索引，优先考虑组合索引。

9.当修改或删除重要数据前，记得要先备份！

如果要修改或删除数据，在执行SQL前一定要先备份要修改的数据，万一误操作，还能进行数据恢复哦~

10. 尽量把所有列定义为NOT NULL。

• 「NOT NULL列更节省空间」，NULL列需要一个额外字节来作为判断是否为 NULL 的标志位。
• 「NULL列需要注意空指针问题」，NULL列在计算和比较的时候，需要注意空指针的问题。

11.修改或者删除SQL，先写WHERE函数查一下，确认后再补充 delete 或 update。

尤其在操作生产的数据时，遇到修改或者删除的SQL，先加个where查询一下，确认数据没有问题之后，再执行update或者delete操作。

12.减少不必要的字段返回，如使用select 代替 select * 。

「正确书写格式：」

SELECT  bit.NAME,  bit.BALANCEFROM  BENEFIT_PROFIT bit 

「错误书写格式：」

SELECT  *FROM  BENEFIT_PROFIT

• 可节省资源、减少网络开销。
• 可能用到覆盖索引，减少回表，提高查询效率。

13.所有表必须使用Innodb存储引擎。

Innodb 「支持事务，支持行级锁，更好的恢复性」，高并发下性能更好，没有特殊要求(即Innodb无法满足的功能如：列存储，存储空间数据等)的情况下，所有表必须要使用Innodb存储引擎。

14.数据库和表的字符集尽量统一使用UTF8编码。

尽量统一使用UTF8编码

• 可以避免乱码问题
• 可以避免，不同字符集比较转换，导致的索引失效问题

「如果需要存储表情，那么选择utf8mb4来进行存储，注意它与utf-8编码的区别。」

15. 尽量使用varchar数据类型来代替 char数据类型。

「正确书写格式：」

`NAME` varchar(100) DEFAULT NULL COMMENT '拂晓时分的印记'

「错误书写格式：」

`NAME` char(100) DEFAULT NULL COMMENT '拂晓时分的印记'

• 因为变长字段存储空间小，可以节省存储空间。

16. 如果修改字段含义或对字段表示的状态作追加时，需要及时更新字段注释文字。

Mysql的规约。当字段是表示枚举状态时，如果含义被修改了，或者状态追加时，为了后面更好去维护，需要即时更新字段的注释。

17. SQL命令行修改数据，要养成begin + commit 事务的习惯。

「正确书写格式：」

BEGIN;  UPDATE BENEFIT_PROFIT   SET BALANCE = 6000000   WHERE  NAME = '拂晓时分的印记';COMMIT;

「错误书写格式：」

  UPDATE BENEFIT_PROFIT   SET BALANCE = 6000000   WHERE  NAME = '拂晓时分的印记';

18.如果修改或更新数据过多时，要考虑批量进行。

• 大批量操作会会造成主从延迟。
• 大批量操作会产生大事务，阻塞。
• 大批量操作，数据量过大，会把cpu打满。

END~

【谁还不是被这个世界毒打呢】