深入解析MySQL索引与约束，提升数据库性能的秘诀

“好事”发生

开始之前推荐一篇实用的文章：《借助AI助手如何高效排查SQL问题》，作者：【努力的小雨】。

https://cloud.tencent.com/developer/article/2471390

该文结合一个项目的案例，介绍如何借助AI代码助手来快速定位和解决bug，帮助你更高效地提升工作效率，节省调试时间，避免掉入冗长的bug修复死循环中；是一篇非常有用的AI实战教程。

接下来开始我们的正文。

一、索引

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

1.1、索引分类

按照数据结构分为： （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[,...]);

1.2、主键选择

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

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

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

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

1.3、索引的代价

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

图片

二、约束

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

2.1、外键约束

外键用来关联两个表，来保证参照完整性；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。

2.2、约束与索引的区别

创建主键索引或者唯一索引的时候同时创建了相应的约束；但是约束是逻辑上的概念；索引是一个数据结构既包含逻辑的概念也包含物理的存储方式。

三、索引使用场景

（1）使用where条件判断时，会使用索引。 （2）使用group by分组查询时，会判断后面的列是否创建了索引，如果创建了就会使用索引对应的B+树。 （3）使用order by时，会判断后面的列是否创建了索引，如果创建了就会使用索引对应的B+树。

四、不要使用索引的场景

（1）没有涉及到where、grop by、order by的使用，不用创建索引。 （2）区分度不高的列，没必要使用索引。 （3）经常修改的列，不要创建索引；因为维护代价太高。 （4）表的数据量少，没必要创建索引。

五、总结

一定要确定一个主键索引的原因是 主键索引对应的是聚集索引B+树，所有的数据要存储在主键对应的B+树中。