分类： Mysql/postgreSQL
2014-05-14 15:09:07
除了设置my.cnf里面林林总总的参数（看官方手册）外，个人感觉还有以下：
MySQL 体系结构分层： 各个索引都有的是connect layer等等来做连接身份验证，SQL语法、语义分析，查询优化器，连接池管理等等，而存储引擎则是数据的管理和实现，比如锁，索引，事务,内存分配管理等，这个是我个人浅显的了解。所以可以根据自己的需要来实现存储引擎，最底层就是数据、数据库自己的文件存放在操作系统和磁盘上。
频繁做query的必须要有正确的索引，否则full scan会大量耗费时间，index就是存储引擎自己维护的数据结构，一般是B树，index在delete/update/insert的时候也会rebuild，此时就类似B树的相关操作，感兴趣可以去看下《数据结构》的东西。所以插入大量数据可以先删除index然后re-create,假如业务允许的话。
必须要正确的建Index，比如table (id,name)
select * from table where name=x; # 此时id字段是index的话，此SQL statement 依然会fulll scan,因为name上没有Index，或者模糊查询select * from table where id like '%x‘；即使ID有Index也毫无用处，因为id是模糊匹配的。所以要善用explain来看，Type（ALL）表明是full scan,无索引或者不正确的字段上
innodb_buffer_pool_size 缓存innodb的索引，还存储了数据、自适应哈希索引（adaptive hash index）、插入缓存、锁、以及其他的一些内部结构，innodb用这个延缓write，所以可以将多个write合并，然后顺序的写到buffer_pool里面，然后写redo log(innodb_log_file_size)。《high performance MySQL》建议设为内存的75%左右。
key_buffer_size(只适用myisam,而且只cache索引，MYI文件的大小就是理想的key_buffer内存大小，但这个是变化的，所以不太可能。多说一句，由于myisam的索引有cache,所以写的时候有延迟操作，这样也为了减少硬盘读写次数提高效率，但问题就是一旦当机，此时Index可能没build完毕，所以需要频繁的repair。而innodb会writing ahead logging，先写事务日志到磁盘然后才会更新数据和index，所以没这个问题。此外建议innodb设小点）
query_cache_size 大量的读一定要有，但问题就是，针对table的，假如该table某个记录更新，则其他查询该表的cache全部失效，所以频繁写的，这个意义不大。
binlog_cache_size(未提交的binlog缓冲大小，比如批量load,这个应该是不够的，此时会inlog_cache_disk_use使用磁盘，committed的时候才会写入logfile里，所以一般设为16-32M就可以了，此
外注意这个是针对session分配的）
binlog_format=mixed（MySQL 5.1后支持，有statement/row2个，假如是statement,那么遇到date(),rand()这种在slave肯定会出现数据的inconsistent,建议用mixed让它自己选择，P.S row的时候binlog占用空间比较大，因为它记录了数据的变化，但数据复制的可靠性提高了，由于binlog变大，M/S复制的网络开销也变大了，可能IO也会有影响）
sync_bin_log=1, 立即写binlog到磁盘，不用操作系统自身带的磁盘缓冲，可靠性高，可能会影响I/O。
以下来自《MySQL核心内幕》
InnoDB内存分innodb_buffer_pool_size/innodb_log_buffer_size和addtional_memory_size
innodb把数据库文件按照Page（16K)读入到pool然后按照LRU算法来淘汰数据，假如文件需要修改，先修改pool的页（dirty page)，然后按照频率flush dirty page到文件。
缓冲的有索引页，数据页，undo,插入缓冲，自适应hash索引，innodb存的lock，数据字典。
innodb_log_file_size # 也就是logfile1/2这个文件
要设一个合适的值，太小的话否则导致事务日志频繁切换比如Logfile1写满了然后写logfile2,然后可能又切回logfile1，这个切换过程中会flush缓冲池的dirty page到磁盘，因为logfile有个cpacity 变量，表示最后的lsn不能超过这个。太大的话MySQL恢复比较费时间。
innodb_log_buffer_size ，
InnoDB 在写事务日志的时候，为了提高性能，也是先将信息写入Buffer 中，当满足 innodb_flush_log_trx_commit 参数所设置的相应条件（或者日志缓冲区写满）之后，才会将日志写到logfile1/2中。不需要太大，不小于每秒的transaction大小就可以，因为这个很快被flush了。
innodb_flush_log_at_trx_commit 自己权衡性能和安全吧
还有个 innodb_max_dirty_pages_pct 这个默认是90%，也就是最大dirty page百分比，大于这个的时候会flush数据到磁盘，innodb每秒会flush 这个缓冲池，只有大于90%才会flush,建议设为75%.
存储引擎文件：表空间ibdata1,存的是该表的数据，index,插入缓冲，logfile1...这个存的就是redo log了。
1. data storage engine的选择，innodb VS myisam,确定应用一定要用innodb吗？假如不考虑transaction,外键以及数据靠性要求不高，而query语句很多，建议用myisam，甚至infobright。数据备份也很容易，但要频繁的repair table。此外table lock不一定比row lock慢。isam适合查询非常多，写少，若是频繁写，isam的读的table会被lock直到update完毕（table lock VS row lock）。
2. 由于myisam是由OS的flush机制来保证数据写到database,所以load数据非常之快，而innodb则不然，具体可以看我另外的博文分析。而且迁移非常容易直接拷贝frm/MYD/MYI 这3个文件即可，但由于innodb这些存在数据字典里面（表空间ibdata1)，只能dump表结构和data,耗时比较多。
3. load大量数据的时候set foreign_key_checks=0,时候再打开，可以快很多。此外load比insert快，原因是见之前的博文分析。此外删除表结构的index,然后create也可以（业务允许的情况下）,还有set autocommit=0结束后设为1. 此外合理的filed做index非常的重要，explain你的sql或者打开my.cnf的一个配置 --log-queries-not-using-indexes ，别忘了打开slow query来分析看下表结构以及index是不是最优的设置等。
4. 使用SSD来优化mysql的I／O，性能提升明显，最好再做RAID，把热点数据放SSD上，切记RAID 卡要设为write back而且BBU（backup battery unit)要有电，否则充电过程中会变为write though,磁盘I/O性能非常的差，我遇到过这种情况。
5. 关于scale out,
水平和垂直sharding
最常见的就是把根本没任何关联的Database放到不同的server上，每个slave只replicate自己需要的database, 这样也更加灵活，甚至可以拆分某几个字段，坏处就是需要更多的resource。这也应该是垂直sharding的最简单的一种情况。复杂的是把db里的table按照逻辑关系放到某个server上。
水平切分： 比如某个表非常大，访问频繁，则按照id分组，一组用户相关的表和数据放到一组数据库上。这样可以减少单个服务器的IO race。
这个我也有接触过，infobright做Query的时候也用过。当然还需要个table来维护user id/sharding id的关系，假如sharding很多，维护关系的table查询频繁，还需要用memcache之类的放在应用层之后。
sharding 缺点: 复杂的join（找到对应的sharding id)和order by(聚合函数等可能需要在应用端来实现。
6. 此外反范式化设计表结构，虽然造成了数据冗余，但性能提高不少。
e.g 从《构建高性能的web站点》来的一个例子
SNS的网站很多情况下都有个人和好友关系，一般都有这种infomation和profile表，大致结构如下
1）information:
（ID，friend_ID）
2）profile
（ID,nickname,birthday,gender,email）# and so forth
从这里看这2个表是comply with NF的，第一无数据冗余，第二不会出现insert/delete/update异常
但是要找到个人好友的nickname,需要一个复杂的join查询，结果就是为了得到一个nickname。
此时可以设计information为
(ID,friend_id,friend_nickname)
此时假如ID是index很快就能查到nickname,但问题就是这个明显不符合NF的，因为数据冗余，此外假如profile的nickname更新了，information的nickname会不一致，此时需要从应用的角度来做，比如限制改nickname,或者延迟加载，此外用户可能不会在意这种细微的变化，比如QQ的昵称变了，估计你的好友没几个人能立即注意到，这样用其他办法比如更改profile的nickname字段等都可以实现延迟加载。
