索引、主键及约束

索引分类

Mysql利用B+树来组织和存储数据，索引用于在B+树中作为key值快速地查找数据。

唯一索引
不可以出现相同的索引值，但可以有NULL值。
```
UNIQUE(key)
```
主键索引
非空的唯一索引，一个表只有一个主键索引；在 InnoDB 中，由主键索引构建的B+树包含了表的完整数据信息；
```
PRIMARY KEY(key)
```
普通索引
可以出现相同值的索引，有两种声明形式
```
INDEX(key)
-- OR
KEY(key)
```

组合索引
一个表中的多个列合起来作为一个索引

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

全文索引
将存储在数据库当中的整本书和整篇文章中的任意内容信息查找出来的技术；在短字符串中用LIKE %；在全文索引中用match和against；

主键的选择

InnoDB 中表是索引组织表，每张表有且仅有一个主键。确定每张表的主键符合以下规律：

1 如果显式指定了PRIMARY KEY，则这里指定的索引key为该表的主键；
2 如果没有显式设置，则从其他非空的唯一索引中选择；若有多个非空的唯一索引，则选择声明的第一个作为主键；
3 非空的唯一索引也没有，则自动生成一个 6 字节的列_rowid作为主键。

约束

InnoDB 中提供如下约束：primary key，unique key，foreign key，default, not null。

其中，外键约束foreign key是用来关联两个表的。定义外键引用的表称为子表，被引用的表为父表，如：

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

索引的实现

索引的存储

InnoDB 数据存储模型由段、区、页组成。其中段分为数据段、索引段、回滚段等；区大小为 1 MB，一个区由64个连续页构成；而页的默认大小为16k，并非物理页。整体结构如下图所示：

在这里插入图片描述

页是 InnoDB 进行磁盘管理的最小单位，可通过innodb_page_size参数来修改。B+树一个节点的空间大小就是一个页，因此数据量越小，一个节点存储得就越多，树高度往往就越小，检索时磁盘I/O的次数就相对较少。

B+树的结构示例如下：
在这里插入图片描述

聚集索引

聚集索引使用主键为 key 构造B+树，如上面的B+树示例图所示，叶子节点中存放完整的数据行记录。

辅助索引

除了主键外，有时我们需要使用其他字段来检索数据。

辅助索引使用非主键的其他 key 构造B+树，其叶子节点不包含行记录的全部数据，而是除了用来排序的 key 之外，还包含了数据对应的主键。辅助索引拿到主键后，再通过聚集索引来取得数据行记录。因为叶子节点只存储key和主键，因此辅助索引的B+树的树高一般要低于聚集索引。

验证实际采用的索引

在SQL语句前加上EXPLAIN，可以知道mysql是如何处理sql语句的。输出中的key字段指明了实际使用的索引，如果为 NULL ，则没有使用索引。
在这里插入图片描述
possible_keys：若查询涉及到字段的索引，则这些索引都会列举出来，但是不一定采纳；

实例：
在这里插入图片描述
比如对于上图这样的一个表，为其添加组合索引作为辅助索引：

ALTER TABLE `goods` ADD KEY `name_num`(`name`, `num`);

执行查询语句：

EXPLAIN SELECT * FROM `goods` where `name`='C' and `num`=54;

可以看到采用的索引是刚才定义的组合索引：
在这里插入图片描述

索引的工作原则

最左匹配原则

对于一个组合索引KEY(row1, row2, row3)，用其作为key进行检索时，从左到右依次进行匹配，可以只匹配前几个字段，也就是说它相当于建立了索引KEY(row1)、KEY(row1, row2)、KEY(row1, row2, row3)三个索引。但匹配时中间的字段不能缺失，比如筛选条件中只使用row1和row3却不使用row2，则不会使用该组合索引。

遇到针对某个字段的条件为>、<、between、like时就停止匹配。比如select * from the_table where row1=1 and row2<2 and row3=3;则匹配完row1=1和row2<2就停止匹配了，接下来只能顺序扫描去匹配row3了。

关于最左匹配原则更详细的论述可以参考这篇MySQL最左匹配原则。

覆盖索引

从辅助索引中就能找到需要的全部数据的话，那就不需要在再去聚集索引做查找了。比如组合索引中key包含的字段就已经涵盖了查询所需的全部字段。辅助索引的B+树高度一般低于聚集索引，因此这种情况下以覆盖索引进行优化更有优势。

索引失效

应该尽可能避免索引失效。

查询语句select ... where A and B 中若条件 A 和 B 中有一个不包含索引，则索引失效；
索引字段参与运算，则索引失效；例如：from_unixtime(idx) = '2021-04-30';
索引字段发生隐式转换，则索引失效；例如将 '1' 隐式转换为 1 ；
用LIKE做模糊查询时，若以通配符%开头，则索引失效；
对索引字段使用条件表达式NOT、<>、!=时索引失效；如果需要判断id <> 0则修改为idx > 0 or idx < 0；
组合索引中，若没有使用第一个字段，则索引失效。

举例：
以之前的那个实例为基础，执行EXPLAIN SELECT * FROM `goods` where `num`=54;，结果为：
在这里插入图片描述

索引的设计原则

为查询频次较?且数据量?的表建?索引；索引的选择应为使?频次较?、过滤效果好的列或者组合；
索引字段的内容应尽可能短，这样B+树每个节点包含的数据更多，可降低树高，减少磁盘IO操作；
如果索引必须使用很长的动态字符串，则应考虑使用前缀索引；
对于BLOB，TEXT，VARCHAR类型的列，必须要使用前缀索引，因为mysql不允许索引这些列的完整长度，使用该方法的诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长。如：alter table user add key(name(4));
对于组合索引，要考虑最左侧匹配原则和覆盖索引；
尽量选择区分度?的列作为索引；该列的值相同的越少越好；
可以使用这个语句来筛选：select count(distinct idx) / count(*) from table_name;，结果越大越好，说明重复的少。
尽可能扩展索引，在现有索引的基础上，添加复合索引；
尽量不要使用select *，应指定需要的字段；
作为索引的列应尽量设置为非空；