Mysql性能優化一

来源:http://www.cnblogs.com/jiekzou/archive/2016/04/11/5371085.html
-Advertisement-
Play Games

mysql的性能優化無法一蹴而就，必須一步一步慢慢來，從各個方面進行優化，最終性能就會有大的提升。 Mysql資料庫的優化技術 對mysql優化是一個綜合性的技術，主要包括 表的設計合理化(符合3NF) 添加適當索引(index) [四種: 普通索引、主鍵索引、唯一索引unique、全文索引] 分表 ...


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：不用讀取表中所有信息，僅通過索引就可以獲取所需數據;


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 另類提示框
    我這裡說的提示框，就是當用戶將滑鼠移動到需要提示的圖標時，就會在這圖標的位置出現一個提示框了。 咦，那這有什麼好說的呢？ 如果你來實現這一效果，你會怎麼做呢？ 初步的做法嘛，就是利用PS製作一張提示框內容區域的png圖片和一張指向位置的箭頭png圖片，然後利用這張圖片作為提示背景，裡面輸入指定內容唄 ...
  • 框架設計—選擇器模塊
    一個禮拜沒動靜了，實在是懶惰。。 好了，不扯淡了，進入正題：框架封裝之選擇器模塊。 首先，我們為什麼要封裝框架？ 淺顯的文字不具有良好的說服性，來做幾個題目吧： 求一個數組所有項之和 2. 求數組中最大值 3. 獲取數組中指定值 ok,題目做到這就行了，我們可以看出每道題目中都用到了for迴圈，每次 ...
  • HTML5學習筆記二 HTML基礎
    一、HTML 標題 HTML 標題（Heading）是通過<h1> - <h6> 標簽來定義的. 二、HTML 段落 HTML 段落是通過標簽 <p> 來定義的. 三、HTML 鏈接 HTML 鏈接是通過標簽 <a> 來定義的.在 href 屬性中指定鏈接的地址。 四、HTML 圖像 HTML 圖像 ...
  • Array類型
    Array類型 也是 中常用類型之一，其特點是數組中的每一項都可以保存任何類型的數據，數組的大小可以動態調整。 創建數組 方式1：使用 構造函數 var books = new Array(); var books = new Array(20); //如果知道數組的大小，可以給構造函數傳遞該參數 ...
  • 《JavaScript面向對象編程指南》
    第一章、引言 1.5 面向對象的程式設計常用概念 對象 （名詞）：是指“事物”在程式設計語言中的表現形式。 這裡的事物可以是任何東西，我們可以看到它們具有某些明確特征，能執行某些動作。 這些對象特征就叫做屬性（形容詞），動作稱之為方法（動詞）。 類： 實際上就是對象的設計藍圖或製作配方。類更多的是一 ...
  • Atitit.跨語言異常轉換機制 java c# php到js的異常轉換
    Atitit.跨語言異常轉換機制 java c# php到js的異常轉換 1. bizEx 直接抓取，然後js catchEX1 2. Chkec runtimeEx1 3. Other異常。。Js convet 2 js err,then throw ...2 1. bizEx 直接抓取，然後js ...
  • atitit.React   優缺點 相比angular react是最靠譜的web ui組件化方案了
    atitit.React 優缺點 相比angular react是最靠譜的web ui組件化方案了 1. React的組件化才是web ui部件的正確方向1 1.1. 組件化集成html ,css,js自我包含一體化，方便復用。1 1.2. 相比angular。Js方便好用1 2. React的問題 ...
  • 一、oracle基本sql語句和函數詳解
    一、 數據定義語言(ddl) 數據定義語言ddl(data definition language)用於改變資料庫結構，包括創建、更改和刪除資料庫對象。 用於操縱表結構的數據定義語言命令有： create table alter table truncate table drop table eg、 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類