整理一下近段时间学习的数据库知识点~ 🥕
说到索引，基本都是在设计数据库的时候被提及，或者是sql慢的令人崩溃的时候。的确索引能提升sql的查找速度，但是建立索引也会造成插入，删除数据消耗更多的时间。所以索引通过空间来换取时间，是一门艺术和学问。
存储引擎
InnoDB
InnoDB是我们最常用的存储引擎，他的优点提供了提供了事务处理、回滚、崩溃修复能力和多版本并发控制的事务安全，以及支持外键和自增列。显而易见，保证了以上的功能的同时，它的缺点就是读写效率较差，占用的数据空间相对较大。后续讲的索引，都是基于InnoDB引擎的。
Memory
MEMORY是MySQL中一类特殊的存储引擎。它使用存储在内存中的内容来创建表，而且数据全部放在内存中。有点类似Mc,之所以说像Mc而不是redis呢。因为它不能做到持久化，重启或关闭服务器的时候，会失去数据。Memory默认使用hash索引，后面会整理hash索引和btree索引的区别。
MyISAM
MyISAM是MySQL是MySQL比较早版本的默认存储引擎。MyISAM是MySQL中常见的存储引擎，曾经是MySQL的默认存储引擎。
需要注意的一点是MyISAM的B+Tree的叶子节点上的data，并不是数据本身，而是数据存放的地址。
聚簇索引
注意：下面讨论的都为innodb引擎。
比较规范的数据库表设计都会有一条不成文的规定，那就是给每张表一个自增主键。我们就算不按着规范，也会习惯性的加上主键。这是为什么呢?
这里我们需要知道一个概念，那就是聚簇索引。聚簇索引并不是一种单独的索引类型，而是一种数据存储方式（不是数据结构，而是存储结构）。其实当没有聚簇索引的时候，表的数据很像我们所认知的表，一行一行的排列的很整齐。但是，当表有了聚簇索引，表并不能被称之为”表”了。那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构，也就是btree结构，换句话说，就是整个表就变成了一个索引。
这也是为什么我们会需要给每张表设计一个主键。这里很容易会造成一个错误的观点，那就是主键=聚簇索引。这是错误的！
对于Innodb，主键毫无疑问是一个聚簇索引。但是当一个表没有主键，或者没有一个索引的时候呢？
- 1 如果一个主键被定义了，那么这个主键就是作为聚簇索引
- 2 如果没有主键被定义，那么该表的第一个唯一非空索引被作为聚簇索引
- 3 如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚簇索引，这个隐藏的主键是一个6个字节的列，改列的值会随着数据的插入自增。
另外我们会将主键设为自增，考究起来也是很有学问的。Innodb中的每张表都会有一个聚簇索引，而聚簇索引又是以物理磁盘顺序来存储的，自增主键会把数据自动向后插入，避免了插入过程中的聚簇索引排序问题。因为聚簇索引的排序，必然会带来大范围的数据的物理移动，这里面带来的磁盘IO性能损耗是非常大的。
所以有时候我们采用UUID作为聚簇索引是很不可取的。因为它使得聚簇索引的插入变得完全随机，使得数据没有任何聚簇特性。当UUID作为主键插入行不仅花费的时间更长，而且索引也更大，这一方面是因为主键字段变长了，另外一方面是由于页分裂导致时间变长和碎片导致的索引变大。因为主键的值是顺序的，所以Innodb把每一条记录都存储在上一条记录的后面，当达到页的最大填充因子时（innodb默认的最大填充因子是页大小的十六分之十五，留出部分空间用于以后修改），下一条记录就会写入新的页中，一旦数据按照这种顺序的方式加载，主键页就会近似被顺序的记录填满，这也正是所期望的结果。
上面提到的碎片，做一下解释。每当MySQL从列表中删除了一行内容，该段空间就会被留空，就会行成碎片。而在一段时间内的大量删除操作，会使这种留空的空间变得比存储列表内容所使用的空间更大。当MySQL对数据进行扫描时，它扫描的对象实际是列表的容量需求上限，也就是数据被写入的区域中处于峰值位置的部分。如果进行新的插入操作，MySQL将尝试利用这些留空的区域，但仍然无法将其彻底占用，所以我们有时候需要对表进行”瘦身”。具体操作如下：
1.先查看某个表所占空间，以及碎片大小。
SELECT table_name,ENGINE,table_rows,data_length + index_length length,DATA_FREE
FROM information_schema. TABLES WHERE TABLE_SCHEMA = ‘db_name’;
2.如果有大量碎片，可以进行清理，清理的频率一般每周或者每月整理一次即可。
OPTIMIZE table table_name;
ALTER TABLE table_name ENGINE=’InnoDB’; //亲测两种方式都是可以的，版本试过5.6+的和5.7+的
3.这里有个注意的点，我自己测试的时候，发现在InnoDB下碎片清理不干净。表的体积比较大的时候，DATA_FREE的值会在2M，4M，5M左右。我猜测可能是mysql为表的预留空间，或者是InnoDB下，设计表字段类型大小或者索引影响的。不过对于一些碎片体积大的，清理一下还是有很显著的效果。
以上讨论的都是基于单机的情况下，那如果在高并发工作负载下，顺序的主键插入就会造成很大的应能问题。主键的上界被称为热点，因为所有的插入都发生在这里，所以并发插入可能导致间隙锁争用，另一个热点可能是auto_increment锁机制。总而言之，就是在保持顺序的主键，会造成阻塞影响性能。
Mysql为提供了innodb_autoinc_lock_mode配置。会有三个参数可以选择：
1 tradition(innodb_autoinc_lock_mode=0) 模式:在这一模式下，所有的insert语句(“insert like”) 都要在语句开始的时候得到一个表级的auto_inc锁，在语句结束的时候才释放这把锁，由于在这种模式下auto_inc锁一直要保持到语句的结束，所以这个就影响到了并发的插入。
2 consecutive(innodb_autoinc_lock_mode=1) 模式:这一模式下去simple insert 做了优化，由于simple insert一次性插入值的个数可以立马得到确定，所以mysql可以一次生成几个连续的值，用于这个insert语句；总的来说这个对复制也是安全的这一模式也是mysql的默认模式，这个模式的好处是auto_inc锁不要一直保持到语句的结束，只要语句得到了相应的值后就可以提前释放锁
3 interleaved(innodb_autoinc_lock_mode=2) 模式：取消了auto_inc锁，所以这个模式下的性能是最好的；但是它也有一个问题，就是对于同一个语句来说它所得到的auto_incremant值可能不是连续的。
非聚簇索引
拉回正题，讲完聚簇索引，接下来就是非聚簇索引，也就是我们平时使用的常规索引。
非聚簇索引和聚簇索引一样，同样是采用btree作为索引的数据结构。索引树结构中各节点的值来自于表中的索引字段，假如给user表的name字段加上索引，那么索引就是由name字段中的值构成，如果给表中多个字段加上索引，那么就会出现多个独立的索引结构，每个索引互相之间不存在关联。 如下图：
这里先给大家安利一波chrome的插件Gliffy Diagrams，绘图相当好用，主要格调不错。^.^
看图，可以发现每次给字段建一个新索引，字段中的数据就会被复制一份出来，用于生成索引。因此，给表添加索引的同时会增加表的体积，占用磁盘存储空间。非聚簇索引和聚簇索引的区别在于，通过聚簇索引可以查到需要查找的数据，而通过非聚簇索引可以查到记录对应的主键值，再使用主键的值通过聚簇索引查找到需要的数据。所以当我们能利用聚簇索引的时候，就尽量使用聚簇索引。因为查询表的操作最终都会利用主键通过聚簇索引来定位到数据，聚簇索引中的数据指针是通往数据的唯一路径。
一般用了唯一的时候一定要注意，因为一不小心就会啪啪啪的打脸。虽然正常的逻辑下，我们的查询拿到数据最终是要通过聚簇索引。但是也是有特例的，被称为索引覆盖，也就是平时所说的联合索引或复合索引。当我们为字段建立索引以后，字段中的内容会被同步到索引之中，那我们指定两个字段，这个两个字段的内容都会被同步到索引。
百言不如一例:
create index index_score_and_name on user_info(score, name);//建立索引 姓名，分数
select name from student where score = 100;//查询分数为100分的学生名字
上面通过非聚簇索引index_score_and_name查找分数为100的叶节点的内容，因为叶节点中保存了聚簇索引的key值还有name的内容，所以不需要省去通过聚簇索引去查找数据行的步骤，直接返回name，大大的提高了查询性能。
btree
大家在建普通索引（非聚簇索引）的时候，在Innodb下往往会有两种索引方法，一种是btree，一种是hash。这里的btree是指b+tree。这里就会有个容易混淆的东西，那就是b+tree和b-tree。
b+tree 如图：
b-tree 如图：
观察两种结构，我们可以发现b-tree和b+tree最重要的一个区别就是B+tree只有叶子节点存放数据，而B-tree是每个节点都会存放数据。另外b+tree中所有叶子节点都是通过指针连接在一起，这样顺序遍历所有数据将变得非常容易，而b-tree不会。
所以相对b-tree，b+tree的层数会更少，因为它的叶子节点存放数据，所以它层数很少，可以达到减少磁盘IO次数的效果，当查询数据的时候，最好的情况就是很快找到目标索引，然后读取数据。而B-tree的每个结点都有data域，这无疑增大了节点大小，说白了增加了磁盘IO次数（磁盘IO一次读出的数据量大小是固定的，单个数据变大，每次读出的就少，IO次数增多，每次IO都是相当耗时的），而B+树除了叶子节点其它节点并不存储数据，节点小，磁盘IO次数就少。这是优点之一。另外就是b+tree的叶子节点之间通过指针来连接，范围扫描将十分简单，而对于b-tree来说，则需要在叶子节点和内部节点不停的往返移动
Mysql采用的是b+tree，而不是b-tree，不是说b-tree不好，而是b+tree更加适合mysql，更加适合关系型数据库。为什么这么说呢，因为我们大家熟知的Mongodb采用的就是b-tree。Mongodb是文档型的nosql，它是以Bson(Binary JSON)格式作为存储
目的就是高性能，高可用，易扩展。首先它摆脱了关系模型，MongoDB使用B-树，对于在内部节点的数据，可直接得到，不必根据叶子节点来定位，只要找到指定索引就可以进行访问，无疑提升单次查询的效率。
Differences between B trees and B+ trees
hash
讲完了btree，那剩余的就是hash了。hash大家都不陌生，其原理是首先根据key值和哈希函数创建一个哈希表（散列表），然后根据键值，通过散列函数，定位数据元素位置。所以hash的效率非常的高，是大于btree的，但是它有它的一些弊端，查阅了一些资料，搬运过来如下：
- 1 由于存放的是hash值，仅仅能满足”=”,”IN”和”<=>”查询，不能使用范围查询。
- 2 hash索引无法通过操作索引来排序,这是因为存放的时候经过hash计算,但是计算的hash值和存放的不一定相等,所以无法排序.
- 3 Hash 索引不能利用部分索引键查询。对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。
- 4 Hash 索引在任何时候都不能避免表扫描。因为不同索引键存在相同Hash值，所以即使取满足某个Hash 键值的数据的记录条数，也无法从 Hash索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。
- 5 当存在大量相同hash值得时候,hash索引的效率会变低.