mysql 数据整理_mysql数据库整理

数据库和实例简介

数据库：数据库是文件的集合，是依照某种数据模型组织起来并存放于二级存储器中的数据集合。

数据库实例：数据库实例是程序，是位于用户与操作系统之间的一层数据管理软件，用户对数据库数据的任何操作，包括数据库定义，数据查询，数据维护，数据库运行控制等都是在数据库实例下进行的，应用程序只有通过数据库实例才能和数据库打交道。

Mysql逻辑结构

Mysql体系结构

Mysql被设计为单进程多线程架构的数据库

mysql逻辑结构

Mysql采用的是C/S模式，Mysql客户端和Mysql服务器之间通过TCP 半双工连接(半双工数据传输指数据可以在一个信号载体的两个方向上传输，但是不能同时传输，一旦一端开始发送消息，另一端要接收完整个消息才能响应它)，由于Mysql采用的TCP连接，所以一旦客户端和服务器建立了连接(三次握手)，那么就占用服务器端的一个TCP连接，而Mysql服务器端对应的连接数是有限的，因此同一个时刻客户端连接到Mysql服务端的数量也是一定的，所以在开发数据库应用程序的时候一定不要老是占用TCP 连接不释放(如果一直占用不释放，则会导致新进来的客户端连接被拒绝或者超时)；而且由于客户端和服务器端采用的TCP连接(三次握手)，因此每一次连接对应的开销都是很大的，因此在客户端连接中一般采用的是数据库连接池的方式(常用的数据库链接池有DBCP，C3P0，druid等)，避免每次请求都重新建立新的TCP连接。

客户端用一个单独的数据包将查询请求发送给服务器，所以当查询语句很长的时候，需要设置max_allowed_packet参数。但是需要注意的是，如果查询实在是太大，服务端会拒绝接收更多数据并抛出异常。与之相反的是，服务器响应给用户的数据通常会很多，由多个数据包组成。但是当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。

连接Mysql操作是一个连接进程和Mysql数据库实例进行通信，从程序设计的角度来说，本质上是进程通信。

Mysql查询过程

Mysql查询过程

Mysql索引

索引其实对应的是存储引擎中存储数据的快速操作的方式，比如如果我们想从有序的数字列表中查找指定的数据，一种方式是从头到尾遍历列表，逐个比较，时间复杂度O(n)；另一种可能是使用二分查找(平衡二分查找树)来加快查找速度，时间复杂度O(logn)；但是对于数据库这种形式的查找，如果也采用平衡二分查找树的话，可能随着数据量的增加对应树的高度也在不断的增加，导致二分查找树在内存中可能存储不下，需要将其存储到磁盘中，这样就带来一个问题是如果根据二分查找树进行查找的话，可能需要耗时的磁盘读取操作，可以想象一下一棵几百万节点的二叉树的深度是多少？如果将这么大深度的一颗二叉树放磁盘上，每读取一个节点，需要一次磁盘的I/O读取，整个查找的耗时显然是不能够接受的。那么如何减少查找过程中的I/O存取次数？一种行之有效的解决方法是减少树的深度，将二叉树变为m叉树(多路搜索树)，而B+Tree就是一种多路搜索树。

存储引擎

Mysql存储引擎是基于表的，不是基于数据库的。

Innodb存储引擎

Innodb存储引擎体系架构

后台线程的主要作用是负责刷新内存池中的数据，保证缓存池中的内存缓存的是最近数据。此外将已修改的数据文件刷新到磁盘文件，同时保证在数据库发生异常的情况下InnoDB能恢复到正常运行状态。

后台线程类型

线程类型

说明

Master Thread

主线程主要负责将缓存池中的数据异步刷新到磁盘

IO Thread

IO线程主要负责这些IO请求的回调处理

Purge Thread

purge(净化)线程主要是来回收已经使用并分配的undo页

Page Cleaner Thread

线程的主要作用是将脏页的刷新操作

内存缓冲池

Innodb内存缓冲池

数据库连接池

常用的数据库连接池

DBCP(DataBase Connection Pool)

C3P0

druid

连接池中连接有效性校验

数据库连接池中对应的连接有可能已经和数据库服务器之间断开，而数据库连接池可能还认为对应的连接时有效的，如果客户端请求使用这样的连接就会导致连接丢失等异常，因此需要对数据库连接池中的连接进行有效性的判断；连接池内部有机制判断，如果当前的总的连接数少于miniIdle，则会建立新的空闲连接，以保证连接数得到miniIdle。如果当前连接池中某 个连接在空闲了timeBetweenEvictionRunsMillis时间后任然没有使用，则被物理性的关闭掉。有些数据库连接的时候有超时限制(mysql连接在8小时后断开，也就是说一个connection空闲超过8个小时，Mysql将自动断开该 connection)，或者由于网络中断等原因，连接池的连接会出现失效的情况，这时候设置一个testWhileIdle参数为true， 可以保证连接池内部定时检测连接的可用性，不可用的连接会被抛弃或者重建，最大情况的保证从连接池中得到的Connection对象是可用的。当然，为了 保证绝对的可用性，你也可以使用testOnBorrow为true(即在获取Connection对象时检测其可用性)，不过这样会影响性能。对于DBCP(DataBase Connection Pool)数据库连接池，默认配置的DBCP连接池，是不对池中的连接做测试的，有时连接已断开了，但DBCP连接池不知道，还以为连接是好的呢。应用从池中取出这样的连接访问数据库一定会报错。

mysql 命令行命令

连接mysql: mysql -h主机地址 -u 用户名 -p

进入指定数据库：use 数据库名称

查看用户：

>use mysql;

>select * from user;

查看存储引擎列表：

>show engines;

>show engine innodb status;

>show variables like 'innodb_version';

查找配置文件

>mysql --help | grep my.cnf

查看数据库参数

查看所有的参数

>show variables;

过滤指定的参数使用like

>show variables like 'xxxx';

在数据库information_schema的表GLOBAL_VARIABLES中查看参数

>select * from GLOBAL_VARIABLES where variable_name like 'xxxx%';

动态参数修改：set [GLOBAL] 变量名=值

定位错误日志文件：文件名称为 主机名.err

>show variables like 'log_error';

mysql 所在主机名称：

>system hostname;

Mysql文件

慢查询日志

查询慢查询日志对应的耗时阈值

>show variables like 'long_query_time';

# mysql5.6版本以上，取消了参数log-slow-queries，更改为slow-query-log-file，切记！！The --log-slow-queries option is deprecated and is removed (along with the log_slow_queries system variable) in MySQL 5.6. Instead, use the --slow_query_log option to enable the slow query log and the --slow_query_log_file=file_name option to set the slow query log file name.

查询日志慢查询开关

>show variables like 'log_slow_queries';

慢查询日志开关

>show variables like 'slow_query_log';

如果设置了log_queries_not_using_indexes,那么运行的sql语句没有使用索引，这条sql语句也会被记录到慢查询日志中，查询对应的功能是否打开

>show variables like 'log_queries_not_using_indexes';

变量log_throttle_queries_not_using_indexes用来表示每分钟允许记录到slow_log的且未使用索引的sql语句次数；

mysqldumpslow可以将慢查询日志放入一张表，方便查询，表名为mysql.slow_log；

变量long_query_io表示将超过指定逻辑IO次数的SQL语句记录到slow log中。

变量slow_query_type用来表示启用slow log的方式，可选值如下：

可选值

说明

0

表示不将SQL记录到slow log中

1

表示根据运行时间将SQL语句记录到slow log中

2

表示根据逻辑IO次数将SQL语句记录到slow log中

3

表示根据运行时间和逻辑IO次数将SQL语句记录到slow log中

查询日志

命名为 主机名.log，记录了所有对数据库请求的信息

二进制日志(binary log)

记录了对Mysql数据库执行更改的所有操作

>show master status;

从上面命名的结果中获得文件名

>show binlog events in '文件名';

通过配置参数log-bin [=name]可以启动二进制日志，如果不指定name，则默认二进制日志文件名为主机名，后缀名为二进制日志的序列号，所在路径为数据库所在的目录(datadir)，通过如下命名获得datadir:

>show variables like 'datadir';

查看二进制日志开关

>show variables like 'log_bin';

在mysql对应的配置文件my.cnf中增加如下内容开启二进制日志记录

log_bin=ON

log_bin_basename=/var/lib/mysql/mysql-bin

log_bin_index=/var/lib/mysql/mysql-bin.index

或者对应的简介方式

log-bin=/var/lib/mysql/mysql-bin

二进制日志文件在默认情况下没有启动，需要手动指定参数来启动。要查看二进制日志文件需要是使用mysqlbinlog工具。

pid文件

命名为 主机名.pid

pid文件受变量pid_file控制，查看pid_file变量通过如下命令

>show variables like 'pid_file';

表结构定义文件

Mysql数据的存储是根据表进行的，每个表都会有与之对应的文件。但不论表采用何种存储引擎，Mysql都有一个以frm为后缀名的文件，这个文件记录了该表的表结构定义。frm还用来存放视图的定义。

mysql数据库库和表文件定义

注意：文件后缀为ibdata(Innodb data表对应的数据文件，若设置了参数innodb_file_per_table，则用户可以将每个基于InnoDB存储引擎的表产生一个独立表空间，独立表空间的命名规则为:表名.idb，独立表空间文件仅仅存储该表的数据，索引和插入缓冲BITMAP等信息，其余信息还是存放在默认的表空间中，默认表空间的路径通过变量innodb_data_file_path来设置)。

重做日志文件

重做日志文件(redo log file)记录了关于InnoDB存储引擎的事务日志，默认文件为ib_logfile0和ib_logfile1，这两个文件构成了一个重做日志文件组(就是相当于AB缓存的意思，先写A缓存，A缓存满了，在写B缓存，B缓存满了在写A缓存)。

重做日志文件

参数innodb_log_files_in_group指定了日志文件组中重做日志文件的数量，默认为2。参数innnodb_mirrored_log_groups指定了日志镜像文件组的数量，默认为1，表示只有一个日志文件组，没有镜像。

参考：