MariaDB四之锁、存储引擎、隔离级别、事务、用户及权限管理、日志管理

浏览数：31 / 时间：2015年06月12日

本文主要讲述MariaDB的锁机制、存储引擎、隔离级别、事务、用户及权限管理、日志管理等相关知识点

连接池与用户请求建立连接

核心功能层查询解析，分析，优化，内置函数，跨存储引擎的功能

存储引擎层数据的存入和提取

mysql的发送默认为明文，可以使用ssl加密数据

parser的功能：词法，语法，语意分析，优化

explain：分析工具

query cache 缓存，只保存select查询

并发访问控制：基于锁来实现

一、MariaDB锁

执行操作时施加的锁的模式

读锁：用户在读的时候施加的锁，为防止别人修改，当时用户可以读，还被称为共享锁

写锁：独占锁，排它锁，其他用户既不可以读，也不可以写

锁粒度

表锁：table lock 锁定整张表

行锁：row lock 锁定需要的行

粒度越小，开销越大，但并发性越好

粒度越大，开销越小，但并发性越差

锁的实现位置：

数据库锁：可以手动使用，可以使用显示锁

存储引擎锁：自动进行的锁（隐式锁）

显示锁：

施加锁

LOCK TABLEStbl_name [[AS] alias] lock_type, tbl_name [[AS] alias] lock_type] ...

锁的类型：READ:WRITE

解锁：

UNLOCK TABLES

InnoDB存储引擎也支持另外一种显示锁（锁定挑选出的部分行，行级锁）

select .....lock in share mode

select .....for update

建议：做备份时要手动施加读锁

二、事务

事务就是一组原子性的查询语句，也即将多个查询当作一个独立的工作单元

ACID测试：能够满足ACID测试就表示其支持事务，或兼容事务

A：Atomicity,原子性，都执行或者都不执行

C：Consistency，一致性，从一个一致性状态转到另外一个一致性状态

I: Isolaction,隔离性，一个事务的修改操作在提交前对其他事物是不可见的

D：Durability,持久性，一旦事务得到提交，其所做的修改会永久有效

隔离级别

READ UNCOMMITTEND 读未提交，会出现脏读，不可重复读，幻读

READ COMMITTEND 读提交，会出现不可重复读，幻读

REPEATABLE READ 可重读，会出现幻读

SERIALIZABLE 可串行化，强制事务的串行执行避免了幻读，性能较低

安全级别越高，并发性越低

启动事务

stat transaction

事务提交

commit

事务回滚

rollback

控制回滚的位置

SAVEPOINT identifier ROLLBACK [WORK] TO [SAVEPOINT] identifier RELEASE SAVEPOINT identifier

如何没有显示启动事务，每个语句都会当成一个默认的事务，其执行完成会被指定提交，

查看事务状态
MariaDB [(none)]> select @@global.autocommit
    -> ;
+---------------------+
| @@global.autocommit |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.00 sec)
修改事务的状态
MariaDB [(none)]> set global autocommit =0;
Query OK, 0 rows affected (0.00 sec)

注意关闭自动提交，请记得手动启动事务，应记得手动提交事务

查看MariaDB的事务隔离级别

MariaDB [(none)]> show global variables like ‘%iso%‘;
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

建议：在对事物要求不特别严格的场景下，可以使用读提交

MVCC：多版本并发控制

每个事物启动时，InnoDB会为每个启动的食物提供一个当下时刻的快照

为了实现此功能，InnoDB会为每个表提供两隐藏的字段，一个用于保存行的创建时间，一个用于保存行的失效时间，里面存储的为系统版本号

旨在两个隔离级别下有效：read committed 和repeatble-read

三、存储引擎

存储引擎也通常被称为“表类型”

查看MariaDB的存储引擎

MariaDB [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| CSV                | YES     | CSV storage engine                                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                         | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears)             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                                      | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                  | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                                      | NO           | NO   | NO         |
| Aria               | YES     | Crash-safe tables with MyISAM heritage                                     | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+---

可以看到，默认的为InnoDB

查看表的默认选项

SHOW TABLE STATUS [{FROM | IN} db_name] [LIKE ‘pattern‘ | WHERE expr]

MariaDB [(none)]> show table status in mysql\G
*************************** 1. row ***************************
           Name（表名）: columns_priv
         Engine（存储引擎）: MyISAM
        Version（存储引擎版本）: 10
     Row_format（行格式）: Fixed {default|dynamic|fixed|commpressed|redundant|compact}
           Rows(表中的行数): 0
 Avg_row_length（平均每行包含的字节数）: 0
    Data_length（表中数据总体大小，单位为字节）: 0
Max_data_length（表能够占用的最大空间，单位为字节，0表示没有上限）: 227994731135631359
   Index_length（索引的大小，单位为字节）: 4096
      Data_free（对MyISAM表，其表示已经分配但尚未使用的空间，其中包含此前删除行之后腾出来的空间）: 0
 Auto_increment（下一个Auto_increment值）: NULL
    Create_time（表的创建时间）: 2014-03-22 20:28:26
    Update_time（表的更新时间）: 2014-03-22 20:28:26
     Check_time（使用check table或myisamchk最近一次检查表的时间）: NULL
      Collation（排序规则）: utf8_bin
       Checksum（如果启动，则为表的checksum）: NULL
 Create_options（创建表时指定使用的其他选项）:
        Comment（表的注释信息）: Column privileges

InnoDB有两种格式

1.innodb_file_per_table=OFF，即使用共享表空间

每个表有一个独立的格式定义文件：tb_name.frm

还有一个默认位于数据目录下的共享的表空间文件：ibdata#

2.innodb_file_per_table=ON，即使用独立表空间

每个表再数据库目录下存储两个文件：tb_name.frm 和tb_name.idb

MyISAM:

每个表都在数据库目录下存储三个文件

tb_name.frm

tb_name.MYD

tb_name.MYI

表空间：table space，由InnoDB管理的特有格式数据文件，内部可用是存储数据和索引

修改默认存储引擎：可以通过修改default_storage_engine变量来实现

各存储引擎的特性

InnoDB：

支持事务，有事务日志（ib_logfile0,ib_logfile1）

支持外键约束

支持MVCC

支持聚簇索引（聚簇索引之外的其他索引，通常称之为辅助索引）

行级锁：间歇锁

支持使用辅助索引

支持自使用hash索引

支持热备份

MyISAM:

全文索引

支持表压缩存放：做数据仓库时，能节约存储空间并提升性能

支持空间索引

表记锁

延迟更新索引

不支持事务、外键和行级锁

崩溃后无法安全恢复数据

使用场景：只读数据，表较小，能够容忍崩溃后的恢复操作和数据丢失

ARCHIVE：

仅支持INSERT和SELECT，支持很好的压缩功能

应用于存储日志信息，或其他按照时间序列实现的数据采集类的应用

CSV：

将数据存储为CSV格式，不支持索引，仅使用与数据交换场景

BLACHOLE：

没有存储机制，任何发往此引擎的数据都会丢弃，其会记录而二进制日志，因此，常用于多级复制架构中作为中转服务器

MEMORY:

保存数据在内存中，内存表；常用于保存中间数据，如周期性的聚合数据等，也用于实现临时表

支持hash索引，使用表记锁，不支持BLOB和TEXT数据类型

MRG_MYISAM：

是MYISAM的一个变种，能够将多个MyISAM表合并成一个虚表

NDB：

是MySQL CLUSTER中专用的存储引擎

第三方的存储引擎

XtraDB：

增强的InnoDB，有Percona提供，编译安装时，下载XtraDB的源码替代Mysql存储引擎中的InnoDB的源码,MariaDB默认的使用就是为XtraDB

PBXT：

MariaDB自带此存储引擎，支持引擎级别的复制，外键约束，对SSD磁盘提供适当支持

支持事务，MCVV

TokuDB：

使用Fractal Trees索引，使用存储大数据，拥有很好的压缩比，已经被引入MariaDB

列式数据存储引擎

Infobright：目前较有名的列式引擎，适用于海量数据存储场景，如PB级别，专为数据分析和数据仓库设计

InfiniDB

MonetDB

LucidDB

开源社区存储引擎

Aira：前身为Maria，是增强帮的MyISAM（支持崩溃后安全恢复，支持数据缓存）

Groona：全文索引引擎

Mroonaga：是基于Groona的二次开发版

OQGraph：由Open Query研发，支持图（网状）结构的存储引擎

Spider：能够将数据切分成不同的分片，比较高效透明的实现了分片（shared），并支持在分片上并行查询

如何选择存储引擎：

是否需要事务

备份的类型的支持

崩溃后的恢复

特有的特性

四、用户及权限管理

用户账号：username@hostname

用户账号管理

create user

drop user

rename user

set password

权限管理

grant

revoke

创建用户

CREATE USER user@hostname [ IDENTIFIED BY [PASSWORD] ‘password‘]

主机可以使用通配符%和_

查看用户能够使用的权限

MariaDB [(none)]> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO ‘root‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*1D7AA67FFCC64F5D65BC7FA9C21838AF31FE63C1‘ WITH GRANT OPTION |
| GRANT PROXY ON ‘‘@‘‘ TO ‘root‘@‘localhost‘ WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

修改用户名

RENAME USER old_user TO new_user [, old_user TO new_user] ...

grant

MariaDB的权限类型分为库级别、表级别、字段级别、管理类、程序类

管理类权限

create temporary tables 临时表
create user file 允许用户读或者写某些文件
lock tables 添加显示锁
process 查看用户的线程
reload 相当于执行flush和reset
replication client 查询有哪些客户端有复制权限
replication slave 赋予用户复制权限
show databases
shutdown
super

数据库访问权限

alter
alter routine 修改存储历程
create
create rontine 创建存储过程，存储函数
create view
delete
drop
execute
grant option 将自己的权限复制给别的用户
index 索引
show view

数据库操作类权限（表级别）

select
insert
update
delete

字段级别：

select (col1,....)
uodate (col1,....)
insert (col1,....)

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_specification [, user_specification] ...
    [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
    [WITH with_option ...]
with_option:
    GRANT OPTION 转赠给别人
  | MAX_QUERIES_PER_HOUR count 每小时允许执行的最大查询次数
  | MAX_UPDATES_PER_HOUR count 每小时允许执行的最大更新次数
  | MAX_CONNECTIONS_PER_HOUR count 每小时允许执行的最大连接次数
  | MAX_USER_CONNECTIONS count 使用同一账号可以同时连接的次数

切记：如果想要创建库，应该对所有表都有权限

收回授权

REVOKE
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user [, user] ...
REVOKE ALL PRIVILEGES, GRANT OPTION
    FROM user [, user] ...
REVOKE PROXY ON user
    FROM user [, user] ..

几个跟用户授权相关的表

db: 库级别的权限

host：主机级别权限，已废弃

tables_priv: 表级别的授权

colomns_priv: 列级别的授权

procs_priv：存储过程和存储函数相关的授权

proxies_priv: 代理用户权限

五、MariaDB的日志

查询日志：默认为关闭

log = {ON|OFF} 是否记录所有语句的日志信息与一般查询日志文件（general_log_file）

log_output = {TABLE|FILE|NONE} 记录日志的类型,table和file可以同时出现，用逗号分隔

general_log 是否启用查询日志