MySQL表设计的那些事

表设计

---

表结构

• 引擎、字符集

• 1NF：字段不可再分

• 2NF：一个表必须有主键，即每行数据都能被唯一的区分

• 3NF：表中的列都和主键直接相关，拆分间接关系

• 反范式：通过增加冗余或重复的数据来提高数据库的读性能，减少关联查询时间，join表的次数

• 需求>性能>表结构

表字段

• 单表1G体积500W⾏

• 单⾏不超过200Byte

• 单表不超过50个INT字段

• 单表不超过20个CHAR(10)字段

• 建议单表字段数控制在20个以内

主/外键

• 要有主键

• 唯一、非空、有序

• 主键不建议有业务含义（任何有业务含义的列都有改变的可能性，主键一旦带上了业务含义，那么主键就有可能发生变更；带有业务含义的主键，不一定是顺序自增的）

• 不使用varchar类型作主键，如UUID，可读性差、无序、性能差。多主，ID生成中间件，主从，自增无符号Bigint(20)（innodb 中的主键是聚簇索引。如果主键是自增的，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。如果不是自增主键，那么可能会在中间插入，就会引发页的分裂，产生很多表碎片，插入时间更长，而且占用空间更大）

• 主外键数据类型一致，防止隐式类型转换不走索引（需要多表 join的字段，数据类型保持绝对一致）

• 不在数据表中追加外键约束，在应用层维持外键关系

选择合适的类型

• 尽量选择小，简单的数据类型。 如boolean类型使用tinyint，邮政编码用char(6)，用整型来存IP

• 字段长度不要比实际业务过大，字段长度越大，索引在B-Tree树上遍历会越耗费时间，查询的时间会越久，VARCHAR的长度只分配真正需要的空间

• 尽量避免Null而是not null default ‘’，索引性能不好，查询会出现一些不可预料的结果，如count(col)

• 尽量使用timestamp（4字节带时区）而非datetime（8字节时间绝对值）

• 尽量避免使用text或blob数据类型，非有不可，建议垂直拆分，查询效率太慢

• 整型定义中不添加长度， 比如使用int， 而不不是int(4)。（在添加了zerofill描述符后的显示宽度，即预存储宽度，所以在不添加zerofill描述符的时候，int(1) 和int(10) int(100) 都没什么区别）

• 存储精度数值使用decimal，float和double是以二进制存储的，有一定的误差，容易失精度

• 不存储任何资源文件，比如照片/视频/网站等，可以用文件路径/外链来代替，这样可以通过路径，链接等来进行索引

• 频繁修改的字段（一般是指状态类字段）最好用独立的数字或者单个字母去表示，不用使用汉字或者英文

优化设计

• 做好静态表和动态表的分离

• 2张表的多对多的表关系，最好设计成3张表，即增加一张中间表，之前的两张表和中间表的关系是一对多的关系

• 计算列允许冗余，提高查询统计的速度

添加逻辑删除&创建时间&修改时间

• 物理删除一旦删除，即不可恢复，逻辑删除数据可用数据分析

• 为每个表中设计create_time和update_time，保存系统当前时间

添加注释

• 表注释，字段注释

索引

---

不加索引

• 慢查询

• 服务超时

• 数据库服务器CPU 100%

• 一条SQL语句可以干爆一台DB

区分度

• 字段去重后的总数与全表总记录数的商 select count(distinct col))/count(*) from table;

• 区分度越大，索引效果越明显

• 在区分度较小的字段上新建索引，基本无效，还会增加大量的索引文件

添加合适的索引

• 在经常需要搜索的列上加索引，可以加快搜索的速度

• 在创建时间上加索引，加快范围搜索的速度

• 在外键字段上加索引，加快多表JOIN的关联查询的速度

• 在SELECT、UPDATE、DELETE语句的WHERE条件中的列上加索引，加快条件的判断速度

• 在包含ORDER BY、GROUP BY、DISTINCT中的字段上加索引，利用索引的排序，加快排序查询时间

• 值分布很稀少的字段不适合建索引，例如"性别"这种只有两三个值的字段

• 对较长的字符数据类型的字段，可以考虑创建前缀索引，加快索引速度：ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);

对于联合索引，

• 区分度较高的字段放联合索引的最左侧

• 字段长度小的列放在联合索引的最左侧

• 使用频繁的列放在联合索引的最左侧

• 联合索引的顺序根据最左前缀原则区分

索引越多越好？

• 要根据查询有针对性的创建，考虑在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描

• 索引可以增加查询效率，同样会降低插入和更新的效率

• 建议单表5个索引

• 考虑联合索引，删除不必要的单列索引，避免冗余索引和重复索引

• 扩展优先，不要新建索引，尽量在已有索引中修改

不走索引

• 应尽量避免在where子句中使用!=或<>操作符，否则引擎将放弃使用该字段索引而进行全表扫描

• 对于联合索引，不是使用的第一部分，则不会使用索引（即不符合最左前缀原则）

• 最左前缀匹配，向右匹配直到遇到范围查询(>、>=、<=、<、between、like)就停止匹配

• 隐式类型转换，不走索引（如列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引）

• 在索引列上进行函数或表达式运算，该列索引会失效，在建有索引的字段上尽量不要使用函数进行操作

• 查询条件中有or，即使其中有条件带索引也不会使用。要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

• like查询以%开头

SQL

---

EXPLAIN

• 在写完SQL后，一定要查看执行计划，尽量避免全表扫描

• 开启慢查询日志，设置阈值分析慢SQL并进行优化

隐式类型转换

• 在相同类型的字段间进行比较的操作，检查参数类型与数据库字段类型一致，如果参数不一致,要使用CAST等函数显示转换成一致，否则可能会将索引字段类型转化成=号右边的类型，造成隐式类型转换，不走索引

如：explain select * from t_base_user where telephone=12345678901

SQL规约

• SQL语句尽量简单，不在数据库做复杂运算，尽量不用存储过程、函数（避免将业务逻辑和DB耦合在一起，数据库存储数据为主，业务逻辑尽量通过应用层实现）

• 少用子查询和复杂查询

• 少用复杂JOIN，并注意JOIN中的驱动表是否最优

• 使用同类型进行比较，比如用"123"和"123"比，123和123比，防止隐式类型转换

• 不要使用COUNT(列名)或COUNT(常量)来替代COUNT(*)，COUNT(*)就是SQL92定义的标准统计行数的语法

• 使用ISNULL()或IS NULL来判断是否为NULL值，不要用=、<>。NULL与任何值的直接比较都为NULL

• 通常情况下，OR的查询效率要低于IN，所以对于影响性能的OR，考虑IN或UNION替代

• IN操作能避免则避免，若实在避免不了，需要仔细评估IN后边的集合元素数量，控制在1000个之内

• 对于连续数值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5

• LIKE只能使用前缀索引，因此避免%xxx式前缀模糊查询，例如LIKE"%abc%"，允许"abc%"

• 尽量避免在WHERE子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描

• 不用SELECT *，只获取需要的字段 ，减少网络带宽消耗，还有可能利用到覆盖索引

• 不使用INSERT INTO table_name VALUES()，要显式的指定插入列

• 不做列运算，如SELECT id WHERE age + 1 = 10，任何对列的操作都将导致表扫描，包括数据库函数、计算表达式等等