Mysql学习mysql InnoDB建表时设定初始大小的方法

发布时间：2020-12-12 02:51:03 所属栏目：MySql教程来源：网络整理

导读：《Mysql学习mysql InnoDB建表时设定初始大小的方法》要点：本文介绍了Mysql学习mysql InnoDB建表时设定初始大小的方法，希望对您有用。如果有疑问，可以联系我们。 MYSQL教程 InnoDB在写密集的压力时,由于B-Tree扩展,因而也会带来数据文件的扩展,然而,InnoD

《Mysql学习mysql InnoDB建表时设定初始大小的方法》要点：
本文介绍了Mysql学习mysql InnoDB建表时设定初始大小的方法，希望对您有用。如果有疑问，可以联系我们。

MYSQL教程InnoDB在写密集的压力时,由于B-Tree扩展,因而也会带来数据文件的扩展,然而,InnoDB数据文件扩展需要使用mutex保护数据文件,这就会导致波动. 丁奇的博客说明了这个问题：
When InnoDB under heavy write workload,datafiles will extend quickly,because of B-Tree allocate new pages. But InnoDB need to use mutex to protect datafile,so it will cause performance jitter. Xiaobin Lin said this in his blog:
解决的方法也很简单,只要知道数据文件可能会增长到多大,预先扩展即可.阅读代码可以知道,InnoDB建表后自动初始化大小是FIL_IBD_FILE_INITIAL_SIZE这个常量控制的,而初始化数据文件是由fil_create_new_single_table_tablespace()函数控制的.所以要改变数据文件初始化大小,只要修改fil_create_new_single_table_tablespace的传入值即可,默认是FIL_IBD_FILE_INITIAL_SIZE.
How to solve it? That's easy. If we know the datafile will extend to which size at most,we can pre-extend it. After reading source code,we can know InnoDB initial datafile size by FIL_IBD_FILE_INITIAL_SIZE,and fil_create_new_single_table_tablespace() function to do it. So if we want to change datafile initial size,we only need to change the initial size parameter in fil_create_new_single_table_tablespace(),the default value is FIL_IBD_FILE_INITIAL_SIZE.
因此,我在建表语法中加上了datafile_initial_size这个参数,例如：
CREATE TABLE test (
…
) ENGINE = InnoDB DATAFILE_INITIAL_SIZE=100000;
如果设定的值比FIL_IBD_FILE_INITIAL_SIZE还小,就依然传入FIL_IBD_FILE_INITIAL_SIZE给fil_create_new_single_table_tablespace,否则传入datafile_initial_size进行初始化.
So,I add a new parameter for CREATE TABLE,named ‘datafile_initial_size'. For example:
CREATE TABLE test (
…
) ENGINE = InnoDB DATAFILE_INITIAL_SIZE=100000;
If DATAFILE_INITIAL_SIZE value less than FIL_IBD_FILE_INITIAL_SIZE,I will still pass FIL_IBD_FILE_INITIAL_SIZE to fil_create_new_single_table_tablespace(),otherwise,I pass DATAFILE_INITIAL_SIZE value to fil_create_new_single_table_tablespace() function for initialization.
因此,这个简单平安的patch就有了,可以看 http://bugs.mysql.com/bug.php?id=67792 关注官方的进展：
So,I wrote this simple patch,see http://bugs.mysql.com/bug.php?id=67792:

代码如下:
Index: storage/innobase/dict/dict0crea.c
===================================================================
--- storage/innobase/dict/dict0crea.c?(revision 3063)
+++ storage/innobase/dict/dict0crea.c?(working copy)
@@ -294,7 +294,8 @@
???error = fil_create_new_single_table_tablespace(
????space,path_or_name,is_path,
????flags == DICT_TF_COMPACT 必修 0 : flags,
-???FIL_IBD_FILE_INITIAL_SIZE);
+???table->datafile_initial_size < FIL_IBD_FILE_INITIAL_SIZE 必修
+??????? FIL_IBD_FILE_INITIAL_SIZE : table->datafile_initial_size);
???table->space = (unsigned int) space;

???if (error != DB_SUCCESS) {
Index: storage/innobase/handler/ha_innodb.cc
===================================================================
--- storage/innobase/handler/ha_innodb.cc?(revision 3063)
+++ storage/innobase/handler/ha_innodb.cc?(working copy)
@@ -7155,6 +7155,7 @@
????col_len);
??}

+? table->datafile_initial_size= form->datafile_initial_size;
??error = row_create_table_for_mysql(table,trx);

??if (error == DB_DUPLICATE_KEY) {
@@ -7760,6 +7761,7 @@

??row_mysql_lock_data_dictionary(trx);

+? form->datafile_initial_size= create_info->datafile_initial_size;
??error = create_table_def(trx,form,norm_name,
???create_info->options & HA_LEX_CREATE_TMP_TABLE 必修 name2 : NULL,
???flags);
Index: storage/innobase/include/dict0mem.h
===================================================================
--- storage/innobase/include/dict0mem.h?(revision 3063)
+++ storage/innobase/include/dict0mem.h?(working copy)
@@ -678,6 +678,7 @@
?/** Value of dict_table_struct::magic_n */
?# define DICT_TABLE_MAGIC_N?76333786
?#endif /* UNIV_DEBUG */
+? uint datafile_initial_size; /* the initial size of the datafile */
?};

?#ifndef UNIV_NONINL
Index: support-files/mysql.5.5.18.spec
===================================================================
--- support-files/mysql.5.5.18.spec?(revision 3063)
+++ support-files/mysql.5.5.18.spec?(working copy)
@@ -244,7 +244,7 @@
?Version:??????? 5.5.18
?Release:??????? %{release}%{必修distro_releasetag:.%{distro_releasetag}}
?Distribution:?? %{distro_description}
-License:??????? Copyright (c) 2000,2011,%{mysql_vendor}. All rights reserved. Under %{license_type} license as shown in the Description field.
+License:??????? Copyright (c) 2000,2012,%{mysql_vendor}. All rights reserved. Under %{license_type} license as shown in the Description field.
?Source:???????? http://www.mysql.com/Downloads/MySQL-5.5/%{src_dir}.tar.gz
?URL:??????????? http://www.mysql.com/
?Packager:?????? MySQL Release Engineering <mysql-build@oss.oracle.com>
Index: sql/table.h
===================================================================
--- sql/table.h?(revision 3063)
+++ sql/table.h?(working copy)
@@ -596,6 +596,7 @@
?? */
?? key_map keys_in_use;
?? key_map keys_for_keyread;
+? uint datafile_initial_size; /* the initial size of the datafile */
?? ha_rows min_rows,max_rows;??/* create information */
?? ulong?? avg_row_length;??/* create information */
?? ulong?? version,mysql_version;
@@ -1094,6 +1095,8 @@
?#endif
?? MDL_ticket *mdl_ticket;

+? uint datafile_initial_size;
+
?? void init(THD *thd,TABLE_LIST *tl);
?? bool fill_item_list(List<Item> *item_list) const;
?? void reset_item_list(List<Item> *item_list) const;
Index: sql/sql_yacc.yy
===================================================================
--- sql/sql_yacc.yy?(revision 3063)
+++ sql/sql_yacc.yy?(working copy)
@@ -906,6 +906,7 @@
?%token? DATABASE
?%token? DATABASES
?%token? DATAFILE_SYM
+%token? DATAFILE_INITIAL_SIZE_SYM
?%token? DATA_SYM????????????????????? /* SQL-2003-N */
?%token? DATETIME
?%token? DATE_ADD_INTERVAL???????????? /* MYSQL-FUNC */
@@ -5046,6 +5047,18 @@
???????????? Lex->create_info.db_type= $3;
???????????? Lex->create_info.used_fields|= HA_CREATE_USED_ENGINE;
?????????? }
+??????? | DATAFILE_INITIAL_SIZE_SYM opt_equal ulonglong_num
+????????? {
+??????????? if ($3 > UINT_MAX32)
+??????????? {
+????????????? Lex->create_info.datafile_initial_size= UINT_MAX32;
+??????????? }
+??????????? else
+??????????? {
+????????????? Lex->create_info.datafile_initial_size= $3;
+??????????? }
+??????????? Lex->create_info.used_fields|= HA_CREATE_USED_DATAFILE_INITIAL_SIZE;
+????????? }
???????? | MAX_ROWS opt_equal ulonglong_num
?????????? {
???????????? Lex->create_info.max_rows= $3;
@@ -12585,6 +12598,7 @@
???????? | CURSOR_NAME_SYM????????? {}
???????? | DATA_SYM???????????????? {}
???????? | DATAFILE_SYM???????????? {}
+??????? | DATAFILE_INITIAL_SIZE_SYM{}
???????? | DATETIME???????????????? {}
???????? | DATE_SYM???????????????? {}
???????? | DAY_SYM????????????????? {}
Index: sql/handler.h
===================================================================
--- sql/handler.h?(revision 3063)
+++ sql/handler.h?(working copy)
@@ -387,6 +387,8 @@
?#define HA_CREATE_USED_TRANSACTIONAL??? (1L << 20)
?/** Unused. Reserved for future versions. */
?#define HA_CREATE_USED_PAGE_CHECKSUM??? (1L << 21)
+/** Used for InnoDB initial table size. */
+#define HA_CREATE_USED_DATAFILE_INITIAL_SIZE (1L << 22)

?typedef ulonglong my_xid; // this line is the same as in log_event.h
?#define MYSQL_XID_PREFIX "MySQLXid"
@@ -1053,6 +1055,7 @@
?? LEX_STRING comment;
?? const char *data_file_name,*index_file_name;
?? const char *alias;
+? uint datafile_initial_size; /* the initial size of the datafile */
?? ulonglong max_rows,min_rows;
?? ulonglong auto_increment_value;
?? ulong table_options;
Index: sql/lex.h
===================================================================
--- sql/lex.h?(revision 3063)
+++ sql/lex.h?(working copy)
@@ -153,6 +153,7 @@
?? { "DATABASE",??SYM(DATABASE)},
?? { "DATABASES",?SYM(DATABASES)},
?? { "DATAFILE",?SYM(DATAFILE_SYM)},
+? { "DATAFILE_INITIAL_SIZE",?? SYM(DATAFILE_INITIAL_SIZE_SYM)},
?? { "DATE",??SYM(DATE_SYM)},
?? { "DATETIME",??SYM(DATETIME)},
?? { "DAY",??SYM(DAY_SYM)},

编程之家PHP培训学院每天发布《Mysql学习mysql InnoDB建表时设定初始大小的方法》等实战技能，PHP、MYSQL、LINUX、APP、JS,CSS全面培养人才。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!