【BATJ】面试必问MySQL索引实现原理-CSDN博客

2019独角兽企业重金招聘Python工程师标准>>>

BATJ面试题剖析

1、为什么需要使用索引？

2、数据结构Hash、平衡二叉树、B树、B+树区别？

3、机械硬盘、固态硬盘区别？

4、Myisam与Innodb B+树的区别？

5、MySQL中的索引什么数据结构？

6、MySQL数据库优化方案？

1.为什么需要使用索引？

MySQL官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构。

也就是说：索引就像书的目录一样可以非常快速的定位到书的页码。

如果向mysql发出一条sql语句请求，查询的字段没有创建索引的话，可能会导致全表扫描，这样查询效率非常低

2.1数据结构Hash算法

哈希表（Hash table，也叫散列表），是根据关键码值(Key value)而直接进行访问的数据结构。也就是说，它通过把关键码值映射到表中一个位置来访问记录，以加快查找的速度。这个映射函数叫做散列函数，存放记录的数组叫做散列表。

优点:通过字段的值计算的hash值，定位数据非常快，查找可以直接根据key访问。

缺点: 因为底层数据结构是散列的，无法进行比较大小，不能进行范围查找

index=Hash(key)

2.2数据结构平衡二叉树算法

平衡二叉查找树，又称 AVL树。它除了具备二叉查找树的基本特征之外，还具有一个非常重要的特点：它的左子树和右子树都是平衡二叉树，且左子树和右子树的深度之差的绝对值（平衡因子 ）不超过1。也就是说AVL树每个节点的平衡因子只可能是-1、0和1（左子树高度减去右子树高度）。

数据结构模拟工具
官网首页:https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

Avl数据结构https://www.cs.usfca.edu/~galles/visualization/AVLtree.html
平衡二叉树查询原理

假设查询10 （需要经历4次IO操作）
1次 从硬盘中读取4 （内存），判断下10>4，取右指针
2次 从硬盘中读取8 （内存），判断下10>8，取右指针
3次 从硬盘中读取9 （内存），判断下10>，取右指针
4次 从硬盘中读取10 （内存），判断下10=10，定位到数据
优点:平衡二叉树算法基本与二叉树查询相同，效率比较高

缺点:插入操作需要旋转，支持范围查询，虽然支持范围查询，但是回旋查询效率低。

规律：如果树的高度越高，那么查询IO次数会越多。

2.3数据结构B树

维基百科对B树的定义为“在计算机科学中，B树（B-tree）是一种树状数据结构，它能够存储数据、对其进行排序并允许以O(log n)的时间复杂度运行进行查找、顺序读取、插入和删除的数据结构。B树，概括来说是一个节点可以拥有多于2个子节点的二叉查找树。与自平衡二叉查找树不同，B-树为系统最优化大块数据的读和写操作。B-tree算法减少定位记录时所经历的中间过程，从而加快存取速度。普遍运用在数据库和文件系统。”

因为B树节点元素比平衡二叉树要多，所以B树数据结构相比平衡二叉树数据结构实现减少磁盘IO的操作。

如何去减少查询IO次数？

B树在平衡二叉树中，减少树的高度

结论：B树比平衡二叉树减少了一次IO操作

B树查询效率比平衡二叉树效率要高，因为B树的节点中可以有多个元素，从而减少树的高度，减少IO操作，从而提高查询效率，缺点：范围查询效率还是比较低。

2.4数据结构B+树

B+树相比B树，新增叶子节点与非叶子节点关系，叶子节点中包含了key和value，非叶子节点中只是包含了key，不包含value。

所有相邻的叶子节点包含非叶子节点，使用链表进行结合，有一定顺序排序，从而范围查询效率非常高。

B+树解决范围查询问题、减少IO查询的操作。

B+树算法：通过继承了B树的特征，通过非叶子节点查询叶子节点获取对应的value，所有相邻的叶子节点包含非叶子节点，使用链表进行结合，有一定顺序排序，从而范围查询效率非常高。

缺点：因为有冗余节点数据，会比较占内存。

3、机械硬盘、固态硬盘区别？

固态硬盘对比机械硬盘优势图表

4、Myisam与Innodb B+树的区别？

索引文件如何查看

默认数据与索引文件位置: /var/lib/mysql

MyISAM引擎的文件：

.myd 即 my data，表数据文件

.myi 即my index，索引文件

.log 日志文件。

InnoDB引擎的文件：

采用表空间（tablespace）来管理数据，存储表数据和索引，

InnoDB数据库文件（即InnoDB文件集，ib-file set）：

ibdata1、ibdata2等：系统表空间文件，存储InnoDB系统信息和用户数据库表数据和索引，所有表共用。

.ibd文件：单表表空间文件，每个表使用一个表空间文件（file per table），存放用户数据库表数据和索引。

MyISAM和InnoDB对B-Tree索引不同的实现方式

主键索引： MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM主键索引的，这里设表一共有三列，假设我们以Col1为主键，图myisam1是一个MyISAM表的主索引（Primary key）示意。可以看出

InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同.

MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

5、MySQL中的索引什么数据结构？

使用B+Tree作为索引结构，使用InnoDB引擎，叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

6、MySQL数据库优化方案？

Mysql的优化，大体可以分为三部分：索引的优化，sql慢查询的优化，表的优化

开启慢查询日志，可以让MySQL记录下查询超过指定时间的语句，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。

先捕获低效SQL→慢查询优化方案→慢查询优化原则

腾讯云MySQL数据库监控

版权@须臾之余https://my.oschina.net/u/3995125
1.腾讯云数据库地址:https://console.cloud.tencent.com/cdb

2. 实例监控https://console.cloud.tencent.com/cdb?ins=4-cdb-0g6a8qct