复习整理的DAY33_数据库存储引擎与索引

存储引擎的分类与说明：

 主要记住两个常见的：

 MyISAM：特点——批量插入速度快，不支持事务，使用表格锁定机制

InnoDB：特点——批量插入速度比起MyISAM较慢，支持事务，支持行级锁定。

可以查询数据库引擎：

SHOW ENGINES；

 #############################################################################

索引：

什么是索引？

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

举个通俗易懂的例子，就像是字典前面的“汉语拼音音节索引”，这也是索引的一种，通过它，我们能够快速的找到我们想要找的字，而不需要一个字一个字的去查找，大大提高了效率。

索引存储在什么地方？

如果需要设置索引，那么这些索引存在哪里呢？当然不会全部存储在内存中，因为索引本身也很大，而且内存有易失性(一旦断电，内存中的数据就会全部清空)，因此索引往往以索引文件的形式存储在磁盘上。

这样存储索引，会产生磁盘I/O消耗，相对于内存存取，I/O存取要高几个数量级，所以，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

索引的优点：

提高检索效率，相对于全表扫描，降低了I/O成本，降低了排序成本，降低了CPU消耗。

 索引的缺点：

1）索引保存了主键与索引字段，指向表的具体记录，也需要占用空间。

2）有得必有失，索引提高了查询的速度，也会降低对表进行更新操作时的速度。因为更新表时，不仅需要对内容进行更新，还需要对索引进行动态的维护。

总结：索引需要根据业务需求场景不停进行试验修改调整。

MySQL主要索引类型：

普通索引：key

唯一索引：unique key

主键索引：primary key = unique key + not null

联合索引：又称复合索引 /多列索引 /组合索引，联合索引名（字段1，字段2，..）

全文索引：全文索引名（字段1，字段2，..）

MySQL索引结构：

目前大部分数据库系统及文件系统都采用B Tree或者B+Tree作为索引结构。

查看索引结构的SQL语句——SHOW INDEX FROM student;

 如图，可以看到student有两个索引，Key_name索引名分别是PRIMARY与banji_id，两个索引的Index_type索引类型都是BTREE。

B Tree即BalanceTree的缩写，意为平衡树。

比如：

 B+树是对B树的进一步优化

比如：

 总结两者：

（1）B树非叶子节点既存储数据，也存储键值，而B+数非叶子节点只存储键值。

（2）B+树索引的所有数据都存储在叶子节点，是按照顺讯排序的。

（3）B+树同层级的节点间有指针互相连接。

 不同的存储方式（B+树）：

在MySQL中，B+树索引按照存储方式的不同分为聚集索引与非聚集索引。

聚集索引：

又叫聚簇索引/物理索引，以主键作为B+树索引的键值而构建的B+树索引称为聚集索引。

非聚集索引：

也叫 Secondary Index，以主键以外的键值作为B+树索引的键值而构建的B+树索引称为非聚集索引。

覆盖索引：

覆盖索引指的是索引的叶子结点已经包含了需要查询的数据。

比如我们需要查询name, age, gender这三个数据，当然可以单单只用where来查询，而覆盖索引，就是更高明一些的方式（它充分考虑到整个查询，包含需要返回的数据集字段）。

比如：（staff是索引）

（1）explain select * from staff where name = 'Lucy' and age = 22 and pos = 'HR';

（2）explain select name, age, pos from staff where name = 'Lucy' and age = 22 and pos = 'HR';

 （2）就是覆盖索引的一个例子，相比于（1）的*，覆盖的意思显然体现在这里就是利用联合索引里面的值（name, age, pos）替换了*。