MySQL 数据库设计规范( 三 ) _数据库设计

【建议】如果选择性超过 20%，那么全表扫描比使用索引性能更优，即没有设置索引的必要。

分库分表、分区表

【强制】分区表的分区字段（partition-key）必须有索引，或者是组合索引的首列。
【强制】单个分区表中的分区（包括子分区）个数不能超过 1024 。
【强制】上线前 RD 或者 DBA 必须指定分区表的创建、清理策略。
【强制】访问分区表的 SQL 必须包含分区键。
【建议】单个分区文件不超过 2G，总大小不超过 50G 。建议总分区数不超过 20 个。
【强制】对于分区表执行 alter table 操作，必须在业务低峰期执行。
【强制】采用分库策略的，库的数量不能超过 1024 。
【强制】采用分表策略的，表的数量不能超过 4096 。
【建议】单个分表不超过 500W 行，ibd 文件大小不超过 2G，这样才能让数据分布式变得性能更佳。
【建议】水平分表尽量用取模方式，日志、报表类数据建议采用日期进行分表。

字符集

【强制】数据库本身库、表、列所有字符集必须保持一致，为 utf8 或 utf8mb4 。
【强制】前端程序字符集或者环境变量中的字符集，与数据库、表的字符集必须一致，统一为 utf8 。

程序层 DAO 设计建议

【建议】新的代码不要用 model，推荐使用手动拼 SQL + 绑定变量传入参数的方式。因为 model 虽然可以使用面向对象的方式操作 db，但是其使用不当很容易造成生成的 SQL 非常复杂，且 model 层自己做的强制类型转换性能较差，最终导致数据库性能下降。
【建议】前端程序连接 MySQL 或者 redis，必须要有连接超时和失败重连机制，且失败重试必须有间隔时间。
【建议】前端程序报错里尽量能够提示 MySQL 或 Redis 原生态的报错信息，便于排查错误。
【建议】对于有连接池的前端程序，必须根据业务需要配置初始、最小、最大连接数，超时时间以及连接回收机制，否则会耗尽数据库连接资源，造成线上事故。
【建议】对于 log 或 history 类型的表，随时间增长容易越来越大，因此上线前 RD 或者 DBA 必须建立表数据清理或归档方案。
【建议】在应用程序设计阶段，RD 必须考虑并规避数据库中主从延迟对于业务的影响。尽量避免从库短时延迟（20 秒以内）对业务造成影响，建议强制一致性的读开启事务走主库，或更新后过一段时间再去读从库。
【建议】多个并发业务逻辑访问同一块数据（InnoDB 表）时，会在数据库端产生行锁甚至表锁导致并发下降，因此建议更新类 SQL 尽量基于主键去更新。
【建议】业务逻辑之间加锁顺序尽量保持一致，否则会导致死锁。
【建议】对于单表读写比大于 10:1 的数据行或单个列，可以将热点数据放在缓存里（如 Memcached 或 Redis），加快访问速度，降低 MySQL 压力。

一个规范的建表语句示例

一个较为规范的建表语句为：

create table user(`id`bigint(11) not null auto_increment,`user_id`bigint(11) not null comment '用户 ID',`username`varchar(45) not null comment '登录名',`email`varchar(30) not null comment '邮箱',`nickname`varchar(45) not null comment '昵称',`avatar`int(11) not null comment '头像',`birthday`date not null comment '生日',`gender`tinyint(4) default '0' comment '性别',`intro`varchar(150) default null comment '简介',`resume_url`varchar(300) not null comment '简历存放地址',`register_ip`int not null comment '用户注册时的源 IP',`review_status` tinyint not null comment '审核状态，1-通过，2-审核中，3-未通过，4-尚未提交审核',`create_time`timestamp not null comment '记录创建的时间',`update_time`timestamp not null comment '资料修改的时间',primary key (`id`),unique key `idx_user_id` (`user_id`),key `idx_username`(`username`),key `idx_create_time`(`create_time`, `review_status`))engine = InnoDBdefault charset = utf8comment = '用户基本信息';

DML 语句