Home > database > 避免innodb plugin创建非聚簇索引时阻塞查询的一种方法

避免innodb plugin创建非聚簇索引时阻塞查询的一种方法

Innodb plugin在增删二级索引的时候不再拷贝数据,在删除一个secondary indexes时,先更改一下InnoDB内部数据字典和MySQL的数据字典,然后把释放的空间归还给InnoDB以供重复使用。如果是增加一个secondary indexes,还是有点复杂的,Plugin先将数据表中的数据取出到memory buffers或者临时表中,并按照新建索引列排好序,然后建立索引的B-Tree,但是在一些较低版本中出现了bug,导致select也会被阻塞,这对于你的应用来说如果ddl期间不能查询,那将是一个恶梦,这个时候如果你需要添加索引,将要转变一下了,请看下面的实验:

root@test 05:18:05>desc test_plg;

+——-+————-+——+—–+———+—————-+

| Field | Type        | Null | Key | Default | Extra          |

+——-+————-+——+—–+———+—————-+

| id    | int(11)     | NO   | PRI | NULL    | auto_increment |

| name  | varchar(30) | YES  |     | NULL    |                |

| dd    | datetime    | YES  |     | NULL    |                |

| dd2   | datetime    | YES  |     | NULL    |                |

| name2 | varchar(30) | YES  |     | NULL    |                |

+——-+————-+——+—–+———+—————-+

5 rows in set (0.00 sec)

root@test 05:18:12>show index from test_plg;

+———-+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+

| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+———-+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+

| test_plg |          0 | PRIMARY  |            1 | id          | A         |     6291866 |     NULL | NULL   |      | BTREE      |         |

+———-+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+

1 row in set (0.10 sec)

test1:

Session1:

root@test 05:18:18>alter table test_plg add index ind_name(name);

Query OK, 0 rows affected (46.39 sec)

Records: 0  Duplicates: 0  Warnings: 0

Session2:

roo@test 05:15:46>select * from test_plg where;

+—-+———+———————+———————+——-+

| id | name    | dd                  | dd2                 | name2 |

+—-+———+———————+———————+——-+

|  1 | ssdsdsd | 2011-03-07 22:03:26 | 2011-03-07 22:03:26 | NULL  |

+—-+———+———————+———————+——-+

1 row in set (42.14 sec)

Session2被阻塞了:(

test2:

Session1:

root@test 05:18:18>alter table test_plg add index ind_name(name);

Query OK, 0 rows affected (46.39 sec)

Records: 0  Duplicates: 0  Warnings: 0

root@test 05:22:11>alter table test_plg add index ind_name(name),

drop column dd,add column dd datetime;

Query OK, 6291456 rows affected (1 min 35.12 sec)

Records: 6291456  Duplicates: 0  Warnings: 0

Session2:

roo@test 05:22:15>select * from test_plg where;

+—-+———+———————+———————+——-+

| id | name    | dd                  | dd2                 | name2 |

+—-+———+———————+———————+——-+

|  1 | ssdsdsd | 2011-03-07 22:03:26 | 2011-03-07 22:03:26 | NULL  |

+—-+———+———————+———————+——-+

1 row in set (0.00 sec)

Session2没有被阻塞^_^

总结:

Session2没有被阻塞^_^,但是创建索引的时间变长了:(;

将变通了的ddll语句(删除表中已有的列删除后在添加上:drop column dd,add column dd datetime),通过show global status看到

Innodb_rows_read   212673524 【由206382068变为212673524  】 –plugin失效,需要拷贝表;

而单独添加索引ddl的sql语句,通过show global status查看: Innodb_rows_read  仍为212673524  —plugin生效,不在拷贝表;

可以看到变通添加索引的方法,需要将原表要拷贝一遍,速度将会变慢许多, 但是可以避免应用查询被阻塞。

Categories: database Tags:
  1. No comments yet.
  1. No trackbacks yet.
You must be logged in to post a comment.