mysql优化总结

对于一个存储设计，必须考虑业务特点，收集的信息如下： 
1.数据的容量：1-3年内会大概多少条数据，每条数据大概多少字节； 

2.数据项：是否有大字段，那些字段的值是否经常被更新； 
3.数据查询SQL条件：哪些数据项的列名称经常出现在WHERE、GROUP BY、ORDER BY子句中等； 
4.数据更新类SQL条件：有多少列经常出现UPDATE或DELETE 的WHERE子句中； 
5.SQL量的统计比，如：SELECT：UPDATE+DELETE：INSERT=多少？ 
6.预计大表及相关联的SQL，每天总的执行量在何数量级？ 
7.表中的数据：更新为主的业务 还是 查询为主的业务 
8.打算采用什么数据库物理服务器，以及数据库服务器架构？ 
9.并发如何？ 
10.存储引擎选择InnoDB还是MyISAM？ 

优化建议:

1.若是指创建好的表，不能变动表结构的话，那建议InnoDB引擎，多利用点内存，减轻磁盘IO负载，因为IO往往是数据库服务器的瓶颈 
2.优化索引结构去解决性能问题，优先考虑修改类SQL语句，使他们更快些，不得已只靠索引组织结构的方式，当然此话前提是， 索引已经创建的非常好，若是读为主，可以考虑打开query_cache， 
以及调整一些参数值：sort_buffer_size,read_buffer_size,read_rnd_buffer_size,join_buffer_size  
3. 索引, 避免扫描,基于主键的查找,上亿数据也是很快的;
4. 反范式化设计,以空间换时间,避免join,有些join操作可以在用代码实现,没必要用数据库来实现; 

5.水平拆分(分库分表)

6.读写分离（Read/Write Splitting），基本的原理是让主数据库处理事务性查询，而从数据库处理SELECT查询。数据库复制被用来把事务性查询导致的变更同步到集群中的从数据库。 

7.垂直拆分 按列进行分割，即把一条记录分开多个地方保存，每个子表的行数相同。例如表T1，可以把id和name放到数据文件p1，把qty放到数据文件p2。  比如text字段字符数超过20000，必选进行分拆，到另外一个表，和原表主键单独组成一个表进行查询

8.Mysql配置推荐:

query_cache：不超过256MB，除非基本静态，InnoDB无效

binlog_cache_size：2MB~4MB，<32MB

table_cache：1024，具体需要根据实际环境调整

thread_cache：1024，<max_connectios

key_buffer_size：无MyISAM16MB，否则所有MYI大小之内尽可能大

innodb_db_buffer_pool_size：仅InnoDB，所有文件大小之内尽可能大

innodb_log_buff_size：4MB~8MB，<32MB

innodb_max_dirty_pages_pct：<1G/innodb_db_buffer_pool_size(G)*100

max_connections：1000~2000，<10000

max_connect_errors：>1000，尽量大一点吧

back_log：100，<OS网络层设置

skip-name-resolve：建议启用，确保授权都是用IP

interactive_timeout和wait_timeout：86400，24小时基本足矣

innodb_flush_method：O_DIRECT(Linux)

innodb_flush_log_at_trx_commit：2，特别重要的设置为1，不建议0

innodb_file_per_table：一般建议开启

sync_binlog：4~8，非常频繁的系统可适当增大，但不建议0 

9.优化建议:

MYISAM:

尽量索引，MyISAM只缓存索引不缓存数据

调整读写优先级，根据实际需求，调整读写优先级

延迟插入，使用insertdelay，减少和select竞争

数据顺序操作，让insert全部到尾部，减少和select竞争

分解大操作，将大操作分解成多步小操作，防止长时间锁定

降低并发数，表锁会导致竞争激烈，通过排队机制提高效率

充分利用QueryCache：对于静态数据，尽量使用QueryCache

InnoDB:

主键尽可能小：所有非主键索引都需要存储主键

索引整合，减少冗余索引，降低数据量

避免全表扫描，因为会导致表锁

尽量自己控制事务，关闭aotucommit

尽量缓存所有数据和索引

合理设置innodb_flush_log_at_trx_commit

充分利用索引避开表锁

避免主键更新 

10.数据库引擎对比:

MYISAM:

不需要事务支持

并发相对较低

数据修改相对较少

以读为主

数据一致性要求较低

InnoDB

需要事务支持

并发较大

数据变更比较频繁

数据一致性要求较高

硬件设备内存较大，远大于索引数据量 

11.避免DOUBLE，区分开TINYINT/INT/BIGINT

尽量避免TEXT，VARCHAR不要留过大缓冲

尽量TIMESTAMP，能用DATE不用DATETIME

拒绝LOB类型，可尝试ENUM&SET

纯拉丁字符能表示的内容，没必要选择latin1

数据类型可精确到字段，极端情况下单独设置

确定不需要多语言，就没必要UNICODE类型

将不常使用的字段以及大字段拆分到独立附属表中

被频繁引用且只能通过Join2张（或者更多）表的方式才能得到的独立小字段，建议冗余