天天看点
返回

MySQL（三）之关系型数据库基本特性

一、目前流行的开源RRDBMS

  • WebScaleSQL

   2015年1月20日，Facebook宣布阿里巴巴旗下的阿里云RDS团队正式加入WebScaleSQL。WebScaleSQL是Facebook、 Google、Twitter和Linkedin四家公司的MySQL团队发起的MySQL开源组织，旨在改进MySQL在规模和性能等方面的问题。WebScaleSQL是基于MySQL 5.6 社区版本改编的MySQL通用分支，基于GPL开源协议发布。WebScaleSQL目前已经做了很多性能改进工作，包括：客户端异步协调、逻辑预读、查询限流、服务端线程池优化、InnoDB大页支持等等。由于我们的分支上本身有一些定制化的需求，因此不会直接使用WebScaleSQL分支提供线上服务，但是这些改进对于我们都是很感兴趣的，好的特性会被吸收进来。因为我们有各种各样应用场景的用户，对MySQL本身的要求也比较高。比如大并发连接的用户，就需要线程池；存大量历史数据的用户，就要求高的压缩比等等。通过这可以看出虽然WebScaleSQL诞生时间较晚，但是其维护力量可以说是很强大的，最后用一句话概括：这款数据库就是专门为web服务设计的数据库。

  • PostgreSQL

   PostgreSQL是以加州大学伯克利分校计算机系开发的 POSTGRES，现在已经更名为PostgreSQL，版本 4.2为基础的对象关系型数据库管理系统（ORDBMS）。PostgreSQL支持大部分 SQL标准并且提供了许多其他现代特性：复杂查询、外键、触发器、视图、事务完整性、MVCC。同样，PostgreSQL 可以用许多方法扩展，比如， 通过增加新的数据类型、函数、操作符、聚集函数、索引。免费使用、修改、和分发 PostgreSQL，不管是私用、商用、还是学术研究使用。

  • SQLite 

   SQLite，是一款轻型的数据库，是遵守ACID的关系型数据库管理系统，它包含在一个相对小的C库中。它是D.RichardHipp建立的公有领域项目。它的设计目标是嵌入式的，而且目前已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。它能够支持Windows/Linux/Unix等等主流的操作系统，同时能够跟很多程序语言相结合，比如 Tcl、C#、PHP、Java等，还有ODBC接口，同样比起Mysql、PostgreSQL这两款开源的世界著名数据库管理系统来讲，它的处理速度比他们都快。SQLite第一个Alpha版本诞生于2000年5月。 至2015年已经有15个年头，SQLite也迎来了一个版本 SQLite 3已经发布。

  • MariaDB

   MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可 MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。在存储引擎方面，使用XtraDB（英语：XtraDB）来代替MySQL的InnoDB。 MariaDB由MySQL的创始人Michael Widenius（英语：Michael Widenius）主导开发，他早前曾以10亿美元的价格，将自己创建的公司MySQL AB卖给了SUN，此后，随着SUN被甲骨文收购，MySQL的所有权也落入Oracle的手中。MariaDB名称来自Michael Widenius的女儿Maria的名字。

   MariaDB基于事务的Maria存储引擎，替换了MySQL的MyISAM存储引擎，它使用了Percona的 XtraDB，InnoDB的变体，分支的开发者希望提供访问即将到来的MySQL 5.4 InnoDB性能。这个版本还包括了 PrimeBase XT (PBXT) 和 FederatedX存储引擎。

   说在最后，目前由于MySQL是属于Oracle公司的一款产品，而Oracle有将MySQL闭源的潜在风险，现在有很多大公司已经弃用MySQL，而MariaDB就是由MySQL之父Michael领头设计出来的一款开源数据库。确切来讲MariaDB是MySQL的分支版，而且整合了开源社区的很多东西，所以这款产品可以说就是MySQL的替代产品。由于其完全兼容MySQL所以今后演示就用MariaDB进行演示，而所有理论上的概念还是以MySQL为基础。

二、MySQL基本概念

1、语句结构

  • 数据定义语言（DDL：Data Definition Language）： 

   其语句包括动词CREATE和DROP。在数据库中创建新表或删除表（CREAT TABLE 或 DROP TABLE）；为表加入索引等。DDL包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。

  • 数据操作语言（DML：Data Manipulation Language）： 

   其语句包括动词INSERT，UPDATE和DELETE。它们分别用于添加，修改和删除表中的行。也可以称为动作查询语言，使用SELECT语句。

  • 数据控制语言（DCL：Data Control Language）： 

   它的语句通过GRANT或REVOKE获得许可，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。

2、ACID特性

   ACID，指数据库事务正确执行的四个基本要素的缩写。包含：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。一个支持事务（Transaction）的，必需要具有这四种特性，否则在事务过程（Transaction processing）当中无法保证数据的正确性，交易过程极可能达不到交易方的要求。

  • 原子性：整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
  • 一致性：在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。具体来说就是，比如表与表之间存在外键约束关系，那么你对数据库进行的修改操作就必需要满足约束条件，即如果你修改了一张表中的数据，那你还需要修改与之存在外键约束关系的其他表中对应的数据，以达到一致性。
  • 隔离性：隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。
  • 持久性：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。 

3、隔离级别

   SQL标准定义了4类隔离级，包括了一些具体规则，用来限定事务内外的哪些改变时可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。

  • READ UNCOMMITTED(读取未提交内容) 

　　在READ UNCOMMITTED隔离级，所有事务都可以“看到”未提交事务的执行结果。在这种级别上，可能会产生很多问题。本隔离级很少用于实际应用，因为它的性能也不比其他级别好多少，而别的级别还有其他更多的优点。读取未提交数据，也被称之为“脏读(Dirty Read)”

  • READ COMMITTED(读取提交内容) 

　　大多数数据库系统的默认隔离级是READ COMMITTED(但这不是MYSQL默认的)。它满足了隔离的早先简单定义：一个事务在开始时，只能“看见”已经提交事务所做的改变，一个事务从开始到提交前，所做的任何数据改变都是不可见的，除非已经提交。这种隔离级别也支持所谓的“不可重复读(Nonrepeatable Read)”。这意味着用户运行同一语句两次，看到的结果是不同的。

  • REPEATABLE READ(可重读) 

　　REPEATABLE READ 隔离级解决了READ UNCOMMITTED隔离级导致的问题。它确保同一事务的多个实例在并发读取数据时，会“看到相同的”数据行。不过理论上，这会导致另一个棘手问题：幻读(Phantom Read)。简单来说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影”行。InnoDB和Falcon存储引擎通过多版本并发控制机制解决了幻读问题。 REPEATABLE READ 是MYSQL的默认事务隔离级。InnoDB和Falcon存储引擎都遵循这种设置。

  • SERIALIZABLE(可串行化) 

　　SERIALIZABLE是最高级别的隔离级，它通过强制事务排斥，使之不可能相互冲突，从而解决幻读问题。简言之，SERIALIZABLE是在每个读的数据行上加锁。在这个级别，可能导致大量的超时现象和锁竞争现象。很少看到有用户选择这种隔离级。

   注：不可重复读与幻读：不可重复读的重点是修改(同样的查询条件，你读取过的数据，再次读取出来发现值不一样了);幻读的重点在于新增或者删除(同样的查询条件，第1次和第2次读出来的记录数不一样)

三、MySQL安装后初始化

   前面我已经将MySQL数据库进行通用二进制安装，而把MariaDB进行源码安装，在安装好数据库后我们首要的任务就是初始好数据库，下面就是介绍如何初始化数据库。

1、初始化第一步提供配置文件

   首先mysql的数据库配置文件可分为三段及：[mysqld]、[mysql]、[client]，这三段配置所对应的意思为：

  • [mysqld] ：对应与mysql服务端
  • [mysqld_safe] ：对应与指定的safe端
  • [client]：对应于所有的客户端

通过下面的命令的输出结果我们可以知道mysql是如何读取配置文件的：

[[email protected] ~]# /usr/local/mysql/bin/mysqld --help --verbose |head -20
  Default options are read from the following files in the given order:
    /etc/mysql/my.cnf  /etc/my.cnf  ~/.my.cnf

 使用配置文件的方式：

  • 它依次查找每个需要查找的文件，结果是所有文件并集； 
  • 如果某参数在多个文件中出现多次，后读取的最终生效； 

然而上面的命令输出结果并不是只输出读取配置文件信息的，还有以下输出信息：

  • 显示mysqld程序启动时可用的选项，通常都是长选项 
  • 显示mysqld的配置文件中可用的服务变量 

我们还可以通过在mysql的交互式接口查看配置信息：

  • mysql> SHOW GLOBAL VARIABLES;   //全局有效的配置
  • mysql> SHOW SESSION VARIABLES;  //当前会话级别有效的配置

2、初始化第二步设置用户信息

删除所有匿名用户

mysql> DROP USER ''@'localhost'; 
mysql> DROP USER ''@'example.com';

    mysql用户帐号由两部分组成：username@'host' 

       host还可以使用通配符：

        %： 任意长度的任意字符

        _： 匹配任意单个字符

在mysql交互式接口给所有的root用户设定密码：

mysql> SET PASSWORD FOR username@'host' = PASSWORD('your_passwrod'); 
mysql> UPDATE user SET password = PASSWORD('your_password') WHERE user = 'root'; 
mysql> FLUSH PRIVILEGES;

在shell命令行给所有的root用户设定密码：

   # mysqladmin -uUserName -hHost password 'new_password' -p
   # mysqladmin -uUserName -hHost -p FLUSH-PRIVILEGES

四、连接MySQL

1、mysql client 和mysqld之间的传输会有下面两种状况

  • 本地通信：客户端与服务器端位于同一主机，而且还要基于127.0.0.1(localhost)地址或lo接口进行通信； 

            Linux OR Unix: Unix Sock,

            Windows: memory, pipe

  • 远程通信：客户端与服务器位于不同的主机，或在同一主机而使用非回环地址通信。 

            TCP socket

2、客户端/非客户端工具

mysql客户端工具有：mysql, mysqladmin, mysqldump, mysqlcheck这些工具的配置信息都可以在配置文件的[client]中定义。

  • 通用的选项： 

    -u, --user=

    -h, --host=

    -p, --password=

    --protocol={tcp|socket|memory|pipe}

    --port=   

    --socket=    例如：/tmp/mysql.sock

mysql的非客户端类的管理工具：myisamchk, myisampack

3、一些小知识

  • 获取mysql帮助信息：

   mysql> help contents    //获取所有命令模块

   mysql> help Keryword   //获取相关命令的帮助信息

  •  mysql命令行选项：

  --compress     //客户端与服务端压缩传输

  --database=, -D    //设定进入mysql命令行时默认的库

  -H, --html：       输出结果为html格式的文档

  -X, --xml:            输出格式为xml

  --sate-updates：  拒绝使用无where子句的update或delete命令；

            --ssl-ca=/path/to/ssl_ca_file       //获取ca证书后传输

            --ssl-capath=/path/to/ssl_ca_dir   //证书有多个时

  • mysql命令提示符：

  mysql> 等待输入命令

  ->    //续行符

  '>    

  ">

  `>

  /*>   //注释信息

  • mysql的快捷键：

  Ctrl + w: 删除光标之前的单词

  Ctrl + u: 删除光标之前至命令行首的所有内容

  Ctrl + y: 粘贴使用Ctrl+w或Ctrl+u删除的内容

  Ctrl + a: 移动光标至行首

  Ctrl + e: 移动光标至行尾

4、mysqldmin工具使用

语法：mysqladmin [options] command [arg] [command [arg]] ...

  • command

create  DB_NAME：创建数据库

举例：# mysqladmin -uroot CREATE testdb -p

drop   DB_NAME：删除数据量

举例：# mysqladmin -uroot DROP testdb -p

 debug：打开调试日志并记录于mysql的error log中；

 status：显示简要状态信息

     --sleep #: 间隔时长

     --count #: 显示的批次

使用举例：

[[email protected] ~]# mysqladmin -uroot status -p
Enter password: 
Uptime: 18616  Threads: 2  Questions: 70  Slow queries: 0  Opens: 5  Flush tables: 1  Open tables: 68  Queries per second avg: 0.003

extended-status： 输出mysqld的各状态变量及其值，相当于执行“mysql> SHOW GLOBAL STATUS”

举例：# mysqladmin -uroot extended-status -p 

variables： 输出mysqld的各服务器变量

flush-hosts： 清空主机相关的缓存：DNS解析缓存，此前因为连接错误次数过多而被拒绝访问mysqld的主机列表。

flush-logs：日志滚动，关闭当前日志打开新日志，主要是打开二进制日志和中继日志。

flush-privileges：让服务器重读授权表。

flush-status： 重置状态变量的值。

flush-tables： 关闭当前打开的表文件句柄，当服务器还在读写时不能进行此操作。

flush-threads：清空线程缓存。

reload： 功能同flush-privileges

refresh： 相当于同时使用flush-logs和flush-hosts

kill：杀死指定的线程，可以一次杀死多个线程，以逗号分隔，但不能有多余空格。

password：修改当前用户的密码；

ping：探测服务器是否在线。

processlist：显示mysql线程列表

举例：

[[email protected] ~]# mysqladmin -uroot processlist -p
Enter password: 
+----+------+-----------+-------+---------+------+-------+------------------+----------+
| Id | User | Host      | db    | Command | Time | State | Info             | Progress |
+----+------+-----------+-------+---------+------+-------+------------------+----------+
| 14 | root | localhost | mysql | Sleep   | 925  |       |                  | 0.000    |
| 32 | root | localhost |       | Query   | 0    | init  | show processlist | 0.000    |
+----+------+-----------+-------+---------+------+-------+------------------+----------+

shutdown： 关闭mysqld进程；

start-slave：启动从服务器线程

stop-slave：关闭从服务器线程

5、mysql的GUI客户端工具

  • Navicat for mysql
  • Toad for mysql
  • mysql front
mysql、mysqldmin、初始化
上一篇: MySQL（二）之MySQL基本操作
下一篇: MySQL（五）之创建删除修改库表

继续阅读