第十三章 索引和约束

1.基本描述

    本章主要讲解索引和约束，索引是最为常见的改善数据库性能的技术。SQL Server引入索引主要是为了提高查询的效率，它能够加速ORDER BY和GROUP BY子句的操作，保证数据的唯一性，并加快表连接的速度。索引是在基本表的列上建立的一种数据库对象，它和基本表分开存储，它的建立或撤销对数据的内容毫无影响。索引一经创建，就完全由系统自动选择和维护，不需要用户指定使用索引，也不需要用户执行打开索引或进行重新索引等操作。
    在创建索引时，需要注意一下问题：
    基本表的纪录数量越多，纪录越长，越有必要建索引；
    索引是为了加快查询速度而创建的，但同时会影响基本表的插入、删除或修改等更新活动的速度。所以一般只对那些数据量大、查询频度高、实时性要求强的基本表才创建索引，且一张基本表上不要创建过多的索引；
    索引是建立在基本表列上的对象，对哪个列进行索引，通常根据列在WHERE、ORDER BY、GROUP BY子句中出现的频率决定。

2.索引分类

    单列索引：是对基本表的某一单独的列进行索引，通常应对每个基本表的主关键字建立单列索引；
    唯一索引：一旦在一个或多个列上建立唯一索引，则不允许在表中相应的列上插入任何相同的取值；
    复合索引：是针对基本表中两个或两个以上列建立的索引；
    聚集索引：采用聚集索引会改变基本表中纪录的物理存储顺序；
    非聚集索引：采用非聚集索引，表中纪录的物理顺序与索引顺序不同，即表中的纪录仍按实际插入的先后顺序排列，不按索引列排序。

3.基本样例

SELECT dept_id, name FROM department
WHERE name LIKE 'A%';

ALTER TABLE department ADD INDEX dept_name_idx (name);

SHOW INDEX FROM department G

ALTER TABLE department DROP INDEX dept_name_idx;

ALTER TABLE department ADD UNIQUE dept_name_idx (name);

INSERT INTO department (dept_id, name) VALUES (999, 'Operations');

ALTER TABLE employee ADD INDEX emp_names_idx (lname, fname);

SELECT emp_id, fname, lname FROM employee WHERE emp_id IN (1, 3, 9, 15);

SELECT cust_id, SUM(avail_balance) tot_bal FROM account
WHERE cust_id IN (1, 5, 9, 11)
GROUP BY cust_id;

EXPLAIN SELECT cust_id, SUM(avail_balance) tot_bal FROM account
WHERE cust_id IN (1, 5, 9, 11)
GROUP BY cust_id G

ALTER TABLE account ADD INDEX acc_bal_idx (cust_id, avail_balance);

EXPLAIN SELECT cust_id, SUM(avail_balance) tot_bal
FROM account WHERE cust_id IN (1, 5, 9, 11) GROUP BY cust_id G

SELECT product_type_cd, name FROM product_type;

SELECT product_type_cd, product_cd, name FROM product ORDER BY product_type_cd;

###
UPDATE product SET product_type_cd = 'XYZ'WHERE product_type_cd = 'LOAN';

UPDATE product_type SET product_type_cd = 'XYZ' WHERE product_type_cd = 'LOAN';
###

ALTER TABLE product DROP FOREIGN KEY fk_product_type_cd;

ALTER TABLE product ADD CONSTRAINT fk_product_type_cd FOREIGN KEY (product_type_cd) REFERENCES
product_type (product_type_cd) ON UPDATE CASCADE;

UPDATE product_type SET product_type_cd = 'XYZ' WHERE product_type_cd = 'LOAN';

SELECT product_type_cd, name FROM product_type;

SELECT product_type_cd, product_cd, name FROM product ORDER BY product_type_cd;