推荐最新

过来人说说， postgresql 和 mysql 究竟怎么选？

从客户要求，系统架构，公司内部技术栈，领导偏好，个人使用体验等多方面聊聊

浏览量298

MYSQL 事务的底层原理 | 京东物流技术团队

事务的底层原理在事务的实现机制上，MySQL 采用的是 WAL：Write-ahead logging，预写式日志，机制来实现的。在使用 WAL 的系统中，所有的修改都先被写入到日志中，然后再被应用到系统中。通常包含 redo 和 undo 两部分信息。为什么需要使用 WAL，然后包含 redo 和 undo 信息呢？举个例子，如果一个系统直接将变更应用到系统状态中，那么在机器掉电重启之后系统需要知道操作是成功了，还是只有部分成功或者是失败了。如果使用了 WAL，那么在重启之后系统可以通过比较日志和系统状态来决定是继续完成操作还是撤销操作。redo log 称为重做日志，每当有操作时，在数据变更之前将操作写入 redo log，这样当发生掉电之类的情况时系统可以在重启后继续操作。undo log 称为撤销日志，当一些变更执行到一半无法完成时，可以根据撤销日志恢复到变更之间的状态。MySQL 中用 redo log 来在系统 Crash 重启之类的情况时修复数据，而 undo log 来保证事务的原子性。事务 id一个事务可以是一个只读事务，或者是一个读写事务：可以通过 START TRANSACTION READ ONLY 语句开启一个只读事务。在只读事务中不可以对普通的表进行增、删、改操作，但可以对用户临时表做增、删、改操作。可以通过 START TRANSACTION READ WRITE 语句开启一个读写事务，或者使用 BEGIN、START TRANSACTION 语句开启的事务默认也算是读写事务。在读写事务中可以对表执行增删改查操作。如果某个事务执行过程中对某个表执行了增、删、改操作，那么 InnoDB 存储引擎就会给它分配一个独一无二的事务 id，针对 MySQL 5.7 分配方式如下：对于只读事务来说，只有在它第一次对某个用户创建的临时表执行增、删、改操作时才会为这个事务分配一个事务 id，否则的话是不分配事务 id 的。对于读写事务来说，只有在它第一次对某个表执行增、删、改操作时才会为这个事务分配一个事务 id，否则的话也是不分配事务 id 的。有的时候虽然开启了一个读写事务，但是在这个事务中全是查询语句，并没有执行增、删、改的语句，那也就意味着这个事务并不会被分配一个事务 id。这个事务 id 本质上就是一个数字，它的分配策略和隐藏列 row_id 的分配策略大抵相同，具体策略如下：服务器会在内存中维护一个全局变量，每当需要为某个事务分配一个事务 id 时，就会把该变量的值当作事务 id 分配给该事务，并且把该变量自增 1。每当这个变量的值为 256 的倍数时，就会将该变量的值刷新到系统表空间的页号为 5 的页面中一个称之为 Max Trx ID 的属性处，这个属性占用 8 个字节的存储空间。当系统下一次重新启动时，会将上边提到的 Max Trx ID 属性加载到内存中，将该值加上 256 之后赋值给全局变量，因为在上次关机时该全局变量的值可能大于Max Trx ID 属性值。这样就可以保证整个系统中分配的事务 id 值是一个递增的数字。先被分配 id 的事务得到的是较小的事务 id，后被分配 id 的事务得到的是较大的事务 id。mvcc全称 Multi-Version Concurrency Control，即多版本并发控制，主要是为了提高数据库的并发性能。同一行数据平时发生读写请求时，会上锁阻塞住。但 MVCC 用更好的方式去处理读写请求，做到在发生读写请求冲突时不用加锁。这个读是指的快照读，而不是当前读，当前读是一种加锁操作，是悲观锁。MVCC 原理在事务并发执行遇到的问题如下：脏读：如果一个事务读到了另一个未提交事务修改过的数据，那就意味着发生了脏读；不可重复读：如果一个事务只能读到另一个已经提交的事务修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值，那就意味着发生了不可重复读；幻读：如果一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来，那就意味着发生了幻读，幻读强调的是一个事务按照某个相同条件多次读取记录时，后读取时读到了之前没有读到的记录，幻读只是重点强调了读取到了之前读取没有获取到的记录。MySQL 在 REPEATABLE READ 隔离级别下，是可以很大程度避免幻读问题的发生的。版本链对于使用 InnoDB 存储引擎的表来说，它的聚簇索引记录中都包含两个必要的隐藏列：trx_id：每次一个事务对某条聚簇索引记录进行改动时，都会把该事务的事务 id 赋值给 trx_id 隐藏列；roll_pointer：每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到 undo 日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息；演示-- 创建表 CREATE TABLE mvcc_test ( id INT, name VARCHAR(100), domain varchar(100), PRIMARY KEY (id) ) Engine=InnoDB CHARSET=utf8; -- 添加数据 INSERT INTO mvcc_test VALUES(1, 'habit', '演示mvcc'); 假设插入该记录的事务 id=50，那么该条记录的展示如图：假设之后两个事务 id 分别为 70、90 的事务对这条记录进行 UPDATE 操作。trx_id=70trx_id=90beginbeginupdate mvcc_test set name='habit_trx_id_70_01' where id=1update mvcc_test set name='habit_trx_id_70_02' where id=1commitupdate mvcc_test set name='habit_trx_id_90_01' where id=1update mvcc_test set name='habit_trx_id_90_02' where id=1commit每次对记录进行改动，都会记录一条 undo 日志，每条 undo 日志也都有一个 roll_pointer 属性，可以将这些 undo 日志都连起来，串成一个链表。对该记录每次更新后，都会将旧值放到一条 undo 日志中，就算是该记录的一个旧版本，随着更新次数的增多，所有的版本都会被 roll_pointer 属性连接成一个链表，把这个链表称之为版本链，版本链的头节点就是当前记录最新的值。另外，每个版本中还包含生成该版本时对应的事务 id。于是可以利用这个记录的版本链来控制并发事务访问相同记录的行为，那么这种机制就被称之为：多版本并发控制，即 MVCC。ReadView对于使用 READ UNCOMMITTED 隔离级别的事务来说，由于可以读到未提交事务修改过的记录，所以直接读取记录的最新版本就好了。对于使用 SERIALIZABLE 隔离级别的事务来说，InnoDB 使用加锁的方式来访问记录。对于使用 READ COMMITTED 和 REPEATABLE READ 隔离级别的事务来说，都必须保证读到已经提交了的事务修改过的记录，也就是说假如另一个事务已经修改了记录但是尚未提交，是不能直接读取最新版本的记录的，核心问题就是：READ COMMITTED 和 REPEATABLE READ 隔离级别在不可重复读和幻读上的区别是从哪里来的，其实结合前面的知识，这两种隔离级别关键是需要判断一下版本链中的哪个版本是当前事务可见的。为此，InnoDB 提出了一个 ReadView 的概念，这个 ReadView 中主要包含 4 个比较重要的内容：m_ids：表示在生成 ReadView 时当前系统中活跃的读写事务的事务id 列表；min_trx_id：表示在生成 ReadView 时当前系统中活跃的读写事务中最小的事务 id，也就是 m_ids 中的最小值；max_trx_id：表示在生成 ReadView 时系统中应该分配给下一个事务的 id 值，注：max_trx_id 并不是 m_ids 中的最大值，事务 id 是递增分配的。比方说现在有 id 为 1，2，3 这三个事务，之后 id 为 3 的事务提交了。那么一个新的读事务在生成 ReadView 时，m_ids 就包括 1 和 2，min_trx_id 的值就是 1，max_trx_id 的值就是 4；creator_trx_id：表示生成该 ReadView 的事务的事务 id；有了这个 ReadView，这样在访问某条记录时，只需要按照下边的步骤判断记录的某个版本是否可见：如果被访问版本的 trx_id 属性值与 ReadView 中的 creator_trx_id 值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问；如果被访问版本的 trx_id 属性值小于 ReadView 中的 min_trx_id 值，表明生成该版本的事务在当前事务生成 ReadView 前已经提交，所以该版本可以被当前事务访问；如果被访问版本的 trx_id 属性值大于或等于 ReadView 中的 max_trx_id 值，表明生成该版本的事务在当前事务生成 ReadView 后才开启，所以该版本不可以被当前事务访问；如果被访问版本的 trx_id 属性值在 ReadView 的 min_trx_id 和 max_trx_id之间 min_trx_id < trx_id < max_trx_id，那就需要判断一下 trx_id 属性值是不是在 m_ids 列表中，如果在，说明创建 ReadView 时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建 ReadView 时生成该版本的事务已经被提交，该版本可以被访问；如果某个版本的数据对当前事务不可见的话，那就顺着版本链找到下一个版本的数据，继续按照上边的步骤判断可见性，依此类推，直到版本链中的最后一个版本。如果最后一个版本也不可见的话，那么就意味着该条记录对该事务完全不可见，查询结果就不包含该记录；在 MySQL 中，READ COMMITTED 和 REPEATABLE READ 隔离级别的一个非常大的区别就是它们生成 ReadView 的时机不同。还是以表 mvcc_test 为例，假设现在表 mvcc_test 中只有一条由事务 id 为 50 的事务插入的一条记录，接下来看一下 READ COMMITTED 和 REPEATABLE READ 所谓的生成 ReadView 的时机不同到底不同在哪里。READ COMMITTED：每次读取数据前都生成一个 ReadView；比方说现在系统里有两个事务id 分别为 70、90 的事务在执行：-- T 70 UPDATE mvcc_test SET name = 'habit_trx_id_70_01' WHERE id = 1; UPDATE mvcc_test SET name = 'habit_trx_id_70_02' WHERE id = 1; 此时表 mvcc_test 中 id 为 1 的记录得到的版本链表如下所示：假设现在有一个使用 READ COMMITTED 隔离级别的事务开始执行：-- 使用 READ COMMITTED 隔离级别的事务 BEGIN; -- SELECE1：Transaction 70、90 未提交 SELECT * FROM mvcc_test WHERE id = 1; -- 得到的列 name 的值为'habit' 这个 SELECE1 的执行过程如下：在执行 SELECT 语句时会先生成一个 ReadView，ReadView 的 m_ids 列表的内容就是[70, 90]，min_trx_id 为 70，max_trx_id 为 91，creator_trx_id 为 0。然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列 name 的内容是 habit_trx_id_70_02，该版本的 trx_id 值为 70，在 m_ids 列表内，所以不符合可见性要求第 4 条：**如果被访问版本的 trx_id 属性值在 ReadView 的 min_trx_id 和 max_trx_id之间 min_trx_id < trx_id < max_trx_id，那就需要判断一下trx_id 属性值是不是在 m_ids 列表中，如果在，说明创建 ReadView 时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建 ReadView 时生成该版本的事务已经被提交，该版本可以被访问。**根据 roll_pointer 跳到下一个版本。下一个版本的列 name 的内容是 habit_trx_id_70_01，该版本的 trx_id 值也为 70，也在 m_ids 列表内，所以也不符合要求，继续跳到下一个版本。下一个版本的列 name 的内容是 habit，该版本的 trx_id 值为 50，小于 ReadView 中的 min_trx_id 值，所以这个版本是符合要求的第 2 条：**如果被访问版本的 trx_id 属性值小于 ReadView 中的 min_trx_id 值，表明生成该版本的事务在当前事务生成 ReadView 前已经提交，所以该版本可以被当前事务访问。**最后返回的版本就是这条列 name 为 habit 的记录。之后，把事务 id 为 70 的事务提交一下，然后再到事务 id 为 90 的事务中更新一下表 mvcc_test 中 id 为 1 的记录：-- T 90 UPDATE mvcc_test SET name = 'habit_trx_id_90_01' WHERE id = 1; UPDATE mvcc_test SET name = 'habit_trx_id_90_02' WHERE id = 1; 此时表 mvcc 中 id 为 1 的记录的版本链就长这样：然后再到刚才使用 READ COMMITTED 隔离级别的事务中继续查找这个 id 为 1 的记录，如下：-- 使用 READ COMMITTED 隔离级别的事务 BEGIN; -- SELECE1：Transaction 70、90 均未提交 SELECT * FROM mvcc_test WHERE id = 1; -- 得到的列 name 的值为'habit' -- SELECE2：Transaction 70 提交，Transaction 90 未提交 SELECT * FROM mvcc_test WHERE id = 1; -- 得到的列 name 的值为'habit_trx_id_70_02' 这个SELECE2 的执行过程如下：在执行 SELECT 语句时又会单独生成一个 ReadView，该 ReadView 的 m_ids 列表的内容就是[90]，min_trx_id 为90，max_trx_id 为 91，creator_trx_id 为 0。然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列 name 的内容是 habit_trx_id_90_02，该版本的 trx_id 值为 90，在 m_ids 列表内，所以不符合可见性要求，根据 roll_pointer 跳到下一个版本。下一个版本的列 name 的内容是 habit_trx_id_90_01，该版本的 trx_id 值为 90，也在 m_ids 列表内，所以也不符合要求，继续跳到下一个版本。下一个版本的列 name 的内容是 habit_trx_id_70_02，该版本的 trx_id 值为 70，小于 ReadView 中的 min_trx_id 值 90，所以这个版本是符合要求的，最后返回这个版本中列 name 为 habit_trx_id_70_02 的记录。以此类推，如果之后事务 id 为 90 的记录也提交了，再次在使用 READ COMMITTED 隔离级别的事务中查询表 mvcc_test 中 id 值为 1 的记录时，得到的结果就是 habit_trx_id_90_02 了。**总结：**使用 READ COMMITTED 隔离级别的事务在每次查询开始时都会生成一个独立的 ReadView。**REPEATABLE READ：**在第一次读取数据时生成一个 ReadView；对于使用 REPEATABLE READ 隔离级别的事务来说，只会在第一次执行查询语句时生成一个 ReadView，之后的查询就不会重复生成了。比方说现在系统里有两个事务id 分别为 70、90 的事务在执行：-- T 70 UPDATE mvcc_test SET name = 'habit_trx_id_70_01' WHERE id = 1; UPDATE mvcc_test SET name = 'habit_trx_id_70_02' WHERE id = 1; 此时表 mvcc_test 中 id 为 1 的记录得到的版本链表如下所示：假设现在有一个使用 REPEATABLE READ 隔离级别的事务开始执行：-- 使用 REPEATABLE READ 隔离级别的事务 BEGIN; -- SELECE1：Transaction 70、90 未提交 SELECT * FROM mvcc_test WHERE id = 1; -- 得到的列name 的值为'habit' 这个 SELECE1 的执行过程如下：在执行 SELECT 语句时会先生成一个 ReadView，ReadView 的 m_ids 列表的内容就是[70, 90]，min_trx_id 为 70，max_trx_id 为 91，creator_trx_id 为 0。然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列 name 的内容是 habit_trx_id_70_02，该版本的 trx_id 值为 70，在 m_ids 列表内，所以不符合可见性要求，根据 roll_pointer 跳到下一个版本。下一个版本的列 name 的内容是 habit_trx_id_70_01，该版本的 trx_id 值也为 70，也在 m_ids 列表内，所以也不符合要求，继续跳到下一个版本。下一个版本的列 name 的内容是 habit，该版本的 trx_id 值为 50，小于 ReadView 中的 min_trx_id 值，所以这个版本是符合要求的，最后返回的就是这条列name 为 habit 的记录。之后，把事务 id 为 70 的事务提交一下，然后再到事务 id 为 90 的事务中更新一下表 mvcc_test 中 id 为 1 的记录：-- 使用 REPEATABLE READ 隔离级别的事务 BEGIN; UPDATE mvcc_test SET name = 'habit_trx_id_90_01' WHERE id = 1; UPDATE mvcc_test SET name = 'habit_trx_id_90_02' WHERE id = 1; 此刻，表 mvcc_test 中 id 为 1 的记录的版本链就长这样：然后再到刚才使用 REPEATABLE READ 隔离级别的事务中继续查找这个 id 为 1 的记录，如下：-- 使用 REPEATABLE READ 隔离级别的事务 BEGIN; -- SELECE1：Transaction 70、90 均未提交 SELECT * FROM mvcc_test WHERE id = 1; -- 得到的列 name 的值为'habit' -- SELECE2：Transaction 70 提交，Transaction 90 未提交 SELECT * FROM mvcc_test WHERE id = 1; -- 得到的列 name 的值为'habit' 这个 SELECE2 的执行过程如下：因为当前事务的隔离级别为 REPEATABLE READ，而之前在执行 SELECE1 时已经生成过 ReadView 了，所以此时直接复用之前的 ReadView，之前的 ReadView的 m_ids 列表的内容就是[70, 90]，min_trx_id 为 70，max_trx_id 为 91， creator_trx_id 为 0。然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列 name 的内容是 habit_trx_id_90_02，该版本的 trx_id 值为 90，在 m_ids 列表内，所以不符合可见性要求，根据 roll_pointer 跳到下一个版本。下一个版本的列 name 的内容是 habit_trx_id_90_01，该版本的 trx_id 值为 90，也在 m_ids 列表内，所以也不符合要求，继续跳到下一个版本。下一个版本的列 name 的内容是 habit_trx_id_70_02，该版本的 trx_id 值为 70，而 m_ids 列表中是包含值为 70 的事务 id 的，所以该版本也不符合要求，同理下一个列 name 的内容是 habit_trx_id_70_01 的版本也不符合要求。继续跳到下一个版本。下一个版本的列 name 的内容是 habit，该版本的 trx_id 值为 50，小于 ReadView 中的 min_trx_id 值 70，所以这个版本是符合要求的，最后返回给用户的版本就是这条列 name 为 habit 的记录。也就是说两次 SELECT 查询得到的结果是重复的，记录的列 name 值都是 habit，这就是可重复读的含义。如果之后再把事务 id 为 90 的记录提交了，然后再到刚才使用 REPEATABLE READ 隔离级别的事务中继续查找这个 id 为 1 的记录，得到的结果还是 habit。MVCC 下的幻读解决和幻读现象REPEATABLE READ 隔离级别下 MVCC 可以解决不可重复读问题，那么幻读呢？MVCC 是怎么解决的？幻读是一个事务按照某个相同条件多次读取记录时，后读取时读到了之前没有读到的记录，而这个记录来自另一个事务添加的新记录。可以想想，在 REPEATABLE READ 隔离级别下的事务 T1 先根据某个搜索条件读取到多条记录，然后事务 T2 插入一条符合相应搜索条件的记录并提交，然后事务 T1 再根据相同搜索条件执行查询。结果会是什么？按照 ReadView 中的比较规则中的第 3 条和第 4 条不管事务 T2 比事务 T1 是否先开启，事务 T1 都是看不到 T2 的提交的。但是，在 REPEATABLE READ 隔离级别下 InnoDB 中的 MVCC 可以很大程度地避免幻读现象，而不是完全禁止幻读。怎么回事呢？来看下面的情况：首先在事务 T1 中执行：select * from mvcc_test where id = 30; 这个时候是找不到 id = 30 的记录的。在事务 T2 中，执行插入语句：insert into mvcc_test values(30,'luxi','luxi');此时回到事务 T1，执行：update mvcc_test set domain='luxi_t1' where id=30; select * from mvcc_test where id = 30; 事务T1 很明显出现了幻读现象。在 REPEATABLE READ 隔离级别下，T1 第一次执行普通的 SELECT 语句时生成了一个 ReadView，之后 T2 向 mvcc_test 表中新插入一条记录并提交。ReadView 并不能阻止 T1 执行 UPDATE 或者 DELETE 语句来改动这个新插入的记录，由于 T2 已经提交，因此改动该记录并不会造成阻塞，但是这样一来，这条新记录的 trx_id 隐藏列的值就变成了 T1 的事务 id。之后 T1 再使用普通的 SELECT 语句去查询这条记录时就可以看到这条记录了，也就可以把这条记录返回给客户端。因为这个特殊现象的存在，可以认为 MVCC 并不能完全禁止幻读。mvcc 总结从上边的描述中可以看出来，所谓的 MVCC（Multi-Version Concurrency Control ，多版本并发控制）指的就是在使用 READ COMMITTD、REPEATABLE READ 这两种隔离级别的事务在执行普通的 SELECT 操作时访问记录的版本链的过程，这样子可以使不同事务的读写、写读操作并发执行，从而提升系统性能。READ COMMITTD、REPEATABLE READ 这两个隔离级别的一个很大不同就是：生成 ReadView 的时机不同，READ COMMITTD 在每一次进行普通 SELECT 操作前都会生成一个 ReadView，而 REPEATABLE READ 只在第一次进行普通 SELECT 操作前生成一个 ReadView，之后的查询操作都重复使用这个 ReadView 就好了，从而基本上可以避免幻读现象。InnoDB 的 Buffer Pool对于使用 InnoDB 作为存储引擎的表来说，不管是用于存储用户数据的索引，包括：聚簇索引和二级索引，还是各种系统数据，都是以页的形式存放在表空间中的，而所谓的表空间只不过是 InnoDB 对文件系统上一个或几个实际文件的抽象，也就是说数据还是存储在磁盘上的。但是磁盘的速度慢，所以 InnoDB 存储引擎在处理客户端的请求时，当需要访问某个页的数据时，就会把完整的页的数据全部加载到内存中，即使只需要访问一个页的一条记录，那也需要先把整个页的数据加载到内存中。将整个页加载到内存中后就可以进行读写访问了，在进行完读写访问之后并不着急把该页对应的内存空间释放掉，而是将其缓存起来，这样将来有请求再次访问该页面时，就可以省去磁盘 IO 的开销了。Buffer PoolInnoDB 为了缓存磁盘中的页，在 MySQL 服务器启动的时候就向操作系统申请了一片连续的内存，这块连续内存叫做：Buffer Pool，中文名：缓冲池。默认情况下 Buffer Pool 只有 128M 大小。查看该值：show variables like 'innodb_buffer_pool_size';可以在启动服务器的时候配置 innodb_buffer_pool_size 参数的值，它表示 Buffer Pool 的大小，配置如下：[server] innodb_buffer_pool_size = 268435456 其中，268435456 的单位是字节，也就是指定 Buffer Pool 的大小为 256M，Buffer Pool 也不能太小，最小值为 5M，当小于该值时会自动设置成 5M。启动 MySQL 服务器的时候，需要完成对 Buffer Pool 的初始化过程，就是先向操作系统申请 Buffer Pool 的内存空间，然后把它划分成若干对控制块和缓存页。但是此时并没有真实的磁盘页被缓存到 Buffer Pool 中，之后随着程序的运行，会不断的有磁盘上的页被缓存到 Buffer Pool 中。在 Buffer Pool 中会创建多个缓存页，默认的缓存页大小和在磁盘上默认的页大小是一样的，都是 16KB。那么怎么知道该页在不在 Buffer Pool 中呢？在查找数据的时候，先通过哈希表中查找 key 是否在哈希表中，如果在证明 Buffer Pool 中存在该缓存也信息，如果不存在证明不存该缓存也信息，则通过读取磁盘加载该页信息放到 Buffer Pool 中，哈希表中的 key 是通过表空间号+ 页号作组成的，value 是 Buffer Pool 的缓存页。flush 链表的管理如果修改了 Buffer Pool 中某个缓存页的数据，那它就和磁盘上的页不一致了，这样的缓存页也被称为：脏页。最简单的做法就是每发生一次修改就立即同步到磁盘上对应的页上，但是频繁的往磁盘中写数据会严重的影响程序的性能。所以每次修改缓存页后，并不着急把修改同步到磁盘上，而是在未来的某个时间进行同步。但是如果不立即同步到磁盘的话，那之后再同步的时候怎么知道 Buffer Pool 中哪些页是脏页，哪些页从来没被修改过呢？总不能把所有的缓存页都同步到磁盘上吧，如果 Buffer Pool 被设置的很大，那一次性同步会非常慢。所以，需要再创建一个存储脏页的链表，凡是修改过的缓存页对应的控制块都会作为一个节点加入到一个链表中，因为这个链表节点对应的缓存页都是需要被刷新到磁盘上的，所以也叫 flush 链表。刷新脏页到磁盘后台有专门的线程每隔一段时间负责把脏页刷新到磁盘，这样可以不影响用户线程处理正常的请求。从 flush 链表中刷新一部分页面到磁盘，后台线程也会定时从 flush 链表中刷新一部分页面到磁盘，刷新的速率取决于当时系统是不是很繁忙。这种刷新页面的方式被称之为：BUF_FLUSH_LIST。redo 日志redo 日志的作用InnoDB 存储引擎是以页为单位来管理存储空间的，增删改查操作其实本质上都是在访问页面，包括：读页面、写页面、创建新页面等操作。在真正访问页面之前，需要把在磁盘上的页缓存到内存中的 Buffer Pool 之后才可以访问。但是在事务的时候又强调过一个称之为持久性的特性，就是说对于一个已经提交的事务，在事务提交后即使系统发生了崩溃，这个事务对数据库中所做的更改也不能丢失。如果只在内存的 Buffer Pool 中修改了页面，假设在事务提交后突然发生了某个故障，导致内存中的数据都失效了，那么这个已经提交了的事务对数据库中所做的更改也就跟着丢失了，这是所不能忍受的。那么如何保证这个持久性呢？一个很简单的做法就是在事务提交完成之前把该事务所修改的所有页面都刷新到磁盘，但是这个简单粗暴的做法有些问题：刷新一个完整的数据页太浪费了；有时候仅仅修改了某个页面中的一个字节，但是在 InnoDB 中是以页为单位来进行磁盘 IO 的，也就是说在该事务提交时不得不将一个完整的页面从内存中刷新到磁盘，一个页面默认是16KB 大小，只修改一个字节就要刷新 16KB 的数据到磁盘上显然是太浪费了。随机 IO 刷起来比较慢；一个事务可能包含很多语句，即使是一条语句也可能修改许多页面，该事务修改的这些页面可能并不相邻，这就意味着在将某个事务修改的 Buffer Pool 中的页面刷新到磁盘时，需要进行很多的随机 IO，随机 IO 比顺序 IO 要慢，尤其对于传统的机械硬盘来说。只是想让已经提交了的事务对数据库中数据所做的修改永久生效，即使后来系统崩溃，在重启后也能把这种修改恢复出来。其实没有必要在每次事务提交时就把该事务在内存中修改过的全部页面刷新到磁盘，只需要把修改了哪些东西记录一下就好，比方说：某个事务将系统表空间中的第 5 号页面中偏移量为 5000 处的那个字节的值 0 改成 5 只需要记录一下：将第 5 号表空间的 5 号页面的偏移量为 5000 处的值更新为：5。这样在事务提交时，把上述内容刷新到磁盘中，即使之后系统崩溃了，重启之后只要按照上述内容所记录的步骤重新更新一下数据页，那么该事务对数据库中所做的修改又可以被恢复出来，也就意味着满足持久性的要求。因为在系统崩溃重启时需要按照上述内容所记录的步骤重新更新数据页，所以上述内容也被称之为：重做日志，即：redo log。与在事务提交时将所有修改过的内存中的页面刷新到磁盘中相比，只将该事务执行过程中产生的 redo log 刷新到磁盘的好处如下：redo log 占用的空间非常小存储表空间 ID、页号、偏移量以及需要更新的值所需的存储空间是很小的；redo log 是顺序写入磁盘的在执行事务的过程中，每执行一条语句，就可能产生若干条 redo log，这些日志是按照产生的顺序写入磁盘的，也就是使用顺序 IO；redo log 的写入过程InnoDB 为了更好的进行系统崩溃恢复，把一次原子操作生成的 redo log 都放在了大小为 512 字节的块（block）中。为了解决磁盘速度过慢的问题而引入了 Buffer Pool。同理，写入 redo log 时也不能直接写到磁盘上，实际上在服务器启动时就向操作系统申请了一大片称之为 redo log buffer 的连续内存空间，即：redo log 缓冲区，也可以简称：log buffer。这片内存空间被划分成若干个连续的 redo log block，可以通过启动参数innodb_log_buffer_size 来指定 log buffer 的大小，该启动参数的默认值为：16MB。向 log buffer 中写入 redo log 的过程是顺序的，也就是先往前边的 block 中写，当该 block 的空闲空间用完之后再往下一个 block 中写。redo log 刷盘时机log buffer 什么时候会写入到磁盘呢？log buffer 空间不足时，如果不停的往这个有限大小的 log buffer 里塞入日志，很快它就会被填满。InnoDB 认为如果当前写入 log buffer 的 redo log 量已经占满了 log buffer 总容量的大约一半左右，就需要把这些日志刷新到磁盘上。事务提交时，必须要把修改这些页面对应的 redo log 刷新到磁盘。后台有一个线程，大约每秒都会刷新一次 log buffer 中的 redo log 到磁盘。正常关闭服务器时等等。undo 日志事务需要保证原子性，也就是事务中的操作要么全部完成，要么什么也不做。但是偏偏有时候事务执行到一半会出现一些情况，比如：情况一：事务执行过程中可能遇到各种错误，比如服务器本身的错误，操作系统错误，甚至是突然断电导致的错误。情况二：程序员可以在事务执行过程中手动输入 ROLLBACK 语句结束当前的事务的执行。这两种情况都会导致事务执行到一半就结束，但是事务执行过程中可能已经修改了很多东西，为了保证事务的原子性，需要把东西改回原先的样子，这个过程就称之为回滚，即：rollback，这样就可以造成这个事务看起来什么都没做，所以符合原子性要求。每当要对一条记录做改动时，都需要把回滚时所需的东西都给记下来。比方说：插入一条记录时，至少要把这条记录的主键值记下来，之后回滚的时候只需要把这个主键值对应的记录删掉。删除了一条记录，至少要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中。修改了一条记录，至少要把修改这条记录前的旧值都记录下来，这样之后回滚时再把这条记录更新为旧值。这些为了回滚而记录的这些东西称之为撤销日志，即：undo log。这里需要注意的一点是，由于查询操作并不会修改任何用户记录，所以在查询操作执行时，并不需要记录相应的 undo log。undo 日志的格式为了实现事务的原子性，InnoDB 存储引擎在实际进行增、删、改一条记录时，都需要先把对应的 undo 日志记下来。一般每对一条记录做一次改动，就对应着一条 undo 日志，但在某些更新记录的操作中，也可能会对应着 2 条 undo 日志。一个事务在执行过程中可能新增、删除、更新若干条记录，也就是说需要记录很多条对应的 undo 日志，这些 undo 日志会被从 0 开始编号，也就是说根据生成的顺序分别被称为第 0 号 undo 日志、第 1 号 undo 日志、...、第 n 号 undo 日志等，这个编号也被称之为 undo no。这些 undo 日志是被记录到类型为 FIL_PAGE_UNDO_LOG 的页面中。这些页面可以从系统表空间中分配，也可以从一种专门存放 undo 日志的表空间，也就是所谓的 undo tablesp中分配。

浏览量1023

秋叶无缘

从ClickHouse通往MySQL的几条道路

一、应用背景简介ClickHouse 是 Yandex（俄罗斯最大的搜索引擎）开源的一个用于实时数据分析的基于列存储的数据库，其处理数据的速度比传统方法快 100-1000 倍。ClickHouse 的性能超过了目前市场上可比的面向列的 DBMS，每秒钟每台服务器每秒处理数亿至十亿多行和数十千兆字节的数据。它是一个用于联机分析（OLAP）的列式数据库管理系统；（OLAP是仓库型数据库，主要是读取数据，做复杂数据分析，侧重技术决策支持，提供直观简单的结果）那 ClickHouse OLAP 适用场景有：1）读多于写；2）大宽表，读大量行但是少量列，结果集较小；3）数据批量写入，且数据不更新或少更新；4）无需事务，数据一致性要求低；5）灵活多变，不适合预先建模。MySQL是一个关系型数据库管理系统，广泛用于各种应用程序和网站开发。MySQL容易上手和学习，已经被广泛应用于各种生产环境中有良好的稳定性和可靠性，MySQL支持事务处理，能够保证数据的完整性和一致性，适合需要复杂数据处理和事务控制的应用。在我们应用中的使用场景来看，简单来说通常会看中了clickhouse在处理大批量数据的写入和读取分析方面的性能，MySQL会主要负责一些基于模型进行指标二次加工的高频查询及复杂join的查询。二、实际应用中存在的问题在数据相关应用处理过程中，一般会按下图的分层进行数据处理；现有的一个实际应用场景中，会把MySQL中的数据进行全量的更替，即在新一批基于ck模型加工的数据插入MySQL库表时删除原表全量数据，来实现对于最新全量数据的刷新；该处理机制因为完全不用考虑历史数据的包袱，每批次都是全量加工和替换，是一种运行简单、有效、数据加工的准确率高的机制，在小业务量场景下可以节省开发量和时间，弊端是在大业务场景下性能端会出现与之关联的多种问题；这些告警可能包括因为读写频率高引起的CPU使用率过高、因为binlog产生量过大导致的磁盘使用量告警等；负责加工的后端应用也可能也会因数据加工量过大而引发内存使用率过高的风险。基于现有架构设计和问题背景，需要对相关的问题进行一定的调研，来探索优化的可能性；三、几种处理方法及适配的场景分析1.使用数据库触发器（Trigger）：在 ClickHouse 表中创建触发器，当订单数据发生变化时，触发器可以将更新操作发送到 MySQL 表中进行更新。触发器可以监视 ClickHouse 表中的 INSERT、UPDATE 和 DELETE 操作，并将相应的操作转发到 MySQL 表中。在类似于同步数据表的场景下，触发器场景比较合适，但是在面对需要高度定制化的数据加工场景下，就显得不太合适了，也不方便调试。2.此外，也会有通过外部触发器结合消息队列的方案可以支持处理这种情况。这里边会涉及到需要监听ClickHouse的binlog记录或者CDC（Change Data Capture）流，在数据发生变化时进行解析和转发。3.也可以在应用层面，来监听ClickHouse数据库的数据变化，并在变化发生时发送相关的消息到消息队列。例如使用Debezium库来监听ClickHouse数据库的数据变化。你可以根据自己的需求来配置连接信息、监听的表等，并在监听器中编写业务逻辑处理数据变化事件。这几种处理方式相对来说对于处理的变化量来说是比较大的，即所有对于数据库的操作过程都会被监听端响应处理，所以如果数据变化量非常大的话，那么监听消费端的压力也会随之上升；4.轮询查询：Java 应用可以定期轮询查询 ClickHouse 表的变化，通过比对新的订单数据和已有的订单数据，找出有变动的数据行，并进行相应的加工处理和更新操作。这其中的关键就是采用何种方法进行比对了。比对方法可以通过逐个字段的比对来筛选该行数据是否有变化，这种方法简单有效，但是瓶颈也比较明显：不适合处理大量数据，因为性能不算好；也可以通过把数据行进行哈希算法和摘要处理，来实现更快速的字段变化的比对，这种方式相对来说会更适合处理数据量大一些的场景；当然，处理过程并不限于查询过程，有些场景是在查询阶段并不需要筛选数据，而是基于原始模型加工完的数据结果上进行字段值比对或哈希处理，用来标记处理完的数据结果是否有变化，有变化的更新无则不处理，从而减小对指标结果数据的更新范围；以上内容是对于所与到问题的处理方法的一个浅显分析，如果您还有什么指标加工方面好的经验，欢迎指正和交流。

浏览量1021

菜鸟码转

1.数据库基础概念

一、什么是数据库数据库（Database）是保存有组织的数据的容器（通常是一个文件或一组文件），是通过数据库管理系统（DataBase- Management System，DBMS）创建和操纵的容器。DBMS 的主要目标是提供一种可以方便、高效地存取数据库信息的途径。我们常说 XX 数据库，其实实质上是 XX 数据库管理系统。目前，较为流行的数据库管理系统有 MySQL、SQL Server、Oracle 等。数据库有两种类型，分别是关系型数据库和非关系型数据库。注：SQL 是结构化查询语言（Structured Query Language）的缩写，是一种数据库查询语言，用于存取数据、查询、更新和管理关系数据库系统。与其他语言（如英语以及 Java 等程序设计语言）不一样，SQL 由少量的描述性很强的词构成，简单易学。二、为什么要使用数据库总而言之，面对庞大的网络数据量，使用数据库可以高效且条理分明地存储数据，它使人们能够更加迅速和方便地管理数据。三、SQL 与 MySQL 有什么区别SQL 和 MySQL 是 DBMS 中最令人困惑的两个术语，二者之间存在本质上的区别。SQL 是一种结构化查询语言，用于在数据库上执行各种操作，但 MySQL 是一个关系数据库管理系统（RDBMS），使用 SQL 执行所有数据库操作。SQL 用于访问，更新和操作数据库中的数据，用户使用时需要学习该语言，然后编写查询，而 MySQL 是一个软件，会为用户提供一个界面，只需单击一些按钮即可用于执行各种数据库操作。由于 MySQL 是一个软件，所以它会定期获得各种更新，但在 SQL 中，命令总是相同的。四、数据库三大范式是什么数据库范式是设计数据库时，需要遵循的一些规范。各种范式是条件递增的联系，越高的范式数据库冗余越小。常用的数据库三大范式为：第一范式（1NF）：每个列都不可以再拆分，强调的是列的原子性。第一范式要求数据库中的表都是二维表。第二范式（2NF）：在第一范式的基础上，一个表必须有一个主键，非主键列完全依赖于主键，而不能是依赖于主键的一部分。第三范式（3NF）：在第二范式的基础上，非主键列只依赖（直接依赖）于主键，不依赖于其他非主键。五、数据库连接泄露的含义数据库连接泄露指的是如果在某次使用或者某段程序中没有正确地关闭 Connection、Statement 和 ResultSet 资源，那么每次执行都会留下一些没有关闭的连接，这些连接失去了引用而不能得到重新使用，因此就造成了数据库连接的泄漏。数据库连接的资源是宝贵而且是有限的，如果在某段使用频率很高的代码中出现这种泄漏，那么数据库连接资源将被耗尽，影响系统的正常运转。六、什么是触发器触发器（trigger）是与表相关的数据库对象，是用户定义在关系表上的一类由事件驱动的特殊的存储过程，在满足定义条件时触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据库的完整性。使用场景可以通过数据库中的相关表实现级联更改；实时监控某张表中的某个字段的更改，并需要做出相应的处理。

浏览量2026

菜鸟码转

7.数据库优化

一、数据库结构优化在数据库设计中，需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。可通过以下方式进行数据库结构的优化：将字段很多的表分解成多个表对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。增加中间表对于需要经常联合查询的表，通过建立中间表以提高查询效率，具体地，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。增加冗余字段众所皆知，设计数据表时应尽量遵循范式理论的规约，尽可能的减少冗余字段，让数据库设计看起来精致、优雅。但是，表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差，所以合理的加入冗余字段可以提高查询速度。二、MySQL 数据库 CPU 使用率飙升到 500% 的话，如何处理？当 CPU 飙升到 500% 时，先用操作系统命令 top 命令观察是不是 mysqld 占用导致的，如果不是，找出占用高的进程，并进行相关处理。如果是 mysqld 造成的，通过 SHOW PROCESSLIST 查看正在运行的线程，是不是有消耗资源的 SQL 在运行，找出其中消耗高的 SQL，看看执行计划是否准确， index 是否缺失，或者是数据量太大造成。然后 kill 掉这些线程（同时观察 CPU 使用率是否下降），等进行相应的调整（比如说加索引、改 SQL、改内存参数）之后，再重新跑这些 SQL。若每个 SQL 消耗资源都不多，只是同一时间大量的 session 连进来导致 CPU 飙升，这种情况就需要分析为何连接数会激增，再做出相应的调整，比如说限制连接数等。三、为什么要分库分表数据库中的数据量不一定是可控的，随着时间和业务的发展，库中的表会越来越多，表中的数据量也会越来越大，相应地数据操作，例如增删改查的开销也会越来越大；另外，若不进行分布式部署，而一台服务器的资源（CPU、磁盘、内存、IO 等）是有限的，最终数据库所能承载的数据量、数据处理能力都将遭遇瓶颈。所以，从性能和可用性角度考虑，会进行数据库拆分处理，具体地说，把原本存储于一个库的数据分块存储到多个库上，把原本存储于一个表的数据分块存储到多个表上，即分库分表。四、分库分表的具体实施策略分库分表有垂直切分和水平切分两种方式，在复杂的业务场景中，也可能会选择两者结合的方式。五、分库分表存在哪些问题进行分库分表操作后，可能会面临以下几类问题：事务问题分库分表后，就成了分布式事务。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价；如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。跨库跨表的 JOIN 问题在执行了分库分表之后，难以避免会将原本逻辑关联性很强的数据划分到不同的表、不同的库上，这时，表的关联操作将受到限制，我们无法 JOIN 位于不同分库的表，也无法 JOIN 分表粒度不同的表，结果原本一次查询能够完成的业务，可能需要多次查询才能完成。额外的数据管理负担和数据运算压力额外的数据管理负担，最为常见的是数据的定位问题和数据的增删改查的重复执行问题，这些都可以通过应用程序来解决，但必然会引起额外的逻辑运算。六、什么是 MySQL 主从复制主从复制是指将主数据库（Master）中的 DDL 和 DML 操作通过二进制日志传输到从数据库（Slave）上，然后将这些日志重新执行（重做），从而使得从数据库的数据与主数据库保持一致。MySQL 支持单向、异步复制，复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主从复制的作用有：当主数据库出现问题时，可以切换到从数据库；可以进行数据库层面的读写分离，实现负载均衡；可以在从数据库上进行实时数据备份。七、MySQL 主从复制的工作原理MySQL 的主从复制是一个异步的复制过程（一般情况下感觉是实时的），数据将从一个 MySQL 数据库（Master）复制到另外一个 MySQL 数据库（Slave），在 Master 与 Slave 之间实现整个主从复制的过程是由三个线程参与完成的，其中有两个线程（SQL 线程和 I/O 线程）在 Slave 端，另外一个线程（ I/O 线程）在 Master 端。基本原理流程Master 端：打开二进制日志（binlog ）记录功能 —— 记录下所有改变了数据库数据的语句，放进 Master 的 binlog 中；Slave 端：开启一个 I/O 线程 —— 负责从 Master上拉取 binlog 内容，放进自己的中继日志（Relay log）中；Slave 端：SQL 执行线程 —— 读取 Relay log，并顺序执行该日志中的 SQL 事件。八、MySQL 读写分离的实现方案MySQL 读写分离的实现方式主要基于主从复制，通过路由的方式使应用对数据库的写请求只在 Master 上进行，读请求在 Slave 上进行。具体地，有以下四种实现方案：方案一：基于 MySQL proxy 代理在应用和数据库之间增加代理层，代理层接收应用对数据库的请求，根据不同请求类型（即是读 read 还是写 write）转发到不同的实例，在实现读写分离的同时可以实现负载均衡。MySQL 的代理最常见的是 mysql-proxy、cobar、mycat、Atlas 等。方案二：基于应用内路由基于应用内路由的方式即为在应用程序中实现，针对不同的请求类型去不同的实例执行 SQL。具体实现可基于 spring 的 aop：用 aop 来拦截 spring 项目的 dao 层方法，根据方法名称就可以判断要执行的类型，进而动态切换主从数据源。方案三：基于 MySQL-Connector-Java 的 JDBC 驱动方式Java 程序通过在连接 MySQL 的 JDBC 中配置主库与从库等地址，JDBC 会自动将读请求发送给从库，将写请求发送给主库，此外， MySQL 的 JDBC 驱动还能够实现多个从库的负载均衡。方案四：基于 sharding-jdbc 的方式sharding-sphere 是强大的读写分离、分表分库中间件，sharding-jdbc 是 sharding-sphere 的核心模块。

浏览量2022

菜鸟码转

4.锁

一、锁的分类从数据库系统的角度，锁模式可分为以下6 种类型：共享锁（S）：又叫他读锁。可以并发读取数据，但不能修改数据。也就是说当数据资源上存在共享锁时，所有的事务都不能对该数据进行修改，直到数据读取完成，共享锁释放。排它锁（X）：又叫独占锁、写锁。对数据资源进行增删改操作时，不允许其它事务操作这块资源，直到排它锁被释放，从而防止同时对同一资源进行多重操作。更新锁（U）：防止出现死锁的锁模式，两个事务对一个数据资源进行先读取再修改的情况下，使用共享锁和排它锁有时会出现死锁现象，而使用更新锁就可以避免死锁的出现。资源的更新锁一次只能分配给一个事务，如果需要对资源进行修改，更新锁会变成排它锁，否则变为共享锁。意向锁：表示 SQL Server 需要在层次结构中的某些底层资源上获取共享锁或排它锁。例如，放置在表级的共享意向锁表示事务打算在表中的页或行上放置共享锁。在表级设置意向锁可防止另一个事务随后在包含那一页的表上获取排它锁。意向锁可以提高性能，因为 SQL Server 仅在表级检查意向锁来确定事务是否可以安全地获取该表上的锁，而无须检查表中的每行或每页上的锁以确定事务是否可以锁定整个表。意向锁包括意向共享 (IS)、意向排它 (IX) 以及与意向排它共享 (SIX)。架构锁：在执行依赖于表架构的操作时使用。架构锁的类型为：架构修改 (Sch-M) 和架构稳定性 (Sch-S)，执行表的数据定义语言（DDL）操作（例如添加列或除去表）时使用架构修改锁，当编译查询时，使用架构稳定性锁。大容量更新锁（BU）：向表中大容量复制数据并指定了 TABLOCK 提示时使用。大容量更新锁允许进程将数据并发地大容量复制到同一表，同时防止其它不进行大容量复制数据的进程访问该表。二、事务隔离级别与锁的关系在读取未提交隔离级别下，读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突；在读取已提交隔离级别下，读操作需要加共享锁，但是在语句执行完以后释放共享锁；在可重复读隔离级别下，读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁；可串行化是限制性最强的隔离级别，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。三、什么是死锁？如何解决死锁？死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。常见的解决死锁的方法如果不同程序并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会；在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率。四、什么是乐观锁和悲观锁？如何实现？DBMS 中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。这对于长事务来讲，可能会严重影响系统的并发处理能力。实现方式：使用数据库中的锁机制。乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。乐观锁适用于读多写少的应用场景，这样可以提高吞吐量。实现方式：一般会使用版本号机制或 CAS 算法实现。

浏览量2018

菜鸟码转

6.Redis常见面试题

一、Redis缓存的特点作为一个高性能的 Key-Value 数据库，Redis 与其他 Key-Value 缓存产品相比，有以下三个特点：Redis 运行在内存中但是可以持久化到磁盘，重启的时候可以再次加载进行使用。Redis 不仅仅支持简单的 Key-Value 类型的数据，同时还提供 List，Set，Sorted set，hash 等数据结构的存储。Redis 还支持数据的备份，即 Master-Slave 主从模式的数据备份。二、Redis 和 MySQL 的区别首先，类型上的不同。MySQL 是关系型数据库，采用表格的存储方式，数据以行列组织：一个表中，每行是一个条目，都有且仅有此表的列所规定的属性。MySQL 作为关系型数据库遵守了传统的数据库的 ACID 原则 [①]，这些要求在记录重要数据（如用户账户密码，银行户头等）时是必须的。Redis 是 NoSQL（非关系型数据库），不严格遵守 ACID 原则，其采用 key-value 存储组织数据，大量使用缓存来读写数据，周期性地把更新数据写入磁盘或在追加文件中写入修改操作，以及同步分布数据库等。这些特点为大大提高了 Redis 在极大并发量下的读写性能。由于不以表格方式组织数据，Redis 的数据扩展十分灵活方便。功能不同。 MySQL 主要用于持久化的存储数据到硬盘，功能强大，但是读取速度较慢。而 Redis 将使用频繁的数据存储在缓存中，缓存的读取速度快，能够大大的提高运行效率，但是一般在使用中，Redis 缓存的数据保存时间是有限的。运行机制不同。MySQL 作为典型的关系型数据库，为了严格遵守 ACID 规则必须采取保守的缓存策略，特别是为了保证数据的一致性，在保证数据可靠性的同时，牺牲了许多（特别是多并发情形下的）读写性能。而现在互联网许多应用场景往往面临了海量用户的访问请求，对数据库并不需要非常强的一致性 [②]。如 Redis 等非关系型数据库（NoSQL），往往以不再满足 ACID 为代价求得更高的吞吐容量。NoSQL 通常采用极大的缓存、分布式服务来提高读写性能。① 原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability)。② 比如推特网站的用户在当次刷新推特时，容许在最新 10 秒内新发的推特不出现在这次刷新结果，但是不希望自己的应用因为等待刷新而陷入 5 秒以上的停顿。三、Redis 和 MongoDB 的区别MongoDB 和 Redis 都是 NoSQL，采用结构型数据存储，二者之间的主要区别如下所示：性能方面，二者都很高，总体而言，TPS 方面 Redis 要大于 MongoDB；可操作性上，MongoDB 支持丰富的数据表达、索引，最类似于关系数据库，支持丰富的查询语言，操作比 Redis 更为便利；内存及存储方面，MongoDB 适合大数据量存储，依赖操作系统虚拟做内存管理，采用镜像文件存储，内存占有率比较高，Redis 2.0 后增加虚拟内存特性，突破物理内存限制，数据可以设置时效性；对于数据持久化和数据恢复，MongoDB 1.8 后，采用 binlog 方式（同 MySQL）支持持久化，增加了可靠性，而 Redis 依赖快照进行持久化、AOF 增强可靠性，但是增强可靠性的同时，也会影响访问性能；在数据一致性上，MongoDB 不支持事务，靠客户端自身保证，而 Redis 支持事务，能保证事务中的操作按顺序执行；数据分析上，MongoDB 内置数据分析功能（mapreduce），而 Redis 不支持数据分析；应用场景不同，MongoDB 适合海量数据，侧重于访问效率的提升，而 Redis 适合于较小数据量，侧重于性能。四、Redis 有哪些数据类型Redis 的五大常用数据类型：String（字符串），是 Redis 最基本的数据类型，二进制安全的，可以包含任何数据，比如 JPG 图片或者序列化的对象，最大能存储 512 MB。Hash（哈希），是一个键值对（key => value）集合，特别适合用于存储对象。List（列表），Redis 列表是简单的字符串列表，按照插入顺序排序，可以添加一个元素到列表的头部（左边）或者尾部（右边）。Set（集合），是 String 类型的无序集合，通过哈希表实现，添删查找操作的复杂度都是 O(1)。Sorted set（有序集合），和 Set 一样也是 String 类型元素的集合，且不允许元素重复，不同的是每个元素都会关联一个 Double 类型的分数（可重复），通过此分数来为集合中的成员进行从小到大的排序。五、如何实现 Redis 的定时机制Redis 服务器是一个事件驱动程序，服务器需要处理以下两类事件：文件事件（服务器对套接字操作的抽象）和时间事件（服务器对定时操作的抽象）。Redis 的定时机制就是借助时间事件实现的。一个时间事件主要由以下三个属性组成：id，时间事件标识号；when，记录时间事件的到达时间；timeProc，时间事件处理器，当时间事件到达时，服务器就会调用相应的处理器来处理时间。一个时间事件根据时间事件处理器的返回值来判断是定时事件还是周期性事件。六、单线程 Redis，为什么如此高效虽然 Redis 文件事件处理器以单线程方式运行，但是通过使用 I/O 多路复用程序来监听多个套接字，文件事件处理器既实现了高性能的网络通信模型，又可以很好地与 Redis 服务器中其他同样以单线程运行的模块进行对接，这保持了 Redis 内部单线程设计的简单性。七、Redis 有哪些数据淘汰策略Redis 内存数据量达到一定限制的时候，就会实行数据淘汰策略（回收策略）。Redis 会根据 maxmemory-policy 配置策略，来决定具体的行为：no-eviction：不删除策略，达到最大内存限制时刻，如果需要更多内存，直接返回错误信息；allkey-lru：从所有 Key 的哈希表（server.db[i].dict）中随机挑选多个 Key，然后在选到的 Key 中利用 lru 算法淘汰最近最少使用的数据；volatile-lru：从已设置过期时间的哈希表（server.db[i].expires）中随机挑选多个 Key，然后在选到的 Key 中用 lru 算法淘汰最近最少使用的数据；volatile-random：从已设置过期时间的哈希表（server.db[i].expires）中随机挑选 Key淘汰掉；allkey-random：从所有的 Key 的哈希表（server.db[i].dict）中随机挑选数据淘汰；volatile-ttl：从已设置过期时间的哈希表（server.db[i].expires）中随机挑选多个 Key，然后在选到的 Key 中选择剩余时间最短的数据淘汰掉。八、对 Redis 的理解Redis，全称为 Remote Dictionary Server，本质上是一个 Key-Value 类型的内存数据库，整个数据库统统加载在内存当中进行操作，定期通过异步操作把数据库数据写入磁盘或把修改操作写入追加的记录文件，并且在此基础上实现 Master-Slave（主从）同步。它支持存储的 Value 类型多样，包括 String（字符串）、List（链表）、Set（集合）、zset（sorted set —— 有序集合）和 Hash（哈希类型），这些数据类型都支持 push/pop、add/remove 及取交集并集和差集及更丰富的操作，而且这些操作都是原子性的。Redis 的主要缺点是数据库容量受到物理内存的限制，不能用作海量数据的高性能读写，因此 Redis 适合的场景主要局限在较小数据量的高性能操作和运算上。

浏览量2013

菜鸟码转

3.事务管理

一、什么是数据库事务数据库的事务（Transaction）是一种机制、一个操作序列，包含了一组数据库操作命令，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行，因此事务是一个不可分割的工作逻辑单元。如果任意一个操作失败，那么整组操作即为失败，会回到操作前状态或者是上一个节点。因此，事务是保持逻辑数据一致性和可恢复性的重要利器。而锁是实现事务的关键，可以保证事务的完整性和并发性二、有哪些事务状态事务在其整个生命周期中会经历不同的状态，这些状态也称为事务状态。活跃状态：事务的第一个状态，任何正在执行的事务都处于此状态，所做的更改存储在主内存的缓冲区中。部分提交状态：执行上次操作后，事务进入部分提交状态。之所以是部分提交，是因为所做的更改仍然在主内存的缓冲区中。失败状态：如果某个检查在活动状态下失败，在活动状态或部分提交状态发生一些错误，并且事务无法进一步执行，则事务进入失败状态。中止状态：如果任何事务已达到失败状态，则恢复管理器将数据库回滚到开始执行的原始状态。提交状态：如果所有操作成功执行，则来自部分提交状态的事务进入提交状态。无法从此状态回滚，它是一个新的一致状态。三、事务的四大特性事务具有 4 个特性，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability），这 4 个特性通常简称为 ACID，关系型数据库需要遵循 ACID 规则。原子性事务是最小的执行单位，不可分割的（原子的）。事务的原子性确保动作要么全部执行，要么全部不执行。以银行转账事务为例，如果该事务提交了，则这两个账户的数据将会更新；如果由于某种原因，事务在成功更新这两个账户之前终止了，则不会更新这两个账户的余额，并且会撤销对任何账户余额的修改，回到此操作前状态，即事务不能部分提交。一致性当事务完成时，数据必须处于一致状态，多个事务对同一个数据读取的结果是相同的。以银行转账事务事务为例。在事务开始之前，所有账户余额的总额处于一致状态。在事务进行的过程中，一个账户余额减少了，而另一个账户余额尚未修改。因此，所有账户余额的总额处于不一致状态。但是当事务完成以后，账户余额的总额再次恢复到一致状态。隔离性并发访问数据库时，一个用户的事务不被其他事务所干扰，各个事务不干涉内部的数据。修改数据的事务可以在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据。持久性一个事务被提交之后，它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。四、如何实现事务的 ACID 特性事务的 ACID 特性是由关系数据库管理系统来实现的。DBMS 采用日志来保证事务的原子性、一致性和持久性。日志记录了事务对数据库所做的更新，如果某个事务在执行过程中发生错误，就可以根据日志，撤销事务对数据库已做的更新，使数据库退回到执行事务前的初始状态。DBMS 采用锁机制来实现事务的隔离性。当多个事务同时更新数据库中相同的数据时，只允许持有锁的事务能更新该数据，其他事务必须等待，直到前一个事务释放了锁，其他事务才有机会更新该数据。五、事务之间的相互影响脏读（Dirty Read）一个事务读取了另一个事务未提交的数据。不可重复读（Non-repeatable Read）就是在一个事务范围内，两次相同的查询会返回两个不同的数据，这是因为在此间隔内有其他事务对数据进行了修改。幻读（Phantom Read）幻读是指当事务不是独立执行时发生的一种现象，例如有一个事务对表中的数据进行了修改，这种修改涉及到表中的全部数据行，同时，第一个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，第一个事务的用户发现表中还有没有修改的数据行，就好像发生了幻觉一样。丢失更新（Lost Update）两个事务同时读取同一条记录，事务 A 先修改记录，事务 B 也修改记录（B 是不知道 A 修改过），当 B 提交数据后，其修改结果覆盖了 A 的修改结果，导致事务 A 更新丢失。六、什么是事务的隔离级别为了尽可能的避免上述事务之间的相互影响，从而达到事务的四大特性，SQL 标准定义了 4 种不同的事务隔离级别（TRANSACTION ISOLATION LEVEL），即并发事务对同一资源的读取深度层次，由低到高依次是读取未提交（READ-UNCOMMITTED）、读取已提交（READ-COMMITTED）、可重复读（REPEATABLE-READ）、可串行化（SERIALIZABLE），这 4 个级别与事务相互间影响问题对应如下：读取未提交最低的隔离级别，一个事务可以读到另一个事务未提交的结果，所有的并发事务问题都会发生。读取已提交只有在事务提交后，其更新结果才会被其他事务看见，可以解决脏读问题，但是不可重复读或幻读仍有可能发生。Oracle 默认采用的是该隔离级别。可重复读在一个事务中，对于同一份数据的读取结果总是相同的，无论是否有其他事务对这份数据进行操作，以及这个事务是否提交，除非数据是被本身事务自己所修改。可以解决脏读、不可重复读。MySQL 默认采用可重复读隔离级别。可串行化事务串行化执行，隔离级别最高，完全服从 ACID，牺牲了系统的并发性，也就是说，所有事务依次逐个执行，所以可以解决并发事务的所有问题。

浏览量2020

菜鸟码转

2.索引

一、索引的优缺点索引的优点通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；可以加快数据的检索速度，这也是创建索引的主要原因；可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义；通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统性能。索引的缺点时间上，创建和维护索引都要耗费时间，这种时间随着数据量的增加而增加，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度；空间上，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。二、索引的数据结构数据库索引根据结构分类，主要有 B 树索引、Hash 索引和位图索引三种。B 树索引B 树索引，又称平衡树索引，是 MySQL 数据库中使用最频繁的索引类型，MySQL、Oracle 和 SQL Server 数据库默认的都是 B 树索引（实际是用 B+ 树实现的，因为在查看表索引时，MySQL 一律打印 BTREE，所以简称为 B 树索引）。B 树索引以树结构组织，它有一个或者多个分支结点，分支结点又指向单级的叶结点。其中，分支结点用于遍历树，叶结点则保存真正的值和位置信息。B+ 树是在 B 树基础上的一种优化，使其更适合实现外存储索引结构。一棵 m 阶 B-Tree 的特性如下：每个结点最多 m 个子结点；除了根结点和叶子结点外，每个结点最少有 m/2（向上取整）个子结点；所有的叶子结点都位于同一层；每个结点都包含 k 个元素（关键字），这里 m/2≤k<m，这里 m/2 向下取整；每个节点中的元素（关键字）从小到大排列；每个元素子左结点的值，都小于或等于该元素，右结点的值都大于或等于该元素。数据库以 B-Tree 的数据结构存储数据的图示如下：B+ Tree 与 B-Tree 的结构很像，但是也有自己的特性：所有的非叶子结点只存储关键字信息；所有具体数据都存在叶子结点中；所有的叶子结点中包含了全部元素的信息；所有叶子节点之间都有一个链指针。数据库以 B+ Tree 的数据结构存储数据的图示如下：Hash 索引哈希索引采用一定的哈希算法（常见哈希算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法），将数据库字段数据转换成定长的 Hash 值，与这条数据的行指针一并存入 Hash 表的对应位置，如果发生 Hash 碰撞（两个不同关键字的 Hash 值相同），则在对应 Hash 键下以链表形式存储。检索时不需要类似 B+ 树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快，平均检索时间为 O(1)。位图索引B 树索引擅长于处理包含许多不同值的列，但是在处理基数较小的列时会变得很难使用。如果用户查询的列的基数非常的小，即只有几个固定值，如性别、婚姻状况、行政区等等，要么不使用索引，查询时一行行扫描所有记录，要么考虑建立位图索引。位图索引为存储在某列中的每个值生成一个位图。例如针对表中婚姻状况这一列，生成的位图索引大致如下所示：对于婚姻状况这一列，索引包含 3 个位图，即生成有 3 个向量，分别属于每一个取值，每个位图为每一个人（行）都分配了 0/1 值（每一行有且仅有一个 1 ），未婚为 110000……，已婚为 000111……，离婚为 001000……。当进行数据查找时，只要查找相关位图中的所有 1 值即可（可根据查询需求进行与、或运算）。例如， Oracle 用户可以通过为 create index 语句简单地添加关键词 bitmap 生成位图：CREATE BITMAP INDEX acc_marital_idx ON account (marital_cd);除了上述提及的，位图索引适合只有几个固定值的列，还需注意，位图索引适合静态数据，而不适合索引频繁更新的列。三、使用 B+ 树的好处由于 B+ 树的内部结点只存放键，不存放值，因此，一次读取，可以在同一内存页中获取更多的键，有利于更快地缩小查找范围。B+ 树的叶结点由一条链相连，因此当需要进行一次全数据遍历的时候，B+ 树只需要使用 O(logN) 时间找到最小结点，然后通过链进行 O(N) 的顺序遍历即可；或者，在找大于某个关键字或者小于某个关键字的数据的时候，B+ 树只需要找到该关键字然后沿着链表遍历即可。四、Hash 索引和 B+ 树索引的区别Hash 索引和 B+ 树索引有以下几点显见的区别：Hash 索引进行等值查询更快（一般情况下），但是却无法进行范围查询；Hash 索引不支持使用索引进行排序；Hash 索引不支持模糊查询以及多列索引的最左前缀匹配，原理也是因为 Hash 函数的不可预测；Hash 索引任何时候都避免不了回表查询数据，而 B+ 树在符合某些条件（聚簇索引，覆盖索引等）的时候可以只通过索引完成查询；Hash 索引虽然在等值查询上较快，但是不稳定，性能不可预测，当某个键值存在大量重复的时候，发生 Hash 碰撞，此时效率可能极差；而 B+ 树的查询效率比较稳定，对于所有的查询都是从根结点到叶子结点，且树的高度较低。五、什么是前缀索引有时需要索引很长的字符列，它会使索引变大并且变慢，一个策略就是索引开始的几个字符，而不是全部值，即被称为前缀索引，以节约空间并得到好的性能。使用前缀索引的前提是此前缀的标识度高，比如密码就适合建立前缀索引，因为密码几乎各不相同。前缀索引需要的空间变小，但也会降低选择性。索引选择性（INDEX SELECTIVITY）是不重复的索引值（也叫基数）和表中所有行数（T）的比值，数值范围为 1/T ～1。高选择性的索引有好处，因为在查找匹配的时候可以过滤掉更多的行，唯一索引的选择率为 1，为最佳值。对于前缀索引而言，前缀越长往往会得到好的选择性，但是短的前缀会节约空间，所以实操的难度在于前缀截取长度的抉择，可以通过调试查看不同前缀长度的平均匹配度，来选择截取长度。六、什么是最左前缀匹配原则在 MySQL 建立联合索引（多列索引）时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。例如有一个 3 列索引（a,b,c），则已经对（a）、（a,b）、（a,b,c）上建立了索引。所以在创建多列索引时，要根据业务需求，where 子句中使用最频繁的一列放在最左边。根据最左前缀匹配原则，MySQL 会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配，比如采用查询条件 where a = 1 and b = 2 and c > 3 and d = 4 时，如果建立（a,b,c,d）顺序的索引，d 是用不到索引的，如果建立（a,b,d,c）的索引则都可以用到，并且 where 子句中 a、b、d 的顺序可以任意调整。如果建立的索引顺序是（a,b），那么根据最左前缀匹配原则，直接采用查询条件 where b = 1 是无法利用到索引的。七、添加索引的原则索引虽好，但也不是无限制使用的，以下为添加索引时需要遵循的几项建议性原则：在查询中很少使用或者参考的列不要创建索引。由于这些列很少使用到，增加索引反而会降低系统的维护速度和增大空间需求。只有很少数据值的列也不应该增加索引。由于这些列的取值很少，区分度太低，例如人事表中的性别，在查询时，需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。定义为 text、image 和 bit 数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。当修改性能远远大于检索性能时，不应该创建索引。这时因为，二者是相互矛盾的，当增加索引时，会提高检索性能，但是会降低修改性能。定义有外键的数据列一定要创建索引。八、什么是聚簇索引聚簇索引，又称聚集索引，首先并不是一种索引类型，而是一种数据存储方式。具体的，聚簇索引指将数据存储和索引放到一起，找到索引也就找到了数据。MySQL 里只有 INNODB 表支持聚簇索引，INNODB 表数据本身就是聚簇索引，非叶子节点按照主键顺序存放，叶子节点存放主键以及对应的行记录。所以对 INNODB 表进行全表顺序扫描会非常快。特点因为索引和数据存放在一起，所以具有更高的检索效率；相比于非聚簇索引，聚簇索引可以减少磁盘的 IO 次数；表的物理存储依据聚簇索引的结构，所以一个数据表只能有一个聚簇索引，但可以拥有多个非聚簇索引；一般而言，会在频繁使用、排序的字段上创建聚簇索引。非聚簇索引除了聚簇索引以外的其他索引，均称之为非聚簇索引。非聚簇索引也是 B 树结构，与聚簇索引的存储结构不同之处在于，非聚簇索引中不存储真正的数据行，只包含一个指向数据行的指针。就简单的 SQL 查询来看，分为 SELECT 和 WHERE 两个部分，索引的创建也是以此为根据的，分为复合索引和覆盖索引。

浏览量2021

菜鸟码转

5.常用SQL语句

一、SQL语句主要是分为哪些类别SQL 语句可分为以下几类：数据定义语言 DDL（Data Definition Language）：例如 CREATE，DROP，ALTER 等，对逻辑结构等有操作的，其中包括表结构，视图和索引。数据查询语言 DQL（Data Query Language）：即查询操作，以 SELECT 关键字为主，各种简单查询、连接查询等都属于 DQL。数据操纵语言 DML（Data Manipulation Language）：例如 INSERT，UPDATE，DELETE 等，对数据进行操作的。DQL 与 DML共同构建了多数初级程序员常用的增删改查操作，而查询是较为特殊的一种，被划分到 DQL 中。数据控制语言 DCL（Data Control Language）：例如 GRANT，REVOKE，COMMIT，ROLLBACK 等，对数据库安全性、完整性等有操作的，可以简单的理解为权限控制等。二、什么是超键？什么是主键？超键：在关系中，能唯一标识元组的属性集称为关系模式的超键。一个属性可以作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。候选键：是最小超键，即没有冗余元素的超键。主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（NULL）。外键：在一个表中存在的另一个表的主键称此表的外键，外键可以有重复的, 可以是空值。外键是用来和其他表建立联系用的。三、QL 约束有哪几种类型四约束是一种简单地强加于表中一列或多列的限制，从而保证表中数据一致性（准确和可靠）。以下为六大约束：非空约束（NOT NULL）：保证该字段值一定不为空；默认约束（DEFAULT）：保证字段有默认值；主键约束（PRIMARY KEY）：标志一列或者多列，并保证其值在表内的唯一性；外键约束（FOREIGN KEY）：限制一列或多列中的值必须被包含在另一表的外键列中，并且在级联更新或级联删除规则建立后也可以限制其他表中的可用值；唯一约束（UNIQUE）：限制一列或多列的值，保证字段值在表内的唯一性，可以为空（主键约束是一种特殊类型的唯一约束）；检查约束（CHECK）：限制一列的可用值范围。四、char 与 varchar 的区别char 表示定长字符串，长度是固定的，最多能存放的字符个数为 255，和编码无关；而 varchar 表示可变长字符串，长度是可变的，最多能存放的字符个数为 65532；使用 char 时，如果插入数据的长度小于 char 的固定长度时，则用空格填充；因为固定长度，char 的存取速度比 varchar 快很多，同时缺点是会占用多余空间，属于空间换时间；五、有哪些关联查询在项目开发过程中，使用数据库查询语句时，有很多需求都是要涉及到较为复杂或者多表的连接查询，需要关联查询实现。以下为总结的 MySQL 的五种关联查询。交叉连接（CROSS JOIN）除了在 FROM 子句中使用逗号间隔连接的表外，SQL 还支持另一种被称为交叉连接的操作，它们都返回被连接的两个表所有数据行的笛卡尔积，返回到的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。惟一的不同在于，交叉连接分开列名时，使用 CROSS JOIN 关键字而不是逗号，即以下两个表达式等价：SELECT * FROM A, B SELECT * FROM A CROSS JOIN B内连接（INNER JOIN）内连接分为三类，分别是等值连接：ON A.id = B.id、不等值连接：ON A.id > B.id 和自连接：SELECT * FROM A T1 INNER JOIN A T2 ON T1.id = T2.pid。外连接（LEFT JOIN/RIGHT JOIN）左外连接：以左表为主，先查询出左表，按照 ON 后的关联条件匹配右表，没有匹配到的用 NULL 填充，可以简写成 LEFT JOIN；右外连接：以右表为主，先查询出右表，按照 ON 后的关联条件匹配左表，没有匹配到的用 NULL 填充，可以简写成 RIGHT JOIN；联合查询（UNION 与 UNION ALL）SELECT * FROM A UNION SELECT * FROM B UNION ...联合查询就是把多个结果集集中在一起，UNION 前的结果为基准，需要注意的是联合查询的列数要相等，相同的记录行会合并；如果使用 UNION ALL，不会合并重复的记录行，所以效率更高。全连接（FULL JOIN）MySQL 本身不支持全连接，但可以通过联合使用 LEFT JOIN、UNION 和 RIGHT JOIN 来实现。SELECT * FROM A LEFT JOIN B ON A.id = B.id UNIONSELECT * FROM A RIGHT JOIN B ON A.id = B.id六、什么是子查询多条 MySQL 语句嵌套使用时，内部的 MySQL 查询语句称为子查询。子查询是一个 SELECT 语句，它嵌套在另一个 SELECT、SELECT…INTO 语句、INSERT…INTO 语句、DELETE 语句、 UPDATE 语句或嵌套在另一子查询中。MySQL 的子查询是多表查询的一个重要组成部分，常常和连接查询一起使用，是多表查询的基础。七、子查询的分类子查询分为以下四类：标量子查询查询返回单一值的标量，如一个数字或一个字符串，是子查询中最简单的形式。列子查询子查询返回的结果集是 N 行一列，该结果通常来自对表的某个字段查询返回。行子查询子查询返回的结果集是一行 N 列，该结果通常是对表的某行数据进行查询而返回的结果集表子查询子查询返回的结果集是 N 行 N 列的一个表数据。八、DROP、DELETE 与 TRUNCATE 的区别三种都可以表示删除，其中的细微区别之处如下：因此，在不再需要一张表的时候，采用 DROP；在想删除部分数据行时候，用 DELETE；在保留表而删除所有数据的时候用 TRUNCATE。九、UNION 与 UNION ALL 的区别UNION 用于把来自多个 SELECT 语句的结果组合到一个结果集合中，MySQL 会把结果集中重复的记录删掉，而使用 UNION ALL，MySQL 会把所有的记录返回，且效率高于 UNION 。

浏览量2009