MySQL之Schema与数据类型优化(七)

时间：2024-05-30 21:26:30

Schema与数据类型优化

物化视图

许多数据库管理系统(例如Oracle或者微软SQL Server)都提供了一个被称作物化视图地功能。物化视图实际上是预先计算并且存储在磁盘上的表，可以通过各种各样的策略刷新和更新。MySQL并不原生支持物化视图。然而Justin Swanhart的开源工具Flexviews，也可以自己实现物化视图。Flexviews比完全自己实现的解决方案更精细，并且提供了很多不错的功能使得可以更简单地创建和维护物化视图。它由下面这些部分组成:

  • 1.变更数据抓取(Change Data Capture, CDC)功能，可以读取服务器的二进制日志，并且解析相关行的变更
  • 2.一系列可以帮助创建和管理视图的定义的存储过程
  • 3.一些可以应用变更到数据库中的物化视图的工具

对比传统的维护汇总表和缓存表的方法，Flexviews通过提取对源表的更改，可以增量地重新计算物化视图地内容。这意味着不需要通过查询原始数据来更新视图。例如，如果创建了一张汇总表用于计算每个分组地行数，此后增加了一行数据到源表中，Flexviews简单地给相应地组的行数加一即可。同样的技术对其他的聚合函数也有效，例如SUM()和AVG().这实际上是有好处的，基于行的二进制日志包含行更新前后的镜像，所以Flexviews不仅仅可以获得每行的新值，还可以需要查找源表就能直到每行数据的旧版本。计算增量数据比源表中读取数据的效率要高得多。
例如写出一个SELECT语句描述想从已经存在的数据库中得到的数据。这可能包含关联和聚合(GROUP BY)。Flexviews中有一个辅助工具可以转换SQL语句到Flexviews的API调用。Flexviews会做完所有的脏活、累活:监控数据库的变更并且转换后用于更新存储物化视图的表。现在应用可以简单地查询物化视图来替代查询需要检索的表。

Flexviews有不错的SQL覆盖范围，包括一些棘手的表达式，你可能没有料到一个工具可以在MySQL服务器之外处理这些工作。这一点对创建基于复杂SQL表达式的视图很有用，可以用基于物化视图的简单、快速的查询替换原来复杂的查询

计数器表

如果应用在表中保存计数器，则在更新计数器时可能碰到并发问题。计数器表在Web应用中很常见。可以用这种表缓存一个用户的朋友数、文件下载次数等。创建一张独立的表存储计数器通常是个好主意，这样可使计数器表小且快。使用独立的表可以帮助避免查询缓存失效，并且可以使用下面展示的一些高级的技巧。应该让事情变得尽可能简单，假设有一个计数器表，只有一行数据，记录网站的点击次数:

mysql> CREATE TABLE hit_counter (
    -> cnt int unsigned not null
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

网站的每次点击都会导致对计数器进行更新:

mysql> UPDATE hit_counter SET cnt = cnt + 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

问题在于，对于任何想要更新这一行的事务来说，这条记录上都有一个全局的互斥锁(mutex)。这会使得这些事务只能串行执行。要获得更高的并发更新性能，也可以将计数器保存在多行中，每次随机选择一行进行更新。这样做需要对计数器表进行如下修改:

mysql> CREATE TABLE hit_counter_new (
    -> slot tinyint unsigned not null primary key,
    -> cnt int unsigned not null
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

然后预先在这张表增加100行数据。现在选择一个随机的槽(slot)进行更新:

mysql> UPDATE hit_counter_new SET cnt = cnt + 1 WHERE slot = RAND() * 100;

要获得统计结果，需要使用下面这样的聚合查询:

mysql> SELECT SUM(cnt) FROM hit_counter_new;

一个常见的需求是每隔一段时间开始一个新的计数器(例如，每天一个)。如果需要这么做，则可以再简单地修改一下表设计:

mysql> CREATE TABLE daily_hit_counter (
    -> day date not null,
    -> slot tinyint unsigned not null,
    -> cnt int unsigned not null,
    -> primary key(day, slot)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (21.37 sec)

在这个场景中，可以不用像前面的例子那样预先生成行，而用ON DUPLICATE KEY UPDATE代替:

mysql> INSERT INTO daily_hit_counter(day,slot,cnt)
    -> VALUES(CURRENT_DATE, RAND() * 100, 1) ON DUPLICATE KEY UPDATE cnt = cnt +1;
Query OK, 1 row affected (0.02 sec)

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

mysql> UPDATE daily_hit_counter AS c
    -> INNER JOIN (
    -> SELECT day,SUM(cnt) AS cnt, MIN(slot) AS mslot
    -> FROM daily_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, c.slot);
Query OK, 1 row affected (0.06 sec)
mysql> DELETE FROM daily_hit_counter WHERE slot <> 0 AND cnt = 0;

更快地读，更慢地写

为了提升读查询的速度，经常会需要建一些额外索引，增加冗余列，甚至是创建缓存表和汇总表。这些方法会增加写查询的负担，也需要额外的维护任务，但在设计高性能数据库时，这些都是常见的技巧:虽然写操作变得更慢了，但更显著地提高了读操作的性能

然而，写操作变慢并不是读操作变得更快所付出的唯一代价，还可能同时增加了读操作和写操作的开发难度

加快ALTER TABLE操作的速度

MySQL的ALTER TABLE操作的性能对大表来说是个大问题。MySQL执行大部分修改表结构操作的方法是用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表。这样操作可能需要花费很长时间，如果内存不足而表又很大，而且还有很多索引的情况下尤其如此。许多人都有这样的经验，ALTER TABLE操作需要花费数个小时甚至数天才能完成。

MySQL5.1以及更新版本包含一些类型的"在线"操作的支持，这些功能不需要再整个操作过程中锁表。最近版本的InnoDB也支持通过排序来建索引，这使得建索引更快，并且有一个紧凑的索引布局。
一般而言，大部分ALTER TABLE操作将导致MySQL服务中断。我们会展示一些在DDL操作时有用的技巧，但这是针对一些特殊的场景而言的。对常见的场景，能使用的技巧只有两种:一种是现在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换；另外一种是"影子拷贝"。影子拷贝的技巧是用要求的表结构和源表无关的新表，然后通过重命名和删表操作交换两张表。也有一些工具可以帮助完成影子拷贝工作:例如,Facebook数据库运维团队的"online schema change"工具、Shlomi Noach的openark toolkit以及Percona Toolkit 如果使用Flexviews，也可以通过其CDC工具执行无锁的表结构变更。
不是所有的ALTER TABLE操作都会引起表重建。例如，有两种方法可以改变或者删除一个列的默认值(一种方法很快，另外一种则很慢)。假如要修改电影的默认租赁期限，从三天改到五天，下面是很慢的方式:

mysql> ALTER TABLE film MODIFY COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5;

SHOW STATUS显示这个语句做了1000次读和1000次插入操作。换句话说，它拷贝了整张表到一张新表，甚至列的类型、大小、和可否为NULL属性都没改变。理论上，MySQL可以跳过创建新表的步骤。列的默认值实际上存在表的.frm文件中，所以可以直接修改这个文件而不需要改动表本身。然而MySQL还没有采用这种优化的方法，所有的MODIFY COLUMN操作都将导致表重建。
另外一种方法是通过ALTER COLUMN操作来该变列的默认值:

mysql> ALTER TABLE film
    -> ALTER COLUMN rental_duration SET DEFAULT 5;

这个语句会直接修改.frm文件而不涉及表数据，所以这个操作是非常快的

相关文章