图解MySQL学习及思考

图解MySQL学习及思考

本文旨在结合自己看书理解，并借此图进行说明，如有谬误，望大家指正，以共同探讨为目的，交流学习。

首先介绍一下架构图的由来:最近看关于mysql方面书籍的一点心得，把文字转化成图片而得，方便理解。

我主要从读、写、底层磁盘三方面进行阐述：

1、读操作：

我们知道数据在读取的时候，需要从磁盘读到内存中，然后再做相应的操作，而在优化读操作的时候，主要想buffer，cache这些进行优化：

key_buffer_size 
这个对MyIsam表来说是一个比较重要的参数，一般可以把他设置成内存的30%-40%，当然这还要根据具体情况，MyISAM表会使用操作系统的缓存来缓存数据，因此需要留出部分内存给它们，很多情况下数据比索引大多了。
 
innodb_buffer_pool_size 
这个对InnoDB来说是一个比较重要的参数，而InnoDB对缓冲更为敏感，MyISAM可以在默认的 key_buffer_size 设置下运行的可以，然而Innodb在默认的 innodb_buffer_pool_size 设置下却跟蜗牛似的。由于Innodb把数据和索引都缓存起来，无需留给操作系统太多的内存，因此如果只需要用Innodb的话则可以设置它高达 70-80% 的可用内存。一些应用于 key_buffer 的规则有 — 如果你的数据量不大，并且不会暴增，那么无需把 innodb_buffer_pool_size 设置的太大了。
 
table_cache 
表的缓存，这个占用系统的资源和内存，因为每一个现成都需要打开一个临时表，所以当连接数大的时候可以加大此值。
 
thread-cache 
线程的缓存，线程的创建和销毁开销可能会很大，所以每个线程的连接和断开需要，如果程序中活跃的并发连接数和Thread-Created的值比较大，可以稍微设置大一点此值。 【帮客之家 http://www.bkjia.com 】
 
query-cache 
如果应用程序中有大量的读，可以设置大一点此值，但是也不要太大，因为维护它也需要不少的开销。一般设置32M-512M即可。
 
sort_buffer_size 
这个是connection级的参数，在每个connection第一次需要使用这个buffer的时候，一次性分配设置的内存，此值不是越大越好，如果设置过大，碰上高并发的情况下就会使性能降低，sort_buffer_size 超过2KB的时候，就会使用mmap()而不是 malloc() 来进行内存分配，导致效率降低。 
 
mysql临时表 当工作在非常大的表上时，你可能偶尔需要运行很多查询获得一个大量数据的小的子集，不是对整个表运行这些查询，而是让MySQL每次找出所需的少数记录，将记录选择到一个临时表可能更快些，然后多这些表运行查询。 mysql服务器会自动创建内部临时表：该临时表可以是只存在于内存的memory临时表，或者是存储于硬盘的myisam临时表；而且初始创建的memory临时表由于表的增大可能会转变为myisam临时表——其转化临界点由max_heap_table_size和tmp_table_size系统变量的较小值决定的！注意：max_heap_table_size系统变量应用于所有的memory引擎的表，不管是用户临时表、正常表、或者内部临时表。当然程序也可以创建临时表：create temporary table XX; 当然这是程序控制，创建使用完后再删除，由程序控制了。

以上是读操作的一些介绍，接下来是写操作方面的。