当前位置 : 首页 » 博文聚焦 » 正文

mysql概念(实例,会话,事务,表空间)

分类 : 博文聚焦 | 发布时间 : 2018-04-25 23:08:49 | 浏览 : 42

概念:

实例(进程和线程

1、MySQL是单进程多线程(而Oracle等是多进程),也就是说MySQL实例在系统上表现就是一个服务进程,即进程(通过多种方法可以创建多实例,再安装一个端口号不同的mysql,或者通过workbench来新建一个端口号不同的 服务器实例等),该架构类似于SQL Server和Windows版本的Oracle;

2、MySQL实例是线程和内存组成,实例才是真正用于操作数据库文件的(MySQL数据库是由一些列物理文件组成,类似于frm、MYD、MYI、ibd结尾的文件);

3、一般情况下一个实例操作一个或多个数据库(Oracle一个实例对应一个数据库);集群情况下多个实例操作一个或多个数据库。


注:在实例启动的时候MySQL会读取配置文件,类似于Oracle的spfile文件,不同的是Oracle如果找不到参数文件会启动失

        败,MySQL如果找不到配置文件则会按照默认参数设置启动实例。



mysql中database、instance、session:

      mysql中建立一个会话,不是和具体的数据库相连接,而是跟某个instance建立会话(每个会话可以使用不同的用户身份)。

      而一个实例可以操作多个数据库,故一个会话(在操作系统概念里,会话即是线程)可以操作一个实例上的多个数据库。

       简单描述如下:instance >> database


connection和session的定义和区别:

1.连接(connection)是一个物理的概念,它指的是一个通过网络建立的客户端和专有服务器(Dedicated Server)或调度器(Shared Server)的一个网络连接。
2.会话(session)是一个逻辑的概念,它是存在于实例中。

注:创建一个连接(connection)实际上是在某个实例(instance,或者说是进程)中创建一个或多个线程。

两者关系:

  • 1、一个连接可以拥有多个会话也可以没有会话(实际上,一条连接上的各个会话可以使用不同的用户身份),同一个连接上的不同会话之间不会相互影响。
  • 2、 两个会话之间的影响,体现在锁和锁存,即对相同资源的操作(对象定义或数据块)或请求(CPU/内存),它们的处理一般是按队列来处理的,前面的没有处理好,后面的就要等待。如果以打电话来比喻:connect就好比你接通对方,这时,connect就建立了,有没有通话,不管。双方进行通话,则session建立了,如果换人,则新的session建立,原session结束,类似的,可以在同一个connect上进行多个会话。最后,挂机,connect结束。


1、会话可以创建多个事务
比如:使用客端连接数据库,这样你就可以执行很多个事务了

2、一个事务只能由一个会话产生
在数据库里的事务,如果在执行的SQL都是由会话发起的,哪怕是自动执行的JOB也是由系统会话发起的

3、一个事务可能会产生一个或多个线程
比如RMAN备份,是可以创建多个线程可加快备份速度

4、一个线程在同一时间内只能执行一个事务
而一个线程,在没结束当前事务是无法释放资源来执行第二个事务。所以当一个线程开始运行 begin


事务:简单理解局势一个业务需求的最小处理单位。

         事务是一个最小的、不可分割的工作单元,不论成功与否都作为一个整体进行工作。(我们可以把一系列的操作看成是事务,最经典的例子是“银行转帐”)。

     事务都应该具备ACID特征。所谓ACID是Atomic(原子性),Consistent(一致性),Isolated(隔离性),Durable(持久性)四个词的首字母所写,下面以“银行转帐”为例来分别说明一下它们的含义:

    原子性:组成事务处理的语句形成了一个逻辑单元,不能只执行其中的一部分。换句话说,事务是不可分割的最小单元。比如:银行转帐过程中,必须同时从一个帐户减去转帐金额,并加到另一个帐户中,只改变一个帐户是不合理的。

一致性:在事务处理执行前后,MySQL数据库是一致的。也就是说,事务应该正确的转换系统状态。比如:银行转帐过程中,要么转帐金额从一个帐户转入另一个帐户(在不考虑转账费用的情况下,转账方减少的金额与收账方的增加的金额应该是相等),要么两个帐户都不变,没有其他的情况。

   隔离性:一个事务处理对另一个事务处理没有影响。就是说任何事务都不可能看到一个处在不完整状态下的事务。比如说,银行转帐过程中,在转帐事务没有提交之前,另一个转帐事务只能处于等待状态。

   持久性:事务处理的效果能够被永久保存下来。反过来说,事务应当能够承受所有的失败,包括服务器、进程、通信以及媒体失败等等。比如:银行转帐过程中,转帐后帐户的状态要能被保存下来。

   

隐式提交

        有些语句在没有使用commit的时候,事务也会提交,这种情况称为隐式提交,ddl语句,事务语句和管理的语句都会产生隐式提交,这个跟oracle一样的。

        任何的DDL和DCL语句都会导致事务隐式提交,所以事务中应该现将DDL和DCl语句放在事务的前面或者后面。

        管理语句:ANALYZE TABLE, CACHE INDEX, CHECK TABLE, LOAD INDEX INTO CACHE, OPTIMIZE TABLE, REPAIR TABLE.这些也会导致事务隐式提交。

        START TRANSACTION、BEGIN语句都可以在mysql命令行下显式地开启一个事务。但是在存储过程中,MySQL分析会自动将BEGIN识别为BEGIN……END。因此在存储过程中,只能使用START TRANSACTION语句来开启一个事务。同时这个时候相当于执行unlock tables

        UNLOCK TABLES显示的释放当前session锁保持的锁。另外通过LOCK TABLES语句为当前session获取新锁前会隐式的释放当前session之前的所有锁。这个时候相当于进行了隐式提交。


共享表空间和单独表空间

    共享表空间:  某一个数据库的所有的表数据,索引文件全部放在一个文件中,默认这个共享表空间的文件路径在data目录下。 默认的文件名为:ibdata1  初始化为10M。

       用 show variables like 'innodb_data%'; 查看共享表空间

    优点:
    可以放表空间分成多个文件存放到各个磁盘上(表空间文件大小不受表大小的限制,如一个表可以分布在不同步的文件上)。数据和文件放在一起方便管理。
    缺点:
    所有的数据和索引存放到一个文件中以为着将有一个很常大的文件,虽然可以把一个大文件分成多个小文件,但是多个表及索引在表空间中混合存储,这样对于一个表做了大量删除操作后表空间中将会有大量的空隙,特别是对于统计分析,日值系统这类应用最不适合用共享表空间。

    独占表空间:  每一个表都将会生成以独立的文件方式来进行存储,每一个表都有一个.frm表描述文件,还有一个.ibd文件。 其中这个文件包括了单独一个表的数据内容以及索引内容,默认情况下它的存储位置也是在表的位置之中。

    show GLOBAL VARIABLES like 'innodb_file_per_table' 可以查看是否独占表空间
    优点:

1.  每个表都有自已独立的表空间。

2.  每个表的数据和索引都会存在自已的表空间中。

3.  可以实现单表在不同的数据库中移动。

4.  空间可以回收(除drop table操作处,表空不能自已回收)

a)   Drop table操作自动回收表空间,如果对于统计分析或是日值表,删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。

b)   对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。

c)   对于使用独立表空间的表,不管怎么删除,表空间的碎片不会太严重的影响性能,而且还有机会处理。

缺点:
单表增加过大,如超过100个G。

想要将共享表空间转化为独立表空间有两种方法:

1.先逻辑备份,然后修改配置文件my.cnf中的参数innodb_file_per_table参数为1,重启服务后将逻辑备份导入即可。

2.修改配置文件my.cnf中的参数innodb_file_per_table参数为1,重启服务后将需要修改的所有innodb表都执行一遍:alter tabletable_name engine=innodb;

使用第二种方式修改后,原来库中的表中的数据会继续存放于ibdata1中,新建的表才会使用独立表空间.


不同的表空间拥有不同文件格式:

   如数据库a,表格b。

    如果表格b采用MyISAM,data\a中会产生3个文件:

b.frm :描述表结构文件,字段长度等

b.MYD(MYData):数据信息文件,存储数据信息(如果采用独立表存储模式)

b.MYI(MYIndex):索引信息文件。

    如果表格b采用InnoDB,data\a中会产生1个或者2个文件:

b.frm :描述表结构文件,字段长度等

如果采用独立表存储模式,data\a中还会产生b.ibd文件(存储数据信息和索引信息)

如果采用共存储模式的,数据信息和索引信息都存储在ibdata1中

    如果采用分区存储,data\a中还会有一个b.par文件(用来存储分区信息)





相关阅读:

MySQL date format DD/MM/YYYY select query?

Multiple select statements in Single query

How to access remote server with local phpMyAdmin client?

Grant **all** privileges on database

How to get a list of MySQL user accounts

How can I prevent SQL injection in PHP?

How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?

'IF' in 'SELECT' statement - choose output value based on column values

Can't connect to local MySQL server through socket '/var/mysql/mysql.sock' (38)

How do you set a default value for a MySQL Datetime column?