数据库优化

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

1.一般数据库选择

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

				

					
				
				

					
				
				

					
				
				

					
				
			

2.数据库类型

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

				

					
				
				

					
				
				

					
				
				

					
				
			

3.硬件选型

1）CPU选型

1.IO密集型：线上系统，OLTP主要是IO密集型的业务，高并发（OLTP），E系列（至强），主频相对低，核心数量多
2.CPU密集型：数据分析数据处理，OLAP，cpu密集型的，需要CPU高计算能力（OLAP，不需要很高的并发，计算只用一个用户就可以了），I系列的（IBM），主频很高，核心少 （打游戏一般选择CPU密集型）

				

					
				
				

					
				
				

					
				
				

					
				
			

2）内存选择

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

				

					
				
				

					
				
				

					
				
				

					
				
			

3）磁盘选择

1.SATA-III   
2.SAS    
3.Fc    
4.SSD（sata） 
    pci-e  级别
    Flash  级别

				

					
				
				

					
				
				

					
				
				

					
				
			

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

5）网络选择

1.硬件买好的（单卡单口，网卡有很多个口，选择单口的，性能更好）
    一般可以插4块卡，两个内网两个外网，避免一块出现问题就挂掉
2.网卡绑定（bonding），交换机堆叠
    意思就像负载均衡，将两块网卡逻辑绑定，一个网卡绑定一个交换机，如果做了网卡绑定，交换机也一定要做堆叠
    绑定方式：负载均衡模式，主备模式

				

					
				
				

					
				
				

					
				
				

					
				
			

4.操作系统优化

1）Swap调整

echo 0 >/proc/sys/vm/swappiness的内容改成0（临时），
 
/etc/sysctl.conf 上添加 vm.swappiness=0（永久）
sysctl -p
 
这个参数决定了Linux是倾向于使用swap，还是倾向于释放文件系统cache。在内存紧张的情况下，数值越低越倾向于释放文件系统cache。
当然，这个参数只能减少使用swap的概率，并不能避免Linux使用swap。

				

					
				
				

					
				
				

					
				
				

					
				
			

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.应用端优化

1. 减少烂SQL：不走索引，复杂逻辑，切割大事务（插入100万条数据可以拆成100条插入一次）
2. 避免业务逻辑错误
3. 说白了就是使用数据库时，操作标准一些

				

					
				
				

					
				
				

					
				
				

					
				
			

二、创建数据库

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

1.简介
Mysql的最大连接数，如果服务器的并发请求量比较大，可以调高这个值，当然这是要建立在机器能够支撑的情况下，因为如果连接数越来越多，mysql会为每个连接提供缓冲区，就会开销的越多的内存，所以需要适当的调整该值，不能随便去提高设值。
 
2.查看方式
mysql> show variables like 'max_connections';
mysql> select @@max_connections;
#查看已经使用多少
mysql> show status like 'Max_used_connections';
 
3.一般配置
vim /etc/my.cnf 
Max_connections=1024
 
4.补充:
    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

1.简介：
mysql能暂存的连接数量，当主要mysql线程在一个很短时间内得到非常多的连接请求时候它就会起作用，如果mysql的连接数据达到max_connections时候，新来的请求将会被存在堆栈中，等待某一连接释放资源，该推栈的数量及back_log，如果等待连接的数量超过back_log，将不被授予连接资源。
back_log值指出在mysql暂时停止回答新请求之前的短时间内有多少个请求可以被存在推栈中，只有如果期望在一个短时间内有很多连接的时候需要增加它
 
2.查看方式
mysql> show variables like '%back_log%';
mysql> select @@back_log;
#查看有没有等待的，如发现大量的待连接进程时，就需要加大back_log或者加大max_connections的值
mysql> show full processlist
 
3.配置方式
vim /etc/my.cnf 
back_log=1024

				

					
				
				

					
				
				

					
				
				

					
				
			

3.wait_timeout和interactive_timeout

1.简介
wait_timeout：指的是mysql在关闭一个非交互的连接之前所要等待的秒数
interactive_timeout：指的是mysql在关闭一个交互的连接之前所需要等待的秒数，比如我们在终端上进行mysql管理，使用的即使交互的连接，这时候，如果没有操作的时间超过了interactive_timeout设置的时间就会自动的断开，默认的是28800，可调优为7200。
wait_timeout:如果设置太小，那么连接关闭的就很快，从而使一些持久的连接不起作用
#interactive_timeout类似跳板机，过了多久没操作就会踢掉你，需要重新连接
 
2.查看方式
mysql> select @@wait_timeout;
mysql> select @@interactive_timeout;
#默认的都是是28800，可调优为7200。
 
3.配置方式（配置这个可以减轻内存的压力）
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