MySQL索引(Index)

作用

对博主来说，索引就是牺牲存储空间，提高查询时间，比如没索引之前，你在一个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';

原则

• 索引不是越多越好
• 经常变动的数据不要加索引，每一次变动需要同时修改索引的结构
• 数据量小不需要加索引
• 常用来查询的数据表，最好加上索引