Kürzlich habe ich Probeinterviews und Lebenslaufoptimierungen für alle durchgeführt und dabei festgestellt, dass viele Menschen weiche Knie bekommen, wenn sie Fragen wie zig Millionen Daten sehen.

Vielleicht sind einige Leute noch nie auf eine Tabelle mit zig Millionen Daten gesto?en und wissen nicht, was passiert, wenn sie zig Millionen Daten abfragen.

Heute werde ich Sie durch eine praktische übung führen. Dieses Mal basiert sie auf MySQL 5.7.26 zum Testen

Daten vorbereiten

Was tun, wenn Sie nicht über 10 Millionen Daten verfügen? ?

Sie k?nnen es ohne Daten nicht selbst erstellen?

Ist es schwierig, Daten zu erstellen?

10 Millionen Code-Erstellung?

Das ist unm?glich, es ist zu langsam, man k?nnte wirklich einen ganzen Tag zum Laufen brauchen. Sie k?nnen Datenbankskripte verwenden, um sie viel schneller auszuführen.

Tabelle erstellen

CREATE TABLE `user_operation_log`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `ip` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `op_data` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr3` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr4` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr5` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr6` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr7` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr8` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr9` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr10` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr11` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr12` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

Datenskript erstellen

Mit der Stapeleinfügung wird die Effizienz viel schneller sein und alle 1000 Elemente werden festgeschrieben Das Einfügen von Stapeln wird ebenfalls langsam sein.

DELIMITER ;;
CREATE PROCEDURE batch_insert_log()
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE userId INT DEFAULT 10000000;
 set @execSql = 'INSERT INTO `test`.`user_operation_log`(`user_id`, `ip`, `op_data`, `attr1`, `attr2`, `attr3`, `attr4`, `attr5`, `attr6`, `attr7`, `attr8`, `attr9`, `attr10`, `attr11`, `attr12`) VALUES';
 set @execData = '';
  WHILE i<=10000000 DO
   set @attr = "&#39;測(cè)試很長(zhǎng)很長(zhǎng)很長(zhǎng)很長(zhǎng)很長(zhǎng)很長(zhǎng)很長(zhǎng)很長(zhǎng)很長(zhǎng)很長(zhǎng)很長(zhǎng)很長(zhǎng)很長(zhǎng)很長(zhǎng)很長(zhǎng)很長(zhǎng)很長(zhǎng)的屬性&#39;";
  set @execData = concat(@execData, "(", userId + i, ", &#39;10.0.69.175&#39;, &#39;用戶登錄操作&#39;", ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ")");
  if i % 1000 = 0
  then
     set @stmtSql = concat(@execSql, @execData,";");
    prepare stmt from @stmtSql;
    execute stmt;
    DEALLOCATE prepare stmt;
    commit;
    set @execData = "";
   else
     set @execData = concat(@execData, ",");
   end if;
  SET i=i+1;
  END WHILE;

END;;
DELIMITER ;

SELECT count(1) FROM `user_operation_log`

Ergebnis zurückgeben: 3148000 Die drei Abfragezeiten sind:

• 14060 ms
• 13755 ms
• 13447 ms

普通分頁(yè)查詢

MySQL 支持 LIMIT 語(yǔ)句來(lái)選取指定的條數(shù)數(shù)據(jù)， Oracle 可以使用 ROWNUM 來(lái)選取。

MySQL分頁(yè)查詢語(yǔ)法如下：

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

• 第一個(gè)參數(shù)指定第一個(gè)返回記錄行的偏移量
• 第二個(gè)參數(shù)指定返回記錄行的最大數(shù)目

下面我們開(kāi)始測(cè)試查詢結(jié)果：

SELECT * FROM `user_operation_log` LIMIT 10000, 10

查詢3次時(shí)間分別為：

• 59 ms
• 49 ms
• 50 ms

這樣看起來(lái)速度還行，不過(guò)是本地?cái)?shù)據(jù)庫(kù)，速度自然快點(diǎn)。

換個(gè)角度來(lái)測(cè)試

相同偏移量，不同數(shù)據(jù)量

SELECT * FROM `user_operation_log` LIMIT 10000, 10
SELECT * FROM `user_operation_log` LIMIT 10000, 100
SELECT * FROM `user_operation_log` LIMIT 10000, 1000
SELECT * FROM `user_operation_log` LIMIT 10000, 10000
SELECT * FROM `user_operation_log` LIMIT 10000, 100000
SELECT * FROM `user_operation_log` LIMIT 10000, 1000000

查詢時(shí)間如下：

1000 Elemente 100000 Artikel1609ms1741ms1764ms 1000000 Artikel16219ms16889ms17081ms

從上面結(jié)果可以得出結(jié)束：數(shù)據(jù)量越大，花費(fèi)時(shí)間越長(zhǎng)

相同數(shù)據(jù)量，不同偏移量

SELECT * FROM `user_operation_log` LIMIT 100, 100
SELECT * FROM `user_operation_log` LIMIT 1000, 100
SELECT * FROM `user_operation_log` LIMIT 10000, 100
SELECT * FROM `user_operation_log` LIMIT 100000, 100
SELECT * FROM `user_operation_log` LIMIT 1000000, 100

Menge	Erstes Mal	Zweites Mal	Drittes Mal
10. Artikel	53ms	52ms	47ms
100 Artikel	50ms	60ms	55ms

偏移量	第一次	第二次	第三次
100	36ms	40ms	36ms
1000	31ms	38ms	32ms
10000	53ms	48ms	51ms
100000	622ms	576ms	627ms
1000000	4891ms	5076ms	4856ms

從上面結(jié)果可以得出結(jié)束：偏移量越大，花費(fèi)時(shí)間越長(zhǎng)

SELECT * FROM `user_operation_log` LIMIT 100, 100
SELECT id, attr FROM `user_operation_log` LIMIT 100, 100

如何優(yōu)化

既然我們經(jīng)過(guò)上面一番的折騰，也得出了結(jié)論，針對(duì)上面兩個(gè)問(wèn)題：偏移大、數(shù)據(jù)量大，我們分別著手優(yōu)化

優(yōu)化偏移量大問(wèn)題

采用子查詢方式

我們可以先定位偏移位置的 id，然后再查詢數(shù)據(jù)

SELECT * FROM `user_operation_log` LIMIT 1000000, 10

SELECT id FROM `user_operation_log` LIMIT 1000000, 1

SELECT * FROM `user_operation_log` WHERE id >= (SELECT id FROM `user_operation_log` LIMIT 1000000, 1) LIMIT 10

查詢結(jié)果如下：

sql	花費(fèi)時(shí)間
第一條	4818ms
第二條(無(wú)索引情況下)	4329ms
第二條(有索引情況下)	199ms
第三條(無(wú)索引情況下)	4319ms
第三條(有索引情況下)	201ms

從上面結(jié)果得出結(jié)論：

• 第一條花費(fèi)的時(shí)間最大，第三條比第一條稍微好點(diǎn)
• 子查詢使用索引速度更快

缺點(diǎn)：只適用于id遞增的情況

id非遞增的情況可以使用以下寫(xiě)法，但這種缺點(diǎn)是分頁(yè)查詢只能放在子查詢里面

注意：某些 mysql 版本不支持在 in 子句中使用 limit，所以采用了多個(gè)嵌套select

SELECT * FROM `user_operation_log` WHERE id IN (SELECT t.id FROM (SELECT id FROM `user_operation_log` LIMIT 1000000, 10) AS t)

采用 id 限定方式

這種方法要求更高些，id必須是連續(xù)遞增，而且還得計(jì)算id的范圍，然后使用 between，sql如下

SELECT * FROM `user_operation_log` WHERE id between 1000000 AND 1000100 LIMIT 100

SELECT * FROM `user_operation_log` WHERE id >= 1000000 LIMIT 100

查詢結(jié)果如下：

sql	花費(fèi)時(shí)間
第一條	22ms
第二條	21ms

從結(jié)果可以看出這種方式非?？?/p>

注意：這里的 LIMIT 是限制了條數(shù)，沒(méi)有采用偏移量

優(yōu)化數(shù)據(jù)量大問(wèn)題

返回結(jié)果的數(shù)據(jù)量也會(huì)直接影響速度

SELECT * FROM `user_operation_log` LIMIT 1, 1000000

SELECT id FROM `user_operation_log` LIMIT 1, 1000000

SELECT id, user_id, ip, op_data, attr1, attr2, attr3, attr4, attr5, attr6, attr7, attr8, attr9, attr10, attr11, attr12 FROM `user_operation_log` LIMIT 1, 1000000

查詢結(jié)果如下：

sql	花費(fèi)時(shí)間
第一條	15676ms
第二條	7298ms
第三條	15960ms

Aus den Ergebnissen geht hervor, dass durch die Reduzierung unn?tiger Spalten auch die Abfrageeffizienz erheblich verbessert werden kann.

Die Geschwindigkeit der ersten und dritten Abfrage ist fast gleich, also warum sollte ich mich beschweren? So viele Felder schreiben? , einfach * und schon sind Sie fertig

Beachten Sie, dass sich mein MySQL-Server und mein MySQL-Client auf demselben Computer befinden, sodass qualifizierte Studenten den Client und MySQL separat testen k?nnen

SELECT *. Riecht es nicht gut?

übrigens m?chte ich hier hinzufügen, warum wir SELECT *. Ist es nicht k?stlich, weil es einfach und sinnlos ist? SELECT *。難道簡(jiǎn)單無(wú)腦，它不香嗎？

主要兩點(diǎn)：

1. 用 "SELECT * " 數(shù)據(jù)庫(kù)需要解析更多的對(duì)象、字段、權(quán)限、屬性等相關(guān)內(nèi)容，在 SQL 語(yǔ)句復(fù)雜，硬解析較多的情況下，會(huì)對(duì)數(shù)據(jù)庫(kù)造成沉重的負(fù)擔(dān)。
2. 增大網(wǎng)絡(luò)開(kāi)銷，*
   Zwei Hauptpunkte:
Verwenden Sie "SELECT * " Die Datenbank muss mehr Objekte, Felder, Berechtigungen, Attribute usw. analysieren. Inhalt: Wenn die SQL-Anweisung komplex ist und viele harte Analysen durchgeführt werden, wird die Datenbank stark belastet.