優(yōu)化 LIMIT 分頁

-- 執(zhí)行耗時(shí)：1.379s

SELECT * from vio_basic_domain_info LIMIT 1000000,10;

處理分頁慢查詢的方式一般有以下幾種：

思路一：構(gòu)造覆蓋索引

通過修改 SQL，使用上覆蓋索引，比如我需要只查詢表中的 app_name、createTime 等少量字段，那么我只需在 app_name、createTime 字段設(shè)置聯(lián)合索引，即可實(shí)現(xiàn)覆蓋索引，無需全表掃描。

適用于查詢列較少的場(chǎng)景，查詢列數(shù)過多的不推薦，耗時(shí)：0.390s。

SELECT app_name,createTime from vio_basic_domain_info LIMIT 1000000,10;

思路二：優(yōu)化 offset

無法用上覆蓋索引，那么重點(diǎn)是想辦法快速過濾掉前 100w 條數(shù)據(jù)。我們可以利用自增主鍵有序的條件，先查詢出第 1000001 條數(shù)據(jù)的 id 值，再往后查 10 行。

原理：先基于索引查詢出第 1000001 條數(shù)據(jù)對(duì)應(yīng)的主鍵 id 的值，然后直接通過該 id 的值直接查詢?cè)?id 后面的 10 條數(shù)據(jù)。

適用于主鍵 id 自增的場(chǎng)景，耗時(shí)：0.471s。

SELECT * from vio_basic_domain_info where id >=(SELECT id from vio_basic_domain_info ORDER BY id limit 1000000,1) limit 10;

方法三：“延遲關(guān)聯(lián)”

耗時(shí)：0.439s，延遲關(guān)聯(lián)適用于數(shù)量級(jí)較大的表。

這里我們利用到了覆蓋索引+延遲關(guān)聯(lián)查詢，相當(dāng)于先只查詢 id 列，利用覆蓋索引快速查到該頁的 10 條數(shù)據(jù) id，然后再把返回的 10 條 id 拿到表中通過主鍵索引二次查詢。(表數(shù)據(jù)增速快的情況對(duì)該方法影響較小)

SELECT * from vio_basic_domain_info inner join (select id from vio_basic_domain_info order by id limit 1000000,10) as myNew using(id);

排查索引沒起作用的情況

模糊查詢盡量避免用通配符'%'開頭，會(huì)導(dǎo)致數(shù)據(jù)庫引擎放棄索引進(jìn)行全表掃描

-- 不走索引

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

-- 走索引

SELECT * FROM t WHERE username LIKE '陳%'

盡量避免使用 not in，會(huì)導(dǎo)致引擎走全表掃描。建議用 not exists 代替

-- 不走索引

SELECT * FROM t WHERE name not IN ('提莫','隊(duì)長');

-- 走索引

select * from t as t1 where not exists (select * from t as t2 where name IN ('提莫','隊(duì)長') and t1.id = t2.id);

盡量避免使用 or，會(huì)導(dǎo)致數(shù)據(jù)庫引擎放棄索引進(jìn)行全表掃描

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

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

SELECT * FROM t WHERE id = 1

UNION

SELECT * FROM t WHERE id = 3

盡量避免進(jìn)行 null 值的判斷，會(huì)導(dǎo)致數(shù)據(jù)庫引擎放棄索引進(jìn)行全表掃描

SELECT * FROM t WHERE score IS NULL

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

SELECT * FROM t WHERE score = 0

盡量避免在 where 條件中等號(hào)的左側(cè)進(jìn)行表達(dá)式、函數(shù)操作，會(huì)導(dǎo)致數(shù)據(jù)庫引擎放棄索引進(jìn)行全表掃描

可以將表達(dá)式、函數(shù)操作移動(dòng)到等號(hào)右側(cè)。如下：

-- 全表掃描

SELECT * FROM T WHERE score/10 = 9

-- 走索引

SELECT * FROM T WHERE score = 10*9

當(dāng)數(shù)據(jù)量大時(shí)，避免使用 where 1=1 的條件。通常為了方便拼裝查詢條件，我們會(huì)默認(rèn)使用該條件，數(shù)據(jù)庫引擎會(huì)放棄索引進(jìn)行全表掃描

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

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

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

使用索引列作為條件進(jìn)行查詢時(shí)，需要避免使用<>或者!=等判斷條件。

如確實(shí)業(yè)務(wù)需要，使用到不等于符號(hào)，需要在重新評(píng)估索引建立，避免在此字段上建立索引，改由查詢條件中其他索引字段代替

where 條件僅包含復(fù)合索引非前導(dǎo)列

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

-- 不走索引

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

-- 走索引

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

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

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

select col1 from table where col_varchar=123;

總結(jié)

以上是生活随笔為你收集整理的mysql 语句优化实例_MySQL 语句优化实例的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

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