MySQL服务器学习笔记!(三) ——视图/事物/锁,以及数据引擎

作者: 魏延是反贼. 分类: 数据库, 系统·安全·调优

这是一步步从头学习MySQL的笔记历程,本人在学习之前只接触过少量SQL Server

第一天历程:压榨自己的学习能力极限,每天学习新内容之MySQL服务器!(一)

第二天历程:压榨自己的学习能力极限,每天学习新内容之MySQL服务器!(二)

第三天历程:压榨自己的学习能力极限,每天学习新内容之MySQL服务器!(三)

第四天历程:压榨自己的学习能力极限,每天学习新内容之MySQL服务器!(四)

 

接下来每天都将更新~相信你通过笔记,一定能从零学会MySQL~

废话不多说,开始今天的学习压榨~!


视图

如何创建视图:

格式:CREATE VIEWview_name [(视图的字段column_list)]

CREATE VIEW view_name AS SELECT clause;

       视图保存的仅仅是语句本身,但是当视图创建之后,却可以像使用表一样使用它

创建视图的目的就是为了避免某些字段被某些用户查看的

假设我们有表:

       比如:把那些CouseID不为空的创建成一个视图,并且没有Totur段。

CREATE VIEW xiake AS SELECT UID,Name,Age,Gender,CouseID FROMknight WHERE CouseID IS NOT NULL;

       视图并不是真正的表,他没有保存数据,所以在处理数据的速度上,肯定是慢于真正的基表的。而当我们的基表改变之后,视图创建出来的虚表会立刻改变。

比如我们修改:

UPDATE knight SET CouseID=15WHERE UID=9

这时再去看刚才创建好的视图,发现它已经改变。

 

往视图中插入数据:

视图(虚表)是可以被插入数据的,但是,插入的所有数据其实是保存在基表之中的,而如果插入的这个数据,在基表中有值没有定义。则有可能报错甚至不让你创建。

WITH CHECK OPTION 在创建数据的时候,可以明确指定让基表中的约束应用到虚表中来。

 

删除视图:

DROP VIEWview_name

 

 

如何实现子查询:

我们SELECT实现了嵌套,我们的SELECT结果是从另一个SELECT的结果中得来的。

比如形式为:

SELECT * FROMSELECT * FROM Knight

SELECT * FROM xxx WHERE SELECT * FROMknight

UPDATE knight SETAge=49 WHERE UID=6

显示这个表中年龄大于所有有年龄人员的平均年龄的:

       我们先查询平均年龄:SELECTAVG(Age) FROM knight WHERE Age IS NOT NULL;

发现,平均年龄是33岁

则我们可以使用子查询,一步解决。

SELECTName,Age FROM knight WHERE Age > (SELECT AVG(Age) FROM knight WHERE Age ISNOT NULL);

 


事物:Transact

什么是事物呢?几个步骤要么同时完成,要么同时都没完成。对于我们SQL来说,则是几个查询语句,要么同时都执行,要么同时都不执行,他们要做一个原子操作,是不可分的。

事物最典型的应用就是在银行里转账。比如A有2000,B有10000,B要转给A3000元。如果刚转走,钱还没到A的时候,银行停电了,这时A还没有加钱,但是B的钱已经减掉了……这时怎么办呢?为了免除这样的差错。事物就出现了。

而事物对于这样的结果处理的方式是:将这3000退回B,让其重新转账。但是对于大型数据库,里面的事物可能含有上万条,所以如果执行到最后出错了,那么回退就要重新完成这上万条的指令。为了避免回退的过长,还可以对事物做“快照”,而这样的“快照”,就叫做Save Point,事物的保存点。

MyISAM是不支持事物的的

支持事物的引擎:InnoDB

只有当一个引擎完全支持ACID的时候,我们才说这个引擎支持事物。

ACID:

A:Atomicity :原子性。事物是一体的。

C:Consistency:一致性。当事物完成以后,A减掉的B一定要加上,这种逻辑,必须是要一致的。体现的其实是结果的一致。

I:Isolation:隔离性:多个事物之间的联系,一个事物发生了改变之后,其他的事物能否发现这样的改变。当正在执行的事物,运行到一条命令发现原有数据库发生改变的时候,它是否接受改变。这取决于数据引擎的隔离级别。对于MySQL来讲,事物的隔离级别是很重要的属性。

D:Durability:持久性:一个事物完成之后,完成的结果要永久保存下来。

 

MySQL的隔离级别:(MySQL的默认事物隔离级别是3.可重读级别。)

1.读未提交:READUNCOMMITTED(一个事物完成的过程叫做提交的过程)。这种级别是最低的。

2.读提交:READCOMMITTED

3.可重读:REPEATABLE-READ

4.串行化:SERIABLIZABLE

 

查看默认级别:

SHOW VARIABLES LIKE ‘tx_isolation’则看出级别为 REPEATABLES-READ

隔离的级别越高,事物的安全性越好,但是并发性越差(所能同时执行的事物就越少)。如果事物对你不是很重要,可以降低事物的级别来增加事物的并发性。

 

四种级别的关系状态:

       1.读未提交:假设A事物中有x=0,B事物在运行时要去读x,当5分钟的时候x被A改为了1,此时B立刻获取A=1,当10分钟的时候,A将x回退回了0,则B中的x立刻变为0

       2.读提交:假设A事物中有x=0,B事物在运行时要去读x,当5分钟的时候x被A改为了1,此时B获取不到,所以x=0,当10分钟的时候,A事物运行结束,x=1,则B在运行的时候,立刻发现x=1并修改自己的x=1,15分钟的时候,B事物运行结束,提交之后,B中的x=1

       3.可重读:无论A中的x如何改变,还是提交之后改变。B在事物的运行中都不管x如何改变,只负责将自己的最初读取的x值为事物的x值运行结束,之后,当提交结束之后,B将x重读为1。

       4.串行化:只有一个事物彻底执行完成后,另一个事物才会开始的。当B事物运行的时候,发现A中的x发生了改变,则B不会开始自己事物的运行。直到A事物彻底执行完成。则B再读取A中最新的x值,开始执行。

 

如何实现两个事物在执行的时候,你执行你的,我执行我的,互相之间发生的数据改变对自己来说是不可见的。而这种类似的机制,我们就叫做多版本并发控制,MACC。

 

事物的引擎:

要想使用相应的事物,必须给予相应的数据引擎来决定。

事物是隐式的

START TRANSACTION:手动开启事物。开启之后,使用updata之类的命令,只有当你手动结束事物COMMIT的时候,才会保存到表中去。

比如:  START TRANSACTION;    开启事物。

SELECT * FROM knight;    查看发现有13行

INSERTINTO knight SET Name=’Shi Zhongyu’,Age=37;    在事物中插入一行;

SELECT* FROM knight    ;发现已经插入了一行变为了14行。

ROLLBACK;     回滚操作,直接退回插入前

SELECT* FROM knight;     则发现,又变成了13行

COMMIT;     结束事物,保存结果至表中。

 

查看事物级别:

SELECT @@SESSION.tx_isolation;

修改会话级别的事物级别:

SET SESSION TRANSACTIONISOLATION LEVEL READ UNCOMMITTED;

查看当前MySQL中的连接进程

SHOW PROCESSLIST

 

我们在两个事物中操作,去查看其关联样式。就能发现如同刚才叙述的级别效果一样,会产生那样的效果。

 


MySQL数据同步,锁

什么是数据同步?如果有两个进程或者两个应用需要访问同一个数据的话,这时候就要启用到同步概念。只要一牵扯到同步,就一定要用到锁的概念。锁:两个操作加之于同一个对象的时候如何避免讹误的。

假设现在有两个SQL语句,M正在插入knight,而N则查询knight表,这样,当N查询的时候,查询的其实可能只是一部分内容。那如何解决呢?则锁的机制决定了,一个人在做事的时候,另一个人不能对其操作。

锁是无时无刻不在的,只要有一个人读或者写,则就开始加锁。

 

锁的分类:

共享锁:读锁,大家都可以同时进行,读是可以允许别人也读的,但是读不允许别人写。

独占锁:写锁,写的时候不允许别人写。也不允许别人读。

而写锁的优先级是比较高的,但是你不能一直在写,过多的读锁是会造成写饥饿的,而过多的写锁也会造成读饥饿的~!

       读和写的锁是互斥的,写和写的锁也是互斥的。只有读和读的锁才是共享的

 

SHOW ENGINE的时候里面就能看到引擎锁

为了保证备份的一致性,在你备份的时候是不能让别人写的。

 

锁的级别:

表锁,一锁就锁定了一张表(并发级别低)

行锁:一锁只锁定了几行(并发级别相对较高,但是可控制性要麻烦和复杂的多的多)

事物引擎大部分都是行级别的锁。而像MyISAM这种就用的是表级别的锁。

 

锁的实现:

服务器级别的实现:在服务器上无论你是哪种存储引擎我都加锁(表锁)

存储引擎级别的实现:大部分事务性引擎都可以实现行锁,有的数据库还能实现页锁(页就是内存的空间Page)。

 

小扩展:页(Page)

 

我们的内存是有虚拟内存空间的。对于任何一个32位系统的进程,都会认为自己有4G的内存可用。当我们只有512M的内存的时候,怎么办呢?我们只是把程序的需要使用的内存在内存上映射出一块,而实现映射的这个功能就是靠页(Page)来实现的。我们的内存就是按页,来储存的。

 

 

 

对表进行加锁

LOCKTABLES tbl_name READ|WRITE,

1.我们给knight加上一个读锁。

LOCK TABLESknight READ;,则到另一台终端上,发现虽然加锁,但依然可以读取。

2.我们给knight换成一个写锁。

LOCK TABLESknight WRITE;

此时,当我们没有给表写东西的时候,在另一台终端上能读,但是当我们在本地插入一个数据之后,数据表真正被锁,而另一台终端上就发现不能读取了,并且一直在等待中… 而当我们只要在本地一释放锁,则另一终端立刻显示读取的新信息。

 

对锁进行释放:

UNLOCK TABLES;

 

为了实现更好的并发性,我们可以实现降低粒度(解析度),来降低锁的级别/范围,这样并发性就会大大提高。

 

 


数据引擎:

存储引擎是表级别的概念,所以存储引擎通常也被称为表类型。有的引擎支持事物,有的存储引擎不支持,如果在一个数据库中使用多种含事物和非事物的引擎的话,势必将会对用户在事物的支持上产生麻烦。

 

常见的存储引擎:       

       MyISAM/Merge:不支持事物, 表级别的锁,不支持在线备份

       InnoDB:支持事物,行级别的锁,支持在线备份(热备)。

       MEMORY:内存事物存储引擎。只要存储在这个表中的数据都保存在内存中。内存引擎的主要目的,其实是来提供内存数据库的,不提供数据的持久化存储,所以内存引擎通常用于实现内存表(临时表)。所以MEMORY表也被称作堆表。

       Maria:支持事物引擎,支持行级别的锁。当InnoDB被收购之后,那些原有开发InnoDB的人开发了Maria,仍处于开发中……

       Falcon:支持事物,也支持热备,行级别的锁,但是需要Mysql 6.0

       PBXT:一个商业化的引擎

       FEDERATED:联合存储引擎,只是用来联合两个表的,本身并不存储什么数据

       NDB:MySQL的集群当中才能够用到的引擎,将集群中的数据在内存中处理,构建这个集群需要5台以上的主机,性能非常好。

       Archive:归档存储引擎,用于实现将数据“挖掘”(智能决策,决策支持)用的。很少在修改它们,对于这种很少用到修改的数据,我们可以将其压缩存放。这就是归档存储引擎。

       Blackhole:黑洞引擎,类似于Linux中的/dev/null,你存进去的任何数据都会被引擎悄悄的丢弃了。在后面的复制日志,作为转发节点使用。

       CSV:存储成文本文件的数据库,主要为了跟其他的文本处理工具进行兼容,也为了移植,但是本身处理效率不高。

 

 

 

MyISAM:

早期的MyISAM是MySQL默认的引擎。现在已经被InnoDB取代。它支持全文索引,支持空间索引机制来索引结构化的数据。而且MyISAM在组织它的数据的时候,结构比较特殊,每一张表都对应了三个文件

       db.opt:定义数据库的选项及其信息

       *.frm:format格式的定义

       *MYD:My Data:数据保存位置

       *MYI:My Index:数据的索引存放位置

 

MyISAM的重要特性:

1.非事物

2.不支持外键约束

3.支持全文索引(能够支持全文索引的引擎只有MyISAM),当我们需要在InnoDB引擎中进行全文索引怎么办呢?利用其他的插件,比如Lucene(Java语言构架)或者Sphinx(C语言构架),都能对Mysql不管什么引擎,对其数据进行快速全文索引的功能。

4.无数据缓存:无法对数据进行缓存,但是对于服务器是可以缓存的。

5.只对索引进行缓存。索引缓存也被称为Key Buffer

6.支持HASH和BTREE索引,HASH一般只在特定场合使用,比如只在Age=30时使用。

7.表级别的锁

8.对于读来说,速度非常快,用于数据仓库是一个非常好的选择。

9.数据可以压缩存放

10.支持在线备份

11.支持最多64个索引(不过这已经足够了)

 

MyISAM的服务器变量:

key_buffer_size:索引缓存:用于定义MyISAM索引缓存大小,

使用SHOWVARIABLES LIKE ‘key_buffer_size’;来查看当前大小。

即便是我们没有使用MyISAM引擎,你也是要设置这个值的。默认是8M。

concurrent_insert:是否允许并发写入(锁),

如果想使用并发写入,无非就是实现了如果两个写入操作影响的不是相同数据的话,可以允许它并发写于,事先需要Mysql对其判断。而且并发写入要求表操作中存在数据间隙才能实现。

delay_key_write:推迟索引/键的写入操作:ON|OFF

索引是为了加速查询过程的,我们如果对表做一次修改,则索引就必须要重建了,而重建索引很浪费时间,浪费系统资源。而这个值,就是用于延迟索引写操作的,定义不是表一修改就立即进行索引重建。默认是开启的

max_write_lock_count:嗯……这个就不介绍了……可以参照官方文档

preload_buffer_size:用于定义刚启动的时候缓存大小的。默认是32KB

 

MyISAM的专用管理工具:

myisamchk:实现分析优化并修复MyISAM表的

myisampack:实现压缩MyISAM表的,当压缩完成之后就不能再修改了

myisam_ftdump:显示全文索引的

 

InnoDB:

具有以下几个特性:

1.支持事物,基于MVCC

2.支持行级别的锁

3.支持外键

4.支持聚簇索引。它是BTREE的一种属性,非聚簇索引,索引和数据是分开存放的,所以找到索引之后索引其实只是一个指针,而聚簇索引,它的索引和数据是在一起的,只要能找到索引则立刻能找到数据,它要比非聚簇索引少一次查找,速度非常快。所以当我们把索引读取到内存,则意味着把数据也读取到内存了。

5.同时支持索引缓存和数据缓存。意味着把用户查询的数据可以直接缓存下来。

6.支持在线非阻塞式的备份,支持热备:不过需要商业化的备份工具来实现

 

InnoDB的存放:

InnoDB不使用3个文件来保存,而是放在一个完整的表空间ib_data中来存放的。

       *.frm innoDB的定义文件

ib_log*:循环写入的两个文件通过“innodb_log_file_size”定义。

 

相关的服务器参数:

Innodb_data_home_dir:数据文件的存放目录。默认情况下这个跟data_dir是一样的。

Innodb_data_file_path:数据文件的路径(使用的是相对路径)

Innodb_file_per_table:每一个表的表文件路径

Innodb_buffer_pool_size:定义缓存数据和索引的空间大小,用于缓存数据和索引,这个空间一般要求比较大。

Innodb_flush_log_at_trx_commit:定义日志多长时间写到真正的数据文件中。这个选项有3个值,0,关闭,1,启用,2,按需

innodb_log_file_size:事物性日志文件大小(记录的是原子操作),是个循环日志,这个文件特别关键,Innodb的数据文件崩溃的话,自我修复要靠这两个文件。这是个重做日志。这个文件可以定义的稍微大一点。理想值为128-256M之间

 

存储引擎的状态:SHOWENGINE INNODB STATUS;

Semaphores:引擎级别的属性和信息

Foreign Keyerrors:外键错误信息

Deadlocks:死锁信息。Mysql是可以自动解除死锁的

Transactions:事物相关的信息

File I/O:文件I/O相关信息

Inser buffer andadaptive hash index:索引和Hash索引的信息

log:日志信息

Buffer poll andmemory:缓存相关信息

Row operations:行操作相关信息

 

 

 


用户管理

通过mysql元数据库中的六张表,来定义权限指派。一共有6个重要的文件

user,db,host,tables_priv,columns_priv,procs_priv

 

       user:其中最重要的信息,3个字段:用户,来自哪个主机,密码是什么:user,host,password,password中的值则是加密存储的。

       tables_priv:定义表级别的权限的,哪个表哪个用户是否可以执行SELECT等DML语句的。

       columns_priv:更细力度的权限控制,不光定义某个表,也能定义某个字段上的权限

       procs_priv:定义用户是否具有执行存储过程权限的。

 

这六张表可以称之为MySQL的授权信息表。

 

对于Mysql数据,可以授权的权限种类:

1.SELECT,INSERT,UPDATE,DELETE等DML相关的语句。

2.CREATE(仅仅指创建数据库和表的权限) DROP:等DDL语句

3.INDEX :索引的权限

4.ALTER :定义用户是否可以修改表结构

5.SHOW DATABASES;用户是否具有查看本服务器上具有访问权限的数据库的权限

6.SUPER:执行管理命令的权限

7.LOCK TABLES:锁权限

8:CREATE VIEW,SHOW VIEW 创建和查看视图的权限

9:CREATE USER 创建和删除用户的权限

10:  REPLICATION SLAVE, 主要用于复制的权限,是否具有从主服务器上复制二进制日志内容的权限

REPLICATION CLIENT:主要用于复制的权限,请求主从环境当中用于复制的相关信息的权限。

11:RELOAD:使用重启服务器和重读的权限

12.SHUTDOWN:关闭服务的权限

13.SUPER: 实现让用户实现管理服务器进程的权限

14:GRANT OPTION:当你把一个数据库的权限授予一个用户之后,这个用户是否能够再将权限授予他人?

 

 

如何去创建一个用户账号:

CREATE USER user_name@’HOST’IDENTIFIED BY ‘PASSWORD’

指定主机的时候%表示任意主机

比如,创建用户jerry并指定密码123456

CREATE USER jerry@’%’IDENTIFIED BY ‘123456’

FLUSH PRIVILEGES;

 

当我们创建一个用户之后,这个用户仅仅只有,连接到数据库上,并使用SHOW DATABASES的权限

在主机名和指定的IP地址上我们可以使用通配符

比如:

192.168.0.%

192.168.0.0/255.255.255.0

 

删除一个用户:

格式:DROP USER username@host

 

如何给用户授权:

格式:GRANT priv_type ON 数据库对象 [权限级别] TO 用户 [并修改其密码] [设定此用户将获得的权限继续授权]

 

1.给jerry用户添加各项权限

授予jerry具有执行创建数据库的权限:

GRANT CREATE ON *.* TO ‘jerry’@’%’;

FLUSH PRIVILEGES;

当授予CREATE权限之后,如果想DROP,则没有权限

授权DROP:

GRANT DROP ON *.* TO ‘jerry‘@’%’;

FLUSH PRIVILEGES;

授予查询权限

GRANT SELECT ON *.* TO ‘jerry’@’%’;

FLUSH PRIVILEGES;

 

2.授予所有的对与hellodb数据库的权限:

GRANT ALL PRIVILEGES ON hellodb.* TO ‘jerry’@‘%‘;

 

       3.授予权限并修改密码:

GRANT priv_list ON db.table TO user@host [IDENTIFIED BY ‘password’]

 

       对象类型:

       TABLE 默认就是TABLES

       FUNCTION 存储函数

       PROCEDURE存储过程

 

       权限级别:

       *

       *.*

       db_name

       db_name.tbl.name

       tbl_name

       db_name.routine_name

 

当我们再次授予权限的时候,它会附加权限,而不会覆盖原有权限

      权限中的“USAGE”就是允许用户连接到服务器上,

 

 

查看一个人的权限:

SHOW GRANTS FOR ‘root’@’localhost’

SHOW GRANTS FOR ‘jerry’@’%’

 

收回权限:

REVOKE [权限类型] [指定对象] [哪个用户];

1.比如收回jerry的SELECT 权限:

REVOKE SELECT ON hellodb.* FROM ‘jerry’@‘%‘;

FLUSH PRIVILEGES;

 

 

限定用户所使用的资源:

这种限定不能立即生效,想要立即生效,要使用:

FLUSH USER_RESOURCES;

       常用的限定资源:

WITH GRANT OPTION:设置用户可以向下授权

WITH MAX_QUERIES_PER_HOUR n; :限定用户每个小时最多执行N次查询

WITH MAX_UPDATES_PER_HOUR n:限定用户每个小时最多执行几次修改操作。

WITH MAX_CONNECTIONS_PER_HOUR n:限定用户一个小时最多连入多少次。

WITH MAX_USER_CONNECTIONS n:限定使用同一个用户账号在同一时间最多能同时连进来几次。

 

 

 

字段级别权限的限定:

常用的字段级别的限定只有SELECT 和UPDATE;

比如:授予查询字段的权限

GRANT SELECT(name) ON db.table TO user@host

授予用户具有执行存储例程的权限:

GRANT EXECUTE ON [对象类型] TO user@host

 

MySQL数据库的密码忘了怎么办?

1.重新初始化mysql数据库

显然,这不是我们想要的。

2.启用1级别,跳过密码,然后进去重新设定密码。

我们需要先重启服务,在重启服务器的时候需要用一个特殊的选项来启动服务器:

首先我们要知道我们启动mysqld的时候,启动的其实是/$USERBIN/mysqld_safe这个服务。($USERBIN)

而我们现在需要用这个外加一些参数,并让他在后台运行

/usr/local/mysql/bin/mysqld_safe –skip-grant-tables –skip-networking &

在这种情况下,要给用户设密码,必须使用修改表的形式来完成,则:

使用mysql命令进入mysql,此时是不需要密码的。

USE mysql

UPDATA user SET Password=PASSWORD(‘redhat’) WHERE User=’root’ AND Host=’localhost’;

UPDATA user SET Password=PASSWORD(‘redhat’) WHERE User=’root’ AND Host=’127.0.0.1’;

 

明天休息一天,后天继续,后天的内容更加重要,Mysql的备份和数据的复制!

标签:, ,

返回正文

您的留言是我最大的支持!