mysql数据库sql的优化_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/，如需转载，请注明出处，否则将追究法律责任。