精华内容
下载资源
问答
  • 制作mysql数据快照

    千次阅读 2019-07-08 11:27:01
    在建立主从关系时,如果主上有旧数据,需要将旧数据拷贝到每一个从上。下面介绍几种不同的拷贝方式。...找到mysqldump(跟mysql启动程序同路径)把主上的数据制作快照 ./mysqldump -uroot -pabc -...

    在建立主从关系时,如果主上有旧数据,需要将旧数据拷贝到每一个从上。下面介绍几种不同的拷贝方式。

    方法一:使用mysqldump工具创建一个你想要复制的所有数据库的一个dump。这是推荐使用的方法,特别是如果表的存储引擎都是innodb。

    具体操作方法:

    • 找到mysqldump(跟mysql启动程序同路径)把主上的数据制作快照
    ./mysqldump -uroot -pabc -h127.0.0.1 --all-databases --master-data > dbdump.db

    注意:

    如果不使用—master-data参数,需要在一个独立的客户端会话里

    执行flush all tables with read lock,以保证制作数据镜像的时候没有新的数据写入.

    如果不想复制所有的数据库或者想忽略某个表,不要使用—all-databases, 分别使用下面2个参数:—database your_db_name, —ignore-table,例如,我想复制主上janey数据下除里表ta,tb以外的表命令:

    ./mysqldump -uroot -pabc -h127.0.0.1 --databases janey --ignore-table janey.ta --ignore-table janey.tb --master-data > dbdump.db
    • 然后把生成的dbdump.db文件通过scp或者其他方式拷贝到从上
    • 在从上恢复数据:
    ./mysql -uroot -pabc -h127.0.0.1 < dbdump.db 

    方法二:Creating a Data Snapshot Using Raw Data Files(自己还没搞懂,todo)

    如果你的数据库都是存在二进制可移植文件中,你可以把这些二进制文件拷贝到每一个从。这比使用mysqldump效率更高。然后在从上import这些二进制文件,之所以这种方式这种情况下效率高是因为跳过了在遇到insert语句被回放时更新索引。对于存储引擎是innodb不推荐这种方式。

    转载于:https://www.cnblogs.com/janey/p/5986973.html

    展开全文
  • SQL Server数据库快照

    2020-07-26 13:51:19
    MS SQL Server 2005和更高版本包含“数据库快照”功能,以具有用于报告的数据库快照,作为不同时期的副本。 The Database Snapshot can be created multiple times and it can only be created using the T-SQL. ...

    介绍 (Introduction)

    MS SQL Server 2005 and later versions include the Database Snapshot feature to have snapshot of the database for reports, as a copy in different periods.

    MS SQL Server 2005和更高版本包含“数据库快照”功能,以具有用于报告的数据库快照,作为不同时期的副本。

    The Database Snapshot can be created multiple times and it can only be created using the T-SQL.

    数据库快照可以创建多次,并且只能使用T-SQL创建。

    In this article, we will show how to create a Database Snapshot, how to see the snapshot created in the SQL Server Management Studio (SSMS), how to recover objects dropped or data removed using the snapshot.

    在本文中,我们将展示如何创建数据库快照,如何查看在SQL Server Management Studio(SSMS)中创建的快照,如何恢复使用快照删除的对象或删除的数据。

    Finally, we will learn how to create Snapshots automatically for reporting purposes.

    最后,我们将学习如何为报告目的自动创建快照。

    要求 (Requirements)

    • SQL Server Enterprise or Evaluation Edition is required

      需要SQL Server Enterprise或评估版
    • We are using SQL Server 2014, but earlier versions can be used

      我们正在使用SQL Server 2014,但是可以使用早期版本
    • The Adventureworks Database is required

      需要Adventureworks数据库
    • The Adventurewoks database has to be online

      Adventurewoks数据库必须在线

    入门 (Getting started)

    To create a database snapshot, we need to use the T-SQL. It is the only way to do it. You cannot create a Database Snapshot in the SSMS.

    要创建数据库快照,我们需要使用T-SQL。 这是唯一的方法。 您不能在SSMS中创建数据库快照。

    The syntax is the following:

    语法如下:

     
    CREATE DATABASE AdventureWorks_snapshot ON
     
    ( NAME = AdventureWorks2012_Data, 
     
    --Name of the snapshot file
     
    FILENAME =
     
    'C:\script2\AdventureWorks_data_1800.ss' )
     
    --It is a Snapshot of the adventureworks2012 database
     
    AS SNAPSHOT OF [AdventureWorks2012];
     
    GO
     
    

    As you can see, the syntax is similar to a normal database creation except for two things:

    如您所见,该语法与普通数据库的创建相似,除了两点:

    1. We use the word AS SNAPSHOT OF DATABASE_NAME to specify the name of the database that requires a snapshot.

      我们使用单词AS SNAPSHOT OF DATABASE_NAME来指定需要快照的数据库的名称。
    2. By default, it is better to specify the extension of the snapshot datafile as .ss (which means SnapShot.

      默认情况下,最好将快照数据文件的扩展名指定为.ss(即SnapShot。

    If everything is OK, you will be able to see the snapshot created in the SSMS:

    如果一切正常,您将能够看到在SSMS中创建的快照:

    Adwentureworks snapshot

    Figure 1


    图1

    The snapshots have read-only tables. If you try to update or delete the data you will not be able to do it. You will receive a read only message:

    快照具有只读表。 如果您尝试更新或删除数据,则将无法执行。 您将收到一条只读消息:

    Read only message is shown - No row was updated

    Figure 2


    图2

    You can also use the T-SQL to try to update tables in the database snapshot with the same results:

    您也可以使用T-SQL尝试以相同的结果更新数据库快照中的表:

    Use T-SQL query to update tables in the database snapshot

    Figure 3


    图3

    The database snapshots files have a similar size that a normal database, but a smaller size on disk. This is because each time that the original database changes, the snapshot grows. This reduces a little bit the database performance because of the synchronization.

    数据库快照文件的大小与普通数据库相似,但磁盘上的文件较小。 这是因为原始数据库每次更改时,快照都会增长。 由于同步,这会降低一点数据库性能。

    Database Snapshot properties

    Figure 4


    图4

    The file size of the Snapshot Database is just 3.31 MB. The Adventureworks2012 database has the same size and size in disk as shown in the picture 5:

    快照数据库的文件大小仅为3.31 MB。 Adventureworks2012数据库的磁盘大小和大小相同,如图5所示:

    Adventureworks2012 database properties

    Figure 5


    图5

    The size on this of the source database is equal to 205 MB.

    源数据库的大小等于205 MB。

    使用快照恢复对象 (Using snapshots to recover objects)

    If by mistake (or any other circumstance) a user drops a stored procedure, a view or a table or any object, you can recover the database object using the snapshot.

    如果用户由于错误(或任何其他情况)删除了存储过程,视图或表或任何对象,则可以使用快照恢复数据库对象。

    (Example)

    Imagine that an evil person drops the dbo.uspGetBillOfMaterials stored procedure in the Database

    想象一个邪恶的人在数据库中删除了dbo.uspGetBillOfMaterials存储过程

     
    Drop procedure dbo.uspGetBillOfMaterials
     
    

    Now, imagine that you are a smart person and you recover the stored procedure using the stored procedure from the Database Snapshot. To do this, generate the CREATE PROCEDURE from the Snapshot Database:

    现在,假设您是一个聪明的人,并且使用数据库快照中的存储过程来恢复存储过程。 为此,请从快照数据库生成CREATE PROCEDURE:

    Generating the CREATE PROCEDURE from the Snapshot Database

    Figure 6


    图6

    In the code generated, just replace the first part like this:

    Replace

    在生成的代码中,只需像这样替换第一部分:

    更换
    USE  [AdventureWorks_snapshot]
    
    USE [AdventureWorks2012]
    

    Once replaced press F5 to execute the script:

    替换后,按F5键执行脚本:

    Executed script

    Figure 7


    图7

    If you follow all the steps, you will have your stored procedure restored. The same concept is applicable to any Database Object. You can easily recreate specific objects from the Snapshot Database to the source Database.

    如果执行所有步骤,将还原存储过程。 相同的概念适用于任何数据库对象。 您可以轻松地将特定对象从快照数据库重新创建到源数据库。

    从表中还原数据 (Restore the data from a table)

    In this new demo, we will truncate all the data from the table Person.Password and restore the information from the Database Snapshot snapshot.

    在这个新的演示中,我们将截断表Person.Password中的所有数据,并从数据库快照快照中还原信息。

    First, truncate all the data from a table:

    首先,截断表中的所有数据:

    truncate table [Person].[Password]
    

    Secondly, restore the information from the Snapshot Database table to the source Database:

    其次,将信息从“快照数据库”表还原到源数据库:

     
    USE [AdventureWorks2012]
    GO
    INSERT INTO [Person].Password
    Select
    	*
    From [AdventureWorks_snapshot].[Person].Password
     
    

    We just inserted the information from the snapshot table to the empty Person.Table table from the Adventureworks2012 database.

    我们只是将快照表中的信息插入到Adventureworks2012数据库中的空Person.Table表中。

    从快照还原整个数据库 (Restore the entire database from the snapshot)

    If all the objects and data were dropped, it would possible to recover all the information from the snapshot to the original database.

    如果删除了所有对象和数据,则可以将所有信息从快照恢复到原始数据库。

    In this new example, we will delete all the views from the AdventureWorks database and recover all the information from the snapshot.

    在这个新示例中,我们将从AdventureWorks数据库中删除所有视图,并从快照中恢复所有信息。

    1. First we will have all the views of the AdventureWorks database:

      首先,我们将拥有AdventureWorks数据库的所有视图:

      Views of the AdventureWorks database

      Figure 8


      图8

    2. Then we will drop all the views:

      然后,我们将删除所有视图:

       
      DECLARE @sql VARCHAR(MAX)='';
      SELECT @sql=@sql+'DROP VIEW ['+name +'];' FROM sys.views;
      EXEC(@sql);
       
      

      As you can see in the Figure 9, all the views were removed:

      如您在图9中看到的,所有视图都被删除:

      All the views are removed

      Figure 9


      图9

    3. Now, revert your database. You may have problems to restore the database because there are multiple connection available. If that is the case, set the AdventureWorks database in a single user mode to close the other connections.

      现在,还原数据库。 由于存在多个可用连接,因此您可能在还原数据库时遇到问题。 如果是这种情况,请在单用户模式下设置AdventureWorks数据库以关闭其他连接。

       
      USE master;
      GO
      ALTER DATABASE AdventureWorks2012
      SET SINGLE_USER
      WITH ROLLBACK IMMEDIATE;
      GO
       
      
    4. Finally, restore the AdventureWorks Database from the snapshot:

      最后,从快照还原AdventureWorks数据库:

       
      RESTORE DATABASE AdventureWorks2012 from
       
      DATABASE_SNAPSHOT = 'AdventureWorks_snapshot';
       
      

    If everything is OK, you will be able to see your dropped views again:

    如果一切正常,您将能够再次看到您的拖放视图:

    Views should be back

    Figure 10


    图10

    每月创建数据库快照 (Create Database Snapshots every month)

    Finally, we will show how to create Database snapshots every month.

    最后,我们将展示如何每月创建数据库快照。

    Here you have the T-SQL code to create a Database with the current month number:

    在这里,您具有使用当前月份号创建数据库的T-SQL代码:

     
    DECLARE @SQL VARCHAR(MAX)='';
     
    --GET THE MONTH NUMBER IN THE @MONTH VARIABLE
     
    DECLARE @MONTH VARCHAR(2)= MONTH(GETDATE())
     
    --CONCATENATE THE SNAPSHOT DATABASE AND THE MONTH NUMBER
     
    SELECT @SQL ='CREATE DATABASE ADVENTUREWORKS_SNAPSHOT_'+@MONTH+
     
    ' ON (NAME=AdventureWorks2012_Data, FILENAME=''C:\scripts\adventure.ss'')
     
    AS SNAPSHOT OF Adventureworks2012'
     
    EXECUTE (@SQL)
     
    

    The code creates a Database Snapshot with the current number of the month.

    该代码使用当前的月份数创建一个数据库快照。

    Database Snapshot created

    Figure 11


    图11

    To create snapshots of the database, it is necessary to schedule a Job each month:

    要创建数据库的快照,有必要每月计划一次作业:

    Creating New Job

    Figure 12


    图12

    Specify a Name and optionally a description of the job:

    指定名称和可选的作业描述:

    Specifying Job name and description

    Figure 13


    图13

    In the steps page create a new page. Specify a Name and select the T-SQL Type (which is the first option by default):

    在步骤页面中创建一个新页面。 指定名称并选择T-SQL类型(默认情况下是第一个选项):

    Choosing Step name and T-SQL type

    Figure 14


    图14

    Finally, schedule the job every month:

    最后,每月安排工作:

    Scheduling New Job every month

    Figure 15


    图15

    As you can see, it is very simple to automate tasks and work with Snapshot Databases.

    如您所见,自动化任务和使用快照数据库非常简单。

    结论 (Conclusion)

    As you can see, Snapshots is a simple way to create copies of your information to partially restore the information. The Snapshots cannot replace the traditional backups, because it depends on the source database. If the source database is corrupted, the Snapshot will not be able to restore the database because there is a dependency between them. However, you can restore data and objects from the snapshot. This is very useful if the database is big and we want to restore some few objects or some specific rows.

    如您所见,快照是创建信息副本以部分还原信息的简单方法。 快照不能替换传统备份,因为它取决于源数据库。 如果源数据库已损坏,则快照将无法还原数据库,因为它们之间存在依赖性。 但是,您可以从快照还原数据和对象。 如果数据库很大并且我们要还原一些对象或某些特定的行,这将非常有用。

    翻译自: https://www.sqlshack.com/sql-server-database-snapshots/

    展开全文
  • Vmware创建 快照 和 克隆 ...程序的配置文件,比如Mysql配置文件 home 用户目录,就像 windows 的 user proc 正在运行中的进程文件 root root用户 ,和windows不一样,不和其他用户在一个文件
  • mysql当前读和快照读(MVCC) mysql读取数据实际上有两种读取模式:当前读和快照读 当前读:每次读取的都是当前最新的数据,但是读的时候不允许写,写的时候也不允许读。 快照读:读写不冲突,每次读取的是快照数据...

    mysql当前读和快照读(MVCC)

    • mysql读取数据实际上有两种读取模式:当前读和快照读
    • 当前读:每次读取的都是当前最新的数据,但是读的时候不允许写,写的时候也不允许读。
    • 快照读:读写不冲突,每次读取的是快照数据,
      • 隔离级别Repeatable Read下(默认隔离级别):有可能读取的不是最新的数据
      • Read Committed隔离级别下:快照读和当前读读取的数据是一样的,都是最新的。

    一. 相关知识

    1.1 undolog

    在操作数据之前,把需要操作的数据和事务记录备份到undo log中,目的就是为了保证原子性,如果一个事务执行中发生了故障,就可以通过undolog进行回滚

    1.2 共享锁和排它锁

    • 共享锁(S锁):共享 (S) 用于不更改或不更新数据的操作(只读操作),如 SELECT 语句。

      • 如果事务T仅对数据A进行读取,那么会对数据A加上共享锁,之后则其他事务如果要读取数据A的话可以对其继续加共享锁,但是不能加排他锁(也就是无法修改数据)。获准共享锁的事务只能读数据,不能修改数据。
    • 排他锁(X锁):用于数据修改操作,例如 INSERT、UPDATE 或 DELETE。确保不会同时同一资源进行多重更新。

      • 如果事务T对数据A要进行修改,则需要对其添加排它锁,加上排他锁后,则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据,又能修改数据。

    二. 当前读

    • Mysql实现当前读是通过共享锁+排他锁+Next-Key Lock实现的。
    • 每次对行数据进行读取的时候,加共享锁。此时就不允许修改,但是允许其他事务读取,所以每次都可以读到最新的数据。
    • 每次对行数据进行修改的时候,加排他锁,不允许其他事务读取和修改。这种情况下其他事务读取的数据也一定是最新的数据。
    • 每次对范围行数据进行读取的时候,对这个范围加一个范围共享锁。
    • 每次对范围行数据进行修改的时候,读这个范围加一个范围排它锁。

    基于上述锁机制,实现当前读,确保每次读取的都是最新的数据。

    三. 快照读

    • mysql中的快照读是通过MVCC+undolog实现的。
    • 快照读,顾名思义,就是读取快照数据,也就是说当某个数据正在被修改的时候,也可以进行读取该数据,保证读写不冲突。
    • 刚刚提到undolog,当我们对记录做了变更操作时,就会产生undo记录,undo记录中存储的是老版数据,当一个旧的事务需要读取数据时,为了能够读取到老版本的数据,需要顺着undo列找到满足其可见性的记录,这个找满足可见行的记录依赖。就是说每次都是读取undolog中的数据。

    MVCC:

    • MVCC也称多版本并发控制,说的通俗易懂一点就是记录数据的不同版本。
    • 实际上,不同事务或者相同事务的对同一记录的修改,会导致该记录的undo log成为一条记录版本线性表,既链表,undo log的链首就是最新的旧记录,链尾就是最早的旧记录。
    • 所以快照读都是去读取undolog中链首的最新的旧记录。

    但是不同隔离级别下的快照读又有所区别。下面简单阐述

    四. 不同隔离级别下的快照读

    这里举例说明:

    • 事务开始前 name = 张三 id = 1
    • 事务A:select name from t where id = 1 (多次)
    • 事务B:update name = 李四 from t where id = 1

    在Read Committed隔离级别下,快照读和当前读读取的是一样的。因为每次select都会去读取最新的快照数据。

    过程如下:

    • 事务A开始
    • 事务B开始
    • 事务A select ,查出name = 张三
    • 事务B update,修改name = 李四
    • 事务B结束
    • 事务A 再次select ,查出name = 李四 (因为此时undolog中的最新数据为李四)
    • 事务A结束

    在默认隔离级别Repeatable Read下,在一个事务内,读取的都是第一次select的数据,所以可能会出现读取的数据不是最新数据的情况
    过程如下:

    • 事务A开始
    • 事务B开始
    • 事务A select ,查出name = 张三
    • 事务B update,修改name = 李四
    • 事务B结束
    • 事务A 再次select ,查出name = 张三 (因为只读第一次的快照数据)
    • 事务A结束
    展开全文
  • mysql数据库底层原理

    千次阅读 2020-05-18 21:47:19
    一、MySQL 的基本定义: ...关系数据库管理系统,由瑞典MySQL AB公司开发,目前属于Oracle公司。 是建立在关系模型基础上的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据。 特点: 数据以表格

    一、MySQL 的基本定义:

    A.术语介绍

    1. 数据库(Database)

    是按照数据结构来组织、存储和管理数据的仓库。
    每个数据库都有一个或多个不同的API用于创建、访问、管理、搜索和复制所保存的数据。
    

    2. RDBMS(Relational Database Management System)

    关系数据库管理系统,由瑞典MySQL AB公司开发,目前属于Oracle公司。
    是建立在关系模型基础上的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据。
    

    特点:

    数据以表格的形式出现。
    每行为各种记录名称。
    每列为记录名称所对应的数据域。
    许多的行和列组成一张表单。
    若干的表单组成Database。
    

    RDBMS术语:

    数据库: 数据库是一些关联表的集合。
    数据表: 表示数据的矩阵。
    列: 一列(数据元素)包含了相同类型的数据。
    行: 一行是一组相关的数据。
    冗余: 存储两倍数据,冗余降低了性能,但提高了数据的安全性。
    主键: 主键是唯一的,一个数据表中只能包含一个主键。
    外键: 外键用于关联两个表。
    复合键: 复合键(组合键)将多个列作为一个索引键,一般用于复合索引。
    索引: 使用索引可快速访问数据库表中的特定信息,索引是对数据库表中一列或多列的值进行排序的一种结构。
    参照完整性: 参照完整性要求关系中不允许引用不存在的实体,与实体完整性是关系模型必须满足的完整性约束条件,目的是保证数据的一致性。
    

    B.MySQL常用命令

    1. 管理MySQL的命令

    MySQL的用户设置,只需要在MySQL数据库中的user表中添加新用户即可。(use mysql; insert into user() values())。
    
    USE  数据库名 :选择要操作的MySQL数据库,使用该命令后所有MySQL命令都只针对该数据库。
    SHOW DATABASES :列出MySQL数据库管理系统的数据库列表。
    SHOW TABLES :显示指定数据库的所有表,使用该命令前需要使用use命令来选择要操作的数据库。
    SHOW COLUMNS FROM 数据表名 :显示数据表的属性,属性类型,主键信息,是否NULL,默认值等其它信息。
    SHOW INDEX FROM 数据表名 : 显示数据表的详细索引信息,包括 PRIMARY KEY(主键)。
    SHOW TABLE STATUS FROM 数据库名 LIKE ‘pattern’\G  :该命令输出MySQL数据库管理系统的性能及统计信息。
    

    2. MySQL的基本命令

    CREATE DATABASE 数据库名:创建数据库。(或者使用root权限创建:mysqladmin -u root -p create数据库名)。
    DROP DATABASE 数据库名:删除数据库。(或者使用root权限删除:mysqladmin -u root -p drop 数据库名)。
    
    SHOW CREATE TABLE 数据库名:获取创建数据表(CREATE TABLE)语句,该语句包含了原数据表的结构、索引。
    

    创建数据表

    CREATE TABLE tb_name (column_name column_type);
    例:
    create tale tb_student(
        s_id  int  PRIMARY KEY  AUTO_INCREMENT,
        s_name varchar(20)  NOT NULL,
        s_sex varchar(10),
        s_age int
    )ENGINE=InnoDB  DEFAULT CHARSET=utf8;
    
    NOT NULL :在操作数据库时如果输入该字段的数据为NULL,则报错。
    AUTO_INCREMENT :定义列为自增的属性,一般用于主键,数值会自动加1。
    PRIMARY_KEY :定义列为主键,(也可以使用多列定义主键,以逗号分隔)(PRIMARY_KEY s_id, s_sex)。
    ENGINE :设置存储引擎,不定义则使用默认存储引擎(MySQL默认存储引擎是 InnoDB)。
    CHARSET :设置编码格式,默认是UTF-8。
    

    删除数据表

    DROP TABLE tb_name;
    

    删除表中字段

    ALTER TABLE tb_name DROP column;
    

    向表中添加字段

    ALTER TABLE tb_name ADD column 类型;          # 添加到最后一列。
    ALTER TABLE tb_name ADD column 类型 FIRST;    # 添加到第一列。
    ALTER TABLE tb_name ADD column 类型 AFTER column;  # 添加到某个字段之后。
    

    修改字段类型及名称

    ALTER TABLE tb_name MODIFY column 类型;    # 使用MODIFY修改。
    ALTER TABLE tb_name CHANGE old_column new_column 类型;  # 使用CHANGE修改。
    ALTER TABLE tb_name MODIFY column 类型 NOT NULL DEFAULT 100; # 指定字段非空,默认值100。
    

    修改字段默认值

    ALTER TABLE tb_name ALTER column SET DEFAULT 1000;  # 修改字段默认值。
    ALTER TABLE tb_name ALTER column DROP DEFAULT;     # 删除字段默认值。
    

    修改表名

    ALTER TABLE old_tb_name RENAME TO new_tb_name;
    

    修改存储引擎

    ALTER TABLE tb_name engine=InnoDB;
    

    修改外键约束

    ALTER TABLE tb_name DROP FOREIGN KEY keyname;
    

    插入数据

    INSERT INTO table_name (field1, field2, ……, fieldN)  VALUES (value1, value2, ……, valueN);
    

    更新数据

    UPDATE tb_name SET field1=new-value1, field2=new-value2 WHERE … ;
    WHERE子句中可以指定任何条件。
    

    删除数据

    DELETE FROM table_name WHERE … ;
    WHERE 子句中可以指定任何条件。
    

    查询数据

    SELECT column_name1, column_name2 FROM table_name1, table_name2 
    WHERE [condition1 [AND [OR]] condition2 …… ;
    
    查询语句中可以使用一个或者多个表,表之间使用逗号(,)分割,并使用WHERE语句设定查询条件。
    WHERE 子句中可以指定任何条件。
    可以使用AND或者OR指定一个或多个条件。
    可以使用LIKE子句代替等号(=),LIKE通常与(%)一同使用,类似于一个元字符的搜索(LIKE“ACC%”)。
    WHERE子句也可以运用SQL的DELETE或UPDATE命令。
    可以使用LIMIT属性来设定返回的记录数。
    可以使用OFFSET指定SELECT语句开始查询的数据偏移量,默认偏移量为0。
    WHERE子句类似于程序语言中的if条件,根据MySQL表中的字段值来读取指定的数据。
    
    WHERE子句操作符:
    =        :等于
    <>, !=   :不等于
    >        :大于
    <        :小于
    >=      :大于等于
    <=      :小于等于
    

    UNION操作符

    UNION操作符用于连接两个以上的SELECT语句的结果组合到一个结果集合中。
    多个SELECT语句会删除重复的数据。
    
    SELECT expression1, expression2, ……, expressionN FROM tb_name1 WHERE ……
    UNION  [ ALL | DISTINCT ]
    SELECT expression1, expression2, ……, expressionN FROM tb_name2 WHERE ……;
    
    ALL :可选项,返回所有结果集,包含重复数据。
    DISTINCT :可选项,删除结果集中重复的数据。
    

    排序

    ORDER BY :对查询结果进行排序。
    SELECT column1, column2 FROM tb_name WHERE …… ORDER BY field1 [ASC/DESC],field2 [ASC/DESC]。
    
    ASC/DESC :升序/降序,默认ASC。
    

    分组

    GROUP BY :对查询结果进行分组。
    SELECT column1, column2 FROM tb_name WHERE …… GROUP BY column1, column2。
    

    连接

    JOIN  :在两个或多个表中查询数据。
    
    INNER JOIN(内连接,或等值连接):获取两个表中字段匹配关系的记录。
     内连接使用比较运算符对两个表中的数据进行比较,并列出与连接条件匹配的数据行,组合成新的记录,结果只保留满足条件的记录。
     SELECT column1, … FROM tb_name1 INNER JOIN tb_name2 ON tb_name1.id = tb_name2.id;
    
    LEFT JOIN(左连接):获取左表所有记录,即使右表没有对应匹配的记录。
    左表保持不动,右表在右侧滑动,用右表匹配左表,结果保留左表的所有行,右表中不匹配的行默认填充为空值NULL。
    SELECT column1, … FROM tb_name1 LEFT JOIN tb_name2 ON tb_name1.id=tb_name2.id;
    
    RIGHT JOIN(右连接):获取右表所有记录,即使左表没有对应匹配的记录。
    右表保持不动,左表在左侧滑动,用左表匹配右表,结果保留右表的所有行,左表中不匹配的行默认填充为空值NULL。
    SELECT column1, … FROM tb_name1 RIGHT JOIN tb_name2 ON tb_name1.id=tb_name2.id;
    

    空值

    IS NULL :当列的值是NULL,此运算符返回true。
    IS NOT NULL :当列的值不为NULL,此运算符返回true。
    <=> :比较操作符(不同于 = 运算符) :当比较的两个值相等或者都为NULL时,返回true。
    

    3. MySQL数据类型

    数值类型

    MySQL中支持所有标准SQL数值数据类型。
    包括严格数值数据类型(INTEGER,SMALLINT,DECIMAL,NUMERIC)。
    以及近似数值数据类型(FLOAT,REAL,DOUBLE PRECISION)。
    

    日期和时间类型

    表示时间值的日期和时间类型(DATETIME,DATE,TIMESTAMP,TIME,YEAR)。
    每个时间类型有一个有效值范围和一个“零”值,当指定不合法的MySQL不能表示的值时使用“零”值。
    

    字符串类型

    字符串类型(CHAR,VARCHAR,BINARY,VARBINARY,BLOB,TEXT,ENUM,SET)。
    

    4. MySQL索引

    索引是应用在SQL查询语句的条件,一般作为WHERE子句的条件。
    
    主键索引:数据列不允许重复,不允许为NULL,一个表只能有一个主键。
    普通索引:基本的索引类型,没有唯一性限制,允许为NULL值。
    唯一索引:索引列的值必须唯一,但允许有空值;如果是组合索引,则列值的组合必须唯一。
    前缀索引:用列的前缀代替整个列作为索引key,比如:like‘xxx%’。
    Hash索引:采用一定的哈希算法,把键值换算成新的哈希值,只需一次哈希算法即可定位到相应的位置,查询速度非常快。
    

    创建索引的三种方式

    CREATE INDEX indexName ON tb_name(column(length));   # 直接创建索引。
    ALTER TABLE tb_name ADD INDEX indexName(column);    # 修改表结构时添加索引。
    CREATE TABLE tb_name(                                  # 创建表的时候直接指定。
        id int NOT NULL,
        username VARCHAR(16) NOT NULL,
        INDEX  [indexName] (username(length))
    );
    

    创建唯一索引的三种方式

    CREATE UNIQUE INDEX indexName ON tb_name(column(length));    # 直接创建索引。
    ALTER TABLE tb_name ADD UNIQUE [indexName] (column(length));  # 修改表结构时创建索引。
    CREATE TABLE tb_name(                                             # 创建表时添加索引。
        id int NOT NULL,
        username VARCHAR(16) NOT NULL,
        UNIQUE [indexName] (username(length))
    );
    

    删除索引

    DROP INDEX [indexName] ON tb_name;
    

    使用ALTER命令添加、删除索引

    ALTER TABLE tb_name ADD PRIMARY KEY (column);   # 该语句添加一个主键,索引值必须是唯一的,且不能为NULL。
    ALTER TABLE tb_name ADD UNIQUE indexName (column);  # 该语句创建索引的值必须是唯一的(除NULL外)。
    ALTER TABLE tb_name ADD INDEX indexName (column);     # 添加普通索引,索引值可出现多次。
    ALTER TABLE tb_name ADD FULLTEXT indexName (column);  # 该语句指定索引为FULLTEXT,用于全文索引。
    ALTER TABLE tb_name DROP INDEX column;   # 删除索引。
    ALTER TABLE tb_name ADD PRIMARY KEY (column);   # 添加主键。
    ALTER TABLE tb_name DROP PRIMARY KEY;      # 删除主键。
    
    SHOW INDEX FROM tb_name;    # 显示索引信息。
    

    explain

    查看执行计划,使用explain关键字可以模拟优化器执行SQL语句,查看使用到的索引列及其它信息。
    explain select * from tb_name;
    

    5. 索引使用策略及优化

    最常用的索引底层存储结构是棵 B- Tree 或 B+ Tree。
    

    索引有两种:单列索引 和 组合索引

    单列索引:一个索引只包含单个列,一个表可以有多个单列索引。
    组合索引:一个索引包含多个列。
    
    单列索引:一个索引只能有一个字段。
    组合索引:也称复合索引,相对于单列索引,组合索引可以为多个字段创建一个索引。
    

    最左前缀原理

    最左匹配就是最左边优先;创建组合索引时,要根据业务要求,where子句中使用最频繁的一列放在最左边。
    组合索引的查找是先根据第一个字段查,然后再根据第二个字段查,或者只根据第一个字段查,但是不能跳过第一个字段、直接从第二个字段开始查,这就是所谓的最左前缀原理。
    
    第一个字段是有序的。
    当第一个字段值相等的时候,第二个字段也是有序的。
    当第一个字段值相等、第二个字段值也相等时,第三个字段也是有序的。
    
    例:在字段 a, b, c上创建一个联合索引,索引顺序会首先按照a字段排序,然后再按照b字段排序,最后是c字段。
    下面的SQL语句是按照((a),(a, b),(a, b, c))的顺序用到索引。
    select * from tb_name where a = 0;
    select * from tb_name where a = 0 and b = 1;
    select * from tb_name where a = 0 and b = 1 and c = 2;
    
    下面的SQL语句只用到一个索引a 。
    select * from tb_name where a = 0 and c = 2;
    
    下面的SQL语句未使用到索引,因未遵循最左匹配原理。
    select * from tb_name where b = 1 and c = 2;
    
    以MySQL为例,下面的SQL语句也能使用到索引,查询优化器会重新编译,不建议这样使用。
    select * from tb_name where b = 1 and c = 2 and a = 0;
    

    索引优化

    1. 主键、外键要建索引。
    2. 对where, on, group by, order by中出现的列使用索引。
    3. 最左匹配原则(重中之重)。
    4. 尽量扩展索引;例:已经有a字段索引,现在要使用(a, b)字段的索引,只需修改原来的索引即可。
    5. 不要过多创建索引,索引过多会影响插入、删除数据的速度。
    6. 对于like查询,“%”不要放在前面。
    7. where条件数据类型不匹配也无法使用索引。
    8. 为较长的字符串使用前缀索引。
    9. 对索引列进行函数运算时索引也会失效。
    

    6. 聚集索引 与 非聚集索引

    聚集索引 与 非聚集索引底层引用的都是 B+ 树索引。
    

    聚集索引(clustered)

    也叫聚簇索引,数据行的物理顺序与列值(主键)的逻辑顺序相同,一个表中只能拥有一个聚集索引。
    

    非聚集索引(unclustered)

    该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同,一个表中可以拥有多个非聚集索引。
    

    7. MySQL数据导入、导出(导入、导出文件需用户有FILE权限)

    常用导出数据语句

    SELECT … INTO OUTFILE
    将一个数据库的数据写入一个文件,输出不能是一个已存在的文件。
    SELECT * FROM tb_name INTO OUTFILE ‘/tmp/tmp.txt’;
    
    LOAD DATA INFILE
    将文件读回数据库。
    

    常用导入数据语句

    mysql命令导入
    mysql  -u用户名  -p密码   <   要导入的数据库数据(tmp.sql)
    例:mysql -uroot -p123456 < tmp.sql     # 将备份的整个数据库tmp.sql导入
    
    source 命令导入
    create database abc;           # 创建数据库。
    use abc;                       # 使用已创建的数据库。
    set names utf8;                # 设置编码。
    source  /home/abc/abc.sql;    # 导入备份数据库。
    
    LOAD DATA命令导入
    LOAD DATA LOCAL INFILE ‘tmp.txt’ INTO TABLE myTable
    FIELDS TERMINATED BY ‘:’
    LINES TERMINATED BY ‘\r\n’;     # 将tmp.txt文件中的数据导入myTable表中
    
    如果指定LOCAL关键词,则表明从客户主机上按路径读取文件。
    如果没有指定,则文件在服务器上按路径读取文件。
    

    二、数据库事务与并发性:

    A.数据库事务

    在MySQL中,只有使用了Innodb引擎的数据库或表才支持事务。
    

    1. 数据库事务定义

    事务处理可以用来维护数据库的完整性,保证SQL语句要么全部执行,要么全部不执行。
    事务用来管理 insert, update, delete 语句。
    

    1)原子性(Atomicity, 或称不可分割性)

    一个事务中的操作,要么全部完成,要么全部不完成,不会结束在中间某个环节。
    事务在执行过程中发生错误,会被回滚到事务开始前的状态。
    

    2)一致性(Consistency)

    在事务开始之前和事务结束之后,数据库的完整性没有被破坏。
    这表示写入的资料必须完全符合所有的预设规则。
    

    3)隔离性(Isolation,又称独立性)

    数据库允许多个并发事务同时对其数据进行读写和修改的能力。
    隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
    

    4)持久性(Durability)

    事务处理结束后,对数据的修改是永久的,即便系统故障也不会丢失。
    

    2. 事务控制语句

    MySQL数据库事务默认都是自动提交的,即执行完SQL语句后就会马上执行COMMIT操作。
    显示地开启、提交事务
    
    BEGIN / START TRANSACTION : 开启一个事务。
    COMMIT / COMMIT WORK :提交事务,使已对数据库进行的所有修改成为永久性的。
    ROLLBACK / ROLLBACK WORK :回滚事务,撤销正在进行的所有未提交的修改。
    
    SAVEPOINT identifier :允许在事务中创建一个保存点,一个事务中可以有多个SAVEPOINT。
    RELEASE SAVEPOINT identifier :删除一个事务的保存点,当没有指定的保存点时,执行该语句会抛出一个异常。
    ROLLBACK TO identifier :把事务回滚到标记点。
    SAVEPOINT 是在数据库事务处理中实现“子事务”,也称嵌套事务的方法。
    
    事务可以回滚到SAVEPOINT而不影响SAVEPOINT创建前的变化,不需要放弃整个事务。
    SAVEPOINT savepoint_name;     # 声明一个savepoint 。
    ROLLBACK TO savepoint_name;   # 回滚到 savepoint 。
    RELEASE SAVEPOINT savepoint_name;   # 删除指定保留点 。
    
    SET TRANSACTION :用来设置事务的隔离级别。
    
    MySQL事务处理的两种方法
    1) 用BEGIN, ROLLBACK, COMMIT实现。
        BEGIN 开始一个事务。
        ROLLBACK 回滚事务。
        COMMIT 提交事务。
    
    2)直接用SET来改变MySQL的自动提交模式
    SET AUTOCOMMIT = 0  :禁止自动提交。
    SET AUTOCOMMIT = 1  :开启自动提交。
    

    B.数据库并发性

    不同的数据库隔离级别不同,使用加锁的方式也不同。
    MySQL支持四种事务隔离级别,默认隔离级别是(RR, Repeatable Read)。
    Oracle 支持两种事务隔离级别(RC 与 Serializable),默认隔离级别是(RC, Read Committed)。
    

    1. 读数据的概念

    1) 脏读(Dirty Reads)

    就是对脏数据的读取,脏数据指的是未提交的数据。
    

    2) 不可重复读(Non-Repeatable Reads)

    一个事务先后读取同一条记录,两次读取的数据不同。
    

    3) 幻读(Phantom Reads)

    一个事务按相同的查询条件重新读取以前检索过的数据,却发现其它事务插入了满足其查询条件的新数据。
    

    例:存在两个事务(T1, T2)同时运行

    T1读取了已经被T2修改但还未提交的字段,由于某种原因,T2事务回滚,则T1读取的内容是临时且无效的;这就是脏读。
    T1读取一个字段,之后T2更新了该字段,T1再次读取该字段值时则读取到的是被T2更新后的新值;这就是不可重复读。
    T1从一个表中读取了一个字段,然后T2在该表中插入了一些新的行,之后T1再次读取该表时会多出几行。这就是幻读。
    

    2. 数据库隔离级别

    1) Read UnCommitted(读未提交数据)

    允许事务读取未被其它事务提交的变更数据。
    会出现脏读、不可重复读和幻读问题,隔离级别最低(读不锁)。
    

    2) Read Committed(读已提交数据)

    允许事务读取已经被其它事务提交的变更数据。
    可避免脏读,扔会出现不可重复读和幻读问题(读锁)。
    

    3) Repeatable Read(可重复读)

    确保事务可以多次从一个字段中读取相同的值。
    在此事务持续期间,禁止其它事务对此字段的更新。
    可以避免脏读和不可重复读,扔会出现幻读问题,RR隔离级别对读取到的记录加锁(写锁)。
    

    4) Serializable(序列化)

    确保事务可以从一个表中读取相同的行,在这个事务持续期间,禁止其它事务对该表执行插入、更新和删除操作。
    可避免所有并发问题,但性能非常低。
    所有的SELECT语句都被隐士的转换成SELECT …… LOCK IN SHARE MODE。
    即读取使用表级共享锁,读写相互都会阻塞,隔离级别最高(表级锁)。
    

    3. 数据库的存储引擎

    常见的数据库存储引擎有:(1)MyISAM    (2)InnoDB 。
    

    1) MyISAM

    支持表级锁。
    适用场景:读多写少,硬件配置不高。
    

    2) InnoDB (MySQL默认存储引擎)

    支持表级、行级(默认)锁。
    适用场景:支持事务、支持外键,即有读又有写的业务中。
    

    4. 数据库锁

    锁主要用于多用户环境下保证数据库完整性和一致性。
    

    1) 锁按使用方式划分

    乐观锁

    每次去拿数据的时候都认为别人不会修改,所以不会上锁。
    但是在更新的时候会判断一下在此期间别人有没有去更新这个数据。
    如果发生冲突了,则返回用户错误信息,让用户决定如何去做。
    大多是基于数据版本(Version)记录机制实现。
    

    悲观锁

    每次去拿数据的时候都认为别人会修改,所以每次拿数据的时候都会上锁。
    

    2) 锁按级别划分

    共享锁

    共享锁(Share Lock),S锁,也叫读锁,用于所有的只读数据操作。
    共享锁是非独占的,允许多个并发事务读取其锁定的资源。
    
    共享锁性质:
    多个事务可封锁同一个共享页。
    任何事务都不能修改该页。
    通常是该页被读取完毕,S锁立即被释放。
    

    排他锁

    排他锁(Exclusive Lock),X锁,也叫写锁,用于对数据进行写操作。
    如果一个事务对对象加了排他锁,其它事务就不能再给它加任何锁了。
    
    排他锁性质
    仅允许一个事务封锁此页。
    其它任何事务必须等到X锁被释放才能对该页进行访问。
    X锁一直到事务结束才能被释放。
    

    3) 锁按粒度划分(MySQL)

    表级锁

    锁的作用范围是整张表。
    开销小、加锁快,不会出现死锁。
    锁定粒度大,发生锁冲突的概率最高,并发度最低。
    

    行级锁

    锁的作用范围是行级别。
    开销大、加锁慢,会出现死锁。
    锁定粒度最小,发生锁冲突的概率最低,并发度最高。
    

    页级锁

    锁的作用范围是整个页面。
    开销和加锁时间界于表锁和行锁之间,会出现死锁,锁定粒度界于表锁和行锁之间,并发度一般。
    

    4) 意向锁

    意向锁是InnoDB自动加的,不需要用户干预。
    

    意向共享锁(Intention Shared Lock)

    表示事务准备给数据行加入共享锁,也就是说一个数据行加共享锁前必须先取得该表的IS锁。
    

    意向排它锁(Intention Exclusive Lock)

    表示事务准备给数据行加入排它锁,也就是说一个数据行加排它锁前必须先取得该表的IX锁。
    

    5. 锁实现方式

    当一个事务获得对一个表的写锁后,只有持有锁的事务可以对表进行更新操作,其它事务的读、写操作都会等待,直到锁被释放为止。
    当一个事务获取对一个表的读锁后,其它事务也可以获取此表的读操作权限,但其它事务不能获取此表的写操作权限,直到锁被释放为止。
    

    1) 行级锁实现方式

    行级锁不是锁记录,而是锁索引;只有通过索引条件检索数据,才能使用行级锁。
    

    隐式加锁

    对于UPDATE、DELETE、INSERT语句,InnoDB会自动给涉及数据集加排他锁。
    对于普通SELECT语句,InnoDB不会加任何锁。
    

    显式加锁

    SELECT * FROM tb_name WHERE …… LOCK IN SHARE MODE;    # 加共享锁。
    SELECT * FROM tb_name WHERE …… FOR UPDATE;              # 加排他锁。
    

    2) 表级锁实现方式

    隐式加锁

    在执行查询语句SELECT前,会自动给涉及的所有表加读锁。
    在执行更新操作UPDATE、DELETE、INSERT前,会自动给涉及的表加写锁。
    

    显式加锁

    LOCK TABLE tb_name WRITE;     # 加写锁
    LOCK TABLE tb_name READ;    # 加读锁
    
    UNLOCK TABLES;     # 释放锁
    

    6. InnoDB锁机制

    1) Record Lock

    行锁,单条索引记录加锁,Record Lock锁住的是索引,而非记录本身。
    

    2) Gap Lock

    间隙锁,在索引记录之间的间隙中加锁,或者是在某一条索引记录之前或者之后加锁,并不包括该索引记录本身。
    Gap Lock 是针对事务隔离级别为RR或以上。
    Gap Lock在InnoDB的唯一作用就是防止其它事务的插入操作,以此防止幻读的发生。
    Gap Lock 一般针对非唯一索引而言。
    

    3) Next-key Lock

    Next-key Lock是Record Lock和Gap Lock的结合,即锁住了记录本身,还要锁住索引之间的间隙。
    MySQL 的事务隔离级别默认是RR,若innodb_locks_unsafe_for_binlog参数为0,默认采用Next-key Lock。
    Next-key Lock是行锁和间隙锁的组合,当InnoDB扫描索引记录的时候,会首先对索引记录加上行锁(Record Lock),再对索引记录两边的间隙加上间隙锁(Gap Lock)。
    
    加上间隙锁之后,其它事务就不能在这个间隙修改或者插入记录。
    

    7. 死锁

    死锁是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,他们都将无法推进下去。
    

    1) 产生死锁的原因

    系统的资源不足。
    代码执行的顺序不合适。
    资源分配不当。
    

    2) 产生死锁的必要条件

    互斥条件:一个资源每次只能被一个进程使用。
    请求与保持条件:一个进程因请求资源而阻塞时,对已获得的资源保持不放。
    不剥夺条件:进程已获得的资源,在未使用完之前,不能强行剥夺。
    循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。
    

    3) 减少死锁发生

    按同一顺序访问对象。
    避免事务中的用户交互。
    保持事务简短并在一个批处理中。
    使用低隔离级别。
    使用绑定连接。
    

    8. MVCC多版本并发控制

    MVCC:Multi-Version Concurrency Control 多版本并发控制。
    MVCC是一种并发控制的方法,一般在数据库管理系统中实现对数据库高并发场景下的吞吐性能。
    

    1) MVCC原理

    MVCC的实现,通过保存数据在某个时间点的快照来实现的。
    在每行记录后面保存两个隐藏的列,一列保存了行的创建时间,另一列保存了行的过期时间(或删除时间)。
    这里存储的时间并不是实际的时间值,而是系统版本号。
    每开始一个新事物,系统版本号都会自动递增。
    事务开始时刻的系统版本号会作为事务的版本号,用来与查询到的每行记录的版本号进行比较。
    

    2) MVCC特征

    每行数据都存在一个版本,每次数据更新时都更新该版本。
    修改时copy出当前版本进行修改,各个事务之间互不干扰。
    保存时比较版本号,如果成功(commit),则覆盖原纪录,失败则放弃copy(rollback)。
    

    3) MVCC实现

    在每一行数据中额外保存两个隐藏的列:(1)DATA_TRX_ID   (2)DATA_ROLL_PTR。
    
    DATA_TRX_ID
    记录最近一次修改(insert / update)本行纪录的事务id,大小为6字节。
    
    DATA_ROLL_PTR
    指向该行回滚段(rollback segment)的undo log record(撤销日志记录)指针,大小为7字节。
    如果一行记录被更新,则undo log record 包含“重建该行记录被更新之前内容”所必须的信息。
    InnoDB便是通过这个指针找到之前版本的数据。
    若该行记录上存储所有的旧版本,在undo中都通过链表的形式组织。
    
    如果表没有主键,则还会有一个隐藏的主键列 DB_ROW_ID。
    
    DB_ROW_ID
    行标识(隐藏单调自增ID),大小为6字节,如果表没有主键,InnoDB会自动生成一个隐藏主键。
    
    例:事务1、事务2, DATA_TRX_ID,   DATA_ROLL_PTR, DB_ROW_ID。
    事务1。
    执行新增一条数据 insert操作。
    此时DB_ROW_ID = 1, DATA_TRX_ID = 1(系统版本号),  DATA_ROLL_PTR = NULL。
    
    事务 2 执行update操作过程。
    对DB_ROW_ID = 1这行记录加排它锁。
    把该行copy前的值拷贝到undo log中。
    修改该行的值,这时会产生一个新版本号,更新DATA_TRX_ID为修改记录的事务ID。
    将DATA_ROLL_PTR指向刚刚copy到undo log 链中的旧版本记录,这样就能通过DATA_ROLL_PTR找到这条记录的历史版本;如果对同一行记录执行连续的UPDATE, undo log会组成一个链表,遍历这个链表可以看到这条记录的变迁。
    记录redo log,包括undo log中的修改。
    

    4) RR隔离级别下,MVCC具体的操作流程。

    SELECT :InnoDB只查找版本早于当前事务版本的数据行;行的删除版本,要么未定义,要么大于当前事务版本号,这样可以确保事务读取到的行在事务开始之前未被删除。
    INSERT :InnoDB为插入的每一行保存当前系统版本号作为行版本号。
    DELETE :InnoDB为删除的每一行保存当前系统版本号作为删除标识,标记为删除、而不是实际删除。
    UPDATE :InnoDB会把原来的行复制一份到回滚段中,保存当前系统版本号作为行版本号,同时,保存当前系统版本号到原来的行作为删除标识。
    

    三、InnoDB引擎的底层实现方式:

    InnoDB核心:(1)日志   (2)内存(缓存池(Buffer Pool))  (3) 磁盘(Datafile)
    InnoDB存储引擎有多个内存块,这些内存块组成了一个大的内存池。
    后台线程主要负责刷新内存池中的数据,将已修改的数据刷新到磁盘。
    
    当某个事务进行一次写操作时,InnoDB引擎将数据写入redo log后就会提交事务。
    而非写入到磁盘(Datafile),之后InnoDB再异步地将新事务的数据异步地写入Datafile,真正存储起来。
    

    A.redo log 和 undo log 和 bin log

    用来恢复事务所对应的脏数据块的日志文件。
    

    1. 前滚 与 回滚

    前滚

    未完全提交的事务,即该事务已经被执行commit命令了。
    该事务所对应的脏数据块中只有一部分被写到磁盘上的数据文件中,一部分还在内存中。
    若此时数据库实例崩溃,就需要用前滚来完成事务的完全提交。
    

    回滚

    未提交的事务,即该事务未被执行commit命令。
    

    2. Redo log

    重做日志,提供前滚操作。
    Redo log 通常是物理日志,记录的是数据页的物理修改,用来恢复提交后的物理数据页。
    恢复数据页、且只能恢复到最后一次提交的位置。
    

    Redo log 包括两部分

    内存中的日志缓冲(redo log buffer),该部分日志是易失性的。
    磁盘上的重做日志文件(redo log file),该部分日志是持久的。
    

    3. Undo log

    回滚日志,提供回滚操作。
    Undo log用来回滚行记录到某个版本。
    undo log一般是逻辑日志,根据每行记录进行记录。
    

    4. Bin log

    二进制日志,记录对数据发生或潜在发生更改的SQL语句,并以二进制的形式保存在磁盘中。
    可以用来查看数据库的变更历史(具体的时间点所做的操作),数据库增量备份和恢复。
    Show variables like %log_bin% ; 
    

    B.InnoDB内存

    1. InnoDB线程

    Master Thread

    最核心的线程,主要负责将缓存池中的数据异步刷新到磁盘,保证数据的一致性。
    

    IO Thread

    IO Thread 主要负责大量的异步IO来处理写IO请求。
    

    Purge Thread

    Purge Thread回收已经使用并分配的undo页,InnoDB支持多个Purge Thread,这样做可以加快undo页的回收。
    

    Page Cleaner Thread

    Page Cleaner Thread是将之前版本中脏页的刷新操作都放入单独的线程中来完成,减轻Master Thread的工作及对于用户查询线程的阻塞。
    

    2. InnoDB内存模型

    InnoDB引擎使用缓存池技术来提高数据库的整体性能。
    InnoDB中缓存池页的大小默认为16KB。
    

    计算机科学中著名的局部性原理

    当一个数据被用到时,其附近的数据也通常会马上被使用,程序运行期间所需要的数据通常比较集中。
    

    内存 与 磁盘

    主存和磁盘是以页为单位交换数据。
    当程序要读取的数据不在主存中时,会触发一个缺页异常。
    此时系统会向磁盘发出读盘信号,磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中。
    然后异常返回,程序继续运行。
    数据库、内存、磁盘
    在数据库中进行读取页的操作时,首先将从磁盘读到的页存放在缓存池中。
    下一次读取相同的页时,首先判断页是不是在缓存池中。
    若在、称该页在缓存池中被命中,直接读取该页。
    否则、读取磁盘上的页。
    
    对于数据库中页的修改操作,首先修改在缓存池中的页,然后再以一定的频率刷新到磁盘。 
    

    3. 缓存池(Buffer Pool)

    为了更好的管理这些被缓存的页,InnoDB为每一个缓存页都创建了一些控制信息(控制块)。
    这些控制信息包括该页所属的表空间编号、页号、页在Buffer Pool中的地址、锁信息、LSN信息等。
    每个缓存页对应一个控制块,每个控制块占用的内存大小是相同的,它们都被放到Buffer Pool中,如下图。

    4. Free List(空闲链表)

    启动MySQL服务器时,需要对Buffer Pool进行初始化,将Buffer Pool划分成若干对控制块和缓存页。
    随着程序的运行,会不断的将磁盘上的页缓存到Buffer Pool中,但如何管理Buffer Pool中空闲的缓存页呢?
    使用Free List(空闲链表)来管理空闲的缓存页,如下图。

    Free List控制信息:包含链表的头结点地址、尾结点地址、以及当前链表中结点的数量。
    每个Free List的结点中都记录了某个缓存页控制块的地址。
    每个缓存页控制块都记录着对应的缓存页地址。
    
    每当需要从磁盘中加载一个页到Buffer Pool中时,就从Free List中取一个空闲的缓存页,并且把该缓存页对应的控制块的信息填上,然后把该缓存页对应的Free List结点从Free List链表中删除。
    

    5. Buffer Pool 清理机制

    缓存命中率:假设一共访问了n次页,那么被访问的页已经在缓存中的次数除以n就是缓存命中率。
    
    InnoDB Buffer Pool采用经典的LRU算法进行页面淘汰,以提高缓存命中率。
    LRU(Least Recently Used):最近最少使用,用来管理缓存池中页的可用性。
    如果该页不在Buffer Pool中,在把该页从磁盘加载到Buffer Pool中的缓存页时,就把该缓存页包装成结点塞到链表的头部。
    如果该页在Buffer Pool中,则直接把该页对应的LRU链表结点移动到链表的头部。
    
    缺点
    若遇一次全表扫描就把热数据给冲完了,就会导致Buffer Pool污染问题,严重的降低了缓存命中率。
    Buffer Pool中的所有数据页都被换了一次血,其它查询语句在执行时又得执行一次从磁盘加载到Buffer Pool的操作。
    

    midpoint insertion stategy

    InnoDB存储引擎对传统的LRU算法做了一些优化。
    在InnoDB中加入了midpoint,新读到的页,虽然是最新访问的页。
    但并不直接插入到LRU列表的首部,而是插入到LRU列表的midpoint位置。
    默认配置插入到列表长度的 5/8 处,midpoint由参数innodb_old_blocks_pct控制。
    
    Midpoint之前的列表称之为new列表,之后的列表称之为old列表。
    可以简单的将new列表中的页理解为最活跃的热点数据。
    
    InnoDB存储引擎还引入了 innodb_old_blocks_time 来表示页读取到mid位置之后需要等待多久才会被加入到LRU列表的热端,可以通过设置该参数保证热点数据不轻易被刷出。
    
    

    6. FLUSH链表(Flush List)

    FLUSH链表用来管理将页刷新回磁盘,缓存池中通过FLUSH 链表存储需要被刷新到磁盘上的页(脏页)。
    这里的脏页指的是此页被加载进Buffer Pool后第一次修改后的页。
    只有第一次修改时才需要加入FLUSH链表(第二次修改时已经存在了)。
    

    7. LRU List, Free List, Flush List 三者关系

    8. Checkpoint 技术

    1) 缩短数据库恢复时间。

    redo log中记录了Checkpoint的位置。
    这个点之前的页已经被刷新回磁盘,只需要对Checkpoint之后的redo log进行恢复。
    

    2) 缓存池不够用时,刷新脏页。

    根据LRU算法,溢出最近最少使用页。
    如果页为脏页,强制执行Checkpoint,将脏页刷新回磁盘。
    

    3) Redo log不可用时,刷新脏页。

    由于redo log是循环使用的,这部分对应的数据还未刷新到磁盘。
    数据库恢复时,如果不需要这部分日志即可被覆盖。
    如果需要,必须强制执行Checkpoint,将缓存池中的页至少刷新到当前重做日志的位置。
    

    InnoDB存储引擎内部,有两种Checkpoint

    Sharp Checkpoint(默认, innodb_fast_shutdown=1)。

    Sharp Checkpoint发生在数据库关闭时,将所有的脏页都刷新回磁盘。
    缺点:不适用于数据库运行时的刷新。
    

    Fuzzy Checkpoint。

    Fuzzy Checkpoint适用于数据库运行时刷新脏页,只刷新一部分脏页。
    MasterThread Checkpoint
    异步刷新,每秒或每10秒从缓存池脏页列表刷新一定比例的页回磁盘。
    
    FLUSH_LRU_LIST Checkpoint
    若Buffer Pool中没有足够的空间时,根据LRU算法、溢出LRU列表尾端的页。
    如果这些页有脏页,需要进行Checkpoint(Page Cleaner Thread线程就是做这个事的)。
    InnoDB存储引擎需要保证LRU列表中差不多有100个空闲页可供使用。
    Innodb_lru_scan_dept :控制LRU列表中可用页的数量,默认1024。
    
    Asnc / Sync Flush Checkpoint
    指重做日志不可用时,需要强制刷新页回磁盘。
    此时的页是脏页列表(FLUSH LIST)中选取的。
    

    LSN

    事务日志中每条记录的编号。
    InnoDB存储引擎,通过LSN(Log Sequence Number)来标记版本,LSN是8字节的数字。
    
    redo_lsn :写入日志的 LSN。
    Checkpoint_lsn :刷新回磁盘的最新页 LSN。
    
    Checkpoint_age = redo_lsn – checkpoint_lsn。
    Async_water_mark = 75% * total_redo_file_size。
    Sync_water_mark = 90% * total_redo_file_size。
    

    Dirty Page too much Checkpoint

    即脏页太多,强制checkpoint,保证缓存池中有足够可用的页。
    参数设置:innodb_max_dirty_pages_pct = 75。
    表示:当缓存池中脏页的数量占75%时,强制checkpoint。1.0x之后默认75。

    C.InnoDB关键特性

    1. 插入缓存(Insert Buffer)

    Insert Buffer的设计,对于非聚集索引的插入和更新操作,不是每次都直接插入到索引页中。
    而是先判断插入非聚集索引页是否在缓存池中。
    若存在则直接插入,若不存在则先放入一个Insert Buffer对象中。
    
    数据库这个非聚集的索引并没有插入到叶子结点(因为B+树只有叶子结点才存储数据),而是存放在另一个位置。
    然后再以一定的频率和情况进行Insert Buffer和辅助索引页子结点的merge(合并)操作。
    这时通常能将多个插入合并到一个操作中(因为在一个索引页中),这就大大提高了对于非聚集索引插入的性能。
    

    使用Insert Buffer需要满足两个条件

    1) 索引是辅助索引。
    2) 索引不是唯一的。
    

    2. 两次写操作

    假设有这样一个场景,当数据库正在从内存向磁盘写一个数据页时,数据库宕机。
    从而导致这个页只写了部分数据,这就是部分写失效,它会导致数据丢失。
    这时是无法通过重做日志(redo log)恢复的。
    因为重做日志记录是对页的物理修改,如果页本身已损坏,重做日志也无能为力。
    
    如何解决以上问题 ?
    为了解决以上问题,可以使用两次写操作。
    因为在磁盘共享表空间中已有数据页副本拷贝,如果数据库在页写入数据文件的过程中宕机。
    在实例恢复时,可以从共享表空间中找到该页副本,将其拷贝覆盖原有的数据页,再应用重做日志即可。
    
    两次写原理。
    1) 当刷新缓存池脏页时,并不直接写到数据文件中,而是先拷贝至内存中的两次写缓存区。
    2) 接着从两次写缓存区分两次写入磁盘共享表空间中,每次写入1MB。
    3) 待第2步完成后,再将两次写缓存区写入数据文件。
    
    两次写需要额外添加两个部分。
    1) 内存中的两次写缓存(doublewrite buffer),大小为2MB。
    2) 磁盘上共享表空间中连续的128页,大小也为2MB。
    
    InnoDB默认开启两次写功能,可以通过skip_innodb_doublewrite禁用两次写功能。

     

    展开全文
  • mysqldump:其特征之一是在处理过程中需要对列表加以锁定,因此... 代码如下:1:轻量级C语言写的 2:执行速度比mysqldump快10倍 3:事务性和非事务性表一致的快照(适用于0.2.2以上版本) 4:快速的文件压缩 5:支持
  • 查看MySQL数据库占用空间大小

    千次阅读 2019-05-16 16:35:22
    MySQL中查询所有数据库占用磁盘空间大小和单个库中所有表的大小的sql语句 查询所有数据库占用磁盘空间大小的SQL语句: select TABLE_SCHEMA, concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size...
  • Mysql数据库热备份

    2019-12-24 10:18:18
    最近要对Mysql数据库进行备份,然后发现我服务器上的数据库被黑了,一直没有发现.还要给我要0.03个比特币哈哈哈哈~ 当然我数据库没啥重要的东西,并且有快照,所以大家还是要注意开放的端口,别丢失了数据. 2. 备份 .....
  • mysql数据库简介和部署

    千次阅读 2020-09-14 20:29:23
    mysql数据库简介和部署 一、 前言简介 1. 数据库能做什么? 不论是淘宝,吃鸡,爱奇艺,抖音,快手,知乎,百度贴吧等,总所周知的服务程序。存储的数据,包含用户的账号,密码,级别,存款,余额,等级,购物记录,...
  • mysql数据库优化大全

    万次阅读 多人点赞 2018-01-14 00:16:34
    数据库优化 sql语句优化 索引优化 加缓存 读写分离 分区 分布式数据库(垂直切分) 水平切分 MyISAM和InnoDB的区别: 1. InnoDB支持事务,MyISAM不支持,对于InnoDB每一条SQL语言都默认封装成事务,自动...
  • 之前三篇文章分别介绍了MySQL数据库常见的备份方法,其中包括逻辑备份和物理备份,本篇将总结一下MySQL数据库的数据恢复相关内容。这些数据恢复方案在之前备份内容介绍时,此处总结一下恢复方案,并结合数据库的二...
  • 快照方式备份MySQL数据库及举例 作者:红豆殺日期: 2011 年 03 月 17 日发表评论7条评论查看评论 一、创建逻辑卷 依照如下连接的文档创建一个逻辑卷 http://www.178-go.com/tags/lvm 要求:物理卷20G;卷组名...
  • 测试面试题集-MySQL数据库灵魂拷问

    千次阅读 2020-09-25 08:30:08
    关于在MySQL中InnoDB引擎是如何解决幻读,一张图甚过千言万语: 综上,高并发数据库系统中,为保证事务与事务之间隔离性和数据一致性,MySQL InnoDB引擎默认是RR的隔离级别,在MySQL 中通过MVCC快照读和next-key(当前读...
  • MySQL数据库详解

    千次阅读 2018-05-10 17:21:12
    1、MySQL存储引擎分为两种:MyISAM和InnoDB,MySQL默认的存储引擎是MyISAM,其他常用的就是InnoDB,InnoDB比较常用。区别:存储结构:MyISAM:每张表存在三个文件中,xxx.frm文件存储表定义;xxx.MYD文件存储表数据...
  • 在我们后端写项目时,数据一般都是保存在MySQL数据库里。但是有时我们不能只是简单的把数据保存在MySQL数据库里(删库跑路)。在这里我们就要经常备份一下我们的数据库或者是数据库里面的某一个表。 Navicat 在这里...
  • 在虚拟机中安装mysql数据库

    千次阅读 2020-04-18 12:37:00
    2、 运行 systemctl start mariadb启动数据库 3、运行 mysql_secure_installation 直接输入mysql_secure_installation命令,设置root密码,删除匿名用户等 如果有看不懂的可以百度翻译一下,选择。 运行命令后第一...
  • MySQL数据库的锁 --- 六种分类 - 14种锁详细介绍

    千次阅读 热门讨论 2020-12-04 13:47:42
    MySQL锁概述 锁是计算机协调多个进程或线程并发访问某一个资源的机制,在数据库中,除传统的计算资源(CPU、RAM、I/O)的争用以外,数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所在...
  • 作者:不剪发的Tony老师blog.csdn.net/horses/article/details/108503541数据库建模和设计是软件开发过程中必不可少的步骤,一个良好的建模工具可...
  • MySQL数据库面试题(2020最全最新)

    千次阅读 2020-03-23 14:54:53
    MySQL数据库面试题(2020最新版) 最近看到这篇文章作者写的很不错,总结的很不错希望能够帮到更多的人 原创作者 ThinkWon 原文链接:https://blog.csdn.net/ThinkWon/article/details/104778621) ...
  • 8 款免费的 MySQL 数据库建模工具

    千次阅读 多人点赞 2020-09-09 22:14:25
    数据库建模和设计是软件开发过程中必不可少的...因此,今天给大家推荐几款免费的 MySQL 数据库建模工具,包括 MySQL Workbench、SQL Power Architect、PDMan、RISE、GenMyModel、DB Designer、dbdiagram.io、Freedgo。
  • 关闭mysql服务 # service mysqld stop 一、创建逻辑卷 依照如下连接的文档创建一个逻辑卷 http://www.178-go.com/archives/43 要求:物理卷20G;卷组名为vg0;逻辑卷2G,逻辑卷名为mydata 创建逻辑卷挂载点...
  • MySQL数据库 资源

    千次阅读 2018-05-22 16:53:00
    2019独角兽企业重金招聘...荐 : http://blog.51cto.com/sery/2131257 Linux下如何实现MySQL数据库每天自动备份定时备份 : https://mp.weixin.qq.com/s/G2-GXwj5o9dnjPTBt-BLdg  十个MySQL 数据库经典错误 : ...
  • 1,数据库备份种类 按照数据库大小备份,有四种类型,分别应用于不同场合,下面简要介绍一下: 1.1完全备份 这是大多数人常用的方式,它可以备份整个数据库,包含用户表、系统表、索引、视图和存储过程等所有...
  • MySQL数据库】笔试题总结

    万次阅读 多人点赞 2018-09-05 14:11:06
    一般数据库的默认隔离级别要么是读已提交,要么是可重复读(例如MySQL的InnoDB引擎),要么是读已提交(例如Oracle )。 如果使用行级读锁、写锁来实现读已提交或可重复读,应当是以下的步骤: 1、事务1会修改...
  • 最近一直在研究数据库同步的问题,在网上查了很多资料,也请教了很多人,找到了一种通过快照复制的方法。研究了一番后发现之前就是用的这个方法,效果不是很好,果断放弃。经过了一番寻觅和他人指点,最后从一位热心...
  • 使用mysqldump进行全量的mysql数据库备份一丶前言二丶知识储备三丶shell脚本四丶定时任务五丶恢复脚本 二丶知识储备 分析一下实现这个功能需要什么: 可以实现数据库备份的shell脚本 定时执行备份脚本 恢复脚本 三...
  • 一、MySQL的安装 1.在线安装: 命令:sudo apt-get install mysql-server 在安装的过程中将提示为“root”用户设置密码,输入自己的密码即可,安装按成后已自动配置好环境变量,可直接使用mysql命令。 2.下载...
  • 导读:本文介绍MySQL数据库备份之主从同步配置,通过将腾讯云服务器上的MySQL备份到本地服务器中这个例子来做出展开解释。 主从同步意义? 主从同步使得数据可以从一个数据库服务器复制到其他服务器上,在复制...
  • 新建一个lvm磁盘,这里我建的lv为mydatalv,挂载到了/data下 [root@localhost ~]# lvs LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert mydatalv mydata -wi-ao--...将原数据库文件复制到/data

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 43,469
精华内容 17,387
关键字:

mysql数据库快照

mysql 订阅