Mysql 事件记录 | performance_schema全方位介绍

2023-11-13 来源：华拓网

Mysql 事件记录 | performance_schema全方位介绍| 导语

在上一篇初相识｜performance_schema全方位介绍中，我们详细介绍了performance_schema的配置表，坚持读完的是真爱，也恭喜大家翻过了一座火焰山。相信有不少人读完之后，已经迫不及待的想要跃跃欲试了，今天将带领大家一起踏上系列第三篇的征程(全系共7个篇章)，在这一期里，我们将为大家全面讲解performance_schema中事件原始记录表。下面，请跟随我们一起开始performance_schema系统的学习之旅吧。

| 等待事件表

通常，我们在碰到性能瓶颈时，如果其他的方法难以找出性能瓶颈的时候(例如：硬件负载不高、SQL优化和库表结构优化都难以奏效的时候)，我们常常需要借助于等待事件来进行分析，找出在MySQL Server内部，到底数据库响应慢是慢在哪里。

等待事件记录表包含三张表，这些表记录了当前与最近在MySQL实例中发生了哪些等待事件，时间消耗是多少。

events_waits_current表：记录当前正在执行的等待事件的，每个线程只记录1行记录

events_waits_history表：记录已经执行完的最近的等待事件历史，默认每个线程只记录10行记录

events_waits_history_long表：记录已经执行完的最近的等待事件历史，默认所有线程的总记录行数为10000行

要注意：等待事件相关配置中，setup_instruments表中绝大部分的等待事件instruments都没有开启(IO相关的等待事件instruments默认大部分已开启)，setup_consumers表中waits相关的consumers配置默认没有开启

events_waits_current 表

events_waits_current表包含当前的等待事件信息，每个线程只显示一行最近监视的等待事件的当前状态

在所有包含等待事件行的表中，events_waits_current表是最基础的数据来源。其他包含等待事件数据表在逻辑上是来源于events_waits_current表中的当前事件信息（汇总表除外）。例如，events_waits_history和events_waits_history_long表中的数据是events_waits_current表数据的一个小集合汇总（具体存放多少行数据集合有各自的变量控制）

表记录内容示例（这是一个执行select sleep(100);语句的线程等待事件信息）

root@localhost : performance_schema 12:15:03> select * from events_waits_current where EVENT_NAME='wait/synch/cond/sql/Item_func_sleep::cond'G;*************************** 1. row *************************** THREAD_ID: 46 EVENT_ID: 140 END_EVENT_ID: NULL EVENT_NAME: wait/synch/cond/sql/Item_func_sleep::cond SOURCE: item_func.cc:5261 TIMER_START: 14128809267002592 TIMER_END: 14132636159944419 TIMER_WAIT: 3826892941827 SPINS: NULL OBJECT_SCHEMA: NULL OBJECT_NAME: NULL INDEX_NAME: NULL OBJECT_TYPE: NULLOBJECT_INSTANCE_BEGIN: 140568905519072NESTING_EVENT_ID: 116NESTING_EVENT_TYPE: STATEMENT OPERATION: timed_wait NUMBER_OF_BYTES: NULL FLAGS: NULL1 row in set (0.00 sec)

上面的输出结果中，TIMER_WAIT字段即表示该事件的时间开销，单位是皮秒，在实际的应用场景中，我们可以利用该字段信息进行倒序排序，以便找出时间开销最大的等待事件。

events_waits_current表完整的字段含义如下：

THREAD_ID，EVENT_ID：与事件关联的线程ID和当前事件ID。THREAD_ID和EVENT_ID值构成了该事件信息行的唯一标识（不会有重复的THREAD_ID+EVENT_ID值）

END_EVENT_ID：当一个事件正在执行时该列值为NULL，当一个事件执行结束时把该事件的ID更新到该列

EVENT_NAME：产生事件的instruments名称。该名称来自setup_instruments表的NAME字段值

SOURCE：产生该事件的instruments所在的源文件名称以及检测到该事件发生点的代码行号。您可以查看源代码来确定涉及的代码。例如，如果互斥锁、锁被阻塞，您可以检查发生这种情况的上下文环境

TIMER_START，TIMER_END，TIMER_WAIT：事件的时间信息。单位皮秒（万亿分之一秒）。 TIMER_START和TIMER_END值表示事件开始和结束时间。 TIMER_WAIT是事件经过时间（即事件执行了多长时间）

如果事件未执行完成，则TIMER_END为当前计时器时间值（当前时间），TIMER_WAIT为目前为止所经过的时间（TIMER_END - TIMER_START）

如果采集该事件的instruments配置项TIMED = NO，则不会收集事件的时间信息，TIMER_START，TIMER_END和TIMER_WAIT在这种情况下均记录为NULL

SPINS：对于互斥量和自旋次数。如果该列值为NULL，则表示代码中没有使用自旋或者说自旋没有被监控起来

OBJECT_SCHEMA，OBJECT_NAME，OBJECT_TYPE，OBJECT_INSTANCE_BEGIN：这些列标识了一个正在被执行的对象，所以这些列记录的信息含义需要看对象是什么类型，下面按照不同对象类型分别对这些列的含义进行说明：

*** 对于同步对象（cond，mutex，rwlock）：**

* 1)、OBJECT_SCHEMA，OBJECT_NAME和OBJECT_TYPE列值都为NULL

* 2)、OBJECT_INSTANCE_BEGIN列是内存中同步对象的地址。OBJECT_INSTANCE_BEGIN除了不同的值标记不同的对象之外，其值本身没有意义。但OBJECT_INSTANCE_BEGIN值可用于调试。例如，它可以与GROUP BY OBJECT_INSTANCE_BEGIN子句一起使用来查看1,000个互斥体（例如：保护1,000个页或数据块）上的负载是否是均匀分布还是发生了一些瓶颈。如果在日志文件或其他调试、性能工具中看到与该语句查看的结果中有相同的对象地址，那么，在你分析性能问题时，可以把这个语句查看到的信息与其他工具查看到的信息关联起来。

*** 对于文件I/O对象：**

* 1)、OBJECT_SCHEMA列值为NULL* 2)、OBJECT_NAME列是文件名* 3)、OBJECT_TYPE列为FILE* 4)、OBJECT_INSTANCE_BEGIN列是内存中的地址，解释同上

*** 对于套接字对象：**

* 1)、OBJECT_NAME列是套接字的IP：PORT值

* 2)、OBJECT_INSTANCE_BEGIN列是内存中的地址，解释同上

*** 对于表I/O对象：**

* 1)、OBJECT_SCHEMA列是包含该表的库名称

* 2)、OBJECT_NAME列是表名

* 3)、OBJECT_TYPE列值对于基表或者TEMPORARY TABLE临时表，该值是table，注意：对于在join查询中select_type为DERIVED，subquery等的表可能不记录事件信息也不进行统计

* 4)、OBJECT_INSTANCE_BEGIN列是内存中的地址，解释同上

INDEX_NAME：表示使用的索引的名称。PRIMARY表示使用到了主键。 NULL表示没有使用索引

NESTING_EVENT_ID：表示该行信息中的EVENT_ID事件是嵌套在哪个事件中，即父事件的EVENT_ID

NESTING_EVENT_TYPE：表示该行信息中的EVENT_ID事件嵌套的事件类型。有效值有：TRANSACTION，STATEMENT，STAGE或WAIT，即父事件的事件类型，如果为TRANSACTION则需要到事务事件表中找对应NESTING_EVENT_ID值的事件，其他类型同理

OPERATION：执行的操作类型，如：lock、read、write、timed_wait

NUMBER_OF_BYTES：操作读取或写入的字节数或行数。对于文件IO等待，该列值表示字节数；对于表I/O等待（wait/io/table/sql/handler instruments的事件），该列值表示行数。如果值大于1，则表示该事件对应一个批量I/O操作。以下分别对单个表IO和批量表IO的区别进行描述：

MySQL的join查询使用嵌套循环实现。performance_schema instruments的作用是在join查询中提供对每个表的扫描行数和执行时间进行统计。示例：join查询语句：SELECT … FROM t1 JOIN t2 ON … JOIN t3 ON …，假设join顺序是t1，t2，t3

在join查询中，一个表在查询时与其他表展开联结查询之后，该表的扫描行数可能增加也可能减少，例如：如果t3表扇出大于1，则大多数row fetch操作都是针对t3表，假如join查询从t1表访问10行记录，然后使用t1表驱动查询t2表，t1表的每一行都会扫描t2表的20行记录，然后使用t2表驱动查询t3表，t2表的每一行都会扫描t3表的30行记录，那么，在使用单行输出时，instruments统计操作的事件信息总行数为：10 +（10 * 20）+（10 * 20 * 30）= 6210

通过对表中行扫描时的instruments统计操作进行聚合（即，每个t1和t2的扫描行数在instruments统计中可以算作一个批量组合），这样就可以减少instruments统计操作的数量。通过批量I/O输出方式，performance_schema每次对最内层表t3的扫描减少为一个事件统计信息而不是每一行扫描都生成一个事件信息，此时对于instruments统计操作的事件行数量减少到：10 +（10 * 20）+（10 * 20）= 410，这样在该join查询中对于performance_schema中的行统计操作就减少了93％，批量输出策略通过减少输出行数量来显着降低表I/O的performance_schema统计开销。但是相对于每行数据都单独执行统计操作，会损失对时间统计的准确度。在join查询中，批量I/O统计的时间包括用于连接缓冲、聚合和返回行到客户端的操作所花费的时间（即就是整个join语句的执行时间）

FLAGS：留作将来使用

PS：events_waits_current表允许使用TRUNCATE TABLE语句

events_waits_history 表

events_waits_history表包含每个线程最近的N个等待事件。在server启动时，N的值会自动调整。如果要显式设置这个N大小，可以在server启动之前调整系统参数performance_schema_events_waits_history_size的值。等待事件需要执行结束时才被添加到events_waits_history表中（没有结束时保存在events_waits_current表）。当添加新事件到events_waits_history表时，如果该表已满，则会丢弃每个线程较旧的事件

events_waits_history与events_waits_current表定义相同

PS：允许执行TRUNCATE TABLE语句

events_waits_history_long 表

events_waits_history_long表包含最近的N个等待事件（所有线程的事件）。在server启动时，N的值会自动调整。如果要显式设置这个N大小，可以在server启动之前调整系统参数

performance_schema_events_waits_history_long_size的值。等待事件需要执行结束时才会被添加到events_waits_history_long表中（没有结束时保存在events_waits_current表），当添加新事件到events_waits_history_long表时，如果该表已满，则会丢弃该表中较旧的事件。

events_waits_history_long与events_waits_current表结构相同

PS：允许使用TRUNCATE TABLE语句

| 阶段事件表

阶段事件记录表与等待事件记录表一样，也有三张表，这些表记录了当前与最近在MySQL实例中发生了哪些阶段事件，时间消耗是多少。阶段指的是语句执行过程中的步骤，例如：parsing 、opening tables、filesort操作等。

在以往我们查看语句执行的阶段状态，常常使用SHOW PROCESSLIST语句或查询INFORMATION_SCHEMA.PROCESSLIST表来获得，但processlist方式能够查询到的信息比较有限且转瞬即逝，我们常常需要结合profiling功能来进一步统计分析语句执行的各个阶段的开销等，现在，我们不需要这么麻烦，直接使用performance_schema的阶段事件就既可以查询到所有的语句执行阶段，也可以查询到各个阶段对应的开销，因为是记录在表中，所以更可以使用SQL语句对这些数据进行排序、统计等操作

要注意：阶段事件相关配置中，setup_instruments表中stage/开头的绝大多数instruments配置默认没有开启（少数stage/开头的instruments除外，如DDL语句执行过程的stage/innodb/alter*开头的instruments默认开启的），setup_consumers表中stages相关的consumers配置默认没有开启

events_stages_current 表

events_stages_current表包含当前阶段事件的监控信息，每个线程一行记录显示线程正在执行的stage事件的状态

在包含stage事件记录的表中，events_stages_current是基准表，包含stage事件记录的其他表（如：events_stages_history和events_stages_history_long表）的数据在逻辑上都来自events_stages_current表（汇总表除外）

表记录内容示例(以下仍然是一个执行select sleep(100);语句的线程，但这里是阶段事件信息)

root@localhost : performance_schema 12:24:40> select * from events_stages_current where EVENT_NAME='stage/sql/User sleep'G;*************************** 1. row *************************** THREAD_ID: 46 EVENT_ID: 280 END_EVENT_ID: NULL EVENT_NAME: stage/sql/User sleep SOURCE: item_func.cc:6056 TIMER_START: 14645080545642000 TIMER_END: 14698320697396000 TIMER_WAIT: 53240151754000WORK_COMPLETED: NULLWORK_ESTIMATED: NULLNESTING_EVENT_ID: 266NESTING_EVENT_TYPE: STATEMENT1 row in set (0.00 sec)

以上的输出结果与语句的等待事件形式类似，这里不再赘述，events_stages_current表完整的字段含义如下：

THREAD_ID，EVENT_ID：与事件关联的线程ID和当前事件ID，可以使用THREAD_ID和EVENT_ID列值来唯一标识该行，这两行的值作为组合条件时不会出现相同的数据行

END_EVENT_ID：当一个事件开始执行时，对应行记录的该列值被设置为NULL，当一个事件执行结束时，对应的行记录的该列值被更新为该事件的ID

EVENT_NAME：产生事件的instruments的名称。该列值来自setup_instruments表的NAME值。instruments名称可能具有多个部分并形成层次结构，如："stage/sql/Slave has read all relay log; waiting for more updates"，其中stage是顶级名称，sql是二级名称，Slave has read all relay log; waiting for more updates是第三级名称。详见链接：

https://dev.mysql.com/doc/refman/5.7/en/performance-schema-instrument-naming.html

SOURCE：源文件的名称及其用于检测该事件的代码位于源文件中的行号

TIMER_START，TIMER_END，TIMER_WAIT：事件的时间信息。这些值的单位是皮秒（万亿分之一秒）。 TIMER_START和TIMER_END值表示事件的开始时间和结束时间。TIMER_WAIT是事件执行消耗的时间（持续时间）

如果事件未执行完成，则TIMER_END为当前时间，TIMER_WAIT为当前为止所经过的时间（TIMER_END - TIMER_START）

如果instruments配置表setup_instruments中对应的instruments 的TIMED字段被设置为 NO，则该instruments禁用时间收集功能，那么事件采集的信息记录中，TIMER_START，TIMER_END和TIMER_WAIT字段值均为NULL

WORK_COMPLETED，WORK_ESTIMATED：这些列提供了阶段事件进度信息

表中的WORK_COMPLETED和WORK_ESTIMATED两列，它们共同协作显示每一行的进度显示：

* 1)、WORK_COMPLETED：显示阶段事件已完成的工作单元数

* 2)、WORK_ESTIMATED：显示预计阶段事件将要完成的工作单元数

如果instruments没有提供进度相关的功能，则该instruments执行事件采集时就不会有进度信息显示，WORK_COMPLETED和WORK_ESTIMATED列都会显示为NULL。如果进度信息可用，则进度信息如何显示取决于instruments的执行情况。performance_schema表提供了一个存储进度数据的容器，但不会假设你会定义何种度量单位来使用这些进度数据：

* 1)、“工作单元”是在执行过程中随时间增加而增加的整数度量，例如执行过程中的字节数、行数、文件数或表数。对于特定instruments的“工作单元”的定义留给提供数据的instruments代码

* 2)、WORK_COMPLETED值根据检测的代码不同，可以一次增加一个或多个单元

* 3)、WORK_ESTIMATED值根据检测代码，可能在阶段事件执行过程中发生变化

阶段事件进度指示器的表现行为有以下几种情况：

* 1)、instruments不支持进度：没有可用进度数据， WORK_COMPLETED和WORK_ESTIMATED列都显示为NULL

* 2) 、instruments支持进度但对应的工作负载总工作量不可预估（无限进度）：只有WORK_COMPLETED列有意义（因为他显示正在执行的进度显示），WORK_ESTIMATED列此时无效，显示为0，因为没有可预估的总进度数据。通过查询events_stages_current表来监视会话，监控应用程序到目前为止执行了多少工作，但无法报告对应的工作是否接近完成

* 3)、instruments支持进度，总工作量可预估（有限进度）：WORK_COMPLETED和WORK_ESTIMATED列值有效。这种类型的进度显示可用于online DDL期间的copy表阶段监视。通过查询events_stages_current表，可监控应用程序当前已经完成了多少工作，并且可以通过WORK_COMPLETED / WORK_ESTIMATED计算的比率来预估某个阶段总体完成百分比

NESTING_EVENT_ID：事件的嵌套事件EVENT_ID值（父事件ID）

NESTING_EVENT_TYPE：嵌套事件类型。有效值为：TRANSACTION，STATEMENT，STAGE，WAIT。阶段事件的嵌套事件通常是statement

对于events_stages_current表允许使用TRUNCATE TABLE语句来进行清理

PS：stage事件拥有一个进度展示功能，我们可以利用该进度展示功能来了解一些长时间执行的SQL的进度百分比，例如：对于需要使用COPY方式执行的online ddl，那么需要copy的数据量是一定的，可以明确的，so..这就可以为"stage/sql/copy to tmp table stage" instruments提供一个有结束边界参照的进度数据信息，这个instruments所使用的工作单元就是需要复制的数据行数，此时WORK_COMPLETED和WORK_ESTIMATED列值都是有效的可用的，两者的计算比例就表示当前copy表完成copy的行数据百分比。

要查看copy表阶段事件的正在执行的进度监视功能，需要打开相关的instruments和consumers，然后查看events_stages_current表，如下：

# 配置相关instruments和consumersUPDATE setup_instruments SET ENABLED='YES' WHERE NAME='stage/sql/copy to tmp table';UPDATE setup_consumers SET ENABLED='YES' WHERE NAME LIKE 'events_stages_%';# 然后在执行ALTER TABLE语句期间，查看events_stages_current表

events_stages_history 表

events_stages_history表包含每个线程最新的N个阶段事件。在server启动时，N的值会自动调整。如果要显式设置N值大小，可以在server启动之前设置系统变量performance_schema_events_stages_history_size的值。stages事件在执行结束时才添加到events_stages_history表中。当添加新事件到events_stages_history表时，如果events_stages_history表已满，则会丢弃对应线程较旧的事件events_stages_history与events_stages_current表结构相同

PS：允许使用TRUNCATE TABLE语句

events_stages_history_long 表

events_stages_history_long表包含最近的N个阶段事件。在server启动时，N的值会自动调整。如果要显式设置N值大小，可以在server启动之前设置系统变量performance_schema_events_stages_history_long_size的值。stages事件执行结束时才会添加到events_stages_history_long表中，当添加新事件到events_stages_history_long表时，如果events_stages_history_long表已满，则会丢弃该表中较旧的事件events_stages_history_long与events_stages_current表结构相同

PS：允许使用TRUNCATE TABLE语句

| 语句事件表

语句事件记录表与等待事件记录表一样，也有三张表，这些表记录了当前与最近在MySQL实例中发生了哪些语句事件，时间消耗是多少。记录了各种各样的语句执行产生的语句事件信息。

要注意：语句事件相关配置中，setup_instruments表中statement/*开头的所有instruments配置默认开启，setup_consumers表中statements相关的consumers配置默认开启了events_statements_current、events_statements_history、statements_digest（对应events_statements_summary_by_digest表，详见后续章节）但没有开启events_statements_history_long。

events_statements_current 表

events_statements_current表包含当前语句事件，每个线程只显示一行最近被监视语句事件的当前状态。

在包含语句事件行的表中，events_statements_current当前事件表是基础表。其他包含语句事件表中的数据在逻辑上来源于当前事件表（汇总表除外）。例如：events_statements_history和events_statements_history_long表是最近的语句事件历史的集合，events_statements_history表中每个线程默认保留10行事件历史信息，events_statements_history_long表中默认所有线程保留10000行事件历史信息

表记录内容示例（以下信息仍然来自select sleep(100);语句的语句事件信息）

root@localhost : performance_schema 12:36:35> select * from events_statements_current where SQL_TEXT='select sleep(100)'G;*************************** 1. row *************************** THREAD_ID: 46 EVENT_ID: 334 END_EVENT_ID: NULL EVENT_NAME: statement/sql/select SOURCE: socket_connection.cc:101 TIMER_START: 15354770719802000 TIMER_END: 15396587017809000 TIMER_WAIT: 41816298007000 LOCK_TIME: 0 SQL_TEXT: select sleep(100) DIGEST: NULL DIGEST_TEXT: NULL CURRENT_SCHEMA: NULL OBJECT_TYPE: NULL OBJECT_SCHEMA: NULL OBJECT_NAME: NULLOBJECT_INSTANCE_BEGIN: NULL MYSQL_ERRNO: 0 RETURNED_SQLSTATE: NULL MESSAGE_TEXT: NULL ERRORS: 0 WARNINGS: 0 ROWS_AFFECTED: 0 ROWS_SENT: 0 ROWS_EXAMINED: 0CREATED_TMP_DISK_TABLES: 0CREATED_TMP_TABLES: 0 SELECT_FULL_JOIN: 0SELECT_FULL_RANGE_JOIN: 0 SELECT_RANGE: 0SELECT_RANGE_CHECK: 0 SELECT_SCAN: 0 SORT_MERGE_PASSES: 0 SORT_RANGE: 0 SORT_ROWS: 0 SORT_SCAN: 0 NO_INDEX_USED: 0NO_GOOD_INDEX_USED: 0 NESTING_EVENT_ID: NULLNESTING_EVENT_TYPE: NULLNESTING_EVENT_LEVEL: 01 row in set (0.00 sec)

以上的输出结果与语句的等待事件形式类似，这里不再赘述，events_statements_current表完整的字段含义如下：

THREAD_ID，EVENT_ID：与事件关联的线程号和事件启动时的事件编号，可以使用THREAD_ID和EVENT_ID列值来唯一标识该行，这两行的值作为组合条件时不会出现相同的数据行

END_EVENT_ID：当一个事件开始执行时，对应行记录的该列值被设置为NULL，当一个事件执行结束时，对应的行记录的该列值被更新为该事件的ID

EVENT_NAME：产生事件的监视仪器的名称。该列值来自setup_instruments表的NAME值。对于SQL语句，EVENT_NAME值最初的instruments是statement/com/Query，直到语句被解析之后，会更改为更合适的具体instruments名称，如：statement/sql/insert

SOURCE：源文件的名称及其用于检测该事件的代码位于源文件中的行号，您可以检查源代码来确定涉及的代码

如果事件未执行完成，则TIMER_END为当前时间，TIMER_WAIT为当前为止所经过的时间（TIMER_END - TIMER_START）。

如果监视仪器配置表setup_instruments中对应的监视器TIMED字段被设置为 NO，则不会收集该监视器的时间信息，那么对于该事件采集的信息记录中，TIMER_START，TIMER_END和TIMER_WAIT字段值均为NULL

LOCK_TIME：等待表锁的时间。该值以微秒进行计算，但最终转换为皮秒显示，以便更容易与其他performance_schema中的计时器进行比较

SQL_TEXT：SQL语句的文本。如果该行事件是与SQL语句无关的command事件，则该列值为NULL。默认情况下，语句最大显示长度为1024字节。如果要修改，则在server启动之前设置系统变量performance_schema_max_sql_text_length的值

DIGEST：语句摘要的MD5 hash值，为32位十六进制字符串，如果在setup_consumers表中statement_digest配置行没有开启，则语句事件中该列值为NULL

DIGEST_TEXT：标准化转换过的语句摘要文本，如果setup_consumers表中statements_digest配置行没有开启，则语句事件中该列值为NULL。performance_schema_max_digest_length系统变量决定着在存入该表时的最大摘要语句文本的字节长度（默认为1024字节），要注意：用于计算摘要语句文本的原始语句文本字节长度由系统变量max_digest_length控制，而存入表中的字节长度由系统变量performance_schema_max_digest_length控制，所以，如果performance_schema_max_digest_length小于max_digest_length时，计算出的摘要语句文本如果大于了performance_schema_max_digest_length定义的长度会被截断

CURRENT_SCHEMA：语句使用的默认数据库（使用use db_name语句即可指定默认数据库），如果没有则为NULL

OBJECT_SCHEMA，OBJECT_NAME，OBJECT_TYPE：对于嵌套语句（存储程序最终是通过语句调用的，所以如果一个语句是由存储程序调用的，虽然说这个语句事件是嵌套在存储程序中的，但是实际上对于事件类型来讲，仍然是嵌套在语句事件中），这些列包含有关父语句的信息。如果不是嵌套语句或者是父语句本身产生的事件，则这些列值为NULL

OBJECT_INSTANCE_BEGIN：语句的唯一标识，该列值是内存中对象的地址

MYSQL_ERRNO：语句执行的错误号，此值来自代码区域的语句诊断区域

RETURNED_SQLSTATE：语句执行的SQLSTATE值，此值来自代码区域的语句诊断区域

MESSAGE_TEXT：语句执行的具体错误信息，此值来自代码区域的语句诊断区域

ERRORS：语句执行是否发生错误。如果SQLSTATE值以00（完成）或01（警告）开始，则该列值为0。其他任何SQLSTATE值时，该列值为1

WARNINGS：语句警告数，此值来自代码区域的语句诊断区域

ROWS_AFFECTED：受该语句影响的行数。有关“受影响”的含义的描述，参见连接：https://dev.mysql.com/doc/refman/5.7/en/mysql-affected-rows.html

使用mysql_query（）或mysql_real_query（）函数执行语句后，可能会立即调用mysql_affected_rows（）函数。如果是UPDATE，DELETE或INSERT，则返回最后一条语句更改、删除、插入的行数。对于SELECT语句，mysql_affected_rows（）的工作方式与mysql_num_rows（）一样（在执行结果最后返回的信息中看不到effected统计信息）

对于UPDATE语句，受影响的行值默认为实际更改的行数。如果在连接到mysqld时指定了CLIENT_FOUND_ROWS标志给mysql_real_connect（）函数，那么affected-rows的值是“found”的行数。即WHERE子句匹配到的行数

对于REPLACE语句，如果发生新旧行替换操作，则受影响的行值为2，因为在这种情况下，实际上是先删除旧值，后插入新值两个行操作

对于INSERT … ON DUPLICATE KEY UPDATE语句，如果行作为新行插入，则每行的affected计数为1，如果发生旧行更新为新行则每行affected计数为2，如果没有发生任何插入和更新，则每行的affected计数为0 （但如果指定了CLIENT_FOUND_ROWS标志，则没有发生任何的插入和更新时，即set值就为当前的值时，每行的受影响行值计数为1而不是0）

在存储过程的CALL语句调用之后，mysql_affected_rows（）返回的影响行数是存储程序中的最后一个语句执行的影响行数值，如果该语句返回-1，则存储程序最终返回0受影响。所以在存储程序执行时返回的影响行数并不可靠，但是你可以自行在存储程序中实现一个计数器变量在SQL级别使用ROW_COUNT（）来获取各个语句的受影响的行值并相加，最终通过存储程序返回这个变量值。

在MySQL 5.7中，mysql_affected_rows（）为更多的语句返回一个有意义的值。

* 1)、对于DDL语句，row_count()函数返回0，例如：CREATE TABLE、ALTER TABLE、DROP TABLE之类的语句

* 2)、对于除SELECT之外的DML语句：row_count()函数返回实际数据变更的行数。例如：UPDATE、INSERT、DELETE语句，现在也适用于LOAD DATA INFILE之类的语句，大于0的返回值表示DML语句做了数据变更，如果返回为0，则表示DML语句没有做任何数据变更，或者没有与where子句匹配的记录，如果返回-1则表示语句返回了错误

* 3)、对于SELECT语句：row_count()函数返回-1，例如：SELECT * FROM t1语句，ROW_COUNT（）返回-1（对于select语句，在调用mysql_store_result（）之前调用了mysql_affected_rows（）返回了）。但是对于SELECT * FROM t1 INTO OUTFILE‘file_name‘这样的语句，ROW_COUNT（）函数将返回实际写入文件中的数据行数

* 4)、对于SIGNAL语句：row_count()函数返回0

* 5)、因为mysql_affected_rows（）返回的是一个无符号值，所以row_count()函数返回值小于等于0时都转换为0值返回或者不返回给effected值，row_count()函数返回值大于0时则返回给effected值

ROWS_SENT：语句返回给客户端的数据行数

ROWS_EXAMINED：在执行语句期间从存储引擎读取的数据行数

CREATED_TMP_DISK_TABLES：像Created_tmp_disk_tables状态变量一样的计数值，但是这里只用于这个事件中的语句统计而不针对全局、会话级别

CREATED_TMP_TABLES：像Created_tmp_tables状态变量一样的计数值，但是这里只用于这个事件中的语句统计而不针对全局、会话级别

SELECT_FULL_JOIN：像Select_full_join状态变量一样的计数值，但是这里只用于这个事件中的语句统计而不针对全局、会话级别

SELECT_FULL_RANGE_JOIN：像Select_full_range_join状态变量一样的计数值，但是这里只用于这个事件中的语句统计而不针对全局、会话级别

SELECT_RANGE：就像Select_range状态变量一样的计数值，但是这里只用于这个事件中的语句统计而不针对全局、会话级别

SELECT_RANGE_CHECK：像Select_range_check状态变量一样的计数值，但是这里只用于这个事件中的语句统计而不针对全局、会话级别

SELECT_SCAN：像Select_scan状态变量一样的计数值，但是这里只用于这个事件中的语句统计而不针对全局、会话级别

SORT_MERGE_PASSES：像Sort_merge_passes状态变量一样的计数值，但是这里只用于这个事件中的语句统计而不针对全局、会话级别

SORT_RANGE：像Sort_range状态变量一样的计数值，但是这里只用于这个事件中的语句统计而不针对全局、会话级别

SORT_ROWS：像Sort_rows状态变量一样的计数值，但是这里只用于这个事件中的语句统计而不针对全局、会话级别

SORT_SCAN：像Sort_scan状态变量一样的计数值，但是这里只用于这个事件中的语句统计而不针对全局、会话级别

NO_INDEX_USED：如果语句执行表扫描而不使用索引，则该列值为1，否则为0

NO_GOOD_INDEX_USED：如果服务器找不到用于该语句的合适索引，则该列值为1，否则为0

NESTING_EVENT_ID，NESTING_EVENT_TYPE，NESTING_EVENT_LEVEL：这三列与其他列结合一起使用，为顶级（未知抽象的语句或者说是父语句）语句和嵌套语句（在存储的程序中执行的语句）提供以下事件信息

对于顶级语句：

OBJECT_TYPE = NULL,OBJECT_SCHEMA = NULL,OBJECT_NAME = NULL,NESTING_EVENT_ID = NULL,NESTING_EVENT_TYPE = NULL,NESTING_LEVEL = 0

对于嵌套语句：OBJECT_TYPE =父语句对象类型,OBJECT_SCHEMA =父语句数据库级名称,OBJECT_NAME =父语句表级对象名称,NESTING_EVENT_ID =父语句EVENT_ID,NESTING_EVENT_TYPE =‘STATEMENT‘,NESTING_LEVEL =父语句NESTING_LEVEL加一，例如：1，表示父语句的下一层嵌套语句

允许使用TRUNCATE TABLE语句

events_statements_history 表

events_statements_history表包含每个线程最新的N个语句事件。在server启动时，N的值会自动调整。要显式设置N的大小，可以在server启动之前设置系统变量performance_schema_events_statements_history_size的值。 statement事件执行完成时才会添加到该表中。当添加新事件到该表时，如果对应线程的事件在该表中的配额已满，则会丢弃对应线程的较旧的事件

events_statements_history与events_statements_current表结构相同

PS：允许使用TRUNCATE TABLE语句

events_statements_history_long 表

events_statements_history_long表包含最近的N个语句事件。在server启动时，N的值会自动调整。要显式设置N的大小，可以在server启动之前设置系统变量performance_schema_events_statements_history_long_size的值。 statement事件需要执行结束时才会添加到该表中。当添加新事件到该表时，如果该表的全局配额已满，则会丢弃该表中较旧的事件

events_statements_history_long与events_statements_current表结构相同

PS：允许使用TRUNCATE TABLE语句

事务事件表

事务事件记录表与等待事件记录表一样，也有三张表，这些表记录了当前与最近在MySQL实例中发生了哪些事务事件，时间消耗是多少

要注意：事务事件相关配置中，setup_instruments表中只有一个名为transaction的instrument，默认关闭，setup_consumers表中transactions相关的consumers配置默认关闭了

events_transactions_current 表

events_transactions_current表包含当前事务事件信息，每个线程只保留一行最近事务的事务事件

在包含事务事件信息的表中，events_transactions_current是基础表。其他包含事务事件信息的表中的数据逻辑上来源于当前事件表。例如：events_transactions_history和events_transactions_history_long表分别包含每个线程最近10行事务事件信息和全局最近10000行事务事件信息

表记录内容示例（以下信息来自对某表执行了一次select等值查询的事务事件信息）

root@localhost : performance_schema 12:50:10> select * from events_transactions_currentG;*************************** 1. row *************************** THREAD_ID: 46 EVENT_ID: 38685 END_EVENT_ID: 38707 EVENT_NAME: transaction STATE: COMMITTED TRX_ID: 422045139261264 GTID: AUTOMATIC XID_FORMAT_ID: NULL XID_GTRID: NULL XID_BQUAL: NULL XA_STATE: NULL SOURCE: handler.cc:1421 TIMER_START: 16184509764409000 TIMER_END: 16184509824175000 TIMER_WAIT: 59766000 ACCESS_MODE: READ WRITE ISOLATION_LEVEL: READ COMMITTED AUTOCOMMIT: YES NUMBER_OF_SAVEPOINTS: 0NUMBER_OF_ROLLBACK_TO_SAVEPOINT: 0NUMBER_OF_RELEASE_SAVEPOINT: 0 OBJECT_INSTANCE_BEGIN: NULL NESTING_EVENT_ID: 38667 NESTING_EVENT_TYPE: STATEMENT1 row in set (0.00 sec)

以上的输出结果与语句的等待事件形式类似，这里不再赘述，events_transactions_current表完整字段含义如下：

END_EVENT_ID：当一个事件开始执行时，对应行记录的该列值被设置为NULL，当一个事件执行结束时，对应的行记录的该列值被更新为该事件的ID

EVENT_NAME：收集该事务事件的instruments的名称。来自setup_instruments表的NAME列值

STATE：当前事务状态。有效值为：ACTIVE（执行了START TRANSACTION或BEGIN语句之后，事务未提交或未回滚之前）、COMMITTED（执行了COMMIT之后）、ROLLED BACK（执行了ROLLBACK语句之后）

TRX_ID：未使用，字段值总是为NULL

GTID：包含gtid_next系统变量的值，其值可能是格式为：UUID:NUMBER的GTID，也可能是：ANONYMOUS、AUTOMATIC。对于AUTOMATIC列值的事务事件，GTID列在事务提交和对应事务的GTID实际分配时都会进行更改(如果gtid_mode系统变量为ON或ON_PERMISSIVE，则GTID列将更改为事务的GTID，如果gtid_mode为OFF或OFF_PERMISSIVE，则GTID列将更改为ANONYMOUS）

XID_FORMAT_ID，XID_GTRID和XID_BQUAL：XA事务标识符的组件。关于XA事务语法详见链接：https://dev.mysql.com/doc/refman/5.7/en/xa-statements.html

XA_STATE：XA事务的状态。有效值为：ACTIVE（执行了XA START之后，未执行其他后续XA语句之前）、IDLE（执行了XA END语句之后，未执行其他后续XA语句之前）、PREPARED（执行了XA PREPARE语句之后，未执行其他后续XA语句之前）、ROLLED BACK（执行了XA ROLLBACK语句之后，未执行其他后续XA语句之前）、COMMITTED（执行了XA COMMIT语句之后）

SOURCE：源文件的名称及其用于检测该事件的代码位于源文件中的行号，您可以检查源代码来确定涉及的代码

如果事件未执行完成，则TIMER_END为当前时间，TIMER_WAIT为当前为止所经过的时间（TIMER_END - TIMER_START）

ACCESS_MODE：事务访问模式。有效值为：READ ONLY或READ WRITE

ISOLATION_LEVEL：事务隔离级别。有效值为：REPEATABLE READ、READ COMMITTED、READ UNCOMMITTED、SERIALIZABLE

AUTOCOMMIT：在事务开始时是否启用了自动提交模式，如果启用则为YES，没有启用则为NO

NUMBER_OF_SAVEPOINTS，NUMBER_OF_ROLLBACK_TO_SAVEPOINT，NUMBER_OF_RELEASE_SAVEPOINT：在事务内执行的SAVEPOINT，ROLLBACK TO SAVEPOINT和RELEASE SAVEPOINT语句的数量

OBJECT_INSTANCE_BEGIN：未使用，字段值总是为NULL

NESTING_EVENT_ID：嵌套事务事件的父事件EVENT_ID值

NESTING_EVENT_TYPE：嵌套事件类型。有效值为：TRANSACTION、STATEMENT、STAGE、WAIT （由于事务无法嵌套，因此该列值不会出现TRANSACTION）

允许使用TRUNCATE TABLE语句

events_transactions_history 表

events_transactions_history表包含每个线程最近的N个事务事件。在server启动时，N的值会自动调整。要显式设置N的大小，可以在server启动之前设置系统变量

performance_schema_events_transactions_history_size的值。事务事件未执行完成之前不会添加到该表中。当有新的事务事件添加到该表时，如果该表已满，则会丢弃对应线程较旧的事务事件

events_transactions_history与events_transactions_current表结构相同

PS：允许使用TRUNCATE TABLE语句

events_transactions_history_long 表

events_transactions_history_long表包含全局最近的N个事务事件。在server启动时，N的值会自动调整。要显式设置N的大小，可以在server启动之前设置系统变量

performance_schema_events_transactions_history_long_size的值。事务事件在执行完之前不会添加到该表中。当添加新事务事件时，如果该表已满，则会丢弃较旧的事件

events_transactions_history_long与events_transactions_current表结构相同

PS：允许使用TRUNCATE TABLE语句

Mysql 事件记录 | performance_schema全方位介绍

标签：存储获取回滚 auto sort read ati 之一 cal

小编还为您整理了以下内容，可能对您也有帮助：

mysql自带的数据库， information_schema | mysql | performance_schema | sakila |这四个是干嘛的？

第一个数据库INFORMATION_SCHEMA：提供了访问数据库元数据的方式。

元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。有些时候用于表述该信息的其他术语包括“数据词典”和“系统目录”。

INFORMATION_SCHEMA是信息数据库，其中保存着关于MySQL服务器所维护的所有其他数据库的信息。在INFORMATION_SCHEMA中，有数个只读表。它们实际上是视图，而不是基本表，因此，你将无法看到与之相关的任何文件。

每位MySQL用户均有权访问这些表，但仅限于表中的特定行，在这类行中含有用户具有恰当访问权限的对象。

第二个数据库mysql：这个是mysql的核心数据库，类似于sql server中的master表，主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息。不可以删除，如果对mysql不是很了解，也不要轻易修改这个数据库里面的表信息。

什么是MySql数据库

MySQL数据库:

MySQL是一种开放源代码的关系型数据库管理系统（RDBMS），使用最常用的数据库管理语言--结构化查询语言（SQL）进行数据库管理。

MySQL是开放源代码的，因此任何人都可以在General Public License的许可下下载并根据个性化的需要对其进行修改。

MySQL因为其速度、可靠性和适应性而备受关注。大多数人都认为在不需要事务化处理的情况下，MySQL是管理内容最好的选择。

数据库简介:

MySQL是一种开放源代码的关系型数据库管理系统（RDBMS），MySQL数据库系统使用最常用的数据库管理语言--结构化查询语言（SQL）进行数据库管理。

由于MySQL是开放源代码的，因此任何人都可以在General Public License的许可下下载并根据个性化的需要对其进行修改。MySQL因为其速度、可靠性和适应性而备受关注。大多数人都认为在不需要事务化处理的情况下，MySQL是管理内容最好的选择。

MySQL这个名字，起源不是很明确。一个比较有影响的说法是，基本指南和大量的库和工具带有前缀“my”已经有10年以上，而且不管怎样，MySQL AB创始人之一的Monty Widenius的女儿也叫My。这两个到底是哪一个给出了MySQL这个名字至今依然是个迷，包括开发者在内也不知道。

MySQL的海豚标志的名字叫“sakila”，它是由MySQL AB的创始人从用户在“海豚命名”的竞赛中建议的大量的名字表中选出的。获胜的名字是由来自非洲斯威士兰的开源软件开发者Ambrose Twebaze提供。根据Ambrose所说，Sakila来自一种叫SiSwati的斯威士兰方言，也是在Ambrose的家乡乌干达附近的坦桑尼亚的Arusha的一个小镇的名字。

MySQL，虽然功能未必很强大，但因为它的开源、广泛传播，导致很多人都了解到这个数据库。它的历史也富有传奇性。

MySQL数据库历史:

MySQL的历史最早可以追溯到1979年，那时Oracle也才小打小闹，微软的SQL Server影子都没有。有一个人叫Monty Widenius, 为一个叫TcX的小公司打工，并用BASIC设计了一个报表工具，可以在4M主频和16KB内存的计算机上运行。过了不久，又将此工具，使用C语言重写，移植到Unix平台，当时，它只是一个很底层的面向报表的存储引擎。这个工具叫做Unireg。

可是，这个小公司资源有限，Monty天赋极高，面对资源有限的不利条件，他反而更能发挥潜能，总是力图写出最高效的代码。并因此养成了习惯。与Monty同在一起的还有一些别的同事，很少有人能坚持把那些代码持续写到20年后，而Monty却做到了。

1990年，TcX的customer 中开始有人要求要为它的API提供SQL支持，当时，有人想到了直接使用商用数据库算了，但是Monty觉得商用数据库的速度难令人满意。于是，他直接借助于mSQL的代码，将它集成到自己的存储引擎中。但不巧的是，效果并不太好。于是, Monty雄心大起，决心自己重写一个SQL支持。

1996年，MySQL 1.0发布,只面向一小拨人，相当于内部发布。到了96年10月，MySQL 3.11.1发布了，呵呵，没有2.x版本。最开始，只提供了Solaris下的二进制版本。一个月后，Linux版本出现了。

紧接下来的两年里，MySQL依次移植到各个平台下。它发布时，采用的许可策略，有些与众不同：允许免费商用，但是不能将MySQL与自己的产品绑定在一起发布。如果想一起发布，就必须使用特殊许可，意味着要花银子。当然，商业支持也是需要花银子的。其它的，随用户怎么用都可以。这种特殊许可为MySQL带来了一些收入，从而为它的持续发展打下了良好的基础。（细想想，PostgreSQL曾经有几年限入低谷，可能与它的完全免费，不受任何有关系）。

MySQL3.22应该是一个标志性的版本，提供了基本的SQL支持。

MySQL关系型数据库于1998年1月发行第一个版本。它使用系统核心提供的多线程机制提供完全的多线程运行模式，提供了面向C、C++、Eiffel、Java、Perl、PHP、Python以及Tcl等编程语言的编程接口（APIs），支持多种字段类型并且提供了完整的操作符支持查询中的SELECT和WHERE操作。

MySQL是开放源代码的，因此任何人都可以在General Public License的许可下下载并根据个性化的需要对其进行修改。MySQL因为其速度、可靠性和适应性而备受关注。

1999-2000年，有一家公司在瑞典成立了，叫MySQL AB (AB是瑞典语“股份公司”的意思)。雇了几个人,与Sleepycat合作，开发出了 Berkeley DB引擎, 因为BDB支持事务处理，所以，MySQL从此开始支持事务处理了。

2000年4月，MySQL对旧的存储引擎进行了整理，命名为MyISAM。同时，2001年，Heikiki Tuuri向MySQL提出建议，希望能集成他们的存储引擎InnoDB，这个引擎同样支持事务处理，还支持行级锁。

如今，遗憾的是，BDB和InnoDB好像都被Oracle收购了，为了消灭竞争对手，哪怕是开源的，都是不择手段。

MySQL与InnoDB的正式结合版本是4.0。

到了MySQL5.0，2003年12月，开始有View,存储过程之类的东东，当然，其间, bug也挺多。

在2008年1月16号 MySQL被Sun公司收购。

最近，MySQL的创始人Monty Widenius已经向Sun提交了辞呈。head都要走了。

据说，被Sun收购的公司多薄命，不知道MySQL今后前途如何，希望一路走好。相信MySQL的生命力还是很长久的。

时至今日 mysql 和 php 的结合绝对是完美.很多大型的网站也用到mysql数据库.mysql的发展前景是非常光明的!

MySQL常用命令:

1:使用SHOW语句找出在服务器上当前存在什么数据库：

mysql> SHOW DATABASES;

2:2、创建一个数据库MYSQLDATA

mysql> CREATE DATABASE MYSQLDATA;

3:选择你所创建的数据库

mysql> USE MYSQLDATA; (按回车键出现Database changed 时说明操作成功！)

4:查看现在的数据库中存在什么表

mysql> SHOW TABLES;

5:创建一个数据库表

mysql> CREATE TABLE MYTABLE (name VARCHAR(20), sex CHAR(1));

6:显示表的结构：

mysql> DESCRIBE MYTABLE;

7:往表中加入记录

mysql> insert into MYTABLE values (”hyq”,”M”);

8:用文本方式将数据装入数据库表中（例如D:/mysql.txt）

mysql> LOAD DATA LOCAL INFILE “D:/mysql.txt” INTO TABLE MYTABLE;

9:导入.sql文件命令（例如D:/mysql.sql）

mysql>use database;

mysql>source d:/mysql.sql;

10:删除表

mysql>drop TABLE MYTABLE;

11:清空表

mysql>delete from MYTABLE;

12:更新表中数据

mysql>update MYTABLE set sex=”f” where name=’hyq’;

全局管理权限对应解释：

FILE: 在MySQL服务器上读写文件。

PROCESS: 显示或杀死属于其它用户的服务线程。

RELOAD: 重载访问控制表，刷新日志等。

SHUTDOWN: 关闭MySQL服务。

数据库/数据表/数据列权限：

ALTER: 修改已存在的数据表(例如增加/删除列)和索引。

CREATE: 建立新的数据库或数据表。

DELETE: 删除表的记录。

DROP: 删除数据表或数据库。

INDEX: 建立或删除索引。

INSERT: 增加表的记录。

SELECT: 显示/搜索表的记录。

UPDATE: 修改表中已存在的记录。

特别的权限：

ALL: 允许做任何事(和root一样)。

USAGE: 只允许登录–其它什么也不允许做。

MySQL数据库导入方法:

MySQL数据库的导入,有两种方法：

1) 先导出数据库SQL脚本，再导入；

2) 直接拷贝数据库目录和文件。

在不同操作系统或MySQL版本情况下，直接拷贝文件的方法可能会有不兼容的情况发生。

所以一般推荐用SQL脚本形式导入。下面分别介绍两种方法。

2. 方法一 SQL脚本形式

操作步骤如下：

2.1. 导出SQL脚本

在原数据库服务器上，可以用phpMyAdmin工具，或者mysqlmp命令行，导出SQL脚本。

2.1.1 用phpMyAdmin工具

导出选项中，选择导出“结构”和“数据”，不要添加“DROP DATABASE”和“DROP TABLE”选项。

选中“另存为文件”选项，如果数据比较多，可以选中“gzipped”选项。

将导出的SQL文件保存下来。

2.1.2 用mysqlmp命令行

命令格式

mysqlmp -u 用户名 -p 数据库名 > 数据库名.sql

范例：

mysqlmp -u root -p abc > abc.sql

（导出数据库abc到abc.sql文件）

提示输入密码时，输入该数据库用户名的密码。

2.2. 创建空的数据库

通过主控界面/控制面板，创建一个数据库。假设数据库名为abc，数据库全权用户为abc_f。

2.3. 将SQL脚本导入执行

同样是两种方法，一种用phpMyAdmin（mysql数据库管理）工具，或者mysql命令行。

2.3.1 用phpMyAdmin工具

从控制面板，选择创建的空数据库，点“管理”，进入管理工具页面。

在"SQL"菜单中，浏览选择刚才导出的SQL文件，点击“执行”以上载并执行。

注意：phpMyAdmin对上载的文件大小有，php本身对上载文件大小也有，如果原始sql文件

比较大，可以先用gzip对它进行压缩，对于sql文件这样的文本文件，可获得1:5或更高的压缩率。

gzip使用方法：

# gzip xxxxx.sql

得到

xxxxx.sql.gz文件。

提示输入密码时，输入该数据库用户名的密码。

3 直接拷贝

如果数据库比较大，可以考虑用直接拷贝的方法，但不同版本和操作系统之间可能不兼容，要慎用。

3.1 准备原始文件

用tar打包为一个文件

3.2 创建空数据库

3.3 解压

在临时目录中解压，如：

cd /tmp

tar zxf mydb.tar.gz

3.4 拷贝

将解压后的数据库文件拷贝到相关目录

cd mydb/

cp * /var/lib/mysql/mydb/

对于FreeBSD:

cp * /var/db/mysql/mydb/

3.5 权限设置

将拷贝过去的文件的属主改为mysql:mysql，权限改为660

chown mysql:mysql /var/lib/mysql/mydb/*

chmod 660 /var/lib/mysql/mydb/*

Mssql转换mysql的方法:

1.导表结构

使用MySQL生成create脚本的方法。找到生成要导出的脚本，按MySQL的语法修改一下到MySQL数据库中创建该表的列结构什么的。

2.导表数据

在MSSQL端使用bcp导出文本文件:

bcp “Select * FROM dbname.dbo.tablename;” queryout tablename.txt -c -Slocalhostdb2005 -Usa

其中”"中是要导出的sql语句,-c指定使用t进行字段分隔,使用n进行记录分隔,-S指定数据库服务器及实例,-U指定用户名,-P指定密码.

在MySQL端使用mysqlimport 导入文本文件到相应表中

mysqlimport -uroot -p databasename /home/test/tablename.txt

其中-u指定用户名,-p指定密码,databasename指定数据库名称,表名与文件名相同

MySQL备份与恢复:

MySQL备份恢复数据的一般步骤

备份一个数据库的例子：

1、备份前读锁定涉及的表

mysql>LOCK TABLES tbl1 READ,tbl1 READ,…

如果，你在mysqlmp实用程序中使用--lock-tables选项则不必使用如上SQL语句。

2、导出数据库中表的结构和数据

shell>mysqlmp --opt db_name>db_name.sql

3、启用新的更新日志

shell>mysqladmin flush-logs

这样可以记录你备份后的数据改变为恢复数据准备。

4、解除表的读锁

mysql>UNLOCK TABLES;

为了加速上述过程，你可以这样做：

shell> mysqlmp --lock-tables --opt db_name>db_name.sql; mysqladmin flush-logs

但是这样可能会有点小问题。上命令在启用新的更新日志前就恢复表的读锁，

在更新繁忙的站点，可能有备份后的更新数据没有记录在新的日志中。

现在恢复上面备份的数据库

1、对涉及的表使用写锁

mysql>LOCK TABLES tbl1 WRITE,tbl1 WRITE,…

2、恢复备份的数据

shell>mysql db_name < db_name.sql

3、恢复更新日志的内容

shell>mysql --one-database db_name < hostname.nnn

假设需要使用的日志名字为hostname.nnn

4、启用新的更新日志

shell>mysqladmin flush-logs

5、解除表的写锁

mysql>UNLOCK TABLES;

希望上面的例子能给你启发，因为备份数据的手法多种多样，你所使用的和上面所述可能大不一样，但是对于备份和恢复中，表的锁定、启用新的更新日志的时机应该是类似的，仔细考虑这个问题。

MySQL数据库优化:

选择InnoDB作为存储引擎

大型产品的数据库对于可靠性和并发性的要求较高，InnoDB作为默认的MySQL存储引擎，相对于MyISAM来说是个更佳的选择。

优化数据库结构

组织数据库的schema、表和字段以降低I/O的开销，将相关项保存在一起，并提前规划，以便随着数据量的增长，性能可以保持较高的水平。

设计数据表应尽量使其占用的空间最小化，表的主键应尽可能短。·对于InnoDB表，主键所在的列在每个辅助索引条目中都是可复制的，因此如果有很多辅助索引，那么一个短的主键可以节省大量空间。

仅创建你需要改进查询性能的索引。索引有助于检索，但是会增加插入和更新操作的执行时间。

InnoDB的ChangeBuffering特性

InnoDB提供了changebuffering的配置，可减少维护辅助索引所需的磁盘I/O。大规模的数据库可能会遇到大量的表操作和大量的I/O，以保证辅助索引保持最新。当相关页面不在缓冲池里面时，InnoDB的changebuffer将会更改缓存到辅助索引条目，从而避免因不能立即从磁盘读取页面而导致耗时的I/O操作。当页面被加载到缓冲池时，缓冲的更改将被合并，更新的页面之后会刷新到磁盘。这样做可提高性能，适用于MySQL5.5及更高版本。

InnoDB页面压缩

InnoDB支持对表进行页面级的压缩。当写入数据页的时候，会有特定的压缩算法对其进行压缩。压缩后的数据会写入磁盘，其打孔机制会释放页面末尾的空块。如果压缩失败，数据会按原样写入。表和索引都会被压缩，因为索引通常是数据库总大小中占比很大的一部分，压缩可以显著节约内存，I/O或处理时间，这样就达到了提高性能和伸缩性的目的。它还可以减少内存和磁盘之间传输的数据量。MySQL5.1及更高版本支持该功能。

注意，页面压缩并不能支持共享表空间中的表。共享表空间包括系统表空间、临时表空间和常规表空间。

使用批量数据导入

在主键上使用已排序的数据源进行批量数据的导入可加快数据插入的过程。否则，可能需要在其他行之间插入行以维护排序，这会导致磁盘I/O变高，进而影响性能，增加页的拆分。关闭自动提交的模式也是有好处的，因为它会为每个插入执行日志刷新到磁盘。在批量插入期间临时转移唯一键和外键检查也可显著降低磁盘I/O。对于新建的表，最好的做法是在批量导入后创建外键/唯一键约束。

一旦你的数据达到稳定的大小，或者增长的表增加了几十或几百兆字节，就应该考虑使用OPTIMIZETABLE语句重新组织表并压缩浪费的空间。对重新组织后的表进行全表扫描所需要的I/O会更少。

优化InnoDB磁盘I/O

增加InnoDB缓冲池大小可以让查询从缓冲池访问而不是通过磁盘I/O访问。通过调整系统变量innodb_flush_method来调整清除缓冲的指标使其达到最佳水平。

MySQL的内存分配

在为MySQL分配足够的内存之前，请考虑不同领域对MySQL的内存需求。要考虑的关键领域是：并发连接——对于大量并发连接，排序和临时表将需要大量内存。在撰写本文时，对于处理3000+并发连接的数据库，16GB到32GB的RAM是足够的。

内存碎片可以消耗大约10%或更多的内存。像innodb_buffer_pool_size、key_buffer_size、query_cache_size等缓存和缓冲区要消耗大约80%的已分配内存。

日常维护

定期检查慢的查询日志并优化查询机制以有效使用缓存来减少磁盘I/O。优化它们，以扫描最少的行数，而不是进行全表扫描。

其他可以帮助DBA检查和分析性能的日志包括：错误日志、常规查询日志、二进制日志、DDL日志(元数据日志)。

定期刷新缓存和缓冲区以降低碎片化。使用OPTIMIZETABLE语句重新组织表并压缩任何可能被浪费的空间。

MySQL 5.7中新增sys schema有什么好处

性能优化利器:剖析MySQL 5.7新特征 sys schema

导读：很多团队在评估合适的时机切换到 MySQL 5.7，本文是在高可用架构群的分享，介绍 MySQL 5.7 新的性能分析利器。

李春，现任科技 MySQL 负责人，高级 MySQL 数据库专家，从事 MySQL 开发和运维工作 8 年。在担任 MySQL 数据库 leader 期间，主要负责应用架构的优化和部署，实现了阿里巴巴 3 亿产品从 Oracle 小型机到 64 台 MySQL 的平滑迁移。专注于研究 MySQL 复制、高可用、分布式和运维自动化相关领域。在大规模、分布式 MySQL 集群管理、调优、快速定位和解决问题方面有丰富经验。管理超过 1400 台 MySQL 服务器，近 3000 个实例。完成 MySQL 自动装机系统、MySQL 标准化文档和操作手册、MySQL 自动规范性检查系统、MySQL 自动信息采集系统等标准化文档和自动化运维工具。

sys schema 由来

Performance schema 引入

Oracle 早就有了 v$ 等一系列方便诊断数据库性能的工具，MySQL DBA 只有羡慕嫉妒恨的份，但是 5.7 引入的 sys schema 缓解了这个问题，让我们可以通过 sys schema 一窥 MySQL 性能损耗，诊断 MySQL 的各种问题。

说到诊断 MySQL 性能问题，不得不提在 MySQL 5.5 引入的 performance_schema，最开始引入时，MySQL 的 performance_schema 性能消耗巨大，随着版本的更新和代码优化，5.7 的 performance_schema 对 MySQL 服务器额外的消耗越来越少，我们可以放心的打开 performance_shema 来收集 MySQL 数据库的性能损耗。Tarique Saleem 同学测试了一下 sys schema 对 CPU 和 IO的额外消耗，基本在 1% - 3% 之间，有兴趣的同学可以参考他的这篇 blog：

（CPU Bound, Sysbench Read Only Mode）

performance_schema 不仅由于他的性能消耗大著名，还由于其复杂难用而臭名昭著。5.7 上的 performance schema 已经有 87 张表了，每个表都是各种统计信息的罗列；另外，他的这些表和 information_schema 中的部分表也缠夹不清，让大家用得很不习惯。

sys schema VS performance schema VS information schema

现在 MySQL 在 5.7 又新增了sys schema，它和 performance_schema 和 information schema 到底是什么关系？

Information_schema 定位基本是 MySQL 元数据信息，比如：TABLES 记录了 MySQL 有哪些表，COLUMNS 记录了各个表有哪些列。

performance_schema 记录了 MySQL 实时底层性能消耗情况，比如：events_waits_current 记录了 MySQL 各个线程当前在等待的 event。

虽然他们之间的这个定位区别并没有那么明显：比如，Information_schema 的 innodb_locks 就记录了 innodb 当前锁的信息，它并不是 MySQL 的元数据信息。sys schema 最开始是 MarkLeith 同学为了方便读取和诊断 MySQL 性能引入到 MySQL 的。所以 sys schema 定位应该是最清晰的：它包含一系列对象，这些对象能够辅助 DBA 和开发人员了解 performance schema 和 information_schema 采集的数据。

sys schema 包含了什么？

sys schema 包含一些对象，这些对象主要用于调优和故障分析。包括：

将 performance schema 和 information schema 中的数据用更容易理解的方式来总结归纳出来的“视图”。

提供 performance schema 和 information schema 配置或者生成分析报告类似操作的“存储过程”

sys schema 本身不采集和存储什么信息，它只是为程序或者用户提供一个更加方便的诊断系统性能和排除故障的“接口”。也就是说，查询 performance schema 和 information schema 配置和提供格式化服务的“存储函数” 。

避免用户在 information schema 和 performance schema 中写各种复杂的查询来获得到底谁锁了谁，每个线程消耗的内存是多少 ( 视图 memory_by_thread_by_current_bytes )，每个 SQL 执行了多少次，大致的执行时间是多少（视图 statements_with_runtimes_in_95th_percentile ）等，这些 sys schema 都直接帮你写好，你只需要直接查询就好了。

编写了一些现成的存储过程，方便你：直接使用 diagnostics() 存储过程创建用于诊断当前服务器状态的报告；使用 ps_trace_thread() 存储过程创建对应线程的图形化（ .dot类型）性能数据。

编写了一些现成的存储函数，方便你：直接使用 ps_thread_account() 存储函数获得发起这个线程的用户，使用 ps_thread_trx_info() 来获得某线程当前事务或者历史执行过的语句（ JSON 格式返回）。

当然，你也可以在 sys schema 下增加自己用于诊断 MySQL 性能的“视图”、“存储过程”和“存储函数”。

sys schema 举例

怎么利用 sys schema 来定位问题和诊断数据库性能？这里简单举一个 innodb 行锁的例子来说明。

模拟行锁

拿一个实际的场景来说 sys schema 能够辅助我们分析当前数据库上哪个 session 被锁住了，并且提供“清理”锁的语句。我们模拟一个表的某一行被锁住的情况，假设表创建语句如下：

CREATE TABLE `test2` (

`id` int(11) NOT NULL,

`name` varchar(16) DEFAULT NULL,

`age` int(11) DEFAULT NULL,

`sex` int(11) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

有一条数据如下：

mysql > select * from test2;

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

| id | name | age | sex |

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

| 2 | pickup1 | 1 | 1 |

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

我们分别在 session 1 和 session 2 上同时操作这条数据，这样的话必然对同一行记录相互有锁死的情况，然后我们通过 session 3 来查看 sys schema 里面的 innodb_lock_waits，确定到底是谁锁了谁，怎么解锁？操作步骤如下：

通过 sys.innodb_lock_waits 查看 innodb 锁表情况

对应的在 session 3上查看到的记录：

mysql > select * from sys.innodb_lock_waitsG

*************************** 1. row ***************************

wait_started: 2016-05-04 01:04:38

wait_age: 00:00:02

wait_age_secs: 2

locked_table: `test`.`test2`

locked_index: PRIMARY

locked_type: RECORD

waiting_trx_id: 5382

waiting_trx_started: 2016-05-04 00:24:21

waiting_trx_age: 00:40:19

waiting_trx_rows_locked: 4

waiting_trx_rows_modified: 0

waiting_pid: 3

waiting_query: update test2 set name='pickup3' where id=2

waiting_lock_id: 5382:31:3:3

waiting_lock_mode: X

blocking_trx_id: 5381

blocking_pid: 2

blocking_query: NULL

blocking_lock_id: 5381:31:3:3

blocking_lock_mode: X

blocking_trx_started: 2016-05-04 00:23:49

blocking_trx_age: 00:40:51

blocking_trx_rows_locked: 1

blocking_trx_rows_modified: 1

sql_kill_blocking_query: KILL QUERY 2

sql_kill_blocking_connection: KILL 2

这里我们可以看到 3 号线程（ waiting_pid: 3 ）在等待 2 号线程（ blocking_pid: 2 ）的 X 锁（ blocking_lock_mode: X ），如果需要解锁，需要杀掉 2 号线程（ sql_kill_blocking_connection: KILL 2 ）。

innodb_lock_waits 本质

其实 sys schema 的 innodb_lock_waits 只是 information schema 的视图而已。

CREATE ALGORITHM = TEMPTABLE DEFINER = `mysql.sys`@`localhost` SQL SECURITY INVOKER VIEW `innodb_lock_waits` AS

SELECT

`r`.`trx_wait_started` AS `wait_started`,

TIMEDIFF(NOW(),

`r`.`trx_wait_started`) AS `wait_age`,

TIMESTAMPDIFF(

SECOND,

`r`.`trx_wait_started`,

NOW()) AS `wait_age_secs`,

`rl`.`lock_table` AS `locked_table`,

`rl`.`lock_index` AS `locked_index`,

`rl`.`lock_type` AS `locked_type`,

`r`.`trx_id` AS `waiting_trx_id`,

`r`.`trx_started` AS `waiting_trx_started`,

TIMEDIFF(NOW(),

`r`.`trx_started`) AS `waiting_trx_age`,

`r`.`trx_rows_locked` AS `waiting_trx_rows_locked`,

`r`.`trx_rows_modified` AS `waiting_trx_rows_modified`,

`r`.`trx_mysql_thread_id` AS `waiting_pid`,

`sys`.`format_statement`(`r`.`trx_query`) AS `waiting_query`,

`rl`.`lock_id` AS `waiting_lock_id`,

`rl`.`lock_mode` AS `waiting_lock_mode`,

`b`.`trx_id` AS `blocking_trx_id`,

`b`.`trx_mysql_thread_id` AS `blocking_pid`,

`sys`.`format_statement`(`b`.`trx_query`) AS `blocking_query`,

`bl`.`lock_id` AS `blocking_lock_id`,

`bl`.`lock_mode` AS `blocking_lock_mode`,

`b`.`trx_started` AS `blocking_trx_started`,

TIMEDIFF(NOW(),

`b`.`trx_started`) AS `blocking_trx_age`,

`b`.`trx_rows_locked` AS `blocking_trx_rows_locked`,

`b`.`trx_rows_modified` AS `blocking_trx_rows_modified`,

CONCAT(

'KILL QUERY ',

`b`.`trx_mysql_thread_id`

) AS `sql_kill_blocking_query`,

CONCAT('KILL ',

`b`.`trx_mysql_thread_id`) AS `sql_kill_blocking_connection`

FROM

(

`information_schema`.`innodb_lock_waits` `w`

JOIN

`information_schema`.`innodb_trx` `b` ON((`b`.`trx_id` = `w`.`blocking_trx_id`))

)

JOIN

`information_schema`.`innodb_trx` `r` ON(

(`r`.`trx_id` = `w`.`requesting_trx_id`)

)

JOIN

`information_schema`.`innodb_locks` `bl` ON(

(

`bl`.`lock_id` = `w`.`blocking_lock_id`

)

JOIN

`information_schema`.`innodb_locks` `rl` ON(

(

`rl`.`lock_id` = `w`.`requested_lock_id`

)

ORDER BY

`r`.`trx_wait_started`

innodb_lock_waits和x$innodb_lock_waits区别

有心的同学可能会注意到，sys schema 里面有 innodb_lock_waits 和 x$innodb_lock_waits。其实 sys schema 的这些视图大部分都成对出现，其中一个的名字除了 x$ 前缀以外跟另外一个是一模一样的。例如，host_summmary_by_file_io 视图分析汇总的是根据主机汇总的文件 IO 情况，并将延迟从皮秒（ picoseconds ）转换成更加易读值（带单位）显示出来：

mysql> SELECT * FROM host_summary_by_file_io;

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

| host | ios | io_latency |

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

| localhost | 67570 | 5.38 s |

| background | 3468 | 4.18 s |

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

而 x$host_summary_by_file_io 视图分析汇总的是同样的数据，但是显示的是未格式化过的皮秒（ picosecond ）延迟值

mysql> SELECT * FROM x$host_summary_by_file_io;

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

| host | ios | io_latency |

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

| localhost | 67574 | 5380678125144 |

| background | 3474 | 4758696829416 |

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

没有 x$ 前缀的视图是为了提供更加友好，对人更加易读的输出格式。带 x$ 前缀的视图显示了数据原始格式，它方便其他工具基于这些数据进行自己的处理。需要了解非 x$ 和 x$ 视图的不同点的进一步信息。

Q&A

提问：sys schema 只是在 performance_schema 和 information_schema 之上创建视图和存储过程？

李春：对，sys schema 主要针对的其实是 iperformance schema，有部分 information schema 的表也会整理到 sys schema 中统一展现。

提问：运行 KILL 2 杀掉 2 线程？blocking_lock_mode: X 的 X 什么意思？

李春：blocking_lock_mode 的 X 是指 X 锁，exclusive 锁，排它锁，跟它对应的是 S 锁，共享锁。kill 2 是杀掉 2 号线程，这样可以将锁释放，让被锁的这个线程正常执行下去。

提问：可以放心的打开 performance_schema，为何不使用 performance_schema 再造一个 sys schema？

李春：performance schema 是 MySQL 采集数据库性能的存储空间。sys schema 其实只是对 performance schema 多个表 join 和整合。两者的定位有所不同，如果直接放在 performance schema 中，分不清哪些是基表，哪些是视图，会比较混淆。

提问：pt-query-digest 这些工具的有开始使用 sys schema 吗？

李春：没有，pt-query-digest 主要用于分析慢查和 tcpmp 的结果，跟 sys schema 的定位有部分重叠的地方，sys schema 会分析得更细，更内核，更偏底层一些，pt-query-digest 主要还是从慢查和 tcpmp 中抽取 SQL 来格式化展现。

提问：阿里这么多数据库实例，使用什么运维工具？分布式事务又是怎么解决的呢？

李春：阿里内部有非常多的运维工具，dbfree，idb 等，用于数据库资源池管理，数据库脱敏，开发测试库同步，数据库订正，表结构变更等。分布式事务主要通过业务上的修改去屏蔽掉，比如：电影买票并不是你选了座位和付款就必须在一个事务里面，抢票，选座，付款分别是自己的子事务，系统耦合性比较弱，相互通知解决问题。

提问：Oracle 有 v$，MySQL 有 x$ ？两个 $ 是完成相似功能的吗？

李春：MySQL 的 x$ 可以说是仿照 Oracle 的 v$ 来做的，但是目前离 Oracle 的那么强大的数据库诊断功能还有一些距离。

提问：数据库脱敏能否简单介绍下实现方式？

李春：开发测试人员无法访问线上数据库，需要通过一个专门的 idb 来访问，而 idb 系统每个字段都有密级定义，满足权限的才能被访问；这个系统页控制了用户是否可以访问某个表，可以访问数据表的行数，只有主管同意了，用户才能访问某个表的数据，并且加密数据是以*显示的。

MySQL 5.7中新增sys schema有什么好处

性能优化利器:剖析MySQL 5.7新特征 sys schema

导读：很多团队在评估合适的时机切换到 MySQL 5.7，本文是在高可用架构群的分享，介绍 MySQL 5.7 新的性能分析利器。

sys schema 由来

Performance schema 引入

（CPU Bound, Sysbench Read Only Mode）

sys schema VS performance schema VS information schema

现在 MySQL 在 5.7 又新增了sys schema，它和 performance_schema 和 information schema 到底是什么关系？

Information_schema 定位基本是 MySQL 元数据信息，比如：TABLES 记录了 MySQL 有哪些表，COLUMNS 记录了各个表有哪些列。

performance_schema 记录了 MySQL 实时底层性能消耗情况，比如：events_waits_current 记录了 MySQL 各个线程当前在等待的 event。

sys schema 包含了什么？

sys schema 包含一些对象，这些对象主要用于调优和故障分析。包括：

将 performance schema 和 information schema 中的数据用更容易理解的方式来总结归纳出来的“视图”。

提供 performance schema 和 information schema 配置或者生成分析报告类似操作的“存储过程”

当然，你也可以在 sys schema 下增加自己用于诊断 MySQL 性能的“视图”、“存储过程”和“存储函数”。

sys schema 举例

怎么利用 sys schema 来定位问题和诊断数据库性能？这里简单举一个 innodb 行锁的例子来说明。

模拟行锁

CREATE TABLE `test2` (

`id` int(11) NOT NULL,

`name` varchar(16) DEFAULT NULL,

`age` int(11) DEFAULT NULL,

`sex` int(11) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

有一条数据如下：

mysql > select * from test2;

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

| id | name | age | sex |

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

| 2 | pickup1 | 1 | 1 |

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

通过 sys.innodb_lock_waits 查看 innodb 锁表情况

对应的在 session 3上查看到的记录：

mysql > select * from sys.innodb_lock_waitsG

*************************** 1. row ***************************

wait_started: 2016-05-04 01:04:38

wait_age: 00:00:02

wait_age_secs: 2

locked_table: `test`.`test2`

locked_index: PRIMARY

locked_type: RECORD

waiting_trx_id: 5382

waiting_trx_started: 2016-05-04 00:24:21

waiting_trx_age: 00:40:19

waiting_trx_rows_locked: 4

waiting_trx_rows_modified: 0

waiting_pid: 3

waiting_query: update test2 set name='pickup3' where id=2

waiting_lock_id: 5382:31:3:3

waiting_lock_mode: X

blocking_trx_id: 5381

blocking_pid: 2

blocking_query: NULL

blocking_lock_id: 5381:31:3:3

blocking_lock_mode: X

blocking_trx_started: 2016-05-04 00:23:49

blocking_trx_age: 00:40:51

blocking_trx_rows_locked: 1

blocking_trx_rows_modified: 1

sql_kill_blocking_query: KILL QUERY 2

sql_kill_blocking_connection: KILL 2

innodb_lock_waits 本质

其实 sys schema 的 innodb_lock_waits 只是 information schema 的视图而已。

CREATE ALGORITHM = TEMPTABLE DEFINER = `mysql.sys`@`localhost` SQL SECURITY INVOKER VIEW `innodb_lock_waits` AS

SELECT

`r`.`trx_wait_started` AS `wait_started`,

TIMEDIFF(NOW(),

`r`.`trx_wait_started`) AS `wait_age`,

TIMESTAMPDIFF(

SECOND,

`r`.`trx_wait_started`,

NOW()) AS `wait_age_secs`,

`rl`.`lock_table` AS `locked_table`,

`rl`.`lock_index` AS `locked_index`,

`rl`.`lock_type` AS `locked_type`,

`r`.`trx_id` AS `waiting_trx_id`,

`r`.`trx_started` AS `waiting_trx_started`,

TIMEDIFF(NOW(),

`r`.`trx_started`) AS `waiting_trx_age`,

`r`.`trx_rows_locked` AS `waiting_trx_rows_locked`,

`r`.`trx_rows_modified` AS `waiting_trx_rows_modified`,

`r`.`trx_mysql_thread_id` AS `waiting_pid`,

`sys`.`format_statement`(`r`.`trx_query`) AS `waiting_query`,

`rl`.`lock_id` AS `waiting_lock_id`,

`rl`.`lock_mode` AS `waiting_lock_mode`,

`b`.`trx_id` AS `blocking_trx_id`,

`b`.`trx_mysql_thread_id` AS `blocking_pid`,

`sys`.`format_statement`(`b`.`trx_query`) AS `blocking_query`,

`bl`.`lock_id` AS `blocking_lock_id`,

`bl`.`lock_mode` AS `blocking_lock_mode`,

`b`.`trx_started` AS `blocking_trx_started`,

TIMEDIFF(NOW(),

`b`.`trx_started`) AS `blocking_trx_age`,

`b`.`trx_rows_locked` AS `blocking_trx_rows_locked`,

`b`.`trx_rows_modified` AS `blocking_trx_rows_modified`,

CONCAT(

'KILL QUERY ',

`b`.`trx_mysql_thread_id`

) AS `sql_kill_blocking_query`,

CONCAT('KILL ',

`b`.`trx_mysql_thread_id`) AS `sql_kill_blocking_connection`

FROM

(

`information_schema`.`innodb_lock_waits` `w`

JOIN

`information_schema`.`innodb_trx` `b` ON((`b`.`trx_id` = `w`.`blocking_trx_id`))

)

JOIN

`information_schema`.`innodb_trx` `r` ON(

(`r`.`trx_id` = `w`.`requesting_trx_id`)

)

JOIN

`information_schema`.`innodb_locks` `bl` ON(

(

`bl`.`lock_id` = `w`.`blocking_lock_id`

)

JOIN

`information_schema`.`innodb_locks` `rl` ON(

(

`rl`.`lock_id` = `w`.`requested_lock_id`

)

ORDER BY

`r`.`trx_wait_started`

innodb_lock_waits和x$innodb_lock_waits区别

mysql> SELECT * FROM host_summary_by_file_io;

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

| host | ios | io_latency |

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

| localhost | 67570 | 5.38 s |

| background | 3468 | 4.18 s |

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

而 x$host_summary_by_file_io 视图分析汇总的是同样的数据，但是显示的是未格式化过的皮秒（ picosecond ）延迟值

mysql> SELECT * FROM x$host_summary_by_file_io;

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

| host | ios | io_latency |

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

| localhost | 67574 | 5380678125144 |

| background | 3474 | 4758696829416 |

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

Q&A

提问：sys schema 只是在 performance_schema 和 information_schema 之上创建视图和存储过程？

李春：对，sys schema 主要针对的其实是 iperformance schema，有部分 information schema 的表也会整理到 sys schema 中统一展现。

提问：运行 KILL 2 杀掉 2 线程？blocking_lock_mode: X 的 X 什么意思？

提问：可以放心的打开 performance_schema，为何不使用 performance_schema 再造一个 sys schema？

提问：pt-query-digest 这些工具的有开始使用 sys schema 吗？

提问：阿里这么多数据库实例，使用什么运维工具？分布式事务又是怎么解决的呢？

提问：Oracle 有 v$，MySQL 有 x$ ？两个 $ 是完成相似功能的吗？

李春：MySQL 的 x$ 可以说是仿照 Oracle 的 v$ 来做的，但是目前离 Oracle 的那么强大的数据库诊断功能还有一些距离。

提问：数据库脱敏能否简单介绍下实现方式？

mysql表分区使用及详细介绍

一、分区概念

　　分区是将一个表分成多个区块进行操作和保存，从而降低每次操作的数据，提高性能。而对于应用来说则是透明的，从逻辑上看只有一张表，但在物理上这个表可能是由多个物理分区组成的，每个分区都是的对象，可以进行处理。

二、分区作用

　　 1.可以逻辑数据分割，分割数据能够有多个不同的物理文件路径。

　　2.可以存储更多的数据，突破系统单个文件最大。

　　3.提升性能，提高每个分区的读写速度，提高分区范围查询的速度。

　　4.可以通过删除相关分区来快速删除数据

　　5.通过跨多个磁盘来分散数据查询，从而提高磁盘I/O的性能。

　　6.涉及到例如SUM()、COUNT()这样聚合函数的查询，可以很容易的进行并行处理。

　　7.可以备份和恢复的分区，这对大数据量很有好处。

三、分区能支持的引擎

　　 MySQL支持大部分引擎创建分区，入MyISAM、InnoDB等；不支持MERGE和CSV等来创建分区。同一个分区表中的所有分区必须是同一个存储引擎。值得注意的是，在MySQL8版本中，MyISAM表引擎不支持分区。

四、确认MySQL支持分区

　　从MySQL5.1开始引入分区功能，可以如下方式查看是否支持：

　　老版本用：SHOW VARIABLES LIKE '%partition%';

　　新版本用：show plugins;

五、分区类型

　　1. RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。

　　　　例如，可以将一个表通过年份划分成两个分区，2001 -2010年、2011-2020。

　　2. LIST分区：类似于RANGE分区，LIST是列值匹配一个离散值集合中的某个值来进行选择。

　　　　比如根据字段把值为1、3、5的放到一起，2、4、6的另外放到一起等等...

　　3. HASH分区：基于用户定义的表达式的返回值来进行选择分区，该表达式使用将要插入到表中的这些行的列值来进行计算，这个函数必须产生非负整数值。

　　通过HASH运算来进行分区，分布的比较均匀

　　4. KEY分区：类似于按HASH分区，由MySQL服务器提供其自身的哈希函数。

　　按照KEY进行分区类似于按照HASH分区

六、分区创建注意事项

　　1. 如果表中存在primary key 或者 unique key 时，分区的列必须是paimary key或者unique key的一个组成部分，也就是说，分区函数的列只能从pk或者uk这些key中取子集

　　2. 如果表中不存在任何的paimary key或者unique key，则可以指定任何一个列作为分区列

　　3. 5.5版本前的RANGE、LIST、HASH分区要求分区键必须是int；MySQL5.5及以上，支持非整形的RANGE和LIST分区，即：range columns 和 list columns (可以用字符串来进行分区)。

七、分区命名

　　1. 分区的名字基本上遵循其他MySQL 标识符应当遵循的原则，例如用于表和数据库名字的标识符。应当注意的是，分区的名字是不区分大小写的。

　　2. 无论使用何种类型的分区，分区总是在创建时就自动的顺序编号，且从0开始记录。

八、创建分区

1. RANGE分区：

CREATE TABLE `test01` (

`dayid` int(11) DEFAULT NULL,

`mac` varchar(32) NOT NULL DEFAULT '',

`dtype` varchar(50) NOT NULL DEFAULT ''

) ENGINE=InnoDB DEFAULT CHARSET=utf8

/*!50100 PARTITION BY LIST (dayid)

(PARTITION p20171205 VALUES IN (20171205) ENGINE = InnoDB,

PARTITION p20171204 VALUES IN (20171204) ENGINE = InnoDB,

PARTITION p20171206 VALUES IN (20171206) ENGINE = InnoDB,

PARTITION p20171207 VALUES IN (20171207) ENGINE = InnoDB) */

解读：以上为 uuid小于5时放到p0分区下，uuid大于5且小于10放到p1分区下，uuid大于10且小于15放到p2分区下，uuid大于15 一直到最大值的存在p3分区下

2. LIST分区：

CREATE TABLE tbl_test (

uuid INT NOT NULL,

title VARCHAR(20)

)

PARTITION BY List (uuid) (

PARTITION p0 VALUES in (1,2,3,5),

PARTITION p1 VALUES in (7,9,10),

PARTITION p2 VALUES in (11,15)

)

);

解读：以上为uuid 等于1/2/3/5时放到p0分区，7/9/10放到p1分区，11/15放到p2分区。当时用insert into时如果uuid的值不存在p0/p1/p2分区时，则会插入失败而报错。

3. HASH分区:

HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE分区和LIST分区中必须明确指定一个指定的列值或列值集合以指定应该保存在哪个分区中。而在HASH分区中，MySQL会自动完成这些工作，要做的只是基于将要被哈希的列值指定一个表达式，以及指定被分区的表将要被分割成的分区数量，如：

CREATE TABLE tbl_test (

uuid INT NOT NULL,

title VARCHAR(20)

))

PARTITION BY HASH (uuid) (

PARTITIONS 3

));

解读：MySQL自动创建3个分区，在执行insert into时，根据插入的uuid通过算法来自动分配区间。

注意：

　　(1) 由于每次插入、更新、删除一行，这个表达式都要计算一次，这意味着非常复杂的表达式可能会引起性能问题，尤其是在执行同时影响大量行的运算（例如批量插入）的时候。

　　(2) 最有效率的哈希函数是只对单个表列进行计算，并且它的值随列值进行一致的增大或减小，因为这考虑了在分区范围上的“修剪”。也就是说，表达式值和它所基于的列的值变化越接近，就越能有效地使用该表达式来进行HASH分区。

3.1：线性HASH分区

　　线性HASH分区在“PARTITION BY”子句中添加“LINEAR”关键字。

　　线性HASH分区的有点在于增加、删除、合并和拆分分区将变得更加快捷，有利于处理含有及其大量数据的表。它的缺点在于各个分区间数据的分布不大可能均衡。

4. KEY分区

类似于HASH分区，HASH分区允许用户自定义的表达式，而KEY分区则不允许使用用户自定义的表达式；HASH分区只支持整数分区，KEY分区支持除了blob和text类型之外的其他数据类型分区。

与HASH分区不同，创建KEY分区表的时候，可以不指定分区键，默认会选择使用主键或唯一键作为分区键，没有主键或唯一键，就必须指定分区键。

CREATE TABLE tbl_test (

uuid INT NOT NULL,

title VARCHAR(20)

))

PARTITION BY LINEAR Key (uuid)

PARTITIONS 3;

解读：根据分区键来进行分区

5. 子分区

子分区是分区表中，每个分区的再次分割，适合保存非常大量的数据。

CREATE TABLE tbl_test (

registerTime Date

))

PARTITION BY GANGE(YEAR(registerTime))

SUBPARTITION BY HASH (TO_DAYS(registerTime))

SUBPARTITIONS 2

(

PARTITION p0 VALUES LESS THAN (2017),

PARTITION p1 VALUES LESS THAN (2020),

PARTITION p2 VALUES LESS THAN MAXVALUE

);

解读：主分区使用RANGE按照年来进行分区，有3个RANGE分区。这3个分区中又被进一步分成了2个子分区，实际上，整个表被分成了3 * 2 = 6个分区。每个子分区按照天进行HASH分区。小于2017的放在一起，2017-2020的放在一起，大于2020的放在一起。

注意：

　　(1) 在MySQL5.1中，对于已经通过RANGE或LIST分区了的表在进行子分区是可能的。子分区既可以使用HASH分区，也可以使用KEY分区。这也被称为复合分区。

　　(2) 每个分区必须有相同数量的子分区。

　　(3) 如果在一个分区表上的任何分区上使用SUBPARTITION来明确定义任何子分区，那么就必须定义所有的子分区。

　　(4) 每个SUBPARTITION子句必须包含（至少）子分区的一个名字。

(5) 在每个子分区内，子分区的名字必须是惟一的，目前在整个表中，也要保持唯一。例如：

PARTITION BY RANGE(YEAR(registerTime))

SUBPARTITION BY HASH(TO_DAYS(registerTime))

(

PARTITION p0 VALUES LESS THAN (2017) (

SUBPARTITION s0,

SUBPARTITION s1

PARTITION p1 VALUES LESS THAN (2020) (

SUBPARTITION s2,

SUBPARTITION s3

PARTITION p2 VALUES LESS THAN MAXVALUE (

SUBPARTITION s4,

SUBPARTITION s5

)

子分区可以用于特别大的表，可以在多个磁盘间分配数据和索引。例如：

SUBPARTITION s0

DATA DIRECTORY = '/disk0/data'

INDEX DIRECTORY = '/disk0/idx'

SUBPARTITION s1

DATA DIRECTORY = '/disk1/data'

INDEX DIRECTORY = '/disk1/idx'

九、MySQL分区处理NULL值的方式

　　MySQL中的分区禁止空值NULL上没有进行处理，无论它是一个列值还是一个用户定义表达式的值，一般而言，在这种情况下MySQL把NULL视为0。如果你希望回避这种做法，你应该在设计表时声明列“NOT NULL”。

十、分区管理概述

　　可以对分区进行添加、删除、重新定义、合并或拆分等管理操作。

① RANGE和LIST分区的管理

　　1. 删除分区语句如：alter table tbl_test drop partition p0;

　　注意：

　　　　(1) 当删除了一个分区，也同时删除了该分区中所有的数据。

　　　　(2) 可以通过show create table tbl_test;来查看新的创建表的语句。

　　　　(3) 如果是LIST分区的话，删除的数据不能新增进来，因为这些行的列值包含在已经删除了的分区的值列表中。

　　2. 添加分区语句如：alter table tbl_test add partition(partition p3 values less than(50));

　　注意：

　　　　(1) 对于RANGE分区的表，只可以添加新的分区到分区列表的最高端。

　　　　(2) 对于LIST分区的表，不能添加已经包含在现有分区值列表中的任意值。

　　3. 如果希望能不丢失数据的条件下重新定义分区，可以使用如下语句：

　　　　ALTER TABLE tbl_name REORGANIZE PARTITION partition_list INTO(partition_definitions)

　　　　(1) 拆分分区如：

　　　　ALTER TABLE tbl_name REORGANIZE PARTITION partition_list INTO(partition s0 values less than(5),partition s1 values less than(10));

　　　　或者如：

　　　　ALTER TABLE tbl_name REORGANIZE PARTITION p0 INTO(partition s0 values in(1,2,3), partition s1 values in(4,5));

　　　　(2) 合并分区如：ALTER TABLE tbl_name REORGANIZE PARTITION s0,s1 INTO(partition p0 values in(1,2,3,4,5));

　　4. 删除所有分区，但保留数据，形式：ALTER TABLE tbl_name remove partitioning;

② HASH和KEY分区的管理

　　 1. 减少分区数量语句如：ALTER TABLE tbl_name COALESCE PARTITION 2;

　　2. 添加分区数量语句如：ALTER TABLE tbl_name add PARTITION partitions 2;

③ 其他分区管理语句

　　 1. 重建分区：类似于先删除保存在分区中的所有记录，然后重新插入它们，可用于整理分区碎片。如：ALTER table tbl_name REBUILD PARTITION p2,p3;

　　 2. 优化分区：如果从分区中删除了大量的行，或者对一个带有可变长度的行（也就是说，有VARCHAR，BLOB或TEXT类型的列）做了许多修改，可以使用 ALTER TABLE tbl_name OPTIMIZE PARTITION来收回没有使用的空间，并整理分区数据文件的碎片。如：ALTER TABLE tbl_name OPTIMIZE PARTITION p2,p3;

　　 3. 分析分区：读取并保存分区的键分布，如：ALTER TABLE tbl_name ANALYZE PARTITION p2,p3;

　　 4. 检查分区：检查分区中的数据或索引是否已经被破坏，如：ALTER TABLE tbl_name CHECK PARTITION p2,p3;

　　 5. 修补分区：修补被破坏的分区，如：ALTER TABLE tbl_name REPAIR PARTITION p2,p3;

十、查看分区信息

　　1. 查看分区信息：select * from information_schema.partitions where table_schema='arch1' and table_name = 'tbl_test' G;

　　2. 查看分区上的数据：select * from tbl_test partition(p0);

　　3. 查看MySQL会操作的分区：explain partitions select * from tbl_test where uuid = 2;

十一、局限性

　　 1. 最大分区数目不能超过1024，一般建议对单表的分区数不要超过50个。

　　2. 如果含有唯一索引或者主键，则分区列必须包含在所有的唯一索引或者主键在内。

　　3. 不支持外键。

　　4. 不支持全文索引，对分区表的分区键创建索引，那么这个索引也将被分区。

　　5. 按日期进行分区很合适，因为很多日期函数可以用。但是对字符串来说合适的分区函数不太多。

　　6. 只有RANGE和LIST分区能进行子分区，HASH和KEY分区不能进行子分区。

　　7. 临时表不能被分区。

　　8. 分区表对于单条记录的查询没有优势。

　　9. 要注意选择分区的成本，没插入一行数据都需要按照表达式筛选插入的分区。

　　10. 分区字段尽量不要可以为null

mysql中information_schema和performance_schema这个数据库需要备份吗...

不需要备份，这两个是用来查看mysql数据库信息和性能的库，系统自带的，系统的变化都会反映到里面，不是用户数据追问哦,那意思是我导入表的时候他会自动创建?
那用户数据是哪个表需要备份吗?

追答用户数据的表是mysql.user

mysql中information_schema和performance_schema这个数据库需要备份吗...

追答用户数据的表是mysql.user

mysql报错Table 'performance_schema.session_status' doesn't exist

有两种方法，一种方法使用mysql的check table和repair table 的sql语句，另一种方法是使用MySQL提供的多个myisamchk, isamchk数据检测恢复工具。前者使用起来比较简便。推荐使用。

1. check table 和 repair table

登陆mysql 终端：

mysql -uxxxxx -p dbname

check table tabTest;

如果出现的结果说Status是OK，则不用修复，如果有Error，可以用：

repair table tabTest;

进行修复，修复之后可以在用check table命令来进行检查。在新版本的phpMyAdmin里面也可以使用check/repair的功能。

2. myisamchk, isamchk

其中myisamchk适用于MYISAM类型的数据表，而isamchk适用于ISAM类型的数据表。这两条命令的主要参数相同，一般新的系统都使用MYISAM作为缺省的数据表类型，这里以myisamchk为例子进行说明。当发现某个数据表出现问题时可以使用：

myisamchk tablename.MYI追问

repair table tabTest;运行结果和check是一样，

目前只能运行 set @@global.show_compatibility_56=ON;但也是暂时的，而且解决之后check table结果和之前一样，

是否证明问题并不是在这？

如何用python更新mysql数据库数据

MySQL 的 Binlog 记录着 MySQL 数据库的所有变更信息，了解 Binlog 的结构可以帮助我们解析Binlog，甚至对 Binlog 进行一些修改，或者说是“篡改”，例如实现类似于 Oracle 的 flashback 的功能，恢复误删除的记录，把 update 的记录再还原回去等。本文将带您探讨一下这些神奇功能的实现，您会发现比您想象地要简单得多。本文指的 Binlog 是 ROW 模式的 Binlog，这也是 MySQL 8 里的默认模式，STATEMENT 模式因为使用中有很多*，现在用得越来越少了。
Binlog 由事件（event）组成，请注意是事件（event）不是事务（transaction），一个事务可以包含多个事件。事件描述对数据库的修改内容。
现在我们已经了解了 Binlog 的结构，我们可以试着修改 Binlog 里的数据。例如前面举例的 Binlog 删除了一条记录，我们可以试着把这条记录恢复，Binlog 里面有个删除行（DELETE_ROWS_EVENT）的事件，就是这个事件删除了记录，这个事件和写行（WRITE_ROWS_EVENT）的事件的数据结构是完全一样的，只是删除行事件的类型是 32，写行事件的类型是 30，我们把对应的 Binlog 位置的 32 改成 30 即可把已经删除的记录再插入回去。从前面的 “show binlog events” 里面可看到这个 DELETE_ROWS_EVENT 是从位置 378 开始的，这里的位置就是 Binlog 文件的实际位置（以字节为单位）。从事件（event）的结构里面可以看到 type_code 是在 event 的第 5 个字节，我们写个 Python 小程序把把第383（378+5=383）字节改成 30 即可。当然您也可以用二进制编辑工具来改。
找出 Binlog 中的大事务
由于 ROW 模式的 Binlog 是每一个变更都记录一条日志，因此一个简单的 SQL，在 Binlog 里可能会产生一个巨无霸的事务，例如一个不带 where 的 update 或 delete 语句，修改了全表里面的所有记录，每条记录都在 Binlog 里面记录一次，结果是一个巨大的事务记录。这样的大事务经常是产生麻烦的根源。我的一个客户有一次向我抱怨，一个 Binlog 前滚，滚了两天也没有动静，我把那个 Binlog 解析了一下，发现里面有个事务产生了 1.4G 的记录，修改了 66 万条记录！下面是一个简单的找出 Binlog 中大事务的 Python 小程序，我们知道用 mysqlbinlog 解析的 Binlog，每个事务都是以 BEGIN 开头，以 COMMIT 结束。我们找出 BENGIN 前面的 “# at” 的位置，检查 COMMIT 后面的 “# at” 位置，这两个位置相减即可计算出这个事务的大小，下面是这个 Python 程序的例子。
切割 Binlog 中的大事务
对于大的事务，MySQL 会把它分解成多个事件（注意一个是事务 TRANSACTION，另一个是事件 EVENT），事件的大小由参数 binlog-row-event-max-size 决定，这个参数默认是 8K。因此我们可以把若干个事件切割成一个单独的略小的事务
ROW 模式下，即使我们只更新了一条记录的其中某个字段，也会记录每个字段变更前后的值，这个行为是 binlog_row_image 参数控制的，这个参数有 3 个值，默认为 FULL，也就是记录列的所有修改，即使字段没有发生变更也会记录。这样我们就可以实现类似 Oracle 的 flashback 的功能，我个人估计 MySQL 未来的版本从可能会基于 Binlog 推出这样的功能。
了解了 Binlog 的结构，再加上 Python 这把瑞士军刀，我们还可以实现很多功能，例如我们可以统计哪个表被修改地最多？我们还可以把 Binlog 切割成一段一段的，然后再重组，可以灵活地进行 MySQL 数据库的修改和迁移等工作。

如何用python更新mysql数据库数据

Python连接mysql数据库及python使用mysqldb连接数据库教程

做python的时候需要用到数据库，于是自己重新整理了一下数据库的知识，并且熟悉了python中MysqlDB模块的功能和函数等接口，现在系统地来总结一下吧：

首先你要做的还是下载相应的模块并且安装啦，下载地址自己搜，网上有很多，安装的话也很好办，安装之后python的安装目录下的Lib文件夹下的site-packages文件夹下的MySQLdb文件夹，这之中存放的便是该模块的定义。准备工作做好之后我们需要在源码中import MySQLdb

数据库的连接：

模块引入之后我们就需要和数据库进行连接了，实例代码如下：

db = MySQLdb.connect("localhost","root","123456","myciti" )

这三个关键参数的含义一目了然：第一个为服务器的地址，第二个为用户名，第三个为dbms密码，第四个为要访问的数据库，其实该connect函数的参数不止这些，不过由于其有默认值而且大多数情况下不用修改，因而省略了。这里做如下列表：

host，连接的数据库服务器主机名，默认为本地主机(localhost)。

user，连接数据库的用户名，默认为当前用户。

passwd，连接密码，没有默认值。

db，连接的数据库名，没有默认值。

conv，将文字映射到Python类型的字典。默认为MySQLdb.converters.conversions

cursorclass，cursor()使用的种类，默认值为MySQLdb.cursors.Cursor。

compress，启用协议压缩功能。

named_pipe，在windows中，与一个命名管道相连接。

init_command，一旦连接建立，就为数据库服务器指定一条语句来运行。

read_default_file，使用指定的MySQL配置文件。

read_default_group，读取的默认组。

unix_socket，在unix中，连接使用的套接字，默认使用TCP。

port，指定数据库服务器的连接端口，默认是3306

大家可能会注意到源码中没有用到端口号，这是因为MySQLdb的connect函数的该参数的默认值便是3306，如果你在安装mysql的时候修改了数据库的端口号，那么你就需要在源码中加上该参数的修改值了。

一，安装mysql

如果是windows 用户，mysql 的安装非常简单，直接下载安装文件，双击安装文件一步一步进行操作即可。

Linux 下的安装可能会更加简单，除了下载安装包进行安装外，一般的linux 仓库中都会有mysql ，我们只需要通过一个命令就可以下载安装：

Ubuntu/deepin

sudo apt-get install mysql-server

Sudo apt-get install mysql-client

centOS/redhat

yum install mysql

二，安装MySQL-python

要想使python可以操作mysql 就需要MySQL-python驱动，它是python 操作mysql必不可少的模块。

下载地址：https://pypi.python.org/pypi/MySQL-python/

下载MySQL-python-1.2.5.zip 文件之后直接解压。进入MySQL-python-1.2.5目录:

python setup.py install

三，测试

测试非常简单，检查MySQLdb 模块是否可以正常导入。

fnngj@fnngj-H24X:~/pyse$ python

Python 2.7.4 (default, Sep 26 2013, 03:20:56)

[GCC 4.7.3] on linux2

Type "help", "copyright", "credits" or "license" for more information.

import MySQLdb

没有报错提示MySQLdb模块找不到，说明安装OK ，下面开始使用python 操作数据库之前，我们有必要来回顾一下mysql的基本操作：

四，mysql 的基本操作

$ mysql -u root -p （有密码时）

$ mysql -u root （无密码时）

mysql show databases; // 查看当前所有的数据库

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

| Database |

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

| information_schema |

| csvt |

| csvt04 |

| mysql |

| performance_schema |

| test |

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

6 rows in set (0.18 sec)

mysql use test; //作用与test数据库

Database changed

mysql show tables; //查看test库下面的表

Empty set (0.00 sec)

//创建user表，name 和password 两个字段

mysql CREATE TABLE user (name VARCHAR(20),password VARCHAR(20)); Query OK, 0 rows affected (0.27 sec)

//向user表内插入若干条数据

mysql insert into user values('Tom','1321');Query OK, 1 row affected (0.05 sec)

mysql insert into user values('Alen','7875');Query OK, 1 row affected (0.08 sec)

mysql insert into user values('Jack','7455');Query OK, 1 row affected (0.04 sec)

//查看user表的数据

mysql select * from user;+------+----------+

| name | password |

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

| Tom | 1321 |

| Alen | 7875 |

| Jack | 7455 |

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

3 rows in set (0.01 sec)

//删除name 等于Jack的数据

mysql delete from user where name = 'Jack';Query OK, 1 rows affected (0.06 sec)

//修改name等于Alen 的password 为 1111

mysql update user set password='1111' where name = 'Alen';Query OK, 1 row affected (0.05 sec)

Rows matched: 1 Changed: 1 Warnings: 0

//查看表内容

mysql select * from user;+--------+----------+

| name | password |

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

| Tom | 1321 |

| Alen | 1111 |

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

3 rows in set (0.00 sec)

五，python 操作mysql数据库基础

#coding=utf-8import MySQLdb

conn= MySQLdb.connect(

host='localhost',

port = 3306,

user='root',

passwd='123456',

db ='test',

)

cur = conn.cursor()#创建数据表#cur.execute("create table student(id int ,name varchar(20),class varchar(30),age varchar(10))")#插入一条数据#cur.execute("insert into student values('2','Tom','3 year 2 class','9')")#修改查询条件的数据#cur.execute("update student set class='3 year 1 class' where name = 'Tom'")#删除查询条件的数据#cur.execute("delete from student where age='9'")cur.close()

conn.commit()

conn.close()

conn = MySQLdb.connect(host='localhost',port = 3306,user='root', passwd='123456',db ='test',)

Connect() 方法用于创建数据库的连接，里面可以指定参数：用户名，密码，主机等信息。

这只是连接到了数据库，要想操作数据库需要创建游标。

cur = conn.cursor()

通过获取到的数据库连接conn下的cursor()方法来创建游标。

cur.execute("create table student(id int ,name varchar(20),class varchar(30),age varchar(10))")

通过游标cur 操作execute()方法可以写入纯sql语句。通过execute()方法中写如sql语句来对数据进行操作。

cur.close()

cur.close() 关闭游标

conn.commit()

conn.commit()方法在提交事物，在向数据库插入一条数据时必须要有这个方法，否则数据不会被真正的插入。

conn.close()

Conn.close()关闭数据库连接

六，插入数据

通过上面execute()方法中写入纯的sql语句来插入数据并不方便。如：

cur.execute("insert into student values('2','Tom','3 year 2 class','9')")

我要想插入新的数据，必须要对这条语句中的值做修改。我们可以做如下修改：

#coding=utf-8import MySQLdb

conn= MySQLdb.connect(

host='localhost',

port = 3306,

user='root',

passwd='123456',

db ='test',

)

cur = conn.cursor()#插入一条数据sqli="insert into student values(%s,%s,%s,%s)"cur.execute(sqli,('3','Huhu','2 year 1 class','7'))

cur.close()

conn.commit()

conn.close()

假如要一次向数据表中插入多条值呢？

#coding=utf-8import MySQLdb

conn= MySQLdb.connect(

host='localhost',

port = 3306,

user='root',

passwd='123456',

db ='test',

)

cur = conn.cursor()#一次插入多条记录sqli="insert into student values(%s,%s,%s,%s)"cur.executemany(sqli,[

('3','Tom','1 year 1 class','6'),

('3','Jack','2 year 1 class','7'),

('3','Yaheng','2 year 2 class','7'),

])

cur.close()

conn.commit()

conn.close()

executemany()方法可以一次插入多条值，执行单挑sql语句,但是重复执行参数列表里的参数,返回值为受影响的行数。

七，查询数据

也许你已经尝试了在python中通过

cur.execute("select * from student")

来查询数据表中的数据，但它并没有把表中的数据打印出来，有些失望。

来看看这条语句获得的是什么

aa=cur.execute("select * from student")

print aa

5.它获得的只是我们的表中有多少条数据。那怎样才能获得表中的数据呢？进入python shell

import MySQLdb conn = MySQLdb.connect(host='localhost',port = 3306,user='root', passwd='123456',db ='test',) cur = conn.cursor() cur.execute("select * from student")5L

cur.fetchone()

(1L, 'Alen', '1 year 2 class', '6') cur.fetchone()

(3L, 'Huhu', '2 year 1 class', '7') cur.fetchone()

(3L, 'Tom', '1 year 1 class', '6')

...cur.scroll(0,'absolute')

fetchone()方法可以帮助我们获得表中的数据，可是每次执行cur.fetchone() 获得的数据都不一样，换句话说我没执行一次，游标会从表中的第一条数据移动到下一条数据的位置，所以，我再次执行的时候得到的是第二条数据。

scroll(0,'absolute') 方法可以将游标定位到表中的第一条数据。

还是没解决我们想要的结果，如何获得表中的多条数据并打印出来呢？

#coding=utf-8import MySQLdb

conn= MySQLdb.connect(

host='localhost',

port = 3306,

user='root',

passwd='123456',

db ='test',

)

cur = conn.cursor()#获得表中有多少条数据aa=cur.execute("select * from student")print aa#打印表中的多少数据info = cur.fetchmany(aa)for ii in info: print ii

cur.close()

conn.commit()

conn.close()

通过之前的print aa 我们知道当前的表中有5条数据，fetchmany()方法可以获得多条数据，但需要指定数据的条数，通过一个for循环就可以把多条数据打印出啦！执行结果如下：

5(1L, 'Alen', '1 year 2 class', '6')

(3L, 'Huhu', '2 year 1 class', '7')

(3L, 'Tom', '1 year 1 class', '6')

(3L, 'Jack', '2 year 1 class', '7')

(3L, 'Yaheng', '2 year 2 class', '7')

[Finished in 0.1s]

关于Mysql中四种常用存储引擎的详细介绍以及如何正确选择

MySQL存储引擎主要有两大类：

1. 事务安全表：InnoDB、BDB。

2. 非事务安全表：MyISAM、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等

MySQL默认的存储引擎是MyISAM（5.7版本中默认为InnoDB）。

配置文件中设置默认存储引擎的参数：default-table-type。

查询当前数据库支持的存储引擎：

show engines;

show variables like 'have%';查看当前的默认存储引擎：

show variables like '%table_type%';创建新表时指定存储引擎：

create table(...) engine=MyISAM;下面详细介绍4个比较常用的存储引擎：MyISAM、InnoDB、MEMORY和MERGE。

一、MyISAM

1. 数据文件：

MyISAM数据表在磁盘存储成3个文件，其文件名都和表名相同，扩展名分别是：

（1）.frm：存储数据表结构定义。

（2）.MYD：存储表数据。

（3）.MYI：存储表索引。

其中，数据文件和索引文件可以放置在不同的目录，平均分布IO，获得更快的速度。指定索引文件和数据文件的路径，需要在创建表的时候通过data directory和index directory语句指定。（文件路径需要是绝对路径并且具有访问的权限）

MyISAM类型的表可能会损坏，原因可能是多种多样的，损坏后的表可能不能访问，会提示需要修复或者访问后返回错误的结果。可以使用check table语句来检查MyISAM表的健康，并用repair table语句修复已经损坏的MyISAM表。

2. 存储格式：

（1）静态表（默认）：字段都是非变长的（每个记录都是固定长度的）。存储非常迅速、容易缓存，出现故障容易恢复；占用空间通常比动态表多。

（2）动态表：占用的空间相对较少，但是频繁的更新删除记录会产生碎片，需要定期执行optimize table或myisamchk -r命令来改善性能，而且出现故障的时候恢复比较困难。

（3）压缩表：使用myisampack工具创建，占用非常小的磁盘空间。因为每个记录是被单独压缩的，所以只有非常小的访问开支。

静态表的数据在存储的时候会按照列的宽度定义补足空格，在返回数据给应用之前去掉这些空格。如果需要保存的内容后面本来就有空格，在返回结果的时候也会被去掉。（其实是数据类型char的行为，动态表中若有这个数据类型也同样会有这个问题）

（静态表和动态表是根据正使用的列的类型自动选择的。）

3. 优劣势：

（1）优势：访问的速度快。

（2）不支持事务、也不支持外键。

4. 适用情况：

如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常适合的。MyISAM 是在Web、数据仓库和其他应用环境下最常使用的存储引擎之一。

二、InnoDB

1. 存储方式：

InnoDB存储表和索引有以下两种方式：

（1）使用共享表空间存储：这种方式创建的表结构保存在.frm文件中，数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中，可以是多个文件。

（2）使用多表空间存储：这种方式创建的表结构仍然保存在.frm文件中，但是每个表的数据和索引单独保存在.idb文件中。如果是个分区表，则每个分区对应单独的.idb文件，文件名是“表名+分区名”，可以在创建分区的时候指定每个分区的数据文件的位置，以此来将表的IO均匀分布在多个磁盘上。

要使用多表空间的存储方式，需要设置参数innodb_file_per_table并重启服务器后才可以生效，而且只对新建的表生效。多表空间的数据文件没有大小，不需要设置初始大小，也不需要设置文件的最大、扩展大小等参数。即使在多表空间的存储方式下，共享表空间仍然是必须的，InnoDB把内部数据词典和工作日志放在这个文件中，所以备份使用多表空间特性的表时直接复制.idb文件是不行的，可以通过命令将数据备份恢复到数据库中：

ALTER TABLE tbl_name DISCARD TABLESPACE;

ALTER TABLE tbl_name IMPORT TABLESPACE;但是这样只能恢复到表原来所在数据库中，如果需要恢复到其他数据库则需要通过mysqlmp和mysqlimport来实现。

2. 数据文件：

InnoDB的数据文件由表的存储方式决定。

（1）共享表空间文件：由参数innodb_data_home_dir和innodb_data_file_path定义，用于存放数据词典和日志等。

（2）.frm：存放表结构定义。

（3）.idb：使用多表空间存储方式时，用于存放表数据和索引，若使用共享表空间存储则无此文件。

3. 外键约束：

InnoDB是MySQL唯一支持外键约束的引擎。外键约束可以让数据库自己通过外键保证数据的完整性和一致性，但是引入外键会使速度和性能下降。在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。

外键约束使用示例：

CREATE TABLE `dep` (

`id` smallint(6) NOT NULL AUTO_INCREMENT,

`name` varchar(20) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `emp` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(20) DEFAULT NULL,

`dep_id` smallint(6) NOT NULL,

PRIMARY KEY (`id`),

KEY `idx_fk_dep_id` (`dep_id`),

CONSTRAINT `fk_emp_dep` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`) ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;KEY ：定义索引约束名称。

CONSTRAINT：定义外键约束名称。（在数据库中应是唯一的，若不指定系统会自动生成一个约束名）

ON：指定父表操作对子表的影响（不定义默认采用restrict）。

Restrict和no action：在子表有相关记录的情况下父表不能更新或删除。

Cascade：在父表更新或删除时，同时更新或删除子表对应的记录。

Set null：在父表更新或删除的时候，子表的对应字段被设置为null。

当某个表被其他表创建了外键参照，那么这个表的对应索引或者主键禁止被删除。在导入多个表的数据时，如果需要忽略表的导入顺序，可以暂时关闭外键的检查；在执行load data和alter table操作的时候，也可以通过暂时关闭外键约束来加快处理的速度。

关闭命令：

set foreign_key_checks=0;开启命令：

set foreign_key_checks=1;4. 优劣势：

（1）优势：提供了具有提交、回滚和崩溃恢复能力的事务安全。

（2）劣势：相比MyISAM，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。

5. 适用情况：

如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包括很多的更新、删除操作，那么InnoDB 存储引擎应该是比较合适的选择。InnoDB 存储引擎除了有效地降低由于删除和更新导致的锁定，还可以确保事务的完整提交和回滚，对于类似计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB 都是合适的选择。

三、MEMORY

1. 数据文件：

每个MEMORY表只对应一个.frm磁盘文件，用于存储表的结构定义，表数据存放在内存中。默认使用HASH索引，而不是BTREE索引。

2. 优劣势：

（1）优势：访问速度非常快，因为数据是存在内存中的。

（2）劣势：一旦服务关闭，表中的数据就会丢失；对表的大小有。

3. 适用情况：

Memory存储引擎主要用在那些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果。

四、MERGE

1. 引擎原理：

Merge存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，merge表本身并没有数据，对merge类型的表可以进行查询、更新、删除的操作，这些操作实际上是对内部的实际的MyISAM表进行的。

通过insert_method子句定义merge表的插入操作：使用first或last可以使插入操作被相应地作用在第一或最后一个表上，不定义或定义为No表示不能对这个merge表进行插入操作。对merge表进行drop操作只是删除了merge的定义，对内部的表没有任何影响。

2. 数据文件：

（1）.frm：存储表定义。

（2）.MRG：存储组合表的信息，包括merge表由哪些表组成、插入新数据时的依据。可以通过修改.mrg文件来修改merge表，但是修改后要通过flush tables刷新。

3. 使用示例：

CREATE TABLE `m1` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(20) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `m2` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(20) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `m` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(20) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION=(`m1`,`m2`);4. 适用情况：

用于将一系列等同的MyISAM 表以逻辑方式组合在一起，并作为一个对象引用它们。MERGE 表的优点在于可以突破对单个MyISAM 表大小的，并且通过将不同的表分布在多个磁盘上，可以有效地改善MERGE 表的访问效率。这对于诸如数据仓储等VLDB环境十分适合。

显示全文

全部频道

Mysql 事件记录 | performance_schema全方位介绍

MySQL数据库:

数据库简介:

MySQL数据库历史:

MySQL常用命令:

MySQL数据库导入方法:

Mssql转换mysql的方法:

MySQL备份与恢复:

MySQL数据库优化:

性能优化利器:剖析MySQL 5.7新特征 sys schema

性能优化利器:剖析MySQL 5.7新特征 sys schema