MySql数据库设计方面

布满荆棘的人生 2021-11-16 15:44 287阅读 0赞

1,选择合适的存储引擎（日常我们频繁用的有两种存储引擎，分别是InnoDB和MyISAM）

一般情况下，InnoDB都是正确的选择，除非说需要用到些InnoDB不具备的特性，优先选择InNoDD。对于一个数据库来说，一般都是选择性地使用InnoDB和MyISAM。 在需要用到事务处理和崩溃恢复方面，那必定是选择InnoDB,那什么时候考虑使用MyISAM呢，在可以不考虑事务处理，对于崩溃恢复的代价可以承受的，需要更好的读操作，那么应该选择MyISAM,总做周知的是MyISAM在读的性能比InnoDB更快，如一些配置文件，就可以考虑使用MyISAM。

区别：

1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；

2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；

3. InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

4. InnoDB不保存表的具体行数，执行select count(\*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；

5. Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高；

2,变长字段使用varchar，不要使用char

3，对于二进制多媒体数据，流水队列数据(如日志)，超大文本数据不要放在数据库字段中 text

4，访问频率较低的大字段拆分出数据表。有些大字段占用空间多，访问频率较其他字段明显要少很多，这种情况进行拆分，频繁的查询中就不需要读取大字段，造成IO资源的浪费。

5，大表可以考虑水平拆分。大表影响查询效率，根据业务特性有很多拆分方式，像根据时间递增的数据，可以根据时间来分。以id划分的数据，可根据id%数据库个数的方式来拆分。

### **表设计** ###

1) 表必须定义主键，默认为ID，整型自增，如果不采用默认设计必须咨询DBA进行设计评估   
2) ID字段作为自增主键，禁止在非事务内作为上下文作为条件进行数据传递   
3) 禁止使用外键   
4) 多表中的相同列，必须保证列定义一致   
5) 国内表默认使用InnoDB，表字符集默认使用gbk，国际默认使用utf8的表   
6) 表必须包含gmt\_create和gmt\_modified字段，即表必须包含记录创建时间和修改时间的字段   
7) 单表一到两年内数据量超过500w或数据容量超过10G考虑分表，且需要提前考虑历史数据迁移或应用自行删除历史数据   
8) 单条记录大小禁止超过8k（列长度(中文)\*2（gbk）/3(utf8)+列长度(英文)\*1）   
9) 日志类数据不建议存储在MySQL上，优先考虑Hbase或OB，如需要存储请找DBA评估使用压缩表存储   
字段设计

1) 表被索引列必须定义为not null，并设置default值   
2) 禁止使用float、double类型，建议使用decimal或者int替代   
3) 禁止使用blob、text类型保留大文本、文件、图片，建议使用其他方式存储（TFS/SFS），MySQL只保存指针信息   
4) 禁止使用varchar类型作为主键语句设计   
语句设计

1)    数据更新建议使用二级索引先查询出主键，再根据主键进行数据更新
    2)    禁止使用非同类型的列进行等值查询！

### **其他** ###

1) 禁止使用：存储过程、触发器、函数、视图、事件等MySQL高级功能   
2) 禁止使用跨库查询   
3) 禁止使用子查询，建议将子查询转换成关联查询   
4) 禁止核心业务流程SQL包含：计算操作、多表关联、表遍历case when等复杂查询，建议拆分成单表简单查询   
5) varchar长度设计需要根据业务实际需要进行长度控制，禁止预留过长空间。例如status使用varchar(128)进行存储