mysql数据库优化

目的

必要且重要

优化：

sql及索引

工具 mysqldumpslow 查询慢日志，信息比较少

pt-query-digest 时间稍长，分析比较详细，次数 占百分比 查多少行 使用行数 ……

慢日志：

pt-query-digest注意rows examine

找到慢日志用explain分析

type：const eq_ref  ref range index all

子查询，子查询优化为join查询

检查索引：pt-duplicate-key-checker检查重复及冗余索引

不适应的索引pt-index-usage工具 pt-index-usage -uroot -p '' mysql-slow.log

 

表结构

可以存下数据的最下的数据类型

简单数据类型，int比varchar，timestamp简单

1、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('2020-03-10 00:00:00'));

select from_unixtime(timestr) from test;

2、bigint 存ip ，inet_aton(),inet_ntoa()
create table sessions(id int auto_increment not null,
                     ipaddress bigint,primary key(id));

insert into sessions(ipaddress) values(inet_aton('192.0.3.2'));

select inet_ntoa(ipaddress) from sessions;

尽可能not null 定义字段

少使用text 大类型，非要不可分表

反范式化设计表

空间换时间，适当冗余，这也是公司常用的选择，关于范式之前的博客有写，不再重复

垂直拆分：很多列的表拆分成多个表

水平拆分：单表数据量过大，表结构相同

 

系统配置

网络配置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=10

打开文件数限制，ulimit -a 查看目录各位限制修改etc/security/limits.conf，增加如下

*soft nofiles 65535

* hard nofile 65535

关闭iptables selinux等防火墙软件

配置文件

mysql启动指定配置参数或使用配置文件进行配置，etc/my.cnf 或/etc/mysql/my.cnf，查找配置文件的顺序：

/usr/sbin/mysqld --verbose --help | grep -A 1 'default options'多个文件，后面覆盖前面

参数说明

innodb_buffer_pool_size配置innodb缓冲池，如果只有innodb表推荐配置为总内存的75%

innodb_buffer_pool_instances控制缓存池的个数，默认情况只有一个缓存池，mysql5.5新增参数

innodb_log_buffer_size：log缓冲大小，不用太大，日志每秒刷新

innodb_flush_log_at_trx_commit：默认值1，每次提交都刷新到磁盘 0每秒钟提交 2每次提交刷新到缓存区1秒提交

innodb_read_io_threads

innodb_write_io_threads 读写io进程数

innodb_file_per_table：控制innodb每个表使用独立的表空间，默认off 所有表建立在共享表空间中，建议on

innodb_stats_on_metadata：什么情况下刷新innodb表的统计信息，建议off

配置工具

percon configuration wizard 网址 https://tools.percona.com/wizard 填写配置生产配置文件，当然只是建议

 

硬件

replicate ，sql 只能使用单核cpu

mysql5.5使用服务器不要超过32核

disk io优化

raid级别

raid0 条带 多个磁盘连接成一个硬盘使用，io级别最好，一个损坏其他全完

raid1镜像，至少两个磁盘，每组磁盘储存的数据相同，较安全

raid5多个(最少3个）硬盘合并成1个逻辑盘使用，数据读写时建立奇偶校验信息且奇偶校验信息和相对应的数据分别存储于不同的磁盘上，当raid5的一个磁盘发生损坏，利用剩下的数据和相应奇偶校验信息恢复被损坏的数据

raid1+0：raid1和raid0组合，建议使用

 

https://www.imooc.com/video/3707