main_Scanner01的博客

第19章_数据库备份与恢复在任何数据库环境中，总会有不确定的意外情况发生，比如例外的停电、计算机系统中的各种软硬件故障、人为破坏、管理员误操作等是不可避免的，这些情况可能会导致数据的丢失、服务器瘫痪等严重的后果。存在多个服务器时，会出现主从服务器之间的数据同步问题。为了有效防止数据丢失，并将损失降到最低，应定期对MySQL数据库服务器做备份。如果数据库中的数据丢失或者出现错误，可以使用备份的数据进行恢复。主从服务器之间的数据同步问题可以通过复制功能实现.物理备份与逻辑备份**物理备份：**备份数据文

第17章_其它数据库日志我们在讲解数据库事务时，讲过两种日志：重做曰志、回滚日志对于线上数据库应用系统，突然遭遇数据库宕机怎么办？在这种情况下，定位宕机的原因就非常关键。我们可以查看数据库的错误日志。因为日志中记录了数据库运行中的诊断信息，包括了错误、警告和注释等信息。比如：从日志中发现某个连接中的SQL操作发生了死循环，导致内存不足，被系统强行终止了。明确了原因，处理起来也就轻松了，系统很快就恢复了运行。除了发现错误，日志在数据复制、数据恢复、操作审计，以及确保数据的永久性和一致性等方面，都有着不可

第16章_多版本并发控制什么是MVCCMVCC （Multiversion Concurrency Control），多版本并发控制。顾名思义，MVCC 是通过数据行的多个版本管理来实现数据库的并发控制。这项技术使得在InnoDB的事务隔离级别下执行一致性读（快照读）操作有了保证。换言之，就是为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值，这样在做查询的时候就不用等待另一个事务释放锁。 （个人感觉有点像CopyOnWrite写时复制技术）MVCC没有正式的标准，在不同的D

第15章_锁事务的隔离性由这章讲述的锁来实现。概述锁是计算机协调多个进程或线程并发访问某一资源的机制。在程序开发中会存在多线程同步的问题，当多个线程并发访问某个数据的时候，尤其是针对一些敏感的数据（比如订单、金额等)，我们就需要保证这个数据在任何时刻最多只有一个线程在访问，保证数据的完整性和一致性。在开发过程中加锁是为了保证数据的一致性，这个思想在数据库领域中同样很重要。在数据库中，除传统的计算资源〈如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。为保证数据的一致性，需要对

第14章_MySQL事务日志事务有4种特性：原子性、一致性、隔离性和持久性。那么事务的四种特性到底是基于什么机制实现呢?事务的隔离性由锁机制实现。而事务的原子性、一致性和持久性由事务的 redo 日志和undo 日志来保证。REDO LOG 称为重做日志，提供再写入操作，恢复提交事务修改的页操作，用来保证事务的持久性。UNDO LOG 称为回滚日志，回滚行记录到某个特定版本，用来保证事务的原子性、一致性。有的DBA或许会认为 UNDO 是 REDO 的逆过程，其实不然。REDO和UND

数据库设计规范小节为什么需要数据库设计**我们在设计数据表的时候，要考虑很多问题。**比如：用户都需要什么数据?需要在数据表中保存哪些数据?如何保证数据表中数据的正确性，当插入、删除、更新的时候该进行怎样的约束检查?如何降低数据表的数据冗余度，保证数据表不会因为用户量的增长而迅速扩张?如何让负责数据库维护的人员更方便地使用数据库?使用数据库的应用场景也各木相同，可以说针对不同的情况，设计出来的数据表可能千差万别。现实情况中，面临的场景:当数据库运行了一段时间之后，我们才发现数据表设计的

关联查询的优化(都可以自己去实验一下)数据准备#分类CREATE TABLE IF NOT EXISTS `type` (`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,`card` INT(10) UNSIGNED NOT NULL,PRIMARY KEY (`id`));#图书CREATE TABLE IF NOT EXISTS `book` (`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMEN

JOIN语句原理join方式连接多个表，本质就是各个表之间数据的循环匹配。MySQL5.5版本之前，MySQL只支持一种表间关联方式，就是嵌套循环(Nested Loop Join)。如果关联表的数据量很大，则join关联的执行时间会非常长。在MySQL5.5以后的版本中，MySQL通过引入BNLJ算法来优化嵌套执行。驱动表和被驱动表驱动表就是主表，被驱动表就是从表、非驱动表SELECT * FROM A JOIN B ON ...A一定是驱动表吗？不一定，优化器会根据你查询语句做优化，决定先查

都有哪些维度可以进行数据库调优？简言之:索引失效、没有充分利用到索引——索引建立关联查询太多JOIN(设计缺陷或不得已的需求)——SQL优化服务器调优及各个参数设置(缓冲、线程数等)——调整my.cnf。数据过多——分库分表关于数据库调优的知识点非常分散。不同的DBMS，不同的公司，不同的职位，不同的项目遇到的问题都不尽相同。这里我们分为三个章节进行细致讲解。虽然SQL查询优化的技术有很多，但是大方向上完全可以分成物理查询优化和逻辑查询优化两大块。物理查询优化是通过索引和表连接方式等技术

EXPLAIN四种输出格式这里谈谈EXPLAIN的输出格式。EXPLAIN可以输出四种格式：传统格式，JSON格式，TREE格式以及可视化输出。用户可以根据需要选择适用于自己的格式。EXPLAIN四种输出格式传统格式传统格式简单明了，输出是一个表格形式，概要说明查询计划。EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHEREs2.common_field IS NOT NULL;JSON格

性能分析工具的使用在数据库调优中，我们的目标就是响应时间更快，吞吐量更大。利用宏观的监控工具和微观的日志分析可以帮我们快速找到调优的思路和方式。数据库服务器的优化步骤当我们遇到数据库调优问题的时候，该如何思考呢？这里把思考的流程整理成下面这张图。整个流程划分成了观察（Show status） 和 行动（Action） 两个部分。字母 S 的部分代表观察（会使用相应的分析工具），字母 A 代表的部分是行动（对应分析可以采取的行动）。如果A2和A3都不能解决问题，我们需要考虑数据库自身的SQL查询

不相关子查询和相关子查询子查询不相关子查询：子查询的查询条件不依赖于父查询，称为不相关子查询。例：查询与"刘晨"在同一个系学习的学生。SELECT Sno,Sname,SdeptFROM StudentwHERE Sdept IN(SELECT Sdept FROM StudentWhERE Sname='刘晨');相关子查询：如果子查询的查询条件依赖于父查询，这类子查询称为相关子查询。例：找出每个学生超过他自己选修课程的平均成绩的课程号。SELECT Sno,CnoFR

索引的设计原则为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。**索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍。**高效的索引对于获得良好的性能非常重要。设计索引时，应该考虑相应准则。数据准备第一步：创建数据库、创建表CREATE DATABASE atguigudb1;USE atguigudb1;#1.创建学生表和课程表CREATE TABLE `student_info` (`id` INT(11) NOT NULL AUTO

MySQL8.0索引新特性支持降序索引降序索引以降序存储键值。虽然在语法上，从MySQL 4版本开始就已经支持降序索引的语法了，但实际上该DESC定义是被忽略的，直到MySQL 8.x版本才开始真正支持降序索引(仅限于InnoDB存储引擎)。MySQL在**8.0版本之前创建的仍然是升序索引，使用时进行反向扫描，这大大降低了数据库的效率。**在某些场景下，降序索引意义重大。例如，如果一个查询，需要对多个列进行排序，且顺序要求不一致，那么使用降序索引将会避免数据库使用额外的文件排序操作，从而提高性能。

索引的分类与使用索引的分类MySQL的索引包括普通索引、唯一索引、全文索引、单列索引、多列索引和空间索引等。从功能逻辑上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引。按照 物理实现方式，索引可以分为 2 种：聚簇索引和非聚簇索引。按照 作用字段个数进行划分，分成单列索引和联合索引。普通索引在创建普通索引时，不附加任何限制条件，只是用于提高查询效率。这类索引可以创建在任何数据类型中，其值是否唯一和非空，要由字段本身的完整性约束条件决定。建立索引以后，可以通过索引进

MySQL数据结构选择的合理性从MySQL的角度讲，不得不考虑一个现实的问题的就是磁盘IO。如果我们能够让索引的数据结构尽量减少磁盘I/O操作，所消耗的时间也就越小。可以说，磁盘的I/O操作次数对索引的使用效率至关重要。查找都是索引操作，一般来说索引非常大，尤其是关系型数据库，当数据量比较大时，索引的大小可能几个G甚至更多，为了减少索引在内存的占用，**数据库索引是存储在外部磁盘上的。**我们利用索引查询的时候，不可能把整个索引加载到内存，只能逐一加载，那么MySQL衡量查询效率的标准就是磁盘的I/O次

MySQL索引的数据结构为什么使用索引索引是存储引擎用于快速找到数据记录的一种数据结构，就好比一本教课书的目录部分，通过目录中找到对应文章的页码，便可快速定位到需要的文章。MysQL中也是一样的道理，进行数据查找时，首先查看查询条件是否命中某条索引，符合则通过索引查找相关数据，如果不符合则需要全表扫描，即需要一条一条地查找记录，直到找到与条件符合的记录。如上图所示，数据库没有索引的情况下，数据分布在硬盘不同的位置上面，读取数据时，摆臂需要前后摆动查找数据，这样操作非常消耗时间。如果数据顺序摆放，那么

MySQL存储引擎为了管理方便，人们把连接管理、查询缓存、语法解析、查询优化这些并不涉及真实数据存储的功能划分为MySQL server的功能，把真实存取数据的功能划分为存储引擎的功能。所以在MySQL server完成了查询优化后，只需按照生成的执行计划调用底层存储引擎提供的API，获取到数据后返回给客户端就好了。MySQL中提到了存储引擎的概念。简而言之，存储引擎就是指表的类型。其实存储引擎以前叫做表处理器，后来改名为存储引擎，它的功能就是接收上层传下来的指令，然后对表中的数据进行提取或写入操作。

SQL执行流程MySQL的配置文件的位置/etc/my.cnfMySQL中的SQL执行流程MySQL的查询流程查询缓存：Server如果在查询缓存中发现了这条SQL语句，就会直接将结果返回给客户端；如果没有，如果进入到解析器阶段。需要说明的是，因为查询混窜往往效率不高，所以在MySQL8.0之后就抛弃了这个功能MySQL拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以key-value对的形式，被直接缓存在内存中。key是查询的语句，va

存储过程与存储函数MysQL从5.0版本开始支持存储过程和函数。存储过程和函数能够将复杂的SQL逻辑封装在一起，应用程序无须关注存储过程和函数内部复杂的SQL逻辑，而只需要简单地调用存储过程和函数即可。存储过程概述理解含义:存储过程的英文是Stored Procedure。它的思想很简单，就是一组经过预先编译的SQL语句的封装。执行过程：存储过程预先存储在MySQL服务器上，需要执行的时候，客户端只需要向服务器端发出调用存储过程的命令，服务器端就可以把预先存储好的这一系列SQL语句全部执行。好处:

视图为什么使用视图**视图一方面可以帮我们使用表的一部分而不是所有的表，另一方面也可以针对不同的用户制定不同的查询视图。**比如，针对一个公司的销售人员，我们只想给他看部分数据，而某些特殊的数据，比如采购的价格，则不会提供给他。再比如，人员薪酬是个敏感的字段，那么只给某个级别以上的人员开放，其他人的查询视图中则不提供这个字段。刚才讲的只是视图的一个使用场景，实际上视图还有很多作用。最后，我们总结视图的优点。基本概念视图(VIEW)：一个或者多个数据表里的数据的逻辑显示，视图并不存储数据（一般用来

事务隔离级别(图文详解)什么是事务?事务是逻辑上的一组操作，要么都执行，要么都不执行。事务最经典也经常被拿出来说例子就是转账了。假如小明要给小红转账1000元，这个转账会涉及到两个关键操作就是：将小明的余额减少1000元，将小红的余额增加1000元。万一在这两个操作之间突然出现错误比如银行系统崩溃，导致小明余额减少而小红的余额没有增加，这样就不对了。事务就是保证这两个关键操作要么都成功，要么都要失败。常用的事务控制语法- 事务开始begin;- 事务提交，提交后就会写入物理磁盘中去commi