阅读全文

⭐⭐⭐ Spring Boot 项目实战	⭐⭐⭐ Spring Cloud 项目实战
《Dubbo 实现原理与源码解析 —— 精品合集》	《Netty 实现原理与源码解析 —— 精品合集》
《Spring 实现原理与源码解析 —— 精品合集》	《MyBatis 实现原理与源码解析 —— 精品合集》
《Spring MVC 实现原理与源码解析 —— 精品合集》	《数据库实体设计合集》
《Spring Boot 实现原理与源码解析 —— 精品合集》	《Java 面试题 + Java 学习指南》

摘要: 原创出处 https://segmentfault.com/a/1190000013672421 「唐成勇」欢迎转载，保留摘要，谢谢！

• 1 影响性能的几个方面
• 2 MySQL体系结构
• 3 InnoDB存储引擎
  • 3.1 InnoDB使用表空间进行数据存储。
  • 3.2 (MySQL5.5默认)系统表空间与(MySQL5.6及以后默认)独立表空间
  • 3.3 系统表空间的表转移到独立表空间中的方法
• 4 InnoDB存储引擎的特性
  • 4.1 特性一：事务性存储引擎及两个特殊日志类型：Redo Log 和 Undo Log
  • 4.2 特性二：支持行级锁
• 5 什么是锁
  • 5.1 锁
  • 5.2 锁类型
  • 5.3 锁的粒度
  • 5.4 阻塞和死锁
• 6 如何选择正确的存储引擎
• 7 配置参数
  • 7.1 内存配置相关参数
• 8 性能优化顺序

---

🙂🙂🙂关注**微信公众号：【芋道源码】**有福利：

1. RocketMQ / MyCAT / Sharding-JDBC 所有源码分析文章列表
2. RocketMQ / MyCAT / Sharding-JDBC 中文注释源码 GitHub 地址
3. 您对于源码的疑问每条留言都将得到认真回复。甚至不知道如何读源码也可以请教噢。
4. 新的源码解析文章实时收到通知。每周更新一篇左右。
5. 认真的源码交流微信群。

---

1 影响性能的几个方面

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

2 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 锁

​

5.2 锁类型

​

5.3 锁的粒度

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 性能优化顺序

​ 从上到下：

​