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

97次阅读

共计 4153 个字符，预计需要花费 11 分钟才能阅读完成。

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

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。例如：

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 管理基础中的性能优化问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注丸趣 TV 行业资讯频道了解更多相关知识。

正文完