Data Model and Performance Metrics

1. ER Diagram and Database Design

• ER Diagram (Figure 1.a):Represents entities and relationships in the BG system.
  • Member Entity:
    • Represents users with a registered profile, including a unique ID and a set of adjustable-length string attributes to create records of varying sizes.
    • Each user can have up to two images:
      • Thumbnail Image: Small (in KBs), used for displaying in friend lists.
      • High-Resolution Image: Larger (hundreds of KBs or MBs), displayed when visiting a user profile.
      • Using thumbnails significantly reduces system load compared to larger images.
  • Friend Relationship:
    • Captures relationships or friend requests between users. An attribute differentiates between invitations and confirmed friendships.
  • Resource Entity:
    • Represents user-owned items like images, questions, or documents. Resources must belong to a user and can be posted on their profile or another user’s profile.
  • Manipulation Relationship:
    • Manages comments and restrictions (e.g., only friends can comment on a resource).

2. BG Workload and SLA (Service-Level Agreement)

• Workload: BG supports defining workloads at the granularity of:

  • Actions: Single operations like “view profile” or “list friends.”
  • Sessions: A sequence of related actions (e.g., browsing a profile, sending a friend request).
  • Mixed Workloads: A combination of actions and sessions.

• Service-Level Agreement (SLA):

  • Goal: Ensures the system provides reliable performance under specified conditions.
  • Example SLA Requirements: SLA, e.g., 95% of requests to observe a response time equal to or faster than 100 msec with at most 0.1% of requests observing unpredictable data for 10 minutes.

• Metrics:

  • SoAR (Social Action Rating): Measures the highest number of actions per second that meet the SLA.
  • Socialites: Measures the maximum number of concurrent threads that meet the SLA, reflecting the system’s multithreading capabilities.

3. Performance Evaluation Example

• SQL-X System Performance:SQL-X is a relational database with strict ACID compliance.
  • Initially, throughput increases with more threads.
  • Beyond a certain threshold (e.g., 4 threads), request queuing causes response times to increase, reducing SLA compliance.
  • With 32 threads, 99.94% of requests exceed the 100-millisecond SLA limit, indicating significant performance degradation.

4. Concurrency and Optimization in BG

• Concurrency Management:

  • BG prevents two threads from emulating the same user simultaneously to realistically simulate user behavior.

• Unpredictable Data Handling:

  • Definition: Data that is stale, inconsistent, or invalid due to system limitations or race conditions.
  • Validation:
    • BG uses offline validation to analyze read and write logs.
    • It determines acceptable value ranges for data and flags any reads that fall outside these ranges as unpredictable.

If SoAR is zero, the data store fails to meet SLA requirements, even with a single-threaded BGClient issuing requests.

Actions

Performance Analysis of View Profile

Performance of VP is influenced by whether profile images are included and their sizes.

Experiment Setup:

• Profile data tested with:
• • No images.
  • 2 KB thumbnails combined with profile images of 2 KB, 12 KB, and 500 KB sizes.
• Metrics: SoAR (Social Action Rating) measures the number of VP actions per second that meet the SLA (response time ≤ 100 ms).

Results:

1. No Images:

   • MongoDB performed the best, outperforming SQL-X and CASQL by almost 2x.

2. 12 KB Images:

   • SQL-X’s SoAR dropped significantly, from thousands of actions per second to only hundreds.

3. 500 KB Images:

   • SQL-X failed to meet the SLA (SoAR = 0) because transmitting large images caused significant delays.

   • MongoDB and CASQL also experienced a decrease in SoAR but performed better than SQL-X.

Role of CASQL:

• CASQL outperformed SQL-X due to its caching layer (memcached):

  • During a warm-up phase, 500,000 requests populate the cache with key-value pairs for member profiles.

  • Most requests are serviced by memcached instead of SQL-X, significantly improving performance with larger images (12 KB and 500 KB).

Performance Analysis of List Friends

1. SQL-X

• Process:
  • Joins the Friends table with the Members table to fetch the friend list.
  • Friendship between two members is represented as a single record in the Friends table.
• Performance:
  • When ϕ (number of friends) is 1000, SQL-X struggles due to the overhead of joining large tables and fails to meet SLA requirements.

2. CASQL

• Process:
  • Uses a memcached caching layer to store and retrieve results of the LF action.
  • Results are cached as key-value pairs.
• Performance:
  • Outperforms SQL-X when ϕ is 50 or 100 by a small margin (<10% improvement).
  • At ϕ=1000, memcached’s key-value size limit (1 MB) causes failures, as the data exceeds this limit.
  • Adjusting memcached to support larger key-value pairs (e.g., 2 MB for 1000 friends with 2 KB thumbnails) could improve performance.

3. MongoDB

• Process:
  • Retrieves the confirmedFriends array from the referenced member’s document.
  • Can fetch friends’ profile documents one by one or as a batch.
• Performance:
  • Performs no joins, but its SLA compliance is poor for larger friend counts.
  • SoAR is zero for ϕ=50,100,1000, as it fails to meet the 100 ms response time requirement.
  • For smaller friend lists (ϕ=10), MongoDB achieves a SoAR of 6 actions per second.

Mix of Read and Write Actions

• Purpose: Evaluates the performance of data stores under different ratios of read and write operations.
• Categories:
  • Read actions: Include operations like View Profile (VP), List Friends (LF), and View Friend Requests (VFR).
  • Write actions: Modify friendship relationships and invalidate cached key-value pairs (e.g., Invite Friend, Accept Friend Request).
• Mix Variations:
  • Very low writes (0.1%): Dominantly read-heavy workloads.
  • Low writes (1%): Slightly higher frequency of write actions.
  • High writes (10%): Write-intensive workloads.

Performance Analysis (Mix of Read and Write Actions)

• SoAR Comparison:
  • CASQL consistently achieves the highest SoAR for all write mixes due to its caching mechanism.
  • MongoDB outperforms SQL-X by a factor of 3 across all workloads.

Observations by Write Percentage:

1. 0.1% Writes (Read-Dominant):

   • CASQL significantly outperforms MongoDB due to efficient use of cached key-value pairs.

   • SQL-X lags due to the overhead of processing read actions directly from the RDBMS.

2. 1% Writes:

   • CASQL remains the best performer but shows sensitivity to increasing writes as it invalidates cached data, redirecting more queries to the RDBMS.

   • MongoDB maintains a consistent performance advantage over SQL-X.

3. 10% Writes (Write-Heavy):

   • CASQL slightly outperforms MongoDB, but the gap narrows due to the higher frequency of cache invalidations.

   • SQL-X continues to struggle with write-heavy workloads due to its lack of caching.

Session

Definition: A session is a sequence of actions performed by a socialite (user) in the social network.

Key Concepts:

1. Think Time: Delay between consecutive actions within a session.
2. Inter-Arrival Time: Delay between sessions initiated by different socialites.

Key Considerations

1. Dependencies:

   • Some sessions rely on specific database states (e.g., friends or pending requests).

   • For example, if m_i has no friends or pending requests, certain sessions terminate early.

2. Concurrency Handling:

   • BG uses in-memory data structures to simulate database states and prevent conflicts (e.g., multiple threads deleting the same comment).

   • Ensures integrity by managing semaphores and detecting unpredictable data.

3. Extensibility:

   • BG allows developers to define new sessions by combining different mixes of actions.

Parallelism

BG’s Scalable Benchmarking Framework

To address these limitations, BG employs a shared-nothing architecture with the following components:

1. BGCoord (Coordinator)

• Role: Oversees and coordinates the benchmarking process.
• Responsibilities:
  • Computes SoAR and Socialites ratings.
  • Assigns workloads to BGClients and monitors their progress.
  • Aggregates results (e.g., response times, throughput) for visualization.
• Process:
  • Splits the workload among N BGClients.
  • Ensures each BGClient works independently to prevent resource contention.

2. BGClient

• Role: Executes tasks assigned by BGCoord.
• Responsibilities:
  • Creates a database based on BG specifications.
  • Simulates workload actions and computes metrics like unpredictable data volume.
  • Periodically reports metrics to BGCoord for aggregation.

3. Visualization Deck

• Role: Provides a user interface for monitoring and controlling the benchmarking process.
• Features:
  • Allows users to configure parameters (e.g., SLA, workloads).
  • Visualizes the ratings (SoAR, Socialites) and progress of the benchmarking.

Scaling with BGClients

• Fragmentation:
  • The database is split into N logical fragments, each assigned to a BGClient.
  • Each fragment includes unique members, friendships, and resources, ensuring no overlap between BGClients.
• Decentralized D-Zipfian Distribution:
  • Used to balance workloads across nodes with different processing speeds.
  • Faster nodes handle larger fragments, ensuring equal workload completion times.

Unpredictable Data

Definition: Data that is stale, inconsistent, or invalid, produced due to race conditions, dirty reads, or eventual consistency.

BG’s Validation Process

Validation Implementation

1. Log Generation:
   • BG generates read log records (observed values) and write log records (new or delta values).
2. Offline Validation:
   • For each read log entry:
     • BG computes a range of valid values using overlapping write logs.
     • If the observed value is outside this range, it is flagged as unpredictable.

Impact of Time-to-Live (TTL) on Unpredictable Data

Results:

1. Higher TTL Increases Stale Data:

   • A higher TTL (e.g., 120 seconds) results in more stale key-value pairs, increasing the percentage of unpredictable data.

   • For T=100T = 100T=100, unpredictable data is:

     • ~79.8% with TTL = 30 seconds.
     • ~98.15% with TTL = 120 seconds.

2. Performance Trade-off:

   • A higher TTL improves performance (fewer cache invalidations) but increases stale data.

   • Lower TTL reduces stale data but impacts cache performance.

Heuristic Search for Rating

Why Use Heuristic Search?

• Exhaustive search starting from T=1 to the maximum T is time-consuming.
• MongoDB with T=1000 and Δ=10 minutes would take 7 days for exhaustive testing.

Steps in Heuristic Search:

1. Doubling Strategy:

   • Start with T=1, double T after each successful experiment.

   • Stop when SLA fails, narrowing down T to an interval.

2. Binary Search:

   • Identify the T corresponding to max throughput within the interval.

   • Used for both SoAR (peak throughput) and Socialites (maximum concurrent threads).

Questions

What system metrics does BG quantify?

SoAR (Social Action Rating):

• The highest throughput (actions per second) that satisfies a given SLA, ensuring at least α% of requests meet the response time β, with at most τ% of requests observing unpredictable data.

Socialites Rating:

• The maximum number of simultaneous threads (or users) that a data store can support while still meeting the SLA requirements.

Throughput:

• Total number of completed actions per unit of time.

Response Time:

• Average or percentile-based latency for each action.

Unpredictable Data:

• The percentage of actions that observe stale, inconsistent, or invalid data during execution.

How does BG scale to generate a large number of requests?

BG employs a shared-nothing architecture with the following mechanisms to scale effectively:

1. Partitioning Members and Resources:

   • BGCoord partitions the database into logical fragments, each containing a unique subset of members, their resources, and relationships.

   • These fragments are assigned to individual BGClients.

2. Multiple BGClients:

   • Each BGClient operates independently, generating workloads for its assigned logical fragment.

   • By running multiple BGClients in parallel across different nodes, BG can scale horizontally to handle millions of requests.

3. D-Zipfian Distribution:

   • To ensure realistic and scalable workloads, BG uses a decentralized Zipfian distribution (D-Zipfian) that dynamically assigns requests to BGClients based on node performance.

   • Faster nodes receive a larger share of the logical fragments, ensuring even workload distribution.

4. Concurrency Control:

   • BG prevents simultaneous threads from issuing actions for the same user, maintaining the integrity of modeled user interactions and avoiding resource contention.

If two modeled users, A and B, are already friends, does BG generate a friend request from A to B?

No, BG does not generate a friend request from A to B if they are already friends.

Before generating a friend request, BG validates whether the relationship between A and B is pending or already confirmed. For example, in the InviteFrdSession, BG only selects users who have no existing “friend” or “pending” relationship with the requester to receive a new friend request.

Reference: https://www.cidrdb.org/cidr2013/Papers/CIDR13_Paper93.pdf

错误日志（Error Log） 用于记录在服务 启动、运行及停止 过程中遇到的各种错误信息。该日志功能默认启用，且默认保存在 Linux 系统的 /var/log/mysqld.log（或 /var/log/mysql/error.log）路径下，也可能在 data 目录中以主机名为 .err 后缀的文件方式存在。若想查看或修改该路径，可通过 MySQL 命令：

1

SHOW VARIABLES LIKE '%log_error%';

该命令会显示当前 log_error 变量的值，即错误日志所在路径。如果该变量为空，通常默认日志会被输出到 stderr，或由系统服务（如 systemd）转至系统日志（如 /var/log/messages 或由 journalctl 管理）。

二进制日志（Binary Log） 会记录所有成功执行的 DDL（结构定义语句）和 DML（数据操作语句），但不会包含纯查询类语句（如 SELECT、SHOW）。它以 Event 形式记录，并且保存每条语句执行的时间戳。默认情况下启用二进制日志，并常用于数据备份以及主从复制场景。

二进制日志系统包括两类文件：

• 索引文件（.index）：跟踪所有二进制日志文件的路径；
• 日志文件（形如 .00000*）：具体记录 DDL 与 DML 的事件。

日志的记录格式有三种：

• STATEMENT：以文本形式记录每条 SQL，并不包含事务上下文、权限或系统状态；
• ROW：以二进制形式记录行数据的变化（旧值和新值）；
• MIXED（MySQL 8.0 以上默认）：通常使用 STATEMENT，在涉及非确定性函数（如 NOW()、UUID() 等）时自动切换到 ROW 格式。

针对日志清理，常用命令如下：

• RESET BINARY LOGS;（或旧版本的 RESET MASTER;）将删除所有二进制日志，并将索引重置，只保留一个新的空日志文件（编号从 .000001 开始）；
• PURGE BINARY LOGS TO 'binlog.xxx'; 删除指定日志文件之前的所有日志；
• PURGE BINARY LOGS BEFORE 'YYYY‑MM‑DD hh:mm:ss'; 删除该时间之前生成的所有日志。

此外，可以设置系统变量（如 binlog_expire_logs_seconds 或旧版的 expire_logs_days）实现自动清理过期日志，默认保留期为 30 天。

更多关于 binlog 的信息，可参考：https://www.cnblogs.com/rickiyang/p/13841811.html

二进制日志（binlog） 功能可以通过在 my.cnf 或启动参数中设置来进行全面配置，下面是一些常用选项与管理方法：

启用与路径配置：log‑bin=/home/mysql/binlog/

该参数不仅开启 binlog 功能，还指定日志文件的存放目录。如果不指定路径，MySQL 默认将日志保存在数据目录，并以主机名或 binlog 为基础名生成一系列 .00000* 日志文件。

文件大小与保留周期：

• max_binlog_size=104857600 将单个日志文件最大限制为 100 MB。
• expire_logs_days=7 设置日志自动保留时限为 7 天，超过该时长的日志文件会被系统定期清理。

过滤数据库：

可以通过以下参数控制哪些库的更新被记录到 binlog：

• binlog-do-db=db_name：仅记录指定数据库的变更。
• binlog-ignore-db=db_name：忽略指定数据库的更新，不写入日志。

同步策略：

sync_binlog=0 表示不强制让 MySQL 将日志立即同步到磁盘，交由操作系统定期执行。该配置提高性能，但在主机崩溃时可能丢失最后一部分日志；若设为 1，则在每次提交时都 fsync，一致性高但性能略受影响。

查看当前设置：

可以执行以下命令确认配置状态：

• SHOW BINARY LOGS;：列出当前所有 binlog 文件；
• SHOW MASTER STATUS;：查看主服务器正在使用的最新日志名称与位置；
• SHOW VARIABLES LIKE '%log_bin%';：确认二进制日志是否已启用及其路径；
• SHOW VARIABLES LIKE '%binlog_format%';：查看当前记录格式（STATEMENT、ROW 或 MIXED）；
• SHOW VARIABLES LIKE '%binlog_expire_logs_seconds%';：检查自动过期设置（通常是以秒为单位）。

手动恢复流程：

1. 停止 MySQL 服务；
2. 利用 mysqlbinlog 工具分析 .00000* 中的日志，确认误操作发生前的起始与结束位置；
3. 清空当前数据库以准备恢复；
4. 执行命令：mysqlbinlog --start-position=xxx --stop-position=yyy bin-log.00000x > recover.sql，其中 start-position 为误操作前最近一次安全点，stop-position 为误前最后一条正常语句的位置；
5. 将导出的 recover.sql 文件导入数据库，恢复指定段的变更。

一般查询日志记录了MySQL 服务器的所有连接信息和客户端的所有操作语句，默认是不开启的。

慢查询日志用于记录执行时间超过 long_query_time（默认 10 秒）并且扫描记录数不少于 min_examined_row_limit 的 SQL 语句，以帮助识别并优化效率低下的查询。注意，这一功能默认处于关闭状态，开启后不包含管理语句（如 ALTER、CREATE INDEX）及未使用索引的查询，除非你通过以下两个系统变量特别配置：

• log_slow_admin_statements：启用后，允许记录管理类语句；
• log_queries_not_using_indexes：启用后，记录所有不使用索引的查询（并不限于慢查询）。

日志触发条件的判断顺序如下（MySQL 8.0+）：

1. 是不是管理语句，或已启用 log_slow_admin_statements；
2. 查询执行时间 ≥ long_query_time，或启用 log_queries_not_using_indexes 并且未使用索引；
3. 扫描的行数 ≥ min_examined_row_limit；
4. 若启用了该选项，还会受 log_throttle_queries_not_using_indexes（对未用索引查询加速限流）的影响。

简而言之，慢查询日志只有在 SQL 足够慢或扫描量大时才会记录，且默认不记录管理或索引缺失的语句，除非明确启用相关变量。若需全面监控优化目标，建议根据实际情况调整这三项核心参数。

重做日志（redo log） 专门记录已提交事务所做的页面级别修改，以确保在崩溃或异常关机后能够快速恢复数据一致性。与完整行记录不同，redo log 只保存页号（Page ID）和偏移量（Offset）处的数据从旧值到新值的变化，例如在页面 12345 的偏移量 256 位置，将值从 25 修改为 30，既紧凑又高效。这些日志文件通常组成一个循环队列：当当前日志文件写满后，InnoDB 会切换到下一个文件，直到用尽后再回到第一个文件，覆盖最早的日志。

其工作流程如下：

1. 事务开始与修改缓冲：当事务对表中数据进行修改时，InnoDB 先将修改记录写入内存中的 redo log buffer；
2. 事务提交与刷盘：提交时，buffer 中的内容根据 innodb_flush_log_at_trx_commit 参数设置，或直接刷入磁盘（值为 1）、或先写入操作系统缓冲区后由系统决定何时落盘（值为 2）、或不强制落盘以提升性能但有丢失风险（值为 0）；
3. Checkpoint 协同：后台 Checkpoint 进程周期性地将内存中的脏数据页同步到表空间文件（*.ibd），并将对应的 redo log 日志标记为可覆写，维持循环日志的持续运作。

通过这种设计，InnoDB 能够在最短时间内重做已提交的事务修改，快速恢复数据库到故障发生前的一致状态。

假设在表空间 1 中，对页 42 的偏移 128 处的某条记录的第 3 个字段（如 age）执行更新，将其从 25 改为 30，则对应的 redo 日志项可示意如下：

1
2
3
4
5
6
7
8
9

[MLOG_REC_UPDATE_IN_PLACE]
Type:        MLOG_REC_UPDATE_IN_PLACE
Space ID:      1
Page Number:    42
Record Offset:   128
Update Field Count: 1
 └ Field Number:   3
  Field Data Length: 4
  Field Data:    0x0000001E

binlog 和 redo log 的区别如下表：

回滚日志（Undo Log） 用于在事务回滚和多版本并发控制（MVCC）中保存数据修改前的旧值。当事务开始时，InnoDB 会初始化事务上下文；在执行更新、删除等修改操作时，它首先将当前行的原始数据写入 undo log，同时将对应的物理改动记录到内存中的 redo log buffer；如果事务提交，则会将 redo 日志刷新到磁盘，并将该事务所产生的 undo 日志标记为无效，待后台清理；若事务选择回滚，则 InnoDB 会读取 undo log 中的旧值，将数据恢复到修改前的状态，并在回滚完成后清理相关的 redo 日志。这样既保证了未提交事务的可回滚性，也为 MVCC 提供了版本快照支持。

在 insert / update / delete 之前或之后，触发并执行触发器中定义的 SQL 语句集合。

确保数据完整性，日志记录，数据校验等操作。

使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容。

行级触发器：触发次数等于影响的行数。

语句级触发器：不论影响的行数，只触发一次。

MySQL触发器只支持行级触发，不支持语句级触发。

创建

1
2
3
4
5
6

create trigger trigger_name
before / after insert / update / delete
on table_name for each row
begin
	SQL 语句;
end;

查看：show triggers;

删除：drop trigger [schema_name]trigger_name;

存储过程是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输。

特点：

1. 封装，复用。
2. 可接收参数，也可返回数据。
3. 减少网络交互，效率提升。

创建：

1
2
3
4

create procedure 存储过程名称(参数列表)
begin
  SQL语句;
end;

调用：call 名称(参数列表);

查看：

1
2

show create procedure 存储过程名称;
select * from information_schema.routines where routine_schema = 'xxx';

删除：drop procedure [if exists] 存储过程名称;

在 MySQL 命令行客户端中执行创建存储过程的 SQL 时，需要通过关键字 delimiter 指定 SQL 语句的结束符。

1
2
3
4
5

delimiter $$
create procedure 存储过程名称(参数列表)
begin
  SQL语句;
end $$

变量

系统变量

由MySQL 服务器提供，属于服务器层面。

• 全局变量（global）
• 会话变量（session）

查看系统变量：

1
2
3

show [session | global] variables;
show [session | global] variables like '...';
select @@[session. | global.] 系统变量名;

设置系统变量：

1
2

set [session | global] 系统变量名 = 值;
set @@[session | global] 系统变量名 = 值;

如果没有指定 session/global，默认是 session，也就是会话变量。

MySQL 服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在 /etc/my.cnf 中配置。

自定义变量

无需提前声明，格式为 @变量名，作用域为当前连接。

赋值：

1
2
3
4

set @var_name=expr [, @var_name=expr] ...;
set @var_name:=expr [, @var_name:=expr] ...;
select @var_name:=expr [, @var_name:=expr] ...;
select 字段名 into @var_name from 表名; -- 把从表中获取的数据赋值给变量

使用：select @var_name;

用户定义的变量无需对其进行声明或初始化，只不过获取到的值为 NULL，但是不会报错。

局部变量

在局部生效的变量，访问之前需要先通过 declare 声明，可用作存储过程的局部变量和输入参数，作为局部变量的话，范围是在 begin … end 块内。

声明：declare 变量名 变量类型 [default ...];

赋值：

1
2
3

set 变量名 = 值;
set 变量名 := 值;
select 字段名 into 变量名 from 表名 ...;

IF

1
2
3
4
5
6
7

If 条件1 then
...
Elseif 条件2 then
...
Else
...
End If

参数

类型

• IN：输入参数。
• OUT：输出参数。
• INOUT：输入输出参数皆可。

用法

1
2
3
4

create procedure 存储过程名称([IN/OUT/INOUT 参数名 参数类型])
begin
  SQL 语句
end;

case

语法 1

1
2
3
4
5

case case_value
when value1 then statement_list1
[when value2 then statement_list2] ...
[else statement_list]
end case;

语法 2

1
2
3
4
5

case
when search_condition1 then statement_list1
[when search_condition2 then statement_list2] ...
[else statement_list]
end case;

while

1
2
3

while 条件 do
  SQL 语句
end while;

repeat

1
2
3
4

repeat
  SQL 语句
until 条件
end repeat;

loop

1
2
3

[label]: loop
	SQL 语句
end loop [label];

leave label; 退出指定标记的循环体（类似于 break）
iterate label; 直接进入下一次循环（类似于 continue）

游标

用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环处理。

• 声明游标：declare 游标名称 cursor for 查询语句;
• 打开游标：open 游标名称;
• 获取游标记录：fetch 游标名称 into 变量 [, 变量];
• 关闭游标：close 游标名称;

条件处理程序

用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。

1

declare handler_action handler for condition_value [, condition_value]... statement;

• handler action:
  • continue：继续执行当前程序。
  • exit：终止执行当前程序。
• condition_value
  • SQLSTATE sqlstate_value：状态码，如 02000。
  • SQLWARNING：所有以 01 开头的 SQLSTATE 代码的简写。
  • NOTFOUND：所有以 02 开头的 SQLSTATE 代码的简写。
  • SQLEXCEPTION：所有没有被 SQLWARNING 和 NOTFOUND 捕获的 SQLSTATE 代码的简写。

例子：

1

declare exit handler for SQLSTATE '02000' close u_cursor;

该 SQL 声明了一个条件处理程序，当满足SQL 状态码为 02000 时，先关闭游标，然后执行退出操作。

存储函数是有返回值的存储过程，参数只能是 IN 类型的。

1
2
3
4
5
6

create function 存储函数名称([参数列表])
returns type [characteristic ...]
begin
  SQL语句;
  return ...;
end;

characteristic 说明：

• deterministic：返回值类型和传入值类型相同。
• NO SQL：不包含 SQL 语句。
• READS SQL DATA： 只包含读取数据的 SQL 语句，不包含修改数据的 SQL 语句。

在 MySQL 数据库中，视图（View）是一个命名的虚表，它由一个 SQL 查询来定义，可以当做表使用。与持久表（permanent table）不同的是，视图中的数据没有实际的物理存储。也就是说视图中的数据并不在数据库中实际存在，行和列的数据来自定义视图的查询中使用的表，在使用视图时动态生成的。

视图只保存了查询的 SQL 逻辑，不保存查询结果。

视图的作用

视图在数据库中发挥着重要的作用。视图的主要用途之一是被用做一个抽象装置，特别是对于一些应用程序，程序本身不需要关心基表（base table）的结构，只需要按照视图定义来取数据或更新数据；因此，视图同时在一定程度上起到一个安全层的作用。

MySQL 数据库 DBA 的一个常用的命令是 SHOW TABLES，该命令会显示出当前数据库下所有的表。但因为视图是虚表，同样被作为表显示出来。若用户只想查看当前架构下的基表，可以通过 information_schema 架构下的 TABLES 表来查询，并搜索表类型为 BASE TABLE 的表。

要想查看视图的一些元数据（meta data），可以访问 information_schema 架构下的 VIEWS 表，该表给出了视图的详细信息，包括视图定义者（definer）、定义内容、是否是可更新视图、字符集等。

创建：create [or replace] view 视图名称 as select 语句 [with cascaded | local check option];

查询：

• 查看创建视图的语句：show create view 视图名称;
• 查看视图数据：select * from 视图名称...;

修改：

1
2
3
4

create [or replace] view 视图名称 as select 语句 [with cascaded | local check option];
alter view 视图名称 as select 语句 [with cascaded | local check option];
# 实际上数据行是被插入了基表当中
insert into 视图名称 values(col1, col2, ...); 

删除：drop view [if exist] 视图名称;

视图的检查选项

当时用 with check option 子句创建视图时，MySQL 会通过视图检查正在更改的每一行，以使其符合视图的定义。MySQL 允许基于一个视图创建另一个视图，还会依赖视图中的规则以保持一致性。MySQL 提供了两个选项确定检查范围：cascaded 和 local，默认值为 cascaded。

视图的更新

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。

如果视图包含以下任何一项，该视图不可更新：

• 聚合函数或窗口函数：sum()，min()，max()，count()；
• distinct；
• group by；
• having；
• union 或 union all。

视图可以简化用户对数据的理解，以及简化操作。

通过视图，用户只能查询和修改他们所能见到的数据。

视图帮助用户屏蔽真实表结构变化带来的影响。

物化视图

Oracle 数据库支持物化视图——该视图不是基于基表的虚表，而是根据基表实际存在的实表，即物化视图的数据存储在非易失的存储设备上。物化视图可用于预先计算并保存多表的连接（JOIN）或聚集（GROUP BY）等耗时较多的 SQL 操作结果。这样，在执行复杂查询时，就可以避免进行这些时耗的操作，从而快速得到结果。物化视图的好处是对于一些复杂的统计类查询能直接给出结果。在 Microsoft SQL Server 数据库中，称这种视图为索引视图。

在 Oracle 数据库中，物化视图的创建方式包括以下两种：

• BUILD IMMEDIATE
• BUILD DEFERRED

BUILD IMMEDIATE 是默认的创建方式，在创建物化视图的时候就生成数据，而 BUILD DEFERRED 则在创建物化视图时不生成数据，以后根据需要再生成数据。

查询重写是指当对物化视图的基表进行查询时，数据库会自动判断能否通过查询物化视图来直接得到最终的结果，如果可以，则避免了聚集或连接等这类较为复杂的 SQL 操作，直接从已经计算好的物化视图中得到所需的数据。

物化视图的刷新是指当基表发生了 DML 操作后，物化视图何时采用哪种方式和基表进行同步。刷新模式有两种：

• ON DEMAND
• ON COMMIT

ON DEMAND 意味着物化视图在用户需要的时候进行刷新，ON COMMIT 意味着物化视图在对基表的 DML 操作提交的同时进行刷新。

而刷新的方法有四种：

• FAST
• COMPLETE
• FORCE
• NEVER

FAST 刷新采用增量刷新，只刷新自上次刷新以来进行的修改。COMPLETE 刷新是对整个物化视图进行完全的刷新。如果选择 FORCE 方式，则数据库在刷新时会去判断是否可以进行快速刷新，如果可以，则采用 FAST 方式，否则采用 COMPLETE 的方式。NEVER 是指物化视图不进行任何刷新。

MySQL 数据库本身并不支持物化视图，换句话说，MySQL 数据库中的视图总是虚拟的。但是用户可以通过一些机制来实现物化视图的功能。例如要创建一个 ON DEMAND 的物化视图还是比较简单的，用户只需定时把数据导入到另一张表。