【归纳总结】数据库之索引

什么是索引？

MySQL官方对于索引的定义是：索引使可以帮助高效获取数据的数据结构。即索引是数据结构。

数据库在执行查询的时候，如何没有索引存在的情况下，会采用全表扫描的方式进行查找。如果存在索引，则会先去索引列表中定位到特定的行或者直接定位到数据，从而可以极大地减少查询的行数，增加查询速度。

可以类比为一部字典开头的目录。

---

索引是哪种数据结构？

1️⃣二叉树、红黑树？

优点：二叉树中的每一个元素保存了相应行数据的磁盘地址，通过该磁盘地址，便可以定位到对应行的数据。只需要几次查询即可查到该索引数据。
缺点：但是当数据量比较大，页的数目很多时，二叉树的高度会比较高。IO 的次数会比较多（查询一层需要一次IO）。查找效率低。

2️⃣Hash表？

优点：把key通过一个=固定的运算转换成一个数字，然后将这个数字对数组的长度取余，最终的结果就当做数组的下标。对应的数据就放在该下标处。如果hash表作为索引，其查询效率也是很高的。
缺点：

1. hash索引仅能够查找=，in 等查找，无法进行范围查找。
2. hash索引无法用来进行排序（经过运算过后的数字大小和原本数字大小没有关系）
3. 如果设置了若干字段的一个组合索引，那么hash索引无法利用部分字段进行索引查找，比如设置了用户名、密码、邮箱的联合索引，那么无法使用用户名、密码来通过索引查找。
4. 如果存在大量的hash值相等的情况，那么hash索引此时的查询性能不一定优秀，即区间查找慢。

3️⃣B树？

B树是一种多路平衡查找树。定义如下：
对于一个m阶的B树而言（阶数表示一个节点最多有多少个孩子节点）：

• 每个节点最多有m-1个key
• 根节点最少有一个key
• 每个结点中的key都按照从小到大的顺序排列，每个key的左子树中的所有key都小于它，而右子树中的所有key都大于它。
• 所有叶子结点都位于同一层，或者说根结点到每个叶子结点的路径长度都相同。

优点：相较于平衡二叉树或者红黑树，当数据量多的时候，B树的高度增加得慢，例如假设一行记录是16B，那么一页大概可以存1000行记录，即B树的阶是1000。二层的B树大概可以存1000 x 1000行记录，三层可以存 1000 x 1000 x 1000 行记录。IO的穿刺术大大减少，效率大大提高。
缺点：范围查找时，性能会下降。当一棵树的数据量很大时，树的高度也会很大，查找的效率依然会比较低。

注意：为什么不将千万条数据全部存放在一个节点内？这样不是只需要一次磁盘 IO 就可以找到对应的数据了吗？
因为由于磁盘存取的速度比内存慢很多，所以磁盘读取时，通常情况下并不是按需读取，而是会预读一部分数据。预读的长度通常情况下为一个页的整数倍。一个页一般情况下大小为== 4k==。也就是说一次磁盘 IO 通常只会读取 4k 的几倍。因此，把全部数据写入到一个节点中，也并没有太大用处，因为一般只会读取 4k 或者 4k 的几倍。

4️⃣B+树？

其实，数据库的底层就是采用B+树来作为索引的。它可以看成是 B 树的变种。具有以下特点：

• 非叶子节点不存储 data，只存储 key
• 所有的叶子节点存储完整的一份 key 信息以及 key 对应的 data
• 每一个父节点都出现在子节点中，是子节点的最大或者最小的元素
• 每个叶子节点都有一个指针，指向下一个数据，形成一个链表

特点：B+树由于非叶子节点不存储数据，仅在叶子节点才存储数据，所以，单个非叶子节点可以存储更多的索引字段。

优点：

1. 之前B树存的是记录，现在B+树存的是索引，索引所占的空间是小于记录的，因此每一页都能存储更多的索引，这样就解决了第一个问题：树的增长过快。
2. 由于叶子节点是由链表按大小依次连接（有序）的 (在InnoDB 中是双链表)。范围查找的时候，也可以避免过多的IO次数。

---

数据库的组成结构是什么？

1️⃣连接器

负责管理连接，权限的验证等。

2️⃣解析器

首先 mysql 需要知道你想做什么。因此需要对输入的sql语句进行解析。首先进行词法分析，需要识别出里面的字符串代表什么意思（比如 select代表查询, id代表某张表的列字段叫id）。之后进行语法分析，根据语法规则，判断输入的sql语句是否符合sql 语法。

3️⃣优化器

经过解析之后，mysql就知道你需要做什么事情了。但是在真正执行之前还需要经过优化器处理（比如当表中存在多个索引的时候，选择哪个索引来使用。或者多表关联的时候，选择各个表的连接先后顺序）。

4️⃣执行器

开始执行之前首先确认对该表有无执行查询的权限。

• 如果没有，则返回错误的信息提示。
• 如果有权限，则开始执行。
  首先根据该表的引擎类型，使用这个引擎提供的接口。比如查询某表，然后利用某字段查找，如果没有添加索引，则调用引擎的接口取出第一行数据，判断结果是不是，如果不是，依次再调用引擎的下一行数据，直至取出这个表中所有的数据。如果该字段有索引，执行过程也大致相似。

所以具体的数据是保存在引擎中的。在MySQL中，常见的引擎有MyISAM和InnoDB。

---

MyISAM和InnoDB

1️⃣区别

1. InnoDB支持事务，MyISAM不支持事务，对于InnoDB中的每条sql语句都自动封装成事务，自动提交，影响速度
2. InnoDB支持外键，MyISAM不支持外键
3. InnoDB支持表锁和行锁，MyISAM支持表锁
4. InnoDB不保存表的行数，查询某张表的行数会全表扫描。MyISAM会保存整个表的行数，执行速度很快
5. 使用 InnoDB 存储表，会生成两个文件:
   .frm # 存储表结构，是任何存储引擎都有的
   .ibd # 存放数据和索引
   使用 MyISAM 存储表，会生成三个文件:
   .frm # 存储表结构，是任何存储引擎都有的
   .myd # 存放数据
   .myi # 存放索引
6. InnoDB是聚集索引，数据文件和索引绑在一起。MyISAM是非聚集索引，索引和数据文件是分开的
   

2️⃣索引实现

MyISAM的索引是非聚集索引。什么叫非聚集？索引文件和数据文件是分离的。索引文件中存放的是对应数据的文件指针。

InnoDB是聚集索引，数据文件本身就是索引文件。索引和文件数据是存放在一起的。

3️⃣如何选择

是否需要事务？如果不需要，则可以使用 MyISAM
绝大多数操作是否是查询？如果是，可以选择MyISAM，有读也有写，则选择InnoDB

4️⃣其他问题

1. 对于Innodb表为什么必须要指定主键，同时推荐使用自增的整数作为主键
   即便自己不去指定主键，innodb引擎也会帮助你去指定一个隐藏列作为主键，但是它指定的可能不太合适。使用使用整数的目的是为了排序更加的方便，或者说范围查找更加便利，为什么推荐使用自增的呢？1 2 3 89 4 5 6，使用自增的数字对于整个索引树的维护来说，会比较方便，因为之前的节点基本不会再发生变化，只需要再后面的节点分裂即可。

2. 对于Innodb表来说，为什么叶子节点存储的数据是主键的值？
   如果不存储主键的值，那么就存储行数据，存储行数据的话会导致每行数据有多个存储，那么如果你需要修改数据，需要在每个索引树里面全部修改一遍。如果存储的是主键的值，只需要修改主键索引树里面的数据即可，其他的索引存的是主键的引用。

---

索引语法

• 查看某张表的索引：show index from 表名;
• 创建普通索引：alter table 表名 add index 索引名(字段列名);
• 创建复合索引：alter table 表名 add index 索引名(字段列名 1，字段列名 2);
• 删除某张表的索引：drop index 索引名 on 表名;