Ubuntu Server 18.04安裝、認識和使用Mysql 5.7數據庫

Ubuntu Server 18.04安裝、認識和使用Mysql 5.7數據庫

一、在線安裝mysql數據庫

ubuntu安裝mysql，對於可以聯網的ubuntu主機或虛擬機，可以在線安裝。

首先登錄ubuntu的操作系統安裝用戶，使用sudo命令轉換到root用戶安裝mysql。

sudo apt install mysql-server

sudo apt install mysql-client

sudo apt install libmysqlclient-dev

安裝完成後可以通過下面的命令測試是否安裝成功：

sudo netstat -tap | grep mysql

出現如下圖1所示的信息，說明mysql的tcp監聽已經啟動，可以接受服務請求，數據庫服務器安裝成功。

圖1：mysql服務的tcp監聽

首次安裝後可以通過如下命令進入mysql服務：$Sudo mysql -uroot –p回車輸入當前具備sudo權限的操作系統口令，要求輸入數據庫的root口令時，直接回車，即可進入mysql數據庫。 如下圖2所示表示安裝成功。

圖2：成功登錄mysql數據庫，說明安裝成功

卸載mysql數據庫的步驟

1 sudo apt autoremove --purge mysql-server 刪除mysql-server軟件抱

2 sudo apt remove mysql-server

3 sudo apt autoremove mysql-server

4 sudo apt remove mysql-common

5 dpkg -l |grep ^rc|awk '{print $2}' |sudo xargs dpkg –P 清理殘留數據

二、Mysql默認安裝的目錄結構

使用apt在線安裝mysql數據庫，不需要指定安裝目錄，安裝的軟件和創建的數據庫到底在ubuntu操作系統的什麼位置呢？

切換到root用戶下，使用find ./ -name mysql –print 命令，可以查找Ubuntu主機上存在的名字為mysql的文件或文件夾，可以瞭解mysql大致的安裝位置。結果如下圖圖3。

圖3：使用find命令查找名字為mysql的文件或文件夾

經分析和查詢相關資料，apt安裝的mysql服務器的目錄結構大致如下表表一所示。

表一：Mysql主要目錄及主要作用

表一：Mysql主要目錄及主要作用

下面截圖說明上述目錄包含的文件、目錄和主要功能。

/var/lib/mysql/ 數據庫存放目錄見下圖4，其中mysql、performance_schema、sys是文件夾，各文件下有對應該數據庫的數據庫對象文件，手工創建的數據庫對應的文件目錄也在此處。

圖4：mysql數據庫的數據目錄

/usr/share/mysql：字符集相關配置文件的存放位置，通過文件名或目錄名，可以初步瞭解該目錄的主要用途，有字符集，有不同國家的語言的文件等等。進入子目錄可以看到更詳細的信息。另外，眾多的sql腳本完成數據庫server的安裝和配置。

圖5：存放mysql字符集等相關信息的位置

/usr/bin：相關命令的存放目錄，下圖6是篩選出的mysql開頭的命令，這些命令多數都是mysql數據庫的管理命令，該目錄下還有很多操作系統級的命令。

圖6：mysql的部分命令的存放位置

/etc/mysql目錄： 數據庫配置文件，如my.cnf等，後面要用到的配置mysql數據庫是否允許遠程訪問的配置信息就在/etc/mysql/mysql.conf.d/mysqld.cnf文件中，裡面綁定了本機地址，使root用戶只能在本地連接數據庫。目錄結構見下圖圖7.

圖7：/etc/mysql的目錄結構

/var/run/mysqld 目錄：/var/run 目錄中存放的是自系統啟動以來描系統信息的文件，msqld目錄下的文件用於描述mysqld服務器程序的pid和相關鎖定的信息，是mysql數據庫運行期間使用的系統信息。目錄結構見圖8

圖8：mysqld服務運行時的信息描述文件的位置

三、mysql自帶的4個數據庫

系統初始安裝後生成了4個數據庫和4個數據庫用戶，4個數據庫用戶中root用戶的authentication_string的訪問密碼串為空。下圖9展示了通過root用戶進入數據庫後查詢到的已經安裝生成的4個數據庫schema和4個數據庫用戶。

圖9：mysql數據庫初始安裝生成4個數據庫和4個數據庫用戶

Mysql.user中authentication_string是加密過的密碼，例如數據庫用戶debian-sys-maint的密碼明文在debian.cnf文件中。如下圖10所示。

圖10：數據庫用戶debian-sys-maint的配置文件

可以使用debian-sys-maint的用戶名和密碼登錄mysql數據庫，如下圖11所示。可以使用該用戶緊急登錄數據庫，進行一些非常規的操作。

圖11：dibian-sys-maint用戶登錄mysql數據庫

如圖2所示，mysql默認安裝後，系統生成了4個數據庫：information_schema、mysql、performance_schema和sys。

Information_schema:存儲了數據庫的元數據，類似oracle的sys和system用戶存儲的數據字典的信息，例如有哪些數據庫對象、有哪些表、每個表有哪些列、列的數據類型、列的主外鍵約束、schema權限、user權限、table權限、column權限、索引、事務控制機制、字符集等等。

Performance_schema：提供了進程等待的詳細信息，包括鎖等待等；存儲記錄了數據庫運行的歷史性能參數；為判斷和優化數據庫性能提供幫助。類似oracle數據庫中sys和system用戶下的一些系統運行數據。

Sys schema：包含了一系列對象，主要是視圖、存儲過程等等，用來輔助解決性能問題和系統故障。這些視圖或存儲過程讀取information_schem和performance_schema的數據，生成便於查找性能瓶頸和系統故障的數據報告。

Mysql schema：是mysql的核心數據庫，最接近使用mysql數據庫開發應用系統的人員，類似於sql server中的master表，主要負責存儲數據庫的用戶、權限設置、關鍵字等mysql自己需要使用的控制和管理信息

Mysql的每一個schema是一個邏輯數據庫，裡面有表、視圖、存儲過程、索引等等數據庫對象。上述4個數據庫都是系統級數據庫，創建具體應用系統需要創建自己的數據庫schema。

四、mysql自帶的4個數據庫用戶user

如上圖圖8所示，mysql默認安裝後，生成了4個數據庫用戶：root、dibian-sys-maint、mysql.session、mysql.sys。

：用於 sys schema中對象的定義。該用戶mysql系統自己使用，已被鎖定，客戶端無法連接。

：插件內部使用來訪問服務器。該用戶已被鎖定，客戶端無法連接。

root@localhost：這個就是mysql數據庫的root賬戶（用戶），該用戶擁有所有權限，可執行任何操作，是MySQL的特權賬號，使用或擴散使用該賬戶存在安全隱患。從圖8中可以看到這個賬號沒有密碼，故在localhost上登錄mysql不需要密碼。

Dibian-sys-maint@host：debian-sys-maint用戶是mysql安裝在dibian和buntu服務器上才會創建的數據庫用戶，是個Mysql安裝之後自帶的用戶，用於MySQL的非常規維護。

上述4個數據庫用戶是系統自帶的，對於應用開發來說，要根據需要創建自己的數據庫用戶，在應用創建的數據庫schema上授予合適的權限，才能保證系統安全運行。

五、解除本機訪問綁定，允許遠程訪問

使用apt在線安裝mysql後， mysql數據庫默認綁定了本機地址，bind-address = 127.0.0.1，只有本機可以訪問。

這樣遠程主機不管是Mysql Workbench、navicat、toad、pl/sql等數據庫客戶端管理工具，還是編寫服務遠程連接使用mysql數據庫，都無法獲得數據庫連接。

做為mysql數據庫服務器，必須要要能讓其他主機訪問才有意義，如果要允許mysql被遠程訪問，需要修改文件/etc/mysql/mysql.conf.d/mysqld.cnf：

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

註釋掉bind-address = 127.0.0.1：則mysql數據庫解除和本機的綁定，其他主機也可以訪問該數據庫 ，見下圖12所示.

圖12：mysqld.cnf定義了很多參數，包括地址綁定參數

六、嘗試在其他主機上使用root用戶登錄mysql數據庫

如上圖圖8所示，在localhost上使用root數據庫用戶連接數據庫時，不需要密碼，可以直接回車連接到數據庫中。要想在非localhost使用root連接數據庫時，除了上面提到的屏蔽bind-address以外，還必須進行下面的配置。

如下圖圖13所示，192.168.1.104使用apt安裝mysql後，使用grant all on *.* to root@'%' identified by 'root' with grant option，設置root用戶的密碼為root，可以連接的主機為%，表示所有主機都可以使用root用戶及root用戶的密碼root來連接192.168.1.104上的mysql數據庫。

從下圖12可以看出，上述命令執行後，在user用戶表中增加了一條用戶為root的記錄，該記錄的host字段值為%。該表的主鍵索引為host+user。

當然也可以Sql>grant all on *.* to root@'192.168.1.100' identified by '123456' with grant option；表示192.168.1.100可以使用root用戶連接數據庫，連接密碼為123456.

Sql>flush privileges; 刷新數據庫授權，將新的數據加載到內存中，使該訪問權限和密碼生效。重啟數據庫服務也能達到這樣的效果，sudo service mysql restart，

修改root賬號的設置後，可以使用flush privileges生效，可以不重啟mysql服務，避免重啟不成功的風險。該命令本質是將user和privilege表中的用戶信息和權限信息從mysql數據庫的內置庫（mysql）中提取到內存中。但好象重啟服務更好使。

圖13：設置root密碼及可連接的主機

下圖圖14是192.168.1.102做為客戶端訪問192.168.1.104上的mysql數據庫服務的示意圖。192.168.1.104上的mysql剛默認安裝完成，或者僅僅解除了本機訪問的綁定限制後，192.168.1.102訪問192.168.1.104上的mysql是被拒絕的。

在192.168.1.104上的數據庫mysql中執行grant all on *.* to root@'%' identified by 'root' ，使用flush 或重啟mysql服務（見圖6所示）後，192.168.1.102做為客戶端使用root/root連接192.168.1.104的mysql數據庫連接成功。root數據庫賬戶要這樣操作，其他數據庫賬戶要遠程操作也需要配置主機權限。

圖14：192.168.1.102做為客戶端訪問192.168.1.104的數據庫服務端

七、Mysql的字符集

Mysql數據庫支持40多種字符集，如下圖圖15所示。每種字符集都有默認的collation，即核對規則。

字符集確定了字符的編碼和存儲方式，核對規則規定了字符排序和比較的標準。例如在mysql數據庫中有VARCHAR、CHAR、TEXT等字符類的數據類型，對於字符串的比較和排序都依據該核對規則。簡單來說，設定不同的collation對於數據庫SQL語句的order by、group by、having、distinct都會產生影響。對字符類型的列創建索引時，也會影響索引的數據存儲，只是我們感覺不到。凡是涉及到字符類型比較或排序的地方，都會和COLLATE有關。

圖15：mysql數據庫支持的字符集和每種字符集默認的collation核對規則

字符集和核對規則collation是一對多的關係，每種字符集可以有多種collation核對規則提供選擇，見下圖16所示。

例如如果使用gb2312字符集，沒有顯示指定collation，則默認使用gb2312_chinese_ci做為核對規則。

圖16：每種字符集可以有多種collation，是一對多的關係

字符集是一套文字符號的編碼、比較規則的集合。 目前支持中文的常用的字符集有UTF-8、GBK、GB2312等，MYSQL服務器支持多種字符集和collation，在同一臺服務器、同一個數據庫或者同一個數據表中的不同字段都可以使用不同的字符集，從而也使用對應的collation。

mysql的字符集和校對規則有四個級別的默認設置：服務器級、數據庫級、數據表級、字段級。它們分別在不同的地方設置，作用域也不相同 。

對於實際的應用程序來說，還存在客戶端和服務器之間交互的字符集和核對規則的設置，這就是連接字符集和它的核對校對規則，例如http協議頭部的相關編碼信息等。

如果沒有設置核對規則，那麼mysql會使用當前字符集的默認核對規則；同樣，如果僅僅設置了核對規則而沒有指定字符集，那麼mysql會使用與設置的核對規則相匹配的字符集 。

八、mysql的存儲引擎

存儲引擎是數據庫管理系統創建、讀取和更新數據的軟件模塊。不同的存儲引擎提供不同的存儲機制、索引技巧、鎖定水平等功能，使用不同的存儲引擎還可以獲得特定的功能。現在許多數據庫管理系統都支持多種不同的存儲引擎。

下圖圖17是mysql5.7支持的存儲引擎，分為兩類：事務性和非事務性。

在 MySQL 中，不需要在整個服務器中使用同一種存儲引擎，針對具體的要求，可以對每一個表使用不同的存儲引擎。

圖17：mysql5.7支持的存儲引擎

MySQL 5.7默認的存儲引擎是InnoDB，是事務性的存儲引擎，能夠保證數據的完整性和一致性。當前默認的存儲引擎是全局於mysql服務器的，對mysql服務器下的數據庫都有效。

SQL>show variables like '%storage_engine%; 則列出系統變量名中含有'strorage_engine'的系統變量和對應的值，從下圖18可以看出MYSQL服務器當前默認的存儲引擎。

圖18：mysql服務器當前默認的存儲引擎

InnoDB是一種支持事務的引擎。所有的數據存儲在一個或者多個數據文件中，支持類似於Oracle的鎖機制。一般在OLTP應用中使用較廣泛。安裝mysql服務器時，在mysql數據目錄下創建一個名為ibdata1的自動擴展數據文件，以及兩個名為ib_logfile0和ib_logfile1的日誌文件。ibdata1為10m大小的自動擴展數據文件，ib_logfile0和 ib_logfile1為5MB大小的日誌文件，這些文件構成mysql服務器級別的全局數據表空間，在參數InnoDB_file_per_table為OFF時，新建的表會存儲在該數據文件中。

MyISAM是一種非事務性的引擎，提供高速存儲和檢索，以及全文搜索能力，適合數據倉庫等查詢頻繁的應用。MyISAM中，一個table實際保存為三個文件，.frm存儲表定義，.MYD存儲數據,.MYI存儲索引。不支持事務。

創建table時可以通過engine關鍵字指定使用的存儲引擎，如果省略則使用系統默認的存儲引擎：CREATE TABLE t (i INT) ENGINE = MyISAM;

InnoDB可以將它的表和索引放在一個邏輯表空間中， MyISAM表中每個表被存放在分離的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制為2GB的操作系統上

InnoDB表/MyISAM的表修改存儲引擎為MyISAM/InnoDB時，修改表的存儲引擎會引起數據文件的搬家。

Memory存儲引擎的每個表對應一個磁盤文件，文件的文件名與表名相同，類型為frm類型。該文件中只存儲表的結構。而其數據文件，都是存儲在內存中，這樣有利於數據的快速處理，提高整個表的效率。值得注意的是，服務器需要有足夠的內存來維持MEMORY存儲引擎的表的使用。如果不需要了，可以釋放內存，甚至刪除不需要的表。

Memory存儲引擎的表的數據存儲在內存中，如果內存出現異常就會影響數據。如果重啟或者關機，所有數據都會消失。因此，基於MEMORY的表的生命週期很短，一般是一次性的。MEMORY表在所由客戶端之間共享（就像其他任何非TEMPORARY表）

存儲引擎的選擇：同一個數據庫也可以使用多種存儲引擎的表。如果一個表要求比較高的事務處理，可以選擇InnoDB；對查詢要求比較高的表選擇MyISAM存儲；如果該數據庫需要一個用於查詢的臨時表，可以選擇MEMORY存儲引擎。

九、為應用創建數據庫

CREATE DATABASE [IF NOT EXISTS] [ [DEFAULT] CHARACTER SET [=] ] [ [DEFAULT] COLLATE [=] ]

創建數據庫時指定數據庫名，可以指定字符集，可以指定字符集的核對規則，沒有其他選項。

mysql>create database if not exists mycrmdb default character set =utf8mb4 default collate=utf8mb4_general_ci;

從下圖可以看到，創建mycrmdb數據庫後，在MySQL的數據庫目錄中增加了一個目錄名mycrmdb，該目錄名和創建的數據庫同名，故創建數據庫時指定的數據庫名稱要服務操作系統目錄名稱的規定

新創建mycrmdb數據庫後，在mysql的數據庫目錄mycrmdb中增加了一個文件：db.opt，打開db.opt後，發現裡面只有存儲著創建數據庫時指定的字符集和核對規則信息，沒有其他信息。見下圖圖19所示。

圖19：創建數據庫後在數據主目錄下創建了一個和創建的數據庫同名的子目錄做為存儲基點

十、為應用創建數據庫用戶

可以使用 CREATE USER 語句來創建MySQL 賬戶（用戶），並設置相應的口令，可以使用CREATE USER [ IDENTIFIED ] BY [ PASSWORD ] 這樣簡單的語法創建數據庫用戶。例如，create user mycrmaccount identified by 'mycrmaccount'，這樣創建的賬號沒有分配權限。創建數據庫用戶的命令參數比較複雜，可以使用圖形界面的客戶端工具創建用戶，這樣比較方便。

下面使用mysql workbench客戶端創建一個名為mycrmaccount賬號。具體創建過程如下：

第一步：見下圖圖20，設置賬戶名稱、密碼和可以登錄的主機，登錄的主機中%是通配符。

圖20：設置數據庫賬戶名稱、密碼和可以登錄的主機

第二步：見下圖圖21，設置該數據庫賬戶登錄數據庫的次數、查詢的次數、更新的次數及該賬戶可以並行登錄數據庫的數量進行限制。

圖21：數據庫賬戶使用數據庫的頻次限制

第三步：見下圖圖22，設置該數據庫賬戶的數據庫對象的權限。

圖22：數據庫賬戶的數據庫對象的權限設置

第四步：見下圖圖23，為數據庫賬戶綁定數據庫schema。

圖23：為數據庫賬戶綁定數據庫schema

第五步：見下圖圖24，設定數據庫賬戶對數據庫schema的數據庫對象權限。

圖24：設定數據庫賬戶對數據庫schema的數據庫對象權限

經過上述配置，mycrmaccount賬戶就配置完成了，可以正常使用了。

十一、配置mysql操作系統用戶

由表一 "Mysql主要目錄及主要作用" 可以看出，mysql數據庫安裝後形成的很多目錄和文件的屬主和屬組都是mysql，所以有必要設置mysql用戶，以便登錄操作系統，查看、使用和修改相關文件。

可以使用usermod命令和passwd命令配置操作系統用戶。

如下圖所示，安裝mysql數據庫後，在passwd文件中增加了mysql一行，mysql用戶的主目錄位置為/nonexistent，用戶沒有配置密碼和主目錄，無法登錄主機的操作系統。通過usermod命令或者直接編輯配置文件，可以設置用戶的主目錄、shell等內容，如下圖圖25所示。

圖25：修改mysql用戶的主目錄和shell

shadow文件存儲操作系統用戶的用戶密碼，初始apt install 安裝mysql後，在shadow文件中增加了mysql一行，但沒有設置密碼。如下圖圖26所示，方框中的內容是使用#passwd mysql為mysql用戶設置密碼後形成的密碼密文。

圖26：設置操作系統賬戶的密碼

有了賬戶、密碼、主目錄、shell後，賬戶myql就可以正常登錄了。

另，登錄到mysql操作系統用戶後，有時想執行sudo操作，可以將mysql用戶加入到sudoers文件中，也可以使用usermod -aG sudo mysql的命令使mysql具備sudo權限。直接在/etc/sudoers文件中加入最好。mysql ALL=(ALL:ALL),當然實際在用的時候不要使用ALL，允許它使用啥就配置啥。如下圖27所示。

圖27：在sudoers文件中配置用戶sudo權限

上述設置完成後，可以在Ubuntu本機直接登錄操作系統，並能使用sudo命令了。

usermod命令的用法如下：

usermod [-LU][-c ][-d ][-e ][-f ][-g ][-G ][-l ][-s <shell>][-u ][用戶帳號]/<shell>

-c 　修改用戶帳號的備註文字。

-d登入目錄> 　修改用戶登入時的目錄。

-e 　修改帳號的有效期限。

-f 　修改在密碼過期後多少天即關閉該帳號。

-g 　修改用戶所屬的群組。

-G 　修改用戶所屬的附加群組。

-l 　修改用戶帳號名稱。

-L 　鎖定用戶密碼，使密碼無效。

-s<shell> 　修改用戶登入後所使用的shell。/<shell>

-u 　修改用戶ID。

-U 　解除密碼鎖定。

十二、認識Mysql的數據文件

表二：mysql的主要數據文件說明

表二：mysql的主要數據文件說明

先說說系統表空間：ibdata1是Mysql服務器innoDB存儲引擎使用的全局數據文件，又稱為系統表空間。系統表空間存儲數據的數據文件是ibdata1,用於控制事務處理的文件是：ib_logfile0、ib_logfile1、ibtmp1。ibdata1是一個自擴展的文件，系統表空間在Mysql服務器中只有一份,存儲innoDB存儲類型的表和索引.

在Mysql5.5.7-5.6.6版本之間，所有innoDB類型的數據庫的表和索引都存儲在系統表空間ibdata1中，

在Mysql5.7中用戶建立的表默認建在獨立的表空間文件中，位於數據庫目錄下，後綴名ibd。當然也可以指定參數使用系統表空間。mysql服務器系統表空間見下圖圖28所示。

圖28：mysql服務器數據主目錄下的數據文件

再說說獨立表空間：innoDB形式的表，也可以使用獨立的表空間存儲數據和索引，一張表對應兩個文件：表名.frm 和表名.ibd.

在上面新創建的數據庫mycrmdb sechema中建立innoDB類型的表emp，由於Mysql5.7默認使用獨立表空間，故emp表的數據和索引都存儲在mycrmdb/emp.ibd中，表結構存儲在mycrmdb/emp.frm中。使用shell腳本給emp加載大約40多萬條數據，發現獨立表空間的數據文件不斷增大，系統表空間的文件一直不會變化，驗證了Mysql5.7的innoDB的表默認使用獨立表空間，如下圖圖29所示。

圖29：驗證獨立表空間插入數據後數據文件的變化

InnoDB和MyISAM存儲類型的相互轉換。在滿足一定的條件下，可以修改表的存儲引擎。例如 ALTER TABLE emp ENGINE = MyISAM ，則可以將emp表的innoDB存儲類型改成MyISAM類型，瞬間完成。將原來的數據和索引從emp.ibd文件拆到emp.MYD和emp.MYI兩個文件中，拆出後兩個文件大小之和比原來的文件要小不少呢，見下圖圖30所示。

圖30：InnoDB變為MyISAM類型時，數據文件的變化

再嘗試將改到MyISAM類型的表改回InnoDB類型，ALTER TABLE `mycrmdb`.`emp` ENGINE = InnoDB ，則發現原來的emp.MYI和emp.MYD文件不見了，新建了一個emp.ibd文件接收了原來的數據和索引，見下圖圖31所示。

圖31：M有ISAM類型改回InnoDB類型，數據文件的變化

InnoDB類型的錶轉變成MEMORY型的表：ALTER TABLE emp EGINE = MEMORY ;

alter table emp engine=MEMORY,將innoDB類型的emp表給成內存表後，存儲數據和索引的emp.ibd文件沒有了，但任何客戶端還是可以查詢到修改前的數據的，因為修改後將數據和索引放到了內存中，數據文件刪掉了，見下圖圖32所示。

圖32：InnoDB變為MEMORY類型，數據文件的變化

再將內存錶轉換成InnoDB型的表，可以轉換，但內存表的數據和索引不會帶回到InnoDB表中。例如 ALTER TABLE `mycrmdb`.`emp` ENGINE = InnoDB ; 將MEMORY類型的表改成InnoDB類型的表，這時內存中的數據不會帶過來。

使用獨立表空間的表，建表、插入數據、truncate 數據，都能很明顯的從數據文件中看出變化。系統表空間中存儲了多個數據庫用戶的多張表和多個索引，可以驗證一下系統表空間文件的變化情況。

SQL>set global innodb_file_per_table=off; 關閉獨立表空間參數，採用系統表空間存儲表的數據和索引。

上面將MEMORY型的emp表修改成了InnoDB型的表，向emp表插入90萬條數據，看看系統表空間文件的變化。

ibdata1表空間文件確實增大了，表空間文件每次擴展應該都有一個固定的值，新插入的數據放到擴展的空間中，擴展空間沒有用滿時，新插入數據不會導致表空間文件繼續擴展。如下圖圖33所示。

圖33：開啟使用共享的系統表空間後，插入數據，觀察系統表空間數據文件的變化

SQL.>set global innodb_file_per_table=on/off指令，只在當前數據庫server運行期間有效，重啟數據庫server或者重啟主機後，原來的設置即失效。例如Mysql5.7默認innodb_file_per_table=on,如果通過set命令設置為off，則系統重啟後會自動設置該參數為on，仍然使用表級的獨立表空間文件。如下圖，使用set命令修改的參數都是臨時生效的，不是永久的，下圖圖34是查詢和設置innodb_file_per_table參數的數據庫命令。

圖34：查詢和設置innodb_file_per_table參數

若想長期有效，可以在配置文件中設置。mysql5.7的/etc/mysql/mysql.conf.d/mysqld.cnf文件中增加一行，通過配置文件配置innodb_file_per_table參數，如下圖圖35所示。

圖35：在mysqld.cnf配置文件中設置全局變量innodb_file_per_table

修改innodb_file_per_table 參數不影響已經存在的表，已經存在的表原來數據和索引在系統表空間ibdata1中的，新更新的數據還在ibdata1中，原來再獨立表空間表名.ibd的，更新的數據孩在表名.ibd文件中。這樣一個數據庫中的InnoDB類型的表，有的可能在系統表空間中，有的可能在獨立的表空間中，跨系統表空間和獨立表空間，可以做多表聯合查詢。

truncate table 時，發現使用獨立表空間的數據文件會回收，數據文件體積變小，但使用全局的系統表空間在truncate talbe後不會變小。delete from table 獨立表空間也不回收。

十三、實際應用中表和存儲引擎的選擇

一般聯機事務處理OLTP應用程序，還是老老實實使用InnoDB類型的表，保證事務的完整性和一致性；數據文件建議使用Mysql5.7的默認方式，使用獨立的表空間，即一個表一套數據文件。

基於Mysql提供的特性，出於測試、驗證的目的，可按下面的規劃構建系統，進行測試驗證。

1. 應用初始化時，裝載靜態參數文件到普通的內存表，供應用的各個不同的用戶連接查詢使用，僅供查詢，相當於將一個表cach到內存中了，提高數據的存取速度。普通的內存表是使用如下的命令建立的： CREATE TABLE (…) ENGINE=MEMORY

2. 經常使用查詢、插入、修改、刪除，需要嚴格保證數據一致性的表，採用InnoDB存儲引擎，使用innodb_file_per_table=ON的參數，使用獨立的表空間。

3. 對於僅存在插入和查詢的流水錶，可以嘗試使用MyISAM類型的表。

4. 對於數據操作過程中，可以使用局部於會話連接的臨時表，臨時表可以是MEMORY形式，也可以是其他的形式，例如打印報表時可以使用臨時表暫存臨時彙總數據，暫存打印結果數據。會話結束後臨時表自動消亡。臨時表是使用CREATE TEMPORY TABLE建立的表。

閱讀更多 IT小小屋 的文章

關鍵字: 軟件 安裝 數據庫