天天看點
傳回

MySQL入門,原理及優化 學習總結 (二)資料庫概念進階部分1、編碼mysql2、mysql的目錄及配置檔案3、資料庫-原理部分4、關系代數分類5、資料庫設計6、資料庫設計遵循的原則7、事務并發操作出現幾種問題8、資料庫并發的控制

MySQL原理及優化學習總結

  • 資料庫概念進階部分
  • 1、編碼mysql
    • 1.1、檢視mysql編碼
    • 1.2、設定mysql編碼
  • 2、mysql的目錄及配置檔案
  • 3、資料庫-原理部分
    • 3.1、資料模型
    • 3.2、資料庫
    • 3.3、資料庫管理系統
    • 3.4、資料庫系統相關人員
    • 3.5、資料庫系統
  • 4、關系代數分類
    • 4.1、基本運算
    • 4.2、組合運算
    • 4.3、擴充的關系代數操作
  • 5、資料庫設計
    • 5.1、需求分析
    • 5.2、概念模式設計
    • 5.3、邏輯模式設計
    • 5.4、資料庫實施
    • 5.5、資料庫的運作和維護
  • 6、資料庫設計遵循的原則
    • 6.1、範式概念
      • 6.1.1、第一範式（First Normal Form）
      • 6.1.2、第二範式（Second Normal Form）
      • 6.1.3、第三範式（Third Normal Form）
      • 6.1.4、BCNF（Boyce-Codd Normal Form）
  • 7、事務并發操作出現幾種問題
    • 7.1、丢失修改資料
    • 7.2、讀“髒”資料
    • 7.3、不一緻分析
  • 8、資料庫并發的控制
    • 8.1、并發排程的可串行化
    • 8.2、封鎖
    • 8.3、死鎖

資料庫概念進階部分

1、編碼mysql

1.1、檢視mysql編碼 

mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

1.2、設定mysql編碼 

# vi /etc/my.cnf
如下(少補)：

[mysqld]
character-set-server=utf8 
collation-server=utf8_general_ci 
sql_mode='NO_ENGINE_SUBSTITUTION'

[mysql]
default-character-set = utf8

[mysql.server]
default-character-set = utf8

[mysqld_safe]
default-character-set = utf8

[client]
default-character-set = utf8

重新開機mysql

# service mysqld restart
再次檢視編碼：

# mysql -uroot -p
mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

2、mysql的目錄及配置檔案

A、/etc/my.cnf 這是mysql的主配置檔案

B、/var/lib/mysql mysql資料庫的資料庫檔案存放位置

[[email protected] etc]# cd /var/lib/mysql/
[[email protected] mysql]# ll
total 176172
-rw-rw----. 1 mysql mysql       56 Oct 20 00:10 auto.cnf
-rw-rw----. 1 mysql mysql 79691776 Oct 21 04:46 ibdata1
-rw-rw----. 1 mysql mysql 50331648 Oct 21 04:46 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 Oct 20 00:09 ib_logfile1
drwx------. 2 mysql mysql     4096 Oct 21 01:38 itcast
drwx------. 2 mysql mysql     4096 Oct 21 04:45 menagerie
drwx--x--x. 2 mysql mysql     4096 Oct 20 00:09 mysql
-rw-r-----. 1 mysql root      2219 Oct 20 00:14 mysql.err
-rw-rw----. 1 mysql mysql        5 Oct 20 00:10 mysql.pid
srwxrwxrwx. 1 mysql mysql        0 Oct 20 00:10 mysql.sock
drwx------. 2 mysql mysql     4096 Oct 20 00:09 performance_schema
-rw-r--r--. 1 root  root       111 Oct 20 00:09 RPM_UPGRADE_HISTORY
-rw-r--r--. 1 mysql mysql      111 Oct 20 00:09 RPM_UPGRADE_MARKER-LAST
drwxr-xr-x. 2 mysql mysql     4096 Oct 20 00:09 test

我們的mysql資料庫的資料庫檔案通常是存放在了/ver/lib/mysql這個目錄下

C、/var/log/mysql資料庫的日志輸出存放位置

D、檢視端口。Netstat –nltp 看是否能找到3306的端口

[[email protected] menagerie]# netstat -nltp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name   
tcp        0      0 0.0.0.0:57958               0.0.0.0:*                   LISTEN      1306/rpc.statd      
tcp        0      0 0.0.0.0:111                 0.0.0.0:*                   LISTEN      1284/rpcbind        
tcp        0      0 0.0.0.0:22                  0.0.0.0:*                   LISTEN      1573/sshd           
tcp        0      0 127.0.0.1:631               0.0.0.0:*                   LISTEN      1362/cupsd          
tcp        0      0 127.0.0.1:25                0.0.0.0:*                   LISTEN      1654/master         
tcp        0      0 :::3306                     :::*                        LISTEN      37860/mysqld        
tcp        0      0 :::49647                    :::*                        LISTEN      1306/rpc.statd      
tcp        0      0 :::111                      :::*                        LISTEN      1284/rpcbind        
tcp        0      0 :::22                       :::*                        LISTEN      1573/sshd           
tcp        0      0 ::1:631                     :::*                        LISTEN      1362/cupsd          
tcp        0      0 ::1:25                      :::*                        LISTEN      1654/master 
           
[[email protected] mysql]#  cat mysql.pid
37860

3、資料庫-原理部分

常用的術語

3.1、資料模型

​ 資料模型（Data model）是資料庫結構的基礎，是用來描述資料的一組概念和定義，資料模型主要有三個要素：資料結構、資料操作、資料限制條件。

資料結構：對象類型的集合，是對靜态屬性的描述。

資料操作：是對資料庫中的各種對象性資料，允許執行的操作的集合，如增删改查等；資料操作是對系統動态熱性的描述。

資料的限制條件：是一組完整性規則的集合，也就是說，對于具體的應用必須先遵循特定的語義限制條件。比如：性别隻能取 “男”或者“女”中的之一。考試成績：（滿分100）隻能是0-100的數值。

3.2、資料庫

​ 資料庫（database）是長期存儲在計算機外存上有結構，可共享的資料集合；資料庫中的資料按照一定的資料模型描述、組織和存儲，具有較小的備援度，較高的資料獨立性和可擴充性，并可以為多個使用者共享。

常見資料庫分類：

關系型資料庫：如：MySQL，oracle，sqlserver

非關系型資料庫：如：redis，hbase等

3.3、資料庫管理系統

​ 資料庫管理系統（database managerment system， DBMS）是指資料庫系統中對資料庫進行管理的軟體系統，是資料庫系統的核心組成部分。資料庫的一切操作，如增删改查以及各種控制，都是通過DBMS進行的。

具有以下4個基本的功能：

1、資料定義功能

使用者可以通過DBMS提供的資料定義語言對資料庫的資料進行定義。

2、資料操縱功能

使用者可以通過資料操縱語言實作對資料庫的增删改查操作

3、資料庫運作管理

管理資料庫的運作是DBMS運作時的核心工作。所有通路資料庫的操作都要在DBMS的統一管理下進行，以保證資料的安全性、完整性、一緻性以及多使用者對資料庫的并發使用。

4、資料庫的建立和維護

建立資料庫，包括資料庫初始資料的輸入與資料轉換等。維護資料庫，包括資料庫的轉儲與恢複，資料庫的重組織，性能監控和分析。

3.4、資料庫系統相關人員

資料庫系統的相關人員是資料庫系統的重要組成部分，具體可以分為以下的三類人員

1、資料庫管理者

職責：負責資料庫的建立、使用、維護的專門人員

2、應用程式開發人員

職責：開發資料庫應用程式的人員，可以使用資料庫管理系統的所有功能。

3、最終使用者

職責：一般來說，是通過應用程式使用資料庫的人員，最終使用者無需自己編寫應用程式。

3.5、資料庫系統

​ 資料庫系統（database system DBS）是由硬體系統，資料庫管理系統，資料庫，資料庫應用程式，資料庫系統相關人員構成的人-機系統，是指有資料庫的整個計算機系統。

說明：在許多場合下，資料庫，資料庫管理系統，資料庫系統不做嚴格區分；

4、關系代數分類

4.1、基本運算

​ 并、差、笛卡爾積、選擇、投影；

關系代數的基本操作（原始運算）：“選擇”、“投影”、笛卡爾積（也叫做“叉積”或“交叉連接配接”）、并集、差集和“重命名”。

4.2、組合運算

​ 交、連接配接、自然連接配接和除；

​ 其中最重要的是交集、除法和自然連接配接。(inner join [join])

4.3、擴充的關系代數操作

​ 外連接配接（左外和右外）、外部并和半連接配接；(left join ,right join)

5、資料庫設計

關系型資料庫的設計分為以下5個階段：

5.1、需求分析

(1) 明确使用者需求，到底做什麼？

5.2、概念模式設計

(1) 該階段是整個資料庫設計的關鍵，它通過對使用者需求進行綜合、歸納與抽象。主要是通過E-R圖表示。

(2) 優點：

A、簡單明了，容易了解

B、獨立于計算機與具體的RDBMS無關。

(3) E-R模型的基本元素

A、實體（Entity） 如：學生

B、屬性（attribute）如：姓名

C、鍵碼（key）如：身份證号碼；

D、關系（relationship）如：兩個實體之間的關系

a) 一對一（1:1）： 一個人一個身份證号碼；一個學校一個校長

b) 一對多（1：n）：學校和老師的關系

c) 多對多（n：n）：學生選課，一個學生可以選擇多門課程，一門課程課被多名學生進行選修。

E、E-R符号表示：

5.3、邏輯模式設計

(1) 該階段會涉及到更多的概念，方法，理論。

(2) 主要任務：

A、與具體的資料庫相關

B、規範化處理，盡可能的消除關系操作過程中的異常情況。

C、E-R圖轉換為如下的關系模式

電影（片名，出品年份，影片長度，影片類型，公司名稱）

明星（姓名，聯系位址，公司名稱）

扮演（片名，出品年份，姓名，角色）

影片公司（公司名稱，位址）

卡通片（片名，出品年份，設計平台）

5.4、資料庫實施

(1) 建立資料庫，定義資料庫結構，組織資料入庫，調試資料庫并進行資料庫的試運作。

5.5、資料庫的運作和維護

(1) 資料庫正式運作之後，對資料庫運作過程中對其進行評價，調整，修改，調優等。

6、資料庫設計遵循的原則

6.1、範式概念

概念：範式就是符合某一規範級别的關系模式的集合。共有7種範式：

1NF ⊃2NF⊃3NF⊃BCNF⊃4NF⊃5NF⊃6NF

6.1.1、第一範式（First Normal Form）

如果一個關系模式R的所有屬性都是不可分割的基本資料項，則這個關系屬于第一範式。

舉例說明：(學生選課：學号，姓名，系别，系部位址，課程名稱，課程成績)

Student(s_no,s_name,s_dept,s_location,s_course_name,s_grade)

注：1NF是關系模式應具備的最起碼的條件，如果資料庫設計不能滿足第一範式，就不能稱作是關系模式；關系資料庫設計研究的關系規範化是在1NF基礎之上進行的。

6.1.2、第二範式（Second Normal Form）

定義：若關系模式R屬于第一範式，且每個非主屬性都是完全函數依賴于主鍵，則R屬于第二範式。

說明：從2NF的定義可以看出，從2NF開始讨論的是主鍵和非主屬性之間的函數依賴關系，是以分析關系模式是屬于2NF，首先指明關系模式的主鍵，然後在讨論非主屬性和主鍵之間的函數依賴關系。

例如：選課關系模式

SC（s_no,c_no,score）中，主鍵為（s_no,c_no），而非主屬性score與主鍵之間不存在部分函數依賴關系，是以關系模式SC屬于2NF

6.1.3、第三範式（Third Normal Form）

定義：若關系模式R屬于第一範式，且每個非主屬性都不傳遞函數依賴于主鍵，則R屬于第三範式。

說明：3NF說明的是非主屬性和主鍵之間的函數依賴關系

例如：選課關系模式

SC（s_no,c_no,score）中,由于除了主鍵之外，隻有一個非主屬性score，是以score不可能構成與主鍵之間的傳遞函數依賴，是以SC屬于3NF

6.1.4、BCNF（Boyce-Codd Normal Form）

定義：若關系模式R屬于第一範式，且每個屬性都不傳遞依賴于主鍵，則R屬于BC範式。

說明：也就是說，在關系模式R中，凡是決定因素的屬性或屬性集包含鍵碼，決定因素是函數依賴的左部屬性集，比如X->Y，X稱為決定因素。

由BC範式的定義可以得到以下的結論，一個滿足BC範式的關系模式有：

​ 1、所有非主屬性對每一個候選碼都是完全函數依賴

​ 2、所有的主屬性對每一個不包含它的候選碼都是完全函數依賴

​ 3、沒有任何屬性完全函數依賴于非候選碼的任何一組屬性

7、事務并發操作出現幾種問題

​ 所謂事務，是使用者定義的一個資料庫操作序列，是資料庫環境中的邏輯工作單元，是一個不可分割的整體。

事務的這個4個特性簡稱為ACID特性，事務ACID特性可能遭到破壞的因素有：

①多個事務并發執行，不同僚務的操作交叉執行；

     ②事務在運作過程中被強行終止。

如何保證在多個事務并發執行的過程中不發生上述的兩種情況，是資料庫管理系統并發控制的主要責任。

7.1、丢失修改資料 

舉例：銀行卡有100元，事務A取10元，事務B取10元，事務AB兩人同時取錢，初始值都是100

7.2、讀“髒”資料 

資料庫技術中，如果正常送出的事務A使用了事務B未送出的撤銷資料，這種資料成為“髒資料”，會造成資料的髒讀和污讀。

7.3、不一緻分析 

造成這種資料不一緻的主要原因是并發執行的兩個事務中，一個事務在讀取資料時，另一個事務正在修改同一個資料。這樣就可能導緻兩個事務的互相幹擾及“讀”事務的錯誤執行結果。

8、資料庫并發的控制

8.1、并發排程的可串行化 

可串行化準則：多個事務的并發執行時正确的，當且僅當其結果按某一次序串行執行它們時的結果相同，這種排程政策稱為可串行化排程。可串行化是并發事務正确性的準則，一個給定的并發排程，當且僅當它是可串行化的，才認為是正确的。

8.2、封鎖

封鎖是實作并發控制的非常重要的技術。封鎖是指某事務在對某資料對象進行操作以前，先請求系統對其加鎖，成功加鎖之後該事務就對該資料對象有了控制權，隻有該事務對其進行解鎖之後，其他的事務才能更新它，DBMS有兩種鎖：

① 排它鎖（也稱作X鎖）

​ 如果事務T在對某個資料對象實施了X鎖，那麼其他的事務必須要等到T事務接觸對該資料對象的X鎖之後，才能對這個資料進行加鎖。

② 共享鎖（也稱作S鎖）

​ 如果事務T在對某個資料對象實施了S鎖，那麼其他的事務也能對該資料對象實施S鎖，但是對這個資料對象施加的所有S鎖都接觸之前不允許任何事務對該資料對象實施X鎖。

8.3、死鎖 

封鎖技術可以避免一些并發操作引起的不一緻錯誤，但也會産生其他的一些問題，活鎖和死鎖。

① 活鎖

如果某個事務處在永遠等待的狀态，得不到封鎖的機會，這種現象為活鎖，避免這種鎖最好的方法就是采用先來先服務的政策。

② 死鎖

兩個或兩個以上的事務都處于等待狀态每個事務都在等待對方事務接觸封鎖，它才能繼續執行下去，這樣任何事務都處于等待狀态而無法繼續執行的現象稱為死鎖

解決死鎖問題方法有兩類：

A、 死鎖的預防

B、 死鎖的診斷與預防

知識總結 mysql 資料庫
上一篇: mysql索引原理總結分類：優缺點索引b+樹查找過程問題整理索引引擎聚集索引輔助索引聯合索引指令
下一篇: mysql進階知識總結1.mysql explain介紹2.mysql 索引失效情況3.sql優化4.mysql慢查詢日志5.mysql show profile6.mysql 鎖

繼續閱讀