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語句分類SelectDml資料操作語言(insert update delete)dtl 資料事物語言(commit rollback savepoint)Ddl資料定義語言 (Data Definition Language)(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))begindeclare i int default 0; #set autocommit =0 把autocommit設定成0set autocommit = 0; repeatset i = i + 1;insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand());until i = max_numend 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 onport=3306slow-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資料庫效能最佳化二，希望大家持續關注本站最新內容！