六、索引

十四2001發表於2024-08-01
索引

索引

MySQL官方對索引的定義：索引（index）是幫助MySQL高效獲取資料的資料結構

提取句子主幹，就可以得到索引的本質：索引是資料結構

分類

在一個表中，主鍵索引只能有一個，唯一索引可以有多個

  • 主鍵索引（PRIMARY KEY）
    • 唯一的標識，主鍵不可重複，只能有一個列作為主鍵
  • 唯一索引（UNIQUE KEY）
    • 避免重複的列出現，唯一索引可以重複，多個列都可以標識為 唯一索引
  • 常規索引（KEY/INDEX）
    • 預設的，index，key 關鍵字來設定
  • 全文索引（FullText）
    • 在特定的資料庫引擎下才有，MyISAM
    • 快速定位資料

基礎語法：

-- 索引的使用
-- 1、在成績表的時候給欄位增加索引
-- 2、建立完畢後，增加索引

-- 顯示所有的索引資訊
SHOW INDEX FROM student

-- 增加一個全文索引    （索引名） 列名
ALTER TABLE school.student ADD FULLTEXT INDEX `studentName` (`studentName`);

-- EXPLAIN  分析sql執行的情況

EXPLAIN SELECT * FROM student; -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(studentName) AGAINST('劉');

測試索引

CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT '',
`email` VARCHAR(50) NOT NULL,
`phone` VARCHAR(20) DEFAULT '',
`gender` TINYINT(4) UNSIGNED DEFAULT '0',
`password` VARCHAR(100) NOT NULL DEFAULT '',
`age` TINYINT(4) DEFAULT NULL,
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- 插入100萬資料.
SET GLOBAL log_bin_trust_function_creators=1; -- 開啟建立函式功能
/*
  第一個語句 delimiter 將 mysql 直譯器命令列的結束符由”;” 改成了”$$”，
  讓儲存過程內的命令遇到”;” 不執行
*/
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;
	WHILE i<num DO
		INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`)VALUES(CONCAT('使用者',i),'19224305@qq.com','123456789',FLOOR(RAND()*2));
		SET i=i+1;
	END WHILE;
	RETURN i;
END;$$

SELECT mock_data()$$ -- 執行此函式 生成一百萬條資料


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

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

-- id _ 表名 _ 欄位名
-- CREATE INDEX 索引名 ON 表（欄位）
CREATE INDEX id_app_user_name ON app_user(`name`)
SELECT * FROM app_user WHERE name = '使用者9999'

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

索引在小資料量的時候，用處不大，但是在大資料的時候，區別十分明顯

索引原則

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

索引的資料結構

Hash型別的索引

Btree：InnoDB的預設資料結構

閱讀：[CodingLabs - MySQL索引背後的資料結構及演算法原理](

相關文章