【mysql】带你玩转mysql数据库中的索引

✨✨ 欢迎大家来到景天科技苑✨✨

🎈🎈 养成好习惯，先赞后看哦~🎈🎈

🏆 作者简介：景天科技苑
🏆《头衔》：大厂架构师，华为云开发者社区专家博主，阿里云开发者社区专家博主，CSDN全栈领域优质创作者，掘金优秀博主，51CTO博客专家等。
🏆《博客》：Python全栈，前后端开发，小程序开发，人工智能，js逆向，App逆向，网络系统安全，数据分析，Django，fastapi，flask等框架，linux，shell脚本等实操经验，网站搭建，数据库等分享。

所属的专栏：MySQL数据库入门，进阶应用实战必备
景天的主页：景天科技苑

文章目录

mysql索引

mysql索引大致包含以下几类

主键索引 : PRI [primary key]
唯一索引 : UNI [unique]
普通索引 : MUL [index]

索引原理

innodb和myisam的索引的存储方式是不同的

• innodb 存储引擎 索引和数据都存在ibd文件中
• myisam 把所有的索引全部单独存储,就是那个MYI文件

索引的数据结构使用的是b+树

• 一个叶子节点也叫一个数据页,可以存多条数据,大概16k
• 单条数据量越小,叶子节点存的数据量就会越大,需要的叶子节点就越少,这样的话,树的高度相对降低一些,查询的速度越快.

联合唯一索引

• unique(字段1,字段2,字段3 … ) 合在一起,该数据不能重复
• unique + not null

createtable t1_server(id int, server_name varchar(10)notnull, ip varchar(15)notnull, port intnotnull,unique(ip,port));insertinto t1_server values(1,"阿里","192.168.11.251",3306);insertinto t1_server values(1,"阿里","192.168.11.251",80);insertinto t1_server values(1,"阿里","192.168.11.252",80);insertinto t1_server values(1,"阿里","192.168.11.252",80); error

只有当ip和端口都一样时，才违反唯一索引

• unique : 如果字段没设置不为空，有可能出现多个空值的情况要注意;

createtable t2_server(id int, server_name varchar(10)notnull, ip varchar(15), port int,unique(ip,port));insertinto t2_server values(1,"腾讯","192.168.11.251",3306);insertinto t2_server values(1,"腾讯","192.168.11.251",3306); error
insertinto t2_server values(1,"腾讯",null,null);# 注意点: 允许插入多个空值;+------+-------------+----------------+------+| id   | server_name | ip             | port |+------+-------------+----------------+------+|1| 腾讯        |192.168.11.251|3306||1| 腾讯        |NULL|NULL||1| 腾讯        |NULL|NULL||1| 腾讯        |NULL|NULL||1| 腾讯        |NULL|NULL|+------+-------------+----------------+------+

没设置非空约束，联合唯一索引，成了普通索引 MUL。可以多次插入空值

联合唯一主键

createtable t3_server(id int,server_name varchar(10)notnull, ip varchar(15), port int,primarykey(ip,port));insertinto t3_server values(1,"华为","192.168.11.251",3306);insertinto t3_server values(1,"华为","192.168.11.251",3307);

设置成联合唯一主键，字段不用设置 非空约束，也是不让插入空

小结

• primary key(字段1,字段2 … ) 联合唯一主键 , 单个字段情况,可以设置一个主键,如果是多个字段只能设置成联合主键,合在一起表达一个主键概念;
• unique(字段1,字段2 … ) 联合唯一索引
• index(字段1,字段2 … ) 联合普通索引

foreign key: 外键

把多张表通过一个关联字段联合在一起 (该字段可以设置成外键,作用是可以级联更新或者级联删除)

    语法:    foreign key(classid) references class1(id)  
    条件:    被关联的字段,必须具备唯一属性;

• sql的外键: 外键约束的作用: 对外键字段的值进行更新和插入时会和引用表中字段的数据进行验证，数据如果不合法则更新和插入会失败， 保证数据有效性

首先是外键的定义

 如果一个字段X在一张表（表一）中是主关键字，而在另外一张表（表二）中不是主关键字，
 则字段X称为表二的外键；换句话说如果关系模式R1中的某属性集不是自己的主键，
 而是关系模式R2的主键，则该属性集称为是关系模式R1的外键。

一个表的外键可以是空值，若不为空值，则每一个外键的值必须等于另一个表中的主键的某个值。
定义一个外键后，不允许删除另一个表中具有关联关系的行；外键的主要作用是保持数据的一致性、完整性。
一个主键表某一字段的和跟他有关联的外键表的某一字段有直接的关联。

不设置外键约束容易产生垃圾数据

主键不一定一定要有关联另一个表的主键。
被关联的字段不一定是主键，但必须是唯一性索引。
主键约束和唯一性约束都是唯一性索引。

对已存在表的字段添加外键约束:

altertable student addforeignkey(cls_id)references class(id);

自己设置外键名称:

altertable student addconstraint fk_class foreignkey(cls_id)references class(id);

定义一个外键后，不允许删除另一个表中具有关联关系的行

mysql>deletefrom class where id ='10011';
ERROR 1451(23000): Cannot deleteorupdate a parent row: a foreignkeyconstraint fails 
(`test2`.`student`,CONSTRAINT`student_ibfk_1`FOREIGNKEY(`cls_id`)REFERENCES`class`(`id`))

mysql>select*from class;+-------+-----------------+| id    | name            |+-------+-----------------+|10011| 一年级一班      ||10012| 一年级二班      |+-------+-----------------+

定义了外键约束，可以保证数据的完整性、一致性

mysql>insertinto student values(0,'张航',23,'1998-02-19',0,1.77,'景浩');
ERROR 1452(23000): Cannot addorupdate a child row: a foreignkeyconstraint fails (`test2`.`student`,CONSTRAINT`student_ibfk_1`FOREIGNKEY(`cls_id`)REFERENCES`class`(`id`))
mysql>insertinto student values(0,'张航',23,'1998-02-19',0,1.77,'10013');
ERROR 1452(23000): Cannot addorupdate a child row: a foreignkeyconstraint fails (`test2`.`student`,CONSTRAINT`student_ibfk_1`FOREIGNKEY(`cls_id`)REFERENCES`class`(`id`))
mysql>insertinto student values(0,'张航',23,'1998-02-19',0,1.77,'10012');
Query OK,1row affected (0.00 sec)

插入的非约束内的字段会报错

如果表中有不符合外键关联表中的数据，比如，关联的表中主键id只有10011和10012，student表中原来cls_id那一列有
除了10011和10012以外的其他值，则创建外键也会报错

在创建表时，设置外键
先添加字段：

cls_id varchar(6)notnull,constraint fk_class foreignkey(cls_id)references class(id)#创建外键

删除外键:
首先先查看表中的外键名称:

mysql>showcreatetable student;+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+|Table|CreateTable|+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| student |CREATETABLE`student`(`id`intNOTNULLAUTO_INCREMENT,`name`varchar(20)NOTNULL,`age`intNOTNULL,`birthday`dateDEFAULTNULL,`is_del`tinyintDEFAULT'0',`height`decimal(3,2)DEFAULTNULL,`cls_id`varchar(6)NOTNULL,PRIMARYKEY(`id`),KEY`cls_id`(`cls_id`),CONSTRAINT`student_ibfk_1`FOREIGNKEY(`cls_id`)REFERENCES`class`(`id`))ENGINE=InnoDBAUTO_INCREMENT=20DEFAULTCHARSET=utf8 |+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1rowinset(0.00 sec)

然后删除外键

altertable student dropforeignkey student_ibfk_1;

在存在外键的情况下删除关联表数据：
在就教大家一个轻松的办法，不用删除外键就可以删除数据库中的一些数据，关键id还能从1开始，不用默认递增

首先，在数据库中查看外键是否有效（没有设置之前都是有效），命令：

select@@foreign_key_checks;

mysql>select @@foreign_key_checks;+----------------------+| @@foreign_key_checks|+----------------------+|1|+----------------------+1rowinset(0.00 sec)

将外键的值设置为0，此时就是失效状态，命令：set foreign_key_checks = 0，这时就能删除数据了

删除之后，外键再设置为有效：

set foreign_key_checks =1

student1:
    id       name                 age    classid      
    1      wangtongpei        58       1
    2      liuyifeng               85       1
    3       wangwen            18       2

class1:
    id classname 
    1  python32
    2  python33

# 创建class1
create table class1(id int , classname varchar(255));
# 添加索引
alter table class1 add unique(id);

增加主键:

删除索引

createtable class222(id intunique, classname varchar(255));altertable class1 dropindex id;

创建student1

createtable student1(
id intprimarykeyauto_increment,
name varchar(255),
age int,
classid int,constraint fk_class foreignkey(classid)references class1(id));

constraint fk_class 可以设置外建名，不设置的话，系统默认生成外键名

添加数据

insertinto class1 values(1,"python32");insertinto class1 values(2,"python33");insertinto class1 values(3,"python34");insertinto student1 values(null,"wangtongpei",58,1);insertinto student1 values(null,"liuyifeng",85,1);insertinto student1 values(null,"wangwen",18,2);

被别的表关联做外键的行，不能删除，例如class1表中，id为1,2的行不能删除。没有关联的数据可以直接删除

deletefrom class1 where id =1;

被关联的行不允许删除

没有关联的行可以删除

设置了外键的表，插入数据时，设置了外键的字段，不允许随意插入值，插入的值要在被关联外键表中存在，不然报错

有关联的数据不能直接删除,要先把关联的数据删掉之后再删除

deletefrom student1 where id =3;deletefrom class1 where id =2;

联级更新 , 联级删除 ( 谨慎使用 ) 删除被关联外键的数据，关联外键数据也会被删除

联级删除 on delete cascade
联级更新 on update cascade

# 创建class2
create table class2(id int primary key auto_increment, classname varchar(255));
# 创建student2
create table student2(
id int primary key auto_increment,
name varchar(255),
age int,
classid int,
foreign key(classid) references class2(id) on delete cascade on update cascade #区别
);

自己设置外键名字要确保外键名不能冲突，所以还是不设置为好，随机生成

添加数据

insertinto class2 values(1,"python32");insertinto class2 values(2,"python33");insertinto class2 values(3,"python34");insertinto student2 values(null,"wangtongpei",58,1);insertinto student2 values(null,"liuyifeng",85,1);insertinto student2 values(null,"wangwen",18,2);

联级删除 (把所有关联数据全部删除,谨慎;)

deletefrom class2 where id =1;

删除之前数据

删除之后数据，学生表中，班级id为1的学生被级联删除

联级更新 (把所有关联数据全部更新,谨慎;)

update class2 set id =100where classname="python33";