事务

1.什么是事务
 

事务（Transaction）是指数据库管理系统（DBMS）中的一个操作单位，它由一个或多个数据库操作（例如插入、更新、删除等）组成。事务的目的是确保数据库的一致性、完整性和持久性。

事务具有以下四个特性，通常被称为 ACID 特性：

  1. 原子性（Atomicity）： 事务被视为一个不可分割的原子操作。它要么完全执行，要么完全不执行。如果在事务执行过程中发生故障，系统将回滚事务，使数据回到事务开始前的状态。

  2. 一致性（Consistency）： 事务的执行使数据库从一个一致性状态转换到另一个一致性状态。在事务开始和结束时，数据库必须满足特定的一致性规则，以保证数据的有效性和完整性。

  3. 隔离性（Isolation）： 并发执行的多个事务之间应该相互隔离，以防止互相干扰。一个事务在提交之前，其所做的修改对其他事务是不可见的，从而避免了数据的混乱状态。

  4. 持久性（Durability）： 一旦事务提交，其对数据库的修改将会被永久保存，即使在系统故障的情况下也不会丢失。系统需要将事务的修改记录在持久存储介质中，如硬盘。

通过保持事务的 ACID 特性，数据库可以确保在任何情况下都能维护数据的完整性和一致性。例如，当多个用户同时访问数据库时，事务可以防止数据损坏或丢失。

2.本地事务
 

本地事务（Local Transaction）是指在单个数据库上执行的事务，而不涉及跨多个数据库或系统的操作。本地事务 是指仅操作单一事务资源的、不需要全局事务管理器进行协调的事务。适用于单个服务使用单个数据源的场景。

本地事务中原子性和持久性的实现

由于内存数据写入磁盘数据不是原子的，中间可能会有系统崩溃或数据库崩溃的情况，无法预知崩溃的情况， 所以只能在发送故障前记录对数据的操作，这样在故障发生后才可以恢复数据或执行回滚操作。这个记录就是提交日志Commit Logging 。

Commit Logging 保障数据持久性、原子性的原理并不难理解：首先，日志一旦成功写入 C ommit Record，那整个事务就是成功的，即使真正修改数据时崩溃了，重启后根据已经写 入磁盘的日志信息恢复现场、继续修改数据即可，这保证了持久性；其次，如果日志没有 成功写入 Commit Record 就发生崩溃，那整个事务就是失败的，系统重启后会看到一部分 没有 Commit Record 的日志，那将这部分日志标记为回滚状态即可，整个事务就像完全没 好有发生过一样，这保证了原子性。

提交日志演变未两种日志

Undo Log,RedoLog 。 当变动数据写入磁盘前，必须先记录 Undo Log，注明修 改了哪个位置的数据、从什么值改成什么值，等等。以便在事务回滚或者崩溃恢复时根据

Undo Log 对提前写入的数据变动进行擦除。Undo Log 现在一般被翻译为“回滚日志”，此 前记录的用于崩溃恢复时重演数据变动的日志就相应被命名为 Redo Log，一般翻译为“重 做日志”。 日志的查找

  • 分析阶段（Analysis）：该阶段从最后一次检查点（Checkpoint，可理解为在这个点之 前所有应该持久化的变动都已安全落盘）开始扫描日志，找出所有没有 End Record 的 事务，组成待恢复的事务集合，这个集合至少会包括 Transaction Table 和 Dirty Page Ta ble 两个组成部分。
  • 重做阶段（Redo）：该阶段依据分析阶段中产生的待恢复的事务集合来重演历史（Rep eat History），具体操作为：找出所有包含 Commit Record 的日志，将这些日志修改的 数据写入磁盘，写入完成后在日志中增加一条 End Record，然后移除出待恢复事务集 合。
  • 回滚阶段（Undo）：该阶段处理经过分析、重做阶段后剩余的恢复事务集合，此时剩 下的都是需要回滚的事务，它们被称为 Loser，根据 Undo Log 中的信息，将已经提前 写入磁盘的信息重新改写回去，以达到回滚这些 Loser 事务的目的。

本地事务隔离性的实现

隔离性就需要通过锁来实现了。隔离性保证了每个事务各自读、写的数据互 相独立，不会彼此影响。 数据库中的三种锁

  • 写锁（Write Lock，也叫作排他锁，eXclusive Lock，简写为 X-Lock）：如果数据有加写 锁，就只有持有写锁的事务才能对数据进行写入操作，数据加持着写锁时，其他事务不 能写入数据，也不能施加读锁。
  • 读锁（Read Lock，也叫作共享锁，Shared Lock，简写为 S-Lock）：多个事务可以对同 一个数据添加多个读锁，数据被加上读锁后就不能再被加上写锁，所以其他事务不能对 该数据进行写入，但仍然可以读取。对于持有读锁的事务，如果该数据只有它自己一个 事务加了读锁，允许直接将其升级为写锁，然后写入数据。
  • 范围锁（Range Lock）：对于某个范围直接加排他锁，在这个范围内的数据不能被写 入。

事务的隔离级别 有

  1. 读未提交（Read Uncommitted）： 这是最低的隔离级别。在该级别下，一个事务可以读取另一个事务尚未提交的数据。这可能导致脏读（Dirty Read），即读取了尚未确认的数据，而这些数据可能在事务回滚时被撤销。

  2. 读已提交（Read Committed）： 在这个级别下，一个事务只能读取已经提交的数据。这避免了脏读，但可能导致不可重复读（Non-repeatable Read），即在同一个事务中，对同一数据进行多次读取，但得到的结果可能不同。。因为读提交隔离级别下，查询完之后，读锁会立刻释放。

  3. 可重复读（Repeatable Read）： 在这个级别下，一个事务在其生命周期内多次读取同一数据，得到的结果应该是一致的。其他事务不能修改被读取的数据，但可能会插入新数据。这避免了脏读和不可重复读，但可能导致幻读（Phantom Read），即一个事务在多次查询中看到了不同数量的行。 但是在mysql中可重复读总一个事务并不会读取到另一个事务以提交的事务，这是因为下面要说的MVCC的存在。

  4. 串行化（Serializable）： 这是最高的隔离级别，也是最严格的。在该级别下，事务被顺序执行，一个事务的操作完全独立于其他事务。这避免了脏读、不可重复读和幻读，但可能会影响系统的并发性能。

 

脏读和幻读的区别

  • 脏读是指一个事务读取了另一个事务尚未提交的数据，可能导致事务基于错误的数据做出决策。
  • 幻读是指在同一个事务中，两次相同的查询由于其他事务的插入、更新或删除操作而返回了不同的结果。
  • 脏读也可以认为是幻读的一种情况，因为也可能读取到其他事务插入还未提交的数据，从这个意思上来说也是幻读。脏读会在错的数据上做修改，而幻读是已提交的数据，可能导致插入或更新时的错误。

mvcc


MVCC通过维护数据的多个版本来解决这些问题。每个事务都可以看到数据的一个特定版本，这个版本是在事务开始时数据库中存在的。当事务进行修改时，它实际上是创建了一个新版本的数据，而不是直接在现有数据上进行修改。这意味着其他事务仍然可以访问旧版本的数据，从而避免了锁定和冲突。

ReadView

ReadView可以理解为数据库中某一个时刻所有未提交事务的快照。ReadView有几个重要的参数：

字段 说明 可见性说明
m_low_limit_id 尚未分配的最小事务id >=它的, 都不可见
m_up_limit_id 最小活动未提交事务id <它的, 都可见
m_creator_trx_id 创建readview的事务id =它的, 都可见
m_ids 当前系统中那些活跃(未提交)的读写事务ID, 它数据结构为一个List。  

所以当创建ReadView时，可以知道这个时间点上未提交事务的所有信息。

隐藏列

InnoDB存储引擎中，它的聚簇索引记录中都包含两个必要的隐藏列，分别是：

  • DB_TRX_ID ：6 byte，插入或更新行的最后一个事务ID. （解读：用于MVCC的ReadView判断事务id） 此外, 删除在内部被视为更新，其中行中的一个特殊位被设置为将其标记为已删除.
  • DB_ROLL_PTR：7 byte，回滚指针. （解读：用于MVCC中指向undo log记录） 指向已写入回滚段(rollback segment)的一条undo log记录, 记录着行(row)更新前的副本.
  • DB_ROW_ID：6 byte，隐藏的自增 ID. （解读：对于MVCC可忽略该字段） 如果InnoDB自动生成聚集索引, 则索引包含这个行ID值. 否则, DB_ROW_ID列不会出现在任何索引中.

通过隐藏列，基于版本号叫undolog日志形成一个链表。然后根据readview查询当前事务可以读取的版本数据来实现可重复度。

  • READ COMMITTED —— 每次读取数据前都生成一个ReadView
  • REPEATABLE READ —— 在第一次读取数据时生成一个ReadView

如下是事务A可重复度的例子

3.全局事务

资源本地事务允许我们在单个资源内执行多个操作，作为一个统一的整体。然而，我们经常处理跨越多个资源的操作。例如，在两个不同的数据库或一个数据库和一个消息队列中的操作。在这种情况下，资源内的本地事务支持是不够的。

在这些情况下，我们需要一个全局机制来标记跨越多个参与资源的事务。XA 规范就是这样一种规范，它定义了一个事务管理器来控制跨多个资源的事务。Java 在符合 XA 规范的情况下，通过 JTA 和 JTS 组件为分布式事务提供了相当成熟的支持。

JTA 最主要的两个接口 是： 事务管理器的接口： javax.transaction.TransactionManager 。这套接口是给 Java EE

服务器提供容器事务（由容器自动负责事务管理）使用的，还提供了另外一套 javax.tr ansaction.UserTransaction 接口，用于通过程序代码手动开启、提交和回滚事务。 满足 XA 规范的资源定义接口： javax.transaction.xa.XAResource ，任何资源（JDB C、JMS 等等）如果想要支持 JTA，只要实现 XAResource 接口中的方法即可。

JTA 原本是 Java EE 中的技术，一般情况下应该由 JBoss、WebSphere、WebLogic 这些 Jav a EE 容器来提供支持，但现在Bittronix 、Atomikos 和JBossTM （以前叫 Arjuna）都 以 JAR 包的形式实现了 JTA 的接口，称为 JOTM（Java Open Transaction Manager），使 得我们能够在 Tomcat、Jetty 这样的 Java SE 环境下也能使用 JTA。

XA 将事务提交拆分成为两阶段过程

准备阶段：又叫作投票阶段，在这一阶段，协调者询问事务的所有参与者是否准备好提 交，参与者如果已经准备好提交则回复 Prepared，否则回复 Non-Prepared。这里所说 的准备操作跟人类语言中通常理解的准备并不相同，对于数据库来说，准备操作是在重 做日志中记录全部事务提交操作所要做的内容，它与本地事务中真正提交的区别只是暂

不写入最后一条 Commit Record 而已，这意味着在做完数据持久化后并不立即释放隔离 性，即仍继续持有锁，维持数据对其他非事务内观察者的隔离状态。

提交阶段：又叫作执行阶段，协调者如果在上一阶段收到所有事务参与者回复的 Prepar ed 消息，则先自己在本地持久化事务状态为 Commit，在此操作完成后向所有参与者发 送 Commit 指令，所有参与者立即执行提交操作；否则，任意一个参与者回复了 Non-P repared 消息，或任意一个参与者超时未回复，协调者将自己的事务状态持久化为 Abor t 之后，向所有参与者发送 Abort 指令，参与者立即执行回滚操作。对于数据库来说， 这个阶段的提交操作应是很轻量的，仅仅是持久化一条 Commit Record 而已，通常能够 快速完成，只有收到 Abort 指令时，才需要根据回滚日志清理已提交的数据，这可能是 相对重负载的操作。

两阶段非常显著的缺点：

单点问题：协调者在两段提交中具有举足轻重的作用，协调者等待参与者回复时可以有 超时机制，允许参与者宕机，但参与者等待协调者指令时无法做超时处理。一旦宕机的 不是其中某个参与者，而是协调者的话，所有参与者都会受到影响。如果协调者一直没 有恢复，没有正常发送 Commit 或者 Rollback 的指令，那所有参与者都必须一直等待。

性能问题：两段提交过程中，所有参与者相当于被绑定成为一个统一调度的整体，期间 要经过两次远程服务调用，三次数据持久化（准备阶段写重做日志，协调者做状态持久 化，提交阶段在日志写入 Commit Record），整个过程将持续到参与者集群中最慢的那 一个处理操作结束为止，这决定了两段式提交的性能通常都较差。

一致性风险：前面已经提到，两段式提交的成立是有前提条件的，当网络稳定性和宕机 恢复能力的假设不成立时，仍可能出现一致性问题。宕机恢复能力这一点不必多谈，19 85 年 Fischer、Lynch、Paterson 提出了“FLP 不可能原理 ”，证明了如果宕机最后不能 恢复，那就不存在任何一种分布式协议可以正确地达成一致性结果。该原理在分布式中 是与“CAP 不可兼得原理“齐名的理论。而网络稳定性带来的一致性风险是指：尽管提交 阶段时间很短，但这仍是一段明确存在的危险期，如果协调者在发出准备指令后，根据 收到各个参与者发回的信息确定事务状态是可以提交的，协调者会先持久化事务状态，

并提交自己的事务，如果这时候网络忽然被断开，无法再通过网络向所有参与者发出 C ommit 指令的话，就会导致部分数据（协调者的）已提交，但部分数据（参与者的）既 未提交，也没有办法回滚，产生了数据不一致的问题。

mysql内部两阶段实现，通过binlog作为协调器。

  1. 首先调用存储引擎的 prepare 接口 （两阶段准备阶段）
  2. 调用 TC_LOG 的 commit 接口写入binlog日志
  3. 调用存储引擎的 commit 接口 （两阶段提交阶段）

写redolog和binlog的三种情况

情况一：一阶段提交之后崩溃了，即写入 redo log，处于 prepare 状态 的时候崩溃了，此时：

由于 binlog 还没写，redo log 处于 prepare 状态还没提交，所以崩溃恢复的时候，这个事务会回滚，此时 binlog 还没写，所以也不会传到备库。

情况二：假设写完 binlog 之后崩溃了，此时：

redolog 中的日志是不完整的，处于 prepare 状态，还没有提交，那么恢复的时候，首先检查 binlog 中的事务是否存在并且完整，如果存在且完整，则直接提交事务，如果不存在或者不完整，则回滚事务。

情况三：假设 redolog 处于 commit 状态的时候崩溃了，那么重启后的处理方案同情况二。

三阶段提交

三段式提交把原本 的两段式提交的准备阶段再细分为两个阶段，分别称为 CanCommit、PreCommit，把提交 阶段改称为 DoCommit 阶段。

  1. 准备阶段（CanCommit）： 在这个阶段，协调者（通常是事务管理器）向所有参与者（资源或子事务）发送询问，询问它们是否可以提交事务。参与者会检查它们自己的资源状态来决定是否可以提交。如果所有参与者都同意，那么协调者会继续进入下一个阶段。

  2. 预提交阶段（PreCommit）： 在这个阶段，协调者要求所有参与者执行事务的预提交操作。参与者执行预提交并在本地记录提交请求，但仍然不对外部环境（如数据库）做出永久性的改变。如果所有参与者成功执行预提交，那么协调者将进入最后一个阶段。

  3. 提交阶段（DoCommit）： 在这个阶段，协调者向所有参与者发送最终的提交请求。参与者执行真正的提交操作，将事务的改变永久地应用到各自的资源中。如果所有参与者成功提交，那么全局事务就成功完成。如果有任何问题发生，比如参与者中的某个资源无法提交，协调者将发送回滚请求，参与者将回滚之前的预提交操作。

4.分布式事务

分布式事务就是指事务的参与者、支持事务的服务器、资源服务器以及事务管理器分别位于不同的分布式系统的不同节点之上。通常它会在由网络连接的不同节点之间进行协调，但也可能横跨单个服务器上的多个数据库。

分布式与集群 

  1. 分布式（Distributed）： 分布式系统是由多台独立的计算机或节点组成，这些计算机通过网络进行通信和协作。每个节点都可以独立运行，并且可以处理任务、存储数据等。分布式系统的目标是将工作分散到多个节点上，以提高系统的可伸缩性、容错性和性能。分布式系统可以包括分布式数据库、分布式文件系统、分布式计算等。在分布式系统中，不同节点之间通常会协调和协作，但它们可以是独立的物理设备。

  2. 集群（Cluster）： 集群是由多个计算机（节点）组成的一个组，这些计算机通过高速网络连接在一起，并以协同方式工作。集群的目标是通过将多台计算机连接在一起，创建一个高性能、高可用性的系统，以共同处理任务和提供服务。在集群中，计算机可以协同工作，共享负载、存储和处理能力，以实现高性能和容错性。集群可以用于各种用途，包括网络服务、数据处理、高性能计算等。集群中的计算机通常是物理上相近的，并且通过特定的集群管理软件来进行协调。

CAP理论

  1. 一致性（Consistency）：代表数据在任何时刻、任何分布式节点中所看到的都是符合预 期的。一致性在分布式研究中是有严肃定义、有多种细分类型的概念，以后讨论分布式 共识算法时，我们还会再提到一致性，那种面向副本复制的一致性与这里面向数据库状 态的一致性严格来说并不完全等同，具体差别我们将在后续分布式共识算法中再作探 讨。
  2. 可用性（Availability）：代表系统不间断地提供服务的能力，理解可用性要先理解与其 密切相关两个指标：可靠性（Reliability）和可维护性（Serviceability）。可靠性使用平 均无故障时间（Mean Time Between Failure，MTBF）来度量；可维护性使用平均可修 复时间（Mean Time To Repair，MTTR）来度量。可用性衡量系统可以正常使用的时间 与总时间之比，其表征为：A=MTBF/（MTBF+MTTR），即可用性是由可靠性和可维护 性计算得出的比例值，譬如 99.9999%可用，即代表平均年故障修复时间为 32 秒。
  3. 分区容忍性（Partition Tolerance）：代表分布式环境中部分节点因网络原因而彼此失 联后，即与其他节点形成“网络分区”时，系统仍能正确地提供服务的能力。

BASE理论

  1. 基本可用性（Basically Available）： 在分布式系统中，系统应该保证在面对故障或部分失效时仍能保持基本的可用性，即系统能够继续处理请求并提供某种程度的服务，尽管可能无法提供完整的功能。

  2. 软状态（Soft State）： 分布式系统中的各个节点可能由于网络延迟、通信失败或其他原因而存在状态不一致的情况。在 BASE 理论中，允许系统处于一段时间内的“软状态”，即在状态同步之间可能存在短暂的不一致性，但最终会达到一致状态。

  3. 最终一致性（Eventual Consistency）： BASE 理论认为，分布式系统中的一致性不需要实时保持，而是在一段时间内最终达到一致状态。即使在数据的复制和同步过程中存在延迟，系统最终会在全局范围内达到一致状态。

分布式事务的实现

分布式事务的实现方式有，可靠事件队列，前面提到的2PC,3PC.还有TCC和Saga事务。具体实现见 seata xa实现原理 

 

MySQL

MYSQL 无root用户或root用户被删除解决方法

注：先改变文件my.ini  mysqld下加入skip-grant-tables

Microsoft Windows [版本 10.0.14393]

(c) 2016 Microsoft Corporation。保留所有权利。

 

C:\WINDOWS\system32>cd C:\Program Files\MySQL\MySQL Server 5.7\bin

 

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -u root -p

Enter password: ******

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

 

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.7.12-log MySQL Community Server (GPL)

 

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

 

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 

mysql> use mysql

Database changed

mysql> UPDATE user SET authentication_string=PASSWORD('111111') where USER='root';

Query OK, 0 rows affected, 1 warning (0.00 sec)

Rows matched: 0  Changed: 0  Warnings: 1

 

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

 

mysql> select * from user;

+-----------+-----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+

| Host      | User      | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin                | authentication_string                     | password_expired | password_last_changed | password_lifetime | account_locked |

+-----------+-----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+

| localhost | mysql.sys | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N                | 2016-04-20 00:15:15   |              NULL | Y              |

+-----------+-----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+

1 row in set (0.00 sec)

 

mysql> quit;

Bye

 

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -u root -p

Enter password: ******

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

 

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -u root

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

 

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.7.12-log MySQL Community Server (GPL)

 

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

 

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 

mysql> use mysql;

Database changed

mysql> update user set password=PASSWORD("111111") where user="root";

ERROR 1054 (42S22): Unknown column 'password' in 'field list'

mysql> update user set authentication_string=PASSWORD("111111") where user="root";

Query OK, 0 rows affected, 1 warning (0.00 sec)

Rows matched: 0  Changed: 0  Warnings: 1

 

mysql> select count(*) from user;

+----------+

| count(*) |

+----------+

|        1 |

+----------+

1 row in set (0.00 sec)

 

mysql> select * from user where user="root";

Empty set (0.00 sec)

 

mysql> select user from user ;

+-----------+

| user      |

+-----------+

| mysql.sys |

+-----------+

1 row in set (0.00 sec)

 

mysql> insert into user set user='root',ssl_cipher='',x509_issuer='',x509_subject='';

Query OK, 1 row affected (0.00 sec)

 

mysql> update user set Host='localhost',select_priv='y', insert_priv='y',update_priv='y',Alter_priv='y',delete_priv='y',create_priv='y',drop_priv='y',reload_priv='y',shutdown_priv='y',Process_priv='y',file_priv='y',grant_priv='y',References_priv='y',index_priv='y',show_db_priv='y',super_priv='y',create_tmp_table_priv='y',Lock_tables_priv='y',execute_priv='y',repl_slave_priv='y',repl_client_priv='y',create_view_priv='y',show_view_priv='y',create_routine_priv='y',alter_routine_priv='y',create_user_priv='y' where user='root';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

 

mysql> quit;

Bye

 

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -uroot -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.7.12-log MySQL Community Server (GPL)

 

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

 

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 

mysql> update user set authtication_string=PASSWORD("111111") where user="root";

ERROR 1046 (3D000): No database selected

mysql> user mysql;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'user mysql' at line 1

mysql> use mysql;

Database changed

mysql> update user set authtication_string=PASSWORD("111111") where user="root";

ERROR 1054 (42S22): Unknown column 'authtication_string' in 'field list'

mysql> update user set authentication_string=PASSWORD("111111") where user="root";

Query OK, 1 row affected, 1 warning (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 1

 

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

 

mysql> quit;

Bye

 

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -u root -p

Enter password: ******

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.7.12-log MySQL Community Server (GPL)

 

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

 

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 

mysql>

mysql事务

开发手册 https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html

https://dev.mysql.com/doc/refman/8.0/en/ibd2sdi.html

查询系统变量 SELECT @@transaction_isolation;  设置隔离级别

set global transaction isolation level read committed;

 

mysql索引

1.索引的分类

2.btree和b+tree

2.1 btree定义

btree是一种平衡的多路查找树，树中结点最大的孩子数目称为B树的阶，通常记为m，一颗m阶B树或为空树，或满足一下特性的m叉树：

  1. 树中每个结点至多有m棵子树。（即至多含有m-1个关键字）
  2. 若根结点不是终端结点，则至少有两棵子树。（至少一个关键字）
  3. 除根结点外的所有非叶结点至少有⌈m/2⌉（向上取整）棵子树
  4. 所有的叶子结点出现在同一层次上，等于树的高度h
  5. 非叶子结点结构：

2.2 b+tree 

  1. 在B+树中，具有n个关键字的结点含有n棵子树，即每个关键字对应一棵子树；而在B树中，具有n个关键字的结点含有（n+1）棵子树。
  2. 在B+树中，每个结点（非根内部结点）关键字个数n的范围是⌈m/2⌉≤n≤m（根结点1≤n≤m），在B树中，每个结点（非根内部结点）关键字个数n的范围是⌈m/2⌉-1≤n≤m-1（根结点：1≤n≤m-1）。
  3. 在B+树中，叶结点包含信息，所有非叶结点仅起到索引作用，非叶结点中的每个索引项只含有对应子树的最大关键字和指向该子树的指针，不含有该关键字对应记录的存储地址。
  4. 在B+树中，叶结点包含了全部关键字，即在非叶结点中出现的关键字也会出现在叶结点中；而在B树中，叶结点包含的关键字和其他结点包含的关键字是不重复的。

2.3区别

在B+树中，搜索键可以重复，B树中叶子结点关键字和其他结点包含的关键字不能重复。

B+树允许将数据仅存储在叶子节点中，而B树则在叶子节点和内部节点中都存储数据。

在B+树中，存储在叶子节点上的数据使得搜索更有效，因为我们可以在内部节点存储更多的键  降低了B+Tree的高度，从而可以减少磁盘io的次数

从B+树中删除数据更容易且耗时较少，因为我们只需要从叶子节点中删除数据。

B+树中的叶子节点是链接在一起的，这使得范围搜索操作更有效且快速。

2.3 构建一个b树和b+tree

使用 50，20，30，70，60，68，52，55 构建一个3阶的b树和b+tree的过程

 

btree 构建过程

b+tree构建过程

3.索引数据结构和数据查找

3.1 最小存储单元

磁盘扇区、文件系统、InnoDB 存储引擎都有各自的最小存储单元。 

  • 磁盘上，存储数据最小单元是扇区，一个扇区的大小是 512 字节。
  • 文件系统(例如EXT4),最小单元是块 (block)，一个block 块的大小是 4k。
  • InnoDB 存储引擎 的最小储存单元——页(Page)，一个页的大小是 16K。

 

3.2 树的查找流程

磁盘的读取 
首先所有磁头位于0柱面（也就是0磁道）0号磁头开始工作，目标扇区旋转到磁头下，从001->002->……->N（N为每磁道扇区数）至此，读完一个磁道。
接下来，磁头位置不动，通过电子转换开关，使1号磁头工作，所读取扇区为：011->012->……->01N
再变换磁头，直到所有盘片上的同一磁道读完：……0M1->0M2->……0MN

数据表中的数据都是存储在页中的
假设一行数据的大小是1k，
那么一个16K页，可以存放16行这样的数据。

B+ 树的叶子节点存储真正的记录，对应的文件系统 page页面，可以叫做 数据页
B+ 树的非叶子节点存放的是键值 + 指针，其对应的文件系统 page页面，可以叫做 索引页

每个节点都会占据一页，而页的大小可以通过配置来调整。

InnoDB存储引擎的最小存储单元是页，页可以用于存放数据也可以用于存放键值+指针， 2、页内的记录是有序的，所以可以使用二分查找在页内到下一层的目标页面的指针

  • 从根页开始，首先通过非叶子节点的二分查找法。

  • 确定数据在下一层哪个页之后，一层一层往下找，一直到叶子节点， 进而在叶子节(数据页)中查找到需要的数据。

对于上面b+tree如果要找55这个数据 MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，假设其他页不在内存中，所以对于根结点直接通过二分查找，判断需要加载下一个页即52所在的页（第一次磁盘io）

然后在加载52，55所在的叶节点所在页（第二次磁盘io）

 

3.3 3阶的b+tree能存储的数据量

InnoDB 数据页结构

我们假设主键的类型是 BigInt，长度为 8 字节， 而指针大小在 InnoDB 中设置为 6 字节，这样一个单元，一共 14 字节。

这样的话，一页或者说一个非叶子节点能够存放 16384 / 14=1170 个这样的单元。 也就是说一个非叶子节点中能够存放 1170 个指针，即对应 1170 个叶子节点。

那么对三阶的来说非叶子结点可以存放1170*1170个指针。假设每条记录的大小为1kb 16kb去掉数据页其他站用大概可以存储15条记录，则 估计的存的数据量为 1170*1170*15=20533500。 大概2千万数据

mysql锁

1.锁的分类

 

 

Oracle

oracle导入数据与导出数据

一 导出

1.远程主机：exp  user/password@主机/实例 file=c:\temp.dmp

2.本地主机：exp 用户名/密码@数据库 owner=用户名 file=文件存储路径（如：F:\abcd.dmp）

二 创建要导入的库

1.登录sysdba">1.登录sysdba">1.登录sysdba

sqlplus sys/pass as sysdba

SQLPLUS /NOLOG;

   CONNECT / AS SYSDBA

2.创建表空间

create tablespace test

datafile 'D:\app\Administrator\oradata\orcl\test.dbf'

size 50m 

autoextend on;

3.创建用户

CREATE USER test

IDENTIFIED BY test

DEFAULT TABLESPACE test

TEMPORARY TABLESPACE temp;

4，授权

GRANT CONNECT TO test;

GRANT RESOURCE TO test; 

GRANT dba TO test;

三 导入

imp 用户名/密码@数据库 fromuser=用户名 touser=用户名 file=d:\cu.dmp ignore=y

imp usename/password@chinacoal full=y  file= d:\data\xxxx.dmp 

Oracle 打补丁

本文翻译自oracle 补丁安装压缩包说明文件README.html

Oracle® Database

Oracle JavaVM Component 11.2.0.4.180810 on Windows

Released:  Aug 10, 2018

本文档介绍如何在Windows DB Bundle Patch 11.2.0.4.180717之上安装Patch 28416098 - Oracle JavaVM组件11.2.0.4.180810

此修补程序不是Oracle RAC Rolling可安装的。

在安装此修补程序之前需要安装以下修补程序：

  • Windows DB Bundle Patch 11.2.0.4.180717

本文档包括以下部分：

  • 第1节“补丁信息”

  • 第2节“先决条件”

  • 第3节“安装”

  • 第4节“安装后”

  • 第5节“卸载”

  • 第6节“安装后”

  • 第7节“已知问题”

  • 第8节“文档可访问性”

1补丁信息

Windows上的Oracle JavaVM组件11.2.0.4.180810是累积性的，包括数据库CPU程序安全性内容。

表1描述了安装类型和安全性内容。对于每种安装类型，它指示最新的修补程序，其中包括与该安装类型相关的新安全修复程序。如果没有要应用于安装类型的安全修复程序，则指示“无”。如果列出了特定的修补程序，则将该修补程序或任何后续修补程序应用于安全修复程序.

表1安装类型和安全内容

安装类型 Latest Database Patch with Security Fixes带有安全修复程序的最新数据库补丁

Server homes

Oracle JavaVM Component 11.2.0.4.180810 on Windows (Aug 2018)

Grid Infrastructure home

Oracle JavaVM Component 11.2.0.4.160719 Database PSU - Generic JDBC Patch 23727132
仅客户端安装

Oracle JavaVM Component 11.2.0.4.160719 Database PSU - Generic JDBC Patch 23727132

即时客户端安装

Oracle JavaVM Component 11.2.0.4.160719 Database PSU - Generic JDBC Patch 23727132

（Instant Client安装与仅客户端安装不同。有关Instant Client安装的其他信息，请参阅Oracle Call Interface Programmer's Guide。）

2先决条件

在安装或卸载修补程序之前，请确保满足以下要求。对于Oracle RAC环境，请在每个节点上满足这些先决条件

  1. 确保要安装修补程序或从中回滚修补程序的Oracle数据库是Oracle Database 11g第2版（11.2.0.4.0）

  2. 在安装此修补程序之前，请确保已安装以下修补程序：

    • Windows DB Bundle Patch 11.2.0.4.180717

  3. 确保您拥有OPatch 11g第1版（11.2.0.3.5）或更高版本。 Oracle建议您使用适用于11g第2版的最新版本。

    如果您没有最新版本，请从补丁＃6880880下载11.2.0.4.0版本。

    有关OPatch文档（包括任何已知问题）的信息，请参阅My Oracle Support Document 293369.1 OPatch文档列表。

  4. 确保将ORACLE_HOME环境变量设置为Oracle数据库的Oracle主目录。

  5. 确保验证Oracle Inventory，因为OPatch访问它以安装修补程序。要验证清单，请运行以下命令。如果该命令显示某些错误，请与Oracle支持部门联系并解决问题。

    $ opatch lsinventory

  6. （仅用于安装）维护用于存储修补程序ZIP文件内容的位置。在文档的其余部分中，此位置（绝对路径）称为PATCH_TOP_DIR。将补丁ZIP文件的内容解压缩到您在上面创建的位置（PATCH_TOP_DIR）。为此，请运行以下命令：

    $ unzip -d <PATCH_TOP_DIR> p28416098_112040_<PLATFORM_NAME>.zip

  7. （仅用于安装）确定是否有任何当前安装的临时补丁与此补丁28416098冲突，如下所示：

    $ cd <PATCH_TOP_DIR>\28416098
$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./

    该报告将指出与此修补程序冲突的修补程序以及当前28416098是超集的修补程序。

     

    注意

    当OPatch启动时，它会验证补丁并确保与ORACLE_HOME中已安装的软件没有冲突。 OPatch将冲突分为以下类型

    • 与已应用于ORACLE_HOME的修补程序冲突，该修补程序是您尝试应用的修补程序的子集 - 在这种情况下，请继续安装修补程序，因为新修补程序包含ORACLE_HOME中现有修补程序的所有修补程序。在安装新补丁之前，将自动回滚子集补丁。

    • 与已应用于ORACLE_HOME的修补程序冲突 - 在这种情况下，请停止修补程序安装并联系Oracle支持服务。

  8. 确保关闭从Oracle主目录运行的所有服务。

    对于Oracle Non RAC环境，请关闭与要更新的Oracle主目录关联的所有数据库和侦听器。有关更多信息，请参见“Oracle数据库管理员指南”。

    对于Oracle RAC环境，请关闭在所有节点上从Oracle主目录运行的所有服务（数据库，ASM，侦听器，节点应用程序和CRS后台驻留程序）。修补完所有节点后，启动服务。 OPatch一次只用在一个节点上。

3 安装

如果您使用的是Data Guard物理备用数据库，则必须在主数据库和物理备用数据库上安装此修补程序，如My Oracle Support Document 278641.1所述。

要在Oracle Non RAC环境中安装修补程序，请按照下列步骤操作。

  1. 将当前目录设置为修补程序所在的目录，然后输入以下命令运行OPatch实用程序：

    $ cd <PATCH_TOP_DIR>\28416098

  2. 通过运行以下命令安装修补程序

    $ opatch apply

  3. 通过运行以下命令验证是否已成功安装修补程序：

    $ opatch lsinventory
  4. 从Oracle主目录启动服务。

  5. 如果有错误，请参见第7节“已知问题”。

要在Oracle RAC环境中安装修补程序，请执行以下步骤。这些步骤应该一次执行一个节点，直到所有节点都被修补

  1. 在要修补的节点上，将当前目录设置为修补程序所在的目录，然后输入以下命令运行OPatch实用程序：

    $ cd <PATCH_TOP_DIR>\28416098

  2. 通过运行以下命令在节点上安装修补程序：

    $ opatch apply -local

  3. 通过运行以下命令验证是否已在要修补的节点上成功安装了修补程序：

    $ opatch lsinventory
  4. 修补所有节点后，可以重新启动所有节点上的服务（数据库，ASM，侦听器，节点应用程序和CRS守护程序）

  5. 如果有错误，请参见第7节“已知问题”。

4 安装后

以下步骤将修改后的SQL文件加载到数据库中。对于Oracle RAC环境，仅在一个节点上执行这些步骤。

  1. 通过对单个实例环境运行以下命令来安装修补程序的SQL部分。

    cd %ORACLE_HOME%\sqlpatch\28416098
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> SHUTDOWN
SQL> STARTUP UPGRADE
SQL> @postinstall.sql
SQL> SHUTDOWN
SQL> STARTUP

    对于Oracle RAC环境，使用以下命令在其中一个节点上重新加载包。确保远程节点上没有其他数据库实例。

    cd %ORACLE_HOME%\sqlpatch\28416098
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> alter system set cluster_database=false scope=spfile
SQL> SHUTDOWN
SQL> STARTUP UPGRADE
SQL> @postinstall.sql
SQL> alter system set cluster_database=true scope=spfile
SQL> SHUTDOWN
SQL> STARTUP

  2. 安装补丁的SQL部分后，某些软件包可能会变为INVALID。这将在访问时重新编译，或者您可以运行utlrp.sql以使它们回到VALID状态。

    cd %ORACLE_HOME%\rdbms\admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> @utlrp.sql

  3. 如果有错误，请参见第7节“已知问题”。

5 卸载

确保遵循第2部分“先决条件”中所述的先决条件。要卸载修补程序，请按照下列步骤操作。对于Oracle RAC环境，请在每个节点上执行以下步骤： 

要在Oracle Non RAC环境中卸载修补程序，请执行以下步骤。

  1. 运行以下命令：

    opatch rollback -id 28416098

  2. 从Oracle主目录启动服务。

  3. 通过运行以下命令验证是否已在节点上成功回滚该修补程序：

    $ opatch lsinventory

要在Oracle RAC环境中卸载修补程序，请按照下列步骤操作。这些步骤应该一次执行一个节点，直到从所有节点回滚补丁。

  1. 在每个节点上运行以下命令，一次一个节点

    $ opatch rollback -id 28416098 -local

  2. 通过运行以下命令验证是否已在节点上成功回滚该修补程序：

    $ opatch lsinventory
  3. 从所有节点回滚补丁后，可以重新启动所有节点上的服务（数据库，ASM，侦听器，节点应用程序和CRS守护程序）

  4. 如果有错误，请参见第7节“已知问题”。

6 卸载后

以下步骤将修改后的SQL文件加载到数据库中。对于Oracle RAC环境，仅在一个节点上执行这些步骤。

  1. 通过对单个实例环境运行以下命令来安装修补程序的SQL部分

    cd %ORACLE_HOME%\sqlpatch\28416098
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> SHUTDOWN
SQL> STARTUP UPGRADE
SQL> @postdeinstall.sql
SQL> SHUTDOWN
SQL> STARTUP

    对于Oracle RAC环境，使用以下命令在其中一个节点上重新加载包。确保远程节点上没有其他数据库实例。

    cd %ORACLE_HOME%\sqlpatch\28416098
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> alter system set cluster_database=false scope=spfile
SQL> SHUTDOWN
SQL> STARTUP UPGRADE
SQL> @postdeinstall.sql
SQL> alter system set cluster_database=true scope=spfile
SQL> SHUTDOWN
SQL> STARTUP

  2. 安装补丁的SQL部分后，某些软件包可能会变为INVALID。这将在访问时重新编译，或者您可以运行utlrp.sql以使它们回到VALID状态。

    cd %ORACLE_HOME%\rdbms\admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> @utlrp.sql

  3. 如果有错误，请参见第7节“已知问题”。

7已知问题

有关OPatch问题的信息，请参阅My Oracle Support Document 293369.1 OPatch文档列表。 已知问题如下。 问题1

如果未正确运行安装后配置或安装后步骤，则可能会出现错误。当数据库中的Java系统classes.bin与oracle可执行文件的类不匹配时，会发生这种情况。

要从此问题中恢复，请完成安装后安装（请参阅第4节“安装后安装”）或安装后安装（请参阅第6节“安装后安装”），这会将更新的classes.bin加载到内存中并解决不一致问题。

8文档可访问性

有关Oracle对可访问性的承诺的信息，请访问Oracle Accessibility Program网站 http://www.oracle.com/us/corporate/accessibility/index.html.

Access to Oracle Support

Oracle客户可以通过My Oracle Support获得电子支持。有关信息，请访问http://www.oracle.com/support/contact.html，或者如果您有听力障碍，请访问http://www.oracle.com/accessibility/support.html。