MYISAM与INNODB两种存储引擎最大区别分四点：
1.缓存机制
2.事务支持
3.锁定实现
4.数据存储方式的差异
问题一：谈一下缓存优化技术
1.Innodb的缓存不仅仅可以缓存索引而且还可以缓存起来数据，所以对于同样的DB用它可以用更多内存来缓存数据的。
说一下影响缓存最主要的一个参数值：innodb_buffer_pool_size
到底设置多少比较理想？
实际的一个案例来分析
假设一台MYSQL主机物理内存8G，最大连接数500，同时还使用了MYISAM引擎。这时要如何设置这个值？
计算机内存分配为如下几个部分：
1、系统使用。假设我们设置800MB
2、线程独享，最大约为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
3、MYISAM KEY CACHE：假设大概为1.5G
4、现在来计算一下Innodb_buffer_pool_size = 8GB - 800MB - 2GB - 1.5G = 3.7GB
建议值：MYSQL的一些参数值的设置一定不要相当然。要保守配置上线然后再细调优！
一旦上线我们就可以使用这个方法来跟踪一下这个值是否设置适当：
show status like 'Innodb_buffer_pool_%';
缓存计算率为：
=(Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads)/Innodb_buffer_pool_read_requests
说明：其中Innodb_buffer_pool_reads表示的是从磁盘读取数据！
说明buffer pool使用大小计算公式为： = Innodb_buffer_pool_pages_data/Innodb_buffer_pool_total
这个值越大说明利用率就越高。如果这个值偏低说明我们的这个参数设置有些偏高了
解释一下什么叫预读？
在一些高端的存储设备上面就有这个功能。什么意思呢？就是分析数据请求的特点来自动判断客户在请求当前的数据块
之后可能会去继续请求的数据块，通过这种自动判断，存储引擎会一次性把当前请求的数据库和后面可能请求的下一个
数据库全部结读出来了。这样的话就不需再去做一次IO了。速度会有所提升的！
参数二：innodb_log_buffer_size
设置日志的BUFFER大小/默认为1MB.它的主要作用就是缓冲LOG数据，提高LOG的IO性能。
如果我们的应用主要是写的话要注意这个参数要适当偏高的！
对于写负载不是很高而且事务偏小的话在8M内就行了！
介绍一下Double Write Buffer
这是INNODB独有的文件FLUSH实现技术。主要作用是在减少文件同步次数提高IO性能的情况下，提高系统CRASH或断电情况
下数据的安全性，避免写入的数据不完整。
介绍一下：
INNODB在将数据同步到数据文件进行持久化之前，首先会将同步的内容写入存在于表空间中的系统保留的存储空间里面
即Double Write Buffer这里面。然后再将数据进行文件同步。所以实质上Double Write Buffer中存放的是一份需要同步
到文件中数据的备份。
问题二：谈一下事务优化技术
2.1 选择合适的事务隔离级别
1. READ UNCOMMITTED
也叫脏读：事务上面的最低隔离级别了。什么意思呢？
当我们执行SELECT的时候可能看到的数据可能并不是查询发起时间点的数据了这就引起了脏读！
2. READ COMMITTED
语句级别的隔离。在这种级别下面有可能会出现所谓的不可重复读即幻读。
3. REPEATABLE READ
默认的级别了。这种隔离级别下可避免上面的两种读。但有可能出现Phantom Reads的可能
4. SERIALIZABLE
最高级别的事务隔离！
事务与IO的关系及优化
当应用修改数据的时候是如何处理的？
INNODB在修改数据的时候只是修改Buffer Pool中的数据，并不是在一个事务提交的时候就将BufferPool中被修改的数据同步到磁盘
它将修改信息记录到相应的事务日志中去。看到没有为啥INNODB里面有这么多的bin二进制日志了吧！
事务日志主要有两个好处：
1、提高系统整体的IO性能
2、能够做灾难恢复
问题三：从数据存储角度来考虑如何优化
INNODB是将数据包括索引存放在了相同的文件中了。而MYISAM则是分开存放的哦！
另外INNODB表的话都是以主键以聚簇索引的形式创建。所有的数据都以主键升序排列在物理磁盘上面的。所以主键查询并且
以主键排序的查询效率是非常高的！
PS：以主键查询效率最高！尽可能不要更新它的主键值！
聚簇索引最大的一个毛病就是更新的索引数据会移动而且整个数据都会跟着移动的！
结论四点：
1、尽量减小secondary index的大小，提高访问效率。作为主键的字段所占用的存储空间越小越好。最好是INTEGER类型
当然字符串类型的字段也是可以当主键的了。
2、创建表的时候尽量是自己指定相应的主键值，让数据按照预设的顺序排列存放，这样可以提高特定条件下的访问效率。
3、尽可能不要在主键上面做更新操作，减少因为主键的移动带来数据的移动的。
4、尽可能提供主键查询。胜过索引列查询哦！
问题四：其它的优化方向
4.1 innodb_flush_method
结论：
如果磁盘是通过RAID卡做了硬件级别的RAID的话建议使用O_DIRECT，可以在一定程序上面提升IO性能的。
如果RAID CACHE不够必须要谨慎对待了。而且当存储环境是SAN环境的话使用它有可能降低性能。
4.2 innodb_thread_concurrency
当系统写IO很大的时候建议将它设置为0
4.3autocommit
将其设置为false
监控INNODB的性能状态的方法：show innodb status\G;
阅读(969) | 评论(0) | 转发(0) |
