MySQL性能优化(二)：优化数据库的设计

数据库设计

数据库命名：数据库名的命名一般和项目的名称保持一致，不要随意的起名字。

数据库编码: 尽量采用utf8mb4而不使用utf8。MySQL 的“utf8”实际上不是真正的UTF-8，真正的UTF-8是每个字符最多四个字节，而MySQL的“utf8”只支持每个字符最多三个字节。

表的设计

数据库表结构的设计是最基础也是最重要的，因为一旦数据库表设计完毕并投入使用，将来再进行修改就相对比较麻烦，特别数据量大时增加字段修改字段类型都比较麻烦，因此在进行数据库设计的时候一定要尽可能的考虑周到。

数据库表设计要遵守如下原则：

表名

• 表的命名一般遵守 “业务名称 _ 表名“或者是“项目名_ 表名“的格式，对于业务名称一般都是简写，不全拼，全拼表名会太长，如sys_user(系统模块对应的用户表)，对于一些公用的可以使用tbl(table简写)作为模块名，如字典表 tbl_dictionary。
• 表名不使用复数形式，表名应该仅仅表示表里面的实体内容，不应该表示实体数量。如sys_user不要命名为sys_users。
• 为什么要使用前缀？如果多个项目都使用同一个数据库的话，可以防止命名冲突，例如用户表，如果没有设置前缀，估计大家都会命名为user，其它项目要使用这个名字就冲突了，为了解决这种问题，可以在表名上增加一个前缀，前缀为项目名称，如xxx_user, yyy_user这样就解决了这种命名冲突问题。

• 在比较复杂的系统中，通过表名前缀可以大概了解到表所在的模块，相同的业务表是在一起的，这样做日常开发和看的时候会比较方便，新人了解系统数据结构的时候也有章可循。

字段名

• MySQL 在 Windows 下不区分大小写，但在 Linux 下默认是区分大小写。因此，数据库名、 表名、字段名，最好都统一为小写字母，避免节外生枝。为了便于肉眼识别表名，一般建议表名为小写字母。
• 一般所有表都要有id, id必为主键，类型为bigint unsigned,单表时自增、步长为1; 有些特殊场景下(如在高并发的情况下该字段的自增可能对效率有比价大的影响)。
• 一般情况下主键id和业务没关系的，例如订单号不是主键id，一般是订单表中的其他字段，一般订单号order_code为字符类型。
• 一般情况下每张表都有着四个字段create_id,create_time,update_id,update_time, 其中create_id表示创建者id，create_time表示创建时间，update_id表示更新者id，update_time表示更是时间，这四个字段的作用是为了能够追踪数据的来源和修改。
• 最好不要使用备用字段(个人观点), 禁用保留字，如 desc、range、match、delayed 等。
• 表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型是 unsigned tinyint (1 表示是，0 表示否), 任何字段如果为非负数，必须是unsigned。表达逻辑删除的字段名 is_deleted，1 表示删除，0 表示未删除。
• 如果某个值能通过其他字段能计算出来就不需要用个字段来存储，减少存储的数据。
• 为了提高查询效率，可以适当的数据冗余，注意是适当。
• 强烈建议不使用外键, 数据的完整性靠程序来保证。
• 单条记录大小禁止超过8k， 一方面字段不要太多，有的都能上百，甚至几百个，另一方面字段的内容不易过大像文章内容等这种超长内容的需要单独存到另一张表中。

字段的数据类型

不同的数据类型搜索的方式不同，所以说要选择合适的数据类型。用尽量少的存储空间来存数一个字段的数据, 缩小存储空间换取查询时间,能用int的就不用char或者varchar,能用tinyint的就不用int,使用UNSIGNED存储非负数值，其中无符号值可以避免误存负数，且扩大了表示范围。合适的字符存储长度，不但节约数据库表的存储空间、节约索引存储，更重要的是提升检索速度。尽量使用数字型字段，提高数据比对效率。

①字符类型

• char是固定长度的字符类型，它的处理速度比varchar快，缺点是浪费存储空间，当实际存储的值小于指定的长度时会以空格来填充，对于长度变化不大并且对查询速度有较高的要求可以选择char。适合存储用户密码的MD5哈希值，手机号，性别，因为它的长度总是一样的。对于经常改变的值，char也好于varchar,因为固定长度的行不容易产生碎片，对于很短的列，char的效率也高于varchar。char(1)字符串对于单字节字符集只会占用一个字节，但是varchar(1)则会占用2个字节，因为1个字节用来存储长度信息 。如果存储的字符串长度几乎相等，使用char定长字符串类型。
• varchar是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长度大于此值，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索引效率。varchar的长度是字符长度，而不是字节长度。varchar还会使用额外的存储空间来记录可变字符串的长度
  • 列的最大长度小于255则只需要额外占用一个字节来记录字符串的长度
  • 列的最大长度大于255则需要额外占用两个字节来记录字符串的长度
• 不同存储引擎对char和varchar的使用原则不同，myisam:建议使用国定长度的数据列代替可变长度。innodb：建议使用varchar，大部分表都是使用innodb，所以varchar的使用频率更高

②数值类型

• 选用合适的长度非常重要，能用tinyint就不用integer

• 金额类型的字段尽量使用long用分表示，尽量不要使用bigdecimal，严谨使用float和double因为计算时会丢失经度
• 如果需要使用小数严谨使用float，double，使用定点数decimal，decimal实际上是以字符串的形式存储的，所以更加精确，java中与之对应的数据类型为BigDecimal

• 如果值为非负数，一定要使用unsigned，无符号不仅能防止负数非法数据的保存，而且还能增大存储的范围
• 不建议使用ENUM、SET类型，使用TINYINT来代替

是否为NULL

MySQL字段属性应该尽量设置为NOT NULL，除非你有一个很特别的原因去使用 NULL 值，你应该总是让你的字段保持 NOT NULL，对于没有值的指定一个默认值，如之前使用null的varchar可以默认为空字符串“”，之前是数字类型的可以用0做为默认值 。

• 在MySQL中NULL其实是占用空间的，“可空列需要更多的存储空间”：需要一个额外字节作为判断是否为NULL的标志位“需要mysql内部进行特殊处理”， 而空值""是不占用空间的。
• 含有空值的列很难进行查询优化，而且对表索引时不会存储NULL值的，所以如果索引的字段可以为NULL，索引的效率会下降很多。因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替null。
• 联表查询的时候，例如SELECT user.username, info.introduction FROM tbl_user user LEFT JOIN tbl_userinfo info ON user.id = info.user_id; 如果tbl_userinfo.introduction设置的可以为null, 假如这条sql查询出了对应的记录，但是username有值，introduction没有值，那么就不是很清楚这个introduction是没有关联到对应的记录，还是关联上了而这个值为null，null意思表示不明确，有歧义

存储引擎

常用的存储引擎的选择有MYISAM、InnoDB、MEMORY，不同的存储引擎支持的功能不一样，MySQL5.5之后默认的是InnoDB。绝大部分场景都是使用InnoDB引擎。

• MYISAM 不支持事务, 不支持外键，其优势是访问速度快，对事务完整性没有要求或者以select、insert为主的应用程序可以选择这个引擎，支持全文索引，表锁，注意：MYISAM 在删除数据时好像类似于逻辑删除，需要定时物理删除，清理碎片：optimize table 名称；
• InnoDB 支持事务，不支持全文索引，标锁，支持外键
• MEMORY：查询速度极快，数据在内存中不持久化，数据库重启数据就消失，类似于缓存的作用memcache

表引擎取决于实际应用场景；日志及报表类这种只涉及到插入和查询并且查询操作更多的建议用myisam；对事务要求高的使用innodb引擎。

建议：不要混合使用存储引擎，实际场景中会有MyISAM和InnoDB混合使用的情况，但是这样有问题，比如一个事务同时操作了myisam引擎的表和innodb引擎的表，而myisam是不支持事务的，就会造成myisam表没有回滚。现在开发中绝大部分都是使用InnoDB，也不经常见到myisam，至少我工作中没见到过。