自研mysql存储引擎 mysql 存储引擎

1.MySQL环境

1.1环境安装

# 查看Linux服务器上是否安装过MySQL
rpm -qa | grep -i mysql # 查询出所有mysql依赖包

# 1、拉取镜像
docker pull mysql:5.7

# 2、创建实例并启动
docker run -p 3306:3306 --name mysql \
-v /root/mysql/log:/var/log/mysql \
-v /root/mysql/data:/var/lib/mysql \
-v /root/mysql/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=root \
-d mysql:5.7

# 3、mysql配置 /root/mysql/conf/my.conf
[client]
#mysqlde utf8字符集默认为3位的，不支持emoji表情及部分不常见的汉字，故推荐使用utf8mb4
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
#设置client连接mysql时的字符集,防止乱码
init_connect='SET collation_connection = utf8_general_ci'
init_connect='SET NAMES utf8'

#数据库默认字符集
character-set-server=utf8

#数据库字符集对应一些排序等规则，注意要和character-set-server对应
collation-server=utf8_general_ci

# 跳过mysql程序起动时的字符参数设置 ，使用服务器端字符集设置
skip-character-set-client-handshake

# 禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。
# 但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，
# 否则MySQL将无法正常处理连接请求！
skip-name-resolve

# 4、重启mysql容器
docker restart mysql

# 5、进入到mysql容器
docker exec -it mysql /bin/bash

# 6、查看修改的配置文件
cat /etc/mysql/my.conf

1.2 安装位置

Docker容器就是一个小型的Linux环境，进入到MySQL容器中

docker exec -it mysql /bin/bash

Linux环境下MySQL的安装目录

 

 

1.3 修改字符集

# 1、进入到mysql数据库并查看字符集
# show variables like 'character%';
# show variables like '%char%';

mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

打开/etc/mysql/my.cnf文件，里面有两行配置

!includedir /etc/mysql/conf.d/ 
!includedir /etc/mysql/mysql.conf.d/

/etc/mysql/mysql.conf.d/mysqld.cnf 添加

[mysqld]
character_set_server=utf8

/etc/mysql/conf.d/mysql.cnf 添加

[mysql]
default-character-set=utf8

#作废
[client]
default-character-set=utf8

[mysql]
default-character-set=utf8
character_set_client=utf8
# 数据库默认字符集
character_set_server=utf8
#数据库字符集对应一些排序等规则，注意要和character-set-server对应
collation-server=utf8_general_ci

[mysqld]
# 设置client连接mysql时的字符集,防止乱码
init_connect='SET NAMES utf8'
init_connect='SET collation_connection = utf8_general_ci'



# 跳过mysql程序起动时的字符参数设置 ，使用服务器端字符集设置
skip-character-set-client-handshake

# 禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求！
skip-name-resolve

注意:安装MySQL完毕之后，第一件事就是修改字符集编码

 

1.4 配置文件

MySQL配置文件 

1、二进制日志 log-bin : 主从复制

# my,cnf
# 开启mysql binlog功能
log-bin=mysql-bin

2、错误日志log-error:默认是关闭的，记录严重的警告和错误信息，每次启动和关闭的详细信息等

# my,cnf
# 数据库错误日志文件
log-error = error.log

3、查询日志log:默认关闭，记录查询的sql 语句，如果开启会降低MySQL 整体的性能，因为记录日志需要消耗系统资源

# my,cnf
# 慢查询sql日志设置
slow_query_log = 1
slow_query_log_file = slow.log

4、数据文件
·
● frm文件∶存放表结构
·
● myd文件:存放表数据

● myi 文件:存放表索引

# mysql5.7 使用.frm文件来存储表结构
# 使用 .ibd文件来存储表索引和表数据
-rw-r-----  1 mysql mysql   8988 Jun 25 09:31 pms_category.frm
-rw-r-----  1 mysql mysql 245760 Jul 21 10:01 pms_category.ibd

MySQL5.7的Innodb存储引擎可将所有数据存放于 ibdata* 的共享表空间，也可将每张表存放于独立的 .ibd 文件的独立表空间。
共享表空间以及独立表空间都是针对数据的存储方式而言的
● 共享表空间:某一个数据库的所有的表数据，索引文件全部放在一个文件中，默认这个共享表空间的文件路径在 data 目录下。默认的文件名为 :ibdata1 初始化为 10M
● 独立表空间:每一个表都将会生成以独立的文件方式来进行存储，每一个表都有一个.frm表描述文件，还有一个 .ibd 文件。其中这个文件包括了单独一个表的数据内容以及索引内容，默认情况下它的存储位置也是在表的位置之中。在配置文件 my.cnf 中设置: innodb_file_per_table。

 

 

 

2.MySQL逻辑架构

 

 

● Connectors 指的是不同语言中与SQL的交互

● Connection Pool 管理缓冲用户连接，线程处理等需要缓存的需求。MySQL数据库的连接层
● Management Serveices & Utilities 系统管理和控制工具。备份、安全、复制、集群等等
● SQL Interface接受用户的SQL命令，并且返回用户需要查询的结果

● Parser SQL语句解析器
● Optimizer查询优化器，SQL语句在查询之前会使用查询优化器对查询进行优化。就是优化客户端请求query，根据客户端请求的query语句，和数据库中的—些统计信息，在一系列算法的基础上进行分析，得出一个最优的策略，告诉后面的程序如何取得这个query语句的结果。For Example:select uid, name from user where gender = 1;这个select查询先根据where语句进行选取，而不是先将表全部查询出来以后再进行gender 过滤;然后根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤。最后将这两个查询条件联接起来生成最终查询结果
● Caches & Buffers 查询缓存
● Pluggable Storage Engines 存储引擎接口。MySQL区别于其他数据库的最重要的特点就是其插件式的表存储引擎（注意:存储引擎是基于表的，而不是数据库)
● File System数据落地到磁盘上，就是文件的存储

 

和其他数据库相比，MySQL有点与众不同，主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需求选择合适的存储引擎
逻辑架构分层

● 连接层: 最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限
● 服务层: MySQL的核心服务功能层，是MySQL的核心，包括查询缓存，解析器，解析树，预处理器，查询优化器。主要进行查询解析、分析、查询缓存、内置函数、存储过程、触发器、视图等,select操作会先检查是否命中查询缓存，命中则直接返回缓存数据，否则解析查询并创建对应的解析树
● 引擎层: 存储引擎层，存储引擎真正的负责了MysQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，可以根据自己的实际需要进行选取
● 存储层: 数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互

 

 

 

3.存储引擎

show engines;

mysql> show engines;

show variables like 'default_storage_engine%';

mysql> show variables like 'default_storage_engine%';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.01 sec)

 

 

 

4.SQL性能下降的原因

● 查询语句写的差
● 索引失效:索引建了，但是没有用上
● 关联查询太多join(设计缺陷或者不得已的需求)

● 服务器调优以及各个参数的设置（缓冲、线程数等)

 

 

5.SQL执行顺序

 

 

 

 

 

6.七种JOIN理论

 

 

/* 1 */
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key;

/* 2 */
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key;

/* 3 */
SELECT <select_list> FROM TableA A INNER JOIN TableB B ON A.Key = B.Key;

/* 4 */
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key 
WHERE B.Key IS NULL;

/* 5 */
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key 
WHERE A.Key IS NULL;

/* 6 */
-- SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key;
/* MySQL不支持FULL OUTER JOIN这种语法 可以改成 1+2 */
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key
UNION
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key;

/* 7 */
-- SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key 
-- WHERE A.Key IS NULL OR B.Key IS NULL;
/* MySQL不支持FULL OUTER JOIN这种语法 可以改成 4+5 */
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key 
WHERE B.Key IS NULL;
UNION
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key 
WHERE A.Key IS NULL;