ANALYZE TABLE与READ LOCK

mysql文档:
ANALYZE TABLE analyzes and stores the key distribution for a table. During the analysis, the table is locked with a read lock for MyISAM, BDB, and InnoDB.

验证analyze table过程中,是不可以写操作的:

session 1:

设置比较大的采样数据块,增加analyze时间
root@lx >set global innodb_stats_sample_pages = 30000000;
Query OK, 0 rows affected (0.00 sec)
 
在analyze同时,在session2执行写入动作
root@lx >analyze table test_t;
+-----------+---------+----------+----------+
| Table     | Op      | Msg_type | Msg_text |
+-----------+---------+----------+----------+
| lx.test_t | analyze | status   | OK       |
+-----------+---------+----------+----------+
1 row in set (3.71 sec)

session 2:

被blocked
root@lx >insert into test_t(tab_name,gmt_create) values('lxlx',now());
Query OK, 1 row affected (0.00 sec)

btw:
介绍一篇文章innodb什么时候去更新表的统计信息

http://www.mysqlperformanceblog.com/2011/10/06/when-does-innodb-update-table-statistics-and-when-it-can-bite/

这三种情况耗时差不多

root@lx 10:52:02>analyze table test_t;
1 row in set (3.65 sec)
 
root@lx 10:52:07>show table status like 'test_t';
1 row in set (3.58 sec)
 
root@lx 10:52:19>show index from test_t;
2 rows in set (3.56 sec)

2 Responses to “ANALYZE TABLE与READ LOCK”

  1. hongquanwang说道:

    analyze table 是阻塞写的,我看了一下,可能是你的表的数据量太小,做分析的时间太短,所以在插入的时候没看出效果。

Post a Comment