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

曾经终败给现在 2022-06-01 11:57 311阅读 0赞

### 1:服务器硬件 ###

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

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

内存:

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

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

> RAID0:简单的把磁盘串联起来，没有提供冗余和错误修复能力。  
> ![这里写图片描述][SouthEast]  
> RAID1: 磁盘镜像，把一个磁盘的数据镜像到另一个磁盘上，不影响性能的情况下最大限度的保证了数据的可靠性和可修复性,成本比较高，利用率为50%。  
> ![这里写图片描述][SouthEast 1]  
> RAID5：分布式奇偶校验磁盘阵列，把数据分散到多个磁盘上，一个磁盘数据失效可以从奇偶校验块中恢复数据,但是两块都失效，整个卷的数据都无法恢复 写入比较慢 随机读和顺序读比较快  
> ![这里写图片描述][SouthEast 2]  
> RAID10：分片镜像最好的选择  
> ![这里写图片描述][SouthEast 3]  
> 选择方式  
> ![这里写图片描述][SouthEast 4]

固态存储：

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

### 2:服务器系统 ###

linux centos操作系统，影响mysql性能的参数：  
内核相关参数（/etc/sysctl.conf）  
![这里写图片描述][SouthEast 5]

### 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，支持数据文件直接编辑。  
> 适用场景:适合作为数据交换的中间表  
> ![这里写图片描述][SouthEast 6]

Archive：

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

Memory:

> HEAP存储引擎，所以数据保存在内存中，重启mysql服务器数据就会丢失，所以要求数据可再生性。  
> 支持HASH索引 （做等值查询快） 和Btree索引（范围查询快）  
> 所有的字段都是固定长度  
> 不支持blog和textda字段类型  
> 使用表级锁  
> 最大大小由max\_heap\_table\_size决定  
> 容易混淆的概念:
> 
> > Memory存储引擎表，所有都可以使用的表  
> > 临时表:create temporary table 建立临时表，值提供给系统使用。  
> > ![这里写图片描述][SouthEast 7]
> 
> 使用场景：  
> 用于查找或者映射表，例如邮编和地区对应表  
> 保存数据分析中产生的中间表  
> 缓存周期性聚合数据的结果表

Federated:

> 默认是关闭的  
> 提供了访问远程mysql服务器上表的方法  
> 数据都存储在远程服务器上  
> 本地保存表的结构信息和远程数据库链接信息  
> 使用场景：
> 
> > 偶尔的统计分析和手工查询中

总结：

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

### 5:mysql服务器参数 ###

mysql服务器参数：  
获取配置参数的方法：命令行参数和配置文件  
![这里写图片描述][SouthEast 8]  
全局参数：

> 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 事务日志组的个数  
> ![这里写图片描述][SouthEast 9]  
> delay\_key\_write myisam存储引擎写入文件的延迟  
> ![这里写图片描述][SouthEast 10]

安全配置参数:

> expire\_logs\_day 指定清理binlog的天数  
> max\_allowed\_packet 控制mysql可以接收的包的大小  
> skip\_name\_resolve 禁用dns查找  
> read\_only 禁止没有超级权限的用户写权限 从库设置  
> skip\_slave\_start 禁用slave自动恢复 从库设置  
> sql\_mode 设置mysql使用的sql模式

其他参数:

> ![这里写图片描述][SouthEast 11]

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

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

### 7:总结 ###

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

[SouthEast]: /images/20220601/f76ff930d3614e449b77a64412603a21.png
[SouthEast 1]: /images/20220601/b2145b820fbb41f384ff6860dfa30c9b.png
[SouthEast 2]: /images/20220601/b7354bbbc07149f1ad63b07a81d102ab.png
[SouthEast 3]: /images/20220601/e8f03b34ec6e447bb2e1d79a1133217a.png
[SouthEast 4]: /images/20220601/e796c1dabed1484fb7cc6249868798b5.png
[SouthEast 5]: /images/20220601/9d4b034fbb9548049f8953d6ff96f13d.png
[SouthEast 6]: /images/20220601/cb005859cb504e43a747cac6746d61a5.png
[SouthEast 7]: /images/20220601/5c947519bcff47be8056765f7895480b.png
[SouthEast 8]: /images/20220601/930670dfb40c4dffbcf7f5b6f99a749a.png
[SouthEast 9]: /images/20220601/2c3e6e08d982428ca4f29d41deca9a2c.png
[SouthEast 10]: /images/20220601/196e5e3224ff428da41d129e37c3948e.png
[SouthEast 11]: /images/20220601/45219604388641c0b53e0960d1c55bac.png