MYSQL的索引类型、数据结构及查找过程

Mysql索引

在数据库中创建索引会有两张表，一张是表数据，一张是索引表，表数据的每条数据都会有一个对应的磁盘地址。假设我们建立id为索引，索引表则会把id值和对应的磁盘地址对应。

Mysql索引类型

Mysql的索引有两种分类方式，一种是逻辑分类，一种是物理分类。

逻辑分类：

普通索引–>没有任何约束的索引

唯一索引–>比普通索引多一个限制，值不能重复

主键索引–>比唯一索引又多了一个值不能为null的限制

联合索引–>也叫组合索引，把多个字段作为索引创建联合索引，查询要遵循最左匹配原则。

全文索引–>匹配大的text文本，like模糊查询不会用到索引，全文索引有特定查询语法

物理分类：

聚集索引：是决定了数据在物理存放顺序的索引，如果表中有主键索引，那么聚集索引就是主键索引。

辅助索引：(二级索引)如果没有主键索引，则会尝试寻找第一个没有空值的唯一索引把他当做聚集索引来决定物理的存放顺序，如果都没有会用隐藏字段rowId当做聚集索引。

Mysql索引方法

索引的数据结构是什么？

AVL树

AVL树(平衡二叉树Balanced binary search Trees)，他是二叉树(binary search Trees)的延伸，弥补了二叉树出现链表的情况，也就是说左右子树的高度差的绝对值不能超过1，并且左右两个子树都是平衡二叉树。往平衡二叉树插入或删除数据会改变树的平衡，所以当触发平衡机制时会通过左旋或者右旋的方式来改变树的结构来保持树的平衡。

一个磁盘块里储存着：1键值 、2数据磁盘地址 、3子节点引用(left，right)

一个磁盘块大小值是16kb，但是使用AVL树可能一个磁盘只能存储几十bytes，这种方式会浪费磁盘块空间，增加IO次数，使得树状会是一种又高又瘦的结构，所以就有了B树(balanced Tree)。

平衡因子：某节点的左子树和右子树的高度(深度)差即为该节点的平衡因子，所有树上节点的平衡因子只可能是-1，0，1，如果某一节点的平衡因子大于绝对值1则说明这不是平衡二叉树。

B树

B树(Balanced Trees)多路平衡树，关键字数是N，节点拥有的子树数量称为度(Degree)：N+1，也就是说如果节点有2个(N)，则它的子树数量为N+1，所以它的子树数量应该是3个。B树的树的深度比较低，所以查询快，但当我们插入或删除操作时，会有大量页的分裂和合并的动作，消耗资源多。

B+树

B+树(B树的增强版)是mysql所使用的索引，它的特点是关键字数(N)是多少，那么度(Degree)也是多少，它的所有数据都存在叶子节点上，所以说降低了树的深度，减少了IO次数，并且它的叶子节点都是有序的，有链表的结构，和B树作对比，当在进行范围或排序操作时，B树需要做局部的中序遍历，可能要跨层访问才能拿到数据，而B+树的数据全部都在叶子节点上，不用跨层，同时有链表结构，只需要找到头和尾就能够把所有数据取出来。
比如：查找7到19-------->

Hash

Hash(mysql的另一种的索引数据结构)由于hash索引比较的是hash值，所以它只支持等值（=）的查询语句，像< > like 这些是不支持的，并且hash的数据结构还会有hash冲突链表变长的问题，所以mysql用的都是B+树。

B+树更加适合磁盘的数据结构，红黑树更适合内存的数据结构。
红黑树的应用场景，有jdk1.8的hashMap，还有TreeMap和TreeSet的底层都是红黑树的实现。
特点：
1.节点是红色或黑色。
2.根节点是黑色。
3.每个叶子节点都是黑色的空节点（NIL节点）。
4 每个红色节点的两个子节点都是黑色。(从每个叶子到根的所有路径上不能有两个连续的红色节点)

从任一节点到其每个叶子的所有路径都包含相同数目的黑色节点。

Mysql存储引擎

Mysql的存储引擎又称为表类型，是存储数据和管理数据的方式，mysql5.5以后默认使用的是InnoDB的表类型。

Mysql索引查找过程

在mysql官网上除了主键索引，其他索引都称之为辅助索引，InnoDB下只有一份完整数据，数据就是索引，索引就是数据，他的完整数据只会在主键索引下的叶子节点上存储，如果把name字段设置为普通索引，它的辅助索引的叶子节点所存储的数据则是name+主键值，通过查找到name上主键再去主键索引表里查找对应的完整数据。

总结：所以通过辅助索引查找数据，会扫描两棵B+树，多扫描的B+树称之为‘回表’没有直接用主键索引查找快。

覆盖索引：也就是当辅助索引满足要查询字段，不需要再走主键索引表的情况称为覆盖索引。

什么字段不设置索引？

离散度低的数据列不建议设置索引，好比性别字段，重复值太高，会优化器放弃使用索引。

MyISAM和InnoDB的区别？

1、InnoDB是支持事务的，Myisam(咪sen)不支持，也就是说增删改出错时myisam是不能回滚的。

2、myisam比较适合查询和插入的应用，而Innodb更适合频繁修改以及安全性要求更高的应用。

3、innodb支持外键，myisam不支持。Innodb不支持全文索引，myisam支持。

4、innodb 是不保存行数的，在查询行数时会扫描整张表，myisam则只需把保存好的行数读出来就行。

myisam表一共有三个文件，frm、myi、myd。Frm存的是表结构，myi存的是索引，myd存的是表数据，Myisam的叶子节点存的是数据磁盘指针，也就是行数据的地址。

Innodb表磁盘文件一共有两个，frm和ibd文件，ibd存的是数据也是主键索引，叶子节点存的是表数据。

Innodb必须有主键索引，没有底层也会创建主键索引，唯一索引–>rowid，通常使用整型自增作为主键索引，因为整型在比较时快。使用自增是为了避免节点出现分裂的情况，如果一个节点存放的是数据满了，但是插入的数据刚好是在这个节点的中间范围，则这个节点需要做一些列分裂的操作，消耗的性能多，如果是自增，则数据永远是往后面添加的。

mysql的表很大，但是如果命中索引的话查询速度还是毫秒级的，但是如果并发量很大，还是会变慢，涉及到磁盘吞吐的问题，解决方案使用redis。

Explain 可以查看SQL语句是否使用了索引，检索了多少行找到。
Show profiles 可以查看SQL执行的快慢。

innodb引擎的4大特性

1.插入缓冲（insert buffer)
2.二次写(double write)
3.3.自适应哈希索引(ahi)
4.4.预读(read ahead)