MySQL 8.0 學習筆記密碼管理角色管理優化器索引通用表達式

新的更安全更快的的认证方式

密码管理

5.7中无密码管理

新增三个密码管理相关配置项

1 2 3 4 5

password_histroy= 3 (不能和最近三次使用过的密码相同)   password_reuse_interval= 90 (不能和最近 90 天使用过的密码相同)   password_require_current=ON (开启后修改密码需要验证旧密码，root用户不需要)

角色管理

MySQL角色是指定的权限集合.

像用户帐户一样，角色可以拥有授予和撤消的权限:

• 可以授予用户帐户角色，授予该帐户与每个角色相关的权限
• 用户被授予角色权限，则该用户拥有该角色的权限。

以下列表总结了MySQL提供的角色管理功能：

• CREATE ROLE并 DROP ROLE角色创建和删除；
• GRANT并 REVOKE为用户和角色分配和撤销权限；
• SHOW GRANTS 显示用户和角色的权限和角色分配；
• SET DEFAULT ROLE 指定哪些帐户角色默认处于活动状态；
• SET ROLE 更改当前会话中的活动角色。
• CURRENT_ROLE()功能显示当前会话中的活动角色。

创建角色并授予用户角色权限

考虑如下几种场景：

应用程序使用名为app_db的数据库 。

与应用程序相关联，可以为创建和维护应用程序的开发人员以及管理员账户。

开发人员需要完全访问数据库。有的用户只需要读取权限，有的用户需要读取/写入权限。

为清楚区分角色的权限，将角色创建为所需权限集的名称。通过授权适当的角色，可以轻松地为用户帐户授予所需的权限。

创建角色，CREATE ROLE

1	CREATE ROLE  'app_developer' ,  'app_read' ,  'app_write' ;

角色名称与用户帐户名称非常相似，由格式中的用户部分和主机部分组成.

主机部分，如果省略，则默认为%。用户和主机部分可以不加引号，除非它们包含特殊字符

与帐户名称不同，角色名称的

用户部分不能为空

为角色分配权限，使用与为用户分配权限相同的语法执行：

1 2 3

GRANT ALL ON app_db.* TO  'app_developer' ;  GRANT SELECT ON app_db.* TO  'app_read' ;  GRANT INSERT, UPDATE, DELETE ON app_db.* TO  'app_write' ;CREATE ROLE  'app_developer' ,  'app_read' ,  'app_write' ;

现在假设最初需要一个开发人员帐户，两个需要只读访问权的用户以及一个需要读取/写入权限的用户.

使用CREATEUSER创建用户：

1 2 3 4

CREATE USER  'dev1' @ 'localhost' IDENTIFIED BY  'dev1pass' ;  CREATE USER  'read_user1' @ 'localhost' IDENTIFIED BY  'read_user1pass' ;  CREATE USER  'read_user2' @ 'localhost' IDENTIFIED BY  'read_user2pass' ;  CREATE USER  'rw_user1' @ 'localhost' IDENTIFIED BY  'rw_user1pass' ;

要为每个用户分配其所需的权限，可以使用GRANT与刚才显示的形式相同的语句，但这需要列举每个用户的个人权限。相反，使用GRANT允许授权角色而非权限的替代语法：

1 2 3

GRANT  'app_developer' TO  'dev1' @ 'localhost' ;  GRANT  'app_read' TO  'read_user1' @ 'localhost' ,  'read_user2' @ 'localhost' ;  GRANT  'app_read' ,  'app_write' TO  'rw_user1' @ 'localhost' ;

结合角色所需的读取和写入权限，在GRANT中授权 rw_user1用户读取和写入的角色。

在GRANT授权角色的语法和授权用户的语法不同：有一个ON来区分角色和用户的授权，有ON的为用户授权，而没有ON用来分配角色。

由于语法不同，因此不能在同一语句中混合分配用户权限和角色。（允许为用户分配权限和角色，但必须使用单独的GRANT语句，每种语句的语法都要与授权的内容相匹配。）

检查角色权限

要验证分配给用户的权限，使用 SHOW GRANTS

1 2 3 4 5 6 7

mysql> SHOW GRANTS FOR  'dev1' @ 'localhost' ;  +-------------------------------------------------+  | Grants  for dev1 @localhost                        |  +-------------------------------------------------+  | GRANT USAGE ON *.* TO `dev1`@`localhost`        |  | GRANT `app_developer`@`%` TO `dev1`@`localhost` |  +-------------------------------------------------+

但是，它会显示每个授予的角色，而不会将其显示为角色所代表的权限。如果要显示角色权限，添加一个 USING来显示：

1 2 3 4 5 6 7 8

mysql> SHOW GRANTS FOR  'dev1' @ 'localhost' USING  'app_developer' ;  +----------------------------------------------------------+  | Grants  for dev1 @localhost                                 |  +----------------------------------------------------------+  | GRANT USAGE ON *.* TO `dev1`@`localhost`                 |  | GRANT ALL PRIVILEGES ON `app_db`.* TO `dev1`@`localhost` |  | GRANT `app_developer`@`%` TO `dev1`@`localhost`          |  +----------------------------------------------------------+

同样验证其他类型的用户：

mysql> SHOW GRANTS FOR ‘read_user1’@‘localhost’ USING ‘app_read’;

±-------------------------------------------------------+

| Grants for [email protected] |

±-------------------------------------------------------+

| GRANT USAGE ON . TO 

read_user1

@

localhost

 |

| GRANT SELECT ON 

app_db

.* TO 

read_user1

@

localhost

 |

| GRANT 

app_read

@

%

 TO 

read_user1

@

localhost

 |

±-------------------------------------------------------+

mysql> SHOW GRANTS FOR ‘rw_user1’@‘localhost’ USING ‘app_read’, ‘app_write’;

±-----------------------------------------------------------------------------+

| Grants for [email protected] |

±-----------------------------------------------------------------------------+

| GRANT USAGE ON . TO 

rw_user1

@

localhost

 |

| GRANT SELECT, INSERT, UPDATE, DELETE ON 

app_db

.* TO 

rw_user1

@

localhost

 |

| GRANT 

app_read

@

%

,

app_write

@

%

 TO 

rw_user1

@

localhost

 |

±-----------------------------------------------------------------------------+

2.3 撤消角色或角色权限

正如可以授权某个用户的角色一样，可以从帐户中撤销这些角色：

REVOKE role FROM user;

REVOKE可以用于角色修改角色权限。这不仅影响角色本身权限，还影响任何授予该角色的用户权限。假设想临时让所有用户只读，使用REVOKE从该app_write角色中撤消修改权限 ：

REVOKE INSERT, UPDATE, DELETE ON app_db.* FROM ‘app_write’;

碰巧，某个角色完全没有任何权限，正如可以看到的那样SHOW GRANTS （这个语句可以和角色一起使用，而不仅仅是查询用户权限可用）：

mysql> SHOW GRANTS FOR ‘app_write’;

±--------------------------------------+

| Grants for [email protected]% |

±--------------------------------------+

| GRANT USAGE ON . TO 

app_write

@

%

 |

±--------------------------------------+

从角色中撤销权限会影响到该角色中任何用户的权限，因此 rw_user1现在已经没有表修改权限（INSERT， UPDATE，和 DELETE权限已经没有了）：

mysql> SHOW GRANTS FOR ‘rw_user1’@‘localhost’

USING ‘app_read’, ‘app_write’;

±---------------------------------------------------------------+

| Grants for [email protected] |

±---------------------------------------------------------------+

| GRANT USAGE ON . TO 

rw_user1

@

localhost

 |

| GRANT SELECT ON 

app_db

.* TO 

rw_user1

@

localhost

 |

| GRANT 

app_read

@

%

,

app_write

@

%

 TO 

rw_user1

@

localhost

 |

±---------------------------------------------------------------+

实际上，rw_user1读/写用户已成为只读用户。对于被授予app_write角色的任何其他用户也会发生这种情况，说明修改使用角色而不必修改个人帐户的权限。

要恢复角色的修改权限，只需重新授予它们即可：

GRANT INSERT, UPDATE, DELETE ON app_db.* TO ‘app_write’;

现在rw_user1再次具有修改权限，就像授权该app_write角色的其他任何帐户一样。

2.4 删除角色

要删除角色，请使用DROP ROLE：

DROP ROLE ‘app_read’, ‘app_write’;

删除角色会从授权它的每个帐户中撤消该角色。

2.5 角色和用户在实际中的应用

假设遗留应用开发项目在MySQL中的角色出现之前开始，因此与该项目相关联的所有用户都是直接授予权限（而不是授予角色权限）。其中一个帐户是最初被授予权限的开发者用户，如下所示：

CREATE USER ‘old_app_dev’@‘localhost’ IDENTIFIED BY ‘old_app_devpass’;

GRANT ALL ON old_app.* TO ‘old_app_dev’@‘localhost’;

如果此开发人员离开项目，则有必要将权限分配给其他用户，或者项目参与人增多，则可能需要多个用户。以下是解决该问题的一些方法：

不使用角色：更改帐户密码，以便原始开发人员不能使用它，并让新的开发人员使用该帐户：

ALTER USER ‘old_app_dev’@‘localhost’ IDENTIFIED BY ‘new_password’;

使用角色：锁定帐户以防止任何人使用它来连接服务器：

ALTER USER ‘old_app_dev’@‘localhost’ ACCOUNT LOCK;

然后将该帐户视为角色。对于每个新开发项目的开发者，创建一个新帐户并授予其原始开发者帐户：

CREATE USER ‘new_app_dev1’@‘localhost’ IDENTIFIED BY ‘new_password’;

GRANT ‘old_app_dev’@‘localhost’ TO ‘new_app_dev1’@‘localhost’;

其效果是将原始开发者帐户权限分配给新帐户。

MySQL8.0的用户和角色管理也越来越像Oracle了，8.0中有不少新的特性，变化还是很大的，需要DBA不断的学习和测试，更新对MySQL新版的认知，更好地运维MySQL数据库。未来MySQL数据库自治和智能数据库是必然发展趋势，对DBA来说是解放，也是挑战。

同时也非常感谢好友知名MySQL数据库专家吴炳锡老师在百忙中抽空对本文进行校对。

优化器索引

隐藏索引

MySQL 8.0有一个称为“隐藏索引”的新功能，它允许快速启用/禁用MySQL Optimizer使用的索引。

有什么用？

一是如果你想删除一个索引，但又想事先知道效果。你就可以使它对优化程序不可见。这是一个快速的元数据更改，使索引不可见。一旦确定没有性能下降，就可以真正去删除索引。

关键的一点是，隐藏索引不能供优化器使用，但它仍然存在，并通过写入操作保持最新。即便我们尝试“FORCE INDEX”，优化器也不会使用它，虽然我认为我们应该能够在某种程度上强制它。可能会有这样的情况：

我们可以创建一个新的隐形索引，但如果想要测试它，必须使它可见。这意味着所有对应用程序有即时影响的查询都将能够使用它。如果目的只是想测试它，我不认为这是最好的方法，不是所有人的服务器上都有相同的数据大小和真实数据。强制隐藏索引这时候可能会很有用。

你有许多索引，但不确定哪一个未使用。你可以将一个索引更改为不可见，以查看是否存在任何性能下降。如果是，你可以立即更改。

你可能有一个特殊情况，只有一个查询可以使用该索引。在这种情况下，隐藏索引可能是一个很好的解决方案。

创建隐藏索引

有两个选项

• 我们可以创建一个具有隐藏索引的表

  

• 或者我们可以使用alter table并将索引更改为隐藏

  

使用隐藏索引

如果我们现在要删除索引，我们可以将其更改为隐藏。 但是使用“FORCE / USE INDEX”的查询怎么样？ 他们是否会抛出一个错误？ 如果强制不存在的索引，你会收到错误。 你不会看到隐藏索引的错误。 优化器不会使用它，但知道它存在。

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

show create table t1 G ***************************  1 . row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `i`  int ( 11 ) DEFAULT NULL, `j`  int ( 11 ) DEFAULT NULL, `k`  int ( 11 ) DEFAULT NULL, KEY `i_idx` (`i`), KEY `idx_1` (`i`,`j`,`k`) ) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set ( 0.00 sec) mysql> explain select * from t1 force index(idx_1) where i= 1 and j= 4 ; +----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key   | key_len | ref         | rows | filtered | Extra       | +----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+ |   1 | SIMPLE      | t1    | NULL       | ref  | idx_1         | idx_1 |  10       |  const , const |     2 |    100.00 | Using index | +----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+ 1 row in set,  1 warning ( 0.00 sec) mysql> alter table t1 alter index idx_1 invisible; Query OK,  rows affected ( 0.01 sec) Records:    Duplicates:    Warnings: 0mysql> explain select * from t1 force index(idx_1) where i= 1 and j= 4 ; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ |   1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    16 |      6.25 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set,  1 warning ( 0.01 sec) mysql> explain select * from t1 where i= 1 and j= 4 ; +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra       | +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+ |   1 | SIMPLE      | t1    | NULL       | ref  | i_idx         | i_idx |  5        |  const |     2 |     10.00 | Using where | +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+ 1 row in set,  1 warning ( 0.00 sec)

正如你所看到的，如果我们使用带有隐藏索引的“FORCE INDEX”，MySQL会执行全表扫描。 MySQL不会抛出任何错误，因为索引存在，但它不可见。 即使有另一个可用的索引，它也将执行全表扫描。 在大型表上，这可能会导致严重的性能问题。 即使MySQL在查询执行期间不抛出任何错误，它也应该会在错误日志中记录一个警告。

降序索引

通用表达式

MySQL递归CTE简介

递归公用表表达式(CTE)是一个具有引用CTE名称本身的子查询的CTE。以下说明递归CTE的语法

1 2 3 4 5 6

WITH RECURSIVE cte_name AS (      initial_query  -- anchor member      UNION ALL      recursive_query -- recursive member that references to the CTE name ) SELECT * FROM cte_name;

SQL递归CTE由三个主要部分组成：

形成CTE结构的基本结果集的初始查询(initial_query)

初始查询部分被称为锚成员。递归查询部分是引用CTE名称的查询，因此称为递归成员。递归成员由一个UNION ALL或UNION DISTINCT运算符与锚成员相连

终止条件是当递归成员没有返回任何行时，确保递归停止。

递归CTE的执行顺序如下：

• 首先，将成员分为两个：锚点和递归成员。
• 接下来，执行锚成员形成基本结果集(R0)，并使用该基本结果集进行下一次迭代
• 然后，将Ri结果集作为输入执行递归成员，并将Ri+1作为输出
• 之后，重复第三步，直到递归成员返回一个空结果集，换句话说，满足终止条件
• 最后，使用UNION ALL运算符将结果集从R0到Rn组合。

递归成员限制递归成员不能包含以下结构

• 聚合函数，如MAX，MIN，SUM，AVG，COUNT等
• GROUP BY子句
• ORDER BY子句
• LIMIT子句
• DISTINCT

请注意，上述约束不适用于锚定成员。 另外，只有在使用UNION运算符时，要禁止DISTINCT才适用。 如果使用UNION DISTINCT运算符，则允许使用DISTINCT。

另外，递归成员只能在其子句中引用CTE名称，而不是引用任何子查询。

简单的MySQL递归CTE示例请参阅以下简单的递归CTE 示例：

1 2 3 4 5 6 7 8 9 10

WITH RECURSIVE cte_count (n) AS (        SELECT  1        UNION ALL        SELECT n +  1        FROM cte_count        WHERE n <  3      ) SELECT n FROM cte_count;

SQL在此示例中，以下查询：SELECT 1

SQL是作为基本结果集返回1的锚成员。以下查询

1 2 3

SELECT n +  1 FROM cte_count WHERE n <  3

是递归成员，因为它引用了cte_count的CTE名称。递归成员中的表达式<3是终止条件。当n等于3，递归成员将返回一个空集合，将停止递归。下图显示了上述CTE的元素：

递归CTE返回以下输出：

递归CTE的执行步骤如下：

• 首先，分离锚和递归成员。
• 接下来，锚定成员形成初始行(SELECT 1)，因此第一次迭代在n = 1时产生1 + 1 = 2。
• 然后，第二次迭代对第一次迭代的输出(2)进行操作，并且在n = 2时产生2 + 1 = 3。
• 之后，在第三次操作(n = 3)之前，满足终止条件(n < 3)，因此查询停止。
• 最后，使用UNION ALL运算符组合所有结果集1,2和3。