MySQL索引

引入索引之前MySQL的性能瓶颈

MySQL数据由于是持久化存储的，因此数据必然是存放在磁盘之上的，磁盘的速度相较于内存和cpu而言是十分底下的

随着时间推移，MySQL所管理的数据体量会越来越庞大，甚至达到百万、千万条数据，这种情况之下对于单条数据的查询只能采用线性遍历的方式注意匹配，**算法数据结构角度来说时间复杂度过高，IO角度来说频繁的IO操作效率低下，**在高并发的场景下可能导致MySQL服务被kill。
为了解决这种问题，需要从2个方面入手解决，第一是重新组织更优秀的数据结构的数据进行管理，第二是尽可能地减少IO次数。

MySQL页机制

索引是建立在页机制之上的，所谓页就是一块内存空间，计算机中的IO单位一般都是页，操作系统的页大小一般为4KB（即操作系统与外设进行数据交互时一次访问的空间是4KB），MySQL也有自己的页大小，这个大小通常是16KB，每一页都管理着数据（在聚簇索引模式下这些数据都是有序的），由于每次IO读取的数据量变大了，一定程度上减少了IO次数，达到了性能的初步提升。MySQL服务器会自动地对所有的页进行管理，因此页中一定需要存放部分管理信息，这些页彼此之间通过双向链表级联，页内的数据通过单链表级联，如图所示：

仅仅是这样还没有完全解决插线效率低下问题，毕竟还是需要逐一线性遍历才能找到目标数据，o(n)的时间复杂度摆在这。先前说得在聚簇索引模式下页内的数据有序的，既然有序，那么就可以有方法减少搜索区间，MySQL采用的方式是引入目录结构，在页内留出一些空间存放目录链表，每一个目录中存放着一小段数据的起始位置，这样当查询数据时就可先通过目录达到初步的筛选

如果没有目录，那么当查询数据600时，就需要从1开始查询600次，引入了目录之后，就可以先去遍历目录，从目录中得知600<=x<700的数据的起始位置，就可以直接定位到目标数据，过程只需要进行6次查询，这种差距随着数据量的增加将被显著扩大

B+树索引结构

页内引入了目录之后，查询效率的问题就不直接是遍历数据本身，而是在于遍历目录了，如果数据体量非常庞大，页中就需要管理大量的目录，如果页中的目录链表遍历完毕后仍然找不到目标，就需要去下一页进行寻找，这又是一个遍历的过程，怎么解决---->不断引入新的页，用目录再管理目录

假设现在要查询目标数据100001，先通过查询第一层的大目录快速定位到小目录100000的起始位置，筛掉大量不符合无效查询，再通过小目录进一步缩短搜索区间，最后在一个小范围遍历查询，提高效率，将这种结构扩展开来，一旦一层目录链表过长，那么就在加一层目录页管理目录，真正有效的数据只存放在最底层中通过链表级联，这就是一棵B+树（这棵B+树我们希望它又矮又胖，这样就能保证每次过滤掉的数据量越大）

B+树存储相较于B树存储的优势，B+树只有叶子节点存放有效数据，非叶子节点可以又更多的空间组织页目录，管理更多的页，并且由于B+树的叶子节点通过链表级联，对于跨页范围查询不要进行回退到上一层进行查询，而是直接通过已知的指针跳转到下一页，速度略优

聚簇索引&&非聚簇索引

**聚簇索引：**聚簇索引是一种将索引和数据捆绑到一起的组织方式，索引值一般为主键。因为B+树索引结构是在内存中维护的，因此相应的数据也会跟着载入到内存中，当进行查询时可以直接定位到物理地址。并且由于数据往往是物理上有序的，针对于范围查找的效率要高于非聚簇索引。但是一旦涉及到数据的写入，就需要为了维护其有序性和连续性对原有数据进行移动，效率低。
非聚簇索引：索引和数据不直接绑定，而是索引与数据在原始表中地址绑定，好处在于不需要对原始数据进行物理上的排序，不需要确保数据存储的一致性，对于修改操作效率较高，但是缺点在于由于空间得不连续性需要频繁得回退再通过新的地址去检测，大量的回表查询降低了查询的效率

索引操作

查看表索引:

show index from $NAME;
show keys from $NAME;

增加表索引:

alter table $NAMEaddprimarykey($COL); //建立主键索引，建表如有主键自动构建
alter table $NAMEaddunique($COL); //建立唯一键索引，建表如有唯一键自动构建
alter table $NAMEaddindex($COL); //建立普通索引
create index $INDEX on $NAME($COL);

对于聚簇索引模式，构建普通索引后叶子节点存放目标的主键而非完整数据，后续通过回表查询进行访问

删除表索引:

alter table $NAMEdropindex($COL); //删除普通或唯一键索引
alter table $NAME drop primary key; //删除主键索引
drop index $COL on $NAME;

特殊的索引类型：
复合索引:将多列属性绑定在一起充当索引值,使用于频繁地查询以某几项and条件满足，当条件满足时并且查询目标恰好为索引值时可以避免回表查询，称之为索引覆盖

create index $INDEX on $NAME($COL1,$COL2,…);
最左匹配原则：在建立符合索引时需要考虑顺序，只有当查询条件是索引从COL1开始的一段连续子列才会命中索引

函数索引:***函数索引的实现原理相当于新增了一个虚拟的列，这个列会根据函数计算结果进行填充，然后数据库使用这个计算后的列作为索引***

create index $INDEX on $NAME(lower($COL));

索引创建规则

1. 频繁作为查询目标项
2. 不经常被修改或删除
3. 唯一性较强

索引命中条件

下列情况会导致索引失效

1. where子句中使用or并且某一条件项未构建索引
2. where子句中使用like+前导通配符
3. where子句中目标项为字符串（不加引号）
4. is null查询
5. 未建立函数索引前where子句中有运算符出现