MariaDB四之锁、存储引擎、隔离级别、事务、用户及权限管理、日志管理-阿里云开发者社区

开发者社区> 数据库> 正文

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

简介:

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

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

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

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

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

   parser的功能:词法,语法,语意分析,优化

   explain:分析工具

   query cache 缓存,只保存select查询

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

一、MariaDB锁

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

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

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

     锁粒度

       表锁:table lock   锁定整张表

       行锁:row lock 锁定需要的行

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

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

     锁的实现位置:

         数据库锁:可以手动使用,可以使用显示锁

         存储引擎锁:自动进行的锁(隐式锁)

     显示锁:

         施加锁

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

         锁的类型:READ:WRITE

         解锁:

1
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

   控制回滚的位置          

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

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

1
2
3
4
5
6
7
8
9
10
11
12
查看事务状态
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的事务隔离级别

1
2
3
4
5
6
7
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的存储引擎

1
2
3
4
5
6
7
8
9
10
11
12
13
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

   查看表的默认选项

1
SHOW TABLE STATUS [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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    

      创建用户

1
CREATE USER user@hostname [ IDENTIFIED BY [PASSWORD] 'password']

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

      查看用户能够使用的权限

1
2
3
4
5
6
7
8
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)

      修改用户名


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

  grant

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

     管理类权限

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

       数据库访问权限

1
2
3
4
5
6
7
8
9
10
11
alter
alter routine 修改存储历程
create
create rontine 创建存储过程,存储函数
create view
delete
drop
execute
grant option 将自己的权限复制给别的用户
index 索引
show view

     数据库操作类权限(表级别)

1
2
3
4
select
insert
update
delete

     字段级别:

1
2
3
select (col1,....)
uodate (col1,....)
insert (col1,....)
1
2
3
4
5
6
7
8
9
10
11
12
13
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 使用同一账号可以同时连接的次数

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

     收回授权

1
2
3
4
5
6
7
8
9
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 是否启用查询日志

        general_log_file 定义了一般查询日志保存的文      

      慢查询日志:查询执行时长超过指定时长的查询,即为慢查询

       slow_query_log = {ON|OFF} 是否记录慢查询日志

       log_output={TABLE|FILE|NONE}

       slow_query_log_file 定义慢查询日志的保存文件

       long_query_time 定义慢查询的时间

       long_slow_filter 不记录慢查询日志

      错误日志:

         服务器启动和关闭过程中的信息

         服务器运行中的错误信息

         事件调度器运行一个事件时产生的信息

         在复制架构中的从服务器上启动从服务器线程是产生的信息

         log_error = /path/to/log/error_log_file

         log_warnings = {1|0} 是否将警告信息保存至错误日志中

      二进制日志:修改相关的操作,用来实现复制的凭据

         记录了当前服务器的数据修改和有潜在可能性影响数据修改的语句

         默认在数据目录下,通常情况下为mysql-bin

         二进制文件可以通过mysqlbinlog来查看

         时间记录:time

         偏移位置postion

         show master status可以查看当前使用的二进制文件和下一个时间开始是的位置

         show binary logs 可以查看当前正在使用的log日志

         日志滚动:当超过1G,日志会滚动,日志的滚动可以按照大小定义滚动,按照时间来定义滚动,执行flush logs来滚动日志

      中继日志:从服务器上的日志

          对于非从服务器 中继日志没有启用

           relay_log_purge = {ON|OFF} 是否自动清理不在需要的中继日志

           relay_log_space_limit 中继大小是否限制

      事务日志:将随机I/O转换为顺序I/O

         日志文件组:至少要存在两个,实现轮询

         注意:尽可能使用小事务来代替大事务来提升事务引擎的性

     清除日志:

      PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr(某个时间点之前) }

     查看MariaDB的日志

1
2
3
4
5
6
7
8
MariaDB [(none)]> show binlog events\G
*************************** 1. row ***************************
   Log_name: master-bin.000001
        Pos: 4
 Event_type: Format_desc
  Server_id(服务器身份标识): 1
End_log_pos: 245
       Info: Server ver: 5.5.36-MariaDB-log, Binlog ver: 4

      MariaDB 记录二进制日志的格式

         基于语句:statement

         基于行:row

         混合模式:mixed

      指定从那个位置开始读取

1
2
3
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos]
也可以通过
mysqlbinlog --start-position= #

     mysqlbinlog常用命令总结:

       -j, --start-position=# 起始节点

           --stop-position=# 结束节点

      --start-datetime=name 起始时间

      --stop-datetime=name 结束时间

     二进制日志文件内容格式

           时间发生的日期和时间

           服务器ID

           事件结束位置

           事件的类型

           原服务器生成此事件时的线程ID号

           语句时间戳和写入二进制文件的时间差,单位为秒

           错误代码,0表示正常执行

           事件内容

           事件位置(相当于下一个时间的开始位置)

       log_bin = {ON|OFF},也可以是一个文件路径

       log_bin_trust_function_creators 不阻止任何存储函数

       sql_log_bin = {ON|OFF} 当前会话是否将修改记入到二进制文件中

       sql_log_off 是否将一般查询记入到查询日志中

       sync_binlog 同步缓冲区二进制到应到的时间,0表示不急于时间同步,旨在时间提交时同步

       binlog_format={statement|row|mixed}

       max_binlog_cache_size MariaDB二进制日志的缓存区大小,仅用于缓存事务类的语句

       max_binlog_stmt_cache_size 语句缓存区大小,即事务类和非事务类公用的大小 

       max_binlog_size 二进制日志文件的上限,单位为字节

       建议:切勿将二进制日志与数据文件放在同一设备上

       可以临时通过sql_log_bin来控制未禁止的写入


本文转自wangfeng7399 51CTO博客,原文链接:http://blog.51cto.com/wangfeng7399/1393720,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章