目录
一.MySQL索引
1.MySQL索引的基本概念
索引(Index)是帮助MySQL高效获取数据的数据结构。我们可以简单理解为:它是快速查找排好序的一种数据结构。
可以用来快速查询数据库表中的特定记录,所有的数据类型都可以被索引。
Mysql索引主要有两种结构:B+Tree索引和Hash索引
1)优缺点
优点
- 可以大大提高MySQL的检索速度
- 索引大大减小了服务器需要扫描的数据量
- 索引可以帮助服务器避免排序和临时表
- 索引可以将随机IO变成顺序IO
缺点
- 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存索引文件。
- 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快。
- 如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果。
- 对于非常小的表,大部分情况下简单的全表扫描更高效;
2)分类
普通索引
- 不应用任何限制条件的索引,该索引可以在任何数据类型中创建。
- 字段本身的约束条件可以判断其值是否为空或唯一。
- 创建该类型索引后,用户在查询时,便可以通过索引进行查询。
唯一性索引
- 使用UNIQUE参数可以设置唯一索引。
- 创建该索引时,索引的值必须唯一,通过唯一索引,用户可以快速定位某条记录
- 主键是一种特殊唯一索引。
全文索引
- 使用FULLTEXT参数可以设置索引为全文索引。
- 全文索引只能创建在CHAR、VARCHAR或者TEXT类型的字段上。查询数据量较大的字符串类型的字段时,使用全文索引可以提高查询速度。
- 在默认情况下,应用全文搜索大小写不敏感。如果索引的列使用二进制排序后,可以执行大小写敏感的全文索引。
单列索引
- 顾名思义,单列索引即只对应一个字段的索引。
- 应用该索引的条件只需要保证该索引值对应一个字段即可。
- 可以包括普通、唯一、全文索引
多列索引
- 多列索引是在表的多个字段上创建一个索引。
- 该索引指向创建时对应的多个字段,用户可以通过这几个字段进行查询。
- 要想应用该索引,用户必须使用这些字段中的第一个字段。
2.创建索引
1)普通索引index
- 一个表中可以有多个index
- 字段的值可以重复,且可以赋值为null
- 通常在where条件中的字段上配置Index
- index索引字段的标志为mul
创建表时创建索引
CREATE TABLE 表名( 字段列表, index(字段名), index(字段名), );
在已有表中创建索引
CREATE INDEX 索引名 ON 表名(字段名);
3.查看索引
DESC 表名; # 注意观察Key这一列 或 SHOW INDEX FROM 表名 \G
4.删除索引
DROP INDEX 索引名 ON 库.表名;
二.用户及授权
1.授权
1)创建用户并授权
语法:
GRANT 权限列表 ON 库名.表名 TO '用户名'@'客户端地址' IDENTIFIED BY '密码' WITH GRANT OPTION;
权限列表:用户的操作权限,如
SELECT
,INSERT
,UPDATE
等,如果要授予所的权限则使用ALL
表名:表名,如果要授予该用户对所有数据库和表的相应操作权限则可用
*
表示,如*.*
WITH GRANT OPTION:用户拥有授权权限
示例:
# 授予zzg用户在本地登陆的权限 mysql> grant select,update(phone_number,email) on nsd2021.employees to zzg@'localhost' identified by 'NSD2021@tedu.cn'; # 授予zzg用户在任意地址登陆的权限 GRANT SELECT, INSERT, UPDATE(phone_number,email) ON nsd2021.employees to zzg@'%' IDENTIFIED BY 'NSD2021@tedu.cn';
客户端连接测试
# 安装mysql/mariadb客户端 [root@zzgrhel8 ~]# yum install -y mariadb [root@zzgrhel8 ~]# mysql -h服务器 -u用户名 -p密码
2.相关查询指令
1)查看用户信息
SELECT USER();
2)显示登陆用户自己的权限
SHOW GRANTS;
3)管理员查看指定用户的权限,用户不存在则报错
SHOW GRANTS FOR 用户名@'客户端地址';
4)用户修改自己的密码
SET password=password('密码');
5)管理员修改指定用户密码
SET PASSWORD FOR 用户名@'客户端地址'=password('密码');
6)删除用户
DROP USER 用户名@'客户端地址';
3.授权库mysql
1)相关表
user:记录已有的授权用户及权限。该表中主要关心host和user字段
db:记录已有授权用户对数据库的访问权限。该表中主要关心host、db和user字段
tables_priv
:记录已有授权用户对表的访问权限
columns_priv
:记录已有授权用户对字段的访问权限
mysql> grant select,insert,update(phone_number,email) on nsd2021.employees to zzg@'localhost' identified by 'NSD2021@tedu.cn';
2)查看所有授权用户
mysql> select user, host from mysql.user; +-----------+-----------+ | user | host | +-----------+-----------+ | root | % | | tom | % | | zzg | % | | mysql.sys | localhost | | root | localhost | | zzg | localhost | +-----------+-----------+ 6 rows in set (0.01 sec)
3)查询zzg@'%'的权限
mysql> show grants for zzg@'%'; mysql> select host, user, db from mysql.db; +-----------+-----------+---------+ | host | user | db | +-----------+-----------+---------+ | % | tom | nsd2021 | | localhost | mysql.sys | sys | +-----------+-----------+---------+ 2 rows in set (0.00 sec) mysql> select * from tables_priv where User like '%zzg%'\G *************************** 1. row *************************** Host: localhost Db: nsd2021 User: zzg Table_name: employees Grantor: root@localhost Timestamp: 0000-00-00 00:00:00 Table_priv: Select,Insert Column_priv: Update 1 row in set (0.00 sec)
4.撤销权限
语法
REVOKE 权限列表 ON 库名.表名 FROM 用户名@'客户端地址';
示例:
# 查看用户有哪些权限 SELECT host, user FROM mysql.user;
# 查看权限 SHOW GRANTS FOR 用户名@'客户端地址';
# 撤回授权权限 REVOKE GRANT OPTION ON *.* FROM 用户名@'客户端地址';
# 撤回用户删除权限 REVOKE DELETE ON *.* FROM 用户名@'客户端地址';
# 创建tom用户,具有授权权限 mysql> grant all on *.* to tom@'%' identified by 'NSD2021@tedu.cn' with grant option;
# tom登陆后,创建jerry用户 [root@zzgrhel8 ~]# mysql -utom -pNSD2021@tedu.cn -h192.168.1.11 MySQL [(none)]> grant select on nsd2021.* to 'jerry'@'%' identified by 'NSD2021@tedu.cn';
5.root密码恢复
步骤
停止MySQL服务
跳过授权表启动MySQL服务程序
修改root密码
以正常方式重启MySQL服务程序
示例:
# 停止MySQL服务 [root@mysql1 ~]# systemctl stop mysqld
# 修改配置文件,跳过授权表启动MySQL服务程序 [root@mysql1 ~]# vim /etc/my.cnf [mysqld] skip-grant-tables ... ...
# 启动服务 [root@mysql1 ~]# systemctl start mysqld
# 修改root密码 [root@mysql1 ~]# mysql mysql> update mysql.user set authentication_string=password('123456') -> where user='root' and host='localhost'; Query OK, 1 row affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 1 mysql> flush privileges; Query OK, 0 rows affected (0.01 sec)
# 以正常方式重启MySQL服务程序 [root@mysql1 ~]# systemctl stop mysqld [root@mysql1 ~]# vim /etc/my.cnf [mysqld] # skip-grant-tables ... ... [root@mysql1 ~]# systemctl start mysqld [root@mysql1 ~]# mysql -uroot -p123456