mysql 优化步骤

glinuz

浏览: 18401 次

最近访客更多访客>>

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

mysql 优化

MySQL Cache SQL thread .net

1. 缓存优化
编辑my.cnf 配置文件
#不进行域名反解析,注意由此带来的权限/授权问题
skip-name-resolve
#索引缓存,根据内存大小而定,如果是独立的db服务器,可以设置高达80%的内存总量
key_buffer = 512M
#连接排队列表总数
back_log = 200 max_allowed_packet = 2M
#打开表缓存总数,可以避免频繁的打开数据表产生的开销
table_cache = 512
#每个线程排序所需的缓冲
sort_buffer_size = 4M
#每个线程读取索引所需的缓冲
read_buffer_size = 4M
#MyISAM表发生变化时重新排序所需的缓冲
myisam_sort_buffer_size = 64M
#缓存可重用的线程数
thread_cache = 128
#查询结果缓存
query_cache_size = 128M
#设置超时时间,能避免长连接
set-variable = wait_timeout=60
#最大并发线程数,cpu数量*2
thread_concurrency = 4
#记录慢查询,然后对慢查询一一优化
log-slow-queries = slow.log
long_query_time = 1
2.索引优化
使用explain对慢查询进行分析。
对explain结果的详细说明可以看mysql手册。
从EXPLAIN的输出包括下面列：
table 输出的行所引用的表。
type 联结类型。
各种类型的信息在下面给出。
possible_keys possible_keys列指出MySQL能使用哪个索引在该表中找到行。注意，该列完全独立于表的次序。这意味着在possible_keys中的某些键实际上不能以生成的表次序使用。如果该列是空的，没有相关的索引。在这种情况下，你也许能通过检验WHERE子句看是否它引用某些列或列不是适合索引来提高你的查询性能。如果是这样，创造一个适当的索引并且在用EXPLAIN检查查询。见7.8 ALTER TABLE句法。为了看清一张表有什么索引，使用SHOW INDEX FROM tbl_name。

key key列显示MySQL实际决定使用的键。如果没有索引被选择，键是NULL。

key_len key_len列显示MySQL决定使用的键长度。如果键是NULL，长度是NULL。注意这告诉我们MySQL将实际使用一个多部键值的几个部分。

ref ref列显示哪个列或常数与key一起用于从表中选择行。

rows rows列显示MySQL相信它必须检验以执行查询的行数。

Extra 如果Extra列包括文字Only index，这意味着信息只用索引树中的信息检索出的。通常，这比扫描整个表要快。如果Extra列包括文字where used，它意味着一个WHERE子句将被用来限制哪些行与下一个表匹配或发向客户。

不同的联结类型列在下面，以最好到最差类型的次序： system 表仅有一行(=系统表)。这是const联结类型的一个特例。 const 表有最多一个匹配行，它将在查询开始时被读取。因为仅有一行，在这行的列值可被剩下的优化器认为是常数。 const表很快，因为它们只读取一次！ eq_ref 对于每个来自于先前的表的行组合，从该表中读取一行。这可能是最好的联结类型，除了const类型。它用在一个索引的所有部分被联结使用并且索引是UNIQUE或PRIMARY KEY。 ref 对于每个来自于先前的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联结只使用键的最左面前缀，或如果键不是UNIQUE或PRIMARY KEY（换句话说，如果联结不能基于键值选择单个行的话)，使用ref。如果被使用的键仅仅匹配一些行，该联结类型是不错的。 range 只有在一个给定范围的行将被检索，使用一个索引选择行。ref列显示哪个索引被使用。 index 这与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。 ALL 对于每个来自于先前的表的行组合，将要做一个完整的表扫描。如果表格是第一个没标记const的表，这通常不好，并且通常在所有的其他情况下很差。你通常可以通过增加更多的索引来避免ALL，使得行能从早先的表中基于常数值或列值被检索出。通过相乘EXPLAIN输出的rows行的所有值，你能得到一个关于一个联结要多好的提示。这应该粗略地告诉你MySQL必须检验多少行以执行查询。当你使用max_join_size变量限制查询时，也用这个数字。

实例：

查询SQL大致如下:
explain SELECT t.* FROM cdb_posts p, cdb_threads t WHERE t.fid IN ('37', '45', '4', '6', '17', '41', '28', '32', '31', '1', '42') AND p.tid=t.tid AND p.author LIKE 'JoansWin' GROUP BY t.tid ORDER BY lastpost DESC LIMIT 0, 80; 用 EXPLAIN 分析的结果如下: mysql>EXPLAIN SELECT t.* FROM cdb_posts p, cdb_threads t WHERE t.fid IN ('37', '45', '4', '6', '17', '41', '28', '32', '31', '1', '42') AND p.tid=t.tid AND p.author LIKE 'JoansWin' GROUP BY t.tid ORDER BY lastpost DESC LIMIT 0, 80;

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
| 1 | SIMPLE | t | range | PRIMARY,fid | fid | 2 | NULL | 66160 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | p | ref | tid | tid | 3 | Forum.t.tid | 10 | Using where |

只用到了 t.fid 和 p.tid,而 p.author 则没有索引可用,总共需要扫描 66160*10 = 661600 次索引,够夸张吧.

再分析 cdb_threads 和 cdb_posts 的索引情况:

mysql>show index from cdb_posts;
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

|cdb_posts | 0 | PRIMARY | 1 | pid | A | 680114 | NULL | NULL | | BTREE | |
| cdb_posts | 1 | fid | 1 | fid | A | 10 | NULL | NULL | | BTREE | |
| cdb_posts | 1 | tid | 1 | tid | A | 68011 | NULL | NULL | | BTREE | |
| cdb_posts | 1 | tid | 2 | dateline | A | 680114 | NULL | NULL | | BTREE | |
| cdb_posts | 1 | dateline | 1 | dateline | A | 680114 | NULL | NULL | | BTREE | |

以及 mysql>show index from cdb_threads;

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
| cdb_threads | 0 | PRIMARY | 1 | tid | A | 68480 | NULL | NULL | | BTREE | |
| cdb_threads | 1 | lastpost | 1 | topped | A | 4 | NULL | NULL | | BTREE | |
| cdb_threads | 1 | lastpost | 2 | lastpost | A | 68480 | NULL | NULL | | BTREE | |
| cdb_threads | 1 | lastpost | 3 | fid | A | 68480 | NULL | NULL | | BTREE | |
| cdb_threads | 1 | replies | 1 | replies | A | 233 | NULL | NULL | | BTREE | |
| cdb_threads | 1 | dateline | 1 | dateline | A | 68480 | NULL | NULL | | BTREE | |
| cdb_threads | 1 | fid | 1 | fid | A | 10 | NULL | NULL | | BTREE | |
| cdb_threads | 1 | enablehot | 1 | enablehot | A | 2 | NULL | NULL | | BTREE | |

看到索引 fid 和 enablehot 基数太小,看来该索引完全没必要,不过,对于fid基数较大的情况,则可能需要保留>该索引.
所做修改如下:
ALTER TABLE `cdb_threads` DROP INDEX `enablehot`, DROP INDEX `fid`, ADD INDEX (`fid`, `lastpost`);
ALTER TABLE `cdb_posts` DROP INDEX `fid`, ADD INDEX (`author`(10));
OPTIMIZE TABLE `cdb_posts`;
OPTIMIZE TABLE `cdb_threads`;
在这里, p.author 字段设定的部分索引长度是 10, 是经过分析后得出来的结果,不同的系统,这里的长度也不同,最好自己先取一下平均值,然后再适当调整.
现在,再来执行一次上面的慢查询,发现时间已经从 6s 变成 0.19s,提高了 30 倍.
总之，mysql总的的查询时explain的row的字段乘积。努力的方向就是减少rows的值。尤其是在多个表关联查询时，可以考虑是用子查询代替and操作。
参考：
http://x.discuz.net/viewthread-586137.html
mysql手册的EXPLAIN

分享到：