innodb_data_file_path和innodb_data_home_dir
今天研究下關(guān)于
mysql數(shù)據(jù)的相關(guān)參數(shù)以及innodb引擎下的共享表空間和獨(dú)立表空間介紹，參數(shù)主要包括innodb_data_file_path、innodb_data_home_dir、innodb_buffer_pool_size、innodb_buffer_pool_instances四個(gè)參數(shù)。
一：首先介紹innodb_buffer_pool_size
我們知道m(xù)ysql數(shù)據(jù)庫(kù)在操作數(shù)據(jù)頁(yè)的時(shí)候，會(huì)先把數(shù)據(jù)頁(yè)pin到內(nèi)存中，之后再做相關(guān)的處理，那么mysql可以使用的mysql的內(nèi)存大小就是由innodb_buffer_pool_size參數(shù)控制的，這個(gè)參數(shù)主要作用是緩存innodb表的索引，數(shù)據(jù)，插入數(shù)據(jù)時(shí)的緩沖。在高并發(fā)高I/O時(shí)正確的配置非常重要，可能帶來(lái)很大的性能提升，這是InnoDB最重要的設(shè)置，對(duì)InnoDB性能有決定性的影響。默認(rèn)的設(shè)置只有8M，所以默認(rèn)的數(shù)據(jù)庫(kù)設(shè)置下面InnoDB性能很差。在只有 InnoDB存儲(chǔ)引擎的數(shù)據(jù)庫(kù)
服務(wù)器上面，可以設(shè)置60-80%的內(nèi)存。這個(gè)參數(shù)是非動(dòng)態(tài)的，要修改這個(gè)值，需要重啟mysqld服務(wù)
# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
# row data. The bigger you set this the less disk I/O is needed to
# access data in tables. On a dedicated database server you may set this
# parameter up to 80% of the machine physical memory size. Do not set it
# too large, though, because competition of the physical memory may
# cause paging in the operating system. Note that on 32bit systems you
# might be limited to 2-3.5G of user level memory per process, so do not
# set it too high.
#innodb，不像myisam，使用緩沖池來(lái)緩存索引和
#行數(shù)據(jù)。您設(shè)置的越大，那么訪問(wèn)表中的數(shù)據(jù)所需的磁盤I /o就越少。
#在專用的數(shù)據(jù)庫(kù)服務(wù)器上，您可以設(shè)置這個(gè)
#參數(shù)最多80%的計(jì)算機(jī)物理內(nèi)存大小。不要設(shè)置它
#太大了，因?yàn)槲锢韮?nèi)存的競(jìng)爭(zhēng)可能
#導(dǎo)致操作系統(tǒng)中的分頁(yè)。注意，在32位系統(tǒng)上
#可能僅限于每個(gè)進(jìn)程的2-3.5g用戶級(jí)內(nèi)存，所以不要
把它定得太高了。
二：innodb_data_home_dir和innodb_data_file_path參數(shù)
innodb_data_file_path用來(lái)指定innodb 共享 表空間文件，如果我們不在My.cnf文件中指定innodb_data_home_dir和innodb_data_file_path那么默認(rèn)會(huì)在datadir目錄下創(chuàng)建ibdata1 作為innodb tablespace。
雖然不指定innodb_data_home_dir和指定它為空顯示是一樣的，但是作用確不一樣，如果不指定那么所有的innodb tablespace 文件只能存放在datadir目錄下。如果顯示指定為空，則可以為innodb tablespace 文件指定不同的路徑
1. 在同一個(gè)路徑下指定多個(gè)innodb tablespace 文件
1)
在my.cnf文件指定如下
[mysqld]
innodb_data_file_path = ibdata1:1G;ibdata2:12M:autoextend:max:500M
note：The autoextend and max attributes can be used only for the last data file in the innodb_data_file_path line.
note：因?yàn)闆](méi)有指定innodb_data_home_dir，所以ibdata1和ibdata2都在datadir(innodb_data_home_dir默認(rèn)值為datadir)目錄下創(chuàng)建
2)
在my.cnf文件指定如下
[mysqld]
innodb_data_home_dir = /data/mysql/mysql3306/data
innodb_data_file_path = ibdata1:1G;ibdata2:12M:autoextend:max:500M
note：可以顯示的指定innodb tablespace文件的路徑，如果指定innodb_data_home_dir，那么就不能為innodb tablespace不同文件指定不同路徑
2. 在不同路徑下指定innodb tablespace
在my.cnf文件下指定
[mysqld]
innodb_data_home_dir =
innodb_data_file_path = ibdata1:12M;/data/mysql/mysql3306/data1/ibdata2:12M:autoextend
note：如果想為innodb tablespace指定不同目錄下的文件，必須指定innodb_data_home_dir =。這個(gè)例子中會(huì)在datadir下建立ibdata1，在/data/mysql/mysql3306/data1/目錄下創(chuàng)建ibdata2
如果我不指定innodb_data_home_dir =，只在my.cnf文件中做如下指定：
innodb_data_file_path =/data/data/ibdata1:18m;/data/data/1ibdata2:100m:autoextend:max:2000M
bogon:root@~>service mysqld start
Starting MySQL. ERROR! The server quit without updating PID file (/tmp/mysql.pid).
查看錯(cuò)誤日志。。。如下
bogon:root@/>tail -n 100 /data/log/error.log
171127 21:08:16 InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
InnoDB: File name .//data/data/ibdata1
InnoDB: File operation call: 'create'.
InnoDB: Cannot continue operation.
##所以如果想為innodb tablespace文件指定不同路徑，那么必須在my.cnf文件中指定innodb_data_home_dir =
注意：
1）設(shè)定文件大小時(shí)一定要注意你的OS是否有最大文件尺寸為2GB的限制！InnoDB是不會(huì)注意你的OS文件尺寸限制的， 在一些文件系統(tǒng)中你可能要設(shè)定最大容量限制：
innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:100M:autoextend:max:2000M
2）如果是修改共享表空間的文件個(gè)數(shù)，注意在描述原來(lái)文件的時(shí)候，一定要找到該文件，查看他當(dāng)前的實(shí)際大小，然后寫在innodb_data_file_path =哪個(gè)文件的后面。否則會(huì)報(bào)錯(cuò)。
三：innodb_buffer_pool_instances
innodb緩沖池被劃分為多個(gè)內(nèi)存緩沖池的數(shù)量。對(duì)于具有多GB范圍的緩沖池的系統(tǒng)，將緩沖池劃分為多個(gè)緩沖池可以提高并發(fā)性，減少對(duì)不同線程讀取和寫入到緩存時(shí)頁(yè)面的爭(zhēng)用。從緩沖池中存儲(chǔ)或讀取的每個(gè)頁(yè)面都隨機(jī)分配給其中一個(gè)緩沖池實(shí)例，使用哈希函數(shù)。每個(gè)緩沖池管理自己的自由列表、刷新列表、LRU和連接到緩沖池的所有其他數(shù)據(jù)結(jié)構(gòu)，并由自己的buffer pool mutex（互斥體）保護(hù)。
只有當(dāng)您將innodb _ buffer _ pool _size設(shè)置為1gb或更高的大小時(shí)，此選項(xiàng)才會(huì)生效。所有緩沖池的大小之和是您指定的總的緩存池大小。為了獲得最佳效率，請(qǐng)指定innodb _ buffer _ pool _instances和innodb _ buffer _ pool _size的組合，以便每個(gè)緩沖池實(shí)例至少為1gb。
在MySQL 5.6.6之前,默認(rèn)是1， 默認(rèn)值 在MySQL 5.6.6和更高版本在32位系統(tǒng)取決于innodb_buffer_pool_size的值
1 innodb_buffer_pool_instances可以開啟多個(gè)內(nèi)存緩沖池，把需要緩沖的數(shù)據(jù)hash到不同的緩沖池中，這樣可以并行的內(nèi)存讀寫。
2 innodb_buffer_pool_instances 參數(shù)顯著的影響測(cè)試結(jié)果，特別是非常高的 I/O 負(fù)載時(shí)。
3 實(shí)驗(yàn)環(huán)境下， innodb_buffer_pool_instances=8 在很小的 buffer_pool 大小時(shí)有很大的提升，而使用大的 buffer_pool 時(shí)，innodb_buffer_pool_instances=1 的表現(xiàn)最棒。
最后說(shuō)說(shuō)mysql的innodb引擎下的表空間：
一、針對(duì)innodb引擎，分為兩種情況一種是共享表空間存儲(chǔ)方式，還有一種是獨(dú)享表空間存儲(chǔ)方式。
共享表空間： Innodb的所有數(shù)據(jù)保存在一個(gè)單獨(dú)的表空間里面，而這個(gè)表空間可以由很多個(gè)文件組成，一個(gè)表可以跨多個(gè)文件存在，所以其大小限制不再是文件大小的限制，而是其自身的限制。從Innodb的官方文檔中可以看到，其表空間的最大限制為64TB，也就是說(shuō)，Innodb的單表限制基本上也在64TB左右了，當(dāng)然這個(gè)大小是包括這個(gè)表的所有索引等其他相關(guān)數(shù)據(jù)。
獨(dú)立表空間：
而當(dāng)使用獨(dú)享表空間來(lái)存放Innodb的表的時(shí)候，每個(gè)表的數(shù)據(jù)以一個(gè)單獨(dú)的文件來(lái)存放，這個(gè)時(shí)候的單表限制，又變成文件系統(tǒng)的大小限制了
針對(duì)myasam引擎：
MySQL單表最大限制就已經(jīng)擴(kuò)大到了64PB了（官方文檔顯示）。也就是說(shuō)，從目前的技術(shù)環(huán)境來(lái)看，MySQL數(shù)據(jù)庫(kù)的MyISAM存儲(chǔ) 引擎單表大小限制已經(jīng)不是有MySQL數(shù)據(jù)庫(kù)本身來(lái)決定，而是由所在主機(jī)的OS上面的文件系統(tǒng)來(lái)決定了。
二、查看數(shù)據(jù)庫(kù)的表空間
MySQL [(none)]> show variables like 'innodb_data%';
+-----------------------+----------------------------------------------------------------------+
| Variable_name | Value |
+-----------------------+----------------------------------------------------------------------+
| innodb_data_file_path | /data/data/ibdata1:100m;/data/data1/ibdata2:100m:autoextend:max:2000M |
| innodb_data_home_dir | |
+-----------------------+----------------------------------------------------------------------+
表空間有四個(gè)文件組成：ibdata1、ibdata2每個(gè)文件的大小為100M，當(dāng)ibdata1文件滿了的時(shí)候，ibdata2會(huì)自動(dòng)擴(kuò)展；
當(dāng)前的存儲(chǔ)空間滿的時(shí)候，可以在其他的磁盤添加數(shù)據(jù)文件，語(yǔ)法如下：語(yǔ)法如下所示：
pathtodatafile:sizespecification;pathtodatafile:sizespec;.;pathtodatafile:sizespec[:autoextend[:max:sizespecification]]
如果用 autoextend 選項(xiàng)描述最后一個(gè)數(shù)據(jù)文件，當(dāng) InnoDB 用盡所有表自由空間后將會(huì)自動(dòng)擴(kuò)充最后一個(gè)數(shù)據(jù)文件，每次增量為 8 MB。示例：
不管是共享表空間和獨(dú)立表空間，都會(huì)存在innodb_data_file文件，因?yàn)檫@些文件不僅僅要存放數(shù)據(jù)，而且還要充當(dāng)著類似于ORACLE的UNDO表空間等一些角色。
三、共享表空間優(yōu)缺點(diǎn)
既然Innodb有共享表空間和獨(dú)立表空間兩種類型，那么這兩種表空間存在肯定都有時(shí)候自己的應(yīng)用的場(chǎng)景，存在即合理。以下是摘自mysql官方的一些介紹：
3.1 共享表空間的優(yōu)點(diǎn)
表空間可以分成多個(gè)文件存放到各個(gè)磁盤，所以表也就可以分成多個(gè)文件存放在磁盤上，表的大小不受磁盤大小的限制，方便擴(kuò)容，放到多個(gè)磁盤磁盤上可以分散io,提高性能。
3.2 共享表空間的缺點(diǎn)
所有的數(shù)據(jù)和索引存放到一個(gè)文件，雖然可以把一個(gè)大文件分成多個(gè)小文件，但是多個(gè)表及索引在表空間中混合存儲(chǔ)，當(dāng)數(shù)據(jù)量非常大的時(shí)候，表做了大量刪除操作后表空間中將會(huì)有大量的空隙，特別是對(duì)于統(tǒng)計(jì)分析，日志系統(tǒng)這類應(yīng)用最不適合用共享表空間，對(duì)于經(jīng)常刪除操作的這類應(yīng)用最不適合用共享表空間。
共享表空間分配后不能回縮：當(dāng)出現(xiàn)臨時(shí)建索引或是創(chuàng)建一個(gè)臨時(shí)表的操作表空間擴(kuò)大后，就是刪除相關(guān)的表也沒(méi)辦法回縮那部分空間了（可以理解為oracle的表空間10G，但是才使用10M，但是操作系統(tǒng)顯示的表空間為10G），進(jìn)行數(shù)據(jù)庫(kù)的冷備很慢；
四、獨(dú)立表空間的優(yōu)缺點(diǎn)
4.1 獨(dú)立表空間的優(yōu)點(diǎn)
每個(gè)表都有自已獨(dú)立的表空間，每個(gè)表的數(shù)據(jù)和索引都會(huì)存在自已的表空間中，可以實(shí)現(xiàn)單表在不同的數(shù)據(jù)庫(kù)中移動(dòng)。
空間可以回收（除drop table操作外，表空不能自已回收）
Drop table操作自動(dòng)回收表空間，如果對(duì)于統(tǒng)計(jì)分析或是日志表，刪除大量數(shù)據(jù)后可以通過(guò):alter table TableName engine=innodb;回縮不用的空間。
對(duì)于使innodb-plugin的Innodb使用turncate table也會(huì)使空間收縮。
對(duì)于使用獨(dú)立表空間的表，不管怎么刪除，表空間的碎片不會(huì)太嚴(yán)重的影響性能；
4.2 獨(dú)立表空間的缺點(diǎn)
單表增加過(guò)大，當(dāng)單表占用空間過(guò)大時(shí)，存儲(chǔ)空間不足，只能從操作系統(tǒng)層面思考解決方法，比如做軟連接；
五、共享表空間和獨(dú)立表空間之間的轉(zhuǎn)換
5.1 查看當(dāng)前數(shù)據(jù)庫(kù)的表空間管理類型
mysql> show variables like "innodb_file_per_table";
ON代表獨(dú)立表空間管理，OFF代表共享表空間管理；（查看單表的表空間管理方式，需要查看每個(gè)表是否有單獨(dú)的數(shù)據(jù)文件）
5.2 修改數(shù)據(jù)庫(kù)的表空間管理方式
修改innodb_file_per_table的參數(shù)值即可，但是修改不能影響之前已經(jīng)使用過(guò)的共享表空間和獨(dú)立表空間；
innodb_file_per_table=1 為使用獨(dú)占表空間
innodb_file_per_table=0 為使用共享表空間
題外話
碎片的產(chǎn)生：
MySQL具有相當(dāng)多不同種類的存儲(chǔ)引擎來(lái)實(shí)現(xiàn)列表中的數(shù)據(jù)存儲(chǔ)功能。每當(dāng)MySQL從你的列表中刪除了一行內(nèi)容，該段空間就會(huì)被留空。而在一段時(shí)間內(nèi)的大量刪除操作，會(huì)使這種留空的空間變得比存儲(chǔ)列表內(nèi)容所使用的空間更大。當(dāng)MySQL對(duì)數(shù)據(jù)進(jìn)行掃描時(shí)，它掃描的對(duì)象實(shí)際是列表的容量需求上限，也就是數(shù)據(jù)被寫入的區(qū)域中處于峰值位置的部分。如果進(jìn)行新的插入操作，MySQL將嘗試?yán)眠@些留空的區(qū)域，但仍然無(wú)法將其徹底占用。
針對(duì)oracle行鏈接和行遷移：新insert的時(shí)候，可能發(fā)生行鏈接；update已經(jīng)存在的行時(shí)，可能發(fā)生行遷移；
行鏈接產(chǎn)生在第一次插入數(shù)據(jù)的時(shí)候如果一個(gè)block不能存放一行記錄的情況下。這種情況下，Oracle將使用鏈接一個(gè)或者多個(gè)在這個(gè)段中保留的block存儲(chǔ)這一行記錄，行鏈接比較容易發(fā)生在比較大的行上，例如行上有LONG、LONG RAW、LOB等數(shù)據(jù)類型的字段，這種時(shí)候行鏈接是不可避免的會(huì)產(chǎn)生的。
當(dāng)一行記錄初始插入的時(shí)候事可以存儲(chǔ)在一個(gè)block中的，由于更新操作導(dǎo)致行長(zhǎng)增加了，而block的自由空間已經(jīng)完全滿了，這個(gè)時(shí)候就產(chǎn)生了行遷移。在這種情況下，Oracle將會(huì)遷移整行數(shù)據(jù)到一個(gè)新的block中（假設(shè)一個(gè)block中可以存儲(chǔ)下整行數(shù)據(jù)），Oracle會(huì)保留被遷移行的原始指針指向新的存放行數(shù)據(jù)的block，這就意味著被遷移行的ROW ID是不會(huì)改變的。
當(dāng)發(fā)生了行遷移或者行鏈接，對(duì)這行數(shù)據(jù)操作的性能就會(huì)降低，因?yàn)镺racle必須要掃描更多的block來(lái)獲得這行的信息。
總結(jié)：原來(lái)mysql也有類似于oracle的undo表空間，也就是innodb共享表空間（ibdata1），默認(rèn)就是自動(dòng)擴(kuò)展的，每次擴(kuò)展8M，共享表空間類似于oracle的表空間，可以實(shí)現(xiàn)把mysql的數(shù)據(jù)和索引放到不同的磁盤下，分散io，進(jìn)而提高性能，就是不像oracle那樣可以動(dòng)態(tài)的給表空間添加文件，mysql如果想要添加共享表空間的文件，需要重啟mysql服務(wù)才能生效。
