MySQL的常见存储引擎介绍与参数设置调优
MySQL常用存储引擎之MyISAM 特性： 1、并发性与锁级别 2、表损坏修复 check table tablename repair table tablename 3、MyISAM表支持的索引类型 ①、全文索引 ②、前缀索引 4、MyISAM表支持数据压缩 m
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、硬件升级
