MySQL索引原理之索引与约束

Lion Long

已于 2023-02-02 08:50:28 修改

阅读量706

点赞数 1

分类专栏： # MySQL 文章标签： mysql elasticsearch 数据库 sql 索引

于 2022-10-30 13:38:32 首次发布

本文链接：https://blog.csdn.net/Long_xu/article/details/127590097

版权

MySQL 专栏收录该内容

15 篇文章 5 订阅

订阅专栏

索引与约束

索引
约束
- 外键约束
- 约束与索引的区别
索引使用场景
不要使用索引的场景
总结

索引

索引的概念：索引是一种有序的存储结构。索引按照单个或多个列的值进行排序。
索引的目的：提升搜索效率。

索引分类

按照数据结构分为：
（1）B+树索引。
（2）自适应hash索引。主要用在内存当中，看MySQL缓冲中是否有数据。
（3）全文索引。将存储在数据库当中的整本书和整篇文章中的任意内容信息查找出来的技术；关键词 FULLTEXT；在短字符串中用 LIKE %；在全文索引中用 match 和against。一般不会用到；如果要用全文索引，会使用elasticsearch工具。

按照物理存储分为：
（1）聚集索引；或者称为聚簇索引。聚集索引是主键对应的那个B+树；会存储对应的行数据。
（2）辅助索引；或称为二级索引。比如通常设计的普通索引或组合索引，只有索引信息和主键ID信息，没有行数据。

按照列属性分为：
（1）主键索引。是一个非空唯一索引，一个表只有一个主键索引；在 innodb 中，主键索引的 B+ 树包含表数据信息。

PRIMARY KEY(key)

（2）唯一索引。不可以出现相同的值，允许出现null。

UNIQUE(key)

（3）普通索引。允许出现相同的索引内容。

INDEX(key)
-- or
KEY(key[...])

（4）前缀索引。只比较前几个字符的长字符串。

按照列的个数分为：
（1）单列索引。
（2）组合索引。对表上的多个列进行索引。

INDEX idx(key1,key2[,...]);
UNIQUE(key1,key2[,...]);
PRIMARY KEY(key1,key2[,...]);

主键选择

innodb 中表是索引组织表，每张表有且仅有一个主键。

如果显示设置 PRIMARY KEY，则该设置的 key 为该表的主键。
如果没有显示设置，则从非空唯一索引中选择。

a. 只有一个非空唯一索引，则选择该索引为主键；
b. 有多个非空唯一索引，则选择声明的第一个为主键；

没有非空唯一索引，则自动生成一个 6 字节的 _rowid 作为主键。

索引的代价

（1）空间上：索引是一种存储结构，需要写入磁盘中，会占用空间，在工程应用中，一般不会超过8个索引。
（2）时间上：维护的代价，体现在DML操作会变慢，因为它要维护所有索引对应的B+树。

约束

为了实现数据的完整性，对于 innodb，提供了以下几种约束：primary key，unique key，foreign key，default，not null。

外键约束

外键用来关联两个表，来保证参照完整性；MyISAM 存储引擎本身并不支持外键，只起到注释作用；而 innodb 完整支持外键，并具备事务性。

create table parent (
   id int not null,
    primary key(id) ) engine=innodb;
    
create table child (
   id int,
   parent_id int,
    foreign key(parent_id) references parent(id) ON DELETE CASCADE ON UPDATE CASCADE
) engine=innodb;

-- 被引用的表为父表，引用的表称为子表；
-- 外键定义时，可以设置行为 ON DELETE 和 ON UPDATE，行为发生时的操作可选择：
-- CASCADE   子表做同样的行为
-- SET NULL 更新子表相应字段为 NULL
-- NO ACTION 父类做相应行为报错
-- RESTRICT 同 NO ACTION

-- 测试
INSERT INTO parent VALUES (1);
INSERT INTO parent VALUES (2);
INSERT INTO child VALUES (10, 1);
INSERT INTO child VALUES (20, 2);
DELETE FROM parent WHERE id = 1;

被引用的表为父表，引用的表称为子表。
外键定义时，可以设置行为 ON DELETE 和 ON UPDATE，行为发生时的操作可选择：
（1）CASCADE，子表做同样的行为。
（2）SET NULL，更新子表相应字段为 NULL。
（3）NO ACTION ，父类做相应行为报错。
（4）RESTRICT 同 NO ACTION。