mysql索引

二叉搜索树、N叉树

页分裂：B+树的插入可能会引起数据页的分裂，删除可能会引起数据页的合并，二者都是比较重的IO消耗，所以比较好的方式是顺序插入数据，这也是我们一般使用自增主键的原因之一。

页分裂逆过程：页合并，当删除数据后，相邻的两个数据页利用率很低的时候会做数据页合并

主键索引：key：主键，value：数据页，存储每行数据

非主键索引：key：非主键索引，value：主键key，导致回表

最左匹配：优先将区分度高的列放到前面，这样可以高效索引，

最左匹配原则遇到范围查询就停止匹配，范围查询(>、<、between、like)为什么？因为出现范围匹配后，后面的索引字段无法保证有序，局部有序失去，顺序失去则无法提高查询效率

SELECT * FROM table WHERE a IN (1,2,3) and b >1

如何建立索引？

还是对(a，b)建立索引，因为IN在这里可以视为等值引用，不会中止索引匹配，所以还是(a,b)!

索引组织表

索引用页存储：key【10】-point【6】，通过调整key大小，当页大小固定的情况下，通过调整key大小，使得N叉树变化；

如key 10, point 6则单个索引16字节，页大小为16k，则页面总共可以存储1024个索引，即N大小

覆盖索引: 二级索引的信息已经存在想要的列，例如主键

如果现在有一个高频请求，要根据市民的身份z号查询他的姓名，这个联合索引就有意义了。它可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间。

索引下推优化：可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

整理索引碎片，重建表：alter table T engine=InnoDB

  首先是看key的大小，另外是数据页的大小，如果需要改变N，则需要从这两个方面做改动；

一个innoDB引擎的表，数据量非常大，根据二级索引搜索会比主键搜索快，文章阐述的原因是主键索引和数据行在一起，非常大搜索慢，我的疑惑是：通过普通索引找到主键ID后，同样要跑一边主键索引，对于使用覆盖索引的情况下，使用覆盖索引可以直接解决问题

https://blog.csdn.net/itworld123/article/details/115144202

https://time.geekbang.org/column/article/69236

https://zhuanlan.zhihu.com/p/334684710

https://www.cxyzjd.com/article/pyzhizhuren/88431380

https://www.jianshu.com/p/4277d9dd0a9f

https://www.cnblogs.com/rjzheng/p/12557314.html

https://mengkang.net/1302.html

https://note.cser.club/database/bi-xu-le-jie-de-mysql-san-da-ri-zhi-binlogredo-log-he-undo-log

https://cloud.tencent.com/developer/news/44861

首先说说索引的 优点 ：最大的好处无疑就是提高查询效率。有的索引还能保证数据的唯一性，比如唯一索引。

而它的 坏处 也很明显：索引也是文件，我们在创建索引时，也会创建额外的文件，所以会占用一些硬盘空间。其次，索引也需要维护，我们在增加删除数据的时候，索引也需要去变化维护。当一个表的索引多了以后，资源消耗是很大的，所以必须结合实际业务再去确定给哪些列加索引。

再说说索引的基本结构。一说到这里肯定会脱口而出：B+树！了解B+树前先要了解二叉查找树和二叉平衡树。 二叉查找树 ：左节点比父节点小，右节点比父节点大，所以二叉查找树的中序遍历就是树的各个节点从小到大的排序。 二叉平衡树 ：左右子树高度差不能大于1。B+树就是结合了它们的特点，当然，不一定是二叉树。

为什么要有二叉查找树的特点？？ 因为查找效率快，二分查找在这种结构下，查找效率是很快的。 那为什么要有平衡树的特点呢？ 试想，如果不维护一颗树的平衡性，当插入一些数据后，树的形态有可能变得很极端，比如左子树一个数据没有，而全在右子树上，这种情况下，二分查找和遍历有什么区别呢？而就是因为这些特点需要去维护，所以就有了上面提到的缺点，当索引很多后，反而增加了系统的负担。

接着说B+树。 它的结构如下 ：

可以发现，叶子节点其实是一个 双向循环链表 ，这种结构的好处就是，在范围查询的时候，我只用找到一个数据，就可以直接返回剩余的数据了。比如找小于30的，只用找到30，其余的直接通过叶子节点间的指针就可以找到。再说说其他特点： 数据只存在于叶子节点 。当叶子节点满了，如果再添加数据，就会拆分叶子节点，父节点就多了个子节点。如果父节点的位置也满了，就会扩充高度，就是拆分父节点，如25 50 75拆分成：25为左子树，75为右子树，50变成新的头节点，此时B+树的高度变成了3。它们的扩充的规律如下表，Leaf Page是叶子节点，index Page是非叶子节点。

再说说B树 ，B树相比较B+树，它所有节点都存放数据，所以在查找数据时，B树有可能没到达叶子节点就结束了。再者，B树的叶子节点间不存在指针。

最后说说Hash索引 ，相较于B+树，Hash索引最大的优点就是查找数据快。但是Hash索引最大的问题就是不支持范围查询。试想，如果查询小于30的数据，hash函数是根据数据的值找到其对应的位置，谁又知道小于30的有哪几个数据。而B+树正好相反，范围查询是它的强项。

附录： Hash到底是啥？？ 哈希中文名散列，哈希只是它的音译。 为啥都说Hash快？？ 首先有一块哈希表（散列表），它的数据结构是个数组，一个任意长度的数据通过hash函数都可以变成一个固定长度的数据，叫hash值。然后通过hash值确定在数组中的位置，相同数据的hash值是相同的，所以我们存储一个数据以后，只需O(1)的时间复杂度就可以找到数据。 那hash函数又是啥？？ 算术运算或位运算，很多应用里都有hash函数，但实际运算过程大不一样。这是Java里String的hashCode方法：

publicint hashCode() {

}

还有一个问题，hash函数计算出来的hash值有可能存在碰撞，即两个不同的数据可能存在相同的hash值，在MySQL或其他的应用中，如Java的HashMap等，如果存在碰撞就会以当前数组位置为头节点，转变成一个链表。

说到这里也清楚了为啥Java中引用类型要同时重写hashCode和equals了。两个对象，实例就算一模一样，它们的hash值也不相等， 为啥不相等？？ 默认的Object的hashCode方法会根据对象来计算hash值的，实例相同，但它们还是两个不同的对象啊，所以我们重写hashCode时，最简单的方法就是调用Object的hashCode方法，然后传入该引用类型的属性，让hashCode方法只根据这几个属性来计算，那么实例相同的话，它们的hash值也会相等。等hashCode比较完后，如果相等再比较实例内容，也就是equals，确保不是hash碰撞。

索引的分类

如果我们指定了一个主键，那么这个主键就是主键索引。如果我们没有指定，Mysql就会自动找一个非空的唯一索引当主键。如果没有这种字段，Mysql就会创建一个大小为6字节的自增主键。如果有多个非空的唯一索引，那么就让第一个定义为唯一索引的字段当主键，注意，是第一个定义，而不是建表时出现在前面的。

对于辅助索引来说，它们的B+树结构稍微有点特殊，它们的叶子节点存储的是主键，而不是整个数据。所以在大部分情况下，使用辅助索引查找数据，需要二次查找。但并不是所有情况都需要二次查找。比如查找的数据正好就是当前索引字段的值，那么直接返回就行。这里提一句，B+树的key就是对应索引字段的内容。

而辅助索引又有一些分类：唯一索引：不能出现重复的值，也算一种约束。普通索引：可以重复、可以为空，一般就是查询时用到。前缀索引：只适用于字符串类型数据，对字符串前几个字符创建索引。全文索引：作用是检测大文本数据中某个关键字，这也是搜索引擎的一种技术。

注意，聚集索引、非聚集索引和前面几个索引的分类并不是一个层面上的。上面的几个分类是从索引的作用来分析的。聚集、非聚集索引是从索引文件上区分的。主键索引就属于聚集索引，即索引和数据存放在一起，叶子节点存放的就是数据。数据表的.idb文件就是存放该表的索引和数据。

辅助索引属于非聚集索引，说到这也就明白了。索引和数据不存放在一起的就是非聚集索引。在MYISAM引擎中，数据表的.MYI文件包含了表的索引， 该表的 叶子节点存储索引和索引对应数据的指针，指向.MYD文件的数据。

索引的几点使用经验

经常被查询的字段；经常作为条件查询的字段；经常用于外键连接或普通的连表查询时进行相等比较字段；不为null的字段；如果是多条件查询，最好创建联合索引，因为联合索引只有一个索引文件。

经常被更新的字段、不经常被查询的字段、存在相同功能的字段

了解mysql的索引类型的时候，我觉得按照以下4中方式划分逻辑是比较清晰的。

1.存储结构 2.物理存储 3.作用字段 4.功能

按照数据存储的结构可以分B树索引和hash索引。

又称为 BTREE 索引，目前大部分的索引都是采用 B-树索引来存储的。B-树索引是一个典型的数据结构。

基于这种树形数据结构，表中的每一行都会在索引上有一个对应值。因此，在表中进行数据查询时，可以根据索引值一步一步定位到数据所在的行。

查询必须从索引的最左边的列开始。

查询不能跳过某一索引列，必须按照从左到右的顺序进行匹配。

存储引擎不能使用索引中范围条件右边的列。

也称为散列索引或 HASH 索引。MySQL 目前仅有 MEMORY 存储引擎和 HEAP 存储引擎支持这类索引。

其中，MEMORY 存储引擎可以支持 B-树索引和 HASH 索引，且将 HASH 当成默认索引。

HASH 索引不是基于树形的数据结构查找数据，而是根据索引列对应的哈希值的方法获取表的记录行。

不能使用 HASH 索引排序。

HASH 索引只支持等值比较，如“=”“IN()”或“<=>”。

HASH 索引不支持键的部分匹配，因为在计算 HASH 值的时候是通过整个索引值来计算的。

聚集索引是按照所以把数据排好序了，所以一个表只能存在一个聚集索引，其它的都是非聚集索引。

因这个特性，聚集索引是查询数据范围的时候有很大的性能优势。

但是也需要注意的是如果频繁更新的列不适合设置为聚集索引，

原因很简单，每次更新都需要从新排序，频繁的更新给的压力也大。

如果不指定的话，默认主键为聚集索引。

一个表里除了一个聚集索引外其他的都是非聚集索引，虽然不能把数据按照索引排序，但是索引数据是可以排序的。

所以非聚集索引查询范围的时候是先找索引列的范围，再通过这个索引查询行的值。

单列索引即一个索引只包含单个列。

组合索引指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合

Primary Key（聚集索引）：InnoDB存储引擎的表会存在主键（唯一非null），如果建表的时候没有指定主键，则会使用第一非空的唯一索引作为聚集索引，否则InnoDB会自动帮你创建一个不可见的、长度为6字节的row_id用来作为聚集索引。

Key（普通索引）：是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值

Unique（唯一索引）：索引列的值必须唯一，但允许有空值。若是组合索引，则列值的组合必须唯一。

主键索引是一种特殊的唯一索引，不允许有空值。

既不是主键索引也不是唯一索引的一般索引。

FULLTEXT（全文索引）：全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。

全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。

空间索引主要用于地理空间数据类型 GEOMETRY。

下面是 mysql官网给出的几个存储引擎和索引之间的关系 。

欢迎大家的意见和交流

email: [email protected]

---