Have 21 good habits for writing SQL in Mysql

mysql video tutorial column introduces good habits for writing SQL.

Preface

Every good habit is a fortune. This article is divided into three directions: SQL regret medicine, SQL performance optimization, and SQL specification elegance. Share and write 21 good habits of SQL, thank you for reading, keep up the good work~

1. After writing the SQL, explain and view the execution plan (SQL performance optimization)

When developing and writing SQL in daily life, try to develop it as much as possible This is a good habit: after writing the SQL, use explain to analyze it, paying special attention to whether the index is used.

explain select * from user where userid =10086 or age =18;复制代码

2. Operate the delete or update statement and add a limit (SQL regret medicine)

When executing the delete or update statement, try to add a limit, as follows Take the following SQL as an example:

delete from euser where age > 30 limit 200;复制代码

Because adding limit has the following main benefits:

• Reduces the risk of writing incorrect SQL The price is , when you execute this SQL on the command line, if you don’t add a limit, if your hands shake accidentally during execution, all the data may be deleted. If deletion is wrong? Adding a limit of 200 makes the difference. If you delete the data incorrectly, you will only lose 200 pieces of data, which can be quickly restored through the binlog log.
• SQL efficiency is likely to be higher. You add limit 1 to the SQL line. If the first line hits the target return, if there is no limit, the scan table will continue to be executed.
• Avoid long transactions. When delete is executed, if age is indexed, MySQL will add write locks and gap locks to all related rows, and all execution-related rows will be locked. If the number of deletions is large, it will directly affect the related services and make them unavailable.
• If the amount of data is large, it is easy to fill up the CPU. If you delete a large amount of data, do not add a limit to limit the number of records, it is easy to fill up the CPU, resulting in more and more deletions. slow.

3. When designing tables, add corresponding comments to all tables and fields (SQL specifications are elegant)

This good habit must be developed when designing database tables , all tables and fields are added with corresponding comments, making it easier to maintain later.

Positive example:

CREATE TABLE `account` (
  `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='账户表';复制代码

Counter example:

CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `balance` int(11) DEFAULT NULL,
  `create_time` datetime NOT NULL ,
  `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8;复制代码

4. SQL writing format, keep the keyword size consistent, use indentation. (SQL specification is elegant)

Positive example:

SELECT stu.name, sum(stu.score)
FROM Student stu
WHERE stu.classNo = '1班'
GROUP BY stu.name复制代码

Counterexample:

SELECT stu.name, sum(stu.score) from Student stu WHERE stu.classNo = '1班' group by stu.name.复制代码

Obviously, unifying keywords in the same case and using indentation alignment will make your SQL look better. It looks more elegant~

5. The INSERT statement indicates the corresponding field name (SQL specification is elegant)

Counter example:

insert into Student values ('666','捡田螺的小男孩','100');复制代码

Positive example:

insert into Student(student_id,name,score) values ('666','捡田螺的小男孩','100');复制代码

6 . Change the SQL operation and perform it in the test environment first, write down the detailed operation steps and rollback plan, and review it before going to production. (SQL regret medicine)

• Change the SQL operation and test it in the test environment first to avoid grammatical errors before putting it into production.
• Changing Sql operations requires detailed steps, especially when there are dependencies, such as modifying the table structure first and then supplementing the corresponding data.
• There is a rollback plan for changing SQL operations, and review the corresponding SQL changes before going to production.

7. When designing the database table, add three fields: primary key, create_time, update_time. (SQL standard is elegant)

Counter example:

CREATE TABLE `account` (
  `name` varchar(255) DEFAULT NULL COMMENT '账户名',
  `balance` int(11) DEFAULT NULL COMMENT '余额',
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='账户表';复制代码

Positive example:

CREATE TABLE `account` (
  `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='账户表';复制代码

Reason:

• The primary key must be added, there is no primary key If the table has no soul
• creation time and update time, it is recommended to add it. Detailed auditing and tracking records are all useful.

Alibaba Development Manual also mentions this point, as shown in Figure

8. After writing the SQL statement, check the columns behind where, order by, group by, and multiple tables Whether the associated columns have been indexed, priority is given to combined indexes. (SQL performance optimization)

Counter example:

select * from user where address ='深圳' order by age ;复制代码

Positive example:

添加索引
alter table user add index idx_address_age (address,age)复制代码

9. Before modifying or deleting important data, back up first, back up first, back up first (SQL regret medicine)

If you want to modify or delete data, you must back up the data to be modified before executing SQL, in case of misoperation , you can also take mouthfulregret medicine~

10. where后面的字段，留意其数据类型的隐式转换（SQL性能优化）

反例：

//userid 是varchar字符串类型
select * from user where userid =123;复制代码

正例：

select * from user where userid ='123';复制代码

理由：

• 因为不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为浮点数再做比较，最后导致索引失效

11. 尽量把所有列定义为NOT NULL（SQL规范优雅）

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

12.修改或者删除SQL，先写WHERE查一下，确认后再补充 delete 或 update（SQL后悔药）

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

13.减少不必要的字段返回，如使用select <具体字段> 代替 select * （SQL性能优化）

反例：

select * from employee;复制代码

正例：

select id，name from employee;复制代码

理由：

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

14.所有表必须使用Innodb存储引擎（SQL规范优雅）

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

15.数据库和表的字符集统一使用UTF8（SQL规范优雅）

统一使用UTF8编码

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

如果是存储表情的，可以考虑 utf8mb4

16. 尽量使用varchar代替 char。（SQL性能优化）

反例：

  `deptName` char(100) DEFAULT NULL COMMENT '部门名称'复制代码

正例：

`deptName` varchar(100) DEFAULT NULL COMMENT '部门名称'复制代码

理由：

• 因为首先变长字段存储空间小，可以节省存储空间。
• 其次对于查询来说，在一个相对较小的字段内搜索，效率更高。

17. 如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释。 （SQL规范优雅）

这个点，是阿里开发手册中，Mysql的规约。你的字段，尤其是表示枚举状态时，如果含义被修改了，或者状态追加时，为了后面更好维护，需要即时更新字段的注释。

18. SQL修改数据，养成begin + commit 事务的习惯；(SQL后悔药)

正例：

begin;
update account set balance =1000000
where name ='捡田螺的小男孩';
commit;复制代码

反例：

update account set balance =1000000
where name ='捡田螺的小男孩';复制代码

19. 索引命名要规范，主键索引名为 pk_ 字段名；唯一索引名为 uk _字段名 ； 普通索引名则为 idx _字段名。（SQL规范优雅）

说明： pk_  即 primary key；uk _ 即  unique key；idx _ 即 index 的简称。

20. WHERE从句中不对列进行函数转换和表达式计算

假设loginTime加了索引

反例：

select userId,loginTime from loginuser where Date_ADD(loginTime,Interval 7 DAY) >=now();复制代码

正例：

explain  select userId,loginTime from loginuser where  loginTime >= Date_ADD(NOW(),INTERVAL - 7 DAY);复制代码

理由：

• 索引列上使用mysql的内置函数，索引失效

21.如果修改\更新数据过多，考虑批量进行。

反例：

delete from account  limit 100000;复制代码

正例：

for each(200次)
{
 delete from account  limit 500;
}复制代码

理由：

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

参考与感谢

• delete后加 limit是个好习惯么
• 《阿里开发手册》

相关免费学习推荐：mysql视频教程

The above is the detailed content of Have 21 good habits for writing SQL in Mysql. For more information, please follow other related articles on the PHP Chinese website!