一、MySQL的并发控制
1、MySQL的并发控制在 服务器层和存储引擎层完成
锁：lock
读锁：共享锁，可以几个用户同事读取,但不能写
写锁：独占锁，
锁粒度：锁表时锁表的范围（比如一个用户读前三行，一个用户写入最后一行）
表级锁：innodb表级锁
行级锁：myisa行级锁（锁控制复杂）
死锁：A请求B，B请求A (自己能协商解锁)
锁分类：
隐式锁：由存储引擎自动完成
显式锁：用户可手动施加锁，表级锁
手动加锁：服务器级别实现
LOCK TABLES tb1 {READ|WRITE},……
UNLOCK TABLES;释放锁
例：>use hellodb;
>LOCK TABLES students READ;
FLUSH TABLES WITH READ LOCK; 关闭表并全局执行读锁
UNLOCK TABLES;
InnoDB存储引擎也支持另外一种显式锁（只锁定挑选的行）
SELECT... LOCK IN SHARE MODE;
SELECT...FOR UPDATE;
2、事务：ACID
一个事务就是一个执行单元：多个sql语句，要么都执行，要么都不执行。
事务：提交，回滚
A：atomicity，原子性
C：consistency，一致性
I：isolation，隔离性
隔离级别：4个级别
D：durability，持久性
事务的隔离级别：
READ-UNCOMMITTED（读未提交）：最低隔离级别，会产生“脏读”；
READ-COMMTTED（读提交）：“不可重复读”
REPEATABLE-READ（可重读）：“幻读”
SERIALIZABILE（可串行化）：使用加锁读
SELECT @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ |
+-----------------------+
启动事务：
START TRANSACTION
提交事务：COMMIT
回滚事务：ROLLBACK
SAVEPOINT
SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier
MySQL的自动提交功能：
SELECT @@GLOBAL.autocommit;
+---------------------+
| @@GLOBAL.autocommit |
+---------------------+
|1 |
+---------------------+
MVCC: 通过保存数据在某个时间的快照实现。无论事务执行多长时间，其看到的数据都是一致的。
MVCC仅在第二、第三隔离级别下有效；
事务日志：将随机I/O转换为顺序I/O，以提升事务操作效率；事务日志也称为Write-Ahead Logging
总结：锁、事务、MVCC、事务日志
二、MySQL存储引擎介绍
三、MySQL用户和权限管理
1、最小化权限集合
用户@主机：表示此用户帐号可从@HOST范围内的某主机对此MySQL建立连接；
@HOST: 表示客户端
%:任意长度的任意字符串
_:单个字符
172.16.0.0/16, 172.16.%.%
创建用户账号：
CREATE USER 'username'@'host' IDENTIFIED BY 'your_password', ...
如：CREATE USER tuser@localhost IDENTIFIED BY 'mageedu';
删除用户账号：
DRP USER 'username'@'host';
查看用户获得的权限：
SHOW GRANTS FOR 'username'@'host';
如：SHOW GRANTS FOR tuser@localhost; 仅能链入数据库使用其基本功能。
2、MySQL的权限类别：库级别、表级别、字段级别、管理类、程序类
管理类：
CREATE TEMPORARY TABLES ：创建临时表
CREATE USER：创建用户
FILE：保存文件，把数据库数据保存成文件
SUPER：不便归类的
SHOW DATABASES：显示数据库
RELOAD：重置日志及缓存相关
SHUTDOWN：关闭MySQL服务
REPLICATION SLAVE：复制架构中的从节点
REPLICATION CLIENT：向主服务器发起链接请求，获取主服务器状态信息权限
LOCK TABLES：施加锁的权限
PROCESS：进程相关权限
storage routine: 存储例程
storage procedure
storage function
库和表级别：（DDL语句）
ALTER：修改库或表
ALTER ROUTINE ：存储例程
CREATE
CREATE ROUTINE
CREATE VIEW ：创建视图
DROP ：删除表、库、视图
EXECUTE
INDEX ：创建或删除索引
GRANT OPTION: 是否可以转授权限的权限；
SHOW VIEW ：显示视图
数据操作（表级别--DML语句）：
SELECT 查询数据权限
INSERT 插入数据权限
UPDATE 修改数据权限
DELETE 删除数据权限
字段级别：
SELECT(col1,...)
UPDATE(col1,...)
INSERT(col1,...)
所有权限：
ALL [PRIVILEGES]
mysql中的授权相关的表：（MySQL自行维护，建议不要修改）
db、host、user
columns_priv、tables_priv, procs_priv
GRANT（授权）, REVOKE（取消授权）
GRANT priv1, priv2, ... ON [TABLE|FUNCTION|PROCEDURE] db_name.tb_name|routine TO 'username'@'host' [IDENTIFIED BY 'password'] [REQUIRE ssl_option] [WITH with_option]
with_option 参数如下:
GRANT OPTION 自己的权限转给别人
| MAX_QUERIES_PER_HOUR count 每小时发起最多的查询次数
| MAX_UPDATES_PER_HOUR count 每小时发起的更新请求数
| MAX_CONNECTIONS_PER_HOUR count 每小时向服务器发起的链接请求
| MAX_USER_CONNECTIONS count 最大用户连接数
REVOKE priv1,priv2,... ON TABLE|FUNCTION|PROCEDURE] db_name.tb_name|routine FROM 'username'@'host',...;
例如：GRANT SELECT ON hellodb.students TO 'tuser'@'localhost';
show databases; use hellodb, show tables;select * from students;
insert into students (name,age,gender) values('zhang',22,'f')
练习：
1、授予testuser能够通过172.16.0.0/16网络内的任意主机访问当前mysql服务器的权限
2、让此用户能够创建及删除testdb数据库，及库中的表；
3、让此用户能够在testdb库中的t1表中执行查询、删除、更新和插入操作；
4、让此用户能够在testdb库上执行创建和删除索引；
5、让此用户能够在testdb.t2表上查询id和name字段，并允许其将此权限转授予其他用户；
GRANT SELECT(id,name) ON testdb.t2 TO testuser@'172.16.%.%' WITH GRANT OPTION;
3、用户重命名：
RENAME USER old_user TO new_user;
四、MySQL查询缓存管理及数据类型选择
缓存：hit（命中），miss（未命中）:衡量缓存的有效性：命中率, hit/(hit+miss)
query_cache_type
on、off、demand（手动指明该语句是否缓存sql_cache | sql_no_cache）
query cache:
key:查询语句的hash码
value：查询语句的执行结果
什么样的语句不会缓存？
查询语句中有不确定数据时不会缓存。
查询中包含用户自定义的函数、存储函数、用户变量、临时表、包含权限的语句不会缓存。
缓存什么场景下会比较有效?
需要消耗大量资源的查询，缓存比较有效
与缓存功能相关的服务器变量：
SHOW GLOBAL VARIABLES LIKE 'query_cache%';显示缓存相关变量
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| query_cache_limit| 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 16777216 |
| query_cache_strip_comments | OFF |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+----------+
query_cache_limit：单条语句查询结果大于该值不与缓存，可修改变量值。
query_cache_min_res_unit：查询缓存时内存中分配最小单位。
query_cache_size：缓存最大空间，该数组必须为1024倍数，0为没有开启缓存。
query_cache_type：是否开启手动缓存功能
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 |
+-------------------------+----------+
show global status like 'Qcache%';：为mysql运行过程中的统计数据不能修改。
Qcache_free_blocks ：查询缓存中空闲块
Qcache_free_memory ：所有未分配的空间
Qcache_hits：查询命中次数
Qcache_inserts：向缓存中插入的次数
Qcache_lowmem_prunes ：缓存满后清除缓存中数据次数
Qcache_not_cached：没能被缓存的次数
Qcache_queries_in_cache：还留在缓存中的数据，0表示都流失了。
Qcache_total_blocks：共分配缓存中的块
show global status like 'Com_select';：现实共执行select的次数
缓存优化的思路：
批量写入比单次写入对缓存的影响要小得多；
缓存空间不宜过大，大量缓存的同时失效会导致MySQL假死；
必要时，使用SQL_CACHE或SQL_NO_CACHE手动控制缓存；
对写密集型的应用场景，禁用缓存反而能提高性能；
碎片整理：flush query cache;
清空缓存：reset query cache;
