【Mysql】資料庫索引，百萬資料測試索引效果

把蘋果v咬哭發表於2021-05-25
MySql資料庫索引

Mysql官方對索引的定義是：索引（index）是幫助Mysql高效獲取資料的資料結構。進而，我們可以知道索引的本質是資料結構。

一、索引的分類

  • 主鍵索引：也就是我們常見的 PRIMARY KEY，只有一個列作為主鍵，唯一標識，不可重複。
  • 唯一索引：UNIQUE KEY，避免重複的列出現，唯一索引是可以有多個，同一張表裡的多個列都可以設定唯一索引。
  • 常規索引：KEY/INDEX，預設的型別，通過關鍵字INDEX或者KEY來設定。
  • 全文索引：FullText，在特定的資料庫引擎下才支援，作用是快速定位資料。

二、使用索引

在建立表或者建立之後，都可以給欄位增加索引。

比如現在建立一個測試用的表，我先在建立的時候加了前三種型別的索引：

-- 建立表
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 "聯絡電話",
	`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`) -- 常規索引
)ENGINE=INNODB DEFAULT CHARSET=utf8;

執行sql，建立表成功。這時候我繼續增加一個全文索引。

-- 增加一個全文索引型別，前面是索引名稱，括號裡是欄位名
ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `StudentName` (`StudentName`);

可以檢視student表的所有索引SHOW INDEX FROM student;

還有第三種，CREATE INDEX 索引名 on 表(欄位)，到後面演示。

三、百萬資料測試索引效果

1. 再來建立個測試表

-- 建立表
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使用者表';

執行建立成功，現在插入資料。

2. 插入資料

-- 插入百萬資料
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;

執行可能會出現This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary報錯。

如果出現，可以先執行set global log_bin_trust_function_creators=TRUE ，然後再試下。

執行成功後，再執行：

SELECT mock_data();

大概持續1分鐘左右，別急。

3. 測試查詢

未加索引

查詢一條資料。

SELECT * FROM `app_user` WHERE `name`='使用者9999'

多次執行查詢，發現花費時間，穩定在0.63s左右，這個從點選執行到看到結果，已經從肉眼可以感知有點慢了。

我們可以增加關鍵詞EXPLAIN分析sql執行的情況。

EXPLAIN SELECT * FROM `app_user` WHERE `name`='使用者9999'

可以看到這個語句查了99W+條，這都是時間消耗。

新增索引
用上面說的第三種方式，增加一個常規索引。

CREATE INDEX id_app_user_name ON app_user(`name`);

再重新執行下查詢：

SELECT * FROM `app_user` WHERE `name`='使用者9999'

查詢時間大幅縮短，只需要要0.1s+。

再分析下加了索引後的查詢。

EXPLAIN SELECT * FROM `app_user` WHERE `name`='使用者9999'

只查了一條，精準查詢。

三、索引使用原則

索引雖然好用，但是不可以濫用，這裡有幾個原則可以記一下：

  • 索引不是越多越好。
  • 不要對經常變動的資料加索引。
  • 小資料量的表不需要加索引。
  • 索引一般加在常用來查詢的欄位上。

以上就是對索引的簡單介紹，但是MySQL索引背後的資料結構及演算法原理，東西可就多了，有一個大佬講的挺細的，有興趣可以翻下，傳送門

相關文章