mysql 提升临时表的性能_优化临时表使用，提升SQL语句性能

发现线程产生大量临时表，产生大量临时表，就意味着性能低。查看机器IO，发现IO飙升：

IO情况：

Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await

svctm %util

fioa 0.00 0.00 0.00 1922.00 0.00 1271.95 1355.33 1904.59 1872.90

0.52 100.40

sda 0.00 45.00 7.00 6.00 0.03 0.20 35.69 0.00 0.00 0.00

0.00

原因是一条SQL产生了大量的临时表，内存放不下，于是拷贝到磁盘，导致IO飙升！该SQL查询了大量的数据(条数，以及stb_topics`.*)，然后使用了order

by产生临时表，最后又只返回了 50条数据

explain分析该SQL：

mysql>

explain SELECT

`stb_topics`.*, `b`.`username`, `b`.`nickname`, `b`.`avatar`, `c`.`username`

as rname, `d`.`cname`

->

FROM (`stb_topics`)

->

LEFT JOIN `stb_users` b ON

`b`.`uid` = `stb_topics`.`uid`

->

LEFT JOIN `stb_users` c ON

`c`.`uid` = `stb_topics`.`ruid`

->

LEFT JOIN `stb_nodes` d ON

`d`.`node_id` = `stb_topics`.`node_id`

->

WHERE `stb_topics`.`is_hidden` = 0

->

ORDER BY `is_top` desc, `ord` desc

->

LIMIT 50

-> ;

+----+-------------+------------+------+---------------+---------+---------+--------------------+--------+----------------------------------------------+

| id | select_type | table

|

type | possible_keys | key

|

key_len | ref

|

rows  | Extra

|

+----+-------------+------------+------+---------------+---------+---------+--------------------+--------+----------------------------------------------+

| 1 |

SIMPLE

|

stb_topics | ALL  | NULL

|

NULL  | NULL

| NULL

|

211771 | Using where; Using temporary; Using filesort |

| 1 |

SIMPLE

|

b

|

ref  | PRIMARY

|

PRIMARY | 4

|

wx.stb_topics.uid

| 1

| |

| 1 |

SIMPLE

|

c

|

ref  | PRIMARY

|

PRIMARY | 4

|

wx.stb_topics.ruid

| 1

| |

| 1 |

SIMPLE

|

d

|

ALL  | PRIMARY

|

NULL  | NULL

| NULL

| 7

| |

+----+-------------+------------+------+---------------+---------+---------+--------------------+--------+----------------------------------------------+

从explain分析结果可知，该SQL产生临时表，并且伴随文件排序操作。

优化思路的思路是拆分SQL，将排序操作和查询所有信息的操作分开：

第一个条语句：根据`stb_topics`.topic_id 查询符合条件的数据：

SELECT `stb_topics`.topic_id FROM (`stb_topics`) LEFT JOIN

`stb_users` b ON

`b`.`uid` = `stb_topics`.`uid`

LEFT JOIN `stb_users` c ON

`c`.`uid` = `stb_topics`.`ruid`

LEFT JOIN `stb_nodes` d ON

`d`.`node_id` = `stb_topics`.`node_id`

WHERE `stb_topics`.`is_hidden` = 0

ORDER BY `is_top` desc, `ord` desc

LIMIT 50 第二条语句：查询符合条件的详细数据，将第一条sql的结果使用in操作拼接到第二条的sql

SELECT

`stb_topics`.*, `b`.`username`, `b`.`nickname`, `b`.`avatar`, `c`.`username`

as rname, `d`.`cname`

FROM (`stb_topics`)

LEFT JOIN `stb_users` b ON

`b`.`uid` = `stb_topics`.`uid`

LEFT JOIN `stb_users` c ON

`c`.`uid` = `stb_topics`.`ruid`

LEFT JOIN `stb_nodes` d ON

`d`.`node_id` = `stb_topics`.`node_id`

WHERE `stb_topics`.`is_hidden` = 0 and

stb_topics.topic_id in (......) ORDER BY `is_top`

desc, `ord` desc