索引整理
索引科普
1.索引是存储在磁盘中的，不是存储在内存中的，索引一定会持久化磁盘
2.我们的电脑持久化存储采用磁盘，磁盘有磁道，磁道有磁头，磁头的移动是进行读取的过程，磁头移动快慢就是读取的快慢
3.由1968年P.Denning研究发现，对程序执行有以下特性
- 局部性原理
程序和数据访问都有聚集成群的倾向，在一个时间段内仅使用其中一小部分（空间局部性）
最近访问的程序代码和数据，再次访问可能性很大（时间局部性）
- 磁盘预读（为页（page）的整数倍）
页是存储器的逻辑块，操作系统往往将主存和磁盘存储分割为连续的块，这个块通常为固定大小
1968年P.Denning研究了程序执行的局部性(principle of locality)，对于程序局部性原理进行研究还有Knuth（分析的一组Fortran程序）、Tanenbaum（分析操作系统的过程）、Huck（分析通用科学计算的程序）。
4.什么是索引？
预先知识
mysql整体架构
-
索引是帮助MySQL的进行优化的，在架构图的优化器中
-
存储引擎：不同的存放位置，不同的文件格式
InnoDB: 磁盘 MyISAM：磁盘 memory：内存
MyISAM：默认表类型，它是基于传统的ISAM类型，ISAM是Indexed Sequential Access Method (有索引的顺序访问方法) 的缩写，它是存储记录和文件的标准方法。不是事务安全的，而且不支持外键，如果执行大量的select，insert MyISAM比较适合。
InnoDB：支持事务安全的引擎，支持外键、行锁、事务是他的最大特点。如果有大量的update和insert，建议使用InnoDB，特别是针对多个并发和QPS较高的情况。
聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据 InnoDB
非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行 MyISAM
聚簇索引
- .frm 存放表结构
- .ibd 存放数据文件和索引文件（在innodb下通常看不到这个文件，被整合到了ibdata1）
- .opt 是存数据库信息
我们使用innodb存储，所有都通过表空间进行存储，创建表必然生成.frm和.ibd文件，
设置不整合到ibdata1，使用分文件存储
set global innodb_file_per_table=on;
非聚簇索引
索引和数据单独一个文件：MyISAM
-
.frm：存放表结构
-
.MYI 存放索引数据
-
.MYD 存放实际数据
什么是索引：
-
索引是帮助MySQL高效获取数据的数据结构
-
索引存储在文件系统中
-
索引的文件存储形式和存储引擎有关
-
索引的文件结构
- hash
哈希表可以完成索引存储，每次在添加索引要计算指定的hash值，取模运算计算下标
适合场景：
等值查询
无序查询
hash表在使用过程中，需要将数据全部加载到内存中，比较耗费内存，不是很合适
- 树
多叉树->二叉树->avl树->红黑树->B-树->B+树
红黑树是基于avl树的，损失部分查询性能，提升插入性能，在红黑树中最低子树和最高子树之差小于2倍即可，在插入过程中，不需要进行N次旋转，而且加入变色特性，来满足插入和查询性能的平衡
B树：
1.所有键值分布在整棵树中
2.搜索可能在非叶子结点结束，在关键字全集内做一次查找性能逼近二分查找
3.每个节点最多有m个子树
4.根节点至少2个子树
5.分支节点至少m/2个子树
6.所有叶子节点都在同一层，每个节点最多有m-1个key，都以升序排列
B+树：
解决B树深度过深问题。
注意：
1.InnoDB B+Tree 叶子节点直接放置数据，如果没有主键，那么会选择唯一键，如果没有唯一键，那么会生成一个6位的row_id作为主键
2.如果创建的索引键是其他字段，那么叶子节点中存储的是主键，再通过主键进行找到相应记录，叫做回表
5.索引的分类
mysql索引的5种类型：主键索引、唯一索引、普通索引、全文索引、组合索引
- 主键索引
主键是一种唯一索引，但它必须指定为PRIMARY KEY，每个表只有一个主键
- 唯一索引
索引列值只出现一次，必须唯一，值可以空（回表，不覆盖）
- 普通索引(建议这个，不用2次)
基本索引，值可以空，没有唯一性限制（回表，会覆盖索引）
- 全文索引，MyISAM支持，Innodb在5.6之后支持（一般不会用到，公司在做搜索引擎，一般使用es、solr、Lucene）目前Lucene不用了，solr在老牌公司用，更多选择es
全文索引类型是FULLTEXT，全文索引可以在varchar、char、text类型的列上创建
- 组合索引
多列组合成一个索引
最左匹配，先去匹配左边的
一个查name，age，另一个查age
可以优化为age，name的索引
| 是否回表 | 是否覆盖 | |
|---|---|---|
| 主键索引 | 没有回表 | 不覆盖 |
| 唯一索引 | 没有回表 | 不覆盖 |
| 普通索引 | 回表 | 覆盖 |
执行计划：explain select * from xxx where xxx=xxx
6.MyISAM和InnoDB区别
MyISAM不支持事务，行锁，外键，适合大量select操作
