MYSQL索引机制

2022-11-22 12:18:58

目录

约束

主键索引

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

 PRIMARY KEY(key)

唯一索引

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

 UNIQUE(key)

普通索引

允许出现相同的索引内容

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

组合索引

对表上的多个列进行索引

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

最左匹配原则

对于组合索引，从左到右依次匹配，遇到 > < between like 就停止匹配
只有满足了左边的匹配条件，才会尝试匹配右边的匹配条件（匹配条件需要使用组合键中的列进匹配， 优化器将自动将匹配条件今天调整，按照组合键的顺序进行排布，未在组合键中的列放到后面）

全文索引

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

辅助索引

叶子节点不包含行记录的全部数据；辅助索引的叶子节点中，除了用来排序的 key 还包含一个bookmark ；该书签存储了聚集索引的 key

-- 某个表 包含 id name lockyNum; id是主键，lockyNum存储辅助索引;
select * from user where lockyNum = 33;

覆盖索引

避免回表查询，直接从节点中获取数据，参考辅助索引

聚集索引

按照主键构造的 B+ 树；叶子节点中存放数据页；数据也是索引的一部分

innodb表结构

  1. frm文件，存储表信息
  2. ibd文件，存储索引信息， 使用B+树进行组织

查询模式

辅助查询 ： 叶子节点中存储索引数据+数据信息， 找到索引所在节点，即获得数据信息

非聚集索引（辅助索引）

myisam表结构

  1. frm文件，存储表信息
  2. myd文件，存储数据信息，使用堆表进行组织
  3. myi文件，存储索引文件， 使用B+树进行组织，存储索引以及该行所在myd文件中的地址

查询模式

回表查询 ： 例如select * from table where id=9， 在myi中查找到id为9，从该索引中拿到数据行所在myd文件的位置，再去myd文件中读取。

索引失效

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

索引原则

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

约束

为了实现数据的完整性，对于innodb，提供了以下几种约束，

  1. primary key 主键约束
  2. unique key 唯一约束
  3. foreign key
  4. default
  5. 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;

约束与索引的区别

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

索引实现

索引存储

  1. 使用B+ 树，（多路平衡搜索树），因为改树能够提供一个稳定的搜索时间复杂度，该树的平衡度比红黑树更加强。它所有的叶子节点都在同一层，每条树的链路都是一样的高度。

  2. B+树是基于B树的一个数据结构，B树的讲解在https://blog.csdn.net/qq_34954047/article/details/120771887

  3. 相较于B树，B+树将每个叶子节点进行前后链接，换言之，我们可以从每个叶子节点，直接进入与它相邻的两个叶子节点，而非回到其对应的父节点。

  4. innodb由段、区、页组成；段分为数据段、索引段、回滚段等；区大小为 1 MB（一个区由64个连续页构成）；页的默认值为16k；页为逻辑页，磁盘物理页大小一般为 4K 或者 8K；为了保证区中的页的连续，存储引擎一般一次从磁盘中申请 4~5 个区

  5. 由于B+树要求每个节点至少存储两个数据。如果一个数据就超过了16K，那就将之拆分为两个节点

  6. 阿里对于数据库的建议指出：超过500W的数据，最好将之分表，因为数据太多将严重影响读写速度

innodb体系结构

MYSQL索引机制

Buffer Pool

Buffer pool 缓存表和索引数据；采用 LRU 算法（原理如下图）让 Buffer pool 只缓存比较热的数据
MYSQL索引机制

Change buffer

Change buffer 缓存非唯一索引的数据变更（DML操作），Change buffer 中的数据将会异步merge 到磁盘当中
MYSQL索引机制

上一篇：MySQL索引（InnoDB）


下一篇：灵魂摆渡 bgm总结