05.主外键与索引

主键与外键

多字段唯一约束

要求表 employee_audit_department 在三个字段 employee_sid, department_sid, purchase 上唯一，即这三个字段的数据组合起来不允许重复

ALTER TABLE employee_audit_department ADD CONSTRAINT uniqueConstraintPurchase UNIQUE(employee_sid, department_sid, purchase);

创建外键模板代码

ALTER TABLE contactcompany
ADD CONSTRAINT contactcompany_contactcategory_pid_fk
FOREIGN KEY (parentid) REFERENCES contactcategory (id);

# 下面的语句在 2019年7月9日 23:18:28 测试通过
# 如果提示不能创建外键要检查表字段（如果是 VARCHAR 类型）
# 字符集类型和排序规则
alter table ordermain
add constraint ordermain_companytitle_sid_fk
foreign key (contractor) references companytitle (sid)
on update cascade;

禁用外键

SET FOREIGN_KEY_CHECKS=0;
# 你的SQL代码
SET FOREIGN_KEY_CHECKS=1;

查看所有外键关系

# 查看数据库中所有外键关系
# 最后一行的 femis 是数据库名称
SELECT C.TABLE_SCHEMA            拥有者,
           C.REFERENCED_TABLE_NAME  父表名称 ,
           C.REFERENCED_COLUMN_NAME 父表字段 ,
           C.TABLE_NAME             子表名称,
           C.COLUMN_NAME            子表字段,
           C.CONSTRAINT_NAME        约束名,
           T.TABLE_COMMENT          表注释,
           R.UPDATE_RULE            约束更新规则,
           R.DELETE_RULE            约束删除规则
      FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE C
      JOIN INFORMATION_SCHEMA. TABLES T
        ON T.TABLE_NAME = C.TABLE_NAME
      JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
        ON R.TABLE_NAME = C.TABLE_NAME
       AND R.CONSTRAINT_NAME = C.CONSTRAINT_NAME
       AND R.REFERENCED_TABLE_NAME = C.REFERENCED_TABLE_NAME
      WHERE C.REFERENCED_TABLE_NAME IS NOT NULL
      AND C.TABLE_SCHEMA='femis';

索引

无法使用索引的几种情况

1.png

离散性

使用下面语句检测一个字段的离散性，数字越大表示离散性越好，性别之类的字段不适合做索引。

select count(DISTINCT billCode)/count(*) as discrete from bpbilldetail where 1=1;

高性能索引的创建策略

1. 不要频繁改变主键 根据B+树是多路平衡树，频繁的修改主键值导致物理顺序改变会导致已经稳定的页被分裂合并致使性能降低。但这并不代表不可 insert，而是不可频繁修改。
2. 不要创建冗余和重复索引，下面两个例子都是重复索引 索引（A,B）和索引（A），索引（A）和索引（A，ID）
3. 不要有未使用的索引

索引的效用

1. 一个索引就是一个B+树，索引让我们的查询可以快速定位和扫描到我们需要的数据记录上，加快查询的速度
2. 一个 select 查询语句在执行过程中一般最多能使用一个二级素银来加快查询，即使在 where 条件中用了多个二级索引