不管對(duì)于哪種服務(wù)，對(duì)于其優(yōu)化，無(wú)非是從兩個(gè)方面著手，第一個(gè)是對(duì)于硬件方面的優(yōu)化，第二個(gè)是對(duì)系統(tǒng)以及服務(wù)本身的優(yōu)化。 1、查詢連接MySQL服務(wù)器的次數(shù)

mysql> show status like 'connections';

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

| Variable_name | Value |

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

| Connections | 3 |

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

1 row in set (0.01 sec)

2、查詢MySQL服務(wù)器的運(yùn)行時(shí)間

mysql> show status like 'uptime'; #單位為秒

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

| Variable_name | Value |

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

| Uptime | 158 |

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

1 row in set (0.00 sec)

3、查詢操作的次數(shù)

mysql> show status like 'com_select';

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

| Variable_name | Value |

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

| Com_select | 1 |

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

1 row in set (0.00 sec)

4、插入操作的次數(shù)

mysql> show status like 'com_insert';

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

| Variable_name | Value |

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

| Com_insert | 2 |

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

1 row in set (0.00 sec)

5、更新操作的次數(shù)

mysql> show status like 'com_update';

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

| Variable_name | Value |

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

| Com_update | 3 |

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

1 row in set (0.00 sec)

6、刪除操作的次數(shù)

mysql> show status like 'com_delete';

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

| Variable_name | Value |

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

| Com_delete | 1 |

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

1 row in set (0.00 sec)

7、查詢MySQL服務(wù)器的慢查詢次數(shù)

mysql> show status like 'slow_queries';

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

| Variable_name | Value |

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

| Slow_queries | 15 |

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

1 row in set (0.00 sec)

二、對(duì)SQL語(yǔ)句進(jìn)行分析 1、使用explain關(guān)鍵字進(jìn)行分析

mysql> explain select * from user\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: user #表名

partitions: NULL

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL #使用哪個(gè)列或常數(shù)與索引一起使用來(lái)查詢記錄

rows: 3

filtered: 100.00

Extra: NULL

1 row in set, 1 warning (0.00 sec)

上面的select_type解釋如下： Select_type:表示select語(yǔ)句的類(lèi)型 其中simple 是簡(jiǎn)單查詢(不包括連接查詢和子查詢) Primary 主查詢 Union 連接查詢；

2、利用索引來(lái)提高查詢效率

mysql> explain select * from stu_info where s_id=3\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: stu_info

partitions: NULL

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 3 #需要查詢?nèi)胁拍懿榈?這個(gè)表數(shù)據(jù)總共也就三行)

filtered: 33.33

Extra: Using where

1 row in set, 1 warning (0.00 sec)

mysql> create index index_1 on stu_info(s_id); # 創(chuàng)建索引

mysql> explain select * from stu_info where s_id=3\G # 再次查詢

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: stu_info

partitions: NULL

type: ref

possible_keys: index_1 #使用的是哪個(gè)索引名稱(chēng)

key: index_1

key_len: 5

ref: const

rows: 1 #創(chuàng)建索引后，查詢1行就查到可

filtered: 100.00

Extra: NULL

1 row in set, 1 warning (0.00 sec)

使用索引注意事項(xiàng)如下：

做索引了之后，用 like ‘xx%’ %不在第一位查詢效率最高；

若使用多字段索引，除了第一字段查詢最快，其余不會(huì)按索引來(lái)，索引不生效；

若創(chuàng)建索引所設(shè)置的字段，查詢索引組合 or 左右邊的值都是屬于索引設(shè)置字段下的值。

關(guān)于使用索引，可參考：MySQL 之索引類(lèi)型 三、profiling分析查詢 通過(guò)慢日志查詢可以知道哪些SQL語(yǔ)句執(zhí)行效率低下，通過(guò)explain我們可以得知SQL語(yǔ)句的具體執(zhí)行情況，索引使用等，還可以結(jié)合show命令查看執(zhí)行狀態(tài)。如果覺(jué)得explain的信息不夠詳細(xì)，可以同通過(guò)profiling命令得到更準(zhǔn)確的SQL執(zhí)行消耗系統(tǒng)資源的信息。 profiling默認(rèn)是關(guān)閉的。可以通過(guò)以下語(yǔ)句查看： 1、查看profiling是否開(kāi)啟

mysql> show variables like '%profiling%';

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

| Variable_name | Value |

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

| have_profiling | YES |

| profiling | OFF | # OFF表示未開(kāi)啟

| profiling_history_size | 15 |

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

3 rows in set (0.00 sec)

mysql> select @@profiling;

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

| @@profiling |

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

| 0 | # 0表示未開(kāi)啟

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

1 row in set, 1 warning (0.00 sec)

2、開(kāi)啟profiling

mysql> set profiling=1; # 開(kāi)啟

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @@profiling; # 查看是否開(kāi)啟

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

| @@profiling |

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

| 1 |

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

1 row in set, 1 warning (0.00 sec)

3、執(zhí)行要測(cè)試的SQL語(yǔ)句

mysql> select * from stu_info;

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

| s_id | s_name | s_into |

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

| 1 | 2 | 3 |

| 3 | 4 | 5 |

| 2 | 4 | 5 |

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

3 rows in set (0.00 sec)

4、查看SQL語(yǔ)句對(duì)應(yīng)的ID，對(duì)其進(jìn)行分析

mysql> show profiles;

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

| Query_ID | Duration | Query |

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

| 1 | 0.00016900 | select @@profiling |

| 2 | 0.00018425 | select * from bank |

| 3 | 0.00018475 | select * from stu_info |

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

3 rows in set, 1 warning (0.00 sec)

mysql> show profile for query 3; #查詢sql語(yǔ)句的詳細(xì)分析

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

| Status | Duration |

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

| starting | 0.000058 |

| checking permissions | 0.000005 |

| Opening tables | 0.000022 |

| init | 0.000015 |

| System lock | 0.000006 |

| optimizing | 0.000002 |

| statistics | 0.000008 |

| preparing | 0.000007 |

| executing | 0.000001 |

| Sending data | 0.000036 |

| end | 0.000002 |

| query end | 0.000004 |

| closing tables | 0.000004 |

| freeing items | 0.000008 |

| cleaning up | 0.000008 |

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

15 rows in set, 1 warning (0.00 sec)

在上面命令的返回結(jié)果中，status是profile里的狀態(tài)，duration是status狀態(tài)下的耗時(shí)，因此我們關(guān)注的就是哪個(gè)狀態(tài)最耗時(shí)，這些狀態(tài)中哪些可以優(yōu)化，當(dāng)然也可以查看更多的信息，比如：CPU等。語(yǔ)法如下：

mysql> show profile block io for query 3\G

mysql> show profile all for query 3\G

除了上面的block io和all以外，還可以換成cpu(顯示用戶cpu時(shí)間、系統(tǒng)cpu時(shí)間)、ipc(顯示發(fā)送和接收相關(guān)開(kāi)銷(xiāo)信息)、page faults(顯示頁(yè)面錯(cuò)誤相關(guān)開(kāi)銷(xiāo)信息)、swaps(顯示交換次數(shù)相關(guān)開(kāi)銷(xiāo)的信息)。 注意：測(cè)試完成之后，記得要關(guān)閉調(diào)試功能，以免影響數(shù)據(jù)庫(kù)的正常使用。 注意：測(cè)試完成之后，記得要關(guān)閉調(diào)試功能，以免影響數(shù)據(jù)庫(kù)的正常使用。 注意：測(cè)試完成之后，記得要關(guān)閉調(diào)試功能，以免影響數(shù)據(jù)庫(kù)的正常使用。 四、對(duì)數(shù)據(jù)庫(kù)表結(jié)構(gòu)進(jìn)行優(yōu)化

對(duì)數(shù)據(jù)庫(kù)表結(jié)構(gòu)的優(yōu)化大概可以從以下幾個(gè)方面著手：

將字段很多的表分解成多個(gè)表，盡量避免表字段過(guò)多；

增加中間表，合理增加冗余字段；

優(yōu)化插入記錄的速度；

在插入數(shù)據(jù)之前禁用索引，會(huì)讓創(chuàng)建索引不生效，命令： ALTER TABLE table_name DISABLE KEYS;

根據(jù)實(shí)際情況來(lái)定，在插入記錄之前禁用唯一性檢查，命令：set unique_checks=0;

多條插入數(shù)據(jù)的命令最好整合為一條；

使用load data infle批量插入數(shù)據(jù)。

對(duì)于innodb引擎的表來(lái)說(shuō)，以下幾點(diǎn)可以進(jìn)行優(yōu)化：

禁用唯一性檢查：set unique_checks=0;

禁用外鍵檢查：set foreign_key_checks=0;

禁用自動(dòng)提交：set autocommit=0;

分析表，檢查表和優(yōu)化表 所謂分析表，就是分析關(guān)鍵字的分布，檢查表就是檢查是否存在錯(cuò)誤，優(yōu)化表就是刪除或更新造成的空間浪費(fèi)。 1、分析表 分析表可以一次分析一個(gè)或多個(gè)表，在分析期間只能讀，不能進(jìn)行插入和更新操作。分析表的語(yǔ)法如下：

mysql> analyze table stu_info;

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

| Table | Op | Msg_type | Msg_text |

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

| mysql.stu_info | analyze | status | OK |

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

1 row in set (0.01 sec)

對(duì)于上述返回的結(jié)果解釋：Table是表名 ，op執(zhí)行的操作是什么， msg_type 信息級(jí)別(status是正常狀態(tài)，info是信息，note注意，warning警告，error錯(cuò)誤)， msg_text 是顯示信息。 2、檢查表 檢查是否存在錯(cuò)誤,關(guān)鍵字統(tǒng)計(jì)，檢查視圖是否有錯(cuò)誤 Check table 表名 option ={quick |fast | medium|extended |changed} Quick 不掃描行，不檢查錯(cuò)誤連接 Fast 只檢查沒(méi)有被正確關(guān)閉的表 Medium 掃描行驗(yàn)證被刪除的連接是有效的，也可以計(jì)算各行的關(guān)鍵字校驗(yàn)和。 Extended 對(duì)每行所有關(guān)鍵字進(jìn)行全面的關(guān)鍵字查找，Changed 只檢查上次檢查后被更改的表和沒(méi)有被正確關(guān)閉的表，Option只對(duì)myisam 有效 對(duì)innodb表無(wú)效，在執(zhí)行時(shí)會(huì)給表加上只讀鎖。http://www.sina.com.cn/mid/search.shtml?q=%E7%99%BE%E5%BA%A6%E5%85%B3%E9%94%AE%E8%AF%8D%E6%8E%92%E5%90%8D%E6%89%A3120280279

mysql> check table stu_info;

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

| Table | Op | Msg_type | Msg_text |

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

| mysql.stu_info | check | status | OK |

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

1 row in set (0.00 sec)

3、優(yōu)化表 消除刪除或更新造成的空間浪費(fèi)，命令語(yǔ)法格式為：Optimize [local |no_write_to_binlog] table tb1_name ….， 優(yōu)化myisam的表和innodb的表都有效， 但是只能優(yōu)化表中的varchar\text\blob數(shù)字類(lèi)型， 執(zhí)行過(guò)程中上只讀鎖。

mysql> optimize table stu_info\G

*************************** 1. row ***************************

Table: mysql.stu_info

Op: optimize

Msg_type: note

Msg_text: Table does not support optimize, doing recreate + analyze instead

*************************** 2. row ***************************

Table: mysql.stu_info

Op: optimize

Msg_type: status

Msg_text: OK

2 rows in set (0.04 sec)

總結(jié)

以上是生活随笔為你收集整理的mysql checking permissions 优化_MySQL 之数据库优化的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

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