MySQL查詢優化

1 SQL優化

show status

了解各種SQL的執行效率：

# 查看本session的sql執行效率 show status like 'Com_%'; # 查看全局的統計結果 SHOW GLOBAL STATUS LIKE 'Com_%' # 查看服務器的狀態 show global status;

1

2

3

4

5

6

7

8

結果：

Com_select:執行select操作的次數，一次查詢只累加1

Com_insert:執行insert操作的次數，對于批量插入的insert操作，只累加一次

Com_update

執行update操作的次數

Com_delete

執行delete的次數

上面的參數是對所有存儲引擎的表進行累計，下面參數是針對InnoDB存儲引擎的，累加的算法也略有不同

Innodb_rows_read

SELECT查詢返回的行數

Innodb_rows_insered

執行inser操作插入的行數

Innodb_rows_updated

執行UPDATE操作更新的行數

Innodb_rows_deleted

執行DELETE操作刪除的行數

通過上述參數可以了解當前DB應用是插入更新為主還是查詢為主，以及各類的SQL執行比例。

對于更新操作的計算，是對執行次數的計數，無論提交還是回滾都會進行累加對于事務形的應用，通過Com_commit和Com_rollback可以了解事務提交和回滾的情況，對于回滾操作非常頻繁的數據庫，可能意味著應用編寫存在的問題

Connections:試圖連接MySql服務器的次數

Uptime：服務器工作時間

Slow_queries:慢查詢的次數

2 定位執行效率低的SQL語句

慢查詢日志

--log-show-queries[=file_name]選項去啟動

mysqlId寫一個包含所有執行時間超過long_querty_time秒的sql語句的日志文件

show processlist

慢查詢日志在查詢結束后才記錄，所以在應用反應執行效率出現問題的時候查詢慢查詢日志并不能定位問題

可以使用show processlist命令查看當前Mysql在進行的線程，包括線程的狀態，是否鎖表等，可以實時查看SQL的執行情況，同時對一些鎖表進行優化。

3 通過explain分析執行SQL執行計劃

4 簡單的優化方法

本語句可以用于分析和存儲表的關鍵字分布，分析的結果可以使得系統得到準確的統計信息使得sql，能夠生成正確的執行計劃。如果用戶感覺實際執行計劃并不預期的執行計劃，執行一次分析表可能會解決問題

analyze table payments;

1

檢查表：檢查表：檢查表的作用是檢查一個表或多個表是否有錯誤,也可以檢查視圖是否錯誤

check table payment;

1

優化表:如果刪除了表的一大部分，或者如果已經對可變長度的行表（含varchar、blob、text列）的表進行改動，則使用optimize 進行表優化，這個命令可以使表中的空間碎片進行合并、并且可以消除由于刪除或者更新造成的空間浪費

optimize table payment;

1

對于innodb引擎的表，可以通過設置innodb_file_per_taable參數，設置InnoDb為獨立表空間模式，這樣每個數據庫的每個表都會生成一個獨立的idb文件，用于存儲表的數據和索引，可以一定程度減少Innodb表的空間回收問題,另外，在刪除大量數據后，Innodb表可以通過alter table但是不銹鋼引擎方式來回收不用的空間

alter table payment enigine=innodb;

1

ANALYZE,CHECK,OPTIMIZE,ALTER TABLE執行期間都是對表進行鎖定，因此要在數據庫不頻繁的時候執行相關的操作

拆分表： 分區將數據在物理上分隔開，不同分區的數據可以制定保存在處于不同磁盤上的數據文件里。

這樣，當對這個表進行查詢時，只需要在表分區中進行掃描，而不必進行全表掃描，明顯縮短了查詢時間，

另外處于不同磁盤的分區也將對這個表的數據傳輸分散在不同的磁盤I/O，一個精心設置的分區可以將數據傳輸對磁盤I/O競爭均勻地分散開。

對數據量大的時時表可采取此方法。可按月自動建表分區。

存儲過程與觸發器的區別

兩者唯一的區別是觸發器不能用EXECUTE語句調用，而是在用戶執行Transact-SQL語句時自動觸發（激活）執行。

觸發器是在一個修改了指定表中的數據時執行的存儲過程。

通常通過創建觸發器來強制實現不同表中的邏輯相關數據的引用完整性和一致性。

觸發器不同于存儲過程，觸發器主要是通過事件執行觸發而被執行的，

存儲過程可以通過存儲過程名稱名字而直接調用。

當對某一表進行諸如UPDATE、INSERT、DELETE這些操作時，SQLSERVER就會自動執行觸發器所定義的SQL語句，從而確保對數據的處理必須符合這些SQL語句所定義的規則。

數據庫優化方向

（1）、根據服務層面：配置mysql性能優化參數

（2）、從系統層面增強mysql的性能：優化數據表結構、字段類型、字段索引、分表，分庫、讀寫分離等等

（3）、從數據庫層面增強性能：優化SQL語句，合理使用字段索引。

（4）、從代碼層面增強性能：使用緩存和NoSQL數據庫方式存儲，如MongoDB/Memcached/Redis來緩解高并發下數據庫查詢的壓力。

（5）、減少數據庫操作次數，盡量使用數據庫訪問驅動的批處理方法。

（6）、不常使用的數據遷移備份，避免每次都在海量數據中去檢索。

（7）、提升數據庫服務器硬件配置，或者搭建數據庫集群。

（8）、編程手段防止SQL注入：使用JDBC PreparedStatement按位插入或查詢；正則表達式過濾（非法字符串過濾）

當用load導入數據，適當的設置可以提供導入的速度

對于MyISAM存儲引擎的表，可以通過以下方式快速導入大量的數據

alter table tab_name disable keys; loading the data alter table tab_name disable keys;

1

2

3

disable keys和enable keys 用來打開或者關閉MyISAM表非索引的更新。在導入大量的數據到一個非空的MyISAM表，通過設置這兩個命令，可以提高導入的效率

對于Innodb類型的表不能使用上面的方式提高導入效率

因為Innodb類型的表是按照主鍵的順序保存，所有將導入的數據按照主鍵的順序排序，可以有效地提高導入數據的效率

在導入數據強執行SET UNIQUE_CHECKS=0，關閉唯一性校驗，在導入結束后執行SET UNIQUE_CHECKS=1.恢復唯一性校驗，可以提高導入的效率，如果應用使用自動提交的方式，建議在導入前執行SET AUTOCOMMIT=0時，關閉自動提交，導入結束后再執行SET AUTOCOMMIT=1，打開自動提交，也可以提高導入的效率

如果同時從一個客戶端插入很多行，應盡量使用多個值表的insert語句，這種方式將大大縮減客戶端與數據庫之間的連接、關閉等消耗，使得效率比分開執行的單個insert語句快（大部分情況下，使用多個值表的insert語句那比單個insert語句快上好幾倍）。

insert into test values(1,2),(1,3)...

1

如果從不同客戶插入很多行，可以通過使用insert delayed語句提高更高的速度，delayed的含義是讓insert語句馬上執行，其實數據都被放到內存的隊列中，并沒有真正寫入磁盤，這比每條語句分別插入要快的多；LOW_PRIORITY剛好相反，在所有其他用戶對表的讀寫完成后才可以進行

將索引文件和數據文件分在不同的磁盤上存放（利用建表中的選項）

如果進行批量插入，可以通過增加bulk_insert_buffer_size變量值的方法來通過速度，但是，這只能對MyISAM表使用。

當從一個文本文件裝載一個表時，使用LOAD DATA INFILE。這通常比使用很多INSERT語句塊快20倍

第一種通過有序排序索引順序掃描，這種方式在使用explain分析查詢的時候顯示為Using Index，不需要額外的排序，操作效率較高-innodb引擎

explain select customer_id from customer order by store_id;

1

第二張通過返回數據進行排序，也就是通常說的Filesort排序，所有不是通過索引直接返回排序結果的排序豆角Filesort排序。Filesort并不代表通過磁盤文件進行排序，而只是說明進行了一個排序操作，至于排序操作是否進行了磁盤文件或臨時表等，則取決于MySql服務器對排序參數的設置和需要排序數據的大小-myshim引擎

explain select * from customer order by store_id;

1

Filesort是通過相應的排序算法，將取得的數據在sort_buffer_size系統變量設置的內存排序區進行排序，如果內存裝載不下，它會將磁盤上的數據進行分塊，再對各個數據塊進行排序，然后將各個塊合并成有序的結果集。sort_buffer_size設置的排序區是每個線程獨占的，所有同一個時刻，MySql存在多個sort buffer排序區

優化目標:盡量減少額外的排序，通過索引直接返回有序數據.where和ordery by 使用相同的索引，并且order by的順序和索引順序相同，并且order by的字段都是升序或者都是降序。否則肯定需要額外的排序操作，這樣就會出現filesort

若查詢包括group by，但想要避免排序結果的消耗，可指定group by null。

子查詢

explain select * from customer where customer_id not in(select customer_id from payment)

1

2

可以被更有效率的連接替代：

explain select * from customer a left join payment b on a.customer_id=b.customer_id where b.customer id is null

1

2

連接之所用更有效率是因為MySQL不需要在內存中創建臨時表來完成這個邏輯上需要兩步的查詢工作。

一般分頁查詢，通過創建覆蓋索引能夠比較好地提高性能。

一個場景是"limit 1000,20"，此時MySQL排序出前1020條數據后，僅需要返回第1001~1020條記錄，前1000條數據都被拋棄，查詢和排序代價非常高。

優化

可以增加一個字段last_page_record.記錄上一頁和最后一頁的編號

通過

explain select ...where last_page_record<... desc limt ..

1

但若排序字段出現大量重復字段，不適用這種方式優化。

MySQL常用技巧

使用

select 'abcdefg' regexp '^a'; .....

1

2

隨機抽取某些行

select * from categrory order by rand() limit 5;

1

使用group by的with rollup可以檢索更多分組聚合信息：

select date_from(payment_date, '%Y-%M'), staff_id, sum(amount) from payment group by date_formate(payment_date, '%Y-%M'), staff_id;

1

2

3

使用GROUP BY語句和BIT_AND、BIT_OR函數完成統計工作，這兩個函數的一般用途就是做數值之間的邏輯。

優化數據庫對象

優化表類型

表需要使用何種數據類型工具應用來判斷，雖然考慮字段的長度會有一定的冗余，但是不推薦讓很多字段都留有大量的冗余，這樣既浪費磁盤的存儲空間，同時在應用操作時也浪費物理內存MySQL，可以使用函數procedure analyse對當前的表進行分析

select * from tb1_name procedure analyse(); select * from tb2_name procedure analyse(16,256);

1

2

輸出的每一類信息都對數據表中的列的數據類型提出優化建議。第二語句告訴procedure anaylse不要為那些包含的值多余16個或256個字節的enum類型提出建議，如果沒有這個限制，輸出的信息可能很長。

ENUM定義通常很難閱讀，通過輸出信息，可以將表中的部分字段修改為效率更高的字段。

重置拆分，把主碼和一些列放到一個表，然后把住碼和另外的列放到另一個表， 好處可以將常用的列放在一起，不常用的列放在一起，使得數據行變少，一個數據頁可以存放更多的數據，在查詢時會減少I/O次數，缺點：管理冗余，查詢所有數據需要用join操作

水平拆分。根據一列或多列數據把數據行放到兩個獨立的表中：水平拆分會給應用增加復雜度，它通常在查詢時需要多個表名，查詢所有數據需要UNION操作，缺點：只要索引關鍵字不大，則在索引查詢時，表中增加了2-3倍的數據量，查詢時也增加了讀一個索引的磁盤次數，所有說拆分要考慮數據量的增長速度。常用場景

表很大，分割后可以降低在查詢時需要讀的數據和索引的頁數，同時也降低了索引的層數，提高查詢速度

表中的數據本來就有獨立性，例如表中分別記錄各個地區的數據或者不同時期的數據，特別是有些數據常用，而有些數據不常用

需要把數據存放在多個介質上：如賬單：最近三個月數據存在一個表中，3個月之前的數據存放在另一個表，成功一年的可以存儲在單獨的存儲介質中。

數據庫設計時需要瞞住規范化，但是規范化程度越高，產生的關系就越多，關系越多直接結果就是表直接的連接操作越頻繁，而表連接的操作是性能較低的操作，直接影響到查詢的數據。

反規范化的好處在于降低連接操作的需求，降低外碼和索引的數目，還可以減少表的樹木，相應帶來的問題可能出現數據的完整性問題。加快查詢速度，但是降低修改速度。好的索引和其他方法經常能夠解決性能問題，而不必采用反規范這種方法

采用的反規范化技術

增加冗余列：指在多個表中具有相同的列，它常用來在查詢時避免連接操作

增加派生列：指增加的列來自其他表中的數據，由其他表中的數據經過計算生成。增加的派生列其他作業是在查詢時減少連接操作，避免使用集函數

重新組表：指如果許多用戶需要查看兩個表連接出來的結果數據，則把這兩個表查詢組成一個表來減少連接而提高性能

分割表

維護數據的完整性

批處理維護是指對復制列或派生列的修改積累一定的時間后，運行一批處理作業或修改存儲過程對復制或派生列進行修改，這只能對實時性要求不高的情況下使用

數據的完整性也可由應用邏輯來實現，這就要求必須在同一事務中對所有涉及的表進行增、刪、改操作。用應用邏輯來實現數據的完整性風險較大，因為同一邏輯必須在所有的應用中使用和維護，容易遺漏。特別是在需求變化時，不易于維護

使用觸發器，對數據的任何修改立即觸發對復制列或者派生列的相應修改，觸發器是實時的，而且相應的處理邏輯只在一個地方出現，易于維護，一般來說，是解決這類問題比較好的方法

對于數據量較大的表，在其上進行統計查詢通常會效率很低，并且還要考慮統計查詢是

否會對在線的應用產生負面影響。通常在這種情況下，使用中間表可以提高統計查詢的效率

session 表記錄了客戶每天的消費記錄，表結構如下：

CREATE TABLE session ( cust_id varchar(10) , --客戶編號 cust_amount DECIMAL(16,2), --客戶消費金額 cust_date DATE, --客戶消費時間 cust_ip varchar(20) –客戶IP 地址 )

1

2

3

4

5

6

由于每天都會產生大量的客戶消費記錄,所以session 表的數據量很大,現在業務部門有

一具體的需求：希望了解最近一周客戶的消費總金額和近一周每天不同時段用戶的消費總金

額。針對這一需求我們通過2 種方法來得出業務部門想要的結果。

方法1：在session 表上直接進行統計,得出想要的結果。

select sum(cust_amount) from session where cust_date>adddate(now(),-7);

1

方法2：創建中間表tmp_session，表結構和源表結構完全相同。

CREATE TABLE tmp_session ( cust_id varchar(10) , --客戶編號 cust_amount DECIMAL(16,2), --客戶消費金額 cust_date DATE, --客戶消費時間 cust_ip varchar(20) –客戶IP 地址 ) ;

1

2

3

4

5

6

insert into tmp_session select * from session where cust_date>adddate(now(),-7);

1

轉移要統計的數據到中間表,然后在中間表上進行統計，得出想要的結果。

在中間表上給出統計結果更為合適,原因是源數據表(session 表)

cust_date 字段沒有索引并且源表的數據量較大，所以在按時間進行分時段統計時效率

很低，這時可以在中間表上對cust_date 字段創建單獨的索引來提高統計查詢的速度。

中間表在統計查詢中經常會用到，其優點如下:

中間表復制源表部分數據，并且與源表相“隔離”，在中間表上做統計查詢不

會對在線應用產生負面影響.

中間表上可以靈活的添加索引或增加臨時用的新字段,從而達到提高統計查詢

效率和輔助統計查詢作用。

MySQL SQL

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。