一、库设计

1、尽可能设计窄表，不用过度预留。

2、存储时间戳可以用，int unsigned存储即可。

3、show tables status 查看表的使用情况：avg_row_length超过100个字节，data_free 大于0表示表中存在碎片。

4、表越小越好，需要让热表数据量尽可能小；

5、请求足够高效，事务尽快提交/回滚；

6、监控MYSQL的线程状态或者监控Innodb的事务状态，查看运行比较久的sql或未提交的事务 （>5s需要发出报警）

7、schema原则：尽量小的原则、禁止使用外键、自增int/bigint作为主键（char或者uuid会导致存储时产生碎片，影响存储效率）、字符集和库表的设计要一致

8、数据量建议：单表数据量不要超过5000万、单表物理大小不要超过20G、索引数量不要超过5个

9、实例：总的大小不要超过5000G，总表数量不要超过5000个

二、表设计

1、每个表建议不要超过50个字段

2、优先选择utf8mb4字符集（兼容了表情包等内容）

3、严禁在数据库中明文存储用户的一些核心数据

4、用好int数据类型，加上unsigned（无符号）扩大存储范围，金额用户，扩大N倍，int是一种高效的数据类型

5、遇到blob、text字段尽量拆除去，在用主键做关联（造成碎片或行溢出）

6、字符类型尽可能用varchar的数据类型（减少加长更新）

7、日期时间数据建议使用datetime类型（datetime：0000-9999年；timestamp：1970-2038年）

三、表sql

1、多表join，join的列数据类型要一致，避免隐式转换导致索引失效

2、多表join把过滤结果小的放在前面

3、在查询的where条件中使用函数或者表达式要使用8.0及以上版本，否则会出现索引失效。

4、不要所有where后面的条件都添加索引

5、尽量不要执行select * 操作

6、尽量不要执行like “%XX%”

7、尽量不要用！=，会走全表扫描

8、优先使用union all，代替union（union all 不需要去重）

四、总结

一、核心原则：

1、定义宽表

执行show table status：

1.1、当avg_row_length的值超过100个字节时（定义为宽表），需要看看怎么优化；

1.2、当Data_free的值大于0，表明有碎片；

2、表越小越好

2.1、表越小IO压力越小

2.2、窄表数据量大性能也好（比如5亿数据）

2.3、宽表数据量小有可能性能也不好（比如50万）

2.4、实验测算，只有一个int类型，单表可存9.7亿，b+tree的根节点到叶子io也只有三次；

3、请求要足够高效

3.1、事务要尽快提交或回滚；（修改、删除数据会产生行锁，或者产生表锁，将导致其他sql或者事务被阻塞）

3.2、监控：

3.2.1、可以监控MySQL的线程状态，监控InnoDB的事务状态，一般是大于5秒

3.2.2、设置修改、锁定行数的阈值，比如大于10行

可以避免潜在长时间锁或者事务SQL运行的风险

3.2.3、检查或者监控SQL注入的风险（例如：SLEEP函数，UNION ALL函数）

二、重要的细节

1、schema设计原则

1.1、尽量小的原则

1.2、禁止使用外键「增加行锁」（高并发不建议）

1.3、自增INT/BIGINT主键（InnoDB引擎表）「备注：CHAR或者UUID主键，数据存储的瞬息离散随机，会造成数据页在存储时有大量的碎片，造成磁盘浪费，性能下降」

1.4、字符集和库表的设计要一致（mysql实例-database-数据库表-字段-存储过程-event）否者报错。eg：utf8：3个字节，utf8mb4：4个字节，join类型转换无法走索引。

1.5、MySQL尽可能高效的建议

表的维度：

   1.5.1、单表数据量尽量不超过5000万；

   1.5.2、单表的物理大小不要超过20个G

   1.5.3、索引的数量不要超过5个

 实例维度：

   1.5.4、总的大小不要超过500G

   1.5.5、总表数量不超过5000个（包括分区表）

2、库表字段设计规范

 2.1、每个表建议不超过50个字段

 2.2、优先选择utf8mb4字符集（支持移动终端的emoji符号，表情包）

 2.3、严禁在数据库中明文存储用户的一些核心数据（最好要单向加密）

 2.4、用好INT数据类型（最好加上UNSIGNED，可扩大使用范围）「金额用途，扩大N倍，用bigint或者int来存储」

 2.5、遇到BLOB、TEXT字段，尽量要拆出去，再用主键做关联

 2.6、字符类型尽可能采用varchar的数据类型（灵活、高效）「char占用空间固定」，最好不要变长更新（会照成磁盘碎片）

 2.7、日期时间数据建议采用datetime（0000-9999年）类型（比timestamp「1970-2038年」多一个字节），5.6以后多个datetime数据类型自动更新为当前时间，5.6之前只有一个可以可以更新为当前时间

3、SQL开发建议

 3.1、多表join时，join列的数据类型要一致（长度、类型，字符集）

 3.2、多表join时，把过滤后结果集较小的表作为驱动表（统一采用inner join让优化器自动优化，如果优化器优化错误，可以采用straight_join强制执行顺序 ）

 3.3、【建议】在查询的where条件中用上函数或表达式要8.0版本（8.0支持函数索引、表达式索引、支持不可见索引、支持倒叙索引）

 3.4、不要看到where条件中出现的列就直接创建索引

 3.5、尽可能不要去执行select*操作（会导致io代价高）

 3.6、不要执行like ’%x%‘，可以用like ’x%'前缀索引

 3.7、尽量不要用 ‘!=’ 条件（扫描的数据量超过20%~30%范围时，会把执行计划变成全表扫描，不管有无索引）「但不是所有的都会如此，具体问题具体分析，比如反例：图3、图4」

 3.8、如果能确定返回结果数量的话，最好加上limit n「优化器通常会再进一步优化，取到以后会立即换回，不再继续扫描」

 3.9、优先使用union all，代替union（减少临时表的生成「5.6之前也会生成临时表」）

 3.10、所有SQL都要通过SQL审核系统检查符合标准后才能上线 。