1. 慢查詢,規(guī)定查詢超過2秒的為慢查詢(救火)

1)mysql> show full processlist;

2)用explain查看查詢語句是否經(jīng)過索引，如果沒有就要建立索引

long_query_time =2

3)對(duì)需要建立索引的條件建立索引，大于300萬的記錄不能建立索引

2. 日常記錄文件

[root@MySQL?~]#?grep?gene?/data/3306/my.cnf

general_log?=?on

general_log_file?=?/data/3306/data/MySQL_oldboy.log

臨時(shí)生效：

mysql>?set?global?general_log_file?=?"/data/3306/data/MySQL_oldboy.log";

Query?OK,?0?rows?affected?(0.04?sec)

mysql>?show?variables?like?'general_log%';

+------------------+----------------------------------+

|?Variable_name????|?Value????????????????????????????|

+------------------+----------------------------------+

|?general_log??????|?ON???????????????????????????????|

|?general_log_file?|?/data/3306/data/MySQL_oldboy.log?|

+------------------+----------------------------------+

2?rows?in?set?(0.00?sec)

mysql>?set?global?general_log?=?on;

Query?OK,?0?rows?affected?(0.03?sec)

[root@oldboy?~]#?cat?/data/3306/data/MySQL_oldboy.log

/application/mysql-5.5.32/bin/mysqld,?Version:?5.5.32-log?(Source?distribution).?started?with:

Tcp?port:?3306??Unix?socket:?/data/3306/mysql.sock

Time?????????????????Id?Command????Argument

160828?19:09:22????15?Query?????show?databases

160828?19:09:25????15?Query?????explain?select?*?from?test?where?state=1

160828?19:09:28????15?Query?????show?full?processlist

160828?19:09:36????15?Query?????select?*?from?test

15?Query?????select?*?from?test

160828?19:09:37????15?Query?????select?*?from?test

160828?19:09:39????15?Quit

刪除binlog日志方法

1、設(shè)置參數(shù)自動(dòng)刪除

expire_logs_days?=?7??#<==刪除7天前的日志

2、從頭刪到指定的文件位置

mysql>?purge?binary?logs?to?'mysql-bin.000004';

Query?OK,?0?rows?affected?(0.03?sec)

mysql>?system?ls?-l?/data/3306/mysql-bin*

-rw-rw----?1?mysql?mysql???126?8月??21?16:31?/data/3306/mysql-bin.000004

-rw-rw----?1?mysql?mysql???126?8月??21?16:32?/data/3306/mysql-bin.000005

-rw-rw----?1?mysql?mysql?15881?8月??28?19:16?/data/3306/mysql-bin.000006

-rw-rw----?1?mysql?mysql????84?8月??28?19:26?/data/3306/mysql-bin.index

3、按照時(shí)間刪除

mysql>?PURGE?MASTER?LOGS?BEFORE?'2016-08-28?13:00:00';

Query?OK,?0?rows?affected?(0.02?sec)

mysql>?system?ls?-l?/data/3306/mysql-bin*

-rw-rw----?1?mysql?mysql?15881?8月??28?19:16?/data/3306/mysql-bin.000006

-rw-rw----?1?mysql?mysql????28?8月??28?19:28?/data/3306/mysql-bin.index

mysql>?PURGE?MASTER?LOGS?BEFORE?DATE_SUB(NOW(?),?INTERVAL?3?DAY);

Query?OK,?0?rows?affected?(0.00?sec)

mysql>?system?ls?-l?/data/3306/mysql-bin*

-rw-rw----?1?mysql?mysql?15881?8月??28?19:16?/data/3306/mysql-bin.000006

-rw-rw----?1?mysql?mysql????28?8月??28?19:28?/data/3306/mysql-bin.index

1、硬件層面優(yōu)化

1.1?數(shù)據(jù)庫物理機(jī)采購(gòu)：

1.2?服務(wù)器硬件配置調(diào)整

1.2.1?服務(wù)器BIOS調(diào)整：

1.2.2?陣列卡調(diào)整：

2、軟件層優(yōu)化

2.1?操作系統(tǒng)層面優(yōu)化

2.1.1?操作系統(tǒng)及MySQL實(shí)例選擇

2.1.2?文件系統(tǒng)層優(yōu)化

2.1.3?linux內(nèi)核參數(shù)優(yōu)化

2.2?mysql數(shù)據(jù)庫層面優(yōu)化

2.2.1?my.cnf參數(shù)優(yōu)化

2.2.2?關(guān)于庫表的設(shè)計(jì)規(guī)范

2.2.3?SQL語句的優(yōu)化

3、網(wǎng)站集群架構(gòu)上的優(yōu)化

4、流程，制度，安全優(yōu)化

總結(jié)

以上是生活随笔為你收集整理的关于mysql优化_MYSQL---关于MYSQL优化的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。