在MySQL中，写SQL的21个好习惯（修订版）

前言每一个好习惯都是一笔财富。本文分为三个方向：SQL后悔药、SQL性能优化、SQL标准优雅，分享21个SQL编写技巧的好习惯，感谢阅读，加油~1.写完SQL，先讲解查看执行计划（SQL性能优化）。在日常开发写SQL的时候，尽量养成这个好习惯：写完SQL，用explain分析，注意不要走索引。explainselectuserid,name,agefromuserwhereuserid=10086orage=18;2、操作delete或update语句，加limit（SQL后悔药）执行delete或update语句，尝试加limit，以下面的SQL为例：deletefromuserwhereage>30limit200;因为加limit主要有这些好处：“减少写错SQL的代价”，当你在命令行执行这条SQL的时候，如果不加limit，执行的时候，如果不小心手一抖，可能所有的数据都被删除了，万一呢“删除是错误的”？加上limit200后就不一样了。如果误删，只会丢失200条数据，可以通过binlog日志快速恢复。“SQL可能会更有效率。”您将limit1添加到SQL行。如果第一行命中目标返回，如果没有限制，则继续扫描表。“避免长交易”。执行delete时，如果age被索引，MySQL会在所有相关行上加上写锁和间隙锁，所有执行相关的行都会被加锁。如果删除数量较大，将直接影响相关业务不可用。“如果数据量很大，很容易把CPU占满。”如果删除大量数据，如果不加limit限制记录数，很容易把CPU占满，导致删除变慢。3、在设计表时，为所有的表和字段添加相应的注释（SQL规范优雅）。这个好习惯一定要养成。在设计数据库表的时候，给所有的表和字段加上相应的注释，这样以后维护起来会更方便。"正例："CREATETABLE`account`(`id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'主keyId',`name`varchar(255)DEFAULTNULLCOMMENT'账户名',`balance`int(11)DEFAULTNULLCOMMENT'balance',`create_time`datetimeNOTNULLCOMMENT'创建时间',`update_time`datetimeNOTNULLONUPDATECURRENT_TIMESTAMPCOMMENT'更新时间',PRIMARYKEY(`id`),KEY`idx_name`(`name`)USINGBTREE)ENGINE=InnoDBAUTO_INCREMENT=1570068DEFAULTCHARSET=utf8ROW_FORMAT'account"table反例："创建表`account`(`id`int(11)NOTNULLAUTO_INCREMENT,`name`varchar(255)DEFAULTNULL,`balance`int(11)DEFAULTNULL,`create_time`datetimeNOTNULL,`update_time`datetimeNOTNULLONUPDATECURRENT_TIMESTAMP,PR`id`KEY(),KEY`idx_name`(`name`)USINGBTREE)ENGINE=InnoDBAUTO_INCREMENT=1570068DEFAULTCHARSET=utf8;4.SQL书写格式，关键字大小要一致，使用缩进。（SQL规范优雅）《正例：》SELECTstu.name,sum(stu.score)FROMStudentstuWHEREstu.classNo='1class'GROUPBYstu.name《反例：》SELECTstu.name,sum(stu.score)fromStudentstuWHEREstu.classNo='Class1'groupbystu.name.显然统一关键字大小写一致，使用缩进对齐会让你的SQL看起来更优雅~5。INSERT语句表示对应字段名（SQL规范优雅）"反例："insertintoStudentvalues('666','捡蜗牛的小男孩','100');"正例："insertintoStudent(student_id,name,score)values('666','捡蜗牛的小男孩','100');6.先在测试环境改SQL操作，写下详细的操作步骤和回滚方案，上线前review。（SQL后悔药）改SQL操作，先在测试环境测试，避免语法错误，再投入生产。改变Sql操作，需要写详细的操作步骤，尤其是有依赖的时候，比如：先修改表结构，再补充相应的数据。改Sql操作有回滚计划，上线前review对应改SQL。7、设计数据库表时，增加三个字段：primarykey，create_time，update_time。(SQL规范优雅)"反例:"CREATETABLE`account`(`name`varchar(255)DEFAULTNULLCOMMENT'账户名',`balance`int(11)DEFAULTNULLCOMMENT'balance',)ENGINE=InnoDBAUTO_INCREMENT=1570068DEFAULTCHARSET=utf8ROW_FORMAT=REDUNDANTCOMMENT='账户表';"正例："CREATETABLE`account`(`id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'主keyId',`name`varchar(255)DEFAULTNULLCOMMENT'账户名',`balance`int(11)DEFAULTNULLCOMMENT'余额',`create_time`datetimeNOTNULLCOMMENT'创建时间',`update_time`datetimeNOTNULLONUPDATECURRENT_TIMESTAMPCOMMENT'更新时间',PRIMARYKEY(`id`),KEY`idx_name`(`name`)USINGBTREE)ENGINE=InnoDBAUTO_INCREMENT=1570068DEFAULTCHARSET_WATD=utf'账户表';”原因：“一般都会加上主键。如果没有主键的表没有创建时间和更新时间，建议加上。详细的审计和跟踪记录很有用。阿里巴巴开发手册中也提到了这一点，如图8所示。写完SQL语句后，查看where、orderby、groupby后面的列，以及多表关联的列是否已经建立索引。组合索引优先。(SQL性能优化)《反例：》select*fromuserwhereaddress='Shenzhen'orderbyage;》正例：》Addindexaltertableuseraddindexidx_address_age(address,age)9、修改或删除重要数据前，必须先备份，先备份，先备份（SQL后悔药）如果要修改或删除数据，必须先备份在执行SQL之前先把要修改的数据up。转换（SQL性能优化）"反例："//userid为varchar字符串类型select*fromuserwhereuserid=123;“正例：”select*fromuserwhereuserid='123';字符串和数字的比较，它们的类型不匹配，MySQL会做隐式类型转换，转换成浮点数再比较，最后导致索引失败11.尝试将所有列定义为NOTNULL(SQLspecificationiselegant)"NOTNULLcolumnsaremorespace-saving",NULL列需要一个额外的字节作为判断是否为NULL的标志。《NULL列需要注意空指针问题》，在计算和比较NULL列的时候，需要注意空指针的问题。12、修改或删除SQL，先写WHERE检查，确认后再添加delete或update（SQL后悔药），再执行update或delete操作13、减少不必要的字段返回，如使用select而不是select*（SQL性能优化）“反例：”select*fromemployee；“正例：”selectid,namefromemployee;原因：节省资源，减少网络开销。覆盖索引可以用来减少表返回，提高查询效率。14、所有表必须使用Innodb存储引擎（SQL规范优雅）Innodb“支持事务，支持行级锁，恢复更好”，在高并发下有更好的性能，所以没有特殊要求（就是，Innodb不能满足的）如：列存储、存储空间数据等功能），所有表必须使用Innodb存储引擎15.数据库和表的字符集统一使用UTF8（SQL规范优雅）尽量统一使用UTF8编码，避免出现乱码、不同字符集比较转换导致的索引失败》如果需要存储表达式，那么选择utf8mb4进行存储，注意两者的区别它和utf-8编码。”16.尽量使用varchar而不是char。(SQL性能优化)《反例：》`deptName`char(100)DEFAULTNULLCOMMENT'部门名'《正例：》`deptName`varchar(100)DEFAULTNULLCOMMENT'部门名'原因：因为变长的存储空间场地小，可以节省存储空间。17、如果修改了字段的含义或者添加了字段代表的状态，需要及时更新字段注释。（SQL规范优雅）这一点是阿里开发手册中Mysql的规范。如果你的字段，尤其是代表枚举状态的时候，其含义发生了变化，或者增加了状态，为了后面更好的维护，你需要立即更新该字段的注释。18、SQL命令行修改数据，养成begin+commit事务的习惯（SQL后悔药）《正例：》begin;updateaccountsetbalance=1000000wherename='捡到蜗牛的小男孩';commit;捡蜗牛的小男孩；19、索引名称要规范，主键索引名称为pk_field名称；唯一索引名称是uk_field名称；公共索引名称是idx_field名称。（SQL规范优雅）解释：pk_为主键；uk_是唯一键；idx_是索引的缩写。20、在WHERE子句中，不要对列进行函数转换和表达式计算。假设loginTime已编入索引。"反例："selectuserId,loginTimefromloginuserwhereDate_ADD(loginTime,Interval7DAY)>=now();"正例："explainselectuserId,loginTimefromloginuserwhereloginTime>=Date_ADD(NOW(),INTERVAL-7DAY);”原因：“索引列上使用了mysql的内置函数，索引失效。21.如果修改/更新的数据太多，可以考虑分批进行。反例：deletefromaccountlimit100000；正例：foreach(200次){deletfromaccountlimit500;}原因：大批量操作会造成主从延迟。大批量操作会产生大交易和区块。大规模运算，数据量太大，会把CPU占满。参考并感谢【先删后加limit是个好习惯吗】（https://blog.csdn.net/qq_39390545/article/details/107519747）♂本文转载自微信公众号《小男孩采摘》upsnails”，您可以通过以下二维码关注。转载本文请联系捡蜗牛的小男孩公众号。