Mysql數(shù)據(jù)庫百萬級數(shù)據(jù)測試索引效果
Mysql數(shù)據(jù)庫百萬數(shù)據(jù)測試索引
Mysql官方對索引的定義是：索引(index)是幫助Mysql高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。進(jìn)而，我們可以知道索引的本質(zhì)是數(shù)據(jù)結(jié)構(gòu)。
一、索引的分類
主鍵索引：也就是我們常見的 PRIMARY KEY，只有一個(gè)列作為主鍵，唯一標(biāo)識，不可重復(fù)。
唯一索引：UNIQUE KEY，避免重復(fù)的列出現(xiàn)，唯一索引是可以有多個(gè)，同一張表里的多個(gè)列都可以設(shè)置唯一索引。
常規(guī)索引：KEY/INDEX，默認(rèn)的類型，通過關(guān)鍵字INDEX或者KEY來設(shè)置。
全文索引：FullText，在特定的數(shù)據(jù)庫引擎下才支持，作用是快速定位數(shù)據(jù)。
二、使用索引
在創(chuàng)建表或者創(chuàng)建之后，都可以給字段增加索引。
比如現(xiàn)在創(chuàng)建一個(gè)測試用的表，我先在創(chuàng)建的時(shí)候加了前三種類型的索引：
-- 創(chuàng)建表 CREATE TABLE `student`( `StudentNo` INT(4) NOT NULL COMMENT "學(xué)號", `LoginPwd` VARCHAR(20) DEFAULT NULL, `StudentName` VARCHAR(20) DEFAULT NULL COMMENT "學(xué)生姓名", `Sex` TINYINT(1) DEFAULT NULL COMMENT "性別：0-1", `GradeId` INT(11) DEFAULT NULL COMMENT "年紀(jì)編號", `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)建表成功。這時(shí)候我繼續(xù)增加一個(gè)全文索引。
-- 增加一個(gè)全文索引類型，前面是索引名稱，括號里是字段名 ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `StudentName` (`StudentName`);
可以查看student表的所有索引SHOW INDEX FROM student;
還有第三種，CREATE INDEX 索引名 on 表(字段)，到后面演示。
三、百萬數(shù)據(jù)測試索引效果
1. 再來創(chuàng)建個(gè)測試表
-- 創(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 "手機(jī)號", `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報(bào)錯(cuò)。
如果出現(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)花費(fèi)時(shí)間，穩(wěn)定在0.63s左右，這個(gè)從點(diǎn)擊執(zhí)行到看到結(jié)果，已經(jīng)從肉眼可以感知有點(diǎn)慢了。
我們可以增加關(guān)鍵詞EXPLAIN分析sql執(zhí)行的情況。
EXPLAIN SELECT * FROM `app_user` WHERE `name`='用戶9999'
可以看到這個(gè)語句查了99W+條，這都是時(shí)間消耗。
添加索引
用上面說的第三種方式，增加一個(gè)常規(guī)索引。
CREATE INDEX id_app_user_name ON app_user(`name`);
再重新執(zhí)行下查詢：
SELECT * FROM `app_user` WHERE `name`='用戶9999'
查詢時(shí)間大幅縮短，只需要要0.1s+。
再分析下加了索引后的查詢。
EXPLAIN SELECT * FROM `app_user` WHERE `name`='用戶9999'
只查了一條，精準(zhǔn)查詢。
四、索引使用原則
索引雖然好用，但是不可以濫用，這里有幾個(gè)原則可以記一下：
- 索引不是越多越好。
- 不要對經(jīng)常變動的數(shù)據(jù)加索引。
- 小數(shù)據(jù)量的表不需要加索引。
- 索引一般加在常用來查詢的字段上。
以上就是對索引的簡單介紹，但是MySQL索引背后的數(shù)據(jù)結(jié)構(gòu)及算法原理，東西可就多了，有一個(gè)大佬講的挺細(xì)的，有興趣可以翻下，傳送門
以上就是Mysql數(shù)據(jù)庫百萬數(shù)據(jù)測試索引效果的詳細(xì)內(nèi)容，更多關(guān)于Mysql百萬數(shù)據(jù)測試索引的資料請關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
MySQL中你可能忽略的COLLATION實(shí)例詳解
mysql的collation大致的意思就是字符序，這篇文章主要給大家介紹了關(guān)于MySQL中COLLATION的相關(guān)資料，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-05-05
MySQL UPDATE 語句的非標(biāo)準(zhǔn)實(shí)現(xiàn)代碼
這篇文章主要介紹了MySQL UPDATE 語句的非標(biāo)準(zhǔn)實(shí)現(xiàn)代碼,本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2021-04-04
使用xtrabackup實(shí)現(xiàn)mysql備份
Xtrabackup 是percona公司的開源項(xiàng)目，用以實(shí)現(xiàn)類似innodb官方的熱備份工具InnoDB Hot Backup的功能，能夠非?？焖俚貍浞菖c恢復(fù)MySQL數(shù)據(jù)庫。今天我們就來詳細(xì)討論下Xtrabackup的使用方法2016-11-11
MySQL是中小型網(wǎng)站普遍使用的數(shù)據(jù)庫之一，然而，很多人并不清楚MySQL到底能支持多大的數(shù)據(jù)量，再加上某些國內(nèi)CMS廠商把數(shù)據(jù)承載量的責(zé)任推給它，導(dǎo)致很多不了解MySQL的站長對它產(chǎn)生了很多誤解2008-12-12
MySQL數(shù)據(jù)庫是一款非常好用的數(shù)據(jù)庫管理系統(tǒng)，但是相對來說卸載起來麻煩一些，本文就詳細(xì)的介紹有一下卸載方法，感興趣的可以了解一下2022-03-03
這篇文章主要介紹了MySQL kill指令的使用方法，幫助大家更好的理解和使用MySQL，感興趣的朋友可以了解下2020-12-12
MySQL8.0登錄時(shí)出現(xiàn)Access?denied?for?user?‘root‘@‘localhost‘?
這篇文章主要給大家介紹了解決MySQL8.0登錄時(shí)出現(xiàn)Access?denied?for?user?‘root‘@‘localhost‘?(using?password:?YES)?拒絕訪問的問題,文中有詳細(xì)的解決方法,需要的朋友可以參考下2023-09-09
如何選擇合適的MySQL日期時(shí)間類型來存儲你的時(shí)間
這篇文章主要介紹了如何選擇合適的MySQL日期時(shí)間類型來存儲你的時(shí)間，小編覺得挺不錯(cuò)的，現(xiàn)在分享給大家，也給大家做個(gè)參考。一起跟隨小編過來看看吧2020-08-08
數(shù)據(jù)庫水平切分的實(shí)現(xiàn)原理解析－－－分庫，分表，主從，集群，負(fù)載均衡器2010-05-05
這篇文章主要介紹了企業(yè)級使用LAMP源碼的安裝教程，本文附含源碼示例，有需要的朋友可以借鑒參考下，希望可以有所幫助，祝升職加薪2021-09-09