如何深入浅出理解MYSQL索引

什么是索引

数据库索引是一种为了加速数据表中行记录检索的数据结构。
数据库中的索引存储于磁盘中，特别是关系库中的索引。

1.mysql数据存储在哪？
磁盘
2.查询速度比较慢，一般情况卡在哪里？
IO
3.去磁盘读取数据的时候，是用多少读取多少吗？
不是
4、索引存储在哪里？
磁盘，查询数据的时候会优先将索引加载到内存中。
5、索引在存储的时候需要什么信息？需要存储什么字段值？
KEY：实际数据行中存储的值
文件物理地址
offset：偏移量
6、这种格式的数据要使用什么样的数据结构来进行存储
K-V
可实现方式：哈希表，树（二叉树，红黑树，AVL树，B树，B+树）
实际使用：B+树
7、MYSQL的索引系统中不是按照刚刚说的格式存储的，为什么？
OLAP：联机分析处理-对海量历史数据进行分析，产生决策性的影响，多指数据仓库-Hive
OLTP：联机事务处理-要求在很短的时效内返回对应的结果，指关系型数据库-mysql,oracle,db2…

如何提高IO效率
1、减少IO次数
2、减少IO量（需要什么查什么）

基本概念：

1、局部性原理：数据和程序都有聚集成群的倾向，同时之前被访问过的数据很可能再次被查询。空间局部性，时间局部性
2、磁盘预读
内存和磁盘在发生数据交互的时候，一般情况下有一个最小的逻辑单元，称之为页，datapage，页一般由操作系统决定是多大，一般是4K或者8K，而我们在进行数据交互的时候，可以取页的整数倍来进行读取，innodb存储引擎每次读取数据，读取16K。

如何设计索引系统？

MYSQL的索引系统

使用的为B+树

哈希表：

不适合的原因：
1、哈希冲突会造成数据散列不均匀，会产生大量的限行查询，比较浪费时间。
2、不支持范围，不具备排序，当进行范围查询的时候，必须要挨个遍历。
3.哈希表需要大量的内存，对内存空间要求比较高。
优点：
如果是等值查询，非常快。

在mysql中有没有hash索引？
1、memory存储引擎使用的是hash索引
2、innodb支持自适应hash
存储引擎在创建的时候自行指定

create table test(id int primary key.name varchar(10)) engine="innodb/memory/myisam"

5.7之前是myisam，现在都是innodb。

树：

二叉树

BST
Binary Search Tree
插入数据的时候必须有序，左子树必须小于根节点，右子树必须保证大于根节点。
使用二分查找来提高查询效率。

如果数据插入是递增或者递减的顺序，会引起树不平衡，退化成链表。

AVL（二叉平衡树）
通过左旋转或者右旋转让树保持平衡，每一层的数据尽可能保持平衡。
最短子树和最长的子树之差不能超过1.
为了保持平衡，再插入数据的时候必须要旋转，通过插入性能的损失来弥补查询性能的提升。

如果读写请求一样多，怎么办？
红黑树

红黑树
在AVL树之上还有变色的行为，只要不超过最短子树的两倍即可。
查询性能和插入性能近似取得平衡。
存在问题：
随着数据的插入，树的深度会变深，树的深度越深，意味着IO次数越多，IO越多，耗费的资源越多，影响数据的读取效率。

每个节点有且仅有两个分支。
将有序的二叉树变成有序多叉树。
每个节点可以存储多少个数据值？
子节点数+1

B树
degree，超过degree后会往下延伸。

实际存储表数据的时候，怎么存储呢？
KEY
完整的数据行

存储数据占用较大空间

B+树
一二层不存放data，所有的data全部放在叶子结点上。
叶子结点才存储数据，非叶子结点不存储数据。

三层能存放更多的数据。
一般情况下MYSQL的B+树为3-4层，尽可能保持3层。如果超过了数据总量就要开始进行分库分表。

创建索引的时候是用int好，还是varchar好？
key值的长度越小，消耗的空间越少，能存储的数据越多。

索引的创建跟存储引擎是挂钩的

存储引擎表示不同的数据在磁盘的不同组织形式。
frm存放的是表结构，ibd存储的是实际数据和索引。
ibd对应的引擎是inodb。

myisam存储引擎为三个
frm、MYD、MYI

聚簇索引和非聚簇索引

是否是聚簇索引取决于数据跟索引是否是放在一起的。

innodb：

只能有一个聚簇索引，但是有很多非聚簇索引。
向innodb插入数据的时候，必须要包含一个索引的key值。
这个索引的key值，可以是主键，如果没有主键，那么就是唯一键，如果没有唯一键，那么就是自生成的6字节的rowid。

innodb直接放数据

存在多个聚簇索引引起的问题：
1、mysql表中有几个索引？
至少一个
2、回表
假设有一张表，有id，name，age，gender四个字段，id是主键，name是索引列
select * from table where name = zhangsan
实现过程会通过name查询到对应id，再根据id查询整行的记录，走了两颗B+树，此时这种现象叫做回表。
当根据普通索引查询到聚簇索引的Key值之后，再根据Key值再聚簇索引中获取所有行记录。

3、索引覆盖
select id,name from table where name = zhangsan
根据name可以直接查询到id，name两个列的值，直接返回即可，不需要从聚簇索引查询任何数据，此时叫做索引覆盖。

4、最左匹配
主键-索引
联合主键-联合索引/组合索引 可能包含多个索引列
假设有一张表，有id，name，age，gender四个字段，id是主键，name，age是组合索引列（定义时确定优先）
组合索引使用的时候必须先匹配name，然后匹配age。
select * from table where name = ? and age=?用
select * from table where name = ?用
select * from table where age=?不用
select * from table where age=? and name = ? 用
mysql内部有优化器，会调整对应的顺序。

组合索引的结构
先比较第一列，再比较第二列

5、索引下推
mysql三层架构
client
server
存储引擎

select * from table where name = ? and age = ?
在没有索引下推前，现根据name从存储引擎中获取符合规则的数据，然后在server层对age进行过滤

有索引下推之后：根据name,age两个条件来从存储引擎中获取对应的数据。

表创建好之后增加索引
alter table test add index idx(name);

所有的非聚簇索引都指向聚簇索引。

myisam：

非聚簇索引

myisam存放对应的地址。