Mysql數(shù)據(jù)庫百萬級數(shù)據(jù)測試索引效果
Mysql數(shù)據(jù)庫百萬數(shù)據(jù)測試索引
Mysql官方對索引的定義是：索引(index)是幫助Mysql高效獲取數(shù)據(jù)的數(shù)據(jù)結構。進而，我們可以知道索引的本質是數(shù)據(jù)結構。
一、索引的分類
主鍵索引：也就是我們常見的 PRIMARY KEY，只有一個列作為主鍵，唯一標識，不可重復。
唯一索引：UNIQUE KEY，避免重復的列出現(xiàn)，唯一索引是可以有多個，同一張表里的多個列都可以設置唯一索引。
常規(guī)索引：KEY/INDEX，默認的類型，通過關鍵字INDEX或者KEY來設置。
全文索引：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í)行到看到結果，已經從肉眼可以感知有點慢了。
我們可以增加關鍵詞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'
只查了一條，精準查詢。
四、索引使用原則
索引雖然好用，但是不可以濫用，這里有幾個原則可以記一下：
- 索引不是越多越好。
- 不要對經常變動的數(shù)據(jù)加索引。
- 小數(shù)據(jù)量的表不需要加索引。
- 索引一般加在常用來查詢的字段上。
以上就是對索引的簡單介紹，但是MySQL索引背后的數(shù)據(jù)結構及算法原理，東西可就多了，有一個大佬講的挺細的，有興趣可以翻下，傳送門
以上就是Mysql數(shù)據(jù)庫百萬數(shù)據(jù)測試索引效果的詳細內容，更多關于Mysql百萬數(shù)據(jù)測試索引的資料請關注腳本之家其它相關文章！
相關文章
這篇文章主要介紹了Docker mysql 主從配置詳解及實例的相關資料,需要的朋友可以參考下2016-11-11
這篇文章主要介紹了MySQL中的binlog相關命令和恢復技巧,需要的朋友可以參考下2014-05-05
正則表達式的作用是匹配文本,將一個正則表達式與一個文本串進行比較,下面這篇文章主要給大家介紹了關于SQL如何使用正則表達式對數(shù)據(jù)進行過濾的相關資料,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下2022-09-09
首先呢，需要有兩個mysql服務器。如果做測試的話可以在同一臺機器上裝兩個mysql服務程序，注意要兩個運行程序的端口不能一樣。我用的是一個是默認的3306，從服務器用的是3307端口。2009-05-05
這篇文章主要為大家詳細介紹了mysql 8.0.13 安裝配置方法圖文教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2018-11-11
這篇文章主要介紹了mysql8連接不上問題及解決,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-08-08
這篇文章主要介紹了mysql的基礎語句和外鍵查詢及其語句詳解(推薦),本文給大家介紹的非常詳細,對大家的學習或工作具有一定的參考借鑒價值,需要的朋友參考下吧2025-04-04
一文帶你永久擺脫Mysql時區(qū)錯誤問題(idea數(shù)據(jù)庫可視化插件配置)
在MySQL啟動時會檢查當前系統(tǒng)的時區(qū)并根據(jù)系統(tǒng)時區(qū)設置全局參數(shù)system_time_zone的值,下面這篇文章主要給大家介紹了關于如何永久擺脫Mysql時區(qū)錯誤問題(idea數(shù)據(jù)庫可視化插件配置)的相關資料,需要的朋友可以參考下2022-08-08
MySQL8.0索引新特性,支持降序索引,降序索引以降序存儲鍵值,下面這篇文章主要給大家介紹了關于MySQL8.0中你必須要知道的索引新特性的相關資料,需要的朋友可以參考下2022-11-11
這篇文章主要介紹了MySQL數(shù)據(jù)庫事務與鎖深入分析，內容介紹的非常詳細，有對這方面不懂的同學可以跟著小編一起研究下吧2020-12-12