本文地址：https://www.askmac.cn/archives/mysql-3rd-chapter-test.html

 

 

从回答中选出你认为正确的答案：

1. 以下哪些陈述是正确的？
a. 在对MySQL Server设置SQL模式后，它会对所有连接到此服务的客户端起作用。
b. 如果你需要设置两个SQL模式(如，STRICT_ALL_TABLES和ERROR_FOR_DIVISION_BY_ZERO模式)，你必须执行两个SET sql_mode语句。
c. 除非显示进行全局声明，否则设置SQL模式仅会对进行此设置的客户端有影响。
d. SQL模式会对server的行为进行影响，如它们会影响到server无效输入数据处理方式。
e. SQL模式可以影响到server对客户端所提供的功能；如你可以使用SQL模式来启用或关闭InnoDB支持。

答案：C，D

 

仅在global全局模式下，SQL模式设置会对所有之后连接服务的客户端其作用。如果有多个SQL模式，你需要使用逗号分隔模式并执行相应语句：

 

SET sql_mode = 'STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO';

[Read more…]

MySQL的SQL查询优化课程

文档下载地址： https://zcdn.askmac.cn/MySQL%E6%9F%A5%E8%AF%A2%E6%80%A7%E8%83%BD%E4%BC%98%E5%8C%96.pdf

 

 

第一课：课程介绍

 

 

 

第二课：数据访问优化

 

 

 

第三课 了解MySQL查询优化概念（1）

 

 

 

第四课 了解MySQL查询优化概念（2）

 

 

 

第五课 了解EXPLAIN

 

 

 

 

第六课 SELECT语句优化实例

 

 

 

L7 – 优化实例 – 单个复杂查询 vs 多个简单查询

 

 

L8 – 优化实例 – JOIN语法中表顺序的影响

 

 

L9 – 优化实例 – 子查询，Exists，表连接？谁是最优之选

 

 

L10 – 优化实例 – 聚合函数 MIN，MAX的调优

 

 

L11 – 优化实例 – Group By语法优化

 

 

L12 – 优化实例 – 使用Limit语法进行分页优化

 

 

L13 – 优化实例 – Union和Union ALL之间的性能差别

 

 

L14 – 优化实例 – 索引和不等号

 

 

L15 – 最佳实践建议总结

 

 

 

本文地址：https://www.askmac.cn/archives/mysql-upgrade.html

 

 

我们知道，对于MySQL的新版本开发仍在继续，Oracle现在对MySQL发行版更新也很频繁。新版本中增加了许多新特性且修正了旧版本中发现的问题。然而，你不应该在未深入了解升级所涉及到的问题和困难程度的情况下贸然进行新版本的升级。如果你需要进行升级，请在执行操作前仔细阅读官方的《MySQL参考手册》：

• 总是留意查看升级部分中是否有你所需进行升级时要注意的要点。如果有，请遵照所描述的操作步骤进行升级操作。
• 检查修改注意部分中在你当前安装版本后的所有相关修改信息，由于有些修正并非都是向后兼容的，因此在你进行升级时，你需要对你的已存应用进行一定的改变。

 

除了那些警告提示外，升级MySQL总体上可以简单使用以下步骤进行操作：

• 备份你的数据库
• 关闭Server
• 在已存版本上安装新版本MySQL
• 启动Server

 

如果你在原有版本的最顶层目录上覆盖安装了最新版本MySQL数据库，你可能就不需要做太多的重新设定。这是因为MySQL在Windows版本的安装，RPM包安装，和源文件安装中都使用相同的安装目录，且无关MySQL版本。不过，如果你在升级MySQL时使用tar文件安装的话，新发行版本的安装更像是进行基于特定版本的目录安装，而不是按已有的安装目录进行安装。因此，在这种情况下，你就需要在升级安装时进行一些重新设置了。如果你已经建立有一个软链接来指向你的旧有安装目录，你可以删除此链接，重建并指向新的安装目录。之后操作中对此链接的引用将对新安装的文件进行访问。

[Read more…]

本文地址：https://www.askmac.cn/archives/mysql-binary-log.html

MySQL Replication（MySQL复制）使用的是一种日志传送复制解决方案。日志传送是指将所有主库(master)上的数据变化存储在一个日志中并发送日志，然后从库(slave)从这些接收到的日志读取信息并依次执行。过程中存储的日志称为二进制日志。由于需要实时地下载日志文件并执行其中内容，因此这意味着一旦一个日志文件事件产生，它会被发送到相连的从库并进行执行。当然，由于网络传输延时和类似问题，它会花上几秒或在甚至最差情况几分钟来接收这些更新。然而，理想状态下这中延时会被控制在1秒内。

 

为了启用二进制日志，使用 --log-bin项（在MySQL Server配置文件中进行设置）。

 

MySQL Server二进制日志默认被建立在 –datadir设置的目录中。其文件名会如以下形式：hostname-bin.000001。你也可以直接将二进制日志文件名和其位置通过 --log-bin项进行指定。

当启动二进制日志后，日志将以顺序进行建立，而不是仅生成一个大的日志文件。它们将以尾缀序号顺序建立，序号大的代表建立的更晚。二进制日志会在以下三种事件发生后，进行日志切换：

• 当MySQL Server重启时
• 当达到最大日志大小时(max_binlog_size)
• 当SQL命令FLUSH LOGS被执行时

[Read more…]

Oracle数据库管理简介

 

Oracle数据库管理简介

 

1.1 目标

从本章中，你应该能够：

• 描述关系型数据库的结构和使用SQL
• 分辨Oracle实例与数据库的组件
• 描述Oracle DBA需执行的基本工作
• 定义用来管理Oracle数据库的工具
• 使用My Oracle Support (Oracle Metalink)来研究问题并上报服务要求(SR)

 

1.2关系型数据库: 介紹

关系型数据库是关系和二维表的组合。

Oracle数据库是一种关系型数据库管理系统(RDBMS), 所以在使用Oracle Database建立的数据库就是关系型数据库。关系型数据库使用关系(Relation) 或者二维表格(Two-dimensional table)来存储数据。

[Read more…]

本文地址：https://www.askmac.cn/archives/mysql-log-status-file.html

 

MySQL Server会将信息写入几种类型的日志文件中。日志会记录下与被Server处理的SQL语句相关的各种信息：

• 通用查询日志(general query log)记录了所有从客户端收到的语句。
• 二进制日志(binary log)记录了对数据进行了修改的语句。
• 慢查询日志(slow query log)记录了长时间执行的查询。
• 报错日志(error log)记录了关于MySQL Server启动，关闭和异常情况的诊断信息。

 

这些日志可用于服务状态的确定, 对奔溃后数据恢复，以及用于有帮助，同时也有助于定位运行很慢的查询。之后我们会简单描述下每种日志的启用。虽然它们都不是默认启用的, 但是z在启用后，你需要意识到(特别对于通用查询日志)，它们的大小会增长很快。因此，你并不需要将它们都启用起来，尤其对一个很繁忙的数据库。以下是推荐的日志策略：

1. 在你最初建立MySQL Server的时候，启用通用查询日志，二进制日志及慢查询日志。

2. 在服务配置好且运行较流畅后，关闭通用查询日志以节约磁盘空间。

 

除了二进制日志外，其他所有日志都是以平文本的形式进行记录。平文日志便于程序或个人进行查看。对于慢查询日志，另一种打开方法是通过使用mysqldumpslow工具；它能对日志中的内容进行总结。如果需要查看二进制日志中的内容，可以使用mysqlbinlog工具。 [Read more…]

章节中会介绍MySQL所使用的客户/服务器模型。你会了解：

• 对MySQL C/S模式的描述
• 理解通信协议(Communication Protocols)
• 理解服务器如何支持存储引擎
• 关于MySQL如何使用内存和磁盘空间的基础知识

 

2.1 MySQL架构概览

 

MySQL架构实际上是一组为了完成数据库服务器任务而协同工作的相关功能组合。这些功能集包含超过50,0000行代码。下图中对其中的功能子系统进行了分层展示，层级之间通过相应API进行交互。多数情况下，每个子系统会对信息进行接收，处理然后再传送给下一个子系统以完成所分配的任务。子系统之间相对独立，这样就会有更大的自由度(如由于执行语句的存储引擎独立性，客户端不必知道哪个存储引擎执行其请求)。

 

2.1.1 核心共享子系统(Core Shared Subsystems)

在MySQL中的每个子系统都能自成一章。由于篇幅所限，这里仅对每个核心共享子系统进行简单描述，以给大家对其性能特点有一个概括性理解。

 

进程，线程和资源管理器(Process, Thread and Resource Management):

MySQL使用了一个基于线程的服务器架构，允许各种执行线程(或称为轻量级进程)访问核心共享资源。MySQL这种的多线程单进程架构能保证多个执行线程之间不会相互冲突或覆盖重要数据。使用基于线程的服务器架构最值得注意的优点是：

• 节约成本 – 和进程相比，线程的建立和销毁成本更低。新的线程可使用其父进程的地址空间而不需要额外的地址空间。
• 切换代价低 – 由于线程运行于相同的服务器进程空间中, 因此线程之间的切换代价很小。
• 极小的开销 – 由于线程可对父进程地址空间进行访问，因此在共享资源下开销也变得极小。

 

缓存(Cache/Buffer)管理:

此子系统专注于缓冲并取回服务器进程中所有线程执行所用的大量数据类型。由于已经将返回的数据进行内存缓存，因此数据缓存使得MySQL可以降低大量对于基于磁盘I/O代价昂贵的操作。

 

网络管理(Networking Management):

此子系统的职责是通过处理在多平台间发送和接收带有MySQL连接请求和命令的网络数据包这样的工作，使得各种通信协议(TCP/IP, 命名管道Named Pipes等)对连接线程变得透明。它也包括了处理安全套接字层(Secured Socket Layers: SSL)这样的工作。

 

日志管理:

这个子系统是为了将各种日志事件子类被维护在一个日志类下而建立的。这样能使得开发者能在不破坏系统核心功能的情况下增加日志和日志事件。通过对日志系统中子系统的区分。各种系统活动(启动，多语句事务，自动增量auto-increment值改变等)就可以通过子类事件进行记录。

 

访问及授权管理:

此子系统定义了所有为执行命令所需的GRANT权限并主要用于保证客户端和服务器间的安全。它会验证用户在登陆过程中的访问权限及查询权限。此子系统也包含了一些对授权表的内存版本修改功能及密码生成功能。

 

2.1.2 存储引擎接口(Storage Engine Abstraction)

此子系统使得MySQL可以在系统架构中使用不同的表数据handlers(处理子程序)。尽管不是所有存储引擎都完整实现了相关handler API，但大部分独立的handler API都被用于转换数据、schema和索引格式，以使其符合MySQL内部记录格式所需(内存记录格式)。

 

2.1.3 查询解析，优化和执行

由于这些子系统负责接收SQL语句，将语句解构为各种数据结构并以最佳路径进行执行，因此它们被称为MySQL服务器的大脑。

• 查询解析：

这是一个将SQL语句解析为一个抽象语法的过程。由于此过程非常复杂，因此解析时不能对任何用户变量进行改变。

• 优化：

此子系统负责找到查询的最优执行计划。

• 执行：

此子系统又被称为语句执行单元，负责按照通过SQL命令解析和优化后所得的最优执行路径进行执行。执行进程的基本功能是都有一个指针作为其第一参数以将结构数据包发回给客户端。

 

2.1.4 查询缓存(Query Cache)

不像其它MySQL子系统，此“子系统”组件由一系列类组成。它不仅负责缓存被执行的SQL命令，还存储命令执行后的结果。

 

本文地址：https://www.askmac.cn/archives/mysql-log-status-file.html

 

 

MySQL在整个网络环境中使用客户端/服务器(Client/Server)架构运行。换言之，其核心程序扮演着服务器角色，而各个客户端程序连接到服务器并提出请求。MySQL的安装涉及以下主要组件: MySQL Server, Client程序和MySQL非客户端工具。

 

 

 

2.2.1 MySQL Server

MySQL Server或者说mysqld，实际上是一个数据库服务器程序。它管理着对磁盘数据库和内存的访问。MySQL Server进行多线程操作，它支持多个客户端连接的同时访问。为了更好地管理数据库内容，MySQL Server的特色架构模型支持多种存储引擎以处理不同类型的表(例如，它同时支持事务和非事务表)。

 

由于MySQL Server此特性配置可能随时间变化而有所改动，因此当你下载新版本MySQL时候，请仔细阅读相关版本文档。

 

请您清楚了解我们的用词，server(服务器)和host(主机)的用词区别。在这里Server是指软件(MySQL server程序mysqld)。Server的特征中有它的版本号，指的是哪些特性包括，哪些不包括等。而host是指server程序运行所在的物理机。Host的特征中包括了硬件配置，所运行的操作系统，其网络地址等等。一个host可以有多个mysqld实例在上面同时运行。

 

2.2.2 Client程序

客户端程序被用于和server进行通信以修改服务器端server管理的数据库信息。MySQL提供了多种client端工具程序：

• MySQL Workbench, 一种作为访问Mysql Server的图形化的前端工具(具有MySQL Query Browser和MySQL Administrator相关功能，MySQL Query Browser和MySQL Administrator现已不再提供更新)。
• mysql，一种文本形式的命令行前端工具。
• 其他命令行客户端工具包括导入数据文件用的mysqlimport，生成备份的mysqldump, 作为服务器管理的mysqladmin,和用于检查数据库文件完整性的mysqlcheck。

 

MySQL客户端/服务器(Client/Server)模型：

MySQL可运行于Windows, Unix和Linux平台上，但客户端和服务器之间的沟通并不受限于所运行的操作系统。客户端程序和服务器之间的连接可以在同一台主机上进行，也可以是不同的主机间进行，且客户端主机和服务器主机不需要操作系统保持一致。例如，客户端程序可以运行于Windows上，而所连接的Server则运行在Linux host上。

 

大多在此讨论的概念都是指针对于MySQL运行的系统。除了一些特定的平台说明外，这里”Unix”一般都是指包括Linux和其他的Unix-like操作系统。

 

2.2.3 通信协议

以下详细描述了和MySQL server进行交互所使用各种不同通信协议：

• TCP/IP – 传输控制协议(Transmission Control Protocol)/互联网协议(Internet Protocol)，是一套被用于连接互联网上各主机的通信协议。TCP/IP一开始是用于UNIX操作系统建立互联网通信的。现在它已经成为了一种网络数据传输的事实标准。即便那些拥有自己通信协议的网络操作系统，如Netware也支持TCP/IP协议。
• Unix Socket – 在计算机世界，一个socket是一种内部进程通信形式，它被用于在相同主机上形成进程间的双向通信连接点(在本地系统上的一个物理文件)。
• 共享内存(Shared Memory) – 一个在程序间传送数据的有效方法。一个程序会建立一个内存区以提供其它受允许的进程进行访问。Windows显式”passive”连接模式仅可工作于(Windows系统)主机中。
• NT管道 – 这种命名管道设计更偏向于客户端-服务器通信，它们更像socket：除了用于通常的读写操作外，Windows命名管道也同时对server应用支持显式”passive”被动连接模式。仅在单独(Windows平台)主机中运行。

 

2.2.4 MySQL非客户端工具

有这么些程序运行时独立于server之外。它们首先在操作时并不会和server建立连接。如myisamchk。它执行表检查及修复操作。其它此类型程序有myisampack，它用于建立压缩的只读版本的MyISAM表。这两个工具都可以直接对MyISAM表文件进行访问，且独立于mysqld数据库server之外。

 

客户端通过以SQL语句的形式发送请求给服务器端从而从表中获取数据或对表数据进行修改。而服务端程序使用以下两层处理模型以执行每个语句请求：

• 上层包括SQL解析器(parser)和优化器(optimizer)。
• 下层包括了一组存储引擎。

需要注意的是，SQL层并不依赖于存储引擎管理的表。这意味着客户端通常不需要考虑哪个引擎被涉及处理其SQL语句，也不用考虑访问和更新的表是受到哪个引擎的管理。当然也有例外：

• CREATE TABLE语句有ENGINE项可用于在建立表时启用你所定义的存储引擎。ALTER TABLE语句也含有一个ENGINE项可以使你能将原表转换使用一个不同的存储引擎。
• 一些索引类型仅在某些特定存储类型中可用。例如, 仅MyISAM引擎支持空间索引(spatial index), 仅MyISAM, InnoDB（6.4以上）支持全文索引(full-text)等。

COMMIT和ROLLBACK仅对由事务型存储引擎(如InnoDB)管理的表有效果。

存储引擎细分

下图简单展示了MySQL服务和其存储引擎之间的交互。

依赖存储引擎不同会有以下不同：

• 存储方法 – 每张表中的数据按其自身的方法进行存储。
• 事务处理能力 – 某些存储引擎可以对事务进行处理，在执行多SQL语句时能确保数据库的完整性。
• 锁 – 是指每个引擎对于多线程执行场景中同一个资源的访问限制及同步机制处理。
• 备份恢复 – 基于不同的存储方法，表数据的备份和恢复也有区分。
• 优化 – 对于数据存储的优化和通过MySQL服务器的数据抽取，每个存储引擎都有其特定的优化点。
• 特定特性 – 某些引擎具有一些不同的特性包括全文搜索，参照完整性和处理空间数据的能力。

多数MySQL服务器会以相同的方式进行操作，即所有的常用SQL语句都是引擎无关的。当然，其优化器可能会按不同的存储引擎做出不同的优化判断，但也都是通过所有引擎都支持的一套标准接口(API)进行所有处理。

 

MySQL Server内存使用中包括了用于管理与客户端之间进行沟通和处理数据库内容Server所建立的数据结构。它会在运行时为许多类型的信息分配内存，如：

• 线程(Thread Handler):

线程在MySQL（或其它程序）中被用于将应用的执行分成多个同步运行的任务。对每个连接到MySQL Server的客户端，Server都会建立一个独立的线程来进行连接处理。MySQL Server为了避免一些可能产生的性能问题，会分配一块小的缓存给这些线程，在客户端断开后，被分配的缓存仍然会被保持。任何新的连接会首先通过此缓存来实例化线程，而不是重新建立一个新线程。这样就能确保内存消耗始终处于最低状态。

• 缓存(Buffers / Cache)

MySQL Server会使用多个buffers（或者称为caches），作为内存集以临时保存数据的输入或输出，目的是避免高成本的磁盘I/O访问。这些缓存主要由缓存管理子系统来进行操作，可对支持MyISAM索引块的索引缓存和持有已打开表的表述符的授权表缓存进行快速访问。此外，MySQL Server还包含了一个查询缓存(Query Cache)用于加速重复查询的处理速度。

• MEMORY存储引擎

MySQL Server的一种存储引擎，此引擎允许表被存储在内存上。MEMORY表默认使用哈希索引，这对建立临时表及提高临时表处理速度很有用。然而，当服务器被关闭后，MEMORY表中的所有数据行会丢失。这些表的表结构定义会被存储在.frm文件中，当server重启后，这些表都是空表。

• 内部临时表

通常，我们碰到MySQL需要建立一张内部临时表的例子都是当你需要进行一个复杂查询时，这强迫你建立一个临时表来解决此类问题。系统变量tmp_table_size决定了这张临时表在转换为一张磁盘存储的MyISAM表之前所能增长的最大值，超出此值后，它将会被自动转换为一张MyISAM表。

• 客户特定缓存(Client specific buffers)

MySQL server同时维护了多个特别设计的缓存以支持独立客户端的连接。例如包括了用于交互信息的通信缓存，用于表的读缓存及支持表连接和排序操作的缓存等。

 

2.5.1 数据存储

数据存储涉及到对性能调优和MySQL整体架构都非常重要的三个方面。这些方面是数据库服务器存储和检索系统的基础。

 

持久的数据存储：

数据存储系统要求持久性，或者说在服务重启时保留住数据的能力。这需要通过持久性数据设备如硬盘或二级存储设备来完成。此类数据存储对于数据的持续性和完整性保证是其关键，但是设备对数据的读写数据会相对慢些。

 

挥发性存储：

对于易挥发性存储如随机访问内存(RAM)，一般处于持久性数据存储和数据库服务之间。好处是可以瞬时内存中读取数据。当然，一旦发生间歇性或非间歇性断电，这些数据会丢失。

逻辑数据呈现：

当数据从持久数据存储(硬盘)中移至易挥发性存储(内存)，它必须有逻辑性地组织在一起并被保存称为页的许多块中。这些页将会由操作系统和硬件缓冲池来管理，作为操作单元被自动分配或释放。

MySQL的缓存管理子系统将和操作系统上的缓存管理一起来处理不同数据类型的缓存。每个存储引擎有不同的处理方法，我们在之后的存储引擎章节中会对其进行讨论。

 

2.5.2 挥发性存储(Memory)

MySQL会对内部缓存的分配大致分为两类：

全局缓存将会被所有线程(连接)所共享。而本地缓存则和独立的线程相关。MySQL可对这两种分类内存进行大量的控制管理设置。

 

本地内存：

本地内存是针对特定会话连接并动态分配的。这些内存区在不连接后会被自动释放。本地内存大多被用于处理查询结果。

全局内存：

全局内存是那些被预先分配且可被共享使用的内存。全局内存仅分配一块缓存区。

需要注意的是不断增长的本地内存则会使用更多、甚至占用机器余下的全部内存。当所有服务器内存被使用，操作系统就会开始使用swap交换区来保存内存信息，这样性能就降低了。MySQL Server的性能最终会收到负面效果，甚至可能导致性能崩溃。