文章目录
Mysql
概述
Mysql是一种关系型数据库，可以很好的描述对象的之间的关系，属于SQL。
而我之前的也学了NO SQL --redis，它采用键值对的表达方式，有五种类型：String,List,Set,Hash,Zset(Sort Set)，是典型的缓存式数据库。
还有阿里巴巴开发的Druid，是一款应用于监控sql动态的数据库。
写博客前想记录的内容
本次尚硅谷周阳老师采用CentOS7搭建mysql,需要在官网下载rmp压缩包，然后进行解压安装等一系列操作。由于我之前学了docker,所以果断采用docker拉取mysql镜像，是真的好用。
对于mysql的配置文件可以在生成的mysql容器后，在终端使用docker命令：
docker exec -it 容器id bash
即可进入容器的所在目录，进行相关的配置。
特贴上mysql路径表格，仅供参考：
Mysql的安装位置
| 参数 | 路径 | 解释 | 备注 |
|---|---|---|---|
| –datadir | /var/lib/mysql/ | mysql 数据库文件的存放路径 | |
| –basedir | /usr/bin | 相关命令目录 | mysqladmin mysqldump 等命令 |
| –plugin-dir | /usr/lib64/mysql/plugin | mysql 插件存放路径 | |
| –log-error | /var/lib/mysql/jack.atguigu.err | mysql 错误日志路径 | |
| –pid-file | /var/lib/mysql/jack.atguigu.pid | 进程 pid 文件 | |
| –socket | /var/lib/mysql/mysql.sock | 本地连接时用的 unix 套接字文件 | |
| /usr/share/mysql | 配置文件目录 | mysql 脚本及配置文件 | |
| /etc/init.d/mysql | 服务启停相关脚本 |
**补充：**根据本人使用情况，以上路径大部分适用docker，少部分不适用，例如我的配置文件路径是在：/etc/mysql
MySQL配置文件部分内容
解决字符集乱码，请在配置文件中加入
[client]
default-character-set=utf8
[mysqld]
character_set_server=utf8
character_set_client=utf8
collation-server=utf8_general_ci
[mysql]
default-character-set=utf8
配置文件中的其他配置：
- 二进制日志log-bin：主从复制。
- 错误日志log-error：默认是关闭的，记录严重的警告和错误信息，每次启动和关闭的详细信息等。
- 查询日志log：默认关闭，记录查询的sql语句，如果开启会减低mysql的整体性能，因为记录日志也会消耗系统资源
- frm文件：存放表结构。
- myd文件：存放表数据。
- myi文件：存放表索引。
MySQL逻辑架构
和其它数据库相比，MySQL 有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在 存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。
1.连接层
最上层是一些客户端和连接服务，包含本地 sock 通信和大多数基于客户端/服务端工具实现的类似于 tcp/ip 的 通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证 安全接入的客户端提供线程。同样在该层上可以实现基于 SSL 的安全链接。服务器也会为安全接入的每个客户端验 证它所具有的操作权限。
2.服务层
| Management Serveices & Utilities | 系统管理和控制工具 |
|---|---|
| SQL Interface: | SQL 接口。接受用户的 SQL 命令，并且返回用户需要查询的结果。比如 select from 就是调用 SQL Interface |
| Parser | 解析器。 SQL 命令传递到解析器的时候会被解析器验证和解析 |
| Optimizer | 查询优化器。 SQL 语句在查询之前会使用查询优化器对查询进行优化，比如有 where 条件时，优化器来决定先投影还是先过滤。 |
| Cache 和 Buffer | 查询缓存。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取 数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key 缓存， 权限缓存等 |
3.引擎层
存储引擎层，存储引擎真正的负责了 MySQL 中数据的存储和提取，服务器通过 API 与存储引擎进行通信。不同 的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。
4.存储层
数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。
存储引擎简介
(Mysql:5.5之后)常用的存储引擎，MyISAM和InnoDB(提供事务、外键和行锁)
//两种查看存储引擎的命令
show engines;
show variables like '%storage_engine%';//推荐
MyISAM和InnoDB对比图：
SQL性能慢，执行效率差
主要表现为：执行时间长，等待时间长。
主要问题是：
- 查询语句写得差
- 索引失效
- 关联查询太多join(设计缺陷或不得已的需求)
- 服务器调优及各个参数的设置(缓冲、线程数等)
MySQL大致查询流程
mysql 的查询流程大致是：
mysql 客户端通过协议与 mysql 服务器建连接，发送查询语句，先检查查询缓存，如果命中，直接返回结果， 否则进行语句解析,也就是说，在解析查询之前，服务器会先访问查询缓存(query cache)——它存储 SELECT 语句以及 相应的查询结果集。如果某个查询结果已经位于缓存中，服务器就不会再对查询进行解析、优化、以及执行。它仅 仅将缓存中的结果返回给用户即可，这将大大提高系统的性能。
语法解析器和预处理：首先 mysql 通过关键字将 SQL 语句进行解析，并生成一颗对应的“解析树”。mysql 解析 器将使用 mysql 语法规则验证和解析查询；预处理器则根据一些 mysql 规则进一步检查解析树是否合法。
查询优化器当解析树被认为是合法的了，并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式， 最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。。
然后，mysql 默认使用的 BTREE 索引，并且一个大致方向是:无论怎么折腾 sql，至少在目前来说，mysql 最多只 用到表中的一个索引。
**SQL执行加载顺序：**随着 Mysql 版本的更新换代，其优化器也在不断的升级，优化器会分析不同执行顺序产生的性能消耗不同而动态调整执行顺序。下面是经常出现的查询顺序：
复习Join查询
**注意：**mysql不支持全外连接(full outer join)，但是可以将左外连接和右外连接的结果集通过union结合在一起来解决。
索引
-
定义：索引（Index）是帮助 MySQL 高效获取数据的数据结构（BTREE）。可以简单理解为排好序的快速查找数据结构(排序+查找)。
-
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。
-
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。
-
我们平常所说的索引，如果没有特别指明。都是指BTREE(多路搜索树，不一定是二叉树)结构组织的索引，其中聚集索引、次要索引、复合索引、前缀索引、唯一索引默认都是使用B+树索引，统称索引。当然除了B+树外，还有哈希索引等。
优点：
- 提高数据检索的效率，降低数据库的IO成本。
- 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。
缺点：
-
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为 更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
-
实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。
单值索引：一张表只给某个字段建立一个索引。（只用了该表的一个字段来建索引）一个表可以有多个单列索引。
create index idx_user_name on user(name)
唯一索引：索引列的值必须唯一，但允许有空值。
复合索引：一张表给多个字段建立一个索引。
create index idx_user_nameEmail on user(name,email)
**注意：**推荐一张表建立的索引不超过5个。当前5.5版本下，mysql的优化器在同一时间段只会用到一个索引。
主键是一种唯一索引，只不过不能为NULL。
Mysql的索引结构：
- BTREE
- Hash索引
- full-text全文索引
- R-TREE索引
索引的创建时机
适合创建索引的情况：
- 主键自动建立唯一索引；
- 频繁作为查询条件的字段应该创建索引；
- 查询中与其它表关联的字段，外键关系建立索引；
- 单键/组合索引的选择问题， 组合索引性价比更高；
- 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度；
- 查询中统计或者分组字段；
不适合创建索引的情况：
- 表记录太少；
- 经常增删改的表或者字段；
- Where 条件里用不到的字段不创建索引；
- 过滤性不好的不适合建索引；
Explain
使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的。分析你的查询语句或是表结构的性能瓶颈。
用法： Explain+SQL 语句。
一下展示列表的属性：
- id
含义：select 查询的序列号,包含一组数字，表示查询中执行 select 子句或操作表的顺序。
三种情况：- id相同：执行顺序由上至下；
- id不同：如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行；
- 有相同也有不同：id 如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id 值越大，优先级越高，越先执行衍生 = DERIVED；
**关注点：**id 号每个号码，表示一趟独立的查询。一个 sql 的查询趟数越少越好。
- select_type
select_type 代表查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。
- table
这个数据是基于哪张表的。
- type
type 是查询的访问类型。是较为重要的一个指标，结果值从最好到最坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ，一般来说，得保证查询至少达到 range 级别，最好能达到 ref。
最好能记住：system > const > eq_ref > ref > range >index > ALL
-
system：
表只有一行记录（等于系统表），这是 const 类型的特列，平时不会出现，这个也可以忽略不计；
-
const：
表示通过索引一次就找到了,const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据，所以很快 如将主键置于 where 列表中，MySQL 就能将该查询转换为一个常量；
-
eq_ref：
唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描；
-
ref：
非唯一性索引扫描，返回匹配某个单独值的所有行.本质上也是一种索引访问，它返回所有匹配某个单独值的行， 然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
-
range：
只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的 where 语句中出现 了 between、<、>、in 等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而 结束语另一点，不用扫描全部索引。
-
index：
出现index是sql使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组。index只遍历索引树，快是因为索引文件通常比数据文件小。
-
ALL：
Full Table Scan，将遍历全表以找到匹配的行。
-
possible_keys
显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
- key
实际使用的索引。如果为NULL，则没有使用索引。
- key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。 key_len 字段能够帮你检查是否充分的利用上了索引。ken_len 越长，说明索引使用的越充分。在保证精确性的前提下，长度越短越好，且该长度只表示最大可能长度。
- ref
显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。
- rows
rows 列显示 MySQL 认为它执行查询时必须检查的行数。越少越好！
- Extra
其他的额外重要的信息。
-
Using filesort
说明 mysql 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL 中无法利用索引 完成的排序操作称为“文件排序”。
-
Using temporary
使了用临时表保存中间结果,MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
-
Using index
Using index 代表表示相应的 select 操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！ 如果同时出现 using where，表明索引被用来执行索引键值的查找;如果没有同时出现 using where，表明索引只是 用来读取数据而非利用索引执行查找。
利用索引进行了排序或分组。
-
Using where
表明使用了 where 过滤。
-
Using join buffer
使用了连接缓存。
-
impossible where
where 子句的值总是 false，不能用来获取任何元组。
-
select tables optimized away
在没有 GROUPBY 子句的情况下，基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUNT(*)操 作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HHPQZAOw-1631865330764)(https://ss0.bdstatic.com/70cFvHSh_Q1YnxGkpoWK1HF6hhy/it/u=2071901845,426147965&fm=11&gp=0.jpg “MySQL逻辑架构图”)]](https://ss0.bdstatic.com/70cFvHSh_Q1YnxGkpoWK1HF6hhy/it/u=2071901845,426147965&fm=11&gp=0.jpg){kind=link}