mysql数据库schema是什么

一、mysql数据库schema是什么

1、schema在数据库中表示的是数据库对象集合，它包含了各种对像，比如：表，视图，存储过程，索引等等。

2、一般情况下一个用户对应一个集合，为了区分不同的集合就需要给不同的集合起名字。用户的schema名就相当于用户名，并作为该用户缺省schema。

3、所以说，schema集合看上去像用户名。例如，当访问一个数据表时，如果该表没有指明属于哪个schema，系统就会自动的加上缺省的schema。

4、Schema的创建在不同的数据库中要创建的Schema方法是不一样的，但是它们有一个共同的特点就是都支持CREATE SCHEMA语句。

5、在MySQL数据库中，可以通过CREATE SCHEMA语句来创建一个数据库Oracle Database在Oracle中，由于数据库用户已经创建了一个模式，所以，CREATE SCHEMA语句创建的是一个schema，它允许将schema同表和视图关联起来，并在这些多个事务中发出多个SQL语句。

6、SQL Server在SQL Server中，CREATE SCHEMA会按照名称来创建一个模式，与MySQL不同，CREATE SCHEMA语句创建了一个单独定义到数据库的模式。和Oracle数据库也有不同，它实际上创建了一个模式，而且一旦创建了模式，就可以往模式中添加用户和对象。

二、mysql中的schema到底是啥意思

1、数据库与模式在MySQL的架构中紧密相联。数据库可以理解为模式的一个实例，模式则定义了数据库的结构。在MySQL中，数据库与模式实际上具有相同的概念，通过执行create database命令，等同于创建了一个新的模式。

2、模式中包含了多种数据库元素，如表、索引、视图、存储过程等，它们构成了数据库的核心内容。这些元素通过模式进行组织和管理，为用户提供了一种结构化的数据存储和访问方式。模式的定义使得数据库的结构更加清晰、易于理解和维护。

3、表是模式中最重要的组成部分之一，它们用于存储具体的数据。每个表都包含了若干列，列代表了数据的属性，而行则对应具体的数据记录。通过表之间的关联，可以实现复杂的数据关系，如一对多、多对多等。

4、索引是优化数据查询性能的关键要素。它们基于特定的表列构建，使得数据库在执行查询操作时能够快速定位到相关数据。索引的使用可以显著提升数据检索的效率，减少查询时间，从而提高整个数据库系统的性能。

5、视图是一种虚拟的表，它基于实际的表进行构建。通过定义视图，用户可以更灵活地访问和操作数据，而无需直接操作原始表。视图提供了数据的抽象层，使得数据的展示和使用更加灵活多样。

6、存储过程是一组预编译的SQL命令，它们封装了特定的功能和操作。存储过程可以执行复杂的数据处理任务，比如数据验证、业务逻辑处理等。通过使用存储过程，可以实现代码的重用和优化，提高数据库应用的性能和安全性。

7、综上所述，模式在MySQL数据库中扮演了核心角色，它通过组织和定义数据库的结构，为用户提供了一种高效、灵活的数据存储和访问方式。模式中的表、索引、视图、存储过程等元素相互协作，共同构成了一个强大、功能丰富的数据库系统。

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

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

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

导读：很多团队在评估合适的时机切换到 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自动信息采集系统等标准化文档和自动化运维工具。

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包含一些对象，这些对象主要用于调优和故障分析。包括：

将 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来定位问题和诊断数据库性能？这里简单举一个 innodb行锁的例子来说明。

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

`name` varchar(16) DEFAULT NULL,

) ENGINE=InnoDB DEFAULT CHARSET=latin1

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

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

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

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

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

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

mysql> select* from sys.innodb_lock_waits\G

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

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

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

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

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

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）。

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

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

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

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

`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`,

`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`,

`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`,

) AS `sql_kill_blocking_query`,

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

`information_schema`.`innodb_lock_waits` `w`

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

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

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

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

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

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

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

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;

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

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

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

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

mysql> SELECT* FROM x$host_summary_by_file_io;

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

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

| localhost| 67574| 5380678125144|

| background| 3474| 4758696829416|

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

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

提问：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主要用于分析慢查和 tcpdump的结果，跟 sys schema的定位有部分重叠的地方，sys schema会分析得更细，更内核，更偏底层一些，pt-query-digest主要还是从慢查和 tcpdump中抽取 SQL来格式化展现。

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

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

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

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

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

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