高性能MySQL（四）—Schema与数据类型优化（2）

最新推荐文章于 2022-07-12 20:30:49 发布

WSYW126

最新推荐文章于 2022-07-12 20:30:49 发布

阅读量840

点赞数

分类专栏： MySQL 文章标签： mysql 高性能优化

本文链接：https://blog.csdn.net/wsyw126/article/details/53486098

版权

MySQL 专栏收录该内容

11 篇文章 1 订阅

订阅专栏

Schema与数据类型优化

MySQL schema设计中的陷阱

太多列
1. MySQL的存储引擎API工作在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。
2. 从行缓冲中将编码的过的列转换为行数据结构的操作代价是非常高的，并且转换的代价依赖于列的数量。
3. MyISAM的定长行结构和服务器层的行结构正好匹配，所以不用转换。
4. MyISAM的变长行结构和InnoDB的行结构总是需要转换的。
太多的关联

“实体-属性-值”（EAV）设计模式是一个常见的糟糕设计模式，尤其是在MySQL下不能靠谱地工作。
全能的枚举类型

过度的使用枚举类型。
变相的枚举类型

枚举（ENUM）列允许在列中存储一组定义值中的单个值，集合（SET）列则允许在列中存储一组定义值中的一个或者多个值。
NUll

虽然说尽可能不适用null，但是在必须的时候，还是要用的。

范式和反范式

范式的优点和缺点

好处
1. 范式化的更新操作通常比反范式化要快。
2. 当数据较好的范式化时，就只有很少或者没有重复数据，所以只需要需改更少的数据。
3. 范式化的表通常更小，可以更好的放到内存里，所以执行操作会更快。
4. 很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句。
缺点
1. 通常需要关联。

反范式的优点和缺点

优点

可以避免关联。
缺点

如果涉及不合理的话，如没有结合业务逻辑的话，可能会产生效率低下的情况。

混用范式化和反范式化

完全的范式或者反范式化都是在实验室才有的情况。在真实情况下，我们大部分情况下是混用：复制和缓存

缓存表和汇总表

缓存表：存储可以比较简单地从schema其他表获取数据的表。

汇总表：保存的是使用group by语句聚合数据的表。

物化视图

预先计算并且存储在磁盘上的表，可以通过各种策略刷新和更新。

计数器表

解决并发的技术方案：预先在下面的表中增加100条数据
CREATE TABLE `hit_counter` (
  `slot` tinyint unsigned not null,
  `cnt` int unsigned not null,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

update hit_counter set cnt = cnt + 1 where slot = (RAND() * 100)
select sum(cnt) from hit_counter

按天统计并解决并发的方案：在这中方案中不需要预先初始化数据。
CREATE TABLE `hit_counter` (
  `day` datetime not null,
  `slot` tinyint unsigned not null,
  `cnt` int unsigned not null,
  primary key (`day`, `slot`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into hit_counter (`day`, `slot`, `cnt`) values (CURRENT_DATE, RAND()*100, 1) on duplicate key update set cnt =cnt +1

如果希望减少表的行数，以避免表变得太大，可以执行一个定时周期任务，合并所有的结果到0号槽，并删除其他的槽。

update hit_counter as c
 inner join (
    select day, sum(cnt) as cnt, MIN(slot) as mslot
     from hit_counter group by day
 ) as x USING(day)
set c.cnt = IF(c.slot = x.mslot, x.cnt, 0), 
c.slot = IF(c.slot = x.mslot, 0, x.slot);

delete from hit_counter where splot <> 0 and cnt = 0;