mysql学习笔记之优化篇(二)之参数-磁盘-应用调优

优化MySQL Server

通过调整mysql server的参数来优化mysql server的效率

查看mysql信息和状态

查看mysql server参数show variables;
查看运行状态 show status;

影响mysql性能的重要参数

适用于myisam
    key_buffer_size 设置索引块缓存的大小
        查看当前的：show variables like 'key_buffer_size';
    table_cache 表示数据库打开表的缓存数量。
        每一个连接来都会打开一个表缓存。
             show global status like 'open_tables';

适用于innodb
    innodb_buffer_pool_size
        定义了表数据和索引数据的最大内存缓冲区大小。

    innodb_flush_log_at_trx_commit
        控制缓冲区中的数据写入到日志文件以及日志文件数据刷新到磁盘的操作时机。         0 每秒一次，并且刷新到磁盘
        1 事务提交时，刷新到磁盘
        2 提交事务时，日志缓冲写到日志文件，但是不会刷新到磁盘。对日志文件到磁盘的刷新每秒一次

    innodb_addition_mem_pool_size
        用来存储数据库结构和其他内部数据结构的内存池的大小，默认是1M，不够会自动分配
    innodb_lock_wait_timeout
        自动监控行锁导致的死锁并进行相应的处理，表锁不自动处理，默认50秒

    innodb_support_xa 
        是否支持分布式事务

    innodb_log_buffer_size
        日志缓存的大小一般8-16M

    innodb_log_file_size
        日志组中每个日志文件的大小，默认5M， 负载越高，大点好    

磁盘I/O问题　

咱们介绍了优化sql，数据库对象优化，数据库参数优化，其实他们的目的就是优化和减少磁盘读写，因为磁盘速度实在是太慢了(相对cpu和内存)

选择合适的磁盘阵列 raid0/raid 1/raid 10/raid4/raid5

读写都很频繁，可靠性很高 raid10
读频繁，写相对较少，可靠性有一定要求 raid5
读写都很频繁，可靠性不高 raid0

使用软连接链接

将不同的数据库表索引指向不同的物理磁盘，从而达到分布磁盘io的目的

禁止更新文件的atime

用裸设备存放innodb的共享表空间

操作系统的磁盘io缓存对innodb无帮助，可能有反作用

应用优化　

使用数据库连接池，减少创建释放连接的消耗

减少对mysql的访问

避免对统一数据做重复检索
使用查询缓存
    show status 查询cache状态

mysql> show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 1048576 |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |

增加cache层

负载均衡

利用mysql的主从复制分流查询操作
采用分布式数据库架构

其他优化措施

 空闲时间删除myisam表，并修复空洞
 充分利用列有默认值
 表尽量不适用自增长字段