北大青鸟设计培训：mysql数据库的优化方法

我们都知道，服务器数据库的开发一般都是通过java或者是PHP语言来编程实现的，而为了提高我们数据库的运行速度和效率，数据库优化也成为了我们每日的工作重点，今天，昌平IT培训>

如何跳过校验MySQL57下有方法可以跳过崩溃恢复时的表空间校验过程嘛？查阅了资料，方法主要有两种：

1配置innodb_force_recovery可以使srv_force_recovery!=0，那么validate=false，即可以跳过表空间校验。实际测试的时候设置innodb_force_recovery=1，也就是强制恢复跳过坏页，就可以跳过校验，然后重启就是正常启动了。通过这种临时方式可以避免崩溃恢复后非常耗时的表空间校验过程，快速启动MySQL，个人目前暂时未发现有什么隐患。2使用共享表空间替代独立表空间这样就不需要打开N个ibd文件了，只需要打开一个ibdata文件即可，大大节省了校验时间。自从听了姜老师讲过使用共享表空间替代独立表空间解决drop大表时性能抖动的原理后，感觉共享表空间在很多业务环境下，反而更有优势。

临时冒出另外一种解决想法，即用GDB调试崩溃恢复，通过临时修改validate变量值让MySQL跳过表空间验证过程，然后让MySQL正常关闭，重新启动就可以正常启动了。但是实际测试发现，如果以debug模式运行，确实可以临时修改validate变量，跳过表空间验证过程，但是debug模式下代码运行效率大打折扣，反而耗时更长。而以非debug模式运行，则无法修改validate变量，想法破灭。

SOLyog的下载、安装以及使用很简单。我去了相关网站下载，它只有384K字节大小。它把两个文件(一个可执行文件exe和一个动态链接库文件dll)安装到C:\Program Files\SQLyog路径下。然后运行可执行文件。安装后没有必要再访问该网站了，我访问该网站是得到了一个消息，说它的域名没有设置(configured)、登记、或正在建设中。我不清楚这个问题是暂时的还是一直是这样。该软件是免费的，并且没有标志广告(banner ads)，所以它可能是一个特定的尚未最终定型的商业模型。最终可能还是要负费的。数据库、表格(table)和列树(column tree)该程序一启动就开始询问我的登录到MySOL服务器的口令。我只需要输入我的服务器名字、用户id和登录密码。所有其它的设置都是正确的默认值。然后(当我开始其它事务、重启几次、睡了一会之后)，我重新运行该程序，这时只需要再次输入我的登录密码。该程序没有保存密码的选项，你可以认为这是该程序的一个bug，也可以说是程序的保密特性。一旦你登录之后，界面就是很值得注意。MySOL服务器上所有的数据库都显示在一个树型控件上。你只能访问你在登录时授权的那个数据库。如果你点开代表授权给你的那个数据库的树型结构，你就可以看到一系列代表表格的节点。点开表格节点后，你就可以看到一系列显示字段名的节点和另一个代表索引的节点集合。索引界面绝对是个好东东，这样你就可以CRUD查询索引和关键字了。这相对前端数据库如Microsoft Access来说是个提高。如果考虑到MySOL刚刚开始提供对主(primary)和非相关(foreign)关键字关系的支持，本程序这部分的设计是很成熟的。在右下方的面板上，有四个标签页，即：结果(Result)、消息(Message)、对象(Object)和历史(History)。在SQLyog FAQ上，有一种观点认为该软件没有正式归档的必要。当然，FAQ(常见问题解答)本身就是一种归档。SQLyog的界面非常直观。我建议你打印一份MySOL文档(包括SQL特殊语法扩展)。我就是这么做的，它只用了一个半英寸的活页封面。最后一步FAQ还让人想到一个让人耳朵起了老茧却又是正确的Occam's Razor准则——一切超出必要的复杂性都是没有必要的。我之所以到处“推销”这个工具，就是因为它可以为我们提供一个可以管理MySOL服务器上许多数据库的、简单的、图形化的界面。它的速度极快，并且它的拷贝很小(可以放在一张软盘上)。SQLyog宣称自己是一个查询分析器，实际上它的功能远远不止这些。

下面我们就看一下常见的备份工具，以及目前最流行的 Percona XtraBackup 的备份流程。

MySQL 常见的备份工具主要分为三种：

这里先说一下 binlog 备份，它只是把 binlog 又复制了一份，并且需要在逻辑备份或者物理备份的基础上才能进行数据恢复，无法单独进行数据恢复。

mysqldump 备份出的文件就是 sql 文件，其核心就是对每个表执行 select ，然后转化成相应的 insert 语句。mysqldump 的备份流程大致如下：

从上面可以看出在 mysqldump 备份期间，备份到某个数据库时，该数据库下的表都会处于只读状态，无法对表进行任何变更，直到该库下的表备份完毕，这对于线上环境一般是无法接受的。若是指定了--master-data或者 --dump-slave 则会在备份开始时加全局读锁（FLUSH TABLES WITH READ LOCK），直到备份结束。当然我们可以选一个从库进行备份，这样就不会影响线上业务。另外使用 mysqldump 备份还有一个最大的好处，因为备份出来的是 sql 语句，所以它支持跨平台和跨版本的数据迁移或者恢复，这是物理备份无法做到的。

但是也正是因为 mysqldump 备份出来的是 sql 语句，在使用时要更加注意，否则可能会酿成大祸。例如，使用 mysqldump 常见的问题有：

所以使用 mysqldump 时一定要了解各个选项的作用，以及确认备份出来的 sql 文件里会有什么 *** 作，会对现有数据造成什么影响。

Mydumper 原理与 Mysqldump 原理类似，最大的区别是引入了多线程备份，每个备份线程备份一部分表，当然并发粒度可以到行级，达到多线程备份的目的。这里不再单独介绍。

Percona XtraBackup 是 Percona 公司开发的一个用于 MySQL 数据库物理热备的备份工具，是基于 InnoDB 的崩溃恢复功能来实现的。它的基本工作原理如下：

Percona XtraBackup 在进行恢复时会应用拷贝的 redo log ，应用已提交的事务，回滚未提交的事物，将数据库恢复到一致性状态。因为 Percona XtraBackup 备份出来的是物理文件，所以在使用备份出的文件进行恢复或者迁移时，不会像 mysqldump 那样会存在很多问题。

使用 XtraBackup 备份时根据备份参数设置不同，对数据库的变更会造成不同程度的影响，具体影响会在下文分析。

通过对比发现，XtraBackup 具有对数据库影响小，且能快速恢复的优点，在日常备份中是首选；mysqldump 使用相对更加灵活，但是使用是要注意对数据库原有数据的影响。

备份策略主要有：全量备份和增量备份，再加上 binlog 备份。

目前去哪儿网数据库备份主要采用 XtraBackup 全量备份 +binlog 备份。数据库的重要级别不同，全量备份的频率不同。备份程序主要架构如下：

说明：

Percona XtraBackup 是目前备份 MySQL 使用最广泛的工具。在备份过程中，数据库可以进行正常的读写或者其他变更 *** 作，但是偶尔也会遇见备份引起的元数据锁，或提交事务时发现被 binlog lock 阻塞等情况。下面我们就看一下 Percona XtraBackup 的备份流程和加锁时机。

说明：以下对 Percona XtraBackup 的分析都是基于 2423 的版本，其他版本会略有差别，但是关键步骤基本相同。

XtraBackup 在备份开始时，会创建一个后台线程，专门用于拷贝数据库的 redo log 。首先 XtraBackup 会扫描每组 redo log 的头部，找出当前的 checkpoint lsn ，然后从该 lsn 后顺序拷贝所有的 redo log ，包括后续新产生的 redo log 。该线程会一直持续到将非事务表完全拷贝完成，才会安全退出。备份日志输出中会记录拷贝开始时的 checkpoint lsn 。日志输出如下：

在拷贝ibd文件之前，会先扫描数据库的数据文件目录，获取ibdata1，undo tablespaces及所有的ibd文件列表，并会记录相应的 space id，因为在恢复时需要这些 space id来找到对应 doublewrite buffer里页面的内容，以及对应的redo log条目。然后开始循环拷贝ibdata1，undo tablespaces及所有的ibd文件。

这里可通过设置--parallel进行多线程备份，提高物理文件的拷贝效率。不设置则默认为1。

在所有ibd文件拷贝完成后，XtraBackup开始备份非ibd文件。这一部分的逻辑比较复杂，因为备份非ibd文件前需要加锁，具体是否会加锁主要受到--no-lock 参数设置的影响。

若是设置了--no-lock为TRUE，则不会使用"FLUSH TABLES WITH READ LOCK"去加全局读锁，但是若备份过程中对non-InnoDB表执行了DDL或者DML *** 作， 这会导致备份的不一致，恢复出来的数据就会有问题。所以是不建议将--no-lock为TRUE，默认值是FALSE，也就是在不指定该选项的情况下会在备份非ibd文件前加全局读锁。

下面我们结合源码来看看判断是否加全局锁这部分的具体流程逻辑：

流程图如下：

总结来看：

1）若--no-lock为FALSE（默认值），则先施加全局读锁，然后再进行拷贝文件，另外若 --safe-slave-backup 设置为TRUE ，则会在加全局锁之前关闭SQL_THREAD线程；

2）若--no-lock为TRUE，则不会施加锁，直接进行拷贝文件。

加锁的逻辑主要由lock_tables_maybe实现,先看一下lock_tables_maybe源代码，如下：

lock_tables_maybe 函数简化处理流程如下：

1）若备份实例上已经加锁（ LOCK TABLES FOR BACKUP / FLUSH TABLES WITH READ LOCK）或者设置lock-ddl-per-table 则直接返回；

2）若支持备份锁，则执行LOCK TABLES FOR BACKUP；

3）若不支持备份锁，则执行 FLUSH TABLES WITH READ LOCK。根据相应选项设置，在执行该 *** 作前会判断是否有执行中的DDL/DML，以及等待超时时间，是否kill 对应的未结束的事务等。

从上文中我们还看到一个参数--safe-slave-backup ，该参数的主要作用是：

若是在从库执行的备份 *** 作时设置了该参数，可以防止因从库同步主库 *** 作，而导致XtraBackup长时间请求不到锁而造成备份失败。

若是设置了 --safe-slave-backup 为TRUE，那么会执行"STOP SLAVE SQL_THREAD"，并等待Slave_open_temp_tables 为零才开始拷贝非 ibd 文件，Slave_open_temp_tables 为零说明SQL thread执行的事务都已经完成，这样就能保证备份的一致性。并且此时也不会有在执行的事务阻塞 XtraBackup 施加全局锁。

备份完非 ibd 文件后，将会备份 slave 和 binlog 信息。

mysql-bin000004 2004 6b7bda9f-15f0-11ec-ba14-fa163ea367a4:1-83,9841546e-15f0-11ec-9557-fa163e736db4:1

需要注意，在支持备份锁的实例上备份，指定了 --slave-info 或--binlog-info 均会先施加 binlog 备份锁（ LOCK BINLOG FOR BACKUP），这会阻塞任何会更改 binlog 位点的 *** 作。

备份完数据库的所有文件和binlog等相关信息，备份工作就基本完成了，之后主要执行的 *** 作如下：

1）执行"FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS",将所有的redo log刷盘；

2）停止redo log复制线程；

3）释放全局读锁（备份锁），binlog锁；

4）开启SQL_THREAD；

5）拷贝ib_buffer_pool和ib_lru_dump文件；

6）生成配置文件backup-mycnf；

7）打印备份信息到xtrabackup_info文件，这些信息主要包含备份时使用的参数信息，备份起止时间，binlog位点信息，以及将会回到的lsn点。

下面是xtrabackup_info记录的部分内容：

加锁对应的函数是 mdl_lock_tables ，释放锁对应的函数是 mdl_unlock_all，主要是执行COMMIT，结束 mdl_lock_tables 中开启的显式事务，来释放MDL锁。mdl_lock_tables 流程如下：

上面参数--lock-ddl和--lock-ddl-per-table是在 Percona XtraBackup 248 之后添加的，因为 MySQL 57 新增了一个叫做 Sorted Index Builds 的功能，这会导致某些 DDL *** 作不记录重做日志而导致备份失败。使用--lock-ddl或--lock-ddl-per-table 就会在备份开始时施加锁，阻止 DDL *** 作。

另外，若备份时指定了--lock-ddl或--lock-ddl-per-table，则在备份非 ibd 文件时就不是再有加锁 *** 作。

注意：LOCK TABLES FOR BACKUP和LOCK BINLOG FOR BACKUP 语句只有在支持备份锁的实例上才会执行，Percona Server for MySQL已经在 5616-640 版本开始支持这种更加轻量的备份锁。

Q1: 使用 XtraBackup 备份的文件进行恢复时，恢复到哪个时间点？ A1：恢复到执行 LOCK BINLOG FOR BACKUP 或 FLUSH TABLES WITH READ LOCK 的时间点，因为这时任何改变 binlog 位点的 *** 作都会被阻塞，redo log和binlog 是一致的。

Q2: 在开启 binlog 的情况下，MySQL 的奔溃恢复是同时依赖 binlog 和 redo log 这两种日志的，为什么XtraBackup 不用备份binlog？

A2：因为在备份中有执行LOCK BINLOG FOR BACKUP/FLUSH TABLES WITH READ LOCK，阻止了任何改变binlog位点的 *** 作，这样只需要根据redo log将有commit log 的事务提交，没有commit log的事务进行回滚即可。

Q3: 使用Percona XtraBackup备份完成后redo的位点是和binlog是一样还是比binlog多一些？

A3：通过分析备份流程可以发现备份 binlog 位点信息（加binlog锁）是发生在停止 redo 拷贝线程前，而释放锁是在停止 redo 拷贝线之后，所以 redo log 会多一些。锁住了 binlog 保证了在该 binlog 位点前已经提交的事务的 redo log 都有 commit log 的信息，未提交的事物也就没有对应的 commit log 的信息，即便在锁住 binlog 后有 Innodb 表新的 DML 产生的 redo log ，但是事务无法提交，也就没有 commit log 的信息的，最后在回放的过程中对没有 commit log 的事务进行回滚就可以了。

Q4：Percona XtraBackup什么时候会加锁，以及影响加锁时间长度的因素有哪些？

A4：上面进行了分析，加锁 *** 作只在备份非 ibd 文件时执行，加锁时长主要和非事务表的数量和大小有关，非事务表的数量越多，体积越大，拷贝文件所用的时间越长，那么加锁时间也就越长。也会和 redo log 生成的速度有关，只是 redo log 刷盘受到多个因素的影响，未及时刷盘的 redo log 一般很小。

Q5：Percona XtraBackup 和mysqldump选择哪个更好？

A5：通过上面的的解析，若是整个实例备份，首先选择 Percona XtraBackup ，因为对数据库的影响最小。若只是备份某个库表，这个就要视数据量而定，若数据量不大可以使用 mysqldump 。注意，对数据库做备份时最好选择业务连接最少的从库，因为备份也会消耗一定的资源，避免影响业务。

导读作为大数据工程师，其必须要掌握的基础知识就是java与mysql的关系、交互和连接，作为基础，也是面试考官经常会考的内容，为了帮助大家都能顺利通过考试，今天小编就来和大家唠一唠java与mysql的关系、交互和连接，好了，开始今天的内容大数据分析师面试必备：java与mysql解析。

1 SQL语言四大类：

DQL 数据查询语言 select

DML 数据 *** 作语言 insert、update、delete

DDL 数据界说语言 create、alter

DCL 数据控制语言 grant权限

2 mysql数据库中的decimal类型(是数值型，不能存放字符串)：

举例：decimal(18,0) 常用于身份z号码，但是带x的不可以。

举例：decimal(5,2)

状况一：假设小数点前面是3位，后边是2位，正常状况。

状况二：5指的是小数点前后不能超过5位，小数点后有必要是2位。

3 mysql中InnoDB和MyISAM引擎的差异：

innodb支撑：事务和主外键

myisam不支撑：事务和主外键

4 不需要背诵，选择题考点向mysql中，a向表中添加数据的几种写法，题目：id int 主键自增，name varchar(11)

不为空。

5 *** 作mysql数据库表有两种方式，第一种：点八点吧;第二种：写代码。不需要背诵，只需要了解，考试选择题会出

6 在Java中，简述面向对象三大特征。

7 在Java中，常用关键字：

1 定义类的关键字是什么 class

2 继承的关键字是什么 extends

3 定义接口的关键字是什么 interface

4 实现接口的关键字是什么 implements

5 抽象类的关键字是什么 abstract

8 在Java中，抽象类和接口的区别：

1 抽象类中可以包含普通方法和抽象方法，接口中只能包含抽象方法

2 抽象类中可以有构造方法，接口中没有构造方法

3 抽象类只能单继承，可以实现多个接口

9 Java接口中有哪些成员

1 构造方法，没有

2 常量，默认访问修饰符public static final，没有变量

3 抽象方法，默认访问修饰符public abstract

10 在Java中，抽象类和抽象方法的关系：

1 抽象类中可以包含普通方法和抽象方法，抽象方法一定存在抽象类中。

2 子类继承抽象父类，必须实现|重写抽象方法，除非子类也是抽象类。

3 判断题抽象类中必须包含抽象方法错误×

4 判断题抽象方法一定存在抽象类中正确√

11 Java重载的特点：

1 在同一个类中

2 方法名相同

3 参数列表(个数、类型、顺序)不同

4 与返回值类型和访问修饰符无关

12 Java重写的特点：

1 在父子类中

2 方法名相同

3 参数列表相同

4 返回值类型相同，或是其子类

5 访问修饰符相同，或不能严于父类

13 列举几种Java实现多态的形式：

1 继承的存在

2 父类引用指向子类对象 | 向上转型

3 父类作为方法的返回值类型，父类作为方法的参数

14 Java接口的特性：单根性和传递性

15 在Java中，throws和throw的区别：

1 throws 声明异常，用在定义方法小括号的后面

2 throw 抛出异常，写在方法体内

以上就是小编今天给大家整理发送的关于大数据分析师面试必备：java与mysql解析的相关内容，希望对各位考生有所帮助，想知道更多关于数据分析师的基本要求有哪些，关注小编持续更新数据分析师岗位解析。

以上就是关于北大青鸟设计培训：mysql数据库的优化方法全部的内容，包括:北大青鸟设计培训：mysql数据库的优化方法、mysql数据库崩溃的原因(mysql崩溃恢复)、详细讲解如何用SQLyog来分析MySQL数据库等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！