本文转载自微信公众号“是的'练级攻略”，作者是是的。转载本文请联系yes的练级指导公众号。你好，我是。首先，从一张图片开始。此图为第一版。我想更详细地填写它，但它看起来多余，所以让我们现在开始吧。主要用来标记一些关键点，以供复习。其实对于我们的后端开发，我们和MySQL的接触很多，但是接触的不多。接触多，就是我们经常写SQL，一直在用。接触不多意味着我们只写SQL。一些配置相关的事情，包括一手掌控，都是DBA们做的。在本系列文章中，我会筛选出与我们开发密切相关的MySQL知识点。我打算先做一个overview，只有BFS，也就是说细节不会很细，先把surface补全。等下一篇再慢慢DFS，一个一个破解。当然面试题也会同步更新，后面还会有空投。MySQL架构非常重要。只有理解了，才能理解后面的一些知识点，比如索引下推。MySQL架构可以分为服务器和存储引擎两部分。客户端与MySQL建立连接后，一条SQL语句从客户端通过TCP传输到服务器端，服务器端首先对语句进行词法分析和语法分析，由分析器完成。如果语法有问题，相信大家对这个错误都不陌生：YouhaveanerrorinyourSQLsyntax;查手册...确认语法无误后，优化器再决定语句是否需要重写，如何选择驱动表，如何选择合适的索引等操作，目的是为了使语句更高效。我们平日用的explain其实就是让MySQL告诉我们它的优化决策策略是什么。至此，MySQL已经知道该做什么，该怎么做了。到了执行器工作的时间了，它会调用存储引擎的接口来执行语句。第一个关键点来了。比如我现在想执行一个select*fromyeswherename='yeslevelingstrategy';这条语句，名称列没有索引。此时的流程是：服务端调用存储引擎的接口返回表的第一行，此时服务端获取到第一行数据。服务端通过where条件判断name是否等于yes的分级策略。如果是，则放入结果集中，如果不是，则跳过。服务端继续调用存储引擎的接口获取下一行!，然后通过where条件进行判断。重复此循环，直到记录完最后一行。它不会等到所有的结果都收集完了再返回给客户端，当net_buffersize满了才会发送结果，也就是边检查边发送。从上面的过程我们可以知道，where的条件如果没有使用索引，是在server层进行过滤的。如果平日看到exlplain中extra中使用wherefromtheextra，说明是在server层使用where进行过滤。.然后是存储引擎的接口。MySQL的存储引擎是插件式的。一个数据库中不同的表可以使用不同的存储引擎，而服务器是一样的，所以需要指定一个统一的接口，让服务器可以调用不同的存储引擎。如上所述，返回该表的第一行是一个标准接口。如果name列有索引，它将返回满足此条件的第一行。从这里我们也可以知道还是使用索引比较好，因为使用索引可以快速的过滤得到正确的数据。如果不用索引，就会一个个拉到server层做where过滤。还有，上面提到的MySQL是边检查边发送的。其实稍微想一下，如果MySQL要等结果集完成了再把数据发给客户端，这样的设计不仅速度慢，而且还需要那么多的查询。缓存完整的结果集，MySQL的内存已经满了。至此，相信大家心里应该已经有了SQL的执行路径，并且已经有了感觉。我再丰富一下上面的图，加上优化器之类的。顺便说一句，你可能看到其他地方也有一个缓存组件，就是用来查询缓存的。具体方法是将一条查询语句作为键，将上次请求的结果作为值存储在缓存组件中。当对同一个语句进行查询时，可以立即返回结果，不需要再经过后面的词法、语法分析等步骤。这个东西在MySQL8.0之后就被砍掉了，只要表有数据变化，缓存就会失效。在我们常见的OLTP场景中是个鸡肋的东西，干脆不画了。新鲜度更重要。接下来，让我们看看这两个存储引擎。InnoDB和MyISAM对于我们来说，最重要的是InnoDB存储引擎，而MyISAM是5.5.8版本之前的默认引擎，所以我们不得不关注它。毕竟他们当了这么久的大哥。还是得给。我们先来看MyISAMMyISAM是基于ISAM引擎，支持全文搜索，数据压缩，空间功能，不支持事务和行级锁，只支持表级锁，它适用于OLAP场景，即，分析，基本上面都是读，不会有写动作的场景。它的数据和索引是分开存储的，即不在一个文件中，数据库只缓存索引文件，数据文件的缓存直接交给操作系统。这有点奇怪。一般来说，这种重要的数据都会自己缓存管理，不过好像没什么问题吧？（不知道有没有其他处理）MyISAM的索引也是B+树，但是不会像InnoDB那样叶子节点会存储完整的数据。MyISAM数据是独立于索引存储的，所以主键索引和非主键索引区别不大。另一种情况是MyISAM不支持崩溃后的安全恢复，而InnoDB有redolog可以支持安全恢复。还有一点就是MyISAM写性能差。因为锁的粒度太粗了，不支持行锁，只支持表锁，所以写的时候会锁住整张表。但是，有一个用于并发插入的开关。开启后，当数据中间没有空洞时，即新插入的数据从末尾插入时，读取数据不会阻塞。InnoDBInnoDB支持事务，实现了四种标准隔离级别，使用MVCC支持高并发。默认事务隔离级别为可重复读，支持行锁，使用行锁+间隙锁在可重复读级别提供防止幻读的能力，支持崩溃后数据的安全恢复。对了，也支持外键，不过一般互联网项目都不用外键，性能太差，可以用业务代码实现约束。InnoDB的主键索引叫做聚簇索引，就是把数据和索引放在一起，这一点和MyISAM不同，它的辅助索引（非主键索引）只存储索引值和主键，所以当辅助索引不能覆盖查询到的列时，就需要通过找到的主键去聚簇索引中查询数据。这个过程被回调到表中。之所以能够取代MyISAM成为默认引擎，是因为支持事务和崩溃后数据的安全恢复。比较出名的有MVCC、Next-keyLock、redolog、WAL、undolog。还有changebuffer、doublewrite、readahead、adaptivehashindex等，这些后续文章会详细介绍。再来说说幻读。幻读是指后面查询的结果多于前面查询的结果。例如queryid大于100，表示在同一个事务的两个query中第一次找到50个item。第二次检测到51项，称为幻读。在标准的SQL隔离级别定义中，可重复读不能防止幻读，但是InnoDB在可重复读中使用了Next-keyLock来防止幻读。所以有些人看到网上有表格说repeatablereadscannotagainstphantomreads可能会觉得奇怪。InnoDB的可重复读如何防止幻读？这是因为标准就是标准，如何实现要看具体的数据库。日志MySQL其实有很多日志，我们关心的是二进制日志（binlog）、重做日志（redolog）、undolog（回滚日志）。还有慢查询日志、错误日志、查询日志。这里还需要区分什么是逻辑日志，什么是物理日志。说白了，逻辑日志可以认为是记录一条SQL，也就是逻辑记录。物理日志说白了可以认为是内存中某个地址的值是xxx，所以先粗略了解一下，再是磁盘。对了，binlog属于Server，redolog和undolog属于InnoDB。这需要澄清。索引其实我之前写的两个故事已经讲到索引了，大家可以点蓝字查看。indexing这个知识点基本相当于面试必答题。这里的重点是B+树是如何存储数据的，主键索引和非主键索引有什么区别。这里先说一下主键索引和非主键索引，它们在InnoDB中也被称为聚簇索引和辅助索引（二级索引）。如果是主键索引：非叶子节点存储主键和页码。叶节点存储完整的数据。叶子节点之间存在双向链表链接，方便范围查询。叶子节点内部有一个页目录，内部记录的是单链表链接。遍历链表就可以得到对应的记录。B+树只能帮助快速定位页面，不能帮助记录。页大小默认16k，按照主键大小排序。所以无序记录插入会因为排序而插入到页面中间，而且由于容量有限，页面会被拆分存储，性能比较差，所以主键必须是有序的。如果是非主键索引：与主键索引的区别在于叶子节点存储的是索引列和主键，没有完整的数据。所以不要没事就select*，因为如果只需要查询索引列，可以直接使用辅助索引直接返回，然后你要select*，那就得走了到主键索引去查找id，很浪费。然后就是B-树，B+树，Hash索引等等。Hash等值查询的优点是范围查询不能。与B-tree相比，B+树的叶子节点之间采用双向链表连接，范围查询性好。再者就是最左匹配原则，联合索引，覆盖索引，索引下推。最左边的匹配无非是likeneedsxx%，不是%xx。稍加思考就不难理解了。如果要查陈姓，我肯定可以通过前缀过滤掉所有陈姓，排除其他姓氏。如果我不给姓氏，我就得把所有的名字都扫描一遍，看看要不要找姓陈的。那么当有多列索引时，必须先用最左边的索引作为查询条件，才能使用上层索引。比如像上面这样的多列索引（姓氏，名字），如果你的查询条件有姓氏，那么就可以使用索引，如果没有姓氏，只有名字，就可以'使用它。再来说说联合指数。以上面的例子为例。如果你已经分别为名字和姓氏建立了两个索引，但是你经常将两个条件一起查询，那么你应该将两个索引合二为一，成为上面提到的多列索引，即联合指数。当然，上面的例子是不恰当的。通常将名称放在一个字段中就足够了。我只是举个例子。之所以合并索引，是因为索引的维护需要开销。举个简单的例子，你插入一条数据，不仅要插入主键索引，还要插入你所有的辅助索引。索引多了，开销自然就大了，删除更新也是一样。覆盖索引是指利用辅助索引直接返回数据。虽然上面已经提到了，但我再说一遍。比如selectfirstnamefromyeswherelastname=Chen，这个就是利用上面的索引直接返回，因为索引列覆盖了需要查询的结果。如果是来selectage的话，需要去主键索引查询，因为辅助索引是没有age列的数据的。下推指标，还是以上面的指标为例。这时候需要执行select*fromyeswheresurname=Chenandfirstnamelike%South%如果没有下推索引，那么查询只能使用surname的条件，会把ID为both的数据返回2和12，然后都需要返回到表中，然后使用Server的where进行过滤。而如果使用了索引下推，name如%South%的过滤条件也会被下推到索引中。取结果前先通过where过滤，然后得到数据，直接排除了ID为2的数据，只需要返回表ID为12的数据。其实我以前一直认为query本身就是通过索引下推来查找的。没想到这是5.6版本之后出来的优化。后来了解了MySQL的架构，觉得也正常。存储引擎毕竟是一个没有感情的读写数据的工具，就像饮水机（存储引擎）只能出冷水或者热水，合适温度的水需要你（Server）自己调节。只是现在科技在进步，所以才研发出一种可以直接生产温度合适的饮用水的饮水机。对了，索引下推只能用在辅助索引上，这个应该不难理解。最后，暂时来说，第一篇写了这么多，知识点还是很密集的。这篇文章大致涵盖了思维导图右上角的一小部分，也不是很深入。我打算先把思维导图上的东西过一遍，然后再一个一个分解。但这不是很粗糙。我认为一般点很清楚，对吗？如果您有任何建议或错误，欢迎骚扰。
