MySQL的调优思路和实际操作

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

这篇文章主要讲解了“MySQL 的调优思路和实际操作”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着丸趣 TV 小编的思路慢慢深入，一起来研究和学习“MySQL 的调优思路和实际操作”吧！

调优思路：

1. 设计与规划 – 以后再修该很麻烦，估计数据量，使用什么存储引擎

2. 数据的应用 – 怎样取数据，sql 语句的优化

3. 服务优化 – 内存的使用，磁盘的使用

4. 操作系统的优化 – 内核、tcp 连接数量

5. 升级硬件设备

磁盘 io 规划

raid 技术：raid0[xfs]

swap 分区：最好使用 raid0

磁盘分区：一个库放到一个分区上或一个磁盘上

物理分区

create table t1(id int,name char(20)) data directory= /data/ index directory = /data

mkdir /data

chown mysql.mysql /data

mysql show variables like %part%

4. 操作系统的优化

网卡 bonding 技术，

tcp 连接数量限制

优化系统打开文件的最大限制

关闭操作系统不必要的服务

5.mysql 服务优化

show status  看系统的资源

show variables  看变量，在 my.cnf 配置文件里定义的

show warnings  查看最近一个 sql 语句产生的错误警告，看其他的需要看.err 日志

show processlist 显示系统中正在运行的所有进程。

show errors

启用 mysql 慢查询：— 分析 sql 语句，找到影响效率的 SQL

log-slow-queries=/var/lib/mysql/slow.log 这个路径对 mysql 用户具有可写权限

long_query_time=2  查询超过 2 秒钟的语句记录下来

上面的 2 是查询的时间，即当一条 SQL 执行时间超过 5 秒的时候才记录，/var/lib/mysql/slow.log 是日志记录的位置。

然后重新启动 MySQL 服务

对查询进行缓存

query_cache_size 使用多大内存来缓存查询语句 [+8M]

mysql show variables like %query%

query_cache_size=8M

[root@st mysql]# vim /etc/my.cnf

mysql show status like %Qcache%

Qcache_free_blocks：说明缓存太大了。缓存中相邻内存的个数。数目大说明可能有碎片。FLUSH QUERY CACHE 会对缓存中的碎片进行整理，从而得到一个空闲块。[+8M]

Qcache_free_memory 缓存中的空闲内存

Qcache_hits 每次查询在缓存中命中时就增大

Qcache_inserts 每插入一个查询时就增大。命中次数除以插入次数就是命中率。

Qcache_lowmen_prunes 缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间看；如果这个数字在不断增长就表示可能碎片非常严重，或者内存很少

Qcache_hits/Qcache_inserts 命中率

关键字缓冲区

mysql show status like %key%

mysql show variables like key_buffer_size

key_buffer_size  指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。[+8M]

key_read_requests  请求总数

key_reads  代表命中磁盘的请求个数

(key_read_requests-key_read)/key_read_requests：命中率

key_buffer_size 只对 MyISAM 表起作用。即使你不使用 MyISAM 表，但是内部的临时磁盘表是 MyISAM 表，也要使用该值。可以使用检查状态值 created_tmp_disk_tables 得知详情。

对于 1G 内存的机器，如果不使用 MyISAM 表，推荐值是 16M（8-64M）。

临时表空间大小：order by 和 group by 时把数据放到临时表里。

tmp_table_size  占的是内存的大小，如果太小在排序时会出错

created_tmp_tables  创建临时表的数量

max_tmp_tables=32

tmpdir=/tmp  硬盘上临时表所在的位置

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

innodb 表：

创建表空间文件

[mysqld]

innodb_data_file_path=ibdata1:10M:autoextend

这个设置配置一个可扩展大小的尺寸为 10MB 的单独文件，名为 ibdata1。没有给出文件的位置，所以默认的是在 MySQL 的数据目录内。

如果你对最后的数据文件指定 autoextend 选项。如果数据文件耗尽了表空间中的自由空间，InnoDB 就扩展数据文件。扩展的幅度是每次 8MB。

要为一个自动扩展数据文件指定最大尺寸，请使用 max 属性。下列配置允许 ibdata1 涨到极限的 500MB：

[mysqld]

innodb_data_file_path=ibdata1:10M:autoextend:max:500M

InnoDB 默认地在 MySQL 数据目录创建表空间文件。要明确指定一个位置，请使用 innodb_data_home_dir 选项。比如，要使用两个名为 ibdata1 和 ibdata2 的文件，但是要把他们创建到 /ibdata，像如下一样配置 InnoDB：

[mysqld]

innodb_data_home_dir = /ibdata

innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

mysql show variables like innodb_buffer_pool_size

innodb_buffer_pool_size

对于 InnoDB 表来说，innodb_buffer_pool_size 的作用就相当于 key_buffer_size 对于 MyISAM 表的作用一样。InnoDB 使用该参数指定大小的内存来缓冲数据和索引。对于单独的 MySQL 数据库服务器，最大可以把该值设置成物理内存的 80%。

根据 MySQL 手册，对于 2G 内存的机器，推荐值是 1G（50%）。

mysql show variables like innodb_%per% [建议打开]

innodb_file_per_table =1 为每一个表单独创建一个表空间文件。

其他参数

skip-locking

取消文件系统的外部锁，减少出错几率增强稳定性

skip-name-resolve

关闭 mysql 的 dns 反查功能。这样速度就快了!

选项就能禁用 DNS 解析，连接速度会快很多。不过，这样的话就不能在 MySQL 的授权表中使用主机名了而只能用 ip 格式。

wait_timeout=10 终止空闲时间超过 10 秒的链接，避免长连接[默认 8 个小时]

max_connect_errors=10 //10 次连接失败就锁定，使用 flush hosts 解锁，

或 mysqladmin flush-hosts 解锁

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL 语句调优：

explain 命令：查询 select。

.type

这列很重要, 显示了连接使用了哪种类别, 有无使用索引

从最好到最差的连接类型为 const、eq_reg、ref、range、indexhe 和 ALL

感谢各位的阅读，以上就是“MySQL 的调优思路和实际操作”的内容了，经过本文的学习后，相信大家对 MySQL 的调优思路和实际操作这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是丸趣 TV，丸趣 TV 小编将为大家推送更多相关知识点的文章，欢迎关注！