當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

sql语句优化总结 mysql_MySQL-SQL优化总结

發(fā)布時間：2025/3/12 数据库 30 豆豆

生活随笔收集整理的這篇文章主要介紹了 sql语句优化总结 mysql_MySQL-SQL优化总结小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

轉(zhuǎn)載:

https://blog.csdn.net/qq_39390545/article/details/107020686

理解SQL優(yōu)化原理，首先要搞清楚SQL執(zhí)行順序：

SELECT語句 - 語法順序:

SELECT

DISTINCT

FROM

JOIN

WHERE

GROUP BY

HAVING

ORDER BY

LIMIT

SELECT語句 - 執(zhí)行順序：

FROM

# 選取表，將多個表數(shù)據(jù)通過笛卡爾積變成一個表。

# 對笛卡爾積的虛表進行篩選

JOIN

# 指定join，用于添加數(shù)據(jù)到on之后的虛表中，例如left join會將左表的剩余數(shù)據(jù)添加到虛表中

WHERE

# 對上述虛表進行篩選

GROUP BY

# 分組

# 用于having子句進行判斷，在書寫上這類聚合函數(shù)是寫在having判斷里面的

HAVING

# 對分組后的結(jié)果進行聚合篩選

SELECT

# 返回的單列必須在group by子句中，聚合函數(shù)除外

DISTINCT

# 數(shù)據(jù)除重

ORDER BY

# 排序

LIMIT

SQL優(yōu)化策略

聲明：以下SQL優(yōu)化策略適用于數(shù)據(jù)量較大的場景下，如果數(shù)據(jù)量較小，沒必要以此為準，以免畫蛇添足。

一、避免不走索引的場景

1. 盡量避免在字段開頭模糊查詢，會導致數(shù)據(jù)庫引擎放棄索引進行全表掃描。如下：

SELECT * FROM t WHERE username LIKE '%陳%'

優(yōu)化方式：盡量在字段后面使用模糊查詢。如下：

SELECT * FROM t WHERE username LIKE '陳%'

如果需求是要在前面使用模糊查詢，

使用MySQL內(nèi)置函數(shù)INSTR(str,substr) 來匹配，作用類似于java中的indexOf()，查詢字符串出現(xiàn)的角標位置

使用FullText全文索引，用match against 檢索

數(shù)據(jù)量較大的情況，建議引用ElasticSearch、solr，億級數(shù)據(jù)量檢索速度秒級

當表數(shù)據(jù)量較少(幾千條兒那種)，別整花里胡哨的，直接用like '%xx%'

2. 盡量避免使用in 和not in，會導致引擎走全表掃描。如下：

SELECT * FROM t WHERE id IN (2,3)

優(yōu)化方式：如果是連續(xù)數(shù)值，可以用between代替。如下：

SELECT * FROM t WHERE id BETWEEN 2 AND 3

如果是子查詢，可以用exists代替。如下:

--不走索引select * from A where A.id in (select id fromB);--走索引select * from A where exists (select * from B where B.id = A.id);

3. 盡量避免使用 or，會導致數(shù)據(jù)庫引擎放棄索引進行全表掃描。如下：

SELECT * FROM t WHERE id = 1 OR id = 3

優(yōu)化方式：可以用union代替or。如下：

SELECT * FROM t WHERE id = 1UNION

SELECT* FROM t WHERE id = 3

4. 盡量避免進行null值的判斷，會導致數(shù)據(jù)庫引擎放棄索引進行全表掃描。如下：

SELECT * FROM t WHERE score IS NULL

優(yōu)化方式：可以給字段添加默認值0，對0值進行判斷。如下：

SELECT * FROM t WHERE score = 0

5.盡量避免在where條件中等號的左側(cè)進行表達式、函數(shù)操作，會導致數(shù)據(jù)庫引擎放棄索引進行全表掃描。

可以將表達式、函數(shù)操作移動到等號右側(cè)。如下:

--全表掃描

SELECT* FROM T WHERE score/10 = 9

--走索引

SELECT* FROM T WHERE score = 10*9

6. 當數(shù)據(jù)量大時，避免使用where 1=1的條件。通常為了方便拼裝查詢條件，我們會默認使用該條件，數(shù)據(jù)庫引擎會放棄索引進行全表掃描。如下：

SELECT username, age, sex FROM T WHERE 1=1

優(yōu)化方式：用代碼拼裝sql時進行判斷，沒 where 條件就去掉 where，有where條件就加 and。

7. 查詢條件不能用 <> 或者 !=

使用索引列作為條件進行查詢時，需要避免使用<>或者!=等判斷條件。如確實業(yè)務需要，使用到不等于符號，需要在重新評估索引建立，避免在此字段上建立索引，改由查詢條件中其他索引字段代替。

8. where條件僅包含復合索引非前置列

如下：復合(聯(lián)合)索引包含key_part1，key_part2，key_part3三列，但SQL語句沒有包含索引前置列"key_part1"，按照MySQL聯(lián)合索引的最左匹配原則，不會走聯(lián)合索引。

select col1 from table where key_part2=1 and key_part3=2

9. 隱式類型轉(zhuǎn)換造成不使用索引

如下SQL語句由于索引對列類型為varchar，但給定的值為數(shù)值，涉及隱式類型轉(zhuǎn)換，造成不能正確走索引。

select col1 from table where col_varchar=123;

10. order by 條件要與where中條件一致，否則order by不會利用索引進行排序

--不走age索引

SELECT*FROM t order by age;--走age索引

SELECT* FROM t where age > 0 order by age;

對于上面的語句，數(shù)據(jù)庫的處理順序是：

第一步：根據(jù)where條件和統(tǒng)計信息生成執(zhí)行計劃，得到數(shù)據(jù)

第二步：將得到的數(shù)據(jù)排序。當執(zhí)行處理數(shù)據(jù)(order by)時，數(shù)據(jù)庫會先查看第一步的執(zhí)行計劃，看order by 的字段是否在執(zhí)行計劃中利用了索引。如果是，則可以利用索引順序而直接取得已經(jīng)排好序的數(shù)據(jù)。如果不是，則重新進行排序操作。

第三步：返回排序后的數(shù)據(jù)。

當order by 中的字段出現(xiàn)在where條件中時，才會利用索引而不再二次排序，更準確的說，order by 中的字段在執(zhí)行計劃中利用了索引時，不用排序操作。

這個結(jié)論不僅對order by有效，對其他需要排序的操作也有效。比如group by 、union 、distinct等。

11. 正確使用hint優(yōu)化語句

MySQL中可以使用hint指定優(yōu)化器在執(zhí)行時選擇或忽略特定的索引。一般而言，處于版本變更帶來的表結(jié)構(gòu)索引變化，更建議避免使用hint，而是通過Analyze table多收集統(tǒng)計信息。但在特定場合下，指定hint可以排除其他索引干擾而指定更優(yōu)的執(zhí)行計劃。

USE INDEX 在你查詢語句中表名的后面，添加 USE INDEX 來提供希望 MySQL 去參考的索引列表，就可以讓 MySQL 不再考慮其他可用的索引。例子: SELECT col1 FROM table USE INDEX (mod_time, name)...

IGNORE INDEX 如果只是單純的想讓 MySQL 忽略一個或者多個索引，可以使用 IGNORE INDEX 作為 Hint。例子: SELECT col1 FROM table IGNORE INDEX (priority) ...

FORCE INDEX 為強制 MySQL 使用一個特定的索引，可在查詢中使用FORCE INDEX 作為Hint。例子: SELECT col1 FROM table FORCE INDEX (mod_time) ...

在查詢的時候，數(shù)據(jù)庫系統(tǒng)會自動分析查詢語句，并選擇一個最合適的索引。但是很多時候，數(shù)據(jù)庫系統(tǒng)的查詢優(yōu)化器并不一定總是能使用最優(yōu)索引。如果我們知道如何選擇索引，可以使用FORCE INDEX強制查詢使用指定的索引。例如:

SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;

二、SELECT語句其他優(yōu)化

1. 避免出現(xiàn)select *

首先，select * 操作在任何類型數(shù)據(jù)庫中都不是一個好的SQL編寫習慣。

使用select * 取出全部列，會讓優(yōu)化器無法完成索引覆蓋掃描這類優(yōu)化，會影響優(yōu)化器對執(zhí)行計劃的選擇，也會增加網(wǎng)絡(luò)帶寬消耗，更會帶來額外的I/O,內(nèi)存和CPU消耗。

2. 避免出現(xiàn)不確定結(jié)果的函數(shù)

特定針對主從復制這類業(yè)務場景。由于原理上從庫復制的是主庫執(zhí)行的語句，使用如now()、rand()、sysdate()、current_user()等不確定結(jié)果的函數(shù)很容易導致主庫與從庫相應的數(shù)據(jù)不一致。另外不確定值的函數(shù),產(chǎn)生的SQL語句無法利用query cache。

3.多表關(guān)聯(lián)查詢時，小表在前，大表在后。

在MySQL中，執(zhí)行 from 后的表關(guān)聯(lián)查詢是從左往右執(zhí)行的(Oracle相反)，第一張表會涉及到全表掃描，所以將小表放在前面，先掃小表，掃描快效率較高，在掃描后面的大表，或許只掃描大表的前100行就符合返回條件并return了。

例如：表1有50條數(shù)據(jù)，表2有30億條數(shù)據(jù)；如果全表掃描表2，你品, 你細品!

4. 使用表的別名當在SQL語句中連接多個表時，請使用表的別名并把別名前綴于每個列名上。這樣就可以減少解析的時間并減少哪些友列名歧義引起的語法錯誤。

5. 用where字句替換HAVING字句

避免使用HAVING字句，因為HAVING只會在檢索出所有記錄之后才對結(jié)果集進行過濾，而where則是在聚合前刷選記錄，如果能通過where字句限制記錄的數(shù)目，那就能減少這方面的開銷。HAVING中的條件一般用于聚合函數(shù)的過濾，除此之外，應該將條件寫在where字句中。

where和having的區(qū)別：where后面不能使用組函數(shù)

6.調(diào)整Where字句中的連接順序

MySQL采用從左往右，自上而下的順序解析where子句。根據(jù)這個原理，應將過濾數(shù)據(jù)多的條件往前放，最快速度縮小結(jié)果集。

三、增刪改 DML 語句優(yōu)化

1. 大批量插入數(shù)據(jù)

如果同時執(zhí)行大量的插入，建議使用多個值的INSERT語句(方法二)。這比使用分開INSERT語句快(方法一)，一般情況下批量插入效率有幾倍的差別。

方法一:

insert into T values(1,2);

insert into T values(1,3);

insert into T values(1,4);

方法二：

Insert into T values(1,2),(1,3),(1,4);

選擇后一種方法的原因有三:

減少SQL語句解析的操作，MySQL沒有類似Oracle的share pool，采用方法二，只需要解析一次就能進行數(shù)據(jù)的插入操作；

在特定場景可以減少對DB連接次數(shù)

SQL語句較短，可以減少網(wǎng)絡(luò)傳輸?shù)腎O

2. 適當使用commit

適當使用commit可以釋放事務占用的資源而減少消耗，commit后能釋放的資源如下：

事務占用的undo數(shù)據(jù)塊；

事務在redo log中記錄的數(shù)據(jù)塊；

釋放事務施加的，減少鎖爭用影響性能。特別是在需要使用delete刪除大量數(shù)據(jù)的時候，必須分解刪除量并定期commit。

3. 避免重復查詢更新的數(shù)據(jù)

針對業(yè)務中經(jīng)常出現(xiàn)的更新行同時又希望獲得改行信息的需求，MySQL并不支持PostgreSQL那樣的UPDATE RETURNING語法，在MySQL中可以通過變量實現(xiàn)。

例如，更新一行記錄的時間戳，同時希望查詢當前記錄中存放的時間戳是什么，簡單方法實現(xiàn)：

Update t1 set time=now() where col1=1;

Select timefrom t1 where id =1;

使用變量，可以重寫為以下方式：

Update t1 set time=now () where col1=1 and @now: =now ();

Select @now;

前后二者都需要兩次網(wǎng)絡(luò)來回，但使用變量避免了再次訪問數(shù)據(jù)表，特別是當t1表數(shù)據(jù)量較大時，后者比前者快很多。

四、查詢條件優(yōu)化

1. 對于復雜的查詢，可以使用中間臨時表暫存數(shù)據(jù)；

2. 優(yōu)化group by語句

默認情況下，MySQL 會對GROUP BY分組的所有值進行排序，如 “GROUP BY col1，col2，....;” 查詢的方法如同在查詢中指定 “ORDER BY col1，col2，...;” 如果顯式包括一個包含相同的列的 ORDER BY子句，MySQL 可以毫不減速地對它進行優(yōu)化，盡管仍然進行排序。因此，如果查詢包括 GROUP BY 但你并不想對分組的值進行排序，你可以指定 ORDER BY NULL禁止排序。例如：

SELECT col1, col2, COUNT(*) FROM table GROUP BY col1, col2 ORDER BY NULL ;

3. 優(yōu)化join語句

MySQL中可以通過子查詢來使用 SELECT 語句來創(chuàng)建一個單列的查詢結(jié)果，然后把這個結(jié)果作為過濾條件用在另一個查詢中。使用子查詢可以一次性的完成很多邏輯上需要多個步驟才能完成的 SQL 操作，同時也可以避免事務或者表鎖死，并且寫起來也很容易。但是，有些情況下，子查詢可以被更有效率的連接(JOIN)..替代

如果使用連接(JOIN).. 來完成這個查詢工作，速度將會有所提升。尤其是當 salesinfo表中對 CustomerID 建有索引的話，性能將會更好，查詢?nèi)缦?#xff1a;

SELECT col1 FROM customerinfo

LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.CustomerID

WHERE salesinfo.CustomerID IS NULL

連接(JOIN).. 之所以更有效率一些，是因為 MySQL 不需要在內(nèi)存中創(chuàng)建臨時表來完成這個邏輯上的需要兩個步驟的查詢工作。

4. 優(yōu)化union查詢

MySQL通過創(chuàng)建并填充臨時表的方式來執(zhí)行union查詢。除非確實要消除重復的行，否則建議使用union all。原因在于如果沒有all這個關(guān)鍵詞，MySQL會給臨時表加上distinct選項，這會導致對整個臨時表的數(shù)據(jù)做唯一性校驗，這樣做的消耗相當高。

高效：

SELECT COL1, COL2, COL3 FROM TABLE WHERE COL1 = 10UNION ALL

SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST';

低效:

SELECT COL1, COL2, COL3 FROM TABLE WHERE COL1 = 10UNION

SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST';

5.拆分復雜SQL為多個小SQL，避免大事務

簡單的SQL容易使用到MySQL的QUERY CACHE；

減少鎖表時間特別是使用MyISAM存儲引擎的表

可以使用多核CPU

6. 使用truncate代替delete

當刪除全表中記錄時，使用delete語句的操作會被記錄到undo塊中，刪除記錄也記錄binlog，當確認需要刪除全表時，會產(chǎn)生很大量的binlog并占用大量的undo數(shù)據(jù)塊，此時既沒有很好的效率也占用了大量的資源。

使用truncate替代，不會記錄可恢復的信息，數(shù)據(jù)不能被恢復。也因此使用truncate操作有其極少的資源占用與極快的時間。另外，使用truncate可以回收表的水位，使自增字段值歸零。

7. 使用合理的分頁方式以提高分頁效率

使用合理的分頁方式以提高分頁效率針對展現(xiàn)等分頁需求，合適的分頁方式能夠提高分頁的效率。

案例1：

select * from t where thread_id = 10000 and deleted = 0order by gmt_create asc limit0, 15;

上述例子通過一次性根據(jù)過濾條件取出所有字段進行排序返回。數(shù)據(jù)訪問開銷=索引IO+索引全部記錄結(jié)果對應的表數(shù)據(jù)IO。因此，該種寫法越翻到后面執(zhí)行效率越差，時間越長，尤其表數(shù)據(jù)量很大的時候。

適用場景：當中間結(jié)果集很小(10000行以下)或者查詢條件復雜(指涉及多個不同查詢字段或者多表連接)時適用。

案例2：

select t.* from (select id from t where thread_id = 10000 and deleted = 0order by gmt_create asc limit0, 15) a, twhere a.id = t.id;

述例子必須滿足t表主鍵是id列，且有覆蓋索引secondary key:(thread_id, deleted, gmt_create)。通過先根據(jù)過濾條件利用覆蓋索引取出主鍵id進行排序，再進行join操作取出其他字段。數(shù)據(jù)訪問開銷=索引IO+索引分頁后結(jié)果(例子中是15行)對應的表數(shù)據(jù)IO。因此，該寫法每次翻頁消耗的資源和時間都基本相同，就像翻第一頁一樣。

適用場景：當查詢和排序字段(即where子句和order by子句涉及的字段)有對應覆蓋索引時，且中間結(jié)果集很大的情況時適用。

五、建表優(yōu)化

1. 在表中建立索引，優(yōu)先考慮where、order by使用到的字段。

2. 盡量使用數(shù)字型字段(如性別，男：1 女：2)，若只含數(shù)值信息的字段盡量不要設(shè)計為字符型，這會降低查詢和連接的性能，并會增加存儲開銷。這是因為引擎在處理查詢和連接時會逐個比較字符串中每一個字符，而對于數(shù)字型而言只需要比較一次就夠了。

3.查詢數(shù)據(jù)量大的表會造成查詢緩慢。主要的原因是掃描行數(shù)過多。這個時候可以通過程序，分段分頁進行查詢，循環(huán)遍歷，將結(jié)果合并處理進行展示。要查詢100000到100050的數(shù)據(jù)，如下：

SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID ASC) AS rowid,*FROM infoTab)t WHERE t.rowid> 100000 AND t.rowid <= 100050

4. 用varchar/nvarchar 代替 char/nchar

盡可能的使用 varchar/nvarchar 代替 char/nchar ，因為首先變長字段存儲空間小，可以節(jié)省存儲空間，其次對于查詢來說，在一個相對較小的字段內(nèi)搜索效率顯然要高些。

不要以為 NULL 不需要空間，比如：char(100) 型，在字段建立時，空間就固定了，不管是否插入值(NULL也包含在內(nèi))，都是占用 100個字符的空間的，如果是varchar這樣的變長字段， null 不占用空間。

總結(jié)

以上是生活随笔為你收集整理的sql语句优化总结 mysql_MySQL-SQL优化总结的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。

上一篇： python网址太长_Python GU
下一篇： weiler-atherton多边形裁剪