mysql 开发规范
MySQL数据库开发规范
sql执行顺序:
(1)from
(3) join
(2) on
(4) where
(5)group by
(6) avg,sum
(7)having
(8) select
(9) distinct
(10) order by
1. 杜绝直接 SELECT * 读取全部字段
即使需要所有字段，也要指定表的字段名：select a.col1,a.col2…. from tab a
2. 能确定返回结果只有一条时，使用 limit 1
在保证数据不会有误的前提下，能确定结果集数量时，多使用limit，尽快的返回结果。
3. 小心隐式类型转换
- 转换规则
a. 两个参数至少有一个是
NULL 时，比较的结果也是 NULL，例外是使用 <=> 对两个 NULL 做比较时会返回 1，这两种情况都不需要做类型转换
b. 两个参数都是字符串，会按照字符串来比较，不做类型转换
c. 两个参数都是整数，按照整数来比较，不做类型转换
d. 十六进制的值和非数字做比较时，会被当做二进制串
e. 有一个参数是 TIMESTAMP
或 DATETIME，并且另外一个参数是常量，常量会被转换为
timestamp
f. 有一个参数是 decimal 类型，如果另外一个参数是 decimal 或者整数，会将整数转换为 decimal 后进行比较，如果另外一个参数是浮点数，则会把 decimal 转换为浮点数进行比较
g. 所有其他情况下，两个参数都会被转换为浮点数再进行比较。
- 如果一个索引建立在string类型上，如果这个字段和一个int类型的值比较，符合第 g 条。如f_phone定义的类型是varchar，但where使用f_phone in (098890)，两个参数都会被当成成浮点型。发生这个隐式转换并不是最糟的，最糟的是string转换后的float，mysql无法使用索引，这才导致了性能问题。如果是
f_user_id = ‘1234567’ 的情况，符合第
b 条,直接把数字当字符串比较。
- 会导致索引失效，如substring(CREATE_TIME,1,8)。可放到右边的常量上计算:
- where CREATE_TIME between concat(i_create_time,’000000’) and concat(i_create_time,’235959’)
- 返回小结果集不是很大的情况下，可以对返回列使用函数，简化程序开发
4. 禁止在where条件列上使用函数
5. 使用like模糊匹配，%不要放首位
会导致索引失效，有这种搜索需求是，考虑其它方案，如name全匹配搜索
6. 涉及到复杂sql时，务必先参考已有索引设计，先explain
- 简单SQL拆分，不以代码处理复杂为由。
- 比如 OR 条件： f_phone=’10000’ or f_mobile=’10000’，两个字段各自有索引，但只能用到其中一个。可以拆分成2个sql，或者union all。
- 先explain的好处是可以为了利用索引，增加更多查询限制条件
- 如 select t1.a,t2.b * from t1,t2 where t1.a=t2.a and t1.b=123 and t2.c= 4 ，如果t1.c与t2.c字段相同，那么t1上的索引(b,c)就只用到b了。此时如果把where条件中的t2.c=4改成t1.c=4，那么可以用到完整的索引
- 在具体业务场景要合理正确的选取inner join和left join
7. 使用join时，where条件尽量使用充分利用同一表上的索引
8. 少用子查询，改用join
5.6版本以前，子查询效率很低，尽管5.6版本开始得到优化，但是效率还是很低。在工作中尽量少用或不用子查询。
9. 考虑使用union all，少使用union，注意考虑去重
- union all不去重，而少了排序操作，速度相对比union要快，如果没有去重的需求，优先使用union all
- 如果UNION结果中有使用limit，在2个子SQL可能有许多返回值的情况下，各自加上limit。如果还有order by，请找DBA。
10. IN的内容尽量不超过200个
超过200个值使用批量的方式，否则一次执行会影响数据库的并发能力，因为单SQL只能且一直占用单CPU，而且可能导致主从复制延迟
11. 拒绝大事务
比如在一个事务里进行多个select，多个update，如果是高频事务，会严重影响MySQL并发能力，因为事务持有的锁等资源只在事务rollback/commit时才能释放。但同时也要权衡数据写入的一致性。
12. 避免使用is null, is not null这样的比较
where col is null 或where col is not null都不会用到索引，会全表扫描。
13. order by .. limit
这种查询更多的是通过索引去优化，order by的字段需要建立索引来优化查询。 。
14. 分页优化
建议使用合理的分页方式以提高分页效率，大页情况下不使用跳跃式分页
假如有类似下面分页语句:
SELECT * FROM table1 ORDER BY ftime DESC LIMIT 10000,10;
这种分页方式会导致大量的io，因为MySQL使用的是提前读取策略。
推荐分页方式：
SELECT *
FROM table1 WHERE ftime < last_time ORDER
BY ftime DESC LIMIT 10
即传入上一次分页的界值
SELECT * FROM table as t1 inner JOIN (SELECT id FROM table ORDER BY time LIMIT 10000，10) as t2 ON t1.id=t2.id
15. count计数
- 首先count(*)、count(1)、count(col1)是有区别的，count(*)表示整个结果集有多少条记录，count(1)表示结果集里以primary key统计数量，绝大多数情况下count(*)与count(1)效果一样的，但count(col1)表示的是结果集里 col1 列 NOT null 的记录数。优先采用count(1)
- 大数据量count是消耗资源的操作，甚至会拖慢整个库，查询性能问题无法解决的，应从产品设计上进行重构。例如当频繁需要count的查询，考虑使用汇总表
- 遇到distinct的情况，group by方式可能效率更高。
16. delete,update语句改成select再explain
select最多导致数据库慢，写操作才是锁表的罪魁祸首
17. 减少与数据库交互的次数，尽量采用批量SQL语句
INSERT ... ON DUPLICATE KEY UPDATE ...，如果直接insert语句插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，INSERT ... ON DUPLICATE KEY UPDATE ...则执行旧行UPDATE，如果不重复则直接插入，影响1行。
INSERT INTO table (a,b,c) VALUES
(1,2,3) ON DUPLICATE KEY UPDATE c=c+1;
INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c= VALUES(a)+VALUES(b);
REPLACE INTO类似，但它是冲突时删除旧行。
REPLACE INTO testa (a,b,c) VALUES(1, 3, 8);
INSERT IGNORE相反，保留旧行，丢弃要插入的新行。
INSERT IGNORE testa (a,b,c) VALUES(1, 9, 9);
- INSERT INTO VALUES(),(),()，合并插入。
INSERT INTO table (a,b,c) VALUES (1,2,3)，(2,2,3)，(3,2,3)
18. 杜绝危险SQL
- 去掉where 1=1 这样无意义或恒真的条件，如果遇到update/delete或遭到sql注入就恐怖了
- SQL中不允许出现DDL语句。一般也不给予create/alter这类权限，但阿里云RDS只区分读写用户
