欢迎光临
我们一直在努力

什么影响了 MySQL 性能?


1 影响性能的几个方面

  1. 服务器硬件。
  2. 服务器系统(系统参数优化)。
  3. 存储引擎
  4. MyISAM: 不支持事务,表级锁。
  5. InnoDB: 支持事务,支持行级锁,事务ACID。
  6. 数据库参数配置。
  7. 数据库结构设计和SQL语句。(重点优化)

2 MySQL体系结构

分三层:客户端->服务层->存储引擎

什么影响了 MySQL 性能?

 

  1. MySQL是插件式的存储引擎,其中存储引擎分很多种。只要实现符合mysql存储引擎的接口,可以开发自己的存储引擎!
  2. 所有跨存储引擎的功能都是在服务层实现的。
  3. MySQL的存储引擎是针对表的,不是针对库的。也就是说在一个数据库中可以使用不同的存储引擎。但是不建议这样做。

3 InnoDB存储引擎

MySQL5.5及之后版本默认的存储引擎:InnoDB。

3.1 InnoDB使用表空间进行数据存储。

show variables like 'innodb_file_per_table

如果innodb_file_per_table 为 ON 将建立独立的表空间,文件为tablename.ibd;

如果innodb_file_per_table 为 OFF 将数据存储到系统的共享表空间,文件为ibdataX(X为从1开始的整数);

.frm :是服务器层面产生的文件,类似服务器层的数据字典,记录表结构

3.2 (MySQL5.5默认)系统表空间与(`MySQL5.6`及以后默认)独立表空间

1.1 系统表空间无法简单的收缩文件大小,造成空间浪费,并会产生大量的磁盘碎片。

1.2 独立表空间可以通过optimeze table 收缩系统文件,不需要重启服务器也不会影响对表的正常访问。

2.1 如果对多个表进行刷新时,实际上是顺序进行的,会产生IO瓶颈。

2.2 独立表空间可以同时向多个文件刷新数据。

强烈建立对Innodb 使用独立表空间,优化什么的更方便,可控。

3.3 系统表空间的表转移到独立表空间中的方法

1、使用mysqldump 导出所有数据库数据(存储过程、触发器、计划任务一起都要导出 )可以在从服务器上操作。

2、停止MYsql 服务器,修改参数(my.cnf加入innodb_file_per_table),并删除Inoodb相关文件(可以重建Data目录)。

3、重启MYSQL,并重建Innodb系统表空间。

4、 重新导入数据。

或者 Alter table 同样可以的转移,但是无法回收系统表空间中占用的空间。

4 InnoDB存储引擎的特性

4.1 特性一:事务性存储引擎及两个特殊日志类型:Redo Log 和 Undo Log

  1. Innodb 是一种事务性存储引擎
  2. 完全支持事务的ACID特性。
  3. 支持事务所需要的两个特殊日志类型:Redo Log 和Undo Log

Redo Log:实现事务的持久性(已提交的事务)。

Undo Log:未提交的事务,独立于表空间,需要随机访问,可以存储在高性能io设备上。

Undo日志记录某数据被修改前的值,可以用来在事务失败时进行rollback;Redo日志记录某数据块被修改后的值,可以用来恢复未写入data file的已成功事务更新的数据。

4.2 特性二:支持行级锁

  1. InnoDB支持行级锁。
  2. 行级锁可以最大程度地支持并发。
  3. 行级锁是由存储引擎层实现的。

5 什么是锁

5.1 锁

什么影响了 MySQL 性能?

 

5.2 锁类型

什么影响了 MySQL 性能?

 

5.3 锁的粒度

MySQL的事务支持不是绑定在MySQL服务器本身而是与存储引擎相关

什么影响了 MySQL 性能?

 

table_name加表级锁命令:lock table table_name write; 写锁会阻塞其它用户对该表的‘读写’操作,直到写锁被释放:unlock tables;

  1. 锁的开销越大,粒度越小,并发度越高。
  2. 表级锁通常是在服务器层实现的。
  3. 行级锁是存储引擎层实现的。innodb的锁机制,服务器层是不知道的

5.4 阻塞和死锁

(1)阻塞是由于资源不足引起的排队等待现象。

(2)死锁是由于两个对象在拥有一份资源的情况下申请另一份资源,而另一份资源恰好又是这两对象正持有的,导致两对象无法完成操作,且所持资源无法释放。

6 如何选择正确的存储引擎

参考条件:

  1. 事务
  2. 备份(Innobd免费在线备份)
  3. 崩溃恢复
  4. 存储引擎的特有特性

总结:Innodb大法好。

注意:尽量别使用混合存储引擎,比如回滚会出问题在线热备问题。

7 配置参数

7.1 内存配置相关参数

确定可以使用的内存上限。

内存的使用上限不能超过物理内存,否则容易造成内存溢出;(对于32位操作系统,MySQL只能试用3G以下的内存。)

确定MySQL的每个连接单独使用的内存。

sort_buffer_size #定义了每个线程排序缓存区的大小,MySQL在有查询、需要做排序操作时才会为每个缓冲区分配内存(直接分配该参数的全部内存);
join_buffer_size #定义了每个线程所使用的连接缓冲区的大小,如果一个查询关联了多张表,MySQL会为每张表分配一个连接缓冲,导致一个查询产生了多个连接缓冲;
read_buffer_size #定义了当对一张MyISAM进行全表扫描时所分配读缓冲池大小,MySQL有查询需要时会为其分配内存,其必须是4k的倍数;
read_rnd_buffer_size #索引缓冲区大小,MySQL有查询需要时会为其分配内存,只会分配需要的大小。

注意:以上四个参数是为一个线程分配的,如果有100个连接,那么需要×100。

MySQL数据库实例:
①MySQL是单进程多线程(而oracle是多进程),也就是说MySQL实例在系统上表现就是一个服务进程,即进程;
②MySQL实例是线程和内存组成,实例才是真正用于操作数据库文件的;
一般情况下一个实例操作一个或多个数据库;集群情况下多个实例操作一个或多个数据库。

如何为缓存池分配内存:

Innodb_buffer_pool_size,定义了Innodb所使用缓存池的大小,对其性能十分重要,必须足够大,但是过大时,使得Innodb 关闭时候需要更多时间把脏页从缓冲池中刷新到磁盘中;

总内存-(每个线程所需要的内存*连接数)-系统保留内存

key_buffer_size,定义了MyISAM所使用的缓存池的大小,由于数据是依赖存储操作系统缓存的,所以要为操作系统预留更大的内存空间;

select sum(index_length) from information_schema.talbes where engine='myisam'

注意:即使开发使用的表全部是Innodb表,也要为MyISAM预留内存,因为MySQL系统使用的表仍然是MyISAM表。

max_connections 控制允许的最大连接数, 一般2000更大。

不要使用外键约束保证数据的完整性。

8 性能优化顺序

从上到下:

什么影响了 MySQL 性能?

 

 收藏 (0) 打赏

您可以选择一种方式赞助本站

支付宝扫一扫赞助

微信钱包扫描赞助

未经允许不得转载:英协网 » 什么影响了 MySQL 性能?

分享到: 生成海报
avatar

热门文章

  • 评论 抢沙发

    • QQ号
    • 昵称 (必填)
    • 邮箱 (必填)
    • 网址

    登录

    忘记密码 ?

    切换登录

    注册

    我们将发送一封验证邮件至你的邮箱, 请正确填写以完成账号注册和激活