表结构优化
单表优化
字段
保存数据长度不确定的情况下使用VARCHAR
Varchar往往用来保存可变长度的字符串。我们只是给其固定了一个最大值,然后系统会根据实际存储的数据量来分配合适的存储空间。
char数据类型与VARCHAR数据类型不同,其采用的是固定长度的存储方式。当数据保存时,即使其没有达到最大的长度,系统也会为其分配这么多的存储空间。
需要注意的是,varchar虽然是可变长度,但也不能随意设置。虽然使用varchar可以保证根据数据实际使用分配磁盘空间。但是在被加载到内存中,内存使用的是字符类型中定义的长度。而不是实际使用长度。这样当需要大量加载这些字段的时候(排序或缓存)。就会产生较大的内存消耗。
在满足业务的情况下尽量使用tinyint、smallint、medium_int作为整数类型而非int,如果非负则加上UNSIGNED
使用tinyint、smallint、medium_int替换int主要是他们占用了更小的空间。
类型 | 占用空间描述 |
---|---|
int | 范围是-2^31 (-2,147,483,648) 到 2^31 - 1 (2,147,483,647) 的整型数据,占用4个字节 |
mediumint | 中等大小整数,有符号的范围是-8388608到8388607,占用3个字节 |
smallint | 有符号的范围是-2^15 (-32,768) 到 2^15 - 1 (32,767) 的整型数据,占用2个字节 |
tinyint | 有符号的范围是-128 - 127的整型数据,占用1个字节 |
使用枚举或整数代替字符串类型
数字类型相对字符串类型要简单的多,当使用索引的时候,使用数据类型比字符串类型效率要高。
而对于枚举,在建立这个字段时,mysql内部会建立一张hash结构的map表,来保存枚举索引和对应值。此时对此字段进行保存,存储在里面的不是这个字符,而是对应的索引,除非enum的个数超过了一定数量,否则数据所占的存储空间也总是1字节。
尽量使用timestamp而非datetime
两者主要有下面的区别
占用空间的区别
timestamp 占用4字节,而datetime占用8字节。而两者
时间范围的区别
timestamp可以描述的时间范围是’1970-01-01 00:00:01.000000’ 至 ‘2038-01-19 03:14:07.999999’
datetime可以描述的时间范围是’1000-01-01 00:00:00.000000’ to ‘9999-12-31 23:59:59.999999’
时区问题
timestamp以utc的格式储存,它会自动检索当前时区并进行转换。如果储存时的时区和检索时的时区不一样,那么拿出来的数据也不一样。
datetime不会进行时区的检索,对于datetime来说,存什么拿到的就是什么。
默认值
如果存进去的是NULL,timestamp会自动储存当前时间,而datetime会储存NULL。
单表不要有太多字段
MySql物理存储的结构,由段-区-页-行组成。每个区是1M大小,由连续的64个16k的页组成,每个页又由N行组成。每个页16k,在MySql内存加载过程中,数据加载的最小单位是页。所以每个页可以保存的行越多,加载的页就越少。实际使用时根据业务场景预估单表每个字段实际长度和和字段数量,进行字段设计。
当然根据大家网上的分享,一般维持在20个以下是比较稳妥的。
避免使用NULL字段,很难查询优化且占用额外索引空间
假如字段中存在null的内容时候,在有些业务中我们不得不使用not null的判断。而使用not null会导致索引的失效。并且,对于MySQL,null不能使用=,<,>这样的运算符,对null做算术运算的结果都是null,count时不会包括null行等。并且null比空字符串需要更多存储空间。所以一般建表的时候最好为字段设置一个默认值。
用整型来存IP
使用整型来保存IP比使用varchar节省更多的存储空间。并且MySql提供了函数,进行数据转换。inet_ntoa()函数:将整数转成IP地址;inet_aton()函数:将IP地址转成整数;可以通过函数式放在右边保证索引可用。
EXPLAIN SELECT inet_ntoa(ip)
FROM ip_test
WHERE ip = inet_aton('127.0.0.1');
+----+-------------+---------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | ip_test | NULL | ref | index | index | 4 | const | 1 | 100.00 | Using index |
+----+-------------+---------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
索引和主外键
外键
对于外键的设置,小型系统和对性能要求并不高的系统中,外键是必须的。但是外键也会明显的影响数据访问的性能。所以在一些对性能要求比较高的系统中,表结构中可以不需要外键,相关约束,由程序来保证。
查询为主
为了提供数据的访问速度,对于经常作为where中的字段,需要设置字段的索引。
写数据为主
对于频繁被修改的字段,最好不要设置索引,因为每次修改数据库都会修改其索引,降低了写数据的效率。
引擎优化
对于日常开发,我们接触的最多的存储引擎是:InnoDB和MyIsam。
- MyIsam引擎是MySQL 5.1及之前版本的默认引擎
- InnoDB在MySQL 5.5后成为默认索引
他们区别是
功能 | MyIsam | InnoDB |
---|---|---|
行锁 | 不支持行锁,读取时对需要读到的所有表加锁,写入时则对表加排它锁 | 支持行锁,采用MVCC来支持高并发 |
事务 | 不支持 | 支持 |
外键 | 不支持 | 支持 |
安全恢复 | 不支持崩溃后的安全恢复 | 支持 |
索引 | 支持延迟更新索引,极大提升写入性能 | 不支持全文索引 |
数索引 | 支持 | 支持 |
数据缓存 | 不支持 | 支持 |
其他 | 支持压缩表,减少磁盘空间占用 |
一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求。所以根据上面的对比,MyIsam适合SELECT密集型的表,而InnoDB适合INSERT和UPDATE密集型的表。
数据拆分
当数据库中数据渐渐增多的时候,除了常规的优化以外,我们还可以对数据进行分区分库分表。
分区
分区是根据规则,把一个表拆分成更小的部分,但是对于访问数据库应用而言,逻辑上只有一个表,但实际上这个表可能有多个物理分区组成。分区对应用来说是完全透明的,不影响应用的业务逻辑。分区引入了分区键,分区键用于根据某个规则让数据根据规则分布在不同分区。MySQL分区既可以对数据进行分区,也可以对索引进行分区。
注意:无论哪种分区,都限制分区键必须是唯一键/主键,当然除非你的表上没有任何主键/唯一键。
分区类型
分区类型 | 描述 |
---|---|
range分区 | 基于一个连续的区间范围进行数据分配 |
list分区 | 类似range,区别在于list分区是居于枚举出的值列表分区 |
hash分区 | 根据自定义的表达式的返回值来进行分区 |
key分区 | 根据MySql提供的hash函数来进行分区 |
新建分区
- 新建range分区
CREATE TABLE `user_info_range` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`email` varchar(20) DEFAULT NULL,
`age` tinyint(4) NOT NULL,
`type` int(11) DEFAULT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`,`age`)
)
PARTITION BY RANGE COLUMNS(age) (
PARTITION a VALUES LESS THAN (10),
PARTITION b VALUES LESS THAN (30),
PARTITION c VALUES LESS THAN (40),
PARTITION d VALUES LESS THAN (50),
PARTITION e VALUES LESS THAN MAXVALUE
);
- 新建list分区
CREATE TABLE `user_info_list` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`email` varchar(20) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`type` int(11) NOT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`,`type`)
)
PARTITION BY LIST COLUMNS(type) (
PARTITION a VALUES IN (1),
PARTITION b VALUES IN (2),
PARTITION c VALUES IN (3),
PARTITION d VALUES IN (4),
PARTITION e VALUES IN (5,6,7,8)
);
- 新建hash分区
CREATE TABLE `user_info_hash` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`email` varchar(20) DEFAULT NULL,
`age` tinyint(4) NOT NULL,
`type` int(11) DEFAULT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`,`age`)
)
PARTITION BY HASH (age) PARTITIONS 4 ;
- 新建key分区
CREATE TABLE `user_info_key` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`email` varchar(20) DEFAULT NULL,
`age` tinyint(4) NOT NULL,
`type` int(11) DEFAULT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`,`age`)
)
PARTITION BY KEY (age) PARTITIONS 4 ;
后续新增分区
alter TABLE `user_info_list` add PARTITION(
PARTITION f VALUES IN (9) ENGINE = InnoDB
);
删除分区
alter table `user_info_list` drop PARTITION f;
查看分区
select partition_name part, partition_expression expr, partition_description descr, table_rows
from information_schema.partitions
where table_name = 'user_info_list';
返回内容
+------+--------+---------+------------+
| part | expr | descr | TABLE_ROWS |
+------+--------+---------+------------+
| a | `type` | 1 | 0 |
| b | `type` | 2 | 0 |
| c | `type` | 3 | 0 |
| d | `type` | 4 | 0 |
| e | `type` | 5,6,7,8 | 0 |
+------+--------+---------+------------+
分区文件
新建完的分区可以在数据文件中看到生成相应的文件
分表
如果表中数据大到使用分区还是无法满足需要的时候,这个时候可以考虑使用分表。
一般来说是否进行分表,我们需要考虑几个方面:
- 数据的使用频率,一张表中数据使用的频率可能不同,有的字段会经常被访问和操作。
- 数据的独立性,数据是否被拆分后每一部分的数据查询的时候是否有关联。比如根据地区划分、根据日期进行划分的记录。
拆分方式
目前分表主要两种:垂直拆分和水平拆分。
垂直拆分
垂直拆分简单说就是讲一张表的字段进行拆分,把一张表的列拆分为多张表中。将大字段和访问频率低的字段进行拆分。这样减少每一行的数据,提高每次检索的行数,减少数据库查询次数。
当然垂直拆分也有它的弊端:拆分后因为要做数据关联,主键会出现冗余,需要管理冗余列,并会引起join操作。
水平拆分
水平拆分主要是对数据的拆分。我们根据数据中的一些规则将数据拆分至多张表中,这样可以避免单表存在大量数据。但是水平拆分会给应用增加复杂度,通常在查询是需要多个表名,查询所有数据需要union操作。
水平分割最重要的是找到分割的标准,不同的表应根据业务找出不同的标准,比如用户可以根据手机号进行拆分,也可以根据地址进行拆分。
分库
分库的思想就是要把一个数据库切分成多个部分放到不同的数据库(server)上,从而缓解单一数据库的性能问题。
其类似分表,把业务紧密的数据表切分放在一个数据库中,或者单表数据异常多,把表的数据按照某种规则切分到多个数据库中。
亦或者水平和垂直拆分一起使用。
读写分离
将数据库的读取和修改放置在不同的位置,也就是读写分离。使用读写分离可以显著提升数据库的并发负载能力。
一般来说都是通过主从复制的方式来同步数据,再通过读写分离来实现并发负载的提升。