MySQL技术之旅-白话点性能上的事

MySQL5.5是一个革命性的版本，其有许多特性使得MySQL这个以前被认为弱小的数据库变得强大起来，下面白话一些MySQL在5.5的变化，内容来自阅读书籍整理，绝非原创。

一、innodb_file_io_threads到innodb_file_io_threads、innodb_read_io_threads的变化


MySQL5.1.x版本中innodb_file_io_threads（后台处理数据页上读写IO的线程数量）参数默认值是4，并且在Linux操作系统上是不可以更改的，这大大的限制了MySQL对于Linux操作系统多核CPU的使用。所以到了MySQL5.5版本，使用两个新的参数取代了innodb_file_io_threads。这俩个参数是innodb_read_io_threads和innodb_write_io_threads。这样在Linux操作系统上，我们可以自己根据CPU核数和数据库服务器的读写比例来更改相应的参数值，让MySQL最大化的利用多核CPU的优势，提升性能。innodb_write_io_threads、innodb_read_io_threads参数都不支持动态修改，需要在数据库配置文件中修改对应的值，然后重启MySQL服务器，值的大小范围1~64.可以通过查看show global variables like '%innodb_%_io_threads';


二、脏页处理代码写死到innodb_io_capacity参数可调


在MySQL5.1版本中，最多只刷新100个脏页到磁盘、合并20个插入到缓冲，就算磁盘有能力处理更多的操作也不会处理，因为这是写死的MySQL源代码中的。显而易见，当更新的量比较大的时候，由于产生的脏页会很多，其刷新的效率会远远跟不上脏页产生的效率，导致性能急剧下降，影响前端响应。
在MySQL5.5版本以后，新增参数innodb_io_capacity，可以动态的调整刷新脏页的数量，一定程度的缓解了在大量更改时的性能限制，其默认值是200，单位是页。其参数值的设定要参考磁盘的IOPS（每秒的输入输出量），磁盘效率越好，IOPS值越大，innodb_io_capacity参数的值就可以设置的越大，提高脏页处理效率


三、增加了innodb_adaptive_flushing自适应刷新脏页
  在没有自适应刷新脏页这个功能之前，MySQL刷新脏页的规则有一下几种情况：
  1.当超过了innodb_max_dirty_pages_pct设定的值时。
  2.当重做日志ib_logfile文件写满。
  3.机器闲时。 当数据库服务器处于很频繁的时候，重做日志文件切换的十分频繁，只要写满了就会有大量的脏页刷新到磁盘，对系统的性能有一定的影响。为了避免这样瞬间大量的写入造成的磁盘IO压力过大，MySQL增加了innodb_adaptive_flushing自适应刷新来缓解这个情况，其算法基本是综合考虑重做日志的ib_logfile生成的速度和刷新频率来刷新脏页入磁盘，这样在ib_logfile没有别写满的时候，也会有部分脏页写入磁盘。这个参数在MySQL5.5后默认就是开启的，可以动态的更新。


四、让innodb_buffer_pool中的热点数据存在的更久，不要轻易被替换到磁盘
  对于MySQL的innodb存储引擎，innodb_buffer_pool应该是其最重要的功能，其缓存数据到内存来提高MySQL服务器读写性能，这个区域被分成两个部分，一个叫做sublist_of_new_blocks区域，另一个叫做sublist_of_old_blocks。当用户访问数据的时候，如果缓冲区里有相应的数据则直接返回，否则就要去磁盘获取数据到内存，放入sublist_of_old_blocks区域，如果内存不足会通过PRU算法来提出数据。这里存在一个问题就是如果做一次大表的全表查询，或者是使用mysqldump做数据导出，就会有数据占用sublist_of_new_blocks区域，把一些真正的热点数据挤出缓存到磁盘，热点数据查询频繁不就就会重新被加入到内存中，造成缓冲区的数据频繁与磁盘交互，磁盘IO频繁。
  MySQL5.5开始，参数innodb_old_blocks_pct参数控制进入缓冲区sub_of_old_blocks区域的数量。默认为37，占整个缓冲区大小的3/8。这个参数可以动态调整来避免热点数据被挤出缓存。这其中还有个参数不得不说一下，就是innodb_old_blocks_time，这个参数控制当访问sublist_of_old_blocks区域时，并不会马上就移动到sublist_of_new_blocks区域，而是会等待innodb_old_blocks_time微秒，然后在移动到sublist_of_new_blocks中，保证了数据不会被马上的提出，！此处还需要理解的不够要在研究一下！
  
五、加快innodb数据恢复所需要的时间
当我们排除了操作系统奔溃，硬件故障和掉电、断网等意外情况，影响MySQL可用性的时间因素，大部分就是在innodb自己恢复所需要的时间了。MySQL5.5通过在内存管理和算法上的改进，大大的缩短了innodb恢复所需要的时间，事务日志最大可以设置4G。很大程度降低了IP请求和刷新脏页的频率，从而提高了写入的性能。MySQL5.5的快速恢复无需任何的人为参与。




六、innodb支持多实例的buffer pool
  innodb buffer pool是MySQL innodb引擎用来缓存数据和索引的内存区域，innodb_buffer_pool_size参数用来设置其大小，值越大数据需要访问磁盘的IO的可能性就越小，产生磁盘IO也就越少。一般最大可以占用物理内存的80%。但不建议设置过大，因为物理内存的竞争会导致操作系统的内存调度。如果数据库大小小于buffer pool的大小，此时所有数据都可以被缓存在内存中，数据库性能会很好。
  缓冲区管理着free list用于初始化空闲内存页，flushlist用于控制内存的脏页刷新和LRU等操作，
  当缓冲区在几十G的情况下，当某个线程在更新缓冲池的时候，其他线程必须等待，造成瓶颈。
  MySQL5.5通过innodb_buffer_pool_instances参数来增加缓冲区的实例个数，并使用哈希函数将读取缓存的数据页随机分配到一个实例里去，这样每个缓冲池就可以单独维护一套自己的free list、flush list和LRU，会缓解上面的瓶颈。
  
  
七、自适应哈希索引
  如果一个表中的数据都在内存中，执行最快的方法是使用哈希索引。在innodb存储引擎中有一个自动机制，它监视索引的使用情况，如果查询会通过哈希索引变得更快，其会自动在内存中建立哈希索引，这个操作是引擎自动发现和完成的，无需维护人员参与，这也是innodb对查询做出的优化。哈希索引是根据已存在的B树索引建立的。其可以是部分的，可以根据经常被访问的索引页进行哈希索引的建立。哈希索引存在一个性能问题，就是在高并发的情况下，有可能会造成读写的栓竞争（RW-latch）,进而会阻塞进程，造成性能问题。可以通过show engine innodb status \G 来查看自适应哈希索引的使用情况。
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 46125245
OS WAIT ARRAY INFO: signal count 2754239353
Mutex spin waits 11335693421, rounds 7505836961, OS waits 6497843
RW-shared spins 1652033756, rounds 3781839468, OS waits 25077833
RW-excl spins 211344597, rounds 3467359150, OS waits 12821532
Spin rounds per wait: -4.84 mutex, 2.29 RW-shared, 16.41 RW-excl


其中如果发现waits值比较高，那么应该关闭该功能，从而提升系统的性能。
参数innodb_adaptive_hash_index，可动态调整。


八、innodb内存的分配
  硬件技术有其摩尔定律，先不论是不是会严格的按照这个定于跟新换代，但是这起码说明了硬件技术在当今社会的革新速度是非常快的，在innodb刚被开发的时候多核CPU并没有像今天那么普遍，也没有针对多核CPU的优化内存分配。所以当时处于优化性能的目的，innodb自己实现了一个自己的内存管理分配子系统，这个系统是通过单个互斥量来实现管理的，既然是互斥的就可能成为性能瓶颈。innodb也对操作系统的内存分配做了一次简单封装，但是这些管理的函数也是通过互斥量的机制来实现的，性能隐患还是存在。
  从MySQL5.5开始，用户可以自定义是使用innodb的内存管理还是使用当前操作系统新的更高效的内存分配机制，其由参数innodb_use_sys_malloc来控制，默认为1，使用操作系统内存分配。这里涉及一个Google开发的一个开源工具TCMalloc，其与标准的glibc库中的malloc相比在内存分配效率和速度上高的多，可以很大程度上提高MySQL服务器在高并发情况下的性能，降低系统负载。
  
九、innodb默认并发数的修改
  innodb使用操作系统线程来处理用户请求时，工作流程为：当引擎收到一个用户请求时，如果当前运行的线程数已经超过了innodb_thread_concurrency预先设置的并发线程数量，那么会等待innodb_thread_sleep_delay设定的秒数，然后再次尝试，如果请求别接受，则会获得一个innodb_concurrency_tickets默认500的通行证，次数用完之前，该线程重新请求时不需要进行前面的innodb_thread_concurrency检查。如果在尝试没有被接受，那么就放到队列中排队等待处理。从MySQL5.5开始，innodb_thread_concurrency的值默认被设置为0，表示不限制并发数，此时innodb_thread_sleep_delay参数无效，同时innodb_concurrency_tickets也没有意义，这样的默认值可以更好的发挥CPU多核处理的能力，提高并发量。innodb_thread_concurrency参数值可动态修改。


十、innodb的预读算法
  为了提高IO性能，innodb有两种预读的算法，一种叫做线性预读，一种叫做随机预读。
 线性预读：当顺序读取extent块的时候，读取的是innodb_read_ahead_threshold设置的page也数量。这会触发一个异步的读取请求，将下一个页提前读取到buffer pool中。
 随机预读：这个算法由于会造成不必要的复杂性，在MySQL5.5版本中已经被删除，其基本逻辑是如果在缓冲池中某个extent有若干个页，那么会触发一个异步读物请求，把剩余的全部页也读取池中，因为其复杂性会导致性能下降，在5.5被干掉了。怎么就导致性能下降了，我还没精力过，不过都去掉了，我相信一定是存在问题的，要不为毛去掉。
 innodb_read_ahead_threshold参数默认为56，可动态修改。
 
十一、实现异步IO
  
  在了解异步IO的实现之前，我们先来了解一下IO的两种机制同步IO和异步IO。
  同步IO：当线程启动了一个IO操作后会立即进入等待状态，直到IO操作完成才会继续执行下次IO。
  异步IO：IO请求被发送到内核后，线程会立即去处理其他请求，当IO请求处理完成后通知线程执行ok了，异步IO操作是在后台进行的，IO操作和应用程序可以同时运行，提高了系统的性能，数据库一般都是利用异步IO来提高性能，让多个IO操作可同时进行。
  MySQL5.5开始实现了在linux上的异步IO，利用libaio库，可以通过innodb_use_native_aio参数来选择是否启用异步IO，默认是启用的，这个参数不支持动态修改。


十二、组提交
  MySQL事务提交采用的是日志先行，也就是也写日志后刷入磁盘的方式。此时要是有多个用户同时提交事务，那么一个一个分别将事务刷入磁盘导致磁盘做多次的IO操作，从而降低IOPS吞吐率。MySQL5.5开始采用了组提交的方式将事务刷新到磁盘，也就是将本来一次一次做的事情现在组团一起做了，从而提高吞吐量。


十三、回滚段提升性能
 不同于之前版本，MySQL5.5可以最多支持高达128K的并发事务处理操作，创建回滚数据。这种改进减少了单个回滚段上的互斥争用，可以使用多个回滚段来提升性能和可扩展性。这个特性默认实现，没有参数控制。
 
十四、改善清楚程序的进度
 innodb中清除无用数据的操作，在之前的版本中都是由主线程来完成的，当回收无用数据时可能会对主线程造成一定的阻塞（大表的删除操作）。在MySQL5.5版本中得到了改善，这种操作应用独立的线程来完成，并支持更多的并发数，可以通过参数innodb_purge_threads配置参数来选择清楚操作是否选择单独的线程来完成，默认为0，不使用，1位使用。这个参数不支持动态修改。
 
十五、缓冲区改进
 当对一个表中的数据进行insert、update、delete操作的时候，对应的索引也需要进行相应的更改，主键的顺序的但是非聚集索引是分散的，这种更改会降低性能。MySQL5.1进行了优化，当一个表insert操作需要更新索引时，如果索引在缓冲池里，那么直接更新并以刷新脏数据的方式刷新到磁盘，如果索引数据不在缓冲池里，那么又插入缓存来缓存索引的变化直到这个索引页被读入缓存，执行缓存合并操作，并使用刷新脏数据的方式刷新到磁盘，这样来提高insert的性能。这个会占用数据页的可用缓存空间，如果数据页和索引的总大小小于可用缓存的大小，那么使用insert缓存没有任何的必要了，还占用部分缓冲区，可以关闭这个功能。
 在MySQL5.5版本中增加了对删除操作同样的扩展。可以使用innodb_change_buffering配置参数来控制删除缓冲和插入缓冲的功能，默认为all。
 
十七、可控制自旋锁spin lock的轮训间隔
 自旋锁是为了保护共享资源而提出的一种锁机制，其逻辑与互斥锁类似，无论是互斥锁还是字段所，在某个时刻只有一个持有者，不可能多个持有者公用。但是两者的调度机制不一样。互斥锁如果资源被占用了，那么资源的申请线程只能进入睡眠状态。但是自旋锁不会引起调用者的睡眠，其调用者发现申请的自旋锁已被其他的调用者占用，那么其会一直循环等待持有者释放自旋锁给自己，也就是原地打转等待。为了防止其循环过快，耗费CPU，MySQL5.5版本引入参数innodb_spin_wait_delay来控制轮训间隔，也就是控制每次轮训之间的时间间隔。其值默认是6，可动态修改。
 
十八、innodb支持创建压缩数据页
 从MySQL5.5开始支持innodb数据页的压缩，数据页的压缩使文件的体积变小，减少磁盘的IO，提高吞吐率。对读多写少的应用尤其适用，同样的内存可以存取更多的数据。必须采用独立表空间和Barracuda文件格式才能支持数据页的压缩。当用户获取数据时，压缩页如果在内存中那么就解压缩响应，如果压缩页不在内存中，那么就从磁盘读入内存解压，响应需求。为了减少磁盘IO和对数据页的压缩操作，在缓冲池里可能同时存在着压缩和未压缩的数据页，也可能只有压缩页。
 在以前的版本中一个页的大小是16KB，现在可以在建表时指定压缩页是1k,2k,4k,8k，如果设置的过小，会导致消耗更多的CPU来支持压缩和解压操作。压缩是一把双刃剑，有压缩必然就需要有解压的过程，同时在数据入库时也是需要做压缩的，对CPU的使用变高了。


十九、动态调整innodb更新元数据的统计功能
 参数innodb_stats_on_metadata用来控制更新information_schema.STATISTICS表信息的更新，当表很大切数量很多的时候耗费时间会比较长，同时经常不访问的数据也会被缓存会占用空间。MySQL5.5版本可以动态修改这个参数来控制信息的更新，默认是开启的。