快捷導(dǎo)航

mysql數(shù)據(jù)庫(kù)數(shù)據(jù)批量插入的實(shí)現(xiàn)

更新時(shí)間：2024年11月11日 10:17:14 作者：danYan_01

本文介紹了在MySQL數(shù)據(jù)庫(kù)中進(jìn)行數(shù)據(jù)批量插入和性能測(cè)試的方法,包括使用Navicat批量插入數(shù)據(jù)、編寫SQL測(cè)試、索引的創(chuàng)建和使用、查詢測(cè)試以及提升SQL性能的優(yōu)化方法,感興趣的可以參考一下

0 、前言

在寫sql語句的時(shí)候常常會(huì)有很多疑問，那就是當(dāng)單表的數(shù)據(jù)量很大的時(shí)候，查詢性能怎么樣，以及索引對(duì)數(shù)據(jù)查詢的影響，今天用navicat批量造了很多重復(fù)數(shù)據(jù)來對(duì)mysq在數(shù)據(jù)量大的時(shí)候查詢的性能的測(cè)試。

1 、使用navicat批量插入數(shù)據(jù)

函數(shù)—>新建函數(shù)–>填寫名，選擇函數(shù)，點(diǎn)擊完成

在這里插入圖片描述

填寫函數(shù)

CREATE DEFINER=`root`@`%` PROCEDURE `userDataInsert`()
BEGIN
	# 設(shè)置循環(huán)變量
   DECLARE i INT DEFAULT 0;
WHILE i<=100000 DO
 INSERT INTO sup_check(advertiser_id,check_time,record_types_id,dept_id) VALUES(20,'2024-04-09',1,203);
 SET i = i + 1;
END WHILE;
END

保存后點(diǎn)擊運(yùn)行即可，此處會(huì)執(zhí)行時(shí)間較長(zhǎng)，主要看你的主機(jī)性能，磁盤的讀寫速度等，最好在自己的本地虛擬機(jī)中去跑，節(jié)省時(shí)間。

2、編寫sql測(cè)試

此sql就是單表的查詢（嵌套子查詢），根據(jù)年份然后按照年月去對(duì)數(shù)據(jù)進(jìn)行一個(gè)分組，問gpt的時(shí)候，gpt說使用函數(shù)的時(shí)候進(jìn)行的是全表的掃描，不走索引，此時(shí)很慌，很想測(cè)試一下，走索引和不走索引到底有什么區(qū)別

SELECT year(check_time) as year ,month(check_time) as month ,COUNT(*) as checkNum
FROM sup_check   
WHERE 
    (dept_id = 100 OR dept_id IN (SELECT t.dept_id FROM sys_dept t WHERE FIND_IN_SET(100,ancestors)))
    AND year(check_time) = 2024
GROUP BY year,month
ORDER BY year,month;

3、索引相關(guān)

單列索引

CREATE INDEX idx_check_time
ON sup_check (check_time);

復(fù)合索引

CREATE INDEX idx_column1_column2 ON sup_check (check_time, record_type_id);

唯一索引

CREATE UNIQUE INDEX uidx_column_name ON table_name (column_name);

全文索引

CREATE FULLTEXT INDEX ftx_column_name ON table_name (column_name);

使用表名，在某些情況下，你可能還想在索引名稱中包含表名，尤其是在大型數(shù)據(jù)庫(kù)中，這有助于避免索引名稱沖突：

CREATE INDEX idx_table_name_column_name ON table_name (column_name);

注意事項(xiàng)
避免使用特殊字符：不要在索引名稱中使用特殊字符，如 !, @, #, $, %, ^, &, *, (, ), -, +, =, {, }, [, ], |, , ;, :, ', ", , <, >, /, ?`。
長(zhǎng)度限制：MySQL索引名稱的最大長(zhǎng)度是64個(gè)字符

查看索引

這將返回以下列：

Table：表名
Non_unique：如果索引不能包含重復(fù)值，則為0；如果可以，則為1。
Key_name：索引的名稱。
Seq_in_index：索引中的列序列號(hào)。
Column_name：列名。
Collation：列如何排序，如果是定序的，則顯示排序順序；如果是未排序的，則顯示NULL。
Cardinality：索引中唯一值的估計(jì)數(shù)量。
Sub_part：如果列只是部分索引，則為索引的字符數(shù)。如果是整個(gè)列被索引，則為NULL。
Packed：指示關(guān)鍵字如何被壓縮，如果沒有被壓縮，則為NULL。
Null：如果列含有NULL，則顯示YES。
Index_type：索引類型，如BTREE、FULLTEXT、HASH等。
Comment：索引的備注信息。

SHOW INDEX FROM table_name;

4、查詢測(cè)試

建立完索引之后，我發(fā)現(xiàn)當(dāng)查詢出的數(shù)據(jù)量比較大的時(shí)候，查詢指定數(shù)據(jù)的速度確實(shí)快了，但是當(dāng)所查詢出的字段多或者數(shù)據(jù)條數(shù)多的時(shí)候還是會(huì)耗費(fèi)很長(zhǎng)時(shí)間，那解決辦法就是分頁(yè)，一下查詢出這么多條數(shù)據(jù)肯定慢，分頁(yè)的話就特別快了。

SELECT year(check_time) as year ,month(check_time) as month ,COUNT(*) as checkNum
FROM sup_check   
WHERE 
    (dept_id = 100 OR dept_id IN (SELECT t.dept_id FROM sys_dept t WHERE FIND_IN_SET(100,ancestors)))
    AND year(check_time) = 2024
GROUP BY year,month
ORDER BY year,month;
LIMIT 20 OFFSET 0;

5、提升sql的辦法

優(yōu)化sql，經(jīng)常查詢的字段使用索引，查詢出需要使用的字段，分頁(yè)查詢

6、探討分頁(yè)查詢的基本實(shí)現(xiàn)原理

首先使用count(*) 查詢出數(shù)據(jù)總條數(shù)，此查詢是經(jīng)過mysql優(yōu)化過的，速度比較快，或者你去count（索引列）然后就是根據(jù)每頁(yè)的數(shù)據(jù)大小計(jì)算出總的頁(yè)數(shù)（int totalPages = (totalRecords + pageSize - 1) / pageSize;），然后就是執(zhí)行分頁(yè)查詢，查詢指定頁(yè)的數(shù)據(jù)

到此這篇關(guān)于mysql數(shù)據(jù)庫(kù)數(shù)據(jù)批量插入的實(shí)現(xiàn)的文章就介紹到這了,更多相關(guān)mysql 數(shù)據(jù)批量插入內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: