MySQL 权限和查询缓存
================================================================================
概述：
本章将主要介绍MySQL数据库中的权限和查询缓存，具体内容如下：
MySQL的权限类别；
·数据类权限：库级别、表级别、字段级别；
·管理类权限；
·程序类权限；
MySQL用户管理：
·用户账号；
·创建、重命名、删除、修改用户密码
忘记管理员密码的解决办法：
授权：GRANT
·创建、查看及取消授权
查询缓存
·查询缓存相关的服务器变量：
·统计缓存数据状态变量：
================================================================================
MySQL用户和权限管理
1.权限类别
★用户账号：user@host
user：账户名称；
host：此账户可通过哪些客户端主机请求创建连接线程；
%：任意长度的任意字符；
_：任意单个字符；
★MySQL权限类别：
※数据类权限：
库级别：把某个数据库的所有权限或某些权限授权给指定用户（数据库级别的）；
表级别：把一个数据库中的某个表或有限的几个表授权给指定用户；
字段级别：把一个表上指定的字段授权给指定用户；
※管理类：如改变服务器变量的值等；
※程序类：能否调用或执行函数，程序代码等程序；
☉管理类：
CREATE USER； //创建用户
RELOAD； //可以使mysql关闭日志文件，滚动日志文件并打开新文件
LOCK TABLES；
REPLICATION CLIENT， REPLICATION SLAVE；
SHUTDOWN； //关闭mysql服务器进程的权限
FILE； //从文件中加载数据的权限
SHOW DATABASES；
PROCESS； //查看进程
SUPER //不受控制的任何管理功能等权限
☉程序类：
FUNCTION（存储函数），PROCEDURE（存储过程），TRIGGER（触发器）
操作：CREATE，ALTER，DROP，EXECUTE（执行）
☉库和表级别：
CREATE，ALTER，DROP
INDEX
CREATE VIEW
SHOW VIEW
GRANT：授权
OPTION：能够把自己获得的权限生成一个副本转赠给其它用户；
☉数据操作权限：
◆表：
INSERT/DELETE/UPDATE/SELECT
◆字段：
SELECT(col1,col2,...)
UPDATE(col1,col2,...)
INSERT(col1,col2,...)
☉所有权限：
ALL
ALL PRIVILEGES
★元数据数据库（数据字典）：mysql
☉授权相关的表：
db, host, user
tables_priv, column_priv, procs_priv, proxies_priv
MariaDB [(none)]> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed 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)
2.MySQL用户管理：
★用户账号：user@host
user：账户名称；
host：此账户可通过哪些客户端主机请求创建连接线程;（IP,主机名，NETWORK）
%：任意长度的任意字符；
_：任意单个字符；
注意：
基于主机名授权和IP地址授权是两个不同的账号，如果基于主机名授权时会反解主机名到IP地址，一般不建议，并且要跳过主机名解析
★skip_name_resolve={ON|OFF}
是否跳过主机名解析
★创建用户：
CREATE USER 'user'@'host' [IDENTIFIED BY [PASSWORD] 'password'] [,'user'@'host' [IDENTIFIED BY [PASSWORD] 'password']...]
★重命名：RENAME USER
RENAME USER old_user TO new_user[, old_user TO new_user] ...
★删除用户：
DROP USER 'user'@'host' [, 'user'@'host'] ...
★修改用户密码
SET PASSWORD [FOR 'user'@'host'] = PASSWORD('cleartext password');
UPDATE mysql.user SET Password=PASSWORD('cleartext password') WHERE User='USERNAME' AND Host='HOST';
mysqladmin -uUSERNAME -hHOST -p password 'NEW_PASS'
☉FLUSH PRIVILEGES
通知mysql进程重读授权表
演示：
1.可以使用SHOW PROCESSLIST 查看当前线程列表，如下：
MariaDB [hellodb]> SHOW PROCESSLIST; +----+---------+-----------------+---------+---------+------+-------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+---------+-----------------+---------+---------+------+-------+------------------+----------+ | 3 | root | localhost | hellodb | Query | 0 | NULL | SHOW PROCESSLIST | 0.000 | | 4 | rsyslog | 127.0.0.1:38101 | Syslog | Sleep | 123 | | NULL | 0.000 | +----+---------+-----------------+---------+---------+------+-------+------------------+----------+ 2 rows in set (0.00 sec)
2.创建用户，修改密码，修改用户名，并查看刚创建用户的权限
# 创建用户账号及密码 MariaDB [(none)]> CREATE USER 'tao'@'192.168.1.%' IDENTIFIED BY '134296'; Query OK, 0 rows affected (0.00 sec)
# 修改用户账户密码，并重读授权表
MariaDB [(none)]> SET PASSWORD FOR 'tao'@'192.168.1.%' = PASSWORD('123456');
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
# 修改用户名 MariaDB [(none)]> RENAME USER 'tao'@'192.168.1.%' TO 'xiu'@'192.168.1.%'; Query OK, 0 rows affected (0.02 sec)
# 并查看刚创建用户的权限，发现为usage MariaDB [(none)]> SHOW GRANTS FOR 'xiu'@'192.168.1.%'; +--------------------------------------------------------------------------------------------------------------+ | Grants for xiu@192.168.1.% | +--------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'xiu'@'192.168.1.%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' | +--------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
3.登录创建的用户账户，创建数据库，可以发现权限不被允许，可见新创建的用户几乎是没有任何权限的；要想有权限就需要给用户授予相关的管理、程序、数据类的权限等
[root@centos7 ~]# mysql -uxiu -h192.168.1.107 -p123456 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 6 Server version: 5.5.44-MariaDB MariaDB Server Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | test | +--------------------+ 2 rows in set (0.05 sec) MariaDB [test]> CREATE DATABASE mydb; ERROR 1044 (42000): Access denied for user 'xiu'@'192.168.1.%' to database 'mydb'
4.要想有权限就需要给用户授予相关的管理、程序、数据类的权限等，授予xiu用户相关的权限如下：
MariaDB [(none)]> GRANT ALL ON mydb.* TO 'xiu'@'192.168.1.%'; # 授予所有的权限 Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> SHOW GRANTS FOR 'xiu'@'192.168.1.%'; +--------------------------------------------------------------------------------------------------------------+ | Grants for xiu@192.168.1.% | +--------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'xiu'@'192.168.1.%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' | | GRANT ALL PRIVILEGES ON `mydb`.* TO 'xiu'@'192.168.1.%' | +--------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) MariaDB [(none)]> REVOKE UPDATE ON mydb.* FROM 'xiu'@'192.168.1.%'; # 回收UPDATE权限 Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> SHOW GRANTS FOR 'xiu'@'192.168.1.%'; +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for xiu@192.168.1.% | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'xiu'@'192.168.1.%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' | | GRANT SELECT, INSERT, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mydb`.* TO 'xiu'@'192.168.1.%' | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
3.忘记管理员密码的解决办法：
★解决步骤如下：
(1)启动mysqld进程时，使用--skip-grant-tables和--skip-networking选项；
CentOS 7：mariadb.service
CentOS 6：/etc/init.d/mysqld
(2)通过UPDATE命令修改管理员密码；
(3)以正常方式启动mysqld进程；
演示：
1.以CentOS 7 为例，首先查看mysql数据库user表中有关用户的账户名和密码如下：
MariaDB [(none)]> SELECT user,host,password FROM mysql.user; +------------+-------------+-------------------------------------------+ | user | host | password | +------------+-------------+-------------------------------------------+ | root | localhost | *41EE0F8759D5340036B009143E1727DB5787A448 | | root | centos7 | *41EE0F8759D5340036B009143E1727DB5787A448 | | root | 127.0.0.1 | *41EE0F8759D5340036B009143E1727DB5787A448 | | root | ::1 | *41EE0F8759D5340036B009143E1727DB5787A448 | | ultraxuser | 127.0.0.1 | *41EE0F8759D5340036B009143E1727DB5787A448 | | ultraxuser | localhost | *41EE0F8759D5340036B009143E1727DB5787A448 | | rsyslog | 127.0.0.1 | *41EE0F8759D5340036B009143E1727DB5787A448 | | rsyslog | local | *41EE0F8759D5340036B009143E1727DB5787A448 | | zbxuser | 10.1.%.% | *24E65C3D3577DA6C2A596788CEAA02923A74B75D | | zbxuser | 127.0.0.1 | *24E65C3D3577DA6C2A596788CEAA02923A74B75D | | xiu | 192.168.1.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +------------+-------------+-------------------------------------------+
2.假如现在我忘记了root管理员用户的密码，执行过程如下：
1）首先关闭mysql服务，修改启动mariadb.service的文件，添加 --skip-grant-tables和--skip-networking选项，如下：
[root@centos7 ~]# systemctl stop mariadb.service # 停止mariadb服务 [root@centos7 ~]# vim /usr/lib/systemd/system/mariadb.service # 修改mariadb启动文件 ExecStart=/usr/bin/mysqld_safe --basedir=/usr --skip-grant-tables --skip-networking //在ExecStart 后添加 [root@centos7 ~]# systemctl daemon-reload # 要想生效需要重载
2）再次启动mariadb服务，然后使用mysql直接连接，连接成功后使用UPDATE修改密码
[root@centos7 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.44-MariaDB MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> UPDATE mysql.user SET Password=PASSWORD('taoxiu') WHERE User='root'; # 修改密码
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
MariaDB [(none)]> exit
Bye
3）然后再次关闭mariadb服务，把mariadb启动文件中添加的--skip-grant-tables和--skip-networking选项删除即可
[root@centos7 ~]# systemctl stop mariadb.service [root@centos7 ~]# vim /usr/lib/systemd/system/mariadb.service ExecStart=/usr/bin/mysqld_safe --basedir=/usr # 把添加的选项删除即可 [root@centos7 ~]# systemctl daemon-reload [root@centos7 ~]# systemctl start mariadb.service [root@centos7 ~]# mysql # 再使用mysql直接登录，发现已经登录不上了 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) [root@centos7 ~]# mysql -uroot -ptaoxiu # 使用修改的密码成功登录 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 3 Server version: 5.5.44-MariaDB MariaDB Server Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
4.授权：GRANT
★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 with_option ...]
★查看授权：SHOW GRANTS
SHOW GRANTS [FOR 'user'@'host']
★取消授权：REVOKE
☉REVOKE
priv_type [(column_list)][, priv_type [(column_list)]] ...
ON [object_type] priv_level
FROM 'user'@'host' [, 'user'@'host'] ...
☉REVOKE ALL PRIVILEGES, GRANT OPTION
FROM user [, user] ...
演示：
1.创建tao用户账户，并指定密码；
MariaDB [mysql]> CREATE USER 'tao'@'localhost' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.01 sec) MariaDB [mysql]> SHOW GRANTS FOR 'tao'@'localhost'; +------------------------------------------------------------------------------------------------------------+ | Grants for tao@localhost | +------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'tao'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' | +------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
2.授予tao用户所有权限；
MariaDB [(none)]> GRANT ALL ON mydb.* TO 'tao'@'localhost'; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> SHOW GRANTS FOR 'tao'@'localhost'; +------------------------------------------------------------------------------------------------------------+ | Grants for tao@localhost | +------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'tao'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' | | GRANT ALL PRIVILEGES ON `mydb`.* TO 'tao'@'localhost' | +------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
3.现在我再创建一个叫'jing'的用户，看让tao用户能否把自己的权限转增给jing用户，如下：
MariaDB [(none)]> CREATE USER 'jing'@'localhost'; # 在root管理员上创建jing用户 Query OK, 0 rows affected (0.00 sec) [root@centos7 ~]# mysql -utao -hlocalhost -p123456 # 登录tao用户账号 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 10 Server version: 5.5.44-MariaDB MariaDB Server Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show databases; # 可以看到授权的mydb数据库 +--------------------+ | Database | +--------------------+ | information_schema | | mydb | | test | +--------------------+ 3 rows in set (0.01 sec) # tao用户把自己的权限转赠给jing用户，发现失败，这是因为要想有转赠的权限，需要管理员授予WITH GRANT OPTION权限 MariaDB [(none)]> GRANT SELECT,INSERT,DELETE on mydb.* TO 'jing'@'localhost'; ERROR 1044 (42000): Access denied for user 'tao'@'localhost' to database 'mydb'
4.管理员授予tao用户允许转赠的权限，再次测试可以发现可以成功转赠给jing用户
MariaDB [(none)]> GRANT ALL ON mydb.* TO 'tao'@'localhost' WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) # --------------------------------------------------------------------------------------- # 登录tao用户账号，查看其权限可以发现多了一项 WITH GRANT OPTION 的权限， MariaDB [(none)]> SHOW GRANTs FOR 'tao'@'localhost'; +------------------------------------------------------------------------------------------------------------+ | Grants for tao@localhost | +------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'tao'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' | | GRANT ALL PRIVILEGES ON `mydb`.* TO 'tao'@'localhost' WITH GRANT OPTION | +------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) # 成功将自己的权限转赠给jing用户 MariaDB [(none)]> GRANT SELECT,INSERT,DELETE on mydb.* TO 'jing'@'localhost'; Query OK, 0 rows affected (0.00 sec)
5.收回tao用户的GRANT OPTION的权限
MariaDB [(none)]> REVOKE GRANT OPTION ON mydb.* FROM 'tao'@'localhost'; # 回收权限 Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> SHOW GRANTS FOR 'tao'@'localhost'; +------------------------------------------------------------------------------------------------------------+ | Grants for tao@localhost | +------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'tao'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' | | GRANT ALL PRIVILEGES ON `mydb`.* TO 'tao'@'localhost' | +------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
6.现在授权tao用户hellodb数据库的stuents表上某个字段的授权，如下：
MariaDB [(none)]> GRANT SELECT(Name,Age),UPDATE(Age) ON hellodb.students TO 'tao'@'localhost'; Query OK, 0 rows affected (0.06 sec)
登录tao的用户账号，查看权限，执行操作如下：
MariaDB [(none)]> SHOW GRANTS FOR 'tao'@'localhost'; # 查看权限如下： +------------------------------------------------------------------------------------------------------------+ | Grants for tao@localhost | +------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'tao'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' | | GRANT ALL PRIVILEGES ON `mydb`.* TO 'tao'@'localhost' | | GRANT SELECT (Age, Name), UPDATE (Age) ON `hellodb`.`students` TO 'tao'@'localhost' | +------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) MariaDB [(none)]> SHOW DATABASES; # 可以看到数据库中又多了一个hellodb的数据库 +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | mydb | | test | +--------------------+ 4 rows in set (0.00 sec) MariaDB [(none)]> USE hellodb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [hellodb]> SHOW TABLES; # 可以发现仅能看见我们授权给的students表 +-------------------+ | Tables_in_hellodb | +-------------------+ | students | +-------------------+ 1 row in set (0.05 sec) MariaDB [hellodb]> DESC students; # 表结构也只有Name和Age两个授权的字段，其他未授权的均不可见 +-------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------+------+-----+---------+-------+ | Name | varchar(50) | NO | | NULL | | | Age | tinyint(3) unsigned | NO | | NULL | | +-------+---------------------+------+-----+---------+-------+ 2 rows in set (0.08 sec) MariaDB [hellodb]> SELECT * FROM students; # 查询所有的的字段发现不允许，因为其他字段未经授权 ERROR 1143 (42000): SELECT command denied to user 'tao'@'localhost' for column 'StuID' in table 'students' MariaDB [hellodb]> SELECT Name,Age FROM students; # 只能查看授权的字段 +---------------+-----+ | Name | Age | +---------------+-----+ | Shi Zhongyu | 22 | | Shi Potian | 22 | | Xie Yanke | 53 | | Ding Dian | 32 | | Yu Yutong | 26 | | Shi Qing | 46 | | Xi Ren | 19 | | Lin Daiyu | 17 | | Ren Yingying | 20 | | Yue Lingshan | 19 | | Yuan Chengzhi | 23 | | Wen Qingqing | 19 | | Tian Boguang | 33 | | Lu Wushuang | 17 | | Duan Yu | 19 | | Xu Zhu | 21 | | Lin Chong | 25 | | Hua Rong | 23 | | Xue Baochai | 18 | | Diao Chan | 19 | | Huang Yueying | 22 | | Xiao Qiao | 20 | | Ma Chao | 23 | | Xu Xian | 27 | | Sun Dasheng | 100 | +---------------+-----+ 25 rows in set (0.00 sec) #----------------------------------------------------------------------------------- MariaDB [hellodb]> UPDATE students SET Age=21 WHERE Name='Duan YU'; # 可以修改年龄 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [hellodb]> UPDATE students SET Name=GuoJing WHERE Name='Duan YU'; # 修改姓名失败，因为只授权了UPDATE允许修改Age字段 ERROR 1143 (42000): UPDATE command denied to user 'tao'@'localhost' for column 'Name' in table 'students' MariaDB [hellodb]> SELECT Name,Age FROM students; +---------------+-----+ | Name | Age | +---------------+-----+ | Shi Zhongyu | 22 | | Shi Potian | 22 | | Xie Yanke | 53 | | Ding Dian | 32 | | Yu Yutong | 26 | | Shi Qing | 46 | | Xi Ren | 19 | | Lin Daiyu | 17 | | Ren Yingying | 20 | | Yue Lingshan | 19 | | Yuan Chengzhi | 23 | | Wen Qingqing | 19 | | Tian Boguang | 33 | | Lu Wushuang | 17 | | Duan Yu | 21 | | Xu Zhu | 21 | | Lin Chong | 25 | | Hua Rong | 23 | | Xue Baochai | 18 | | Diao Chan | 19 | | Huang Yueying | 22 | | Xiao Qiao | 20 | | Ma Chao | 23 | | Xu Xian | 27 | | Sun Dasheng | 100 | +---------------+-----+ 25 rows in set (0.00 sec)
查询缓存：
1.介绍
★缓存：k/v
key：查询语句的hash值
value：查询语句的执行结果
★如何判断缓存是否命中：
通过查询语句的哈希值判断：哈希值考虑的因素包括
查询本身；
要查询数据库；
客户端使用的协议版本；
...
★哪些查询可能不会被缓存？
查询语句中包含UDF（用户自定义函数）
存储函数 如:CURRENT_TIME()
用户自定义变量
临时表
mysql系统表或者是包含列级别权限的查询
有着不确定结果值的函数(now())；
★附图：
查询执行过程
2.查询缓存相关的服务器变量：
★query_cache_limit：
能够缓存的最大查询结果；（单语句结果集大小上限）
有着较大结果集的语句，显式使用 SQL_NO_CACHE，以避免先缓存再移出；
★query_cache_min_res_unit：
内存块的最小分配单位；缓存过小的查询结果集会浪费内存空间；
较小的值会减少空间浪费，但会导致更频繁地内存分配及回收操作；
较大值的会带来空间浪费；
★query_cache_size：
查询缓存空间的总共可用的大小；单位是字节，必须是1024的整数倍；
★query_cache_strip_comments
★query_cache_type：
缓存功能启用与否；
ON：启用；
OFF：禁用；
DEMAND：按需缓存，仅缓存SELECT语句中带SQL_CACHE的查询结果；
★query_cache_wlock_invalidate：
如果某表被其它连接锁定，是否仍然可以从查询缓存中返回查询结果；
默认为OFF，表示可以；ON则表示不可以；
----------------------------------------------------------------------------
注意：
要想让设定永久有效，要写在配置文件当中
3.统计缓存数据状态变量：
★查看命令：
mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';
★命中率：
Qcache_hits/Com_select （命中次数/执行查询语句的次数）
★查看SELECT语句的执行次数
mysql> SHOW STATUS LIKE 'Com_select '
演示：
1.查询缓存相关的服务器变量
MariaDB [(none)]> SHOW VARIABLES LIKE '%query_cache%'; +------------------------------+---------+ | Variable_name | Value | +------------------------------+---------+ | have_query_cache | YES | //是否拥有缓存功能 | query_cache_limit | 1048576 | //单语句结果集上限，字节为单位，默认大小为1M | query_cache_min_res_unit | 4096 | //内存块的最小分配单位 | query_cache_size | 0 | | query_cache_strip_comments | OFF | | query_cache_type | ON | //缓存功能是否启用 | query_cache_wlock_invalidate | OFF | +------------------------------+---------+
2.设定缓存空间大小，并执行SELECT语句，查看缓存命中率等统计参数
# 设定缓存空间大小为10M MariaDB [(none)]> SET GLOBAL query_cache_size=10485760; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> SHOW VARIABLES LIKE '%query_cache%'; +------------------------------+----------+ | Variable_name | Value | +------------------------------+----------+ | have_query_cache | YES | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 10485760 | # 设置成功 | query_cache_strip_comments | OFF | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+----------+ 7 rows in set (0.01 sec) MariaDB [(none)]> USE hellodb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [hellodb]> SHOW STATUS LIKE 'Qcache%'; # 查看缓存统计变量 +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 10468296 | | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 1 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | +-------------------------+----------+ 8 rows in set (0.01 sec) # 执行SELECT语句3次 MariaDB [hellodb]> SELECT Name,Age FROM students WHERE Age >= 20; +---------------+-----+ | Name | Age | +---------------+-----+ | Shi Zhongyu | 22 | | Shi Potian | 22 | | Xie Yanke | 53 | | Ding Dian | 32 | | Yu Yutong | 26 | | Shi Qing | 46 | | Ren Yingying | 20 | | Yuan Chengzhi | 23 | | Tian Boguang | 33 | | Duan Yu | 21 | | Xu Zhu | 21 | | Lin Chong | 25 | | Hua Rong | 23 | | Huang Yueying | 22 | | Xiao Qiao | 20 | | Ma Chao | 23 | | Xu Xian | 27 | | Sun Dasheng | 100 | +---------------+-----+ 18 rows in set (0.00 sec) MariaDB [hellodb]> SHOW STATUS LIKE 'Qcache%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 10466752 | | Qcache_hits | 2 | # 缓存命中2次 | Qcache_inserts | 1 | # 插入了一个缓存语句 | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 1 | | Qcache_queries_in_cache | 1 | | Qcache_total_blocks | 4 | +-------------------------+----------+ 8 rows in set (0.00 sec) # 查询执行SELECT语句的次数 MariaDB [hellodb]> SHOW STATUS LIKE 'Com_select'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Com_select | 5 | +---------------+-------+ 1 row in set (0.00 sec)
3.查看各种语句的执行次数，不能修改，可以置零
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE 'Com_%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Com_admin_commands | 0 | | Com_alter_db | 0 | | Com_alter_db_upgrade | 0 | | Com_alter_event | 0 | | Com_alter_function | 0 | | Com_alter_procedure | 0 | | Com_alter_server | 0 | | Com_alter_table | 0 | | Com_alter_tablespace | 0 | | Com_analyze | 0 | | Com_assign_to_keycache | 0 | | Com_begin | 157 | | Com_binlog | 0 | | Com_call_procedure | 0 | | Com_change_db | 5 | | Com_change_master | 0 | | Com_check | 0 | | Com_checksum | 0 | | Com_commit | 157 | | Com_create_db | 0 | | Com_create_event | 0 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_procedure | 0 | | Com_create_server | 0 | | Com_create_table | 0 | | Com_create_trigger | 0 | | Com_create_udf | 0 | | Com_create_user | 2 | | Com_create_view | 0 | | Com_dealloc_sql | 0 | | Com_delete | 0 | | Com_delete_multi | 0 | | Com_do | 0 | | Com_drop_db | 0 | | Com_drop_event | 0 | | Com_drop_function | 0 | | Com_drop_index | 0 | | Com_drop_procedure | 0 | | Com_drop_server | 0 | | Com_drop_table | 0 | | Com_drop_trigger | 0 | | Com_drop_user | 0 | | Com_drop_view | 0 | | Com_empty_query | 0 | | Com_execute_sql | 0 | | Com_flush | 1 | | Com_grant | 6 | | Com_ha_close | 0 | | Com_ha_open | 0 | | Com_ha_read | 0 | | Com_help | 0 | | Com_insert | 207 | | Com_insert_select | 0 | | Com_install_plugin | 0 | | Com_kill | 0 | | Com_load | 0 | | Com_lock_tables | 0 | | Com_optimize | 0 | | Com_preload_keys | 0 | | Com_prepare_sql | 0 | | Com_purge | 0 | | Com_purge_before_date | 0 | | Com_release_savepoint | 0 | | Com_rename_table | 0 | | Com_rename_user | 0 | | Com_repair | 0 | | Com_replace | 0 | | Com_replace_select | 0 | | Com_reset | 0 | | Com_resignal | 0 | | Com_revoke | 1 | | Com_revoke_all | 0 | | Com_rollback | 0 | | Com_rollback_to_savepoint | 0 | | Com_savepoint | 0 | | Com_select | 27 | | Com_set_option | 2 | | Com_show_authors | 0 | | Com_show_binlog_events | 0 | | Com_show_binlogs | 0 | | Com_show_charsets | 0 | | Com_show_client_statistics | 0 | | Com_show_collations | 0 | | Com_show_contributors | 0 | | Com_show_create_db | 0 | | Com_show_create_event | 0 | | Com_show_create_func | 0 | | Com_show_create_proc | 0 | | Com_show_create_table | 0 | | Com_show_create_trigger | 0 | | Com_show_databases | 9 | | Com_show_engine_logs | 0 | | Com_show_engine_mutex | 0 | | Com_show_engine_status | 0 | | Com_show_errors | 0 | | Com_show_events | 0 | | Com_show_fields | 37 | | Com_show_function_status | 0 | | Com_show_grants | 9 | | Com_show_index_statistics | 0 | | Com_show_keys | 0 | | Com_show_master_status | 0 | | Com_show_open_tables | 0 | | Com_show_plugins | 0 | | Com_show_privileges | 0 | | Com_show_procedure_status | 0 | | Com_show_processlist | 0 | | Com_show_profile | 0 | | Com_show_profiles | 0 | | Com_show_relaylog_events | 0 | | Com_show_slave_hosts | 0 | | Com_show_slave_status | 0 | | Com_show_status | 4 | | Com_show_storage_engines | 0 | | Com_show_table_statistics | 0 | | Com_show_table_status | 0 | | Com_show_tables | 7 | | Com_show_triggers | 0 | | Com_show_user_statistics | 0 | | Com_show_variables | 3 | | Com_show_warnings | 0 | | Com_signal | 0 | | Com_slave_start | 0 | | Com_slave_stop | 0 | | Com_stmt_close | 0 | | Com_stmt_execute | 0 | | Com_stmt_fetch | 0 | | Com_stmt_prepare | 0 | | Com_stmt_reprepare | 0 | | Com_stmt_reset | 0 | | Com_stmt_send_long_data | 0 | | Com_truncate | 0 | | Com_uninstall_plugin | 0 | | Com_unlock_tables | 0 | | Com_update | 2 | | Com_update_multi | 0 | | Com_xa_commit | 0 | | Com_xa_end | 0 | | Com_xa_prepare | 0 | | Com_xa_recover | 0 | | Com_xa_rollback | 0 | | Com_xa_start | 0 | | Compression | OFF | +----------------------------+-------+ 144 rows in set (0.00 sec)
转载于:https://blog.51cto.com/1992tao/1877656
95
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
