二次写 mysql_MySQL二次整理(5)v1.0

MySQL用户和权限管理

用户账号：user@host

MySQL权限类别：

库级别：

表级别：

字段级别：

管理类：

程序类：

管理类：

CREATE USER创建用户

RELOAD是否允许重载

LOCK TABLES锁定表

REPLICATION CLIENT， REPLICATION SLAVE授权复制操作

SHUTDOWN关闭数据库

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代理

MySQL用户管理：

'user'@'host';

host:

IP

主机名

NETWORK

通配符%, _

skip_name_resolve={ON|OFF}是否启动DNS解析 建议开启 减少时间

创建用户：

CREATE USER'user'@'host' [IDENTIFIED BY [PASSWORD] 'password'] [,'user'@'host' [IDENTIFIED BY [PASSWORD] 'password']...] 创建命令 没有任何权限

重命名：RENAME USER

RENAMEUSER old_userTOnew_user[, old_user TO new_user] ...

删除用户：

DROP USER 'user'@'host' [, 'user'@'host'] ...

修改用户密码：

(1) SET PASSWORD [FOR 'user'@'host'] = PASSWORD('cleartext password');

(2) UPDATE mysql.user SET Password=PASSWORD('cleartext password') WHERE User='USERNAME' AND Host='HOST';

(3) mysqladmin -uUSERNAME -hHOST -ppassword 'NEW_PASS'要求用户有SUPER权限

生效：FLUSH PRIVILEGES此表会缓存到内存中 所以必须刷新才能生效

忘记管理员密码的解决办法：

(1) 启动mysqld进程时，使用--skip-grant-tables和--skip-networking选项;不读mysql.user并只能从本机登录

(2) 通过UPDATE命令修改管理员密码；

(3) 以正常 方式启动mysqld进程；

修改配置文件自动登录:

~]# vim .my.cnf

[mysql]

user=’USER’

password=’PASSWD’

授权：GRANT

GRANTpriv_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 ...]

object_type:权限级别

TABLE表名

| FUNCTION函数名

| PROCEDURE过程名

priv_level:权限级别 哪个库哪个表

*

| *.*

| db_name.*

| db_name.tbl_name

| tbl_name

| db_name.routine_name

ssl_option:

SSL

| X509

| CIPHER 'cipher'

| ISSUER 'issuer'

| SUBJECT 'subject'

with_option:

GRANT OPTION

| MAX_QUERIES_PER_HOUR count每小时最多能发起多少此查询

| MAX_UPDATES_PER_HOUR count每小时最多能发起多少次更新操作

| MAX_CONNECTIONS_PER_HOUR count每小时最多能链接多少次

| MAX_USER_CONNECTIONS count单用户最大并发连接数

查看授权：SHOW GRANTS

SHOW GRANTS [FOR 'user'@'host']

取消授权：REVOKE

REVOKEpriv_type [(column_list)][, priv_type [(column_list)]] ...

ON [object_type] priv_level

FROM'user'@'host' [,'user'@'host'] ...

例:REVOKE All ON hellodb.students FROM‘test’@’%’; 收回授权时的priv_level必须于授权时给定的一致

REVOKE ALL PRIVILEGES, GRANT OPTION

FROM user [, user] ...

查询缓存：

缓存机制：k/v

key：查询语句的hash值

value：查询语句的执行结果

如何判断缓存是否命中：

通过查询语句的哈希值判断：哈希值考虑的因素包括

查询本身、要查询数据库、客户端使用的协议版本、...

哪些查询可能不会被缓存？

查询语句中包含UDF(用户自定义函数)

存储函数

用户自定义变量

临时表

mysql系统表或者是包含列级别权限的查询

有着不确定结果值的函数(now())；

查询缓存相关的服务器变量：

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则表示不可以；

Mysql>SET @@global.query_cache_size = XXX;

状态变量：缓存命中率的统计数据

mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';

+-------------------------+----------+

| Variable_name| Value|

+-------------------------+----------+

| Qcache_free_blocks| 1|有多少块是空闲的

| Qcache_free_memory| 16759688 |空闲空间

| Qcache_hits| 0|命中次数

| Qcache_inserts| 0|缓存的插入次数(比in_cache大,有失效的/LRU算法清理)

| Qcache_lowmem_prunes| 0|内存被清理的次数

| Qcache_not_cached| 0|未缓存的查询

| Qcache_queries_in_cache| 0|在缓存中的条目数量

| Qcache_total_blocks| 1|已分配的空间被划分成几个快

+-------------------------+----------+

命中率：

Qcache_hits/Com_select

**************************************

MySQL的索引：

索引的作用：加速查询操作；

表中数据子集：把表中某个或某些字段的数据提取出来另存为一个特定数据结构组织的数据；

某个字段或某些字段：WHERE子句中用到的字段；

索引类型：B+ TREE，HASH

B+ TREE：顺序存储，每一个叶子结点到根结点的距离相同；左前缀索引，适合于范围类型的数据查询；

适用于B+ TREE索引的查询类型：全键值、键值范围或键前缀；

全值匹配：精确匹配某个值；

匹配最左前缀：只精确起头的部分；

匹配范围值：

精确匹配某一列，范围匹配另一列；

只用访问索引的查询：覆盖索引；

不适用B+ TREE索引：

如查条件不是从最左侧列开始，索引无效；

不能跳过索引中的某列；

如果查询中的某个列是为范围查询，那么其右侧的列都无法再使用索引优化查询；

Hash索引：基于哈希表实现，特别适用于值的精确；

只有memory引擎

适用场景：

只支持等值比较查询，例如=, IN(), <=> (等值比较 但允许NULL存在)

不用场景：

所有非精确值查询；

索引优点：

降低需要扫描的数据量，减少了IO次数；

可以帮助避免排序操作，避免使用临时表； (临时表用来排序)

帮助将随机IO转为顺序IO；

高性能索引策略：

(1) 在WHERE中独立使用列，尽量其参与运算；(不要做数学运算)

(2) 左前缀索引：索引构建于字段的最左侧的多少个字符，要通过索引选择性来评估

索引选择性：不重复的索引值和数据表的记录总数的比值；

(3) 多列索引：

AND边接的多个查询条件更适合使用多列索引，而非多个单键索引；

(4) 选择合适的索引列次序：选择性最高的放左侧；

EXPLAIN来分析索引有效性：

EXPLAIN [explain_type] SELECT select_options

explain_type:

EXTENDED扩展信息输出

| PARTITIONS

输出结果：

id: 1

select_type: SIMPLE查询类型

table: students表

type: const查询方式

possible_keys: PRIMARY可能用到的索引

key: PRIMARY最终用到的索引

key_len: 4索引长度

ref: const

rows: 1

Extra:

id：当前查询语句中，第个SELECT语句的编号；

复制的查询的类型主要三种：

简单子查询

用于FROM中的子查询

联合查询

注意：联合查询的分析结果会出现一个额外的匿名临时表；

select_type：查询类型：

简单查询：SIMPLE

复杂查询：

简单子查询：SUBQUERY

用于FROM中的子查询：DERIVED

联合查询中的第一个查询：PRIMARY

联合查询中的第一个查询之后的其它查询：UNION

联合查询生成的临时表：UNION RESULT

table：查询针对的表；

type：关联类型，或称为访问类型，即MySQL如何去查询表中的行

ALL：全表扫描；

index：根据索引的顺序进行的全表扫描；但同时如果Extra列出现了"Using index”表示使用了覆盖索引；

range：有范围限制地根据索引实现范围扫描；扫描位置始于索引中的某一项，结束于另一项；

ref：根据索引返回的表中匹配到某单个值的所有行(匹配给定值的行不止一个)；

eq_ref：根据索引返回的表中匹配到某单个值的单一行，仅返回一个行，但需林与某个额外的参考值比较，而不是常数；

const，system：与某个常数比较，且只返回一行；

possiable_keys：查询中可能会用到的索引；

key：查询中使用的索引；

key_len：查询中用到的索引长度；

ref：在利用key字段所显示的索引完成查询操作时所引用的列或常量值；

rows：MySQL估计为找到所有的目标项而需要读取的行数；

Extra：额外信息

Using index：使用了覆盖索引进行的查询；

Using where：拿到数据后还要再次进行过滤；

Using temporary：使用了临时表以完成查询；

Using filesort：对结果使用了一个外部索引排序；