mysql優化sql語句

❶ sql優化（二）

SQL優化一： sql優化（一）

上片文章已經詳細介紹了explain各個欄位的含義，以及什麼情況應該建立索引，什麼情況不需要建立索引以及sql語句性能的判斷依據，接下來我介紹下如何合理的建立索引。

sql語句：select id，author_id from article where category_id = 1 and comments>1 order by views desc limit 1;

分析：首先我們根據where後面的條件建立符合索引，然後根據order by後面的欄位建立索引，因此建立索引idx_article_ccv，即以（category_id，comments，views）數據列建立復合索引，但由於comments是一個范圍，按照BTree索引的原理，先排序category_id,如果遇到相同的category_id則再排序comments，如果遇到相同的comments則再排序views，又因為comments欄位在復合索引里處於中間位置，而comments>1是一個條件（是一個范圍值），在復合索引的一個范圍值的數據列後面的索引全部失效，mysql無法利用索引再對後面的views部分進行檢索，也就是說views無法按照索引排序，所以explain下此sql語句，type為range，extra使用的是Using filesort，這是比較糟糕的。所以我們放棄comments這個范圍欄位，建立索引idx_article_cv，即以（category_id，views）數據列建立復合索引，explain 此sql，type變成了ref，extra的using filesort也變成了using index，這就變得好多了。

索引：idx_article_cv，即以（category_id，views）數據列建立復合索引

前段時間做了一個銷售精細化項目，是公司crm項目的一個大模塊，大致就是為銷售人員制定指標，實現銷售目標從區域到團到業務員到客戶，實時跟蹤業務員所負責客戶的下單量的情況。這就存在許多關聯關系，區域-團，團-業務員，業務員-客戶，這使得sql常常需要關聯多張表。

sql語句：SELECT

tu.fuserid,

tu.faccount,

tu.fphone,

tu.fcertificationtype,

tu.fcertificatename,

tu.fkeyarea,

tu.fkeyareatext,

DATE_FORMAT(tcr.fupdatetime,'%Y-%m-%d %H:%i:%s') as fupdatetime,

tag.forggroupid,

tag.forggroupname,

tug.forguserid,

tug.fusername,

tug.fuserphone,

tag.fcitycode

FROM t_finedt_user AS tu

LEFT JOIN t_finedt_customer_relation AS tcr

ON tu.fuserid = tcr.fuserid

LEFT JOIN t_finedt_usergroup AS tug

ON tcr.forguserid = tug.forguserid

and tcr.forggroupid = tug.forggroupid

LEFT JOIN t_finedt_areagroup AS tag

ON tug.forggroupid = tag.forggroupid

where tu.fkeyarea=? and tu.fuserid=? and tug.forggroupid = ?

分析：上面的sql是左連接，左邊的表一定是全表查詢，所以要建立右邊表對應關聯欄位的索引，在表t_finedt_user上建立tu_fuserid_fkeyarea索引，即以(fuserid，fkeyarea)欄位建立索引，在表t_finedt_customer_relation 上建立tcr_forguserid_forggroupid索引，即以（forguserid，forggroupid）欄位建立索引，在表t_finedt_usergroup 上建立tug_forguserid_forggroupid索引，即以（forguserid,forggroupid)欄位建立索引，在表t_finedt_areagroup上建立tag_forggroupid索引，即以（forggroupid)欄位建立索引。建立索引後，sql查詢速度明顯快了很多

索引：tcr_forguserid_forggroupid，tu_fuserid_fkeyarea，tug_forguserid_forggroupid，tag_forggroupid

1、盡可能減少join語句中的NestedLoop的循環次數，永遠用小結果集驅動大結果集

2、優先優化NestedLoop的內層循環

3、保證join語句總被驅動表上的join欄位已經被索引

4、當無法保證被驅動表join條件欄位被索引，且內存資源充足的前提下，不要太吝嗇joinBuffer的設置

1、全值匹配我最愛

2、最佳左前綴原則——如果索引了多列，要遵守最左前綴原則，指的是查詢從索引的最左前列開始並且不跳過索引中的列

3、並在索引列上做任何操作（計算、函數、自動or手動類型轉換），這些會導致索引失效而轉向全表掃描

4、存儲引擎不能使用索引中范圍條件右邊的列，范圍之後的索引全失效

5、盡量使用覆蓋索引（之訪問索引的查詢（索引列和查詢的列一致）），減少select *

6、mysql在使用不等於（！=、>、<）的時候無法使用索引會導致全表掃描。

7、is null、is not null也無法使用索引。

8、like以通配符開頭（"%abc.."），mysql索引失效也會變成全表掃描的操作。

9、字元串不加單引號也會引起索引失效

10、少用or，用它來連接時會索引失效。

1、對於單值索引，盡量選擇針對當前query過濾性更好的索引

2、在選擇組合索引的時候，當前query中過濾性最好的欄位在索引欄位順序中，位置越靠前越好

3、在選擇組合索引的時候，盡量選擇盡可能包含當前query中的where字句中更多欄位的索引

4、盡可能通過分析統計信息和調整query的寫法來達到選擇合適索引的目的。

全值匹配我最愛，最左前綴要遵守

帶頭大哥不能死，中間兄弟不能斷

索引列上少計算，范圍之後全失效

like百分寫最右，覆蓋索引不寫里

不等空值還有or，索引失效要少用

var引號不可丟，sql高級也不難

❷ MySQL資料庫優化都包括哪些項目

此文章主要向大家介紹的是MySQL資料庫優化 其中還包括MySQL資料庫的性能優化 常用的SQL語句的優化以及MySQL資料庫對INSERT語句進行優化的實際操作方案的描述 望你會有所收獲

MySQL InnoDB 的性能問題討論

MySQL性能優化

InnoDB delete from xxx速度暴慢原因

推薦圈子: mysql研究

更多相關推薦 定期分析表和檢查表

分析表的語法如下

引用

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb _name[ tbl_name]

以上語句用於分析和存儲表的關鍵字分布 分析的結果將可以使得系統得到准確的統計信息 使得SQL能夠生成正確的執行計劃 如果用戶感覺實際執行計劃並不是預期的執行計劃 執行一次分析表可能會解決問題 在分析期間 使用一個讀取鎖定對表進行鎖定 這對於MyISAM DBD和InnoDB表有作用

例如分析一個數據表

引用

*** yze table table_name

檢查表的語法如下

引用

CHECK TABLE tb _name[ tbl_name] [option] option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

檢查表的作用是檢查一個或多個表是否有錯誤 CHECK TABLE 對MyISAM 和 InnoDB表有作用 對於MyISAM表 關鍵字統計數據被更新

CHECK TABLE 也可以檢查視圖是否有錯誤 比如在視圖定義中被引用的表不存在

定期優化表

MySQL資料庫優化表的語法如下

引用

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb _name [ tbl_name]

如果刪除了表的一大部分 或者如果已經對含有可變長度行的表(含有 VARCHAR BLOB或TEXT列的敗宏表)進行更多更銀枯畝改 則應使用OPTIMIZE TABLE命令來進行表優化 這個命令可以將表中的空間碎片進行合並 並且可以消除由於刪除或者更新造成的空間浪費 但OPTIMIZE TABLE 命令只對MyISAM BDB 和InnoDB表起作用

例如 optimize table table_name

鋒森注意 *** yze check optimize執行期間將對表進行鎖定 因此一定注意要在資料庫不繁忙的時候執行相關的操作

常用的SQL優化

我們在開發的時候常常用到的SQL語句 無非是INSERT GROUPBY等等 對於這些SQL語句 我們怎麼進行優化？

大批量插入數據

當用load命令導入數據的時候 適當的設置可以提高導入的速度

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

引用

ALTER TABLE tb _name DISABLE KEYS;

loading the data

ALTER TABLE tb _name ENABLE KEYS;

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

對於導入大量的數據到一個空的MyISAM表時 默認就是先導入數據然後才創建索引的 索引不用進行設置

引用

load data infile /home/mysql/text_txt into table text

對於InnoDB類型的表 這種方式不能提高導入數據的效率 但也有幾種針對InnoDB類型的表進行MySQL資料庫優化的方式

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

在導入數據前執行 SET UNIQUE_CHECKS= 關閉唯一性校驗 在導入結束後執行SET UNIQUE_CHECKS= 恢復唯一性校驗 可以提高導入的效率

如果應用使用自動提交的方式 建議在導入前執行SET AUTOMIT= 關閉自動提交 導入結束後執行SET AUTOMIT= 打開自動提交 也可以提高導入效率

MySQL資料庫優化INSERT語句

當進行數據INSERT的時候 可以考慮採用以下幾種方式進行優化

如果同時從一個客戶插入很多行 盡量使用多個值表的INSERT語句 這種方式將大大縮短客戶端與資料庫的鏈接 關閉等消耗 使得效率比分開執行的單個INSERT語句快

例如

insert into test values( )

insert into test values( )

insert into test values( )

將上面三句改為:insert into test values( ) ( ) ( )

如果從不同客戶插入很多行 能通過使用INSERT DELAYED 語句得到更高的速度

DELAYED 的含義是讓INSERT 語句馬上執行 其實數據都被放在內存的隊列中 並沒有真正寫入磁碟 這比每條語句分別插入要快得多 LOW_PRIORITY剛好相反 在所有其他用戶對表的讀寫完後才進行插入

將索引文件和數據文件分在不同的磁碟上存放

如果進行批量插入 可以增加bulk_insert_buffer_size變數值的方法來提高速度 但是 這只能對於MyISAM表使用

當從一個文本文件中裝載一個表時 使用LOAD DATA INFILE 這通常比使用很多insert語句快 倍左右