前言：

本文内容：SQL100万条数据测试索引、索引原则、数据库用户管理

推荐免费MySQL基础讲解视频：【狂神说Java】MySQL最新教程通俗易懂_哔哩哔哩_bilibili

SQL编程创建100万条数据测试索引

索引在处理大量数据的时候，对查询速度有很大提升；

测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
-- 测试索引
-- 创建用户测试表
create table `app_user`(
	`id` bigint(20) unsigned not null auto_increment,
	`name` varchar(50) default '' comment '用户名',
	`email` varchar(50) default '' comment '邮箱',
	`phone` varchar(20) default '' comment '手机号',
	`gender` tinyint(4) default '0' comment '性别(0男 1女)',
	`password`varchar(100) not null comment '密码',
	`age` tinyint(4) default '0' 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=utf8mb4 comment='app用户表'

-- 插入100w条数据
delimiter $$ -- 函数标志
create function mock_data()
-- mysql 8.0 需要加deterministic 
returns int deterministic
begin
	declare num int default 1000000;
	declare i int default 0;
	while i<num do
		-- 插入语句
		insert into app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)values(concat('用户',i),'jokerdaxiong@qq.com',concat('18',floor(rand()*(999999999-100000000)+100000000)),floor(rand()*2),uuid(),floor(rand()*100));
		set i = i+1;
	end while;
	return i;
-- mysql 8.0 end 后也要加$$ 
end $$;
-- 执行函数
select mock_data();
-- 查询结果
select * from app_user;

-- 测试索引
select * from app_user; -- 查询时间: 2.181s
select * from app_user where `name` = '用户9999'; --  查询时间: 0.994s
-- 分析查询
explain select * from app_user where `name` = '用户9999'; -- rows:992494

-- 创建索引
-- create index 索引名 on 表(字段)
-- 耗时：时间: 5.565s
create index id_app_user_name on app_user(`name`);
-- 在执行这条查询语句 耗时：时间: 0.001s 速度提升很大
select * from app_user where `name` = '用户9999'; 
-- 分析查询
explain select * from app_user where `name` = '用户9999'; -- rows:1

索引原则

  • 索引不是越多越好
  • 不要对经常变动的数据加索引
  • 小数据量的表不需要加索引
  • 索引一般加在常用来查询的字段上

拓展：去了解索引的数据结构

数据库用户管理

可视化管理

172

171

170

SQL命令操作

用户表：mysql.user

本质：对这张表进行增删改查

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 创建用户
-- create user 用户名 identified by '密码';
create user joker identified by '123456';
-- 修改当前用户密码
flush privileges; -- 刷新权限
ALTER user 'root'@'localhost' IDENTIFIED BY '123456';-- 修改密码为123456
-- 修改指定用户密码 
flush privileges;-- 刷新权限
ALTER user 'joker'@'%' IDENTIFIED BY '12321';
-- 重命名 rename user 原名称 to 新名称;
rename user joker to joker1;
-- 授予所有权限 grant all privileges on *.* to 要授权的用户; 
-- 但是不能给其他用户授权
grant all privileges on *.* to joker1;
-- 查看用户权限
show grants for joker1;
-- 查询root权限
show grants for root@localhost;
-- 撤销权限 revoke all privileges on *.* from 要撤销的用户
revoke all privileges on *.* from joker1;
-- 删除用户
drop user joker1;
-- 查询用户表
select * from user;