0 、前言

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

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)行即可，此處會執(zhí)行時間較長，主要看你的主機(jī)性能，磁盤的讀寫速度等，最好在自己的本地虛擬機(jī)中去跑，節(jié)省時間。

2、編寫sql測試

此sql就是單表的查詢（嵌套子查詢），根據(jù)年份然后按照年月去對數(shù)據(jù)進(jìn)行一個分組，問gpt的時候，gpt說使用函數(shù)的時候進(jìn)行的是全表的掃描，不走索引，此時很慌，很想測試一下，走索引和不走索引到底有什么區(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ù)庫中，這有助于避免索引名稱沖突：

CREATE INDEX idx_table_name_column_name ON table_name (column_name);

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

查看索引

這將返回以下列：

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

SHOW INDEX FROM table_name;

4、查詢測試

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

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)常查詢的字段使用索引，查詢出需要使用的字段，分頁查詢

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

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

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

最新評論