MySQL中，写SQL的21个好习惯

每一个好习惯都是一笔财富。本文分为SQL后悔药、SQL性能优化、SQL规范与优雅三个方向，分享21个编写SQL的好习惯。感谢阅读加油~1、写完SQL，先讲解查看执行计划（SQL性能优化）。在日常开发写SQL的时候，尽量养成这个好习惯：写完SQL，用explain分析一下，尤其要注意是否留有索引。explainselect*fromuserwhereuserid=10086orage=18;2、操作删除或更新语句，加一个limit（SQL后悔药）执行删除或更新语句，尝试加limit，以如下SQL为例：deletefromuserwhereage>30limit200；因为加入limit的主要好处是：减少写错SQL的代价。当你在命令行执行这条SQL的时候，如果不加limit，如果执行的时候不小心手一抖，可能会把所有的数据都删掉。如果删错了么？加上limit200后就不一样了。如果误删，只会丢失200条数据，可以通过binlog日志快速恢复。SQL可能更高效。您将limit1添加到SQL行。如果第一行命中目标return，如果没有limit，就会继续扫表。为了避免长事务，在执行delete时，如果age被索引，MySQL会在所有相关行上加上写锁和间隙锁，所有执行相关的行都会被加锁。如果删除的数量很大，会直接影响到相关业务，使其无法使用。如果数据量很大，很容易把CPU占满。如果删除大量数据，如果不加limit限制记录数，很容易把CPU占满，导致删除变慢。3、在设计表时，为所有的表和字段添加相应的注释（SQL规范优雅）。这个好习惯一定要养成。在设计数据库表的时候，给所有的表和字段加上相应的注释，这样以后维护起来会更方便。正例：CREATETABLE`account`(`id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'主键Id',`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=REDUNDANTCOMMENT='账户表`account(`id`int(11)NOTNULLAUTO_INCREMENT,`name`varchar(255)DEFAULTNULL,`balance`int(11)DEFAULTNULL,`create_time`datetimeNOTNULL,`update_time`datetimeNOTNULLONUPDATECURRENT_TIMESTAMP,PRIMARYKEY(`id`),KEY`idx_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='1class'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'余额',)ENGINE=InnoDBAUTO_INCREMENT=1570068DEFAULTCHARSET=utf8ROW_FORMAT=REDUNDANTCOMMENT='账户表';正例：CREATETABLE`account`(`id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'primarykeyId',`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'原因：必须添加主键。如果没有主键的表没有创建时间和更新时间，建议加上。详细的审计和跟踪记录很有用。阿里巴巴开发手册中也提到了这一点，如图8所示。写完SQL语句后，查看where、orderby、groupby后面的列，以及多表关联的列是否已经建立索引。组合索引优先。(SQL性能优化)反例：select*fromuserwhereaddress='Shenzhen'orderbyage;正例：addindexaltertableuseraddindexidx_address_age(address,age)9、修改或删除重要数据前，一定要先备份，先备份，再备份（SQL后悔药）如果要修改或删除数据，一定要将数据备份到在执行SQL之前修改。万一用错了，还可以追悔莫及~10.对于where之后的字段，注意其数据类型的隐式转换（SQL性能优化）反例：//userid是varcharstring类型select*fromuserwhereuserid=123;正例：select*fromuserwhereuserid='123';原因：因为没有加单引号的时候，是字符串和数字的比较，他们的类型不匹配，MySQL会做隐式类型转换，转换成浮点数再比较，最后导致indextofailsection作为判断是否为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编码可以避免乱码问题，可以避免不同字符集比较转换导致的索引失败16.尽量使用varchar而不是char。(SQL性能优化)反例：`deptName`char(100)DEFAULTNULLCOMMENT'部门名'正例：`deptName`varchar(100)DEFAULTNULLCOMMENT'部门名'原因：由于变长字段存储空间小，它可以节省存储空间。其次，对于查询来说，在一个比较小的领域内搜索效率更高。17、如果修改了字段的含义或者添加了字段代表的状态，需要及时更新字段注释。（SQL规范优雅）这一点是阿里开发手册中Mysql的规范。如果你的字段，尤其是代表枚举状态的时候，其含义发生了变化，或者增加了状态，为了后面更好的维护，你需要立即更新该字段的注释。18、SQL修改数据，养成begin+commit事务的习惯（SQL后悔药）正例：begin;updateaccountsetbalance=1000000wherename='捡蜗牛的小男孩';commit;反例：updateaccountsetbalance=1000000wherename='捡蜗牛的小男孩';19.索引命名要规范，主键索引名称为pk_field名称；唯一索引名称是uk_field名称；公共索引名称是idx_field名称。(SQLSpecificationElegant)解释：pk_是主键；uk_是唯一键；idx_是索引的缩写。20、不要对WHERE子句中的列进行函数转换和表达式计算。假设loginTime已编入索引。反例：selectuserId,loginTimefromloginuserwhereDate_ADD(loginTime,Interval7DAY)>=now();正例：explainselectuserId,loginTimefromloginuserwhereloginTime>=Date_ADD(NOW(),INTERVAL-7DAY);原因：索引列上使用mysql内置函数，索引失效。反例：deletefromaccountlimit100000；正例：foreach(200次){deletfromaccountlimit500;}原因：大批量操作会造成主从延迟。大批量操作会产生大交易和区块。大规模运算，数据量太大，会把CPU占满。