数据表 设计、规范

 

命名规范

所有数据库对象名（库名、表名、字段名），必须使用小写字母并用下划线分割，避免使用数据库关键字、预留字，即使要使用也应该用反引号包裹。常见的数据库关键字、预留字如下

DATA FILE TEXT DESC STATUS TYPE KEY KEYS VALUE VALUES LEVEL

 

字段类型选择

1、少用TEXT，避免使用BLOB。
mysql内存临时表不支持text、blob这样的大字段类型，如果查询中包含大字段，排序等操作就不能使用内存临时表，会强制生成硬盘临时表进行操作。mysql是以页为单位存储数据的，含有大字段时容易出现跨页存储、加载数据，导致频繁进行磁盘IO，严重影响数据库性能。不建议直接存储在数据库中，占用空间，使用起来也低效。

文件之类的二进制数据，尽量使用专门的文件服务器进行存储，数据表中保存文件地址。如果非要在数据表中存储大字段，也尽量把大字段拆分到单独的扩展表中。

2、避免使用ENUM枚举类型
相比于tinyint之类的普通类型，enum需要额外处理，操作效率低。

3、避免使用 NULL 字段，尽量指定默认值。

• null字段做比较、计算需要特殊处理，null字段的复合索引无效，难以对null字段进行查询优化；
• null字段加索引后，每条记录都需要1个字节的额外存储空间；
• 在代码层面需要进行大量判空，容易造成NPE。

 

字段使用规范

1、没有特殊需求的情况下，数据表尽量都使用 Innodb 存储引擎，支持事务、行级锁，更好的恢复性，高并发下性能更好。

2、尽量控制单表字段数、数据量。

• 字段数越多，把表装载进内存缓冲池时所占用的内存也就越大，会消耗更多的IO，建议单表字段数上限不超过20~50个，可以拆分冷热字段、大字段作纵向分表，避免加载大量冷门字段数据、大体积字段数据，尽量让一行数据在一页中，减少IO次数，避免翻页加载。
• 建议控制单表数据量的大小在500万以内，过大会造成修改表结构，备份，恢复都会有很大的问题。可以归档历史数据（应用于日志数据）、分库分表（应用于业务数据）等手段来控制数据量大小，可以按时间（年月日）、地区、主键范围等做横向分表。

3、少用 text 类型，避免使用 blob 类型，如果大字段类型很多，尽量拆分到单独的数据表中。大字段类型，会短时间内造成数据量快速增长，数据库进行数据库读取时，通常会进行大量的随机IO操作，文件很大时，IO操作很耗时 通常存储于文件服务器，数据库只存储文件地址信息。

4、金额财务等需要精确存储的数值字段，可以作为整型存储的就使用整型，比如金额可以采用最小单位分来存储，存储为整型，涉及到小数存储的，float、double表示浮点数有误差，一律使用decimal，以保证精度。

eg. decimal(20,2)   总长度20位，小数部分2位

 

5、手机号、密码、身份证号、银行卡号等敏感数据加密存储

6、避免在数据库直接使用外键，添加外键后关联表的写操作需要额外处理，会拉低性能，外键尽量在业务代码中自行维护。

7、insert 语句都应该指定要操作的字段列表。

8、设计表时，如果记录会发生变化，则应该考虑添加 create_time、update_time 字段；如果涉及状态变更，则应该考虑添加 status 字段；如果需要后续需要用到变更前的数据，则考虑在数据变更时通过流水表之类的方式记录快照。

 

字段约束

1、InnoDB根据主键建立聚簇索引（数据的物理存储顺序和索引顺序相同），InnoDB引擎的表推荐使用有序递增的数值作为主键列, 避免字符串做主键，便于InnoDB引擎对主键列建立聚簇索引。

2、避免使用外键、级联更新，有业务代码自行维护关联关系、关联更新。
使用外键有额外开销，数据变化时需要加锁处理关联的其它表的记录，影响数据库的插入效率，高并发时容易造成死锁；级联更新是强阻塞，存在数据库更新风暴的问题。

 

索引使用原则

1、谨慎合理添加索引
添加索引是为了改善查询，索引不是越多越好，索引可以提高效率也可能降低效率，添加索引会减慢插入、更新，甚至有些情况下会降低查询效率。mysql优化器会对每一个可能用到的索引进行评估，生成出一个较好的执行计划，如果索引比较多，会增加评估、生成执行计划的时间，会降低查询性能。综合评估数据密度和数据分布，最好不超过字段数20%，最好不要超过5、6个，结合核心SQL优先考虑覆盖索引，值重复率高的字段不适合建索引。

1、对索引进行函数转换、计算时会导致无法使用索引，尽量不对索引字段做函数、表达式计算。
不管是不是索引字段，都尽量不要在数据库做运算，尽量在业务代码层计算。

2、避免使用%前缀模糊查询
例如LIKE “%name”或者LIKE “%name%”，这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”。

3、避免负向查询
负向查询主要有 !=、<>、NOT EXISTS、NOT IN、NOT LIKE等，否则引擎放弃使用索引而进行全表扫描。

5、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 group by、order by、关联查询on条件涉及的列上 建立索引。

6、sql中可能用到多个索引的，设计时优先考虑联合索引（复合索引）
5.6版本之前，一个sql最多用到一个索引，5.6以后，虽然1个sql可以同时使用多个索引，但效果往往没有使用1个联合索引好。联合索引遵守最左匹配原则，使用联合索引时，尽量把区分度高、使用频率高的字段放在左侧。

示例：3列组成联合索引 idx(username, tel, email)

-- 以联合索引的第一个字段usename开始，都可以走联合索引
where username='xxx'... 
where username='xxx' and tel='xxx'
where username='xxx' and email='xxx'
where username='xxx' and tel='xxx' and email='xxx'
where username='xxx' and email='xxx' and tel='xxx' 

-- 反之则不会走联合索引
where tel='xxx'... 
where email='xxx'...

 

7、有使用覆盖索引的优先使用覆盖索引，避免回表二次查询

8、隐式转换会使索引失效，避免隐式类型转换。
比如where条件的字段类型和传入的参数类型不一致、或者多表关联字段数据类型不一致时，会发生数据类型隐式转换（对列进行了运算），会造成列上的索引失效。

9、尽量不用SELECT *
select * 会消耗更多的CPU、内存、IO以及网络带宽，尤其是表中有大字段时，尽量只取需要的字段，减少资源使用，为使用覆盖索引提供可能性。

10、尽量避免在 where 子句中对字段进行 null 值判断（is null、is not null），否则会导致引擎放弃使用索引进行全表扫描。

11、避免使用子查询，能转化为关联查询的尽量转化为关联查询
子查询的结果集通常会被存储到临时表中，无法使用索引，从子查询结果集中查询数据性能差，尤其是子查询结果集记录数较多的情况。

 

拒绝3B

• 大SQL（BIG SQL）：避免使用复杂、操作较多的sql，容易阻塞数据库，可以拆解成多条简单的SQL，简单SQL缓存命中率更高，减少锁表时间，sql语句尽可能简单。
• 大事务（BIG Transaction）：事务使用原则：即开即用，用完即关。与事务无关操作都放到事务外面，减少锁资源的占用，不破坏一致性前提下，使用多个短事务代替长事务。尽可能少用存储过程、触发器，尽量不使用复杂的sql函数，将上述这些事情交给业务代码处理。
• 大批量（BIG Batch）：主从环境中，大批量操作可能会造成严重的主从延迟，大批量的写操作执行时间长、增加数据库阻塞风险，而只有当主库上执行完成后，才会在其他从库上执行，所以会造成主库与从库长时间的延迟情况。

 

join关联查询

1、减少使用多表关联查询，尽量拆解为多个单表查询的简单sql，以保证并发性能。

2、关联查询的表数量尽量不超过3个，避免超过5个。

3、尽量用小表驱动大表

• inner join：在没有其他过滤条件的情况下，mysql会自动选择数据量少的表作为驱动表
• left join：mysql自动选择左边的表作为驱动表
• right join：mysql自动选择右边的表作为驱动表