如何写好SQL

doMore 148 2024-03-07

一个程序员在工作中难免会与数据库打交道,有的是写在程序中的;有的是需要直接在生产环境中执行一些修改数据的sql;有的是修改表结构 等等。这样的情况每一次都提心吊胆,生怕执行错误,无法恢复。久而久之,也算是总结了一点书写的经验,现记录一下。

0. 修改或删除重要数据前,要先备份,先备份,先备份

1. 写完查询 SQL 先 查看执行计划

日常开发写SQL的时候,尽量养成这个好习惯:写完SQL后,分析一下,尤其注意走不走索引。

-- 分析方式 
-- 1. explain 
explain select userid,name,age from user where userid =10086 or age =18;
-- 2. desc
desc select userid,name,age from user where userid =10086 or age =18;
-- 3. desc format=json 
desc format=json select userid,name,age from user where userid =10086 or age =18;
-- 4. desc analyze
desc analyze select userid,name,age from user where userid =10086 or age =18;

2. 操作 delete 或者 update 语句,加 limit

注意:尤其是范围删除

  1. 降低写错 SQL 的代价
  2. SQL 效率可能会提高,提前命中,避免全表扫描
  3. 避免长事务
  4. 数据量大的情况下也不会占满CPU
delete from user where age > 30 limit 200;

3. 设计表,所有的字段都应该加相应的注释

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='账户表';

4. SQL 书写,关键字大小写保持一致,使用缩进

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

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

-- 提倡
insert into Student values ('666','小孩子真可爱','100');
-- 不提倡
insert into Student(student_id,name,score) values ('666','小孩子真可爱','100');

6. 设计数据库表结构,加三个字段 主键(id)、create_time、update_time

  • 主键一般都要加上的,没有主键的表是没有灵魂的
  • 创建时间和更新时间的话,建议加上,详细审计、跟踪记录,都是有用的。
  • update_time 字段建议加上 ON UPDATE ,也就是在更新数据库记录的时候直接保留更新时间
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='账户表';

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

8. where后面的字段,留意其数据类型的隐式转换

当一个属性类型为字符串

// userid 是varchar字符串类型  
// 错误
select * from user where userid = 123;
// 正确
select * from user where userid = '123';

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

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

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

10. 尽量使用varchar代替 char

11. SQL命令行修改数据,养成begin + commit 事务的习惯

12. 索引命名要规范,主键索引名为 pk_ 字段名;唯一索引名为 uk _字段名 ;普通索引名则为 idx _字段名

13. 如果修改/更新数据过多,考虑批量进行。

  • 大批量操作会会造成主从延迟。
  • 大批量操作会产生大事务,阻塞。
  • 大批量操作,数据量过大,会把cpu打满。
// 错误
delete from account  limit 100000;

// 正例
for each(200次)  
{  
 delete from account  limit 500;  
}

14. 尽量不要返回 group by 后不存在的列(主键除外)

15. 建表/排序 需要注意排序规则

在 mysql 中 创建数据库、表 的时候有一个 COLLATE 参数可以设置。

CREATE TABLE `order` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `code` varchar(20) COLLATE utf8mb4_bin NOT NULL,
  `name` varchar(30) COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `un_code` (`code`),
  KEY `un_code_name` (`code`,`name`) USING BTREE,
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

它的作用是用来排序的。字符排序规则跟字符集有关,比如:字符集如果是 utf8mb4,则字符排序规则也是以:utf8mb4_ 开头的,常用的有:utf8mb4_general_ci、utf8mb4_bin、utf8mb4_0900_ai_ci(mysql8之后新增的,并且默认)等。

  • utf8mb4_general_ci 排序规则,对字母的大小写不敏感。说得更直白一点,就是不区分大小写。
  • utf8mb4_bin 排序规则,对字符大小写敏感,也就是区分大小写。
  • UTF8MB4_0900_AI_CI 这是一个基于AI(Accent Insensitive)规则的排序规则,对于重音符号不敏感,也同样对字母的大小写不敏感。例如,字母"a"和"á"将被视为相等。
    ‒ UTF8MB4_0900_BIN 对字符大小写敏感

utf8mb4_0900_ai_ci 是在MySQL 8.0.0版本后引入的一种排序规则。它基于Unicode Collation Algorithm,支持对多种语言进行排序,并考虑了不同语言的特殊规则和排序顺序。
utf8mb4_general_ci 则是MySQL较早版本中使用的排序规则。它对字符进行简单的二进制比较,不考虑各种语言的排序规则和特殊情况。相比于utf8mb4_0900_ai_ci,utf8mb4_general_ci 能够提供更快的排序性能,但在某些特殊情况下可能会导致排序结果不符合用户的期望。

-- 插入三条 名字区分大小写的数据
INSERT INTO no_up_down_test (name, age, create_time) VALUES ('SUXINGKANG', 27, DEFAULT);
INSERT INTO no_up_down_test (name, age, create_time) VALUES ('suxingkang', 28, DEFAULT);
INSERT INTO no_up_down_test (name, age, create_time) VALUES ('SuXingKang', 18, DEFAULT);


select * from no_up_down_test nudt where nudt.name = 'suxingkang';

-- utf8mb4_general_ci、UTF8MB4_0900_AI_CI 排序规则下 三条数据都可以查询到
-- UTF8MB4_0900_BIN、utf8mb4_bin  只能查到一个

注意:我们目前系统使用数据库版本为 mysql 8.0.22,但是 以 sys_ 开头的表结构中,排序规则设置为 utf8mb4_general_ci。