mysql数据库简单优化 – 数据库结构和配置优化

选择合适的数据类型

1.使用可以存下你的数据的最小的数据类型
2.使用简单的数据类型。int 要比varchar类型在mysql处理上简单
3.尽可能的使用not null定义字段
4.尽量少用text类型，费用不可时最好考虑分表
例如:
1.用int来存储日期时间，通过FROM_UNIXTIME(),UNIX_TIMESTAMP()来相互转化

create table test(
id int auto_increment not null,
timestr int,
primary key(id)
);
insert into test(timestr) values(UNIX_TIMESTAMP('2015-01-01 11:10:09'));
select FROM_UNIXTIME(timestr) FROM  test;

2.用bigint来存储ip地址，利用INET_ATON(),INET_NTOA()两个函数来相互转化

create table testq(
id int auto_increment not null,
ipaddress bigint,
primary key(id)
);
insert into testq(ipaddress) values(INET_ATON('192.168.0.1'));
select INET_NTOA(ipaddress) from testq;

表的范式化和反范式优化

表的垂直和水平拆分

系统配置优化

网络方面的配置,

修改/etc/sysctl.conf

增加tcp支持的队列数
net.ipv4.tcp_max_syn_backlog = 65535
减少断开连接时,资源回收
net.ipv4.tcp_max_tw_buckets = 8000
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_fin_timeout = 1

由于每次表查询都是打开一个表文件，打开文件数的限制,可以使用ulimit -a 查看目录的各位限制，
可以修改/etc/security/limits.conf文件，增加以下内容以修改
打开文件数量的限制

*soft nofile 65535
*hard nofile 65535

除此之外最好在mysql服务器上关闭iptables,selinux等防火墙软件

mysql 配置文件配置

这里大多针对Innodb数据引擎
1.Innodb_buffer_pool_size
非常重要的一个参数，用于配置innodb的缓冲池如果数据库中只有innodb表，则推荐配置量为总内存的75%

//显示系统中每个数据库引擎表的总的大小
select ENGINE,
ROUND(SUM(data_length+index_length)/1024/1024,1) AS "Total MB"
FROM INFORMATION_SCHEMA.TABLES WHERE table_schema not in
("information_schema","performance_schema") group by ENGINE;

则Innodb_buffer_pool_size>=Total MB

2.innodb_buffer_pool_instances
mysql5.5中新添加参数，可以控制缓冲池的个数，默认情况下只有一个缓冲池

3.innodb_log_buffer_size
innodb log缓冲的大小,由于日志最长每秒钟就会刷新所以一般不会太大

4.innodb_flush_log_at_trx_commit
多长时间把变更刷新到磁盘.关键参数，对innodb的IO效率影响很大。默认为1,可以取0,1,2三个值
一般建议设为2,但如果数据安全性要求比较高则使用默认值1

5.innodb_read_io_threads 和 innodb_write_io_threads
决定了innodb读写的IO进程数,默认为4

6.innodb_file_per_table
关键参数。控制innodb每个表使用独立的表空间，默认为OFF,
也就是所有表都会建立在共享表空间中…可以设为ON.

7.innodb_stats_on_metadata
觉得mysql在什么情况下刷新innodb表的统计信息

MySQL数据库简单优化(一)
MySQL数据库简单优化(二)

MySQL数据库简单优化(三)