数据库mysql各种工作原理

1、什么是数据库？什么是关系型数据库？什么是非关系型数据库？

数据库就是一个存储数据的仓库。

关系型数据库是依据关系模型（就是“一对一、一对多、多对多”等关系模型，关系模型就是指二维表格模型,因而一个关系型数据库就是由二维表及其之间的联系组成的一个数据组织。）来创建的数据库。例如：Oracle、DB2、PostgreSQL、Microsoft SQL Server、Microsoft Access、MySQL。

非关系型数据库主要是基于“非关系模型”（列模型：Hbase；键值对模型：redis,MemcacheDB；

文档类模型：mongoDB）的数据库（由于关系型太大，所以一般用“非关系型”来表示其他类型的数据库）

关系型数据库与非关系型数据比较：

查询速度：nosql数据库优于关系型数据库。nosql数据库将数据存储于缓存之中，关系型数据库将数据存储在硬盘中，自然查询速度远不及nosql数据库。

安全：关系型数据库优于nosql数据库。因为关系型数据库存储在磁盘中，不会说突然断电数据就没有了。断电丢失数据，但其中redis可以同步数据到磁盘中，现在很多非关系型数据库都开始支持转存到磁盘中。

存储数据的格式：关系型数据库优于nosql数据库。nosql的存储格式是key,value形式、文档形式、图片形式等等，所以可以存储基础类型以及对象或者是集合等各种格式，而数据库则只支持基础类型（varchar等）。

2、MYSQL、ORACLE、SQL SERVER功能和应用场合

Mysql 端口号3306，主要用于大型门户（中小型公司也在使用），例如搜狗、新浪等，它主要的优势就是开放源代码，因为开放源代码这个数据库是免费的，它现在是甲骨文公司的产品。

Oracle 端口号 1521，主要用于银行、铁路、飞机场等。该数据库功能强大，软件费用高。也是甲骨文公司的产品。

sql server 端口号1433，是微软公司的产品，主要应用于大中型企业，如联想、方正等。

3、数据库服务器、数据库、表与记录的关系

所谓安装数据库服务器，只是在机器上装了一个数据库管理程序，这个管理程序可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体的数据。

4、数据的存储引擎。

存储引擎（engines）：存储引擎就是指表的类型。数据库的存储引擎决定了表在计算机中的存储方式。

mysql支持的存储引擎包括：MyISAM、InnoDB、MEMORY、ARCHIVE、MRG_MYISAM等。其中，InnoDB为默认存储引擎。最常使用的两种存储引擎是MyISAM和InnoDB（5.5以上默认Engine是Innodb，其他版本默认是MyISAM）。

5.sql语言的分类

1）数据定义语言（DDL）

       用于创建、修改、和删除数据库内的数据结构，如：1：创建和删除数据库(CREATE     DATABASE || DROP  DATABASE)；2：创建、修改、重命名、删除表(CREATE  TABLE || ALTER   TABLE|| RENAME TABLE||DROP  TABLE||TRUNCATE TABLE)；3：创建和删除索引    (CREATEINDEX  || DROP   INDEX)

    2）数据查询语言（DQL）

        从数据库中的一个或多个表中查询数据(SELECT)

    3）数据操作语言（DML）

         修改数据库中的数据，包括插入(INSERT)、更新(UPDATE)和删除(DELETE)

    4）数据控制语言（DCL）

          用于对数据库的访问，如：1：给用户授予访问权限（GRANT）;2：取消用户访问    权限（REMOKE）

问：什么时候使用DROP?什么时候使用DELETE?什么时候用TRUNCATE？

答：由上可看出，对于结构删除，如数据库删除、表删除、索引删除等当使用DROP;而对于数据的删除则是DELETE，DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作；TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

从表和索引所占空间来看，当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小，DELETE操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。

6、SQL语句查询的基本语句：

7、mysql密码的修改与恢复

a)    方法一：

①　停止mysql服务

②　启动跳过授权列表 mysqld --skip-grant-tables或者在mysql主配置文件中增加--skip-grant-tables再重启

③　update user set password=password(‘新密码‘) where host=‘主地址‘ and user=‘用户名‘;

④　flush privileges;//刷新权限  

⑤　重启登陆mysql服务                                                

b)    方法二：

①　停止mysql服务

②　启动跳过授权列表 mysqld --skip-grant-tables或者在mysql主配置文件中增加--skip-grant-tables再重启

③　set password for 用户名@‘主机地址‘ =password(‘新密码‘);    //注这种方法无需刷新权限

④　重启并登陆mysql服务

8、数据库的备份与恢复

a)备份的概念：为应付文件、数据丢失或损坏等可能出现的意外情况，将电子计算机存储设备中的数据复制到磁带等大容量存储设备中,从而在原文中独立出来单独贮存的程序或文件副本; 如果系统的硬件或存储媒体发生故障，“备份”工具可以帮助您保护数据免受意外的损失。

b)一般的备份可分为：1、系统备份：指的是用户操作系统因磁盘损伤或损坏，计算机病毒或人为误删除等原因造成的系统文件丢失，从而造成计算机操作系统不能正常引导，因此使用系统备份，将操作系统事先贮存起来，用于故障后的后备支援。2、数据备份：指的是用户将数据包括文件，数据库，应用程序等贮存起来，用于数据恢复时使用。

c)备份更专业地可分为：

i.全量备份：完全备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝

ii.增量备份：增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加和者被修改的文件

iii.差异备份：差异备份是指在一次全备份后到进行差异备份的这段时间内，对那些增加或者修改文件的备份

d)对mysql数据库而言，就是数据备份，可分为全量备份与增量备份

e)备份工具有：mysqldump、xtrabackup、mydumper

9、备份计划，mysqldump以及xtranbackup的实现原理 

(1)、备份计划； 

这里每个公司都不一样，您别说那种1小时1全备什么的就行 

(2)、备份恢复时间； 

这里跟机器，尤其是硬盘的速率有关系，以下列举几个仅供参考 

20G的2分钟（mysqldump） 

80G的30分钟(mysqldump) 

111G的30分钟（mysqldump) 

288G的3小时（xtra) 

3T的4小时（xtra) 

逻辑导入时间一般是备份时间的5倍以上

(3)  、xtrabackup实现原理 

Xtrabackup是由percona提供的mysql数据库备份工具, 有两个主要的工具：xtrabackup、innobackupex，备份过程快速、可靠，备份过程不会打断正在执行的事务，能够基于压缩等功能节约磁盘空间和流量，自动实现备份检验，还原速度快，备份可在线备份，但是恢复要关闭服务器，恢复后再启动.

在InnoDB内部会维护一个redo日志文件，我们也可以叫做事务日志文件。事务日志会存储每一个InnoDB表数据的记录修改。当InnoDB启动时，InnoDB会检查数据文件和事务日志，并执行两个步骤：它应用（前滚）已经提交的事务日志到数据文件，并将修改过但没有提交的数据进行回滚操作。

(4)  mysqldump实现原理

mysqldump的本质是通过select * from tab来获取表的数据的。mysqldump只适合放到业务低峰期做，如果备份的过程中数据操作很频繁，会造成Undo表空间越来越大，undo表空间默认是放到共享表空间中的，而ibdata的特性是一旦增大，就不会收缩。

(5) mydumper实现原理

mydumper是一个使用C语言编写的多线程导出导入工具,并且能够保证多个表之间的一致性

10、mysql日志类型

错误日志：记录启动、运行或停止mysqld时出现的问题。参数:log-error。

二进制日志： 该日志文件会以二进制形式记录数据库的各种操作，但是不记录查询语句。参数：log-bin。

查询日志：该日志分为两类：通用查询日志（参数：log）和慢查询日志（参数：log-slow-queries和设置时间的参数：long_query_time）。前者记录建立的客户端连接和执行的语句。 后者记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询。

11、mysql主从复制优点

健壮性:主服务器出现故障时,可以切到从服务器作为备份

速度快:更新操作在主服务器端,查询操作在从服务器端,可以加快用户的响应时间

备份:避免影响业务

12、mysql主从复制原理

从库生成两个线程，一个I/O线程，一个SQL线程；

 i/o线程去请求主库 的binlog，并将得到的binlog日志写到relay log（中继日志） 文件中；

主库会生成一个 log dump 线程，用来给从库 i/o线程传binlog；

SQL 线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，而最终数据一致；

13、mysql支持的复制类型：

(1)：基于语句的复制： 在主服务器上执行的SQL语句(写入bin log中)，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。binlog_format = ‘STATEMENT‘

(2)：基于行的复制：把改变的内容(写入bin log中)复制过去，而不是把命令在从服务器上执行一遍. 从mysql5.0开始支持。binlog_format = ‘ROW‘

(3)：混合类型的复制: 默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。binlog_format = ‘MIXED‘

14、mysql读写分离原理

Amoeba(变形虫)项目，该开源框架于2008发布一款Amoeba for mysql软件，该软件致力于mysql的分布式数据库前端代理层，主要的作用是应用服务访问mysql服务器时充当SQL路由功能，并具有负载均衡、高可用性、SQL过滤、读写分离、可路由相关SQL的到目标数据库、可并发请求多台数据库全并结果的作用。通过Amoeba能够完成多数据源的高可用、负载均衡、数据切片的功能。原理如下：

在数据库集群架构中，让主库负责处理事务性查询，而从库只负责处理select查询，让两者分工明确达到提高数据库整体读写性能。当然，主数据库另外一个功能就是负责将事务性查询导致的数据变更同步到从库中，也就是写操作。

读写分离优点：

1）分摊服务器压力，提高机器的系统处理效率

    读写分离适用于读多于写的场景，如果有一台服务器，当select很多时，update和delete会被这些select访问中的数据堵塞，等待select结束，并发性能并不高，而主从只负责各自的写和读，极大程度的缓解X锁和S锁争用；

   假如我们有1主3从，不考虑上述1中提到的从库单方面设置，假设现在1分钟内有10条写入，150条读取。那么，1主3从相当于共计40条写入，而读取总数没变，因此平均下来每台服务器承担了10条写入和50条读取（主库不承担读取操作）。因此，虽然写入没变，但是读取大大分摊了，提高了系统性能。另外，当读取被分摊后，又间接提高了写入的性能。所以，总体性能提高了，说白了就是拿机器和带宽换性能；

2）增加冗余数据库服务器宕机后可以调整另外一台从库以最快速度恢复服务，提高服务可用性。

15、amoeba进行数据切分

根据数据的特点，将不同特性的数据纪录存放到不同的数据库服务器中就是数据切分，可以设置规则匹配数据特性以实现数据切分，其实，数据切分就是根据设置规则将数据分别存放到不同的数据库，以减轻数据库服务器的压力和减小单个数据库的大小，可通过设置amoeba的rule.xml文件中的切片规则实现。原理如图：

    水平分割:将关键字段按特定的算法存放到不同的服务器

    垂直分割:将数据按业务的不同分别保存到不同的服务器

数据库扩展读写分离软件还有：mycat,mysql-proxy,maxscale

原文：https://www.cnblogs.com/wendyluo/p/13394261.html