常用工具

1、日志管理、慢日志

2、CloudDBA功能

3、數(shù)據(jù)管理DMS平臺

4、查看表空間大小

select table_name, round(DATA_LENGTH/1024/1024/1024, 2) as data, round(INDEX_LENGTH/1024/1024/1024, 2) as idx_data, table_rows from tables where table_schema = 'exe_sf_p' order by data desc;

常見問題

1、Table 'exe_sf_t/#sql-ib27027' already exists

14.21.3 Troubleshooting InnoDB Data Dictionary Operations

2、Lock wait timeout exceeded; try restarting transaction

SELECT * FROM information_schema.innodb_trx order by trx_started asc;

kill threadid刪除Lock住最久的那個線程

3、RDS MySQL CPU使用率高情況的原因和解決

4、語法效率問題宣導(dǎo)

參數(shù)化

查詢字段索引

JOIN、IN

歸檔、分表：

條件中盡量能夠過濾一些行將驅(qū)動表變得小一點(diǎn)，用小表去驅(qū)動大表

右表的條件列一定要加上索引（主鍵、唯一索引、前綴索引等），最好能夠使type達(dá)到range及以上（ref,eq_ref,const,system）

一般不要用left join

explain

5、MySql Host is blocked because of many connection errors

MySql Host is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts' 解決方法

6、主從同步延遲

show processlist

show slave status

重啟RDS從庫，避免RDS從庫同步假死

7、The table '/home/mysql/data3003/tmp/#sql_25c8_2' is full

修改RDS的tmp_table_size參數(shù)

8、RDS MySQL 空間問題的原因和解決

9、RDS MySQL 管理長時間運(yùn)行查詢

10、too many connections

show variables like '%max_connections%';

set global max_connections=1400;

11、The MySQL server is running with the --read-only option

RDS磁盤空間滿導(dǎo)致實(shí)例鎖定