MySQL5.7收集表统计信息时对该表上增删改查的影响

先说下结论：

收集统计信息期间对该表上DML和select的影响:结论是会影响DML，select不影响，并不是整改analyze过程都影响，大概影响1/3 analyze table时间。

官方文档说明：

https://dev.mysql.com/doc/refman/5.7/en/analyze-table.html 中有一段话对analyze table行为做了说明，如下：

During the analysis, the table is locked with a read lock for InnoDB and MyISAM.

ANALYZE TABLE removes the table from the table definition cache, which requires a flush lock. If there are long running statements or transactions still using the table, subsequent statements and transactions must wait for those operations to finish before the flush lock is released. Because ANALYZE TABLE itself typically finishes quickly, it may not be apparent that delayed transactions or statements involving the same table are due to the remaining flush lock.

测试过程：

使用sysbench压测也验证了确实会受影响：总共耗时1分23秒，其中23左右增删改完全受影响

/usr/bin/sysbench /usr/share/sysbench/oltp_read_write.lua --table_size=200000000 --db-driver=mysql --tables=1 --threads=2 --time=900 --mysql-host=192.168.30.11 --mysql-user=root --mysql-port=3306 --mysql-password=1234 --report-interval=1 run 

开启压测的过程中，执行收集表统计信息的命令

观察到在收集统计信息过程中TPS突然变为0

期间show processlist中观察到analyze table和压测过程中，analyze table一直为system lock状态：

再次使用read_only脚本测试，并没有发现tps降为0的现象。

/usr/bin/sysbench /usr/share/sysbench/oltp_read_only.lua --table_size=200000000 --db-driver=mysql --tables=1 --threads=2 --time=900 --mysql-host=192.168.30.11 --mysql-user=root --mysql-port=3306 --mysql-password=1234 --report-interval=2 run