1.今天接到开发人员 :mysql update 50w和30w的表关联慢,等了2小时也没有出来
mysql> UPDATE DIM_DEVICE_WIR_E_UTRANCELL_F a, temp_vipcell b SET a.vip_bts=b.vip_bts where a.cgi=b.cgi;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
2.那么我的处理方案
先看关联的数据cgi是否有重复,基数有多大? 言外之意-->适合建索引吗,查询,更新,就是减少io,减少数据扫描的块,避免笛卡尔积等
3.OK
那肯定适合建立索引
4.查看索引
show index from 2 个表
no rows ;
无索引
5.建立索引
所以create index idx_xxxx_01 on 表1(cgi);
卡了5分钟,察。。。
6.不对,再慢也得有响应啊
于是怀疑锁表
mysql> show processlist 默认100行,哇塞,太多
7.show processlist where info like '*xx表*' ;
报错
8. show prcoesslist 应该是默认列100个,并且不支持带参数
show full processlist; --列出全部进程信息
9.查询某个表被锁,应该是这样
mysql>SELECT id,user, host, time, command,info FROM information_schema.processlist WHERE state IS NOT NULL and info like '%xxx表%';
mysql>kill id号;
10.我们打算用10000条做测试, 如果不行,我们就1w一次
mysql> create table temp_vipcell_2 as select * from temp_vipcell limit 10000;
Query OK, 10000 rows affected (0.26 sec)
Records: 10000 Duplicates: 0 Warnings: 0
mysql> create index idx_temp_01 on temp_vipcell_2 (cgi);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from temp_vipcell_2;
+----------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| temp_vipcell_2 | 1 | idx_temp_01 | 1 | cgi | A | 9985 | NULL | NULL | | BTREE | | |
+----------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
11.更新试试,4秒搞定50w+1w的更新,按理推算,34w应该是34秒搞定。
mysql> UPDATE DIM_DEVICE_WIR_E_UTRANCELL_F a, temp_vipcell_2 b SET a.vip_bts=b.vip_bts where a.cgi=b.cgi;
Query OK, 0 rows affected (4.13 sec)
Rows matched: 10000 Changed: 0 Warnings: 0
12.我们在主表建立索引,果然和推算一样34秒
mysql> create index idx_dim_device_wir_e_001 on DIM_DEVICE_W (cgi);
Query OK, 0 rows affected (2.26 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> UPDATE DIM_DEVICE_W a, temp_vipcell_2 b SET a.vip_bts=b.vip_bts where a.cgi=b.cgi;
Query OK, 0 rows affected (4.13 sec)
Rows matched: 10000 Changed: 0 Warnings: 0
mysql> UPDATE DIM_DEVICE_W a, temp_vipcell b SET a.vip_bts=b.vip_bts where a.cgi=b.cgi;
Query OK, 331262 rows affected (34.84 sec)
Rows matched: 341262 Changed: 331262 Warnings: 0
13.算了不折腾,一晚一次,34秒够了。
别想歪了,兄弟们。
数据库的东西是越快越好, 而某些东西是越慢越好