MySQL进阶实战6,缓存表、视图、计数器表
一、缓存表和汇总表
有时提升性能最好的方法是在同一张表中保存衍生的冗余数据,有时候还需要创建一张完全独立的汇总表或缓存表。
- 缓存表用来存储那些获取很简单,但速度较慢的数据;
- 汇总表用来保存使用group by语句聚合查询的数据;
对于缓存表,如果主表使用InnoDB,用MyISAM作为缓存表的引擎将会得到更小的索引占用空间,并且可以做全文检索。
在使用缓存表和汇总表时,必须决定是实时维护数据还是定期重建。哪个更好依赖于应用程序,但是定期重建并不只是节省资源,也可以保持表不会有很多碎片,以及有完全顺序组织的索引。
当重建汇总表和缓存表时,通常需要保证数据在操作时依然可用,这就需要通过使用影子表来实现,影子表指的是一张在真实表背后创建的表,当完成了建表操作后,可以通过一个原子的重命名操作切换影子表和原表。
为了提升读的速度,经常建一些额外索引,增加冗余列,甚至是创建缓存表和汇总表,这些方法会增加写的负担妈也需要额外的维护任务,但在设计高性能数据库时,这些都是常见的技巧,虽然写操作变慢了,但更显著地提高了读的性能。
二、视图与物化视图
1、视图
视图可以理解为一张表或多张表的与计算,它可以将所需要查询的结果封装成一张虚拟表,基于它创建时指定的查询语句返回的结果集。
查询者并不知道使用了哪些表、哪些字段,只是将预编译好的SQL执行,返回结果集。每次查询视图都需要执行查询语句。
2、物化视图
为了防止每次都查询,先将结果集存储起来,这种有真实数据的视图,称为物化视图。
MySQL并不原生支持物化视图,可以使用Justin Swanhart
的开源工具Flexviews
实现。
相对于传统的临时表和汇总表,Flexviews
可以通过提取对源表的更改,增量地重新计算物化视图的内容。
三、加快alter table操作的速度
MySQL的alter table 操作的性能对大表来说是个大问题。MySQL执行大部分修改表结构的操作的方法使用新的结构创建一个空表,从旧表中查出所有数据插入新表,然后删除旧表。
这样操作可能需要花费很长时间,如果内存不足而表又很大,而且还有很多索引的情况下更为严重。
改善的方法有两种:
- 第一种是先在一台不提供服务的机器上执行alter table操作,然后和提供服务的主表进行切换;
- 第二种方式是通过影子拷贝,影子拷贝的技巧是用要求的表结构创建一张和源表无关的新表,然后通过重命名和删表的操作交换两张表。
四、计数器表
通常创建一张表来存储用户的点赞数、网站访问数等。
create table like_count(num int unsigned not null) engine=InnoDB;
每次点赞都会导致计数器进行更新:
update like_count set num = num + 1;
问题在于,对于任何想要更新这一行的事务来说,这条记录上都有一个全局的互斥锁mutex
。这会使这些事务都只能串行执行,要获得更高的并发更新性能,可以将计数器保存在多行中,每次随机选择一行进行更新。
create table like_count(
slot tinyint unsigned not null primary key,
num int unsigned not null
) engine=InnoDB;
预先在这张表中新增10条数据,然后选择一个随机的槽slot进行更新:
注意:为了研究之后遇到的问题,后来又插入了一条~
update like_count set num = num + 1 where slot = floor(rand() * 10);
更新了两行,这是为什么呢?
select一下,查询结果,有的时候0条,有的时候1条,有的时候2条,有的时候3条
,惊呆了,这么有趣的事情,我怎么能放过,让我们一起一探究竟。
让我们一起一探究竟:
- floor() 函数的作用:返回小于等于该值的最大整数;
- rand()函数的作用:获得0到1之间的随机值;
在ORDER BY或GROUP BY子句中使用带有RAND()值的列可能会产生意想不到的结果,因为对于这两个子句,RAND()表达式都可以对同一行计算多次,每次返回不同的结果。要从一组行中随机选择一个样本,将ORDER BY RAND()和LIMIT配合使用。
在MySQL的官方手册里,针对RAND()的提示大概意思就是,在ORDER BY从句里面不能使用RAND()函数,因为这样会导致数据列被多次扫描。
这就完了?
相关文章
- 哈佛教授用3个大数据案例告诉你:大数据,重要的不是数据!
- ROS机器人程序设计(原书第2版)2.4.9 创建节点
- 大数据全真案例带你来解密如何挑选“风水宝地”
- 大数据小说 | 如何用一小时看透一个初识的姑娘
- D-News | 洲际旗下12家酒店遭大规模数据泄露 Snap收购以色列明星AR公司
- 5月17日云栖精选夜读:大数据浪潮下,前端工程师眼中的完整数据链图
- 只有数据分析师才能看懂的十大吐槽
- ROS机器人程序设计(原书第2版)3.7 绘制标量数据图
- ROS机器人程序设计(原书第2版)3.9 3D可视化
- ROS机器人程序设计(原书第2版)3.9.1 使用rqt_rviz在3D世界中实现数据可视化
- 致敬统计学家Hans Rosling,感谢你带来的那些数据背后的故事
- C++程序设计:原理与实践(进阶篇)15.1 存储和处理数据
- 数据脱口秀 | 埃隆·马斯克和他的13个疯狂脑洞
- 数据工程师的崛起
- C++程序设计:原理与实践(进阶篇)15.2 STL理念
- C++程序设计:原理与实践(进阶篇)15.3 序列和迭代器
- 数据科学家和工程师:各霸一方,一旦联手,所向披靡
- 2016年研究数据可视化最不应该错过的10篇文章
- 自定义JDBC数据库连接池小例子
- JDBC连接MySQL数据库小例子