innodb_data_file_path和innodb_data_home_dir
今天研究下关于mysql数据的相关参数以及innodb引擎下的共享表空间和独⽴表空间介绍，参数主要包括innodb_data_file_path、 innodb_data_home_dir、innodb_buffer_pool_size、innodb_buffer_pool_instances四个参数。
⼀：⾸先介绍innodb_buffer_pool_size
我们知道mysql数据库在操作数据页的时候，会先把数据页pin到内存中，之后再做相关的处理，那么mysql可以使⽤的mysql的内存⼤⼩就是由innodb_buffer_pool_size参数控制的，这个参数主要作⽤是缓存innodb表的索引，数据，插⼊数据时的缓冲。在⾼并发⾼I/O时正确的配置⾮常重要，可能带来很⼤的性能提升，这是InnoDB最重要的设置，对InnoDB性能有决定性的影响。默认的设置只有8M，所以默认的数据库设置下⾯InnoDB性能很差。在只有 InnoDB存储引擎的数据库服务器上⾯，可以设置60-80%的内存。这个参数是⾮动态的，要修改这个值，需要重启mysqld服务
# 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，使⽤缓冲池来缓存索引和
#⾏数据。您设置的越⼤，那么访问表中的数据所需的磁盘I /o就越少。
#在专⽤的数据库服务器上，您可以设置这个
#参数最多80%的计算机物理内存⼤⼩。不要设置它
#太⼤了，因为物理内存的竞争可能
#导致操作系统中的分页。注意，在32位系统上
#可能仅限于每个进程的2-3.5g⽤户级内存，所以不要
北京工业大学学报把它定得太⾼了。
⼆：innodb_data_home_dir和innodb_data_file_path参数
anit
innodb_data_file_path⽤来指定innodb 共享表空间⽂件，如果我们不在Myf⽂件中指定innodb_data_home_dir和innodb_data_file_path 那么默认会在datadir⽬录下创建ibdata1 作为innodb tablespace。
虽然不指定innodb_data_home_dir和指定它为空显⽰是⼀样的，但是作⽤确不⼀样，如果不指定那么所有的innodb tablespace ⽂件只能存放在datadir⽬录下。如果显⽰指定为空，则可以为innodb tablespace ⽂件指定不同的路径
1. 在同⼀个路径下指定多个innodb tablespace ⽂件
1)
在myf⽂件指定如下
[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：因为没有指定innodb_data_home_dir，所以ibdata1和ibdata2都在datadir(innodb_data_home_dir默认值为datadir)⽬录下创建
2)
在myf⽂件指定如下
[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
在myf⽂件下指定
[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 =。这个例⼦中会在datadir下建⽴ibdata1，
在/data/mysql/mysql3306/data1/⽬录下创建ibdata2
如果我不指定innodb_data_home_dir =，只在myf⽂件中做如下指定：
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).
查看错误⽇志。。。如下
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⽂件指定不同路径，那么必须在myf⽂件中指定innodb_data_home_dir =
注意：
1）设定⽂件⼤⼩时⼀定要注意你的OS是否有最⼤⽂件尺⼨为2GB的限制！InnoDB是不会注意你的OS⽂件尺⼨限制的，在⼀些⽂件系统中你可能要设定最⼤容量限制：
innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:100M:autoextend:max:2000M
2）如果是修改共享表空间的⽂件个数，注意在描述原来⽂件的时候，⼀定要到该⽂件，查看他当前的实际⼤⼩，然后写在
innodb_data_file_path =哪个⽂件的后⾯。否则会报错。
三：innodb_buffer_pool_instances
innodb缓冲池被划分为多个内存缓冲池的数量。对于具有多GB范围的缓冲池的系统，将缓冲池划分为多个缓冲池可以提⾼并发性，减少对不同线程读取和写⼊到缓存时页⾯的争⽤。从缓冲池中存储或读取的每个页⾯都随机分配给其中⼀个缓冲池实例，使⽤哈希函数。每个缓冲池管理⾃⼰的⾃由列表、刷新列表、LRU和连接到缓冲池的所有其他数据结构，并由⾃⼰的buffer pool mutex（互斥体）保护。
只有当您将innodb _ buffer _ pool _size设置为1gb或更⾼的⼤⼩时，此选项才会⽣效。所有缓冲池的⼤⼩之和是您指定的总的缓存池⼤⼩。为了获得最佳效率，请指定innodb _ buffer _ pool _instances和innodb _ buffer _ pool _size的组合，以便每个缓冲池实例⾄少为1gb。
在MySQL 5.6.6之前，默认是1，默认值在MySQL 5.6.6和更⾼版本在32位系统取决于innodb_buffer_pool_size的值
1 innodb_buffer_pool_instances可以开启多个内存缓冲池，把需要缓冲的数据hash到不同的缓冲池中，这样可以并⾏的内存读写。
2 innodb_buffer_pool_instances 参数显著的影响测试结果，特别是⾮常⾼的 I/O 负载时。
3 实验环境下， innodb_buffer_pool_instances=8 在很⼩的 buffer_pool ⼤⼩时有很⼤的提升，⽽使⽤⼤的 buffer_pool
时，innodb_buffer_pool_instances=1 的表现最棒。
最后说说mysql的innodb引擎下的表空间：
⼀、针对innodb引擎，分为两种情况⼀种是共享表空间存储⽅式，还有⼀种是独享表空间存储⽅式。
共享表空间： Innodb的所有数据保存在⼀个单独的表空间⾥⾯，⽽这个表空间可以由很多个⽂件组成，⼀个表可以跨多个⽂件存在，所以其⼤⼩限制不再是⽂件⼤⼩的限制，⽽是其⾃⾝的限制。从Innodb的官⽅⽂档中可以看到，其表空间的最⼤限制为64TB，也就是
说，Innodb的单表限制基本上也在64TB左右了，当然这个⼤⼩是包括这个表的所有索引等其他相关数据。
独⽴表空间：
⽽当使⽤独享表空间来存放Innodb的表的时候，每个表的数据以⼀个单独的⽂件来存放，这个时候的单表限制，⼜变成⽂件系统的⼤⼩限制了
针对myasam引擎：
MySQL单表最⼤限制就已经扩⼤到了64PB了（官⽅⽂档显⽰）。也就是说，从⽬前的技术环境来看，MySQL数据库的MyISAM存储引擎单表⼤⼩限制已经不是有MySQL数据库本⾝来决定，⽽是由所在主机的OS上⾯的⽂件系统来决定了。
⼆、查看数据库的表空间
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 | |
+-----------------------+----------------------------------------------------------------------+
表空间有四个⽂件组成：ibdata1、ibdata2每个⽂件的⼤⼩为100M，当ibdata1⽂件满了的时候，ibdata2会⾃动扩展；
当前的存储空间满的时候，可以在其他的磁盘添加数据⽂件，语法如下：语法如下所⽰：
pathtodatafile:sizespecification;pathtodatafile:sizespec;.;pathtodatafile:sizespec[:autoextend[:max:sizespecification]]
如果⽤ autoextend 选项描述最后⼀个数据⽂件，当 InnoDB ⽤尽所有表⾃由空间后将会⾃动扩充最后
马勒第二交响曲⼀个数据⽂件，每次增量为 8 MB。⽰例：
不管是共享表空间和独⽴表空间，都会存在innodb_data_file⽂件，因为这些⽂件不仅仅要存放数据，⽽且还要充当着类似于ORACLE的UNDO表空间等⼀些⾓⾊。
三、共享表空间优缺点
既然Innodb有共享表空间和独⽴表空间两种类型，那么这两种表空间存在肯定都有时候⾃⼰的应⽤的场景，存在即合理。以下是摘⾃mysql 官⽅的⼀些介绍：
3.1 共享表空间的优点
表空间可以分成多个⽂件存放到各个磁盘，所以表也就可以分成多个⽂件存放在磁盘上，表的⼤⼩不受磁盘⼤⼩的限制，⽅便扩容，放到多个磁盘磁盘上可以分散io，提⾼性能。
3.2 共享表空间的缺点
所有的数据和索引存放到⼀个⽂件，虽然可以把⼀个⼤⽂件分成多个⼩⽂件，但是多个表及索引在表空间中混合存储，当数据量⾮常⼤的时候，表做了⼤量删除操作后表空间中将会有⼤量的空隙，特别是对于统计分析，⽇志系统这类应⽤最不适合⽤共享表空间，对于经常删除操作的这类应⽤最不适合⽤共享表空间。
共享表空间分配后不能回缩：当出现临时建索引或是创建⼀个临时表的操作表空间扩⼤后，就是删除相关的表也没办法回缩那部分空间了（可以理解为oracle的表空间10G，但是才使⽤10M，但是操作系统显⽰的表空间为10G），进⾏数据库的冷备很慢；
四、独⽴表空间的优缺点
4.1 独⽴表空间的优点
每个表都有⾃已独⽴的表空间，每个表的数据和索引都会存在⾃已的表空间中，可以实现单表在不同的数据库中移动。
空间可以回收（除drop table操作外，表空不能⾃已回收）
Drop table操作⾃动回收表空间，如果对于统计分析或是⽇志表，删除⼤量数据后可以通过:alter table TableName engine=innodb；回缩不⽤的空间。
对于使innodb-plugin的Innodb使⽤turncate table也会使空间收缩。
对于使⽤独⽴表空间的表，不管怎么删除，表空间的碎⽚不会太严重的影响性能；
4.2 独⽴表空间的缺点
cnf单表增加过⼤，当单表占⽤空间过⼤时，存储空间不⾜，只能从操作系统层⾯思考解决⽅法，⽐如做软连接；
五、共享表空间和独⽴表空间之间的转换哭泣的奶牛
5.1 查看当前数据库的表空间管理类型
mysql> show variables like "innodb_file_per_table";
ON代表独⽴表空间管理，OFF代表共享表空间管理；（查看单表的表空间管理⽅式，需要查看每个表是否有单独的数据⽂件）拟水狼蛛
5.2 修改数据库的表空间管理⽅式
修改innodb_file_per_table的参数值即可，但是修改不能影响之前已经使⽤过的共享表空间和独⽴表空间；
innodb_file_per_table=1 为使⽤独占表空间
innodb_file_per_table=0 为使⽤共享表空间
题外话
碎⽚的产⽣：
MySQL具有相当多不同种类的存储引擎来实现列表中的数据存储功能。每当MySQL从你的列表中删除了⼀⾏内容，该段空间就会被留空。⽽在⼀段时间内的⼤量删除操作，会使这种留空的空间变得⽐存储列表内容所使⽤的空间更⼤。当MySQL对数据进⾏扫描时，它扫描的对象实际是列表的容量需求上限，也就是数据被写⼊的区域中处于峰值位置的部分。如果进⾏新的插⼊操作，MySQL将尝试利⽤这些留空的区域，但仍然⽆法将其彻底占⽤。
针对oracle⾏链接和⾏迁移：新insert的时候，可能发⽣⾏链接；update已经存在的⾏时，可能发⽣⾏迁移；
⾏链接产⽣在第⼀次插⼊数据的时候如果⼀个block不能存放⼀⾏记录的情况下。这种情况下，Oracle将使⽤链接⼀个或者多个在这个段中保留的block存储这⼀⾏记录，⾏链接⽐较容易发⽣在⽐较⼤的⾏上，例如⾏上有LONG、LONG RAW、LOB等数据类型的字段，这种时候⾏链接是不可避免的会产⽣的。
当⼀⾏记录初始插⼊的时候事可以存储在⼀个block中的，由于更新操作导致⾏长增加了，⽽block的⾃
由空间已经完全满了，这个时候就产⽣了⾏迁移。在这种情况下，Oracle将会迁移整⾏数据到⼀个新的block中（假设⼀个block中可以存储下整⾏数据），Oracle会保留被迁移⾏的原始指针指向新的存放⾏数据的block，这就意味着被迁移⾏的ROW ID是不会改变的。
当发⽣了⾏迁移或者⾏链接，对这⾏数据操作的性能就会降低，因为Oracle必须要扫描更多的block来获得这⾏的信息。
总结：原来mysql也有类似于oracle的undo表空间，也就是innodb共享表空间（ibdata1），默认就是⾃动扩展的，每次扩展8M，共享表空间类似于oracle的表空间，可以实现把mysql的数据和索引放到不同的磁盘下，分散io，进⽽提⾼性能，就是不像oracle那样可以动态的给表空间添加⽂件，mysql如果想要添加共享表空间的⽂件，需要重启mysql服务才能⽣效。
来⾃ “ ITPUB博客 ” ，链接：blog.itpub/29654823/viewspace-2148189/，如需转载，请注明出处，否则将追究法律责任。
豫公网安备41160202000603
站长QQ:729038198
关于我们
投诉建议