MySQL服务器管理及查询缓存详解(笔记)

MySQL帐号:username@hostname

用户帐号管理:

            CREATE USER :这种用户创建完成后,只能连入数据库进行很有限的操作
            DROP UESER :删除用户
            RENAME USER:如果被修改账户当前在线,只有当他下次登录时才会需要使用新用户名
            SET PASSWORD:修改密码

权限管理:

            GRANT :授权
            REVOKE :收回权限

CREATE USER

  1: mysql> CREATE USER testuser@‘192.168.%.%‘ IDENTIFIED BY  ‘password‘;
  2: Query OK, 0 rows affected (0.07 sec)

RENAME USER:语法 RENAME USER old_name TO new_name

SET PASSWORD:语法SET PASSWORD FOR ‘username‘@‘host‘ =PASSWORD(‘new_passwd‘)

MySQL的权限类别：
        库级别:能否创建库
        表级别:能否创建表
        字段级别:
        管理类:
        程序类:

        管理类权限：
            CREATE TEMPORARY TABLES:创建临时表
            CREATE USER:创建用户
            FILE:在服务器上保存查询的结果的
            SUPER:其他不便归类的命令都有此类授予
            SHOW DATABASES:默认就授予此权限了
            RELOAD:Use FLUSH and RESET
            SHUTDOWN:关闭服务器
            REPLICATION SLAVE:授权复制的
            REPLICATION CLIENT:想服务器查询一共有多少可复制的客户端
            LOCK TABLES:显示的施加表锁
            PROCESS:查看线程列表,SHOW PROCESSLIST:查看当前服务器上的所有线程

        库级别和表级别：
            ALTER:修改表
            ALTER ROUTINE:修改存储过程和存储函数
            CREATE :创建表和库
            CREATE ROUTINE:创建储存过程和存储函数
            CREATE VIEW:创建视图
            DROP:删除表和库
            EXECUTE:能否执行存储过程或函数的
            GRNAT OPTION:把自己的权限授予给他人的
            INDEX:删除和创建索引
            SHOW VIEW:查看视图是如何被创建的

        数据操作（表级别）：
            SELECT:查询
            INSERT:插入
            UPDATE:更新
            DELETE:删除

        字段级别：
            SELECT(col1,...):
            UPDATE(col1,...):
            INSERT(col1,...):

        所有权限：
            ALL [PRIVILEGES]:

GRANT ALL ON [FUNCTION] *.*

        GRANT priv_type [(column_list)]
              [, priv_type [(column_list)]] ...
            ON [TABLE|FUNCTION|PROCEDURE] priv_level
            TO username@hostname [IDENTIFIED BY ‘password‘], [username@hostname [],...]
            [REQUIRE SSL] 限定必须使用ssl加密
            [WITH with_option ...]

        priv_level:
            * 所有
          | *.* 所有库的所有对象
          | db_name.*指定库的所有对象
          | db_name.tbl_name 执行库的指定对象
          | tbl_name 指定表
          | db_name.routine_name 指定库的存储历程

        with_option:
            GRANT OPTION 把自己的权限授予他人
          | MAX_QUERIES_PER_HOUR count 每小时能执行的最多请求次数
          | MAX_UPDATES_PER_HOUR count 每小时所能更新的次数
          | MAX_CONNECTIONS_PER_HOUR count 每小时能进行的最大连接次数
          | MAX_USER_CONNECTIONS count 指定某一用户最多同时并发次数

收回授权：
        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] ...

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

FLUSH hosts:登录多次输入错误密码需执行此命令

缓存用于保存MySQL查询语句返回的完整结果。被命中时，MySQL会立即返回结果，省去解析、优化和执行等阶段,大大提升了查询性能。但是对于现在的高性能平台来说,缓存不一定是好事,因为单独的一个查询最多只能在一个cpu上运行,那么,在某一时刻,缓存只能被一颗cpu所使用,这时,缓存就成了大量资源争用的焦点.所以,在一个多cpu的环境下,缓存是否能提高性能就要重新考虑了
如何检查缓存:

MySQL保存结果于缓存中： 把SELECT语句本身做hash计算，计算的结果作为key，查询结果作为value,在缓存列表中有两个字段组成,key和value,当用户输入一个查询语句,首先将这条语句做hash计算,并用计算的结果去找缓存中的key,并且一一比较,如果找到相同的结果,就把key后面的values返回给用户,这就是缓存命中了.

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

缓存会带来额外开销：
        1、每个查询都得先检查是否命中；
        2、查询结果只要不包含不缓存内容都要先缓存；

查询缓存都是在内存中的,一旦内存分配给查询缓存的空间被缓存满了,缓存管理器就会基于最近最少使用或最近做多使用等一些缓存置换策略把一些老的缓存清理出去.缓存管理器要缓存数据的时候,需要向内存申请,用完了需要释放,这需要消耗时间.每个缓存都是固定的内存块大小,单个缓存太大,需要申请多个单位内存块,单个缓存太小,会浪费内存空间,所以设定一个适当大小的内存块也是缓存是否高效的一个环节.单位内存是固定大小的,所以频繁的读写会产生碎片,有了碎片后,内存的申请和释放将会变的更加低效,所以,必须要选择或着设计一个合适的算法来分配内存.在MySQL启动时,会一次性初始化,并分配指定的内存空间大小给MySQL的缓存使用,MySQL会把这部分内存空间分成相同大小的块,一个块存一条缓存语句,或多个块存一条缓存语句.

以下是用户可以自己设定的缓存的相关的值

  1:   MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE ‘query_cache%‘;

  2:     +------------------------------+----------+

  3:     | Variable_name                | Value    |

  4:     +------------------------------+----------+

  5:     | query_cache_limit            | 1048576  |

  6:     | query_cache_min_res_unit     | 4096     |

  7:     | query_cache_size             | 16777216 |

  8:     | query_cache_strip_comments   | OFF      |

  9:     | query_cache_type             | ON       |

 10:     | query_cache_wlock_invalidate | OFF      |

 11:     +------------------------------+----------+

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

          DEMAND：意味着SELECT语句明确使用 SQL_CACHE 选项时才会缓存；

query_cache_size: 缓存使用的总空间，单位为字节，大小必须是1024的整数倍。MySQL启动时，会一次分配并立即初始化这里指定大小的内存空间；这意味着，如果修改此大小，会清空缓存并重新初始化的。

query_cache_min_res_unit: 存储缓存的最小内存块；(query_cache_size-Qcache_free_memory)/Qcache_queries_in_cache能够获得一个理想的值。有点类似磁盘格式化的块,太大了存小数据浪费空间,太小了存大数据浪费时间

query_cache_limit: 单个缓存对象的最大值，超出时则不预缓存；手动使用SQL_NO_CACHE可以人为地避免尝试缓存返回结果超出此参数限定值的语句。(MySQL有个额外的步骤,他会先把结果缓存下来,才查看是否超过了最大值,超过了才会把此数据清出缓存)

query_cache_wlock_invalidate: 如果某个表被其它用户连接锁住了，是否仍然从缓存中返回结果。OFF表示返回。

---

命中率

         :次数命中率:命中的次数,如:有100条缓存数据,每次命中20条

          :字节命中率:命中的大小,如:有100M的缓存大小,每次可以命中20M

  :在实际生产环境中,以两种命中方式共同衡量缓存命中的率

查看次数命中率

  1: mysql> SHOW GLOBAL STATUS LIKE ‘Qcache%‘;
  2: +-------------------------+----------+
  3: | Variable_name           | Value    |
  4: +-------------------------+----------+
  5: | Qcache_free_blocks      | 6        |
  6: | Qcache_free_memory      | 16671120 |
  7: | Qcache_hits             | 19       |
  8: | Qcache_inserts          | 74       |
  9: | Qcache_lowmem_prunes    | 0        |
 10: | Qcache_not_cached       | 52       |
 11: | Qcache_queries_in_cache | 60       |
 12: | Qcache_total_blocks     | 132      |
 13: +-------------------------+----------+
 14: 8 rows in set (0.04 sec)

  Qcache_hits : 命中次数

  Qcache_inserts :向缓存空间中写入缓存数据的次数

　Qcache_free_memory ：空闲空间大小,这是不可调的,要想调,得调总空间大小

  Qcache_free_blocks : 空闲的块数

  Qcache_total_blocks : 总块数,缓存初始化分配的总块数的数目

  Qcache_queries_in_cache : 在缓存中缓存的查询个数

  Qcache_not_cached : 没被缓存的查询语句数目

  Qcache_lowmem_prunes : 因为内存太小,而修减内存的次数(缓存满了,清除老缓存的次数)

 如果Qcache_free_memory 还很多,但是Qcache_lowmem_prunes的次数也很多,说明,内存中可能存在碎片了,这时需要碎片整理,整理连续的内存空间

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

   碎片整理：FLUSH QUERY_CACHE
   清空缓存：RESET QUERY_CACHE

 计算命中率:

  1: mysql> SHOW GLOBAL STATUS WHERE Variable_name=‘Qcache_hits‘ OR Variable_name=‘Com_select‘;
  2: +---------------+-------+
  3: | Variable_name | Value |
  4: +---------------+-------+
  5: | Com_select    | 133   |
  6: | Qcache_hits   | 19    |
  7: +---------------+-------+
  8: 2 rows in set (0.00 sec)

 命中率：Qcache_hits/(Com_select+Qcache_hits)------>19/(133+19),仅做参考,次数命中率

 如果缓存已经预热了,但就是命中不了,出现缓存抖动,那么建议关闭缓存.

---

    缓存优化使用思路：
        1、批量写入而非多次单个写入；
        2、缓存空间不宜过大，因为大量缓存同时失效时会导致服务器假死；
        3、必要时，使用SQL_CACHE和SQL_N0_CACHE手动控制缓存；
        4、对写密集型的应用场景来说，禁用缓存反而能提高性能；
        5、在线事务的场景,读写比例一样大的时候,建立关闭缓存;

 

MySQL服务器管理及查询缓存详解(笔记),古老的榕树,5-wow.com