如何进行MySQL管理基础中的性能优化

如何优化MySQL管理基础的性能，针对这个问题，本文详细介绍了相应的分析和解答，希望能帮助更多想要解决这个问题的小伙伴找到更简单更容易的方法。

1.索引

1应编制索引的列包括：用于联接的列，以及WHERE、GROUP BY或ORDER BY子句中涉及的列。

2具有许多重复值的列不能通过建立索引来提高性能，而具有唯一值的列可以充分受益于索引。

3索引越多越方便。它可能会提高搜索速度，并减缓写操作，如插入、删除和更新。非常小的表不需要编制索引。

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

Handler_read_key:按索引读取行的请求数。低值表示索引不经常使用。

Handler_read_rnd_next:读取数据文件中下一行的请求数。较高的值表示存在大量的全表扫描，这表明表没有被正确索引或者查询没有利用索引。

您可以使用mysqladmin扩展状态或SHOW STATUS命令查看状态变量，例如：

mysql显示像“Handler_read_%”这样的状态；

– –

|变量名|值|

– –

| 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行一组(0.00秒)

[@more@]对于索引表，应该定期执行ANALYZE TABLE命令。该命令分析并存储表的密钥分布。MySQL使用存储的密钥分布来确定连接时表的连接顺序以及查询时使用哪些索引。

2 .查询缓存

检查相关的服务器系统变量，并确认查询缓存已打开：

mysql显示像“%query_cache%”这样的变量；

– –

|变量名|值|

– –

|有_query_cache |是|

| query_cache_limit | 1048576

| query _ cache _ min _ RES _ unit | 4096 |

| query_cache_size | 15728640 |

| query_cache_type | ON |

| query _ cache _ wlock _ invalidate | OFF |

– –

6行一组(0.00秒)

have _ query _ cache:MySQL服务器是否支持查询缓存。

查询_c

ache_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：服务器缓存线程以便重用的总数。如果有许多新连接，可以考虑增大该值。

undefinedundefinedundefinedundefinedundefinedundefinedundefinedundefinedundefinedundefinedundefinedundefinedundefinedundefined

关于如何进行MySQL管理基础中的性能优化问题的解答就分享到这里了，希望