MYSQL-innodb
性能优化
几个点
数据库
常用
参数
MYSQL
数据库的参数配置一般在
my.ini
配置（部分参数也可以用
set global
参数名
=
值 做临时调整，重启后失效），配置完后需要重启数据库才生效。
参数
1
：
slow_query_log
= 0|1
说明
:
开关慢查询日志。
slow_query_log_file
=
为存放路径；
long_query_time =
记录超过的时间，默认为
10s
。
参数
2
：
join_buffer_size
= MB
说明：
join buffer
存放基于每
thread
的连接表信息，连接时，只需访问
join buffer
,
不需要再去有并发机制保护的
cache.
参数
3
：
Sort_Buffer_Size
= MB
说明
:
Sort_Buffer_Size
是一个
connection
级参数，每个
connection
第一次需要使用这个
buffer
的时候，一次性分配设置的内存。
Sort_Buffer_Size
并不是越大越好，由于是
connection
级的参数，过大的设置
+
高并发可能会耗尽系统内存资源。
官网
文档说
“On Linux, there are thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation”
参数
4:
binlog_format
=
STATEMENT
|
ROW
|
MIXED
说明：日志格式
1)
STATEMENT
模式（
SBR
）
每一条会修改数据的
sql
语句会记录到
binlog
中。优点是并不需要记录每一条
sql
语句和每一行的数据变化，减少了
binlog
日志量，节约
IO
，提高性能。缺点是在某些情况下会导致
master-slave
中的数据不一致
(
如
sleep()
函数，
last_insert_id()
，以及
user-defined functions(udf)
等会出现问题
)
。
2)
ROW
模式（
RBR
）
不记录每条
sql
语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或
function
、或
trigger
的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是
alter table
的时候会让日志暴涨。
3)
MIXED
模式（
MBR
）
以上两种模式的混合使用，一般的复制使用
STATEMENT
模式保存
binlog
，对于
STATEMENT
模式无法复制的操作使用
ROW
模式保存
binlog
，
MySQL
会根据执行的
SQL
语句选择日志保存方式。
参数
5
：
binlog_cache_size = MB
说明：
默认大小是
37268即32K.根据事务需要调整大小
。
该
参数表示在事务中容纳二进制日志
sql语句的缓存大小。二进制日志缓存，是服务器支持事务存储引擎并且服务器启用了二进制日志(-log-bin选项)的前提下为每个客户端分配的内存，是每个client都可以分配设置大小的binlog cache空间
。
参数
6:
Max_binlog_cache_size = MB
说明：默认值是
18446744073709547520，这个值很大，够我们使用的了。此参数和binlog_cache_size相对应，代表binlog所能使用的cache最大使用大小。如果
系统
中事务过多，而此参数值设置有小，则会报错。
参数
7:
Max_binlog_size = GB/MB
说明：
Max_binlog_size: 1073741824=1G ，binlog的最大值，一般设置为512M或1G,一般不能超过1G。此参数不能非常严格控制binlog的大小，特别是在遇到大事务时，而binlog日志又到达了尾部，为了保证事务完整性，不切换日志，把所有sql都写到当前日志。
参数
8:
expire_logs_days
= N
说明
:
设置
binlog
老化日期
；
有大致三种情况引发日志切换：
binlog
大小超过
max_binlog_size
；
手动执行
flush logs
；
重新启动时
(
MySQL
将会
new
一个新文件用于记录
binlog)
参数
9:innodb_file_per_table = 0|1
说明：参数值为
1
，表示
对每张表使用单独的
innoDB
文件
参数
10:
innodb_log_file_size
= GB/MB
说明
:
对
Innodb
数据表有大量的写入操作，那么选择合适的
innodb_log_file_size
值对提升
MySQL
性能很重要
;
值太大了会让恢复过程变慢
.
参数
11
：
innodb_log_files_in_group =
N
说明
:
该变量控制日志文件数。默认值为
2
。日志是以顺序的方式写入。
参数
12:
innodb_flush_method
=
说明
:
设置
InnoDB
同步
IO
的方式：
Default
(
fsync
);
O_SYNC
(
以
sync
模式打开文件，通常比较慢
);
O_DIRECT
(
在
Linux
上使用
Direct IO
。可以显著提高速度，特别是在
RAID
系统上。避免额外的数据复制和
double buffering
（
mysql buffering
和
OS buffering
）
)
参数
13:
transaction_isolation =
READ-UNCOMMITTED | READ-COMMITTED |REPEATABLE-READ | SERIALIZABLE
说明
:
设定事务隔离级别
1)
未提交读
(Read Uncommitted)
：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据
2)
提交读
(Read Committed)
：只能读取到已经提交的数据。
Oracle
等多数数据库默认都是该级别
(
不重复读
)
3)
可重复读
(Repeated Read)
：可重复读。在同一个事务内的查询都是事务开始时刻一致的，
InnoDB
默认级别。在
SQL
标准中，该隔离级别消除了不可重复读，但是还存在幻象读
4)
串行读
(Serializable)
：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞
参数
14:
character-set-server
= utf8|utf8mb4
说明
:设定字符集，utf8存3个字节，utf8mb4存4个字节。
参数
1
5
：
innodb_buffer_pool_size = Gb/MB
说明：此参数类似于
oracle的SGA配置，当主机做为mysql数据库服务器时，一般配置为整机内存的
60%~80%。
参数
16
：
innodb_buffer_pool_instances=
N
说明：内存缓冲池实例数，将
innodb_buffer_pool_size
配置的内存分割成
N份，此参数当配置内存大小于1G时才生效，当数据库有多个会话进行数据库操作时，用于并行在多个内存块中处理任务，一般配置值《=服务器CPU的个数。
参数
17
：
max_connections =
xxxx
说明：最大连接数，当数据库面对高并发时，这个值需要调节为一个合理的值，才满足业务的并发要求，避免数据库拒绝连接。
参数
18
：
max_user_connections=
xxxx
说明：设置单个用户的连接数。
参数
19
：
innodb_log_buffer_size =
xxxxx
说明：
日志缓冲区大小
,
一般不用设置太大，能存下
1
秒钟操作的数据日志就行了，
mysql默认1秒写一轮询写一次日志到磁盘。
参数
20
：
innodb_flush_log_at_trx_commit
=
说明：（这个配置很关键）一般的实时业务交易配置为
2
，取值
0,1,2
0:
数据操作时，直接写内存，并不同时写入磁盘；
2:
数据操作时，直接写内存，并不同时写入磁盘；
1:
就每个事务提交就会要刷新到磁盘后才算提交完成
,
这种情况是保证了事务的一致性
,
但性能会有很大的影响。
0
与
2
的区别：
0
：当
mysql
挂了之后，可能会损失前一秒的事务信息
2
：当
mysql
挂了之后，如果系统文件系统没挂，不会有事务丢失。
参数
21
：
innodb_read_io_threads =
xxxx
说明：数据库读操作时的线程数，用于并发。
参数
22
：
innodb_write_io_threads =
xxx
说明：数据库写操作时的线程数，用于并发。
参数
23
：
innodb file per table=
1
说明：每一个表是否使用独立的数据表空间，默认为
OFF
（使用共享表空间），一般建议配置为
1
，
InnoDB 默认会将所有的数据库InnoDB引擎的表数据存储在一个共享空间中：（ibdata1），这样就感觉不爽，增删数据库的时候，ibdata1文件不会自动收缩，
单个数据库的备份也将成为问题。通常只能将数据使用
mysqldump 导出，然后再导入解决这个问题。
共享表空间在
Insert操作上少有优势。其它都没独立表空间表现好，如果数据库基本上都插入操作则配置为0。
参数
24
：
innodb_stats_on_metadata=
{
OFF|on
}
说明：是否动态收集统计信息，开启时会影响数据库的性能
(
一般关闭，找个时间手动刷新，或定时刷新
）
如果为关闭时，需要配置数据库调度任务，定时刷新数据库的统计信息。
参数
25
：
innodb_spin_wait_delay=
xxxxx
说明：控制
CPU的轮询时间间隔，默认是6,配置过低时，任务调度比较频繁，会消耗CPU资源。
参数
26
：
innodb_lock_wait_timeout=
xxxx
说明：控制锁的超时时间，默认为
50，这个值要注意，如果有特殊业务确实要耗时较长时，不能配置太短。
具体的调优参数内容较多，可参考官方文档，一些比较重要的参数：
back_log
：
back_log
值指出在
MySQL
暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。也就是说，如果
MySql
的连接数据达到
max_connections
时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即
back_log
，如果等待连接的数量超过
back_log
，将不被授予连接资源。可以从默认的
50
升至
500
wait_timeout
：数据库连接闲置时间，闲置连接会占用内存资源。可以从默认的
8
小时减到半小时
max_user_connection:
最大连接数，默认为
0
无上限，最好设一个合理上限
thread_concurrency
：并发线程数，设为
CPU
核数的两倍
skip_name_resolve
：禁止对外部连接进行
DNS
解析，消除
DNS
解析时间，但需要所有远程主机用
IP
访问
key_buffer_size
：索引块的缓存大小，增加会提升索引处理速度，对
MyISAM
表性能影响最大。对于内存
4G
左右，可设为
256M
或
384M
，通过查询
show status like 'key_read%'
，保证
key_reads / key_read_requests
在
0.1%
以下最好
innodb_buffer_pool_size
：缓存数据块和索引块，对
InnoDB
表性能影响最大。通过查询
show status like 'Innodb_buffer_pool_read%'
，保证
(Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests
越高越好
innodb_additional_mem_pool_size
：
InnoDB
存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小，当数据库对象非常多的时候，适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率，当过小的时候，
MySQL
会记录
Warning
信息到数据库的错误日志中，这时就需要该调整这个参数大小
innodb_log_buffer_size
：
InnoDB
存储引擎的事务日志所使用的缓冲区，一般来说不建议超过
32MB
query_cache_size
：缓存
MySQL
中的
ResultSet
，也就是一条
SQL
语句执行的结果集，所以仅仅只能针对
select
语句。当某个表的数据有任何任何变化，都会导致所有引用了该表的
select
语句在
Query Cache
中的缓存数据失效。所以，当我们的数据变化非常频繁的情况下，使用
Query Cache
可能会得不偿失。根据命中率
(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))
进行调整，一般不建议太大，
256MB
可能已经差不多了，大型的配置型静态数据可适当调大
.
可以通过命令
show status like 'Qcache_%'
查看目前系统
Query catch
使用大小
read_buffer_size
：
MySql
读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，
MySql
会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，可以通过增加该变量值以及内存缓冲区大小提高其性能
sort_buffer_size
：
MySql
执行排序使用的缓冲大小。如果想要增加
ORDER BY
的速度，首先看是否可以让
MySQL
使用索引而不是额外的排序阶段。如果不能，可以尝试增加
sort_buffer_size
变量的大小
read_rnd_buffer_size
：
MySql
的随机读缓冲区大小。当按任意顺序读取行时
(
例如，按照排序顺序
)
，将分配一个随机读缓存区。进行排序查询时，
MySql
会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但
MySql
会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。
record_buffer
：每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，可能想要增加该值
thread_cache_size
：保存当前没有与连接关联但是准备为后面新的连接服务的线程，可以快速响应连接的线程请求而无需创建新的
table_cache
：类似于
thread_cache_size
，但用来缓存表文件，对
InnoDB
效果不大，主要用于
MyISAM
执行计划分析
mysql> EXPLAIN SELECT * FROM smssendbak;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | smssendbak | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | NULL |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
说明
：
Table
:表名称
Type
:
重要的一列，显示使用了何种连接，从好到差依次为
const
、
eq_ref
、
ref
、
range
、
index
、
all
，下面详细说明：
type
的描述：
System
：
表只有一行，这是
const
连接类型的特例；
Const
：
表中一个记录的最大值能够匹配这个查询
(
索引可以是主键或唯一索引
)
。因为只有一行，这个值实际就是常数，因为
mysql
先读这个值，再把它当作常数对待
eq_ref
：
从前面的表中，对每一个记录的联合都从表中读取一个记录。在查询使用索引为主键或唯一索引的全部时使用；
Ref
：
只有使用了不是主键或唯一索引的部分时发生。对于前面表的每一行联合，全部记录都将从表中读出，这个连接类型严重依赖索引匹配记录的多少－越少越好；
Range
：
使用索引返回一个范围中的行，比如使用
>
或
<
查找时发生；
Index
：
这个连接类型对前面的表中的每一个记录联合进行完全扫描
(
比
all
好，因为索引一般小于表数据
)
；
All
：
这个连接类型多前面的表中的每一个记录联合进行完全扫描，这个比较糟糕，应该尽量避免。
possible_keys
：
可以应用在这张表中的索引，如果为
null
，则表示没有可用索引；
Key
：
实际使用的索引，如为
null
，表示没有用到索引；
key_len
：
索引的长度，在不损失精确度的情况下，越短越好；
Ref
：
显示索引的哪一列被使用了，如果可能的话，是个常数；
Rows
：
返回请求数据的行数；
Extra
：
关于
mysql
如何解析查询的额外信息，下面会详细说明。
extra
行的描述：
distinct
－
mysql
找到了域行联合匹配的行，就不再搜索了；
not exists
－
mysql
优化了
left join
，一旦找到了匹配
left join
的行，就不再搜索了；
range checked for each
－没找到理想的索引，一次对于从前面表中来的每一个行组合；
record(index map: #)
－检查使用哪个索引，并用它从表中返回行，这是使用索引最慢的一种；
using filesort
－看到这个就需要优化查询了，
mysql
需要额外的步骤来发现如何对返回的行排序。他根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
.
using index
－列数据是从单使用了索引中的信息而没有读取实际行的表返回的，这发生在对表的全部的请求列都是同一个索引时；
using temporary
－看到这个就需要优化查询了，
mysql
需要创建一个临时表来查询存储结果，这通常发生在多不同的列表进行
order by
时，而不是
group by
；
where used
－使用了
where
从句来限制哪些行将与下一张表匹配或是返回给用户。如不想返回表中用的全部行，并连接类型是
all
或
index
，这就会发生，也可能是查询有问题。
SQL
语句优化：注意
SQL
语句的书写规则，
where
条件，
order by ,group by ,having , in ,like ,jion on,
表顺序，聚合函数的使用，子查询等。
索引优化
1
、
是否有无重复索引
2
、
索引字段类型，顺序是否合理
3
、
是否有无用索引
4
、
索引利用率
表结构优化
1
、
表的字段类型是否合理
2
、
数据是否冗余
3
、
根据业务规则建立合理的约束
4
、
建表时，尽量使字段值不为空
(
加
not null
约束
)
，索引列值尽量离散（不重复）
5
、
不常用的字段列可适当考虑折分表
6
、
数据量较大的表，有存储时间，
IP
地址数据时，转为
int ,bigint
INT
类型的时间数据转换：
UNIX_TIMESTAMP('2015-01-10 12:00:00')
转
int
（插入数据时）
FROM_UNIXTIME(
时间字段
)
取时间字段的值
IP
地址数据操作转换：
INET_ATON :IP
地址转
bigint
（
inet_aton(192.168.1.1)
）
INET_NTOA :BIGINT
转
IP
地址（
inet_ntoa(ip
地址字段
)
）
注
:
在
mysql
中
int ,
比
varchar
处理要简单，尽量少使用
text
类型
数据库主机参数优化
网络：（
/etc/security/sysctl.conf）
net.ipv4.tcp_keepalive_time = 1200
说明：表示当
keepalive
起用的时候，
TCP
发送
keepalive
消息的频度。缺省是
2
小时，改为
20
分钟。
net.ipv4.ip_local_port_range = 10000 65000
说明：表示用于向外连接的端口范围
,
一般低位端口不要设置太低，有可能会用到其它程序固定的端口
net.ipv4.tcp_max_syn_backlog = 65535
说明：表示
SYN
队列的长度，默认为
1024
，加大队列长度为
65535
，可以容纳更多等待连接的网络连接数。
net.ipv4.tcp_max_tw_buckets = 5000
说明：示系统同时保持
TIME_WAIT
的最大数量，如果超过这个数字，
TIME_WAIT
将立刻被清除并打印警告信息。默
认为
180000
，避免被大量的
timewait
拖死。
net.ipv4.tcp_syncookies = 1
说明：表示开启
SYN Cookies
net.ipv4.tcp_tw_reuse=1
说明：表示开启重用
,
允许将
TIME-WAIT sockets
重新用于新的
TCP
连接
net.ipv4.tcp_recycle=1
说明：表示开启
TCP
连接中
TIME-WAIT sockets
的快速回收，默认为
0
，表示关闭；
net.ipv4.tcp_fin_timeout=10
说明：修改系統默认的
TIMEOUT
时间。
limit.conf
内核相关参数优化：
详见：《
limits.conf
详解》
vi /etc/security/limits.conf
limits.conf的格式如下：
<domain> <type> <item> <value>
或：username|@groupname type resource limit
username|@groupname：设置需要被限制的用户名，组名前面加@和用户名区别。也可以用通配符*来做所有用户的限制。
如：
type：有 soft，hard 和 -
soft 指的是当前系统生效的设置值
hard 表明系统中所能设定的最大值
soft 的限制不能比har 限制高
用 - 就表明同时设置了 soft 和 hard 的值。
resource：
core - 限制内核文件的大小
data - 最大数据大小
fsize - 最大文件大小
memlock - 最大锁定内存地址空间
nofile - 打开文件的最大数目
rss - 最大持久设置大小
stack - 最大栈大小
cpu - 以分钟为单位的最多 CPU 时间
noproc - 进程的最大数目
as - 地址空间限制
maxlogins - 此用户允许登录的最大数目
要使 limits.conf 文件配置生效，必须要确保 pam_limits.so 文件被加入到启动文件中。
查看 /etc/pam.d/login 文件中有：
session required /lib/security/pam_limits.so
暂时地:适用于通过 ulimit 命令登录 shell 会话期间。
永久地:通过将一个相应的 ulimit 语句添加到由登录 shell 读取的文件之一（例如 ~/.profile），即特定于 shell 的用户资源文件；或者通过编辑 /etc/security/limits.conf。
何谓core文件,当一个程序崩溃时，在进程当前工作目录的core文件中复制了该进程的存储图像。core文件仅仅是一个内存映象(同时加上调试信息)，主要是用来调试的。 core文件是个二进制文件，需要用相应的工具来分析程序崩溃时的内存映像。
系统默认core文件的大小为0，所以没有创建。可以用ulimit命令查看和修改core文件的大小。
$ulimit -c 0
$ ulimit -c 1000
$ ulimit -c 1000
-c 指定修改core文件的大小，1000指定了core文件大小。也可以对core文件的大小不做限制，如：
# ulimit -c unlimited
如果想让修改永久生效，则需要修改配置文件，如 .bash_profile、/etc/profile或/etc/security/limits.conf。
2.nofile - 打开文件的最大数目
[plain]
* soft nofile 10000 #软限制
* hard nofile 10000 #硬限制
对于需要做许多套接字连接并使它们处于打开状态的应用程序而言，最好通过使用 ulimit –n，或者通过设置nofile 参数，为用户把文件描述符的数量设置得比默认值高一些
vi /etc/security/limits.conf
用ulimit -n 2048 修改只对当前的shell有效，退出后失效：！！！！！！！！！！！！！！！！！
如A程序已经运行，此时ulimit -n为1024;之后ulimit -n 2048,这时在运行B程序；退出当前shell用户，再次进行shell，之后运行C程序；这时只有B程序用的是2048，其它用的都是1024.
数据库参数调节效果对比
下列参数配置测试结果对比。
innodb_buffer_pool_size = 1024M
innodb_buffer_pool_instances = 4
max_connections = 2000
max_user_connections =
2
000
innodb_flush_log_at_trx_commit = 2
innodb_write_io_threads = 16
innodb_read_io_threads = 16
innodb_spin_wait_delay = 10
innodb_lock_wait_timeout = 30
更改前：增查删操作通过
jmeter测试每秒只有80.8笔。
更改后：增查删操作通过
jmeter测试每秒达到1513.1笔。
