Mysql优化详细介绍_MySQL

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了Mysql优化详细介绍_MySQL，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含28732字，纯文字阅读大概需要42分钟。

内容图文

Mysql 优化详细介绍

MySQL 提供了很多参数进行服务器的设置， MySQL 在安装的时候提供几个默认的参数文件供选择，分别是： my-small.cnf 、 my-medium.cnf 、 my-large.cnf 、 my-huge.cnf 、 my-innodb-heavy-4G.cnf ，从文件名我们可以看出该配置文件适合的应用规模。通常情况下，我们可以选择使用接近自己的系统规模的配置文件，但是系统默认的参数在不同的生产环境可能不能完全满足实际的应用需求，我们可以再按照实际情况对部分参数进行调整，从而使数据库的运转达到最佳的性能。

因为数据最终是要保存到磁盘上，所以对于大型的数据库，磁盘 I/O 通常会成为系统的瓶颈。由于磁盘 I/O 是不可避免的，因此，我们希望通过一些有效的手段来增强磁盘 I/O 本身的性能和吞吐量，以此来改善服务器的性能。

在实际的生产环境中，数据库可以做的优化措施也是有局限的，对应用进行优化，减少对数据库的访问压力，同样也可以有效地改善服务器的性能，达到优化服务器的目的。

2.1 参数查看

如果 MySQL 服务正在运行，我们可以用 SHOW VARIABLES 命令查看 MySQL 的服务器静态参数值，或者使用 SHOW STATUS 命令查看动态运行状态信息。下面我们举例说明两者的区别：

查看服务器静态参数值，主要是显示数据库启动后不会动态更改的值，比如缓冲区大小、可以使用哪些存储引擎、 InnoDB 数据文件位置等，这些静态参数可以用来了解数据库当前的运行环境。

mysql> SHOW VARIABLES;

+———————————+—————————————-+

| Variable_name | Value |

+———————————+—————————————-+

| auto_increment_increment | 1 |

| auto_increment_offset | 1 |

| automatic_sp_privileges | ON |

| back_log | 50 |

| basedir | /usr/local/mysql/ |

| binlog_cache_size | 32768 |

| bulk_insert_buffer_size | 8388608 |

查看动态运行状态，主要 是指数据库运行期间的动态变化的信息，比如锁等待、当前连接数等，可以用来监控数据库运行的情况。

mysql> SHOW STATUS;

+———————————–+———–+

| Variable_name | Value |

+———————————–+———–+

| Aborted_clients | 568 |

| Aborted_connects | 755 |

| Binlog_cache_disk_use | 0 |

| Binlog_cache_use | 0 |

| Bytes_received | 183 |

| Bytes_sent | 6688 |

同样，在操作系统使用 mysqladmin 也可以获得系统静态参数和动态运行的状态信息：

-bash-3.00$ mysqladmin -u root -p variables

Enter password:

/+———————————+—————————————-+

| Variable_name | Value |

+———————————+—————————————-+

| auto_increment_increment | 1 |

| auto_increment_offset | 1 |

| automatic_sp_privileges | ON |

……

-bash-3.00$ mysqladmin -u root -p extended-status

Enter password:

+———————————–+———–+

| Variable_name | Value |

+———————————–+———–+

| Aborted_clients | 568 |

| Aborted_connects | 764 |

| Binlog_cache_disk_use | 0 |

……

如果需要了解某个参数的详细说明和当前的参数值，也可以使用 mysqld –verbose –help 命令查看：

-bash-3.00$ mysqld –verbose –help | grep key_buffer_size

–key_buffer_size=# The size of the buffer used for index blocks for MyISAM

key_buffer_size 8388600

2.2关键参数设置

Mysqld 数据库的参数设置有两种类型，一种是全局参数，影响 服务器的全局操作；另一种是会话级参数，只影响当前的客户端连接的相关操作。

服务器启动时，所有全局参数都初始化为默认值。可以在初始化文件或命令行中指定的选项来更改这些默认值。服务器启动后，通过连接服务器并执行 SET GLOBAL var_name 语句可以更改动态全局参数。要想更改全局参数，必须具有 SUPER 权限。全局参数的修改只对新的连接生效，已有的客户端连接并不会生效。

服务器还可以为每个客户端连接维护会话级参数，客户端连接时使用相应全局参数的当前值对客户端会话参数进行初始化。客户可以通过 SET SESSION var_name 语句来更改动态会话参数。设置会话级参数不需要特殊权限，但每个客户端可以只更改自己的会话级参数，不能更改其它客户的会话级参数。

不指定设置的参数类型时，默认设置的是会话级参数。

2.2.1 调节 key_buffer_size 参数

为了最小化磁盘的 I/O ， MyISAM 存储引擎的表使用键高速缓存来缓存索引，这个键高速缓存的大小则通过 key-buffer-size 参数来设置。如果应用系统中使用的表以 MyISAM 存储引擎为主，则应该适当增加该参数的值，以便尽可能的缓存索引，提高访问的速度。

默认情况下，所有的索引都使用相同的键高速缓存，当访问的索引不在缓存中时，使用 LRU （ Least Recently Used 最近最少使用）算法来替换缓存中最近最少使用的索引块。为了进一步避免对键高速缓存的争用，从 MySQL5.1 开始，可以设置多个键高速缓存，并为不同的索引键指定使用的键高速缓存。下面的例子演示如何修改高速键缓存的值，如何设置多个键高速缓存，以及如何为不同的索引指定不同的缓存：

显示当前的参数大小，为16M：

mysql> show variables like ‘key_buffer_size’;

+—————–+——-+

| Variable_name | Value |

+—————–+——-+

| key_buffer_size | 16384 |

+—————–+——-+

1 row in set (0.00 sec)

修改参数值到200M：

mysql> set global key_buffer_size=204800;

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like ‘key_buffer_size’;

+—————–+——–+

| Variable_name | Value |

+—————–+——–+

| key_buffer_size | 204800 |

+—————–+——–+

1 row in set (0.00 sec)

上面介绍的是默认的键缓存，下面介绍如何设置多个键缓存：

设置 hot_cache 的键缓存 100M ， cold_cache 的键缓存 100M ，另外还有 200M 的默认的键缓存。如果索引不指定键缓存，则会放在默认的键缓存中。

mysql> set global hot_cache.key_buffer_size=102400;

Query OK, 0 rows affected (0.00 sec)

mysql> set global cold_cache.key_buffer_size= 1024 00;

Query OK, 0 rows affected (0.01 sec)

mysql> show variables like ‘key_buffer_size’;

+—————–+——–+

| Variable_name | Value |

+—————–+——–+

| key_buffer_size | 204800 |

+—————–+——–+

1 row in set (0.00 sec)

如果要显示设置的多键缓存的值，可以使用：

mysql> SELECT @@global.hot_cache.key_buffer_size;

+————————————+

| @@global.hot_cache.key_buffer_size |

+————————————+

| 102400 |

+————————————+

1 row in set (0.03 sec)

mysql> SELECT @@global.cold_cache.key_buffer_size;

+————————————-+

| @@global.cold_cache.key_buffer_size |

+————————————-+

| 102400 |

+————————————-+

1 row in set (0.00 sec)

指定不同的索引使用不同的键缓存：

mysql> CACHE INDEX test1 in hot_cache;

+————+——————–+———-+———-+

| Table | Op | Msg_type | Msg_text |

+————+——————–+———-+———-+

| test .test1 | assign_to_keycache | status | OK |

+————+——————–+———-+———-+

1 row in set (0.00 sec)

mysql> CACHE INDEX test2 in hot_cache;

+————+——————–+———-+———-+

| Table | Op | Msg_type | Msg_text |

+————+——————–+———-+———-+

| test .test2 | assign_to_keycache | status | OK |

+————+——————–+———-+———-+

1 row in set (0.00 sec)

通常在数据库刚刚启动的时候，需要等待数据库热起来，也就是等待数据被缓存到缓存区中，这段时间数据库会因为 buffer 的命中率低而导致应用的访问效率不高。使用键高速缓存的时候，可以通过命令将索引预加载到缓存区中，大大缩短了数据库预热的时间。具体的操作方式是：

mysql> LOAD INDEX INTO CACHE test1,test2 IGNORE LEAVES;

+————+————–+———-+———-+

| Table | Op | Msg_type | Msg_text |

+————+————–+———-+———-+

| test .test1 | preload_keys | status | OK |

| test .test2 | preload_keys | status | OK |

+————+————–+———-+———-+

2 rows in set (3.89 sec)

如果已经使用 CACHE INDEX 语句为索引分配了一个键高速缓冲，预加载可以将索引块放入该缓存，否则，索引块将被加载到默认的键高速缓冲。

2.2.2 调节 bulk_insert_buffer_size 参数

使用多个值表的 INSERT 语句或者 LOAD DATA 命令 ，可以大大缩减客户端与数据库之间的连接、语法分析等消耗，使得效率比分开执行的单个 INSERT 语句快很多，相关的命令我们会在 SQL 优化详细介绍。如果多值的 INSERT 或者 LOAD DATA 是往一个非空的数据表里增加记录 ，也可以通过调整 bulk_insert_buffer_size 参数来提高数据插入的效率，这个参数设置的是 bulk insert 的缓存大小，默认是 8M 。

2.2.3 调节 table_cache 参数

table_cache 参数设置表高速缓存的数目。每个连接进来，都会至少打开一个表缓存。因此， table_cache 的大小应与 max_connections 的设置有关。例如，对于 200 个并行运行的连接，应该让表的缓存至少有 200 × N ，这里 N 是应用可以执行的查询的一个联接中表的最大数量。此外，还需要为临时表和文件保留一些额外的文件描述符。

当 Mysql 访问一个表时，如果该表在缓存中已经被打开，则可以直接访问缓存；如果还没有被缓存，但是在 Mysql 表缓冲区中还有空间，那么这个表就被打开并放入表缓冲区；如果表缓存满了，则会按照一定的规则将当前未用的表释放，或者临时扩大表缓存来存放，使用表缓存的好处是可以更快速地访问表中的内容。

执行 flush tables 会清空缓存的内容。一般来说，可以通过查看数据库运行峰值时间的状态值 Open_tables 和 Opened_tables ，判断是否需要增加 table_cache 的值。其中 open_tables 是当前打开的表的数量， Opened_tables 则是已经打开的表的数量。下面我们的例子显示了这两个状态值的变化情况：

首先，清空表缓存：

mysql> flush tables;

Query OK, 0 rows affected (0.00 sec)

察看当前的表缓存情况：

mysql> show global status like ‘open%_tables’;

+—————+——-+

| Variable_name | Value |

+—————+——-+

| Open_tables | 0 |

| Opened_tables | 543 |

+—————+——-+

2 rows in set (0.00 sec)

在当前连接访问一个表：

mysql> select count(*) from t1;

+———-+

| count(*) |

+———-+

| 4 |

+———-+

1 row in set (0.03 sec)

mysql> show global status like ‘open%_tables’;

+—————+——-+

| Variable_name | Value |

+—————+——-+

| Open_tables | 1 |

| Opened_tables | 544 |

+—————+——-+

2 rows in set (0.00 sec)

Open_tables和opened_tables都增加1。

再访问一个其他表，可以看到两个参数都增加。

mysql> select count(*) from t2;

+———-+

| count(*) |

+———-+

| 1 |

+———-+

1 row in set (0.06 sec)

mysql> show global status like ‘open%_tables’;

+—————+——-+

| Variable_name | Value |

+—————+——-+

| Open_tables | 2 |

| Opened_tables | 545 |

+—————+——-+

2 rows in set (0.00 sec)

再访问表t1的时候：

mysql> select * from t1;

+——+

| id |

+——+

| 2 |

| 1 |

| 1 |

| 3 |

+——+

4 rows in set (0.02 sec)

mysql> show global status like ‘open%_tables’;

+—————+——-+

| Variable_name | Value |

+—————+——-+

| Open_tables | 2 |

| Opened_tables | 545 |

+—————+——-+

2 rows in set (0.00 sec)

两个参数都没有变化，因为该表已经在表缓存中打开了，没有重复打开。

如果发现 open_tables 接近 table_cache 的时候，并且 Opened_tables 这个值在逐步增加，那就说明可能 table_cache 设置的偏小，经常需要将缓存的表清出，将新的表放入缓存，这时可以考虑增加这个参数的大小来改善访问的效率。

2.2.4 调节 innodb_buffer_pool_size 参数

innodb_buffer_pool_size 定义了 InnoDB 存储引擎的表数据和索引数据的最大内存缓冲区大小。和 MyISAM 存储引擎不同， MyISAM 的 key_buffer_size 只能缓存索引键，而 innodb_buffer_pool_size 却可以缓存数据块和索引键。适当的增加这个参数的大小，可以有效的减少 InnoDB 类型的表的磁盘 I/O 。在一个以 InnoDB 为主的专用数据库服务器上，可以考虑把该参数设置为物理内存大小的 60%-80% 。

2.2.5 调节 innodb_additional_mem_pool_size 参数

这个参数用来设置 InnoDB 存储的数据目录信息和其它内部数据结构的内存池大小。应用程序里的表越多，你需要在这里分配越多的内存。对于一个相对稳定的应用，这个参数的大小也是相对稳定的，也没有必要预留非常大的值。如果 InnoDB 用光了这个池内的内存， InnoDB 开始从操作系统分配内存，并且往 MySQL 错误日志写警告信息。默认值是 1MB ，当发现错误日志中已经有相关的警告信息时，就应该适当的增加该参数的大小。

2.2.6 调节 innodb_log_buffer_size 参数

这个参数设置 InnoDB 用来往磁盘上的日志文件写操作的缓冲区的大小。这有点像 Oracle 的 log_buffer ，通过内存缓冲来延缓磁盘 I/O 以提高访问的效率。 因为 MySQL 每秒都会将日志缓冲区的内容刷新到日志文件，因此无需设置超过 1 秒所需的内存空间。通常设置为 8 ～ 16MB 就足够了，默认值是 1MB 。

2.3 控制查询优化器的性能

查询优化器的主要工作是为 SQL 查询语句寻找最佳的执行方案，大多数查询优化器，包括 MySQL 的查询优化器，总是或多或少地在所有可能的查询评估方案中搜索最佳方案。而对于需要访问多个表的查询来说，搜索所有可能的方案消耗的解析时间可能是不能接受的。特别是对于那些频繁执行的 SQL 。

MySQL 提供两个参数来控制查询优化器执行的评估行为：

optimizer_prune_level 这个参数的默认值为 on(optimizer_prune_level=1) ，表示告诉优化器根据对每个表访问的行数的估计跳过某些方案。这样可以大大降低查询编辑次数，并且按照 MySQL 的测试，这样的设置是基本不会错过最佳的执行方案。如果认为这样的设置导致优化器错过了一个更好的查询方案，希望对所有的方案进行评估，则该选项可以关闭 (optimizer_prune_level=0) 。

optimizer_search_depth 参数告诉优化器对于每个未完成的 “ 未来的 ” 方案，应查看多深，以评估是否应对它进一步扩大。如果这个值设置的过大，则复杂查询会花很长的时间产生执行方案，如果设置的过小，则可能导致产生的执行方案不够优化。如果不能确定合理的 optimizer_search_depth 值，该参数可以设置为 0 ，告诉优化器自动确定该值。

3.1 使用 RAID 改善磁盘 I/O

RAID 是 Redundant Array of Inexpensive Disks 的缩写，翻译成中文就是“廉价磁盘冗余阵列”，通常就叫做磁盘阵列。 RAID 就是按照一定策略将数据分布到若干物理磁盘上，这样不仅增强了数据存储的可靠性，而且可以提高数据读写的整体性能，因为通过分布实现了数据的“并行”读写。

根据数据分布和冗余方式， RAID 分为许多级别，不同存储厂商提供的 RAID 卡或设备支持的 RAID 级别也不尽相同，下面介绍最常见也是最基本的几种：

RAID0 ：也叫条带化（ Stripe ），按一定的条带大小（ Chunk Size) 将数据依次分布到各个磁盘，没有数据冗余，优点是速度快、省钱，缺点是没有冗余带来的可靠性差。

RAID1 ：也叫磁盘镜像（ Mirror ），两个磁盘一组，所有数据都同时写入两个磁盘，但读时从任一磁盘读都可以，优点是可靠性好，也可以提供并发的读，缺点是投资会比较大。

RAID10 ：也叫 RAID 1+0 ，是 RAID 1 和 RAID 0 的结合。先做磁盘镜像，再条带化，使其兼具 RAID 1 的可靠性和 RAID 0 的优良并发读写性能，缺点也是投资大。

RAID4 ：象 RAID 0 一样对磁盘组条带化，并额外增加一个磁盘，用来写各 Stripe 的校验纠错数据，通过校验纠错数据可以恢复损坏的磁盘数据，所以比 RAID0 增加了可靠性，缺点是写磁盘的性能受到影响。

RAID5 ：是 RAID4 的改进，将每一个条带（ Stripe ）的校验纠错数据块也分布写到各个磁盘，而不是写到一个特定的磁盘，这样提高了可靠性和写磁盘的性能，但是写性能不及 RAID 0 、 RAID 1 和 RAID 10 ，容错能力也不及 RAID 1 ；在出现坏盘时，读性能会下降。

通常我们会根据应用的特点来选择合适的 RAID 级别，对于读写频繁、可靠性要求高的应用，推荐使用 RAID10 ；对于读操作为主且可靠性有一定要求的，推荐使用 RAID5 ；对于效率要求比较高，可靠性要求不高的，推荐使用 RAID0 。

最初， RAID 都是由硬件实现的，要使用 RAID 至少需要有一个 RAID 卡。但现在，一些操作系统中提供的软件包，也模拟实现了一些 RAID 的特性，虽然性能上不如硬 RAID ，但相比单个磁盘，性能和可靠性都有所改善。所以在不具备硬件条件的情况下，也可以考虑使用软 RAID 技术，具体配置方法可参见相关操作系统的帮助文档。

3.2 分布关键数据文件以均衡 I/O

MySQL 的数据文件默认都创建到参数 datadir 定义的目录下，不能像 Oracle 那样指定数据文件的位置，这样如果不使用 RAID 或者逻辑卷，则数据文件都分布在相同的磁盘上，无法发挥多个磁盘的并行读写的效率。在这种情况下，可以通过操作系统的符号连接（ Symbolic Links ）的方式，将不同的数据库或者 MyISAM 类型的表、索引分布到不同的设备上，从而均衡服务器的 I/O 。

将一个数据库指向其他物理磁盘：

-bash-3.00$ mkdir /disk2/databases/test

将数据库文件移动到新的目录下，然后创建符号链接：

-bash-3.00$ ln -s /disk2/databases/test /disk1/databases

重起服务以后，原有的数据库就可以在新的磁盘上被访问了。

需要注意的是， 使用 Symbolic Links 存在一定的安全风险，如果不使用 Symbolic Links ，应通过启动参数 skip-symbolic-links 禁用这一功能。

3.3 使用裸设备提高 I/O

MyISAM 存储引擎只能缓存索引，不缓存数据文件，所以操作系统磁盘 I/O 缓存对 MyISAM 表的存取效率很重要。但是 InnoDB 存储引擎与 MyISAM 存储引擎不同，它不仅可以缓存索引，还可以缓存数据，因此操作系统的磁盘 I/O 缓存对其性能没有更多的帮助。

因此，在 InnoDB 的缓存充足的情况下，我们可以考虑使用裸设备来存放 InnoDB 共享表空间，这样不再需要文件系统的开支，从而改善了性能。下面介绍具体的操作方法：

（ 1 ）修改 MySQL 配置文件，在 innodb_data_file_path 参数中增加裸设备文件名并指定 newraw 属性，分区必须至少和你指定的尺寸一样大：

……

[mysqld]

innodb_data_home_dir=

innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw

……

（ 2 ）启动 MySQL ，使其完成分区初始化工作，然后关闭 MySQL 。此时还不能创建或修改 InnoDB 表，否则再重起服务的时候会丢失这些修改。

（ 3 ）将 innodb_data_file_path 中的 newraw 改成 raw ，重新启动 MySQL 服务后即可开始使用。

……

[mysqld]

innodb_data_home_dir=

innodb_data_file_path=/dev/hdd1:3Graw;/dev/hdd2:2Graw

……

4.1 使用连接池减少数据库连接开销

应用访问数据库的时候，建立连接是开销相对比较大的操作，每一次数据访问请求都必须经历建立数据库连接、打开数据库、存取数据和关闭数据库连接等步骤。如果可以通过连接池来管理连接，则可以减少这部分的开销，从而改善性能。

连接池通常保存一定数量的数据库连接，应用通过连接池来获取到数据库的连接，并在使用完成之后将连接返回给连接池，以供其他访问的需要。这样，通过复用这些已经建立的数据库连接，可以有效地节省系统的资源和时间。

4.2 使用 Mysql 的查询缓存

MySQL 的查询缓存是将客户端执行的 SELECT 语句和查询结构都缓存起来，如果再执行一个相同的查询，则不再进行解析和查询，直接将缓存的结果返回。这样的特性对于更新不频繁，以读操作为主的数据库有很大的性能提升。对于更新频繁的数据库则相对没有那么有效，因为数据的更新会将查询从查询缓存条目清空。

首先我们需要确认当前的数据库环境是否可以使用查询缓存特性，检查参数 have_query_cache ：

mysql> show variables like ‘have_query_cache’;

+——————+——-+

| Variable_name | Value |

+——————+——-+

| have_query_cache | YES |

+——————+——-+

1 row in set (0.00 sec)

然后我们再查看当前的查询缓存的参数设置情况：

mysql> show variables like ‘query_cache%’;

+——————————+———+

| Variable_name | Value |

+——————————+———+

| query_cache_limit | 1048576 |

| query_cache_min_res_unit | 4096 |

| query_cache_size | 0 |

| query_cache_type | ON |

| query_cache_wlock_invalidate | OFF |

+——————————+———+

5 rows in set (0.00 sec)

query_cache_size 为查询缓存的大小，默认值为 0 ，表示禁用查询缓存。如果需要启用查询缓存，则需要修改这个参数的值。需要注意的是，由于查询缓存本身需要 40K 左右来保存数据结构，所以如果设置这个参数，则这个参数的值不能小于 40K 。

query_cache_type 设置查询缓存的工作方式： 0 或者 OFF ，表示阻塞查询缓存； 1 或 ON 表示允许缓存，以 SELECT SQL_NO_CACHE 开始的查询语句除外。 2 或 DEMAND ，表示仅对以 SELECT SQL_CACHE 开始的那些查询语句启用缓存。可以根据需要设置该参数的值。

query_cache_limit 设置可以被缓存的具体查询结果的最大值，默认值是 1M ，结果超过该值则不缓存。

可以使用 FLUSH QUERY CACHE 或者 RESET QUERY CACHE 来维护查询缓存：

² FLUSH QUERY CACHE 用来清理查询缓存碎片，以提高内存使用性能。该语句不会从缓存中移出任何查询。

² RESET QUERY CACHE 语句从查询缓存中移出所有查询。 FLUSH TABLES 语句也执行同样的工作。

可以使用 SHOW STATUS 来监控查询缓存使用的情况：

mysql> show status like ‘Qcache%’;

+————————-+——-+

| Variable_name | Value |

+————————-+——-+

| Qcache_free_blocks | 1 |

| Qcache_free_memory | 90752 |

| Qcache_hits | 4 |

| Qcache_inserts | 2 |

| Qcache_lowmem_prunes | 0 |

| Qcache_not_cached | 1 |

| Qcache_queries_in_cache | 2 |

| Qcache_total_blocks | 7 |

+————————-+——-+

8 rows in set (0.00 sec)

Qcache_hits ：表示从查询缓存中返回结果的次数。

Qcache_inserts ：表示加入到查询缓存的次数。

如果加入查询缓存的次数很大，但是从查询缓存返回结果的次数很小，说明查询缓存的效果并不是很好，可能是 SQL 复用的不多，也可能是结果更新的很频繁不适于缓存。

Qcache_lowmem_prunes ：表示由于内存不够而从缓存删除的查询数量。如果这个值比较大，说明查询缓存设置的可能偏小，也有可能是因此导致 Qcache_hits 不够理想。

Qcache_queries_in_cache ：表示当前被缓存的查询的数量，一旦数据发生了变化，则查询会自动从缓存中清除，这个值也会减小。如果 Qcache_inserts 很大，但是 Qcache_lowmem_prunes 和 Qcache_queries_in_cache 都偏小，则可能是由于表变更频繁导致不能使用查询缓存。

Qcache_not_cached ：表示没有被缓存的查询的数目，查询没有被缓存的原因可能是因为结果集超过参数的设置，或者查询的时候指定 SQL_NO_CACHE ，或者是按照 query_cache_type 设定的原则，不会被缓存。

4.3 应用端增加 CACHE 层

在应用中，我们可以在应用端加 CACHE 层来达到减轻数据库的负担的目的。 CACHE 层有很多种，也有很多种实现的方式，只要能达到降低数据库的负担，又能满足应用就可以，这就需要根据应用的实际情况进行特殊处理。

比如用户可以在应用端建立一个二级数据库，把访问频度非常大的数据放到二级库上，然后设定一个机制与主数据库进行同步，这样用户的主要操作都在二级数据库上进行，大大地降低了主数据库的压力。

再比如，可以把查询为主的数据从数据库中抽取出来放到应用端以文本方式存储，然后有查询需求的话，可以直接从这个“ CACHE ”中检索，由于这里的数据量小所以能够达到很高的查询效率，而且也减轻了数据库的负担。缓存的文件存放机制和更新的算法需要应用自己来设计和实现。

此外，也可以使用第三方的软件来进行数据的缓存，比如可以使用 memcached 将数据分布式的缓存到多台服务器的物理内存中，查询访问不再涉及数据操作，节省了连接数据库、解析 SQL 、执行查询等等的开销，也可以非常有效的提高查询的访问效率。需要注意的是，使用 memcached 时，需要应用程序根据缓存分布的逻辑访问对应得缓存服务器，且数据是缓存在物理内存中的， memcached 服务重新启动或者服务器重起，都会导致缓存内容的丢失，需要有配合的机制重新将数据刷新到缓存。在 Mysql5.1 之前，如果缓存的数据发生了变化，数据库不能自动更新缓存的内容，需要应用程序来控制和维护缓存的数据，从 Mysql5.1 开始，新增的 MySQL memcached UDFs 接口和触发器的功能，可以在表的触发器中主动的更新缓存，这样应用就可以不用再考虑数据库和缓存之间更新的同步问题。下面我们将会对 memcached 和 Mysql5.1 开始新增的 MySQL memcached UDFs 进行简单的介绍，不同的开发程序都会提供访问 Memcached 的接口，这里我们就不再进行一一的介绍，请参阅各自的接口说明。

4.3.1 Memcached 简介

Memcached 是高性能的，分布式的内存对象缓存系统，用于在动态应用中减少数据库负载，提升访问速度。 Memcached 可以将数据库负载大幅度降低，更好的分配资源，提供更快速的访问。

从上图可以看出，通常情况下，只有首次访问的时候会访问数据库获得数据，并放入 Memcached 中，之后的访问就会直接从 Memcached 中获得数据，这对于以查询为主，更新为辅的应用系统，访问数据库的频率会有很明显的下降，甚至配置很低的数据库都可以轻松完成。

Memcached 中保存的数据只存储在 Memcached 服务器的内存中，因此重起 Memcached 服务或者重起操作系统都会导致缓存的数据消失。可以设置缓存的数据的过期时间，超过时间的缓存数据会优先从缓存中删除。此外当缓存的内容到达指定的容量大小时，就会按照 LRU 算法自动删除不使用的缓存数据。这个指定的容量大小默认值是 64M ，可以通过 -m 参数进行调整。同时也可以通过 -M 参数控制 Memcached ，在空间容量不足时，也不使用 LRU 算法删除部分缓存，而是返回错误提示。

可以使用一台或者多台 Memcached 来提供缓存的服务，如果使用多台，那么多台之间可以通过分布式算法把数据放到不同的服务器上，以此来减轻某一台服务器的访问压力，并且当其中一台出现故障的时候，其他的服务器仍然是可用的，增强了系统得可用性。分布式是 Memcached 的最大的特点。但是这个分布式的算法，并不是 Memcached 来提供的，数据应该放到那个缓存服务器以及查询时应该从哪个缓存服务器获得都是客户端程序来控制的。同时，客户端程序还需要考虑增加或者减少 Memcached 服务器时如何重新部署已有的数据、更新数据库时如何更新 Memcached 内容等等。

4.3.2 Memcached 安装和服务的启动

Memcached 的安装很简单，从网上下载最新的包解压后安装即可，需要注意的是，需要事先安装 libevent 。下面是 libevent 和 Memcached 的安装步骤：

cd libevent-1.3e

configure

make

make install

cd memcached-1.2.6

configure

make

make install

Memcached 的下载地址： http://danga.com/memcached/download.bml

Libevent 的下载地址： http://www.monkey.org/~provos/libevent/

安装完成以后，就可以在服务器上启动 memcached 服务：

memcached -d -m 128 -l 192.168.0.3 -p 11211 -u memcached

主要参数解释：

-d ：以守护程序（ daemon ）方式运行 Memcached ；

-m ：设置 Memcached 可以使用的内存大小，默认为 64M ，单位为 M ；

-l ：设置监听的 IP 地址；

-p ：设置监听的端口，默认为 11211 ；

-u ：指定用户，如果当前为 root 的话，需要使用此参数指定用户。

其他参数请参见 Memcached 的帮助信息。

使用 Memcached 的 stats 的命令可以获得很多 Memcached 的统计信息。执行这个命令的方法很多，使用 telnet 最为简单：

-bash-3.00$ telnet localhost 11211

Trying 127.0.0.1…

Connected to localhost.localdomain (127.0.0.1).

Escape character is ‘^]’.

stats

STAT pid 27936

STAT uptime 7842

STAT time 1220478114

STAT version 1.2.6

STAT pointer_size 32

STAT rusage_user 0.000000

STAT rusage_system 0.011998

STAT curr_items 6

STAT total_items 10

STAT bytes 356

STAT curr_connections 2

STAT total_connections 29

STAT connection_structures 3

STAT cmd_get 16

STAT cmd_set 10

STAT get_hits 10

STAT get_misses 6

STAT evictions 0

STAT bytes_read 632

STAT bytes_written 418

STAT limit_maxbytes 67108864

STAT threads 1

END

stats detail dump

PREFIX id get 5 hit 5 set 1 del 0

END

stats items

STAT items:1:number 6

STAT items:1:age 5601

STAT items:1:evicted 0

STAT items:1:outofmemory 0

END

4.3.3 MySQL memcached UDFs

UDFs 是 User Defined Functions 的缩写，指 Mysql 的用户定义函数，应用可以通过使用这些函数从 Mysql5.0 以上版本的数据库中访问 Memcached 写入或者获得数据。此外，从 Mysql5.1 开始支持触发器，这样就可以在触发器中使用 UDFs 直接更新 Memcached 的内容，减轻了应用程序设计和编写的复杂性。下面我们简要介绍 UDFs 的安装和使用：

UDFs 的安装，需要在数据库服务器上安装两个包，分别是 libmemcached 和 memcached_functions_mysql ，都可以从 http://download.tangent.org/ 下载。

Libmemcached 包的安装比较简单，只要从网上下载最新的包解压后安装即可：

cd libmemcached-0.22

configure

make

make install

memcached_functions_mysql 安装的时候需要指定 Mysql 服务的路径，其他并没有什么特别的地方：

cd memcached_functions_mysql-0.4

./configure –with-mysql=/home/mysql/bin/mysql_config

make

make install

安装完成之后，需要将 libmemcached_functions_mysql 的库文件拷贝到 mysql/lib 目录下的 plugin 目录中。

cp /usr/local/lib/libmemcached_functions_mysql* /home/mysql/lib/plugin/.

首次使用的时候，需要使用 CREATE FUNCTION 来初始化用户定义函数。有两种方法可以初始化所有提供的用户定义函数，一个是执行 memcached_functions_mysql-0.4/sql 目录下的 install_functions.sql ；另一个是执行 memcached_functions_mysql-0.4/utils/install.pl 。

创建好函数之后，就可以使用这些函数进行 Memcached 的操作，首先需要定义 Memcached 服务器，假设我们测试的环境配置了两个 Memcached 服务器（ 192.168.0.1 和 192.168.0.2 ），都使用默认端口 11211 ：

mysql> SELECT memc_servers_set(’192.168.0.1:11211,192.168.0.2:11211′);

配置完成后，就可以进行数据的写入和读取的操作了，下面我们创建了一个临时表用来介绍通过触发器更新 Memcached 的简要步骤：

创建测试表：

mysql> create table test3 (

-> id int(3) not null,

-> name varchar(64) not null default ”,

-> primary key (id)

-> );

Query OK, 0 rows affected (0.17 sec)

给自增变量赋初值：

mysql> select memc_set(’id:sequence’, 0);

+—————————-+

| memc_set(’id:sequence’, 0) |

+—————————-+

| 0 |

+—————————-+

1 row in set (0.00 sec)

mysql>

在测试表上创建 insert 触发器：

mysql> DELIMITER |

mysql>

mysql> DROP TRIGGER IF EXISTS test3_insert;

-> CREATE TRIGGER test3_insert

-> BEFORE INSERT ON test3

-> FOR EACH ROW BEGIN

-> SET NEW.id= memc_increment(’id:sequence’);

-> SET @mm= memc_set(concat(’id:’,NEW.id), NEW.name);

-> END |

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

mysql> DELIMITER ;

mysql>

插入记录：

mysql> insert into test3 (name) values (’memcached’);

Query OK, 1 row affected (0.00 sec)

mysql> insert into test3 (name) values (’test’);

Query OK, 1 row affected (0.00 sec)

mysql> insert into test3 (name) values (’mysql’);

Query OK, 1 row affected (0.00 sec)

mysql> select * from test3;

+—-+———–+

| id | name |

+—-+———–+

| 1 | memcached |

| 2 | test |

| 3 | mysql |

+—-+———–+

3 rows in set (0.00 sec)

访问 Memcached 可以得到已经通过触发器写入缓存的记录：

mysql> select memc_get(’id:1′);

+——————+

| memc_get(’id:1′) |

+——————+

| memcached |

+——————+

1 row in set (0.00 sec)

mysql> select memc_get(’id:2′);

+——————+

| memc_get(’id:2′) |

+——————+

| test |

+——————+

1 row in set (0.00 sec)

mysql> select memc_get(’id:3′);

+——————+

| memc_get(’id:3′) |

+——————+

| mysql |

+——————+

1 row in set (0.00 sec)

4.4 使用复制分流查询操作

MySQL 的主从复制可以有效地分流更新操作和查询操作。具体的实现是一个主服务器承担更新操作，而多台从服务器承担查询操作，主从之间通过复制实现数据的同步。

多台从服务器的结构既可以确保数据库的可用性，又可以通过在从服务器上创建不同的索引来满足应用不同查询的需要。

大多数情况下，从数据库分担的查询压力并不需要访问主数据库拥有的全部表，对于这种情况，可以通过在主服务器上搭建一个虚拟的从服务器，将需要复制到从服务器的表设置成 BlackHole 引擎，然后定义 replicate-do-table 参数只复制这些表，这样就过滤出需要复制的 BINLOG ，减少了传输 BINLOG 的带宽。因为搭建的虚拟从服务器只起到过滤 BINLOG 的作用，并没有实际记录任何数据 ，所以对主数据库服务器的性能影响也非常得有限。通过 BlackHole 引擎实现的主从复制可以在满足查询要求的前提下，有效的减轻 BINLOG 的网络传输。

通过复制来分流查询是减少主数据库负载的一个常用方法，但是这种办法也存在一些问题，最主要的问题是当主数据库上更新频繁或者网络出现问题的时候，主从之间的数据可能存在比较大的延迟更新，从而造成查询结果和主数据库上有所差异。因此应用在设计的时候需要有所考虑，尽量将允许部分差异的查询安排访问从数据库，对准确性要求高的查询仍然需要从主数据库获取。

4.5 历史数据的处理

对于一个以插入和更新为主的应用，在设计阶段就要考虑到未来的数据量，并因此决定关键表中是否需要对历史数据的存放进行特别的考虑。通常在应用上线之初，数据量都会相对较小，系统运行的效率也会非常好。但是随着时间的推移，应用的推广使用，数据量就可能会有质的飞跃。如果没有在设计阶段有准备，那么后续处理起来会相对比较困难。

通常我们会建议对表的内容进行分析，对表的数据进行横向或者纵向的拆分。例如对于保存日志数据的表，将一个月的数据放在一个小表中，一个月以上的历史数据放在另一个大表中，通过相应的机制定期将数据从小表移动到大表中。通常，用户对于一个月以上的日志的查询需求通常都会线性的下降，而小表由于数据量小，对应的索引也会比较小，可以更轻松的进行扫描和缓存，访问的效率会因此有明显的改善。

4.6 使用分布式架构

分布式的数据库架构适合大数据量、负载高的情况，它具有良好的扩展性和高可用性。通过在多台服务器之间分布数据，可以实现在多台服务器之间的负载平均，提高了访问的执行效率。具体实现的时候，可以使用MySQL的CLUSTER功能或者通过用户自己编写的程序来实现全局事务。需要注意的是当前分布式事务只支持InnoDB存储引擎，因此如果自己编写程序来实现分布式架构数据库的话，那么就必须采用InnoDB存储引擎。

内容总结

以上是互联网集市为您收集整理的Mysql优化详细介绍_MySQL全部内容，希望文章能够帮你解决Mysql优化详细介绍_MySQL所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端