Mysql數(shù)據(jù)庫(kù)性能優(yōu)化一

WindySky 2016-04-13

展開全文

今天，數(shù)據(jù)庫(kù)的操作越來(lái)越成為整個(gè)應(yīng)用的性能瓶頸了，這點(diǎn)對(duì)于Web應(yīng)用尤其明顯。關(guān)于數(shù)據(jù)庫(kù)的性能，這并不只是DBA才需要擔(dān)心的事，而這更是我們程序員需要去關(guān)注的事情。當(dāng)我們?nèi)ピO(shè)計(jì)數(shù)據(jù)庫(kù)表結(jié)構(gòu)，對(duì)操作數(shù)據(jù)庫(kù)時(shí)（尤其是查表時(shí)的SQL語(yǔ)句），我們都需要注意數(shù)據(jù)操作的性能。這里，我們不會(huì)講過(guò)多的SQL語(yǔ)句的優(yōu)化，而只是針對(duì)MySQL這一Web應(yīng)用最多的數(shù)據(jù)庫(kù)。

mysql的性能優(yōu)化無(wú)法一蹴而就，必須一步一步慢慢來(lái)，從各個(gè)方面進(jìn)行優(yōu)化，最終性能就會(huì)有大的提升。

Mysql數(shù)據(jù)庫(kù)的優(yōu)化技術(shù)

對(duì)mysql優(yōu)化是一個(gè)綜合性的技術(shù)，主要包括

·表的設(shè)計(jì)合理化(符合3NF)

·添加適當(dāng)索引(index) [四種: 普通索引、主鍵索引、唯一索引unique、全文索引]

·分表技術(shù)(水平分割、垂直分割)

·讀寫[寫: update/delete/add]分離

·存儲(chǔ)過(guò)程 [模塊化編程，可以提高速度]

·對(duì)mysql配置優(yōu)化 [配置最大并發(fā)數(shù)my.ini, 調(diào)整緩存大小 ]

·mysql服務(wù)器硬件升級(jí)

·定時(shí)的去清除不需要的數(shù)據(jù),定時(shí)進(jìn)行碎片整理(MyISAM)

數(shù)據(jù)庫(kù)優(yōu)化工作

對(duì)于一個(gè)以數(shù)據(jù)為中心的應(yīng)用，數(shù)據(jù)庫(kù)的好壞直接影響到程序的性能，因此數(shù)據(jù)庫(kù)性能至關(guān)重要。一般來(lái)說(shuō)，要保證數(shù)據(jù)庫(kù)的效率，要做好以下四個(gè)方面的工作：

① 數(shù)據(jù)庫(kù)設(shè)計(jì)

② sql語(yǔ)句優(yōu)化

③ 數(shù)據(jù)庫(kù)參數(shù)配置

④ 恰當(dāng)?shù)挠布Y源和操作系統(tǒng)

此外，使用適當(dāng)?shù)拇鎯?chǔ)過(guò)程，也能提升性能。

這個(gè)順序也表現(xiàn)了這四個(gè)工作對(duì)性能影響的大小

數(shù)據(jù)庫(kù)表設(shè)計(jì)

通俗地理解三個(gè)范式，對(duì)于數(shù)據(jù)庫(kù)設(shè)計(jì)大有好處。在數(shù)據(jù)庫(kù)設(shè)計(jì)中，為了更好地應(yīng)用三個(gè)范式，就必須通俗地理解三個(gè)范式(通

俗地理解是夠用的理解，并不是最科學(xué)最準(zhǔn)確的理解)：

第一范式：1NF是對(duì)屬性的原子性約束，要求屬性(列)具有原子性，不可再分解；(只要是關(guān)系型數(shù)據(jù)庫(kù)都滿足1NF)

第二范式：2NF是對(duì)記錄的惟一性約束，要求記錄有惟一標(biāo)識(shí)，即實(shí)體的惟一性；

第三范式：3NF是對(duì)字段冗余性的約束，它要求字段沒(méi)有冗余。沒(méi)有冗余的數(shù)據(jù)庫(kù)設(shè)計(jì)可以做到。

但是，沒(méi)有冗余的數(shù)據(jù)庫(kù)未必是最好的數(shù)據(jù)庫(kù)，有時(shí)為了提高運(yùn)行效率，就必須降低范式標(biāo)準(zhǔn)，適當(dāng)保留冗余數(shù)據(jù)。具體做法是：在概念數(shù)據(jù)模型設(shè)計(jì)時(shí)遵守第三范式，降低范式標(biāo)準(zhǔn)的工作放到物理數(shù)據(jù)模型設(shè)計(jì)時(shí)考慮。降低范式就是增加字段，允許冗余。

數(shù)據(jù)庫(kù)的分類

關(guān)系型數(shù)據(jù)庫(kù): mysql/oracle/db2/informix/sysbase/sql server

非關(guān)系型數(shù)據(jù)庫(kù): (特點(diǎn): 面向?qū)ο蠡蛘呒?

NoSql數(shù)據(jù)庫(kù): MongoDB(特點(diǎn)是面向文檔)

舉例說(shuō)明什么是適度冗余，或者說(shuō)有理由的冗余!

上面這個(gè)就是不合適的冗余，原因是:

在這里，為了提高學(xué)生活動(dòng)記錄的檢索效率，把單位名稱冗余到學(xué)生活動(dòng)記錄表里。單位信息有500條記錄，而學(xué)生活動(dòng)記錄在

一年內(nèi)大概有200萬(wàn)數(shù)據(jù)量。如果學(xué)生活動(dòng)記錄表不冗余這個(gè)單位名稱字段，只包含三個(gè)int字段和一個(gè)timestamp字段，只占用了16字節(jié)，是一個(gè)很小的表。而冗余了一個(gè) varchar(32)的字段后則是原來(lái)的3倍，檢索起來(lái)相應(yīng)也多了這么多的I/O。而且記錄數(shù)相差懸殊，500 VS 2000000 ，導(dǎo)致更新一個(gè)單位名稱還要更新4000條冗余記錄。由此可見，這個(gè)冗余根本就是適得其反。

訂單表里面的Price就是一個(gè)冗余字段，因?yàn)槲覀兛梢詮挠唵蚊骷?xì)表中統(tǒng)計(jì)出這個(gè)訂單的價(jià)格，但是這個(gè)冗余是合理的，也能提升查詢性能。

從上面兩個(gè)例子中可以得出一個(gè)結(jié)論:

1---n 冗余應(yīng)當(dāng)發(fā)生在1這一方.

SQL語(yǔ)句優(yōu)化

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

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

2.定位執(zhí)行效率較低的SQL語(yǔ)句-（重點(diǎn)select）

3.通過(guò)explain分析低效率的SQL

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

-- select語(yǔ)句分類

Select

Dml數(shù)據(jù)操作語(yǔ)言(insert update delete)

dtl 數(shù)據(jù)事物語(yǔ)言(commit rollback savepoint)

Ddl數(shù)據(jù)定義語(yǔ)言(create alter drop..)

Dcl(數(shù)據(jù)控制語(yǔ)言) grant revoke

-- Show status 常用命令

--查詢本次會(huì)話

Show session status like 'com_%'; //show session status like 'Com_select'

--查詢?nèi)?/code>

Show global status like 'com_%';

-- 給某個(gè)用戶授權(quán)

grant all privileges on *.* to 'abc'@'%';

--為什么這樣授權(quán) 'abc'?表示用戶名 '@' 表示host, 查看一下mysql->user表就知道了

--回收權(quán)限

revoke all on *.* from 'abc'@'%';

--刷新權(quán)限[也可以不寫]

flush privileges;

SQL語(yǔ)句優(yōu)化-show參數(shù)

MySQL客戶端連接成功后，通過(guò)使用show [session|global] status 命令可以提供服務(wù)器狀態(tài)信息。其中的session來(lái)表示當(dāng)前的連接的統(tǒng)計(jì)結(jié)果，global來(lái)表示自數(shù)據(jù)庫(kù)上次啟動(dòng)至今的統(tǒng)計(jì)結(jié)果。默認(rèn)是session級(jí)別的。

下面的例子：

show status like 'Com_%';

其中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)用是以插入更新為主還是以查詢操作為主，以及各類的SQL大致的執(zhí)行比例是多少。

還有幾個(gè)常用的參數(shù)便于用戶了解數(shù)據(jù)庫(kù)的基本情況。

Connections：試圖連接MySQL服務(wù)器的次數(shù)

Uptime：服務(wù)器工作的時(shí)間（單位秒）

Slow_queries：慢查詢的次數(shù) (默認(rèn)是慢查詢時(shí)間10s)

show status like 'Connections'

show status like 'Uptime'

show status like 'Slow_queries'

如何查詢mysql的慢查詢時(shí)間

1	`Show variables` `like` `'long_query_time';`

修改mysql 慢查詢時(shí)間

1	`set` `long_query_time=2`

SQL語(yǔ)句優(yōu)化-定位慢查詢

問(wèn)題是：如何從一個(gè)大項(xiàng)目中，迅速的定位執(zhí)行速度慢的語(yǔ)句. (定位慢查詢)

首先我們了解mysql數(shù)據(jù)庫(kù)的一些運(yùn)行狀態(tài)如何查詢(比如想知道當(dāng)前mysql運(yùn)行的時(shí)間/一共執(zhí)行了多少次

select/update/delete.. / 當(dāng)前連接)

為了便于測(cè)試，我們構(gòu)建一個(gè)大表(400 萬(wàn))-> 使用存儲(chǔ)過(guò)程構(gòu)建

默認(rèn)情況下，mysql認(rèn)為10秒才是一個(gè)慢查詢.

修改mysql的慢查詢.

1 2	`show variables` `like` `'long_query_time'` `; //可以顯示當(dāng)前慢查詢時(shí)間` `set` `long_query_time=1 ;//可以修改慢查詢時(shí)間`

構(gòu)建大表->大表中記錄有要求, 記錄是不同才有用，否則測(cè)試效果和真實(shí)的相差大.創(chuàng)建:

CREATE TABLE dept( /*部門表*/

deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*編號(hào)*/

dname VARCHAR(20) NOT NULL DEFAULT "", /*名稱*/

loc VARCHAR(13) NOT NULL DEFAULT "" /*地點(diǎn)*/

) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

CREATE TABLE emp

(empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*編號(hào)*/

ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/

job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/

mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上級(jí)編號(hào)*/

hiredate DATE NOT NULL,/*入職時(shí)間*/

sal DECIMAL(7,2) NOT NULL,/*薪水*/

comm DECIMAL(7,2) NOT NULL,/*紅利*/

deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部門編號(hào)*/

)ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

CREATE TABLE salgrade

(

grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,

losal DECIMAL(17,2) NOT NULL,

hisal DECIMAL(17,2) NOT NULL

)ENGINE=MyISAM DEFAULT CHARSET=utf8;

測(cè)試數(shù)據(jù)

INSERT INTO salgrade VALUES (1,700,1200);

INSERT INTO salgrade VALUES (2,1201,1400);

INSERT INTO salgrade VALUES (3,1401,2000);

INSERT INTO salgrade VALUES (4,2001,3000);

INSERT INTO salgrade VALUES (5,3001,9999);

為了存儲(chǔ)過(guò)程能夠正常執(zhí)行，我們需要把命令執(zhí)行結(jié)束符修改delimiter $$
創(chuàng)建函數(shù)，該函數(shù)會(huì)返回一個(gè)指定長(zhǎng)度的隨機(jī)字符串

create function rand_string(n INT)

returns varchar(255) #該函數(shù)會(huì)返回一個(gè)字符串

begin

#chars_str定義一個(gè)變量 chars_str,類型是 varchar(100),默認(rèn)值'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';

declare chars_str varchar(100) default

'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';

declare return_str varchar(255) default '';

declare i int default 0;

while i < n do

set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));

set i = i + 1;

end while;

return return_str;

end

創(chuàng)建一個(gè)存儲(chǔ)過(guò)程

create procedure insert_emp(in start int(10),in max_num int(10))

begin

declare i int default 0;

#set autocommit =0 把a(bǔ)utocommit設(shè)置成0

set autocommit = 0;

repeat

set i = i + 1;

insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand());

until i = max_num

end repeat;

commit;

end

#調(diào)用剛剛寫好的函數(shù), 1800000條記錄,從100001號(hào)開始

call insert_emp(100001,4000000);

這時(shí)我們?nèi)绻霈F(xiàn)一條語(yǔ)句執(zhí)行時(shí)間超過(guò)1秒中，就會(huì)統(tǒng)計(jì)到.

如果把慢查詢的sql記錄到我們的一個(gè)日志中

在默認(rèn)情況下，低版本的mysql不會(huì)記錄慢查詢，需要在啟動(dòng)mysql時(shí)候，指定記錄慢查詢才可以

bin\mysqld.exe - -safe-mode - -slow-query-log [mysql5.5 可以在my.ini指定]

bin\mysqld.exe –log-slow-queries=d:/abc.log [低版本mysql5.0可以在my.ini指定]

該慢查詢?nèi)罩緯?huì)放在data目錄下[在mysql5.0這個(gè)版本中時(shí)放在 mysql安裝目錄/data/下],在 mysql5.5.19下是需要查看

my.ini 的 datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/“來(lái)確定.

在mysql5.6中，默認(rèn)是啟動(dòng)記錄慢查詢的，my.ini的所在目錄為：C:\ProgramData\MySQL\MySQL Server 5.6，其中有一個(gè)配置項(xiàng)

slow-query-log=1

針對(duì) mysql5.5啟動(dòng)慢查詢有兩種方法

bin\mysqld.exe - -safe-mode - -slow-query-log

也可以在my.ini 文件中配置:

[mysqld]

# The TCP/IP Port the MySQL Server will listen on

port=3306

slow-query-log

通過(guò)慢查詢?nèi)罩径ㄎ粓?zhí)行效率較低的SQL語(yǔ)句。慢查詢?nèi)罩居涗浟怂袌?zhí)行時(shí)間超過(guò)long_query_time所設(shè)置的SQL語(yǔ)句。

1 2	`show variables` `like` `'long_query_time';` `set` `long_query_time=2;`

為dept表添加數(shù)據(jù)

desc dept;

ALTER table dept add id int PRIMARY key auto_increment;

CREATE PRIMARY KEY on dept(id);

create INDEX idx_dptno_dptname on dept(deptno,dname);

INSERT into dept(deptno,dname,loc) values(1,'研發(fā)部','康和盛大廈5樓501');

INSERT into dept(deptno,dname,loc) values(2,'產(chǎn)品部','康和盛大廈5樓502');

INSERT into dept(deptno,dname,loc) values(3,'財(cái)務(wù)部','康和盛大廈5樓503');UPDATE emp set deptno=1 where empno=100002;

****測(cè)試語(yǔ)句***[對(duì)emp表的記錄可以為3600000 ,效果很明顯慢]

1	`select` `*` `from` `emp` `where` `empno=(select` `empno` `from` `emp` `where` `ename='研發(fā)部')`

如果帶上order by e.empno 速度就會(huì)更慢，有時(shí)會(huì)到1min多.

測(cè)試語(yǔ)句

1	`select` `*` `from` `emp e,dept d` `where` `e.empno=100002` `and` `e.deptno=d.deptno;`

查看慢查詢?nèi)罩荆耗J(rèn)為數(shù)據(jù)目錄data中的host-name-slow.log。低版本的mysql需要通過(guò)在開啟mysql時(shí)使用- -log-slow-queries[=file_name]來(lái)配置

SQL語(yǔ)句優(yōu)化-explain分析問(wèn)題

1	`Explain` `select` `*` `from` `emp` `where` `ename=“wsrcla”`

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

select_type:表示查詢的類型。

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

type:表示表的連接類型

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

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

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

rows:掃描出的行數(shù)(估算的行數(shù))

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

explain select * from emp where ename='JKLOIP'

如果要測(cè)試Extra的filesort可以對(duì)上面的語(yǔ)句修改

1	`explain` `select` `*` `from` `emp` `order` `by` `ename\G`

EXPLAIN詳解

SELECT識(shí)別符。這是SELECT的查詢序列號(hào)

id 示例

1	`SELECT` `*` `FROM` `emp` `WHERE` `empno = 1` `and` `ename = (SELECT` `ename` `FROM` `emp` `WHERE` `empno = 100001) \G;`

select_type

PRIMARY :子查詢中最外層查詢

SUBQUERY : 子查詢內(nèi)層第一個(gè)SELECT，結(jié)果不依賴于外部查詢

DEPENDENT SUBQUERY:子查詢內(nèi)層第一個(gè)SELECT，依賴于外部查詢

UNION :UNION語(yǔ)句中第二個(gè)SELECT開始后面所有SELECT，

SIMPLE

UNION RESULT UNION 中合并結(jié)果

Table

顯示這一步所訪問(wèn)數(shù)據(jù)庫(kù)中表名稱

Type

對(duì)表訪問(wèn)方式

ALL：

1	`SELECT` `*` `FROM` `emp \G`

完整的表掃描通常不好

1	`SELECT` `` `FROM` `(SELECT` `` `FROM` `emp` `WHERE` `empno = 1) a ;`

system：表僅有一行(=系統(tǒng)表)。這是const聯(lián)接類型的一個(gè)特

const：表最多有一個(gè)匹配行

Possible_keys

該查詢可以利用的索引，如果沒(méi)有任何索引顯示 null

Key

Mysql 從 Possible_keys 所選擇使用索引

Rows

估算出結(jié)果集行數(shù)

Extra

查詢細(xì)節(jié)信息

No tables ：Query語(yǔ)句中使用FROM DUAL 或不含任何FROM子句

Using filesort ：當(dāng)Query中包含 ORDER BY 操作，而且無(wú)法利用索引完成排序，

Impossible WHERE noticed after reading const tables: MYSQL Query Optimizer

通過(guò)收集統(tǒng)計(jì)信息不可能存在結(jié)果

Using temporary：某些操作必須使用臨時(shí)表，常見 GROUP BY ; ORDER BY

Using where：不用讀取表中所有信息，僅通過(guò)索引就可以獲取所需數(shù)據(jù);

以上所述是小編給大家介紹的Mysql數(shù)據(jù)庫(kù)性能優(yōu)化一，下篇文章繼續(xù)給大家介紹mysql數(shù)據(jù)庫(kù)性能優(yōu)化二，希望大家持續(xù)關(guān)注本站最新內(nèi)容！

本站是提供個(gè)人知識(shí)管理的網(wǎng)絡(luò)存儲(chǔ)空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點(diǎn)。請(qǐng)注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購(gòu)買等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請(qǐng)點(diǎn)擊一鍵舉報(bào)。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來(lái)自： WindySky > 《mysql》

舉報(bào)/認(rèn)領(lǐng)