我们为什么要使用索引+select *（面试）

目录

做个比喻

为什么要使用它

索引的注意事项

 索引是怎么样降低IO次数的

为什么不用select *（深入）

1. 不必要的磁盘I/O

做个比喻

通过索引我们查询数据时可以不用读取完所有信息，而只是查询索引列；否则，索引库系统将会读取每条记录的所有信息进行匹配；

可以把索引比作新华字典的音序表。例如，要查“库”字，如果不使用音序，就需要从字典的 400 页中逐页来找。但是，如果提取拼音出来，构成音序表，就只需要从 10 多页的音序表中直接查找。这样就可以大大节省时间。

为什么要使用它

索引：就是根据表中的一列或者多个列（多个字段构成的索引）——>按照一定的顺序建立的列值与记录行之间的对应关系表，实际就是一张描述索引列值与原表记录行之间一一对应关系的有序表

总结：类似与字典的目录

在 MySQL 中，通常有以下两种方式访问数据库表的行数据：

1.顺序访问：

顺序访问是在表中实行全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据。

顺序访问实现比较简单，但是当表中有大量数据的时候，效率非常低下。例如，在几千万条数据中查找少量的数据时，使用顺序访问方式将会遍历所有的数据，花费大量的时间，显然会影响数据库的处理性能。

2. 索引访问

简而言之，就是通过列上的索引快速找到对应行的位置（利用映射）

例如，在学生基本信息表 tb_students 中，如果基于 student_id 建立了索引，系统就建立了一张索引列到实际记录的映射表。当用户需要查找 student_id 为 12022 的数据的时候，系统先在 student_id 索引上找到该记录，然后通过映射表直接找到数据行，并且返回该行数据。因为扫描索引的速度一般远远大于扫描实际数据行的速度，所以采用索引的方式可以大大提高数据库的工作效率。

简而言之，不使用索引，MySQL 就必须从第一条记录开始读完整个表，直到找出相关的行。表越大，查询数据所花费的时间就越多。如果表中查询的列有一个索引，MySQL 就能快速到达一个位置去搜索数据文件，而不必查看所有数据，这样将会节省很大一部分时间。
 

索引的注意事项

虽然索引可以加快查询速度，提高 MySQL 的处理性能，但是过多地使用索引也会造成以下弊端：

1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
2.除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间就会更大。
3.当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

 索引是怎么样降低IO次数的

首先，数据查询是这样的，1.先将索引页加载到缓存中，然后从缓存中找到对应的数据页 2.然后将数据页数据加载到内存中，最后将数据返回回去

抛出问题

众所周知，IO查询效率很低，需要读磁盘将数据加载到内存当中，而在大量数据存储时，我们是不能一下子将所受数据加载到内存中，而是逐渐加载到磁盘页，每个磁盘页对应树的节点，也就是侧面体现出树的节点（数据）越多，磁盘页也就越多，IO也就越多（反推）；——>树的深度和IO操作成正比

降低IO次数主要是利用到了索引的底层结构：B+树

(28条消息) 简单理解B树和B+树_Fairy要carry的博客-CSDN博客

先说B树：B树在非叶子节点存储数据了，这样我们一个索引页上除了有数据还有索引，运气比较好的话一下就找到了，但是众所周知，B树除了有指向节点数据的指针还有指向下一个节点的指针，占用内存较大，这样相同内存能储存的数据就更少了，所以效率相对就没那么高了，IO次数对比B+也会多一些；

B+树的话

B树中每个节点（包括叶节点和非叶节点）都存储真实的数据，B+树中只有叶子节点存储真实的数据，非叶节点只存储键（这样B+树的非叶子节点就能存放更多的索引，最终数据都在叶子节点上，树高度就不会高， 查询效率稳定，无论查询什么数据最终都是树高决定的，因为数据都在页字节点）

这样每次读取磁盘就可以读取更多的数据

更少的IO次数：

B+树的非叶节点只包含键，而不包含真实数据，因此每个节点存储的记录个数比B数多很多（即阶m更大），因此B+树的高度更低，访问时所需要的IO次数更少。此外，由于每个节点存储的记录数更多，所以对访问局部性原理的利用更好，缓存命中率更高。——>总结就是，数据页容纳了贡多元素，是的查询更少，IO变少

但其实我们的B树对比其他二叉树查找树页减少了IO次数，一个数据节点存储数据变多，从而导致IO次数大大减少数据的寻找是在内存中进行的，内存速度快的很，所以没事，主要是数据的读写

(28条消息) Mysql索引底层的Bmore树设置非叶子节点数据小于4KB来减少IO磁盘读取次数_IT乾坤的博客-CSDN博客

为什么不用select *（深入）

 之前我们其实都知道，无非就是覆盖索引，防止我们查询出现不必要的回表操作

1. 不必要的磁盘I/O

我们知道 MySQL 本质上是将用户记录存储在磁盘上，因此查询操作就是一种进行磁盘IO的行为（前提是要查询的记录没有缓存在内存中）。

查询的字段越多，说明要读取的内容也就越多，因此会增大磁盘 IO 开销。尤其是当某些字段是 TEXT、MEDIUMTEXT或者BLOB 等类型的时候，效果尤为明显。
 

2.那使用SELECT *会不会使MySQL占用更多的内存呢？

理论上不会，因为对于Server层而言，并非是在内存中存储完整的结果集之后一下子传给客户端（不是一下全部传送），而是每从存储引擎获取到一行，就写到一个叫做net_buffer的内存空间中，这个内存的大小由系统变量net_buffer_length来控制，默认是16KB；当net_buffer写满之后再往本地网络栈的内存空间socket send buffer中写数据发送给客户端，发送成功（客户端读取完成）后清空net_buffer，然后继续读取下一行并写入。
也就是说，默认情况下，结果集占用的内存空间最大不过是net_buffer_length大小罢了，不会因为多几个字段就占用额外的内存空间。

3.无法使用覆盖索引

CREATE TABLE `user_innodb` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_NAME_PHONE` (`name`,`phone`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

我们创建了一个存储引擎为InnoDB的表user_innodb，并设置id为主键，另外为name和phone创建了联合索引，最后向表中随机初始化了500W+条数据。

InnoDB会根据id创建一颗名为主键索引的B+树，这颗B+树重要的点就是叶子节点包含完整的用户记录

 执行：

SELECT * FROM user_innodb WHERE name = '蝉沐风';

然后用explain分析sql发现用了一个二级索引IDX_NAME_PHONE

二级索引的叶子节点长这个样子：

InnoDB存储引擎会根据搜索条件在该二级索引的叶子节点中找到name为蝉沐风的记录，但是二级索引中只记录了name、phone和主键id字段（谁让我们用的是SELECT *呢），因此InnoDB需要拿着主键id去主键索引中查找这一条完整的记录，这个过程叫做回表。

想一下，如果二级索引的叶子节点上有我们想要的所有数据，是不是就不需要回表了呢？是的，这就是覆盖索引。