Java面试题之 mysql面试题

文章目录

• ​​导言​​
• ​​MySQL的逻辑架构​​

• ​​1. reference​​
• ​​2. 架构图​​
• ​​3. 日志介绍​​

• ​​一、mysql 存储引擎​​

• ​​1. 存储引擎是什么​​
• ​​2. 各种存储引擎的介绍​​

• ​​二、mysql 日志​​

• ​​1. reference​​
• ​​2. 日志的种类​​
• ​​3. 重点日志种类介绍​​

• ​​a、重做日志（redo log）​​
• ​​b、 回滚日志（undo log）​​
• ​​c、二进制日志（binlog）​​

• ​​三、事务的特性和隔离级别​​

• ​​1. 事务的特性​​
• ​​2. 事务的隔离级别​​
• ​​3. 隔离级别、锁​​

• ​​四、索引​​

• ​​1、explain 查看索引执行情况​​

• ​​a、建表​​
• ​​b. 对比加索引前后的SQL查询情况​​
• ​​c、解释Explain得到的结果​​

• ​​2、MySQL 的覆盖索引与回表​​

• ​​a、两大类索引​​
• ​​b、示例​​

• ​​1. **建表**​​
• ​​2. **填充数据**​​
• ​​3. **索引存储结构**​​

• ​​聚簇索引（ClusteredIndex）​​
• ​​普通索引（secondaryIndex）​​
• ​​聚簇索引查找过程​​
• ​​普通索引查找过程第一步​​
• ​​普通索引查找过程第二步​​

• ​​c、回表查询​​
• ​​d、索引覆盖​​
• ​​e、如何实现覆盖索引​​

• ​​3. 哪些场景适合使用索引覆盖来优化SQL​​

• ​​a、全表count查询优化​​
• ​​b、列查询回表优化​​
• ​​c、分页查询​​

• ​​4. 总结​​
• ​​5. 返璞归真：索引作用的位置​​

• ​​五、如何优化一段sql​​

导言

​​MySQL 精选 60 道面试题（含答案）

MySQL的逻辑架构

1. reference

1、MySQL中的日志有什么作用？：https://www.zhihu.com/question/450862540/answer/1797682794

2. 架构图

MySQL的逻辑架构大致可以分为三层：

• 第一层：处理客户端连接、授权认证，安全校验等。
• 第二层：服务器server层，负责对SQL解释、分析、优化、执行操作引擎等。
• 第三层：存储引擎，负责MySQL中数据的存储和提取。

我们要知道MySQL的服务器层是不管理事务的，事务是由存储引擎实现的，而MySQL中支持事务的存储引擎又属InnoDB使用的最为广泛，所以后续文中提到的存储引擎都以InnoDB为主。

记住！ 记住！ 记住！ 上边这张图， 她是MySQL更新数据的基础流程，其中包括​​redo log、bin log、undo log​​三种日志间的大致关系，好了闲话少说直奔主题。

3. 日志介绍

一、mysql 存储引擎

1. 存储引擎是什么

MySQL5.5之前，默认引擎是“MyISAM”；
从MySQL5.5版本开始，默认引擎是“InnoDB”，该引擎完全支持符合ACID和事务，支持外键、提交、回滚、前滚操作，表的大小最高可达64TB。在MySQL中，可以使用“SHOW ENGINES;”命令查看系统所支持的引擎类型以及默认引擎；输出结果中，DEFAULT关键字标识的引擎就是当前默认的存储引擎。

• 数据库存储引擎是​​数据库底层软件组件​​，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。简而言之，​​存储引擎就是指表的类型​​。
• 数据库的存储引擎决定了表在计算机中的存储方式。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能。
  MySQL为其表提供各种存储引擎，如InnoDB、MyISAM、Memory、Merge、Archive、CSV、BLACKHOLE 等。
• 可以使用SHOW ENGINES;语句查看系统所支持的引擎类型，结果如图所示。



• Support 列的值表示某种引擎是否能使用，YES表示可以使用，NO表示不能使用，DEFAULT表示该引擎为当前默认的存储引擎。
  可以看出，当前默认的存储引擎是InnoDB。

2. 各种存储引擎的介绍

1. MyISAM 引擎

• MyISAM扩展了以前的ISAM存储引擎。MyISAM表针对压缩和速度进行了优化。MyISAM表也可以在平台和操作系统之间移植。
• MyISAM表的大小可以达到256TB，这是巨大的。此外，MyISAM表可以压缩为只读表以节省空间。在启动时，MySQL会检查MyISAM表是否存在损坏，甚至在出现错误时对其进行修复。MyISAM表不是事务安全的。

2. InnoDB 引擎

• InnoDB表完全支持符合ACID和事务。它们也是性能的最佳选择。InnoDB表支持外键，提交，回滚，前滚操作。InnoDB表的大小最高可达64TB。
• 与MyISAM一样，InnoDB表可在不同平台和操作系统之间移植。如有必要，MySQL还会在启动时检查和修复InnoDB表。

3. MERGE 引擎

• MERGE表是一个虚拟表，它将多个MyISAM表组合在一起，这些表具有与一个表类似的结构。MERGE存储引擎也称为MRG_MyISAM引擎。MERGE表没有自己的索引; 它使用组件表的索引。
• 使用MERGE表，可以在连接多个表时加快性能 。MySQL只允许您对MERGE表执行SELECT，DELETE，UPDATE和INSERT操作。如果DROP TABLE在MERGE表上使用MERGE语句，则仅删除规范。基础表不会受到影响。

4. Memory 引擎

• 内存表存储在内存中并使用哈希索引，因此它们比MyISAM表更快。内存表数据的生命周期取决于数据库服务器的正常运行时间。内存存储引擎以前称为HEAP。

5. Archive 引擎

• 归档存储引擎允许您将大量记录（用于归档）存储为压缩格式以节省磁盘空间。存档存储引擎在插入时压缩记录，并在读取时使用zlib库对其进行解压缩。
• 归档表仅允许INSERT和SELECT语句。ARCHIVE表不支持索引，因此需要对表读取行进行全表扫描。

6. CSV

• CSV存储引擎以逗号分隔值（CSV）文件格式存储数据。CSV表提供了一种将数据迁移到非SQL应用程序（如电子表格软件）的便捷方法。
• CSV表不支持NULL数据类型。此外，读取操作需要全表扫描。

7. FEDERATED

• FEDERATED存储引擎可让您无需使用群集或复制技术管理从远程MySQL服务器的数据。本地联合表不存储任何数据。从本地联合表查询数据时，将从远程联合表中自动提取数据。

二、mysql 日志

1. reference

​​原来MySQL的日志文件也是非常的重要的​​

2. 日志的种类

MySQL中有八种日志文件，分别是：

1. ​​重做日志（redo log）​​，
2. ​​回滚日志（undo log）​​，
3. ​​二进制日志（binlog）​​，
4. 错误日志（errorlog），
5. 慢查询日志（slow query log），
6. 一般查询日志（general log），
7. 中继日志（relay log），
8. DDL日志 （metadata log），

他们分别都有各自的作用，而且默认情况下，服务器的日志文件都位于数据目录（datadir）中。下面来给大家详细讲解。

3. 重点日志种类介绍

a、重做日志（redo log）

• 作用：
  确保事务的持久性。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。
• 内容：
  物理格式的日志，记录的是物理数据页面的修改的信息，其redo log是顺序写入redo log file的物理文件中去的。
• 什么时候产生：
  事务开始之后就产生redo log，redo log的落盘并不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入redo log文件中。
• 什么时候释放：
  当对应事务的脏页写入到磁盘之后，redo log的使命也就完成了，重做日志占用的空间就可以重用（被覆盖）。
• 对应的物理文件：
  默认情况下，对应的物理文件位于数据库的data目录下的 ib_logfile1&ib_logfile2

• innodb_log_group_home_dir 指定日志文件组所在的路径，默认./ ，表示在数据库的数据目录下。
• innodb_log_files_in_group 指定重做日志文件组中文件的数量，默认2

• 关于文件的大小和数量，由以下两个参数配置：

• innodb_log_file_size 重做日志文件的大小。
• innodb_mirrored_log_groups 指定了日志镜像文件组的数量，默认1

• 其他：
  很重要一点，redo log是什么时候写盘的？前面说了是在事物开始之后逐步写盘的。

之所以说重做日志是在事务开始之后逐步写入重做日志文件，而不一定是事务提交才写入重做日志缓存，原因就是，重做日志有一个缓存区Innodb_log_buffer，Innodb_log_buffer的默认大小为8M(这里设置的16M),Innodb存储引擎先将重做日志写入innodb_log_buffer中。

然后会通过以下三种方式将innodb日志缓冲区的日志刷新到磁盘

Master Thread 每秒一次执行刷新Innodb_log_buffer到重做日志文件。
每个事务提交时会将重做日志刷新到重做日志文件。
当重做日志缓存可用空间 少于一半时，重做日志缓存被刷新到重做日志文件

由此可以看出，重做日志通过不止一种方式写入到磁盘，尤其是对于第一种方式，Innodb_log_buffer到重做日志文件是Master Thread线程的定时任务。

因此重做日志的写盘，并不一定是随着事务的提交才写入重做日志文件的，而是随着事务的开始，逐步开始的。

即使某个事务还没有提交，Innodb存储引擎仍然每秒会将重做日志缓存刷新到重做日志文件。

这一点是必须要知道的，因为这可以很好地解释再大的事务的提交（commit）的时间也是很短暂的。

b、 回滚日志（undo log）

• 作用：
  保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读
• 内容：
  逻辑格式的日志，在执行undo的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，这一点是不同于redo log的。
• 什么时候产生：
  事务开始之前，将当前是的版本生成undo log，undo 也会产生 redo 来保证undo log的可靠性
• 什么时候释放：
  当事务提交之后，undo log并不能立马被删除，而是放入待清理的链表，由purge线程判断是否由其他事务在使用undo段中表的上一个事务之前的版本信息，决定是否可以清理undo log的日志空间。
• 对应的物理文件：
  MySQL5.6之前，undo表空间位于共享表空间的回滚段中，共享表空间的默认的名称是ibdata，位于数据文件目录中。
  MySQL5.6之后，undo表空间可以配置成独立的文件，但是提前需要在配置文件中配置，完成数据库初始化后生效且不可改变undo log文件的个数
  如果初始化数据库之前没有进行相关配置，那么就无法配置成独立的表空间了。
• 关于MySQL5.7之后的独立undo 表空间配置参数如下：
  innodb_undo_directory = /data/undospace/ –undo独立表空间的存放目录
  innodb_undo_logs = 128 –回滚段为128KB
  innodb_undo_tablespaces = 4 –指定有4个undo log文件
• 如果undo使用的共享表空间，这个共享表空间中又不仅仅是存储了undo的信息，共享表空间的默认为与MySQL的数据目录下面，其属性由参数innodb_data_file_path配置。



• 其他：

1. undo是在事务开始之前保存的被修改数据的一个版本，产生undo日志的时候，同样会伴随类似于保护事务持久化机制的redolog的产生。
2. 默认情况下undo文件是保持在共享表空间的，也即ibdatafile文件中，当数据库中发生一些大的事务性操作的时候，要生成大量的undo信息，全部保存在共享表空间中的。
3. 因此共享表空间可能会变的很大，默认情况下，也就是undo 日志使用共享表空间的时候，被“撑大”的共享表空间是不会也不能自动收缩的。
4. 因此，mysql5.7之后的“独立undo 表空间”的配置就显得很有必要了。

c、二进制日志（binlog）

作用：
用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。
用于数据库的基于时间点的还原。

• 内容：

1. 逻辑格式的日志，可以简单认为就是执行过的事务中的sql语句。
2. 但又不完全是sql语句这么简单，而是包括了执行的sql语句（增删改）反向的信息，也就意味着delete对应着delete本身和其反向的insert；update对应着update执行前后的版本的信息；insert对应着delete和insert本身的信息。
3. 在使用mysqlbinlog解析binlog之后一些都会真相大白。
4. 因此可以基于binlog做到类似于oracle的闪回功能，其实都是依赖于binlog中的日志记录。

• 什么时候产生：

1. 事务提交的时候，一次性将事务中的sql语句（一个事物可能对应多个sql语句）按照一定的格式记录到binlog中。
2. 这里与redo log很明显的差异就是redo log并不一定是在事务提交的时候刷新到磁盘，redo log是在事务开始之后就开始逐步写入磁盘。
3. 因此对于事务的提交，即便是较大的事务，提交（commit）都是很快的，但是在开启了bin_log的情况下，对于较大事务的提交，可能会变得比较慢一些。
4. 这是因为binlog是在事务提交的时候一次性写入的造成的，这些可以通过测试验证。

• 什么时候释放：
  binlog的默认是保持时间由参数expire_logs_days配置，也就是说对于非活动的日志文件，在生成时间超过expire_logs_days配置的天数之后，会被自动删除。



• 对应的物理文件：

1. 配置文件的路径为log_bin_basename，binlog日志文件按照指定大小，当日志文件达到指定的最大的大小之后，进行滚动更新，生成新的日志文件。
2. 对于每个binlog日志文件，通过一个统一的index文件来组织。

• 其他：

1. 二进制日志的作用之一是还原数据库的，这与redo log很类似，很多人混淆过，但是两者有本质的不同

• 作用不同：redo log是保证事务的持久性的，是事务层面的，binlog作为还原的功能，是数据库层面的（当然也可以精确到事务层面的），虽然都有还原的意思，但是其保护数据的层次是不一样的。
• 内容不同：redo log是物理日志，是数据页面的修改之后的物理记录，binlog是逻辑日志，可以简单认为记录的就是sql语句

另外，两者日志产生的时间，可以释放的时间，在可释放的情况下清理机制，都是完全不同的。

恢复数据时候的效率，基于物理日志的redo log恢复数据的效率要高于语句逻辑日志的binlog

关于事务提交时，redo log和binlog的写入顺序，为了保证主从复制时候的主从一致（当然也包括使用binlog进行基于时间点还原的情况），是要严格一致的，MySQL通过两阶段提交过程来完成事务的一致性的，也即redo log和binlog的一致性的，理论上是先写redo log，再写binlog，两个日志都提交成功（刷入磁盘），事务才算真正的完成。

二进制相关的几个主要系统变量

mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /var/lib/mysql/binlog       |
| log_bin_index                   | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF                         |
| sql_log_bin                     | ON                          |
+---------------------------------+-----------------------------+
6 rows in set (0.01 sec)

三、事务的特性和隔离级别

MySQL如何保证ACID
MYSQL是如何实现ACID的？：https://zhuanlan.zhihu.com/p/408357761
mysql事务执行流程

1. 事务的特性

说到MySQL事务的ACID，大家应该都不陌生，有很多人认为必须完全满足ACID才是一个合格的事务，但实际上并不是这样，真正能满足ACID的事务少之又少。可以说ACID并不是事务必须满足的条件，而是用来衡量事务的四个不同维度。

• Atomic 原子性
  一个事务的所有操作步骤被看成是一个动作，所有的步骤要么全部完成要么一个也不会完成。如果事务过程中任何一点失败，将要被改变的数据库记录就不会被真正被改变，而是回到事务执行之前的状态，也就是事务回滚。
• Consistent 一致性
  根据定义，一致性是指事务执行前后，数据处于一种合法的状态，这种状态是语义上的而不是语法上的。
  那什么是合法的数据状态呢？这个状态是满足预定的约束就叫做合法的状态，再通俗一点，这状态是由你自己来定义的。满足这个状态，数据就是一致的，不满足这个状态，数据就是不一致的。
  其实一致性的实现依靠的就是其他3个特性，也可以说一致性是事务的最终目的。
• Isolated 隔离性
  隔离性研究的是不同事务之间的相互影响，主要用于实现并发控制，隔离能够确保并发执行的事务能够顺序一个接一个执行，通过隔离，一个未完成事务不会影响另外一个未完成事务。
  关于隔离性的探讨，主要可以分为两个方面：

• 一个事务写操作对另一个事务写操作的影响：锁机制保证隔离性
• 一个事务写操作对另一个事务读操作的影响：MVCC保证隔离性

• Durable 持久性
  一旦一个事务被提交，它应该持久保存，不会因为和其他操作冲突而取消这个事务。很多人认为这意味着事务是持久在磁盘上，但是规范没有特别定义这点。

2. 事务的隔离级别

下面来聊一下事务的几种隔离级别。

• Read Uncommitted 读未提交
  就是说某个事务还没提交的时候，修改的数据，就让别的事务给读到了，如果这个事务回滚了，就很容易出错。
  该隔离级别很少使用到，也被称为 ​​​脏读​​。
• Read Committed 读已提交
  一个事务开始时，只能“看见”已经提交的事物所做的修改。换句话说一个事务从开始直到提交之前，所做的任何修改都是对其他事务不可见的。
  大多数的数据库默认的隔离级别是读已提交，但是Mysql不是。这个级别也叫作不可重复读。
• Read Repeatable 可重复读
  该级别保证了在同一个事务多次读取同样记录的结果是一致的，哪怕这条记录被其他事务修改并提交了，解决了脏读了问题。
  但是可重复读还是无法解决幻读的问题，​​​幻读​​​ 就是指的是当某个事务在读取某个范围内的记录的时候，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时候就会产生幻行。
  ​​可重复读是Mysql的事务的默认隔离级别​​，InnoDB和XtraDB存储引擎通过版本并发控制解决而了幻读的问题。
• Serializable 串行化
  是最高的隔离级别，通过强制事务串行化执行，避免了前面所说到的幻读的问题。设置该隔离级别，会在读取的每一行数据上都加上锁，但是这样会导致超时和锁争用问题，实际生产环境也很少使用。
• 总结一下几种隔离级别存在的问题： x :不存在，√：存在问题

3. 隔离级别、锁

1. ​​数据库的隔离级别​​
2. ​​MySQL常见的七种锁详细介绍​​

四、索引

1. ​​MySQL索引及常见面试题​​
2. ​​MySQL笔记5–索引​​
3. ​​mysql 五种索引类型介绍和创建​​
4. ​​五种索引的底层实现原理​​
5. ​​mysql中的聚集索引(聚簇索引)、非聚集索引、稀疏索引、稠密索引​​
6. ​​一文搞懂MySQL索引（清晰明了）​​

1、explain 查看索引执行情况

索引 是提高MySQL查询性能的非常有用的一个工具，当我们对数据库中的某些字段建立了索引，那么怎么查看在执行的SQL查询的过程中是否用到了这些索引呢？

查询SQL语句的执行情况通常通过关键字 explain 来进行.

a、建表

例如，如下的数据表

use test;

drop table if exists `student`;
create table `student`
(
  `id`      int not null auto_increment,
  `name`    varchar(50) not null,
  `number`  varchar(20) not null,
  `address` varchar(100),
  `age`     int default 0,
  primary key (`id`)
)Engine=InnoDB DEFAULT CHARSET=utf8;

insert into student (`name`, `number`, `address`, `age`)
values
("马云", "18000001", "浙江省杭州市余杭区", 55),
("马化腾", "18000002", "广东省深圳市南山区", 50),
("张一鸣", "18000003", "北京市海淀区", 38),
("王兴", "18000004", "北京市朝阳区", 40),
("李彦宏", "18000005", "北京市海淀区", 45),
("程维", "18000006", "北京市海淀区", 42),
("雷军", "18000007", "北京市朝阳区", 54),
("刘备", "18000008", "四川省成都市青羊区", 60),
("诸葛亮", "18000009", "四川省成都市武侯区", 43),
("关羽", "18000010", "湖北省荆州市荆州区", 58),
("张飞", "18000011", "四川省阆中市", 56),
("曹操", "18000012", "河南省洛阳市老城区", 63),
("孙权", "18000013", "江苏省南京市建邺区", 49),
("李世民", "18000014", "陕西省西安市长安区", 38),
("李隆基", "18000015", "陕西省西安市长安区", 28),
("朱元璋", "18000016", "江苏省南京市玄武区", 61),
("朱棣", "18000017", "北京市东城区", 39);

b. 对比加索引前后的SQL查询情况

​​EXPLAIN​​ 关键字查看SQL查询过程的情况：

SQL查询 address 为北京市的人：

explain select name, address from student where address like "北京市%";

在 ​​student ​​表上加索引:

alter table student add index (address(9));

为了加快索引，我们采用前缀索引，因为一个汉字在UTF-8编码下占3个字节，因此选择对address字段的前9位加索引。

再次执行上面的SQL查询语句：

c、解释Explain得到的结果

1. ​​type​​​ 反应查询语句的性能
   我们主需要注意一个最重要的的 type 的信息很明显地体现出是否用到了索引：
   ​​​type​​ 结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说，得保证查询至少达到 range 级别，最好能达到 ref 级别，否则就可能出现性能问题。

2. ​​possible_keys​​​: SQL查询时用到的索引。
   可以看到，没加索引时，​​possible_keys​​ 的值为 ​​NULL​​，加了索引后的值为 ​​address​​，即用到了索引address(索引默认为(column_list)中的第一个列的名字).
3. ​​key ​​​显示SQL实际决定查询结果使用的键(索引)。如果没有使用索引，值为NULL
   可以看到，没加索引时，key 的值为 NULL，加了索引后的值为 address，即决定查询结果用到了索引address
4. ​​rows​​​ 显示MySQL认为它执行查询时必须检查的行数
   可以看到，没加索引时，rows 的值为​​17​​，即数据表student中所有数据，说明没加索引时的SQL查询是全表扫描；
   加了索引后，rows 的值为​​6​​，数据库表中address以“北京市”开头的一共也就6条，SQL在执行查询操作时，一共也检查了6行，不必进行全表扫描查询，可以很容易得出结论：加索引的SQL查询性能远高于不加索引的情况。
5. 总结
   通过在SQL查询语句前面添加关键字 explain 就可以分析SQL查询语句的性能了.

2、MySQL 的覆盖索引与回表

a、两大类索引

使用的存储引擎：MySQL5.7 InnoDB

1. 聚簇索引

• 如果表设置了主键，则主键就是聚簇索引
• 如果表没有主键，则会默认第一个NOT NULL，且唯一（UNIQUE） 的列作为聚簇索引
• 以上都没有，则会默认创建一个隐藏的row_id作为聚簇索引

InnoDB的聚簇索引的叶子节点存储的是行记录（其实是页结构，一个页包含多行数据），InnoDB必须要有至少一个聚簇索引。
由此可见，使用聚簇索引查询会很快，因为可以直接定位到行记录。

2. 普通索引

普通索引也叫二级索引，除聚簇索引外的索引，即非聚簇索引。
​​​InnoDB的普通索引叶子节点存储的是主键（聚簇索引）的值​​

b、示例

1. 建表

```sql
mysql> create table user(
    -> id int(10) auto_increment,
    -> name varchar(30),
    -> age tinyint(4),
    -> primary key (id),
    -> index idx_age (age)
    -> )engine=innodb charset=utf8mb4;
```
id 字段是聚簇索引，age 字段是普通索引（二级索引）

2. 填充数据

```sql
insert into user(name,age) values('张三',30);
insert into user(name,age) values('李四',20);
insert into user(name,age) values('王五',40);
insert into user(name,age) values('刘八',10);

mysql> select * from user;
+----+--------+------+
| id | name  | age |
+----+--------+------+
| 1 | 张三  |  30 |
| 2 | 李四  |  20 |
| 3 | 王五  |  40 |
| 4 | 刘八  |  10 |
+----+--------+------+
```

3. 索引存储结构

聚簇索引（ClusteredIndex）

id 是主键，所以是聚簇索引，其叶子节点存储的是对应行记录的数据

普通索引（secondaryIndex）

age 是普通索引（二级索引），非聚簇索引，其叶子节点存储的是聚簇索引的的值

聚簇索引查找过程

如果查询条件为主键（聚簇索引），则只需扫描一次B+树即可通过聚簇索引定位到要查找的行记录数据。
如：select * from user where id = 1;

普通索引查找过程第一步

如果查询条件为普通索引（非聚簇索引），需要扫描两次B+树，第一次扫描通过普通索引定位到聚簇索引的值，然后第二次扫描通过聚簇索引的值定位到要查找的行记录数据。
如：select * from user where age = 30;
​​​注意这里是select * ，即 有些字段不是索引的，所以需要回表查询全量表。​​

1. 先通过普通索引 age=30 定位到主键值 id=1
2. 再通过聚集索引 id=1 定位到行记录数据

普通索引查找过程第二步

c、回表查询

​​先通过查询条件为普通索引的值定位聚簇索引值，再通过聚簇索引的值定位行记录数据，需要扫描两次索引B+树​​，它的性能较扫一遍索引树更低。

也就是 上面的 普通索引查找过程第一步和第二步，这就是回表查询。

d、索引覆盖

​​只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。​​​
例如：select id,age from user where age = 10;

这条sql 使用到了普通索引，age，但是

e、如何实现覆盖索引

常见的方法是：将​​被查询的字段​​​，建立到​​联合索引​​​里去。
1、如实现：select id,age from user where age = 10;
explain分析：因为age是普通索引，使用到了age索引，通过一次扫描B+树即可查询到相应的结果，这样就实现了覆盖索引

create index idx_age on user(`age`);

2、实现：select id,age,name from user where age = 10;
explain分析：age是普通索引，但name列不在索引树上，所以通过age索引在查询到id和age的值后，需要进行回表再查询name的值。此时的Extra列的NULL表示进行了回表查询

为了实现索引覆盖，需要​​建组合索引​​idx_age_name(age,name)

drop index idx_age on user;
create index idx_age_name on user(`age`,`name`);

explain分析：此时字段age和name是组合索引idx_age_name，查询的字段id、age、name的值刚刚都在索引树上，​​只需扫描一次组合索引B+树即可，这就是实现了索引覆盖，此时的Extra字段为Using index表示使用了索引覆盖​​。

3. 哪些场景适合使用索引覆盖来优化SQL

a、全表count查询优化

mysql> create table user(
    -> id int(10) auto_increment,
    -> name varchar(30),
    -> age tinyint(4),
    -> primary key (id),
    -> )engine=innodb charset=utf8mb4;

例如：select count(age) from user;

使用索引覆盖优化：创建age字段索引

​​create index idx_age on user(age);​​

b、列查询回表优化

前文在描述索引覆盖使用的例子就是
例如：select id,age,name from user where age = 10;
使用索引覆盖：建组合索引idx_age_name(age,name)即可

c、分页查询

例如：select id,age,name from user order by age limit 100,2;
因为name字段不是索引，所以在分页查询需要进行回表查询，此时Extra为Using filesort文件排序，查询性能低下。

使用索引覆盖：建组合索引idx_age_name(age,name)

4. 总结

普通索引：造成回表查询
组合索引：进行索引覆盖，进行一次查询。

5. 返璞归真：索引作用的位置

索引是根据你的条件来判断是否使用索引的
举个例子：有个D表，有D1（number），D2(varchar2)，D3(number) 三个字段,其中D1，D2字段有作了索引。

1. ​​select * from D ​​；–这时候尽管表中有索引，但是不会走索引，会全表扫描
2. ​​select D1 from D​​ ;–这时候会走D1索引；
3. ​​select * from D where D1=1​​,–这时候会走D1索引
4. ​​select * from D where D2=1​​,–这时候不会走索引。尽管D2有索引，但是D2是字符型，where条件中是数值型，加上单引就可以走D2索引。
5. update ,delete和select 是同样的。

五、如何优化一段sql

很大一方面是从索引的角度去看，更加细粒度的去看。

回表查询、索引覆盖