mysql调优（二）schema与数据类型优化（二）

mysql调优（二）schema与数据类型优化（二）

本文主要介绍下MySQL数据库的合理使用范式和反范式，主键的选择，字符集的选择，存储引擎的选择，适当的冗余数据几个方面来聊一聊MySQL的优化

合理使用范式和反范式

范式

优点

1. 范式化更新通常比反范式快
2. 当数据较好的范式话后，很少或者没有重复的数据
3. 范式化数据比较少，可以放到内存中，操作较快

缺点

1. 通常要关联查询

反范式

优点

1. 所有数据放在一张表中，可以避免关联查询
2. 可以有效的索引

缺点

1. 表中冗余数据较多，删除数据会造成数据丢失

注意 ：在企业中很少能做到严格意义上的范式与反范式，一般需要混合使用，目标是以空间换时间

主键的选择

代理主键 ：与业务无关，无意义的数字序列
自然主键 ：事物属性中的自然唯一标识（如身份证号）
推荐使用代理主键

1. 它不与业务耦合，因此容易维护
2. 一个大多数表，最好全部表通用的键值策略能够减少需要编写的源码数量，减少系统的总体拥有成本。

字符集的选择

1. 纯拉丁字符能表示的内容，没必要选择latin1之外的其他字符编码，因为这个会大量节省空间
2. 如果我们能确定不需要存放多种语言，就没必要非的选择UTF-8或者其他UNLCODE字符类型，这会造成大量的存储空间的浪费
3. MySQL数据库字符集可以精确到字段，所以当我们需要大型数据库中存放多字节数据时，可以通过对不同表不同字段使用不同的字符集来较大程度上减少数据的存储量，今儿降低IO操作次数，并提高缓存命中率。

存储引擎的选择

可以通过修改配置文件来修改存储引擎
打开my.in defalt-staoge-engine = INNODB
建表时也可以指定存储引擎

create table if not exists `data_quality_configuration`
(
   `id`                   int not null auto_increment COMMENT '配置信息ID',
   `key`                  varchar(255) COMMENT 'key',
   `value`                text COMMENT 'value',
   `description`          text COMMENT '描述',
   `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
   `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  primary key (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

存储引擎的对比

对比	MyISAM	INNODB
索引类型	非聚簇索引	聚簇索引
支持事物	否	是
支持表锁	是	是
支持行锁	否	是
支持外键	否	是
支持全文索引	否	是（5.6之后支持）
适合操作类型	大量select	大量insert/update/delete

适当的数据冗余

1. 被频繁引用且只能通过join两张表或者更多大表的方式才能得到独立的小字段
2. 这样的场景由于每次join仅仅是为了取得某个小字段的值，join到的记录又大，会造成大量不必要的IO，完全可以通过用空间换时间的方式优化，不过冗余的同时需要确保数据一致性，不会遭到破坏，确保更新的同时冗余字段也被更新

适当拆分（垂直&水平）

当我们表存在类似于text或者很大的varchar类型的大字段时，如果我们大部分防伪这张表都不需要这个字段，我们就义无反顾的将其拆分到另外的独立表中，以减少复用数据占用的存储空间，这样能减少IO提高命中率