浅谈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的值比较大，可以稍微设置大一点此值。 

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; 当然这是程序控制，创建使用完后再删除，由程序控制了。 

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

2、写操作：

  写操作分为热门数据和普通数据，简而言之就是按照频繁程度进行划分的。然频繁修改的数据可以和非频繁的数据进行分开。

举个例子:

比如我网站每天PV 1000w,而在PV统计的表中，我每次访问就会插入一条数据，一天下来1000w，当然这还不可能是分摊在24小时，就按照10个小时的中高峰期来说，每个小时也是100w条数据，如果我的网站其他表中的跟新的数据每天2w条，相对1000w来说，就是太少了，但是这2w条数据有事比较重要的数据，如果是用户注册、客户购买商品下的订单，他可比记录PV信息更重要吧，这时候问题就出现了：我的热门数据究竟是什么？是PV统计还是订单、注册用户，不言而喻，当然其中一个还是重点数据了，所以为了不让记录PV的数据来影响更重要的数据的更新，我们可以把他分开，如果后面还有主从同步的话，分开后，同步的负载也会降低很多，这样就可以只同步2w条数据，而不用考虑那1000w条数据了，进而主数据库的负载也会降低。 

  现在是把热门数据和普通数据分开了，但是对于高并发的数据库服务器来说，如何抗并发，这到成了一个重要的问题，当然高配服务器，集群用上，包括Nosql也用上可一解决这样的问题，如果在设计的时候能使用队列机制，这样不就更好了嘛！（地铁上看到一句广告语：有序方能通畅！）

  当然你可能有更好的方法，可以共同交流。

3、底层磁盘规划：

RAID0:

连续以位或字节为单位分割数据，并行读/写于多个磁盘上，因此具有很高的数据传输率，但它没有数据冗余，因此并不能算是真正的RAID结构。RAID0只是单纯地提高性能，并没有为数据的可靠性提供保证，而且其中的一个磁盘失效将影响到所有数据。因此，RAID 0不能应用于数据安全性要求高的场合。 

RAID1：

它是通过磁盘数据镜像实现数据冗余，在成对的独立磁盘上产生互为备份的数据。当原始数据繁忙时，可直接从镜像拷贝中读取数据，因此RAID1： 

可以提高读取性能。RAID1是磁盘阵列中单位成本最高的，但提供了很高的数据安全性和可用性。当一个磁盘失效时，系统可以自动切换到镜像磁盘上读写，而不需要重组失效的数据。 

RAID0+1： 

也被称为RAID10标准，实际是将RAID0和RAID1标准结合的产物，在连续地以位或字节为单位分割数据并且并行读/写多个磁盘的同时，为每一块磁盘作磁盘镜像进行冗余。它的优点是同时拥有RAID 0的超凡速度和RAID 1的数据高可靠性，但是CPU占用率同样也更高，而且磁盘的利用率比较低。 

RAID5： 

不单独指定的奇偶盘，而是在所有磁盘上交叉地存取数据及奇偶校验信息。在RAID5上，读/写指针可同时对阵列设备进行操作，提供了更高的数据流量。RAID5更适合于小数据块和随机读写的数据。RAID3与RAID5相比，最主要的区别在于RAID3每进行一次数据传输就需涉及到所有的阵列盘；而对于RAID5来说，大部分数据传输只对一块磁盘操作，并可进行并行操作。在RAID5中有“写损失”，即每一次写操作将产生四个实际的读/写操作，其中两次读旧的数据及奇偶信息，两次写新的数据及奇偶信息。 

RAID6： 

RADI6技术是在RAID5基础上，为了进一步加强数据保护而设计的一种RAID方式，实际上是一种扩展RAID5等级。与RAID5的不同之处于除了每个硬盘上都有同级数据XOR校验区外，还有一个针对每个数据块的XOR校验区。当然，当前盘数据块的校验数据不可能存在当前盘而是交错存储的，具体形式见图。这样一来，等于每个数据块有了两个校验保护屏障（一个分层校验，一个是总体校验），因此RAID6的数据冗余性能相当好。但是，由于增加了一个校验，所以写入的效率较RAID5还差，而且控制系统的设计也更为复杂，第二块的校验区也减少了有效存储空间。 

  而对于热门数据可以使用RAID10，这样他的性能和安全性会提高很多；而普通数据可以采用RAID5，主要提供安全性，像临时表这样的使用RAID0，在性能上发挥巨大的优势。

以上均个人见解，如有疑问，可共同交流学习！

©著作权归作者所有：来自51CTO博客作者Ro_410的原创作品，如需转载，请注明出处，否则将追究法律责任

raidmysql优化mysql学习MySQL DBA