扛得住双11的Mysql---mysql优化

mysql本身性能影响

插件式存储引擎

frm后缀文件记录了表的结构

MyISAM

mysql版本<5.5默认存储引擎

MyISAM存储引擎表由MYD和MYI组成

• 并发性与锁级别
• 表损坏修复
• 支持的索引类型

• 支持数据压缩

  check table tb_name
  repair table tb_name

  命令

  myisampack

限制

版本<5.0默认表大小为4G

如存储大表则要修改MAX_Rows和AVG_ROW_LENGTH

适用场景

1. 非事务型应用,比如数据仓库,报表类应用还有大多数不涉及到财务的应用
2. 可以对表进行压缩,对于只读报表这样的应用也很合适
3. 空间类应用

Innodb

版本5.5默认存储引擎

使用表空间进行数据存储

innodb_file_per_table

ON:独立表空间---tablename.ibd
OFF:系统表空间---ibdataX


mysql>show variables like 'innodb_file_per_table';

mysql>set global innodb_file_per_table=off;

如何选择系统表空间和独立表空间

1. 系统表空间无法简单的收缩文件大小
2. 独立表空间可以通过optimize table命令收缩系统文件
3. 系统表空间会产生IO瓶颈
4. 独立表空间可以同时向多个文件刷新数据

建议

• 对Inoodb使用独立表空间

表转移的步骤

把原来存在于系统表空间中的表转移到独立表空间的办法

1. 使用mysqldump导出所有数据库表数据
2. 停止MYSQL服务,修改参数,并删除Innodb相关文件
3. 重启MYSQL服务,重建Innodb系统表空间
4. 重新导入数据

系统表空间

• Innodb数据字典信息
• undo回滚段

特性

• Innodb是一种事务性存储引擎
• 完全支持事务的ACID特性
• Redo log和Undo log
• 支持行级锁
• 行级锁可以最大程度的支持并发
• 行级锁是由存储引擎层实现的

• Inodb状态检测

  show variables like ‘innodb_log_buffer_size’;

  ib_logfile文件

  show variables like ‘innodb_log_files_in_group’;

  show engine innodb status 状态检测

锁

• 锁的主要作用是管理共享资源的并发访问
• 实现事务的隔离性

锁的类型

1. 共享锁(读锁)
2. 独占锁(写锁)

锁的粒度

• 表级锁

• 行级锁

  lock table tablename write; 表级锁

阻塞和死锁

innodb适合于大多数OLTP应用

CSV存储引擎

1. 数据以文本方式存储在文件中
2. .csv文件存储表内容
3. .csm文件存储表的元数据如表状态和数据量
4. .frm文件存储表结构信息

特点
1. 以CSV格式进行数据存储
2. 所有列必须都是不能为NULL的
3. 不支持索引,不适合大表,不适合在线处理
4. 可以对数据文件直接编辑

1,"aaa","bbb"
2,"ccc","ddd"

mysql> create table mycsv(id int,c1 varchar(10),c2 char(10)) engine=csv;
ERROR 1178 (42000): The storage engine for the table doesn't support nullable columns

mysql> create table mycsv(id int not null,c1 varchar(10) not null,c2 char(10) not null) engine=csv; 

#表刷新操作,重新读取表
mysql> flush tables; 

#不支持建立索引
mysql> create index idx_id on mycsv(id);
ERROR 1069 (42000): Too many keys specified; max 0 keys allowed

适用场景

1. 适合做为数据交换的中间表
2. 电子表格—>CSV文件—>MySQL数据目录
3. 数据—>CSV文件—>其它web程序

Archive引擎

文件系统存储特点

1. 以zlib对表数据进行压缩,磁盘IO更少
2. 数据存储在ARZ为后缀的文件中

特点

1. 只支持insert和select操作

2. 只允许在自增ID列上加索引

   mysql> create table myarchive(id int auto_increment not null,c1 varchar(20),c2 char(10), key(id)) engine=archive;

   文件系统中的存储

   -rw-r—– 1 mysql mysql 8696 Jun 20 18:11 myarchive.ARZ
   -rw-r—– 1 mysql mysql 8608 Jun 20 18:11 myarchive.frm

   mysql> insert into myarchive(c1,c2) values(‘aa’,’bb’),(‘cc’,’dd’);

   不支持删除delete操作

   mysql> delete from myarchive where id=1;
   ERROR 1031 (HY000): Table storage engine for ‘myarchive’ doesn’t have this option

   不支持更新update操作

   mysql> update myarchive set c1=’aaa’ where id=1;
   ERROR 1031 (HY000): Table storage engine for ‘myarchive’ doesn’t have this option

使用场景

1. 日志和数据采集类应用

Memory存储引擎

文件系统存储特点

也称之为HEAP存储引擎,所以数据保存在内存中

mysql重启之后,表数据会丢失,表结构存在

功能特点

1. 支持HASH索引和BTree索引
2. 等值查找—HASH索引
3. 范围查找—BTree索引
4. 所有字段都为固定长度 varchar(10)=char(10)
5. 不支持BLOG和TEXT等大字段
6. 使用表级锁

7. 表的最大大小由max_heap_table_size参数决定,修改后对已存在的表无效,需要重建

   mysql> create table mymemory(id int,c1 varchar(10),c2 char(10),c3 text) engine=memory;
   ERROR 1163 (42000): The used table type doesn’t support BLOB/TEXT columns

   mysql> create table mymemory(id int,c1 varchar(10),c2 char(10)) engine=memory;

   文件系统存储

   -rw-r—– 1 mysql mysql 8608 Jun 20 18:33 mymemory.frm

   建立索引

   mysql> create index idx_c1 on mymemory(c1);
   mysql> create index idx_c2 using btree on mymemory(c2);

   mysql> show index from mymemory\G;

   mysql> show table status like ‘mymemory’\G;

使用场景

1. 用于查找或者是映射表,例如邮编和地区的对应表
2. 用于保存数据分析中产生的中间表
3. 用于缓存周期性聚合数据的结果表

memory数据易丢失,所以要求数据可再生

容易混淆的概念

临时表

系统使用临时表

1. 超过限制使用Myisam临时表
2. 未超限制使用memory表

create temporary table 建立的临时表

Federated存储引擎

特点

1. 提供了访问远程mysql服务器上表的方法
2. 本地不存储数据,数据全部放到远程服务器上
3. 本地需要保存表结构和远程服务器的连接信息

使用

1. 默认禁止,启用需要在启动时增加federated参数

   mysql://user_name[:password]@host_name[:port_num]/db_name/tbl_name

   mysql> show engines\G;

   my.cnf
   federated=1

如何选择正确的存储引擎参考条件

1. 事务
2. 备份
3. 崩溃恢复
4. 存储引擎的特性

建议:不要混合使用存储引擎

Mysql配置

命令行参数

mysqld_safe --datadir=/data/sql_data    

配置文件
/etc/my.cnf (centos)
/etc/mysql/my.cnf(Debian)

#mysql读取配置文件的顺序
mysqld --help --verbose | grep -A 1 'Default options'

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /data/mysqld/etc/my.cnf ~/.my.cnf

全局参数

set global 参数名=参数值;
set @@global.参数名:=参数值;

会话参数

set [session] 参数名=参数值;
set @@session.参数名:=参数值;

内存配置相关参数

#确定MySQL的每个连接使用的内存
#为每一个线程所分配,当有很多连接的时候,占据内存几倍的数量
sort_buffer_size
join_buffer_size
read_buffer_size(n*4k)
read_rnd_buffer_size 索引

#确定需要为操作系统保留多少内存

#如何为缓存池分配内存
#影响了innodb的性能,延迟写入的功能
#总内存-(每个线程所需要的内存*连接数)-系统保留内存
Innodb_buffer_pool_size

#myisam引擎的配置
key_buffer_size

#myisam表索引所占用的空间大小
select sum(index_lengt) from information_schema.tables where engine='myisam'

#mysql的系统表还在使用myisam存储引擎

IO相关配置参数

innodb_log_file_size
innodb_log_files_in_group

#32M~128M
innodb_log_buffer_size 事务日志缓冲区大小

#0:每秒进行一次log写入cache,并flush log到磁盘
#1[默认]:在每次事务提交执行log写入cache,并flush log到磁盘
#2[建议]:每次事务提交,执行log数据写入到cache,每秒执行一次flush log到磁盘
innodb_flush_log_at_trx_commit 

innodb_flush_method=O_DIRECT

innodb_file_per_table=1 建立单独表空间(强烈建议)

innodb_doublewrite=1 双写缓存

事务日志总大小=innodb_log_files_in_group*innodb_log_file_size

选择合适的innodb_log_file_size

#OFF:每次写操作后刷新键缓冲中的脏块到磁盘
#ON:只对在键表时指定了delay_key_write选项的表使用延迟刷新
#ALL:对所有Myisam表都使用延迟键写入
delay_key_write 

安全配置

expire_logs_days 指定自动清理binlog的天数
max_allowed_packet 控制Mysql可以接受的包的大小
skip_name_resolve 禁用DNS查找
sysdate_is_now 确保sysdate()返回确定性日期
read_only 禁止非super权限的用户写权限
skip_slave_start 禁用slave自动恢复
sql_mode 设置MySQL所使用的SQL模式

    strict_trans_tables
    no_engine_subtitution
    no_zero_data
    no_zero_in_date
    only_full_group_by

其它配置

sync_binlog 控制Mysql如何向磁盘刷新binlog

#一起使用的两个参数
#控制内存临时表大小
tmp_table_size
max_heap_table_size

max_connections 控制允许的最大连接数

数据库设计对性能的影响

1. 过分的反范式化为表建立太多的列
2. 过分的范式化造成太多的表关联
3. 在OLTP环境中使用不恰当的分区表
4. 使用外键保证数据的完整性

性能优化顺序

1. 数据库结构设计和SQL语句
2. 数据库存储引擎的选择和参数配置
3. 系统选择及优化
4. 硬件升级

---

source

1. iMySQL—专注MySQL十余年