Linux:MySQL（七）-資料庫存儲引擎

MySQL體系結構

存儲引擎

InnoDB support for FULLTEXT indexes is available in MySQL 5.6.4 and later.

存儲引擎比較：https://docs.oracle.com/cd/E17952_01/mysql-5.5-en/storage-engines.html

一、MyISAM引擎特點：

不支援事務
表級鎖定
讀寫互相阻塞，寫入不能讀，讀時不能寫
隻緩存索引
不支援外鍵限制
不支援聚簇索引
讀取資料較快，占用資源較少
不支援MVCC（多版本并發控制機制）高并發
崩潰恢複性較差
MySQL5.5.5前預設的資料庫引擎
           

• MyISAM存儲引擎适用場景

  隻讀（或者寫較少）、表較小（可以接受長時間進行修複操作）

• MyISAM引擎檔案

  tbl_name.frm 表格式定義

  tbl_name.MYD 資料檔案

  tbl_name.MYI 索引檔案

二、 InnoDB引擎特點:

行級鎖
支援事務，适合處理大量短期事務 保持資料完整性，更嚴謹
讀寫阻塞與事務隔離級别相關
可緩存資料和索引
支援聚簇索引
崩潰恢複性更好
支援MVCC高并發 避免髒資料，查到記錄的instert 事務編号比目前的事務編号小（早），delete事務編号要大（晚）
從MySQL5.5後支援全文索引
從MySQL5.5.5開始為預設的資料庫引擎
           

• 老版本修改預設引擎為innoDB,建立的新表為innoDB不影響舊表

  在mysqld資料塊下添加

vim /etc/my.cnf
[mysqld]
defaut-storage-engine=INNODB

systemctl restart mysqld
service mysqld restart

show engines;
           

• InnoDB資料庫檔案

  所有InnoDB表的資料和索引放置于同一個表空間中

  表空間檔案：datadir定義的目錄下 預設/var/lib/mysql

  資料檔案：ibddata1, ibddata2, …

• 每個表單獨使用一個表空間存儲表的資料和索引

  啟用：innodb_file_per_table=ON myslqd語句塊下

  參看：https://mariadb.com/kb/en/library/xtradbinnodb-server-system-variables/#innodb_file_per_table

  ON (>= MariaDB 5.5)

  5.5版本以後自動開啟了

  兩類檔案放在資料庫獨立目錄中

  資料檔案(存儲資料和索引)：tb_name.ibd

  表格式定義：tb_name.frm

其它存儲引擎

• Performance_Schema：Performance_Schema資料庫使用
• Memory ：将所有資料存儲在RAM中，以便在需要快速查找參考和其他類似資料的環境中進行快速通路。适用存放臨時資料。引擎以前被稱為HEAP引擎
• MRG_MyISAM：使MySQL DBA或開發人員能夠對一系列相同的MyISAM表進行邏輯分組，并将它們作為一個對象引用。适用于VLDB(Very Large Data Base)環境，如資料倉庫
• Archive ：為存儲和檢索大量很少參考的存檔或安全稽核資訊，隻支援SELECT和INSERT操作；支援行級鎖和專用緩存區
• Federated聯合：用于通路其它遠端MySQL伺服器一個代理，它通過建立一個到遠端MySQL伺服器的用戶端連接配接，并将查詢傳輸到遠端伺服器執行，而後完成資料存取，提供連結單獨MySQL伺服器的能力，以便從多個實體伺服器建立一個邏輯資料庫。非常适合分布式或資料集市環境
• BDB：可替代InnoDB的事務引擎，支援COMMIT、ROLLBACK和其他事務特性
• Cluster/NDB：MySQL的簇式資料庫引擎，尤其适合于具有高性能查找要求的應用程式，這類查找需求還要求具有最高的正常工作時間和可用性
• CSV：CSV存儲引擎使用逗号分隔值格式将資料存儲在文本檔案中。可以使用CSV引擎以CSV格式導入和導出其他軟體和應用程式之間的資料交換

• BLACKHOLE ：黑洞存儲引擎接受但不存儲資料，檢索總是傳回一個空集。該功能可用于分布式資料庫設計，資料自動複制，但不是本地存儲

  用于主從複制，加速性能 A傳給A1，A1再同步給其他資料庫，可加速傳導。

• example：“stub”引擎，它什麼都不做。可以使用此引擎建立表，但不能将資料存儲在其中或從中檢索。目的是作為例子來說明如何開始編寫新的存儲引擎
• MariaDB支援的其它存儲引擎：
  • OQGraph
  • SphinxSE
  • TokuDB
  • Cassandra
  • CONNECT
  • SQUENCE

管理存儲引擎

• 檢視mysql支援的存儲引擎

  show engines;

• 檢視目前預設的存儲引擎

  show variables like ‘%storage_engine%’;

• 設定預設的存儲引擎

  vim /etc/my.conf

  [mysqld]

  default_storage_engine= InnoDB

• 檢視庫中所有表使用的存儲引擎

  show table status from db_name;

• 檢視庫中指定表的存儲引擎

  show table status like ’ tb_name ';

  show create table tb_name;

• 設定表的存儲引擎：

  CREATE TABLE tb_name(… ) ENGINE=InnoDB;

  ALTER TABLE tb_name ENGINE=InnoDB;

MySQL中的系統資料庫

• mysql資料庫

  是mysql的核心資料庫，類似于Sql Server中的master庫，主要負責存儲資料庫的使用者、權限設定、關鍵字等mysql自己需要使用的控制和管理資訊

• performance_schema資料庫

  MySQL 5.5開始新增的資料庫，主要用于收集資料庫伺服器性能參數,庫裡表的存儲引擎均為PERFORMANCE_SCHEMA，使用者不能建立存儲引擎為PERFORMANCE_SCHEMA的表

• information_schema資料庫

  MySQL 5.0之後産生的，一個虛拟資料庫，實體上并不存在information_schema資料庫類似與“資料字典”，提供了通路資料庫中繼資料的方式，即資料的資料。比如資料庫名或表名，列類型，通路權限（更加細化的通路方式）