腳本之家服務(wù)器常用軟件

快捷導(dǎo)航

MySql的優(yōu)化步驟介紹(推薦)

更新時(shí)間：2016年06月22日 09:58:40 投稿：jingxian

下面小編就為大家?guī)?lái)一篇MySql的優(yōu)化步驟介紹(推薦)。小編覺(jué)得挺不錯(cuò)的，現(xiàn)在就分享給大家，也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧

MySql優(yōu)化的一般步驟：

1.通過(guò)show status 命令了解各種sql的執(zhí)行效率

SHOW STATUS提供msyql服務(wù)器的狀態(tài)信息

一般情況下，我們只需要了解以”Com”開(kāi)頭的指令

show session status like ‘Com%'：顯示當(dāng)前的連接的統(tǒng)計(jì)結(jié)果

show global status like ‘Com%' ：顯示自數(shù)據(jù)庫(kù)上次啟動(dòng)至今的統(tǒng)計(jì)結(jié)果

注：默認(rèn)是session級(jí)別的

其中Com_XXX表示XXX語(yǔ)句所執(zhí)行的次數(shù)。

重點(diǎn)注意：Com_select,Com_insert,Com_update,Com_delete通過(guò)這幾個(gè)參數(shù)，可以容易地了解到當(dāng)前數(shù)據(jù)庫(kù)的應(yīng)用是以插

入更新為主還是以查詢(xún)操作為主，以及各類(lèi)的SQL大致的執(zhí)行比例是多少。

另外，還有幾個(gè)參數(shù)需要注意下：

show status like ‘Connections'// 試圖連接MySQL服務(wù)器的次數(shù)

show status like ‘Uptime'//服務(wù)器工作的時(shí)間（單位秒）

show status like ‘Slow_queries'//慢查詢(xún)的次數(shù) (默認(rèn)是10秒中就當(dāng)做是慢查詢(xún)，如下圖所示)

chabaoo.cn

a) 如何查詢(xún)mysql的慢查詢(xún)時(shí)間

Show variables like 'long_query_time';

b) 修改mysql 慢查詢(xún)時(shí)間

set long_query_time=2//如果查詢(xún)時(shí)間超過(guò)2秒就算作是慢查詢(xún)

2. 定位執(zhí)行效率較低的SQL語(yǔ)句（dql出現(xiàn)問(wèn)題的概率較dml的大）

問(wèn)題是：如何在一個(gè)項(xiàng)目中，找到慢查詢(xún)的select語(yǔ)句？

答案：mysql支持把慢查詢(xún)語(yǔ)句記錄到日志文件中。程序員需要修改php.ini的配置文件，默認(rèn)情況下，慢查詢(xún)記錄是不開(kāi)啟的。

開(kāi)啟慢查詢(xún)記錄的步驟：

打開(kāi) my.ini ,找到 [mysqld] 在其下面添加

long_query_time = 2

log-slow-queries = D:/mysql/logs/slow.log #設(shè)置把日志寫(xiě)在那里，可以為空，系統(tǒng)會(huì)給一個(gè)缺省的文件

例子：我們數(shù)據(jù)表中有1千萬(wàn)條的數(shù)據(jù)量

DQL語(yǔ)句：SELECT * FROM order_copy WHERE id=12345;

chabaoo.cn

查詢(xún)耗時(shí)：19s>2s，所以mysql會(huì)將該條select語(yǔ)句記錄到慢查詢(xún)?nèi)罩局?/p>

SELECT * FROM order_copy WHERE id=12345的執(zhí)行時(shí)間：

添加索引前：19s

添加索引后：0.08s

3.通過(guò)explain分析低效率的SQL語(yǔ)句的執(zhí)行情況

使用explain分析該dql語(yǔ)句：

EXPLAIN SELECT * FROM order_copy WHERE id=12345

會(huì)產(chǎn)生如下信息：

select_type:表示查詢(xún)的類(lèi)型。

table:輸出結(jié)果集的表

type:表示表的連接類(lèi)型(system和const為佳)

possible_keys:表示查詢(xún)時(shí)，可能使用的索引

key:表示實(shí)際使用的索引

key_len:索引字段的長(zhǎng)度

rows:掃描的行數(shù)

Extra:執(zhí)行情況的描述和說(shuō)明

注意：要盡量避免讓type的結(jié)果為all，extra的結(jié)果為：using filesort

chabaoo.cn

4.確定問(wèn)題并采取相應(yīng)的優(yōu)化措施

常用的優(yōu)化措施是添加索引。添加索引，我們不用加內(nèi)存，不用改程序，不用調(diào)sql，只要執(zhí)行個(gè)正確的'create index'，查詢(xún)速度就可能提高百倍千倍。但是天下沒(méi)有免費(fèi)的午餐，查詢(xún)速度的提高是以插入、更新、刪除的速度為代價(jià)的，這些寫(xiě)操作，增加了大量的I/O。

例如：給字段id添加索引：

ALTER TABLE order_copy ADD PRIMARY KEY(id)

給1千萬(wàn)的數(shù)據(jù)添加primary key 需要耗時(shí)： 428秒（7分鐘）

EXPLAIN SELECT * FROM order_copy WHERE id=12345

chabaoo.cn

正是因?yàn)榻oid添加了索引，才使得rows的結(jié)果為1

但是索引并不是可以隨便添加的，以下幾種情況需牢記在心：

較頻繁的作為查詢(xún)條件字段應(yīng)該創(chuàng)建索引

select * from order_copy where id = $id

唯一性太差的字段不適合單獨(dú)創(chuàng)建索引，即使頻繁作為查詢(xún)條件

select * from order_copy where sex='女'

更新非常頻繁的字段不適合創(chuàng)建索引

select * from order_copy where order_state='未付款'

不會(huì)出現(xiàn)在WHERE子句中字段不該創(chuàng)建索引

索引的類(lèi)型：

PRIMARY 索引 => 在主鍵上自動(dòng)創(chuàng)建INDEX 索引 => 就是普通索引UNIQUE 索引 => 相當(dāng)于INDEX + UniqueFULLTEXT => 只在MYISAM 存儲(chǔ)引擎支持, 目的是全文索引，在內(nèi)容系統(tǒng)中用的多，在全英文網(wǎng)站用多(英文詞獨(dú)立). 中文數(shù)據(jù)不常用，意義不大國(guó)內(nèi)全文索引通常使用 sphinx 來(lái)完成.

索引的使用

建立索引 create [UNIQUE|FULLTEXT] index index_name on tbl_name (col_name [(length)] [ASC | DESC] , …..);
alter table table_name ADD INDEX [index_name] (index_col_name,...)

添加主鍵(索引) ALTER TABLE 表名 ADD PRIMARY KEY(列名,..); 聯(lián)合主鍵

刪除索引 DROP INDEX index_name ON tbl_name;
alter table table_name drop index index_name;刪除主鍵(索引)比較特別: alter table t_b drop primary key;查詢(xún)索引(均可) show index from table_name;
show keys from table_name;
desc table_Name;

以上就是小編為大家?guī)?lái)的MySql的優(yōu)化步驟介紹(推薦)全部?jī)?nèi)容了，希望大家多多支持腳本之家~

您可能感興趣的文章: