2010-03-22--03-30 MySQL管理基础4--性能优化

1.索引

<1> 应该添加索引的列包括：用于连接的列，WHERE、GROUP BY或ORDER BY子句涉及的列。
<2> 有很多重复值的列不能通过建立索引来改善性能，取值唯一的列则能充分通过索引获益。
<3> 索引并非越多越方便，它可能在增加搜索速度的同时，减慢INSERT、DELETE和UPDATE等写操作。很小的表不需要创建索引。

一些服务器状态变量的值可以反映索引的情况或为添加索引提供参考，例如：

Handler_read_key：通过索引读取行的请求数。该值较低，表示索引不经常使用。
Handler_read_rnd_next：在数据文件中读下一行的请求数。该值较高，表示存在大量的全表扫描，说明表上没有适当地建立索引或查询没有利用到索引。

可以mysqladmin extended-status或SHOW STATUS命令查看状态变量，例如：

mysql> show status like 'Handler_read_%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
6 rows in set (0.00 sec)

[@more@]对于建立了索引的表，应该定期执行ANALYZE TABLE命令。该命令分析并存储一个表的键分布，MySQL使用存储的键分布决定连接时表的连接顺序，以及查询时使用哪些索引。


2.query cache

查看相关的服务器系统变量，确认query cache已经打开：

mysql> show variables like '%query_cache%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 15728640 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+----------+
6 rows in set (0.00 sec)

have_query_cache：MySQL服务器是否支持query cache。
query_cache_size：分配给query cache的内存总量。
query_cache_type：query cache的类型。0或OFF表示不使用query cache，但系统还是会为query cache分配内存空间；1或ON表示缓存所有查询结果，但以SELECT SQL_NO_CACHE开头的除外；2或DEMAND表示只缓存以SQL_CACHE开头的语句。

一旦表发生变化，query cache中涉及该表的缓存内容就会失效，所以经常变化的表并不能从query cache的使用中受益，可以考虑为这些查询添加SQL_NO_CACHE选项。


3.查询分析

在查询语句最前面添加EXPLAIN关键字，返回的结果将列出该语句中所有的SELECT。例如：

mysql> explain select a.username, b.nickname, a.email from members a, memberfields b where a.uid = b.uid and a.email lik
e "%163.com";
+----+-------------+-------+--------+---------------+---------+---------+-------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-------------+-------+-------------+
| 1 | SIMPLE | b | ALL | PRIMARY | NULL | NULL | NULL | 46237 | |
| 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 3 | test2.b.uid | 1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+-------------+-------+-------------+
2 rows in set (0.00 sec)

id列为SELECT在整个查询中的位置；select_type列为查询的类型，如简单查询、UNION中最后面的查询、子查询等等；type列为执行连接的情况，const最佳，ALL最差；possible_keys列为可能使用的索引；key列为实际使用的索引；rows列为执行该查询需要在相应表中检索的行的数量，将每条记录中的数字相乘即为该查询的总开销；Extra列为一些其它相关信息。


4.优化多表查询

(1) 子查询转连接

例如：

select email from cdb_members where uid = (select uid from cdb_memberfields where nickname = 'aquxyz');

可转化为：

select email from cdb_members c_m, cdb_memberfields c_mf where c_m.uid = c_mf.uid and nickname = 'aquxyz';

(2) 巧妙利用对结果集的限制

例如：

select nickname from cdb_memberfields where uid = (select uid from cdb_members where email like '%com.cn' and regdate = (select min(regdate) from cdb_members));

可转化为：

select nickname from cdb_memberfields c_mf, cdb_members c_m where c_mf.uid = c_m.uid and c_m.email like '%com.cn' order by c_m.regdate asc limit 1;

(3) 使用变量

还是上面的例子，可转化为如下两条语句：

select @min:=min(regdate) from cdb_members;
select nickname from cdb_memberfields c_mf, cdb_members c_m where c_mf.uid = c_m.uid and c_m.email like '%com.cn' and c_m.regdate = @min;


5.优化表

<1> 对于经常修改的表，使用定长字段，而不用变长字段（对InnoDB表无效）；删除没有用的字段。
<2> OPTIMIZE TABLE命令。
<3> 对表进行合并；在查询中使用具有相同类型和长度的连接字段。


6.调整服务器设置

可以通过调整一些服务器系统变量，提高MySQL服务器的性能。

<1> key_buffer_size：MyISAM表的索引块使用的缓冲区大小。一般可以设置为服务器可用内存的25%-30%。
<2> table_cache：MySQL的所有线程可以打开的表的总数。应根据Opened_tables状态变量的值来决定是否要增大该值。
<3> sort_buffer_size：为每个线程的排序操作分配的缓冲区大小。增大该值可以加快ORDER BY和GROUP BY操作。
<4> read_buffer_size：为每个线程的sequential scan分配的缓冲区。增大该值可以提高查询速度。
<5> read_rnd_buffer_size：为每个线程的random scan分配的缓冲区。索引排序操作后，MySQL从这个缓冲区中读取排好顺序的行，增大该值可以加快ORDER BY操作。每个客户端都会分配此缓冲区，所以不要全局变量设置得太大。
<6> binlog_cache_size：事务过程中为为binary log保存SQL语句的高速缓存。如果经常使用大型、多语句的事务，可以增大该值以获得更好的性能。
<7> bulk_insert_buffer_size：为每个线程的批量insert操作分配的树状cache的大小。
<8> tread_cache_size：服务器缓存线程以便重用的总数。如果有许多新连接，可以考虑增大该值。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11662464/viewspace-1032469/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11662464/viewspace-1032469/