阿牛将从3个方面讲起，本文讲索引，后面讲日志：
一、索引深入详解
二、redolog、binlog、undolog以及事物
三、锁
----------------------------------------------------------
在正式开始之前，我们先看看MySQL的基础架构图
在上图中，分为三部分：客户端、server层、存储引擎层，每一层的用处和涉及的功能见上图。
首先我们讲的索引是什么：它帮助mysql高效获取数据有序的数据结构
记住，有序、有序、有序
说起数据结构，我们常见的部分数据结构有：
二叉树、红黑树、B-树、B+树这几种
那么我们99.99%的人可能都知道MySQL索引使用的数据结构是B+树，那么MySQL为什么要选择B+树作为索引的数据结构呢？
1、为什么不用平衡二叉树
如果我们使用二叉树：假如有一棵 100 万节点的平衡二叉树，树高 20。一次查询可能需要访问 20 个数据块。在机械硬盘时代，从磁盘随机读一个数据块需要 10 ms 左右的寻址时间。也就是说，对于一个 100 万行的表，如果使用二叉树来存储，单独访问一个行可能需要 20 个 10 ms 的时间。
这时候是不是觉得二叉树不适合，简直是太差了
2、为什么不用红黑树
查找时间和普通二叉搜索树几乎完全一样,因为在查找的过程中并没有应用红黑树的特征.只是增加了每一个结点的存储空间来存储颜色;
插入和删除操作和普通的二叉搜索树时间上的开销略有增加,因为要执行颜色变换和旋转.
所以红黑树也不适合
附上红黑树的特性:（1）每个节点或者是黑色，或者是红色。（2）根节点是黑色。（3）每个叶子节点（NIL）是黑色。[注意：这里叶子节点，是指为空(NIL或NULL)的叶子节点！]（4）如果一个节点是红色的，则它的子节点必须是黑色的（2个红色的节点不能在一起）。（5）从一个节点到该节点的子孙节点的所有路径上包含相同数目的黑节点。[这里指到叶子节点的路径]
3、为什么不用B-树
InnoDB页的大小默认是16K，页是InnoDB的最小存储单元（比如元素2、4所在的存储单元；567所在的存储单元），每个单元里面又有若干元素，那么在一页中，每个元素越大，能容纳的元素个数越小，结合上图data越大，每页能容纳的元素越少，树的高度就越高，假设每个data的大小为1K（这里为了方便计算），那么页最多16行数据（实际不会达到16，因为除了主键还有指针占用空间，为了方便统计，这里先忽略不计），那么一个三层高的数，能容纳多少行数据呢？答案是：16*16*16=4096，那5层高呢，也才区区百万条数据，试想一下，查一条数据，5次随机磁盘IO，性能肯定不太高。另外B-树无法很好的支持范围查找。
（为防止抠字眼，这里说明一下，如果你是通过类似Navicat等工具客户端尝试告诉我很快，那请你离开互联网公司吧，去做做管理后台）
4、为什么用B+树
下面我们看看B+树索引结构图
从图中可以看到，data数据是在叶子节点上，不在非叶子节点，这一点和B-树有很大的不同。目的是非叶子节点能存更多的数据。--Innodb存储引擎时，这里的data指的是除主键外的其他所有的数据
如果我们用的是自增主键类型是bigint类型，那么索引元素就是8B，每个索引后面都有其指向的下一页的指针6B，我们就可以看做一个索引元素14B，那么根据上面提到过的，一页16KB可以存放的个数约为16KB/14B=1170
那三层b+树能存储多少行数据呢，就是1170*1170*16=2000万，如果有人问：为什么MySQL单表数据超2000万性能下降严重？你可以把这个讲给他听
另一方面，各位应该已经注意到，叶子节点的数据是全部的数据，并且是有序排列，每一个Innodb页之间是双向链表连接而成（Innodb页中记录之间是单向链表，后期文章会详细将），基于这种特性，范围查找非常方便。
什么是聚集索引呢？
如上图，索引和数据在一棵索引树上，叫做聚集索引，也叫聚簇索引！（一张表仅有一个聚集索引）
为什么这么设计这里不做展开，后续会有专题来解答
什么是非聚集索引？
如上图，叶子节点的值是主键，为什么不和聚集索引一样，是记录剩余的属性值，主要考虑的是空间优化以及一致性
什么是复合索引？
上图可以看出，name和age为符合索引，先用name排序，如果一样的话再用age排序，最终叶子节点是有序的。
基于上面的内容，我们知道增加索引可以帮助我们快速获取数据，但是有时候虽然我们定义了索引，如果用法不对也可能会失效，或者效果不好
如下面的案例分析
上面的案例分析，当limit足够大的时候，虽然我们只需要20行，但是我们扫描的行数太大，查询效率基本上没怎么提高。优化过程2、3用到了覆盖索引的特性，减少回表，大大提升了效率；优化过程4则是利用了主键是连续的特性，只要记录上一次查询返回最大的id，然后继续查20条。
上述的隐式转换各位都知道了，原理就是如果字段类型和值类型不一致时，MySQL需要将每一行的数据转换为值一样的类型再进行比较，这样就相当于全表扫描，索引失效
小知识&答案：
1、为什么主键最好选择数字型？
答：数值类型占用空间小，每个Innodb页能够存储更多的数据，降低所引述的高度，加快查询速度；另外数值的比较比字符串比较更快。
2、为什么主键最好要自增或者趋势递增？
答：因如果不是递增，MySQL为了保证索引有序，将伴随着Innodb的也分裂以及记录的移动，高并发情形下，严重影响性能，比较常见的错误是用（uuid做主键）
3、为什么有最左原则？
答：因为复合索引是按照字段顺序排列的，如果where中缺少复合索引前1列或者前几列，MySQL将无法知道索引节点中前多少位代表第一个字段或者前几个字段，这样就无法走索引，最终全表扫描。
4、为什么写sql尽量使用索引覆盖？
答：使用索引覆盖，减少回表，减少随机磁盘IO，高并发情况下，索引覆盖用法极大提高性能。
5、通过delete删除数据可以释放空间吗？
答：可以也不可以；不可以是一个Innodb页存在很多条数据，不是一条，如果只删除某些数据，mysql为保证性能不会将数据来回移动，删除的数据所在的空间不会被释放；可以是因为如果一整个Innodb页被删除，那么它所占的空间会被释放。
6、复合索引怎么选择字段？
答：选择性比较大的字段在前，选择性小的字段在后。比如学生信息表，我们可以将name放在前面，是重名的很少，区分度更高，一样年龄的很多，区分度不大。
下期预告：redolog、undolog、binlog；事物是怎么保证一致性的
首发自：
https://mp.weixin.qq.com/s?__biz=MzU3ODA4NTc2Ng==&mid=2247484540&idx=1&sn=52424a1798db586def3fd222fe530c8c&chksm=fd7bf295ca0c7b83ff807cfdcc82badd84fba20b5bdadf418810c8b1590edcf0a53d18961a0e&token=499379208&lang=zh_CN#rd
网友评论