MySQL高级

linux安装MySQL前准备

需要准备两台centos7虚拟机，可安装一台后通过克隆一台，克隆后需要修改如下方面：
1、mac地址：网络适配器 - 高级 - 下方mac地址点击生成即可(启动前修改)
启动后修改以下三项，通过终端命令方式修改:使用root用户修改
2、主机名：vim /etc/hostname 修改后重启虚拟机reboot命令
3、ip地址：动态ip无需修改，静态ip修改如下文件：
修改目录/etc/sysconfig/network-scripts/ifcfg-ens33
4、UUID: 修改目录/etc/sysconfig/network-scripts/ifcfg-ens33
修改后重启网络即可：systemctl restart network

字符集级别

MySQL数据库字符集级别从高至低分别为服务器级别->数据库级别->表级别->列级别，若不单独指定字符集则低级别的会默认依赖高级别的，当然也可以对任意级别的字符集单独设置。

主要目录结构

1、MySQL数据库文件存放路径：/var/lib/mysql
可通过命令查看数据目录：show variables like ‘%datadir%’
数据库中存储的方式：
（1）Innodb引擎：5.5以后开始使用
A、5.7版本中每个数据库文件下包含db.opt文件（该文件存放该库相关配置如字符集信息）、xxx.frm文件（该文件是存储表结构每张表都有一个）、xxx.ibd文件（该文件存储表中的数据每张表都有一个）;
B、8.0版本中每个数据库文件下只有xxx.ibd文件（该文件存储表的结果及数据每张表都有一个）;
查看文件可通过oracle自带的ibd2sdi --dump-file=***.txt ***.ibd命令将ibd文件转为txt格式后查看内容
（2）MyISAM引擎
A、5.7版本中每个数据库文件下每张表对应3个文件包含xxx.frm文件（该文件是存储表结构每张表都有一个）、xxx.MYD（表中数据）、xxx.MYI（索引信息）
B、8.0版本中每个数据库文件对应3个文件包含xxx.sdi文件（该文件是存储表结构每张表都有一个）、xxx.MYD（表中数据）、xxx.MYI（索引信息）
相对视图来讲就只有frm文件存储结构，因为其是虚拟表不存储数据
2、命令相关目录：/usr/bin/和/usr/sbin/
3、配置文件目录：/etc/my.cnf和/usr/share/mysql-8.0/

用户管理

用户信息存储在mysql.user表中。
创建用户：create user 用户名identified by ‘用户密码’;
修改用户：直接修改user表中数据即可，但一般不会操作
删除用户：drop user 用户名，如果存在名字相同用户但是host不同可通过命令删除：drop user ‘用户名’@’host’ ;不加host默认是删除host=%的用户。或者使用delete删除user表中数据，但是不推荐因为会有残留信息保留。
修改密码：
1、修改当前用户密码（修改当前登录用户密码）
（1）使用alter
ALTER USER USER() IDENTIFIED BY ‘新密码’;
（2）使用set
SET PASSWORD=‘新密码’;
2、修改其他用户密码
（1）使用alter
ALTER USER ‘用户名’@’HOST’ IDENTIFIED BY ‘新密码’;
（2）使用set
SET PASSWORD FOR ‘用户名’@’HOST’=‘新密码’;
同时还可以使用update直接修改表中数据，但是密码存储是经过加密的，加密是可使用PASSWORD()函数，但是该函数在8.0中已取消5.7可以使用，所以不推荐该方式修改用户密码。

权限管理

查看MySQL有哪些权限：show privileges;
授权命令：GRANT 权限1，权限2… ON 数据库名.表名(代表库中所有表) TO 用户名@用户地址[IDENTIFIED BY ‘用户密码’]如：
GRANT select ON testdb. TO ‘li4’@’%’ ;授与li4testdb库下所有表的查询权限
授所有权给某个用户:grant all privileges on . to ‘用户名’@’地址’ ;此时li4拥有除了grant权限的所有权限，如果想将grant授权给li4需要在赋权限时加上WITH GRANT OPTION即可。表示可将自己拥有的权限赋给其他人.
查看当前用户权限:show grants;
查看某用户的权限:show grants for ‘用户名’@’host’;
收回权限:REVOKE 权限1，权限2… ON 数据库名.表名(*代表库中所有表) FROM 用户名@用户地址.
收回用户所有权限:REVOKE all privileges on . from to ‘用户名’@’地址;

权限表

MySQL服务器通过权限表来控制用户对数据库的访问.权限表存放在mysql库中,涉及的表如user(用户信息及对应权限),db(用户针对某个库所拥有的权限),tables_priv(用户针对某个库中某个表所拥有的权限),columns_priv(用户针对某个库中某个表某个列所拥有的权限),procs_priv(用户针对存储过程所拥有的权限)等.在服务启动时会将表中的权限信息读取进内存中.

角色管理

角色是MySQL8中新引入的功能,角色是权限的集合.可以为角色添加或移除权限.用户可以被赋予角色同时拥有角色的权限.注意角色创建后会存储在user表中
创建角色:create role ‘角色名称’@’host’;
角色赋权:grant 权限1… on 库名.表名 to ‘角色名’;
回收角色权限:revoke 权限1… on 库名.表名 from ‘角色名’;
查看角色拥有哪些权限:show grants for ‘角色名’;
删除角色:drop role ‘角色名’;
给用户赋角色:grant ‘角色名’@’host’ to ‘用户名’@’host’ ;
激活角色(否则用户无法使用角色的权限):set default role ‘角色名称’@’host’ to ‘用户名’@’host’ ;
撤销用户角色:revoke ‘角色名’@’host’ from ‘用户名’@’host’ ;
设置强制角色:强制角色是给每个创建账户的默认角色,不需要手动设置,注意强制角色无法被revoke或drop.
方式一:服务启动前设置,配置文件:
[mysqld]
mandatory_roles=’角色名称,角色名称@host…’
方式二:运行时设置
SET PERSIST mandatory_roles=’角色名称,角色名称@host…’#重启后仍有效
SET GLOBAL mandatory_roles=’角色名称,角色名称@host…’#重启后失效

逻辑架构

MySQL的架构主要分为三层：
1、连接层：负责客户端的连接，用户名密码的验证；
2、服务层：负责接收，解析、优化sql语句；
3、引擎层：负责和数据库文件交互，负责数据的存储与读取。
SQL执行流程
客户端连接至MySQL服务器后：
MySQL执行流程
1、查询缓存：如果在缓存中发现这条sql语句就直接返回结果；若没有则进入到解析器阶段（因为查询效率不高即命中相同sql概率低因为任何字符上的不同(如空格、注释、大小写等)都会导致不会命中8.0版本抛弃该功能）。
查询缓存前提条件：query_cache_type开关需要打开：0关，1开，2按需使用（在sql语句中使用SQL_CACHE关键字才会缓存使用SQL_NO_CACHE不会缓存如SELECT SQL_CACHE）默认关闭；开关可在配置文件中配置
2、解析器：对sql语句进行语义解析即sql中的字符串是什么代表什么和语法解析即sql语句是否满足MySQL语法；如果不对会报错；如果正确会生成一个语法树即什么操作（增删改查）使用到哪些字段、使用哪张表、条件是什么；
3、优化器：在这会确定sql语句的执行路径比如是根据全表检索还是索引检索等。优化器的作用就是找到最好的执行计划同时会生成执行计划。
4、执行器：获取到优化器的执行计划后需先判断用户是否具备权限，如果没有返回错误；如果有则打开表继续执行，在打开表示会根据表的引擎定义调用存储引擎的API对表进行读写后返回结果。（引擎是插拔式的可在定义表时选择）
可通过开启profiling后查看sql执行的情况，show profiles：查询所有执行的sql语句的query_id,show profile for query query_id,具体sql的执行情况。
Oracle执行流程
1、语法检查：检查sql拼写是否正确；
2、语义检查：检查sql中访问的对象是否存在，保证sql语句正确性；
3、 权限检查：检查用户是否具备访问数据库的权限；
4、共享池检查：其是一块内存池，主要用来缓存sql语句和语句的执行计划；在共享池中oracle首先对sql进行hash运算后根据hash值进行查找，如果存在sql的执行计划就直接拿来执行直接进入执行器，称为软解析；如果没有找到sql语句和执行计划oracle需要进入优化器创建解析树进行解析并生成执行计划称为硬解析；
5、优化器：解析sql创建解析树，生成执行计划；
6、执行器：拿到解析树和生成执行计划后执行sql语句返回结果；

存储引擎

存储引擎就是指表的类型，它的功能就是接收上层传下来的指令然后对表中的数据进行读写操作；不同的引擎存储方式/格式也不一样；只有Innodb支持事务。在创建表时可以指定使用哪种存储引擎若不指定则使用默认的存储引擎。
查看MySQL提供什么存储引擎：show engines;
引擎介绍
1、Innodb引擎：具备外键支持功能的事务存储引擎
MySQL从3.23.34a就开始包含Innodb引擎，从5.5版本开始默认采用Innodb引擎；Innodb是MySQL的默认事务型引擎，它被设计用来处理大量的短期事务，可以确保事务的完整提交和回滚;除非有非常特别的原因需要使用其他存储引擎否则应该优先考虑Innodb引擎。但是Innodb引擎写的处理效率会差一些并且会占用更多的磁盘空间以保存数据和索引；但是Innodb引擎不仅缓存了索引还缓存了真实数据所以其对内存要求较高。
2、MyISAM引擎：主要的非事务处理存储引擎
MyISAM提供了大量的特性，包括全文检索、压缩、空间函数等但其不支持事务、行级锁和外键，最大的缺陷就是崩溃后无法安全恢复。5.5版本前默认使用的存储引擎。优点是访问速度快，应用于只读或已读为主的业务。
1-2 Innodb与MyISAM对比
（1）事务：MyISAM不持支，Innodb支持事务；
（2）行表锁：MyISAM支持表锁（即使操作一条记录也会锁住整个表，不适合高并发场景），Innodb支持行锁（操作时只锁某一行，对其他行无影响适合高并发场景）；
（3）缓存：MyISAM只缓存索引不缓存真实数据，Innodb不仅缓存索引还会缓存真实数据所以对内存要求较高；
（4）存储数据的结构：8.0版本-MyISAM三个文件sdi、myd、myi，Innodb一个文件ibd；5.7-MyISAM三个文件frm、myd、myi，Innodb一个文件frm、ibd；
（5）默认使用：5.5之前默认使用MyISAM，之后默认使用Innodb；
（6）如果是小型应用大多操作为查询和新增时选择MyISAM，若对数据比较严格高并发场景选择Innodb。
3、Archive引擎：用于数据存档
Archive是存档的意思，仅仅支持插入和查询两种功能(数据插入后不能修改)；
适用于日志和数据采集类应用，插入速度快但是查询支持交叉。
4、Blackhole引擎：丢弃写操作读操作会返回空内容
无任何存储机制，丢弃所有插入的数据，但服务器会记录Blackhole表的日志。
5、CSV引擎：存储数据时以逗号分隔各个数据项
CSV引擎可将普通的CSV文件作为MySQL的表来处理，但不支持索引；其存储的数据可直接在操作系统中使用文本编辑器或者excel读取，对于数据的快去导入导出有明显的优势。创建CSV表时服务器会创建一个纯文本的数据文件，其名称以表名开头并带有.CSV扩展名。
6、Memory引擎：置于内存的表
Memory采用的逻辑介质是内存(表结构存储在文件中，数据存储在内存)，响应速度快，但当崩溃时数据会丢失。
7、