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     查詢快取中塊的總數