解决思路:
- 避免一次处理太多数据 , 可以分批次处理;
- 移出不必要的SELECT操作 , 保证事务中只有必要的写操作 。
- 服务器硬件 。
- 服务器系统(系统参数优化) 。
- 存储引擎 。
- MyISAM: 不支持事务 , 表级锁 。
- InnoDB: 支持事务 , 支持行级锁 , 事务ACID 。
- 数据库参数配置 。
- 数据库结构设计和SQL语句 。(重点优化)
分三层:客户端->服务层->存储引擎
文章插图
- MySQL是插件式的存储引擎 , 其中存储引擎分很多种 。只要实现符合mysql存储引擎的接口 , 可以开发自己的存储引擎!
- 所有跨存储引擎的功能都是在服务层实现的 。
- MySQL的存储引擎是针对表的 , 不是针对库的 。也就是说在一个数据库中可以使用不同的存储引擎 。但是不建议这样做 。
MySQL5.5及之后版本默认的存储引擎:InnoDB 。
2.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 :是服务器层面产生的文件 , 类似服务器层的数据字典 , 记录表结构 。
2.3.2 (MySQL5.5默认)系统表空间与(MySQL5.6及以后默认)独立表空间
1.1 系统表空间无法简单的收缩文件大小 , 造成空间浪费 , 并会产生大量的磁盘碎片 。
1.2 独立表空间可以通过optimeze table 收缩系统文件 , 不需要重启服务器也不会影响对表的正常访问 。
2.1 如果对多个表进行刷新时 , 实际上是顺序进行的 , 会产生IO瓶颈 。
2.2 独立表空间可以同时向多个文件刷新数据 。
强烈建立对Innodb 使用独立表空间 , 优化什么的更方便 , 可控 。
2.3.3 系统表空间的表转移到独立表空间中的方法
- 使用mysqldump 导出所有数据库数据(存储过程、触发器、计划任务一起都要导出 )可以在从服务器上操作 。
- 停止MYsql 服务器 , 修改参数(my.cnf加入innodb_file_per_table) , 并删除Inoodb相关文件(可以重建Data目录) 。
- 重启MYSQL , 并重建Innodb系统表空间 。
- 重新导入数据 。
2.4 InnoDB存储引擎的特性
2.4.1 特性一:事务性存储引擎及两个特殊日志类型:Redo Log 和 Undo Log
- Innodb 是一种事务性存储引擎 。
- 完全支持事务的ACID特性 。
- 支持事务所需要的两个特殊日志类型:Redo Log 和Undo Log
Undo Log:未提交的事务 , 独立于表空间 , 需要随机访问 , 可以存储在高性能io设备上 。
Undo日志记录某数据被修改前的值 , 可以用来在事务失败时进行rollback;Redo日志记录某数据块被修改后的值 , 可以用来恢复未写入data file的已成功事务更新的数据 。2.4.2 特性二:支持行级锁
- InnoDB支持行级锁 。
- 行级锁可以最大程度地支持并发 。
- 行级锁是由存储引擎层实现的 。
2.5.1 锁
文章插图
2.5.2 锁类型
文章插图
【MySQL性能及架构设计:什么影响了数据库查询速度与MySQL性能】2.5.3 锁的粒度
MySQL的事务支持不是绑定在MySQL服务器本身 , 而是与存储引擎相关
文章插图
将table_name加表级锁命令:lock table table_name write; 写锁会阻塞其它用户对该表的‘读写’操作 , 直到写锁被释放:unlock tables;