Mysql數(shù)據(jù)庫百萬級數(shù)據(jù)測試索引效果
Mysql數(shù)據(jù)庫百萬數(shù)據(jù)測試索引
Mysql官方對索引的定義是:索引(index)是幫助Mysql高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。進而,我們可以知道索引的本質(zhì)是數(shù)據(jù)結(jié)構(gòu)。
一、索引的分類
主鍵索引:也就是我們常見的 PRIMARY KEY,只有一個列作為主鍵,唯一標識,不可重復。
唯一索引:UNIQUE KEY,避免重復的列出現(xiàn),唯一索引是可以有多個,同一張表里的多個列都可以設(shè)置唯一索引。
常規(guī)索引:KEY/INDEX,默認的類型,通過關(guān)鍵字INDEX或者KEY來設(shè)置。
全文索引:FullText,在特定的數(shù)據(jù)庫引擎下才支持,作用是快速定位數(shù)據(jù)。
二、使用索引
在創(chuàng)建表或者創(chuàng)建之后,都可以給字段增加索引。
比如現(xiàn)在創(chuàng)建一個測試用的表,我先在創(chuàng)建的時候加了前三種類型的索引:
-- 創(chuàng)建表 CREATE TABLE `student`( `StudentNo` INT(4) NOT NULL COMMENT "學號", `LoginPwd` VARCHAR(20) DEFAULT NULL, `StudentName` VARCHAR(20) DEFAULT NULL COMMENT "學生姓名", `Sex` TINYINT(1) DEFAULT NULL COMMENT "性別:0-1", `GradeId` INT(11) DEFAULT NULL COMMENT "年紀編號", `Phone` VARCHAR(50) NOT NULL COMMENT "聯(lián)系電話", `Address` VARCHAR(255) NOT NULL COMMENT "地址", `BornDate` DATETIME DEFAULT NULL COMMENT "出生日期", `Email` VARCHAR(50) NOT NULL COMMENT "郵箱", `IdentityCard` VARCHAR(18) DEFAULT NULL COMMENT "身份證號", PRIMARY KEY (`StudentNo`), -- 主鍵索引 UNIQUE KEY `IdentityCard` (`IdentityCard`), -- 唯一索引,前面是索引名稱,括號里是字段名 KEY `Email` (`Email`) -- 常規(guī)索引 )ENGINE=INNODB DEFAULT CHARSET=utf8;
執(zhí)行sql,創(chuàng)建表成功。這時候我繼續(xù)增加一個全文索引。
-- 增加一個全文索引類型,前面是索引名稱,括號里是字段名 ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `StudentName` (`StudentName`);
可以查看student表的所有索引SHOW INDEX FROM student;
還有第三種,CREATE INDEX 索引名 on 表(字段),到后面演示。
三、百萬數(shù)據(jù)測試索引效果
1. 再來創(chuàng)建個測試表
-- 創(chuàng)建表 CREATE TABLE `app_user`( `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(50) DEFAULT '' COMMENT '昵稱', `email` VARCHAR(50) DEFAULT NULL COMMENT "郵箱", `phone` VARCHAR(20) DEFAULT NULL COMMENT "手機號", `gender` TINYINT(4) DEFAULT NULL COMMENT "性別 0-男, 1-女", `password` VARCHAR(100) NOT NULL COMMENT "密碼", `age` TINYINT(4) NOT NULL COMMENT "年齡", `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP, `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) )ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='app用戶表';
執(zhí)行創(chuàng)建成功,現(xiàn)在插入數(shù)據(jù)。
2. 插入數(shù)據(jù)
-- 插入百萬數(shù)據(jù) DELIMITER $$ CREATE FUNCTION mock_data() RETURNS INT BEGIN DECLARE num INT DEFAULT 1000000; DECLARE i INT DEFAULT 0; WHILE i < num DO -- 插入語句 INSERT INTO `school`.`app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`)VALUES (CONCAT('用戶',i), '123456@qq.com', CONCAT('18', FLOOR(RAND()*((999999999-100000000)+100000000))), FLOOR(RAND()*2), UUID(), FLOOR(RAND()*100)); SET i = i+1; END WHILE; RETURN i; END;
執(zhí)行可能會出現(xiàn)This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary報錯。
如果出現(xiàn),可以先執(zhí)行set global log_bin_trust_function_creators=TRUE ,然后再試下。
執(zhí)行成功后,再執(zhí)行:
SELECT mock_data();
大概持續(xù)1分鐘左右,別急。
3. 測試查詢
未加索引
查詢一條數(shù)據(jù)。
SELECT * FROM `app_user` WHERE `name`='用戶9999'
多次執(zhí)行查詢,發(fā)現(xiàn)花費時間,穩(wěn)定在0.63s左右,這個從點擊執(zhí)行到看到結(jié)果,已經(jīng)從肉眼可以感知有點慢了。
我們可以增加關(guān)鍵詞EXPLAIN分析sql執(zhí)行的情況。
EXPLAIN SELECT * FROM `app_user` WHERE `name`='用戶9999'
可以看到這個語句查了99W+條,這都是時間消耗。
添加索引
用上面說的第三種方式,增加一個常規(guī)索引。
CREATE INDEX id_app_user_name ON app_user(`name`);
再重新執(zhí)行下查詢:
SELECT * FROM `app_user` WHERE `name`='用戶9999'
查詢時間大幅縮短,只需要要0.1s+。
再分析下加了索引后的查詢。
EXPLAIN SELECT * FROM `app_user` WHERE `name`='用戶9999'
只查了一條,精準查詢。
四、索引使用原則
索引雖然好用,但是不可以濫用,這里有幾個原則可以記一下:
- 索引不是越多越好。
- 不要對經(jīng)常變動的數(shù)據(jù)加索引。
- 小數(shù)據(jù)量的表不需要加索引。
- 索引一般加在常用來查詢的字段上。
以上就是對索引的簡單介紹,但是MySQL索引背后的數(shù)據(jù)結(jié)構(gòu)及算法原理,東西可就多了,有一個大佬講的挺細的,有興趣可以翻下,傳送門
以上就是Mysql數(shù)據(jù)庫百萬數(shù)據(jù)測試索引效果的詳細內(nèi)容,更多關(guān)于Mysql百萬數(shù)據(jù)測試索引的資料請關(guān)注腳本之家其它相關(guān)文章!
相關(guān)文章
MySQL8.0登錄時出現(xiàn)Access?denied?for?user?‘root‘@‘localhost‘?
這篇文章主要給大家介紹了解決MySQL8.0登錄時出現(xiàn)Access?denied?for?user?‘root‘@‘localhost‘?(using?password:?YES)?拒絕訪問的問題,文中有詳細的解決方法,需要的朋友可以參考下2023-09-09