MySQL开发约束

MySQL 表设计规范

• 明确规定：禁用存储过程，函数，触发器，视图，外键，所有逻辑业务上实现
• 必须使用 innodb 引擎，不允许用myisam。统一utf8mb4编码，不能gbk。
• 必须要有主键，主键如无特殊情况，必须自增整形。例如： INT或者 BIGINT类型，如果是分片表，主键自增可由中间件实现。
• 所有字段要求使用not null，字段都定义为： not null default ‘ 合理默认值’ 。
• 需要多表 join的字段，数据类型、长度必须保持绝对一致，才能用到索引。例如：
  • int 和 varchar 不能索引
  • char(10) 和 varchar(10) 能用到索引
  • char(10) 和 varchar(20) 无法索引
• 当表的字段数较多时，将表分成两张表，一张作为条件查询表，一张作为详细内容表。
• 当字段的类型为枚举型或布尔型时，建议使用 tinyint类型，不用char或者varchar。。
• 常见字段类型(一般不允许用text和blob)，若必须使用则拆分到单独的表。
• 字段类型越短越好，能用整形的尽量用整形，比如 tinyint 代替 char(1) 。
• 字段不区分大小写。

为什么要用自增ID做主键？

1. MySQL InnoDB为索引组织表，即使你不设置主键它也会维护一个隐藏主键。所有隐藏主键共享一个自增锁。

   如果很多表没定义主键，就会成为瓶颈。

2. 隐藏主键为一个6字节的整型，可能存在被写完而不被察觉的风险。

3. 很多工具一定要求表明确定义主键，如online ddl（不锁表加索引、字段等表变更）。工具不可用会让日

   常维护变得复杂。

4. 没有设置主键MySQL可能用表上面的唯一索引作为主键，如果这个唯一索引很长，性能将特别差。
5. 什么是索引组织表？类似英文字典，每个词按照字母组织的（ a-z）
   1. 越短的单词越容易被查到；
   2. MySQL InnoDB所有的二级索引（普通索引）都包含主键，主键越长二级索引越大；

   3. 添加单词要加在字典已有单词中间，相类似，可能引发数据页的分页操作。但如果一直是追加就不会

      分页。

6. 自增ID可以用来做分页优化。如下语句性能相差1000倍。
   1. Select * from tbl limit 10000000,30;
   2. Select * from tbl where id>10000000 limit 30;

为什么字段类型越短越好？

1. 更少的存储空间
2. 更少的磁盘IO
3. 更少的网络IO
4. 更少的MySQL计算空间
5. 更少的APP计算空间
6. 整形存储和运算代价比字符型小，有text字段的，最好分表。（本质上说,不是mysql不适合存储text,而是在太多的情况下我们期望 MySQL 能够更加高效的提供小数据查询/事务处理）

为什么尽量不使用default null

1. 索引不会包括NULL值。影响索引的统计信息（譬如count统计不到NULL值 ），影响优化器的判断。
2. 复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。
3. NULL会使用更多的空间。

如果NULL有业务意义，可以用其他值表示，例如0或者空字符串。

MySQL索引设计规范

• 不要修改聚集索引（主键）

  • 为了维持B+tree会带来大量的数据移动，所以一般要求使用跟业务不相关的id做一个整形自增

    主键

• 索引不是越多越好，尽量合并索引
  • 索引加快了查询度，但是却会影响写入性能。
  • 一个表的索引应该结合这个表相关的所有SQL综合创建，尽量合并。
  • 组合索引的原则是，过滤性越好的字段越靠前。例如key (a)和key（ a,b）存在，那么key（ a）可以删除了，对于select ……from tb where a=123；可以用到索引（ a， b）
• 不要给选择性低的字段建单列索引
  • MySQL对索引的过滤性有要求，如果过滤性太低MySQL会放弃使用。
• 不要使用外键约束
  1. 对性能损耗特别大。
  2. 让应用程序去维护约束。
• 字符类型字段尽量使用前缀索引
  • 太长的索引不仅影响写入性能，而且使用效果也差，因此字符串类型字段一般只建前缀索引
  • alter table test_long_str add index idx_str(str(16));

  • where语句不区分先后，索引才区分先后顺序。 Where a=x and b=x和Where b=x and c=x没区别

    最左前缀原理：索引从左到右：如有索引idx_abc(a,b.c)

    Where a =x and b=x order c 能用到索引

    Where b= x order by c不能用到索引

• 合理使用复合索引，减少书签查找。
• LIKE查询的索引问题
  • like只能使用前缀索引，因此 ：
  • col like “abc%” 能用上索引
  • col like “%abc%” 不能能用上索引
  • col like “%abc” 不能能用上索引
• 否定条件无法使用索引 <>,not in,not exists都无法用到索引
• join的字段，数据类型、 长度、字符集必须保持绝对一致，才能用到索引。
  • on 加在 int 和 varchar 不能索引
  • char(10) 和varchar(10) 能用到索引
  • char(10) 和varchar(20) 无法索引

MySQL SQL语句规范

• 语句尽量简单，不在数据库做复杂运算
• select， insert一定要带上字段名，不允许select *
• 优化LIMIT分页：不要用LIMIT start, offset
• 隐形类型转换问题

• 不允许where后面字段上做运算或者加函数，会导致用不了索引：例如 where id+1=5； where

  from_unixtime(create_time)等

• 禁用select for update、 insert……select from、 select *（必须明确给出需要字段）、 insert

  TB values……（必须明7、只用inner join或者left join；禁止用right join。表关联的on必须有索

  引，只关联需要表，只选择需要的列

• 拒绝order by rand（）语法。
• 复杂查询拆分简单查询；尽量小批量小语句分段执行；一个sql不要超过1G的binlog
• 编程语言支持的，尽量学习sql编写使用preparedStatement 技术，安全卫生干净可读性强
• 大事务可以set auto_commit =0 关闭自动提交，但是拒绝滥用，会导致阻塞
• 拒绝3大类型sql，
  • 大SQL (BIG SQL)
  • 大事务 (BIG Transaction)

  • 大批量 (BIG Batch)

    小语句小事务好处： 减少锁、用上多cpu，缓存命中率高

• 不要在索引列做运算和表达式
  • select * from table WHERE to_days(current_date) – to_days(date_col) <= 10
  • GOOD: select * from table WHERE date_col >= DATE_SUB(‘2011-10-22’,INTERVAL 10 DAY);
  • select * from ZS_User where substr(UserNickName,1,4)=’ lucy’
  • GOOD： select a.UserCity,a.UserSource from ZS_User a where a.UserNickName like ’ lucy%