找到
18
篇与
数据库
相关的结果
- 第 3 页
-
再有人问你MySQL是如何查询数据的，请把这篇文章甩给他！ 本文来自作者投稿，作者：zyz1992 上一篇我们说到了关于MySQL的索引的原理，主要说的是 MySQL 对于索引的字段是怎么去维护的，我们再来简单的回顾下： MySQL 对于主键索引的维护是最简单的，就是根据主键去维护一个 B+ 树，因为主键的特点一般是递增的，也就是说是有序的，所以 MySQL 在维护的时候只需要将记录依次往数据页中追加即可，数据页满了就继续添加到下一个数据页。且每一条记录是完整的，即所有的列的值都维护。 但是对于非主键索引，在维护 B+ 树的时候，会根据联合索引的字段依次去判断。 假设联合索引为：name + address + age，那么 MySQL 在维护该索引的 B+ 树的时候，首先会根据 name 进行排序，name 相同的话会根据第二个 address 排序，如果 address 也一样，那么就会根据 age 去排序，如果 age 也一样，那么就会根据主键字段值去排序（主键不可能是一样的），且对于非主键索引，MySQL 在维护 B+ 树的时候，仅仅是维护索引字段和主键字段。 另外 B+ 树的结构大致是这样子的： 这里数据的维护过程就不再详细赘述了，不清楚的朋友可以看上一篇文章 今天，我们就来一起看看对于 MySQL 的查询有哪些基本的原则。 因为只要在了解原则的基础之上，才能够写出符合预期的 SQL，才能知道自己的 SQL 到底有没有使用到索引。这是一个最最基本的原则。 本文因为讲的是一些原则，所以很多东西不是很好画图，但是能画我一定给大家画。 1、等值匹配原则 我们现在已经知道了如果是【主键索引】，在插入数据的时候是根据主键的顺序依次往后排列的，一个数据页不够就会分裂到另外一个数据页，然后再通过索引页来维护数据页。 数据页之间是通过双向链表来维护的，索引页如果过多就会往上分裂（就像上面这张图），以此类推，这样就形成了由组件组成的 B+ 树结构，即【聚簇索引】 但是问题是我们不仅建立了主键索引，同时也建立了非主键索引，那这时候非主键索引是如何维护的呢？ 因为对于主键索引是不可能重复的，所以在保存到数据页的时候是直接追加插入的（我们默认主键是自增的） 至于非主键一般是可以重复的，假设此时某个联合索引字段的值真的都是一样的，那该怎么办？ 那就像上面开头说的，此时只能按照主键字段来排序，这就是为什么非主键索记录在保存的时候还保存一个主键字段的作用。 另外刚刚上面也说了，索引如果建立太多会占用太多的空间，因为MySQL 会为每个索引维护一颗 B+ 树，毕竟非主键的字段一方面不一定是递增的，另一方面可能是重复的。所以就基于这点，那些频繁增删的字段一定不适合来做索引。 好了，我们还是要回到刚刚说的 name+age 的联合索引假设我们现在有一条这样的 SQL SELECT * FROM student WHERE name='wx' AND age=1 像这种 WHERE 后面的条件是联合索引的并且是联合索引中的字段的顺序排列的，且全是使用等于号条件，我们称这种为：等值匹配；这个是非常重要的一个原则。 2、最左前缀匹配原则 假设现在有这样的几条记录： classId=1,name=wx,age=1,id=1; classId=1,name=xq,age=2,id=2; classId=1,name=wx,age=1,id=3; classId=2,name=zs,age=3,id=4; 根据上面说的（classId,name,age）联合索引他们是这样子保存在数据页中的。 首先根据 classId 字段值排序。 如果 classId 字段值一样，那么就根据第二个 name 字段值排序。 如果name 字段值也一样，那么就根据 age 字段值一样，如果 age 字段值也一样，那么就根据主键字段值排序。 然后在查找的时候，因为你现在条件是 calssId 和 name，所以 MySQL 是能够通过 classId 很快定位到一批数据的。 因为这个条件就是 MySQL 维护 B+ 树的第一条件（即先根据 classId 排序），然后同理，name 是MySQL 维护B+树的第二个条件（即根据 name 排序），所以此时哪怕你 age 条件不添加，使用到索引classId 和 name 的索引一定是没问题的，但是如果你这么查询 SELECT * FROM student WHERE age=1 这样子就不行了，因为 MySQL 会根据你建立的联合索引。 首先是根据 classId 查询，然后是根据 name，然后再根据 age。 如果你直接跳过前面的两个字段，那么这样子跟全表扫描是没有区别的，因为MySQL 此时根本就无法确认 age 在哪里，只能一个一个去扫描了。 同理，如果你WHERE 条件后面是 classId=xx，然后是 age=xx 此时这种情况下 classId 是可以使用到索引的，因为 B+ 树维护的第一个字段就是 classId。 但是 age 却无法使用到索引查询了，因为 name 是无法定位的，所以此时只能是根据满足 classId 的记录再做一次全扫描。这规则叫：最左前缀匹配原则； 如果你想不明白最左匹配原则，那我来做个类比再来介绍下，我们假设classId,name,age，这三个组成的联合索引就好比是三层楼，classId是第一层,name是第二层，age是第三层。 假设你想要到第三层，是不是必须要要从第一层开始爬，然后是第二层，然后是第三层；你可以就爬到第一层，剩下两层不爬也没关系，这就对应你可以就使用 classId来做等值查询，剩下的字段不使用都没关系； 同理，你可以从第一层爬，然后再爬到第二层，不爬第三层，这就好比是你使用 classId,name去查询一样，亦或者你依次从第一层爬到第二层再爬到第三层都是可以的，也就是你使用classId,name,age这三个字段依次去做等值查询。到此这一切都是 OK 的。 但是如果你不想爬第一层，你想跳过第一层，直接从第二层开始爬，可能吗？ 显然是不可能的，这也就是说查询的时候跳过 classId 直接查询name，这样子就根本无法使用到索引。调过 name 查询age 也是同理，直接跳过一二层直接从第三层开始，也就是说调过classId 和name直接查询age也是无法使用到索引的. 这下你应该彻底明白最左匹配的原则了吧？以下的原则最基础的条件就是需要满足：最左前缀匹配原则。 3、范围查找规则 范围查找规则，相信这个也是大家最经常使用的原则了，例如像下面的SQL SELECT * FROM student WHERE classId > 1 AND classId < 4 因为此时由联合索引（classId,name,age）构建出来的 B+ 树中的数据是根据 classId,name,age 去排序的，所以此时是能够根据 classId 查询到一个范围中的数据的，虽然他们可能不在同一个数据页中，但是我们说过了，数据页之间是通过双向链表进行连接的。所以 此时针对 classId 的范围查找依旧是能走索引的。继续看如果条件是这样子的 SELECT * FROM student WHERE classId > 1 AND classId < 4 and name > a AND name < x 你是不是觉得前面的 classId 是符合范围查找的，然后在查询出来的结果中继续范围查找 name。 但实际上并不是这样子的，因为我们说了联合索引（classId,name,age）是按照 calssId、name、age 依次去排序的，因为此时 classId 的顺序确定以后，是不需要根据 name 排序的，也就是说在 classId 的范围内 name 是无序的，听不明白？没关系，看我画图 现在我们依次插入的数据是上面的四条，因为在插入的时候是可以直接根据 classId 就能够确定下这四条记录的顺序了。 所以此时是根本不会去管后面的 name 或者是 age 是什么顺序的，或许你可能觉得如果他们的 class Id 一样呢？ 好，我就来一点一点排除你心中的疑惑，看下面这张图 我们假设第三条记录的 calssId 和第二条记录的 classId 字段值是一样的，那这个时候才会去根据 name 判断。 结果发现 aa 是小于 zz 的，这样就会把 name 值更小的排在前面，但是为什么我上面还会说按照 calssId 排序好了以后 name 是无序的呢？ 因为我们说的第二种情况（classId 相同）是属于特殊情况，我们不能使用特殊的情况来下一般性的结论，age 同理。 所以记住了：针对于范围查找只要联合索引的最左侧列有效，其他的都无法使用到索引（既然无法使用到索引，那么只能是走全表扫描） 4、等值匹配+范围查找 假设我们有这样的一条 SQL SELECT * FROM student WHERE classId = 1 AND name > a AND name < x 首先 calssId 是走索引的，其次 name 也是走索引的。 为什么？你怎么前后说的有矛盾？刚刚才说了范围的之后第一个列才能走索引，现在却说 name 也走索引，name 明明是第二列。 听我慢慢道来，首先范围查找只有第一个列走索引单纯针对的范围查找，具体原因我已经详细的解释了，但是现在如果使用联合索引中的第一个条件去做等值匹配，第二个去使用范围查询走索引是没问题的，看下面的图 现在我们首先定位的是 classId=1 这些记录，这些一定是确定的，但是在 MySQL维护 B+ 树的时候，是没法根据 classId=1 的记录来直接进行排序的。 因为此时的 classId 都是1，换句话说，三条记录的 classId 都是1，MySQL 根本无法确定谁在钱面，谁在后面。 所以此时就需要根据 name 去继续判断，结果也就是上面图的样子。 根据 name 发现是能够确定记录顺序的，所以在 classId 等于 1 的记录中的所有的 name 都是有序的。 这就是为什么等值后面可以范围的原因（但是一条贯穿始终的原则是：必须是满足最左匹配原则，也就是前面的记录必须是确定的，这样子才能继续对后面的数据判断）。此时此刻你是不是想大声的喊一句 到此为止，你现在是否能够根据建立的索引来判断你的 SQL 是否使用到了索引，使用到了哪些索引了呢。 是不是想赶紧的写几个SQL 试试？ 别急，一定要看完总结部分。 5、Order By + limit 优化 上面说道的一些都是最最基本的查询的一些原则，但是想要实际运用，这里是必须要学习的，因为我们平时写sql的时候绝对离不开分页。 而分页基本是也排序组合使用的，所以我们也将这个放在一起在说。 假设现在对name，age，adderss 这三个字段创建联合索引，且在查询的时候 SQL 语句是这样子的: SELECT name,age,address FROM student ORDER BY name,age,address LIMIT 10 你如果这么写那 MySQL 就能明白了啊，你是想根据 name,age,address联合索引进行排序，然后在取前10条记录，且取的记录的字段在维护联合索引的 B+ 树中都是有的，那么此时就需要再去进行回表到聚簇索引中查询了。 另外 ORDER BY后面的字段的方式一定要一致，也就是说要么全是升序，要么全是降序，不能有的升序有的降序。 说白了就是一般对什么字段排序就对哪些字段建立索引，但是升序降序不要混用。 其实对于 MySQL 的优化看到这里相信大家或多或少也发现了，优化真的没有所以的规律和套路，因为最好的优化是结合实际的业务区做调整。没有一蹴而就的方式和一劳永逸的方法。 6、分组查询优化 其实分组查询优化和上面的Order By + limit 优化差不多，基本是一个道理，例如有这样的 SQL SELECT count(*) FROM student GROUP BY NAME 如果不对 NAME 建立索引，那么就是将所有的数据查询出来，放在一个临时文件中，然后按照分组的字段将数据一组一组的分好。 然后再去执行聚合操作（这里就是count(*)操作），这样子很显然效率是很低的，所以我们肯定是需要对 NAME 去建立索引的。 这是不是不明白为什么需要会有临时文件？ 根据 group by 的语义逻辑，是按照name去做统计，因为此时name并没有索引，所以按照name去分组首先需要得到一个根据name排序的数据啊，所以我们就需要有一个临时表，来记录并统计结果。 也就是说我们需要的不就是一个排好序的结果吗？那直接对name建立索引就可以了。 假设我们是根据name建立好了索引，因为此时name已经是被排好序的了，这个时候就可以拿到 group by 的结果，不需要临时表，也不需要再额外排序。 也就是说，如果语句执行过程可以一边读数据，一边直接得到结果，是不需要额外内存的，否则就需要额外的内存，来保存中间结果。 7、结束语 索引查询基本原则总结 我们一般写 SQL 基本都是【等值 + 范围】的，这个是最常见的条件搜索，像这样子的情况一定要建立好索引，建立索引的根本依据就是要明白【MySQL 是如何帮我们维护非主键索引的】。 MySQL 是如何帮我们维护非主键索引的 其实在开头我已经强调过了，但是为了让大家在巩固下，我这里在来说一遍。 对于主键索引，MySQL 就是根据主键字段进行排序（一般主键字段我们都设置为自增的，否则真的是在给自己找麻烦，假设主键不是自增的，这还会导致页分裂的发生，这样就很降低性能了）； 而对于非主键索引（我们一般指联合索引）MySQL 同样会为我们维护一个B+ 树，只不过这颗B+的叶子结点（即数据页）上面的保存的数据仅仅是索引字段数据和主键数据。 假设有联合索引 name、address、age，这样在插入数据的时候，MySQL 首先会根据name进行排序，name一样就根据address 排序，address 字段值一样再根据 age 字段值排序。 age 字段值还一样，就根据主键字段排序。 这也是为什么会维护主键字段的原因。 另外为什么对于非主键字段只维护索引列？因为聚簇索引（通过维护主键字段的B+树）中已经有全部记录的值，如果其他的索引再维护所有的字段，这样就是在浪费空间。
-
我以为我对Mysql索引很了解，直到我遇到了阿里的面试官 本文来自一位不愿意透露姓名的粉丝投稿 相信很多人对于MySQL的索引都不陌生，索引(Index)是帮助MySQL高效获取数据的数据结构。 因为索引是MySQL中比较重点的知识，相信很多人都有一定的了解，尤其是在面试中出现的频率特别高。楼主自认为自己对MySQL的索引相关知识有很多了解，而且因为最近在找工作面试，所以单独复习了很多关于索引的知识。 但是，我还是图样图森破，直到我被阿里的面试官虐过之后我才知道，自己在索引方面的知识，只是个小学生水平。 以下，是我总结的一次阿里面试中关于索引有关的问题以及知识点。 索引概念、索引模型 我们是怎么聊到索引的呢，是因为我提到我们的业务量比较大，每天大概有几百万的新数据生成，于是有了以下对话： 面试官：你们每天这么大的数据量，都是保存在关系型数据库中吗？ 我：是的，我们线上使用的是MySQL数据库 面试官：每天几百万数据，一个月就是几千万了，那你们有没有对于查询做一些优化呢？ 我：我们在数据库中创建了一些索引（我现在非常后悔我当时说了这句话）。 这里可以看到，阿里的面试官并不会像有一些公司一样拿着题库一道一道的问，而是会根据面试者做过的事情以及面试过程中的一些内容进行展开。 面试官：那你能说说什么是索引吗？ 我：（这道题肯定难不住我啊）索引其实是一种数据结构，能够帮助我们快速的检索数据库中的数据。 面试官：那么索引具体采用的哪种数据结构呢？ 我：（这道题我也背过）常见的MySQL主要有两种结构：Hash索引和B+ Tree索引，我们使用的是InnoDB引擎，默认的是B+树。 这里我耍了一个小心机，特意说了一下索引和存储引擎有关。希望面试官可以问我一些关于存储引擎的问题。 面试官：既然你提到InnoDB使用的B+ Tree的索引模型，那么你知道为什么采用B+ 树吗？这和Hash索引比较起来有什么优缺点吗？ 我：（突然觉得这道题有点难，但是我还是凭借着自己的知识储备简单的回答上一些）因为Hash索引底层是哈希表，哈希表是一种以key-value存储数据的结构，所以多个数据在存储关系上是完全没有任何顺序关系的，所以，对于区间查询是无法直接通过索引查询的，就需要全表扫描。所以，哈希索引只适用于等值查询的场景。而B+ Tree是一种多路平衡查询树，所以他的节点是天然有序的（左子节点小于父节点、父节点小于右子节点），所以对于范围查询的时候不需要做全表扫描。 面试官：除了上面这个范围查询的，你还能说出其他的一些区别吗？ 我：（这个题我回答的不好，事后百度了一下） 科普时间：B+ Tree索引和Hash索引区别 哈希索引适合等值查询，但是不无法进行范围查询 哈希索引没办法利用索引完成排序 哈希索引不支持多列联合索引的最左匹配规则 如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题 聚簇索引、覆盖索引 面试官：刚刚我们聊到B+ Tree ，那你知道B+ Tree的叶子节点都可以存哪些东西吗？ 我：InnoDB的B+ Tree可能存储的是整行数据，也有可能是主键的值。 面试官：那这两者有什么区别吗？ 我：（当他问我叶子节点的时候，其实我就猜到他可能要问我聚簇索引和非聚簇索引了）在 InnoDB 里，索引B+ Tree的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引。而索引B+ Tree的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引。 面试官：那么，聚簇索引和非聚簇索引，在查询数据的时候有区别吗？ 我：聚簇索引查询会更快？ 面试官：为什么呢？ 我：因为主键索引树的叶子节点直接就是我们要查询的整行数据了。而非主键索引的叶子节点是主键的值，查到主键的值以后，还需要再通过主键的值再进行一次查询。 面试官：刚刚你提到主键索引查询只会查一次，而非主键索引需要回表查询多次。（后来我才知道，原来这个过程叫做回表）是所有情况都是这样的吗？非主键索引一定会查询多次吗？ 我：（额、这个问题我回答的不好，后来我自己查资料才知道，通过覆盖索引也可以只查询一次） 科普时间——覆盖索引 覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。 当一条查询语句符合覆盖索引条件时，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少I/O提高效率。 如，表covering_index_sample中有一个普通索引 idx_key1_key2(key1,key2)。当我们通过SQL语句：select key2 from covering_index_sample where key1 = ‘keytest’;的时候，就可以通过覆盖索引查询，无需回表。 联合索引、最左前缀匹配 面试官：不知道的话没关系，想问一下，你们在创建索引的时候都会考虑哪些因素呢？ 我：我们一般对于查询概率比较高，经常作为where条件的字段设置索引 面试官：那你们有用过联合索引吗？ 我：用过呀，我们有对一些表中创建过联合索引。 面试官：那你们在创建联合索引的时候，需要做联合索引多个字段之间顺序你们是如何选择的呢？ 我：我们把识别度最高的字段放到最前面。 面试官：为什么这么做呢？ 我：（这个问题有点把我问蒙了，稍微有些慌乱）这样的话可能命中率会高一点吧。。。 面试官：那你知道最左前缀匹配吗？ 我：（我突然想起来原来面试官是想问这个，怪自己刚刚为什么就没想到这个呢。）哦哦哦。您刚刚问的是这个意思啊，在创建多列索引时，我们根据业务需求，where子句中使用最频繁的一列放在最左边，因为MySQL索引查询会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。所以当我们创建一个联合索引的时候，如(key1,key2,key3)，相当于创建了（key1）、(key1,key2)和(key1,key2,key3)三个索引，这就是最左匹配原则。 虽然我一开始有点懵，没有联想到最左前缀匹配，但是面试官还是引导了我。很友善。 索引下推、查询优化 面试官：你们线上用的MySQL是哪个版本啊呢？ 我：我们MySQL是5.7 面试官：那你知道在MySQL 5.6中，对索引做了哪些优化吗？ 不好意思，这个我没有去了解过。（事后我查了一下，有一个比较重要的 ：Index Condition Pushdown Optimization） 科普时间—— Index Condition Pushdown（索引下推） MySQL 5.6引入了索引下推优化，默认开启，使用SET optimizer_switch = ‘index_condition_pushdown=off’;可以将其关闭。官方文档中给的例子和解释如下： people表中（zipcode，lastname，firstname）构成一个索引 SELECT * FROM people WHERE zipcode=’95054′ AND lastname LIKE ‘%etrunia%’ AND address LIKE ‘%Main Street%’; 如果没有使用索引下推技术，则MySQL会通过zipcode=’95054’从存储引擎中查询对应的数据，返回到MySQL服务端，然后MySQL服务端基于lastname LIKE ‘%etrunia%’和address LIKE ‘%Main Street%’来判断数据是否符合条件。 如果使用了索引下推技术，则MYSQL首先会返回符合zipcode=’95054’的索引，然后根据lastname LIKE ‘%etrunia%’和address LIKE ‘%Main Street%’来判断索引是否符合条件。如果符合条件，则根据该索引来定位对应的数据，如果不符合，则直接reject掉。 有了索引下推优化，可以在有like条件查询的情况下，减少回表次数。 面试官：你们创建的那么多索引，到底有没有生效，或者说你们的SQL语句有没有使用索引查询你们有统计过吗？ 我：这个还没有统计过，除非遇到慢SQL的时候我们才会去排查 面试官：那排查的时候，有什么手段可以知道有没有走索引查询呢？ 我：可以通过explain查看sql语句的执行计划，通过执行计划来分析索引使用情况 面试官：那什么情况下会发生明明创建了索引，但是执行的时候并没有通过索引呢？ 我：（依稀记得和优化器有关，但是这个问题并没有回答好） 科普时间——查询优化器 一条SQL语句的查询，可以有不同的执行方案，至于最终选择哪种方案，需要通过优化器进行选择，选择执行成本最低的方案。 在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案。这个成本最低的方案就是所谓的执行计划。 优化过程大致如下： 1、根据搜索条件，找出所有可能使用的索引 2、计算全表扫描的代价 3、计算使用不同索引执行查询的代价 4、对比各种执行方案的代价，找出成本最低的那一个 面试官：哦，索引有关的知识我们暂时就问这么多吧。你们线上数据的事务隔离级别是什么呀？ 我：(后面关于事务隔离级别的问题了，就不展开了) 感觉是因为我回答的不够好，如果这几个索引问题我都会的话，他还会追问更多，恐怕会被虐的更惨 总结&感悟 以上，就是一次面试中关于索引部分知识的问题以及我整理的答案。感觉这次面试过程中关于索引的知识，自己大概能够回答的内容占70%左右，但是自信完全答对的内容只占50%左右，看来自己索引有关的知识了解的还是不够多。 通过这次面试，发现像阿里这种大厂对于底层知识还是比较看重的，我以前以为关于索引最多也就问一下Hash和B+有什么区别，没想到最后都能问到查询优化器上面。 最后，不管本次面试能不能通过，都非常感谢有这样一次机会，可以让自己看到自己的不足。通过这次面试，我也收获了很多东西。加油！ 参考资料： 极客时间 -《MySQL实战45讲》 掘金小册 -《MySQL 是怎样运行的：从根儿上理解 MySQL》 博文视点 -《高性能MySQL》
-
彻底理解数据库事务 事务 事务(Transaction)，一般是指要做的或所做的事情。在计算机术语中是指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。在计算机术语中，事务通常就是指数据库事务。 概念 一个数据库事务通常包含对数据库进行读或写的一个操作序列。它的存在包含有以下两个目的： 1、为数据库操作提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。2、当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。 当一个事务被提交给了DBMS（数据库管理系统），则DBMS需要确保该事务中的所有操作都成功完成且其结果被永久保存在数据库中，如果事务中有的操作没有成功完成，则事务中的所有操作都需要被回滚，回到事务执行前的状态（要么全执行，要么全都不执行）;同时，该事务对数据库或者其他事务的执行无影响，所有的事务都好像在独立的运行。 但在现实情况下，失败的风险很高。在一个数据库事务的执行过程中，有可能会遇上事务操作失败、数据库系统/操作系统失败，甚至是存储介质失败等情况。这便需要DBMS对一个执行失败的事务执行恢复操作，将其数据库状态恢复到一致状态（数据的一致性得到保证的状态）。为了实现将数据库状态恢复到一致状态的功能，DBMS通常需要维护事务日志以追踪事务中所有影响数据库数据的操作。 特性 并非任意的对数据库的操作序列都是数据库事务。事务应该具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。 原子性（Atomicity）：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。持久性（Durability）：一个事务一旦提交，他对数据库的修改应该永久保存在数据库中。 举例 用一个常用的“A账户向B账号汇钱”的例子来说明如何通过数据库事务保证数据的准确性和完整性。熟悉关系型数据库事务的都知道从帐号A到帐号B需要6个操作： 1、从A账号中把余额读出来（500）。2、对A账号做减法操作（500-100）。3、把结果写回A账号中（400）。4、从B账号中把余额读出来（500）。5、对B账号做加法操作（500+100）。6、把结果写回B账号中（600）。 原子性： 保证1-6所有过程要么都执行，要么都不执行。一旦在执行某一步骤的过程中发生问题，就需要执行回滚操作。 假如执行到第五步的时候，B账户突然不可用（比如被注销），那么之前的所有操作都应该回滚到执行事务之前的状态。 一致性 在转账之前，A和B的账户中共有500+500=1000元钱。在转账之后，A和B的账户中共有400+600=1000元。也就是说，数据的状态在执行该事务操作之后从一个状态改变到了另外一个状态。同时一致性还能保证账户余额不会变成负数等。 隔离性 在A向B转账的整个过程中，只要事务还没有提交（commit），查询A账户和B账户的时候，两个账户里面的钱的数量都不会有变化。如果在A给B转账的同时，有另外一个事务执行了C给B转账的操作，那么当两个事务都结束的时候，B账户里面的钱应该是A转给B的钱加上C转给B的钱再加上自己原有的钱。 持久性 一旦转账成功（事务提交），两个账户的里面的钱就会真的发生变化（会把数据写入数据库做持久化保存）！ 原子性与隔离行 一致性与原子性是密切相关的,原子性的破坏可能导致数据库的不一致，数据的一致性问题并不都和原子性有关。比如刚刚的例子，在第五步的时候，对B账户做加法时只加了50元。那么该过程可以符合原子性，但是数据的一致性就出现了问题。 因此，事务的原子性与一致性缺一不可。
-
再有人问你什么是MVCC，就把这篇文章发给他！ 一想到并发控制，很多人第一反应就是加锁，的确，加锁确实是解决并发问题最常见的方案。但是，其实除了加锁以外，在数据库领域，还有一种无锁的方案可以来实现并发控制，那就是大名鼎鼎的MVCC。 MVCC，是Multiversion Concurrency Control的缩写，翻译过来是多版本并发控制，他也是一种并发控制的解决方案。 我们知道，在数据库中，对数据的操作主要有2中，分别是读和写，而在并发场景下，就可能出现以下三种情况： 读-读并发 读-写并发 写-写并发 我们都知道，在没有写的情况下读-读并发是不会出现问题的，而写-写并发这种情况比较常用的就是通过加锁的方式实现。那么，读-写并发则可以通过MVCC的机制解决。 快照读和当前读 要想搞清楚MVCC的机制，最重要的一个概念那就是快照读。 所谓快照读，就是读取的是快照数据，即快照生成的那一刻的数据，像我们常用的普通的SELECT语句在不加锁情况下就是快照读。如： SELECT * FROM xx_table WHERE ... 和快照读相对应的另外一个概念叫做当前读，当前读就是读取最新数据，所以，加锁的 SELECT，或者对数据进行增删改都会进行当前读，比如： SELECT * FROM xx_table LOCK IN SHARE MODE; SELECT * FROM xx_table FOR UPDATE; INSERT INTO xx_table ... DELETE FROM xx_table ... UPDATE xx_table ... 可以说快照读是MVCC实现的基础，而当前读是悲观锁实现的基础。 那么，快照读读到的快照是从哪里读到的的呢？换句话说，快照是存在哪里的呢？ UndoLog undo log是Mysql中比较重要的事务日志之一，顾名思义，undo log是一种用于回退的日志，在事务没提交之前，MySQL会先记录更新前的数据到 undo log日志文件里面，当事务回滚时或者数据库崩溃时，可以利用 undo log来进行回退。 这里面提到的存在undo log中的”更新前的数据”就是我们前面提到的快照。所以，这也是为什么很多人说UndoLog是MVCC实现的重要手段的原因。 那么，一条记录在同一时刻可能有多个事务在执行，那么，undo log会有一条记录的多个快照，那么在这一时刻发生SELECT要进行快照读的时候，要读哪个快照呢？ 这就需要用到另外几个信息了。 行记录的隐式字段 其实，数据库中的每行记录中，除了保存了我们自己定义的一些字段以外，还有一些重要的隐式字段的： db_row_id：隐藏主键，如果我们没有给这个表创建主键，那么会以这个字段来创建聚簇索引。 db_trx_id：对这条记录做了最新一次修改的事务的ID db_roll_ptr：回滚指针，指向这条记录的上一个版本，其实他指向的就是Undo Log中的上一个版本的快照的地址。 因为每一次记录变更之前都会先存储一份快照到undo log中，那么这几个隐式字段也会跟着记录一起保存在undo log中，就这样，每一个快照中都有有一个db_trx_id字段记录了本次变更的事务ID，以及一个db_roll_ptr字段指向了上一个快照的地址。（db_trx_id和db_roll_ptr是重点，后面还会用到） 这样，就形成了一个快照链表： 有了undo log，又有了几个隐式字段，我们好像还是不知道具体应该读取哪个快照，那怎么办呢？ Read View 这时候就需要Read View 登场了， Read View 主要来帮我们解决可见性的问题的, 即他会来告诉我们本次事务应该看到哪个快照，不应该看到哪个快照。 在 Read View 中有几个重要的属性： trx_ids，系统当前未提交的事务 ID 的列表。 low_limit_id，未提交的事务中最大的事务 ID。 up_limit_id，未提交的事务中最小的事务 ID。 creator_trx_id，创建这个 Read View 的事务 ID。 每开启一个事务，我们都会从数据库中获得一个事务 ID，这个事务 ID 是自增长的，通过 ID 大小，我们就可以判断事务的时间顺序。 那么，一个事务应该看到哪些快照，不应该看到哪些快照该如何判断呢？ 其实原则比较简单，那就是事务ID大的事务应该能看到事务ID小的事务的变更结果，反之则不能！举个例子： 假如当前有一个事务3想要对某条记录进行一次快照读的时候，他会先创建一个Read View，并且把当前所有还未提交的事务的信息记录下来。比如up_limit_id = 2，low_limit_id = 5，trx_ids= [2,4,5]，creator_trx_id= 6 我们前面说过，每一条记录上都有一个隐式字段db_trx_id记录对这条记录做了最新一次修改的事务的ID，如db_trx_id = 3； 那么接下来，数据库会拿这条记录db_trx_id和Read View进行可见性比较。 如果db_trx_idlow_limit_id，则说明，db_trx_id = 3的这个事务是在Read View中所有未提交的事务创建之后才提交的，也就是说，在当前事务开启之后，有别的事务修改了数据并作了提交。所以，这个记录对于当前事务来说应该就是不可见的。（不可见怎么办呢？后面讲） 那么，还有另外一种情况，那就是up_limit_id > db_trx_id > low_limit_id，这种情况下，会再拿db_trx_id和Read View中的trx_ids进行逐一比较。 如果，db_trx_id在trx_ids列表中，那么表示在当前事务开启时，并未提交的某个事务在修改数据之后提交了，那么这个记录对于当前事务来说应该是不可见的。 如果，db_trx_id不在trx_ids列表中，那么表示的是在当前事务开启之前，其他事务对数据进行修改并提交了，所有，这条记录对当前事务就应该是可见的。 所以，当读取一条记录的时候，经过以上判断，发现记录对当前事务可见，那么就直接返回就行了。那么如果不可见怎么办？没错，那就需要用到undo log了。 当数据的事务ID不符合Read View规则时候，那就需要从undo log里面获取数据的历史快照，然后数据快照的事务ID再来和Read View进行可见性比较，如果找到一条快照，则返回，找不到则返回空。 所以，总结一下，在InnoDB中，MVCC就是通过Read View + Undo Log来实现的，undo log中保存了历史快照，而Read View 用来判断具体哪一个快照是可见的。 MVCC和可重复读 其实，根据不同的事务隔离级别，Read View的获取时机是不同的，在RC下，一个事务中的每一次SELECT都会重新获取一次Read View，而在RR下，一个事务中只在第一次SELECT的时候会获取一次Read View。 所以，可重复读这种事务隔离级别之下，因为有MVCC机制，就可以解决不可重复读的问题，因为他只有在第一次SELECT的时候才会获取一次Read View，天然不存在重复读的问题了。
-
MySQL中的行级锁,表级锁,页级锁 在计算机科学中，锁是在执行多线程时用于强行限制资源访问的同步机制，即用于在并发控制中保证对互斥要求的满足。 在数据库的锁机制中介绍过，在DBMS中，可以按照锁的粒度把数据库锁分为行级锁(INNODB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎 )。 行级锁 行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁 和 排他锁。 特点 开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。 表级锁 表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。 特点 开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。 页级锁 页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。BDB支持页级锁 特点 开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般 MySQL常用存储引擎的锁机制 MyISAM和MEMORY采用表级锁(table-level locking) BDB采用页面锁(page-level locking)或表级锁，默认为页面锁 InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁 Innodb中的行锁与表锁 前面提到过，在Innodb引擎中既支持行锁也支持表锁，那么什么时候会锁住整张表，什么时候或只锁住一行呢？ InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！ 在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。 在不通过索引条件查询的时候,InnoDB 确实使用的是表锁,而不是行锁。 由于 MySQL 的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行 的记录,但是如果是使用相同的索引键,是会出现锁冲突的。应用设计的时候要注意这一点。 当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论 是使用主键索引、唯一索引或普通索引,InnoDB 都会使用行锁来对数据加锁。 即便在条件中使用了索引字段,但是否使用索引来检索数据是由 MySQL 通过判断不同 执行计划的代价来决定的,如果 MySQL 认为全表扫 效率更高,比如对一些很小的表,它 就不会使用索引,这种情况下 InnoDB 将使用表锁,而不是行锁。因此,在分析锁冲突时, 别忘了检查 SQL 的执行计划,以确认是否真正使用了索引。 行级锁与死锁 MyISAM中是不会产生死锁的，因为MyISAM总是一次性获得所需的全部锁，要么全部满足，要么全部等待。而在InnoDB中，锁是逐步获得的，就造成了死锁的可能。 在MySQL中，行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引；如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。 在UPDATE、DELETE操作时，MySQL不仅锁定WHERE条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的next-key locking。 当两个事务同时执行，一个锁住了主键索引，在等待其他相关索引。另一个锁定了非主键索引，在等待主键索引。这样就会发生死锁。 发生死锁后，InnoDB一般都可以检测到，并使一个事务释放锁回退，另一个获取锁完成事务。 有多种方法可以避免死锁，这里只介绍常见的三种 1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。 2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率； 3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率； 参考资料 20.3.4 InnoDB行锁实现方式
