读SQL学习指南（第3版）笔记10_元数据与大数据

陈侠

1. 元数据

1.1. metadata

1.2. 关于数据的数据

1.3. 数据字典

1.3.1. ⒅与外键关联的数据表/列

1.3.2. ⒄外键列

1.3.3. ⒃外键名

1.3.4. ⒂存储索引的信息

1.3.5. ⒁索引列的排序（升序或降序）

1.3.6. ⒀已索引的列

1.3.7. ⑿索引类型（B树、位图）

1.3.8. ⑾索引名

1.3.9. ⑩主键索引名

1.3.10. ⑨主键名

1.3.11. ⑧主键列

1.3.12. ⑦not null列约束

1.3.13. ⑥列名

1.3.14. ⑤列数据类型

1.3.15. ④列的默认值

1.3.16. ③存储引擎

1.3.17. ②存储数据表的信息（数据表空间、初始大小等）

1.3.18. ①数据表名

1.3.19. 系统编目

1.3.19.1. system catalog

2. information_schema数据库

2.1. 所有可用对象都是视图

2.2. information_schema的特殊模式，在每个数据库中自动提供

2.3. 为了遵循ANSI SQL:2003标准，MySQL 和SQL Server都提供这个接口

2.4. information_schema数据库中的视图是可以被查询的

2.5. information_schema.tables

2.5.1. mysql

1. -＞ SELECT table_name, table_type
2.     -＞ FROM information_schema.tables
3.     -＞ WHERE table_schema = 'sakila'
4.     -＞ ORDER BY 1;

复制代码

2.5.2. 包含了数据表和视图

2.6. information_schema.views

2.6.1. mysql

1. -＞ SELECT table_name, is_updatable
2.     -＞ FROM information_schema.views
3.     -＞ WHERE table_schema = 'sakila'
4.     -＞ ORDER BY 1;

复制代码

2.7. information_schema.columns

2.7.1. mysql

1. -＞ SELECT column_name, data_type,
2.     -＞   character_maximum_length char_max_len,
3.     -＞   numeric_precision num_prcsn, numeric_scale num_scale
4.     -＞ FROM information_schema.columns
5.     -＞ WHERE table_schema = 'sakila' AND table_name = 'film'
6.     -＞ ORDER BY ordinal_position;

复制代码

2.8. information_schema.statistics

2.8.1. mysql

1. -＞ SELECT index_name, non_unique, seq_in_index, column_name
2.     -＞ FROM information_schema.statistics
3.     -＞ WHERE table_schema = 'sakila' AND table_name = 'rental'
4.     -＞ ORDER BY 1, 3;

复制代码

2.8.2. 检索关于数据表的索引信息

2.9. information_schema.table_constraints

2.9.1. mysql

1. -＞ SELECT constraint_name, table_name, constraint_type
2.     -＞ FROM information_schema.table_constraints
3.     -＞ WHERE table_schema = 'sakila'
4.     -＞ ORDER BY 3,1;

复制代码

2.9.2. 检索已创建的不同类型的约束（外键、主键、唯一）

3. 使用元数据

3.1. 模式生成脚本

3.1.1. mysql

1. -＞ SELECT 'CREATE TABLE category (' create_table_statement
2.     -＞ UNION ALL
3.     -＞ SELECT cols.txt
4.     -＞ FROM
5.     -＞  (SELECT concat(' ',column_name, ' ', column_type,
6.     -＞    CASE
7.     -＞      WHEN is_nullable = 'NO' THEN ' not null'
8.     -＞      ELSE ''
9.     -＞    END,
10.     -＞    CASE
11.     -＞      WHEN extra IS NOT NULL AND extra LIKE 'DEFAULT_GENERATED%'
12.     -＞        THEN concat(' DEFAULT ',column_default,substr(extra,18))
13.     -＞      WHEN extra IS NOT NULL THEN concat(' ', extra)
14.     -＞      ELSE ''
15.     -＞    END,
16.     -＞    ',') txt
17.     -＞   FROM information_schema.columns
18.     -＞   WHERE table_schema = 'sakila' AND table_name = 'category'
19.     -＞   ORDER BY ordinal_position
20.     -＞  ) cols
21.     -＞ UNION ALL
22.     -＞ SELECT concat(' constraint primary key (')
23.     -＞ FROM information_schema.table_constraints
24.     -＞ WHERE table_schema = 'sakila' AND table_name = 'category'
25.     -＞   AND constraint_type = 'PRIMARY KEY'
26.     -＞ UNION ALL
27.     -＞ SELECT cols.txt
28.     -＞ FROM
29.     -＞  (SELECT concat(CASE WHEN ordinal_position ＞ 1 THEN ' ,'
30.     -＞     ELSE ' ' END, column_name) txt
31.     -＞   FROM information_schema.key_column_usage
32.     -＞   WHERE table_schema = 'sakila' AND table_name = 'category'
33.     -＞     AND constraint_name = 'PRIMARY'
34.     -＞   ORDER BY ordinal_position
35.     -＞  ) cols
36.     -＞ UNION ALL
37.     -＞ SELECT ' )'
38.     -＞ UNION ALL
39.     -＞ SELECT ')';

复制代码

3.1.2. mysql

1. -＞ CREATE TABLE category2 (
2.     -＞   category_id tinyint(3) unsigned not null auto_increment,
3.     -＞   name varchar(25) not null ,
4.     -＞   last_update timestamp not null DEFAULT CURRENT_TIMESTAMP
5.     -＞     on update CURRENT_TIMESTAMP,
6.     -＞   constraint primary key (
7.     -＞     category_id
8.     -＞   )
9.     -＞ );

复制代码

3.2. 验证部署

3.2.1. 查询返回Sakila模式中每个数据表的列数、索引数以及主键约束（0或1）数

3.2.2. mysql

1. -＞ SELECT tbl.table_name,
2.     -＞ (SELECT count(*) FROM information_schema.columns clm
3.     -＞  WHERE clm.table_schema = tbl.table_schema
4.     -＞    AND clm.table_name = tbl.table_name) num_columns,
5.     -＞ (SELECT count(*) FROM information_schema.statistics sta
6.     -＞  WHERE sta.table_schema = tbl.table_schema
7.     -＞    AND sta.table_name = tbl.table_name) num_indexes,
8.     -＞ (SELECT count(*) FROM information_schema.table_constraints tc
9.     -＞  WHERE tc.table_schema = tbl.table_schema
10.     -＞    AND tc.table_name = tbl.table_name
11.     -＞    AND tc.constraint_type = 'PRIMARY KEY') num_primary_keys
12.     -＞ FROM information_schema.tables tbl
13.     -＞ WHERE tbl.table_schema = 'sakila' AND tbl.table_type = 'BASE TABLE'
14.     -＞ ORDER BY 1;

复制代码

3.3. 动态SQL生成

3.3.1. mysql

1. -＞ SELECT concat('SELECT ',
2.     -＞   concat_ws(',', cols.col1, cols.col2, cols.col3, cols.col4,
3.     -＞     cols.col5, cols.col6, cols.col7, cols.col8, cols.col9),
4.     -＞   ' FROM customer WHERE customer_id = ?')
5.     -＞ INTO @qry
6.     -＞ FROM
7.     -＞  (SELECT
8.     -＞     max(CASE WHEN ordinal_position = 1 THEN column_name
9.     -＞       ELSE NULL END) col1,
10.     -＞     max(CASE WHEN ordinal_position = 2 THEN column_name
11.     -＞       ELSE NULL END) col2,
12.     -＞     max(CASE WHEN ordinal_position = 3 THEN column_name
13.     -＞       ELSE NULL END) col3,
14.     -＞     max(CASE WHEN ordinal_position = 4 THEN column_name
15.     -＞       ELSE NULL END) col4,
16.     -＞     max(CASE WHEN ordinal_position = 5 THEN column_name
17.     -＞       ELSE NULL END) col5,
18.     -＞     max(CASE WHEN ordinal_position = 6 THEN column_name
19.     -＞       ELSE NULL END) col6,
20.     -＞     max(CASE WHEN ordinal_position = 7 THEN column_name
21.     -＞       ELSE NULL END) col7,
22.     -＞     max(CASE WHEN ordinal_position = 8 THEN column_name
23.     -＞       ELSE NULL END) col8,
24.     -＞     max(CASE WHEN ordinal_position = 9 THEN column_name
25.     -＞       ELSE NULL END) col9
26.     -＞  FROM information_schema.columns
27.     -＞  WHERE table_schema = 'sakila' AND table_name = 'customer'
28.     -＞  GROUP BY table_name
29.     -＞ ) cols;
30. Query OK, 1 row affected (0.00 sec)
31. mysql＞ SELECT @qry;
32. +--------------------------------------------------------------------+
33. | @qry                                                               |
34. +--------------------------------------------------------------------+
35. | SELECT customer_id,store_id,first_name,last_name,email,
36.     address_id,active,create_date,last_update
37.   FROM customer WHERE customer_id = ? |
38. +--------------------------------------------------------------------+
39. 1 row in set (0.00 sec)
40. mysql＞ PREPARE dynsql3 FROM @qry;
41. Query OK, 0 rows affected (0.00 sec)
42. Statement prepared
43. mysql＞ SET @custid = 45;
44. Query OK, 0 rows affected (0.00 sec)
45. mysql＞ EXECUTE dynsql3 USING @custid;

复制代码

3.3.2. 使用包含循环结构的过程化语言（比如Java、PL/SQL、Transact-SQL或MySQL的存储过程语言）生成查询的做法更佳

4. 大型数据库

4.1. 现代磁盘阵列可以存储超过4PB的数据

4.2. 云端存储量基本上是无限的

4.2.1. 将服务托管在云端所带来的最大好处之一是即时可扩展性，允许快速提高或降低运行服务所需的计算能力

4.3. 通过分区、集群、分片等策略，企业可以通过将数据分散在多个存储层和服务器上来继续使用关系型数据库

4.4. 在视图中，用户与作为接口而不是实际数据表的模式对象进行交互

5. 分区

5.1. 数据表分区最初是在20世纪90年代后期由Oracle引入的

5.2. 当数据表增长至超过数百万行时，会变得愈加困难和/或耗时的任务

5.2.1. 需要全表扫描的查询

5.2.2. 索引创建/重建

5.2.3. 数据归档/删除

5.2.4. 生成数据表/统计索引信息

5.2.5. 数据表重定位（例如，移动到不同的表空间）

5.2.6. 数据库备份

5.3. 数据表被分区后，会创建出两个或更多的数据表分区

5.4. 每个分区的定义完全相同，但具有不重叠的数据子集

5.5. 一旦数据表被分区，数据表本身就变成了一个虚拟的概念

5.6. 分区保存数据，任何索引都建立在分区数据之上

5.7. 优势

5.7.1. 只需要与一个分区交互，而不再与整个数据表交互

5.7.2. 分区修剪（partition pruning）

5.7.2.1. 服务器会检查数据表的元数据，以确定实际需要包含哪些分区

5.7.3. 分区连接（partitionwise join）

5.7.3.1. 只有那些包含查询所需数据的分区才会被包含在内

5.7.4. 能够快速删除不再需要的数据

5.7.4.1. 财务数据可能需要在线保存7年，如果数据表已根据事务日期进行分区，就可以撤销数据保存时长超过7年的分区

5.7.5. 能够同时更新多个分区，这可以大大减少数据表中每行所需的处理时间

5.8. 管理特性

5.8.1. 分区可以存储在不同的表空间中，表空间可以位于不同的物理存储层

5.8.2. 可以使用不同的压缩方案来压缩分区

5.8.3. 可以撤销某些分区的本地索引

5.8.4. 可以在某些分区上冻结数据表的统计信息，同时在其他分区上定期刷新

5.8.5. 单个分区可以固定在内存中或存储在数据库的闪存存储层中

5.9. 数据表分区

5.9.1. 水平分区

5.9.1.1. 将整行分配给一个分区

5.9.2. 垂直分区

5.9.2.1. 将列集分配给不同的分区，但必须手动完成

5.10. 索引分区

5.10.1. 全局索引

5.10.1.1. 如果经过分区的数据表有索引，可以选择特定的索引保持原样

5.10.1.2. 跨越数据表的所有分区，对于没有指定分区键值的查询很有用

5.10.2. 局部索引

5.10.2.1. 划分成几个部分，使每个分区有自己的索引

5.11. 分区方法

5.11.1. 范围分区

5.11.1.1. range partitioning

5.11.1.2. 第一种被实现的分区方法

5.11.1.3. 使用最广泛的方法

5.11.1.4. 允许一个maxvalue分区捕获任何没有映射到其他分区的行

5.11.1.5. mysql

1. -＞ CREATE TABLE sales
2.     -＞  (sale_id INT NOT NULL,
3.     -＞   cust_id INT NOT NULL,
4.     -＞   store_id INT NOT NULL,
5.     -＞   sale_date DATE NOT NULL,
6.     -＞   amount DECIMAL(9,2)
7.     -＞  )
8.     -＞ PARTITION BY RANGE (yearweek(sale_date))
9.     -＞  (PARTITION s1 VALUES LESS THAN (202002),
10.     -＞   PARTITION s2 VALUES LESS THAN (202003),
11.     -＞   PARTITION s3 VALUES LESS THAN (202004),
12.     -＞   PARTITION s4 VALUES LESS THAN (202005),
13.     -＞   PARTITION s5 VALUES LESS THAN (202006),
14.     -＞   PARTITION s999 VALUES LESS THAN (MAXVALUE)
15.     -＞  );

复制代码

5.11.1.6. sql

1. ALTER TABLE sales REORGANIZE PARTITION s999 INTO
2. (PARTITION s6 VALUES LESS THAN (202007),
3.   PARTITION s7 VALUES LESS THAN (202008),
4.   PARTITION s999 VALUES LESS THAN (MAXVALUE)
5. );

复制代码

5.11.1.7. 使用information_schema数据库中的partitions数据表

5.11.1.8. mysql

1. -＞ SELECT partition_name, partition_method, partition_expression
2.     -＞ FROM information_schema.partitions
3.     -＞ WHERE table_name = 'sales'
4.     -＞ ORDER BY partition_ordinal_position;

复制代码

5.11.2. 列表分区

5.11.2.1. 选择作为分区键的列包含州编码（例如CA、TX、VA等）、货币（例如USD、EUR、JPY等）或其他一些枚举值集

5.11.2.2. 它允许指定将哪些值分配给各个分区

5.11.2.3. 不提供溢出分区（spillover partition）

5.11.2.3.1. 添加另一个列值，就得修改分区定义，然后才能将包含新值的行添加到数据表中

5.11.2.4. mysql

1. -＞ CREATE TABLE sales
2.     -＞  (sale_id INT NOT NULL,
3.     -＞   cust_id INT NOT NULL,
4.     -＞   store_id INT NOT NULL,
5.     -＞   sale_date DATE NOT NULL,
6.     -＞   geo_region_cd VARCHAR(6) NOT NULL,
7.     -＞   amount DECIMAL(9,2)
8.     -＞  )
9.     -＞ PARTITION BY LIST COLUMNS (geo_region_cd)
10.     -＞  (PARTITION NORTHAMERICA VALUES IN ('US_NE','US_SE','US_MW',
11.     -＞                                     'US_NW','US_SW','CAN','MEX'),
12.     -＞   PARTITION EUROPE VALUES IN ('EUR_E','EUR_W'),
13.     -＞   PARTITION ASIA VALUES IN ('CHN','JPN','IND')
14.     -＞ );

复制代码

5.11.2.5. mysql

1. -＞ ALTER TABLE sales REORGANIZE PARTITION ASIA INTO
2.     -＞ (PARTITION ASIA VALUES IN ('CHN','JPN','IND', 'KOR'));

复制代码

5.11.3. 哈希分区

5.11.3.1. hash partition

5.11.3.2. 尽力将行均匀地分布在一组分区中

5.11.3.3. 不同于列表分区（选择作为分区键的列应该包含少量值），哈希分区的分区键列在包含大量不同值时效果最佳

5.11.3.4. mysql

1. -＞ CREATE TABLE sales
2.     -＞  (sale_id INT NOT NULL,
3.     -＞   cust_id INT NOT NULL,
4.     -＞   store_id INT NOT NULL,
5.     -＞   sale_date DATE NOT NULL,
6.     -＞   amount DECIMAL(9,2)
7.     -＞  )
8.     -＞ PARTITION BY HASH (cust_id)
9.     -＞   PARTITIONS 4
10.     -＞    (PARTITION H1,
11.     -＞     PARTITION H2,
12.     -＞     PARTITION H3,
13.     -＞     PARTITION H4
14.     -＞    );

复制代码

5.11.4. 复合分区

5.11.4.1. composite partition

5.11.4.2. 允许对同一个数据表使用两种不同类型的分区

5.11.4.2.1. 第一种分区方法定义了分区

5.11.4.2.2. 第二种分区方法定义了子分区

5.11.4.3. mysql

1. -＞ CREATE TABLE sales
2.     -＞  (sale_id INT NOT NULL,
3.     -＞   cust_id INT NOT NULL,
4.     -＞   store_id INT NOT NULL,
5.     -＞   sale_date DATE NOT NULL,
6.     -＞   amount DECIMAL(9,2)
7.     -＞  )
8.     -＞ PARTITION BY RANGE (yearweek(sale_date))
9.     -＞ SUBPARTITION Y HASH (cust_id)
10.     -＞  (PARTITION s1 VALUES LESS THAN (202002)
11.     -＞     (SUBPARTITION s1_h1,
12.     -＞      SUBPARTITION s1_h2,
13.     -＞      SUBPARTITION s1_h3,
14.     -＞      SUBPARTITION s1_h4),
15.     -＞   PARTITION s2 VALUES LESS THAN (202003)
16.     -＞     (SUBPARTITION s2_h1,
17.     -＞      SUBPARTITION s2_h2,
18.     -＞      SUBPARTITION s2_h3,
19.     -＞      SUBPARTITION s2_h4),
20.     -＞   PARTITION s3 VALUES LESS THAN (202004)
21.     -＞     (SUBPARTITION s3_h1,
22.     -＞      SUBPARTITION s3_h2,
23.     -＞      SUBPARTITION s3_h3,
24.     -＞      SUBPARTITION s3_h4),
25.     -＞   PARTITION s4 VALUES LESS THAN (202005)
26.     -＞     (SUBPARTITION s4_h1,
27.     -＞      SUBPARTITION s4_h2,
28.     -＞      SUBPARTITION s4_h3,
29.     -＞      SUBPARTITION s4_h4),
30.     -＞   PARTITION s5 VALUES LESS THAN (202006)
31.     -＞     (SUBPARTITION s5_h1,
32.     -＞      SUBPARTITION s5_h2,
33.     -＞      SUBPARTITION s5_h3,
34.     -＞      SUBPARTITION s5_h4),
35.     -＞   PARTITION s999 VALUES LESS THAN (MAXVALUE)
36.     -＞     (SUBPARTITION s999_h1,
37.     -＞      SUBPARTITION s999_h2,
38.     -＞      SUBPARTITION s999_h3,
39.     -＞      SUBPARTITION s999_h4)
40.     -＞  );

复制代码

6. 集群

6.1. clustering

6.2. 允许将多个服务器用作单个数据库

6.3. 共享磁盘/共享缓存的配置

6.3.1. shared-disk/shared-cache configuration

6.3.2. 集群中的每个服务器都可以访问所有磁盘，缓存在一个服务器中的数据可以被集群中的其他服务器访问

6.4. Oracle Exadata平台托管可供数千个并发用户访问的超大型数据库

6.4.1. 该平台仍无法满足更大型公司的需求，这导致Google、Facebook、Amazon等公司另辟新径

7. 分片

7.1. 对单个数据表和整个数据库进行分区

7.2. 跨多个数据库对数据进行分区，类似于数据表分区，但规模更大，复杂性也高得多

7.3. 需要选择一个分片键，用于决定连接到哪个数据库

7.4. 单独的行分配给单个分片，但较小的参考表可能需要复制到所有分片上，同时还要定义相应的策略，以决定如何修改参考数据并将改动传播给所有分片

7.5. 如果单个分片变得过大（例如社交媒体公司现在拥有了20亿用户），就需要计划添加更多的分片并将数据重新分布在分片中

7.6. 当需要变更模式时，要定义相应的策略，用于在所有分片上部署变更，以便模式保持同步

7.7. 如果应用程序逻辑需要访问存储在多个分片中的数据，要定义相应的策略，以决定如何跨数据库查询以及如何实现跨数据库事务

8. 大数据

8.1. 容量（Volume）

8.1.1. 常意味着数十亿或数万亿数据点

8.2. 速度（Velocity）

8.2.1. 对数据到达速度的一种衡量

8.3. 多样性（Variety）

8.3.1. 文档数据库属于所谓的NoSQL数据库的子集，通常使用简单的“键-值”机制存储数据

8.3.2. MongoDB是用于文档存储的一种比较流行的NoSQL平台

8.4. Hadoop分布式文件系统

8.4.1. Hadoop Distributed File System，HDFS

8.4.2. HDFS允许跨很多个服务器管理文件

8.5. MapReduce

8.5.1. 该技术通过将任务分解为可以在多个服务器上并行运行的许多细小部分来处理大量结构化数据和非结构化数据

8.6. YARN

8.6.1. 用于HDFS的资源管理器和作业调度器

8.7. 允许像单一逻辑系统那样在数百甚至数千台服务器上存储和处理文件

8.8. Hive

8.8.1. 允许用户查询存储在Hadoop中数据的初期尝试之一

8.9. Spark SQL

8.9.1. 一个库，用于查询以各种格式存储在Spark中的数据

9. Presto

10. Toad Data Point

11. Apache Drill

11.1. 初现于2015年

11.2. 更像是一个抽象层，促进了跨各种数据库平台的数据分析

11.3. 特性

11.3.1. 促进跨多种数据格式的查询，包括分隔数据、JSON、Parquet和日志文件

11.3.2. 连接到关系型数据库、Hadoop、NoSQL、HBase、Kafka，以及PCAP、BlockChain等专用数据格式

11.3.3. 允许创建自定义插件，以连接到大多数其他数据存储

11.3.4. 不需要前期的模式定义

11.3.5. 支持SQL:2003标准

11.3.6. 可与Tableau、Apache Superset等流行的商业智能（business intelligence，BI）工具配合使用

11.3.7. 可以连接到任意数量的数据源进行查询，无须先设置元数据仓库

11.4. 查询文件

11.4.1. sql

1. apache drill＞ SELECT file_name, is_directory, is_file, permission
2. . . . . . . ＞ FROM information_schema.`files`
3. . . . . . . ＞ WHERE schema_name = 'dfs.data';

复制代码

11.5. 查询MySQL

11.5.1. apache drill (information_schema)＞ use mysql.sakila;

11.5.2. apache drill (mysql.sakila)＞ show tables;

11.5.3. apache drill (mysql.sakila)

1. )＞ SELECT a.address_id, a.address, ct.city
2. )＞ FROM address a
3. )＞   INNER JOIN city ct
4. )＞   ON a.city_id = ct.city_id
5. )＞ WHERE a.district = 'California';

复制代码

11.6. 查询MongoDB

11.6.1. apache drill (mongo.sakila)

1. )＞ SELECT Rating, Actors
2. )＞ FROM films
3. )＞ WHERE Rating IN ('G','PG');

复制代码

11.7. 处理多个数据源

11.7.1. 在同一个查询中组合多个来源的数据，而无须将一个来源的数据转换并加载到另一个来源

11.7.2. apache drill (mongo.sakila)

1. )＞ SELECT first_name, last_name,
2. )＞   sum(cast(cust_payments.payment_data.Amount
3. )＞         as decimal(4,2))) tot_payments
4. )＞ FROM
5. )＞  (SELECT cust_data.first_name,
6. )＞     cust_data.last_name,
7. )＞     f.Rating,
8. )＞     flatten(cust_data.rental_data.Payments)
9. )＞       payment_data
10. )＞   FROM mysql.sakila.film f
11. )＞     INNER JOIN
12. )＞    (SELECT c.`First Name` first_name,
13. )＞       c.`Last Name` last_name,
14. )＞       flatten(c.Rentals) rental_data
15. )＞     FROM mongo.sakila.customers c
16. )＞    ) cust_data
17. )＞     ON f.film_id =
18. )＞       cast(cust_data.rental_data.filmID as integer)
19. )＞   WHERE f.rating IN ('G','PG')
20. )＞  ) cust_payments
21. )＞ GROUP BY first_name, last_name
22. )＞ HAVING
23. )＞   sum(cast(cust_payments.payment_data.Amount
24. )＞         as decimal(4,2))) ＞ 80;

复制代码

来源:https://www.cnblogs.com/lying7/p/17674383.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！