数据库的约束规则与语义优化

数据完整性：是指数据的精确性和可靠性。

作用：

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) |

+----+-------------+-------+------+----------------------------------------------------+

原文地址 https://blog.csdn.net/qq_36594703/article/details/81269676