Haben Sie 21 gute Gewohnheiten zum Schreiben von SQL in MySQL

In der Spalte „MySQL-Video-Tutorial“ werden gute Gewohnheiten zum Schreiben von SQL vorgestellt.

Vorwort

Dieser Artikel ist in drei Richtungen unterteilt: SQL-Bedauernsmedizin, SQL-Leistungsoptimierung und SQL-Standard-Eleganz. Vielen Dank fürs Lesen. Machen Sie weiter so ~

1. Erklären Sie nach dem Schreiben der SQL zunächst, wie Sie den Ausführungsplan anzeigen (SQL-Leistungsoptimierung).

Wenn Sie täglich SQL entwickeln und schreiben, versuchen Sie, diese gute Angewohnheit zu entwickeln: Nach dem Schreiben der SQL Verwenden Sie EXPLAIN, um es zu analysieren, und achten Sie dabei besonders darauf, ob der Index verwendet wird.

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

2. Fügen Sie beim Ausführen der Lösch- oder Aktualisierungsanweisung ein Limit hinzu (SQL-Bedauernsmedizin).

Versuchen Sie beim Ausführen der Lösch- oder Aktualisierungsanweisung, ein Limit hinzuzufügen:

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

Aufgrund des Zusatzes Die Hauptvorteile der Verwendung von Limit sind:

Reduzieren Sie die Kosten für das Schreiben falscher SQL

Wenn Sie dieses SQL in der Befehlszeile ausführen und kein Limit hinzufügen, können Ihre Hände zittern versehentlich
• während der Ausführung, und die Daten könnten alle gelöscht werden, was wäre, wenn die falschen gelöscht würden? Das Hinzufügen eines Limits von 200 macht den Unterschied. Wenn Sie die Daten falsch löschen, gehen nur 200 Daten verloren, die über das Binlog-Protokoll schnell wiederhergestellt werden können. SQL ist wahrscheinlich effizienter Wenn die erste Zeile die Zielrückgabe erreicht, wird die Scantabelle weiterhin ausgeführt.
• Vermeiden Sie lange Transaktionen Wenn das Alter indiziert ist, fügt MySQL allen zugehörigen Zeilen Schreibsperren und Lückensperren hinzu, und alle ausführungsbezogenen Zeilen werden gesperrt wirkt sich direkt aus. Zugehörige Dienste können nicht verwendet werden.
• Wenn die Datenmenge groß ist, ist es leicht, die CPU zu füllen. Wenn Sie eine große Datenmenge löschen und die Anzahl der Datensätze nicht begrenzen, ohne ein Limit hinzuzufügen, kann es leicht sein, die CPU zu füllen , wodurch der Löschvorgang immer langsamer wird.
• 3. Fügen Sie beim Entwerfen von Tabellen entsprechende Kommentare zu allen Tabellen und Feldern hinzu (SQL-Spezifikationen sind elegant).
Diese gute Angewohnheit muss beim Entwerfen von Datenbanktabellen entwickelt werden, um die Wartung zu erleichtern später.

Positives Beispiel:

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

Gegenbeispiel:

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. Halten Sie die Schlüsselwortgröße konsistent und verwenden Sie Einrückungen. (SQL-Spezifikation ist elegant) Positives Beispiel:

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

Gegenbeispiel:

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

Offensichtlich wird Ihr SQL durch die Vereinheitlichung von Schlüsselwörtern im gleichen Fall und die Verwendung der Einrückungsausrichtung eleganter aussehen~

5 Name (SQL-Spezifikation ist elegant)

Gegenbeispiel:

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

Positives Beispiel:

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

6. Ändern Sie die SQL-Operation und führen Sie sie zuerst in der Testumgebung aus, notieren Sie die detaillierten Operationsschritte und den Rollback-Plan und überprüfen Sie sie vorher Produktion. (SQL-Bedauernsmedizin)

Ändern Sie den SQL-Vorgang und testen Sie ihn zunächst in der Testumgebung, um Grammatikfehler zu vermeiden, bevor Sie ihn in die Produktion bringen.

Um die SQL-Operation zu ändern, müssen detaillierte Operationsschritte geschrieben werden, insbesondere wenn Abhängigkeiten bestehen, wie zum Beispiel: Zuerst die Tabellenstruktur ändern und dann die entsprechenden Daten hinzufügen.

• Es gibt einen Rollback-Plan zum Ändern von SQL-Vorgängen. Überprüfen Sie das entsprechende geänderte SQL, bevor Sie in die Produktion gehen.
• 7. Fügen Sie beim Entwerfen der Datenbanktabelle drei Felder hinzu: Primärschlüssel, Erstellungszeit und Aktualisierungszeit. (SQL-Spezifikation ist elegant)
• Gegenbeispiel:

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

Positives Beispiel:

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

Grund:

Eine Tabelle ohne Primärschlüssel hat keine Seele

In Bezug auf Erstellungszeit und Aktualisierung Es wird empfohlen, dies rechtzeitig hinzuzufügen. Detaillierte Audits und Tracking-Aufzeichnungen sind allesamt nützlich.

• Alibaba Development Manual erwähnt diesen Punkt ebenfalls, wie in Abbildung

8 gezeigt. Überprüfen Sie nach dem Schreiben der SQL-Anweisung die Spalten hinter wo, sortieren nach, gruppieren nach und ob die mit mehreren Tabellen verknüpften Spalten indiziert wurden. Vorrang haben kombinierte Indizes. (SQL-Leistungsoptimierung)

Gegenbeispiel:

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

Positives Beispiel:

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

9. Bevor Sie wichtige Daten ändern oder löschen, sichern Sie zuerst, sichern Sie zuerst, sichern Sie zuerst (SQL-Bedauernsmedizin)

Wenn Sie Daten ändern oder löschen möchten, sichern Sie unbedingt die zu ändernden Daten, bevor Sie SQL ausführen. Im Falle einer Fehlbedienung werden Sie es trotzdem bereuen

~

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

Das obige ist der detaillierte Inhalt vonHaben Sie 21 gute Gewohnheiten zum Schreiben von SQL in MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!