索引及约束

索引

• 索引是什么：一种有序的存储结构，按照单个或者多个列的值进行排序
• 索引的目的：提升搜索效率（为什么不用hash？因为还有查询范围内的值的需求）

innodb中B+树

• 多路平衡搜索树（层高低，降低磁盘io）
• 所有的叶子节点都在同一层，而且叶子节点构成一个双向链表（这样可以不需要回到根节点就能找到下一个节点，相邻的磁盘页访问速度非常快）
• B+数节点的大小都为16K，即是磁盘页大小（4K或8K）的整数倍
• 非叶子节点只记录索引信息，叶子节点记录了一行的数据

索引分类

1. 数据结构：
   1. B+树索引（磁盘索引）
   2. hash索引（内核在内存中使用，用户无法指定）
   3. 全文索引（反向索引，通过关键字索引文章，mysql通常不适合使用，可以使用elasticsearch中间件）
2. 物理存储：（对应的磁盘索引）
   1. 聚集索引（聚簇索引，主键对应的B+树）
   2. 辅助索引（二级索引，另外的B+树）
3. 列属性
   1. 主键索引
   2. 唯一索引
   3. 普通索引
   4. 前缀索引
4. 列个数
   1. 单列索引
   2. 组合索引

聚集与辅助索引

1
2
3
4
5
6
7

create table 'covering_index_t'(
   'id' int(11) not null auto_increment,
   'cid' int(11) default null,
   'name' varchar(255) default null, 
   primary key('id'), -- 对应聚集索引B+树，叶子节点包括该行所有的数据
   key 'name_cid_idx' ('name', 'cid') -- 对应辅助索引B+树，非叶子节点是辅助索引值，叶子节点只包括部分的数据（主键+辅助索引）
)

• 假设以辅助索引查询其他数据，需要先在辅助索引B+树中找到对应的主键，再去聚集索引中查找对应的数据（回表查询）
• 所以每个辅助索引都对应了一个额外的B+树，那么B+树越多DML操作需要维护的数据结构也越多，效率越低
• 每个表不要超过6个索引，即不要多于5个辅助索引（聚集索引是不能少的）

索引的代价

1. 占用空间（索引是需要创建存放在磁盘上的数据结构的）
2. 维护代价高（DML操作需要维护多个数据结构）

索引使用场景

1. where
2. group by
3. order by

• 以上操作若没有索引，即进行全表扫描，速度会很慢

没必要创建索引的情况

1. 没有使用where、group by、order by
2. 区分度不高的列（列的值大多都一样，不适合做索引）
3. 经常修改的列（需要维护多个数据结构）
4. 表数据量很小（全表索引也不会很慢）

创建索引

1. 主键索引—–primary key（key1，key2）
2. 唯一索引—–unique（key）
3. 普通索引—–index(key) or key(key[…])
4. 组合索引—–index idx(key1,key2,[…]) or unique(key1, key2[…]) or primary key(key1, key2[…])

主键选择规则

1. 如果设置了primary key，则该设置的key为该表的主键
2. 如果没有设置，则从非空+唯一索引中选择
   1. 只有一个非空+唯一索引，则选择
   2. 有多个非空+唯一索引，则选择声明的第一个
3. 没有非空唯一索引，则自动生成一个6字节的_rowid作为主键

索引实现

索引存储

• innodb 由段、区、页组成；段分为数据段、索引段、回滚段等；区大小为 1 MB（一个区由 64 个连续页构成）；页的默认值为 16k；页为逻辑页，磁盘物理页大小一般为 4K 或者 8K；为了保证区中的页的连续，存储引擎一般一次从磁盘中申请4~5 个区；
• 每个逻辑页至少存储2条行数据，假设行数据大于16K？行数据会进行拆分，由链式结构向连，但是必须保证，每个页存储大于2行数据；
  

innodb体系结构

• 背景：write写操作会先写入内核区的page buffer中，再调用fsync或sync才会真正刷入磁盘；（但是该过程page buffer仅能由内核操作，应用程序不可控）
• 解决：write操作设置O_DIRECT（直接刷盘），MySQL的存储引擎层实现Buffer Pool，用于接管内核区page buffer的工作；

Buffer Pool

• buffer pool缓存表和索引数据，且只缓存比较热的数据
• 分为5/8的新sublist和3/8的旧sublist两个链表，新加入的数据先入old链中，频繁的数据入new链中

Change buffer

• change buffer仅缓存那些非唯一索引的dml数据

1. 当发生dml操作时，先在buffer pool中查找，如果未找到，先存入change buffer中
2. 当下一次再次查找该数据位置时，从磁盘中读入buffer pool中，并将change buffer数据进行合并
3. 若没有下一次查找该数据，则由定时periodic定时将change buffer数据写入buffer pool并和磁盘数据合并

SQL优化

覆盖索引

1. 辅助索引的B+树比聚集索引B+树矮
2. 假设查询仅查询辅助索引B+树就能获得数据，无需回表查询，那么效率较高

• 启示：select语句尽量写出我们需要的字段，不要动不动就*，会造成回表查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

DROP TABLE IF EXISTS `covering_index_t`;
CREATE TABLE `covering_index_t` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(255) DEFAULT NULL,
	`cid` INT(11) DEFAULT NULL,
	`age` SMALLINT DEFAULT 0,
	`score` SMALLINT DEFAULT 0,
	PRIMARY KEY (`id`),
	KEY `name_cid_idx` (`name`, `cid`)
)ENGINE = INNODB AUTO_INCREMENT=0 DEFAULT CHARSET = utf8;
-- ENGINE = INNODB 指定存储引擎

INSERT INTO `covering_index_t` (`name`, `cid`, `age`, `score`)
VALUES
	('mark', 10001, 12, 99),
	('darren', 10002, 13, 98),
	('vico', 10003, 14, 97),
	('king', 10004, 15, 100);

SHOW INDEX FROM `covering_index_t`;

-- explain是查询执行计划
EXPLAIN SELECT * FROM `covering_index_t` WHERE `name` = 'mark'; -- 涉及到了回表查询

SELECT `name`, `cid`, `id` FROM `covering_index_t` WHERE `name` = 'mark'; -- 不涉及回表查询，属于覆盖索引

EXPLAIN SELECT `id`, `name` FROM `covering_index_t` WHERE `cid` = 1; -- 这里不会走辅助索引B+树，因为组合索引，会优先比较name，name一致才会比较cid

最左匹配原则

1. 组合索引中，先比较左边，相等再比较右边
2. 假设查询时仅写出了右边，则会进行全表查询

• 尽量扩展索引，如：

1. KEY name_cid_idx (name, cid)
2. 不要写成key name_idx(name) key cid_idx(cid)
3. 多维护一个B+树

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

DROP TABLE IF EXISTS `left_match_t`;
CREATE TABLE `left_match_t` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(255) DEFAULT NULL,
	`cid` INT(11) DEFAULT NULL,
	`age` SMALLINT DEFAULT 0,
	PRIMARY KEY (`id`),
	KEY `name_cid_idx` (`name`, `cid`)
)ENGINE = INNODB AUTO_INCREMENT=0 DEFAULT CHARSET = utf8;


INSERT INTO `left_match_t` (`name`, `cid`, `age`)
VALUES
	('mark', 10001, 12),
	('darren', 10002, 13),
	('vico', 10003, 14),
	('king', 10004, 15)

SHOW INDEX FROM `left_match_t`;

-- 作用优化器
EXPLAIN SELECT * FROM `left_match_t` WHERE `name` = 'mark'; -- 走索引

-- 优化器
EXPLAIN SELECT * FROM `left_match_t` WHERE `cid` = 1 AND `name` = 'mark'; -- and左右可以调转顺序，所以走索引

EXPLAIN SELECT * FROM `left_match_t` WHERE `cid` = 1; -- 因为先比较name才能比较cid，这里不走索引

索引下推（理解原理，不能指导具体sql编写）

• 目的：减少回表次数，提升查询效率
• 5.6以后的版本支持
• 没有索引下推：server层向存储引擎层请求数据，在server层根据索引条件判断进行数据过滤
• 有索引下推：将索引条件判断下推到存储引擎中过滤数据，最终由存储引擎进行数据汇总返回给server层

  1 2 3 4

  -- 索引下推 EXPLAIN SELECT * FROM `covering_index_t` WHERE `name` = 'mark' AND `cid` > 1; -- 没有索引下推：server层 使用WHERE `name` = 'mark'条件进行请求数据，存储引擎逐个返回数据，由server层再次判断`cid` > 1，最终汇总数据返回给客户端 -- 有引擎下推：sercer层 使用WHERE `name` = 'mark' AND `cid` > 1条件进行数据请求，存储引擎直接返回所有匹配的结果，再由server层返回给客户端

索引失效

• 即由sql语句引起了索引失效（全表查询）的情况：

1. select … where A and B 若 A 和 B 中有一个不包含索引，则索引失效；
2. 索引字段参与运算，则索引失效；例如：from_unixtime(idx) = ‘2021-04-30’; 修改：idx = unix_timestamp(‘2021-04-30’) ;
3. 索引字段发生隐式转换，则索引失效；例如：将列隐式转换为某个类型，实际等价于在索引列上作用了隐式转换函数；
4. LIKE 模糊查询，通配符 % 开头，则索引失效；例如：select * from user where name like ‘%Mark’;
5. 在索引字段上使用 NOT <> != 索引失效；如果判断 id <> 0则修改为idx > 0 or idx < 0；
6. 组合索引中，没使用第一列索引，索引失效；

索引原则（总结）

1. 查询频次较高且数据量大的表建立索引；索引选择使用频次较高，过滤效果好的列或者组合；
2. 使用短索引；节点包含的信息多，较少磁盘 IO 操作；比如：smallint，tinyint；
3. 对于很长的动态字符串，考虑使用前缀索引；
4. 对于组合索引，考虑最左侧匹配原则和覆盖索引；
5. 尽量选择区分度高的列作为索引；该列的值相同的越少越好；
6. 尽量扩展索引，在现有索引的基础上，添加复合索引；最多6个索引；
7. 不要 select *； 尽量只列出需要的列字段；方便使用覆盖索引；
8. 索引列，列尽量设置为非空；
9. 可选：开启自适应 hash 索引或者调整 change buffer；

出现了sql较慢，怎么办？

1. 找到SQL语句
   1. show processlist
   2. 开启慢查询日志
2. 分析SQL语句
   1. 索引：where、group by、order by有没有索引？
   2. sql语句：in优化成联合查询、减少联合查询（不要超过3个表）
   3. 业务类型：不要使用age字段，因为每年都要更改，存储他的生日，不要存储随着时间改变的值