MySQL-day13-数据库优化、调优

数据库优化

一、数据库硬件优化（选型）

1.一般数据库选择

• 真实的硬件，物理机
• 云产品ECS，自己搭建数据库
• 云数据库（RDS、DRDS）

2.数据库类型

• OLTP 在线事务处理系统

  • 支持大量并发用户定期添加和修改数据。
  • 反映随时变化的单位状态，但不保存其历史记录。
  • 包含大量数据，其中包括用于验证事务的大量数据。
  • 可以进行优化以对事务活动做出响应。
  • 提供用于支持单位日常运营的技术基础结构。
  • 个别事务能够很快地完成，并且只需访问相对较少的数据。
  • 实时性要求高。
  • 交易一般是确定的，所以OLTP是对确定性的数据进行存取。(比如存取款都有一个特定的金额)
  • 并发性要求高并且严格的要求事务的完整、安全性。

• OLAP 数据仓库，数据处理，数据展示（使用nosql更适合）

  • ROLAP
  • MOLAP
  • HOLAP

3.硬件选型

1）CPU选型

• IO密集型：

  • 线上系统，OLTP主要是IO密集型的业务，高并发（OLTP），E系列（至强），主频相对低，核心数量多

• CPU密集型：

  • 数据分析数据处理，OLAP，cpu密集型的，需要CPU高计算能力（OLAP，不需要很高的并发，计算只用一个用户就可以了），I系列的（IBM），主频很高，核心少 （打游戏一般选择CPU密集型）

2）内存选择

• 建议2-3倍cpu核心数量 （ECC）
• 内存越大它使用越多，浪费越多，命中率越低

3）磁盘选择

• SATA-III

• SAS

• Fc

• SSD（sata）

  • pci-e 级别
  • Flash 级别

4）存储选择（一般大型企业）

5）网络选择

• 硬件买好的

  • 单卡单口，网卡有很多个口，选择单口的，性能更好
  • 一般可以插4块卡，两个内网两个外网，避免一块出现问题就挂掉

• 网卡绑定（bonding），交换机堆叠

  • 意思就像负载均衡，将两块网卡逻辑绑定，一个网卡绑定一个交换机，如果做了网卡绑定，交换机也一定要做堆叠
  • 绑定方式：负载均衡模式，主备模式

4.操作系统优化

1）Swap调整

• 这个参数决定了Linux是倾向于使用swap，还是倾向于释放文件系统cache。在内存紧张的情况下，数值越低越倾向于释放文件系统cache。
• 当然，这个参数只能减少使用swap的概率，并不能避免Linux使用swap。

echo 0 >/proc/sys/vm/swappiness的内容改成0（临时），

/etc/sysctl.conf 上添加 vm.swappiness=0（永久）
sysctl -p

2）IO调度策略

centos 7 默认是deadline
cat /sys/block/sda/queue/scheduler

#临时修改为deadline(centos6)
echo deadline > /sys/block/sda/queue/scheduler 

vi /boot/grub/grub.conf
更改到如下内容:
kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline rhgb quiet

5.应用端优化

• 减少烂SQL：不走索引，复杂逻辑，切割大事务（插入100万条数据可以拆成100条插入一次）

• 避免业务逻辑错误

• 说白了就是使用数据库时，操作标准一些

二、创建数据库

1.创建一个库一个表，并插入100万数据

#创建库
create database opt
use opt
#创建表
create table test(id int(11),num int(11),k1 char(2),k2 char(4),dt timestamp not null);

#插入100万数据
delimiter //
create procedure rand_data(in num int)
begin
declare str char(62) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890';
declare str2 char(2);
declare str4 char(4);
declare i int default 0;
while i<num do
set str2=concat(substring(str,1+floor(rand()*61),1),substring(str,1+floor(rand()*61),1));
set str4=concat(substring(str,1+floor(rand()*61),2),substring(str,1+floor(rand()*61),2));
set i=i+1;
insert into test values(i,floor(rand()*num),str2,str4,now());
end while;
end;
//
delimiter;

mysql> call rand_data(1000000);

2.查看数据可用性

mysql -uroot -p123
select count(*) from opt.test;

3.进行压力测试

mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 --iterations=1 --create-schema='opt' \
--query="select * from opt.test where num='505037'" engine=innodb \
--number-of-queries=20000 -uroot -p123 -verbose

三、数据库参数优化

1.Max_connections

• 简介
  Mysql的最大连接数，如果服务器的并发请求量比较大，可以调高这个值，当然这是要建立在机器能够支撑的情况下，因为如果连接数越来越多，mysql会为每个连接提供缓冲区，就会开销的越多的内存，所以需要适当的调整该值，不能随便去提高设值。

1.查看方式
mysql> show variables like 'max_connections';
mysql> select @@max_connections;
#查看已经使用多少
mysql> show status like 'Max_used_connections';

2.一般配置
vim /etc/my.cnf 
Max_connections=1024

3.补充:
	1.开启数据库时,我们可以临时设置一个比较大的测试值
	2.观察show status like 'Max_used_connections';变化
	3.如果max_used_connections跟max_connections相同,那么就是max_connections设置过低或者超过服务器的负载上限了，低于10%则设置过大.

#额外指标
IOPS  	每秒支持的IO
connections	连接数
TPS 	每秒最多允许的事务
QPS		每秒最多的查询量

2.back_log

• 简介：
  mysql能暂存的连接数量，当主要mysql线程在一个很短时间内得到非常多的连接请求时候它就会起作用，如果mysql的连接数据达到max_connections时候，新来的请求将会被存在堆栈中，等待某一连接释放资源，该推栈的数量及back_log，如果等待连接的数量超过back_log，将不被授予连接资源。
  back_log值指出在mysql暂时停止回答新请求之前的短时间内有多少个请求可以被存在推栈中，只有如果期望在一个短时间内有很多连接的时候需要增加它

1.查看方式
mysql> show variables like '%back_log%';
mysql> select @@back_log;
#查看有没有等待的，如发现大量的待连接进程时，就需要加大back_log或者加大max_connections的值
mysql> show full processlist

2.配置方式
vim /etc/my.cnf 
back_log=1024

3.wait_timeout和interactive_timeout

• 简介:
  • wait_timeout：指的是mysql在关闭一个非交互的连接之前所要等待的秒数
  • interactive_timeout：指的是mysql在关闭一个交互的连接之前所需要等待的秒数，比如我们在终端上进行mysql管理，使用的即使交互的连接，这时候，如果没有操作的时间超过了interactive_timeout设置的时间就会自动的断开，默认的是28800，可调优为7200。
  • wait_timeout:如果设置太小，那么连接关闭的就很快，从而使一些持久的连接不起作用
  • interactive_timeout类似跳板机，过了多久没操作就会踢掉你，需要重新连接

1.查看方式
mysql> select @@wait_timeout;
mysql> select @@interactive_timeout;
#默认的都是是28800，可调优为7200。

2.配置方式（配置这个可以减轻内存的压力）
wait_timeout=60
interactive_timeout=1200
#如果设置太大，容易造成连接打开时间过长，在show processlist时候，能看到很多的连接 ，一般希望wait_timeout尽可能低
#长连接的应用，为了不去反复的回收和分配资源，降低额外的开销。一般我们会将wait_timeout设定比较小，interactive_timeout要和应用开发人员沟通长链接的应用是否很多。如果他需要长链接，那么这个值可以不需要调整。

4.key_buffer_size

1.简介
key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度
	1)此参数与myisam表的索引有关
	select table_name,engine from information_schema.tables where engine='myisam';
	2)临时表的创建有关（多表链接、子查询中、union）
		在有以上查询语句出现的时候，需要创建临时表，用完之后会被丢弃
		临时表有两种创建方式：
						内存中------->key_buffer_size
						磁盘上------->ibdata1(5.6)
								     ibtmp1 (5.7）

2.查看方式
mysql> show variables like "%key_buffer_size%";
#默认是8M
#查看有多少在走索引，上面的总数，下面的是走磁盘的
mysql> show status like "key_read%";

3.查看临时表创建
mysql> show status like "created_tmp%";
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 10    |		#创建在磁盘的临时表
| Created_tmp_files       | 6     |		#一共临时文件的数量
| Created_tmp_tables      | 70    |		#创建在内存中的临时表
+-------------------------+-------+
#通常地，我们习惯以磁盘建表百分比或者已各自的一个时段内的差额计算，来判断基于内存的临时表利用率。所以，我们会比较关注 Created_tmp_disk_tables 是否过多，从而认定当前服务器运行状况的优劣。
#忽略mysqldump备份时导致的大量使用磁盘表

4.配置方式
key_buffer_size=64M

5.query_cache_size

• 简介：
  查询缓存简称QC，使用查询缓冲，mysql将查询结果存放在缓冲区中，今后对于同样的select语句（区分大小写），将直接从缓冲区中读取结果。

1.SQL层：
	select * from t1 where name=:NAME;
	select * from t1 where name=:NAME;
	1）查询完结果之后，会对SQL语句进行hash运算，得出hash值,我们把他称之为SQL_ID
	2）会将存储引擎返回的结果+SQL_ID存储到缓
	
2.查看方式
mysql> show variables like "%query_cache_size%";
#查看是否开启
mysql> show variables like "query_cache%";
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_cache_limit            | 1048576 |		#超过此大小的查询将不缓存
| query_cache_min_res_unit     | 4096    |		#缓存块的最小大小，太小的话会生成很多内存碎片
| query_cache_size             | 1048576 |		#查询缓存大小
| query_cache_type             | OFF     |		#缓存类型，是否开启
| query_cache_wlock_invalidate | OFF     |		#查询的表被锁，也可以走缓存查询数据
+------------------------------+---------+

3.配置多大根据谁呢
mysql> show status like "%Qcache%";
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |		#缓存中相邻内存块的个数，内存碎片
| Qcache_free_memory      | 1031360 |		#Query Cache 中目前剩余的内存大小
| Qcache_hits             | 0       |		#表示有多少次命中缓存，数字越大，缓存效果越理想
| Qcache_inserts          | 0       |		#没有命中，新插入的数据
| Qcache_lowmem_prunes    | 0       |		#多少条Query因为内存不足而被清除出QueryCache
| Qcache_not_cached       | 2002    |		#不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数
| Qcache_queries_in_cache | 0       |		#当前Query Cache 中cache 的Query 数量
| Qcache_total_blocks     | 1       |		#当前Query Cache 中的block 数量
+-------------------------+---------+
#求命中率：
Qcache_hits / (Qcache_inserts+Qcache_not_cached+Qcache_hits) 
如果出现hits比例过低，其实就可以关闭查询缓存了。使用redis专门缓存数据
#判断内存够不够
Qcache_free_memory   +   Qcache_lowmem_prunes

4.一般配置
修改/etc/my.cnf,配置完后的部分文件如下：
query_cache_size=128M
query_cache_type=1

6.max_connect_errors

• 简介:
  max_connect_errors是一个mysql中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码等情况，当超过指定次数，mysql服务器将禁止host的连接请求，直到mysql服务器重启或通过flush hosts命令清空此host的相关信息 max_connect_errors的值与性能并无太大关系。

1.查看方式
mysql> show variables like "%connect_error%";

2.配置方式
修改/etc/my.cnf文件，在[mysqld]下面添加如下内容
max_connect_errors=2000

7.sort_buffer_size

• 简介：
  • 每个需要进行排序的线程分配该大小的一个缓冲区，增加这值加速
  • ORDER BY、GROUP BY、distinct、union
  • Sort_Buffer_Size并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。列如：500个连接将会消耗500*sort_buffer_size（2M）=1G内存

1.查看方式
mysql> show variables like "%sort_buffer_size%";

3.配置方法
修改/etc/my.cnf文件，在[mysqld]下面添加如下：
sort_buffer_size=1M

8.max_allowed_packet

• 简介：
  mysql根据配置文件会限制，server接受的数据包大小。所有程序都是数据包的形式访问数据库的

1.查看方式
mysql> show variables like '%max_allowed_packet%';

2.配置依据：
有时候大的插入和更新会受max_allowed_packet参数限制，导致写入或者更新失败，更大值是1GB，必须设置1024的倍数，最好的方式就是让开发修改，不要让数据包超过限制

3.一般配置
max_allowed_packet=32M

9.join_buffer_size

• 简介
  • 每个使用join的线程分配该大小的一个缓冲区，增加这值加速
  • select a.name,b.name from a join b on a.id=b.id where xxxx
  • 用于表间关联缓存的大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。
  • 尽量在SQL与方面进行优化，效果较为明显。
  • 优化的方法：在on条件列加索引，至少应当是有普通索引

1.查看方式
mysql> show variables like "%join_buffer_size%";

2.一般配置
join_buffer_size=2M

10.thread_cache_size

• 简介：
  服务器线程缓存，这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时,那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁(前提是缓存数未达上限),如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能.

• 每个连接数据库都会分配一部分资源，如果退出资源会释放，下次再来新的又会分配，频繁的有用户访问和退出，会对服务器线程造成很大的压力，配置这个可以理解为长链接，他的值不是大小，而是数量

1.查看方式
mysql> show variables like "%thread_cache_size%";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 9     |		#个数而不是大小
+-------------------+-------+

2.配置要根据实际情况
#查看试图连接到MySQL(不管是否连接成功)的连接数
mysql>  show status like 'threads_%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 8     |
| Threads_connected | 2     |
| Threads_created   | 4783  |
| Threads_running   | 1     |
+-------------------+-------+
4 rows in set (0.00 sec)

Threads_cached :代表当前此时此刻线程缓存中有多少空闲线程。
Threads_connected:代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。
Threads_created:代表从最近一次服务启动，没有走缓存创建线程的数量，如果发现Threads_created值过大的话，表明MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值。
Threads_running :代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态。

3.一般配置（内存压力过大，不适合设置太大）
(3)配置方法：
thread_cache_size=32

#整理：
Threads_created  ：一般在架构设计阶段，会设置一个测试值，做压力测试。
结合zabbix监控，看一段时间内此状态的变化。
如果在一段时间内，Threads_created趋于平稳，说明对应参数设定是OK。
如果一直陡峭的增长，或者出现大量峰值，那么继续增加此值的大小，在系统资源够用的情况下（内存）

11.innodb_buffer_pool_size

• 简介：
  对于InnoDB表来说，innodb_buffer_pool_size的作用就相当于key_buffer_size对于MyISAM表的作用一样。
  简单来说，就是pool-size可以缓存索引和行数据，值越大，IO读写就越少，如果单纯的做数据库服务，该参数可以设置到电脑物理内存的80%

• 设置要根据自己的实际情况来设置，如果设置的值不在合理的范围内，并不是设置越大越好，可能设置的数值太大体现不出优化效果，反而造成系统的swap空间被占用，导致操作系统变慢，降低sql查询性能。

1.查看方式（默认128M）
mysql> show variables like "%innodb_buffer_pool_size%";

2.配置方法
innodb_buffer_pool_size=2048M

12.innodb_flush_log_at_trx_commit （面试可能会问）

• 简介：
  主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0、1、2三个。
  0：当事务提交时，不做日志写入操作，而是每秒钟将log buffer中的数据写入日志文件并flush磁盘一次；
  1：则在每秒钟或是每次事物的提交都会引起日志文件写入、flush磁盘的操作，确保了事务的ACID；
  2：每次事务提交引起写入日志文件的动作,但每秒钟完成一次flush磁盘操作。

• 配置依据：
  实际测试发现，该值对插入数据的速度影响非常大，设置为2时插入10000条记录只需要2秒，设置为0时只需要1秒，而设置为1时则需要229秒。因此，MySQL手册也建议尽量将插入操作合并成一个事务，这样可以大幅提高速度。根据MySQL官方文档，在允许丢失最近部分事务的危险的前提下，可以把该值设为0或2。

• 详解（了解）
  当innodb_flush_log_at_trx_commit被 设置为0，日志缓冲每秒一次地被写到日志文件，并且对日志文件做到磁盘操作的刷新，但是在一个事务提交不做任何操作。

  • 当这个值为1（默认值）之时，在每个事务提交时，日志缓冲被写到日志文件，对日志文件做到磁盘操作的刷新。
  • 当设置为2之时，在每个提交，日志缓冲被写到文件，但不对日志文件做到磁盘操作的刷新。
  • 尽管如此，在对日志文件的刷新在值为2的情况也每秒发生一次。我们必须注意到，因为进程安排问题，每秒一次的刷新不是100%保证每秒都发生。你可以通过设置这个值不为1来获得较好的性能，但随之你会在一次崩溃中损失二分之一价值的事务。
  • PS：
    如果你设置这个值为0，那么任何mysqld进程的崩溃会删除崩溃前最后一秒的事务；
    如果你设置这个值为2，那么只有操作系统崩溃或掉电才会删除最后一秒的事务。尽管如此，InnoDB的崩溃恢复不受影响，而且因为这样崩溃恢复开始作用而不考虑这个值。注意，许多操作系统和一些磁盘硬件会欺骗刷新到磁盘操作。尽管刷新没有进行，你可以告诉mysqld刷新已经进行。
    如果你设置这个值为1，事务的持久程度不被保证，且在最坏情况下掉电甚至会破坏InnoDB数据库。在SCSI磁盘控制器中，或在磁盘自身中，使用有后备电池的磁盘缓存会加速文件刷新并且使得操作更安全。你也可以试着使用Unix命令hdparm来在硬件缓存中禁止磁盘写缓存，或使用其它一些对硬件提供商专用的命令。这个选项的默认值是1。

1.查看方式
mysql> show variables like "%innodb_flush_log_at_trx_commit%";

2.配置方法
innodb_flush_log_at_trx_commit=1
双1标准中的一个1

3.双一标准另一个1
sync_binlog

• sync_binlog 的默认值是0，像操作系统刷其他文件的机制一样，MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。sync_binlog控制数据库的binlog刷到磁盘上去
• 默认，sync_binlog=0，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。
  如果sync_binlog>0，表示每sync_binlog次事务提交，MySQL调用文件系统的刷新操作将缓存刷下去。
• 最安全的就是sync_binlog=1了，表示每次事务提交，MySQL都会把binlog刷下去，是最安全但是性能损耗最大的设置。这样的话，在数据库所在的主机操作系统损坏或者突然掉电的情况下，系统才有可能丢失1个事务的数据。但是binlog虽然是顺序IO，但是设置sync_binlog=1，多个事务同时提交，同样很大的影响MySQL和IO性能。虽然可以通过group commit的补丁缓解，但是刷新的频率过高对IO的影响也非常大。对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

13.innodb_thread_concurrency

• 简介

  • 此参数用来设置innodb线程的并发数量，默认值为0表示不限制。数据库属于单进程多线程，怎么保证CPU使用的；
  • 一般线程小于64，设置innodb_thread_concurrency 为0，如果工作负载一直很高，建议设置innodb_thread_concurrency=128，逐渐降低测试一个最优的值，一般查看CPU使用率去配置，如果CPU使用率很平均，那么不需要调优，如果不平均，可以配置innodb_thread_concurrency由低逐渐加大来测试；

• 配置测试：

1.设置标准：
	1）当前系统cpu使用情况，均不均匀
	top
	2）当前的连接数，有没有达到顶峰
	show status like 'threads_%';
	show processlist;
设置方法:
	1）看top ,观察每个cpu的各自的负载情况
	2）发现不平均,先设置参数为cpu个数,然后不断增加(一倍)这个数值
	3）一直观察top状态,直到达到比较均匀时,说明已经到位了.

2.查看方式
mysql> show variables like "%innodb_thread_concurrency%";

3.配置方式
innodb_thread_concurrency=8

• 在官方doc上，对于innodb_thread_concurrency的使用，也给出了一些建议，如下：
  • 如果一个工作负载中，并发用户线程的数量小于64，建议设置innodb_thread_concurrency=0；
  • 如果工作负载一直较为严重甚至偶尔达到顶峰，建议先设置innodb_thread_concurrency=128，并通过不断的降低这个参数，96, 80, 64等等，直到发现能够提供最佳性能的线程数，例如，假设系统通常有40到50个用户，但定期的数量增加至60，70，甚至200。你会发现，性能在80个并发用户设置时表现稳定，如果高于这个数，性能反而下降。在这种情况下，建议设置innodb_thread_concurrency参数为80，以避免影响性能。
  • 如果你不希望InnoDB使用的虚拟CPU数量比用户线程使用的虚拟CPU更多（比如20个虚拟CPU），建议通过设置innodb_thread_concurrency 参数为这个值（也可能更低，这取决于性能体现），如果你的目标是将MySQL与其他应用隔离，你可以l考虑绑定mysqld进程到专有的虚拟CPU。
  • 但是需 要注意的是，这种绑定，在myslqd进程一直不是很忙的情况下，可能会导致非最优的硬件使用率。在这种情况下，你可能会设置mysqld进程绑定的虚拟 CPU，允许其他应用程序使用虚拟CPU的一部分或全部。在某些情况下，最佳的innodb_thread_concurrency参数设置可以比虚拟CPU的数量小。定期检测和分析系统，负载量、用户数或者工作环境的改变可能都需要对innodb_thread_concurrency参数的设置进行调整。

14.innodb_log_buffer_size

• 简介
  此参数确定些日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，对于较大的事务，可以增大缓存大小。
• 设定依据：
  • 大事务
  • 多事务：事务并发提交时，如果值太小会影响效率，所有的事务都在等待

1.查看方式
mysql> show variables like "%innodb_log_buffer_size%";

2.配置方式
innodb_log_buffer_size=128M

15.innodb_log_file_size

• 简介
  • 设置磁盘文件的大小
  • 设置 ib_logfile0 ib_logfile1 大小
  • 此参数确定数据日志文件的大小，以M为单位，更大的设置可以提高性能.

1.查看方式
mysql> show variables like '%innodb_log_file_size%';

2.配置
innodb_log_file_size=128M

16.innodb_log_files_in_group

• 简介
  为提高性能，MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3

1.查看方法
mysql> show variables like '%innodb_log_files_in_group%';

2.设置
innodb_log_files_in_group=3

17.read_buffer_size

• 简介
  MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。和 sort_buffer_size一样，该参数对应的分配内存也是每个连接独享

1.查看方式
mysql> show variables like '%read_buffer_size%';

2.配置
read_buffer_size=1M

18.read_rnd_buffer_size

• 简介
  • MySql的随机读（查询操作）缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。
  • 注：顺序读是指根据索引的叶节点数据就能顺序地读取所需要的行数据。随机读是指一般需要根据辅助索引叶节点中的主键寻找实际行数据，而辅助索引和主键所在的数据段不同，因此访问方式是随机的。

1.查看方式
mysql> show variables like '%read_rnd_buffer_size%';

2.配置
read_rnd_buffer_size=1M

19.bulk_insert_buffer_size = 8M

• 简介
  批量插入数据缓存大小，可以有效提高插入效率，默认为8M

1.查看方式
mysql> show variables like '%bulk_insert_buffer_size%';

2.一般配置
bulk_insert_buffer_size=8M

20.binary log

log-bin=/data/mysql-bin
binlog_cache_size = 2M //为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存, 提高记录bin-log的效率。没有什么大事务，dml也不是很频繁的情况下可以设置小一点，如果事务大而且多，dml操作也频繁，则可以适当的调大一点。前者建议是--1M，后者建议是：即 2--4M

max_binlog_cache_size = 8M //表示的是binlog 能够使用的最大cache 内存大小
max_binlog_size= 512M //指定binlog日志文件的大小，如果当前的日志大小达到max_binlog_size，还会自动创建新的二进制日志。你不能将该变量设置为大于1GB或小于4096字节。默认值是1GB。在导入大容量的sql文件时，建议关闭sql_log_bin，否则硬盘扛不住，而且建议定期做删除。
expire_logs_days = 7 //定义了mysql清除过期日志的时间。
二进制日志自动删除的天数。默认值为0,表示“没有自动删除”。

log-bin=/data/mysql-bin
binlog_format=row 
sync_binlog=1

双1标准(基于安全的控制)：
sync_binlog=1   什么时候刷新binlog到磁盘，每次事务commit
innodb_flush_log_at_trx_commit=1
set sql_log_bin=0;
#查看语句执行数量
show status like 'com_%';

21.安全参数

Innodb_flush_method=(O_DIRECT, fdatasync) 

1.fdatasync
	1)在数据页需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘
	2)在redo buffuer需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘，但如果innodb_flush_log_at_trx_commit=1的话，日志还是直接每次commit直接写入磁盘
2.Innodb_flush_method=O_DIRECT
	1)在数据页需要持久化时，直接写入磁盘
	2)在redo buffuer需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘，但如果innodb_flush_log_at_trx_commit=1的话，日志还是直接每次commit直接写入磁盘
	
1.数据库基于安全的话
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT

2.数据库基于性能的话
innodb_flush_log_at_trx_commit=0
innodb_flush_method=fdatasync

22.最终数据库配置

[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
log-error=mysql.err
log_bin=mysql-bin
binlog_format=row
skip-name-resolve
server-id=1
log-slave-updates=1
relay_log_purge=0
max_connections=1024
back_log=128
wait_timeout=60
interactive_timeout=7200
key_buffer_size=16M
query_cache_size=64M
query_cache_type=1
query_cache_limit=50M
max_connect_errors=20
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
thread_cache_size=200
innodb_buffer_pool_size=1024M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=3
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
read_buffer_size=2M
read_rnd_buffer_size=2M
bulk_insert_buffer_size=8M
[client]
socket=/tmp/mysql.sock	

四、优化测试

1.没有优化时测试

···

2.优化后访问测试

···

3.优化后第二次访问测试

···