MySQL之索引详解

1 mysql中索引的介绍

1.1 什么是索引

1. 索引是一种物理上的概念，是一种数据结构，每一个索引都对应一个B+树的数据结构，而约束是一个逻辑概念，为了保证数据的完整性。
2. 索引是帮助MySQL高效获取数据的数据结构。索引往往存储在磁盘上的文件中。在innodb引擎中，索引存储在idb文件中，在myisam引擎中 索引结构存储在myi 文件中。
3. 索引中包括:聚集索引，覆盖索引，组合索引，前缀索引，唯一索引等，默认都是使用B+树结构组织索引

1.2 索引的优缺点

1.2.1 优势

1. 检索:可以提高数据检索的效率，降低数据库的IO成本
2. 排序:通过索引列对数据进行排序，降低了CPU的消耗

1.2.2 劣势

占磁盘空间和降低更新表的效率

所以说 当设计一张数据表的时候，要合理搞笑的创建索引。

1.3 索引的类型

1.3.1 聚集索引

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

PRIMARY KEY(key)

1.3.2唯一索引

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

UNIQUE(key)
//创建唯一索引例程
create unique index index_name on table (column(lnegth));
alter table table_name add unique index index_name(column);

1.3.3 普通索引

允许出现相同的索引内容

INDEX(key)
-- OR
KEY(key[,...])
//创建普通索引例程
create index index_name on table(column(length));
alter table table_name add index index_name(column(length));

1.3.4 组合索引

对表上面的多个列进行索引

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

1.3.5 全文索引

将存储在数据库当中的整本书和整篇文章中的任意内容信息查找出来的技术；关键词 FULLTEXT;
在短字符串中用 LIKE % ；在全文索引中用 match 和 against ；
全文索引(elasticseach 索引就是全文索引，mysql 做全文索引不是很专业)
github上的搜索用的就是elasticseach 索引，又叫倒排索引。

create fulltext index index_name on table(column(length));
alter table table_name add fulltext index_name(column);

1.4 主键索引的选择

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

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

1.5 约束

为了实现数据的完整性，对于innodb，提供了以下几种约束，primary key，unique key， foreign key， default, not null；
foreign key 没有创建物理的特性，只是保证数据完整性的约束 。

1.5.1外键约束

外键用来关联两个表，来保证参照完整性；MyISAM存储引擎本身并不支持外键，只起到注释作 用；而innodb完整支持外键；在项目中尽量不要用外键约束。因为外键约束产生隐藏的语句很容易被别人忽视造成bug。并且外键约束时具有事务性的

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 from child where id=1；这个语句是隐藏在上一条delete语句之中的，这两个语句之间是原子的。
 

1.5.2 约束和索引之间的区别

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

2 mysql中的B+树

2.1 mysql为什么要用B+树数据结构

红黑树：平衡二叉搜索树
B+树：多路平衡搜索树
搜索树：中序遍历是有序的结构可以称之为搜索树
平衡的目的：平衡树的高度，提供一种稳定的搜索时间复杂度
红黑树和B+树的差异：一个是二叉，一个是多路 B+树是一种高度平衡的搜索树，非常平衡，叶子节点都在同一层，每 一条链路的高度都是一样的。至于mysql为什么要采用b+树。是因为二叉树的形状是瘦高的。B+树的的形是一个矮胖的形状。书的高度代表着比较key的此时，也就是代表着访问磁盘次数。

访问磁盘和访问内存在时间上面的差异。
访问一次磁盘IO 大约10ms
访问一次内存  100us

3 索引实现

3.1索引存储

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

页是 innodb 磁盘管理的最小单位；默认16k，可通过 innodb_page_size 参数来修改；
B+ 树的一个节点的大小就是该页的值；
全称：多路平衡搜索树，减少磁盘访问次数；用来组织磁盘数据，以页为单位，物理磁盘页一般为
4K，innodb 默认页大小为 16K；对页的访问是一次磁盘io，缓存中会缓存常访问的页；
特征：非叶子节点只存储索引信息，叶子节点存储具体数据信息；叶子节点之间互相连接，方便范
围查询；
每个索引对应着一个 B+ 树

平常调用IO函数访问磁盘 通常是4k或者8k。节点存储数据大小是他们的整数倍。
一个节点的大小是固定的，是一次访问磁盘数量的整数倍，B+树一个节点至少存储两行数据。
将16k 进行拆分，这两行数据都会存储6.7k的数据，因为前面还会存储其他数据，其他数据空间会存储共享表空间。
B+树的非叶子节点只存储索引信息
B+树范围查询
limit 1，5
order by limit
select * from table where id > 1 and i < 10
所有的相邻叶子节点之间是相互连接，方便不用回溯的查找。叶子节点之间存储前后叶子节点的物理地址。
非叶子节点存储的是索引信息
叶子节点存储的是具体的信息
innodb
主键索引的B+树非叶子节点 存储的索引信息，叶子节点存储的是具体的行数据
辅助索引的B+树非叶子节点存储的是索引信息，叶子节点存储的是索引信息+主键信息
innodb 在走辅助索引时，如果查找的字段是select * 或者是辅助索引中叶子节点所没有的都会回表查询。
MYISAM
frm:表的构建信息文件,创建表的时候，表的约束，字段类型，字段名，主键信息等信息
.myi文件 m:mysam引擎 d：data（数据信息） 存储数据文件 堆表 进行组织
myi 索引文件 B+树进行组织 myisam b+叶子节点 存储的是 索引和行坐在数据文件的地址。
myisam索引信息也是由B+树组织，不管是辅助索引还是主键索引。其叶子节点存储的是具体数据的物理地址，当我们通过索引信息找到具体的叶子节点，根据叶子节点中的记录的物理地址来查找具体的行数据，也就是每次都需要回表查询。
example：
select * from table where id = 9;
回表查询：通过id = 9找到具体索引，通过索引找到行所在数据文件的地址，找到地址之后，然后回到.myd数据文件中根据地址找到所对应的行数据。每次都是回表查询

3.2关于索引B+树的层高问题

B+树的一个节点对应一个数据页；B+树的层越高，那么要读取到内存的数据页越多，io次数越
多；
innodb一个节点16kB；
假设：
key为10byte且指针大小6byte，假设一行记录的大小为1kB；
那么一个非叶子节点可存下16kB/16byte=1024个（key+point）；每个叶子节点可存储1024行数
据；
结论：
2层B+树叶子节点1024个，可容纳最大记录数为： 1024 * 16 = 16384；
3层B+树叶子节点1024 * 1024，可容纳最大记录数为：1024 * 1024 * 16 = 16777216；
4层B+数叶子节点1024 * 1024 * 1024，可容纳最大记录数为：1024 * 1024 * 1024 * 16 =
17179869184;
那么关于自增id的超过最大值的情况基本上是不存在的。bugint 的范围是（-2⁶³,2⁶³-1）
假设采用 bigint 1秒插入1亿条数据，大概需要5849年才会用完索引；

3.3聚集索引和辅助索引

聚集索引
按照主键构造的 B+ 树；叶子节点中存放数据页；数据也是索引的一部分；所有的相邻叶子节点之间是相互连接，方便不用回溯的查找。叶子节点之间存储前后叶子节点的物理地址。
辅助索引
叶子节点不包含行记录的全部数据；辅助索引的叶子节点中，除了用来排序的 key 还包含一个
bookmark ；该书签存储了聚集索引的 key。辅助索引有可能会进行全表扫描。

3.4innodb的体系结构

3.4.1 buffer pool

Buffer pool 缓存表和索引数据；采用 LRU 算法（原理如下图）让 Buffer pool 只缓存比较热的数
据 ；buffer poll 最近最少使用算法，把最近最少使用的数据给换出去。
用的环形缓冲区，mysql认为的策略，先把热数据插入列表的中间。大约是链表的5/8左右

3.4.2 Change buffer

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

4 最左匹配原则和覆盖索引

4.1最左匹配原则

由多个key 构成一个组合索引
组合索引【key1，key2】，比较规则 先比较k1，k1相同才会比较k2
k1有序 k2 比一定有序。
当k1相同时，k2才会有序，遇到 < > between like 才会停止匹配

select 。。。。where col
group by col having col
order by col
join on col
explain 的主要作用再优化器阶段
对于组合索引，从左到右依次匹配，遇到 > < between like 就停止匹配；

4.2

从辅助索引中就能找到数据，而不需通过聚集索引查找；利用辅助索引树高度一般低于聚集索引
树；较少磁盘 io；

5索引失效

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
   ‘%Mark’;
5. 在索引字段上使用 NOT <> != 索引失效；如果判断 id <> 0 则修改为 idx > 0 or idx < 0 ；
6. 组合索引中，没使用第一列索引，索引失效；
7. in + or 索引失效；单独的in 是不会失效的；not in 肯定失效的；

6索引原则

1. 查询频次较高且数据量大的表建立索引；索引选择使用频次较高，过滤效果好的列或者组合；
2. 使用短索引；节点包含的信息多，较少磁盘io操作；比如：smallint，tinyint；
3. 对于很长的动态字符串，考虑使用前缀索引；
   有时候需要索引很长的字符串，这会让索引变的大且慢，通常情况下可以使用某个列开始的
   部分字符串，这样大大的节约索引空间，从而提高索引效率，但这会降低索引的区分度，索
   引的区分度是指不重复的索引值和数据表记录总数的比值。索引的区分度越高则查询效率越
   高，因为区分度更高的索引可以让mysql在查找的时候过滤掉更多的行。对于 BLOB , TEXT ,
   VARCHAR 类型的列，必要时使用前缀索引，因为mysql 不允许索引这些列的完整长度，使用
   该方法的诀窍在于要选择足够长的前缀以保证较高的区分度
4. 对于组合索引，考虑最左侧匹配原则和覆盖索引；
5. 尽量选择区分度高的列作为索引；该列的值相同的越少越好
6. 尽量扩展索引，在现有索引的基础上，添加复合索引；最多6个索引
7. 不要 select * ； 尽量只列出需要的列字段；方便使用覆盖索引；
8. 索引列，列尽量设置为非空；
9. 可选：开启自适应 hash 索引或者调整 change buffer；