[MYSQL]整理下mysql的知识点（未完成）

本文是对网上资料的整理。

所谓索引，就是以某个字段为关键字的B树文件。

数据库是用来存储数据的，由于数据量大，一般是存在磁盘上。索引的数据量也大，也存在磁盘上。那么，怎么高效地找到需要的数据呢？最原始的方法就是逐个比较，这种方式效率太差，所以有人想出了一个方法。根据数据计算出一个数值，存储时，将数据按这个数值的大小排序。查询时，也根据要查的数据计算出一个数值，拿这个数值比较，先取中间的值，如果大，往后找，如果小，往前找。有种数据结构就是用于这种算法的，就是二分查找树，左子节点 < 父节点 < 右子节点。

如果最小值或者最大值作为父节点，那么这颗树就是一条线性的数值了。取中间的值作为父节点，可以使树的高度最小，这颗树叶越平衡，平衡指左右子树的高度的相差程度。一颗二分查找树的高度越小，查询效率越高（树的节点只存储一个数据的情况下，如果一个节点存了所有数据，高度只有1，这和链表就没区别了），或者说越平衡，查询效率越高。红黑树，是一颗具有自平衡特性的二叉查找树，所以如果数据都存储在内存中，红黑树的查询效率应该是最高的。比如HashMap桶中，如果个数超过8个就会变为红黑树，增加查询效率。PS：红黑树允许正负一内的不平衡，牺牲了平衡性来减少自平衡所需的旋转次数，任何不平衡都会在三次旋转内完成。严格平衡的树叫AVL树。

但是，不巧，数据库的索引是存储在磁盘上的，磁盘的读取效率比内存差超多，比起数据结构带来的性能提升，应优先考虑减少磁盘载入内存的次数。于是就出现了B树，B树是多路搜索树，一个节点允许记录多条索引，磁盘每次读取都是以页为单位的，数据库的设计者巧妙地将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入节点（数据库在读取内存的时候，发现想要的数据不存在，便会触发一个缺页异常，让系统从磁盘中读取）。可以这样理解，B树的一层只会选取一个节点进行比较，一个节点读取一次IO，那么一层读取一次IO，要减少IO次数，就是要减少树的高度。

B树和B+树的区别：B树，又叫B-树，B树的叶子节点和非叶子节点，都会存储索引和数据。而B+树的非叶子节点只存储索引，所有的数据都存储在叶子节点，由于非叶子节点把本来存储数据的空间用来存储索引，B+树相比B树，高度会更小。所以Mysql默认采用了B+树结构存储索引。

B+树的叶子节点，还比B树多了一个指针，指向下一个叶子节点。这样使得mysql的范围查询变得简单。慢速遍历，指的就是用叶节点指针进行遍历。

聚簇索引：术语“聚簇”表示数据行和键值在物理地址上连续存储，实际的结构是数据行和键值交叉存储。因为物理地址连续，大大增加磁盘IO的效率。更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。地址顺序与主键顺序一致。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行-页分裂。

除聚集索引之外的所有索引都称为 辅助索引。非聚簇索引（二级索引）的叶子节点包含的是指向主键的指针和主键；聚簇索引的叶子节点包含的是主键和其对应的数据行。因此，非聚簇索引实际上查询了2次树，先查询主键再查询数据记录的过程叫回表查询，mysql可以通过启用自适应hash索引（只索引热点数据），来减少2次查询。根据参数得到的hash索引直接指向数据行。innodb的hash索引是建在内存中的，成为自适应索引，底层依然是btree索引。聚簇索引使用主键值当作指针会让二级索引占用更多的存储空间(原本存储的是物理地址)，换来的好处是，InnoDB在移动行时，无需更新二级索引中的这个指针。

 

建索引的几大原则(https://www.cnblogs.com/E-star/p/5667996.html)

1.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录
4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
5.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可