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

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

eq_range_index_dive_limit 默認值是 200 .

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

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

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

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

解決方案

方案一

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

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

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

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

方案二

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

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

結果: 在 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'

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

方案三

使用 實體表

創(chuàng)建實體表

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 '當前存儲關聯(lián) hash 值',
    ref         varchar(100)                comment '關聯(lián)數(shù)據(jù) id',
    ref_long    bigint                             null,
    create_time datetime default CURRENT_TIMESTAMP null comment '創(chuàng)建時間',
    index idx_hash_ref(hash, ref),
    index idx_hash_ref_long(hash, ref)
);

• 將上面 task_tag_id 插入至 臨時表

  可使用 insert values 插入

• 如果是結果值可以直接使用

  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'

注意點

• 需要及時清理 jump_data 表
• 定時需要 truncate 表因為反復的新增和刪除導致 MySQL 預估數(shù)據(jù)不準確導致速度下降

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

最新評論