下面是目录可跳转对应页面学习；

1. 简介

• • 1.1 安装
  • 1.2 MySQL逻辑架构
  • • 1.2.1 存储引擎

  博客地址：地址

• 2. 索引优化分析

• • 2.1 原因
  • 2.2 常见通用的join查询
  • 2.3 索引
  • • 2.3.1 索引分类
    • 2.3.2 索引结构
    • 2.3.3 索引情况
  • 2.4 性能分析
  • • 2.4.1 id（查询序列号）
    • 2.4.2 select_type（查询类型）
    • 2.4.3 type（访问类型）
    • 2.4.4 possible_keys 、key 和 key_len（可能用到索引、实际用到索引、长度）
    • 2.4.5 ref（条件查询）
    • 2.4.6 rows（行数）
    • 2.4.7 explain例子分析
  • 2.5 优化分析
  • • 2.5.1 索引单表优化
    • 2.5.2 索引两表优化
    • 2.5.3 索引三表优化
  • 2.6 索引失效
  • 2.7 面试常考
  • 2.8 总结

  博客地址：地址

• 3. 查询截取分析

• • 3.1 in和exists
  • 3.2 OrderBy优化
  • 3.3 GroupBy优化
  • 3.4 慢查询日志
  • 3.5 批量插入数据脚本
  • 3.6 Show Profile
  • 3.7 全局查询日志

• 4. 锁机制

• 5. 主从复制

  博客地址：[地址]

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=333 \
-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
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

1.2.安装位置

视频地址：视频

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

docker exec -it mysql /bin/bash
Linux环境下MySQL的安装目录。
1
2

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)
MySQL5.7配置文件位置是/etc/my.cnf或者/etc/mysql/my.cnf，如果字符集不是utf-8直接进入配置文件修改即可。

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

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

# 数据库默认字符集
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

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

1.4.配置文件

MySQL配置文件讲解：https://www.cnblogs.com/gaoyuechen/p/10273102.html

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文件的独立表空间。 共享表空间以及独立表空间都是针对数据的存储方式而言的。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

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

2.MySQL逻辑架构

MySQL逻辑架构

视频地址：https://www.bilibili.com/video/BV1KW411u7vy?p=8&spm_id_from=pageDriver&vd_source=2e82c2a7903cd83d3a5ab9e6d3195382

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

逻辑架构分层

MySQL逻辑架构

连接层：最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

服务层：MySQL的核心服务功能层，该层是MySQL的核心，包括查询缓存，解析器，解析树，预处理器，查询优化器。主要进行查询解析、分析、查询缓存、内置函数、存储过程、触发器、视图等，select操作会先检查是否命中查询缓存，命中则直接返回缓存数据，否则解析查询并创建对应的解析树。

引擎层：存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。

存储层：数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

3.存储引擎

• 查看mysql以提供什么存储引擎 show engines;
  
  

• 查看mysql当前默认的存储引擎 show variables like '%storage_engine%';

• myIsam只关注性能，是因为他只能查询，查询后不用提交事务\
• 而innodb关注事务，比如增删改，都要手动提交事务\
• 不缓存真实数据，所以要查询的时候比较慢，而缓存了真实数据，查询就比较快