【MySQL】关于MySQL索引的底层实现的问题解读

清疚 2022-02-27 13:38 248阅读 0赞

#### 1、为什么不用Hash表作为索引？ ####

Hash表进行范围查询比较困难，如select \* from sanguo where id >10;

#### 2、为什么不用平衡二叉树作为索引？ ####

虽然平衡二叉树支持范围查询，但是这这种数据结构要范围查找要往回找，即回溯到父结点，而B+树的 叶子结点的指针的效率则更高。

#### 3、为什么选择B树的一个结点存多个元素的结构？ ####

因为数据库的索引是存储在文件中的，而读取文件内容又要进行磁盘I/O操作，普通树的结 点只有一个元素，进行磁盘I/O的次数很多，而B树的一节点多数据的结构减少了磁 盘I/O的次数，提高了查找效率。

#### 4、磁盘存取数据的局部性原理 ####

某个数据被取出，那么该数据的周围一定会被用到。

#### 5、操作系统存储数据的单位 ####

操作系统是按照页为单位存取数据的，1页默认为4KB

#### 6、一个结点里面应该存多少个元素？或者说一个结点应该多大？ ####

因为操作上系统是按页为单位存取的，因此为了避免数据的浪费，一个结点的大小应该为页的整数倍。MySQL数据库的一页大小为16KB，因此为一个结点应为4页。

#### 7、MySQL的B+树为什么不在非叶子结点存储数据？ ####

MySQL中B+树的一个结点的大小刚好为数据库的一页的大小，如果存储了数据，那么 存储的索引数就会减少，从而促使整颗B+数变高，从而增加了磁盘I/O次数，降低了查找效率。

#### 8、MyISAM和InnoDB的主键索引B+树的区别？ ####

MyISAM的B+树索引中的叶子节点存储的是数据的地址，而InnoDB引擎中B+树的叶子 节点中则直接存的是数据，这样可以减少一次磁盘I/O操作。

#### 9、MyISAM和InnoDB的辅助索引和辅助索引B+树结构的区别？ ####

MyISAN的辅助索引的存储结构与主键索引相同，而InnoDB的辅助索引的B+树的叶子 结点没有存储所有的数据，而是存储了每行数据的主键，如果表中没有创建主键，InnoDB会自己创建一个隐藏的默认主键存储。

#### 10、MySQL的一页的默认值为什么为16KB？ ####

假设一行数据为1KB大小，那么B+树的一个叶子结点可以存16行（16KB/1KB）数据， 非叶子结点只存储了索引值和指针，主键索引在InnoDB默认为bigInt类型（8B），一个 指针的大小为6B，因此一个非叶子结点可存储1170对索引+指针，B+树高度为2时， 叶子结点的个数为1170*16条数据，当为高度为3时，可存储1170*1170\*3条数据。 一页的数据为16KB已经足够。

#### 11、联合索引的存储 ####

联合索引是将多个索引拼接起来来构建B+树的。

#### 12、如何判断能不能用到索引？ ####

如果到使用B+树的结点能帮助缩小查询范围，那么就能用到索引，如果查询条件不能 用到B+树的结点来缩小查询范围，那么就是用不到索引。因此联合索引查询要遵循最左前缀原则，如果不使用最左前缀原则，那么就用不到联合索引。

#### 13、最左前缀原则 ####

如果对表中的字段a、b、c建立联合索引，且顺序为a、b、c。

select * from table where a = 1 ;
    select * from table where a = 1 and b = 1;
    select * from table where a = 1 and b = 1 and c = 1;
    select * from table where a = 1 and c = 1;

采用以上字段进行查询的sql语句都会使用到索引，但是最后一个sql语句略有不同，虽然根据a、c字段联合查询，但是它只用到了a的索引，使用索引的结果就等效于第一句的情况，也就是说c = 1并没有用到联合索引。

而b、bc、c字段单独使用则不会用到联合索引。