江苏龙网

  1. 主页 > 生活百科 > >

用惨痛教训换来的156条MySQL设计规约( 四 )

MySQL设计


20.【推荐】尽量不使用外键、外键用来保护参照完整性，可在业务端实现 。
说明：避免对父表和子表的操作会相互影响，降低可用性 。
21.【强制】字符串不应做主键 。
22.【强制】表必须有无符号int型自增主键，对应表中id字段 。
说明：必须得有主键的原因：采用RBR模式复制，无主键的表删除 ， 会导致备库夯住 ；使用自增的原因：数据写入可以提高插入性能，避免page分裂，减少表碎片 。
23.【推荐】对长度过长的VARCHAR字段建立索引时 ， 添加crc32或者MD5 Hash字段，对Hash字段建立索引 。
说明：下面的表增加一列url_crc32，然后对url_crc32建立索引 ， 减少索引字段的长度 ， 提高效率 。
CREATE TABLE url( ... url VARCHAR(255) NOT NULL DEFAULT 0, url_crc32 INT UNSIGNED NOT NULL DEFAULT 0, ... index idx_url(url_crc32) ）
24.【推荐】WHERE条件中的非等值条件（IN、BETWEEN、<、<=、>、>=）会导致后面的条件使用不了索引 。
25.【推荐】索引字段的顺序需要考虑字段值去重之后的个数 ， 个数多的放在前面 。
26.【推荐】ORDER BY，GROUP BY，DISTINCT的字段需要添加在索引的后面 。
27.【参考】合理创建联合索引（避免冗余），如(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c) 。
28.【推荐】复合索引中的字段数建议不超过5个 。
29.【强制】不在选择性低的列上建立索引，例如"性别", "状态"，"类型" 。
30.【推荐】对于单独条件如果走不了索引，可以使用force –index强制指定索引 。
31.【强制】禁止给表中的每一列都建立单独的索引 。
32.【推荐】在varchar字段上建立索引时，必须指定索引长度 ， 没必要对全字段建立索引，根据实际文本区分度决定索引长度即可? 。
八、SQL使用规范
1.【强制】禁止使用SELECT *，只获取必要的字段，需要显示说明列属性 。
说明：按需获取可以减少网络带宽消耗，能有效利用覆盖索引，表结构变更对程序基本无影响 。
2.【强制】禁止使用INSERT INTO t_xxx VALUES(xxx)，必须显示指定插入的列属性 。
3.【强制】WHERE条件中必须使用合适的类型，避免MySQL进行隐式类型转化 。
说明：因为MySQL进行隐式类型转化之后 ， 可能会将索引字段类型转化成=号右边值的类型 ， 导致使用不到索引，原因和避免在索引字段中使用函数是类似的，例子 select uid from t_user where phone=15855550101（phone为 varchat 类型，此时查询中使用数字查询，会导致索引失效） 。
4.【强制】禁止在WHERE条件的属性上使用函数或者表达式 。
5.【强制】禁止负向查询，以及%开头的模糊查询 。
6.【强制】应用程序必须捕获SQL异常，并有相应处理 。
7.【推荐】sql语句尽可能简单、大的sql想办法拆成小的sql语句 。
说明：简单的SQL容易使用到MySQL的querycache、减少锁表时间特别是MyISAM、可以使用多核cpu 。
8.【推荐】事务要简单 ， 整个事务的时间长度不要太长 。
9.【强制】避免在数据库中进行数学运算或者函数运算（MySQL不擅长数学运算和逻辑判断，也容易将业务逻辑和DB耦合在一起） 。
10.【推荐】sql中使用到OR的改写为用IN() （or的效率没有in的效率高） 。
11.【参考】SQL语句中IN包含的值不应过多，里面数字的个数建议控制在1000个以内 。
12.【推荐】limit分页注意效率 。Limit越大，效率越低 。可以改写limit 。
说明：改写例子：
1）改写方法一
延迟回表写法 select xx,xx from t t1, (select id from t where .... limit 10000,10) t2 where t1.id = t2.id
2）改写方法二
select id from t limit 10000, 10; 应该改为 => select id from t where id > 10000 limit 10;
13.【推荐】尽量使用union all替代union 。
14.【参考】避免使用大表JOIN 。
15.【推荐】对数据的更新要打散后批量更新，不要一次更新太多数据 。
16.【推荐】使用合理的SQL语句减少与数据库的交互次数 。
17.【参考】注意使用性能分析工具 Sql explain / showprofile / mysqlsla 。
18.【推荐】能不用NOT IN就不用NOT IN，坑太多了，会把空和NULL给查出来 。
19.【推荐】关于分页查询，程序里建议合理使用分页来提高效率 ， limit、offset较大要配合子查询使用 。
20.【强制】禁止在数据库中跑大查询 。
21.【强制】禁止单条SQL语句同时更新多个表 。
22.【推荐】统计表中记录数时使用COUNT(*) ， 而不是COUNT(primary_key)和COUNT(1) 。


推荐阅读


上一篇：解决 Java 打印日志吞异常堆栈的问题

下一篇：业务系统常用限流算法浅析