天天看點
傳回

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（五）之建立删除修改庫表

繼續閱讀