MySQL`--2--基础介绍--架构组成

目录

I.MySQL Server 简介

回顶部

i.什么是MySQL

回顶部

MySQL 是由MySQL AB 公司（目前已经被SUN 公司收归麾下）自主研发的，目前IT 行业

最流行的开放源代码的数据库管理系统之一，它同时也是一个支持多线程高并发多用户的关系型数据库管理系统。

MySQL数据库以其简单高效可靠的特点，在最近短短几年的时间就从一个名不见经传的数据库系统，变成一个在IT行业几乎是无人不知的开源数据库管理系统。从小型的web 网站，至大型的企业级应用，到处都可见其身影的存在。

MySQL 与其他数据库的简单比较

1.功能比较

回顶部

作为一个成熟的数据库管理系统，要满足各种各样的商业需求，功能肯定是重点参考对象的。MySQL经过多年的改进和完善之后，已经基本具备了所有通用数据库管理系统所需要的相关功能。

MySQL基本实现了ANSI SQL 92 的大部分标准，仅有少部分并不经常被使用的部分没有实现。比如在字段类型支持方面，另一个著名的开源数据库PostGreSQL支持的类型是最完整的，而Oracle 和其他一些商业数据库，比如DB2、Sybase 等，较MySQL来说也要相对少一些。在事务支持方面，虽然MySQL自己的存储引擎并没有提供，但是已经通过第三方插件式存储引擎Innodb实现了SQL 92 标准所定义的四个事务隔离级别的全部。

不过在可编程支持方面，MySQL和其他数据库相比还有一定的差距，虽然最新版的MySQL已经开始提供一些简单的可编程支持，如开始支持Procedure，Function，Trigger等，但是所支持的功能还比较有限，和其他几大商用数据库管理系统相比，还存在较大的不足。如Oracle 有强大的PL/SQL，SQL Server 有T-SQL，PostGreSQL也有功能很完善的PL/PGSQL的支持。

整体来说，MySQL的功能完全可以满足我们的通用商业需求，提供足够强大的服务。而且不管是哪一种数据库在功能方面都不敢声称自己比其他任何一款商用通用数据库管理系统都强，甚至都不敢声称能够自己拥有某一数据库产品的所有功能。因为每一款数据库管理系统都有起自身的优势，也有起自身的限制，这只能代表每一款产品所倾向的方向不一样。

2.易用性比较

回顶部

从系统易用性方面来比较，每一个使用过MySQL的用户都能够明显地感觉出MySQL在这方面与其他通用数据库管理系统之间的优势所在。尤其是相对于一些大型的商业数据库管理系统如Oracle、DB2以及Sybase来说，对于普通用户来说，操作的难易程度明显不处于一个级别。MySQL一直都奉行简单易用的原则，也正是靠这一特性，吸引了大量的初级数据库用户最终选择了MySQL。

从安装方面来说，MySQL 安装包大小仅仅只有100MB左右，这与几大商业数据库完全不在一个数量级。安装难易程度也要比Oracle等商业数据库简单很多，不论是通过已经编译好的二进制分发包还是源码编译安装，都非常简单。

再从数据库创建来比较，MySQL仅仅只需要一个简单的CREATE DATABASE命令，即可在瞬间完成建库的动作，而Oracle数据库与之相比，创建一个数据库简直就是一个非常庞大的工程。当然，二者数据库的概念存在一定差别。

3.性能比较

回顶部

性能方面，一直是MySQL引以为自豪的一个特点。在权威的第三方评测机构多次测试较量各种数据库TPCC 值的过程中，MySQL一直都有非常优异的表现，而且在其他所有商用的通用数据库管理系统中，仅仅只有Oracle数据库能够与其一较高下。

4.可靠性

回顶部

关于可靠性的比较，MySQL 也有非常优异的表现，从当前最火的Facebook这样大型的网站都是使用MySQL数据库，就可以看出，MySQL在稳定可靠性方面，而且排在全球前10位的大型网站里面，大部分都有部分业务是运行在MySQL数据库环境上，如Yahoo，Google等。

总的来说，MySQL数据库在发展过程中一直有自己的三个原则：简单、高效、可靠。

ii.MySQL 的主要适用场景

回顶部

MySQL是目前最为流行的开源数据库管理系统软件了。那么MySQL主要用于什么场景下

1.Web 网站系统

回顶部

Web 站点，是MySQL 最大的客户群，MySQL 之所以能成为Web站点开发者们最青睐的数据库管理系统，是 为MySQL数据库的安装配置都非常简单，使用过程中的维护也不像很多大型商业数据库管理系统那么复杂，而且性能出色。还有一个非常重要的原因就是MySQL是开放源代码的，完全可以免费使用。

2.日志记录系统

回顶部

  • 分散管理（本地）
  • 集中管理

MySQL 数据库的插入和查询性能都非常的高效，如果设计地较好，在使用MyISAM存储引擎的时候，两者可以做到互不锁定，达到很高的并发性能。所以，对需要大量的插入和查询日志记录的系统来说，MySQL是非常不错的选择。比如处理用户的登录日志，操作日志等是非常适合的应用场景。

3.数据仓库系统

回顶部

性能指标（此处可以拓展）

  • 计算能力
  • 磁盘I/O能力
  • 网络I/O能力

随着现在数据仓库数据量的飞速增长，我们需要的存储空间越来越大。数据量的不断增长，使数据的统计分析变得越来越低效，也越来越困难。怎么办？这里有几个主要的解决思路，

  • 一个是采用昂贵的高性能主机以提高计算性能，用高端存储设备提高I/O性能，效果理想，但是成本非常高；
  • 第二个就是通过将数据复制到多台使用大容量硬盘的廉价pc server上，以提高整体计算性能和I/O 能力，效果尚可，存储空间有一定限制，成本低廉；
  • 第三个，通过将数据水平拆分(分库分表），使用多台廉价的pc server和本地磁盘来存放数据，每台机器上面都只有所有数据的一部分，解决了数据量的问题，所有pc server 一起并行计算，也解决了计算能力问题，通过中间代理程序调配各台机器的运算任务，既可以解决计算性能问题又可以解决I/O性能问题，成本也很低廉。

在上面的三个方案中，第二和第三个的实现，MySQL 都

有较大的优势。通过MySQL的简单复制功能，可以很好的将数据从一台主机复制到另外一台，

不仅仅在局域网内可以复制，在广域网同样可以。当然，其他的数据库同样也可以做到，不是只有MySQL有这样的功能。但是MySQL是免费的，其他数据库大多都是按照主机数量或者cpu数量来收费，当我们使用大量的pc server 的时候，license费用相当惊人。第一个方案，基本上所有数据库系统都能够实现，但是其高昂的成本并不是每一个公司都能够承担的。

II.MySQL架构组成

回顶部

Mysql物理文件组成：

i.日志文件

回顶部

日志文件主要包含：

  • 错误日志
    • 启动
    • 关闭
    • 错误信息
  • 查询日志
  • 慢查询日志
  • 事务日志
  • 二进制日志

故障排除，第一步就是查看错误日志

日志是mysql数据库的重要组成部分。日志文件中记录着mysql数据库运行期间发生的变化；也就是说用来记录mysql数据库的客户端连接状况、SQL语句的执行情况和错误信息等。当数据库遭到意外的损坏时，可以通过日志查看文件出错的原因，并且可以通过日志文件进行数据恢复。

1.错误日志Error Log

回顶部

在mysql数据库中，错误日志功能是默认开启的。默认情况下，错误日志存储在mysql数据库的数据目录中。错误日志文件通常的名称为hostname.err。其中，hostname表示服务器主机名。

错误日志信息可以自己进行配置的，错误日志所记录的信息是可以通过log-error和log-warnings来定义的，其中log-error是定义是否启用错误日志的功能和错误日志的存储位置，log-warnings是定义是否将警告信息也定义至错误日志中。默认情况下错误日志大概记录以下几个方面的信息：服务器启动和关闭过程中的信息（未必是错误信息，如mysql如何启动InnoDB的表空间文件的、如何初始化自己的存储引擎的等等）、服务器运行过程中的错误信息、事件调度器运行一个事件时产生的信息、在从服务器上启动服务器进程时产生的信息

注：MySQL有很多系统变量可以设置，系统变量设置不同，会导致系统运行状态的不同。因此mysql提供两组命令，分别查看系统设置和运行状态。

1)查看系统设置

回顶部

SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]   
SHOW VARIABLES： shows the values of MySQL system variables.
  • like为模糊查询
  • where为精确查询

2)运行状态

回顶部

SHOW [GLOBAL | SESSION] STATUS [like_or_where]   
SHOW STATUS： provides server status information.

3)如何修改系统配置

回顶部

方法1:配置文件设置my.cnf

回顶部

如：binlog_cache_size = 1M

方法2

回顶部

set global binlog_cache_size = 1048576;

一般而言，日志级别的定义没有会话变量都只是在全局级别下进行定义

查看mysql版本

  • mysql -V
  • mysql> status;
  • mysql> select version();

4)错误日志的状态

回顶部

1

其中

  • log_error定义为错误日志文件路径
  • log_error_verbosity:

The MySQL error log has received some attention in MySQL 5.7, with a new setting called log_error_verbosity.

There are three possible values, as documented in the manual

Verbosity Value Message Types Logged
1 Errors only
2 Errors and warnings
3 Errors, warnings, and notes (default)

更改错误日志位置可以使用log-error来设置形式如下

vi /etc/my.cnf
log-error = /usr/local/mysql/data/mysqld.err

5)查看mysql错误日志

回顶部

# tail /usr/local/mysql/data/mysqld.err

2

为了方便维护需要，有时候会希望将错误日志中的内容做备份并重新开始记录，这时候

就可以利用MySQL 的FLUSH LOGS 命令来告诉MySQL备份旧日志文件并生成新的日志文件。备份文件名以.old结尾。

6)删除错误日志

回顶部

在mysql5.5.7之前：数据库管理员可以删除很长时间之前的错误日志，以保证mysql服务器上的硬盘空间。mysql数据库中，可以使用mysqladmin命令开启新的错误日志。mysqladmin命令的语法如下：mysqladmin –u root –p flush-logs也可以登录mysql数据库中使用FLUSH LOGS语句来开启新的错误日志。

在mysql5.5.7之后：服务器将关闭此项功能。只能使用重命名原来的错误日志文件，手动冲洗日志创建一个新的：方式如下：

3

4

更多信息请查阅官方文档：

2.二进制日志:Binary Log 和 Binary Log Index

回顶部

二进制日志，也就是我们常说的binlog，也是MySQL Server 中最为重要的日志之一，主要用于记录修改数据或有可能引起数据改变的mysql语句，并且记录了语句发生时间、执行时长、操作的数据等等。所以说通过二进制日志可以查询mysql数据库中进行了哪些变化。一般大小体积上限为1G。

当我们通过log-bin=file_name打开了记录的功能之后，MySQL会将所有修改数据库数据的query以二进制形式记录到日志文件中。当然，日志中并不仅限于query语句这么简单，还包括每一条query所执行的时间，所消耗的资源，以及相关的事务信息，所以binlog是事务安全的。

和错误日志一样，binlog记录功能同样需要log-bin=file_name参数的显式指定才能开启，如果未指定file_name，则会在数据目录下记录为mysql-bin.******（*代表0～9之间的某一个数字，来表示该日志的序号）。

1)二进制开启状态

回顶部

5

binlog 还有其他一些附加选项参数：

max_binlog_size设置binlog的最大存储上限，一般设置为512M或1G,一般不能超过1G当日志达到该上限时，MySQL会重新创建一个日志开始继续记录。不过偶尔也有超出该设置的binlog产生，一般都是因为在即将达到上限时，产生了一个较大的事务，为了保证事务安全，MySQL不会将同一个事务**分开记录到两个binlog 中。

binlog-do-db=db_name参数明确告诉MySQL，需要对某个（db_name）数据库记录binlog，如果有了binlog-do-db=db_name参数的显式指定，MySQL会忽略针对其他数据库执行的query，而仅仅记录针对指定数据库执行的query。

binlog-ignore-db=db_name与binlog-do-db=db_name完全相反，它显式指定忽略某个（db_name）数据库的binlog记录，当指定了这个参数之后，MySQL会记录指定数据库以外所有的数据库的binlog。

binlog-ignore-db=db_name与binlog-do-db=db_name两个参数有一个共同的概念需要大家理解清楚，参数中的db_name不是指query语句更新的数据所在的数据库，而是执行query的时候当前所处的数据库。不论更新哪个数据库的数据，MySQL仅仅比较当前连接所处的数据库（通过use db_name切换后所在的数据库）与参数设置的数据库名，而不会分析query语句所更新数据所在的数据库。

mysql-bin.index 文件（binary log index）的功能是记录所有Binary Log的相对路径，保证MySQL 各种线程能够顺利的根据它找到所有需要的Binary Log文件。

binlog_cache_size =32768（占用内存空间） #默认值32768 binlog_cache_size：一个事务，在没有提交（uncommitted）的时候，产生的日志，记录到Cache中；等到事务提交（committed）需要提交的时候，则把日志持久化到磁盘。一般来说，如果我们的数据库中没有什么大事务，写入也不是特别频繁，2MB～4MB是一个合适的选择。但是如果我们的数据库大事务较多，写入量比较大，可与适当调高binlog_cache_size。同时，我们可以通过binlog_cache_use以及binlog_cache_disk_use(可以理解为虚拟内存）来分析设置的binlog_cache_size是否足够，是否有大量的binlog_cache由于内存大小不够而使用临时文件（binlog_cache_disk_use）来缓存了。

6

binlog_stmt_cache_size=32768 #当非事务语句使用二进制日志缓存，但是超出binlog_stmt_cache_size时，使用一个临时文件来存放这些语句。

2)事务概念解释

回顶部

  • 事务

表支持将批处理当做一个完整的任务统一提交或回滚，即对包含在事务中的多条语句要么全执行，要么全部不执行

  • 非事务

表则不支持此种操作，批处理中的语句如果遇到错误，在错误前的语句执行成功，之后的则不执行。

  • log_bin = mysql-bin #指定binlog的位置，默认在数据目录下。

  • binlog-format= {ROW|STATEMENT|MIXED} #指定二进制日志的类型，默认为MIXED。

3)mysql复制主要有三种方式

回顶部

基于SQL语句的复制(statement-based replication,SBR)，基于行的复制(row-based replication,RBR)，混合模式复制(mixed-based replication, MBR)。对应的，binlog的格式也有三种：STATEMENT，ROW，MIXED。

① STATEMENT模式（SBR）

回顶部

每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)

② ROW模式（RBR）

回顶部

不记录每条sql语句的信息，仅需记录哪条数据被修改了，修改成什么样了。缺点是会产生大量的日志，让日志暴涨。

③ MIXED模式（MBR）

回顶部

以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。即交替使用行和语句、由mysql服务器自行判断。

其中基于行的定义格式数据量会大一些但是可以保证数据的精确性

选项 作用
sync_binlog = 10 设定多久同步一次二进制日志至磁盘文件中，0表示不同步，任何正数值都表示对二进制每多少次写操作之后同步一次。当autocommit的值为1时，每条语句的执行都会引起二进制日志同步，否则，每个事务的提交会引起二进制日志同步
max_binlog_cache_size= {4096 ..18446744073709547520} 二进定日志缓存空间大小，5.5.9及以后的版本仅应用于事务缓存，其上限由max_binlog_stmt_cache_size决定。
max_binlog_stmt_cache_size= {4096 ..18446744073709547520} 二进定日志缓存空间大小，5.5.9及以后的版本仅应用于事务缓存
expire_log_days={0..99} 设定二进制日志的过期天数，超出此天数的二进制日志文件将被自动删除。默认为0，表示不启用过期自动删除功能。如果启用此功能，自动删除工作通常发生在MySQL启动时或FLUSH日志时。

通过编辑my.cnf中的log-bin选项可以开启二进制日志；形式如下：

log-bin [=DIR/[filename]]

其中，DIR参数指定二进制文件的存储路径；filename参数指定二级制文件的文件名，其形式为filename.number，number的形式为000001、000002等。每次重启mysql服务或运行mysql > flush logs;都会生成一个新的二进制日志文件，这些日志文件的number会不断地递增。除了生成上述的文件外还会生成一个名为filename.index的文件。这个文件中存储所有二进制日志文件的清单又称为二进制文件的索引

7

8

4)查看二进制日志

回顶部

二进制日志的定义方式为二进制格式；使用此格式可以存储更多的信息，并且可以使写入二进制日志的效率更高。但是不能直接使用查看命令打开并查看二进制日志。

9

当前使用的二进制文件及所处位置

回顶部

10

11

查看当前二进制文件的信息

回顶部

12

查看二进制日志信息的命令

回顶部

语法格式：SHOW BINLOG EVENTS[IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

查看所有的二进制信息

回顶部

mysql> show binlog eventsG;

查看指定日志的二进制信息

回顶部

mysql> show binlog events in 'mysql-bin.000016'G;

从指定的事件位置开始

回顶部

mysql> show binlog events  in  'mysql-bin.000016'  from 727;

13

14

注：二进制日志的记录位置：通常为上一个事件执行结束时间的位置

指定偏移量(不是语句，是事件)

回顶部

mysql> show binlog events in 'mysql-bin.000017' from 154 limit 3;

15

5)命令行下查看二进制日志

回顶部

由于无法使用cat等方式直接打开并查看二进制日志；所以必须使用mysqlbinlog命令。但是当正在执行mysql读写操作时建议不要使用此打开正在使用的二进制日志文件；若非要打开可flushlogs。mysqlbinlog命令的使用方式：

16

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!50003 SET
OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 219 #事件开始处

#160829 0:23:22 server id 1 end_log_pos 296 CRC32 0xc81eb3b9 Query
thread_id=2 exec_time=0 error_code=0

#160829 0:23:22年月日的简写方式；end_log_pos事件结束处；thread_id=2

哪个会话线程创建的此语句；exec_time=0 执行时长单位为秒；error_code=0 错误代码0表示没有

SET TIMESTAMP=1472401402/*!*/;

SET @@session.pseudo_thread_id=2/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0,
@(1-MySQL数据库初体验)@session.unique_checks=1, @@session.autocommit=1/*!*/;

SET @@session.sql_mode=1075838976/*!*/;

SET @@session.auto_increment_increment=1,
@@session.auto_increment_offset=1/*!*/;

/*!C utf8 *//*!*/;

SET
@@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

BEGIN

/*!*/;

# at 296

#160829 0:23:22 server id 1 end_log_pos 393 CRC32 0xbc94b235 Query
thread_id=2 exec_time=0 error_code=0

use `db2`/*!*/;

SET TIMESTAMP=1472401402/*!*/;

insert into tb1 values(3)

删除二进制日志信息

回顶部

二进制日志会记录大量的信息（其中包含一些无用的信息）。如果很长时间不清理二进制日志，将会浪费很多的磁盘空间。但是，删除之后可能导致数据库崩溃时无法进行恢复，所以若要删除二进制日志首先将其和数据库备份一份，其中也只能删除备份前的二进制日志，新产生的日志信息不可删。也不可在关闭mysql服务器之后直接删除因为这样可能会给数据库带来错误的。若非要删除二进制日志需要做如下操作：导出备份数据库和二进制日志文件进行压缩归档存储。删除二进制文件的方法如下：

方法1：根据文件或时间点来删除二进制日志：

回顶部

语法形式：

mysql> PURGE { BINARY | MASTER } LOGS {TO 'log_name' | BEFORE datetime_expr }

其中TO 'log_name'表示把这个文件之前的其他文件都删除掉，也可使用BEFORE datetime_expr指定把哪个时间之前的二进制文件删除了。

17

18

或者用ls查看

19

方法2：删除所有的二进制日志（慎用）：

回顶部

使用RESET MASTER语句可以删除所有的二进制日志。该语句的形式如下：

20

不建议在生产环境下使用此操作；删除所有的二进制日志后，Mysql将会重新创建新的二进制日志。新二进制日志的编号从000001开始。

3.事务日志(或称redo日志)

回顶部

事务日志（InnoDB特有的日志）可以帮助提高事务的效率。使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。事务日志采用追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多。事务日志持久以后，内存中被修改的数据在后台可以慢慢的刷回到磁盘。目前大多数的存储引擎都是这样实现的。

如果数据的修改已经记录到事务日志并持久化，但数据本身还没有写回磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这部分修改的数据。具有的恢复方式则视存储引擎而定。

一般情况下，mysql会默认提供多种存储引擎,你可以通过下面的查看:

看你的mysql现在已提供什么存储引擎: 

mysql> show engines;

看你的mysql当前默认的存储引擎: 

mysql> show variables like '%storage_engine%';

你要看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎): 

mysql> show create table 表名;

注：

create table 库名.表名 engine = innodb; 这样就可以将表的引擎变更为innodb引擎了。
也可以在创建表之后通过下面语句来变更：
alter table库名.表名engine =innodb;

查看事务日志的定义：

mysql> show global variables like '%log%';

显示结果：

| innodb_flush_log_at_timeout | 1 |

| innodb_flush_log_at_trx_commit | 1

在事务提交时innodb是否同步日志从缓冲区到文件中，当这个值为1（默认值）之时，在每个事务提交时，日志缓冲被写到日志文件，对日志文件做到磁盘操作的刷新，性能会很差造成大量的磁盘I/O但这种方式最安全；如果设为2,每次提交事务都会写日志，但并不会执行刷的操作。每秒定时会刷到日志文件。要注意的是，并不能保证100%每秒一定都会刷到磁盘，这要取决于进程的调度。每次事务提交的时候将数据写入事务日志，而这里的写入仅是调用了文件系统的写入操作，而文件系统是有 缓存的，所以这个写入并不能保证数据已经写入到物理磁盘。设置为0，日志缓冲每秒一次地被写到日志文件，并且对日志文件做到磁盘操作的刷新，但是在一个事务提交不做任何操作。

注：刷写的概念

刷写其实是两个操作，刷（flush）和写（write），区分这两个概念是很重要的。在大多数的操作系统中，把Innodb的log buffer（内存）写入日志（调用系统调用write），只是简单的把数据移到操作系统缓存中，操作系统缓存同样指的是内存。并没有实际的持久化数据。

所以，通常设为0和2的时候，在崩溃或断电的时候会丢失最后一秒的数据，因为这个时候数据只是存在于操作系统缓存。之所以说“通常”，可能会有丢失不只1秒的数据的情况，比如说执行flush操作的时候阻塞了。

总结

设为1当然是最安全的，但性能页是最差的（相对其他两个参数而言，但不是不能接受）。如果对数据一致性和完整性要求不高，完全可以设为2,如果只最求性能，例如高并发写的日志服务器，设为0来获得更高性能

  • innodb_locks_unsafe_for_binlog OFF
  • innodb_log_buffer_size 16777216
  • innodb_log_checksums ON
  • innodb_log_compressed_pages ON
  • innodb_log_file_size 50331648 日志文件大小
  • innodb_log_files_in_group 2 # DB中设置几组事务日志，默认是2
  • innodb_log_group_home_dir ./

定义innodb事务日志组的位置,此位置设置默认为MySQL的datadir |

每个事务日志都是大小为50兆的文件（不同版本的mysql有差异）：

在mysql中默认以ib_logfile0,ib_logfile1名称存在

21

4.慢查询日志:slow query log

回顶部

顾名思义，慢查询日志中记录的是执行时间较长的query，也就是我们常说的slow query。

慢查询日志采用的是简单的文本格式，可以通过各种文本编辑器查看其中的内容。其中记录了语句执行的时刻，执行所消耗的时间，执行用户，连接主机等相关信息。

1)慢查询日志的作用

回顶部

慢查询日志是用来记录执行时间超过指定时间的查询语句。通过慢查询日志，可以查找出哪些查询语句的执行效率很低，以便进行优化。一般建议开启，它对服务器性能的影响微乎其微，但是可以记录mysql服务器上执行了很长时间的查询语句。可以帮助我们定位性能问题的。MySQL 还提供了专门用来分析满查询日志的工具程序mysqldumpslow，用来帮助数据库管理人员解决可能存在的性能问题。

2)查看慢查询日志的定义

回顶部

22

23

3)启动和设置慢查询日志

回顶部

方法1：通过配置文件my.cnf开启慢查询日志

回顶部

注：在不同的mysql版本中，开启慢查询日志参数不太一样，不过都可以通过 show variables like "%slow%" 和show variables like "%long%"查看出来。

24

其中：

变量名称 解释
slow_query_log： off关闭状态 on开启状态
slow_query_log_file 慢查询日志存放地点
long_query_time 选项来设置一个时间值，时间以秒为单位，可以精确到微秒。如果查询时间超过了这个时间值（默为10秒），这个查询语句将被记录到慢查询日志中,设置为0的话表示记录所有的查询。
slow_launch_time 表示如果建立线程花费了比这个值更长的时间,slow_launch_threads 计数器将增加

注：如果不指定存储路径，慢查询日志默认存储到mysql数据库的数据文件下，如果不指定文件名，默认文件名为hostname-slow.log

修改my.cnf文件：

25

重启mysqld服务

再次查询慢查询日志定义：

26

27

方法2：通过登录mysql服务器直接定义，方式如下

回顶部

mysql>set global slow_query_log=1;  开启慢查询日志
Query OK, 0 rowsaffected (0.35 sec)

mysql>set session long_query_time=0.0001; 更改时间（当前`session`中，退出则重置）
Query OK, 0 rowsaffected (0.00 sec)

mysql>set global long_query_time=0.0001; 更改时间（全局中，重启服务则重置）

mysql> SHOW VARIABLES LIKE 'long%';  查询定义时间

4)查看慢查询日志

回顶部

mysql> use mysql;
mysql> selec tuser,host from user where user="root";

28

或用系统查看文件内容命令如cat直接查看慢日志文件

29

30

第一行表示记录日志时的时间。其格式是 YYYY-MM-DD HH:MM:SS。我们可以看出上面的查询记录于 2016 年 8 月 29 日下午 15:47：24

  • 注意：这个是服务器时间.

MySql 用户、服务器以及主机名第三行表示总的查询时间、锁定时间、"发送"或者返回的行数

Query_time: 0.000304 表示用了0.000304秒

Lock_time: 0.000128 表示锁了0.000128秒

Rows_sent: 4 表示返回4行

Rows_examined: 4 表示一共查了4行

SET timestamp=UNIXTIME; 这是查询实际发生的时间

何将其变成一个有用的时间，将 Unix 时间转成一个可读的时间，可以使用 date–d@日志中的时间戳

31

以看到查询进行的同时记录了该日志 ，但是对于一台超负载的服务器常常并非如此。因此记住：SET timestamp= value 才是实际的查询的执行时间。

慢查询分析mysqldumpslow

回顶部

们可以通过打开log文件查看得知哪些SQL执行效率低下。从日志中，可以发现查询时间超过long_query_time时间的query为慢查询，而小于long_query_time时间的没有出现在此日志中。

如果慢查询日志中记录内容很多，可以使用mysqldumpslow工具（MySQL客户端安装自带）来对慢查询日志进行分类汇总。mysqldumpslow对日志文件进行了分类汇总，显示汇总后摘要结果

进入log的存放目录，运行

[root@localhost data]# mysqldumpslow mysqld-slow.log

32

注：

mysqldumpslow -s c -t 10 /database/mysql/slow-query.log

这会输出记录次数最多的10条SQL语句，其中：

-s, 是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒序；

-t, 是top n的意思，即为返回前面多少条的数据；

-g, 后边可以写一个正则匹配模式，大小写不敏感的；

例如：

/path/mysqldumpslow -s r -t 10 /database/mysql/slow-log

得到返回记录集最多的10个查询。

/path/mysqldumpslow -s t -t 10 -g “left join”/database/mysql/slow-log

官方文档位置 4.6.8 mysqldumpslow — Summarize Slow Query Log Files

得到按照时间排序的前10条里面含有左连接的查询语句。

[root@master data]# mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count
                 l: lock time
                 r: rows sent
                 t: query time  
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time

ii.数据文据

回顶部

在MySQL中每一个数据库都会在定义好（或者默认）的数据目录下存在一个以数据库名字命名的文件夹，用来存放该数据库中各种表数据文件。不同的MySQL存储引擎有各自不同的数据文件。如MyISAM用.MYD作为扩展名，Innodb 用.ibd，Archive 用.arc，CSV用.csv，等等。

如何查看你的mysql现在已提供什么存储引擎: 

mysql> show engines;

看你的mysql当前默认的存储引擎: 

mysql> show variables like '%storage_engine%';

你要看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎): 

mysql> show create table 表名;

注：

create table 库名.表名 engine = innodb; 这样就可以将表的引擎变更为innodb引擎了。

登录mysql，创建一个数据库如testdb，并在数据库中创建一个表，如下图所示：

1

查看数据库所在目录会发现数据目录下存在一个以数据库名字命名的文件夹

2

查看testdb目录的文件列表

3

从上图可以看出表使用的是innodb存储引擎。

以myisam存储引擎创建一个测试表tb2

4

查看数据库目录

5

注：修改mysql的默认存储引擎

  • 1、查看mysql存储引擎命令，在mysql>提示符下搞入show engines;字段Support为:Default表示默认存储引擎
  • 2、设置InnoDB为默认引擎：在配置文件my.cnf中的 [mysqld]下面加入default-storage-engine=INNODB 一句
  • 3、重启mysql服务器：mysqladmin -u root -p shutdown或者service mysqld restart登录mysql数据库，

1. .frm文件

回顶部

与表相关的元数据（meta）信息都存放在.frm文件中，包括表结构的定义信息等。不论是什么存储引擎（MySQL常用的两个存储引擎是MyISAM和InnoDB），每一个表都会有一个以表名命名的.frm文件。所有的.frm文件都存放在所属数据库的文件夹下面。

MyISAM数据库表文件： .MYD文件：表数据文件； .MYI文件：索引文件

2. .MYD文件

回顶部

.MYD文件是MyISAM存储引擎专用，存放MyISAM 表的数据。每一个MyISAM 表都会有一个.MYD文件与之对应，同样存放于所属数据库的文件夹下，和.frm文件在一起。

3. .MYI文件

回顶部

.MYI文件也是专属于MyISAM 存储引擎的，主要存放MyISAM表的索引相关信息。对于MyISAM 存储来说，可以被cache的内容主要就是来源于.MYI文件中。每一个MyISAM表对应一个.MYI文件，存放于位置和.frm以及.MYD一样。

InnoDB采用表空间（tablespace）来管理数据，存储表数据和索引。

.ibd文件：单表表空间文件，每个表使用一个表空间文件（file pertable），存放用户数据库表数据和索引。

InnoDB共享表空间（即InnoDB文件集，ib-file set）：ibdata1、ibdata2等，存储InnoDB系统信息和用户数据库表数据和索引，所有表共用。

4. .ibd文件和ibdata 文件

回顶部

这两种文件都是存放Innodb 数据的文件，之所以有两种文件来存放Innodb的数据（包括索引），是因为Innodb的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是独享表空间存放存储数据。独享表空间存储方式使用.ibd文件来存放数据，且每个表一个.ibd文件，文件存放在和MyISAM数据相同的位置。如果选用共享存储表空间来存放数据，则会使用ibdata文件来存放，所有表共同使用一个（或者多个，可自行配置）ibdata 文件。

ibdata 文件可以通过innodb_data_home_dir和innodb_data_file_path两个参数共同配置组成， innodb_data_home_dir配置数据存放的总目录， 而innodb_data_file_path 配置每一个文件的名称。

innodb_data_file_path 中可以一次配置多个ibdata文件。文件可以是指定大小，也可以是自动扩展的，但是Innodb 限制了仅仅只有最后一个ibdata文件能够配置成自动扩展类型。当我们需要添加新的ibdata文件的时候，只能添加在innodb_data_file_path配置的最后，而且必须重启MySQL才能完成ibdata的添加工作。不过如果我们使用独享表空间存储方式的话，就不会有这样的问题。

总结：

共享表空间以及独占表空间都是针对数据的存储方式而言的。

共享表空间:

某一个数据库的所有的表数据，索引文件全部放在一个文件中。

独占表空间: 每一个表都将会生成以独立的文件方式来进行存储，每一个表都有一个.frm表描述文件，还有一个.ibd文件。 其中这个文件包括了单独一个表的数据内容以及索引内容。

两者之间的优缺点

共享表空间：
|优缺点|内容| |:----|:---| |优点|可以放表空间分成多个文件存放到各个磁盘上。数据和文件放在一起方便管理。 | |缺点|所有的数据和索引存放到一个文件中，多个表及索引在表空间中混合存储，这样对于一个表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析，日值系统这类应用最不适合用共享表空间。|

独立表空间：

优点：

  • 1．每个表都有自已独立的表空间。
  • 2．每个表的数据和索引都会存在自已的表空间中。
  • 3．可以实现单表在不同的数据库中移动。
  • 4．空间可以回收
    • a) Drop table操作自动回收表空间，如果对于统计分析或是日值表，删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。
    • b)对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。

缺点：
单表增加过大，如超过100个G。

相比较之下，使用独占表空间的效率以及性能会更高一点

查看当前数据库的表空间管理类型

6

ON代表独立表空间管理，OFF代表共享表空间管理；（查看单表的表空间管理方式，需要查看每个表是否有单独的数据文件）

7

Innodb共享表空间配置：

修改my.cnf文件：

8

参数解释： |参数|解释| |:----|:-----| |innodb_data_home_dir = "/path/"|数据库文件所存放的目录| |innodb_log_group_home_dir = "/path/" |日志存放目录| |innodb_data_file_path=ibdata1:10M:autoextend |设置一个可扩展大小的尺寸为10MB的数据文件（共享数据文件），名为ibdata1。没有给出文件的位置，所以默认的是在MySQL的数据目录内。| |innodb_file_per_table=1｜0 |1为使用独占表空间，0为使用共享表空间|

注：InnoDB不创建目录，所以在启动服务器之前请确认”所配置的路径目录”的确存在。

重启mysqld服务

9

mysqld启动失败,查看错误日志

# tail -20 /usr/local/mysql/data/mysqld.err

显示内容如下：

10

注：不同版本的mysql报错略有不同，注意看错误日志的内容。

从错误日志中显示可以看出

在/etc/my.cnf文件中设置6400页而当前ibdata1为768页

需要计算768/64=12 修改配置为

11

重启mysqld服务

12

启动mysql，成功！

注：计算公式：64pages相当于1M，1page是16KB

如果不清楚默认文件page大小，可以先 du -h ibdata1 查看下，再去设置；

13

这说明mysql5.7.13中ibdata初始化为12M

登录mysql执行mysql> show variables like '%innodb_file_per_table%';

14

15

这时新建的表就会使用共享表空间了。

创建一个数据库testdb并新建一个表

16

向表中插入若干行数据

这里定义一个存储过程向表中插入100000行数据

17

调用存储过程

18

查看表中行数：

19

如何查看表在表空间占用情况：

方法1：对INNODB，你可以直接用命令show table status查看某个表的表空间占用情况。

20

方法2：

如果想知道MySQL数据库中每个表占用的空间、表记录的行数的话，可以打开MySQL的 information_schema 数据库。在该库中有一个 TABLES 表，这个表主要字段分别是：

参数 解释
TABLE_SCHEMA 数据库名
TABLE_NAME 表名
ENGINE 所使用的存储引擎
TABLE_ROWS 记录数
DATA_LENGTH 数据大小
INDEX_LENGTH 索引大小

21

iii.Replication相关文件

回顶部

1.master.info 文件

回顶部

master.info文件存在于Slave 端的数据目录下，里面存放了该Slave 的Master 端的相关信息，包括Master 的主机地址，连接用户，连接密码，连接端口，当前日志位置，已经读取到的日志位置等信息。

2.relay log 和relay log index

回顶部

mysql-relay-bin.xxxxxn文件用于存放Slave 端的I/O 线程从Master端所读取到的Binary Log 信息，然后由Slave 端的SQL 线程从该relay log 中读取并解析相应的日志信息，转化成Master 所执行的SQL 语句，然后在Slave 端应用。

mysql-relay-bin.index 文件的功能类似于mysql-bin.index，同样是记录日志的存放位置的绝对路径，只不过他所记录的不是Binary Log，而是Relay Log。

3.relay-log.info 文件

回顶部

类似于master.info，它存放通过Slave的I/O线程写入到本地的relay log的相关信息。供Slave 端的SQL 线程以及某些管理操作随时能够获取当前复制的相关信息。

iv.其他文件

回顶部

1.system config file

回顶部

MySQL的系统配置文件一般都是my.cnf，默认存放在/etc目录下，my.cnf文件中包含多种参数选项组（group），每一种参数组都通过中括号给定了固定的组名，如[mysqld]组中包括了mysqld服务启动时候的初始化参数，[client]组中包含着客户端工具程序可以读取的参数。

2.pid file

回顶部

pid file 是mysqld 应用程序在Unix/Linux 环境下的一个进程文件，和许多其他Unix/Linux服务端程序一样，存放着自己的进程id。

3.socket file

回顶部

socket 文件也是在Unix/Linux 环境下才有的，用户在Unix/Linux环境下客户端连接可以不通过TCP/IP 网络而直接使用Unix Socket 来连接MySQL。

mysql有两种连接方式，常用的一般是tcp``mysql –h mysql主机ip -uroot -pxxx mysql -S /path /mysql.sock

22

注：采用unix socket连接方式，比用tcp的方式更快，但只适用于mysql和应用同在一台PC上。

回顶部

results matching ""

    No results matching ""