Mysql数据库百万级数据测试索引效果

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索引背后的数据结构及算法原理，东西可就多了，有一个大佬讲的挺细的，有兴趣可以翻下，

以上就是mysql数据库百万数据测试索引效果的详细内容，更多关于mysql百万数据测试索引的资料请关注其它相关文章！