MySQL高级知识概括

MySQL高级知识概括

一、MySQL的日志文件

1.1 日志的种类

- 1.重做日志（redo log）log-update：确保事务的持久性。
​
- 2.回滚日志（undo log）：保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC）。
​
- 3.二进制日志（binlog）log-bin ：主从复制。
​
- 4.错误日志（errorlog）log-err ：默认是关闭的，记录严重的警告和错误信息，每次启动和关闭的详细信息等。
​
- 5.慢查询日志（slow query log）og-slow-queries：它用来记录在MySQL中响应时间超过阀值的语句。
​
- 6.一般/全局/通用查询日志（general log）log：用来记录用户的所有操作，包括启动和关闭 MySQL 服务、更新语句和查询语句等。默认情况下，通用查询日志功能是关闭的

1.2 查看日志的方法

1.打开cmd

输入 mysql -u root -p，然后输入密码，进入数据库

2.查询当前日志记录的状况

mysql>show variables like 'log%';（是否启用了日志）

1. 默认情况下，MySQL查询日志是关闭的。生产环境，如果开启MySQL查询日志，对性能还是有很大的影响的。

二、MySQL的逻辑分层

2.1 引言

和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上

2.2 分层

• 连接层：最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcplip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。

• 服务层：第二层架构主要完成大多少的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行

• 引擎层：存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取

• 存储层：数据存储层，主要是将数据存储在运行于裸设备的文件系统之上

三、MySQL的执行过程及顺序

3.1 执行过程

1.连接器 （负责与客户端的通信）
2.缓存
3.分析器（主要作用是将客户端发过来的sql语句进行分析，这将包括预处理与解析过程，在这个阶段会解析sql语句的语义，并进行关键词和非关键词进行提取、解析，并组成一个解析树）
4.优化器（主要是进行sql语句的优化，会根据执行计划进行最优的选择,匹配合适的索引,选择最佳的执行方案）
5.执行器（此时会调用存储引擎的API,API会调用存储引擎，主要有一下存储的引擎，不过常用的还是myisam和innodb）

3.2 如何查看SQL的执行状态?

- 查看SQL的执行状态
    show full processlist;

3.3 SQL的执行顺序

事实上,sql并不是按照我们的书写顺序来从前往后、左往右依次执行的,它是按照固定的顺序解析的,主要的作用就是从上一个阶段的执行返回结果来提供给下一阶段使用，sql在执行的过程中会有不同的临时中间表，一般是按照如下顺序：

四、MySQL的存储引擎

4.1 概念

存储引擎是MySQL将数据存储在文件系统中的存储方式或者存储格式

4.2 命令

- 1.查看数据库中支持哪些存储引擎
    show engines;
    
- 2.查看当前mysql数据库默认的存储引擎
    show variables like '%storage_engine%';
    
    
    
- 3.查看某张表的存储引擎（方式一）
    show table status from 数据库名 where name = '表名'\G;
    
- 4.查看某张表的存储引擎（方式二）
    use 数据库;
    show create table 表名;
    
    
    
- 5.修改存储引擎(方式一)
    use 数据库;
    alter table 表名 engine=存储引擎；
    
- 6.修改存储引擎（方式二）
    修改mysql配置文件参数（修改完成后务必重启服务器生效）

4.3 MyISAM 和 InnoDB 存储引擎的区别？

对比项	MyiSAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁,即使操作一条记录也会锁住整个表,不适合高并发的操作	行锁,操作时只锁某一行，不对其它行有影响，适合高并发的操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据,对内存要求较高，而且内存大小对性能有决定性的影响
表空间	小	大
关注点	性能	事务
默认安装	Y	Y

五、MySQL配置文件参数解释

- 客户端参数
    - 1.port 表示的是MySQL数据库的端口，默认的端口是3306，如果你需要更改端口号的话，就可以通过在这里修改。
    - 2.default-character-set 参数是客户端默认的字符集，如果你希望它支持中文，可以设置成gbk或者utf8
    
​
- 服务器端参数
    - 1.port参数也是表示数据库的端口。
    - 2.basedir参数表示MySQL的安装路径。
    - 3.datadir参数表示MySQL数据文件的存储位置，也是数据库表的存放位置。
    - 4.character-set-server=utf8参数表示默认的字符集，这个字符集是服务器端的。
    - 5.default-storage-engine参数默认的存储引擎。
    - 6.sql-mode参数表示SQL模式的参数，通过这个参数可以设置检验SQL语句的严格程度
    - 7.max_connections参数表示允许同时访问MySQL服务器的最大连接数。
    - 8.query_cache_size参数表示查询时的缓存大小

六、保障MySQL安装的最佳方法？

    1.避免从互联网访问MySQL数据库，确保特定主机才拥有访问特权
    2.定期备份数据库
    3.禁用或限制远程访问
    4.设置root用户的口令并改变其登录名
    5.移除测试(test)数据库
    6.移除匿名账户和废弃的账户
    7.降低用户的数据库特权

七、数据库设计的三大范式

7.1 目的

数据库的的三大范式主要目的是设计规范合理的数据库。要遵循后边的范式，就必须先遵循前面的范式要求，比如第二范式就必须先遵循第一范式的，第三范式必须先遵循第二范式

7.2 简述三大范式

- 第一范式(1NF)：每个列都不可以再拆分。
- 第二范式(2NF)：在第一范式的基础上，每张表只描述一件事
- 第三范式(3NF)：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。

八、MySQL查看信息命令

1.查看表的定义
	desc 表名;
2.查看表的定义
	show columns from 表名;
3.查看目前处理的列表
	show processlist;
4.查看有哪些存储过程
	show procedure status;
5.查看表的创建过程 
	show create table 表名;
6.查看存储过程的创建过程
	show create procedure 存储过程名;
7.查看索引
	show index from 表名;
8.主服务器
	show master status;
9.从服务器
	show slave status;
10.查看服务器支持的权限
	show privileges；
11.查看所有支持的字符集
	show character set；
12.查看一条命令的执行方案
	explain select * from 表名;