MariaDB系列之基础

Posted by Mathew on 2016-09-17

MariaDB is one of the most popular database servers in the world. It’s made by the original developers of MySQL and guaranteed to stay open source. Notable users include Wikipedia, Facebook and Google.

什么是MariaDB数据库

  通常意义上,数据库就是数据的集合,具体到计算机上数据库可以是存储器上一些文件的集合或者一些内存数据的集合。
  我们通常说的Mariadb(Mysql)数据库,SQL Server数据库等等其实是数据库管理系统,它们可以存储数据,并提供查询和更新数据库中的数据的功能等等。根据数据库  如何存储数据和如何操作数据的实现机制不同,这些数据库之间即有区别又有共同点。

  Mariadb数据库是开放源代码的关系型数据库。目前,它可以提供的功能有:支持sql语言、子查询、存储过程、触发器、视图、索引、事务、锁、外键约束和影像复制等。在后期,我们会详细讲解这些功能。

  同Oracle 和SQL Server等大型数据库系统一样,Mariadb也是客户/服务器系统并且是单进程多线程架构的数据库。

  Mariadb区别于其它数据库系统的一个重要特点是支持插件式存储引擎。

那么什么又是存储引擎呢?

   存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系型数据库中数据的存储是以表的形式存储的,所以存储引擎也可以称为表类型(即存储和操作此表的类型)。

   在Oracle和SQL Server等数据库中只有一种存储引擎,所以数据存储管理机制都是一样的。而Mariadb数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎,用户可以根据自己的需要编写自己的存储引擎。

MariaDB中支持哪些存储引擎呢?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
MariaDB [(none)]> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| FEDERATED | YES | FederatedX pluggable storage engine | YES | NO | YES |
| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+

InnoDB

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
处理大量的短期事务;
数据存储于“表空间(table space)”中;
(1) 所有InnoDB表的数据和索引放置于同一个表空间中;
表空间文件:datadir定义的目录下
数据文件:ibddata1, ibddata2, ...
(2) 每个表单独使用一个表空间存储表的数据和索引;
innodb_file_per_table=ON

数据文件(存储数据和索引):tbl_name.ibd,
表格式定义:tbl_name.frm

基于MVCC来支持高并发,支持所有的四个隔离级别,默认级别为REPEATABLE READ; 间隙锁防止幻读;
使用聚集索引
支持“自适应hash索引”
锁粒度:行级锁

MariaDB (XtraDB (percona))


数据存储:表空间
并发:MVCC, 间隙锁
索引:聚集索引、辅助索引
性能:预计操作、自适应hash、插入缓存区
备份:支持热备(xtrabacup)

MyISAM

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
支持全文索引(FULLTEXT index)、压缩、空间函数(GIS); 但不支持事务,且为表级锁;
崩溃后无法安全恢复

适用场景:只读(或者写较少)、表较小(可以接受长时间进行修复操作)
Ariacrash-safe

文件:
tbl_name.frm: 表格式定义
tbl_name.MYD: 数据文件
tbl_name.MYI: 索引文件

特性:
加锁和并发:表级锁
修复:手工或自动修复、但可能丢失数据
索引:非聚集索引
延迟更新索引键:
压缩表

行格式:dynamic, fixed, compressed, compact, redundent

其他存储引擎

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CSV:将普通的CSV(字段通过逗号分隔)作为MySQL表使用;
MRG_MYISAM:将多个MyISAM表合并成为一个虚拟表;
BLACKHOLE:类似于/dev/null,不真正存储任何数据;
MEMORY:所有数据都保存于内存中,内存表;支持hash索引;表级锁;
临时表
PERFORMANCE_SCHEMA:伪存储引擎;
ARCHIVE:只支持SELECT和INSERT操作;支持行级锁和专用缓存区;
FEDERATED:用于访问其它远程MySQL服务器一个代理,它通过创建一个到远程MySQL服务器的客户端连接,并将查询传输到远程服务器执行,而后完成数据存取;
MariaDB的上实现是FederatedX

MariaDB支持的其它存储引擎:
OQGraph
SphinxSE
TokuDB
Cassandra
CONNECT
SQUENCE

MariaDB中关于存储引擎的操作

查看数据库可以支持的存储引擎

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
MariaDB [(none)]> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| FEDERATED | YES | FederatedX pluggable storage engine | YES | NO | YES |
| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+

显示创建表的创建语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
MariaDB [hellodb]> SHOW TABLES;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
MariaDB [hellodb]> SHOW CREATE TABLE classes;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| classes | CREATE TABLE `classes` (
`ClassID` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`Class` varchar(100) DEFAULT NULL,
`NumOfStu` smallint(5) unsigned DEFAULT NULL,
PRIMARY KEY (`ClassID`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 | ### 创建该表时候的存储引擎为InnoDB
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

显示表的当前状态值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
MariaDB [hellodb]> SHOW TABLE STATUS LIKE 'classes'\G
*************************** 1. row ***************************
Name: classes
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 8
Avg_row_length: 2048
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 9437184
Auto_increment: 9
Create_time: 2016-09-12 12:39:27
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)

MariaDB [hellodb]>

MariaDB事务

MySQL 事务主要用于处理操作量大,复杂度高的数据。比如说,在人员管理系统中,你删除一个人员,你即需要删除人员的基本资料,也要删除和该人员相关的信息,如信箱,文章等等,这样,这些数据库操作语句就构成一个事务!

· 在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务
· 事务处理可以用来维护数据库的完整性,保证成批的SQL语句要么全部执行,要么全部不执行
· 事务用来管理insert,update,delete语句

一般来说,事务是必须满足4个条件(ACID): Atomicity(原子性)、Consistency(稳定性)、Isolation(隔离性)、Durability(可靠性)

· 事务的原子性:一组事务,要么成功;要么撤回。
· 稳定性 : 有非法数据(外键约束之类),事务撤回。
· 隔离性:事务独立运行。一个事务处理后的结果,影响了其他事务,那么其他事务会撤回。事务的100%隔离,需要牺牲速度。
· 可靠性:软、硬件崩溃后,InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得, innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里。

事务控制

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
启动事务
START TRANSACTION
...
...
结束事务
(1) COMMIT: 提交
(2) ROLLBACK: 回滚

事务支持savepoint
SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier

事务隔离级别
READ UNCOMMITTED (读未提交)
READ COMMITTED (读提交)
REPEATABLE READ (可重读)
SERIALIZABILE (可串行化)

事务隔离级别可在此设置:tx_isolation:服务器变量,默认为REPEATABLE-READ;可在SESSION级进行修改;

SET tx_isolation=''
READ-UNCOMMITTED
READ-COMMITTED
REPEATABLE-READ
SERIALIZABLE

死锁:

		两个或多个事务在同一资源相互占用,并请求锁定对方占用的资源的状态;

事务日志:

1
2
3
4
5
事务日志的写入类型为“追加”,因此其操作为“顺序IO”;此日志通常也被称为“预写式日志(write ahead logging)”;

innodb_log_file_size
innodb_log_files_in_group
innodb_log_group_home_dir

MariaDB日志

查询日志:query log

1
2
3
4
5
6
7
记录查询操作:
文件:file
表:table

general_log=ON|OFF
general_log_file=HOSTNAME.log
log_output=TABLE|FILE|NONE

慢查询日志

1
2
3
4
5
6
7
8
9
10
11
SHOW GLOBAL VARIABLES LIKE 'long_query_time';
SELECT @@GLOBAL.long_query_time;
SET GLOBAL long_query_time=

slow_query_log=ON|OFF
slow_query_log_file=HOSTNAME-slow.log

log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
log_slow_queries = OFF
log_slow_rate_limit = 1
log_slow_verbosity

错误日志

	mysqld启动和关闭过程中输出的事件信息;
	mysqld运行中产生的错误信息;
	event scheduler运行一个event时产生的日志信息;
	在主从复制架构中的从服务器上启动从服务器线程时产生的信息;


	log_error=/PATH/TO/LOG_ERROR_FILE
	log_warnings=1|0: 是否记录警告信息至错误日志文件中;

二进制日志

	记录导致数据改变或潜在导致数据改变的SQL语句;
	功能:用于通过“重放”日志文件中的事件来生成数据副本;
1
2
3
SHOW {BINARY | MASTER} LOGS:查看mariadb自行管理使用中的二进制日志文件列表;
SHOW MASTER STATUS:查看使用中的二进制日志文件;
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

二进制日志记录格式:

		基于“语句”记录:statement
		基于“行”记录:row
		混合模式:mixed, 让系统自行判定该基于哪种方式进行;

二进制日志文件的构成:

		两类文件
			日志文件:mysql-bin.文件名后缀,二进制格式
			索引文件:mysql-bin.index,文本格式

服务器变量:

1
2
3
4
5
6
7
sql_log_bin=ON|OFF:是否记录二进制日志;
log_bin=/PATH/TO/BIN_LOG_FILE:记录的文件位置;通常为ON;
binlog_format=STATEMENT|ROW|MIXED:二进制日志记录的格式;
max_binlog_size=1073741824:单个二进制日志文件的最大体积,默认为1G;
注意:(1) 到达最大值会自动滚动;(2) 文件达到上限时的大小未必为指定的精确值;

sync_binlog=1|0:设定是否启动二进制日志同步功能;

mysqlbinlog:客户端命令工具

1
2
3
4
5
6
7
mysqlbinlog [OPTIONS] log_file
--start-position
--stop-position

--start-datetime=
--stop-datetime=
YYYY-MM-DD hh:mm:ss

二进制日志事件的格式:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# at 328
#151105 16:31:40 server id 1 end_log_pos 431 Query thread_id=1 exec_time=0 error_code=0
use `mydb`/*!*/;
SET TIMESTAMP=1446712300/*!*/;
CREATE TABLE tb1 (id int, name char(30))
/*!*/;

事件发生的日期和时间:151105 16:31:40
事件发生的服务器标识:server id 1
事件的结束位置:end_log_pos 431
事件的类型:Query
事件发生时所在服务器执行此事件的线程的ID:thread_id=1
语句的时间戳与将其写入二进制文件中的时间差:exec_time=0
错误代码:error_code=0
事件内容:

GTID:Global Transaction ID;

			专属属性:GTID

中继日志

	复制架构中,从服务器用于保存从主服务器的二进制日志中读取到的事件;

事务日志

	事务型存储引擎自行管理和使用;

	redo log
	undo log