mysql 优化步骤

plantegg

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