千万级数据表如何用索引快速查找？

编程入门行业动态更新时间:2024-10-28 16:27:47

千万<a href=https://www.elefans.com/category/jswz/34/1763665.html style= 级数据表如何用索引快速查找？"/>

千万级数据表如何用索引快速查找？

1. 索引的本质解析

索引： 帮助 MySQL 高效获取数据的排好序的数据结构
索引数据结构：二叉树、红黑树、Hash表、B-Tree
注：查找一次经过一次I/O
二叉树：右边的子节点>父节点，左边的子节点<父节点

红黑树：二叉平衡树，会自旋，二叉树当索引结构并不合适，I/O次数太多

B-Tree：当我们想减少I/O次数，那就得减少树的高度，但是数据量恒定的情况下，高度减少意味着宽度得增加，从而引入B树的概念。

B+Tree：（MySql数据库索引使用的是B+树）多路多叉树，非叶子节点无数据，叶子节点存储data元素，叶子节点加指针。Mysql中innodb_page_size = 16kb

MySQL每个B+树节点最大存储容量：16KB （指针+数据+索引）。

假设我们一行数据大小为1K，那么一页就能存16条数据，也就是一个叶子节点能存16条数据；再看非叶子节点，假设主键ID为bigint类型，那么长度为8B，指针大小在Innodb源码中为6B，一共就是14B，那么一页里就可以存储16K/14=1170个(主键+指针)
那么一颗高度为2的B+树能存储的数据为：1170 16=18720条，一颗高度为3的B+树能存储的数据为：11701170*16=21902400（千万级条）

2.MySQL存储引擎

注：存储引擎是形容表的

2.1 MyISAM：使用非聚集索引，索引文件和数据文件是分离的

frm文件：存储这张表的表结构
MYD文件：存储这张表的所有数据行
MYI文件：存储这张表的索引字段

2.2 InnoDB：使用聚集索引，索引文件和数据文件放在一起

frm文件：存储这张表的表结构
ibd文件：存储这张表的所有数据行和索引字段

总结：
InnoDB在叶子节点就可以直接取到数据，MyISAM在叶子节点取到数据的地址，然后还要进行一个I/O操作，才能取到数据。因此，InnoDB的执行效率会高一点。

3.联合索引

联合索引是由多个字段组成的索引。

如果你经常要用到多个字段的多条件查询，可以考虑建立联合索引，一般是除第一个字段外的其它字段不经常用于条件筛选情况，比如说a,b 两个字段，如果你经常用a条件或者a+b条件去查询，而很少单独用b条件查询，那么可以建立a,b的联合索引。如果a和b都要分别经常独立的被用作查询条件，那还是建立多个单列索引。

如果我们创建了(a, b, c)的复合索引，那么其实相当于创建了(a,b,c)、(a,b)、(a)三个索引，这被称为最佳左前缀特性。

4.常见面试题

4.1 为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？

首先，为了满足MySQL的索引数据结构B+树的特性，必须要有索引作为主键，可以有效提高查询效率，因此InnoDB必须要有主键。如果不手动指定主键，InnoDB会从插入的数据中找出不重复的一列作为主键索引，如果没找到不重复的一列，InnoDB会在后台增加一列rowId做为主键索引。
其次，索引的数据类型是整型，一方面整型占有的磁盘空间或内存空间相比字符串更少，另一方面整型比较比字符串比较更快速，字符串比较是先转换为ASCII码，然后再比较的。
最后，B+树本质是多路多叉树，如果主键索引不是自增的，那么后续插入的索引就会引起B+树的其他节点的分裂和重新平衡，影响数据插入的效率，如果是自增主键，只用在尾节点做增加就可以。

4.2 为什么基本不用hash索引？

Mysql索引分为【B+树索引】和【hash索引】

hash索引无法进行范围查找

select * from table where col1>20

还有hash不能避免全表扫描，数据量大检索效率低等问题

4.3 B+树是怎么实现范围查找的

B+树的叶子节点是从左到右依次递增的，而且各节点之间存在双向指针，可以很好的支持范围查找

4.4 为什么非主键索引结构叶子节点存储的是主键值？

首先，区分一下主键索引和非主键索引。非主键索引的叶子节点存放的是主键的值，而主键索引的叶子节点存放的是整行数据，其中非主键索引也被称为二级索引，而主键索引也被称为聚簇索引。