后端开发【一大波有用知识】MySQL索引原理（索引、约束、索引实现、索引失效、索引原则）以及SQL优化

最新推荐文章于 2025-03-27 17:44:21 发布

原创

最新推荐文章于 2025-03-27 17:44:21 发布 · 343 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #中间件 #后端开发 #Linux服务器开发

本文详细介绍了MySQL的索引类型，包括主键、唯一、普通、组合和全文索引，强调了主键选择和B+树在索引实现中的重要性。此外，还探讨了索引失效的情况和优化SQL的方法，如避免全表扫描、使用覆盖索引和遵循最左匹配原则。最后提到了优化SQL的工具，如EXPLAIN和慢查询日志分析。

一、索引

索引分类：主键索引、唯一索引、普通索引、组合索引、以及全文索引（elasticsearch）

主键索引

非空唯一索引，一个表只有一个主键索引；在 innodb 中，主键索引的 B+ 树包含表数据信息

PRIMARY KEY(key)

唯一索引

不可以出现相同的值，可以有NULL值

UNIQUE(key)

主键索引和唯一索引的差别：

唯一索引可以有一个NULL值，只要整体不重复就行了。而主键索引，是非空唯一索引。

普通索引

允许出现相同的索引内容

INDEX(key)
-- OR
KEY(key[,...])

组合索引

对表上的多个列进行索引

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

比如有(key1,key2,key3)这个组合索引

先比较key1，如果key1相等再就比较key2,…

其他是合单个索引是类似的。非叶子节点，就存储组合索引（3个值），叶子节点存储(3个值+数据/主键索引)

全文索引

将存储在数据库当中的整本书和整篇文章中的任意内容信息查找出来的技术；关键词 FULLTEXT; 在短字符串中用 LIKE %

在全文索引中用 match 和 against

也就是说，全文索引通过关键字，可以将整篇文章给搜索出来

主键选择

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

如果显示设置 PRIMARY KEY ，则该设置的key为该表的主键；

如果没有显示设置，则从非空唯一索引中选择；

只有一个非空唯一索引，则选择该索引为主键；

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

没有非空唯一索引，则自动生成一个 6 字节的 _rowid 作为主键；所有的索引都会创建一个B+树，用key值，来保证有序。

二、约束

为了实现数据的完整性，对于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;

外键在项目中不推荐使用。外键是为了保证数据的完整性，当这张表中数据删除的时候，外键关联的另一张也要删除这一行，这个事务由我们自己去定义实现。

比如下图中，DELETE代表删除，CASCADE表示和父表做同样的行为，当前父表中删除这行，子表也会删除，因此外键是具备事务性的。