MySQL 查询数据不一致
上一篇 /
下一篇 2012-11-20 11:37:24
/ 个人分类：数据库
51Testing软件测试网i+`J,^l
y;Q2r S 最近出现一个很奇怪的MySQL问题，使用不同select语句查询全部数据集居然得到不同的记录数。select * 得到4条记录，select 字段得到的是3条记录。51Testing软件测试网 D"oq#|7g RK)L(p ]
51Testing软件测试网5~n-a\"LC Y5y5CP 具体问题可以看下面的查询结果：51Testing软件测试网V&LmBtYs
| 51Testing软件测试网Tv2y1BHa mysql> select * from table_myisam;
H`j6c8S9a*w ^0+----------+-------+-----------+------+
j:n j'A;{:~*uU+_c0| datetime | uid | content | type |51Testing软件测试网 u ['q
JRK T"Sw
+----------+-------+-----------+------+51Testing软件测试网#F2Zg c*u AUH o}
| 1 | uid_1 | content_1 | 1 |
V0d!b7m!f(W:b Y0| 2 | uid_2 | content_2 | 1 |
/[6_qdT1f2b/n_.z0| 4 | uid_4 | content_4 | 1 |51Testing软件测试网u'm3v4rqE OK
| 3 | uid_3 | content_3 | 1 |51Testing软件测试网-p8{4j!xU d$k9\?
+----------+-------+-----------+------+51Testing软件测试网/[6v]wps
4 rows in set (0.00 sec)51Testing软件测试网EK#cd+K3|
M
oE |#m5Z%uR ^5|0mysql> select uid from table_myisam;
c vh f@!P%B)Q6f0c0+-------+51Testing软件测试网-^9SfT)v0c
| uid |51Testing软件测试网)]b-^ r"vr
+-------+51Testing软件测试网8RY `0J`1^,F.A
| uid_1 |51Testing软件测试网+~(\-Y\ HJt
| uid_2 |51Testing软件测试网(mL1t9{ I6T
| uid_4 |
Pefg!A J
|5]0+-------+51Testing软件测试网c
\xO%d5`6U:]L
3 rows in set (0.00 sec) +`
T B#P'Z~ ? I0 |
51Testing软件测试网6H
TDJ[ uE:^6n 通过select uid只得到3行记录，丢失了其中uid='uid_3'的记录。本来百思不得其解，后来在同事的提醒下使用了check table，才找到问题的所在。51Testing软件测试网H K Dx G9X h1F
mysql> check table table_myisam;51Testing软件测试网V&z#e,} \![ M O x4i
+--------------------+-------+----------+-------------------------------------------------------+51Testing软件测试网q#s c3Z:` [9Q5H&f7J
| Table | Op | Msg_type | Msg_text |
e@q'~.?9I e0+--------------------+-------+----------+-------------------------------------------------------+51Testing软件测试网*e8UNB
\{+["dL;S,o.w
| qitai.table_myisam | check | warning | 1 client is using or hasn't closed the table properly |
"Fu(o:` i(m})`q
e0| qitai.table_myisam | check | warning | Size of indexfile is: 2049 Should be: 2048 |51Testing软件测试网u%X:o$RX*t
| qitai.table_myisam | check | error | Found 3 keys of 4 |51Testing软件测试网2| YP T9@w6l rq
| qitai.table_myisam | check | error | Corrupt |
#V*^
Z/{!X m*?3~0+--------------------+-------+----------+-------------------------------------------------------+ |
51Testing软件测试网VxA W"Dm X9C!c6B
查询数据不一致的原因是table_myisam的索引文件损坏了，对应的索引文件table_myisam.MYI与数据文件
table_myisam.MYD不一致。select
*并不需要遍历每个索引项，只需要获取第一条记录，根据链表顺序访问，因此当前的索引损坏并没有影响到select *的使用。而select
uid需要遍历所有索引项，因而只获取到损坏状态，三条索引记录。
e ]"d!U
S8]zG3g051Testing软件测试网,H c P4FVj 解决方案是使用repair table进行表索引的修复。51Testing软件测试网+{7o.{cqP
mysql> repair table table_myisam;51Testing软件测试网Y1O4Ka.Em|:@
+--------------------+--------+----------+----------+
g
e L `%p_ ?%s
?a0| Table | Op | Msg_type | Msg_text |51Testing软件测试网 i%ad g n*Hl2I
+--------------------+--------+----------+----------+51Testing软件测试网!|u;YX|Da X:bR,^ {#B
| qitai.table_myisam | repair | status | OK |
a1R
M2N q
L6g0+--------------------+--------+----------+----------+51Testing软件测试网(\Q b\.Y4C9k'}
1 row in set (0.00 sec) |
51Testing软件测试网!q
Y!j Z\WJm 修复后使用check table可以看到表状态变成正常，使用select *与select uid都能获取到4条记录。
m |![p3xS;R0mysql> check table table_myisam;
Q o q+NQg w0+--------------------+-------+----------+----------+51Testing软件测试网#`d}'kR ]G5^
| Table | Op | Msg_type | Msg_text |51Testing软件测试网:?2A5G.x[ _f
h7IP
+--------------------+-------+----------+----------+51Testing软件测试网O^#XE)o(j ^!T:sN kV
| qitai.table_myisam | check | status | OK |
i,g9? P;k$QX? Z0+--------------------+-------+----------+----------+51Testing软件测试网h`Q-ZT#i
Rl
1 row in set (0.00 sec)
收藏
举报
TAG:
