《Mysql入門Mysql數據庫性能優化一》要點：
本文介紹了Mysql入門Mysql數據庫性能優化一，希望對您有用。如果有疑問，可以聯系我們。

本日,數據庫的操作越來越成為整個應用的性能瓶頸了,這點對于Web應用尤其明顯.關于數據庫的性能,這并不只是DBA才需要擔心的事,而這更是我們程序員需要去關注的事情.當我們去設計數據庫表結構,對操作數據庫時(尤其是查表時的SQL語句),我們都需要注意數據操作的性能.這里,我們不會講過多的SQL語句的優化,而只是針對MySQL這一Web應用最多的數據庫.MYSQL學習

mysql的性能優化無法一蹴而就,必需一步一步慢慢來,從各個方面進行優化,最終性能就會有大的提升.
MYSQL學習

Mysql數據庫的優化技術
MYSQL學習

對mysql優化是一個綜合性的技術,主要包含
MYSQL學習

?表的設計合理化(符合3NF)
MYSQL學習

?添加適當索引(index) [四種: 普通索引、主鍵索引、唯一索引unique、全文索引]
MYSQL學習

?分表技術(程度分割、垂直分割)
MYSQL學習

?讀寫[寫: update/delete/add]分離
MYSQL學習

?存儲過程 [模塊化編程,可以提高速度]
MYSQL學習

?對mysql配置優化 [配置最大并發數my.ini, 調整緩存大小 ]
MYSQL學習

?mysql服務器硬件升級
MYSQL學習

?定時的去清除不必要的數據,定時進行碎片整理(MyISAM)
MYSQL學習

數據庫優化工作
MYSQL學習

對于一個以數據為中心的應用,數據庫的好壞直接影響到法式的性能,因此數據庫性能至關重要.一般來說,要保證數據庫的效率,要做好以下四個方面的工作：
MYSQL學習

① 數據庫設計
MYSQL學習

② sql語句優化
MYSQL學習

③ 數據庫參數配置
MYSQL學習

④ 恰當的硬件資源和操作系統
MYSQL學習

此外,使用適當的存儲過程,也能提升性能.
MYSQL學習

這個順序也表示了這四個工作對性能影響的大小
MYSQL學習

數據庫表設計
MYSQL學習

通俗地理解三個范式,對于數據庫設計大有好處.在數據庫設計中,為了更好地應用三個范式,就必需通俗地理解三個范式(通MYSQL學習

俗地理解是夠用的理解,并不是最科學最準確的理解)：
MYSQL學習

第一范式：1NF是對屬性的原子性約束,要求屬性(列)具有原子性,弗成再分解；(只要是關系型數據庫都滿足1NF)
MYSQL學習

第二范式：2NF是對記錄的惟一性約束,要求記錄有惟一標識,即實體的惟一性；
MYSQL學習

第三范式：3NF是對字段冗余性的約束,它要求字段沒有冗余. 沒有冗余的數據庫設計可以做到.
MYSQL學習

但是,沒有冗余的數據庫未必是最好的數據庫,有時為了提高運行效率,就必須降低范式標準,適當保存冗余數據.具體做法是： 在概念數據模型設計時遵守第三范式,降低范式標準的工作放到物理數據模型設計時考慮.降低范式就是增加字段,允許冗余.
MYSQL學習

? 數據庫的分類
MYSQL學習

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

非關系型數據庫: (特點: 面向對象或者集合)
MYSQL學習

NoSql數據庫: MongoDB(特點是面向文檔)
MYSQL學習

舉例闡明什么是適度冗余,或者說有理由的冗余!
MYSQL學習

MYSQL學習

上面這個便是不合適的冗余,原因是:
MYSQL學習

在這里,為了提高學生活動記錄的檢索效率,把單位名稱冗余到學生活動記錄內外.單位信息有500條記錄,而學生活動記錄在MYSQL學習

一年內大概有200萬數據量. 如果學生活動記錄表不冗余這個單位名稱字段,只包括三個int字段和一個timestamp字段,只占用了16字節,是一個很小的表.而冗余了一個 varchar(32)的字段后則是原來的3倍,檢索起來相應也多了這么多的I/O.而且記錄數相差懸殊,500 VS 2000000 ,導致更新一個單位名稱還要更新4000條冗余記錄.由此可見,這個冗余根本就是適得其反.
MYSQL學習

MYSQL學習

訂單表里面的Price便是一個冗余字段,因為我們可以從訂單明細表中統計出這個訂單的價格,但是這個冗余是合理的,也能提升查詢性能.
MYSQL學習

從上面兩個例子中可以得出一個結論:
MYSQL學習

1---n 冗余應當產生在1這一方.
MYSQL學習

SQL語句優化
MYSQL學習

SQL優化的一般步調
MYSQL學習

1.通過show status命令了解各種SQL的執行頻率.
MYSQL學習

2.定位執行效率較低的SQL語句-(重點select)
MYSQL學習

3.通過explain闡發低效率的SQL
MYSQL學習

4.確定問題并采取相應的優化步伐MYSQL學習

-- 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學習

MySQL客戶端連接成功后,通過使用show [session|global] status 命令可以提供服務器狀態信息.其中的session來表現當前的連接的統計結果,global來表現自數據庫上次啟動至今的統計結果.默認是session級別的.
MYSQL學習

下面的例子：
MYSQL學習

show status like 'Com_%';
MYSQL學習

其中Com_XXX表現XXX語句所執行的次數.
MYSQL學習

重點注意：Com_select,Com_insert,Com_update,Com_delete通過這幾個參數,可以容易地了解到當前數據庫的應用是以插入更新為主還是以查詢操作為主,以及各類的SQL大致的執行比例是多少.
MYSQL學習

還有幾個常用的參數便于用戶了解數據庫的基本情況.
MYSQL學習

Connections：試圖連接MySQL服務器的次數
MYSQL學習

Uptime：服務器工作的時間(單位秒)
MYSQL學習

Slow_queries：慢查詢的次數 (默認是慢查詢時間10s)
MYSQL學習

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

如何查詢mysql的慢查詢時間
MYSQL學習

Show variables like 'long_query_time'; 

修改mysql 慢查詢時間
MYSQL學習

set long_query_time=2 

SQL語句優化-定位慢查詢
MYSQL學習

問題是： 如何從一個大項目中,迅速的定位執行速度慢的語句. (定位慢查詢)
MYSQL學習

首先我們了解mysql數據庫的一些運行狀態如何查詢(好比想知道當前mysql運行的時間/一共執行了多少次MYSQL學習

select/update/delete.. / 當前連接)
MYSQL學習

為了便于測試,我們構建一個大表(400 萬)-> 使用存儲過程構建
MYSQL學習

默認情況下,mysql認為10秒才是一個慢查詢.
MYSQL學習

修改mysql的慢查詢.
MYSQL學習

show variables like 'long_query_time' ; //可以顯示當前慢查詢時間
set long_query_time=1 ;//可以修改慢查詢時間 

構建大表->大表中記錄有要求, 記錄是不同才有用,不然測試效果和真實的相差大.創建:MYSQL學習

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; 

測試數據
MYSQL學習

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 $$
創建函數,該函數會返回一個指定長度的隨機字符串MYSQL學習

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 

創建一個存儲過程MYSQL學習

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秒中,就會統計到.
MYSQL學習

如果把慢查詢的sql記錄到我們的一個日志中
MYSQL學習

在默認情況下,低版本的mysql不會記錄慢查詢,必要在啟動mysql時候,指定記錄慢查詢才可以
MYSQL學習

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

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

該慢查詢日志會放在data目錄下[在mysql5.0這個版本中時放在 mysql安裝目錄/data/下],在 mysql5.5.19下是必要查看
MYSQL學習

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

在mysql5.6中,默認是啟動記錄慢查詢的,my.ini的所在目錄為：C:\ProgramData\MySQL\MySQL Server 5.6,其中有一個配置項
MYSQL學習

slow-query-log=1
MYSQL學習

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

bin\mysqld.exe - -safe-mode - -slow-query-log
MYSQL學習

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

[mysqld]
# The TCP/IP Port the MySQL Server will listen on
port=3306
slow-query-log 

通過慢查詢日志定位執行效率較低的SQL語句.慢查詢日志記錄了所有執行時間跨越long_query_time所設置的SQL語句.
MYSQL學習

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

為dept表添加數據MYSQL學習

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 ,效果很明顯慢]
MYSQL學習

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

如果帶上order by e.empno 速度就會更慢,有時會到1min多.
MYSQL學習

測試語句
MYSQL學習

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]來配置
MYSQL學習

SQL語句優化-explain闡發問題
MYSQL學習

Explain select * from emp where ename=“wsrcla”

會發生如下信息：
MYSQL學習

select_type:表現查詢的類型.
MYSQL學習

table:輸出成果集的表
MYSQL學習

type:表現表的連接類型
MYSQL學習

possible_keys:表現查詢時,可能使用的索引
MYSQL學習

key:表現實際使用的索引
MYSQL學習

key_len:索引字段的長度
MYSQL學習

rows:掃描出的行數(估算的行數)
MYSQL學習

Extra:執行情況的描述和闡明
MYSQL學習

MYSQL學習

explain select * from emp where ename='JKLOIP'
MYSQL學習

如果要測試Extra的filesort可以對上面的語句修改
MYSQL學習

explain select * from emp order by ename\G 

EXPLAIN詳解
MYSQL學習

id
MYSQL學習

SELECT辨認符.這是SELECT的查詢序列號
MYSQL學習

id 示例
MYSQL學習

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

select_type
MYSQL學習

PRIMARY :子查詢中最外層查詢
MYSQL學習

SUBQUERY : 子查詢內層第一個SELECT,成果不依賴于外部查詢
MYSQL學習

DEPENDENT SUBQUERY:子查詢內層第一個SELECT,依賴于外部查詢
MYSQL學習

UNION :UNION語句中第二個SELECT開始后面所有SELECT,
MYSQL學習

SIMPLE
MYSQL學習

UNION RESULT UNION 中合并成果
MYSQL學習

Table
MYSQL學習

顯示這一步所拜訪數據庫中表名稱
MYSQL學習

Type
MYSQL學習

對表拜訪方式
MYSQL學習

ALL：
MYSQL學習

SELECT * FROM emp \G

完整的表掃描 通常欠好
MYSQL學習

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

system：表僅有一行(=系統表).這是const聯接類型的一個特
MYSQL學習

const：表最多有一個匹配行
MYSQL學習

Possible_keys
MYSQL學習

該查詢可以利用的索引,如果沒有任何索引顯示 null
MYSQL學習

Key
MYSQL學習

Mysql 從 Possible_keys 所選擇使用索引
MYSQL學習

Rows
MYSQL學習

估算出成果集行數
MYSQL學習

Extra
MYSQL學習

查詢細節信息
MYSQL學習

No tables ：Query語句中使用FROM DUAL 或不含任何FROM子句
MYSQL學習

Using filesort ：當Query中包括 ORDER BY 操作,而且無法利用索引完成排序,
MYSQL學習

Impossible WHERE noticed after reading const tables: MYSQL Query Optimizer
MYSQL學習

通過收集統計信息弗成能存在結果
MYSQL學習

Using temporary：某些操作必需使用臨時表,常見 GROUP BY ; ORDER BY
MYSQL學習

Using where：不消讀取表中所有信息,僅通過索引就可以獲取所需數據;
MYSQL學習

以上所述是小編給大家介紹的Mysql數據庫性能優化一 ,下篇文章繼續給大家介紹mysql數據庫性能優化二,希望大家連續關注本站最新內容!MYSQL學習

維易PHP培訓學院每天發布《Mysql入門Mysql數據庫性能優化一》等實戰技能，PHP、MYSQL、LINUX、APP、JS,CSS全面培養人才。

轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/12374.html