MySQL用户管理及查询缓存详解

MySQL用户管理

用户帐号：username@hostname, password       

表示这个账号允许通过hostname主机连入mysql服务器。hostname可以是单个ip，也可以是一个网络，也可以是通配符。

MySQL对用户帐号的管理是分两步的：创建和授权。

用户账号管理

• CREATE USER #使用这种方式创建完用户后，他仅能够连入mysql服务器，并能够执行有限的查看类命令，想创建数据库、创建表、插入数据等没有权限。
• DROP UESER
• RENAME USER
• SET PASSWORD

CREATE USER

CREATE USER username@hostname
   [
       IDENTIFIED BY [PASSWORD] 'password'
   ]            

【注意】:如果不给密码就是空密码。

主机也可使用通配符：

%：匹配任何字符
_: 匹配单个字符

【示例】：

testuser@'172.16.100.1__' 代表 172.16.100.100-172.16.100.199


mysql> CREATE USER testuser@'172.16.%.%' IDENTIFIED BY 'testpass';
Query OK, 0 rows affected (0.13 sec)

对于CREATE USER、GRANT来说，FLUSH不是必须的。然后我们使用这个刚创建的用户账户连接mysql，

[root@res ~]# mysql -h172.16.206.30  -utestuser -p
Enter password: 
mysql> CREATE DATABASE mydb;
ERROR 1044 (42000): Access denied for user 'testuser'@'172.16.%.%' to database 'mydb'
mysql> SHOW GRANTS FOR 'testuser'@'172.16.%.%';
+-------------------------------------------------------------------------------+
| Grants for testuser@172.16.%.%                                                |
+-------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'172.16.%.%' IDENTIFIED BY PASSWORD <secret> |
+-------------------------------------------------------------------------------+
1 row in set (0.04 sec)

【说明】：USAGE：只是一个简单的连入mysql服务器的权限。这是默认获取的授权。

查看用户能够使用的权限：

SHOW GRANTS FOR username@'hostname'

重命名用户账号：

mysql> RENAME USER 'testuser'@'172.16.%.%' TO 'tuser'@'172.16.%.%';
Query OK, 0 rows affected (0.01 sec)

权限管理

权限管理命令：

• GRANT #GRANT命令在用户事先不存在的前提下，它可以自动创建用户。
• REVOKE

权限管理分类

• 库级别
• 表级别
• 字段级别
• 管理类：比如能否把自己的权限转赠给其他用户、能否创建用户、能否执行复制等。
• 程序类：比如存储过程、存储函数、触发器。 (这两类是我们自己定义的)

1.管理类权限

• CREATE TEMPORARY TABLES
• CREATE USER
• FILE：在服务器上能够读取或写入文件的。比如我们通过客户端连入mysql服务器，如果想把mysql的查询结果保存到文件中，这个文件很显然就是在服务器上的。这个权限很危险，不能让用户随意使用。
• SUPER：杂项管理类命令。
• SHOW DATABASES
• RELOAD
• SHUTDOWN
• REPLICATION SLAVE
• REPLICATION CLIENT
• LOCK TABLES：显式施加表锁。
• PROCESS：查看当前服务器上执行的所有进程，可以理解为线程。

查看当前MySQL服务器上的执行的所有线程：

mysql> SHOW PROCESSLIST;
+---------+----------------+----------------------+------------+---------+------+-------+------------------+
| Id      | User           | Host                 | db         | Command | Time | State | Info             |
+---------+----------------+----------------------+------------+---------+------+-------+------------------+
| 7793848 | root           | localhost            | NULL       | Query   |    0 | init  | SHOW PROCESSLIST |
| 7793872 | sonar          | localhost:57831      | sonar      | Sleep   |   41 |       | NULL             |
| 7794027 | testuser       | res.wisedu.com:39982 | NULL       | Sleep   |  241 |       | NULL             |
...

【注意】: 这个命令是我们将来非常常用的命令，用于查看当前mysql上内部所执行的众多跟用户相关的线程的。

2.库级别和表级别

• ALTER
• ALTER ROUTINE：修改存储例程，包括存储过程和存储函数。
• CREATE
• CREATE ROUTINE
• CREATE VIEW
• DROP
• EXECUTE：是否能够执行存储过程或存储函数。
• GRNAT OPTION：把自己获得的权限转赠给别人。
• INDEX
• SHOW VIEW：查看一个视图是怎么被创建的。

3.数据操作(表级别)

• SELECT
• INSERT
• UPDATE
• DELETE

4.数据操作(字段级别)

• SELECT(col1,…)
• UPDATE(col1,…)
• INSERT(col1,…)

5.所有权限

• ALL [PRIVILEGES]

权限管理命令之GRANT

两种使用格式：

GRANT ALL ON [FUNCTION] *.*      
第一个*表示库，如果第一个*前面没有修饰，那默认第2个*表示表，否则比如加个FUNCTION，第二个*表示的就是某个库的存储函数。修饰有3个，[TABLE|FUNCTION|PROCEDURE]。

mysql> HELP GRANT
Name: 'GRANT'
Description:
Syntax:
GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_specification [, user_specification] ...
    [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
    [WITH {GRANT OPTION | resource_option} ...]

GRANT PROXY ON user_specification
    TO user_specification [, user_specification] ...
    [WITH GRANT OPTION]

object_type: {
    TABLE
  | FUNCTION
  | PROCEDURE
}

priv_level: {
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name
}

user_specification:
    user [ auth_option ]

auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED BY PASSWORD 'hash_string'
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin AS 'hash_string'
}

ssl_option: {
    SSL
  | X509
  | CIPHER 'cipher'
  | ISSUER 'issuer'
  | SUBJECT 'subject'
}

GRANT OPTION # 允许把自己的权限转赠给其他用户

resource_option: {
  | MAX_QUERIES_PER_HOUR count #每小时所能执行的最多查询请求的次数，这是做资源限定的
  | MAX_UPDATES_PER_HOUR count # 每小时所能执行的最多更新的次数
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count # 指定某个用户帐号最多同时连接几次
}

在上面那个例子中，我们使用普通用户 tuser@’172.16.%.%’ 连接上去是不能创建数据库的，接下来我们使用root账户登录上去授权这个普通用户一些权限，看看效果：

mysql> GRANT CREATE ON mydb.tb1 TO 'tuser'@'172.16.%.%';
Query OK, 0 rows affected (0.02 sec)

【注意】:这个权限只能创建tb1这个表，不能创建库mydb，见下面。管理员授权后，普通用户连接上mysql，可以查看自己的权限：

mysql> SHOW GRANTS FOR 'tuser'@'172.16.%.%';   
+----------------------------------------------------------------------------+
| Grants for tuser@172.16.%.%                                                |
+----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tuser'@'172.16.%.%' IDENTIFIED BY PASSWORD <secret> |
| GRANT CREATE ON `mydb`.`tb1` TO 'tuser'@'172.16.%.%'                       |
+----------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> CREATE DATABASE mydb;
ERROR 1044 (42000): Access denied for user 'tuser'@'172.16.%.%' to database 'mydb'

此时确实不能创建数据库。使用root账号授权如下：

mysql> GRANT CREATE ON mydb.* TO 'tuser'@'172.16.%.%';   
Query OK, 0 rows affected (0.03 sec)

普通用户再次查看自己的权限，然后在创建数据库就可以了：

mysql> SHOW GRANTS FOR 'tuser'@'172.16.%.%';
+----------------------------------------------------------------------------+
| Grants for tuser@172.16.%.%                                                |
+----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tuser'@'172.16.%.%' IDENTIFIED BY PASSWORD <secret> |
| GRANT CREATE ON `mydb`.* TO 'tuser'@'172.16.%.%'                           |
| GRANT CREATE ON `mydb`.`tb1` TO 'tuser'@'172.16.%.%'                       |
+----------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> CREATE DATABASE mydb;
Query OK, 1 row affected (0.00 sec)

在赋予删除表的权限：

mysql> GRANT DROP ON mydb.* TO 'tuser'@'172.16.%.%';      
Query OK, 0 rows affected (0.00 sec)

普通用户查看权限和删除表、库：

所以为了让那些在线用户能够立即获取权限，就要在授权后FLUSH。

权限管理命令之REVOKE

两种使用格式：

REVOKE
  priv_type [(column_list)]
    [, priv_type [(column_list)]] ...
  ON [object_type] priv_level
  FROM user [, user] ...

REVOKE ALL PRIVILEGES, GRANT OPTION
  FROM user [, user] ...

所有给用户授权的这些权限都会保存在跟用户相关的授权表当中，几个跟用户授权相关的表(都在mysql库中)：

• db: 库级别权限。
• host: 主机级别权限，已废弃。
• tables_priv: 表级别权限。
• colomns_priv：列级别的权限。
• procs_priv：存储过程和存储函数相关的权限。
• proxies_priv：代理用户权限。

对MySQL来讲，检查用户授权分两个阶段，mysql用户刚刚执行连入数据库时需要认证，检查用户名密码并且检查用户是否有连进来的权限；连接进来以后，用户每次执行SELECT操作也要检验权限。所以MySQL在执行权限检查时，几乎每一次操作都涉及到，这也是为什么mysql为什么将授权表载入内存中的原因，不然它的速度会有多慢。
如果用户在登录mysql时输错了命令，连续输错了好多次，会被mysql锁定的。再次登录，mysql会直接拒绝了，不会检查授权表。mysql认为这是尝试攻击。如果遇到这种情况，我们需要FLUSH hosts，让系统清空一下hosts相关的缓存，这样用户就可以再次连接。

之前的文章中提到过MySQL作者在MySQL被Oracle收购后，又带着团队重新开发了MariaDB。在MariaDB中，有两款支持事务的，FEDERATED和InnoDB。只不过FEDERATED不支持XA(分布式事务)。
Aria是增强版的MyISAM。
MySQL和MariaDB除了这些存储引擎上的区别，他们两个区别并不大。但是要注意MariaDB的服务器变量非常多，有400多个。

MySQL查询缓存

每个查询语句要做词法分析，语法分析、语义分析，然后执行解析树，从中选择一个最优路径，并且通过优化器重写查询，并选择合适的索引完成查询操作。而如果这个查询计划被缓存下来，多个用户使用同一个SELECT语句时，语句如果避免二次解析，它就能够加速查询操作。
用于保存MySQL查询语句返回的完整结果。被命中时，MySQL会立即返回结果，省去解析、优化和执行等阶段。
但是查询缓存带来的未必都是好事。为什么呢？现在运行数据库数据的服务器硬件越来越强大，内存动辄几十个g，32g、64g都很常见，而且CPU动辄就是好几颗，每一颗都有好多核，而多个用户同时连进来时，每个用户都有一个连接线程，这些线程会被分配到多颗CPU上执行的。由于MySQL的内生性限制，单独的一条查询最多只能在一颗CPU上执行，如果我们使用了查询缓存，多个用户都执行查询操作，每个查询操作都要查缓存，也就意味着缓存会成为多个能够并发在多颗CPU上执行的查询请求的热点所在，会不会争用。第一个线程发起了查询语句，去查了缓存，假设2颗CPU，每颗32核，现在连进来了64个用户，大家都发起了查询操作，都去查询缓存，所以缓存就成为资源争用的热点所在了。在某一时刻，这段内存空间只能为其中一颗CPU所访问。所以在这种场景下，缓存所带来的未必有我们想象中的好。因为你不能只用单颗CPU的方式去思考mysql服务器。并发查询量非常大，而且CPU核心数又非常多时，缓存是否有效就是个值得思考的问题。

一个mysql查询操作会经过类似的步骤：
一个查询请求来了，首先检查缓存是否命中，命中则直接返回结果。没命中，解析、优化、执行，执行后的结果先看看能否进行缓存，如果能，缓存到缓存中，然后在把结果返回给用户。

什么样的语句不会被缓存？

查询语句中有一些不确定数据时，不会缓存：例如NOW(), CURRENT_TIME()；一般来说，如果查询中包含用户自定义函数、存储函数、用户变量、临时表、mysql库中系统表、或者任何包含权限的表，一般都不会缓存；

缓存会带来额外开销

缓存会带来如下的开销，只有当这种开销小于缓存给我们带来的益处时才有必要启用缓存

1. 每个查询都得先检查是否命中；
2. 查询结果要先缓存；

查看当前MySQL上缓存的配置：

mysql> SHOW GLOBAL VARIABLES LIKE 'query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 1048576 |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
5 rows in set (0.00 sec)

• query_cache_type: 查询缓存类型；就是指是否开启缓存功能，其开启方式有三种{ON|OFF|DEMAND}；

  • DEMAND：意味着SELECT语句明确使用 SQL_CACHE 选项时才会缓存；
  • 一般而言除非我们明确知道哪些语句缓存下来才有意义的时候才会使用DEMAND，否则我们如果要想使用缓存机制，设置为ON。

• query_cache_size: 总空间，单位为字节，大小必须是1024的整数倍。16777216是16M，比较小。如果内存够用，可以调大点，但是也不要太大，看你命中率。如果调大能提高命中率，就应该调大些。但是查询命中率，要先”预热”，刚开机你就去查缓存命中率，那没意义。MySQL启动时，会一次性分配并立即初始化这里指定大小的内存空间；这意味着，如果修改此大小，会清空缓存并重新初始化的。不建议经常改，最好事先设置好。

• query_cache_min_res_unit: 存储缓存的最小内存块；(query_cache_size-Qcache_free_memory)/Qcache_queries_in_cache能够获得一个理想的值。缓存必须要划分成内存块进行缓存的，比如一个划分的内存块一个为1k，某一个查询结果只有30bytes，缓存在一个内存块里，剩余的空间就浪费了。所以这个内存块太大了会导致浪费，太小了我缓存一个大结果，得申请很多个内存块才能存下来。最好你能够观测一下大多数查询，很多结果都是满足正太分布的，找那些分布率比较集中的地方的缓存大小。当然也可以用上面的公式去计算，得到的值是比较理想的。

• query_cache_limit: 单个缓存对象的最大值，超出时则不予缓存；可以在SELECT语句中手动使用SQL_NO_CACHE可以人为地避免尝试缓存返回结果超出此参数限定值的语句。

• query_cache_wlock_invalidate: 如果某个表被其它用户的连接(查询)锁住了，是否仍然从缓存中返回结果。OFF表示返回。
  因为其他用户锁住了表，他可能会改表中的数据，也可能不改，这个时候是返回还是不返回呢？

如何检查缓存？

MySQL保存结果于缓存中：

• 把SELECT语句本身做hash计算，计算的结果作为key，查询结果作为value。
• 所以缓存由两个字段组成，一个是key，一个是value。

缓存到底有没有效不能简单的根据有多少SELECT语句来判定，我们要去判定缓存命中率。但是这个缓存命中率的高和低不能简单的根据查询的命中次数来判定，一般来讲，要根据它的开销来判断命中率到底有多高。命中率有两种，次数命中率和字节命中率(命中后所省去的传输数据量)。

如何判断命中率？

查看次数命中率，而不是字节命中率。衡量时最好都衡量

mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';     
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        | 空闲的块数
| Qcache_free_memory      | 16757008 | 空闲空间
| Qcache_hits             | 4        | 命中次数
| Qcache_inserts          | 2        | 向缓存空间中缓存数据的次数
| Qcache_lowmem_prunes    | 0        | 因为内存太少而腾出内存的次数
| Qcache_not_cached       | 18       | 没被缓存的语句的次数
| Qcache_queries_in_cache | 2        | 在缓存中缓存的查询的个数。和Qcache_inserts不一定一样大。
| Qcache_total_blocks     | 6        | 缓存总块数
+-------------------------+----------+

【注意】:总空间-空闲空间=已用空间。但并非已用空间都缓存了数据，这些已用块数表示已划分好格式了，可以随时等待数据缓存进来。
如果内存中的确有碎片了，比如说空闲空间还很多，但是内存总是被修剪(腾出)，这可能是有大量碎片导致大结果无法被缓存。这就需要清理碎片了。只是把碎片挪出去，做成连续空间。
碎片整理：FLUSH QUERY_CACHE
清空缓存：RESET QUERY_CACHE

计算次数命中率：(节命中率很难估算)

mysql> SHOW GLOBAL STATUS WHERE Variable_name='Qcache_hits' OR Variable_name='Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 24    |  一共查询了这么多次
| Qcache_hits   | 4     |  命中的查询的次数，注意命中了缓存，上面那个查询次数是不会加1的。
+---------------+-------+

Qcache_hits/(Com_select+Qcache_hits)

也应该参考另外一个指标：命中和写入的比率，即Qcache_hits/Qcache_inserts的值，此比值如果能大于3:1，则表明缓存也是有效的。能达到10:1，为比较理想的情况。

缓存优化使用思路

1、批量写入而非多次单个写入；
2、缓存空间不宜过大，因为大量缓存同时失效时会导致服务器假死；
3、必要时，使用SQL_CACHE和SQL_N0_CACHE手动控制缓存；
4、对写密集型的应用场景来说，禁用缓存反而能提高性能。读写比例一样大的时候最好也关了。