数据库的约束规则与语义优化
数据完整性:是指数据的精确性和可靠性。
作用:
1.防止用户向数据库中添加不合语义的数据
2.利用基于DBMS的完整性控制机制来实现业务规则,易于定义,容易理解,而且可以降低应用程序的复杂性,提高应用程序的运行效率,同时,基于DBMS的完整性控制机制是集中管理的,因此比应用程序更容易实现数据库的完整性。
数据完整性分为四类:
1.实体完整性:唯一性---主键
2.域完整性:保证数据库字段取值的合理性。包括检查(CHECK),默认值(DEFAULT),不为空,可为空
3.参照完整性:主外键
4.用户自定义完整性
语义优化是从语义的角度对SQL进行优化,不是一种形式上的优化,所以其优化的范围,可能覆盖其他类型的优化范围。
语义转换:因为完整性限制等的原因使得一个转换成立的情况成为语义。
语义优化:因为语义转换形成的优化称为语义优化。
语义转换其实是根据完整性约束等信息对“某特定语义”进行推理,进而得到的一种查询效率不同但结果相同的查询。
语义优化常见的方式:
1.连接消除:例如A、B两个基表做自然连接,创建一个视图V,如果在视图V上执行查询只涉计其中一个基表的信息,则对视图的查询完全可以转化为对某个基表的查询。
2.连接引入:增加连接有助于原始关系变小或原关系的选择率降低。
3.谓词引入:根据完整性约束等信息,引入新谓词,如引入基于索引的列,可能是查询更快。
例如:一个表上,有“c1<c2”的列约束,c2列上存在一个索引,查询语句中的WHERE条件有“c1>200”,则可以推知“c2>200”,WHER条件变更为“c1>200 AND c2>200 AND c1<c2”
4.检测空回答集:查询语句中的谓词与约束相勃,可以推知条件结果为false,也许最终的结果集能为空。
例如:CHECK约束限定“score”列的范围是60到100,而一个查询条件是“score<60”,则能立刻推知条件不成立
5.排序优化:ORDERBY操作通常由索引或排序完成,如果能够利用索引,则排序操作可省略。
6.唯一性使用:利用唯一性、索引等特点,检查是否存在不必要的DISTINCT操作。
示例1:语义优化中的检测空回答集技术,MYSQL支持:
create table student(name VARCHAR(30) not null,age int);
insert into student values('tom',19);
insert into student values('marray',19);
insert into student values('jack',19);
mysql> explain EXTENDED SELECT * FROM student WHERE name IS NULL AND age>18;
+----+-------------+-------+------+------+---------+------+------+----------+------------------+
| id | select_type | table | type | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+------+---------+------+------+----------+------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+------+---------+------+------+----------+-----------
示例2:语义优化中的谓词引入技术,MYSQL不支持
创建表如下,列c2有唯一索引存在,并创建CHECK约束:
CREATE TABLE C (c1 INT, c2 INT UNIQUE, CHECK(c1<c2));
在c1列上进行条件查询,查询执行计划:
mysql> EXPLAIN EXTENDED SELECT * FROM C WHERE c1>60;
+----+-------------+-------+------+------+---------+------+------+-------------+
| id | select_type | table | type | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+------+---------+------+------+-------------+
| 1 | SIMPLE | C | ALL | NULL | NULL | NULL | 1 | Using where |
+----+-------------+-------+------+------+---------+------+------+-------------+
示例3:语义优化中的排序优化,mysql支持,但条件较为苛刻。
创建表如下:
CREATE TABLE D (d1 INT, d2 INT UNIQUE);
对D进行自连接,连接条件使用有唯一索引的列,且连接条件的列与排序列相同。
查询执行计划:
mysql> EXPLAIN EXTENDED SELECT * FROM D F1, D F2 WHERE F1.d2=F2.d2 ORDER BY F1.d2;
+----+-------------+-------+------+------+-----------+-----------------------------+
| id | select_type | table | type | key | ref | Extra |
+----+-------------+-------+------+------+-----------+-----------------------------+
| 1 | SIMPLE | F1 | ALL | NULL | NULL | Using where; Using filesort |
| 1 | SIMPLE | F2 | ref | d2 | tt2.F1.d2 | NULL |
+----+-------------+-------+------+------+-----------+-----------------------------+
示例4:语义优化中的唯一性使用,MySQL支持
create table e(e1 int,e2 int UNIQUE,e3 int,PRIMARY key(e1))
insert into e values(1,1,1);
insert into e values(2,null,null);
insert into e values(3,3,3);
insert into e values(4,null,null);
insert into e values(5,5,5);
mysql> EXPLAIN EXTENDED SELECT DISTINCT e1 FROM E;
+----+-------------+-------+-------+------+---------+------+------+----------+-------------+
| id | select_type | table | type | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | E | index | e2 | 5 | NULL | 5 | 100.00 | Using index |
+----+-------------+-------+-------+------+---------+------+------+----------+-------------+
mysql> EXPLAIN EXTENDED SELECT DISTINCT e2 FROM E;
+----+-------------+-------+-------+------+---------+------+------+----------+-------------+
| id | select_type | table | type | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | E | index | e2 | 5 | NULL | 5 | 100.00 | Using index |
+----+-------------+-------+-------+------+---------+------+------+----------+-------------+
mysql> EXPLAIN EXTENDED SELECT DISTINCT e3 FROM E;
+----+-------------+-------+------+------+-----------------+
| id | select_type | table | type | key | Extra |
+----+-------------+-------+------+------+-----------------+
| 1 | SIMPLE | E | ALL | NULL | Using temporary |
+----+-------------+-------+------+------+-----------------+
示例5:语义优化中的连接消除技术MySQL不支持
create table a(a1 int,a2 int);
create table b(b1 int,b2 int);
create view v as select * from a,b;
insert into a values(1,1);
insert into a values(2,2);
insert into a values(3,3);
mysql> EXPLAIN SELECT a1, a2 FROM V WHERE a1>2;
+----+-------------+-------+------+----------------------------------------------------+
| id | select_type | table | type | Extra |
+----+-------------+-------+------+----------------------------------------------------+
| 1 | SIMPLE | b | ALL | NULL |
| 1 | SIMPLE | a | ALL | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+----------------------------------------------------+