MySQL使用规范_MySQL

MySQL使用规范

一、 核心规范

1. 不用数据库做运营，如md5()、order by rand()

2. 控制单表数据量

a) 单表纯int不超过1000w

b) 单表含char不超过500w

c) 单库不超过300-400个表

3. 表字段数少而精

a) 影响因素

i. IO高效

ii. 全表遍历

iii. 表修复快

iv. 提高并发

v. Alter table快

b) 单表字段上限控制在20-50个

4. 拒绝大SQL、大事务、大批量

二、 字段规范

1. 用好数值字段类型

a) TINYINT（1 Byte）

b) SMALLINT(2B)

c) MEDIUMINT(3B)

d) INT(4B)、BIGINT（8B）

e) FLOAT(4B)、DOUBLE(8B)

f) DECIMAL(M,D)

2. 尽量用数值型字段，非字符串型

a) 更高效

b) 查询更快

c) 占用空间更小

3. 优先使用enum或set

a) Enum占1字节，转为数值运算

b) Set视节点订，最多占8字节

c) 比较时需要加单引号

4. 避免使用NULL字段

a) 很难进行查询优化

b) NULL列加索引，需要额外空间

c) 含NULL符合索引无效

5. 少用或拆分TEXT/BLOB

a) Text类型处理性能远低于varchar

b) 如必须使用则拆分到单独的表

6. 不在数据库里存图片

三、 索引规范

1. 谨慎合理添加索引

a) 改善查询，减慢更新，索引不是越多越好

b) 能不加索引尽量不加，最好不超过字段数20%

2. 字符字段必须建前缀索引

a) Mysq的区分度是成平次增长的

3. 不在索引列做数学运算或函数运算

a) 无法使用索引

b) 导致全表扫描

4. 自增列或全局id做innodb主键

5. 尽量不用外键

a) 外键可节省开发量

b) 有额外开销

c) 逐行操作

d) 可到达其他表，意味着锁

e) 高并发时容易死锁

四、 SQL规范

1. 拒绝大sql，拆解成多条简单sql

a) 简单sql缓存命中率更高

b) 减少锁表时间，特别是myisam

c) 用上多cpu

2. 保持事务、db连接短小精悍

a) 使用原则：即开即用，用完即关

b) 与事务无关的操作放到事务外面，减少锁资源的占用

c) 不破坏一致性前提下，使用多个短事务代替长事务

3. 尽可能避免使用存储过程、触发器、函数

4. 尽量不用select *，只取需要的数据列

5. 改下or为in

a) Or效率：O(n)

b) In 效率：O(log n)

c) 当N很大时，or会慢很多，建议in的n小于200

6. 如果不同字段，改写or为union

7. 避免负向查询和%前缀模糊

a) Not、!=、<>等

b) 前缀模糊使用不了索引，会导致全表扫描

8. 减少count

a) Count(1)==count(*)全表扫描

b) 实时数据：用memcache，双向更新，凌晨跑基准

c) 非实时统计：尽量用单独统计表，定期重算

9. Limt高效分页

a) Limit 偏移量越大则越慢

b) 推荐分页：select * from table where id>id limit 10;

10. 用union all而非union

a) 若无需对结果进行去重，则用union all，union有去重开销

11. 分解链接保证高并发

a) 高并发db不建议进行两个表以上的join

12. Group by 去除排序

a) Group by 实现包括：分组、排序

b) 无需排序：order by null

c) 特定排序：group by desc

13. 尽量保证同数据类型的列值进行比较

a) 数值列于字符类型比较

i. 同时转换为双精度

ii. 进行比对

b) 字符列与数值类型比较

i. 字符列整列转数值

ii. 不用使用索引查询

14. Load data

a) 批量装载比单行快，不需要刷新缓存

b) 无索引比有索引装载快

c) 尽量不用insert select

15. 打散大批量更新

16. 常见检测db的命令

a) Show profile

b) Mysqlsla

c) Mysqldumpslow

d) Show slow log

e) Show processlist

f) Show query_response_time（percona）

g) Explan

17. 尽量用连接代替子查询

18. 统一字符集：utf8，校对规则：utf8_general_ci