精通MySQL从数据结构开始

推翻MySQL重学，由深到浅，由浅到深

• 数据结构
• EXPLAIN关键字
• 查询优化
• 事务与数据库锁

数据结构

• 为什么要有数据结构？
  如果没有数据结构我们的程序会怎样？是不是我一个List（这里提一下ArrayList底层是数组，数组也属于数据结构）如果没有数据结构我们的程序其实就只剩下变量了，抛去我们一个类上千条代码不说，当从堆来看，我们的变量如果是String类型的话我们的堆其实很容易就内存溢出导致fullGc的，如果fullGc了我们的程序是相当于在清理垃圾，那么你这个程序其他的事都不能做了，为什么fullGc了我们程序不做其他事呢？很形象的一个比喻：你们家垃圾桶满了，垃圾都掉出来了，你去搞卫生，这时候你旁边另一个人嗑瓜子，你在搞卫生他在吐瓜子壳，垃圾桶满了，垃圾就掉出来了，这时你会不会把那个人的嘴巴拿胶带绑住不给他嗑瓜子吐瓜子壳的机会，等你把地上的垃圾清理掉了之后，把垃圾桶的垃圾清空后你在把他嘴巴解开，让他继续嗑瓜子呢。（这里需要注意，String是引用变量，他存放的是JVM中堆中的）

• 二叉树
  二叉树：二叉树就是将你要存的数据做一个比较，先存一个数据作为根节点，然后在做插入操作的话是先去跟这个根节点做比较，比根节点小的放在左边，大的放在右边。
  优点：解决了我们没有数据结构时，大量引用对象占用大量堆内存空间导致频繁fullGc导致我们程序无法正常使用，用户体验不良好。
  缺点：这棵树他是固定根节点的，所以我们在找一个有序数据的时候，比如1,2,3,4,5,6我找5的时候要经过的io操作比较多。因为他找的时候，实际上是找到你内存地址指针，然后在去你的内存中拿数据，所以，你if(tree = 5) 这个时候其实是从一开始拿到内存中地址的数据跟你这个5做一个比较，依次比较，这样是非常消耗磁盘性能的，而且我们的磁盘写入写出的时候性能远远没有我们运行内存快的。所以数据量大的时候不建议使用二叉树。概括来说，二叉树，如果存大数据量的时候高度不可控，找有序数据的时候，io操作太多。
  

• 红黑树
  红黑树是在二叉树的基础上增加了一个平衡的功能，你插入1,2,3的时候他默认选择最中间的元素作为根节点，达到一个平衡的效果，这样一来，我们需要去找一个自增的数据，1,2,3,4,5,6这样的数因为有一个平衡的效果，我只需要拿出我需要找的数据跟根节点做一个对比，如果比根节点大我就往右边找，如果比根节点小我就往左边找，提高了查找的效率。
  优点：提高了查找效率。
  缺点：树的高度不可控，如果我有大量数据的时候，我需要一直往下找，才能找到我想要的数据，是很费时间的。上面说的提高查找效率只是相对于二叉树。
  

• Hash表
  Hash表实际上是将你存放数据的内存地址，插入到我的Hash表中，他只需要经过一次Hash运算就能知道你那个数据存放的地址了。所以他在找数据的时候是所以数据结构中最快的，没有之一。MySQL也有Hash索引，大家在建立索引的时候会发现一个叫Hash一个叫Tree
  优点：查找速度快。
  缺点：表中数据是无序的，我入过在SQL中查找一个范围的数据是不实用的。
  

• B-Tree
  B-Tree就是在红黑树的基础上优化了树的一个高度，因为红黑树他插入数据是往下面加的，B-Tree他在插入的时候，他会根据你树的高度来平衡你的数，一般控制在3-4行。比如说你达到了3-4行的时候，他做一次平衡操作。平衡时，他将你平衡过来的数据往横向增加，不会再往下面增加了。
  优点：B-Tree做了一个高度的平衡，可以解决数据量较大的情况。
  缺点：B-Tree在存数据的时候是每个节点都有自己的数据，因为我们MySQL的索引是控制在16kb，如果你用B-Tree作为索引底层的话，其实算下来一棵树存的数据并不是很大。所以他达到一定的大小时，他会从新开辟一个索引树给你去使用，那么我大数据的时候，我要找的数据比较靠后，我是不是得扫描几个索引树？不利于查找效率。并且不适合做范围查询，看下图可以看到，如果我要找大于1的数据我改怎么找呢？
  

• B+Tree
  B+Tree是在B-Tree上的数据存储节点上做了一个优化，优化成为，不是子节点不存数据，并且为了兼容范围查询，他把节点改成了冗余的，其实不单单是从为了兼容范围查询来说，因为他需要让我这一棵索引树能存储的数据更多，所以他设置成子节点存储数据，子节点冗余来说的话只是MySQL对于B+Tree做的一个优化，这里我们称为变种B+Tree。为甚需要子节点冗余呢？其实回过头来想想，如果我设置的是子节点才存数据，那么我子节点不冗余，我的数据是不是存储就丢失了呢？所以MySQL大叔考虑的非常周到。让我们的子节点冗余
  优点：相对于B-Tree来说，我这颗索引树能存储的数据跟多，更加良好的支持范围查询。
  

  聚集索引、非聚集索引和辅助索引

• 聚集索引
  聚集索引存在于Innodb存储引擎，原理：在你创建表的时候选择innodb存储引擎，其实在底层是将表结构数据存放在一起的，另外还有一个文件是存储你的索引数据结构的。在你创建表的时候，MySQL为了更好的维护这张表，如果你没有创建一个Id自增列，那么MySQL会默认给你生成一个隐藏的id列作为主键自增列，为了更好的可读性，我们一般在建表的时候都会选择建立一个自增列的主键，这样MySQL就会给我们选择id列来建立主键索引也就是聚集索引，聚集索引的存放规则就是利用B+Tree来进行维护索引。如下图所示，同时，他id列下面的data元素是我们表中除了id列以外其他字段的所有字段数据。为什么不能使用uuid作为主键呢？第一，uuid是varchar字符串类型，占用空间比int类型大。第二uuid不能完全满足主键规则，可能会有重复的uuid。
  

• 非聚集索引
  非聚集索引，就是将表结构、数据，索引全部分离开来。非聚集索引典型的代表就是MySAM了。那么他在建立索引时，其实也是生成一个主键索引来维护，但是他的data元素，存放的是我们数据文件的内存地址指针。如下图所示，跟聚集索引一样，是将除id列剩下的所有字段放到索引数据结构中来。
  

• 辅助索引
  辅助索引，其实就是我们在已经创建好的表上面建立的索引。一般这类索引创建于Where条件使用比较多的字段上，不宜多建，因为如果你建立的索引太多的话，你做增删改查的时候，MySQL不仅仅是将你的数据插入到表中，他还需要去维护多颗索引树。

• 覆盖索引
  覆盖索引，何为覆盖索引？覆盖索引其实就是在你的查询语句中，你要查的字段都存在于索引中。比如我又id , name ,age,time 这四个字段。id列有一个主键索引，name，age两个字段是联合索引（联合索引下面有讲）那么我SQL语句是Select id , name ,age from demo的时候，这三个字段都存在于我们的索引树中，所以他走的是联合索引。执行原理其实就是id列放弃走主键索引，默认执行联合索引，直接在联合索引中拿数据。

• 联合索引
  联合索引就是一条创建索引的SQL语句上面有多个字段存在于索引中。什么意思呢？就是在你crate index时有name ,age 这两个字段，那么他创建索引时是将这两个 字段一起建立索引的。那么我们在使用索引的时候需满足最左前缀原则，就是说你在创建索引的时候是name在前面还是age在前面。如果name在前面那么你在用索引的时候你就要先where name = 'test’然后在and age = 18 。相反则是先age在name。联合索引结构如下图所示