请教两个关于MySQL的性能调校及索引问题

wasuka 2010-04-12 10:38:38
问题1：

MySQL版本是5.1.37-1ubuntu5，执行UPDATE很慢，SHOW PROCESSLIST看到SQL的State是“freeing items”，大部分时间都花在了这步。请教如何解决？


==============================================================


问题2：

MySQL版本是5.0.75-0ubuntu10.3，两个数据表，结构如下：

CREATE TABLE A
(
ID MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
BID MEDIUMINT(8) UNSIGNED NOT NULL
)
ENGINE = InnoDB;

CREATE TABLE B
(
ID MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
AID MEDIUMINT(8) UNSIGNED NOT NULL
)
ENGINE = InnoDB;

两表数据量相同，都是100万；
执行SQL“ALTER TABLE A ADD PRIMARY KEY (ID, BID), ADD INDEX (BID)”，大约用时5分钟；
执行SQL“ALTER TABLE B ADD PRIMARY KEY (ID, AID), ADD INDEX (AID)”，大约用时8小时，过程中使用SHOW PROCESSLIST查看State为“copy to tmp table”。
请教，两表结构相同、数据也大致相同，为什么耗时相差悬殊、如何解决？


谢谢！
...全文
261 24 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
24 条回复
切换为时间正序
请发表友善的回复…
发表回复
WWWWA 2010-04-16
  • 打赏
  • 举报
 回复
都是在同一块硬盘上？
wasuka 2010-04-16
  • 打赏
  • 举报
 回复
[Quote=引用 20 楼 xys_777 的回复:]

执行SQL“ALTER TABLE A ADD PRIMARY KEY (ID, BID), ADD INDEX (BID)”，大约用时5分钟；
执行SQL“ALTER TABLE B ADD PRIMARY KEY (ID, AID), ADD INDEX (AID)”，大约用时8小时;

创建索引和数据有很大关系，看看两个表的数据什么区别。
[/Quote]


没有什么太大的区别，都是整数，AID和BID分别对应另外两表的ID。
wasuka 2010-04-16
  • 打赏
  • 举报
 回复
[Quote=引用 22 楼 wwwwa 的回复:]

都是在同一块硬盘上？
[/Quote]

是的
wasuka 2010-04-15
  • 打赏
  • 举报
 回复
是在一台机器上。分析后结果依旧。
永生天地 2010-04-15
  • 打赏
  • 举报
 回复
执行SQL“ALTER TABLE A ADD PRIMARY KEY (ID, BID), ADD INDEX (BID)”，大约用时5分钟；
执行SQL“ALTER TABLE B ADD PRIMARY KEY (ID, AID), ADD INDEX (AID)”，大约用时8小时;

创建索引和数据有很大关系，看看两个表的数据什么区别。
shenshine 2010-04-14
  • 打赏
  • 举报
 回复
要不这样吧,你先分析下表然后再执行看看:

analyze table b;

shenshine 2010-04-14
  • 打赏
  • 举报
 回复
看了下你贴出的结果,没什么差别啊,就是b表的非主键索引的Cardinality比a表的小,但这个应该不会导致你这个明显问题啊,很奇怪

对了,你2个语句是在同一机器上面执行的?
wasuka 2010-04-13
  • 打赏
  • 举报
 回复
[Quote=引用 13 楼 wwwwb 的回复:]

是同一数据库的表？你新建一个数据库，将B表导入，再测试一下，看看是否是硬盘的问题
[/Quote]

这样快了很多，大约用了3个小时。
wasuka 2010-04-12
  • 打赏
  • 举报
 回复
[Quote=引用 6 楼 acmain_chm 的回复:]

两表数据量相同，都是100万；
执行SQL“ALTER TABLE A ADD PRIMARY KEY (ID, BID), ADD INDEX (BID)”，大约用时5分钟；
执行SQL“ALTER TABLE B ADD PRIMARY KEY (ID, AID), ADD INDEX (AID)”，大约用时8小时，过程中使用SHOW PROCESSLIST查看State为“copy t……
[/Quote]

没有，只有当前SQL在执行。
现在的情况是表没有索引，导入数据后建立索引，耗时8小时；如果索引是之前就建好的，倒数据一样也需要8小时左右。
wasuka 2010-04-12
  • 打赏
  • 举报
 回复
[Quote=引用 5 楼 acmain_chm 的回复:]

MySQL版本是5.1.37-1ubuntu5，执行UPDATE很慢，SHOW PROCESSLIST看到SQL的State是“freeing items”，大部分时间都花在了这步。请教如何解决？

具体语句是什么？有没有where 从句？ 如果没有，则是全表更新，速度不会太快，并且受锁的影响。
[/Quote]

不论是什么语句，比如“UPDATE table SET filed = field + 1”或者“UPDATE table SET filed = field + 1 WHERE ID = 1”，都会有“freeing items”占用大量时间。
而且，相同的表和数据，在另一台服务器上执行就没有上述问题。
ACMAIN_CHM 2010-04-12
  • 打赏
  • 举报
 回复
两表数据量相同，都是100万；
执行SQL“ALTER TABLE A ADD PRIMARY KEY (ID, BID), ADD INDEX (BID)”，大约用时5分钟；
执行SQL“ALTER TABLE B ADD PRIMARY KEY (ID, AID), ADD INDEX (AID)”，大约用时8小时，过程中使用SHOW PROCESSLIST查看State为“copy to tmp table”。
请教，两表结构相同、数据也大致相同，为什么耗时相差悬殊、如何解决？

检查一下有没有其它进程锁住了这个表。
ACMAIN_CHM 2010-04-12
  • 打赏
  • 举报
 回复
MySQL版本是5.1.37-1ubuntu5，执行UPDATE很慢，SHOW PROCESSLIST看到SQL的State是“freeing items”，大部分时间都花在了这步。请教如何解决？

具体语句是什么？有没有where 从句？ 如果没有，则是全表更新，速度不会太快，并且受锁的影响。
wasuka 2010-04-12
  • 打赏
  • 举报
 回复
单独执行是什么意思？有100万数据的情况下，至少8小时。
WWWWA 2010-04-12
  • 打赏
  • 举报
 回复
如果单独执行ALTER TABLE B ADD PRIMARY KEY (ID, AID), ADD INDEX (AID)
这句，要多长时间
wasuka 2010-04-12
  • 打赏
  • 举报
 回复
[Quote=引用 1 楼 wwwwa 的回复:]

数据量有多大？
[/Quote]

问题1中情况我只测试了一个表，大约12000条数据。
同样的表、数据和操作，如果在其它服务器上执行是正常的。
WWWWA 2010-04-12
  • 打赏
  • 举报
 回复
数据量有多大？
wasuka 2010-04-12
  • 打赏
  • 举报
 回复
[Quote=引用 14 楼 acmain_chm 的回复:]

这样

show create table a;
show create table b;
show index from a;
show index from b;
SHOW TABLE STATUS like 'a';
SHOW TABLE STATUS like 'b';


贴出来看一下
[/Quote]


show create table a;

CREATE TABLE `a` (
`ID` smallint(5) unsigned NOT NULL,
`BID` mediumint(8) unsigned NOT NULL,
PRIMARY KEY (`ID`,`BID`),
KEY `BID` (`BID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

==========================================

show create table b;

CREATE TABLE `b` (
`ID` mediumint(8) unsigned NOT NULL,
`AID` mediumint(8) unsigned NOT NULL,
PRIMARY KEY (`ID`,`AID`),
KEY `AID` (`AID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

==========================================

show index from a\G

*************************** 1. row ***************************
Table: a
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: ID
Collation: A
Cardinality: 4287
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 2. row ***************************
Table: a
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 2
Column_name: BID
Collation: A
Cardinality: 1080549
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 3. row ***************************
Table: a
Non_unique: 1
Key_name: BID
Seq_in_index: 1
Column_name: BID
Collation: A
Cardinality: 28435
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:

==========================================

show index from b;

*************************** 1. row ***************************
Table: b
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: ID
Collation: A
Cardinality: 33315
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 2. row ***************************
Table: b
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 2
Column_name: AID
Collation: A
Cardinality: 1066089
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 3. row ***************************
Table: b
Non_unique: 1
Key_name: AID
Seq_in_index: 1
Column_name: AID
Collation: A
Cardinality: 1773
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:

==========================================

SHOW TABLE STATUS like 'a'\G

Name: a
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 1055536
Avg_row_length: 27
Data_length: 28901376
Max_data_length: 0
Index_length: 19415040
Data_free: 0
Auto_increment: NULL
Create_time: 2010-04-12 09:11:29
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment: a; InnoDB free: 4096 kB

==========================================

SHOW TABLE STATUS like 'b';

Name: b
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 1190545
Avg_row_length: 47
Data_length: 56197120
Max_data_length: 0
Index_length: 13123584
Data_free: 0
Auto_increment: NULL
Create_time: 2010-04-12 15:07:18
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment: b; InnoDB free: 4096 kB
ACMAIN_CHM 2010-04-12
  • 打赏
  • 举报
 回复
这样

show create table a;
show create table b;
show index from a;
show index from b;
SHOW TABLE STATUS like 'a';
SHOW TABLE STATUS like 'b';


贴出来看一下
wwwwb 2010-04-12
  • 打赏
  • 举报
 回复
是同一数据库的表？你新建一个数据库，将B表导入，再测试一下，看看是否是硬盘的问题
wasuka 2010-04-12
  • 打赏
  • 举报
 回复
[Quote=引用 11 楼 wwwwb 的回复:]

引用 4 楼 wasuka 的回复:
单独执行是什么意思？有100万数据的情况下，至少8小时。

你是先执行
“ALTER TABLE A ADD PRIMARY KEY (ID, BID), ADD INDEX (BID)”
再执行
SQL“ALTER TABLE B ADD PRIMARY KEY (ID, AID), ADD INDEX (AID)”
？

还是分别单独执……
[/Quote]


单独执行试过了，ALTER TABLE B依然很慢。
加载更多回复（3）
数据库访问的性能问题与瓶颈问题
声明：本文是一篇有争议的文章，甚至有可能是一篇争议非常大的文章，可能争来争去依然无法得到一个统一的意见。场景个别公司的技术决策者要求团队的开发人员在编写数据访问层的时候，禁止在程序中出现任何的SQL语句，禁止使用Entity Library，禁止使用NBear、NHibernate、IBatis、Entity Framework等ORM框架，只允许使用存储过程。试想一下，您的公司是
题解 | #拦截导弹# java &；&； c++
先自我介绍，然后问项目。29所参加了提前批进了终面，但是由于投递的岗位不在提前批次招聘所以挂了，重新投递正式批，宣讲会上老师说都可以面试，但是好像直接没有面试机会了(没有面试短信，首次。实习转正失败后，整个人信心崩塌，面试官质疑我不能毕业，大哭特哭，回来秋招连面试都很少很少，进华池子是俺最近收到的最好的消息啦，虽然不一定能泡开，但好在没有挂我给。#用友sp#本人是前端开发，用友薪资开的比较高，参照offershow上的sp开奖，这两天就要申请签约了，感觉比往年美团携程都高，有了解用友情况的uu可以分享下。
#Pick女友 #亿纬锂能 VS 欣旺达 #售前技术支持岗位
合肥就业求职好公司及薪资汇总 接到华为的保温电话了 shein希音二面(已凉) SHEIN希音Java后端一面二面Hr面CTO面面经结果挂 【10.22更新】24届求职黑名单汇总贴 刚毕业干了一个月 被裁 秋招结束啦，卸载牛客！ 秋招结束啦，卸载牛客！ 签了比亚迪拒了广东电网，肠子都要悔青了 掌
基于感应加热的避雷器电压致热缺陷仿真模拟研究.zip
基于感应加热的避雷器电压致热缺陷仿真模拟研究.zip
高并发访问量下网络I-O模型选择的研究.docx
高并发访问量下网络I-O模型选择的研究.docx
MySQL

56,875

社区成员

56,754

社区内容

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章