数据库高阶（二）索引与SQL优化、B+树

MySQL高阶（二）

🌟索引与SQL优化
✨什么是索引？
索引（Index）类似图书的目录索引，可以提高数据检索的效率，降低数据库的IO成本。
MySQL官方对索引的定义为：
索引（index）是帮助MySQL高效获取数据的数据结构
是快速查找排好序的一种数据结构

✨索引的分类：
1）主键索引：primary key
即主索引，根据主键 建立索引，不允许重复，不允许空值，通常自增；
注意删除时要先取消自增，再删除主键
如果表中没有定义主键，InnoDB 会选择⼀个唯⼀的非空索引代替。 如果没有这样的索引，InnoDB 会隐式定义⼀个主键来作为聚簇索引

2）唯一索引：unique key
用来建立索引的列的值必须是唯一的，允许空值。

3）普通索引：
用表中的普通列构建的索引，没有任何限制

4）全文索引：用大文本对象的列构建的索引

MySQL8的版本中⽀支持了了对中⽂文分词的全⽂文索引
5）组合索引
用多个列组合构建的索引，这多个列中的值不允许有空值
注意：组合索引的“最左”原则

🚩B+Tree
1.B+树只有叶子节点存储数据
2.非叶子节点起到了索引的作用
3.所有叶子节点使用链表相连

🚩索引的实现原理——B+树的好处：
1.磁盘读写代价更低
B树的数据和索引都在同一个节点上，那么每个块中包含的索引是少量的，如果想要取出比较深层的数据，意味着要读取更多的块，才能得到想要的索引和数据，那么就增加了IO次数

而B+树中每个块能存储的索引是B树的很多倍，那么获取比较深层的数据，也只需要读取少量的块就可以，那么就减少了磁盘的IO次数

2.随机IO的次数更少 B+树的优势是什么？
而随机I/O是指读写操作事件连续，但访问地址不连续，时长约为10ms
在相同情况下，B树要进行更多的随机IO,而B+树需要更多的顺序IO,因此B+树效率也更快
3.查询速度更稳定
由于B+Tree 非叶子节点不存储数据（data）,因此所有的数据都要查询至叶子节点，而叶子节点的高度都是相同的，因此所有数据的查询速度都是一样的。

🚩聚簇索引和非聚簇索引
聚簇索引：索引即数据，数据即索引
非聚簇索引：找到索引仅仅是找到当前索引值和key 如果需要索引外的内容，则需要回表

⚠️注意：关于索引原理和聚簇非聚簇索引都是以InnoDB表引擎为基础

🚩MyISAM 和 InnoDB的区别
✨数据存储方式：
InnoDB由两种文件组成，表结构，数据和索引
MyISAM由三种文件组成，表结构、数据、索引
✨索引的方式：
索引的底层都是基于B+Tree的数据结构建立
InnoDB中主键索引为聚簇索引，辅助索引是非聚簇索引
MyISAM中数据和索引存在不同的文件中，因此都是非聚簇索引
✨事务的支持：
InnoDB支持事务
MyISAM不支持事务
🚩总结：
数据库的索引是为了提高数据检索速度
数据库的索引是基于B+Tree的数据结构实现的
在InnoDB中主键是聚簇索引而辅助索引是非聚簇索引
在MyISAM中主键索引和辅助索引都是非聚簇索引

🌟慢查询优化
✨使用Explain 分析一条查询语句，关注以下信息：
type 针对单表的访问⽅方法
possible_keys 可能⽤用到的索引
key 实际上使⽤用的索引
rows 预估的需要读取的记录条数
✨检索速度慢通常是由于：
type: All 全表检索
最好的检索类型：
type: const 直接找到检索
✨无法使用检索的几种情况：
1.在列上使用函数和进行运算
2.出现了查询条件左右两侧类型不匹配时发生隐士转换，导致索引失效

✨复合索引使用的注意事项：
1.多个单列表索引并不是最佳选择
2.复合索引的最左前缀原则
查询条件中使用了复合索引的第一个字段，索引才会被使用。因此，在复合索引中索引列的顺序至关重要。如果不是按照索引的最左列开始查找，则无法使用索引。
3.尽可能达成索引覆盖
如果一个索引包含所需要的查询的字段的值，直接根据索引的查询结果返回数据，而无需读表，能够极大的提高性能。因此，可以定义一个让索引包含的额外的列，即使这个列对于索引而言是无用的。