MySQL索引(Index)

MySQL索引

• 作用
• 分類
• SQL操作索引
• 測試
• 原則

作用

對部落客來說，索引就是犧牲存儲空間，提高查詢時間，比如沒索引之前，你在一個100萬資料的表中查詢某條件資料需要10s，而建立了索引可能隻需要0.1s，但是索引是需要存儲空間去存放的

分類

• 主鍵索引：在設定主鍵時，自動增加的索引，唯一，例如

create table `test` (
	...
	primary key (`id`)
)engine=innodb default charset=utf-8;
           

• 唯一索引：使用’Unique‘關鍵字建立的索引，可以設定多個，例如

create table `test` (
	...
	unique key `id_card`(`id_card`) -- 格式為 索引名(列名)
)engine=innodb default charset=utf-8;
           

• 正常索引：使用’Key/Index’關鍵字建立的索引，例如

create table `test` (
	...
	key `email`(`email`) -- 格式為 索引名(列名)
	index `address`(`address`)
)engine=innodb default charset=utf-8;
           

• 全文索引：使用’fulltext’關鍵字建立的索引，例如

create table `test` (
	...
	fulltext index `name`(`name`) -- 格式為 索引名(列名)
)engine=myisam default charset=utf-8; -- 使用了不同的引擎，因為有些資料庫引擎并不支援全文索引
           

SQL操作索引

show index from test;
-- 顯示test表中所有索引
alter table test add fulltext index `name`(`name`);
-- 在建立表之後再增加索引
create index id_test_name on `test`(`name`);
-- 直接在制定表的指定列建立索引
explain select * from test;
-- 分析SQL執行情況
           

測試

編寫函數，插入100萬條資料

create table `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) default 0,
	`password` varchar(100) not null,
	`age` tinyint(4) default 0,
	`gmt_create` datetime default current_timestamp,
	`gmt_modifed` datetime default current_timestamp on update current_timestamp,
	primary key (`id`)
)engine=innodb default charset=utf-8
----------------------------------------------------------------------------------
-- 建立函數 mock_data()
delimiter $$
create function mock_data()
returns int
begin 
	declare n int default 1000000;
	declare i int default 0;
	while i < n do
		insert into `use`(`name`,`email`,`phone`,`gender`,`password`,`age`)
		values(
			concat('使用者', i),
			concat(concat('virtual_user_', i), '@a.com'),
			concat('136', floor(rand() * (999999999 - 100000000) + 10000000)),
			floor(rand() * 2),
			uuid(),
			floor(rand() * 100
		);
		set i = i + 1;
	end while;
	return i;
end;
-- 執行函數
select mock_data();
           

不建立額外索引的情況下，測試查詢

select * from `user` where name = '使用者9999'; 
-- 根據機器性能不同，部落客大概平均在 0.8s 左右
explain select * from `user` where name = '使用者9999';
           

為 name 列建立一個索引，再進行測試

create index id_user_name on `user`(`name`);
-- 實際執行 create index 就是資料庫為制定列建立了一個”樹“或其他資料結構，
-- 并且該資料結構中的資料和原列中的資料一一對應，就是犧牲存儲空間，
-- 如果你學習過資料結構，就應該明白一個好的資料結構，有多麼重要
select * from `user` where name = '使用者9999'; 
-- 根據機器性能不同，部落客大概平均在 0.001s 左右
explain select * from `user` where name = '使用者9999';
           

原則

• 索引不是越多越好
• 經常變動的資料不要加索引，每一次變動需要同時修改索引的結構
• 資料量小不需要加索引
• 常用來查詢的資料表，最好加上索引