mysql数据库的特性以及参数性能

本篇文章给大家主要讲的是关于MySQL数据库的特性以及参数性能的内容，感兴趣的话就一起来看看这篇文章吧，相信看完mysql数据库的特性以及参数性能对大家多少有点参考价值吧。                

成都创新互联公司主营土默特右旗网站建设的网络公司,主营网站建设方案,重庆App定制开发,土默特右旗h5微信小程序定制开发搭建,土默特右旗网站营销推广欢迎土默特右旗等地区企业咨询

一：mysql与其他数据库的比较

MySQL是一个关系型数据库管理系统，开发者为瑞典MySQL AB公司，现在已经被Sun公司收购，支持FreeBSD、Linux、MAC、Windows等多种操作系统与其他的大型数据库例如Oracle、DB2、SQL Server等相比功能稍弱一些

1、可以处理拥有上千万条记录的大型数据

2、支持常见的SQL语句规范

3、可移植行高，安装简单小巧

4、良好的运行效率，有丰富信息的网络支持

5、调试、管理，优化简单(相对其他大型数据库)

易用性比较

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

性能比较

   MySQL一直以来奉行一个原则，那就是在保证足够稳定性的前提下，尽可能地提高自身的处理能力。也就是说，在性能和功能方面，MySQL第一考虑的要素主要还是性能，MySQL希望能够在满足客户99%的需求的前提下，将剩余的所有精力都用来努力提高系统性能，而不希望自己是一个比其他任何数据库的功能都要强大的产品。

总体来说，MySQL数据库在发展过程中一直追求三项原则：简单、高效、可靠。

二：mysql架构组成

 mysql物理文件组成：

1）日志文件：主要包含{错误日志、查询日志、慢查询日志、事物日志、二进制日志}

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

首先挨个介绍日志的功能：

错误日志：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. 

2、运行状态： 
SHOW [GLOBAL | SESSION] STATUS [like_or_where] 
SHOW STATUS：provides server status information.

那么接下来修改系统配置：在主配置文件中

vi /etc/my.cnf

    例如：log_bin=/usr/local/mysql/data/

查看mysql的版本

或

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

错误日志的状态：

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.

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

在主配置文件中： vi /etc/my.cnf

log-error = /usr/local/mysql/data/mysqld.err

查看mysql的错误日志内容：

在工作中有时候希望将错误日志做备份，并且重新记录，这时候可以使用mysql的flush logs刷新日志进行生成新的日志文件。备份文件为.beifen结尾

删除错误日志：

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

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

更多信息请查阅官方文档：http://dev.mysql.com/doc/refman/5.5/en/error-log.html

http://dev.mysql.com/doc/refman/5.6/en/error-log.html

http://dev.mysql.com/doc/refman/5.7/en/error-log.html

二进制日志：Binary Log & Binary Log Index

二进制日志，俗称Binary Log，主要用于记录修改数据或有可能引起数据改变的mysql语句；并且记录语句的发生时间、执行时长、操作数据。。。。一般情况下大小体积上限为1G

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

主：如果log-bin日志不开启的话那么将无法做mysql主从复制

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

二进制的开启：

当前是关闭状态

可以通过直配置文件开启：

之后重启mysql服务

再次查看mysql服务已经启动：

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。

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_size ：一个事务，在没有提交（uncommitted）的时候，产生的日志，记录到Cache中；等到事务提交（committed）需要提交的时候，则把日志持久化到磁盘。 

接着，binlog_cache_size设置多大呢？答案是：根据公司生产的实际情况而定 
设置太大的话，会比较消耗内存资源（Cache本质就是内存），更加需要注意的是：binlog_cache不是全局的，是按SESSION为单位独享分配的，也就是说当一个线程开始一个事务的时候，Mysql就会为这个SESSION分配一个binlog_cache （备注：我想之所以以SESSION为单位分配binlog_cache是有道理的，因为不同的mysql请求，产生的binlog数量不一样的，批量插入数据必然产生大量的binlog，而简单注册一个用户，产生的binlog有限，那么JDBC连接上能否设置binlog_cache_size呢？）。 
设置太小的话，如果用户提交一个“长事务（long_transaction）”，比如：批量导入数据。那么该事务必然会产生很多binlog，这样cache可能不够用（默认binlog_cache_size是32K），不够用的时候mysql会把uncommitted的部分写入临时文件（临时文件cache的效率必然没有内存cache高），等到committed的时候才会写入正式的持久化日志文件。 

概念解释：

事务表支持将批处理当做一个完整的任务统一提交或回滚，即对包含在事务中的多条语句要么全执行，要么全部不执行
非事务表则不支持此种操作，批处理中的语句如果遇到错误，在错误前的语句执行成功，之后的则不执行。

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

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

概念解释：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云服务器自行判断。

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

注：在生产环境下多使用MBR模式，虽然I/O使用增大，但对数据安全性比较高

sync_binlog = 10#设定多久同步一次二进制日志至磁盘文件中，0表示不同步，任何正数值都表示对二进制每多少次写操作之后同步一次。当autocommit的值为1时，每条语句的执行都会引起二进制日志同步，否则，每个事务的提交会引起二进制日志同步

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

log-bin = /usr路径

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

查看二进制日志：

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

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

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

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

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

#查看所有的二进制信息

mysql> show binlog events\G;

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

#从指定的事件位置开始

mysql> show binlog events in 'log.000002' from 1215\G;

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

删除二进制日志信息：

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

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

语法形式：

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

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

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

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

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名称存在

4、慢查询日志：slow query log

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

慢查询日志采用的是简单的文本格式，可以通过各种文本编辑器查看其中的内容。其中

记录了语句执行的时刻，执行所消耗的时间，执行用户，连接主机等相关信息。

慢查询日志的作用：

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

查看慢查询日志的定义：

\启动和设置慢查询日志：

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

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

其中：

slow_query_log： off关闭状态 (0) on开启状态(1)
slow_query_log_file  慢查询日志存放地点

long_query_time选项来设置一个时间值，时间以秒为单位，可以精确到微秒。如果查询时间超过了这个时间值（默认为10秒），这个查询语句将被记录到慢查询日志中,设置为0的话表示记录所有的查询。

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

修改my.cnf文件：

另外也可以通过mysql直接定义（只不过属于临时生效）

mysql>set globalslow_query_log=1;  #开启慢查询日志

Query OK, 0 rowsaffected (0.35 sec)

mysql>setsession long_query_time=0.0001; #更改时间（当前session中，退出则重置）

Query OK, 0 rowsaffected (0.00 sec)

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

mysql> SHOWVARIABLES LIKE 'long%';  #查询定义时间

查看慢查询日志

查看文件内容命令如cat直接查看慢日志文件

数据文据  (在这里主要介绍myisam和innodb的区别以及功能)

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

如何查看你的mysql现在已提供什么存储引擎:
mysql> show engines;
看你的mysql当前默认的存储引擎:
mysql> show variables like '%storage_engine%';

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

另外换可以在创建表的时候在表名的后面跟engine=innodb 可以改变表的引擎

create table 库名.表名 engine = innodb

可以在文件目录当中查看创建的文件格式

查看mysql存储引擎命令，在mysql>提示符下搞入show engines;字段 Support为:Default表示默认存储引擎  

2、设置InnoDB为默认引擎：在配置文件my.cnf中的 [mysqld] 下面加入default-storage-engine=INNODB 一句
3、重启mysql云服务器：service mysqld restart 登录mysql数据库，

1、“.frm”

主要存放表的数据；包括定义表结构信息，另外在每个表当中都会有一个以表命名的.frm的文件，所有的文件存放在此文件夹下面

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

2、“.MYD”文件

myisam专门存放存储引擎的专用文件

3、“.MYI”文件

“.MYI”文件也是专属于MyISAM存储引擎的，主要存放MyISAM表的索引相关信息。

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

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

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

.idb和ibdata的区别：

.id两者之间的优缺点

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

独立表空间：

优点：
1．每个表都有自已独立的表空间。
2．每个表的数据和索引都会存在自已的表空间中。
3．可以实现单表在不同的数据库中移动。
4．空间可以回收
b只能存放单独的文件数据，ibdata可以存放多的数据相当一个共享文件夹

查看当前的数据库的表空间：

on代表独立表空间；off代表共享表空间

那么修改下主配置文件来开启共享表空间

可以先 du -h ibdata1 查看下

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

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

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

drop procedure  if exists test；  =====> 删除之前存在的文件

create  procedure  test() ========> 创建test文件

begin  ================>开始

declare i int；=========>  通告i的类型

set  i=1  ======>  i的数值等于1

while  i < 100000 do =====> i的值如果小于100000

insert  into  lxf.ttt(id) values (i); =======>插入数据变量为i

set i = i +1 ========>  每执行一次之后i的值加1直到为99999

end while ======>循环结束

end &&结束

调用存储过程：

查看标的行数

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

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 线程以及某些管理操作随时能够获取当前复制的相关信息。

其他文件：

1）system config file

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

2）pid file

pid file 是mysqld应用程序环境下的一个进程文件存放自己的pid号

3）socket file

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

mysql有两种连接方式，常用的一般是tcp
mysql–hmysql主机ip -uroot -pxxx（可以远程连接，但是速度稍慢）
mysql-S /path/mysql.sock （只能试用与本地连接，但速度快）

以上关于mysql数据库的特性以及参数性能详细内容，对大家有帮助吗？如果想要了解更多相关，可以继续关注我们的行业资讯板块。