壓榨自己的學習能力極限，每天學習新內容之MySQL伺服器！（三）（今天內容更新完結）

 寫在前面：不是很明白，到底是筆記不好，還是寫的內容不殷實，莫名其妙的就被踩了14次，而且還是瞬間踩過去的，我想說如果你覺得好，你可以收藏，儲存，轉載（註明出處即可），如果你覺得不好，方便的話給出不好的建議和意見，我會改正，要是不方便的話，我也沒有強求您非要看我這篇筆記。您大可不必動用那珍貴的力氣去踩我這並不是非常專業的筆記。感謝所有給出建議和意見的網友。

 

這是一步步從頭學習MySQL的筆記曆程，本人在學習之前只接觸過少量SQL Server。

第一天曆程：壓榨自己的學習能力極限，每天學習新內容之MySQL伺服器！（一）

第二天曆程：壓榨自己的學習能力極限，每天學習新內容之MySQL伺服器！（二）

接下來每天都將更新~相信你通過筆記，一定能從零學會MySQL~

廢話不多說，開始今天的學習壓榨~！

視圖

如何建立視圖：

       格式：CREATE VIEWview_name [(視圖的欄位column_list)]

                   CREATE VIEW view_name AS SELECT clause;

       視圖儲存的僅僅是語句本身，但是當視圖建立之後，卻可以像使用表一樣使用它

       建立視圖的目的就是為了避免某些欄位被某些使用者查看的

       假設我們有表：

       

       比如：把那些CouseID不為空白的建立成一個視圖，並且沒有Totur段。

       CREATE VIEW xiake AS SELECT UID,Name,Age,Gender,CouseID FROMknight WHERE CouseID IS NOT NULL;

       

       視圖並不是真正的表，他沒有儲存資料，所以在處理資料的速度上，肯定是慢於真正的基表的。而當我們的基表改變之後，視圖建立出來的虛表會立刻改變。

       比如我們修改：

       UPDATE knight SET CouseID=15WHERE UID=9

       這時再去看剛才建立好的視圖，發現它已經改變。

       

 

往視圖中插入資料：

       視圖(虛表)是可以被插入資料的，但是，插入的所有資料其實是儲存在基表之中的，而如果插入的這個資料，在基表中有值沒有定義。則有可能報錯甚至不讓你建立。

       WITH CHECK OPTION 在建立資料的時候，可以明確指定讓基表中的約束應用到虛表中來。

 

刪除視圖：

       DROP VIEWview_name

 

 

如何?子查詢：

       我們SELECT實現了嵌套，我們的SELECT結果是從另一個SELECT的結果中得來的。

       比如形式為：      

                     SELECT * FROMSELECT * FROM Knight

                     SELECT * FROM xxx WHERE SELECT * FROMknight

       UPDATE knight SETAge=49 WHERE UID=6

       顯示這個表中年齡大於所有有年齡人員的平均年齡的：

     

       我們先查詢平均年齡：SELECTAVG(Age) FROM knight WHERE Age IS NOT NULL;

       發現，平均年齡是33歲

       則我們可以使用子查詢，一步解決。

              SELECTName,Age FROM knight WHERE Age > (SELECT AVG(Age) FROM knight WHERE Age ISNOT NULL);

       

      

事物：Transact

       什麼是事物呢？幾個步驟要麼同時完成，要麼同時都沒完成。對於我們SQL來說，則是幾個查詢語句，要麼同時都執行，要麼同時都不執行，他們要做一個原子操作，是不可分的。

       事物最典型的應用就是在銀行裡轉賬。比如A有2000，B有10000，B要轉給A3000元。如果剛轉走，錢還沒到A的時候，銀行停電了，這時A還沒有加錢，但是B的錢已經減掉了……這時怎麼辦呢？為了免除這樣的差錯。事物就出現了。

       而事物對於這樣的結果處理的方式是：將這3000退回B，讓其重新轉賬。但是對於大型資料庫，裡面的事物可能含有上萬條，所以如果執行到最後出錯了，那麼回退就要重新完成這上萬條的指令。為了避免回退的過長，還可以對事物做“快照”，而這樣的“快照”，就叫做Save Point，事物的儲存點。

       MyISAM是不支援事物的的

       支援事物的引擎：InnoDB

       只有當一個引擎完全支援ACID的時候，我們才說這個引擎支援事物。

       ACID：

       A:Atomicity :原子性。事物是一體的。

       C:Consistency：一致性。當事物完成以後，A減掉的B一定要加上，這種邏輯，必須是要一致的。體現的其實是結果的一致。

       I:Isolation：隔離性：多個事物之間的聯絡，一個事物發生了改變之後，其他的事物能否發現這樣的改變。當正在執行的事物，運行到一條命令發現原有資料庫發生改變的時候，它是否接受改變。這取決於資料引擎的隔離等級。對於MySQL來講，事物的隔離等級是很重要的屬性。

       D:Durability：持久性：一個事物完成之後，完成的結果要永久儲存下來。

 

MySQL的隔離等級：(MySQL的預設事物隔離等級是3.可重讀層級。)

       1.讀未提交：READUNCOMMITTED（一個事物完成的過程叫做提交的過程）。這種層級是最低的。

       2.讀提交：READCOMMITTED

       3.可重讀：REPEATABLE-READ

       4.序列化：SERIABLIZABLE

 

       查看預設層級：

       SHOW VARIABLES LIKE ‘tx_isolation’則看出層級為 REPEATABLES-READ

       隔離的層級越高，事物的安全性越好，但是並發性越差（所能同時執行的事物就越少）。如果事物對你不是很重要，可以降低事物的層級來增加事物的並發性。

 

四種層級的關係狀態：

       1.讀未提交：假設A事物中有x=0,B事物在運行時要去讀x，當5分鐘的時候x被A改為了1，此時B立刻擷取A=1,當10分鐘的時候，A將x回退回了0,則B中的x立刻變為0

       2.讀提交：假設A事物中有x=0,B事物在運行時要去讀x，當5分鐘的時候x被A改為了1，此時B擷取不到，所以x=0,當10分鐘的時候，A事物運行結束，x=1，則B在啟動並執行時候，立刻發現x=1並修改自己的x=1,15分鐘的時候，B事物運行結束，提交之後，B中的x=1

       3.可重讀：無論A中的x如何改變，還是提交之後改變。B在事物的運行中都不管x如何改變，只負責將自己的最初讀取的x值為事物的x值運行結束，之後，當提交結束之後，B將x重讀為1。

       4.序列化：只有一個事物徹底執行完成後，另一個事物才會開始的。當B事物啟動並執行時候，發現A中的x發生了改變，則B不會開始自己事物的運行。直到A事物徹底執行完成。則B再讀取A中最新的x值，開始執行。

 

       如何?兩個事物在執行的時候，你執行你的，我執行我的，互相之間發生的資料改變對自己來說是不可見的。而這種類似的機制，我們就叫做多版本並發控制，MACC。

 

事物的引擎：

       要想使用相應的事物，必須給予相應的資料引擎來決定。

       事物是隱式的

       START TRANSACTION:手動開啟事物。開啟之後，使用updata之類的命令，只有當你手動結束事物COMMIT的時候，才會儲存到表中去。

       比如：  START TRANSACTION;    開啟事物。

                     SELECT * FROM knight;    查看發現有13行

                     INSERTINTO knight SET Name=’Shi Zhongyu’,Age=37;    在事物中插入一行；

                     SELECT* FROM knight    ;發現已經插入了一行變為了14行。

                     ROLLBACK;     復原操作，直接退回插入前

                     SELECT* FROM knight;     則發現，又變成了13行

                     COMMIT;     結束事物，儲存結果至表中。

 

查看事物層級：

       SELECT @@SESSION.tx_isolation;

修改會話層級的事物層級：

       SET SESSION TRANSACTIONISOLATION LEVEL READ UNCOMMITTED;

查看當前MySQL中的串連進程

       SHOW PROCESSLIST

 

我們在兩個事物中操作，去查看其關聯樣式。就能發現如同剛才敘述的層級效果一樣，會產生那樣的效果。

 

MySQL資料同步，鎖

       什麼是資料同步？如果有兩個進程或者兩個應用需要訪問同一個資料的話，這時候就要啟用到同步概念。只要一牽扯到同步，就一定要用到鎖的概念。鎖：兩個操作加之於同一個對象的時候如何避免訛誤的。

       假設現在有兩個SQL語句，M正在插入knight，而N則查詢knight表，這樣，當N查詢的時候，查詢的其實可能只是一部分內容。那如何解決呢？則鎖的機制決定了，一個人在做事的時候，另一個人不能對其操作。

       鎖是無時無刻不在的，只要有一個人讀或者寫，則就開始加鎖。

 

鎖的分類：

       共用鎖定：讀鎖，大家都可以同時進行，讀是可以允許別人也讀的，但是讀不允許別人寫。

       獨佔鎖：寫鎖，寫的時候不允許別人寫。也不允許別人讀。

      

       而寫鎖的優先順序是比較高的，但是你不能一直在寫，過多的讀鎖是會造成寫饑餓的，而過多的寫鎖也會造成讀饑餓的~！

       讀和寫的鎖是互斥的，寫和寫的鎖也是互斥的。只有讀和讀的鎖才是共用的

 

       SHOW ENGINE的時候裡面就能看到引擎鎖

       為了保證備份的一致性，在你備份的時候是不能讓別人寫的。

 

鎖的層級：

       表鎖，一鎖就鎖定了一張表（並發層級低）

       行鎖：一鎖只鎖定了幾行（並發層級相對較高，但是可控制性要麻煩和複雜的多的多）

       事物引擎大部分都是行層級的鎖。而像MyISAM這種就用的是表層級的鎖。

 

鎖的實現：

       伺服器層級的實現：在伺服器上無論你是哪種儲存引擎我都加鎖（表鎖）

       儲存引擎層級的實現：大部分事務性引擎都可以實現行鎖，有的資料庫還能實現頁鎖（頁就是記憶體的空間Page）。

      

小擴充：頁（Page）

我們的記憶體是有虛擬記憶體空間的。對於任何一個32位系統的進程，都會認為自己有4G的記憶體可用。當我們只有512M的記憶體的時候，怎麼辦呢？我們只是把程式的需要使用的記憶體在記憶體上映射出一塊，而實現映射的這個功能就是靠頁（Page）來實現的。我們的記憶體就是按頁，來儲存的。

 

 

對錶進行加鎖

       LOCKTABLES tbl_name READ|WRITE,

       1.我們給knight加上一個讀鎖。

       LOCK TABLESknight READ;，則到另一台終端上，發現雖然加鎖，但依然可以讀取。

       2.我們給knight換成一個寫鎖。

       LOCK TABLESknight WRITE;

       此時，當我們沒有給表寫東西的時候，在另一台終端上能讀，但是當我們在本地插入一個資料之後，資料表真正被鎖，而另一台終端上就發現不能讀取了，並且一直在等待中… 而當我們只要在本地一釋放鎖，則另一終端立刻顯示讀取的新資訊。

 

對鎖進行釋放：

       UNLOCK TABLES;

 

為了實現更好的並發性，我們可以實現降低粒度（解析度），來降低鎖的層級/範圍，這樣並發性就會大大提高。

 

 

資料引擎：

       儲存引擎是表層級的概念，所以儲存引擎通常也被稱為表類型。有的引擎支援事物，有的儲存引擎不支援，如果在一個資料庫中使用多種含事物和非事物的引擎的話，勢必將會對使用者在事物的支援上產生麻煩。

 

常見的儲存引擎：

       

       MyISAM/Merge：不支援事物， 表層級的鎖，不支援線上備份

       InnoDB：支援事物，行層級的鎖，支援線上備份（熱備）。

       MEMORY：記憶體事物儲存引擎。只要儲存在這個表中的資料都儲存在記憶體中。記憶體引擎的主要目的，其實是來提供記憶體資料庫的，不提供資料的持久化儲存，所以記憶體引擎通常用於實現記憶體表（暫存資料表）。所以MEMORY表也被稱作堆表。

       Maria：支援事物引擎，支援行層級的鎖。當InnoDB被收購之後，那些原有開發InnoDB的人開發了Maria，仍處於開發中……

       Falcon：支援事物，也支援熱備，行層級的鎖，但是需要Mysql 6.0

       PBXT：一個商業化的引擎

       FEDERATED：聯合儲存引擎，只是用來聯合兩個表的，本身並不儲存什麼資料

       NDB：MySQL的叢集當中才能夠用到的引擎，將叢集中的資料在記憶體中處理，構建這個叢集需要5台以上的主機，效能非常好。

       Archive：Archive Storage引擎，用於實現將資料“挖掘”(智能決策，決策支援)用的。很少在修改它們，對於這種很少用到修改的資料，我們可以將其壓縮存放。這就是Archive Storage引擎。

       Blackhole：黑洞引擎，類似於Linux中的/dev/null，你存進去的任何資料都會被引擎悄悄的丟棄了。在後面的複製日誌，作為轉寄節點使用。

       CSV：儲存成文字檔的資料庫，主要為了跟其他的文本處理工具進行相容，也為了移植，但是本身處理效率不高。

 

 

 

MyISAM：

       早期的MyISAM是MySQL預設的引擎。現在已經被InnoDB取代。它支援全文索引，支援空間索引機制來索引結構化的資料。而且MyISAM在組織它的資料的時候，結構比較特殊，每一張表都對應了三個檔案，

       db.opt：定義資料庫的選項及其資訊

       *.frm：format格式的定義

       *MYD：My Data：資料儲存位置

       *MYI：My Index：資料的索引存放位置

 

MyISAM的重要特性：

       1.非事物

       2.不支援外鍵約束

       3.支援全文索引（能夠支援全文索引的引擎只有MyISAM）,當我們需要在InnoDB引擎中進行全文索引怎麼辦呢？利用其他的外掛程式，比如Lucene(Java語言構架)或者Sphinx(C語言構架)，都能對Mysql不管什麼引擎，對其資料進行快速全文索引的功能。

       4.無資料緩衝：無法對資料進行緩衝，但是對於伺服器是可以緩衝的。

       5.只對索引進行緩衝。索引緩衝也被稱為Key Buffer

       6.支援HASH和BTREE索引，HASH一般只在特定場合使用，比如只在Age=30時使用。

       7.表層級的鎖

       8.對於讀來說，速度非常快，用於資料倉儲是一個非常好的選擇。

       9.資料可以壓縮存放

       10.支援線上備份

       11.支援最多64個索引（不過這已經足夠了）

 

MyISAM的伺服器變數：

       key_buffer_size:索引緩衝：用於定義MyISAM索引緩衝大小,

       使用SHOWVARIABLES LIKE ‘key_buffer_size’;來查看當前大小。

       即便是我們沒有使用MyISAM引擎，你也是要設定這個值的。預設是8M。

       concurrent_insert：是否允許並發寫入（鎖），

              如果想使用並發寫入，無非就是實現了如果兩個寫入操作影響的不是相同資料的話，可以允許它並發寫於，事先需要Mysql對其判斷。而且並發寫入要求表操作中存在資料間隙才能實現。

       delay_key_write：延遲索引/鍵的寫入操作：ON|OFF

              索引是為了加速查詢過程的，我們如果對錶做一次修改，則索引就必須要重建了，而重建索引很浪費時間，浪費系統資源。而這個值，就是用於延遲索引寫操作的，定義不是表一修改就立即進行索引重建。預設是開啟的

       max_write_lock_count：嗯……這個就不介紹了……可以參照官方文檔

       preload_buffer_size：用於定義剛啟動的時候緩衝大小的。預設是32KB

 

MyISAM的專用管理工具：

       myisamchk：實現分析最佳化並修複MyISAM表的

       myisampack：實現壓縮MyISAM表的，當壓縮完成之後就不能再修改了

       myisam_ftdump：顯示全文索引的

 

InnoDB:

具有以下幾個特性：

       1.支援事物，基於MVCC

       2.支援行層級的鎖

       3.支援外鍵

       4.支援聚簇索引。它是BTREE的一種屬性，非聚簇索引，索引和資料是分開存放的，所以找到索引之後索引其實只是一個指標，而聚簇索引，它的索引和資料是在一起的，只要能找到索引則立刻能找到資料，它要比非聚簇索引少一次尋找，速度非常快。所以當我們把索引讀取到記憶體，則意味著把資料也讀取到記憶體了。

       5.同時支援索引緩衝和資料緩衝。意味著把使用者查詢的資料可以直接緩衝下來。

       6.支援線上非阻塞式的備份，支援熱備：不過需要商業化的備份工具來實現

 

InnoDB的存放：

       InnoDB不使用3個檔案來儲存，而是放在一個完整的資料表空間ib_data中來存放的。

       *.frm innoDB的定義檔案

       ib_log*：迴圈寫入的兩個檔案通過“innodb_log_file_size”定義。

 

相關的伺服器參數：

       Innodb_data_home_dir：資料檔案的存放目錄。預設情況下這個跟data_dir是一樣的。

       Innodb_data_file_path：資料檔案的路徑（使用的是相對路徑）

       Innodb_file_per_table：每一個表的表檔案路徑

       Innodb_buffer_pool_size：定義快取資料和索引的空間大小，用於快取資料和索引，這個空間一般要求比較大。

       Innodb_flush_log_at_trx_commit：定義日誌多長時間寫到真正的資料檔案中。這個選項有3個值，0，關閉，1，啟用，2，按需

       innodb_log_file_size：事物性記錄檔大小（記錄的是原子操作），是個迴圈日誌，這個檔案特別關鍵，Innodb的資料檔案崩潰的話，自我修複要靠這兩個檔案。這是個重做日誌。這個檔案可以定義的稍微大一點。理想值為128-256M之間

 

儲存引擎的狀態：SHOWENGINE INNODB STATUS;

       Semaphores：引擎層級的屬性和資訊

       Foreign Keyerrors：外鍵錯誤資訊

       Deadlocks：死結資訊。Mysql是可以自動解除死結的

       Transactions：事物相關的資訊

       File I/O：檔案I/O相關資訊

       Inser buffer andadaptive hash index：索引和Hash索引的資訊

       log：日誌資訊

       Buffer poll andmemory：緩衝相關資訊

       Row operations：行操作相關資訊

 

 

 

使用者管理

        通過mysql中繼資料庫中的六張表，來定義許可權指派。一共有6個重要的檔案

        user,db,host,tables_priv,columns_priv,procs_priv

 

       user：其中最重要的資訊，3個欄位：使用者，來自哪個主機，密碼是什麼：user,host,password，password中的值則是加密儲存的。

       tables_priv：定義表層級的許可權的，哪個表哪個使用者是否可以執行SELECT等DML語句的。

       columns_priv：更細力度的許可權控制，不光定義某個表，也能定義某個欄位上的許可權

       procs_priv：定義使用者是否具有執行預存程序許可權的。

 

       這六張表可以稱之為MySQL的授權資訊表。

 

對於Mysql資料，可以授權的許可權種類：

       1.SELECT,INSERT,UPDATE,DELETE等DML相關的語句。

       2.CREATE（僅僅指建立資料庫和表的許可權） DROP:等DDL語句

       3.INDEX ：索引的許可權

       4.ALTER ：定義使用者是否可以修改表結構

       5.SHOW DATABASES;使用者是否具有查看本伺服器上具有存取權限的資料庫的許可權

       6.SUPER：執行管理命令的許可權

       7.LOCK TABLES：鎖許可權

       8:CREATE VIEW,SHOW VIEW 建立和查看視圖的許可權

       9:CREATE USER 建立和刪除使用者的許可權

       10:  REPLICATION SLAVE, 主要用於複製的許可權，是否具有從主伺服器上複製二進位日誌內容的許可權

              REPLICATION CLIENT：主要用於複製的許可權，請求主從環境當中用於複製的相關資訊的許可權。

       11:RELOAD：使用重啟伺服器和重讀的許可權

       12.SHUTDOWN：關閉服務的許可權

       13.SUPER: 實現讓使用者實現管理伺服器進程的許可權

       14:GRANT OPTION：當你把一個資料庫的許可權授予一個使用者之後，這個使用者是否能夠再將許可權授予他人？

 

 

如何去建立一個使用者帳號：

       CREATE USER user_name@’HOST’IDENTIFIED BY ‘PASSWORD’

       指定主機的時候%表示任意主機

       比如，建立使用者jerry並指定密碼123456

              CREATE USER jerry@’%’IDENTIFIED BY ‘123456’

              FLUSH PRIVILEGES;

 

       當我們建立一個使用者之後，這個使用者僅僅只有，串連到資料庫上，並使用SHOW DATABASES的許可權

       在主機名稱和指定的IP地址上我們可以使用萬用字元

       比如：

              192.168.0.%

              192.168.0.0/255.255.255.0

 

刪除一個使用者：

       格式：DROP USER username@host

 

如何給使用者授權：

       格式：GRANT priv_type ON 資料庫物件 [權限等級] TO 使用者 [並修改其密碼] [設定此使用者將獲得的許可權繼續授權]

 

       1.給jerry使用者添加各項許可權

       授予jerry具有執行建立資料庫的許可權：

              GRANT CREATE ON *.* TO ‘jerry’@’%’;

              FLUSH PRIVILEGES;

              當授予CREATE許可權之後，如果想DROP，則沒有許可權

       授權DROP:

              GRANT DROP ON *.* TO ‘jerry‘@’%’;

              FLUSH PRIVILEGES;

       授予查詢許可權

              GRANT SELECT ON *.* TO ‘jerry’@’%’;

              FLUSH PRIVILEGES;

      

 

       2.授予所有的對與hellodb資料庫的許可權：

       GRANT ALL PRIVILEGES ON hellodb.* TO ‘jerry’@‘%‘；

 

       3.授予許可權並修改密碼：

       GRANT priv_list ON db.table TO user@host [IDENTIFIED BY ‘password’]

 

       物件類型：

       TABLE 預設就是TABLES

       FUNCTION 儲存函數

       PROCEDURE預存程序

 

       權限等級：

       *

       *.*

       db_name

       db_name.tbl.name

       tbl_name

       db_name.routine_name

 

       當我們再次授予許可權的時候，它會附加許可權，而不會覆蓋原有許可權

      許可權中的“USAGE”就是允許使用者串連到伺服器上，

 

 

查看一個人的許可權：

       SHOW GRANTS FOR ‘root’@’localhost’

       SHOW GRANTS FOR ‘jerry’@’%’

 

收回許可權：

       REVOKE [權限類別型] [指定對象] [哪個使用者]；

       1.比如收回jerry的SELECT 許可權：

       REVOKE SELECT ON hellodb.* FROM ‘jerry’@‘%‘；

       FLUSH PRIVILEGES;

 

 

限定使用者所使用的資源：

       這種限定不能立即生效，想要立即生效，要使用：

       FLUSH USER_RESOURCES;

       常用的限定資源：

       WITH GRANT OPTION：設定使用者可以向下授權

       WITH MAX_QUERIES_PER_HOUR n; ：限定使用者每個小時最多執行N次查詢

       WITH MAX_UPDATES_PER_HOUR n：限定使用者每個小時最多執行幾次修改操作。

       WITH MAX_CONNECTIONS_PER_HOUR n：限定使用者一個小時最多連入多少次。

       WITH MAX_USER_CONNECTIONS n：限定使用同一個使用者帳號在同一時間最多能同時連進來幾次。

 

 

 

欄位層級許可權的限定：

       常用的欄位層級的限定只有SELECT 和UPDATE；

       比如：授予查詢欄位的許可權

       GRANT SELECT(name) ON db.table TO user@host

      

授予使用者具有執行儲存常式的許可權：

       GRANT EXECUTE ON [物件類型] TO user@host

 

MySQL資料庫的密碼忘了怎麼辦？

       1.重新初始化mysql資料庫

              顯然，這不是我們想要的。

       2.啟用1層級，跳過密碼，然後進去重新設定密碼。

       我們需要先重啟服務，在重啟伺服器的時候需要用一個特殊的選項來啟動伺服器：

       首先我們要知道我們啟動mysqld的時候，啟動的其實是/$USERBIN/mysqld_safe這個服務。（$USERBIN）

       而我們現在需要用這個外加一些參數,並讓他在後台運行

              /usr/local/mysql/bin/mysqld_safe --skip-grant-tables --skip-networking &

       在這種情況下，要給使用者設密碼，必須使用修改表的形式來完成，則：

              使用mysql命令進入mysql，此時是不要求輸入密碼的。

       USE mysql

       UPDATA user SET Password=PASSWORD(‘redhat’) WHERE User=’root’ AND Host=’localhost’;

       UPDATA user SET Password=PASSWORD(‘redhat’) WHERE User=’root’ AND Host=’127.0.0.1’;

 

明天休息一天，後天繼續，後天的內容更加重要，Mysql的備份和資料的複製！