MySQL(06) mysql用户管理、权限管理和日志管理

一、MySQL用户管理

MySQL数据库的安全性需要通过账户管理来保证。

1、登录MySQL服务器

最全的登录命令

---

2、查询MySQL 所有用户

MySQL用户可以分为普通用户和root用户。root用户是超级管理员，拥有所有权限，包括创建用户、删除用户和修改用户的密码等管理权限；普通用户只拥有被授予的各种权限。

mysql数据库下的user表

-- 查询 MySQL 中的所有用户及其允许连接的主机
SELECT HOST,USER FROM mysql.user;

Host 列：用户允许连接的主机名（% 表示允许任意主机）

---

3、创建、删除、修改用户

创建用户

创建zhang3用户，默认host是%

CREATE USER 'zhang3' IDENTIFIED BY 'abc123'; 

如果不想host是%，可以指定

得出结论：mysql.user表的主键是host字段和user字段的联合主键

修改用户名

修改后要记得 flush privileges

删除用户

---

4、设置当前用户密码

适用于root用户修改自己的密码，以及普通用户登录后修改自己的密码。

方式一：使用ALTER USER命令来修改当前用户密码

把当前用户的密码修改为abcabc

方式二：SET命令修改

该set语句会自动将密码加密后再赋值给当前用户 

---

5、root修改其他用户的密码

root用户不仅可以修改自己的密码，还可以修改其它普通用户的密码。root用户登录MySQL服务器后，可以通过ALTER语句和SET语句来修改普通用户的密码。

方式一：使用ALTER语句修改普通用户的密码 

方式二：使用SET命令修改普通用户的密码

---

二、MySQL权限管理

1、MySQL权限分布

 查看MySQL有哪些权限

-- 查看权限列表
SHOW PRIVILEGES;

MySQL权限分布

---

2、授予权限的原则

权限控制主要是出于安全因素，因此需要遵循以下几个经验原则：
1、只授予能满足需要的最小权限，防止用户干坏事。比如用户只是需要查询，那就只给select权限就可以了，不要给用户赋予update、insert或者delete权限。
2、创建用户的时候限制用户的登录主机，一般是限制成指定IP或者内网IP段。
3、为每个用户设置满足密码复杂度的密码。
4、定期清理不需要的用户，回收权限或者删除用户。

---

3、授权命令

查看当前用户权限

SHOW GRANTS;

给用户授权

对'zhang3'@'%'用户授权

可以使用GRANT重复给用户添加权限，权限叠加，比如你先给用户添加一个SELECT权限，然后又给用户添加一个INSERT权限，那么该用户就同时拥有了SELECT和INSERT权限。

---

4、授权所有 all privileges

特殊授权操作

此时，li4用户在权限层面来说和root的区别：不能给别人赋予权限

如果需要赋予包括GRANT的权限，添加参数“WITH GRANT OPTION”这个选项即可，表示该用户可以将自己拥有的权限授权给别人。经常有人在创建操作用户的时候不指定WITH GRANT OPTION选项导致后来该用户不能使用GRANT命令创建用户或者给其它用户授权。

---

5、收回权限命令

MySQL中使用REVOKE语句取消用户的某些权限。

建议大家，尽量使用数据库自己的角色和用户机制来控制访问权限，不要轻易用Root账号。因为Root账号密码放在代码里面不安全，一旦泄露，数据库就会完全失去保护。而且，MySQL的权限控制功能十分完善，应该尽量利用，可以提高效率，而且安全可靠。

---

三、MySQL日志管理

1、MySQL日志

MySQL有不同类型的日志文件，用来存储不同类型的日志，分为二进制日志、错误日志、通用查询日志和慢查询日志，这也是常用的4种。MySQL8又新增两种支持的日志：中继日志和数据定义语句日志。使用这些日志文件，可以查看MySQL内部发生的事情。

除二进制日志外，其他日志都是文本文件。默认情况下，所有日志创建于MySQL数据目录中。

---

2、日志的弊端

日志功能会降低MySQL数据库的性能。例如，在查询非常频繁的MySQL数据库系统中，如果开启了通用查询日志和慢查询日志，MySQL数据库会花费很多时间记录日志。

日志会占用大量的磁盘空间。对于用户量非常大、操作非常频繁的数据库，日志文件需要的存储空间甚至比数据库文件需要的存储空间还要大。

---

3、通用查询日志

通用查询日志：记录所有连接的起始时间和终止时间，以及连接发送给数据库服务器的所有指令，对我们复原操作的实际场景、发现问题，甚至是对数据库操作的审计都有很大的帮助。

---

4、错误日志

错误日志：记录MySQL服务的启动、运行或停止MySQL服务时出现的问题，方便我们了解服务器的状态，从而对服务器进行维护。

---

5、二进制日志

二进制日志：记录所有更改数据的语句（DDL和DML），可以用于主从服务器之间的数据同步，以及服务器遇到故障时数据的无损失恢复。

---

6、中继日志

中继日志：用于主从服务器架构中，从服务器用来存放主服务器二进制日志内容的一个中间文件。从服务器通过读取中继日志的内容，来同步主服务器上的操作。

---

7、数据定义语言日志

数据定义语句日志：记录数据定义语句执行的元数据操作。

---

8、慢查询日志

慢查询日志：记录所有执行时间超过long_query_time（默认10s）的所有查询，方便我们对查询进行优化。
查看慢查询日志是否开启，及其位置。

show variables like '%slow%'

SQL优化

select *

问题：select *不会走覆盖索引，会出现大量的回表操作，而从导致查询sql的性能很低。

sql语句查询时，只查需要用到的列，多余的列根本无需查出来。

小表驱动大表

1

高效的分页

select id,name,age 
from user limit 10,20;

如果表中数据量少，用limit关键字做分页，没啥问题。但如果表中数据量很多，用它就会出现性能问题。

select id,name,age 
from user limit 1000000,20;

mysql会查到1000020条数据，然后丢弃前面的1000000条，只查后面的20条数据，这个是非常浪费资源的。

那么，这种海量数据该怎么分页呢？

先找到上次分页最大的id，然后利用id上的索引查询。不过该方案，要求id是连续的，并且有序的。

select id,name,age 
from user where id > 1000000 limit 20;

还能使用between优化分页。

select id,name,age 
from user where id between 1000000 and 1000020;

用连接查询代替子查询

mysql中如果需要从两张以上的表中查询出数据的话，一般有两种实现方式：子查询 和 连接查询

子查询语句的优点是简单，结构化，如果涉及的表数量不多的话。

但缺点是mysql执行子查询时，需要创建临时表，查询完毕后，需要再删除这些临时表，有一些额外的性能消耗

join时要注意

我们在涉及到多张表联合查询的时候，一般会使用join关键字。而join使用最多的是left join和inner join。

• left join：求两个表的交集外加左表剩下的数据。
• inner join：求两个表交集的数据。

select o.id,o.code,u.name 
from order o 
inner join user u on o.user_id = u.id
where u.status=1;

如果两张表使用inner join关联，mysql会自动选择两张表中的小表，去驱动大表，所以性能上不会有太大的问题。

使用left join的示例如下：

select o.id,o.code,u.name 
from order o 
left join user u on o.user_id = u.id
where u.status=1;

如果两张表使用left join关联，mysql会默认用left join关键字左边的表，去驱动它右边的表。如果左边的表数据很多时，就会出现性能问题。

要特别注意的是在用left join关联查询时，左边要用小表，右边可以用大表。如果能用inner join的地方，尽量少用left join。

索引优化

很多时候sql语句，走了索引，和没有走索引，执行效率差别很大。所以索引优化被作为sql优化的首选。

那么，如何查看sql走了索引没？

可以使用explain命令，查看mysql的执行计划。