优化mysql数据库性能的十个参数

(1)、max_connections：
查看对应信息：show variables like ‘%max_connections%’;
允许的同时客户的数量。增加该值增加 mysqld 要求的文件描述符的数量。这个数字应该增加，否则，你将经常看到 too many connections 错误。 默认数值是100，我把它改为1024 。
(2)、record_buffer：
每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，你可能想要增加该值。默认数值是131072(128k)，我把它改为16773120 (16m)
(3)、key_buffer_size：
索引块是缓冲的并且被所有的线程共享。key_buffer_size是用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，到你能负担得起那样多。如果你使它太大，系统将开始换页并且真的变慢了。默认数值是8388600(8m)，我的mysql主机有2gb内存，所以我把它改为 402649088(400mb)。
4)、back_log：
要求 mysql 能有的连接数量。当主要mysql线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。
back_log 值指出在mysql暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的tcp/ip连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。试图设定back_log高于你的操作系统的限制将是无效的。
当你观察你的主机进程列表，发现大量 264084 | unauthenticated user | xxx.xxx.xxx.xxx | null | connect | null | login | null 的待连接进程时，就要加大 back_log 的值了。默认数值是50，我把它改为500。
(5)、interactive_timeout：
服务器在关闭它前在一个交互连接上等待行动的秒数。一个交互的客户被定义为对 mysql_real_connect()使用 client_interactive 选项的客户。 默认数值是28800，我把它改为7200。
(6)、sort_buffer：
每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速order by或group by操作。默认数值是2097144(2m)，我把它改为 16777208 (16m)。
(7)、table_cache：
为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。mysql对每个唯一打开的表需要2个文件描述符。默认数值是64，我把它改为512。
(8)、thread_cache_size：
可以复用的保存在中的线程的数量。如果有，新的线程从缓存中取得，当断开连接的时候如果有空间，客户的线置在缓存中。如果有很多新的线程，为了提高性能可以这个变量值。通过比较 connections 和 threads_created 状态的变量，可以看到这个变量的作用。我把它设置为 80。
(9)mysql的搜索功能
用mysql进行搜索，目的是能不分大小写，又能用中文进行搜索
只需起动mysqld时指定 --default-character-set=gb2312
(10)、wait_timeout：
服务器在关闭它之前在一个连接上等待行动的秒数。 默认数值是28800，我把它改为7200。

临时打开这些统计数据

update performance_schema.setup_instruments set enabled = 'yes' where name like 'memory%'

查看mysql里的线程，观察是否有长期运行或阻塞的sql

show full processlist

先简单说一下各列的含义和用途，第一列，id，不用说了吧，一个标识，你要kill一个语句的时候很有用。user列，显示单前用户，如果不是root，这个命令就只显示你权限范围内的sql语句。host列，显示这个语句是从哪个ip的哪个端口上发出的。呵呵，可以用来追踪出问题语句的用户。db列，显示这个进程目前连接的是哪个数据库。command列，显示当前连接的执行的命令，一般就是休眠（sleep），查询（query），连接（connect）。time列，此这个状态持续的时间，单位是秒。state列，显示使用当前连接的sql语句的状态，很重要的列，后续会有所有的状态的描述，请注意，state只是语句执行中的某一个状态，一个sql语句，已查询为例，可能需要经过copying to tmp table，Sorting result，Sending data等状态才可以完成，info列，显示这个sql语句，因为长度有限，所以长的sql语句就显示不全，但是一个判断问题语句的重要依据。

这个命令中最关键的就是state列，mysql列出的状态主要有以下几种：

Checking table
正在检查数据表（这是自动的）。
Closing tables
正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。
Connect Out
复制从服务器正在连接主服务器。
Copying to tmp table on disk
由于临时结果集大于 tmp_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存。
Creating tmp table
正在创建临时表以存放部分查询结果。
deleting from main table
服务器正在执行多表删除中的第一部分，刚删除第一个表。
deleting from reference tables
服务器正在执行多表删除中的第二部分，正在删除其他表的记录。
Flushing tables
正在执行 FLUSH TABLES，等待其他线程关闭数据表。
Killed
发送了一个kill请求给某线程，那么这个线程将会检查kill标志位，同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位，不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了，那么kill请求会在锁释放时马上生效。
Locked
被其他查询锁住了。
Sending data
正在处理 SELECT 查询的记录，同时正在把结果发送给客户端。
Sorting for group
正在为 GROUP BY 做排序。
Sorting for order
正在为 ORDER BY 做排序。
Opening tables
这个过程应该会很快，除非受到其他因素的干扰。例如，在执 ALTER TABLE 或 LOCK TABLE 语句行完以前，数据表无法被其他线程打开。 正尝试打开一个表。
Removing duplicates
正在执行一个 SELECT DISTINCT 方式的查询，但是MySQL无法在前一个阶段优化掉那些重复的记录。因此，MySQL需要再次去掉重复的记录，然后再把结果发送给客户端。
Reopen table
获得了对一个表的锁，但是必须在表结构修改之后才能获得这个锁。已经释放锁，关闭数据表，正尝试重新打开数据表。
Repair by sorting
修复指令正在排序以创建索引。
Repair with keycache
修复指令正在利用索引缓存一个一个地创建新索引。它会比 Repair by sorting 慢些。
Searching rows for update
正在讲符合条件的记录找出来以备更新。它必须在 UPDATE 要修改相关的记录之前就完成了。
Sleeping
正在等待客户端发送新请求.
System lock
正在等待取得一个外部的系统锁。如果当前没有运行多个 mysqld 服务器同时请求同一个表，那么可以通过增加 --skip-external-locking参数来禁止外部系统锁。
Upgrading lock
INSERT DELAYED 正在尝试取得一个锁表以插入新记录。
Updating
正在搜索匹配的记录，并且修改它们。
User Lock
正在等待 GET_LOCK()。
Waiting for tables
该线程得到通知，数据表结构已经被修改了，需要重新打开数据表以取得新的结构。然后，为了能的重新打开数据表，必须等到所有其他线程关闭这个表。以下几种情况下会产生这个通知：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, 或 OPTIMIZE TABLE。
waiting for handler insert
INSERT DELAYED 已经处理完了所有待处理的插入操作，正在等待新的请求。
大部分状态对应很快的操作，只要有一个线程保持同一个状态好几秒钟，那么可能是有问题发生了，需要检查一下。
还有其他的状态没在上面中列出来，不过它们大部分只是在查看服务器是否有存在错误是才用得着。

疑似mysql连接使用完成后没有真正释放内存，查看mysql内存，缓存的相关配置，使用如

show global variables like ‘%sort_buffer_size%’;
查看相关的配置项

aria_sort_buffer_size 268434432
innodb_sort_buffer_size 1048576
myisam_sort_buffer_size 134216704
sort_buffer_size 65536

mysql使用内存计算器，具体地址为http://www.mysqlcalculator.com ||

Parameter | MySQL Default | Your Value |

key_buffer_size 64MB 64MB
query_cache_size 64MB 64MB
tmp_table_size 32MB 32MB
innodb_buffer_pool_size 8MB 8MB
innodb_additional_mem_pool_size 1MB 1MB
innodb_log_buffer_size 1MB 1MB
max_connections 150 1500
sort_buffer_size 2MB 2MB
read_buffer_size 0.128MB 0.128MB
read_rnd_buffer_size 0.256MB 0.256MB
join_buffer_size 0.128MB 0.128MB
thread_stack 0.196MB 0.196MB
binlog_cache_size 0MB 0MB
Totals: 576.2 MB 4232 MB

左列为mysql默认配置，右列为当前数据库的配置，可见预期内存使用最大值足足达到了1T，不符合当前系统负载量，说明当前配置不合理，需要进行调整
key_buffer_size = 32M //key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。由于我的数据库引擎为innodb，大部分表均为innodb，此处取默认值一半32M。

query_cache_size = 64M //查询缓存大小，当打开时候，执行查询语句会进行缓存，读写都会带来额外的内存消耗，下次再次查询若命中该缓存会立刻返回结果。默认改选项为关闭，打开则需要调整参数项query_cache_type=ON。此处采用默认值64M。

tmp_table_size = 64M //范围设置为64-256M最佳，当需要做类似group by操作生成的临时表大小，提高联接查询速度的效果，调整该值直到created_tmp_disk_tables / created_tmp_tables * 100% <= 25%，处于这样一个状态之下，效果较好，如果网站大部分为静态内容，可设置为64M，如果为动态页面，则设置为100M以上，不宜过大，导致内存不足I/O堵塞。此处我们设置为64M。

innodb_buffer_pool_size = 8196M //这个参数主要作用是缓存innodb表的索引，数据，插入数据时的缓冲。专用mysql服务器设置的大小： 操作系统内存的70%-80%最佳。由于我们的服务器还部署有其他应用，估此处设置为8G。此外，这个参数是非动态的，要修改这个值，需要重启mysqld服务。设置的过大，会导致system的swap空间被占用，导致操作系统变慢，从而减低sql查询的效率。

innodb_additional_mem_pool_size = 16M //用来存放Innodb的内部目录，这个值不用分配太大，系统可以自动调。不用设置太高。通常比较大数据设置16Ｍ够用了，如果表比较多，可以适当的增大。如果这个值自动增加，会在error log有中显示的。此处我们设置为16M。

innodb_log_buffer_size = 8M //InnoDB的写操作，将数据写入到内存中的日志缓存中，由于InnoDB在事务提交前，并不将改变的日志写入到磁盘中，因此在大事务中，可以减轻磁盘I/O的压力。通常情况下，如果不是写入大量的超大二进制数据（a lot of huge blobs），4MB-8MB已经足够了。此处我们设置为8M。

max_connections = 800 //最大连接数，根据同时在线人数设置一个比较综合的数字，最大不超过16384。此处我们根据系统使用量综合评估，设置为800。

sort_buffer_size = 2M //是一个connection级参数，在每个connection第一次需要使用这个buffer的时候，一次性分配设置的内存。并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。官方文档推荐范围为256KB~2MB，这里我们设置为2M。

read_buffer_size = 2M //（数据文件存储顺序）是MySQL读入缓冲区的大小，将对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区，read_buffer_size变量控制这一缓冲区的大小，如果对表的顺序扫描非常频繁，并你认为频繁扫描进行的太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能，read_buffer_size变量控制这一提高表的顺序扫描的效率 数据文件顺序。此处我们设置得比默认值大一点，为2M。

read_rnd_buffer_size = 250K //是MySQL的随机读缓冲区大小，当按任意顺序读取行时（列如按照排序顺序）将分配一个随机读取缓冲区，进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要大量数据可适当的调整该值，但MySQL会为每个客户连接分配该缓冲区所以尽量适当设置该值，以免内存开销过大。表的随机的顺序缓冲 提高读取的效率。此处设置为跟默认值相似，250KB。

join_buffer_size = 250K //多表参与join操作时的分配缓存，适当分配，降低内存消耗，此处我们设置为250KB。

thread_stack = 256K //每个连接线程被创建时，MySQL给它分配的内存大小。当MySQL创建一个新的连接线程时，需要给它分配一定大小的内存堆栈空间，以便存放客户端的请求的Query及自身的各种状态和处理信息。Thread Cache 命中率：Thread_Cache_Hit = (Connections - Threads_created) / Connections * 100%;命中率处于90%才算正常配置，当出现“mysql-debug: Thread stack overrun”的错误提示的时候需要增加该值。此处我们配置为256K。

binlog_cache_size = 250K // 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存。作用是提高记录bin-log的效率。没有什么大事务，dml也不是很频繁的情况下可以设置小一点，如果事务大而且多，dml操作也频繁，则可以适当的调大一点。前者建议是1048576 –1M；后者建议是： 2097152 – 4194304 即 2–4M。此处我们根据系统实际，配置为250KB。

造成mysql数据库服务器CPU过高也可能是查询时区设置

show variables like '%time_zone%';
set time_zone = '+8:00'

查询慢查询时间

show variables like ‘long_query_time’;
show variables like “%slow%”;
set slow_query_log=on;
set global general_log=‘ON’; //开启执行sql日志
释义：
slow_query_log_file（慢查询日志文件）
/var/log/mariadb/mysqllocalhost-slow.log

修改慢查询时间

set long_query_time=1; —但是重启mysql之后，long_query_time依然是my.ini中的值

显示慢查询次数

show status like ‘slow_queries’;

显示到mysql数据库的连接数

show status like ‘connections’;

显示到mysql数据库的连接数

show status like ‘connections’;

查看当前状态的连接数量，以定夺该值的大小

show global status like ‘Max_used_connections’;

最大连接数值

show variables like ‘%max_connections%’;

未知用途

show status like ‘Threads%’;

启动mysql的慢查询功能

set global log_slow_queries = on

查询是否启动慢查询功能

show variables like ‘log_slow_queries’

查看慢查询存放的日志路径

show variables like ‘slow_query_log_file’

2G内存,针对站多,抗压型的设置,最佳

table_cache=1024 物理内存越大,设置就越大.默认为2402,调到512-1024最佳
innodb_additional_mem_pool_size=4M 默认为2M
innodb_flush_log_at_trx_commit=1
(设置为0就是等到innodb_log_buffer_size列队满后再统一储存,默认为1)
innodb_log_buffer_size=2M 默认为1M
innodb_thread_concurrency=8 你的服务器CPU有几个就设置为几,建议用默认一般为8
key_buffer_size=256M 默认为218 调到128最佳
tmp_table_size=64M 默认为16M 调到64-256最挂
read_buffer_size=4M 默认为64K
read_rnd_buffer_size=16M 默认为256K
sort_buffer_size=32M 默认为256K
max_connections=1024 默认为1210
thread_cache_size=120 默认为60
query_cache_size=64M

MYSQL监控工具

mysqlslap 压力测试工具（mysql自带测试工具）
sysbench 性能测试工具
实时的观察的话，也有mytop、mycheckpoint(绘图显示)、mtop（托管在sourceforge，从04年至今没见更新了。还有一个mongodb 的监控工具也要mtop，托管在github）等等。
MYSQL监控工具–mytop
第1行很简单，就是版本信息

第2行是整体信息

Queries 服务器处理过的query总数

qps 每秒处理的query数量的平均值

Slow 慢查询总数

Se/In/Up/De(%) Select,Insert,Update,Delete 各自的占比

第3行是实时信息，本刷新周期内的信息统计，刷新周期是在配置文件中指定

qps now 本周期内的每秒处理query的数量

Slow qps 本周期内的每秒慢查询数量

Threads 当前连接线程数量，后面括号内的第一个数字是active状态的线程数量，第二个数字是在线程缓存中的数量

最后一列是本周期内的 Select,Insert,Update,Delete 各自的占比

Key Efficiency 表示有多少key是从缓存中读取，而不是从磁盘读取的

Bps in/out 表示mysql平均的流入流出数据量

Now in/out
是本周期内的流入流出数据量

剩下的就是线程信息列表

列出了当前的mysql线程，根据idle状态时间排序，通过o 键可以选择升序或降序

列表中显示出各线程的详细信息，例如 线程ID、用户名、客户端的地址、连接的数据库名称、详细查询语句

会发现 “show full processlist” 一直都在，因为 mytop 会使用这个语句收集 mysql 信息

辅助命令

mytop 提供了一些有用的命令，在运行界面按下相应按键即可

例如按下?，会进入帮助界面

其他示例：

按键 h 可以根据客户端地址进行过滤

按键s 可以根据用户名进行过滤

按键k 可以杀死某个线程

按键m 进入QPS模式，只是动态显示QPS数量
安装配置

安装

以 centos7 为例，执行以下几个命令即可

rpm -ivh http://dl.fedoraproject.org/pub/epel/7/x86_64/e/epel-release-7-8.noarch.rpm
yum install yum-plugin-protectbase.noarch -y
yum install mytop -y
配置

1
vi /root/.mytop
写入如下内容：

复制代码
host=localhost

user=root

pass=111111

db=mysql

port=3306

socket=/tmp/mysql.sock

delay=5

batchmode=0

color=1

idle=1
复制代码
保存退出

其中就是mysql的连接信息和基本配置

pass是密码，如果感觉不安全，可以不指定，在执行 mytop 命令时再输入，执行方式：mytop --prompt

delay 指定 mytop 多长时间刷新一次，也就是前面所说的刷新周期

安装配置完成后，执行mytop 命令就可以了

explain命令

使用explain命令，explain显示了select语句如何使用索引，以及相关的查询分析，我们使用explian + select语句即可。
操作1：输出结果中最重要的是type属性，即联合查询所使用的类型，其结果值由好到坏分别为：system>const>eq_ref>ref>fulltext>ref_or_null>index_merge> unique_subquery > index_subquery>range>index>ALL，一般来说，查询至少达到range级别，最好能达到ref。否者sql的查询性能会很慢。
操作2：如果select查询比较慢，可以通过添加索引的方式来达到相应的目的。然后再通过explain查看添加的索引是否生效（key、possible_key选项）。