MySQL 基礎總結

標籤：innodb   mysql   myisam   事務   

服務端：mysqld 用戶端： mysql

常用管理命令：(註：所有要在服務端執行的命令都要以;結尾，以告訴伺服器命令結束）

1. 登陸： mysql –uuser –ppassword –hhost 分別指定使用者，密碼和mysql主機即可登陸，也可以在家目錄下建立個.my.cnf設定檔，把使用者名稱和密碼主機等配置在裡面：假定root的密碼是mypass

   [[email protected] ~]# cat << ‘EOF‘ > .my.cnf        
   > [client]        
   > user = root        
   > password = mypass        
   > host = localhost        
   > EOF

   當然如果在本機上登陸還是unix sock 的方式最快，消耗資源最小，因為這是在核心底層實現的，如果用IP地址登陸則還需要先送到核心的TCP/IP協議棧處理。

2. 給使用者加密碼：

   1. 在mysql命令列下執行 SET PASSWROD FOR ‘user’@’host’=PASSWORD(‘password’);

   2. Shell下用mysqladm修改： mysqladm –uroot –hlocalhost password ‘mypass’ –predhat （把[email protected]的密碼由redhat改為mypass)

   3. 直接修改mysql庫user表：UPDATE mysql.user SET PASSWORD=PASSWORD(‘redhat‘) WHERE User=‘root‘ AND Host=‘localhost‘;

3. 建立和刪除使用者：

   1. CREATE USER ‘root’@’172.16.100.100’ IDENTIFIED BY ‘mypass’;

   2. DROP USER ‘root’@’172.16.100.100’;

4. 忘記管理員密碼咋整：

   1. 停掉mysqld服務後，mysqld_safe –-skip-grant(跳過授權表） –-skip-networking（不允許遠端存取mysql，以保證安全），此方式啟動後，無密碼登陸後修改密碼

5. 使用者授權和取消授權：

   1. GRANT SELECT,UPDATE ON db_name.tb_name TO ’user’@’host’  [IDENTIFIED BY  PASSWORD ]; FLUSH PRIVILEGES;

   2. REVOKE SELECT,UPDATE ON db_name.tb_name FROM USER;

6. 查看已經授權使用者資訊：SHOW GRANTS\G

7. 查看mysql線程資訊：SHOW PROCESSLIST;

8. 查看錶索引資訊：SHOW INDEXES FROM tb_name;

9. mysqld –help –verbose 可以查看mysql設定檔中可以使用的參數

10. 各種秀：SHOW DATABASES; SHOW TABLES; DESC tb_name; SHOW ERRORS; SHOW STATUS; SHOW BINARY LOGS; SHOW BINLOG EVENTS;

11. 查處某個庫中的表的詳細屬性資訊：SHOW TABLE STATUS RLIKE ‘%user%’\G (用RLIKE可以匹配Regex、\G以縱向的方式顯示，當列比較多時候比較有效果，\g可以直接送到服務端，不用再加分號）

12. SHOW MASTER STATUS; 可查看當前二進位日誌的position

13. SHOW GLOBAL | SESSION VARIABLES LIKE | RLIKE ‘%log%’; 查看伺服器變數資訊

14. SET GLOBAL.var_name=VALUE;

15. SET @@GLOBAL.var_name=VALUE;

16. SELECT DATABASE(); SELECT LAST_INSERT_ID();

17. SHOW CREATE TABLE user; SHOW ENGINE INNODB STATUS;

18. HELP Contents; –> SHOW Administrations; ….

19. mysql> source /tmp/backup.sql | mysql –uroot –pmypass < /tmp/backup.sql

20. mysql -uroot -predhat -e ‘SHOW DATABASES;‘

Tips: 由於緩衝是對查詢語句做雜湊作為鍵儲存的，而Hash是case-sensitive的，所以呢為了提高快取命中率，SQL語句要保持一定的風格，例如把所以命令關鍵字全部大寫。養成良好的編程習慣利人利已。

關於儲存引擎：mysql是外掛程式式儲存引擎，可以按需隨時切換（儲存引擎可以理解為資料庫的邏輯資料與磁碟上位元據的中介軟體，它完成兩種資料格式之間的相互轉換過程）

MyISAM: 是ISAM的改進版本，不支援熱備，支援溫備，支援B-tree indexes，不支援T-tree indexes 和Hash indexes，支援全文檢索索引（full-text search indexes：可以索引到一個欄位上的所有資料上)等，和InnoDB最大的區別在於不支援事務（Transaction), 支援表層級的鎖，InnoDB支援行層級的鎖，支援延遲更新索引鍵（delay key update)

MyISAM資料檔案儲存形式：db_name.MYI index檔案， db_name.MYD data檔案， db_name.frm framwork表定義檔案，存放表結構資訊等

適合資料倉儲、olap(online analytical processing)、寫入少，查詢多的情境，對巨量資料情境支援不好，資料庫很大（超過500G)時一旦崩潰，很難恢複，MariaDB 啟用aira引擎，是改良的myisam，提供了安全的崩潰恢複。

InnoDB: InnoDB公司開發的支援事務和外鍵的儲存引擎，也吸收了非死不可、穀哥等公司大牛的貢獻，後被假骨人收購後就走上了商業化的道路，不過這假骨頭的人也對innodb做了n多的改進和最佳化。

InnoDB支援的行層級的鎖並不是絕對的，當執行的語句所影響的行不確定時，是對全表進行鎖定的，例如 SELECT * FROM db_name.tb_name WHERE Name LIKE ‘%mysql%’;

innodb支援 外鍵、事務、MVCC(Multi-Version Concurrency Control) 多版本並發控制、聚簇索引（索引和表資料放在一起並按一定的順序排列，是有序資料，而myisam儲存的是堆資料，heap,不支援聚簇索引）、間隙鎖（Gap Lock:間隙鎖，鎖定一個範圍，但不包含記錄本身）、輔助索引、自適應hash索引、支援熱備、行級鎖

資料檔案存放：ibdata1 資料表空間檔案，建議開啟innodb_file_per_table = 1,每表一個資料表空間，tb_name.ibd,否則所有表放在一個檔案中不便於管理，但是，單獨的資料表空間檔案僅儲存該表的資料、索引和插入緩衝等資訊，其餘資訊還是存放在預設的資料表空間中。tb_name.frm 表定義檔案

MEMORY 建立暫存資料表時用到，記憶體儲存引擎

BLACKHOLE 黑洞，在複製結構中，為減少master壓力和二進位日誌複製次數，只複製一份到一個slave上，其他slave全部到此slave上複製binlog, 而此slave並不儲存資料，只提供binlog源，應用relaylog時全部扔到BLACKHOLE去避免不必要的IO。

CSV 可以把資料匯出為csv文字格式設定，可以在不同資料庫之間交換資料

ARCHIVE 5.5後支援索引，適用於儲存日誌等按時間序列的資料擷取，僅支援insert, select， 支援很好的壓縮功能

FEDRATED:

        能夠訪問遠程伺服器上的資料的儲存引擎，FedratedX

MRG_MYISAM merge MySQL已經支援表分區，這個用處就不大了

        能夠合并多個MyISAM表的儲存引擎，

NDB：叢集儲存引擎，專用於MySQL Cluster

第三方儲存引擎，

    OLTP類，線上交易處理

        XtraDB, InnoDB的增強版

        PBXT: 支援事務，社區不活躍，MariaDB 5.5.32後原生也不再支援

TokuDB 很牛逼，已經開源，MySQL外掛程式，可以直接使用，支援分形樹索引結構，適用於需要插入大量資料的分析型情境

    圖示資料庫，有著複雜串連的

    列式資料庫，最適於儲存海量資料，

列式儲存引擎：

    Infobright: 最著名，真正在生產環境中使用，數十TB, 支援壓縮，如果使用需要對mysql伺服器定製

    InfiniDB

    MonetDB

    LucidDB

    社區儲存引擎

        Aria

        Groona: 全文索引引擎

        QQGraph: 支援圖操作，Open query 研發

        SphinxSE：獅身人面象，已經被Maria社區採用，MariaDB 5.5.32+

        Spider：支援分區（sharding）

        VPForMySQL：支援垂直資料分割，vertical partition, spider作者寫的

 

關於MySQL日誌：

    二進位日誌：記錄會引起資料庫資料變化的語句：

• • SHOW BINARY | MASTER LOGS;

  • SHOW MASTER STATUS;

  • SHOW BINLOG EVENTS [IN ‘log_name’] [FROM position] [LIMIT n] 位移n條語句

  • FLUSH LOGS; 一般只滾動binlog 和 relaylog, 每次mysql服務的重啟也會引起binlog滾動，日誌大小達到上限也會自動滾動，一般預設1G

  • 清除binlog安全的方式：PURGE BINARY | MASTER LOGS TO ‘log_name’ | BEFORE ‘2012-10-10 12:09:09’;

  • log-bin = /path/to/some_log_file

  • expire_log_days = 100

  • sync_binlog 事務提交時，是否從cache刷寫到磁碟，建議開啟，對效能稍微有些影響，但是更可靠，worthful

  • mysqlbinlog –end-position=234 mysql_bin.000012 > /tmp/1.sql

  • mysql –uroot –pmypass < /tmp/1.sql

  • 在用二進位恢複資料庫時，要把二進位日誌關掉，不然重複記錄操作，徒增二進位空間

innodb_flush_log_at_trx_commit = 0|1|2 日誌刷寫到磁碟頻率，0 --> 1秒一次，1 --> 每次事務提交就寫，2 --> 0+1

慢查詢日誌：建議開啟，記錄執行時間長的查詢過程以方便分析最佳化

• slow_query_log 定義是否開啟慢查詢日誌

• slow_query_log_file 定義慢查詢記錄檔儲存位置

• long_query_time 定義查詢時間長度達到多少被認為是慢查詢

• 慢查詢時間長度：從查詢啟動到查詢完成，中間無論什麼原因導致都計算在內，牆上掛鐘時間而非CPU時間

錯誤記錄檔

• 記錄伺服器啟動和關閉過程中的資訊

• 記錄伺服器運行中的錯誤資訊

• 事件調度器運行一個事件時產生的資訊

• 在從伺服器上啟動從伺服器處理序時產生的資訊

查詢日誌：一般不建議開戶，消耗伺服器資源，影響效能

交易記錄：把無序IO轉化為有序IO,不宜過大，如果過大伺服器從崩潰是恢複會消耗很大時間長度

中繼日誌：用於複製情境，slave從master複製過來的binlog先放到中繼日誌，然後再應用到本地

 

關於innodb,關於事務：(參考http://javabkb.iteye.com/blog/1441197內容）

• 事務自然要支援ACID

  • Atomicity 原子性：整個事務中的所有操作要麼全部完成，要麼全部不完成，如果執行過程中出現錯誤則復原，資料和沒發生這個事務一樣

  • Consistency 一致性

  • Isolation 隔離性

  • Durability 持久性

 

• 重做日誌：執行個體和介質失敗，重做記錄檔就能派上用場，如資料庫掉電，InnoDB儲存引擎會使用重做日誌恢複到掉電前的時刻，以此來保證資料的完整性。參數innodb_log_file_size指定了重做記錄檔的大小；innodb_log_file_in_group指定了記錄檔組中重做記錄檔的數量，預設為2，innodb_mirrored_log_groups指定了日誌鏡像檔案組的數量，預設為1，代表只有一個記錄檔組，沒有鏡像；innodb_log_group_home_dir指定了記錄檔組所在路徑，預設在資料庫路徑下。      
       二進位日誌和重做日誌的區別：首先，二進位日誌會記錄所有與Mysql有關的會引起資料變化的日誌記錄，包括InnoDB、MyISAM、Heap等其他儲存引擎的日誌。而InnoDB儲存引擎重做日誌只儲存有關其本身的交易記錄；其次內容不同，不管將二進位記錄檔記錄的格式設為STATEMENT還是ROW，又或者是MIXED，其記錄的都是關於一個事務的具體操作內容。而InnoDB儲存引擎的重做記錄檔記錄的關於每個頁的更改的物理情況 。此外，寫入時間不同，二進位記錄檔是在事務提交前進行記錄的，而在事務進行的過程中，不斷有重做日誌條目被 寫入重做記錄檔中。

 

• InnoDB 表結構

  • 資料表空間：innodb儲存引擎邏輯結構的最高層

  • 段：資料表空間由各個段組成，如資料區段、索引段、復原段等

  • 區：64個連續的頁組成，每個頁16kb，即每個區1MB

  • 頁：每頁16kb,且不能更改，資料頁、undo頁、系統頁、交易資料頁、插入緩衝位元影像頁、插入緩衝空閑列表頁、未壓縮的二進位大對象頁、壓縮的二進位大對象頁

  • 行：innodb儲存引擎是面向行的（row-oriented),每頁最多允許存放7992行資料

  • 行記錄格式：常見兩種行記錄格式Compact和Redundant，mysql 5.1版本後，主要是Compact，此格式不管CHAR還是VARCHAR, null不佔儲存空間，對於Redudant, VARCHAR的null不佔空間，CHAR的null占儲存空間。

• 鎖：

  • 共用鎖定(S Lock)：允許事務讀一行資料；

  • 獨佔鎖定(X Lock)：允許事務刪除或者更新一行資料。          
         當一個事務已經獲得了行r的共用鎖定，那麼另外的事務可以立即獲得行r的共用鎖定，因為讀取沒有改變行r的資料，我們稱這種情況為鎖相容。但如果有事務想獲得行r的獨佔鎖定，則它必須等待事務釋允許存取r上的共用鎖定--這種情況稱為鎖不相容。

  • 在InnoDB Plugin之前，只能通過SHOW FULL PROCESSLIST，SHOW ENGINE INOODB STATUS等命令來查看當前的資料庫請求，然後再判斷當前事務中的鎖的情況。新版本的InnoDB Plugin中，在INFORMATION_SCHEMA架構下添加了INNODB_TRX、INNODB_LOCKS、InnoDB_LOCK_WAITS。通過這三張表，可以更簡單地監控當前的事務並分析可能存在的鎖的問題。

  • 一致性的非鎖定讀：InnoDB儲存引擎通過行多版本控制的方式來讀取當前執行時間資料庫中行的資料。如果讀取的行正在執行Delete、update操作，這時讀取操作不會因此而會等待行上鎖的釋放，相反，InnoDB儲存引擎會去讀取行的一個快照資料。快照資料是指該行之前版本的資料，該實現是通過Undo段來實現。而Undo用來事務中復原資料，因此快照本身是沒有額外開銷的。此外，快照資料是不需要上鎖的，因為沒有必要對曆史的資料進行修改。一個行可能有不止一個快照資料，所以稱這種技術為行多版本技術。由此帶來並發控制，稱之為多版本並發控制(Multi VersionConcurrency Control, MVCC)。

• 鎖的演算法：      
       Record Lock:單行記錄上的鎖      
       Gap Lock:間隙鎖，鎖定一個範圍，但不包含記錄本身      
       Next-Key Lock:Gap Lock + Record Lock，鎖定一個範圍，並且鎖定記錄本身。

• 鎖的問題：

  • 丟失更新：經典的資料庫問題，當兩個或多個事務選擇同一行，然後基於最初選定的值更新該行時，會發生丟失更新問題。每個事務都不知道其它事務的存在。最後的更新將重寫由其它事務所做的更新，這將導致資料丟失。          
             例:          
                 事務A和事務B同時修改某行的值，          
                  1.事務A將數值改為1並提交          
                  2.事務B將數值改為2並提交。          
                  這時資料的值為2，事務A所做的更新將會丟失。          
                  解決辦法：事務並行變串列操作，對更新操作加獨佔鎖定

  • 髒讀：一個事務讀到另一個事務未提交的更新資料，即讀到髒資料。          
              例:          
                  1.Mary的原工資為1000, 財務人員將Mary的工資改為了8000(但未提交事務)       
                  2.Mary讀取自己的工資 ,發現自己的工資變為了8000，歡天喜地！          
                  3.而財務發現操作有誤，復原了事務,Mary的工資又變為了1000, 像這樣,Mary記取的工資數8000是一個髒資料。          
                  解決辦法：髒讀只有在交易隔離等級是Read Uncommitted的情況下才會出現，innoDB預設隔離等級是Repeatable Read，所以生產環境下不會出現髒讀。

  • 不可重複讀取：在同一個事務中,多次讀取同一資料,返回的結果有所不同。換句話說就是,後續讀取可以讀到另一個事務已提交的更新資料。相反"可重複讀"在同一事務多次讀取資料時,能夠保證所讀資料一樣,也就是後續讀取不能讀到另一事務已提交的更新資料。髒讀和不可重複讀取的主要區別在於，髒讀是讀到未提交的資料，不可重複讀取是讀到已提交的資料。          
              例:          
                  1.在事務1中，Mary 讀取了自己的工資為1000,操作並沒有完成          
                  2.在事務2中，這時財務人員修改了Mary的工資為2000,並提交了事務.          
                  3.在事務1中，Mary 再次讀取自己的工資時，工資變為了2000          
                  解決辦法：讀到已提交的資料，一般資料庫是可接受的，因此交易隔離等級一般設為Read Committed。Mysql InnoDB通過Next-Key Lock演算法避免不可重複讀取，預設隔離等級為Repeatable Read。

 

• 事務的隔離等級：Read uncommitted、Read committed、Repeatable read、serializable。在Read Committed和Repeatable Read下，InnoDB儲存引擎使用非鎖定一致性讀。然而，對於快照的定義卻不同。在Read Committed交易隔離等級下，對於快照資料，非一致性讀總是讀取被鎖定行的最新一份快照資料。在Repeatable交易隔離等級下，對於快照資料，非一致性讀總是讀取事務開始時的行資料版本。  

 

 

 

 

• 
  

 

 

   

本文出自 “不懂IT的中醫不是好IT” 部落格，請務必保留此出處http://zhishen.blog.51cto.com/1612050/1531580