SQL 约束&索引

旧城等待， 2021-10-01 08:00 366阅读 0赞

# SQL 约束&索引 #

记录常用的约束(主键，外键)和索引的用法。

## 1.标准SQL ##

### 1.1 Constraints 约束 ###

`Constraints`：约束用于限制加入表的数据的类型。

约束主要有：

*  1.NOT NULL
 *  2.UNIQUE
 *  3.PRIMARY KEY
 *  4.FOREIGN KEY
 *  5.CHECK
 *  6.DEFAULT

经常混乱的：  
`UNIQUE`：约束唯一标识数据库表中的每条记录。  
`PRIMARY KEY`：主键必须包含唯一的值，主键不允许NULL值，自动定义成UNIQUE约束，每个表都应有且只有一个主键。  
`FOREIGN KEY`：一个表中的FOREIGN KEY指向另一个表中的PRIMARY KEY。  
`CHECK`：用于限制列中的值的范围。

### 1.2 Index 索引 ###

`INDEX`：在不读取整个表的情况下，使用索引可以更快地查找数据。  
将每行数据中的某个(多个)字段提取出来跟这行数据形成映射关系，通过使用索引，不再匹配每行的所有字段，时间复杂度和磁盘IO大大降低，同时利用数据结构的特性达到快速定位某行数据的目的。

> 注意：  
> 1.UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。  
> 2.每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

## 2.MySQL ##

*  1.数据库版本  
    MySQL：8.0.16
 *  2.完整栗子  
    主表为`t_student`，子表为`t_score`

CREATE TABLE `t_student` (
      `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
      `name` varchar(255) DEFAULT '' COMMENT '姓名',
      `age` int(11) DEFAULT '10' COMMENT '年龄',
      `sex` varchar(255) DEFAULT 'Male' COMMENT '性别',
      `hobbies` varchar(255) DEFAULT '' COMMENT '爱好',
      `address` varchar(255) DEFAULT '' COMMENT '住址',
      PRIMARY KEY (`id`),
      UNIQUE KEY `idx_student_name_address` (`name`,`address`) USING BTREE,
      KEY `idx_age_sex` (`age`,`sex`)
    ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8 COMMENT='学生表';

CREATE TABLE `t_score` (
      `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
      `student_id` int(11) DEFAULT NULL COMMENT '学生ID',
      `student_name` varchar(255) DEFAULT NULL COMMENT '学生姓名',
      `subject` varchar(255) DEFAULT NULL COMMENT '学科',
      `subject_score` int(11) DEFAULT NULL COMMENT '学科成绩',
      PRIMARY KEY (`id`),
      UNIQUE KEY `idx_score_studentname_subject` (`student_name`,`subject`) USING BTREE,
      KEY `fk_score_student_id` (`student_id`),
      CONSTRAINT `fk_score_student_id` FOREIGN KEY (`student_id`) 
      REFERENCES `t_student` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
    ) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8 COMMENT='学生成绩表'

### 2.1 MySQL约束 ###

#### 2.1.1 MySQL主键 ####

主键用法跟标准SQL无差异。

#### 2.1.2 MySQL外键 ####

MySQL支持外键，它允许跨表交叉引用相关数据，以及 外键约束，这有助于保持这种展开数据的一致性。

*  1.语法

外键约束定义的基本语法：

[CONSTRAINT [symbol]] FOREIGN KEY
        [index_name] (col_name, ...)
        REFERENCES tbl_name (col_name,...)
        [ON DELETE reference_option]
        [ON UPDATE reference_option]
    
    reference_option
        RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

可选属性`reference_option`：  
**1**.`ON DELETE`：主表`删除`时，子表进行…  
**2**.`ON UPDATE`：主表`更新`时，子表进行…  
他俩有如下可选项：

*  `RESTRICT`：  
    拒绝父表的删除或更新操作。指定 RESTRICT（或NO ACTION）与省略ON DELETEor ON UPDATE子句相同。
 *  `CASCADE`：  
    从父表中删除或更新行，并自动删除或更新子表中的匹配行。这两个ON DELETE CASCADE和ON UPDATE CASCADE 支持。在两个表之间，不要定义多个ON UPDATE CASCADE子句，这些 子句作用于父表或子表中的同一列。
 *  `SET NULL`  
    从父表中删除或更新行，并将子表中的外键列设置为NULL。  
    支持两个ON DELETE SET NULL和ON UPDATE SET NULL子句。  
    如果指定SET NULL操作，请 确保未将子表中的列声明为NOT NULL。
 *  `NO ACTION` ：  
    标准SQL中的关键字。在MySQL中，相当于RESTRICT。  
    如果引用的表中存在相关的外键值，则MySQL服务器拒绝父表的删除或更新操作。某些数据库系统具有延迟检查，并且NO ACTION是延迟检查。  
    在MySQL中，立即检查外键约束，因此NO ACTION也是如此RESTRICT。
 *  `SET DEFAULT`：  
    这个动作由MySQL解析器认可，但两者 InnoDB并 NDB拒绝包含表定义ON DELETE SET DEFAULT或 ON UPDATE SET DEFAULT条款。

> 外键和引用键中的相应列必须具有相似的数据类型。  
> 整数类型的大小和符号必须相同。  
> 字符串类型的长度不必相同。  
> 对于非二进制（字符）字符串列，字符集和排序规则必须相同。

*  2.用法
 *  建表时创建外键：参考\#2的完整sql

...
    CONSTRAINT `fk_score_student_id` FOREIGN KEY (`student_id`) 
      REFERENCES `t_student` (`id`) 
      ON UPDATE CASCADE 
      ON DELETE RESTRICT
    ...

*  建表后创建外键：

ALTER TABLE `t_score`
        ADD CONSTRAINT `fk_score_student_id` FOREIGN KEY
        `fk_score_student_id` (`student_id`)
        REFERENCES `t_student` (`id`)
        ON DELETE RESTRICT
        ON UPDATE CASCADE

*  删除外键：

ALTER TABLE `t_score` DROP FOREIGN KEY `fk_score_student_id`;

> 参考：[https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html][https_dev.mysql.com_doc_refman_8.0_en_create-table-foreign-keys.html]

#### 2.1.3 MySQL索引 ####

*  1.语法

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
        [index_type]
        ON tbl_name (key_part,...)
        [index_option]
        [algorithm_option | lock_option] ...
    
    key_part: {col_name [(length)] | (expr)} [ASC | DESC]
    
    index_option:
        KEY_BLOCK_SIZE [=] value
      | index_type
      | WITH PARSER parser_name
      | COMMENT 'string'
      | {VISIBLE | INVISIBLE}
    
    index_type:
        USING {
       BTREE | HASH}
    
    algorithm_option:
        ALGORITHM [=] {
       DEFAULT | INPLACE | COPY}
    
    lock_option:
        LOCK [=] {
       DEFAULT | NONE | SHARED | EXCLUSIVE}

MySQL索引属性：

*  1.IndexCharacteristics (索引特征)

图形化创建索引界面：`索引特征`  
![在这里插入图片描述][20190823111415482.png]

Noin-Unique：唯一索引创建一个约束，使索引中的所有值必须是不同的。  
Unique：唯一索引创建一个约束，使索引中的所有值必须是不同的。  
FullText：全文索引仅支持InnoDB和MyISAM表，并且只能包含CHAR，VARCHAR和TEXT列。  
Spatial：空间索引用来支持空间数据类型的作为索引[(空间数据类型见)][Link 1]。

*  2.IndexType (索引存储格式类型)

不同存储引擎支持的不同的索引存储数据结构：

CREATE TABLE lookup (id INT) ENGINE = MEMORY;
    CREATE INDEX id_index ON lookup (id) USING BTREE;

![在这里插入图片描述][20190823124315840.png]  
不同存储引擎不同Index特征下的IndexType选项：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXh4Mw_size_16_color_FFFFFF_t_70]

*  3.Algorithm\_option (增删改索引同步算法)

在NDB表的可变宽度列上`添加`和`删除`索引的操作在线进行，是非复制的，也就是说，它们不需要重新创建索引。  
它们不会锁定被更改的表以防止NDB集群中的其他API节点访问。  
对于具有多个API节点的NDB集群中的NDB表更改，此类操作不需要单用户模式，在线DDL操作期间，事务可以继续不间断。

图形化创建索引界面：`同步算法`  
![在这里插入图片描述][201908231206265.png]

Default：在线增删改索引是非复制的，其它节点不会重建索引。  
Copy：在线增删改索引时将表数据逐行复制到新表中，该算法不允许INSERT和UPDATE等并发数据操作语句。  
InPlace：在线增删改索引时表被重建而不是复制到新表， 该算法允许并发数据操作语句。（支持ADD COLUMN，ADD INDEX（包括CREATE INDEX语句）和DROP INDEX操作）。

> ALGORITHM子句是可选的。  
> 如果你跳过它，MySQL使用INPLACE。 如果不支持INPLACE，MySQL使用COPY。  
> 使用DEFAULT与省略ALGORITHM子句具有相同的效果。

*  4.Lock\_option (增删改索引加锁策略)

在增删改索引时，lock\_option控制表上的并发读写和写入级别。  
图形化创建索引界面：`加锁策略`  
![在这里插入图片描述][20190823120655927.png]  
Default：这允许您具有给定算法的最大并发级别； 首先，如果支持，它允许并发读取和写入； 如果不支持，如果支持则允许并发读取； 如果不是，请强制执行独占访问。  
None：如果支持NONE，则可以进行并发读写；否则，MySQL会发出错误。  
Shared：如果支持SHARED，则可以进行并发读取，但不能写入； 如果不支持并发读取，MySQL会发出错误。  
Exclusive：这会强制执行独占访问。

#### 2.3.2 用法 ####

建表时创建索引：参考\#2的完整sql

...
      USING {
       BTREE | HASH}
      ALGORITHM [=] {
       DEFAULT|INPLACE|COPY}
      LOCK [=] {
       DEFAULT|NONE|SHARED|EXCLUSIVE}
    ...

建表后创建索引：

CREATE INDEX `idx_student_name_address` ON `t_student`(`name`,`address`) USING BTREE;

删除索引：

DROP INDEX `idx_student_name_address`ON `t_student`
    ALGORITHM = INPLACE 
    LOCK = DEFAULT;

> 参考：[https://dev.mysql.com/doc/refman/8.0/en/mysql-cluster-online-operations.html][https_dev.mysql.com_doc_refman_8.0_en_mysql-cluster-online-operations.html]，[http://www.mysqltutorial.org/mysql-index/mysql-drop-index/][http_www.mysqltutorial.org_mysql-index_mysql-drop-index]

## 3.SQLServer ##

待记录。

## 4.附录 DML和DDL ##

可以把 SQL 分为两个部分：数据操作语言 (DML) 和 数据定义语言 (DDL)。

### 4.1 DML(结构化查询语言) ###

DML(结构化查询语言)是用于执行查询的语法。  
查询和更新指令构成了SQL的DML部分：

*  SELECT - 从数据库表中获取数据
 *  UPDATE - 更新数据库表中的数据
 *  DELETE - 从数据库表中删除数据
 *  INSERT INTO - 向数据库表中插入数据

### 4.2 DDL(数据定义语言) ###

DDL(数据定义语言)部分使我们有能力创建或删除表格。  
我们也可以定义索引（键），规定表之间的链接，以及施加表间的约束。  
SQL 中最重要的 DDL 语句:

*  CREATE DATABASE - 创建新数据库
 *  ALTER DATABASE - 修改数据库
 *  CREATE TABLE - 创建新表
 *  ALTER TABLE - 变更（改变）数据库表
 *  DROP TABLE - 删除表
 *  CREATE INDEX - 创建索引（搜索键）
 *  DROP INDEX - 删除索引

本文内容属于DDL部分。

[https_dev.mysql.com_doc_refman_8.0_en_create-table-foreign-keys.html]: https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html
[20190823111415482.png]: /images/20210911/d658707776bd44eba746148661954e21.png
[Link 1]: https://dev.mysql.com/doc/refman/8.0/en/spatial-types.html
[20190823124315840.png]: /images/20210911/7dff1ccccd90475e85821d12b7357e83.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXh4Mw_size_16_color_FFFFFF_t_70]: /images/20210911/abf03d33aff941a7ac68c68d83e05260.png
[201908231206265.png]: /images/20210911/35f744187d9c4136976e426a83725b65.png
[20190823120655927.png]: /images/20210911/99375023a88740639b2b05ba75ab90f5.png
[https_dev.mysql.com_doc_refman_8.0_en_mysql-cluster-online-operations.html]: https://dev.mysql.com/doc/refman/8.0/en/mysql-cluster-online-operations.html
[http_www.mysqltutorial.org_mysql-index_mysql-drop-index]: http://www.mysqltutorial.org/mysql-index/mysql-drop-index/