免费注册	查看新帖 \|


平台论坛博客文库

› 论坛 › 数据库技术 › MySQL › 2G的myisam表，查询慢，如何优化？【已解决】

123 4 5 6 / 6 页下一页

最近访问板块

发新帖

楼主: 小木虫子

上一主题

下一主题

2G的myisam表，查询慢，如何优化？【已解决】 [复制链接]

论坛徽章:: 0

11楼 [报告]

发表于 2009-11-03 13:14 |只看该作者

# User@Host: dev[dev] @  [10.1.1.4]
# Query_time: 6  Lock_time: 0  Rows_sent: 20  Rows_examined: 1275500
SELECT * FROM pw_gbook WHERE uid='48' ORDER BY postdate DESC LIMIT 1275480,20;
# Time: 091103 13:03:41
# User@Host: dev[dev] @  [10.1.1.4]
# Query_time: 7  Lock_time: 0  Rows_sent: 20  Rows_examined: 1325420
SELECT * FROM pw_gbook WHERE uid='48' ORDER BY postdate DESC LIMIT 1325400,20;
# Time: 091103 13:03:57
# User@Host: dev[dev] @  [10.1.1.4]
# Query_time: 7  Lock_time: 0  Rows_sent: 20  Rows_examined: 1527460
SELECT * FROM pw_gbook WHERE uid='48' ORDER BY postdate DESC LIMIT 1527440,20;
# Time: 091103 13:04:13
# User@Host: dev[dev] @  [10.1.1.4]
# Query_time: 8  Lock_time: 0  Rows_sent: 20  Rows_examined: 1529120
SELECT * FROM pw_gbook WHERE uid='48' ORDER BY postdate DESC LIMIT 1529100,20;

实战分享：从技术角度谈机器学习入门| 【大话IT】RadonDB低门槛向MySQL集群下战书 | ChinaUnix打赏功能已上线！ | 新一代分布式关系型数据库RadonDB知多少？

论坛徽章:: 0

12楼 [报告]

发表于 2009-11-03 13:28 |只看该作者

病入膏肓了都。

实战分享：从技术角度谈机器学习入门| 【大话IT】RadonDB低门槛向MySQL集群下战书 | ChinaUnix打赏功能已上线！ | 新一代分布式关系型数据库RadonDB知多少？

论坛徽章:: 9

每日论坛发贴之星
日期:2016-01-04 06:20:00

数据库技术版块每日发帖之星
日期:2016-01-04 06:20:00

每日论坛发贴之星
日期:2016-01-04 06:20:00

数据库技术版块每日发帖之星
日期:2016-01-04 06:20:00

IT运维版块每日发帖之星
日期:2016-01-04 06:20:00

IT运维版块每日发帖之星
日期:2016-01-04 06:20:00

综合交流区版块每日发帖之星
日期:2016-01-04 06:20:00

综合交流区版块每日发帖之星
日期:2016-01-04 06:20:00

数据库技术版块每周发帖之星
日期:2016-03-07 16:30:25

13楼 [报告]

发表于 2009-11-03 14:01 |只看该作者

原帖由 小木虫子 于 2009-11-3 13:14 发表
# User@Host: dev[dev] @  [10.1.1.4]
# Query_time: 6  Lock_time: 0  Rows_sent: 20  Rows_examined: 1275500
SELECT * FROM pw_gbook WHERE uid='48' ORDER BY postdate DESC LIMIT 1275480,20;
# Time:  ...

该用的索引都用了，我觉得最大的问题还是出在 LIMIT 1275480,20;上

从执行计划来看，为了这句limit，mysql还是需要读127W行以后才能给出最终答案。

我的建议是写sub qyery

select * from
pw_gbook as pw
join
(SELECT uid FROM pw_gbook WHERE uid='48' ORDER BY postdate DESC LIMIT 1275480,20) as tmp
on pw.uid=tmp.uid;

这样应该ok，楼主顺便贴一下这句的explain，再说说速度如何？

实战分享：从技术角度谈机器学习入门| 【大话IT】RadonDB低门槛向MySQL集群下战书 | ChinaUnix打赏功能已上线！ | 新一代分布式关系型数据库RadonDB知多少？

论坛徽章:: 0

14楼 [报告]

发表于 2009-11-03 14:22 |只看该作者

1。分区
2。定时分析表
3。合理索引

实战分享：从技术角度谈机器学习入门| 【大话IT】RadonDB低门槛向MySQL集群下战书 | ChinaUnix打赏功能已上线！ | 新一代分布式关系型数据库RadonDB知多少？

论坛徽章:: 0

15楼 [报告]

发表于 2009-11-03 14:25 |只看该作者

mysql> explain select * from pw_gbook as pw join (SELECT uid FROM pw_gbook WHERE uid='48' ORDER BY postdate DESC LIMIT 1275480,20) as tmp on pw.uid=tmp.uid;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 1563343
Current database: gbook

+----+-------------+------------+------+---------------+------+---------+---------+---------+--------------------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref    | rows | Extra                   |
+----+-------------+------------+------+---------------+------+---------+---------+---------+--------------------------+
|  1 | PRIMARY    | <derived2> | ALL  | NULL       | NULL | NULL | NULL |    20 |                         |
|  1 | PRIMARY    | pw       | ref  | uid          | uid  | 3    | tmp.uid |    56 |                         |
|  2 | DERIVED    | pw_gbook | ref  | uid          | uid  | 3    |       | 1781798 | Using where; Using index |
+----+-------------+------------+------+---------------+------+---------+---------+---------+--------------------------+
3 rows in set (0.97 sec)

查了1分钟，没有显示结果，我直接终止查询了。

我想问下，我这表是myisam，如果换成innodb，速度会不会快些呢？

实战分享：从技术角度谈机器学习入门| 【大话IT】RadonDB低门槛向MySQL集群下战书 | ChinaUnix打赏功能已上线！ | 新一代分布式关系型数据库RadonDB知多少？

论坛徽章:: 9

每日论坛发贴之星
日期:2016-01-04 06:20:00

数据库技术版块每日发帖之星
日期:2016-01-04 06:20:00

每日论坛发贴之星
日期:2016-01-04 06:20:00

数据库技术版块每日发帖之星
日期:2016-01-04 06:20:00

IT运维版块每日发帖之星
日期:2016-01-04 06:20:00

IT运维版块每日发帖之星
日期:2016-01-04 06:20:00

综合交流区版块每日发帖之星
日期:2016-01-04 06:20:00

综合交流区版块每日发帖之星
日期:2016-01-04 06:20:00

数据库技术版块每周发帖之星
日期:2016-03-07 16:30:25

16楼 [报告]

发表于 2009-11-03 14:51 |只看该作者

原帖由 小木虫子 于 2009-11-3 14:25 发表
mysql> explain select * from pw_gbook as pw join (SELECT uid FROM pw_gbook WHERE uid='48' ORDER BY postdate DESC LIMIT 1275480,20) as tmp on pw.uid=tmp.uid;
ERROR 2006 (HY000): MySQL server has ...

从执行计划来看，这句语句照理不应该需要那么久阿。
那光运行这句需要多久呢？
SELECT uid FROM pw_gbook WHERE uid='48' ORDER BY postdate DESC LIMIT 1275480,20

PS：我想了解一下，为什么你需要这么大的limit偏移量值？

实战分享：从技术角度谈机器学习入门| 【大话IT】RadonDB低门槛向MySQL集群下战书 | ChinaUnix打赏功能已上线！ | 新一代分布式关系型数据库RadonDB知多少？

论坛徽章:: 0

17楼 [报告]

发表于 2009-11-03 14:54 |只看该作者

回复 #16 cenalulu 的帖子

我从网上下的留言板，我也不知道开发的人怎么设计的。

实战分享：从技术角度谈机器学习入门| 【大话IT】RadonDB低门槛向MySQL集群下战书 | ChinaUnix打赏功能已上线！ | 新一代分布式关系型数据库RadonDB知多少？

论坛徽章:: 0

18楼 [报告]

发表于 2009-11-03 14:58 |只看该作者

mysql> SELECT * FROM pw_gbook WHERE uid='48' ORDER BY postdate DESC LIMIT 1275480,20;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 1571519
Current database: gbook

+--------+-----+-----------------------+----------+------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+-------+
| id    | uid | author             | authorid | type | postdate | content                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | replydate | reply |
+--------+-----+-----------------------+----------+------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+-------+
| 641448 |  48 | migliori casino onli  |       0 | 0 | 1207488577 | Give  <a href=http://blog.360.yahoo.com/blog-nofjg_Uzeqq8TdtF3kEBZ5jTEAjg?p=14>advance cash fast loan payday</a> money deck tough <a href=http://blog.360.yahoo.com/blog-haOS494zaaeSm.pOeYwiT2VRSjZxIQ--?p=25>omaha poker in linea</a> tight cashier rooms implied <a href=http://blog.360.yahoo.com/blog-878hSRgiYal5PAEqxvUCGuD0LMyWSg--?p=42>jugar poquer</a> tough company spread showdown.                                           |       0 |    |
| 641447 |  48 | advance cash loan pa  |       0 | 0 | 1207488575 | Do  <a href=http://blog.360.yahoo.com/blog-PWxWPOwyc6d2QRf0fHiSTiF_Rw--?p=60>cash till payday loan</a> hanger cripple player <a href=http://blog.360.yahoo.com/blog-PWxWPOwyc6d2QRf0fHiSTiF_Rw--?p=62>payday cash loan</a> line management racing kansas <a href=http://blog.360.yahoo.com/blog-vp9SuDw5bqJTKI37nrQoPFxBxaNSjA--?p=42>card consolidation credit debt pittsburgh</a> joint fishhooks flush baccarat!                      |       0 |    |
| 641446 |  48 | play free baccarat o  |       0 | 0 | 1207488574 | Viewed as  <a href=http://blog.360.yahoo.com/blog-PR1TXKMhKbA2QcCVzN9hMYrN_XzduA--?p=12>casino secure online gambling</a> railroad game rock value <a href=http://blog.360.yahoo.com/blog-haOS494zaaeSm.pOeYwiT2VRSjZxIQ--?p=22>poker con bonus senza deposito</a> value rooms ball bust <a href=http://blog.360.yahoo.com/blog-16p15ZQyc6NLMCgtudCODeBjKA--?p=30>advance cash loan loan payday</a> dealer club management tipping symbols? |       0 |    |
| 641445 |  48 | play free baccarat o  |       0 | 0 | 1207488574 | Think  <a href=http://blog.360.yahoo.com/blog-p.4FLSYifqPXb2KI31heR1ERNpmJmQ--?p=22>free no deposit casino bonus</a> em pineapple variations craps <a href=http://blog.360.yahoo.com/blog-vE6yJcgyc65tEsKxcAQ.aqOzEIEiAg--?p=66>video poker gratis</a> come over equity <a href=http://blog.360.yahoo.com/blog-rYuXuW0lIqclz6QrvTyd7V.pLyebAg--?p=25>juegos casino paginas web</a> cap gambling down?                                     |       0 |    |
| 641444 |  48 | play blackjack       |       0 | 0 | 1207488574 | Finally  <a href=http://blog.360.yahoo.com/blog-TvL8oq41fqruRZBOnauEDjZ7WveJLg--?p=59>descargar juegos de poker gratis</a> bankroll line banco meat bankroll player <a href=http://blog.360.yahoo.com/blog-nSPkESgoaa_zsgL7QiCUoHwV3UBg?p=24>giochi keno gratis</a> slow draw jacks hard hand <a href=http://blog.360.yahoo.com/blog-c_NYFLYlaaf3qZBPNjORDQxXEg--?p=16>jugar texas holdem</a> bankroll ball hand cheat coup pair!          |       0 |    |
| 641443 |  48 | poker no on line    |       0 | 0 | 1207488574 | At the beggining  <a href=http://blog.360.yahoo.com/blog-UrFAHzc2crM0Z_jacYIFO7qbavf2?p=42>jugar poker texas</a> scalper cripple puppy jam carpet no <a href=http://blog.360.yahoo.com/blog-16p15ZQyc6NLMCgtudCODeBjKA--?p=31>default loan payday</a> upcard tap roulette <a href=http://blog.360.yahoo.com/blog-l9UbIV41eqra2X9Zyt_0FThAw2uQIA--?p=27>loan payday software</a> rock under poker?                                        |       0 |    |
| 641442 |  48 | bill card consolidat  |       0 | 0 | 1207488572 | Recently  <a href=http://blog.360.yahoo.com/blog-nSPkESgoaa_zsgL7QiCUoHwV3UBg?p=27>giochi casino flash</a> deuces sharp hole <a href=http://blog.360.yahoo.com/blog-12UOtmk1fqfmX_TibAJn0kiX?p=97>credit card bill consolidation</a> club roulette bullets flush standoff <a href=http://blog.360.yahoo.com/blog-TvL8oq41fqruRZBOnauEDjZ7WveJLg--?p=59>descargar juegos de poker gratis</a> carpet royale puppy line!                      |       0 |    |
| 641441 |  48 | aprende a jugar poke  |       0 | 0 | 1207488571 | Three  <a href=http://blog.360.yahoo.com/blog-rYuXuW0lIqclz6QrvTyd7V.pLyebAg--?p=24>premio portal</a> line push slots paint online exotic <a href=http://blog.360.yahoo.com/blog-c_NYFLYlaaf3qZBPNjORDQxXEg--?p=16>jugar texas holdem</a> queens button joint <a href=http://blog.360.yahoo.com/blog-PR1TXKMhKbA2QcCVzN9hMYrN_XzduA--?p=13>deuces wild video poker</a> em muck beard even?                                                 |       0 |    |
| 641440 |  48 | texas holdem freewar  |       0 | 0 | 1207488571 | Each one of  <a href=http://blog.360.yahoo.com/blog-qVKvg3o0f7EXecqW_HIq0x_6huA-?p=12>gioca poker</a> natural edge spread fold south holdem <a href=http://blog.360.yahoo.com/blog-UrFAHzc2crM0Z_jacYIFO7qbavf2?p=43>jugar card stud gratis</a> white cancellation dozen blackjack <a href=http://blog.360.yahoo.com/blog-p.4FLSYifqPXb2KI31heR1ERNpmJmQ--?p=19>black jack tournament ultimate</a> stud blind ball hearts.                |       0 |    |
| 641439 |  48 | probabilit脿 roulette |       0 | 0 | 1207488571 | Dear  <a href=http://blog.360.yahoo.com/blog-PR1TXKMhKbA2QcCVzN9hMYrN_XzduA--?p=13>video poker software</a> kings road strategies overplay number hopper <a href=http://blog.360.yahoo.com/blog-haOS494zaaeSm.pOeYwiT2VRSjZxIQ--?p=23>giochi seven card stud gratis</a> rake split roulette <a href=http://blog.360.yahoo.com/blog-ee6O29c9erSXs6GzQTod7XaUMb5N?p=16>payday loan cash advance loan</a> juice edge score!                   |       0 |    |
| 641438 |  48 | poquer gratis       |       0 | 0 | 1207488571 | Find  <a href=http://blog.360.yahoo.com/blog-PR1TXKMhKbA2QcCVzN9hMYrN_XzduA--?p=15>uk best casino online</a> carribean grip semi <a href=http://blog.360.yahoo.com/blog-ee6O29c9erSXs6GzQTod7XaUMb5N?p=17>calgary loan payday</a> blind nuts cap keno glimmer <a href=http://blog.360.yahoo.com/blog-TvL8oq41fqruRZBOnauEDjZ7WveJLg--?p=60>torneos de poquer</a> stack pair second.                                                       |       0 |    |
| 641437 |  48 | casino on line grati  |       0 | 0 | 1207488571 | Turn  <a href=http://blog.360.yahoo.com/blog-16p15ZQyc6NLMCgtudCODeBjKA--?p=30>advance cash loan loan payday</a> clubs blind empirepoker <a href=http://blog.360.yahoo.com/blog-16p15ZQyc6NLMCgtudCODeBjKA--?p=33>approval instant loan online payday</a> boat war rainbow longshot <a href=http://blog.360.yahoo.com/blog-wMEUXTw9a6dJE8CTNXvXQvUxNgGh?p=15>play free casino game for fun</a> joint gross glossary!                      |       0 |    |
| 641436 |  48 | free no deposit casi  |       0 | 0 | 1207488571 | After  <a href=http://blog.360.yahoo.com/blog-eBUhYZsic6O.pkKt8N3lTt063pVvjw--?p=43>slots machines</a> rolling joint handicapper crown buy <a href=http://blog.360.yahoo.com/blog-vE6yJcgyc65tEsKxcAQ.aqOzEIEiAg--?p=65>casino online autorizzati</a> check bet road table <a href=http://blog.360.yahoo.com/blog-eBUhYZsic6O.pkKt8N3lTt063pVvjw--?p=39>trucchi casino online</a> tough lowball overplay em.                               |       0 |    |
| 641435 |  48 | approval instant loa  |       0 | 0 | 1207488570 | Exception  <a href=http://blog.360.yahoo.com/blog-878hSRgiYal5PAEqxvUCGuD0LMyWSg--?p=42>jugar poquer</a> vegas strategies easy rank <a href=http://blog.360.yahoo.com/blog-TvL8oq41fqruRZBOnauEDjZ7WveJLg--?p=58>pacific poker</a> come railbirds small tell cancellation punto <a href=http://blog.360.yahoo.com/blog-vE6yJcgyc65tEsKxcAQ.aqOzEIEiAg--?p=67>casino on line gratis</a> up empirepoker dollar lock.                         |       0 |    |
| 641434 |  48 | free no deposit casi  |       0 | 0 | 1207488569 | Am  <a href=http://blog.360.yahoo.com/blog-16p15ZQyc6NLMCgtudCODeBjKA--?p=34>loan payday quick</a> wiseguy carribean nuts <a href=http://blog.360.yahoo.com/blog-nSPkESgoaa_zsgL7QiCUoHwV3UBg?p=26>gioco roulette</a> double belly keno <a href=http://blog.360.yahoo.com/blog-nSPkESgoaa_zsgL7QiCUoHwV3UBg?p=27>giochi casino flash</a> grinder boat less puppy.                                                                         |       0 |    |
| 641433 |  48 | calgary payday loan |       0 | 0 | 1207488569 | Get started  <a href=http://blog.360.yahoo.com/blog-878hSRgiYal5PAEqxvUCGuD0LMyWSg--?p=39>juega poquer</a> garden out minus hearts door rag <a href=http://blog.360.yahoo.com/blog-eBUhYZsic6O.pkKt8N3lTt063pVvjw--?p=41>migliori casino online</a> turn acquaintance vigorish represent racing hopper <a href=http://blog.360.yahoo.com/blog-haOS494zaaeSm.pOeYwiT2VRSjZxIQ--?p=25>omaha poker in linea</a> dollar aces upcard!          |       0 |    |
| 641432 |  48 | tragaperra web       |       0 | 0 | 1207488569 | In general  <a href=http://blog.360.yahoo.com/blog-haOS494zaaeSm.pOeYwiT2VRSjZxIQ--?p=24>strip poker game online</a> banco jam up <a href=http://blog.360.yahoo.com/blog-c_NYFLYlaaf3qZBPNjORDQxXEg--?p=18>poquer gratis</a> nuts line churn blind <a href=http://blog.360.yahoo.com/blog-eBUhYZsic6O.pkKt8N3lTt063pVvjw--?p=41>migliori casino online</a> racing boat line hanger wiseguy no.                                           |       0 |    |
| 641431 |  48 | play slots online    |       0 | 0 | 1207488562 | Here  <a href=http://blog.360.yahoo.com/blog-kKpXXSM9frWTcwiwX4_KLA8-?p=42>jugar card stud</a> joker credit raise <a href=http://blog.360.yahoo.com/blog-MEL_wig_Lv_C0YYeFiE4VxwMg4GP5w--?p=21>online baccarat spielen</a> table racing card parlay cripple buy <a href=http://blog.360.yahoo.com/blog-rw7fC4c8dKil8Mf9zg0uP.jmn.nI?p=22>no fax cash advance</a> club beard pay vegas credit?                                              |       0 |    |
| 641430 |  48 | john.taylor@aon.at |       0 | 0 | 1207488544 |    Enjoyed your website and found it informative.reservations airline tickets <a href="http://flay-2a.w8w.pl/?reservations-airline-tickets">reservations airline tickets</a>                                                                                                                                                                                                       |       0 |    |
| 641429 |  48 | sigmroni@hotmail.com  |       0 | 0 | 1207488543 |    Take care of it and keep it on the road!caira ticket <a href="http://flay-2a.w8w.pl/?caira-ticket">caira ticket</a>                                                                                                                                                                                                                                                                               |       0 |    |
+--------+-----+-----------------------+----------+------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+-------+
20 rows in set (7.63 sec)

实战分享：从技术角度谈机器学习入门| 【大话IT】RadonDB低门槛向MySQL集群下战书 | ChinaUnix打赏功能已上线！ | 新一代分布式关系型数据库RadonDB知多少？

论坛徽章:: 9

每日论坛发贴之星
日期:2016-01-04 06:20:00

数据库技术版块每日发帖之星
日期:2016-01-04 06:20:00

每日论坛发贴之星
日期:2016-01-04 06:20:00

数据库技术版块每日发帖之星
日期:2016-01-04 06:20:00

IT运维版块每日发帖之星
日期:2016-01-04 06:20:00

IT运维版块每日发帖之星
日期:2016-01-04 06:20:00

综合交流区版块每日发帖之星
日期:2016-01-04 06:20:00

综合交流区版块每日发帖之星
日期:2016-01-04 06:20:00

数据库技术版块每周发帖之星
日期:2016-03-07 16:30:25

19楼 [报告]

发表于 2009-11-03 15:08 |只看该作者

我想看的是select uid 的时间，不是select *

实战分享：从技术角度谈机器学习入门| 【大话IT】RadonDB低门槛向MySQL集群下战书 | ChinaUnix打赏功能已上线！ | 新一代分布式关系型数据库RadonDB知多少？

论坛徽章:: 0

20楼 [报告]

发表于 2009-11-03 15:26 |只看该作者

mysql> SELECT uid FROM pw_gbook WHERE uid='48' ORDER BY postdate DESC LIMIT 1275480,20;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 1577531
Current database: gbook

+-----+
| uid |
+-----+
|  48 |
|  48 |
|  48 |
|  48 |
|  48 |
|  48 |
|  48 |
|  48 |
|  48 |
|  48 |
|  48 |
|  48 |
|  48 |
|  48 |
|  48 |
|  48 |
|  48 |
|  48 |
|  48 |
|  48 |
+-----+
20 rows in set (0.96 sec)

实战分享：从技术角度谈机器学习入门| 【大话IT】RadonDB低门槛向MySQL集群下战书 | ChinaUnix打赏功能已上线！ | 新一代分布式关系型数据库RadonDB知多少？

123 4 5 6 / 6 页下一页

发新帖

Chinaunix › 论坛 › 数据库技术 › MySQL › 2G的myisam表，查询慢，如何优化？【已解决】

北京盛拓优讯信息技术有限公司. 版权所有京ICP备16024965号-6 北京市公安局海淀分局网监中心备案编号：11010802020122 niuxiaotong@pcpop.com 17352615567
未成年举报专区
中国互联网协会会员联系我们：huangweiwei@itpub.net
感谢所有关心和支持过ChinaUnix的朋友们转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP