Mysql數據庫機能優化一

Mysql數據庫機能優化一。本站提示廣大學習愛好者：（Mysql數據庫機能優化一）文章只能為提供參考，不一定能成為您想要的結果。以下是Mysql數據庫機能優化一正文

---

明天，數據庫的操作愈來愈成為全部運用的機能瓶頸了，這點關於Web運用特別顯著。關於數據庫的機能，這其實不只是DBA才須要擔憂的事，而這更是我們法式員須要去存眷的工作。當我們去設計數據庫表構造，對操作數據庫時（特別是查表時的SQL語句），我們都須要留意數據操作的機能。這裡，我們不會講過量的SQL語句的優化，而只是針對MySQL這一Web運用最多的數據庫。

mysql的機能優化沒法一揮而就，必需一步一步漸漸來，從各個方面停止優化，終究機能就會有年夜的晉升。

Mysql數據庫的優化技巧

對mysql優化是一個綜合性的技巧，重要包含

•表的設計公道化(相符3NF)

•添加恰當索引(index) [四種: 通俗索引、主鍵索引、獨一索引unique、全文索引]

•分表技巧(程度朋分、垂直朋分)

•讀寫[寫: update/delete/add]分別

•存儲進程 [模塊化編程，可以進步速度]

•對mysql設置裝備擺設優化 [設置裝備擺設最年夜並發數my.ini, 調劑緩存年夜小 ]

•mysql辦事器硬件進級

•准時的去消除不須要的數據,准時停止碎片整頓(MyISAM)

數據庫優化任務

關於一個以數據為中間的運用，數據庫的利害直接影響到法式的機能，是以數據庫機能相當主要。普通來講，要包管數據庫的效力，要做好以下四個方面的任務：

① 數據庫設計

② sql語句優化

③ 數據庫參數設置裝備擺設

④ 適當的硬件資本和操作體系

另外，應用恰當的存儲進程，也能晉升機能。

這個次序也表示了這四個任務對機能影響的年夜小

數據庫表設計

淺顯地輿解三個范式，關於數據庫設計年夜有利益。在數據庫設計中，為了更好地運用三個范式，就必需淺顯地輿解三個范式(通

俗地輿解是夠用的懂得，其實不是最迷信最精確的懂得)：

第一范式：1NF是對屬性的原子性束縛，請求屬性(列)具有原子性，弗成再分化；(只需是關系型數據庫都知足1NF)

第二范式：2NF是對記載的唯一性束縛，請求記載有唯一標識，即實體的唯一性；

第三范式：3NF是對字段冗余性的束縛，它請求字段沒有冗余。 沒有冗余的數據庫設計可以做到。

然則，沒有冗余的數據庫未必是最好的數據庫，有時為了進步運轉效力，就必需下降范式尺度，恰當保存冗余數據。詳細做法是： 在概念數據模子設計時遵照第三范式，下降范式尺度的任務放到物理數據模子設計時斟酌。下降范式就是增長字段，許可冗余。

☞ 數據庫的分類

關系型數據庫: mysql/oracle/db2/informix/sysbase/sql server

非關系型數據庫: (特色: 面向對象或許聚集)

NoSql數據庫: MongoDB(特色是面向文檔)

舉例解釋甚麼是過度冗余，或許說有來由的冗余!

下面這個就是不適合的冗余，緣由是:

在這裡，為了進步先生運動記載的檢索效力，把單元稱號冗余到先生運動記載內外。單元信息有500筆記錄，而先生運動記載在

一年內年夜概有200萬數據量。 假如先生運動記載表不冗余這個單元稱號字段，只包括三個int字段和一個timestamp字段，只占用了16字節，是一個很小的表。而冗余了一個 varchar(32)的字段後則是本來的3倍，檢索起來響應也多了這麼多的I/O。並且記載數相差差異，500 VS 2000000 ，招致更新一個單元稱號還要更新4000條冗余記載。因而可知，這個冗余基本就是拔苗助長。

定單內外面的Price就是一個冗余字段，由於我們可以從定單明細表中統計出這個定單的價錢，然則這個冗余是公道的，也能晉升查詢機能。

從下面兩個例子中可以得出一個結論:

1---n 冗余應該產生在1這一方.

SQL語句優化

SQL優化的普通步調

1.經由過程show status敕令懂得各類SQL的履行頻率。

2.定位履行效力較低的SQL語句-（重點select）

3.經由過程explain剖析低效力的SQL

4.肯定成績並采用響應的優化辦法

-- select語句分類
Select
Dml數據操作說話(insert update delete)
dtl 數據事物說話(commit rollback savepoint)
Ddl數據界說說話(create alter drop..)
Dcl(數據掌握說話) grant revoke
-- Show status 經常使用敕令
--查詢本次會話
Show session status like 'com_%'; //show session status like 'Com_select'
--查詢全局
Show global status like 'com_%';
-- 給某個用戶受權
grant all privileges on *.* to 'abc'@'%';
--為何如許受權 'abc'表現用戶名 '@' 表現host, 檢查一下mysql->user表就曉得了
--收受接管權限
revoke all on *.* from 'abc'@'%';
--刷新權限[也能夠不寫]
flush privileges; 

SQL語句優化-show參數

MySQL客戶端銜接勝利後，經由過程應用show [session|global] status 敕令可以供給辦事器狀況信息。個中的session來表現以後的銜接的統計成果，global來表現自數據庫前次啟動至今的統計成果。默許是session級其余。

上面的例子：

show status like 'Com_%';

個中Com_XXX表現XXX語句所履行的次數。

重點留意：Com_select,Com_insert,Com_update,Com_delete經由過程這幾個參數，可以輕易地懂得到以後數據庫的運用是以拔出更新為主照樣以查詢操作為主，和各類的SQL年夜致的履行比例是若干。

還有幾個經常使用的參數便於用戶懂得數據庫的根本情形。

Connections：試圖銜接MySQL辦事器的次數

Uptime：辦事器任務的時光（單元秒）

Slow_queries：慢查詢的次數 (默許是慢查詢時光10s)

show status like 'Connections'
show status like 'Uptime'
show status like 'Slow_queries' 

若何查詢mysql的慢查詢時光

Show variables like 'long_query_time'; 

修正mysql 慢查詢時光

set long_query_time=2 

SQL語句優化-定位慢查詢

成績是： 若何從一個年夜項目中，敏捷的定位履行速度慢的語句. (定位慢查詢)

起首我們懂得mysql數據庫的一些運轉狀況若何查詢(好比想曉得以後mysql運轉的時光/一共履行了若干次

select/update/delete.. / 以後銜接)

為了便於測試，我們構建一個年夜表(400 萬)-> 應用存儲進程構建

默許情形下，mysql以為10秒才是一個慢查詢.

修正mysql的慢查詢.

show variables like 'long_query_time' ; //可以顯示以後慢查詢時光
set long_query_time=1 ;//可以修正慢查詢時光 

構建年夜表->年夜表中記載有請求, 記載是分歧才有效，不然測試後果和真實的相差年夜.創立:

CREATE TABLE dept( /*部分表*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*編號*/
dname VARCHAR(20) NOT NULL DEFAULT "", /*稱號*/
loc VARCHAR(13) NOT NULL DEFAULT "" /*所在*/
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

CREATE TABLE emp
(empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*編號*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*任務*/
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*下級編號*/
hiredate DATE NOT NULL,/*入職時光*/
sal DECIMAL(7,2) NOT NULL,/*薪水*/
comm DECIMAL(7,2) NOT NULL,/*盈余*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部分編號*/
)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; 

測試數據

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); 

為了存儲進程可以或許正常履行，我們須要把敕令履行停止符修正delimiter $$
創立函數，該函數會前往一個指定長度的隨機字符串

create function rand_string(n INT) 
returns varchar(255) #該函數會前往一個字符串
begin 
#chars_str界說一個變量 chars_str,類型是 varchar(100),默許值'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 

創立一個存儲進程

create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0; 
#set autocommit =0 把autocommit設置成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 
#挪用方才寫好的函數, 1800000筆記錄,從100001號開端
call insert_emp(100001,4000000);

這時候我們假如湧現一條語句履行時光跨越1秒中，就會統計到.

假如把慢查詢的sql記載到我們的一個日記中

在默許情形下，低版本的mysql不會記載慢查詢，須要在啟動mysql時刻，指定記載慢查詢才可以

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指定]

該慢查詢日記會放在data目次下[在mysql5.0這個版本中時放在 mysql裝置目次/data/下],在 mysql5.5.19下是須要檢查

my.ini 的 datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/“來肯定.

在mysql5.6中，默許是啟動記載慢查詢的，my.ini的地點目次為：C:\ProgramData\MySQL\MySQL Server 5.6，個中有一個設置裝備擺設項

slow-query-log=1

針對 mysql5.5啟動慢查詢有兩種辦法

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 

經由過程慢查詢日記定位履行效力較低的SQL語句。慢查詢日記記載了一切履行時光跨越long_query_time所設置的SQL語句。

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

為dept表添加數據

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,'研發部','康和隆重廈5樓501');
INSERT into dept(deptno,dname,loc) values(2,'產物部','康和隆重廈5樓502');
INSERT into dept(deptno,dname,loc) values(3,'財政部','康和隆重廈5樓503');UPDATE emp set deptno=1 where empno=100002;

****測試語句***[對emp表的記載可認為3600000 ,後果很顯著慢]

select * from emp where empno=(select empno from emp where ename='研發部')

假如帶上order by e.empno 速度就會更慢，有時會到1min多.

測試語句

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

檢查慢查詢日記：默許為數據目次data中的host-name-slow.log。低版本的mysql須要經由過程在開啟mysql時應用- -log-slow-queries[=file_name]來設置裝備擺設

SQL語句優化-explain剖析成績

Explain select * from emp where ename=“wsrcla”

會發生以下信息：

select_type:表現查詢的類型。

table:輸入成果集的表

type:表現表的銜接類型

possible_keys:表現查詢時，能夠應用的索引

key:表現現實應用的索引

key_len:索引字段的長度

rows:掃描出的行數(預算的行數)

Extra:履行情形的描寫和解釋

explain select * from emp where ename='JKLOIP'

假如要測試Extra的filesort可以對下面的語句修正

explain select * from emp order by ename\G 

EXPLAIN詳解

id

SELECT辨認符。這是SELECT的查詢序列號

id 示例

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

select_type

PRIMARY :子查詢中最外層查詢

SUBQUERY : 子查詢內層第一個SELECT，成果不依附於內部查詢

DEPENDENT SUBQUERY:子查詢內層第一個SELECT，依附於內部查詢

UNION :UNION語句中第二個SELECT開端前面一切SELECT，

SIMPLE

UNION RESULT UNION 中歸並成果

Table

顯示這一步所拜訪數據庫中表稱號

Type

對表拜訪方法

ALL：

SELECT * FROM emp \G

完全的表掃描 平日欠好

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

system：表唯一一行(=體系表)。這是const聯接類型的一個特

const：表最多有一個婚配行

Possible_keys

該查詢可以應用的索引，假如沒有任何索引顯示 null

Key

Mysql 從 Possible_keys 所選擇應用索引

Rows

預算出成果集行數

Extra

查詢細節信息

No tables ：Query語句中應用FROM DUAL 或不含任何FROM子句

Using filesort ：當Query中包括 ORDER BY 操作，並且沒法應用索引完成排序，

Impossible WHERE noticed after reading const tables: MYSQL Query Optimizer

經由過程搜集統計信息弗成能存在成果

Using temporary：某些操作必需應用暫時表，罕見 GROUP BY ; ORDER BY

Using where：不消讀取表中一切信息，僅經由過程索引便可以獲得所需數據;

以上所述是小編給年夜家引見的Mysql數據庫機能優化一 ，下篇文章持續給年夜家引見mysql數據庫機能優化二，願望年夜家連續存眷本站最新內容！