MySQL的常见存储引擎介绍与参数设置调优
作者:demon_gdy 发布时间:2024-01-16 15:08:07
MySQL常用存储引擎之MyISAM
特性:
1、并发性与锁级别
2、表损坏修复
check table tablename
repair table tablename
3、MyISAM表支持的索引类型
①、全文索引
②、前缀索引
4、MyISAM表支持数据压缩
myisampack
限制:
版本 < MySQL5.0时默认表大小为4G
如存储达标则要修改MAX_Rows和AVG_ROW_LENGTH
版本 > MySQL5.0时默认支持为256TB适用场景:
1、非事务形应用
2、只读类应用
3、空间类应用
MySQL常用存储引擎之Innodb
Innodb存储引擎的特征
1、Innodb是一种事务性存储引擎
2、完全支持事务的ACID特性
3、Redo Log 和 Undo Log
4、Innodb支持行级锁Innodb使用表空间进行 数据存储
为每个表独立创建一个表空间存储
innodb_file_per_table
ON:独立表空间:tablename.ibd
OFF:系统表空间:ibdataX(X是个数字,从1开始的数字)
系统表空间和独立表空间要如何选择
比较:
系统表空间无法捡的收缩文件大小
独立表空格键可以通过optimize table命令收缩系统文件
系统表空间会产生IO瓶颈
独立表空间可以同时向多个文件刷新数据
表转移的步骤
步骤:
1、使用mysqldump到处所有数据库表数据
2、停止MySQL服务,修改参数,并删除Innodb相关文件
3、重启MySQL服务,重建Innodb系统表空间
4、重新导入数据
MySQL常见的存储引擎之CSV
文件系统存储特点
1、数据以文本方式存储在文本中
2、.csv文件存储表内容
3、.csm文件存储表的元数据如表状态和数据量
4、.frm文件存储表结构信息
5、以csv格式进行存储
6、所有列必须都是不能为Null的
7、不支持索引适用场景:
适用作为数据交换的中间表(电子表格->csv文件->MySQL数据库目录)
MySQL常用存储引擎之Archive
文件系统存储特点
1、以zlib对表数据进行压缩,磁盘I/O更少
2、数据存储在ARZ为后缀的文件中Archive存储引擎的特点
1、只支持insert和select操作
2、只允许在自增的ID列上加索引
适用场景:
日志和数据采集类应用
MySQL常用存储引擎之Memory
文件系统存储特点
1、也成HEAP存储引擎,所以数据保存在内存中功能特点:
1、支持HASH索引和Btree索引
2、所有字段都有固定长度varchar(10)=char(10)
3、不支持BLOG和TEXT等大字段
4、Memory存储引擎使用表级锁
5、最大大小由max_heap_table_size参数决定适用场景:
1、用于查找或者是映射表,例如邮编和地区的对应表
2、用于保存数据分心中产生的中间表
3、用于缓存周期性聚合数据的结果表
MySQL常用存储引擎之Federated
特点:
1、提供了访问远程MySQL服务器上表的方法
2、本地不存储数据,数据全部放到远程服务器上
3、本地需要保存表结构和远程服务器的连接信息
如何使用
默认静止,启用需要在启动时增加federated参数
mysql://user_name[:password]@host_name[:port]/db_name/table_name
适用场景:
偶尔的统计分析及手工查询
如何选择正确的存储引擎
参考条件
1、是否要支持事务
2、定期备份
3、崩溃恢复
4、存储引擎的特有特性
Mysql的服务器参数介绍
MySQL获取配置信息路径
1、命令行参数
mysqld_safe --datadir=/data/sql_data
2、配置文件
查看配置文件的命令:
[root@localhost ~]# mysqld --help --verbose | egrep -A 1 'Default options'
配置文件的有效路径
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
MySQL配置参数的作用域
1、全局参数
set global 参数名=参数值;
set @@global.参数名:=参数值;
2、会话参数
set [session] 参数名=参数值;
set @@session.参数名:=参数值;
内存配置相关参数
1、确定可以使用的内存的上限
2、确定MySQL的每个连接使用的内存
sort_buffer_size
join_buffer_size
read_buffer_size
read_rnd_buffer_size
3、确定需要为操作系统保留多少内存
4、如何为缓存池分配内存
Innodb_buffer_pool_size
注:设置缓存池的大小的考量标准为:总内存-(每个编程所以需要的内存*连接数)-系统保留内存
key_buffer_size
select sum(index_length) from information_schema.tables where engines='myisam'
I/O相关配置参数
Innodo I/O相关配置
Innodb_log_file_size 单个事务日志的大小
Innodb_log_files_in_group 控制文件日子的个数
事务日志总大小 = Innodb_log_files_in_group * Innodb_log_file_size
Innodb_log_buffer_size = (32M or 128M)
Innodb_flush_log_at_trx_commint
0:每秒进行一次log写入cache,并flush log到磁盘
1[默认]:在每次事务提交执行log写入cache,并flush log到磁盘
2[建议]:每次事务提交,执行log数据写入到cache中,每秒执行一次flush log到磁盘
Innodb_flush_method=O_DIRECT
Innodb_file_per_table = 1
Innodb_doublewrite = 1
MyISAM I/O相关配置
delay_key_write
OFF:每次写操作后刷新键缓冲中的脏块到磁盘
ON:只对在键表时指定了delay_key_write选项的表使用延迟刷新
ALL:对所有的MyISAM表都使用延迟建写入
安全相关配置参数
expire_logs_days 指定自动清理binlog的天数
max_allowed_packet 控制MySQL可以连接的包大小,建议设置为32M,如果使用了主从复制,参数应该设置成一致的
skip_name_resolve 禁用DNS查找
sysdate_is_now 确保sysdate()返回确保性日期
read_only 禁止非super权限的用户写操作 注:建议在主从复制中的从库开启此功能。以确保不能修改从库中的操作,只能从主库同步过来
skip_slave_start 禁用Salve自动恢复(从库中的设置使用)
sql_mode 设置MySQL所使用的SQL模式 (谨慎操作,可能会造成MySQL无法执行)
① strict_trans_tables 给定的数据如果不能插入到数据库中,对事务引擎会终端操作,对非事务引擎是没有影响的
② no_engine_subitiution 在create table中指定engines的时候,如果引擎不可用,不会使用默认引擎建立表
③ no_zero_date 不能再表中插入0年0月0日的日期
④ no_zero_in_date 不接受一部分的为0的日期
⑤ noly_full_group_by
其他常用的配置参数
sync_binlog 控制MySQL如何向磁盘刷新binlog
tmp_table_size 和 max_heap_table_size 控制内存临时表大小(不宜设置的太大,以避免内存的溢出)
max_connections 控制允许的最大连接数(默认为100,有点小,根据自己的业务适当的调整大小)
什么影响了性能
数据库设计对性能的影响
1、过分的反范式化为表建立太多的列
2、过分的范式化造成太多的表关联(关联的表尽可能的控制在10个之内)
3、在OLTP环境中使用不前挡的分区表
4、使用外键保证数据的完整性
总结
性能优化的顺序
1、数据库结构设计和SQL语句
2、数据库存储引擎的选择参数配置
3、系统选择及优化
4、硬件升级
来源:http://www.cnblogs.com/demon89/p/mysql_engines.html
猜你喜欢
- 之前遇到过一类问题,要求快速做文件搜索,当时小编找了很多内容,但是没有发现实现方法,突然看到glob模块便豁然开朗了,该模块主要就是能够实现
- 先说需求: 1、django 自带了admin后管,如果我们需要使用,只需把我们定义的models注册即可;2、但如果只是简单注册,那显示的
- 本文先抓取网络上的表情图像,然后利用百度 AI 识别表情包上的说明文字,并利用表情文字重命名文件,这样当发表情包时,不需要逐个打开查找,直接
- 当使用MySQL做站点的时候,肯定会有不知道的错误发生,怎么记录呢?以下是具体解决方法:class.method //建立错误日志 func
- 在Python中,变量是没有类型的,这和以往看到的大部分编辑语言都不一样。在使用变量的时候,不需要提前声明,只需要给这个变量赋值即可。但是,
- 1. 概念1.1 基本概念时间,对于我们来说很重要,什么时候做什么?什么时候发生什么?没有时间的概念,生活就乱了。在日常的运维当中,我们更关
- 一、前言这篇文章主要是记录一下php生成静态页面的大致步骤,关于页面静态化,大家想必都听说过,但是真正用的时候反而不是很多。有些页面考虑到访
- 1.nginx使用哪种网络协议? nginx是应用层 我觉得从下往上的话 传输层用的是tcp/ip 应用层用的是http fastcgi负责
- 众所周知,在设计爬虫时,最麻烦的一步就是对网页元素进行分析,目前流行的网页元素获取的工具有BeautifulSoup,lxml等
- Session作用Session的根本作用就是在服务端存储用户和服务器会话的一些信息。典型的应用有:1、判断用户是否登录。2、购物车功能。s
- 开源监控系统 Prometheus 集成了跟踪多种类型的时间序列数据,但如果没有集成你想要的数据,那么很容易构建一个。一个经常使用的例子使用
- Jones向量假设光波沿z轴传播,那么其三个方向的电场分量可以表示为Jones矩阵能够保证二维列向量形状不变的运算有无穷多种,但最符合我们直
- 这篇文章主要介绍了python with (as)语句实例详解,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,
- 跑模型和测试一些批量操作时,常常需要一个或多个文件中的文件的命名格式具有一定的规律。有时候获取的数据又是从一些网站爬取下来的,数据名具有一定
- 变量不是盒子在示例所示的交互式控制台中,无法使用“变量是盒子”做解释。图说明了在 Python 中为什么不能使用盒子比喻,而便利贴则指出了变
- 想弄个截屏工具,整理一下学生错题什么的,原来用的方法是:先运行QQ,再监听键盘热键(“ctrl+alt+a”)。后来发现有些问题:需要先上Q
- 插入一条记录后,如何得到最新的自动增加ID?我们要用到SQL Server的@@IDENTITY。它能够记录下系统最近使用的一个IDENTI
- 配置日志在Django中,可以通过logging模块来记录日志。日志记录器是将日志消息传递给日志处理器的对象。当需要记录日志时,可以使用以下
- 触发器是一种特殊类型的存储过程,它不同于之前的我们介绍的存储过程。触发器主要是通过事件进行触发被自动调用执行的。而存储过程可以通过存储过程的
- 一、半同步简介MASTER节点在执行完客户端提交的事务后不是立刻返回结果给客户端,而是等待至少一个SLAVE节点接收并写到relay log