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的备份和数据的复制！