重新认识MySQL

        MySQL的使用作为一项开发人员的基本技能，已经不存在会不会用的问题了，只有用的好或者不好。处理方式也都是五花八门，有的人把业务融入到了sql中，为了简化业务层，将sql写的什么臃肿，上百行、上千行的都有。还有一种是简化sql，将sql拆分，业务层会稍微复杂些，但是易懂。说了这么多，只是提到了怎么去用，但是往往有那么些人会问你为什么这么用？这么用有什么好处？我套你个猴子，我会用不就行了？一番无能怒吼，带着不情愿再去看看吧！

        今天的MySQL是基于5.7版本的，也是比较大众的版本，会讲到sql的执行过程，存储引擎，事务，索引等等。

MySQL的执行过程

        之前的我哪会想到我也有今天，用都用不利索，还得去把它怎么用还得搞搞明白。不发牢骚了，直接来吧。

执行流程

        从宏观角度来看，我们可以将MySQL分为服务层（Server层）和存储引擎层，接下来认识一下其中用到的组件：

        连接器：就是客户端与MySQL的服务层建立连接。在建立连接之前，客户端用户会进行登录操作，然后对账号密码进行校验，校验通过之后，还会查询用户权限，之后，在这次连接过程中涉及到权限都依赖于此时的权限，并不会因为中途修改权限而发生变化。只有重新创建新的连接，新的权限才会生效。

登录-权限信息

        查询缓存：这里的缓存是查询之后的结果，以SQL为Key，Value为结果集的形式缓存下来。在获取到连接之后，会优先去查询缓存，如果缓存中有记录，会取出来直接返回客户端，可以省去很多磁盘IO；如果缓存中没有记录，继续执行下边的操作，执行完毕获取到结果集后也会缓存下来，方便下一次查询。听起来好像效率很高，可是在查询缓存的时候key的命中很严格，SQL任何字符上的不同，如空格、注释、都会导致缓存不命中。如果查询中有不确定数据，比如Now()这种函数，也不会被缓存。还存在一个问题，就是在执行Update操作时，查询缓存会被清空，很容易失效。如果你有一张不经常修改的表可以尝试使用查询缓存。

        分析器：分析器主要是处理查询缓存失败后对SQL的解析。主要分为关键字分析和语法分析。关键字分析是从 select 等关键字后解析表名或 where 后的字段名。语法分析主要是判断你的SQL是否满足MySQL语法。

        优化器：在对我们的SQL进行分析之后，确保了SQL语法的正确。之后通过优化器，选择一个它认为最优的方案，去决定怎么使用索引和连表顺序。

        执行器：优化器在帮我们优化过SQL之后，就要通过执行器去执行SQL了，在执行的时候首先还会对将要操作的表进行权限验证，没有权限就无法执行下去了，直接返回错误信息；如果有操作权限，就可以调用存储引擎的接口，最后拿到结果集并返回到客户端。

存储引擎

        从上边我们看到了执行器会调用存储引擎的接口去查询结果。没错存储引擎就是对数据的存和取，是我们关注的一个地方。MySQL支持多种存储引擎，我们可以看一下。

MySQL支持的存储引擎

        上边的信息我们可以看出个大概，在这里，我们只对 InnoDB、MyISAM、MEMORY进行介绍，其他的太不常用了（其实也只用 InnoDB）。

        InnoDB是MySQL的默认存储引擎，支持事务、行级锁，和外键。行级锁优点是适用于高并发的频繁表修改，高并发是性能优于 MyISAM。缺点是系统消耗较大，索引不仅缓存自身，也缓存数据，相比 MyISAM 需要更大的内存。支持 Hash和B-Tree 索引类型。

        MyISAM不支持事务和外键，支持表级锁。表级锁的优点是开销小，加锁快；缺点是锁粒度大，发生锁冲动概率较高，容纳并发能力低，这个引擎适合查询为主的业务。支持 Hash和B-Tree 索引类型。

        MEMORY是内存级别存储引擎，数据存储在内存中，所以他能够存储的数据量较小。支持的锁为表级锁，不支持事务。但访问速度非常快，并且默认使用 Hash 索引。

        通过上边的比较，反复提到了表级锁和行级锁这俩个名词，它们到底是用来做什么的呢？数据库中锁的使用主要是为了保证事务准确和数据库的一致性。锁可以防止用户读取其他用户正在更新的数据，并防止多个用户同时操作相同的记录。

        1、行级锁是MySQL中锁定粒度最小的锁，表示只对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突和保证数据的准确。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁和排它锁。

        2、表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与InnoDB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。

        排它锁：事务A对数据库D加了排它锁之后，只有事务A可以对数据库D进行事务操作，其他事务不能读也不能操作数据库D。

        共享锁：事务A对数据库D加了共享锁之后，其它事务也只能对数据库D加共享锁，且有共享锁的众多事务只能对数据库D做读操作，直到事务A释放了共享锁，否则都不能执行其他操作。

事务        

        说到事务，大家可能都会想到事务的四个特性ACID，我们先来复习一下：

        原子性（Atomicity）：当前事务中包含的所有操作，要么都成功，要么都失败。

        一致性（Consistency）：一个事务执行前后都要保证处于一致性状态。一致性状态指的是数据库中的所有数据都满足数据库定义的所有约束。

        隔离性（Isolation）：事务之间相互隔离，本身事务不受其他事务的影响。

        持久性（Durability）：事务一旦提交，数据将一直存在宇数据库中。

        接下来是事务的隔离级别：

        脏读（读未提交）：事务A读取到了事务B还未提交的数据。

未提交读

        不可重复读（读已提交）：大部分数据库的默认隔离级别。可以读取其他事务commit后的数据。事务A多次查询某个数据，这事务A执行的过程中事务B也对这一数据进行了事务操作，导致了事务A多次查询返回的结果不一致。

已提交读

        幻读（可重复读）：mysql的默认隔离级别。这个其实和不可重复读（读已提交）很相似，只不过针对的是一批数据。事务A查询某个范围区间内的数据，事务B进行了新增操作，导致了事务A的结果集不一致。

可重复读

        可串行化：这个隔级别是最高的，通过加锁和释放锁保证事务的绝对安全。如果当前事务A在执行读或写的操作，在执行过程中会上锁，使得其他事务必须等当前事务执行完毕之后才能执行。带来的影响就是效率太低了。

        也许上边的描述还是不太清晰，再了解了事务隔离级别的实现原理，我想你就更加清楚了。事务的机制是通过视图来实现的，不同的事务隔离级别创建读视图的时间点不同。

        可串行化：直接通过加锁实现并发访问。

        可重复读：每个事务重新创建事务，整个事务存在期间都用这个视图。

        读已提交：在每个SQL语句开始执行的时候创建视图。

        读未提交：读未提交不会不创建视图，直接返回最新的结果。

索引

        提到索引，不说别的，面试反正少不了它。经常会被问到，你们数据库的优化手段都有什么啊？我们的回答除了对SQL语句语法上的一些优化，为了把自己的能力提高档次，免不了提一波索引，这一提我们不要紧，面试官就好像被踩着尾巴了，索引是个什么？为什么要用索引？索引一般用到什么地方？你能给我介绍一下你们索引具体采用的哪种数据结构呢？一顿素质拷问，让你措手不及。如果再来一次，你还会说你 “会”索引吗？哈哈，不要紧，都是过来人，好好学就是了。

        什么是索引？

        索引是对数据库表中一个或多个列的值进行排序的结构。

        为什么要用索引？

        假如一个表中有1W条数据，如果你没有索引，在查询某条数据的时候就会全表查询，直到匹配到我们要找的数据，也许你会不以为然，那10W条数据呢？100W条数据呢？如果我们加上索引之后，即可在索引中查找。由于索引是经过某种算法优化过的，因而查找次数要少的多，效率根本不在一个档次。至于是什么算法，我们下边再去讲。

        索引一般用到什么地方？

        索引的选择是一个很讲究的事，一般我们常回答的结果就是被当做条件查询的频率很高的字段上面加索引。

        你能给我介绍一下你们索引具体采用的哪种数据结构呢？

        现在开发使用的数据库版本基本上5.7的居多，这个版本的存储引擎默认是 InnoDB，所用我们索引支持的索引的数据结构就是Hash和B+Tree了。说到这里就不得不它俩放一块比较了。

        1、Hash索引：提到Hash其实我们应该不陌生了，我们涉及到Hash算法的东西还是挺多的。Hash索引也是key-value的存储结构，很明显我们可以通过key直接可以获取到value，言外之意就是等值查询的效率会比较高，但是因为结构上是无序的，导致了无法范围查询（硬要查可定是全表查询了）。既然是Hash，难以避免的会发生Hash碰撞，发生Hash碰撞后会生成链表，导致了最后还得进行循环，所以说在这种情况下，哈希索引的效率也会很低。

        2、B+Tree索引：B+ Tree是一种多路平衡查询树，它并不是二叉树，它的节点是天然有序的（左子节点小于父节点、父节点小于右子节点），所以对于范围查询的时候不需要做全表扫描，而且支持多列联合索引的最左匹配规则。

        B+Tree索引在使用过程中有分为了聚簇索引和非聚簇索引。

        聚簇索引：所谓聚簇索引，就是把索引和索引对应的这一行数据放到了一块，找到了索引就找到了数据。你说聚簇索引选择的字段重不重要？

聚簇索引

        非聚簇索引：非聚簇索引也叫做二级索引，在二级索引的子叶节点中存储的是数据行的聚簇索引，拿到数据行的索引再去聚簇索引的子叶节点中获取数据（回查）。

非聚簇索引（二级索引）

        到了这里，我们再去想想索引到底该怎么去选择。第一个肯定是加索引的字段一定是常作为条件查询的字段；第二个我们知道B+Tree在节点上是有序的，在正常情况下我们的表中一般都会有自增的主键id，保证了顺序，在你新增数据后只需在后边继续添加就行。可是我们可以试想一下，如果我们设置UUID为我们的聚簇索引，顺序从何而言，我们新增一条数据，也不知道会插入到哪个子叶中，如果放到最后，那没什么影响，可是如果放到中间，那后边的位置岂不是都会发生改变。还有可能正好这个数据页满了，需要重新申请一个数据页，还得把后边的数据挪到新的数据页中。第三个就是从存储空间的角度来看，如果选择身份证号或者UUID这种字段为聚簇索引，聚簇索引本身占据的内存就很大了，然后还有可能存在非聚簇索引（二级索引），导致二级索引的子叶节点所占内存也很大。综上所述，我们在选择聚簇索引（主键）的时候，在条件允许的时候要选择查询频率高、有序和占用内存小的字段（其实就是我们最常用 PRIMARY KEY AUTO_INCREMENT）。

        上边我们提到了聚簇索引和非聚簇索引，有的面试官很刁钻，他会问你聚簇索引和非聚簇索引执行效率哪个高，大家可能会不假思索的回答说是聚簇索引，毕竟号称拿到索引就拿到数据了嘛！而非聚簇索引拿到的是聚簇索引，还需要进行回查，才能拿到数据。那所有用非聚簇索引查询的都是这样吗？一般这样问的，肯定不是啊，但是心里慌的一匹，是真的不知道啊。在一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取，就是我只要聚簇索引，不要数据，这种情况也叫做覆盖索引。

        在上边我们还提到了最左匹配规则，这种规则是针对联合索引。其实可以把它们（多个字段）理解为是一个组合，把这一个组合作为一个索引。建立联合索引的时候我们一般把最容易匹配的字段放到前面，这么做的原因其实就是最左匹配规则了。假设我们在某一张表中建立索引（name，sex，email）,在为这个联合索引构建索引树的时候是按照从左到右的书序的，即在命中索引的时候先去筛选name，如(“张三”，“男”，“@qq.com”)，索引在执行过程中会先查询“张三”，再查询“男”，最后是“@qq.com”，但是如果数据是（“男”，“@qq.com”）按照最左匹配规则来说，它无法匹配第一个字段，导致了索引失效。但是如果数据是(“张三”，“@qq.com”)，会成功匹配“张三”，但是sex却法无匹配，导致索引在执行过程中只有在“name”字段生效，会先找到符合name为“张三”的数据，然后正常匹配“@qq.com”（索引没有生效）。换句话说，我们的索引（name，sex，email）可以跟据最左匹配原则将它理解为是(name)、（name，sex）、（name，sex，email）三个索引，这个就是最左匹配原则。

        下次见~