Innodb存储引擎和MyISAM存储引擎最大区别主要有四点，第一点是缓存机制，第二点是事务支持，第三点是锁定实现，最后一点就是数据存储方式的差异。

一、Innodb缓存相关优化
1、innodb_buffer_pool_size的合理设置
Innodb存储引擎的缓存机制和MyISAM的最大区别就在于Innodb不仅仅缓存索引，同时还会缓存实际的数据。所以，完全相同的数据库，使用Innodb存储引擎可以使用更多的内存来缓存数据库相关的信息，当然前提是要有足够的物理内存。

innodb_buffer_pool_size参数用来设置Innodb最主要的Buffer(Innodb_Buffer_Pool)的大小，也就是缓存用户表及索引数据的最主要缓存空间，对Innodb整体性能影响也最大。无论是MySQL官方手册还是网络上很多人所分享的Innodb优化建议，都简单的建议将Innodb的Buffer Pool设置为整个系统物理内存的50%～80%之间。

2、如何分析MySQL内存使用
假设是一台单独给MySQL使用的主机，物理内存总大小为8G，MySQL最大连接数为500，同时还使用了MyISAM存储引擎，这时候我们的整体内存该如何分配呢？
内存分配为如下几大部分：
a）系统使用，假设预留800M；
b）线程独享，约2GB=500*(1MB+1MB+1MB+512KB+512KB)，组成大概如下：
sort_buffer_size：1MB
join_buffer_size：1MB
read_buffer_size：1MB
read_rnd_buffer_size：512KB
thread_statck：512KB
c）MyISAM Key Cache，假设大概为1.5GB；
d）Innodb Buffer Pool最大可用量：8GB-800MB-2GB-1.5GB=3.7GB；
假设这个时候我们还按照50%～80%的建议来设置，最小也是4GB，而通过上面的估算，最大可用值在3.7GB左右，那么很可能在系统负载很高当线程独享内存差不多出现极限情况的时候，系统很可能就会出现内存不足的问题了。而且上面还仅仅只是列出了一些使用内存较大的地方，如果进一步细化，很可能可用内存会更少。

正式环境上线之初，一般都会采取相对保守的参数配置策略。上线之后，再根据实际情况和收集到的各种性能数据进行针对性的调整。

3、分析Innodb的Buffer Pool使用情况

mysql> show status like 'Innodb_buffer_pool_%';
+---------------------------------------+--------------------------------------------------+
| Variable_name                         | Value                                            |
+---------------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status        | Dumping of buffer pool not started               |
| Innodb_buffer_pool_load_status        | Buffer pool(s) load completed at 211106  0:26:25 |
| Innodb_buffer_pool_resize_status      |                                                  |
| Innodb_buffer_pool_pages_data         | 237                                              |
| Innodb_buffer_pool_bytes_data         | 3883008                                          |
| Innodb_buffer_pool_pages_dirty        | 0                                                |
| Innodb_buffer_pool_bytes_dirty        | 0                                                |
| Innodb_buffer_pool_pages_flushed      | 36                                               |
| Innodb_buffer_pool_pages_free         | 65291                                            |
| Innodb_buffer_pool_pages_misc         | 0                                                |
| Innodb_buffer_pool_pages_total        | 65528                                            |
| Innodb_buffer_pool_read_ahead_rnd     | 0                                                |
| Innodb_buffer_pool_read_ahead         | 0                                                |
| Innodb_buffer_pool_read_ahead_evicted | 0                                                |
| Innodb_buffer_pool_read_requests      | 1122                                             |
| Innodb_buffer_pool_reads              | 204                                              |
| Innodb_buffer_pool_wait_free          | 0                                                |
| Innodb_buffer_pool_write_requests     | 325                                              |
+---------------------------------------+--------------------------------------------------+
18 rows in set (0.01 sec)

Innodb_buffer_pool_pages_total：总共有65528个pages
Innodb_buffer_pool_pages_free：free状态的pages有65291个
Innodb_buffer_pool_pages_data：有数据的pages有237个
Innodb_buffer_pool_read_requests：read请求1122次
Innodb_buffer_pool_reads：从物理磁盘读取请求数204次
缓存命中率：(1122 – 204) / 1122 * 100% = 81.81%

4、innodb_log_buffer_size参数的使用
这个参数就是用来设置Innodb的Log Buffer大小的，系统默认值为1MB。Log Buffer的主要作用就是缓冲Log数据，提高写Log的IO性能。一般来说，如果你的系统不是写负载非常高且以大事务居多的话，8MB以内的大小就完全足够了。

5、分析Innodb的Log的使用情况

mysql> show status like 'innodb_log%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| Innodb_log_waits          | 0     |
| Innodb_log_write_requests | 0     |
| Innodb_log_writes         | 2     |
+---------------------------+-------+
3 rows in set (0.00 sec)

通过这三个状态参数我们可以很清楚的看到Log Buffer的等待次数等性能状态。

转载请注明：我是IT » MySQL Innodb存储引擎优化