MySQL in 太多出現(xiàn)慢的原因

在MySQL中有一個(gè)配置參數(shù)eq_range_index_dive_limit，它的作用是一個(gè)等值查詢(比如：in 查詢)，其等值條件數(shù)小于該配置參數(shù)，則查詢成本分析使用掃描索引樹的方式分析，如果大于等于該配置參數(shù)，則使用索引統(tǒng)計(jì)的方式分析。使用掃描索引樹的方式分析在MySQL內(nèi)部叫做index dives，使用索引統(tǒng)計(jì)的方式分析在MySQL內(nèi)部叫做index statistics。

eq_range_index_dive_limit 默認(rèn)值是 200 .

select * from dogs where id in (1, 2, 3, 4);

結(jié)合上面這條 SQL，就是如果 SQL 中 IN 查詢字段 id 的值出現(xiàn)的數(shù)量小于 eq_range_index_dive_limit，則走索引樹掃描分析查詢成本，大于等于 eq_range_index_dive_limit，則走索引統(tǒng)計(jì)的方式分析查詢成本。

掃描索引樹的方式分析 SQL 的查詢成本，它的好處就是在 IN 查詢的值數(shù)量不多時(shí)，得到的成本結(jié)果是精確的，這就意味著 MySQL 可以選擇正確的執(zhí)行計(jì)劃，保證語(yǔ)句查詢的性能。你現(xiàn)在一定有個(gè)疑問(wèn)：為什么說(shuō)是在 IN 查詢的值數(shù)量不多時(shí)才是精確的，因?yàn)閽呙栊阅艿脑?，MySQL 在 IN 查詢的值數(shù)量很多的情況下，掃描索引樹成本提高，性能下降，導(dǎo)致查詢成本分析代價(jià)也隨之提高了。

索引統(tǒng)計(jì)的方式分析 SQL 的查詢成本，由于無(wú)需掃描索引樹，所以，它的優(yōu)勢(shì)就是查詢成本分析過(guò)程快，代價(jià)低。但是，它的缺點(diǎn)也很明顯，由于無(wú)需掃描索引樹，通過(guò)粗略統(tǒng)計(jì)索引使用情況，得出查詢成本，導(dǎo)致 MySQL 可能選錯(cuò)執(zhí)行計(jì)劃，使得 SQL 查詢性能下降。

解決方案

方案一

可以通過(guò)拆分 in 的數(shù)量, 分批查詢.

select * from dogs where id in (1, 2);

select * from dogs where id in (3, 4);

這種方法缺點(diǎn)也明顯, 對(duì)于分頁(yè)或者是查詢總條件的一部分并不能實(shí)現(xiàn).

方案二

使用 union all 實(shí)現(xiàn)內(nèi)存級(jí)別臨時(shí)表.

select *
from users where task_created > '2020-01-01' and  task_tag_id in ('-1', '1' , ....'1000個(gè)');

結(jié)果: 在 1 s 631 ms (execution: 172 ms, fetching: 1 s 459 ms) 內(nèi)檢索到從 1 開始的 500 行

select * from users u
    inner join (select -99 as id union all select '1' union all select '-1'
union all select '1' ) as temp on u.task_tag_id = temp.id
where task_created > '2020-01-01'

結(jié)果: 在 383 ms (execution: 201 ms, fetching: 182 ms) 內(nèi)檢索到從 1 開始的 500 行

方案三

使用 實(shí)體表

創(chuàng)建實(shí)體表

create table jump_data
(
    id          bigint auto_increment
        primary key,
    user_id      bigint   default -1                not null comment '人員id',
    hash        varchar(70)          not null comment '當(dāng)前存儲(chǔ)關(guān)聯(lián) hash 值',
    ref         varchar(100)                comment '關(guān)聯(lián)數(shù)據(jù) id',
    ref_long    bigint                             null,
    create_time datetime default CURRENT_TIMESTAMP null comment '創(chuàng)建時(shí)間',
    index idx_hash_ref(hash, ref),
    index idx_hash_ref_long(hash, ref)
);

• 將上面 task_tag_id 插入至 臨時(shí)表

  可使用 insert values 插入

• 如果是結(jié)果值可以直接使用

  insert select 插入

使用

select *
from users u  inner join jump_data jd on u.hash = '' and u.ref_long = u.id
where task_created > '2020-01-01'

注意點(diǎn)

• 需要及時(shí)清理 jump_data 表
• 定時(shí)需要 truncate 表因?yàn)榉磸?fù)的新增和刪除導(dǎo)致 MySQL 預(yù)估數(shù)據(jù)不準(zhǔn)確導(dǎo)致速度下降

以上就是MySQL in太多過(guò)慢的三種解決方案的詳細(xì)內(nèi)容，更多關(guān)于MySQL in太多過(guò)慢的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論