MySQL用户,权限,索引,事务,存储引擎,日志管理(第40天)
权限：
mysql中的权限可以分为：库级别，表级别，字段级别，管理类权限，程序类级别（是否有执行某些函数，存储过程的权限）
管理类级别的权限有：
CREATE TEMPORARY TABLES
CREATE USER
FILE 读写文件权限
SUPER
SHOW DATABASES
RELOAD
SHUTDOWN
REPLICATION SLAVE
REPLICATION CLIENT
LOCK TABLES 锁表权限
PROCESS 进程相关
程序类权限有：
FUNCTION 函数
PROCEDURE 存储过程
TRIGGER 触发器
如上配合CREATE， ALTER， DROP， EXCUTE 使用
库级别和表级别：TABLE or DATABASE
ALTER
CREATE
CREATE VIEW
DOOP
INDEX
SHOW VIEW
GRANT OPTION 把自己获得的权限赠给其他用户一个副本
数据操作权限：
SELECT 查询权限
INSERT 插入数据权限
DELETE 删除权限
UPDATA 更新权限
字段级别：
SELECT(col1, col2, col3, ...) 查询某些字段的权限
UPDATE(col1, col2, col3, ...)
INSERT(col1, col2, col3, ....)
所有权限：
ALL PRIVILEGES
数据库的权限数据保存在元数据库mysql中的，进入mysql时，使用 use mysql; show tables; 查看具体的权限授权情况， mysql中的授权相关的表有db、host、user，其他的表比如columns_priv、tables_priv, procs_priv
mysql的用户：
mysql的用户格式如下：有两部分组成用户和主机
用户@主机：表示此用户帐号可从@HOST范围内的某主机对此MySQL服务端建立连接；
@HOST: 表示客户端，HOST可以使用通配符（%，_)，主机名，ip地址
%:
_:
172.16.0.0/16, 172.16.%.%
创建用户：
CREATE USER 'username'@'host' IDENTIFIED BY 'your_password', ...
删除用户账号：
DRP USER 'username'@'host';
查看用户获得的权限：
SHOW GRANTS FOR 'username'@'host';
用户重命名：
RENAME USER old_user TO new_user;
修改用户的密码：3种方法 （进入mysql中输入help set password 查看帮助）
进入mysql后使用set命令 SET PASSWORD FOR username='your password'
进入mysql后更新mysql数据库中的user表中root用户
UPDATE mysql.user SET password=PASSWORD('password_for_root') WHERE user='root'; flush privileges;
在终端中使用mysqladmin password 'your password' 命令，不需要进入mysql （在终端中输入mysqladmin --help 查看帮助）
忘记mysql管理员密码步骤如下：
1：启动mysqld服务的时候，使用 --skip-grant-tables 和--skip-networking 选项
2：使用如上UPDATE命令修改管理员密码
3：关闭mysqld进程
4：启动mysqld服务
授权操作命令：使用help grant查看帮助信息
GRANT priv1, priv2, ... ON [TABLE|FUNCTION|PROCEDURE] priv_level TO 'username'@'host' [IDENTIFIED BY 'password'] [REQUIRE ssl_option] [WITH with_option]
priv_level 取值如下：
priv_level: {
*
*.*
db_name.*
db_name.tbl_name
tbl_name
db_name.routine_name
}
例如 grant update on pythondb.* to root@localhost; 表示root@localhost用户被授予对于pythondb数据库下的所有表等有更新操作
取消授权操作：
REVOKE priv1,priv2,... ON [TABLE|FUNCTION|PROCEDURE] priv_level FROM 'username'@'host',...;
mysql的查询缓存：
mysql的查询缓存原理：把查询语句的哈希值作为key，查询结果作为value。
什么样的语句不会缓存？
查询语句中有不确定数据时不会缓存；
一般来主，如果查询中包含用户自定义的函数（UDF）、存储函数、用户变量、临时表、mysql库中表、或者任何包含权限信息表，都不会缓存；
查询缓存相关的mysql的变量有：show global variables like '%query_cache%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
query_cache_limit: MySQL能够缓存的最大查询结果；如果某查询的结果大于此值，则不会被缓存；
query_cache_min_res_unit: 查询缓存中分配内存的最小单位；计算公式：（query_cache_size-Qcache_free_memory）/Qcache_queries_in_cache
query_cache_size: 查询缓存的总体可用空间；其必须为1024的倍数；
query_cache_type: 有3种植可选，ON， OFF， DEMAND
query_cache_wlock_invalidate：当其它会话锁定此次查询用到资源时，是否不能再从缓存中返回数据；
与查询缓存相关的状态变量：SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 16759656 |
| Qcache_hits | 16 |
| Qcache_inserts | 71 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 57 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+----------+
Qcache_hits/Com_select = 缓存命中率
衡量缓存是否足够有效的另一种思路：Qcache_hits/Qcache_inserts，如果此比值大于3:1, 说明缓存也是有效的；如果高于10:1，相当理想；
缓存优化的思路：
1、批量写入比单次写入对缓存的影响要小得多；
2、缓存空间不宜过大，大量缓存的同时失效会导致MySQL假死；
3、必要时，使用SQL_CACHE或SQL_NO_CACHE手动控制缓存；
4、对写密集型的应用场景，禁用缓存反而能提高性能；
碎片整理：FLUSH QUERY_CACHE
清空缓存：RESET QUERY_CACHE
mysql中的索引：
SHOW ENGINES; 显示支持的存储引擎
基本法则：索引应该构建在被用作查询条件的字段上
约束：PRIMARY KEY, UNIQUE KEY, FOREIGN KEY
索引：特殊的数据结构，用于快速找到数据记录（行），mysql中的key（键）可用作索引；并非所有索引都是键；
索引可以创建在一个字段，也可是多个字段上：简单索引、组合索引
例子：索引(姓名,性别)
where name like 'tom%'
where gender = 'female'
where familyname = 'tom' and gender = 'female'
索引类型：B-Tree (B+ Tree) (左前缀), hash (key-value), R-Tree, FullText
B树索引：
B-Tree索引的适用场景:如下：
全值匹配：比较操作符 =, <=>
左前缀匹配：LIKE 'tom%'
列前缀匹配
匹配范围值：
组合索引类型中，精确匹配前一列，并范围匹配后一列；
只访问索引的查询：覆盖索引，从索引就可以直接得到最终结果；
哈希索引适用场景：
哈希索引只包含哈希值和行指针；不是按照索引值顺序存储，无法用于排序；不支持部分索引匹配查找；
哈希索引只支持等值比较查询，包含=, IN(), <=>
空间索引（R-Tree）：
全文索引（FULLTEXT）：在文本中查找关键词
索引的优点：
大大减少服务器需要扫描的数据量；
索引可以帮助服务器尽量避免进行排序及使用临时表；
索引可以将随机I/O转换为顺序I/O；
索引的使用策略：
1、要使用独立的列：索引列不是表达式的一部分；
SELECT Name FROM students WHERE Age + 2 > 32;
2、前缀索引
索引选择性：单独的索引项与数据表中的记录的总数的比值；取值范围：1/n-1;books, book
3、多列索引
查询条件多用AND，则建议使用多列索引；
查询条件多用OR，建议使用多个简单索引；
4、合适的次序：将选择性高的列写在最左侧；(Name, Gender)
5、聚簇索引：
好处：数据访问更快；
缺点：更新聚簇索引列的代价很高；
6、覆盖索引
SELECT Name FROM students WHERE Name LIKE 'tom%';
7、避免冗余索引
name, (name,gender)
EXPLAIN生成SQL 分析结果，可用于观察查询优化器如何查询：
EXPLAIN SELECT Name,Age FROM students WHERE Name LIKE 's%' or Age > 28\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: students
type: index_merge
possible_keys: Name,Age
key: Name,Age
key_len: 152,1
ref: NULL
rows: 7
Extra: Using sort_union(Name,Age); Using where
id: SELECT语句的标识符
select_type有如下几种情况
SIMPLE
PRIMARY
UNION
UNION RESULT：UNION的执行结果
SUBQUERY：子查询
DERIVED：用于FROM子句中的子查询；
table:
查询语句所关系到的表的名字；
type: mysql访问到目标记录使用的方法
system: 表中仅有一行；
const: 表中至多有一行匹配；一般只有用于PRIMARY KEY或UNIQUE KEY（NOT NULL）索引时，此种结果才会出现；
eq_ref: 类似于const，表中至多有一个匹配到的行
ref:
fulltext: 使用全文索引访问到目标记录
ref_or_null:
index_merge:
unique_subquery: 通常出现于IN子查询中
index_subquery: 类似上一个，
range: 带有范围限制的索引；
index: 全索引扫描
ALL：全表扫描
Extra:
using where:
using index:
using index for group by:
using filesort: 使用外部索引排序
mysql存储引擎：
创建表的时候可以指定存储引擎，存储引擎可以称之为表类型
mysql> SHOW TABLE STATUS LIKE 'stu'\G 查看stu表的状态
*************************** 1. row ***************************
Name: stu
Engine: InnoDB InnoDB引擎
Version: 10
Row_format: Dynamic
Rows: 6
Avg_row_length: 2730
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 8
Create_time: 2019-04-04 10:29:21
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
InnoDB存储引擎：（MariaDB使用的是XtraDB）
存储引擎特性：
InnoDB: 设计用于处理大量的短期事务；支持自动的崩溃恢复；
Table Space: 表空间可由一个或多个物理文件组成
支持两种使用风格：
1、将所有innodb表的数据和索引放置同一个表空间中；
默认：数据目录下ibdata1，ibdata2， ibdata3，......
2、每个表使用自己专用的表空间；
表结构定义：tb_name.frm
数据和索引：tb_name.ibd
InnoDB基于聚簇索引建立的：基于主键索引查询时，性能较好；
辅助索引中必须包含主键索引；因此，为了节约空间，尽可能使用较小的主键索引；
InnoDB支持自适应hash索引；
支持事务；
支持行级锁；
支持MVCC；
支持热备份
MyISAM存储引擎:
支持全文索引、压缩、空间函数；
不支持事务、行级锁
崩溃后无法安全恢复
支持延迟更新索引键（delayed_key_write）
不支持热备份
myisam表存储格式：每张表都有三个文件（位于数据库目录下）
tb_name.frm: 表格式
tb_name.MYD: 数据
tb_name.MYI: 索引
其它存储引擎：
Memory: 早期叫HEAP表，数据保存于内存中
显式支持hash索引；无法持久存储数据
CSV: 文本文件，字段以逗号分隔；
不支持索引；常用于数据交换的场景
MRG_MYISAM： MyISAM的变种，将多个MyISAM表合并表示为一个虚拟表；
Federated：访问其它MySQL服务上的数据的代理
MariaDB上用的FederatedX
Blackhole: 没有任何存储机制，所以会丢弃所有的插入的数据；
PERFORMANCE_SCHEMA：伪存储引擎，mysql性能相关
NDB：Cluster
XtraDB: InnoDB的改进版，MarIaDB使用的存储引擎之一
Aria: 改进的MyISAM
OQGraph: 支持图操作
SphinxSE: 为sphinx全文搜索引擎提供了SQL接口；
Spider: 可以将数据切分成不同的分区，较透明实现分片功能；
PBXT：支持ACID和MVCC
TokuDB: 支持使用分形树的索引结构，适用存储大数据
MYSQL的并发控制机制：通过锁控制
锁：读锁和写锁
读锁：也叫共享锁，一个资源可以被附加多个读锁
写锁：也叫独占锁，一个资源被某个进程附加写锁后，其他进程就无法在附加读锁和写锁
锁粒度：包括表级别锁和行级别锁，锁粒度并非越细越好，锁的管理同样需要消耗时间和资源。每个存储引擎都实现了各自的锁策略和锁粒度，锁的操作一般由存储引擎自己管理。
同样的，mysql在服务器也实现了表级别锁，此锁用户可以显示请求,help lock; 查看帮助
LOCK TABLES
tbl_name [[AS] alias] lock_type
[, tbl_name [[AS] alias] lock_type] ...
lock_type: {
READ [LOCAL]
| [LOW_PRIORITY] WRITE
}
UNLOCK TABLES; 释放锁
上锁的另外一种方法：FLUSH命令：
FLUSH TABLES tb_name[, ....] WITH READ LOCK;
UNLOCK TABLES; 释放锁
InnoDB存储引擎也支持另外一种显式锁(只锁定挑选出的行)：用select语句来上锁
SELECT ... LOCK IN SHARE MODE; 读锁
SELECT ... FOR UPDATE; 写锁
UNLOCK TABLES; 释放锁
MySQL事务机制：
mysql事务：一组原子性的sql查询，事务有如下4中特性：
A：atomicity，原子性，这个事务中的所有操作要么全部完成，要么失败全部回滚至操作之前的状态
C：consistency，一致性，数据库总是从一个一致性状态转移到另一个一致性状态
I：isolation，隔离性 一个事务所做的操作在提交之前，不能为其他事务所见
隔离级别：4个级别
D：durability，持久性，一旦事务提交，其所作的修改会永久保存于数据库中
事务的流程如下：
启动事务的命令：start transaction; 之后是一系列的业务sql语句；然后是使用命令 commit; 提交，或者是失败则使用命令 rollback; 回滚到开启事务之前的状态。
建议在启用事务之前关闭数据库的自动提交，通过设置autocommit变量为off或者0来关闭自动提交 SET SESSION autocommit=off;
事务还支持保存点（SAVEPOINT）：保存点可以理解为是在事务执行过程中插入的铆钉，保存点可以依据业务需求插入，回滚的时候可以回滚到指定的保存点，这样一来，就不用每次都回滚到事务开启之前。（help savepoint; 查看帮助信息）
SAVEPOINT identifier 设置一个保存点
ROLLBACK [WORK] TO [SAVEPOINT] identifier 回滚到指定的保存点
RELEASE SAVEPOINT identifier 撤销保存点
事务的4大隔离级别：演示时必须至少两个客户端连接mysqld服务，两边都设置相同的事务级别，然后两边都启动事务：
READ-UNCOMMITTED（读未提交）：最低隔离级别，会产生“脏读”；就是可以读取别人未提交的数据
READ-COMMITTED（读提交）：“这个值会导致不可重复读”；别人提交过的数据，我们才能读取到，别人未提交的数据，我们是读取不到的，所以叫做读提交。
REPEATABLE-READ（可重读）：“这个值会产生幻读”；在我们的事务过程中，我们读取到的数据从始至终都是事务启动前的状态，不管对方提交已否。
SERIALIZABLE（可串行化）：加锁读，事务只能串行，这是最高隔离级别
tx_isolation 是服务器变量，其值就是上面之一，mysql通过设置改变量来设置隔离级别，默认是REPEATABLE-READ
SHOW GLOBAL VARIABLES LIKE 'tx_isolation'; 查看事务级别
SHOW SESSION VARIABLES LIKE 'tx_isolation'; 查看当前事务级别
SELECT @@global.tx_isolation; 查看事务级别
SELECT @@session.tx_isolation; 查看事务级别
SET tx_isolation=READ-UNCOMMITTED; 设置事务级别为READ-UNCOMMITTED
MVCC: 多版本并发控制：通过保存数据在某个时间的快照实现。无论事务执行多长时间，其看到的数据都是一致的。MVCC仅在第二、第三隔离级别下有效。
事务日志：事务日志的写入类型为"追加", 所以，事务日志是顺序I/O， 如此一来，这就将随机I/O转换为顺序I/O，以提升事务操作效率；事务日志也称为Write-Ahead Logging（预写日志）. 事务提交之后，所有的事务过程中的操作都会写入事务日志中。
事务日志文件：ib_logfile0, ib_logfile1
事务日志相关的变量：
SHOW GLOBAL VARIABLES LIKE 'innodb_log%';
+-----------------------------+-----------+
| Variable_name | Value |
+-----------------------------+-----------+
| innodb_log_buffer_size | 8388608 |
| innodb_log_checksums | ON |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 268435456 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | .\ |
| innodb_log_write_ahead_size | 8192 |
+-----------------------------+-----------+
MySQL死锁问题：
比如：
MySQL的日志：
评论
发表评论