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
适用场景
- 非事务型应用,比如数据仓库,报表类应用还有大多数不涉及到财务的应用
- 可以对表进行压缩,对于只读报表这样的应用也很合适
- 空间类应用
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;
如何选择系统表空间和独立表空间
- 系统表空间无法简单的收缩文件大小
- 独立表空间可以通过optimize table命令收缩系统文件
- 系统表空间会产生IO瓶颈
- 独立表空间可以同时向多个文件刷新数据
建议
- 对Inoodb使用独立表空间
表转移的步骤
把原来存在于系统表空间中的表转移到独立表空间的办法
- 使用mysqldump导出所有数据库表数据
- 停止MYSQL服务,修改参数,并删除Innodb相关文件
- 重启MYSQL服务,重建Innodb系统表空间
- 重新导入数据
系统表空间
- 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 状态检测
锁
- 锁的主要作用是管理共享资源的并发访问
- 实现事务的隔离性
锁的类型
- 共享锁(读锁)
- 独占锁(写锁)
锁的粒度
- 表级锁
行级锁
lock table tablename write; 表级锁
阻塞和死锁
innodb适合于大多数OLTP应用
CSV存储引擎
- 数据以文本方式存储在文件中
- .csv文件存储表内容
- .csm文件存储表的元数据如表状态和数据量
- .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
适用场景
- 适合做为数据交换的中间表
- 电子表格—>CSV文件—>MySQL数据目录
- 数据—>CSV文件—>其它web程序
Archive引擎
文件系统存储特点
- 以zlib对表数据进行压缩,磁盘IO更少
- 数据存储在ARZ为后缀的文件中
特点
- 只支持insert和select操作
只允许在自增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.frmmysql> 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
使用场景
- 日志和数据采集类应用
Memory存储引擎
文件系统存储特点
也称之为HEAP存储引擎,所以数据保存在内存中
mysql重启之后,表数据会丢失,表结构存在
功能特点
- 支持HASH索引和BTree索引
- 等值查找—HASH索引
- 范围查找—BTree索引
- 所有字段都为固定长度 varchar(10)=char(10)
- 不支持BLOG和TEXT等大字段
- 使用表级锁
表的最大大小由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 columnsmysql> 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;
使用场景
- 用于查找或者是映射表,例如邮编和地区的对应表
- 用于保存数据分析中产生的中间表
- 用于缓存周期性聚合数据的结果表
memory数据易丢失,所以要求数据可再生
容易混淆的概念
临时表
系统使用临时表
- 超过限制使用Myisam临时表
- 未超限制使用memory表
create temporary table 建立的临时表
Federated存储引擎
特点
- 提供了访问远程mysql服务器上表的方法
- 本地不存储数据,数据全部放到远程服务器上
- 本地需要保存表结构和远程服务器的连接信息
使用
默认禁止,启用需要在启动时增加federated参数
mysql://user_name[:password]@host_name[:port_num]/db_name/tbl_name
mysql> show engines\G;
my.cnf
federated=1
如何选择正确的存储引擎参考条件
- 事务
- 备份
- 崩溃恢复
- 存储引擎的特性
建议:不要混合使用存储引擎
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
#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 控制允许的最大连接数
数据库设计对性能的影响
- 过分的反范式化为表建立太多的列
- 过分的范式化造成太多的表关联
- 在OLTP环境中使用不恰当的分区表
- 使用外键保证数据的完整性
性能优化顺序
- 数据库结构设计和SQL语句
- 数据库存储引擎的选择和参数配置
- 系统选择及优化
- 硬件升级