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.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
使用场景
日志和数据采集类应用
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 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;
使用场景
用于查找或者是映射表,例如邮编和地区的对应表
用于保存数据分析中产生的中间表
用于缓存周期性聚合数据的结果表
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语句
数据库存储引擎的选择和参数配置
系统选择及优化
硬件升级
source