Mysql优化读书笔记

感谢Sky Jian的一系列文章，让在下受益匪浅。下面做一些简单的读书笔记

一）缓存参数优化

由于数据库是密集型的数据IO操作应用，而硬盘IO几乎是内存IO的三个时间数量级的消耗。所以性能优化第一点就是尽量使用内存IO，也就是磁盘IO转换到内存IO。

1）query_cache_size/query_cache_type

Query Cache主要缓存resultset，只针对select语句。实现方式就是把select语句用hash算法计算后，在已cache的数据中查找，省略后续IO操作，达到提高性能。

Query Cache缺点是，某表更新后，对应该表的select语句缓存，都会失效。所以数据更新频繁时，就少用query cache。

Query Cache的使用需要多个参数配合，其中最为关键的是 query_cache_size 和 query_cache_type ，前者设置用于缓存 ResultSet 的内存大小，后者设置在何场景下使用 Query Cache。在以往的经验来看，如果不是用来缓存基本不变的数据的MySQL数据库，query_cache_size 一般 256MB 是一个比较合适的大小。当然，这可以通过计算Query Cache的命中率（Qcache_hits/(Qcache_hits+Qcache_inserts)*100)）来进行调整。query_cache_type可以设置为0(OFF)，1(ON)或者2(DEMOND)，分别表示完全不使用query cache，除显式要求不使用query cache（使用sql_no_cache）之外的所有的select都使用query cache，只有显示要求才使用query cache（使用sql_cache）。

2）binlog_cache_size

Binlog Cache 用于在打开了二进制日志（binlog）记录功能的环境，是 MySQL 用来提高binlog的记录效率而设计的一个用于短时间内临时缓存binlog数据的内存区域。

无大事务，写入不频繁，2M~4M比较合适。也可以使用binlog_cache_use以及binlog_cache_disk_use来分析设置的binlog_cache_size是否足够，是否有大量的binlog_cache由于内存大小不够而使用临时文件（binlog_cache_disk_use）来缓存了。

3）key_buffer_size

key_buffer_size 参数用来设置用于缓存 MyISAM存储引擎中索引文件的内存区域大小。如果我们有足够的内存，这个缓存区域最好是能够存放下我们所有的 MyISAM 引擎表的所有索引，以尽可能提高性能。MyISAM 引擎的特性限制了他仅仅只会缓存索引块到内存中，而不会缓存表数据库块。所以，我们的 SQL 一定要尽可能让过滤条件都在索引中，以便让缓存帮助我们提高查询效率。

4）bulk_sinsert_buffer_size

和key_buffer_size一样，这个参数同样也仅作用于使用 MyISAM存储引擎，用来缓存批量插入数据的时候临时缓存写入数据。当我们使用如下几种数据写入语句的时候，会使用这个内存区域来缓存批量结构的数据以帮助批量写入数据文件。

5）innodb_buffer_pool_size（global）

使用InnoDB存储引擎的时候，innodb_buffer_pool_size 参数可能是影响我们性能的最为关键的一个参数了，他用来设置用于缓存 InnoDB 索引及数据块的内存区域大小，类似于 MyISAM 存储引擎的 key_buffer_size 参数，当然，可能更像是 Oracle 的 db_cache_size。当我们操作一个 InnoDB 表的时候，返回的所有数据或者去数据过程中用到的任何一个索引块，都会在这个内存区域中走一遭。

内存足够的时候，尽量把该参数设置大一点。尽量多的把InnoDB的数据放在该缓存中，直到我们可以通过 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 计算缓存命中率，并根据命中率来调整 innodb_buffer_pool_size 参数大小进行优化全部。

6）innodb_additional_mem_pool_size（global）

设置了InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小，所以当我们一个MySQL Instance中的数据库对象非常多的时候，是需要适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率的。当过小的时候，MySQL 会记录 Warning 信息到数据库的 error log 中，这时候你就知道该调整这个参数大小了。

7）innodb_log_buffer_size （global）

8）innodb_max_dirty_pages_pct （global）

相关参数的建议值：
query_cache_type : 如果全部使用innodb存储引擎，建议为0，如果使用MyISAM 存储引擎，建议为2，同时在SQL语句中显式控制是否是哟你gquery cache
query_cache_size: 根据 命中率（Qcache_hits/(Qcache_hits+Qcache_inserts)*100)）进行调整，一般不建议太大，256MB可能已经差不多了，大型的配置型静态数据可适当调大
binlog_cache_size: 一般环境2MB～4MB是一个合适的选择，事务较大且写入频繁的数据库环境可以适当调大，但不建议超过32MB
key_buffer_size: 如果不使用MyISAM存储引擎，16MB足以，用来缓存一些系统表信息等。如果使用 MyISAM存储引擎，在内存允许的情况下，尽可能将所有索引放入内存，简单来说就是“越大越好”
bulk_insert_buffer_size: 如果经常性的需要使用批量插入的特殊语句（上面有说明）来插入数据，可以适当调大该参数至16MB～32MB，不建议继续增大，一般8MB
innodb_buffer_pool_size: 如果不使用InnoDB存储引擎，可以不用调整这个参数，如果需要使用，在内存允许的情况下，尽可能将所有的InnoDB数据文件存放如内存中，同样将但来说也是“越大越好”
innodb_additional_mem_pool_size: 一般的数据库建议调整到8MB～16MB，如果表特别多，可以调整到32MB，可以根据error log中的信息判断是否需要增大
innodb_log_buffer_size: 默认是1MB，系的如频繁的系统可适当增大至4MB～8MB。当然如上面介绍所说，这个参数实际上还和另外的flush参数相关。一般来说不建议超过32MB
innodb_max_dirty_pages_pct: 根据以往的经验，重启恢复的数据如果要超过1GB的话，启动速度会比较慢，几乎难以接受，所以建议不大于 1GB/innodb_buffer_pool_size(GB)*100 这个值。当然，如果你能够忍受启动时间比较长，而且希望尽量减少内存至磁盘的flush，可以将这个值调整到90，但不建议超过90

二）表结构优化

1）数据类型选择：

数字类型优先选择顺序：tinyint->int->bigint，非负添加unsigned。尽量不适用double类型，可转化成整形存储。

字符类型优先选择顺序：char->varchar->text，尽量不适用text类型。

时间类型优先选择顺序：date->datetime，不建议使用timestamp，不直观，也不方便。

状态字段，使用enum。预先可定义数据，尽量尝试set类型。

LOB类型，尽量不存放LOB数据。

2）字符编码选择：

非必须使用UTF-8或者unicode的时候，尽量放弃使用。

只有数字内容的时候，选择latin1.没有存放多种语言的时候；

不需要UTF-8或者unicode的时候放弃使用；

可针对表的具体字段设置编码格式；

3）适当拆分：有大字段内容，且大部分访问不需要此字段的时候，就把该拆分出来，这样就增加数据块的内容（条数），增加了内存缓存命中率；

4）适当冗余：比如频繁join的两张表时，合并表有利于减少大量不必要的IO，空间换取时间；

5）尽量不使用NULL

三）索引优化

1）索引有哪些“副作用”？

图书的变更（增，删，改）都需要修订索引，索引存在额外的维护成本

查找翻阅索引系统需要消耗时间，索引存在额外的访问成本

这个索引系统需要一个地方来存放，索引存在额外的空间成本

2）索引是不是越多越好？

如果我们的这个图书馆只是一个进出中转站，里面的新书进来后很快就会转发去其他图书馆而从这个馆藏中“清除”，那我们的索引就只会不断的修改，而很少会被用来查找图书

所以，对于类似于这样的存在非常大更新量的数据，索引的维护成本会非常高，如果其检索需求很少，而且对检索效率并没有非常高的要求的时候，我们并不建议创建索引，或者是尽量减少索引。

如果我们的书籍量少到只有几本或者就只有一个书架，索引并不会带来什么作用，甚至可能还会浪费一些查找索引所花费的时间。
所以，对于数据量极小到通过索引检索还不如直接遍历来得快的数据，也并不适合使用索引。

如果我们的图书馆只有一个10平方的面积，现在连放书架都已经非常拥挤，而且馆藏还在不断增加，我们还能考虑创建索引吗？
所以，当我们连存储基础数据的空间都捉襟见肘的时候，我们也应该尽量减少低效或者是去除索引。

3）索引该如何设计才高效？

尽量让查找条件尽可能多的在索引中，尽可能通过索引完成所有过滤，回表只是取出额外的数据字段。

字段的顺序对组合索引效率有至关重要的作用，过滤效果越好的字段需要更靠前。

当我们需要读取的数据量占整个数据量的比例较大抑或者说索引的过滤效果并不是太好的时候，使用索引并不一定优于全表扫描。

在实际使用过程中，一次数据访问一般只能利用到1个索引，这一点在索引创建过程中一定要注意，不是说一条SQL语句中Where子句里面每个条件都有索引能对应上就可以了。

四）sql优化

1）优化目标：减少IO次数(IO永远是数据库瓶颈，大部分数据库IO操作占90%的时间)；降低CPU计算（除了IO，一个重要消耗就是CPU计算了，特别是orderby，groupby，distinct等，由于需要比较运算，会大量消耗CPU）

2）常见误区：

count(1)和count(primary_key) 优于 count(*)；

count(column) 和 count(*) 是一样的；

select a,b from … 比 select a,b,c from … 可以让数据库访问更少的数据量；

order by 一定需要排序操作；

执行计划中有 filesort 就会进行磁盘文件排序；

3）基本原则

尽量少用join；尽量少排序；尽量避免select *；尽量join代替子查询；尽量少用or；尽量用union all代替union；尽量早过滤；避免类型转换

五）存储引擎选择

1）MyISAM

不支持事务；表级锁定；读写互阻塞；只会缓存索引；

不需要事务的，并发低，修改少，读为主，一致性不高的优先选择；

2）InnoDB

较好支持事务（4个事务隔离级别）；行级锁定（索引实现，全表扫描，也是表级锁定）；读写阻塞与事务级别有关系；高效缓存（索引与数据）；表与主键以Cluster方式存储，组成平衡树；二级索引都会保存主键信息；

3）DNBCluster

分布式；事务支持；内存消耗大；可与mysqld不在一台服务器上；