mysql系统学习总结篇_mysql课后总结

mysql关系型数据库

上图是一条sql进入mysql经历的大体流程，这里选用的引擎是innodb，innodb引擎支持事物且对update语句是默认开启事物的。

sql的执行流程

一条sql被mysql执行，要经历一系列流程，示意图如上所示。

1. 如果是首次连接，mysql连接器要判断用户权限。他会读取system下的user表中的数据，判断 用户名、密码、IP的信息是否符合，如果通过，就会在内存中开辟一个空间，将连接用户的所有权限信息写入内存中。这是为了在实际大并发下使用时，不会频繁的去磁盘读取这些信息。mysql使用的是长连接，在没有数据交互的情况下会维持8小时。由于以上情况，会导致当一个用户的连接没有断开时，即使root用户修改了它的权限，也不会刷入它的缓存。只有当它重连后才会生效。
2. 经过连接器，sql会进入解析器。他会提取各关键字，按照规则尽心分析，返回错误sql语句。若果sql没有问题，将进入优化器。
3. 优化器会优化sql语句，进行内部计算，选择使用的索引，重写sql语句。
4. 执行器会将优化后的sql交由使用的数据引擎执行。

mysql的存储数据结构

mysql作为数据库，需要频繁的读取，对一条sql来说，mysql希望它的查询速度尽可能的快。数据库中的数据为了持久化，最终会被写到磁盘上，这也就导致，磁盘I/O成了数据读取最大的耗时，如果能在读取一条数据时，进行最少的磁盘I/O,就是mysql数据结构选型的核心。mysql最终选择了B+Tree的存储结构。

B-Tree与B+Tree

数据库存储数据到磁盘后，一般来说，是无序的。对于一张表的每条数据，按照主键在逻辑上是顺序的，但每一条数据但插入时间不定，也就导致存储在磁盘时但位置，就不再有序了，那么每读取一条数据都要进行一次寻址，效率无法满足我们但需求。
mysql在这里引入了页的概念，它也是每次读取但最小数据量（默认为16kb）。mysql每次开辟磁盘时，会占用一页的空间，所以每一页内的数据在磁盘上自然是连续的。

B-Tree：

上图是B-Tree的数据机构概念图，数据的存储以key-value的形式，在节点的两侧会存储下层节点的内存地址。假设使用3层树结构，存储表中一行数据需要占用1kb的空间（因为内存地址占用的空间一般为6bit，相对与1kb很小，就不计算了），那么一个节点在mysql数据库中能存储16个数据（这里一个节点对应mysql的一页数据，16kb）。那么，3层数结构满数据能存储16×16×16个数据。

B+Tree：

上图是B+Tree的数据结构概念图，他是B-Tree的变种。除去叶子节点外，其他节点不再存储数据，而且这些节点内的数据都是冗余的，他们都可以在叶子节点内找到。这种树形结构虽然会占用更多的磁盘空间（冗余数据的出现），但相较于B-Tree来说，可以存储更多但数据。同样但以3层树为例，假设key使用的是bigint类型，占用8byte，内存地址存储为占用6byte，那么非叶子结点存储一个数据需要占用14byte的磁盘空间，一个节点约可存储1170个数据。3层树结构可存储：1170×1170×16个数据，明显多余B-Tree。

对于mysql来说，3层树结构意味着，在B-Tree中检索一个数据，只需要进行3次磁盘I/O，而B+Tree能存储更多的数据，B+Tree是优于B-Tree的。那么其他树结构与之相比，树高就成了最大的问题（树高对应磁盘I/O的最大次数）。

innodb引擎

mysql支持多种不同的引擎，从sql执行流程图概念图中可以看到，各种引擎是执行器调用的，他们更像是各种不同类型的插槽工具，执行器将sql交给它，它将结果返回。
引擎是建立在数据表上的，也就是说，一个库中，可以包含多张使用了不同引擎的表。
innodb是目前使用最多的，它要求每一张表都必须拥有一个主键，因为它的所有数据都维护在一个以主键ID为key值的B+Tree中，这里，将这样的存储了所有表数据的结构，称为聚簇索引。innodb是支持事物的，也正是因此，它的锁粒度是行锁级别的。

索引

mysql建立索引时，是将索引字段作为key，对应的主键id作为value，创建一颗新的B+Tree。所以过多的索引会导致数据量极度膨胀，增删改的速度明显下降，因为它要维护更多的索引树的数据。因此，一张表不应该建立多个单独的索引，而是尽可能的只使用2-3个联合索引（多个列组成的索引），让他们包含80%的索引需求。如果是使用唯一索引，不在此概念内。因为维护数据的准确要更为重要。

聚簇索引

聚簇索引就是主键索引，它包含了一张表中的所有数据。常说的全表扫描就是指的检索聚簇索引，但不同但是，在mysql中，B+Tree但叶子节点，也就是数据但存储位，在手段和末端，会存储下一个叶子节点但内存地址（按key的顺序排列）。所以全表扫描并不会从跟节点起始，而是从叶子节点的最左侧（主键最小值）开始遍历，其他索引也是同理。

联合索引

上图是联合索引的概念图，它是3个列字段联合创建的索引，这里假设他们分别是（age，color_id，class_id）。索引存储方式是，分别按找索引字段的顺序，也就是先排序age，在age相同的情况下，排序color_id，在color_id相同的情况下，最后排列class_id。这意味着，只有在前一字段相同的情况下，后一字段才是有序的，也就是最左匹配原则。

二级索引

除去主键索引/聚簇索引外，其他的索引被称为二级索引或辅助索引，它们相比主键索引要小的多，因为它们只存储了索引字段与主键，而主键索引中存储了所有的数据。因此，使用二级索引可以使检索一般可以速度更快，但也要看sql中需求但数据情况，如果索引中但数据不足以满足sql但需求，就需要通过主键回到主键索引中拿去其他数据，这被称为回表，过多但回表可能导致使用二级索引效率并不如单独遍历主键索引。如果一次sql，他所有需求但数据，都能在二级索引中获得，就省去了回表但过程，效率就极大但提升了，这被称为覆盖索引。

explain

一个优秀的索引可以极大的加快mysql的检索速度，对于一个复杂但sql来说，它在执行但时候会被分解为多个简单sql，使用explain关键字，可以方便但看到这些分解但sql语句但执行方式，使用了什么索引等等情况。下面记录了explian返回但字段信息：

1. id：它代表了一条sql分解出的多个子sql的优先级别。序号越大，越先执行，如果序号相同，则排在上面的优先执行。
2. select_type：代表查询的复杂程度
   ① simple：简单查询。
   ② primary：复杂查询的最终执行sql。
   ③ subquery：子查询。
   ④ derived：虚拟表。
   ⑤ union：联合表。
3. table：使用的表名，可能会出现union或derived的情况。
4. partitions：分区，一般使用分库分表，而不使用分区。
5. type：查询类型：
   ① system/const：使用主键或唯一键进行常数匹配。system是特殊的情况，数据库只有一个数据时。
   ② eq_ref：使用主键或唯一键作为条件或链接。
   ③ ref：是由其他索引作为条件或链接。
   ④ range：索引使用范围查询。
   ⑤ index：遍历二级索引。
   ⑥ ALL：全表遍历。
6. possible_keys：可能使用的索引。
7. key：实际上使用的索引。
8. key_length：索引长度。索引的每个字段占用的长度都是不同的，如 tiny-int 1btye，char 1byte，date 3byte ，date_time 8字节 等等。如果允许使用null，会多占用一字节。
9. ref：查询条件是常量 const 还是某个字段。
10. rows：结果行数，这是一预估值，不一定100%准确。
11. filtered： 检索的行数与结果行数的百分比。
12. extra：一些扩展信息。
    ① using index：使用类覆盖索引。
    ② using where：普通条件查询。
    ③ using index condition：使用索引查询，非覆盖索引。
    ④ using temporary：mysql需要创建一张临时表来处理数据。
    ⑤ using filesort：使用类文件排序。当排序字段不在索引中或不满足最左前缀原则当时候，就会使用文件排序。文件排序分为两种模式，单路排序和双路排序。单路排序中，mysql会把需要排序的数据，全部家在到缓存中，进行排序后返回。双路排序中，mysql只会将数据的排序字段与主键ID加载到缓存，排序后有一个回表到过程。mysql中对两种不同排序方案对使用，是根据max_length_for_sort_data=1024byte，如果需要排序对数据，所有字段长度超过1024byte，就使用双路排序，否则，会使用单路排序。
    ⑥ select tables optimized away：使用了类似min()，max()这样的聚合函数来访问索引字段，mysql在优化阶段就能得到结果。

mysql在执行sql前，会经历优化阶段，例如使用覆盖索引时，索引顺序写错了，mysql可以智能的调整顺序。但所有可以通过代码优化但sql，都不要浪费mysql的资源。优化阶段，mysql会自行判断对每一条sql（可能会拆解负载sql）的索引使用，它拥有自己的判断机制，会计算sql的预估 cost，通过cost值，大概的判断是使用那一条索引，或者干脆全表扫描。在sql优化阶段，mysql是没有运行sql语句的，得到的所有结果都是一个预估值（基本准确），可以通过trace。

在使用explain的时候，可以同步运行 show warmings；它会返回一条sql最终运行的 类sql语句，通过它，可以分析出sql语句最终执行的，到底是什么。

tarce工具：trace工具会影响mysql性能，生产环境不要开启。tarce工具可以反馈出sql语句执行前，mysql对其进行的优化执行步骤，可以看到cost的计算结果。

索引相关优化

声明：sql的优化与它的存储方式息息相关，下面记录的优化策略都是基于数据模型得来的。优化策略并不是绝对的，只是在大多数情况下适用，具体方案要依照explain结果具体分析。

1. 索引的建立一定要在业务逻辑大体完成后，至少要在主逻辑完成之后，对所有的sql分析、总结后，再开始建立。要确保，每张表在建立2-3个联合索引的情况下，能够覆盖80%以上的业务索引需求。
2. 尽量使用覆盖索引，他会大大加快检索速度，避免回表。
3. 对长度较大的varchar类型字段建立索引时，一般来说，使用前20个字段就足够了。
4. 遵循最左前缀原则，尽可能是更多的字段使用索引。
5. 如果 where 与 order by 只有一方能使用索引，优先where。因为 where 是在过滤数据，一般来说，认为一个条件语句是可以过滤掉大量数据的，那么，在索引中过滤掉大量数据后，即使使用了文件排序，速度仍应该更快。
6. 联合索引的使用中，如果第一个字段就使用范围查找，而sql又不是覆盖索引的情况下，是有可能放弃使用索引，而选择全表扫描的。
7. like关键字，对于 like% 及与其类似的使用方式（如：like abc%%dc%）来说，，它符合了最左前缀原则，sql对他的使用有些类似与 = ，虽说它之后的数据并非有序的，但sql可能认为它过滤了大量但数据，剩余但数据在索引中检索会更快，而对它后续对字段继续使用索引。这也叫做索引下推。而对于 %like 之类的使用，就无法使用索引了。
8. in 与 or ，谨慎使用它们，因为很可能不会使用索引。对于in来说，如果不得不使用时，它内部的数据也不要多余200个，否则会严重影响效率。
9. is null 与 is not null ，mysql对空字段采用的是集中存储在索引树最左端的，使用它们时，可能会从索引树的最左端开始遍历，如果mysql认为结果太多，需要大量的回表，就很可能不使用索引。
10. != 与<>，他么的使用，会不会使用索引，主要是成本问题。因为这种条件过滤后结果集一般来说会很大，如果不是覆盖索引，这种回表量的开销会大于全表扫描。
11. 分页查询，limit 关键字的查询机制与一般理解的有所不同。例如表中又100w条数据，如果使用 limit 50w,10 这个条件，mysql并不会查询到第50w条数据，然后返回10条结果。而是会查询500010条数据，然后返回最后10条。这也导致了分页查询越往后越慢，因为检索的数据量太大，这里就需要在 java 层，通过代码逻辑，来进行数据过滤。
12. order by 与 group by，group by 与 order by 类似，一般来说都要经历排序阶段。所以也会遵循最左前缀原则，group by 可以关闭排序。

连表 JION

原则上，要避免使用连表。除特殊业务，都应尽可能的使用单表查询。对于关联比较大的表，可以使用冗余字段存储必要的数据，如果不得不使用连表，连表数量也不要高于3张。mysql连表主要使用两种方式：

1. nested loop join 嵌套循环连接 NLJ：如上图所示，嵌套循环连接的流程，它主要用于有索引的表连接。首先会遍历小表的索引树，将获取的每一条数据到B表的索引中检索。A表的每一条数据，在B中只需要检索一条数据就能得到（检索过程可能经过几次I/O，这里认为的是检索目标需要遍历的行数）。那么它的耗费是：100 + 100。如果没有索引的，那么耗费立即膨胀到：100 × 10000。
   

2. block nested loop join 基于块的嵌套循环连接 BNL：如上图所示，它主要用于没有索引的表连接。小表中的数据会写入jion buffer中，如果无法一次写入所有数据，那么会分批多次写入。每次写完成后，用jion buffer 中的数据在A表中遍历比较，将匹配数据返回。它的耗费为：100 × 10000，看起来在无索引时与嵌套循环连接没有区别，但由于缓存但引入，减少了大量但磁盘I/O。

根据mysql连表的两种算法来看，如果不得不使用连表的情况，就务必要使用索引。连接中，小表被称为驱动表，大表被称为被驱动表。当使用 inner jion 时，mysql 会自主判断哪张表为驱动表，当然也可以强行制定，通过 straight_jion 代替 inner jion ，那么关键字的左表，就是驱动表。left_jion 会强制指定左表为驱动表，right_jion 指定右表为驱动表。

事物

innodb支持事物，事物应满足以下原则（原则的解释是个人理解）：

1. 原子性：事物中的每一个操作，要同时成功或失败。
2. 一致性：事物中对数据库操作的结果，要是一致的。
3. 隔离性：事物与事物之间，应互不影响。
4. 持久性：事物的结果要是持久化的。

innodb对事物进行了4种隔离性：

1. uncommitted read ：未提交可读。
2. committed read：提交可读。
3. repeated read：可重复读。
4. serialized ：序列化的。

其中，1、2是不满足事物隔离性的。3，4满足了事物的原则。根据不同的事物类型，可能会出现以下的几种问题：

1. 覆盖更新：两个事物同时对一个数据做更新处理，后更新的事物会覆盖掉先更新事物更新的数据。
2. 脏读：事物1更新了一个数据，被事物2读取了。这时事物1发生了回滚，而事物2依然根据读取的数据进行逻辑处理。这个被读取的数据就是脏数据，发生了脏读。
3. 不可重复读：一个事物中，读取同一个数据时，每次读到的结果都不相同。
4. 幻读：事物中读取到了另一个事物中新增的信息。

对于覆盖更新，需要在sql语句中修改，例如要修改一个订单，不要先查询到订单结果后，进行修改，再更新到数据库中。这样操作必然会造成数据错误。而应该在更新中使用 order = order - 1；这样的操作。

脏读和不可重复读，在 repeated read 及以后的隔离性中得到了解决，它们就是不满足隔离性而导致的。

而幻读，repeated read 仍无法解决。例如 我在事物1中新增了一条数据，新增数据的 id 为43，事物提交后，在事物2中，通过select是无法检索到这条数据的。但如果，事物2通过 id = 43 对这一条件对这行数据更新，那么它会成功，并在后续的 select 中，可以检索到这条数据。

事物中，对于改/删/增这样的操作，会添加行锁。

锁

因为 innodb 支持事物，所以必然要有对应的锁来保证事物的实现。innodb 支持 行级锁 ，可以对数据行进行加锁。mysql锁一般会加在索引对应的字段上，如果锁不在索引上，它也不是加在主键上的，那么就会发生锁表。

1. 读锁：共享锁，可以添加多个读锁，当数据被添加了读锁，将不可进行删/改操作。

2. 写锁：非共享锁，写锁具有排他性，当数据被添加了写锁，在锁解除前，无法进行其他操作。
   

3. 间隙锁：当惊醒范围更新的时候，会出现间隙锁。间隙锁样式如上所示，当我对上图这样当一组数据更新时，如果我更新当范围时14-35，那么从(6,∞]的所有数据，都会被加锁。这个范围内的id，即使还不存在，insert 也不会成功，而是会等待锁解除。范围所跨的所有间隙都会美加锁，组成了间隙锁。

4. 临键锁：它是间隙锁的一种补充，间隙锁的范围是 ( … ] 的，左侧是不被锁定的，而右侧的数据会被加锁。

我们可以通过mysql查看锁的各种情况，来判断是否有问题sql。

mvcc机制

mvcc（multi version concurrent controller）多版本并发控制，mysql使用它实现事物隔离，而不需要对 select 语句添加锁。mvcc机制提供给事物读取对内容是一个快照，它目前对状态可能早已经改变了，但在事物结束前，读取对内容是基于快照对。mvcc的实现基于undo日志。

数据页

mysql的数据存储的最小单位，一般为16kb。下图是一个数据页的构成（图片来自网络）：

这些分段中，只有infimum+superemum 与 user records 是真正记录行数据的，其中前一部分记录的是两个虚拟数据，页中的最小/大值。这两个数据是自动创建的，他们分别指向真实的最小数据和被真实的最大数据指向。user records 记录的是用户插入的数据，当空间不够用时，会向 free space 中申请，如果空间不足，会开辟新的页。
page directory 会将正常的数据分组，包括最小/大值（虚拟），每一组的最后会记录这一组的数据量以及最后一个数据的头信息，这一组被称为 slot 。各个slot之间，是顺序的，查询时，使用二分查找，找到目标 slot ，再去遍历组内的数据。它有点像书的目录，可以进一步减少检索的时间。

行格式

compact格式（来自网图）

其中两项隐藏列，transaction_id 记录了事物id，roll_pointer 记录了 undo 日志的位置指针。

undo日志

undo日志是innodb自身为为实现事物回滚而实现的，它记录了被更改数据的上一个状态。对于增/删/改，实现的方式都不同，nudo日志的结构包括：type——undo日志的类型、end of record——日志的结束处的地址、no——编号、table id——影响的表的id、start of record——事物起始地址 等等。undo 日志是链表形式的，每一行日志会指向相关的下一行日志（以下的总结是个人理解）。
① 对于新增：undo日志记录了新增行的主键，回滚发生时，直接删除新增记录即可。
② 对于删除：undo日志记录了删除行的主键，被删除的行，其行头信息中的delete_mark标记被标记为1，此时，这条数据仍然是正常数据，事物回滚，修复标记即可。如果事物进行了提交，会有专门的线程，将它移入垃圾链表（垃圾链表、正常链表的概念，在行数据头中，有一个next_record部分，用于记录下一条数据的地址，形成一个正常数据链表，它的头/尾分别是 supremum/infimum。而垃圾链表是删除的数据，会被移入一个可地址复用的链表中，它的头部数据被记录在page header 中，每次新增数据，会添加在链表的头部。这两个链表是基于页的）。
③ 对于更新：undo日志记载了更新行数据的信息，以及它相关的各索引信息。对于事物中的更新操作，如果修改了主键，那么聚簇索引的原行数据会被删除（进入垃圾链表），新增一条新的数据。如果没修改主键，就要看原记录空间是否能容纳修改后的数据，否则仍然要删除原记录并新增。

mvcc的实现

事物开启后，当进行增/删/改操作的时候，会生成事物ID，并将相关信息记录在相关行数据的transaction_id 和 roll_pointer 中。当进行 select 操作时，会生成一个 read_view。read_view记录了当前未提交事物的最小ID、已提交事物的最大ID、和未提交事物的列表。例如:[100,103] 300，以此分析：
① 所有ID小于100的事物，都是以提交的。
② 所有ID大于300的事物，都是未提交的。
③ 100 - 300 之间，所有的未提交事物都记录在列表中了，100 和 103。其他的，都是已提交事物。

由此可知：在事物中查询是，会对检索到的数据行的事物ID进行比较，按照不同的事物隔离性（mvcc只支持 committed read 和 repeated read），返会相应数据。