MySQL에서 SQL을 작성하는 21가지 좋은 습관을 가지세요

mysql 동영상 튜토리얼 칼럼에서는 SQL 작성의 좋은 습관을 소개합니다.

서문

이 글은 SQL 후회약, SQL 성능 최적화, SQL 표준 우아함이라는 세 가지 방향으로 나누어져 있습니다. 읽어주셔서 감사합니다. 계속 수고하세요~

1. SQL을 작성한 후 먼저 실행 계획을 볼 수 있도록 설명합니다(SQL 성능 최적화)

일상적으로 SQL을 개발하고 작성할 때, SQL을 작성한 후, 이런 좋은 습관을 기르도록 하세요. , 인덱스 사용 여부에 특히주의하면서 explain을 사용하여 분석하십시오.

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

2. delete 또는 update 문을 실행할 때 한도를 추가하세요(SQL 후회약)

delete 또는 update 문을 실행할 때 한도를 추가해 보세요.

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

추가로 인해 제한 사용의 주요 이점은 다음과 같습니다.

• 잘못된 SQL을 작성하는 비용을 줄입니다 명령줄에서 이 SQL을 실행할 때 제한을 추가하지 않으면 손이 떨릴 수 있습니다. 실행 중 실수로 데이터가 모두 삭제될 수 있는데, 잘못 삭제면 어쩌죠? 200이라는 제한을 추가하면 차이가 발생합니다. 데이터를 잘못 삭제하면 200개의 데이터만 손실되며, 이는 binlog 로그를 통해 빠르게 복구할 수 있습니다.
• SQL이 더 효율적일 가능성이 높습니다. SQL 라인에 제한 1을 추가하면 첫 번째 라인이 대상 반환에 도달하고 제한이 없으면 스캔 테이블이 계속 실행됩니다.
• 긴 트랜잭션을 피하세요 삭제가 실행될 때 age가 색인화되면 MySQL은 모든 관련 행에 쓰기 잠금 및 간격 잠금을 추가하고 삭제 횟수가 많으면 모든 실행 관련 행이 잠깁니다. 관련 서비스를 이용할 수 없습니다.
• 데이터량이 많으면 CPU가 차지 쉽습니다. 대용량 데이터를 삭제하고 제한을 추가하지 않고 레코드 수를 제한하지 않으면 CPU가 차지 쉽습니다. , 삭제 속도가 점점 느려집니다.

3. 테이블을 디자인할 때 모든 테이블과 필드에 해당 코멘트를 추가하세요. (SQL 사양은 우아합니다.)

데이터베이스 테이블을 디자인할 때 모든 테이블과 필드에 해당 코멘트를 추가하는 것이 유지 관리가 더 쉽습니다. 나중에.

긍정적 예:

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='账户表';复制代码

카운터 예:

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 작성 형식, 키워드 크기를 일관되게 유지하고 들여쓰기를 사용합니다. (SQL 명세는 우아합니다)

긍정적 예시:

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

카운터 예시:

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

당연히 같은 대소문자의 키워드를 통일하고 들여쓰기 정렬을 사용하면 SQL이 더욱 우아해 보입니다~

5. INSERT 문은 해당 필드를 나타냅니다. 이름 (SQL 사양이 우아함)

카운터 예시:

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

긍정 예시:

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

6. 먼저 SQL 연산을 변경하고 테스트 환경에서 실행한 뒤, 세부 연산 단계와 롤백 계획을 적어두고 사전 검토한다. 생산. (SQL 후회약)

SQL 연산을 변경하여 테스트 환경에서 먼저 테스트하여 문법 오류가 발생하지 않도록 제작에 투입합니다.
• SQL 작업을 변경하려면 특히 테이블 구조를 수정한 다음 해당 데이터를 추가하는 등 종속성이 있는 경우 자세한 작업 단계를 기록해야 합니다.
• SQL 작업 변경에 대한 롤백 계획이 있으며, 프로덕션에 들어가기 전에 해당 변경된 SQL을 검토하세요.

7. 데이터베이스 테이블을 디자인할 때 기본 키, create_time, update_time 세 가지 필드를 추가합니다. (SQL 명세는 우아합니다)

카운터 예시:

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='账户表';复制代码

긍정 예시:

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='账户表';复制代码

이유:

기본 키가 없는 테이블에는 영혼이 없습니다
• 생성 시간 및 업데이트 측면에서 시간이 지나면 추가하는 것이 좋습니다. 자세한 감사 및 추적 기록이 모두 유용합니다.

알리바바 개발 매뉴얼에서도 그림

8과 같이 이 점을 언급하고 있습니다. SQL 문을 작성한 후 where 뒤에 있는 열을 확인하고, order by, group by, 여러 테이블과 관련된 열이 index되어 있는지 확인하세요. 결합된 인덱스에 우선순위가 부여됩니다. (SQL 성능 최적화)

카운터 예시:

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

긍정 예시:

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

9. 중요한 데이터를 수정하거나 삭제하기 전에 먼저 백업하고, 먼저 백업하고, 먼저 백업하세요.

원하시면 데이터를 수정하거나 삭제할 때 SQL을 실행하기 전에 반드시 수정할 데이터를 백업해 두시기 바랍니다.

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视频教程

위 내용은 MySQL에서 SQL을 작성하는 21가지 좋은 습관을 가지세요의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!