用户权限与事务

### EXPLAIN语句

  • 用于查看mysql执行sql命令的相关信息
  • 查看执行过程的语句主要有select、insert、update、delete等
    image-20191203154758600
列名 说明
id SQL语句标识符，如果仅包含一个表的简单查询id始终为1；如果查询是多表连接查询，id按照顺便往下排
select_type 查询类型，例如：SIMPLE（简单查询不包括子查询和union运算符）
table 语句相关的表名
partitions 语句相关的表分区，非分区表始终为NULL
type 语句中表对象间的连接类型。如果值为all代表使用低效的全表扫方式；如果值为const代表仅有一行匹配的结果记录；如果值为range代表使用了索引范围查找；ref表示索引列使用了=或<=>比较运算符。
possible_keys 执行时有可能用到的索引名称
key 执行时实际用到的索引名称
key_len 执行时使用索引的长度
ref 哪些表列或常数列与“key”列出的索引名进行了比较操作
rows 执行的语句所涉及的行记录数，该数值对于INNODB引擎的表为估算值不是实际真实值
filtered 返回结果的行记录数占执行中所有读取到的行记录数的百分比，该数值为估算值，最高为100
Extra 执行SQL语句时一些相关的补充信息

Mysqlslap压力测试工具

  • mysqlslap是MySQL自带压力测试工具

  • 增加并发用户数量和查询次数比较多种方式的运行效率

  • mysqlslap命令常用选项如下所示：

    • -u： 连接数据库用户名
    • -p： 连接数据库用户密码
    • - - concurrency：并发连接客户端数量
    • - -query：运行的测试SQL语句
    • - -create-schema：测试SQL语句所在数据库
    • - -number-of-queries：所有连接客户端运行测试SQL语句的次数
    • - -iterations：本次测试的重复执行的次数

  • 帮助信息查看

    • 使用man 命令
      image-20191203160043026
    • 使用--help
      image-20191203160120476

  • 测试
    image-20191203162043832

    Benchmark
	Average number of seconds to run all queries: 1.076 seconds
	Minimum number of seconds to run all queries: 1.076 seconds
	Maximum number of seconds to run all queries: 1.076 seconds
	Number of clients running queries: 1000
	Average number of queries per client: 0
#基准测试
#运行所有查询的平均秒数：1.076秒
#运行所有查询的最小秒数：1.076秒
#运行所有查询的最大秒数：1.076秒
#运行查询的客户端数：1000
#每个客户的平均查询数量：0

SQL书写注意事项

  • 不要使用表达式作为查询条件
  • 尽量使用in运行符来替代or进行运算
  • 条件列表值如果连续使用between替代<= and >=
  • 无重复记录的结果集使用union all合并
  • 可以使用where就不使用having
  • 使用like操作符时通配符尽量放在字符串右侧

mysql用户管理

  • MySQL用户验证的三个基本要素

    • MySQL 使用“用户名”、“客户端主机”和“密码” 三个基本要素来验证用户

    • MySQL数据库使用“用户名”和“客户端主机”来区分不同的用户，例如：root@192.168.2.3和root@localhost为不同的用户

    • MySQL的用户以行的形式存放在mysql数据库的user表中

    • MySQL用户

      image-20191203163036249

      select user,host,authentication_string from mysql.user;
#user 用户
#host 主机
	#localhost表示本机同127.0.0.1
	#主机可以使用通配符%表示所有主机或192.168.1.%某个网段内的主机
#authentication_string 加密后的密码

  • 用户创建
    image-20191203171024531

    	create user 'test1'@'localhost' identified by 'C1d2try.';
	#语法格式：create user 'username'@'host' identified by 'password';

  • 设置用户自身密码

    image-20191203171244253

    也可以指定指定要修改密码的用户

    image-20191203171931391

  • 用户重命名

    image-20191203185921765

mysql权限管理

  • 权限的范围分类

    • 全局 *.*
    • 数据库级 <dbname>.*
    • 表级 <dbname>.<tabname>
    • 列级 权限类别(col_name) on <dbname>.<tabname>
    • 存储过程 <dbname>.<procname>

  • 权限的分类

    • SELECT 查询，应用范围全局、数据库级、表级
    • INSERT 插入记录，全局、数据库级、表级
    • UPDATE 更新记录，全局、数据库级、表级
    • DELETE 删除记录，全局、数据库级、表级
    • CREATE 创建，全局、数据库级、表级
    • ALTER 修改表属性，全局、数据库级、表级
    • DROP 删除所有，全局、数据库级、表级
    • FILE 访问主机文件，全局
    • PROCESS 查看当前所有mysql用户的会话进程详情，全局
    • SHUTDOWN 关闭mysql服务（允许用户使用shutdown语句），全局

  • 用户权限查看

    image-20191203194153360

    或使用show命令

    image-20191203194303069

    或指定显示的用户及主机

    image-20191203194610740

  • 授权命令

    • 语法：grant 权限(colname) on . to username@host identified by 'password ';

      image-20191203194427043

      image-20191203194610740

      grant select on *.* to test2@'%' identified by 'L1g2qwe.';
#授予test2用户select权限应用在所有数据库的所有表中

  • 撤销授权命令

    • 语法：revoke 权限(colname) on . from username@host;
      image-20191203194807626

      image-20191203195744601

  • 权限相关的表

    权限表 级别 说明
    mysql.user 全局 针对服务器已知的每个用户包含一个记录 *.*
    mysql.db 数据库级 特定某个数据库 .*
    mysql.tables_priv 表级 特定数据库的某个表.
    mysql.columns_priv 列级 特定数据库特定表的某些列

  • mysql用户验证流程图

    image-20191203202307521

  • 会话进程查看

    image-20191203202508419

    +------+------+-----------+------+---------+------+----------+------------------+
| Id   | User | Host      | db   | Command | Time | State    | Info             |
+------+------+-----------+------+---------+------+----------+------------------+
| 1163 | root | localhost | NULL | Query   |    0 | starting | show processlist |
| 1165 | root | localhost | NULL | Sleep   |    3 |          | NULL             |
+------+------+-----------+------+---------+------+----------+------------------+
#Id 用户连接标识符
#User MySQL 用户
#Host 客户机的主机名
#db 用户选择的数据库，未选为 NULL
#Command 进程正在执行的命令类型
#Time 进程处于当前状态的时间（秒）
#State 指示线程正在执行的内容的操作、事件或状态
#Info 进程正在执行的语句；未执行操作为 NULL

  • 查看当前连接的标识符

    image-20191203202916931

    用于区分多个同时登陆的同名用户

  • 强制使当前用户重连

    image-20191203203814303

    kill PID

事务

  • 事务是将一系列DML语句分组操作执行的一种机制，每一组操作对应一个事务

  • 在组内的所有DML操作步骤或者都成功，或者都失败；如果组内所有步骤都执行正常，则提交，使数据库记录其操作状态；如果组内有些步骤出现错误或不完整，则取消组内执行的所有操作，使数据库回到操作之前的状态

  • 一个连接会话同一时刻只能有一个事务，MySQL默认采用自动提交机制，每个DML语句后面都自动添加commit，也就是说每个语句都是一个事务

  • 事务的特性

    • Atomicity （原子性）
    • 一个事务中的所有语句作为一个单元要么都成功，要么都取消
    • Consistent (一致性)
      • 对数据同时进行的多个操作不破坏数据的一致性
    • Isolated (隔离性)
      • 事务之间相互隔离，不互相影响。即：未完成的事务对于数据产生的变化对于并发的其他事务不可见
    • Durable (持久性)
      • 事务成功完成后，所做的所有对数据的变更都会准确地记录在数据库中，这些变更通常以日志的形式记录下来，以确保所做的变更不会丢失

  • mysql事务存储引擎

    • INNODB是MySQL的默认存储引擎

  • mysql事务控制命令

    语句 说明
    START TRANSACTION（或BEGIN） 显式开始一个新事务，直到通过COMMIT 或ROLLBACK 显式结束
    SAVEPOINT spname 事务中可以回滚到的位置的唯一标识符
    COMMIT 永久记录当前事务所做的更改，事务显示结束
    ROLLBACK 取消当前事务所做的更改，事务显示结束
    ROLLBACK TO spname 取消在savepoint 之后执行的更改，事务未结束
    RELEASE SAVEPOINT 删除savepoint 标识符，不会删除任何事务语句
    SET AUTOCOMMIT 为当前连接禁用或启用默认autocommit 模式

  • 关闭自动提交机制

    image-20191203205958893

    或使用begin命令

    image-20191203210035264

    也可以使用set命令设置默认禁用或启用自动提交，SET AUTOCOMMIT=0 禁止自动提交，SET AUTOCOMMIT=1 开启自动提交

    image-20191203211152386

  • 事务的提交

    • 显式提交

      commit命令提交事务

      image-20191203210740110

      rollback命令事务回滚

      image-20191203210817248

    • 隐式提交

      部分命令会间接导致事务的提交称为隐式提交，包括：n create; alter; drop; grant; revoke; set password; start transaction; truncate 等

  • 事务的级别

    隔离级别 脏读 不可重复读 幻读 说明
    读未提交 read uncommited 未解决 未解决 未解决 不满足ACID中的事务隔离性
    读提交 read committed 解决 未解决 未解决 Oracle等数据库默认
    可重复读 repeatable read 解决 解决 未解决 MySQL默认级别
    串行化 serializable 解决 解决 解决 并发事务 效率极低

  • 数据库事务隔离中的问题

    • 脏读：读取（select）到了其他事务中未提交的数据
    • 不可重复读：每次读取（select），同样的查询语句，看到的结果不一样。看到了其他事务更新并提交后的新数据
    • 幻读：幻读也称作幽灵读。读取（select）数据时，同样的查询语句，第二次返回的结果比第一次读取时莫名其妙地多了（或少了）一些记录行。看到了其他事务插入（或删除）并提交后

  • 查看当前事务隔离级别

    image-20191203211923568

    show variables like 'tx_iso%';
#REPEATABLE-READ 可重复读，默认级别

  • 修改事务隔离级别

    image-20191203212134797

    set global tx_isolation='serializable';
set global transaction_isolation='serializable';
#变量tx_isolation与transaction_isolation在5.7.20及之后的版本功能相同
#可设置的值包括
	#READ-UNCOMMITTED
	#READ-COMMITTED
	#REPEATABLE-READ
	#SERIALIZABLE
posted @ 2019-12-03 21:28  MirL  阅读(284)  评论(0)  编辑  收藏  举报