MySql高级
MySql架构介绍
MysqlLinux版的安装
-
系统：centos7
-
安装 wget ： yum install wget
-
方法一：
- 下载并安装MySQL官方的 Yum Repository
- wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
- 使用上面的命令就直接下载了安装用的Yum Repository，大概25KB的样子，然后就可以直接yum安装了。
- yum -y install mysql57-community-release-el7-10.noarch.rpm
- 下载并安装MySQL官方的 Yum Repository
-
方法二：安装mariadb
-
centos7下使用mysql安装包安装my sql5.7Mysql配置文件
-
1、检查系统是否安装过mysql
-
//检查系统中有无安装过mysql rpm -qa|grep mysql //查询所有mysql 对应的文件夹，全部删除 whereis mysql find / -name mysql
-
-
-
修改字符集
- 配置文件的修改
- default-character-set=utf8
- 修改数据库的字符集
- mysql> alter database mydb character set ‘utf8’;
- 修改数据表的字符集
- mysql>alter table mytbl convert to character set ‘utf8’;
- 配置文件的修改
-
二进制日志log-bin
- 主从复制
- Mysql8.0默认关闭了log_bin
-
错误日志log-error
- 默认关闭，记录严重的警告和错误信息，每次启动和关闭的详细详细
-
查询日志log
- 默认关闭，记录查询的sql语句，如果开启会减低mysql的整体性能，因为记录日志
-
数据文件
- 两个系统
- windows：data目录下
- linux：/var/lib/mysql
- frm文件
- 存放表结构
- myd文件
- 存放表数据
- myi文件
- 存放表索引
- 两个系统
-
用户管理
-
查看用户信息
-
use mysql; select * from user\G;
-
-
创建用户
- create user jiangl identified by ‘123123’;
- 表示创建名为jiangl的用户，密码设为123123；
-
了解user表
- 查看用户表：select host,user,authentication_string from mysql.user
- host：表示连接类型
- %表示所用远程通过TCP方式的连接
- IP地址如（192.168.1.2，127.0.0.1）通过制定ip地址进行的TCP方式的连接
- 机器名 通过制定i网络中的机器名进行的TCP方式的连接
- ::1 IPv6的本地ip地址等同于IPv4的 127.0.0.1
- localhost 本地方式通过命令行方式的连接，比如mysql -u xxx -p 123xxx 方式的连接。
- user：表示用户名，同一用户通过不同方式链接的权限是不一样的
- password：密码。所有密码串通过password（明文字符串）生成的密文字符串。加密算法为MYSQLSHA1，不可逆的
- mysql5.7的密码保存到authentication_string字段中，不再使用password字段
- host：表示连接类型
- select_piv,inser_piv等：为该用户所拥有的权限
- 查看用户表：select host,user,authentication_string from mysql.user
-
设置密码
- 修改当前用户的密码：set password=password(‘123456’);
- 修改某个用户的密码：update mysql.user set password = password(‘123456’) where user = ‘jiangl1’;
- flush privileges：#所有对user 表的修改，必须使用flushprivileges命令后才生效
-
修改用户
- 修改用户名：update mysql.user set user = ‘jiangl2’ where user = ‘jiangl1’;
-
删除用户
- drop user jiangl1;
- 不要通过delete from user u where user = ‘jiangl1’; 进行删除，系统会有残留信息保留。
-
-
权限管理
- 授予权限
- 授权命令：grant 权限1,权限2,…权限n on 数据库名称 to 用户名称@用户地址 identified by ‘连接口令’;
- grant命令如果发现没有该用户，则会直接创建一个用户（mysql8.0以前的版本可以使用grant在授权的时候隐式的创建用户，8.0以后已经不支持，所以必须先创建用户，然后再授权）。
- 比如：grant select,insert,delete,drop on jl.* to ‘jiangl1’@‘localhost’;
- #给jiangl1用户用本地命令行方式下，授予jl这个库下的所有的增删改查的权限。
- grant all privileges on * . * to ‘jiangl’@’%’;
- #授予通过网络方式登录的jiangl用户，对所有库所有表的全部权限。
- 刷新权限
- flush privileges;
- 收回权限
- 查看当前用户权限：show grants;
- 收回权限命令：revoke 权限1,权限2,…权限n on 数据库名称.表名称 from 用户名@用户地址;
- REVOKE ALL PRIVILEGES ON mysql.* FROM jiangl@localhost;
- #收回全库全表的所有权限
- REVOKE select,insert,update,delete ON mysql.* FROM jiangl@localhost;
- #收回mysql库下所有表的增删改查权限
- 必须用户重新登录后生效
- 查看权限
- 查看当前用户所有权限：show grants;
- 查看某用户的全局权限：select * from user;
- 查看某用户的某个表的权限：select * from tables_priv;
- 授予权限
-
Mysql的sql_mode的合理设置
- sql_mode是个很容易忽视的变量，默认值为空值，在这种设置下是可以允许一些非法操作的，比如允许一些非法数据的插入。在生成环境必须将这个值设置为严格模式，所以开发、测试环境的数据库也必须要设置，这样在开发测试阶段就可以发现问题。
- 查看sql_mode：show variables like ‘sql_mode’;
- sql_mode常用值如下：
- ONLY_FULL_GROUP_BY：对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为select列不再GROUP BY从句中
- NO_AUTO_VALUE_ON_ZERO：该值影响自增长列的插入，默认设置下，插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。
- STRICK_TRANS_TABLES：在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制。
- NO_ZERO_IN_DATE：在严格模式下，不允许日期和月份为零。
- NO_ZERO_DATE：设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告。
- ERROR_FOR_DIVISION_BY_ZERO：在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如果未给出该模式，那么数据被零除时Mysql返回NULL
- NO_AUTO_CREATE_USER：禁止GRANT创建密码为空的用户
- NO_ENGINE_SUBSTITUTION：如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常。
- PIPES_AS_CONCAT：将‘||“视为字符串的连接操作符而非或运算符，这个Oracle数据库是一样的。也可字符串的拼接函数Concat相类似
- ORACLE：设置等同
- PIPES_AC_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER
Mysql逻辑架构介绍
-
层级介绍
-
连接层
- 最上层是一些客户端和连接服务，包含本地socket通信和大多数基于客户端/服务器工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全连接。服务器也会为安全连接的每个客户端验证它所具有的操作权限。
-
服务层
- 第二次架构主要完成大多数核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程，函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作环境中能够很好的提升系统的性能。
- Management Services&Utilities：系统管理和控制工具
- SQL Interface：SQL接口，接收用户的SQL命令，并且返回用户需要的查询结果。比如select from就是调用SQL Interface
- Parser：mysql解析器，SQL命令传递到解析器的时候会被解析器验证和解析。
- Optimizer：查询优化器，SQL语句在查询之前会使用查询优化器对查询进行优化。用一个例子就可以理解：select uuid,name from user where gender = 1; 优化器来决定先投影还是先过滤
- Cache和Buffer：查询缓存，如果查询缓存命中查询结果，查询语句就可以直接取查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等。
-
引擎层
- 存储引擎层，存储引擎真正的负责了Mysql中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样可以根据实际需要进行选取。
-
存储层
- 数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。
-
-
基本架构概览
- 连接器
- 身份验证和权限相关（登录MYSQL的时候）
- 查询缓存
- 执行查询语句的时候，会先查询缓存（MySQL8.0版本后移除，因为这个功能不太实用，因为大多数情况下，数据的改变概率高，通常缓存都是过时的，失效的）。
- 分析器
- 没有命中缓存的话，SQL语句会经过分析器，分析器就是要解析SQL语句要干嘛，检查SQL语句语法是否正确。
- 优化器
- 按照MYSQL认为最优的方法去执行，在不改变数据结果的情况下，可以改变where子句后面的条件的查询条件顺序
- 执行器
- 执行语句，然后从存储引擎返回数据。
- 连接器
-
简单的来说MYSQL主要分为服务层和存储引擎层
- Server层：主要包括连接器、查询缓存、分析器、优化器、执行器等。所有跨存储引擎的功能都要在这一层实现，比如存储过程，触发器，视图，函数等，还有通用的日志模块binlog日志模块。
- 存储引擎：主要负责数据的存储和读取，采用可以替换的插件式架构，支持innodb，myisam，memory等，其中innodb有自有的日志模块redlog模块。目前最常用的存储引擎是innodb，它从mysql5.5.5开始就被当做默认的存储引擎了。
-
Service层基本组件介绍
- 连接器
- 连接器主要和身份认证和权限相关的功能相关。
- 主要负责用户登录数据库，进行用户的身份验证，包括校验账户密码，权限等操作，如果用户账户密码已通过，连接会到权限表中查询该用户的所有权限，之后的这个连接里的权限逻辑判断都是会依赖此时读取到的权限数据，也就是说，后续只要这个连接不断开，即使管理员修改了这个用户的权限，本次连接的权限是不会受影响的。
- 查询缓存
- 查询缓存主要用来缓存我们所执行的select语句以及该语句的结果集。
- 建立连接以后，执行查询语句的时候，会先查询缓存，MYSQL会校验这个sql是否执行过，以Key-Value的形式缓存在内存中，Key是查询预计，Value是结果集。如果缓存key被命中，就会直接返回给客户端，如果没有命中，就会执行后续的操作，完成后也会把这个结果缓存起来，方便下一次调用。当然在真正执行缓存查询的时候还是会校验用户的权限，是否有该表的查询条件
- MYSQL查询不建议使用缓存，因为查询缓存失效在实际场景中可能会非常频繁，假如对一个表更新的话，这个表上所有的查询缓存都会被清空。对于不经常更新的数据来说，使用缓存还是可以的
- 所以，在大多数情况下都是不推荐使用查询缓存的。
- MYSQL8.0版本后删除了缓存的功能，官方也是认为该功能在实际应用场景中比较少，所以干脆去掉了。
- 分析器
- MYSQL没有命中缓存，那么就会进入分析器，分析器主要是用来分析SQL语句做什么的，分析器也会分为几步骤
- 第一步，词法分析，一条SQL有多个字符串组成，首先提取关键字，比如select，提出查询的表，提出字段名，提出查询条件等等。做完这些操作后，就会进入第二步。
- 第二步，语法分析，主要就是判断输入的sql是否正确，是否符合MYSQL的语法。
- 完成这两步，mysql就准备开始执行了，但是如何执行，怎么执行是最好的结果，这就需要优化器了。
- 优化器
- 优化器的作用就是按优化器认为的最优的方案去执行（有时候可能也不是最优的），比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。
- 执行器
- 当选择了执行方案后，MYSQL就准备开始执行了，首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用引擎的接口，返回接口执行的结果。
- 连接器
利用show profile查看sql的执行周期
- 在mysql8.0以前，mysql存在查询缓存，通过修改配置文件添加query_cache_type=1;开启查询缓存，在mysql8.0之后，mysql取消了查询缓存，所以没有此配置
- 查看profiling配置：show variables like ‘%profiling%’;
- 先开始profiling ：set profiling=1;
- 查询语句
- 通过命令查看sql的执行周期：show profiles;
-
- 通过命令查看想起信息：show profile cpu,block io query 编号
- show profile cpu,block io for query 4
Mysql语句执行流程分析
-
sql查询步骤简单说明
- mysql客户端通过协议与mysql服务端建立连接，发送查询语句，先检查查询缓存（mysql8.0取消了查询缓存），如果命中，直接返回结果，否则进行语句解析，也就是说，在解析查询之前，服务器会先访问查询缓存（query cache）—查询缓存存储SELECT语句以及相应的查询结果集。如果某个查询结果已经位于缓存中，服务器就不会再对查询进行解析、优化、以及执行了。服务器会将缓存中的结果返回给客户端即可，这将大大的提高性能。
- 语法解析器和预处理：首先mysql通过关键字将sql语句进行解析，并生成一颗对应的“解析树”。mysql解析器将使用mysql语法规则验证和解析查询；预处理则根据一些mysql规则进一步检查解析树是否合法。
- 查询优化器当解析树被认为是合法的了，并且由优化器将其转化为执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划
- 然后。mysql默认使用Btree索引，并且一个大致方向：无论怎么折腾mysql，至少目前来说，mysql最多只用到表中的一个索引
-
查询语句执行流程分析
-
语句
-
select * from student where age=18 and name = '张三';
-
-
sql执行的过程分析
-
先检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限，在MYSQL8.0版本以前，会先查询缓存，以这条sql语句为key在内存中查询是否有结果，如果有直接缓存，直接返回缓存。如果没有缓存，则执行下一步。
-
通过分析器进行词法分析，提取sql语句的关键元素，比如提取上面这个语句是查询 select，提取需要查询的表明为 student，需要查询所有的列，查询条件是这个表的age = 18 和 name = ‘张三’。然后判断这个sql语句是否有语法错误，比如关键字是否正确等等，如果检查没有问题就执行下一步。
-
接下来就是优化器进行确定执行方案，上面的sql有两个执行方案
a. 先查询学生表中姓名为“张三”的学生，然后判断年龄是否为18。
b. 先找出学生表中年龄为18的学生，然后再查询姓名为“张三”的学生
那么优化器根据自己的优化算法进行选择，选择出执行效率最好的一个方案（优化器认为的，有时候不一定是最好的）。那么确认了执行计划后就准备开始执行了。
-
执行器执行，进行权限校验，如果没有权限就会返回错误信息，如果有权限就会调用数据库引擎接口，返回引擎的执行结果。
-
-
-
更新语句执行流程分析
-
语句
-
update student set age=23 where name = '张三';
-
-
在mysql中更新语句需要记录日志，这就需要引入日志模块，Mysql自带的日志模块binlog（归档日志），所有存储引擎都可以使用，开发者常用的InnoDB引擎自带了一个日志模块redo log（重做日志），现以innodb做为例子，分析更新语句在innodb引擎下的执行流程
- 客户端通过连接器与mysql建立连接
- 先查询到张三这一条数据，如果有缓存，也是会用缓存
- 删除要update表的缓存
- 分析器解析sql并检查是否与语法错误
- 优化器确定查询索引
- 执行器调用Innodb存储引擎接口获取 name 为张三的数据行。
- Innodb存储引擎扫描name为张三的行返回给server层。
- 执行器将这行数据行的age值设置成23，再调用存储引擎接口写入该行数据。
- Innodb存储引擎将数据保存在内存中（WAL）
- Innodb存储引擎写redo日志，prepare状态，通知server层
- server层提交Binlog后，通知Innodb存储引擎
- Innodb存储引擎将redo日志状态改为commit
-
-
疑问：为什么要用两个日志模块，为什么要用二阶段提交
- 最开始Mysql是没有innodb的，mysql默认的存储引擎是myisam（只有默认的日志模块，binlog），而redo log是innodb特有的日志模块，binlog只能用来归档，并没有crash-safe的能力（crash-safe的能力即使数据库发生异常重启，之前提交的记录都不会丢失），innodb通过redo log来让mysql拥有了crash-safe的能力
- 并不是说只用一个日志模块不可以，而是innodb通过redo-log来支持事务。
- 二阶段提交的原因，反证法，
- 先写redo log直接提交，然后写binlog，假设写完redo log后机器挂了，binlog日志没有被写入，那么机器重启后，会通过redo log恢复数据，但是这个时候bin log没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据
- 先写binlog，然后写redo log，假设写完bin log，机器异常重启了，由于没有redo log，机器无法恢复这一条记录，但是binlog又有记录，那么和上面同样道理，又会产生数据不一致的情况
- 采用redo log二阶段提交的方式，先写redo log 状态为prepare，写完bin log后，然后再提交redo log，状态为commit。可以防止上面两种情况的数据不一致的情况出现。
- 另外一个极端情况，redo log处于预提交状态，bin log写完了，这时候机器重启了，怎么办？
- 这里就需要依赖mysql的处理机制了，mysql的处理过程
- 判断redo log 是否完整，如果判断是完整的，立即提交
- 如果redo log只是预提交，但不是commit状态，这时候会去判断binlog 是否完整，如果bin log完整就提交redo log，如果bin log不完整则回滚redo log
- 这样就解决了数据一致性的问题
- 这里就需要依赖mysql的处理机制了，mysql的处理过程
-
总结
- Mysql主要分为Server层和引擎层，Server层主要包括：连接器，查询缓存，分析器，优化器，执行器，同时还有一个日志模块（binlog），binlog所有的mysql存储引擎都能使用，redo log只有innodb引擎可以使用
- 引擎层，是插件式的，主要包括：myisam，innodb，memory等
- 查询语句的执行流程如下：权限校验（如果命中缓存）-》查询缓存-》分析器-》优化器-》权限校验-》执行器-》数据引擎
- 更新语句执行流程如下：分析器-》权限校验-》执行器-》引擎-》redo log（prepare 状态）-》bin log -》redo log（commit状态）
Mysql三范式
- 第一范式
- 数据表里的每一列都要保持它的原子性，也就是列不能被分割。
- 第二范式
- 属性必须完全依赖主键
- 依赖
- 在数据表中，属性（属性组）X确定的情况下，能完全推出属性Y完全依赖属性X
- 完全依赖
- 完全依赖针对属性组来说的，当一组属性X能推出来的时候就说Y完全依赖于X
- 第三范式
- 所有非主属性不依赖于其他的非主属性
- 三范式的作用
- 为了在设计中更好的解决数据的冗余，数据有效性检查，提高存储效率。
Mysql存储引擎
-
查看存储引擎命令：show engines;
-
各个存储引擎的介绍
- InnoDB存储引擎：InnoDB是MYSQL默认事务型引擎，它被设计用来处理大量的短期（shorted-lived）事务。除非有非常特别的原因需要使用其他存储引擎，否则应该优先考虑InnoDB引擎
- MyISAM存储引擎：MyISAM提供大量的特性，包括全文索引、压缩、空间函数（GIS）等，但MyISAM不支持事务和行级锁，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。
- Archive引擎：Archive档案存储引擎只支持INSERT和SELECT操作，在MYSQL5.1之前不支持索引。Archive表适合日志和数据采集类应用。根据英文的测试结论来看，Archive表比MyISAM表要小大约75%，比支持事务处理的InnoDB表小大约83%。
- Blackhole引擎：Blockhole引擎没有实现任何存储机制，它会丢弃所有插入的数据，不做任何保存。但数据库会记录Blackhole表的日志，所以可以用于复制数据到备库，或者简单记录到日志。但这种方式会碰到很多问题，因此不推荐
- CSV引擎：CSV引擎可以将普通的CSV文件作为Mysql的表来处理，但不支持索引。CSV引擎可以作为一种数据交换的机制，非常有用。CSV存储的数据直接可以在操作系统里，用文本编辑器，或者excel读取。
- Memory引擎：如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory表是非常有用的。Memory表至少比MyISAM表要快一个数量级
- Federated引擎：Federated引擎是访问其他MYSQL服务器的一个代理，尽管引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的。
-
MyISAM和Innodb对比
-
对比项 MyISAM InnoDB 外键 不支持 支持 事务 不支持 支持 行表锁 支持表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作 行锁，操作时只锁某一行，不对其他行有影响，适合高并发的操作 缓存 只缓存索引，不缓存真实数据 不仅缓存索引还要缓存真实数据，内存要求较高，而且内存大小对性能有决定性的影响 表空间 小 大 关注点 性能，节省资源，消耗少 事务，并发写，事务，更大资源 默认安装 Y Y 自带系统表使用 Y N
-
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
