MySQL优化
1.数据库硬件优化(选型)
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. 说白了就是使用数据库时,操作标准一些
2.创建数据库
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