數據庫的約束與索引

一、約束

1、定義

數據庫的約束是指對數據庫表中的數據施加的一些規則或限制，以確保數據的完整性和一致性。

2、主鍵索引

2.1、 理解：唯一約束+非空約束，該字段上的數據不能重復且不能為null。

2.2、 注意：一張表必須有且只有一個主鍵

2.3、 添加主鍵約束

CREATE TABLE USER(username VARCHAR(32) PRIMARY KEY,PASSWORD VARCHAR(32),nick_name VARCHAR(32),name VARCHAR(32),age INT(3),salary DECIMAL(20,2)
);

2.4、 刪除主鍵約束

ALTER TABLE user DROP PRIMARY KEY;

3、唯一約束

3.1、 定義： 唯一約束是指定table的列或列組合不能重復，保證數據的唯一性

3.2、 理解：該字段上的數據不能重復，但可以為null

3.3、 注意：同一個表可以有多個唯一約束，多個列組合的約束。

在創建唯一約束時，如果不給唯一約束名稱，就默認和列名相同。

唯一約束不僅可以在一個表內創建，而且可以同時多表創建組合唯一約束。

3.4、 創建唯一約束

ALTER TABLE user add UNIQUE(phone);

3.5、 刪除唯一約束

ALTER TABLE user DROP INDEX phone;

4、非空約束

4.1、 理解：該字段上的數據不能為null，但可以重復

4.2、 添加非空約束

ALTER TABLE user MODIFY PASSWORD VARCHAR(32) not NULL;

4.3、 刪除非空約束

ALTER TABLE user MODIFY PASSWORD VARCHAR(32)  NULL;

4.4、 需求：設置password字段為非空字段，設置默認值為’000000’

ALTER TABLE user MODIFY PASSWORD VARCHAR(32) NOT NULL DEFAULT '000000';

5、外鍵約束

5.1、 理解：外鍵約束是保證一個或兩個表之間的參照完整性，外鍵是構建于一個表的兩個字段或是兩個表的兩個字段之間的參照關系

5.2、 創建外鍵約束

ALTER TABLE user ADD FOREIGN KEY(notionality_id) REFERENCES nationality(id);

5.3、 刪除外鍵約束

注意：外鍵名使用SHOW CREATE TABLE user;查看

ALTER TABLE USER DROP FOREIGN KEY user_ibfk_1;

二、索引

1、定義

數據庫索引（Index） 是一種輔助數據結構，存儲在數據庫中，旨在提高數據檢索的速度。索引通常以B樹或哈希表的形式實現。通過創建索引，可以顯著減少數據查找的時間復雜度，從而提升查詢效率。

理解：相當于一本書的目錄，讓查詢變得更快

2、主鍵索引

2.1、 理解：主鍵作為約束，讓該字段上的數據不能為重復且不能為空；主鍵作為索引，讓該字段的數據作為條件查詢更快

2.2、 添加主鍵索引

CREATE TABLE student(id INT(3) PRIMARY KEY auto_increment,name VARCHAR(32),sex VARCHAR(32),age INT(3),salary FLOAT(8,2),course VARCHAR(32)

2.3、 刪除主鍵索引

alter table student drop primary key;

3、唯一索引

3.1、 理解：唯一作為約束，讓該字段上的數據不能重復但可以為null；唯一作為索引，讓該字段的數據作為條件查詢更快

3.2、 創建唯一索引

alter table student add unique(name);

3.3、刪除唯一索引

alter table student drop index name;

4、普通索引

4.1、 理解：普通索引上的數據沒有任何約束，可以重復也可以為null；普通索引讓字段的數據作為條件查詢更快

4.2、 添加普通索引

ALTER TABLE student ADD INDEX(age);

4.3、刪除普通索引

drop index age on student;

5、全文索引

拓展：PARSER ngram - 添加ngram解析器（該解析器可以解析中文、日文、韓文）

5.1、 添加全文索引

ALTER TABLE news ADD FULLTEXT(info) WITH PARSER ngram;

5.2、 利用全文索引去查詢

SELECT * FROM news WHERE MATCH(info) against('胡歌很帥');

5.3、 刪除全文索引

drop index info on news;

三、深入索引

面試題（一）

索引的底層數據結構是什么？索引是如何實現的？

答：索引的底層數據結構是B+tree(多路樹)；當設置了字段為索引以后，底層會將字段上的數據使用B+Tree的數據結構存儲在索引文件里

面試題（二）

索引的優缺點？

答：優點：索引字段作為條件查詢更快；

? 缺點：添加、刪除數據會改變表，也會改變索引文件里的數據結構

? 解決方案：少量的添加、刪除就直接忽略，批量添加、刪除之前可以把所有刪除，批量操作后再把所有添加上

面試題（三）

索引的分類？

答：聚簇索引/聚集索引：主鍵索引

? 非聚簇索引/非聚集索引：唯一索引、普通索引、全文索引

面試題（四）

聚簇索引與非聚簇索引的區別？
答： 聚簇索引：葉子節點上存放的是數據行的數據

? 非聚簇索引：葉子節點存放的是數據行的地址（拿到地址后，還要去找地址上的數據）

面試題（五）

索引什么情況下會失效？

答：

– 1、查詢條件中包含or
– 2、like 通配符% 錯誤使用
– 3、聯合索引最左匹配原則
– 4、索引列使用MySQL函數，索引失效
– 5、索引列存在計算，使用（+、-、*、/），索引失效
– 6、使用（！= 或者 < >，not in），導致索引失效
– 7、使用is null， is not null，導致索引失效
– 8、左連接、右連接關聯字段編碼不一致，索引失效
– 9、使用了select ，導致索引失效
– 10、order by使用,導致索引失效
3、聯合索引最左匹配原則
– 4、索引列使用MySQL函數，索引失效
– 5、索引列存在計算，使用（+、-、、/），索引失效
– 6、使用（！= 或者 < >，not in），導致索引失效
– 7、使用is null， is not null，導致索引失效
– 8、左連接、右連接關聯字段編碼不一致，索引失效
– 9、使用了select *，導致索引失效
– 10、order by使用,導致索引失效
– 11、group by 使用違反最左匹配原則，導致索引失效