MySQL服务器优化方法（2）_MySQL

　　　如果当前有 mysqld 服务器在运行，可以连接上去用以下命令来查看实际使用的系统变量：

　　mysql> SHOW VARIABLES;

　　也可以用以下语句来查看运行中的系统的统计结果及状态报告：

　　mysql> SHOW STATUS;

　　系统变量以及状态信息也可以通过

　　mysqladmin 来得到：

　　shell> mysqladmin variables
　　shell> mysqladmin extended-status

　　MySQL使用的算法有高伸缩性，因此它通常可以只使用很少内存就能运行。不过，给MySQL更多的内存通常能取得更好的性能。

　　调整MySQL服务器时，两个最重要的变量就是 key_buffer_size 和 table_cache。在试图修改其他变量前应该首先确认已经合理设定这两个变量了。

　　以下例子展示了在不同的运行时配置一些典型的变量值。这些例子使用 mysqld_safe 脚本和 --var_name=value 语法来设定变量 var_name 的值为 value。这个语法在MySQL 4.0以后就可以用了，在旧版本的MySQL中，考虑到如下一些不同之处：

　　使用 safe_mysqld 脚本而非 mysqld_safe。
　　使用 --set-variable=var_name=value 或 -O var_name=value 语法来设置变量。
　　如果变量名以 _size 结尾，就必须去掉 _size。例如，一个旧变量名为 sort_buffer_size 就是 sort_buffer，旧变量名read_buffer_size 就是 record_buffer。用 mysqld --help来要看那些变量是当前服务器版本可以识别的。
　　如果至少有256MB内存，且有大量的数据表，还想要在有中等数量的客户端连接时能有最大性能，可以这么设定：

　　shell> mysqld_safe --key_buffer_size=64M --table_cache=256 /
　　--sort_buffer_size=4M --read_buffer_size=1M &

　　如果只有128MB内存，且只有少量表，但是需要做大量的排序，可以这么设定：

　　shell> mysqld_safe --key_buffer_size=16M --sort_buffer_size=1M

　　如果有大量的并发连接，除非 mysqld 已经设置成对每次连接只是用很少的内存，否则可能发生交换问题。mysqld 在对每次连接都有足够内存时性能更好。
　　　如果只有很少内存且有大量连接，可以这么设定：

　　shell> mysqld_safe --key_buffer_size=512K --sort_buffer_size=100K /
　　--read_buffer_size=100K &

　　甚至这样：

　　shell> mysqld_safe --key_buffer_size=512K --sort_buffer_size=16K /
　　--table_cache=32 --read_buffer_size=8K /
　　--net_buffer_length=1K &

　　如果在一个比可用内存大很多的标上做 GROUP BY 或 ORDER BY 操作时，那么最好加大 read_rnd_buffer_size 的值以加速排序操作后的读数据。

　　安装MySQL后，在 `support-files' 目录下会有一些不同的 `my.cnf' 样例文件： `my-huge.cnf', `my-large.cnf', `my-medium.cnf' 和 `my-small.cnf'。可以把它们作为优化系统的蓝本。

　　注意，如果是通过命令行给 mysqld 或 mysqld_safe 指定参数，那么它只在那次启动服务器时有效。想要让这些选项在服务器启动时都有效，可以把它们放到配置文件中。

　　想要看参数改变后的效果，可以用以下方法(在MySQL 4.1以前，忽略 --verbose)：

　　shell> mysqld --key_buffer_size=32M --verbose --help

　　这个变量就会在结果的靠近末尾列出来。确认 --verbose 和 --help 选项是放在最后面，否则，在命令行上列出来的结果中在它们之后的其他选项效果就不会被反映出来了。

　　关于调整 InnoDB 存储引擎的详细信息请参考"16.12 InnoDB Performance Tuning Tips"。

　　3、 控制查询优化性能

　　查询优化程序的任务就是找到最佳的执行SQL查询的方法。因为"好"和"坏"方法之间的性能差异可能有数量级上的区别(也就是说，秒相对小时，甚至是天)，MySQL中的大部分查询优化程序或多或少会穷举搜索可能的优化方法，从中找到最佳的方法来执行。拿连接查询来说，MySQL优化程序搜索的可能方法会随着查询中引用表数量的增加而指数增加。如果表数量较少(通常少于7-10个)，那么这基本上不是问题。不过，当提交一个很大的查询时，服务器的性能主要瓶颈很容易就花费在优化查询上。

　　MySQL 5.0.1引进了一个更灵活的方法，它允许用户控制在查询优化程序穷举搜索最佳优化方法的数量。一般的考虑是，优化程序搜索的方法越少，那么在编译查询时耗费的时间就越少。另一个方面，由于优化程序可能会忽略一些方法，因此可能错过找到最佳优化方法。

　　关于控制优化程序评估优化方法的数量可以通过以下两个系统变量：

　　变量 optimizer_prune_level 告诉优化程序在估算要访问的每个表的记录数基础上忽略一定数量的方法。我们的经验表明，这种"学习猜测"方法很少会错过最佳方法，因为它可能戏剧性地减少编译时间。这就是为什么这个选项默认是打开的(optimizer_prune_level=1)。不过，如果确信优化程序会错过更好的方法，这个选项可以关上(optimizer_prune_level=0)，不过要注意编译查询的时间可能会更长了。要注意尽管是用了这种试探方法，优化程序仍会调查指数级的方法。

　　变量 optimizer_search_depth

　　告诉优化程序"将来"的每次顺序调查不完全的方法是否需要扩充的更远的深度。optimizer_search_depth 的值越小，可能会导致查询编译时间的越少。例如，有一个12-13或更多表的查询很容易就需要几小时甚至几天的时间来编译，如果 optimizer_search_depth 的值和表数量相近的话。同样，如果 optimizer_search_depth 的值等于3或4，则编译器可能至需要花不到几分钟的时间就完成编译了。如果不能确定 optimizer_search_depth 的值多少才合适，就把它设置为0，让优化程序来自动决定。

独旅网还为您提供以下相关内容希望对您有帮助：

（1）设置正确的InnoDB缓存区域Buffer Pool：适当设置InnoDB的缓存大小可以有效减少内存占用；（2）及时清理MySQL废弃内存：及时优化数据库表删除、优化字段、优化存储过程以及有好的维护工作日程可以有效清理废弃内存；（3）正确设置MySQL内存表：在某些场景下，可能需要在MySQL中临时创建表，非常大的时候，可以...

MySQL优化的万能技巧主要包括如下几个方面：1.索引优化 MySQL中的索引是用于提高查询效率的一种结构，如果查询的数据没有使用索引，那么MySQL就需要全表扫描，查询速度自然就会变得特别慢。因此，优化索引是提高MySQL性能的一个重要手段。MySQL支持多种类型的索引，如B-Tree索引、Hash索引、Fulltext索引等。其...

1. 使用正确的引擎 MySQL支持多个存储引擎，如MyISAM、InnoDB、MEMORY、ARCHIVE等。每个引擎有其独特的特点，选择正确的引擎可以大大影响性能。常用的引擎是InnoDB，其支持ACID属性（原子性、一致性、隔离性、持久性），对于高并发读写操作有很好的支持。2. 优化查询语句 查询是MySQL数据库最常用的操作之一...

1. 垂直分区 垂直分区是一种常用的MySQL优化方法，通过将一张表中的列分离为不同的表，可以使得访问表时获取更少的数据，从而提高查询速度。对于存储上亿数据的表，可以将经常使用的列和不经常使用的列分离为不同的表，以减少查询时需要读取的列数，从而提高查询效率。示例代码：创建主表：CREATE TABLE...

1. 使用连接池技术 连接池技术可以提高连接的组织和管理，缩短连接时间，避免连接过程中的资源浪费和效率降低。通过使用连接池技术，MySQL可以通过复用连接资源，有效地减少连接的错误。在Java应用程序中，使用连接池技术可以避免创建和关闭每个数据库连接的开销。具体操作方式如下：在应用服务器中设置数据源：/...

查询语句是MySQL操作中最频繁使用的语句之一，因此查询语句优化是解决MySQL卡顿问题的第一步。以下是一些优化查询语句的方法：（1）使用索引 索引可以大大提高查询语句的效率，尤其是对大数据量的表进行查询时更为显著。因此，在表创建时，建议添加索引。（2）限制查询结果数量 查询结果数量过多也会导致MySQL...

数据切分：将数据分散存储于多个数据库，通过路由规则访问特定数据库，降低单台服务器负载。其他优化：使用视图加速查询：创建视图简化优化器工作，减少磁盘I/O，提升查询效率。算法优化：避免游标，优先考虑基于集的方法或使用存储过程。通过上述策略，可以有效提升MySQL数据库性能，实现高效数据处理。优化需全局...

innodb_buffer_pool_size：InnoDB存储引擎的缓冲池大小。默认值为128M，可以根据实际情况适当增加或减少。query_cache_size：查询缓存的大小。默认值为0，可以根据实际情况适当增加。2.优化查询语句 一些查询语句可能会使用大量的内存，导致MySQL占用系统内存过多，影响系统的性能和稳定性。因此，我们需要优化...

2. MySQL Performance Tuning Primer MySQL Performance Tuning Primer是另一个很好用的工具，它可以提供有关您MySQL配置的详细信息，例如缓存设置、连接池、查询构建、磁盘操作和内存利用率等。它可以根据您的实际用例自动为您提供一些优化建议，以改善性能和可用性。要使用MySQL Performance Tuning Primer，请...

MySQL在运行过程中会使用一些缓存来提高查询效率，其中最常见的是查询缓存和InnoDB缓存。优化缓存设置可以大幅提高MySQL的性能。设置方法如下：1.查询缓存的优化 在my.ini或my.cnf文件中添加如下内容：[mysqld]query_cache_type=1 query_cache_size=64M（根据机器内存情况设置）2.重启MySQL服务 设置完成后...