【mysql基础】09、MySQL管理
一、MySQL管理mysql协议支持文本和二进制，默认使用二进制（二进制协议更高效）
mysql的发送默认为明文，可以使用ssl加密数据
explain：分析工具，可以分析语句的执行流程，缓存是否命中
query cache：缓存，只保存select语句的查询结果，可以手动管理是否开启缓存功能并设置缓存级别
mysql的并发访问控制：基于锁来实现
1、MySQL锁
执行操作时施加的锁的模式：
读锁：用户在读的时候施加的锁，为防止别人修改，但是用户可以读，还被称为共享锁
写锁：独占锁，排它锁。其他用户不能读，不能写
锁粒度：
表锁：table lock
锁定整张表
行锁：row lock
只锁定需要的行
粒度越小，开销越大，但并发性越好：
粒度越大，开销越小，但并发性越差；
锁策略，在开销和并发性能之间寻找一个平衡点
锁的实现位置：
MySQL锁：可以手动使用，可以使用显示锁
存储引擎锁：自动进行的（隐式锁），
显示锁：手动施加
语法格式：
LOCK TABLES
tbl_name lock_type
[, tbl_name lock_type] ...
锁的类型：READ | WRITE
释放所有锁：unlock tables；
MariaDB > LOCK TABLES ZY READ;
Query OK, 0 rows affected (0.00 sec)
# 此时对该表进行写操作将会阻塞，直到该表的锁释放后才能执行成功
MariaDB > UNLOCK TABLES;
Query OK, 0 rows affected (0.02 sec)
InnoDB存储引擎也支持另外一种显示锁（锁定挑选出的部分行,行级锁）：
select .... lock in share mode；# 共享锁，读锁，锁定查询结果的行，只是在该查询语句执行时，施加锁，执行结束后，锁就自动解除了。
select .... for update； # 排它锁，写锁，
不建议手动施加锁，因为存储引擎会自动施加锁，而且性能更好，但是做备份时要手动对表施加读锁
2、事务：Transaction
事务就是一组原子性的查询语句（和数据库对象创建无关），也即将多个查询当作一个独立的工作单元
ACID测试：能够满足ACID测试就表示其支持事务，或兼容事务
A：Atomicity，原子性，都执行或者都不执行
C：Consistency，一致性，从一个一致性状态转到另外一个一致性状态
I：Isolaction，隔离性（导致并发性能降低），一个事务的所有修改操作在提交前对其他事务时不可见的
D: Durability, 持久性，一旦事务得到提交，其所做的修改会永久有效
隔离级别：
READ_UNCOMMITTEND（读未提交）：脏读/不可重复读（可以是在自己启动的事务内，二次查看的数据不一样）/幻读，用的很少
READ_COMMITTEND（读提交）：一个事务开始时，只能读已提交的事务修改；解决了脏读
REPEATABLE_READ (可重读)：mysql的默认隔离级别，在自己的事务内，多此读取的数据一定是一样的；解决了重复读
SERIALIZABLE（可串行化）：强制事务的串行执行，避免了幻读；性能极低
启动事务：
START TRANSACTION;
事务提交：
COMMIT;
事务回滚：
ROLLBACK; # 就放弃了这次事务，回滚所有操作
savepoint：保存点，控制回滚的位置
SAVEPOINT>
ROLLBACK TO >
注意：
如果没有显式启动事务，每个语句都会当作一个默认的事务，其执行完成会被自动提交，显示启动事务，不会自动提交.
select @@global.autocommit；
set global autocommit = 0；
cimmication：注意关闭自动提交，请记得手动启动事务，手动提交；感觉这里有点矛盾，不是显示启动的事务，会被系统默认的当成一个事务，那怎么提交被自动当做的事务的语句？
查看mysql的事务隔离级别：
show global variables like '%isolation%';
select @@global.tx_isolation;
建议：对事务要求不特别严格的场景下，可以使用读提交（大多数数据库的默认事务隔离级别）
MVCC：多版本并发控制 # 对于可重读实现的机制
每个事务启动时，InnoDB会为每个启动的事务提供一个当下时刻的快照，为实现此功能，InnoDB会为每个表提供两隐藏的字段，一个用于保存行的创建是的系统版本号（system version number），一个用于保存行的失效系统版本号；
每启动一个事务，InnoDB就会创建一个当前时间的快照，并且系统版本号加1，而后在这个事务中读取的数据一定来自之前或当下的系统版本，从而使得读取的数据都是过去的数据，从而实现REPEATABLE RED。
MVCC只在两个隔离级别下有效：read committed和repeatable read
二、MySQL各存储引擎对比
1、查看当前所使用的存储引擎
mysql存储引擎：存储引擎也通常被称作“表类型”，是表级别的概念
show engines;
show table status；
SHOW TABLE STATUS [{FROM | IN} db_name] ；
MariaDB [(none)]> SHOW TABLE STATUS FROM mydb WHERE NAME="ZY"\G
*************************** 1. row ***************************
Name: ZY
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 4
Avg_row_length: 4096
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2017-02-18 18:48:43
Update_time: NULL
Check_time: NULL
Collation: gbk_chinese_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
MariaDB [(none)]>
Name 表名
Engine 存储引擎
Version 版本
Row_format: 行格式
{DEFAULT|DYNAMIC|FIXED|COMMPRESSED|REDUNDANT|COMPACT}
Rows 表中的行数(InnoDB表中数值可能不准确)
Avg_row_length 平均每行包含的字节数
Data_length 表中数据总体大小，单位为字节
Max_data_length 表能够占用的最大空间，单位为字节，0表示么有上限
Index_length 索引的大小，单位为字节
Data_free 对于MyISAM表，表示已经分配但尚未使用的空间，其中包含此前删除行之后腾出来的空间
Auto_increment 下一个AUTO_INCREMENT的值
Create_time 表的创建时间
Update_time 表数据的最后一次修改时间
Check_time 使用CHECK TABLE或myisamchk最近一次检查表的时间
Collation 排序规则
Checksum 如果启动校验功能，则为表的checksum（校验和）
Create_options 创建表时指定使用的其他选项
Comment 表的注释信息
2、InnoDB和MyISM存储引擎的表数据文件
InnoDB
两种格式：
1.innodb_file_per_table=OFF,即是用共享表空间
每个表一个独有的格式定义文件：tb_name.frm
还有一个默认位于数据目录下的共享的表空间文件：ibdata#
2.innodb_file_per_table=ON，即是用独立表空间
每个表在数据库目录下存储两个文件
tb_name.frm
tb_name.ibd
MyISAM：
每个表都在数据库目录下存储三个文件
tb_name.frm
tb_name.MYD
tb_name.MYI
表空间：table space
由InnoDB管理的特有格式数据文件，内部可同时存储数据和索引
如何修改默认存储引擎：通过default_storage_engine服务变量实现
3、各存储引擎的特性
InnoDB：
设计目标：处理大量的短期事务
支持事务，有事务日志文件：# 将随机IO改为顺序IO，并且大小固定不会改变
ib_logfile0
ib_logfile1
支持MVCC(多版本并发控制)
支持外键约束
支持聚簇索引：
聚簇索引之外的其他索引，通常称之为辅助索引
支持使用辅助索引
支持自适应hash索引（还有B树索引，R树索引，全文索引）
支持行级锁：间隙锁（行和行之间间隙锁）
支持热备份，表级别热备份依赖于表独立空间文件
MariaDB中虽然仍然叫InnoDB其实是percona的XtraDB是对InnoDB的改进增强版
MyISAM：
全文索引：
支持表压缩存放（数据不能修改了）：做数据仓库，能节约存储空间并提升性能
支持空间索引：
表级锁：
延迟更新索引：降低IO压力
不支持事务、外键和行级锁，崩溃后无法安全可靠恢复数据
使用场景：只读数据，表较小，能够忍受崩溃后的修复操作和数据丢失
ARCHIVE：
仅支持INSERT和SELECT，支持很好压缩功能
应用于存储日志信息，或其他按照时间序列实现的数据采集类的应用
不支持事务，不能很好的支持索引
CSV：
将数据存储为CSV格式（文本文件），不支持索引，仅使用与数据交换场景
BLACKHOLE：
没有存储机制，任何发往次引擎的数据都会丢弃，其会记录二进制日志，因此，常用于多级复制架构中作中转服务器
MEMORY：
保存数据在内存中，内存表；常用于保存中间数据，如周期性的聚合数据等，也用于实现临时表
支持hash索引，使用表级锁，不支持BLOB和TEXT数据类型
MRG_MYISAM:
是MYISAM的一个变种，能够将多个MyISAM表合并成一个虚表
NDB：
是MySQL CLUSTER中专用的存储引擎、
第三方的存储引擎：
XtraDB：增强版的InnoDB，有Percona提供
编译安装时，下载XtraDB的源码替代MySQL存储引擎中的InnoDB的源码
PBXT：MariaDB自带此存储引擎
支持引擎级别的复制、外键约束，对SSD磁盘提供适当支持
支持事务、MVCC
TokuDB：使用Fractal Trees索引，适用存储大数据，拥有很好的压缩比，已经被引入MariaDB
列式数据存储引擎：
Infobright：目前较有名的列式引擎，适用于海量数据存储场景，如PB级别，专为数据分析和数据仓库设计，mysql需要定制才能使用该存储引擎
InfiniDB
MonetDB
LucidDB
开源社区存储引擎：
Aria：前身为Maria，是增强版的MyISAM（支持崩溃后安全恢复，支持数据缓存）
Groona：全文索引引擎，常用于搜索引擎
Mroonga：是基于Groona的二次开发版
OQGraph：由open query研发，支持图（网状）结构（其它结构有：逻辑结构，顺序结构，树型结构）的存储引擎
SphinxSE：为Sphinx全文搜索服务器提供了SQL接口
Spider：能将数据切分成不同的分片，比较高效透明的实现了分片（shared），并支持在分片上支持并行查询
如何选择适合的存储引擎？
是否需要事务
备份类型的支持
崩溃后的恢复
特有的特性
索引类型：
聚簇索引
辅助索引
B树索引
R树索引
hash索引
全文索引
空间索引
三、用户及权限管理
1、用户帐号管理
创建用户帐号：
CREATE USER username@hostname
'password'] ...;# 不指定密码则使用空密码
主机可以使用通配符%,_：'username'@'192.168.1__.2__'
查看用户的授权：
新创建的用户如果没授权，默认只有连入和有限的查看权限，想查看用户有哪些权限可以使用命令：SHOW GRANTS FOR 'username'@'host';
修改用户帐号：
RENAME USER old_user TO new_user [, old_user TO new_user] ...
修改用户密码：
SET PASSWORD =
{
PASSWORD('cleartext password')
| OLD_PASSWORD('cleartext password')
| 'encrypted password'
}
删除用户：
DROP USER user [, user] ...
MariaDB [(none)]> create user "test"@"192.168.10.%" identified by '123456';
Query OK, 0 rows affected (0.03 sec)
MariaDB [(none)]> select user,host,password from mysql.user;
+------+--------------+-------------------------------------------+
| user | host | password |
+------+--------------+-------------------------------------------+
| root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | node5 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | 127.0.0.1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | ::1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| test | 192.168.10.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+--------------+-------------------------------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> show grants for 'test'@'192.168.10.%';
+----------------------------------------------------------------------------------------------------------------+
| Grants for test@192.168.10.% |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'192.168.10.%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)
# USAGE 是默认的权限，只能连接
MariaDB [(none)]> rename user 'test'@'192.168.10.%' to 'xj'@'192.168.10.%';
Query OK, 0 rows affected (0.00 sec)
# 自己的权限没有权限修改自己的名字
MariaDB [(none)]> set password for "xj"@"192.168.10.%"= password("12");
Query OK, 0 rows affected (0.00 sec)
2、权限管理
1）Mysql的权限类型
库级别
表级别
字段级别
管理类
程序类
管理类权限：
create temporary tables 创建临时表
create user
file 允许用户读或者写某些文件
lock tables 添加显式锁
process 查看用户的线程列表
reload 相当于可以执行flush和reset
replication client 查询有哪些复制客户端
replication slave 赋予用户复制权限
show databases
shutdown 关闭服务器
super 其它的不便归类的管理命令，高级别管理操作比如：授权，复制
数据库访问权限：
alter
alter routine 存储历程
create
create routine 存储过程，存储函数
create view
delete
drop
execute
grant option 将自己的权限复制给别的用户
index 索引
show view 查看视图是如何创建的
数据操作类权限（表级别）：
select
insert
update
delete
字段级别：
select（col1,....） 授予操作某字段的权限
update（col1,....）
insert（col1,....）
所有权限：ALL
用户授权：
GRANT priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON priv_level
TO username@hostname ,...
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 FOR 'username'@'host';
取消用户的授权：
REVOKE priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON priv_level
FROM user [, user] ...
REVOKE ALL PRIVILEGES, GRANT OPTION
FROM user [, user] ...
REVOKE PROXY ON user
FROM user [, user] ...
几个跟用户授权相关的表：
db：库级别权限
host：主机级别权限，已废弃
tables_priv：表级别权限
colomns_priv：字段级别的权限
procs_priv：存储过程和存储函数相关的权限
proxies_priv：代理用户权限
四、mysql查询缓存
1、查询缓存
用于保存MySQL查询语句返回的完整结果，被命中时，MySQL会立即返回结果，省去解析、优化和执行等操作
如何检查缓存？
MySQL保存结果于缓存中：
把select语句本身做hash计算，计算的结果作为key，查询结果作为value
什么样的语句不会被缓存？
查询语句中有一些不确定数据时，不会缓存；例如now()，current_time();
一般来说，如果查询中包含用户自定义函数、存储函数、用户变量、临时表、mysql库中系统表、或者任何包含权限的表，一般都不会缓存
缓存会带来额外开销：
1、每个查询都会先检查是否命中
2、查询结果要先缓存
缓存置换策略：最近最多使用
2、和缓存相关的配置
MariaDB > 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 |
+------------------------------+----------+
6 rows in set (0.00 sec)
query_cache_type：查询缓存类型；是否开启缓存功能，开启方式有三种{ON|OFF|DEMAND}
DEMAND（按需缓存）：意味着select语句明确使用sql_cache选项才会缓存
query_cache_size：缓存总大小；单位为字节，大小必须为1024的整数倍。mysql启动时，会一次分配并立即初始化这里指定大小的内存空间；这意味着，如果修改此大小，会清空缓存并重新初始化的
query_cache_min_res_unit：存储缓存的最小内存块；等于（总空间 - 空闲空间）% 缓存条目个数
(query_cache_size - Qcache_free_memory)/Qcache_queries_in_cache 能够获得一个理想的值
query_cache_limit：单个缓存对象的最大值；超出时则不予缓存；手动使用SQL_NO_CACHE可以人为的避免尝试缓存返回超出此参数限定值得语句
query_cache_wlock_invalidate：如果某个表被其他用户连接锁住了，是否仍然从缓存中返回结果，OFF表示返回
3、如何判断命令率
命中率分为次数和字节命中率
查看系统缓存使用的统计信息：
MariaDB > show global status like 'Qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 2 | # 空闲块数
| Qcache_free_memory | 16753000 | # 空闲空间，未初始化的空间
| Qcache_hits | 2 | # 缓存命中次数
| Qcache_inserts | 19 | # 向缓存空间缓存数据的次数
| Qcache_lowmem_prunes | 0 | # 修剪内存的次数（缓存空间占满了）
| Qcache_not_cached | 69 | # 未缓存的查询个数
| Qcache_queries_in_cache | 5 | # 缓存中缓存的查询个数
| Qcache_total_blocks | 15 | # 缓存总块数
+-------------------------+----------+
8 rows in set (0.00 sec)
缓存碎片整理：flush query cache；
清空缓存： reset query cache；
计算命中率：
show global status where Vaiable_name='Qcache_hits' OR Variable_name='Com_Select(查询次数)';
Qcache_hits/(Qcache_hits + Com_Select)
查询命中时，不会增加查询次数，所以这里用Qcache_hits + Com_Select，仅能计算命中次数，不能计算命中的字节，仅作为参考
也可以参考另外一个指标，命中和写入的比率，即Qcache_hits/Qcache_inserts的值，此比值如果能大于3:1，则表明缓存也是有效的，能达到10：1，为比较理想的情况。
4、缓存优化思路
1.批量写入，而非多次单个写入
2.缓存空间不宜过大，因为大量缓存同时失效时会导致服务器假死
3.必要时，使用sql_cache和sql_no_cache手动控制缓存。
4.对写密集型的应用场景来说，禁用缓存反而提高性能。
页:
[1]