MySQL实现原理-存储引擎-数据结构-面试详解_3000w数据索引树高度-优快云博客

与之相反的是，服务器响应给用户的数据通常会很多，由多个数据包组成。但是当服务器响应客户端请求时，客户端必须完整的接受整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。因而在实际开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用SELECT * 以及加上LIMIT限制的原因之一。

2、查询缓存

在解析一个查询语句前，如果查询缓存是打开的，那么MySQL会检查这个查询语句是否命中查询缓存中的数据。如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。这种情况下，查询不会被解析，也不会生成执行计划，更不会执行。

MySQL将缓存存放在一个引用表（类似于HashMap的数据结构），通过一个哈希值索引，这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同（空格、注释），都会导致缓存不会命中。

如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表，其查询结果都不会被缓存。比如函数NOW()或者CURRENT_DATE()会因为不同的查询时间，返回不同的查询结果，再比如包含CURRENT_USER或者CONNECION_ID()的查询语句会因为不同的用户而返回不同的结果，将这样的查询结果缓存起来没有任何的意义。

不要轻易打开查询缓存，特别是写密集型应用。如果实在是忍不住，可以将query_cache_type 设置为DEMAND，这时只有加入SQL_CACH的查询才会走缓存，其他查询则不会，这样可以非常自由地控制哪些查询需要被缓存。

3、语法解析和预处理

MySQL通过关键字将SQL语句进行解析，并生成一颗对应的解析树。这个过程解析器主要通过语法规则来验证和解析。比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。预处理则会根据MySQL规则进一步检查解析树是否合法。比如检查要查询的数据表和数据列是否存在等等。

4、查询优化

语法树被认为是合法之后，并且有优化器将其转化成查询计划，多数情况下，一条查询可以有很多种执行方式，最后都返回相应的结果，优化器的作用就是找到这其中最好的执行计划。

MySQL使用基于成本的优化器，它尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。在MySQL可以通过查询当前会话的last_query_cost的值来得到其计算当前查询的成本。（show status like 'last_query_cost'）

MySQL的查询优化器是一个非常复杂的部件，它使用了非常多的优化策略来生成一个最优的执行计划：

1.重新定义表的关联顺序（多张表关联查询时，并不一定按照SQL中指定的顺序进行，但有一些技巧可以指定关联顺序）

2.优化MIN()和MAX()函数（找某列的最小值，如果该列有索引，只需要查找B+Tree索引最左端，反之则可以找到最大值）

3.提前终止查询（使用Limit时，查找到满足数量的结果集后会立即终止查询）

4.优化排序（在老版本MySQL会使用两次传输排序，即先读取行指针和需要排序的字段在内存中对其排序，然后再根据排序结果去读取数据行，而新版本采用的是单次传输排序，也就是一次读取所有的数据行，然后根据给定的列排序）

5、查询执行引擎

在完成解析和优化阶段以后，MySQL会生成对应的执行计划，查询执行引擎根据执行计划给出的指令逐步执行得出结果。整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成，这些接口被称为handler API。查询过程中的每一张表由一个handler实例表示，实际上，MySQL在查询优化阶段就为每一张表创建了一个handler实例，优化器可以根据这些实例的接口来获取表的相关信息，包括表的所有列名、索引统计信息等。存储引擎接口提供了非常丰富的功能，但其底层仅有几十个接口，这些接口像塔积木一样完成了一次查询的大部分操作。

6、MySQL整个查询执行过程

1.客户端向MySQL服务器发送一条查询请求

2.服务器首先先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一级段

3.服务器进行SQL解析、预处理、再由优化器生成对应的执行计划

4.MySQL根据执行计划，调用存储引擎的API来执行查询

5.将结果返回给客户端，同时缓存查询结果

二、存储引擎

1、MySQL存储引擎

MySQL支持多种存储引擎,比如InnoDB,MyISAM,Memory,Archive等等.在大多数的情况下,直接选择使用InnoDB引擎都是最合适的,InnoDB也是MySQL的默认存储引擎.

2、InnoDB和MyISAM区别

InnoDB支持事物，而MyISAM不支持事物

InnoDB支持行级锁，而MyISAM支持表级锁

InnoDB支持MVCC, 而MyISAM不支持

InnoDB支持外键，而MyISAM不支持

InnoDB不支持全文索引，而MyISAM支持。

3、innodb引擎的4大特性

插入缓冲（insert buffer)

InnoDB设计出的插入缓冲技术，对于非聚集类索引的插入和更新操作，不是每一次都直接插入到索引页中，而是先插入到内存中。具体做法是：如果该索引页在缓冲池中，直接插入；否则，先将其放入插入缓冲区中，再以一定的频率和索引页合并，这时，就可以将同一个索引页中的多个插入合并到一个IO操作中，大大提高写性能。

插入缓冲的启用需要满足一下两个条件：
1）索引是辅助索引（secondary index）
2）索引不适合唯一的
如果辅助索引是唯一的，就不能使用该技术，原因很简单，因为如果这样做，整个索引数据被切分为2部分，无法保证唯一性。

任何一项技术在带来好处的同时，必然也带来坏处。插入缓冲主要带来如下两个坏处：

1）可能导致数据库宕机后实例恢复时间变长。如果应用程序执行大量的插入和更新操作，且涉及非唯一的聚集索引，一旦出现宕机，这时就有大量内存中的插入缓冲区数据没有合并至索引页中，导致实例恢复时间会很长。
2）在写密集的情况下，插入缓冲会占用过多的缓冲池内存，默认情况下最大可以占用1/2，这在实际应用中会带来一定的问题。

二次写(double write)

在写数据页之前，先把这个数据页写到一块独立的物理文件位置（ibdata），然后再写到数据页。这样在宕机重启时，如果出现数据页损坏，那么在应用redo log之前，需要通过该页的副本来还原该页，然后再进行redo log重做，这就是double write。double write技术带给innodb存储引擎的是数据页的可靠性。

自适应哈希索引(ahi)

自适应哈希索引采用之前哈希表的方式实现，不同的是，这仅是数据库自身创建并使用的，DBA本身并不能对其进行干预。自适应哈希索引近哈希函数映射到一个哈希表中，因此对于字典类型的查找非常快速，如SELECT * FROM TABLE WHERE index_col='xxx'但是对于范围查找就无能为力。通过SHOW ENGINE INNODB STATUS 可以看到当前自适应哈希索引的使用情况

预读(read ahead)

InnoDB在I/O的优化上相对之前有着独特的预读机制，预读机制就是发起一个i/o请求，异步地在缓冲池中预先回迁若干页面，预计将会用到回迁的页面，这些请求在一个范围内引入所有页面。

4、innodb的读写参数优化

缓存参数以及缓存的适用场景。query cache/query_cache_type并不是所有表都适合使用query cache。造成query cache失效的原因主要是相应的table发生了变更

第一个：读操作多的话看看比例，简单来说，如果是用户清单表，或者说是数据比例比较固定，比如说商品列表，是可以打开的，前提是这些库比较集中，数据库中的实务比较小。

第二个：我们“行骗”的时候，比如说我们竞标的时候压测，把query cache打开，还是能收到qps激增的效果，当然前提示前端的连接池什么的都配置一样。大部分情况下如果写入的居多，

访问量并不多，那么就不要打开，例如社交网站的，10%的人产生内容，其余的90%都在消费，打开还是效果很好的，但是你如果是qq消息，或者聊天，那就很要命。

第三个：小网站或者没有高并发的无所谓，高并发下，会看到很多 qcache 锁等待，所以一般高并发下，不建议打开query cache

5、MySQL 日志与事务

日志类型

MySQL中有六种日志文件，分别是：重做日志（redo log）、回滚日志（undo log）、二进制日志（binlog）、错误日志（errorlog）、慢查询日志（slow query log）、一般查询日志（general log），中继日志（relay log）。其中重做日志和回滚日志与事务操作息息相关，二进制日志也与事务操作有一定的关系，这三种日志，对理解MySQL中的事务操作有着重要的意义。

重做日志（redo log）

确保事务的持久性。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。

回滚日志（undo log）

保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读。

二进制日志（binlog）

记录对数据库执行更改的所有操作。用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步，也可用于数据库的基于时间点的还原。

错误日志（errorlog）

记录出错信息，也记录一些警告信息或者正确的信息。

慢查询日志（slow query log）

设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中。

一般查询日志（general log）

记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行。

中继日志（relay log）

中继日志也是二进制日志，用来给slave 库恢复。从服务器I/O线程将主服务器的二进制日志读取过来记录到从服务器本地文件，然后从服务器SQL线程会读取relay-log日志的内容并应用到从服务器，从而使从服务器和主服务器的数据保持一致。

日志与事务

事务日志是通过redo、undo和innodb的存储引擎日志缓冲（Innodb log buffer）来实现的，当开始一个事务的时候，会记录该事务的lsn(log sequence number)号;当事务执行时，会往InnoDB存储引擎的日志的日志缓存里面插入事务日志；当事务提交时，必须将存储引擎的日志缓冲写入磁盘（通过innodb_flush_log_at_trx_commit来控制），也就是写数据前，需要先写日志。这种方式称为“预写日志方式”

总结

日志直接性能损耗数据库系统中最为昂贵的IO 资源，在默认情况下，系统仅打开错误日志，关闭了其他所有日志，以达到尽可能减少IO 损耗提高系统性能的目的。

但在实际应用场景中，都至少需要打开二进制日志，因为这是MySQL 很多存储引擎进行增量备份的基础，也是MySQL 实现复制的基本条件。有时候为了进一步的性能优化，定位执行较慢的SQL 语句&#x