MySQL用户权限查询缓存与索引

(Updated: )
,

接下来就来看一下MySQL对于用户权限的管理相关.

MySQL中的用户和权限

首先来说说MySQL中的对于通常所说的权限可以分成用户和用户权限两个概念. 对于MySQL来说, 就算由一个用户可以登录进入, 他也有可能无法进行任何操作. 对于这样的权限我们可以划分成:

  • 库级别
  • 表级别
  • 字段级别

这样, 通过操作这些级别的权限就可以指定用户是否有权限进行数据库的某些操作.

除了这些, 还有两种:

  • 管理类
  • 程序类

其中, 管理类的权限就是指的能否存在第三方用户去进行数据库的管理和一些维护操作. 而程序类权限就是说用户能否运行某些存储过程或者存储函数.

我们先从管理类权限说起吧.

管理类权限

一个典型的管理权限就是进行临时表的创建, 一般临时表是不会具有持久性的, 但是他的查询效率要比普通的表高处一个数量级的, 所以一般我们可以使用临时表来存储一些临时的数据. 一个临时表, 一般需要16M大小的内存空间.

另外还有例如: 创建用户(CREATE USER), 进行文件导入导出(FILE), SUPER权限分配, 显示数据库, RELOAD, SHUTDOWN, REPLICATION SLAVE/CLIENT做复制等等, 这些我们在后面也会再次提到的.

程序类权限

所谓程序就是指的一些存储过程代码段和一些函数, 当然还有一些触发器的代码.

当我们安装好MySQL之后, 会看到有一个元数据数据库, 现在我们可以看一下这个表:

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
MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
24 rows in set (0.00 sec)

可以看到这里面有很多使用priv结尾的表, 这些表就是描述权限的, 除了一个特例, 就是db表, 这个是描述库级别的权限的. 包括一些用户的连接访问权限也会有描述.

首先我们划分授权表: 包括db, host, user. 这三者描述哪些用户可以通过哪些主机进行连接访问哪些数据库. 其实要是打开看一下的话就会知道了, db和host这两个表几乎是一样的, db比host多了2个字段, 也即是User和Event_priv这两个.

接下来我们说说如果管理员忘记了密码的解决方法:

一种可行的方法就是在启动mysqld进程的时候, 加上--skip-grant-table的选项. (建议在这个时候同时加上--skip-networking防止远程用户登录进入). 然后使用UPDATE直接修改密码, 接着关闭进程, 移除选项重新启动.

顺便我们来说说GRANT, 大家都知道这个命令可以进行用户权限的设定, 其实还可以通过他提供的选项进行一些更细致的设定, 例如:

现在我们创建一个用户test, 允许这个用户通过172.16.9.*的主机连接mysqld, 但是每秒钟的最大查询次数不得超过5次, 同时该账户的同时连接次数不得超过3次.

创建的语句如下:

1
MariaDB [mysql]> grant usage on *.* to 'test'@'172.16.9.%' with  MAX_QUERIES_PER_HOUR 18000 MAX_USER_CONNECTIONS 3;

查询缓存

之前我们曾经简单的描述了一下关于MySQL的查询缓存. 除了有查询引擎自行实现的表级别或者是其他什么的缓存, MySQL的服务端也自行实现了一个查询缓存. 这个查询缓存的是SELECT语句所执行之后得到的结果集数据. 这么设计的一个问题就是如果查询的目标表数据发生变化的话, 这个查询缓存就会立即失效, 也就是说有可能出现刚刚缓存结束还没有被命中就已经失效了. 所以这样就反而影响了整体的查询效率.

另外, 这个SELECT缓存的数据集, MySQL如何判断缓存命中了呢? 第一是通过查询语句的哈希值, 其中哈希值考虑到的因素包括: 查询本身, 要查询的数据库, 客户端使用的协议版本等等, 都会纳入哈希生成的条件.

这也就是说, 查询语句在任何字符上的不同, 都会导致缓存不能命中. (所以说要规范SQL的写法, 或者是养成自己习惯). 另外, 就像我们之前说的, 如果查询中包含自己的自定义的函数, 存储过, 可临时表或者包含列权限的表, 是不会被缓存的. 如果查询中包含随机值, 例如时间(now()), 查询的结果也不会被缓存, 因为这样缓存了也没有作用.

另外, 查询缓存是根据使用情况进行动态的改变的, 这也就必然引入了关于内存碎片的问题, 既然会产生碎片就需要进行整理, 这也就带来了额外的开销. 当然了, 只要缓存带来的性能提升大于额外的开销就是可以接受的.

那么到底我们查询缓存的收益是大于这个额外的开销的呢? 还记得我们之前查看的关于缓存的相关变量参数嘛:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
MariaDB [(none)]> show global variables like '%query%';
+------------------------------+--------------------+
| Variable_name                | Value              |
+------------------------------+--------------------+
| expensive_subquery_limit     | 100                |
| ft_query_expansion_limit     | 20                 |
| have_query_cache             | YES                |
| long_query_time              | 10.000000          |
| query_alloc_block_size       | 8192               |
| query_cache_limit            | 1048576            |
| query_cache_min_res_unit     | 4096               |
| query_cache_size             | 0                  |
| query_cache_strip_comments   | OFF                |
| query_cache_type             | ON                 |
| query_cache_wlock_invalidate | OFF                |
| query_prealloc_size          | 8192               |
| slow_query_log               | OFF                |
| slow_query_log_file          | VM-master-slow.log |
+------------------------------+--------------------+
14 rows in set (0.01 sec)

其中有一个query_cache_min_res_unit, 这个表示的是缓存的最小资源单元, 如果结果小于4K, 是不予缓存的. 如果这个值调整的过小, 能够减少浪费, 但是必然会导致更频繁的内存分配操作, 如果调整的较大, 会导致浪费(碎片变得更多).

而query_cache_limit表示的是MySQL所缓存的最大查询结果.

除了这些全局变量, 还有一些状态变量可以用来评估当前的缓存状态, 也就是我们之前所看到的:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
MariaDB [(none)]> show global status like 'Qcache%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_free_blocks      | 0     |
| Qcache_free_memory      | 0     |
| Qcache_hits             | 0     |
| Qcache_inserts          | 0     |
| Qcache_lowmem_prunes    | 0     |
| Qcache_not_cached       | 0     |
| Qcache_queries_in_cache | 0     |
| Qcache_total_blocks     | 0     |
+-------------------------+-------+
8 rows in set (0.00 sec)

另外还有一个统计当前的查询总数的: Com_select. 因此我们可以计算当前的缓存命中率: Qcache_hits / (Qcache_hits+Com_select).

索引和explain

之前虽然简单的说了一下MySQL中的索引, 但是在这里我们再来详细的说一说.

我们说索引建立的最基本的法则就是需要在经常被使用到的字段上, 或者说是被用作查询条件的字段上. 在MySQL中, 一般的索引类型都是B树类型的. 但是即使都是B树索引, 不同的存储引擎实现上都是不一样的. 有些可能会进行索引的压缩.

鉴于B树数据结构的特性, 并且由于采用了左前缀索引的方案, 使得MySQL特别适合:

  • 进行全值匹配, 精确查找
  • 匹配最左前缀, 只精确匹配开头部分
  • 匹配范围数据, 精确匹配某一列并范围匹配另一列
  • 只访问索引的查询

既然有适合的, 就有不适合使用B-Tree索引的场合, 例如:

  • 如果不从最左列开始, 索引就是无效的
  • 不能跳过索引中的列, 如果我们的索引是一个组合的, 多键索引.
  • 如果查询中的某个列(左侧的列)是范围查询, 右侧的列都无法使用索引优化, 只能将左侧的结果进行扫描筛选.

MySQL除了B树索引, 还有另外一个, 即哈希索引. 其实这个就比较简单了, 这个索引将所有的字段进行哈希计算, 并且再分成一级哈希头, 二级哈希头等等. 但是我们知道哈希的特性, 如果元数据发生改变. 他的哈希值也会发生巨大的改变, 不仅如此, 哈希索引不适合进行顺序查询, 因为无法进行数据的排序, 而且模糊匹配也不容易做. 那么哈希索引最适合的查询是哪一种? 当然就是进行列的精确匹配了. 注: 在MySQL中, 只有Memory存储引擎支持显示Hash索引.

另外, 除了B树和哈希索引, 第三种索引就是空间索引, 只有MyISAM存储引擎支持这种索引. 最后一种要说的就是全文索引(FULLTEXT), 在文本中查找关键字.

全文索引更多的应该通过搜索引擎实现, 需要分析全部文本进行权重排序, 这种涉及到很多复杂的算法和排序构建以及运算量, 这里就不说了.

那么! 我到底为啥要使用索引呢?

首先, 索引可以降低服务需要扫描的数据量, 减少了IO次数. 其次, 索引可以帮助服务器避免排序和使用临时表. 另外还有一点, 所以可以帮助将随机IO改为顺序IO, 当然这里所说的是传统的机械硬盘. 随机和顺序IO对固态来说没啥大的影响.

那么说了这么多, 我们如何才能高性能的使用索引呢?

  • 独立使用列, 尽量避免其参与到运算中, 一旦使用了运算就会忽略索引
  • 左前缀索引, 索引构建在左侧的N个字符. 这个字符数量的选择是需要评估的
    • RELATED索引选择性: 不重复的索引值和数据表的记录总数的比值
  • 多列索引, 有些时候单独的三个索引可能会比一个三列的组合索引更好, 根据具体的查询业务做定夺
  • 选择合适的索引列次序, 将选择性最高的放在左侧

在使用索引的过程中是可能出现冗余和重复的, 例如下面的这样子:

建立两个索引, 分别是(Name)和(Name, Age)此时的(Name)单键索引就是一个冗余的重复索引, 因为我们完全可以使用后面的组合索引, 因为是最左侧匹配. 另外, 例如这样子的两个索引: (Age)和(Age, Name).在需要进行年龄的范围查询的时候就不是重复的了. 因为后面的组合索引是用不到的.

那么怎么知道索引到底有没有使用到呢?对了, 就使用之前的EXPALIN来分析就行了.

我们只要把SELECT执行语句放到EXPLAIN的后面, 就可以获取到查询执行计划信息了. 这是什么东西? 其实就是MySQL查询优化器如何执行查询的, 不过, 说到底这也是一个查询计划, 实际上的查询过程可能也是和这个结果又出入的.

我们之前曾经使用过这个explain, 显示的信息有这些:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
MariaDB [hellodb]> explain select course from courses where CourseID=2\G
MariaDB [hellodb]> explain select course from courses where CourseID=2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: courses
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: const
         rows: 1
        Extra: 
1 row in set (0.00 sec)

其中, id表示当前的查询语句中, select语句的编号. 复杂类型的查询有三种, 包括简单子查询, 用于FROM的子查询, 联合查询: UNION. 其中只要使用到了UNION, 就一定会引入一张额外的匿名临时表.

而第二列, select_type就表示的是查询的类型, 如果是简单的查询, 就是SIMPLE. 而对应上面说的几种复杂查询的, 分别有:

  • SUBQUERY: 表示不用FROM的简单子查询
  • DERIVED: 表示用于FROM的子查询
  • UNION: 表示UNION语句的第一个之后的SELECT语句
  • UNION_RESULT: 表示UNION之后的匿名临时表

来试试:

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
31
32
33
34
35
36
MariaDB [hellodb]> explain select Name from students union select Name from teachers\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: students
         type: index
possible_keys: NULL
          key: name_index
      key_len: 152
          ref: NULL
         rows: 25
        Extra: Using index
*************************** 2. row ***************************
           id: 2
  select_type: UNION
        table: teachers
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: 
*************************** 3. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union1,2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: 
3 rows in set (0.00 sec)

这里就是使用到了UNION的情况.

接着table很简单了, 就是查询关联到的表的名字. type表示的是关联的类型, 说通俗一点就是MySQL是如何查询表中的行的方式, 有这些:

  • ALL: 表示的是全表扫描
  • index: 根据索引的次序进行全表扫描, 如果在Extra中列出了Using index表示使用了覆盖索引.
  • range: 范围扫描, 从索引中的某一个点到另一个点
  • ref: 也是索引访问, 但是他是根据索引返回表中匹配的某个单值的所有行
  • eq_ref
  • const, system

后面的结果也都很简单, possible_keys表示查询的时候可能使用到的索引 key就是查询中使用了的索引 而key_len表示在索引中使用的字节数, row表示为了找到目标所扫描的行数, 最后的extra就是一些额外的信息.

额外的信息直接读就能明白, 有:

  • Using index: 将会使用索引, 避免访问表
  • Using where: 在存储引擎层次进行检索, 在进行一次过滤
  • Using temporary: 将会使用临时表
  • Using filesort: 对结果进行一个外部的索引排序