Schauen wir uns zunächst eine Interviewszene an:
- Interviewer: Reden wir über 10 Millionen Daten, wie haben Sie sie abgefragt?
- Bruder: Fragen Sie direkt per Paging ab, verwenden Sie Limit-Paging.
- Interviewer: Haben Sie es schon einmal in der Praxis gemacht?
- Bruder: Es muss einen geben
Vielleicht sind einige Freunde noch nie auf eine Tabelle mit zig Millionen Daten gestoßen und wissen nicht, was passiert, wenn zig Millionen Daten abgefragt werden.
Heute werde ich Sie durch eine praktische Operation führen. Dieses Mal basiert es auf der MySQL 5.7.26-Version zum Testen
Vorbereiten von Daten
Was tun, wenn Sie keine 10 Millionen Daten haben? ?
Erstellen
Code zum Erstellen von 10 Millionen? Das ist unmöglich, es ist zu langsam und es kann einen ganzen Tag dauern. 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;
Nach dem Login kopieren
Datenskript erstellen
Mit der Stapeleinfügung ist die Effizienz viel schneller und alle 1000 Elemente werden zu groß, was auch zu einer langsamen Stapeleinfügungseffizienz führt
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 = "'测试很长很长很长很长很长很长很长很长很长很长很长很长很长很长很长很长很长的属性'";
set @execData = concat(@execData, "(", userId + i, ", '10.0.69.175', '用户登录操作'", ",", @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 ;Nach dem Login kopieren
开始测试
田哥的电脑配置比较低：win10 标压渣渣i5 读写约500MB的SSD
由于配置低，本次测试只准备了3148000条数据，占用了磁盘5G(还没建索引的情况下)，跑了38min，电脑配置好的同学，可以插入多点数据测试
SELECT count(1) FROM `user_operation_log`
Nach dem Login kopieren
返回结果：3148000
三次查询时间分别为：
普通分页查询
MySQL 支持 LIMIT 语句来选取指定的条数数据， Oracle 可以使用 ROWNUM 来选取。
MySQL分页查询语法如下：
SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
Nach dem Login kopieren
下面我们开始测试查询结果：
SELECT * FROM `user_operation_log` LIMIT 10000, 10
Nach dem Login kopieren
查询3次时间分别为：
这样看起来速度还行，不过是本地数据库，速度自然快点。
换个角度来测试
相同偏移量，不同数据量
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
Nach dem Login kopieren
查询时间如下：
| Menge | Erstes Mal | Zweites Mal | Drittes Mal |
|---|
| 10. Artikel | 53ms | 52ms | 47ms |
| 100 Artikel | 50ms | 60ms | 55ms |
| 1000 Artikel | 61ms | 74ms | 60ms |
| 10000 Artikel | 164ms | 180ms | 217ms |
| 100000 Artikel | 1609ms | 1741ms | 1764ms |
| 1000000 Artikel | 16219ms | 16889ms | 17081ms |
Aus den obigen Ergebnissen können wir schließen: Je größer die Datenmenge, desto länger dauert es
相同数据量，不同偏移量
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
Nach dem Login kopieren
| 偏移量 | 第一次 | 第二次 | 第三次 |
|---|
| 100 | 36ms | 40ms | 36ms |
| 1000 | 31ms | 38ms | 32ms |
| 10000 | 53ms | 48ms | 51ms |
| 100000 | 622ms | 576ms | 627ms |
| 1000000 | 4891ms | 5076ms | 4856ms |
从上面结果可以得出结束：偏移量越大，花费时间越长
SELECT * FROM `user_operation_log` LIMIT 100, 100
SELECT id, attr FROM `user_operation_log` LIMIT 100, 100
Nach dem Login kopieren
如何优化
既然我们经过上面一番的折腾，也得出了结论，针对上面两个问题：偏移大、数据量大，我们分别着手优化
优化偏移量大问题
采用子查询方式
我们可以先定位偏移位置的 id，然后再查询数据
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
Nach dem Login kopieren
查询结果如下：
| SQL | Es braucht Zeit |
|---|
| Der erste | 4818ms |
| Der zweite (ohne Index) | 4329ms |
| Artikel 2 (mit Index) ) | 199ms |
| Der dritte Artikel (ohne Index) | 4319ms |
| Der dritte Artikel (mit Index) | 201ms |
从上面结果得出结论：
缺点：只适用于id递增的情况
id非递增的情况可以使用以下写法，但这种缺点是分页查询只能放在子查询里面
注意：某些 mysql 版本不支持在 in 子句中使用 limit，所以采用了多个嵌套select
SELECT * FROM `user_operation_log` WHERE id IN (SELECT t.id FROM (SELECT id FROM `user_operation_log` LIMIT 1000000, 10) AS t)
Nach dem Login kopieren
采用 id 限定方式
这种方法要求更高些，id必须是连续递增，而且还得计算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
Nach dem Login kopieren
查询结果如下：
从结果可以看出这种方式非常快
注意：这里的 LIMIT 是限制了条数，没有采用偏移量
优化数据量大问题
返回结果的数据量也会直接影响速度
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
Nach dem Login kopieren
查询结果如下：
| sql |
花费时间 |
| 第一条 |
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 * Das ist der Fall nicht Riecht es gut?
Übrigens möchte ich hinzufügen, warum SELECT * verboten werden sollte. Ist es nicht köstlich, weil es einfach und sinnlos ist?
Hauptsächlich zwei Punkte:
- Bei Verwendung von „SELECT *“ muss die Datenbank mehr Objekte, Felder, Berechtigungen, Attribute und andere verwandte Inhalte analysieren. Wenn die SQL-Anweisungen komplex sind und es viele schwierige Analysen gibt, verursacht dies große Probleme Schäden an der Datenbankbelastung.
- Erhöht den Netzwerkaufwand. * Manchmal werden versehentlich unnötige und große Textfelder wie log und IconMD5 hinzugefügt, und die Datenübertragungsgröße nimmt geometrisch zu. Insbesondere da sich MySQL und die Anwendung nicht auf demselben Rechner befinden, ist dieser Overhead sehr offensichtlich.
Ende
Abschließend hoffe ich, dass Sie es selbst üben können und auf jeden Fall mehr gewinnen werden!
