MySQL的常见存储引擎介绍与参数设置调优

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、硬件升级

(0)

相关推荐

  • mysql数据存储过程参数实例详解

    MySQL 存储过程参数有三种类型:in.out.inout.它们各有什么作用和特点呢? 一.MySQL 存储过程参数(in) MySQL 存储过程 "in" 参数:跟 C 语言的函数参数的值传递类似, MySQL 存储过程内部可能会修改此参数,但对 in 类型参数的修改,对调用者(caller)来说是不可见的(not visible). drop procedure if exists pr_param_in; create procedure pr_param_in ( in id

  • MySQL中slave_exec_mode参数详解

    今天无意当中看到参数slave_exec_mode,从手册里的说明看出该参数和MySQL复制相关,是可以动态修改的变量,默认是STRICT模式(严格模式),可选值有IDEMPOTENT模式(幂等模式).设置成IDEMPOTENT模式可以让从库避免1032(从库上不存在的键)和1062(重复键,需要存在主键或则唯一键)的错误,该模式只有在ROW EVENT的binlog模式下生效,在STATEMENT EVENT的binlog模式下无效.IDEMPOTENT模式主要用于多主复制和NDB CLUST

  • MySQL中参数sql_safe_updates在生产环境的使用详解

    前言 在应用 BUG或者 DBA误操作的情况下,会发生对全表进行更新:update delete 的情况.MySQL提供 sql_safe_updates 来限制次操作. set sql_safe_updates = 1; 设置之后,会限制update delete 中不带 where 条件的SQL 执行,较严格.会对已有线上环境带来不利影响.对新系统.应用做严格审核,可以确保不会发生全表更新的问题. CREATE TABLE working.test01 (id INT NOT NULL AU

  • MYSQL配置参数优化详解

    MySQL参数优化对于不同的网站,及其在线量,访问量,帖子数量,网络情况,以及机器硬件配置都有关系,优化不可能一次性完成,需要不断的观察以及调试,才有可能得到最佳的效果. 1)连接请求的变量 1.max_connections MySQL的最大连接数,如果服务器的并发连接请求量较大,建议调高此值,以增加并行连接数量,当然这建立在机器能支撑的情况下,因为如果连接数越多,MySQL回味每个连接提供连接缓冲区,就会开销越多的内存,所以要适当调整该值,不能盲目提高设值. 数值过小经常会出现ERROR 1

  • MySQL性能全面优化方法参考,从CPU,文件系统选择到mysql.cnf参数优化

    本文整理了一些MySQL的通用优化方法,做个简单的总结分享,旨在帮助那些没有专职MySQL DBA的企业做好基本的优化工作,至于具体的SQL优化,大部分通过加适当的索引即可达到效果,更复杂的就需要具体分析了,可以参考本站的一些优化案例或者联系我们 1.硬件层相关优化 1.1.CPU相关 在服务器的BIOS设置中,可调整下面的几个配置,目的是发挥CPU最大性能,或者避免经典的NUMA问题: 1.选择Performance Per Watt Optimized(DAPC)模式,发挥CPU最大性能,跑

  • MySQL通过实例化对象参数查询实例讲解

    本篇文章给大家带来的内容是关于MySQL如何通过实例化对象参数查询数据 ?(源代码),有一定的参考价值,有需要的朋友可以参考一下,希望对你有所帮助. public static string QueryByEntity<T>(T t) where T : new() { string resultstr = string.Empty; MySqlDataReader reader = null; try { Type type = typeof(T); PropertyInfo[] prope

  • MySQL 5.6下table_open_cache参数优化合理配置详解

    1.简介 table_cache是一个非常重要的MySQL性能参数,它在5.1.3之后的版本中叫做table_open_cache.table_cache主要用于设置table高速缓存的数量.由于每个客户端连接都会至少访问一个表,因此此参数的值与max_connections有关. 例如,对于 1000 个并行运行的连接,应该让表的缓存至少有 1000 × N ,这里 N 是应用可以执行的查询的一个联接中表的最大数量.此外,还需要为临时表和文件保留一些额外的文件描述符. 2.缓存机制 当某一连接

  • MySQL的常见存储引擎介绍与参数设置调优

    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.只读类

  • mysql 体系结构和存储引擎介绍

    目录 1 前言 2 mysql 配置文件加载顺序 3 mysql 引擎介绍 3.1 InnoDB 引擎 3.2 MyISAM 引擎 3.3 NDB 引擎 3.4 Archive 引擎 3.5 Federated 引擎 3.6 Maria 引擎 3.7 其它引擎 4 总结 1 前言 mysql 是一个单进程多线程架构的可移植的数据库,mysql 数据库实例在系统上的表现就是一个进程,可以在所有的平台上运行. mysql 的整体架构图如下图所示:  2 mysql 配置文件加载顺序 mysql 启动

  • MySQL数据库存储引擎介绍及数据库的操作详解

    目录 MySQL存储引擎 InnoDB存储引擎 MyISAM储存引擎 Memory存储引擎 Archive存储引擎 数据库的相关操作 创建数据库 修改数据库 删除数据库 查看数据库列表 打开数据库 MySQL存储引擎 InnoDB存储引擎 InnoDB存储引擎是MySQL常见的的存储引擎, 它给MySQL的表提供了事务处理.回滚.崩溃修复和多版本并发控制等功能: 支持列值自动增长(列值不能为空且必须唯一): 支持外键. 缺点: 占用的空间相对较大 MyISAM储存引擎 MyISAM储存引擎支持3

  • MySQL 常见存储引擎的优劣

    查看所有存储引擎 我们可以通过 show engines 命令来看到我们的 mysql server 提供了哪些引擎: show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions

  • 详解MySql中InnoDB存储引擎中的各种锁

    目录 什么是锁 InnoDB存储引擎中的锁 锁的算法 行锁的3种算法 幻像问题 锁的问题 脏读 不可重复读 丢失更新 死锁 什么是锁 现实生活中的锁是为了保护你的私有物品,在数据库中锁是为了解决资源争抢的问题,锁是数据库系统区别于文件系统的一个关键特性.锁机制用于管理对共享资源的并发访. 数据库系统使用锁是为了支持对共享资源进行并发访问,提供数据的完整性和一致性 InnoDB存储引擎区别于MyISAM的两个重要特征就是:InnoDB存储引擎支持事务和行级别的锁,MyISAM只支持表级别的锁 In

  • MySql中的存储引擎和索引

    目录 一.MySql的逻辑结构 二.什么是存储引擎 MySQL支持的存储引擎 三.操作 四.数据库的索引 索引的分类 五.索引操作 一.MySql的逻辑结构 MySQL体系结构分为四层:分别是连接层.服务层.存储引擎层.系统文件层. 连接层又称为客户端连接器(Client Connectors):提供与MySQL服务器建立的支持.连接池:管理.缓冲用户的连接,线程处理等需要缓存的需求. 服务层是MySQL Server的核心:主要包含系统管理和控制工具.SQL接口.解析器.查询优化器.缓存. 存

  • Mysql更换MyISAM存储引擎为Innodb的操作记录总结

    一般情况下,mysql会默认提供多种存储引擎,可以通过下面的查看: 1)查看mysql是否安装了innodb插件. 通过下面的命令结果可知,已经安装了innodb插件. mysql> show plugins; +------------+--------+----------------+---------+---------+ | Name | Status | Type | Library | License | +------------+--------+---------------

  • MySQL修改默认存储引擎的实现方法

    mysql存储引擎: MySQL服务器采用了模块化风格,各部分之间保持相对独立,尤其体现在存储架构上.存储引擎负责管理数据存储,以及MySQL的索引管理.通过定义的API,MySQL服务器能够与存储引擎进行通信.目前使用最多的是MyISAM和InnoDB.InnoDB被Oracle收购后,MySQL自行开发的新存储引擎Falcon将在MySQL6.0版本引进. MyISAM引擎是一种非事务性的引擎,提供高速存储和检索,以及全文搜索能力,适合数据仓库等查询频繁的应用.MyISAM中,一个table

  • 详解mysql中的存储引擎

    mysql存储引擎概述 什么是存储引擎? MySQL中的数据用各种不同的技术存储在文件(或者内存)中.这些技术中的每一种技术都使用不同的存储机制.索引技巧.锁定水平并且最终提供广泛的不同的功能和能力.通过选择不同的技术,你能够获得额外的速度或者功能,从而改善你的应用的整体功能. 例如,如果你在研究大量的临时数据,你也许需要使用内存存储引擎.内存存储引擎能够在内存中存储所有的表格数据.又或者,你也许需要一个支持事务处理的数据库(以确保事务处理不成功时数据的回退能力). 这些不同的技术以及配套的相关

  • MySQL表类型 存储引擎 的选择

    目录 1.查看当前数据库支出的存储引擎 方法1: 方法2: 2.ENGINE={存储引起类型}  创建表的时候,设置存储引擎 3.alter able tablename engine={存储引起类型} 修改表为其他存储引擎 3.1 常用存储引擎的对比 3.2 常用存储引擎学习(MyISAM.InnoDB.MEMORY 和 MERGE) 1.查看当前数据库支出的存储引擎 方法1: mysql> show engines \G; *************************** 1. row

随机推荐