16.一个B+树中大概能存放多少条索引记录？

MySQL设计者将一个B+Tree的节点的大小设置为等于一个. (这样做的目的是每个节点只需要一次I/O就可以完全载入), InnoDB的一个的大小是16KB,所以每个节点的大小也是16KB, 并且B+Tree的根节点是保存在内存中的,子节点才是存储在磁盘上.

假设一个B+树高为2，即存在一个根节点和若干个叶子节点，那么这棵B+树的存放总记录数为：

根节点指针数*单个叶子节点记录行数.

计算根节点指针数: 假设表的主键为INT类型,占用的就是4个字节,或者是BIGINT占用8个字节, 指针大小为6个字节,那么一个(就是B+Tree中的一个节点) ,大概可以存储: 16384B / (4B+6B) = 1638 ,一个节点最多可以存储1638个索引指针.

计算每个叶子节点的记录数:我们假设一行记录的数据大小为1k,那么一就可以存储16行数据,16KB / 1KB = 16.

一颗高度为2的B+Tree可以存放的记录数为: 1638 * 16=26208 条数据记录, 同样的原理可以推算出一个高度3的B+Tree可以存放: 1638 *1638 * 16 = 42928704条这样的记录.

所以InnoDB中的B+Tree高度一般为1-3层,就可以满足千万级别的数据存储,在查找数据时一次的查找代表一次 IO，所以通过主键索引查询通常只需要 1-3 次 IO 操作即可查找到数据。

17.explain 用过吗，有哪些主要字段？

使用 EXPLAIN 关键字可以模拟优化器来执行SQL查询语句，从而知道MySQL是如何处理我们的SQL语句的。分析出查询语句或是表结构的性能 瓶颈。

MySQL查询过程

通过explain我们可以获得以下信息：

1.表的读取顺序

2.数据读取操作的操作类型

3.哪些索引可以被使用

4.哪些索引真正被使用

5.表的直接引用每张表的有多少行被优化器查询了

Explain使用方式: explain+sql语句, 通过执行explain可以获得sql语句执行的相关信息

explain select * from users;

18.type字段中有哪些常见的值？

type字段在 MySQL 官网文档描述如下：

The join type. For descriptions of the different types.

type字段显示的是连接类型 ( join type表示的是用什么样的方式来获取数据)，它描述了找到所需数据所使用的扫描方式, 是较为重要的一个指标。

下面给出各种连接类型,按照从最佳类型到最坏类型进行排序:

一般来说,需要保证查询至少达到 range级别,最好能到ref,否则就要就行SQL的优化调整

下面介绍type字段不同值表示的含义：

19.Extra有哪些主要指标，各自的含义是什么？

Extra 是 EXPLAIN 输出中另外一个很重要的列，该列显示MySQL在查询过程中的一些详细信息

20.如何进行分页查询优化？

（1）一般性分

一般的分查询使用简单的 limit 子句就可以实现。limit格式如下：

SELECT * FROM 表名 LIMIT [offset,] rows

1.第一个参数指定第一个返回记录行的偏移量，注意从0开始；

2.第二个参数指定返回记录行的最大数目；

3.如果只给定一个参数，它表示返回最大的记录行数目；

思考1：如果偏移量固定，返回记录量对执行时间有什么影响？

结果：在查询记录时，返回记录量低于100条，查询时间基本没有变化，差距不大。随着查询记录量越大，所花费的时间也会越来越多。

思考2：如果查询偏移量变化，返回记录数固定对执行时间有什么影响？

结果：在查询记录时，如果查询记录量相同，偏移量超过100后就开始随着偏移量增大，查询时间急剧的增加。（这种分查询机制，每次都会从数据库第一条记录开始扫描，越往后查询越慢，而且查询的数据越多，也会拖慢总查询速度。）

（2）分优化方案

优化1: 通过索引进行分

直接进行limit操作 会产生全表扫描,速度很慢. Limit限制的是从结果集的M位置处取出N条输出,其余抛弃.

假设ID是连续递增的,我们根据查询的数和查询的记录数可以算出查询的id的范围，然后配合 limit使用

EXPLAIN SELECT * FROM user WHERE id >= 100001 LIMIT100;

优化2：利用子查询优化

原因：使用了id做主键比较(id>=)，并且子查询使用了覆盖索引进行优化。

21.如何做慢查询优化？

MySQL 慢查询的相关参数解释：

1.slow_query_log：是否开启慢查询日志，ON(1)表示开启,OFF(0) 表示关闭。

2.slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。

3.long_query_time： 慢查询阈值，当查询时间多于设定的阈值时，记录日志。

慢查询配置方式

1. 默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的

2. 可以通过设置slow_query_log的值来开启

mysql> set global slow_query_log=1; 

3. 使用 set global slow_query_log=1 开启了慢查询日志只对当前数据库生效，MySQL重启后则会失效。如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）

4. 那么开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里面呢？ 这个是由参数 long_query_time控制，默认情况下long_query_time的值为10秒.

5. 修改了变量long_query_time，但是查询变量long_query_time的值还是10，难道没有修改到呢？注意：使用命令 set globallong_query_time=1 修改后，需要重新连接或新开一个会话才能看到修改值。

6. log_output 参数是指定日志的存储方式。log_output='FILE' 表示将日志存入文件，默认值是'FILE'。log_output='TABLE' 表示将日志存入数据库，这样日志信息就会被写入到 mysql.slow_log 表中。

MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output='FILE,TABLE'。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件.

7. 系统变量
log-queries-not-using-indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。如果调优的话，建议开启这个选项。

3) 慢查询测试

1. 执行 test_index.sql 脚本,监控慢查询日志内容

2. 执行下面的SQL,执行超时 (超过1秒) 我们去查看慢查询日志

3. 日志内容

我们得到慢查询日志后，最重要的一步就是去分析这个日志。我们先来看下慢日志里到底记录了哪些内容。

如下图是慢日志里其中一条SQL的记录内容，可以看到有时间戳，用户，查询时及具体的SQL等信息.

1.Time: 执行时间

2.User: 用户信息 ,Id信息

3.Query_time: 查询时

4.Lock_time: 等待锁的时

5.Rows_sent:查询结果的行数

6.Rows_examined: 查询扫描的行数

7.SET timestamp: 时间戳

8.SQL的具体信息

慢查询SQL优化思路

1) SQL性能下降的原因

在日常的运维过程中，经常会遇到DBA将一些执行效率较低的SQL发过来找开发人员分析，当我们拿到这个SQL语句之后，在对这些SQL进行分析之前，需要明确可能导致SQL执行性能下降的原因进行分析，执行性能下降可以体现在以下两个方面：

等待时间

锁表导致查询一直处于等待状态，后续我们从MySQL锁的机制去分析SQL执行的原理

执行时间

2) 慢查询优化思路

1. 优先选择优化高并发执行的SQL,因为高并发的SQL发生问题带来后果更严重

2. 定位优化对象的性能瓶颈(在优化之前了解性能瓶颈在哪)

3. 明确优化目标

4. 从explain执行计划入手

5. 永远用小的结果集驱动大的结果集

6. 尽可能在索引中完成排序

7. 只获取自己需要的列

不要使用select * ,select * 很可能不走索引,而且数据量过大

8. 只使用最有效的过滤条件

误区 where后面的条件越多越好,但实际上是应该用最短的路径访问到数据

9. 尽可能避免复杂的join和子查询

10. 合理设计并利用索引

22.Hash索引有哪些优缺点？

MySQL中索引的常用数据结构有两种: 一种是B+Tree,另一种则是Hash.Hash底层实现是由Hash表来实现的，是根据键值 <key,value> 存储数据的 结构。非常适合根据key查找value值，也就是单个key查询，或者说等值查询。

对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值,如果出现哈希码值相同的情况会拉出一条链表.

Hsah索引的优点

1.因为索引自身只需要存储对应的Hash值,所以索引结构非常紧凑, 只需要做等值比较查询，而不包含排序或范围查询的需求，都适合使用哈希索引 .

2.没有哈希冲突的情况下,等值查询访问哈希索引的数据非常快.(如果发生Hash冲突,存储引擎必须遍历链表中的所有行指针,逐行进行比较,直到找到所有符合条件的行).

Hash索引的缺点

1.哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。

2.哈希索引只支持等值比较查询。不支持任何范围查询和部分索引列匹配查找。

3.哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。

23.说一下InnoDB内存相关的参数优化？

1.1 缓冲池内存大小配置

一个大的日志缓冲区允许大量的事务在提交之前不写日志到磁盘。因此，如果你有很多事务的更新，插入或删除操作，通过设置这个参数会大量的减少磁盘I/O的次数数。

建议: 在专用数据库服务器上，可以将缓冲池大小设置为服务器物理内存的60% - 80%

1.查看缓冲池大小

2.在线调整InnoDB缓冲池大小innodb_buffer_pool_size可以动态设置，允许在不重新启动服务器的情况下调整缓冲池的大小.

1.2监控在线调整缓冲池的进度

1.3 InnoDB 缓存性能评估

当前配置的innodb_buffer_pool_size是否合适，可以通过分析InnoDB缓冲池的缓存命中率来验证。

以下公式计算InnoDB buffer pool 命中率:

1.4 Page管理相关参数

查看Page的大小(默认16KB), innodb_page_size只能在初始化MySQL实例之前配置，不能在之后修改。如果没有指定值，则使用默认面大小初始化实例。

Page管理状态相关参数

1.pages_data: InnoDB缓冲池中包含数据的数。 该数字包括脏面和干净面。

2.pages_dirty: 显示在内存中修改但尚未写入数据文件的InnoDB缓冲池数据的数量（脏刷新）。

3.pages_flushed: 表示从InnoDB缓冲池中刷新脏的请求数。

4.pages_free: 显示InnoDB缓冲池中的空闲面

5.pages_misc: 缓存池中当前已经被用作管理用途或hash index而不能用作为普通数据的数目pages_total: 缓存池的总数目。单位是page。

24.InnoDB日志相关的参数优化了解过吗？

1.日志缓冲区相关参数配置

日志缓冲区的大小。一般默认值16MB是够用的，但如果事务之中含有blog/text等大字段，这个缓冲区会被很快填满会引起额外的IO负载。配置更大的日志缓冲区,可以有效的提高MySQL的效率.

innodb_log_buffer_size 缓冲区大小

innodb_log_files_in_group 日志组文件个数日志组根据需要来创建。而日志组的成员则需要至少2个，实现循环写入并作为冗余策略。

innodb_log_file_size 日志文件大小

参数innodb_log_file_size用于设定MySQL日志组中每个日志文件的大小(默认48M)。此参数是一个全局的静态参数，不能动态修改。

参数innodb_log_file_size的最大值，二进制日志文件大小（innodb_log_file_size * innodb_log_files_in_group）不能超过

512GB.所以单个日志文件的大小不能超过256G.

2.日志文件参数优化

首先我们先来看一下日志文件大小设置对性能的影响

设置过小

1. 参数 innodb_log_file_size设置太小，就会导致MySQL的日志文件( redo log）频繁切换，频繁的触发数据库的检查点（Checkpoint），导致刷新脏到磁盘的次数增加。从而影响IO性能。

2. 处理大事务时，将所有的日志文件写满了，事务内容还没有写完，这样就会导致日志不能切换.

设置过大

参数 innodb_log_file_size如果设置太大，虽然可以提升IO性能，但是当MySQL由于意外宕机时，二进制日志很大，那么恢复的时间必然很。而且这个恢复时间往往不可控，受多方面因素影响。

优化建议:

如何设置合适的日志文件大小 ?

根据实际生产场景的优化经验,一般是计算一段时间内生成的事务日志（redo log）的大小， 而MySQL的日志文件的大小最少应该承载一个小时的业务日志量(官网文档中有说明)。

想要估计一下InnoDB redo log的大小，需要抓取一段时间内LogSequenceNumber（日志顺序号）的数据,来计算一小时内产生的日志大小.

Log sequence number
自系统修改开始，就不断的生成redo日志。为了记录一共生成了多少日志，于是mysql设计了全局变量log sequence number，简称lsn，但不是从0开始，是从8704字节开始。

有了一分钟的日志量,据此推算一小时内的日志量

太大的缓冲池或非常不正常的业务负载可能会计算出非常大(或非常小)的日志大小。这也是公式不足之处，需要根据判断和经验。但这个计算方法是一个很好的参考标准。

25.InnoDB IO线程相关参数优化了解过吗？

数据库属于 IO 密集型的应用程序，其主要职责就是数据的管理及存储工作。从内存中读取一个数据库数据的时间是微秒级别，而从一块普通硬盘上读取一个IO是在毫秒级别。要优化数据库，IO操作是必须要优化的，尽可能将磁盘IO转化为内存IO。

1) 参数: query_cache_size&have_query_cache

MySQL查询缓存会保存查询返回的完整结果。当查询命中该缓存，会立刻返回结果，跳过了解析，优化和执行阶段。

查询缓存会跟踪查询中涉及的每个表，如果这些表发生变化，那么和这个表相关的所有缓存都将失效。

1. 查看查询缓存是否开启

2. 开启缓存,在my.ini中添加下面一行参数

3. 测试能否缓存查询

1.Qcache_free_blocks:缓存中目前剩余的blocks数量（如果值较大，则查询缓存中的内存碎片过多）

2.Qcache_free_memory:空闲缓存的内存大小

3.Qcache_hits:命中缓存次数

4.Qcache_inserts: 未命中然后进行正常查询

5.Qcache_lowmem_prunes:查询因为内存不足而被移除出查询缓存记录

6.Qcache_not_cached: 没有被缓存的查询数量

7.Qcache_queries_in_cache:当前缓存中缓存的查询数量

8.Qcache_total_blocks:当前缓存的block数量

优化建议: Query Cache的使用需要多个参数配合，其中最为关键的是query_cache_size 和 query_cache_type ，前者设置用于缓存 ResultSet的内存大小，后者设置在何场景下使用 Query Cache。

MySQL数据库数据变化相对不多，query_cache_size 一般设置为256MB比较合适 ,也可以通过计算Query Cache的命中率来进行调整

( Qcache_hits / ( Qcache_hits + Qcache_inserts ) * 100) )

2. 参数:
innodb_max_dirty_pages_pct 该参数是InnoDB 存储引擎用来控制buffer pool中脏的百分比，当脏数量占比超过这个参数设置的值时，InnoDB会启动刷脏的操作。

优化建议: 该参数比例值越大，从内存到磁盘的写入操作就会相对减少，所以能够一定程度下减少写入操作的磁盘IO。但是，如果这个比例值过大，当数据库 Crash 之后重启的时间可能就会很，因为会有大量的事务数据需要从日志文件恢复出来写入数据文件中.最大不建议超过90,一般重启恢复的数据在超过1GB的话,启动速度就会变慢.

3) 参数: innodb_old_blocks_pct&innodb_old_blocks_time

innodb_old_blocks_pct 用来确定LRU链表中old sublist所占比例,默认占用37%

innodb_old_blocks_time 用来控制old sublist中page的转移策略，新的page在进入LRU链表中时，会先插入到old sublist的头部，然后page需要在old sublist中停留innodb_old_blocks_time这么久后，下一次对该page的访问才会使其移动到new sublist的头部，默认值1秒.

优化建议: 在没有大表扫描的情况下，并且数据多为频繁使用的数据时，我们可以增加innodb_old_blocks_pct的值，并且减小innodb_old_blocks_time的值。让数据能够更快和更多的进入的热点数据区。

26.什么是写失效？

InnoDB的和操作系统的大小不一致，InnoDB大小一般为16K，操作系统大小为4K，InnoDB的写入到磁盘时，一个需要分4次写。

如果存储引擎正在写入的数据到磁盘时发生了宕机，可能出现只写了一部分的情况，比如只写了4K，就宕机了，这种情况叫做部分写失效（partial page write），可能会导致数据丢失。

双写缓冲区 Doublewrite Buffer

为了解决写失效问题，InnoDB实现了double write buffer Files, 它位于系统表空间，是一个存储区域。

在BufferPool的page刷新到磁盘真正的位置前，会先将数据存在Doublewrite 缓冲区。这样在宕机重启时，如果出现数据损坏，那么在应用redo log之前，需要通过该的副本来还原该，然后再进行redo log重做，double write实现了InnoDB引擎数据的可靠性.

默认情况下启用双写缓冲区，如果要禁用Doublewrite 缓冲区，可以将innodb_doublewrite设置为0。

数据双写流程

step1：当进行缓冲池中的脏刷新到磁盘的操作时,并不会直接写磁盘,每次脏刷新必须要先写double write .

step2：通过memcpy函数将脏复制到内存中的double write buffer .

step3: double write buffer再分两次、每次1MB, 顺序写入共享表空间的物理磁盘上, 第一次写.

step4: 在完成double write的写入后，再将double wirite buffer中的写入各个表的独立表空间文件中(数据文件 .ibd), 第二次写。

为什么写两次 ?

可能有的同学会有疑问，为啥写两次，刷一次数据文件保存数据不就可以了，为什么还要写共享表空间 ?其实是因为共享表空间是在ibdbata文件中划出2M连续的空间，专给double write刷脏用的, 由于在这个过程中，double write的存储是连续的，因此写入磁盘为顺序写，性能很高；完成double write后，再将脏写入实际的各个表空间文件，这时写入就是离散的了.

27.什么是行溢出？

行记录格式

1) 行格式分类

表的行格式决定了它的行是如何物理存储的，这反过来又会影响查询和DML操作的性能。如果在单个page中容纳更多行，查询和索引查找可以更快地工作，缓冲池中所需的内存更少，写入更新时所需的I/O更少。

InnoDB存储引擎支持四种行格式：Redundant、Compact、Dynamic 和Compressed .

查询MySQL使用的行格式,默认为: dynamic

指定行格式语法

2) COMPACT 行记录格式

Compact 设计目标是高效地存储数据，一个中存放的行数据越多，其性能就越高。

Compact行记录由两部分组成: 记录放入额外信息 和 记录的真实数据.

记录额外信息部分

服务器为了描述一条记录而添加了一些额外信息(元数据信息)，这些额外信息分为3类，分别是: 变字段度列表、NULL值列表和记录头信息.

变字段度列表

MySQL支持一些变的数据类型，比如VARCHAR(M)、VARBINARY(M)、各种TEXT类型，各种BLOB类型，这些变的数据类型占用的存储空间分为两部分：

1. 真正的数据内容

2. 占用的字节数

变字段的度是不固定的，所以在存储数据的时候要把这些数据占用的字节数也存起来，读取数据的时候才能根据这个度列表去读取对应度的数据。

在 Compact行格式中，把所有变类型的列的度都存放在记录的开头部位形成一个列表，按照列的顺序逆序存放,这个列表就是 变字段度列表。

NULL值列表

表中的某些列可能会存储NULL值，如果把这些NULL值都放到记录的真实数据中会比较浪费空间，所以Compact行格式把这些值为NULL的列存储到NULL值列表中。( 如果表中所有列都不允许为 NULL，就不存在NULL值列表 )

记录头信息

记录头信息是由固定的5个字节组成，5个字节也就是40个二进制位，不同的位代表不同的意思，这些头信息会在后面的一些功能中看到。

1. delete_mask

这个属性标记着当前记录是否被删除，占用1个二进制位，值为0 的时候代表记录并没有被删除，为1 的时候代表记录被删除掉了

2. min_rec_mask

B+树的每层非叶子节点中的最小记录都会添加该标记。

3. n_owned

代表每个分组里，所拥有的记录的数量，一般是分组里主键最大值才有的。

4. heap_no在数据的User Records中插入的记录是一条一条紧凑的排列的，这种紧凑排列的结构又被称为堆。为了便于管理这个堆，把记录在堆中的相对位置给定一个编号——heap_no。所以heap_no这个属性表示当前记录在本中的位置。

5. record_type

这个属性表示当前记录的类型，一共有4种类型的记录， 0 表示普通用户记录， 1 表示B+树非叶节点记录， 2 表示最小记录， 3 表示最大记录。

6. next_record

表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量，可以理解为指向下一条记录地址的指针。值为正数说明下一条记录在当前记录后面，为负数说明下一条记录在当前记录的前面。

记录真实数据部分

记录的真实数据除了插入的那些列的数据，MySQL会为每个记录默认的添加一些列（也称为隐藏列），具体的列如下：

生成隐藏主键列的方式有：

3) Compact中的行溢出机制

什么是行溢出 ?

MySQL中是以为基本单位,进行磁盘与内存之间的数据交互的,我们知道一个的大小是16KB,16KB = 16384字节.而一个varchar(m) 类型列最多可以存储65532个字节,一些大的数据类型比如TEXT可以存储更多.

如果一个表中存在这样的大字段,那么一个就无法存储一条完整的记录.这时就会发生行溢出,多出的数据就会存储在另外的溢出中.

总结: 如果某些字段信息过，无法存储在B树节点中，这时候会被单独分配空间，此时被称为溢出，该字段被称为外列。

Compact中的行溢出机制

InnoDB 规定一至少存储两条记录(B+树特点)，如果中只能存放下一条记录，InnoDB存储引擎会自动将行数据存放到溢出中.

当发生行溢出时，数据只保存了前768字节的前缀数据，接着是20个字节的偏移量，指向行溢出.

28.如何进行JOIN优化？

JOIN 是 MySQL 用来进行联表操作的，用来匹配两个表的数据，筛选并合并出符合我们要求的结果集。

JOIN 操作有多种方式，取决于最终数据的合并效果。常用连接方式的有以下几种:

什么是驱动表 ?

1.多表关联查询时,第一个被处理的表就是驱动表,使用驱动表去关联其他表.

2.驱动表的确定非常的关键,会直接影响多表关联的顺序,也决定后续关联查询的性能

驱动表的选择要遵循一个规则:

1.在对最终的结果集没有影响的前提下,优先选择结果集最小的那张表作为驱动表

3) 三种JOIN算法

1.Simple Nested-Loop Join（ 简单的嵌套循环连接 )

简单来说嵌套循环连接算法就是一个双层for 循环 ，通过循环外层表的行数据，逐个与内层表的所有行数据进行比较来获取结果.

这种算法是最简单的方案，性能也一般。对内循环没优化。

例如有这样一条SQL:

转换成代码执行时的思路是这样的：

匹配过程如下图

SNL 的特点

简单粗暴容易理解，就是通过双层循环比较数据来获得结果

查询效率会非常慢,假设 A 表有 N 行，B 表有 M 行。SNL 的开销如下：

1.A 表扫描 1 次。

2.B 表扫描 M 次。

3.一共有 N 个内循环，每个内循环要 M 次，一共有内循环 N * M次

2) Index Nested-Loop Join（ 索引嵌套循环连接 ）

Index Nested-Loop Join 其优化的思路: 主要是为了减少内层表数据的匹配次数 , 最大的区别在于，用来进行 join 的字段已经在被驱动表中建立了索引。

从原来的 匹配次数 = 外层表行数 * 内层表行数 , 变成了 匹配次数 = 外层表的行数 * 内层表索引的高度 ，极大的提升了 join的性能。

当 order 表的 user_id 为索引的时候执行过程会如下图：

注意：使用Index Nested-Loop Join 算法的前提是匹配的字段必须建立了索引。

3) Block Nested-Loop Join( 块嵌套循环连接 )

如果 join 的字段有索引，MySQL 会使用 INL 算法。如果没有的话，MySQL 会如何处理？

因为不存在索引了，所以被驱动表需要进行扫描。这里 MySQL 并不会简单粗暴的应用 SNL 算法，而是加入了 buffer 缓冲区，降低了内循环的个数，也就是被驱动表的扫描次数。

1.在外层循环扫描 user表中的所有记录。扫描的时候，会把需要进行 join用到的列都缓存到 buffer 中。buffer 中的数据有一个特点，里面的记录不需要一条一条地取出来和 order 表进行比较，而是整个 buffer 和order表进行批量比较。

2.如果我们把 buffer 的空间开得很大，可以容纳下 user 表的所有记录，那么 order 表也只需要访问一次。

3.MySQL 默认 buffer 大小 256K，如果有 n 个 join 操作，会生成 n-1 个join buffer。

4) JOIN优化总结

1. 永远用小结果集驱动大结果集(其本质就是减少外层循环的数据数量)

2. 为匹配的条件增加索引(减少内层表的循环匹配次数)

3. 增大join buffer size的大小（一次缓存的数据越多，那么内层包的扫表次数就越少）

4. 减少不必要的字段查询（字段越少，join buffer 所缓存的数据就越多）

29.索引哪些情况下会失效？

1. 查询条件包含 or，会导致索引失效。

2. 隐式类型转换，会导致索引失效，例如 age 字段类型是 int，我们where age = “1”，这样就会触发隐式类型转换

3. like 通配符会导致索引失效，注意:”ABC%” 不会失效，会走 range 索引，”% ABC” 索引会失效

4. 联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。

5. 对索引字段进行函数运算。

6. 对索引列运算（如，+、-、*、/），索引失效。

7. 索引字段上使用（!= 或者 < >，not in）时，会导致索引失效。

8. 索引字段上使用 is null， is not null，可能导致索引失效。

9. 相 join 的两个表的字符编码不同，不能命中索引，会导致笛卡尔积的循

10. mysql 估计使用全表扫描要比使用索引快，则不使用索引。

30.什么是覆盖索引？

覆盖索引是一种避免回表查询的优化策略: 只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

具体的实现方式:

将被查询的字段建立普通索引或者联合索引，这样的话就可以直接返回索引中的的数据，不需要再通过聚集索引去定位行记录，避免了回表的情况发生。

覆盖索引的定义与注意事项:

如果一个索引包含了 所有需要查询的字段的值 (不需要回表)，这个索引EXPLAIN SELECT user_name,user_age,user_level FROM usersWHERE user_name = 'tom' AND user_age = 17;就是覆盖索引。

MySQL只能使用B+Tree索引做覆盖索引 (因为只有B+树能存储索引列值)

在explain的Extra列, 如果出现 ** Using index 表示 使用到了覆盖索引 , 所取的数据完全在索引中就能拿到

由于篇幅有限，小编已将上面介绍的所以MySQL面试问题真理好了全部都是PDF文档，

有需要的可以转发此文关注小编，私信小编【学习】免费获取！