数据库优化专题---11、数据库结构优化

数据库优化专题—1、表的主键用数字还是UUID

数据库优化专题—2、逻辑删除还是物理删除

数据库优化专题—3、千万记录如何快速分页

数据库优化专题—4、读多写少和读多写多

数据库优化专题—5、删改数据如何避免锁表

数据库优化专题—6、如何避免偷换交易中的商品信息

数据库优化专题—7、SQL语句优化

数据库优化专题—8、Mysql参数优化

数据库优化专题—9、索引优化

数据库优化专题—10、存储优化

数据库优化专题—11、数据库结构优化

目录

• • 优化表结构
  • 表拆分
  • • 垂直拆分
    • 水平拆分
  • 表分区
  • 读写分离
  • 数据库集群
  • 总结

这节主要介绍下数据库结构优化，包含优化表结构、表分区等。

优化表结构

• 尽量将表字段定义为NOT NULL约束，这时由于在MySQL中含有空值的列很难 进行查询优化，NULL值会使索引以及索引的统计信息变得很复杂；
• 对于只包含特定类型的字段，可以使用enum、set 等数据类型；
• 数值型字段的比较比字符串的比较效率高得多，字段类型尽量使用小、简单的数据类型。例如IP地址可以使用int类型；
• 尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED。但对整数类型指定宽度，比如INT(11)，没有任何用，因为指定的类型标识范围已经确定；
• VARCHAR的长度只分配真正需要的空间；
• 尽量使用TIMESTAMP而非DATETIME，但TIMESTAMP只能表示1970 - 2038年，比DATETIME表示的范围小得多，而且TIMESTAMP的值因时区不同而不同；
• 单表不要有太多字段，建议在20以内；
• 合理的加入冗余字段可以提高查询速度。

表拆分

垂直拆分

垂直拆分按照字段进行拆分，其实就是把组成一行的多个列分开放到不同的表中，这些表具有不同的结构，拆分后的表具有更少的列。例如用户表中的一些字段可能经常访问，可以把这些字段放进一张表里。另外一些不经常使用的信息就可以放进另外一张表里。

插入的时候使用事务，也可以保证两表的数据一致。缺点也很明显，由于拆分出来的两张表存在一对一的关系，需要使用冗余字段，而且需要join操作。但是我们可以在使用的时候可以分别取两次，这样的来说既可以避免join操作，又可以提高效率。

水平拆分

水平拆分按照行进行拆分，常见的就是分库分表。以用户表为例，可以取用户ID，然后对ID取10的余数，将用户均匀的分配进这 0-9这10个表中。查找的时候也按照这种规则，又快又方便。

有些表业务关联比较强，那么可以使用按时间划分的。例如每天的数据量很大，需要每天新建一张表。这种业务类型就是需要高速插入，但是对于查询的效率不太关心。表越大，插入数据所需要索引维护的时间也就越长。

表分区

分区适用于例如日志记录，查询少。一般用于后台的数据报表分析。对于这些数据汇总需求，需要很多日志表去做数据聚合，我们能够容忍1s到2s的延迟，只要数据准确能够满足需求就可以。

MySQL主要支持4种模式的分区：range分区、list预定义列表分区，hash 分区，key键值分区。

录入使用key键值分区：

CREATE TABLE `test2` (
  `id` int(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(100) DEFAULT NULL COMMENT '名称',
  `state` int(1) DEFAULT NULL COMMENT '状态',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY KEY (id)
PARTITIONS 10;
           

读写分离

大型网站会有大量的并发访问，如果还是传统的数据存储方案，只是靠一台服务器处理，如此多的数据库连接、读写操作，数据库必然会崩溃，数据丢失的话，后果更是不堪设想。这时候，我们需要考虑如何降低单台服务器的使用压力，提升整个数据库服务的承载能力。

我们发现一般情况对数据库而言都是“读多写少”，也就说对数据库读取数据的压力比较大，这样分析可以采用数据库集群的方案。其中一个是主库，负责写入数据，我们称为写库；其它都是从库，负责读取数据，我们称为读库。这样可以缓解一台服务器的访问压力。

MySql自带主从复制功能，我们可以使用主从复制的主库作为写库，从库和主库进行数据同步，那么可以使用多个从库作为读库，已完成读写分离的效果。

数据库集群

如果访问量非常大，虽然使用读写分离能够缓解压力，但是一旦写操作一台服务器都不能承受了，这个时候我们就需要考虑使用多台服务器实现写操作。

例如可以使用MyCat搭建MySql集群，对ID求3的余数，这样可以把数据分别存放到3台不同的服务器上，由MyCat负责维护集群节点的使用。

总结

数据结构可以优先从表结构优化，数据量大、并发量大可以考虑读写分离、数据库集群优化。