什么影响了 MySQL 性能？

来源：http://t.cn/RnU0h2o

• 1 影响性能的几个方面
• 2 MySQL体系结构
• 3 InnoDB存储引擎
• 4 InnoDB存储引擎的特性
• 5 什么是锁
• 6 如何选择正确的存储引擎
• 7 配置参数
• 8 性能优化顺序

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

从上到下：