读SQL学习指南（第3版）笔记08_视图和索引

2023-08-31 07:01 由躺柒发表于 #数据库

1. 精心设计的应用程序通常会在保持实现细节私有的同时公开公有接口，以便未来在不影响最终用户的情况下修改设计

2. 视图

2.1. 不同于数据表，视图并不涉及数据存储，不用担心视图会填满你的磁盘空间

2.2. 一种数据查询机制

2.3. 从用户的视角来看，视图和数据表没什么两样

3. 为什么要使用视图

3.1. 数据安全

3.1.1. 如果你创建了数据表并允许用户查询，用户就可以访问数据表中的每一行和每一列

3.1.2. 保持数据表的私有性（不授权任何用户select许可），然后创建一个或多个视图，忽略或模糊化（比如customer_vw.email列采用的'*'方法）敏感列

3.1.3. 虚拟私有数据库（virtual private database，VPD）

3.1.3.1. Oracle Database用户另一种选择可以保护数据表的行列数据安全

3.1.3.2. VPD允许用户对数据表施加策略，服务器据此对用户的查询进行修改

3.2. 数据聚合

3.2.1. 生成报表的应用程序通常需要聚合数据

3.2.2. 将数据预先在数据表中聚合而不是使用视图求和以极大地提高查询性能

3.3. 隐藏复杂性

3.3.1. 为了使最终用户免受复杂性的影响

3.4. 连接分区数据

3.4.1. 为了提升性能会将较大的数据表拆分为多个部分

3.4.2. 设计人员可以在无须强制数据库用户修改查询的情况下改动底层数据的结构

4. 可更新视图

4.1. MySQL、Oracle Database和SQL Server都允许用户在遵守特定限制的前提下通过视图修改数据

4.2. MySQL满足下列条件，视图就是可更新的

4.2.1. 没有使用聚合函数（max()、min()、avg()等）

4.2.2. 视图没有使用group by或having子句

4.2.3. select或from子句中不存在子查询，并且where子句中的任何子查询都不引用from子句中的数据表

4.2.4. 视图没有使用union、union all或distinct

4.2.5. from子句至少包括一个数据表或可更新视图

4.2.6. 如果有不止一个数据表或视图，from子句只使用内连接

5. 索引

5.1. 查找资源内特定项的一种机制

5.2. 数据库服务器也使用索引来定位数据表中的行

5.3. 与普通的数据表不同，索引是一种以特定顺序保存的专用数据表

5.4. 索引并不包含实体的所有相关数据，而是只包含那些可用于定位数据表中行的列，以及描述这些行所在的物理位置信息

5.5. 索引的作用就是使检索数据表中行和列的子集实现便捷化，无须再检查数据表中的每一行

5.6. MySQL 5.0版也提供了create index，但该命令被映射到alter table命令，仍然必须使用alter table命令创建主键索引

5.6.1. mysql-

-＞ ALTER TABLE customer
    -＞ ADD INDEX idx_email (email);

5.6.2. sql

CREATE INDEX dept_name_idx
    ON department (name);

5.7. MySQL也支持drop index命令，不过同样是被映射到alter table命令

5.7.1. mysql

-＞ ALTER TABLE customer
    -＞ DROP INDEX idx_email;

5.7.2. sql

DROP INDEX idx_email; (Oracle)
DROP INDEX idx_email ON customer; (SQL Server)

5.8. MySQL用户可以使用show命令查看特定数据表的所有索引

5.9. 所有的数据库服务器都允许查看可用的索引

5.10. 唯一索引

5.10.1. 提供普通索引所能提供的所有便利

5.10.2. 避免索引列出现重复值

5.10.3. 只要有行插入或是索引列被修改，数据库服务器就会检查唯一索引，以查看该值是否已经在数据表中的其他行存在

5.10.4. SQL Server和Oracle Database用户只需在创建索引时加入unique关键字

5.10.4.1. sql

CREATE UNIQUE INDEX idx_email
    ON customer (email);

5.10.5. mysql

-＞ ALTER TABLE customer
    -＞ ADD UNIQUE idx_email (email);

5.11. 多列索引

5.11.1. 在创建多列索引时，必须仔细考虑哪一列在前，哪一列在后，这样才能使索引尽可能地发挥作用

5.11.2. 如果需要确保充分的响应时间，完全可以基于不同顺序为列的同一集合创建多个索引

5.11.3. mysql

-＞ ALTER TABLE customer
    -＞ ADD INDEX idx_full_name (last_name, first_name);

5.12. 索引类型

5.12.1. B树索引

5.12.1.1. 平衡树索引（balanced-tree index）

5.12.1.1.1. B树索引（B-tree index）

5.12.1.2. MySQL、Oracle Database和SQL Server均默认采用B树索引

5.12.1.3. B树索引擅长处理包含大量不同值的列

5.12.2. 位图索引

5.12.2.1. 对于那些只包含少量值却占据了大量行的列（所谓的低基数数据）

5.12.2.2. 对于低基数数据而言，位图索引是一种友好且紧凑的索引解决方案

5.12.2.3. 如果列中存储的值的数量相较于行数攀升得过高（所谓的高基数数据），这种索引策略就不适合了，因为服务器需要维护太多的位图

5.12.2.4. Oracle Database引入了位图索引（bitmap index），其为存储在列中的每个值生成一个位图

5.12.2.4.1. CREATE BITMAP INDEX idx_active ON customer (active);

5.12.2.5. 通常用于数据仓库环境，其中大量数据通常在包含相对较少值的列（例如销售季度、地理区域、产品、销售人员）上进行索引

5.12.3. 文本索引

5.12.3.1. MySQL和SQL Server提供的是全文索引（full-text index）

5.12.3.2. Oracle Database提供了一套称为Oracle Text的强大工具集

5.13. 允许用户查看查询优化器是如何处理SQL语句的

5.13.1. SQL Server用户可以在执行SQL 语句之前通过发出set showplan_text on语句查看该语句的执行计划

5.13.2. Oracle Database提供了explain plan语句，通过执行该语句可以将执行计划写入专用的数据表plan_table

5.14. 索引的不足

5.14.1. 索引并不是越多越好

5.14.1.1. 每个索引其实都是一个数据表（特殊类型的表）

5.14.1.2. 索引越多，服务器就需要做越多的工作来保持所有模式对象都处于最新状态，这会使服务器的运行速度减慢

5.14.2. 索引需要磁盘空间，同时也需要管理员花费精力进行管理，因此对于索引的最佳策略就是仅当有明确需求时才添加索引

5.14.2.1. 如果出于一些特殊目的要用到索引，比如每月的例行维护工作，可以先添加索引，例行维护，然后再删除索引，下次需要例行维护时再如此重复

5.14.2.2. 数据被连夜加载到数据仓库时就会出现问题，常见做法是在数据被加载之前撤销索引，然后在数据仓库开放业务之前重新创建索引

5.15. 索引不能太多，也不能太少

5.15.1. 确保所有主键列被索引

5.15.2. 对于多列主键，可以考虑为主键列的子集或是以不同于主键约束定义的顺序为所有主键列创建额外的索引

5.15.3. 为所有被外键约束引用的列创建索引

5.15.4. 为被用于频繁检索数据的列创建索引

5.15.5. 除了短字符串（2～50个字符）列，大多数日期列也是不错的候选对象

6. 约束

6.1. 施加于数据表中一列或多列的限制

6.1.1. 如果没有约束，数据库的一致性就会存疑

6.2. 主键约束

6.2.1. 标识一列或多列，保证其值在数据表中的唯一性

6.3. 外键约束

6.3.1. 限制一列或多列只能包含其他数据表的主键列中的值

6.3.2. on delete restrict

6.3.2.1. on delete restrict，如果删除了父表（address或store）中被子表（customer）引用的行，服务器会引发错误

6.3.3. on delete cascade

6.3.4. on delete set null

6.3.5. on update restrict

6.3.6. on update cascade

6.3.6.1. on update cascade，使服务器将父表（address或store）主键值的改动传播到子表（customer）

6.3.7. on update set null

6.4. 唯一约束

6.4.1. 限制一列或多列的值，保证其在数据表中的唯一性

6.5. 检查约束

6.5.1. 限制列的可用值范围

6.6. sql

ALTER TABLE customer
ADD CONSTRAINT fk_customer_address FOREIGN KEY (address_id)
REFERENCES address (address_id) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE customer
ADD CONSTRAINT fk_customer_store FOREIGN KEY (store_id)
REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE;

6.7. 如果想删除主键约束或外键约束，也可以使用alter table语句，只不过要将add改为drop