Avoir 21 bonnes habitudes pour écrire du SQL dans Mysql

La colonne

tutoriel vidéo mysql présente les bonnes habitudes pour écrire du SQL.

Avant-propos

Chaque bonne habitude est une fortune Cet article est divisé en trois directions : la médecine des regrets SQL, l'optimisation des performances SQL et l'élégance des spécifications SQL. Partagez et écrivez 21 bonnes habitudes SQL, merci d'avoir lu, continuez votre bon travail ~

1 Après avoir écrit le SQL, expliquez et visualisez le plan d'exécution (optimisation des performances SQL)

Quand. Développer et écrire du SQL dans la vie quotidienne, essayez de le développer autant que possible. C'est une bonne habitude : après avoir écrit le SQL, utilisez l'explication pour l'analyser, en accordant une attention particulière à l'utilisation ou non de l'index.

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

2. Lors de l'exécution de l'instruction de suppression ou de mise à jour, ajoutez une limite (médecine des regrets SQL)

Lors de l'exécution de l'instruction de suppression ou de mise à jour, essayez de ajoutez une limite, comme suit Prenons le SQL suivant comme exemple :

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

Parce que l'ajout d'une limite présente les principaux avantages suivants :

• Réduit les erreurs d'écriture en SQL Le coût, lorsque vous exécutez ce SQL sur la ligne de commande, si vous n'ajoutez pas de limite, si vos mains tremblent accidentellement lors de l'exécution, toutes les données peuvent être supprimé. Si la suppression est erronéeOù est-il ? Ajouter une limite de 200 fait la différence. Si vous supprimez les données de manière incorrecte, vous ne perdrez que 200 éléments de données, qui peuvent être rapidement restaurés via le journal binlog.
• SQL est susceptible d'être plus efficace . Vous ajoutez la limite 1 à la ligne SQL Si la première ligne atteint le retour cible, sans limite, la table d'analyse continuera à être exécutée.
• Évitez les transactions longues Lorsque la suppression est exécutée, si l'âge est indexé, MySQL ajoutera des verrous d'écriture et des verrous d'espacement à toutes les lignes associées, et toutes les lignes liées à l'exécution seront verrouillées si. le nombre de suppressions est important, cela affectera directement les services associés et les rendra indisponibles.
• Si la quantité de données est importante, il est facile de remplir le CPU Si vous supprimez une grande quantité de données, n'ajoutez pas de limite pour limiter le nombre d'enregistrements, il est facile de remplir le processeur, ce qui entraîne de plus en plus de suppressions.

3. Lors de la conception des tables, ajoutez les commentaires correspondants à toutes les tables et champs (les spécifications SQL sont élégantes)

Cette bonne habitude doit être développée lors de la conception des tables de base de données, de toutes les tables et champs. sont ajoutés avec les commentaires correspondants, ce qui facilite leur maintenance ultérieure.

Exemple positif :

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

Contre-exemple :

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;复制代码

Format d'écriture SQL, gardez la taille du mot-clé cohérente, utiliser l'indentation. (La spécification SQL est élégante)

Exemple positif :

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

Contre-exemple :

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

Évidemment, unifier des mots-clés dans la même casse et utiliser l'alignement d'indentation rendra votre SQL plus beau . Cela a l'air plus élégant ~

5. L'instruction INSERT indique le nom du champ correspondant (la spécification SQL est élégante)

Contre-exemple :

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

Exemple positif :

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

6 . Modifiez l'opération SQL et effectuez-la d'abord dans l'environnement de test, notez les étapes détaillées de l'opération et le plan de restauration, et examinez-les avant de passer en production. (Médecine des regrets SQL)

• Modifiez l'opération SQL et testez-la d'abord dans l'environnement de test pour éviter les erreurs grammaticales avant de la mettre en production.
• Pour modifier le fonctionnement SQL, des étapes d'opération détaillées doivent être écrites, surtout lorsqu'il y a des dépendances, telles que : modifier d'abord la structure de la table, puis ajouter les données correspondantes.
• Il existe un plan de restauration pour modifier les opérations SQL, et les modifications SQL correspondantes seront examinées avant la production.

7. Lors de la conception de la table de base de données, ajoutez trois champs : clé primaire, create_time, update_time. (La spécification SQL est élégante)

Contre-exemple :

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

Exemple positif :

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

Raison :

• La clé primaire doit être ajoutée, il n'y a pas de clé primaire La table n'a pas d'âme
• Si vous souhaitez ajouter l'heure de création et l'heure de mise à jour, il est recommandé de l'ajouter. Des enregistrements détaillés d'audit et de suivi sont tous utiles.

Le manuel de développement d'Alibaba a également mentionné ce point, comme le montre la figure

8 Après avoir écrit l'instruction SQL, vérifiez les colonnes derrière où, trier par, regrouper par. , et tables multiples Que les colonnes associées aient été indexées, la priorité est donnée aux index combinés. (Optimisation des performances SQL)

Contre-exemple :

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

Exemple positif :

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

9 . Avant de modifier ou de supprimer des données importantes, sauvegardez d'abord, sauvegardez d'abord, sauvegardez d'abord (médecine du regret SQL)

Si vous souhaitez modifier ou supprimer des données, vous devez sauvegarder les données à modifier avant d'exécuter. SQL, en cas de mauvaise opération , vous pouvez aussi en prendre une bouchéeRegret 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视频教程

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!