mysql数据库SQL优化3

SQL优化3
优化表结构类型
select * from tbl_name procedure analyze()
select * from tbl_name procedure analyze(16,256)
表的拆分

mysql> show global status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 45783 |
| Innodb_row_lock_time_avg      | 7     |
| Innodb_row_lock_time_max      | 11396 |
| Innodb_row_lock_waits         | 5900  |
+-------------------------------+-------+
select * from information_schema.innodb_locks;
select * from information_schema.innodb_lock_waits;

create table innodb_monitor(a int) engine=innodb;
show engine innodb status\G
drop table innodb_monitor;

MyISAM内存优化
1-((key_blocks_unused*key_cache_block_size)/key_buffer_size)
使用率在80%左右

set global hot_cache.key_buffer_size=128*1024
set global hot_cache.key_buffer_size=0
cache index sales,sales2 in hot_cache;

vim my.cnf
key_buffer_size=4G
hot_cache.key_buffer_size=2G
cold_cache.key_buffer_size=1G
init_file=/path/to/mysqld_init.sql
vim mysql_init.sql
cache index sales in hot_cache;
cache index sales2 in hot_cache;
load index into cache sales,sales2;

如果我们希望将大致30%的缓存用来cache最热的索引快，可以对key_cache_division_limit
set global key_cache_division_limit=70
set global hot_cache.key_cache_division_limit=70;


InnoDB内存优化
mysqladmin  -uroot -h 127.0.0.1 -P 3307 -p ext |grep -i innodb_buffer
计算innodb缓存命中率
(1-innodb_buffer_pool_reads/innodb_buffer_pool_read_request)/100
如果命中率太低，考虑增大innodb

show variables like 'innodb_old_blocks_pct';
如果non-youngs/s很低，youngs/s很高，可以考虑innodb_old_blocks_time增大(ms)

innodb_max_dirty_pages_pct,它控制缓存池中脏页的最大比例，75%
innodb_io_capacity代表磁盘系统IO能力，7200RPM磁盘---100，
innodb_adapive_flushing=true 如果脏页没有达到innodb_max_dirty_pages_pct时，也会刷新。
Innodb_buffer_pool_pages_free值增长过快，增大innodb_buffer_pool,减小innodb_max_dirty_pages_pct，提高innodb_io_capacity
show global variables like 'innodb_doublewrite%';
show global status like 'sort_merge_passes';

LOG
---
Log sequence number 175284360806  上次数据页修改还没有刷新到日志文件的lsn号
Log flushed up to   175284360806  上次成功操作，已经刷新到日志文件中的lsn号
Last checkpoint at  175284360806  上次检查点成功完成时的lsn号意味着恢复的起点
innodb_flush_log_at_trx_commit 0,1,2

mysql> pager grep -i "Log sequence number"
PAGER set to 'grep -i "Log sequence number"'
mysql> show engine innodb status\G select sleep(60); show engine innodb status\G
Log sequence number 16907904
1 row in set (0.18 sec)
1 row in set (1 min 0.00 sec)
Log sequence number 16907904
1 row in set (0.00 sec)
mysql> nopager
select round((16907904-16907904)/1024/1024) as MB;

select  @a1:=variable_value as a1 from information_schema.global_status where variable_name='innodb_os_log_written'
union all
select sleep(60)
union all
select  @a2:=variable_value as a2 from information_schema.global_status where variable_name='innodb_os_log_written';

mysql并发相关的参数
mysqladmin ext | grep -i Connection_errors_max_connections
mysqladmin var  | grep max_connections
windows
(open_tables*2+open connections)<2048
调整
back_log参数控制mysql监听tcp端口时设置的积压请求栈大小，5.6以后default50+

table_open_cache

thread_cache_size

innodb_lock_wait_timeout=50ms

查询缓存
have_query_cache 表明服务器在安装时是否已经配置了高速缓存
query_cache_size 表明缓存区大小，单位为MB
query_cache_type 变量值从0到2，含有分别为：
        0或off：关闭缓存
        1或on：打开缓存，使用sql_no_cache提示的select除外
        2或demand：只带有sql_cache的select语句提供高速缓存
Qcache_free_blocks    查询缓存空闲内存块的数目
Qcache_free_memory    查询缓存空闲内存数
Qcache_hits           缓存采样数数目
Qcache_inserts        被加入到缓存的查询数目
Qcache_lowmem_prunes  因缺少内存而从缓存中删除条目
Qcache_not_cached     没有被缓存的查询条目
Qcache_queries_in_cache 在缓存中已经注册的条目
Qcache_total_blocks     查询缓存中块的总数

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29620572/viewspace-1810849/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29620572/viewspace-1810849/