MySQL 权限和查询缓存
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
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
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 > 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' 'password'] [,'user'@'host' 'password']...]
★重命名：RENAME USER
[*]RENAME USER old_user TO new_user[, old_user TO new_user] ...
★删除用户：
[*]DROP USER 'user'@'host' [, 'user'@'host'] ...
★修改用户密码
[*]SET PASSWORD = PASSWORD('cleartext password');
[*]UPDATE mysql.user SET Password=PASSWORD('cleartext password')WHERE User='USERNAME' AND Host='HOST';
[*]mysqladmin -uUSERNAME -hHOST -ppassword 'NEW_PASS'
※ FLUSH PRIVILEGES//通知mysql进程重读授权表命令演示：
1.可以使用SHOW PROCESSLIST 查看当前线程列表，如下：
1
2
3
4
5
6
7
8
MariaDB > 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.创建用户，修改密码，修改用户名，并查看刚创建用户的权限
1
2
3
# 创建用户账号及密码
MariaDB [(none)]> CREATE USER 'tao'@'192.168.1.%' IDENTIFIED BY '134296';
Query OK, 0 rows affected (0.00 sec)
1
2
3
4
5
6
# 修改用户账户密码，并重读授权表
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)
1
2
3
# 修改用户名
MariaDB [(none)]> RENAME USER 'tao'@'192.168.1.%' TO 'xiu'@'192.168.1.%';
Query OK, 0 rows affected (0.02 sec)
1
2
3
4
5
6
7
8
# 并查看刚创建用户的权限，发现为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.登录创建的用户账户，创建数据库，可以发现权限不被允许，可见新创建的用户几乎是没有任何权限的；要想有权限就需要给用户授予相关的管理、程序、数据类的权限等
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 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 > CREATE DATABASE mydb;
ERROR 1044 (42000): Access denied for user 'xiu'@'192.168.1.%' to database 'mydb'
4.要想有权限就需要给用户授予相关的管理、程序、数据类的权限等，授予xiu用户相关的权限如下：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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表中有关用户的账户名和密码如下：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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选项，如下：
# systemctl stop mariadb.service# 停止mariadb服务# vim /usr/lib/systemd/system/mariadb.service# 修改mariadb启动文件 ExecStart=/usr/bin/mysqld_safe --basedir=/usr --skip-grant-tables --skip-networking # systemctl daemon-reload# 要想生效需要重载2）再次启动mariadb服务，然后使用mysql直接连接，连接成功后使用UPDATE修改密码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 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: 4Changed: 4Warnings: 0
MariaDB [(none)]> exit
Bye
3）然后再次关闭mariadb服务，把mariadb启动文件中添加的--skip-grant-tables和--skip-networking选项删除即可
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# systemctl stop mariadb.service
# vim /usr/lib/systemd/system/mariadb.service
ExecStart=/usr/bin/mysqld_safe --basedir=/usr# 把添加的选项删除即可
# systemctl daemon-reload
# systemctl start mariadb.service
# mysql# 再使用mysql直接登录，发现已经登录不上了
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
# 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 priv_level
[*]TO user_specification [, user_specification] ... 指明用户名、主机、密码（存在的话就不需要了），一次可授权多个
[*] ssl_option] ...}]
[*]
★查看授权：SHOW GRANTS
[*]SHOW GRANTS
★取消授权：REVOKE☉REVOKE
[*]priv_type [(column_list)][, priv_type [(column_list)]] ...
[*]ON priv_level
[*]FROM'user'@'host' [,'user'@'host'] ...
☉REVOKE ALL PRIVILEGES, GRANT OPTION
[*]FROM user [, user] ...
演示：
1.创建tao用户账户，并指定密码；
1
2
3
4
5
6
7
8
9
10
MariaDB > CREATE USER 'tao'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.01 sec)
MariaDB > 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用户所有权限；
1
2
3
4
5
6
7
8
9
10
11
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用户，如下：
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
MariaDB [(none)]> CREATE USER 'jing'@'localhost';# 在root管理员上创建jing用户
Query OK, 0 rows affected (0.00 sec)
# 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用户
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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的权限
1
2
3
4
5
6
7
8
9
10
11
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表上某个字段的授权，如下：
1
2
MariaDB [(none)]> GRANT SELECT(Name,Age),UPDATE(Age) ON hellodb.students TO 'tao'@'localhost';
Query OK, 0 rows affected (0.06 sec)
登录tao的用户账号，查看权限，执行操作如下：
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
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
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 > SHOW TABLES;# 可以发现仅能看见我们授权给的students表
+-------------------+
| Tables_in_hellodb |
+-------------------+
| students |
+-------------------+
1 row in set (0.05 sec)
MariaDB > 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 > SELECT * FROM students; # 查询所有的的字段发现不允许，因为其他字段未经授权
ERROR 1143 (42000): SELECT command denied to user 'tao'@'localhost' for column 'StuID' in table 'students'
MariaDB > 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 > UPDATE students SET Age=21 WHERE Name='Duan YU'; # 可以修改年龄
Query OK, 1 row affected (0.00 sec)
Rows matched: 1Changed: 1Warnings: 0
MariaDB > 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 > 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语句，查看缓存命中率等统计参数
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
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
# 设定缓存空间大小为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 > 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 > 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 > 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 > SHOW STATUS LIKE 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select | 5 |
+---------------+-------+
1 row in set (0.00 sec)
3.查看各种语句的执行次数，不能修改，可以置零
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
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
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)
页:
[1]