mysql优化之影响mysql性能因素二

1:服务器硬件

cpu资源和可用内存的大小，网络 ，i/o资源
cpu:

计算密集型应用：选择速度快 频率高的cpu
web型应用：系统并发量高，选择数量多的cpu

内存:

尽可能大 增加缓存 读取速度块 多次写入数据一次写入

磁盘:
RAID磁盘冗余队列 把小磁盘扩展成大磁盘并提供数据冗余来保证数据完整性的技术

RAID0:简单的把磁盘串联起来，没有提供冗余和错误修复能力。

RAID1: 磁盘镜像，把一个磁盘的数据镜像到另一个磁盘上，不影响性能的情况下最大限度的保证了数据的可靠性和可修复性,成本比较高，利用率为50%。

RAID5：分布式奇偶校验磁盘阵列，把数据分散到多个磁盘上，一个磁盘数据失效可以从奇偶校验块中恢复数据,但是两块都失效，整个卷的数据都无法恢复 写入比较慢 随机读和顺序读比较快

RAID10：分片镜像最好的选择

选择方式

固态存储：

SSDPCI-E SSD 有更好的随机读写性能，
解决大量单线程负载的I/O瓶颈，更好的支持并发，但是更容易损坏（每次写入前都擦除数据）

2:服务器系统

linux centos操作系统，影响mysql性能的参数：
内核相关参数（/etc/sysctl.conf）

3:数据库存储引擎的选择

myisam 把索引缓存到内存中，数据由操作系统进行缓存， 不支持事务，表级锁
innodb 把索引和数据都缓存到内存中，完美支持事务，行级锁

4:数据库参数配置

常用的存储引擎：
myisam：

表级锁 :数据修改把整个表加锁，表的数据读取时加共享锁，所以读写操作是互斥的，写并发性较低，读并发性能接受，因为共享锁不会阻塞共享锁。
表损坏修复:可以检查和修复因为意外被关闭的表，可能会导致数据丢失。
check table tablename 检查表是否损坏
repair table tablename 恢复表的数据 有可能导致数据丢失
支持全文索引 支持数据压缩（myisampack命令）,只能读操作，读取时不用单独解压。
限制：默认支持256TB
适用场景：非事务型应用 只读类应用 空间类应用 如GPS数据 可以用空间函数，进行运算

innodb：

5.5版本后的默认存储引擎， 支持事务，ACID 特性（原子性，一致性，隔离性，持久性），事务型存储引擎
行级锁
实现事务的acid特性，日志：
Redo log 存储已提交的事务 重构日志 实现事务的持久性
Undo log 存储未提交的事务 回滚日志
show engine innodb status 查看事务的具体输出

锁介绍：

主要作用是管理共享资源的并发访问
实现事务的隔离性
锁的类型：共享锁（读锁） 独占锁（写锁）
锁的粒度：被加锁的最小单位 行 页 表
阻塞：不同锁之间的兼容性,在有些时候一个事务的锁等待另一个事务释放 。
死锁：两个或两个以上的事务相互占用对方的等待的资源，数据库会自动发现，找出占用资源最少的事务提交处理。

csv：

数据以文本方式存储在文件中 ，格式csv格式进行数据存储
.csv 文件存储表内容
.scm文件存储表的元数据如表的状态和数据量
.frm文件存储表的结构信息
特点:不支持索引，不适合大表，每次查询都扫表，不支持null，支持数据文件直接编辑。
适用场景:适合作为数据交换的中间表

Archive：

以zlib对表的数据进行压缩，磁盘I/O更少，节约存储空间
arz为后缀的文件中
只支持insert和select操作
只支持在自增id列上增加索引
适用场景：

日志和数据采集类应用

Memory:

HEAP存储引擎，所以数据保存在内存中，重启mysql服务器数据就会丢失，所以要求数据可再生性。
支持HASH索引 （做等值查询快） 和Btree索引（范围查询快）
所有的字段都是固定长度
不支持blog和textda字段类型
使用表级锁
最大大小由max_heap_table_size决定
容易混淆的概念:

Memory存储引擎表，所有都可以使用的表
临时表:create temporary table 建立临时表，值提供给系统使用。

使用场景：
用于查找或者映射表，例如邮编和地区对应表
保存数据分析中产生的中间表
缓存周期性聚合数据的结果表

Federated:

默认是关闭的
提供了访问远程mysql服务器上表的方法
数据都存储在远程服务器上
本地保存表的结构信息和远程数据库链接信息
使用场景：

偶尔的统计分析和手工查询中

总结：

大部分都可以使用innodb存储引擎

5:mysql服务器参数

mysql服务器参数：
获取配置参数的方法：命令行参数和配置文件

全局参数：

set global 参数名=参数值

会话参数：

set [session] 参数名=参数值

内存参数：

确定可以使用的内存上限
确定mysql每个链接使用的内存
sort_buffer_size 排序缓存区大小 每次有排序时为每个缓存区分配此配置全部内存。
join_buffer_size 链接缓冲区大小 查询有关联表 就分配内存大小
read_buffer_size mysiam全局扫描时分配此内存
read_rnd_buffer_size 索引缓冲区的大小

I/O配置参数：

innodb_log_file_size 事务日志文件大小
innodb_log_files_in_group 事务日志组的个数

delay_key_write myisam存储引擎写入文件的延迟

安全配置参数:

expire_logs_day 指定清理binlog的天数
max_allowed_packet 控制mysql可以接收的包的大小
skip_name_resolve 禁用dns查找
read_only 禁止没有超级权限的用户写权限 从库设置
skip_slave_start 禁用slave自动恢复 从库设置
sql_mode 设置mysql使用的sql模式

其他参数:

6:数据库结构设计和sql优化

1.过分的反范式为表建立太多的列。
2.过分的范式化造成太多的表关联 控制在10个以内。
3.在OLPT环境中使用不恰当的分区表。
4.使用外键保证数据的完整性，效率特别低。

7:总结

优化顺序：
数据库结构设计和sql语句优化=>数据库存储引擎的选择和参数配置=>系统选择和优化=>硬件升级