java学习基地

微信扫一扫 分享朋友圈

已有 1337 人浏览分享

MySQL中,21个写SQL的好习惯(修正版)

[复制链接]
1337 2
本帖最初由 进修派 于 2020-12-4 19:47 编纂


每个好风俗皆是一笔财产,本文分SQL懊悔药, SQL机能劣化,SQL标准文雅三个标的目的,分享写SQL的21个好风俗,感谢浏览,减油哈~


1. 写完SQL先explain检察施行方案(SQL机能劣化)

一样平常开辟写SQL的时分,只管养成那个好风俗呀:写完SQL后,用explain阐发一下,特别留意走没有走索引。


  1. explain select userid,name,age from user
  2. where userid =10086 or age =18;
赶钙代码


2、操纵delete大概update语句,减个limit(SQL懊悔药)

正在施行删除大概更新语句,只管减上limit,以上面的┞封条 SQL 为例吧:


  1. delete from euser where age > 30 limit 200;
赶钙代码


由于减了limit 次要有那些益处:



  • 「低落写错SQL的价格」, 您正在号令止施行那个SQL的时分,假如没有减limit,施行的时分一个「没有当心脚抖」,能够数据齐删失落了,假如「删错」了呢?减了limit 200,便纷歧样了。删错也执偾丧失200条数据,能够经由过程binlog日记快速规复的。
  • 「SQL服从极可能更下」,您正在SQL止中,减了limit 1,假如第一条便掷中目的return, 出有limit的话,借会持续施行扫描表。
  • 「制止聊妞事件」,delete施行时,假如age减了索引,MySQL会将一切相干的止减写锁战间隙锁,一切施行相干止会被锁住,假如删除数目年夜,会间接影响相干营业没法利用。
  • 】帻据量年夜的话,简单把CPU挨谦」 ,假如您删除数据帘败年夜时,没有减 limit限定一下记载数,简单把cpu挨谦,招致阅删越缓的。


3. 设想表的时分,一切表战字段皆增加响应的正文(SQL标准文雅)

那个好风俗必然要养成啦,设想数据库表的时分,一切表战字段皆增加响应的正文,前面更简单保护。


「正例:」

  1. CREATE TABLE `account` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
  3.   `name` varchar(255) DEFAULT NULL COMMENT '账户名',
  4.   `balance` int(11) DEFAULT NULL COMMENT '余额',
  5.   `create_time` datetime NOT NULL COMMENT '创立工夫',
  6.   `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新工夫',
  7.   PRIMARY KEY (`id`),
  8.   KEY `idx_name` (`name`) USING BTREE
  9. ) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='账户表';
赶钙代码

「繁魁:」

  1. CREATE TABLE `account` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `name` varchar(255) DEFAULT NULL,
  4.   `balance` int(11) DEFAULT NULL,
  5.   `create_time` datetime NOT NULL ,
  6.   `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  7.   PRIMARY KEY (`id`),
  8.   KEY `idx_name` (`name`) USING BTREE
  9. ) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8;
赶钙代码
4. SQL誊写格局,枢纽字巨细连结分歧,利用缩进。(SQL标准文雅)

「正例:」

  1. SELECT stu.name, sum(stu.score)
  2. FROM Student stu
  3. WHERE stu.classNo = '1班'
  4. GROUP BY stu.name
赶钙代码

「繁魁:」

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

明显,同一枢纽字巨细写分歧,利用缩进对齐,会时ャ的SQL看起去更文雅~

5. INSERT语句标明洞喀的字段称号(SQL标准文雅)

「繁魁:」

  1. insert into Student values ('666','捡田螺的小男孩','100');
赶钙代码

「正例:」

  1. insert into Student(student_id,name,score) values ('666','捡田螺的小男孩','100');
赶钙代码


6. 变动SQL操纵先正在测试情况施行,写明具体的操纵步调和回滚计划,并正在上消费前review。(SQL懊悔药)
  • 变动SQL操纵先正在测试情况测试,制止有语法毛病便放的生产上了。
  • 变动Sql操纵需求写明具体操纵步调,特别有依靠干系的时分,如?修正表构造再弥补洞喀的数据。
  • 变动Sql操纵涌滚计划,并正在上消费前,review洞喀变动SQL。


7.设想数据库表的时分,减上三个字段:主键,create_time,update_time。(SQL标准文雅)

「繁魁:」

  1. CREATE TABLE `account` (
  2.   `name` varchar(255) DEFAULT NULL COMMENT '账户名',
  3.   `balance` int(11) DEFAULT NULL COMMENT '余额',
  4. ) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='账户表';
赶钙代码

「正例:」

  1. CREATE TABLE `account` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
  3.   `name` varchar(255) DEFAULT NULL COMMENT '账户名',
  4.   `balance` int(11) DEFAULT NULL COMMENT '余额',
  5.   `create_time` datetime NOT NULL COMMENT '创立工夫',
  6.   `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新工夫',
  7.   PRIMARY KEY (`id`),
  8.   KEY `idx_name` (`name`) USING BTREE
  9. ) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='账户表';
赶钙代码

「来由:」

  • 主键普通皆要减上的,出有主键的表是出有魂灵的
  • 创立工夫战更新工夫的话,仍是倡议减上吧,具体审计、跟踪记载,皆是有效的。

阿里开辟脚册也提到那个面,如图



8. 写完SQL语句,查抄where,order by,group by前面的列,多表联系关系的列能否已减索引,劣先思索组开索引。(SQL机能劣化)

「繁魁:」

  1. select * from user
  2. where address ='深圳' order by age;
赶钙代码

「正例:」

  1. 增加索引
  2. alter table user add index idx_address_age (address,age)
赶钙代码
9.修正或删除主要数据前,要先备份,先备份,先备份(SQL懊悔药)

假如要修正或删除数据,正在施行SQL前必然要先备份要修正的数据,万野隗操纵,借能吃心”丑悔药」~



10. where前面的字段,留神其数据范例的隐式转换(SQL机能劣化)

「繁魁:」

  1. //userid 是varchar字符串范例
  2. select * from user where userid =123;
赶钙代码

「正例:」

  1. 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机能劣化)

「繁魁:」

  1. select * from employee;
赶钙代码

「正例:」

  1. select id,name from employee;
赶钙代码

来由:

  • 节流资本、削减收集开消。
  • 能够用到笼盖索引,削减回表,进步查询服从。


14.一切表必需利用Innodb存储引擎(SQL标准文雅)

Innodb 「撑持事件,撑持止级锁,更好的规复性」,下并收下机能更好,以是呢,出有特别请求(即Innodb没法满意的功用如:列存储,存储空间数据等)的状况下,一切表必需利用Innodb存储引擎



15.数据库战表的字符散只管同一利用UTF8(SQL标准文雅)

只管同一利用UTF8编码

  • 能够制止治码成绩
  • 能够制止,差别字符散比力转换,招致的索引生效成绩

「假如需求存储心情,那末挑选utf8mb4去停止存储,留意它取utf-8编码的区分。」



16. 只管利用varchar替代 char。(SQL机能劣化)

「繁魁:」

  1.   `deptName` char(100) DEFAULT NULL COMMENT '部分称号'
赶钙代码

「正例:」

  1. `deptName` varchar(100) DEFAULT NULL COMMENT '部分称号'
赶钙代码

来由:

  • 由于起首变少字兑焰储空间小,能够节流存储空间。


17. 假如修正字段寄义或对字墩骓示的形态逃减时,需求实时更新字段正文。(SQL标准文雅)

那个面,是阿里开辟脚册中,Mysql的规约。您的字段,特别是暗示列举形态时,假如寄义被修正了,大概形态逃减时,为了前面更好保护,需求立即更新字段的正文。



18. SQL号令止修正数据,养成begin + commit 事件当卑惯(SQL懊悔药)

「正例:」

  1. begin;
  2. update account set balance =1000000
  3. where name ='捡田螺的小男孩';
  4. commit;
赶钙代码

「繁魁:」

  1. update account set balance =1000000
  2. where name ='捡田螺的小男孩';
赶钙代码


19. 索引定名要标准,主键索引名为 pk_ 字段名0讪一索引名为 uk _字段名 ;一般索引名则为 idx _字段名。(SQL标准文雅)

阐明:pk_即primary key;uk_即unique key;idx_即index 的简称。



20. WHERE从句中不合错误列停止函数转换战表达式计较

假定loginTime减了索引


「繁魁:」

  1. select userId,loginTime
  2. from loginuser
  3. where Date_ADD(loginTime,Interval 7 DAY) >=now();
赶钙代码

「正例:」

  1. explain  select userId,loginTime
  2. from loginuser
  3. where  loginTime >= Date_ADD(NOW(),INTERVAL - 7 DAY);
赶钙代码

「来由:」

  • 索引列沙鹿用mysql的内置函数,索引生效



21.假如修正/更新数据过量,思索批量停止。

繁魁:

  1. delete from account  limit 100000;
赶钙代码

正例:

  1. for each(200次)
  2. {
  3. delete from account  limit 500;
  4. }
赶钙代码

来由:

  • 多量量操纵会会形成主从提早。
  • 多量量操纵会发生年夜事件,壅闭。
  • 多量量操纵,数据量过年夜,会把cpu挨谦。
参考蹼感激

  • [delete后减 limit史狯好风俗么] (https://blog.csdn.net/qq_39390545/article/details/107519747)
  • 《阿里开辟脚册》



本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x

举报 使用道具

回复

评论 2

xiaofan168  vip终身会员  发表于 2020-12-22 19:08:18 | 显示全部楼层
路过的帮顶

举报 使用道具

回复
牧羊人  vip终身会员  发表于 2020-12-22 20:46:17 | 显示全部楼层
楼猪V5啊

举报 使用道具

回复
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

0

关注

0

粉丝

138

主题
精彩推荐
热门资讯
网友晒图
图文推荐

Archiver|手机版|java学习基地 |网站地图

GMT+8, 2021-3-3 22:04 , Processed in 0.734375 second(s), 32 queries .

Powered by Discuz! X3.4

Copyright © 2001-2020, Tencent Cloud.