mysql 在线表table1 (有一个text字段 a)
text和blob字段类型是不会被buffer存储的.而是存储在tmp_table中.tmp_table是存在磁盘中的.因此引起磁盘IO的问题.请看下该mysql所在linux的 当前状态[root@www var]# vmstat 1 10
procs memory swap io system cpu
r b swpd free buff cache si so bi bo in cs us sy id wa
4 9 114368 16208 4600 855608 1 2 0 2 2 0 2 2 2 1
2 11 114852 17716 4604 854524 212 616 3476 616 317 872 1 2 0 97
0 8 115184 18804 4632 853624 140 420 3704 588 292 763 3 3 0 95
0 9 115600 17292 4632 855476 104 504 2884 504 269 749 4 4 0 92
1 11 115864 16104 4636 856968 68 264 3492 264 262 682 4 2 0 94
5 7 116368 17068 4640 856460 128 604 4212 604 337 916 5 2 0 93[root@www var]# top -d 116:33:31 up 82 days, 14:46, 2 users, load average: 8.17, 4.53, 3.05
144 processes: 141 sleeping, 3 running, 0 zombie, 0 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 4.3% 0.0% 2.5% 0.1% 0.0% 92.8% 0.0%
cpu00 5.5% 0.0% 1.7% 0.0% 0.0% 92.7% 0.0%
cpu01 3.1% 0.0% 3.4% 0.3% 0.0% 93.0% 0.0%
Mem: 1028320k av, 1011796k used, 16524k free, 0k shrd, 4800k buff| 1672784 | root | localhost | word | Query | 380 | Sending data | SELECT c.* FROM pwcms_comment c WHERE c.itemid='55' AND ifcheck=1 ORDER BY postdate LIMIT 44800,20 |
| 1672792 | root | localhost | word | Query | 354 | Sending data | SELECT c.* FROM pwcms_comment c WHERE c.itemid='64' AND ifcheck=1 ORDER BY postdate LIMIT 16160,20 |
| 1672804 | root | localhost | word | Query | 307 | Sending data | SELECT c.* FROM pwcms_comment c WHERE c.itemid='43' AND ifcheck=1 ORDER BY postdate LIMIT 17160,20 |
| 1672807 | root | localhost | word | Query | 298 | Sending data | SELECT c.* FROM pwcms_comment c WHERE c.itemid='44' AND ifcheck=1 ORDER BY postdate LIMIT 67780,20 |
| 1672816 | root | localhost | word | Query | 281 | Sending data | SELECT c.* FROM pwcms_comment c WHERE c.itemid='43' AND ifcheck=1 ORDER BY postdate LIMIT 17160,20 |
| 1672822 | root | localhost | word | Query | 257 | Sending data | SELECT c.* FROM pwcms_comment c WHERE c.itemid='65' AND ifcheck=1 ORDER BY postdate LIMIT 10020,20 |
| 1672825 | root | localhost | word | Query | 218 | Sending data | SELECT c.* FROM pwcms_comment c WHERE c.itemid='65' AND ifcheck=1 ORDER BY postdate LIMIT 10020,20 |
| 1672830 | root | localhost | word | Query | 206 | Sending data | SELECT c.* FROM pwcms_comment c WHERE c.itemid='66' AND ifcheck=1 ORDER BY postdate LIMIT 23120,20 |
| 1672833 | root | localhost | word | Query | 201 | Sending data | SELECT c.* FROM pwcms_comment c WHERE c.itemid='82' AND ifcheck=1 ORDER BY postdate LIMIT 40440,20 |
| 1672837 | root | localhost | word | Query | 176 | Sending data | SELECT c.* FROM pwcms_comment c WHERE c.itemid='66' AND ifcheck=1 ORDER BY postdate LIMIT 23120,20 |
| 1672845 | root | localhost | word | Query | 162 | Sending data | SELECT c.* FROM pwcms_comment c WHERE c.itemid='55' AND ifcheck=1 ORDER BY postdate LIMIT 37260,20 |
| 1672854 | root | localhost | word | Query | 132 | Sending data | SELECT c.* FROM pwcms_comment c WHERE c.itemid='55' AND ifcheck=1 ORDER BY postdate LIMIT 37260,20 |
问题
1 这里对sql引起的磁盘io性能下降 不知道如何进行分析 ， 如何针对上面的现实信息进行分析磁盘io有问题？？
2 如何做data index数据分离 在mysql？
调整系统参数(极端需要swap时才使用)
echo 0 >/proc/sys/vm/swappiness将mysql变量tempdir指向到tmpfs下面这种语句会非常占用资源
SELECT c.* FROM pwcms_comment c WHERE c.itemid='55' AND ifcheck=1 ORDER BY postdate LIMIT 37260,20;您可以建立复合索引(itemid,ifcheck,postdate)
然后使用上面的索引先检查primary key or unique key ,这里假设是idSELECT c.* FROM pwcms_comment as c join (SELECT id FROM pwcms_comment WHERE itemid='55' AND ifcheck=1 ORDER BY postdate LIMIT 37260,20) as p on c.id=p.id;