4、MySQL 架构及日志

该栏目讲叙 MySQL 相关的知识体系，包括数据库简介、SQL 简介、数据定义、数据操作、数据查询及数据优化等模块

---

---

MySQL 架构

1、相关组件

• Connection Pool：连接池组件
• Management Services & Utilities：管理服务和工具组件
• SQL Interface：SQL 接口组件
• Parser：查询分析器组件
• Optimizer：优化器组件
• Caches & Buffers：缓冲池组件
• Pluggable Storage Engines：存储引擎
• File System：文件系统

2、MySQL 层级

• 连接层：主要完成一些类似于连接处理、授权认证、及相关的安全方案
• 服务层：主要完成大多数的核心服务功能，如SQL接口、分析、优化及缓存
• 引擎层：真正负责MySQL中数据的存储和提取
• 存储层：主要是将数据存储在文件系统之上，并完成与存储引擎的交互

---

存储引擎

1、简介

• 概述：存储引擎作用对象是表，提供存储数据、查询数据、建立索引等技术的实现
• 查看支持的引擎：show engines
• 各种存储引擎的特征
  

2、存储方式

• Innodb存储
  • .frm：存储表的定义
  • .ibd：存储数据和索引
• MyISAM存储
  • .frm：存储表定义
  • .MYD： 存储数据
  • .MYI：存储索引

---

索引

1、简介

• 概述：索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的集合
  
• 优点：提高查询效率
• 缺点：降低数据更新的效率
• 类型
  • 普通索引（INDEX）：提高查询速度
  • 唯一索引（UNIQUE）：字段数据是唯一的
  • 主键索引（PRIMARY）：字段数据唯一且不为 null
  • 全文索引（FULLTEXT）：提高文本字段的检索

2、索引结构

• 各种引擎的支持的索引结构
  

• B+Tree 结构：在 InnoDB 中，表数据文件本身就是按 B+Tree 组织的一个索引结构。这棵树的叶节点 data 域保存了完整的数据记录，key 保存数据表的主键。因此 InnoDB 表数据文件本身就是主索引，另外相邻叶子节点的链表指针，就形成了带有顺序指针的 B+Tree，提高区间访问的性能
  

3、操作索引

# 查看索引
show index;

# 创建普通索引
create index 索引名 on 表名(列名(长度) ASC|DESC);

# 创建唯一索引
create unique index 索引名 on 表名(列名(长度) ASC|DESC);

# 创建表结构时添加主键约束
create table 表名(列名1 数据类型,列名2 数据类型..., primary key(列名));

# 修改表结构时添加主键约束
alter table 表名 add constraint 主键名 primary key(列名)

# 删除索引
drop index 索引名 on 列名;

4、设计原则

• 对查询频次较高，且数据量比较大的表建立索引
• 索引字段的选择，最佳候选列就当从 where 子句的条件中提取
• 使用唯一索引，区分度越高，使用索引的效率越高
• 使用短索引，即构成索引的字段比较短
• 利用最左前缀

5、使用索引

• 全值匹配，对索引中所有列都指定具体值
• 最左前缀法则：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列
• 范围查询右边的列，索引失效
• 索引列上进行运算操作，索引失效
• 字符串不加单引号，索引失效
• 尽量使用覆盖索引，避免 select *
• 用 or 分割开的条件，如果 or 前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用
• 以 % 开头的 Like 模糊查询，索引失效（使用覆盖索引解决）
• in 走索引，not in 索引失效
• 如果 MySQL 评估使用索引比全表更慢，则不使用索引
• NULL ，IS NOT NULL 有时索引失效
• 尽量使用复合索引，而少使用单列索引（因为都是单列索引时数据库会选择一个最优的索引（辨识度最高索引）来使用，并不会使用全部索引）

# 创建索引
create index idx_seller_name_sta_addr on tb_seller(name,status,address);

# 全值匹配
explain select * from tb_seller where name = '小米科技' 
and status = '1' and address = '北京市'\G;

# 匹配最左前缀法则
explain select * from tb_seller wherename = '小米科技';
explain select * from tb_seller where name = '小米科技' and status = '1';

# 违反最左前缀法则
explain select * from tb_seller where status = '1';

# status后面的索引已经失效 
explain select * from tb_seller where name = '小米科技' and status > 1 
and address = '北京市'\G;

# 不要在索引列上进行运算操作，索引将失效
explain select * from tb_seller where status = '1' where substring(name,3,2) = '科技';

6、查看使用状态

show status like 'Handler_read%';
show global status like 'Handler_read%';

---

日志

1、错误日志

• 概述：记录 mysql 服务器在运行过程中发生任何严重错误时的异常信息
• 查看

# 查看日志位置指令（默认/var/lib/mysql）
show variables like 'log_error%';

# 查看日志内容
tail -f /var/lib/mysql/[主机名].err 

2、二进制日志

• 概述：记录所有的 DDL 语句 和 DML 语句信息
• 作用：数据恢复、主从复制
• 操作

# 进入/usr/my.cnf
# 配置开启 binlog 日志， 日志的文件前缀为 mysqlbin -----> 生成的文件名如：mysqlbin.000001,mysqlbin.000002
log_bin=mysqlbin
# 配置二进制日志的格式
binlog_format= STATEMENT

## 日志格式类型
STATEMENT：该日志格式在日志文件中记录的都是 SQL 语句（statement），通过 Mysql 提供的 mysqlbinlog 工具，
		   可以查看到每条语句的文本。主从复制的时候，从库（slave）会将日志解析为原文本，并在从库重新执行一次
ROW：该日志格式在日志文件中记录的是每一行的数据变更，而不是记录 SQL 语句
MIXED：这是目前 MySQL 默认的日志格式，即混合了 STATEMENT 和 ROW 两种格式

# 日志读取：由于日志以二进制方式存储，不能直接读取，需要用 mysqlbinlog 工具来查看
mysqlbinlog mysqlbing.000001;  # 查看 STATEMENT 格式日志

# 查看 ROW 格式日志
mysqlbinlog -vv mysqlbin. 000002

# 删除日志相关的命令
reset master：删除所有
purge master logs to mysqlbin：删除编号之前的日志
purge master logs before 'yyyy -mm-dd hh24:mi:ss'：删除指定时间之前产生的日志
-expire_logs_days=#：设置日志的过期天数

3、查询日志

• 概述：日志中记录了客户端的所有操作语句
• 配置

# 该选项用来开启查询日志，0代表关闭，1代表开启
general_log = 1
# 设置日志的文件名，默认的文件名为 host_name.log
general_log_file = file_name