首先要聲明的就是，千萬級(jí)數(shù)據(jù)對(duì)于MySQL來說就是不太合理的一個(gè)存在。

優(yōu)化MySQL千萬級(jí)數(shù)據(jù)策略還是比較多的。

• 分表分庫
• 創(chuàng)建中間表，匯總表
• 修改為多個(gè)子查詢

這里討論的情況是在MySQL一張表的數(shù)據(jù)達(dá)到千萬級(jí)別。表設(shè)計(jì)很爛，業(yè)務(wù)統(tǒng)計(jì)規(guī)則又不允許把sql拆成多個(gè)子查詢。

在這樣的情況下，開發(fā)者可以嘗試通過優(yōu)化SQL來達(dá)到查詢的目的。

當(dāng)MySQL一張表的數(shù)據(jù)達(dá)到千萬級(jí)別，會(huì)出現(xiàn)一些特殊的情況。這里主要是討論在比較極端的情況下SQL的優(yōu)化策略。

先來個(gè)千萬級(jí)數(shù)據(jù)

通過存儲(chǔ)過程傳遞函數(shù)制造1000萬條數(shù)據(jù)。

表結(jié)構(gòu)如下：

CREATE TABLE `orders` (
  `order_id` int NOT NULL AUTO_INCREMENT,
  `user_id` int DEFAULT NULL,
  `order_date` date NOT NULL,
  `total_amount` decimal(10,2) NOT NULL,
  PRIMARY KEY (`order_id`),
  KEY `idx_user_id` (`user_id`) USING BTREE,
  KEY `idx_user_amount` (`user_id`,`total_amount`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `users` (
  `user_id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `email` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  KEY `idx_user_id` (`user_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

造數(shù)據(jù)的存儲(chǔ)過程如下。

用戶數(shù)據(jù)：

-- 產(chǎn)生用戶存儲(chǔ)過程，1000個(gè)
CREATE DEFINER=`root`@`localhost` PROCEDURE `create_users`()
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE total_users INT DEFAULT 1000; -- 調(diào)整用戶數(shù)量
    DECLARE rnd_username VARCHAR(50);
    DECLARE rnd_email VARCHAR(100);

    WHILE i < total_users DO
        -- 生成隨機(jī)用戶名和郵箱
        SET rnd_username = CONCAT('User', FLOOR(1 + RAND() * 10000000)); -- 假設(shè)用戶名唯一
        SET rnd_email = CONCAT(rnd_username, '@example.com'); -- 假設(shè)郵箱唯一
        -- 將數(shù)據(jù)插入用戶表
        INSERT INTO users (username, email) VALUES (rnd_username, rnd_email);

        SET i = i + 1;
    END WHILE;
END

訂單數(shù)據(jù)生成存儲(chǔ)過程如下：

CREATE DEFINER=`root`@`localhost` PROCEDURE `generate_orders`()
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE total_users INT DEFAULT 1000; -- 用戶數(shù)量
    DECLARE total_orders_per_user INT DEFAULT 1000; -- 每個(gè)用戶的訂單數(shù)量
    DECLARE rnd_user_id INT;
    DECLARE rnd_order_date DATE;
    DECLARE rnd_total_amount DECIMAL(10, 2);
    DECLARE j INT DEFAULT 0;

    WHILE i < total_users DO
        -- 獲取用戶ID
        SELECT user_id INTO rnd_user_id FROM users LIMIT i, 1;

        WHILE j < total_orders_per_user DO
            -- 生成訂單日期和總金額
            SET rnd_order_date = DATE_ADD('2020-01-01', INTERVAL FLOOR(RAND() * 1096) DAY); -- 2020-01-01和2022-12-31之間的隨機(jī)日期
            SET rnd_total_amount = ROUND(RAND() * 1000, 2); -- 0到1000之間的隨機(jī)總金額
            -- 將數(shù)據(jù)插入訂單表
            INSERT INTO orders (user_id, order_date, total_amount) VALUES (rnd_user_id, rnd_order_date, rnd_total_amount);

            SET j = j + 1;
        END WHILE;
        SET j = 0;

        SET i = i + 1;
    END WHILE;
END

將users和orders的數(shù)據(jù)生成分開，這樣可以通過多次調(diào)用orders存儲(chǔ)過程多線程參數(shù)數(shù)據(jù)。

調(diào)用一次call create_users()，然后開15個(gè)窗口調(diào)用orders存儲(chǔ)過程call generate_orders()。

整個(gè)過程會(huì)產(chǎn)生1000個(gè)用戶，15*1000*1000也就是1500萬條訂單數(shù)據(jù)。

原始SQL

這是一個(gè)很簡單的sql，統(tǒng)計(jì)每個(gè)用戶的訂單總額。

在默認(rèn)情況下，什么索引都沒有創(chuàng)建，需要花費(fèi)190+s的時(shí)間。

-- 第一個(gè)版本
SELECT a.*,sum(b.total_amount) as total from users a left join orders b  on a.user_id = b.user_id
group by a.user_id;

explain分析如下：

可以看到什么索引也沒使用，type為all，直接全表掃描。

用時(shí)191s。

第一次優(yōu)化：普通索引

把查詢條件用到的sql條件都創(chuàng)建索引。也就是where和join、sum涉及到的知道。

CREATE INDEX idx_orders_user_id ON orders (user_id);
CREATE INDEX idx_orders_total_amount ON orders (total_amount);
CREATE INDEX idx_users_user_id ON users (user_id);

查詢sql仍然是第一個(gè)版本。

-- 第一個(gè)版本
SELECT a.*,sum(b.total_amount) as total from users a left join orders b  on a.user_id = b.user_id
group by a.user_id;

先看看expalin的結(jié)果：

type為index或者ref，全部走的索引。

查詢結(jié)果卻讓人失望，這次用的時(shí)間更多，用了460+s。也就是說查詢變慢了。

推測(cè)是由于mysql的回表機(jī)制導(dǎo)致查詢變得更慢了。所以接下來繼續(xù)優(yōu)化索引。

第二次優(yōu)化：覆蓋索引

覆蓋索引是指一個(gè)索引包含了查詢所需的所有列，從而可以滿足查詢的要求，而不需要訪問實(shí)際的數(shù)據(jù)行。

通常情況下，數(shù)據(jù)庫查詢需要根據(jù)索引定位到對(duì)應(yīng)的數(shù)據(jù)行，然后再從數(shù)據(jù)行中獲取所需的列值。

而當(dāng)索引中包含了查詢所需的所有列時(shí)，數(shù)據(jù)庫引擎可以直接通過索引就能夠滿足查詢的要求，無需訪問實(shí)際的數(shù)據(jù)行，這樣就可以提高查詢性能。

這也是普通索引添加了還是查詢慢的原因，因?yàn)槠胀ㄋ饕辛诉€是會(huì)去找主鍵，通過主鍵找到關(guān)聯(lián)字段的值做過濾。

-- 先不刪除普通索引
-- drop INDEX idx_orders_user_id ON orders;
-- drop INDEX idx_orders_total_amount ON orders;
CREATE INDEX idx_orders_total_amount_user_id ON orders (total_amount,user_id);
CREATE INDEX idx_orders_user_id_total_amount ON orders (user_id,total_amount);

1500萬數(shù)據(jù)創(chuàng)建索引就花費(fèi)了300+s。所以創(chuàng)建索引得適度。

查詢sql還是第一個(gè)版本。

-- 第一個(gè)版本
SELECT a.*,sum(b.total_amount) as total from users a left join orders b  on a.user_id = b.user_id
group by a.user_id;

先看看expalin的結(jié)果：

可以看到orders表的type從index提升到了ref。

此時(shí)的查詢時(shí)間為從460s+降低到10s了。

結(jié)果證明覆蓋索引能提升查詢速度。

問題就在于這次建的兩個(gè)覆蓋索引，只有 idx_orders_user_id_total_amount 降低了查詢時(shí)間，而 idx_orders_total_amount_user_id沒有。

這個(gè)和mysql的關(guān)鍵詞執(zhí)行順序有一定關(guān)系（推測(cè)，沒找到資料）。

mysql執(zhí)行順序如下：

from
on
join
where
group by
having
select
distinct
union （all）
order by
limit

可以看到在覆蓋索引使用過程先是where,再是到select的sum函數(shù)。這也是 idx_orders_user_id_total_amount 索引的創(chuàng)建順序。

drop INDEX idx_orders_user_id ON orders;
drop INDEX idx_orders_total_amount ON orders;
drop INDEX idx_orders_total_amount_user_id ON orders;

drop掉相關(guān)的多余索引可以發(fā)現(xiàn)執(zhí)行查詢時(shí)間沒有變化，仍然為10s。

索引優(yōu)化這塊差不多就是通過覆蓋索引來命中索引。

第三次優(yōu)化：減少數(shù)據(jù)量

減少數(shù)據(jù)量在業(yè)務(wù)上來說就是移除不必要的數(shù)據(jù)，或者可以在架構(gòu)設(shè)計(jì)這塊做一些工作。

分表就是這個(gè)原則。

通過這個(gè)方式能把千萬的數(shù)據(jù)量減少到百萬甚至幾十萬的量。提升的查詢速度是可以想象的。

-- 第三次優(yōu)化：減少數(shù)據(jù)量
SELECT a.*,sum(b.total_amount) as total from users a left join orders b  on a.user_id = b.user_id
where a.user_id > 1033
group by a.user_id;

expain結(jié)果如下：

可以看到users表的type為range。能過濾一部分?jǐn)?shù)據(jù)量。

查詢時(shí)間從10s降低到7s，減少數(shù)據(jù)量證明有效。

第四次優(yōu)化：小表驅(qū)動(dòng)大表

在 MySQL 中，通常情況下，優(yōu)化器會(huì)根據(jù)查詢條件和表的大小選擇合適的驅(qū)動(dòng)表（即主導(dǎo)表）。

小表驅(qū)動(dòng)大表是一種優(yōu)化策略，它指的是在連接查詢中，優(yōu)先選擇小表作為驅(qū)動(dòng)表，以減少連接操作所需的內(nèi)存和處理時(shí)間。

在第三次優(yōu)化的結(jié)果上，可以嘗試使用小表驅(qū)動(dòng)大表優(yōu)化策略。

-- 第三個(gè)版本，小標(biāo)驅(qū)動(dòng)大表  沒啥效果
SELECT a.*,sum(b.total_amount) as total from users a
left join (select user_id,total_amount from orders c where c.user_id > 1033 ) b  on a.user_id = b.user_id
where a.user_id > 1033
group by a.user_id;

將left join的表修改為子查詢，能提前過濾一部分?jǐn)?shù)據(jù)量。

expain結(jié)果如下：

可以看到explain沒什么變化。實(shí)際執(zhí)行效果也沒啥變化。

小表驅(qū)動(dòng)大表在這里無效，但是可以結(jié)合具體的業(yè)務(wù)進(jìn)行優(yōu)化sql。這個(gè)策略是沒問題的。

第五次優(yōu)化：強(qiáng)制索引

當(dāng) MySQL 中的 IN 子句用于查詢千萬級(jí)數(shù)據(jù)時(shí)，如果未正確設(shè)計(jì)和使用索引，可能導(dǎo)致索引失效，從而影響查詢性能。

通常情況下，MySQL 的優(yōu)化器會(huì)根據(jù)查詢條件選擇最優(yōu)的執(zhí)行計(jì)劃，包括選擇合適的索引。然而，對(duì)于大數(shù)據(jù)量的 IN 子句查詢，MySQL 可能無法有效使用索引，從而導(dǎo)致全表掃描或索引失效。

查詢sql如下，由于in的數(shù)據(jù)量不是很稀疏，實(shí)際查詢強(qiáng)制索引和普通索引效果一致

-- 第五個(gè)版本，強(qiáng)制索引 
SELECT a.*,sum(b.total_amount) as total from users a left join orders b force index (idx_orders_user_id_total_amount)  on a.user_id = b.user_id
where b.user_id in (1033,1034,1035,1036,1037,1038)
group by a.user_id;
-- 第五個(gè)版本，不走強(qiáng)制索引 
SELECT a.*,sum(b.total_amount) as total from users a left join orders b  on a.user_id = b.user_id
where b.user_id in (1033,1034,1035,1036,1037,1038)
group by a.user_id;

查詢時(shí)間都是零點(diǎn)幾秒。

筆者在實(shí)際業(yè)務(wù)中是遇到過這種場(chǎng)景的，業(yè)務(wù)sql更加復(fù)雜。這里由于臨時(shí)創(chuàng)建的訂單用戶表沒復(fù)現(xiàn)。

當(dāng)你發(fā)現(xiàn)explain都是命中索引的，但是查詢依然很慢。這個(gè)強(qiáng)制索引可以試試。

優(yōu)化策略

• 提前命中索引，小表驅(qū)動(dòng)大表
• 千萬級(jí)數(shù)據(jù)in索引失效，進(jìn)行強(qiáng)制索引
• 使用覆蓋索引解決回表問題

下次該怎么優(yōu)化SQL

• 數(shù)據(jù)接近千萬級(jí)，需要分表，比如按照用戶id取模分表。
• 用匯總表代替子查詢來命中索引，比如把小時(shí)表生成日表、月表匯總數(shù)據(jù)。
• 關(guān)聯(lián)字段冗余、直接放到一張表就是單表查詢了。
• 命中索引，空間換時(shí)間，這也是本文分析的場(chǎng)景。

關(guān)于命中索引核心點(diǎn)就是覆蓋索引，再者是千萬數(shù)據(jù)產(chǎn)生的特有場(chǎng)景需要走強(qiáng)制索引。

tips

explain結(jié)果type的含義

在 MySQL 的 EXPLAIN 查詢結(jié)果中，type 字段表示了查詢使用的訪問類型，即查詢執(zhí)行過程中的訪問方法。

根據(jù)不同的訪問類型，MySQL 查詢優(yōu)化器將選擇不同的執(zhí)行計(jì)劃。以下是 type 字段可能的取值及其含義：

• system： 這是最好的情況，表示查詢只返回一行結(jié)果。這通常是通過直接訪問表的 PRIMARY KEY 或唯一索引來完成的。
• const： 表示 MySQL 在查詢中找到了常量值，這是在連接的第一個(gè)表中進(jìn)行的。由于這是常量條件，MySQL 只會(huì)讀取一次表中的一行數(shù)據(jù)。例如，通過主鍵訪問一行數(shù)據(jù)。
• eq_ref： 類似于 const，但在使用了索引的情況下。此類型的查詢是通過某個(gè)唯一索引來訪問表的，對(duì)于每個(gè)索引鍵值，表中只有一行匹配。常見于使用主鍵或唯一索引進(jìn)行連接操作。
• ref： 表示此查詢使用了非唯一索引來查找值。返回的是所有匹配某個(gè)單獨(dú)值的行。該類型一般出現(xiàn)在聯(lián)接操作中，使用了非唯一索引或者索引前綴。
• range： 表示查詢使用了索引來進(jìn)行范圍檢索，通常出現(xiàn)在帶有范圍條件的查詢語句中，例如 BETWEEN、IN()、>、<等。
• index： 表示 MySQL 將掃描整個(gè)索引來找到所需的行。這通常是在沒有合適的索引的情況下，MySQL 會(huì)選擇使用這種訪問類型。
• all： 表示 MySQL 將掃描全表以找到所需的行，這是最差的情況。這種情況下，MySQL 將對(duì)表中的每一行執(zhí)行完整的掃描。

通常來說，type 字段的排序從最好到最差依次是 system、const、eq_ref、ref、range、index、all，當(dāng)然，實(shí)際情況取決于查詢的具體情況、表結(jié)構(gòu)和索引的使用情況。更好的查詢性能通常對(duì)應(yīng)著更好的 type 類型。

mysql的回表機(jī)制

在 MySQL 中，回表（"ref" or "Bookmark Lookup" in English）是指在使用索引進(jìn)行查詢時(shí)，MySQL 首先通過索引找到滿足條件的行的位置，然后再回到主表（或稱為數(shù)據(jù)表）中查找完整的行數(shù)據(jù)的過程。

這個(gè)過程通常發(fā)生在某些查詢中，特別是涉及到覆蓋索引無法滿足查詢需求時(shí)。

當(dāng)一個(gè)查詢不能完全通過索引滿足時(shí)，MySQL 就需要回到主表中查找更多的信息。這種情況通常出現(xiàn)在以下幾種情況下：

• 非覆蓋索引查詢： 如果查詢需要返回主表中未包含在索引中的其他列的數(shù)據(jù)時(shí)，MySQL 就需要回到主表中查找這些額外的列數(shù)據(jù)。
• 使用索引范圍條件： 當(dāng)查詢中使用了范圍條件（例如 BETWEEN、>、< 等），而索引只能定位到范圍起始位置時(shí)，MySQL 需要回到主表中檢查滿足范圍條件的完整行。
• 使用了聚簇索引但需要查找的列不在索引中： 在使用了聚簇索引的表中，如果需要查詢的列不在聚簇索引中，MySQL 需要回到主表中查找這些列的數(shù)據(jù)。

當(dāng) MySQL 需要執(zhí)行回表操作時(shí)，會(huì)發(fā)生額外的磁盤訪問，因?yàn)樾枰x取主表中的數(shù)據(jù)。這可能會(huì)導(dǎo)致性能下降，特別是在大型數(shù)據(jù)表中或者在高并發(fā)環(huán)境中。

為了盡量減少回表操作的發(fā)生，可以考慮以下幾點(diǎn)：

• 創(chuàng)建覆蓋索引：確保查詢所需的所有列都包含在索引中，從而避免回表操作。
• 優(yōu)化查詢語句：盡量避免使用范圍條件，或者確保所有的過濾條件都可以被索引完全匹配。
• 考慮表設(shè)計(jì)：在設(shè)計(jì)數(shù)據(jù)庫表結(jié)構(gòu)時(shí)，可以考慮將常用的查詢字段都包含在索引中，以減少回表操作的發(fā)生。

以上就是MySQL千萬級(jí)數(shù)據(jù)從190秒優(yōu)化到1秒的全過程的詳細(xì)內(nèi)容，更多關(guān)于MySQL千萬級(jí)數(shù)據(jù)優(yōu)化的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論