手机浏览 RSS 2.0 订阅 膘叔的简单人生 , 腾讯云RDS购买 | 超便宜的Vultr , 注册 | 登陆
浏览模式: 标准 | 列表Tag:修改

MySQL之表结构修改[转]

Author:丹臣 posted on Taobao.com

mysql数据库里,对一个已创建的表进行DDL操作,比如说添加一个字段。在做测试时,发现ddl操作的时间特别的长。oracle里,通常情况下只是 修改数据字典就可以了,操作时间非常的短,阻塞DML的时间也比较短。mysql数据库对表进行ddl操作跟oracle数据库有很大的不同,它先要把原 表拷贝一份到临时表,这期间不阻塞select,阻塞所有的更改操作(update,delete,insert),对临时表ddl操作完成,删除原表, 重命名临时表。
如果一张比较大的表进行ddl变更,比如说40G,那拷贝的时间让人无法忍受,并且阻塞所有的DML操作,让业务无法继续。

以下是测试过程:

mysql> desc t1;
+-------------------+------------------+------+-----+---------+-------+
| Field                  | Type               | Null   | Key | Default | Extra |
+-------------------+------------------+------+-----+---------+-------+
| id                      | int(11)            | YES  | MUL | NULL    |       |
| nick                   | varchar(32)   | YES  |         | NULL    |       |
| email                 | varchar(32)    | YES  |         | NULL    |       |
| gmt_create       | datetime         | YES  |         | NULL    |       |
| gmt_modified    | datetime        | YES  |         | NULL    |       |
+-------------------+------------------+------+-----+---------+-------+
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|  2228017 |
+----------+
1 row in set (1.78 sec)

现在对它进行表结构变更,增加一列:

mysql> alter table t1 add(tel varchar(20));
Query OK, 2304923 rows affected (41.03 sec)
Records: 2304923  Duplicates: 0  Warnings: 0

在上述表结构变更过程中,启动另外一个会话,进行select查询操作和一个更新操作:

mysql> select count(*) from t1;
+---------------+
| count(*)      |
+---------------+
|  2304923     |
+---------------+
1 row in set (2.10 sec)

mysql> select * from t1 limit 10;
+------+-------+------------------------+----------------------------+----------------------------+
| id      | nick   | email                       | gmt_create                 | gmt_modified              |
+------+-------+------------------------+----------------------------+----------------------------+
|    0   | nick0 | nick0@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
|    1   | nick1 | nick1@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
|    2   | nick2 | nick2@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
|    3   | nick3 | nick3@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
|    4   | nick4 | nick4@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
|    5   | nick5 | nick5@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
|    6   | nick6 | nick6@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
|    7   | nick7 | nick7@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
|    8   | nick8 | nick8@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
|    9   | nick9 | nick9@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
+------+-------+------------------------+----------------------------+----------------------------+
10 rows in set (0.00 sec)

mysql> update t1 set nick='test_nick' where id=1;
Query OK, 4 rows affected (43.89 sec)          --这里是阻塞的时间
Rows matched: 4  Changed: 4  Warnings: 0

通过以上实验可以看出,对表进行ddl操作时,mysql并不阻塞select查询,但会严重阻塞dml操作。另外,如果你要对表进行ddl操作,由于有一个拷贝操作,你要计算好你的可用空间够不够?如果你的系统经常要进行表结构变更,那么你将不得不要考虑此问题!

--EOF--

膘叔:说实话,一般对于表的操作往往都是在夜深人静的时候,虽然select并不影响,但为避免在更新表结构的时候更新数据,这还是必须的。。。以防万一啊

 

Tags: mysql, database, 修改, 表结构, taobao dba

Mysql动态修改参数

    mysql数据库也像ORACLE数据库一样,可以动态的修改参数,可以修改会话级变量只对当前会话产生影响;也可以修改全局变量,对所有新连接的会话都产生影响。

修改会话级变量

用show variables 命令查看当前参数的值,like 'pattern'用于模式匹配,查找指定的参数

mysql> show variables like '%sort_buffer_size%';
+---------------------------+------------+
| Variable_name             | Value      |
+---------------------------+------------+
| sort_buffer_size          | 6291448    |
+---------------------------+------------+
1 rows in set (0.00 sec)

用set SESSION命令设置会话级变量的新值

mysql> set SESSION sort_buffer_size=7000000;
Query OK, 0 rows affected (0.00 sec)

--修改会话级变量对当前会话来说立刻生效
mysql> show variables like '%sort_buffer_size%';       
+---------------------------+------------+
| Variable_name             | Value      |
+---------------------------+------------+
| sort_buffer_size          | 7000000    |
+---------------------------+------------+
1 rows in set (0.00 sec)

mysql> exit
Bye
退出重新连接后,此参数恢复原值
[root@devdbc_stb root]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 40 to server version: 5.0.37-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like '%sort_buffer_size%';       
+---------------------------+------------+
| Variable_name             | Value      |
+---------------------------+------------+
| sort_buffer_size          | 6291448    |
+---------------------------+------------+
1 rows in set (0.00 sec)

修改全局变量
[root@devdbc_stb root]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 40 to server version: 5.0.37-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like '%sort_buffer_size%';       
+---------------------------+------------+
| Variable_name             | Value      |
+---------------------------+------------+
| sort_buffer_size          | 6291448    |
+---------------------------+------------+
1 rows in set (0.00 sec)

用set GLOBAL 命令设置全局变量

mysql> set GLOBAL sort_buffer_size = 7000000;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%sort_buffer_size%';        
+---------------------------+------------+
| Variable_name             | Value      |
+---------------------------+------------+
| sort_buffer_size          | 6291448    |
+---------------------------+------------+
1 rows in set (0.00 sec)
当前此参数的值并不发生变化,先退出,然后重新连进去
mysql> exit
Bye
[root@devdbc_stb root]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 41 to server version: 5.0.37-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like '%sort_buffer_size%';
+---------------------------+------------+
| Variable_name             | Value      |
+---------------------------+------------+
| sort_buffer_size          | 7000000    |
+---------------------------+------------+
1 rows in set (0.00 sec)
新的参数值生效

Tags: mysql, 优化, database, 修改