mysql-架构_一张自增表里面总共有 7 条数据,删除了最后 2 条数据,重启 mysql 数据库,又插入了

一、 MySQL逻辑架构

1.MySQL整体逻辑架构

参考架构图1:

参考架构图2:

第一层，即最上一层，所包含的服务并不是MySQL所独有的技术。它们都是服务于C/S程序或者是这些程序所需要的 ：连接处理，身份验证，安全性等等。

第二层值得关注。这是MySQL的核心部分。通常叫做 SQL Layer。
在 MySQL据库系统处理底层数据之前的所有工作都是在这一层完成的，包括权限判断， sql解析，行计划优化， query cache 的处理以及所有内置的函数(如日期,时间,数学运算,加密)等等。各个存储引擎提供的功能都集中在这一层，如存储过程，触发器，视 图等。

第三层包括了存储引擎。
通常叫做StorEngine Layer ，也就是底层数据存取操作实现部分，由多种存储引擎共同组成。它们负责存储和获取所有存储在MySQL中的数据。就像Linux众多的文件系统 一样。每个存储引擎都有自己的优点和缺陷。服务器是通过存储引擎API来与它们交互的。这个接口隐藏 了各个存储引擎不同的地方。对于查询层尽可能的透明。这个API包含了很多底层的操作。如开始一个事 物，或者取出有特定主键的行。存储引擎不能解析SQL，互相之间也不能通信。仅仅是简单的响应服务器 的请求。

2.sql执行流程

一条 SQL 语句执行时，总结起来大概分为以下几个步骤：

1.若查询缓存打开则会优先查询缓存，若命中则直接返回结果给客户端。
2.若缓存未命中，此时 MySQL 需要搞清楚这条语句需要做什么，则通过分析器进行词法分析、语法分析。
3.搞清楚要做什么之后，MySQL 会通过优化器对 SQL 进行优化，生成一个最优的执行计划
4.最后通过执行器与存储引擎提供的接口进行交互，将结果返回给客户端

在 MySQL 执行过程中，优化器可能会对我们即将要执行的 SQL 进行改造，改造思路如下：

1.根据搜索条件，找出 SQL 中所有可能使用的索引
2.然后计算全表扫描的成本开销
3.接着计算使用不同索引执行查询的成本开销
4.最后会对比各种执行方案的成本开销，找出开销值最小的那一个

二、存储引擎

1.MyISAM引擎：

1.不支持事务，但是每次查询都是原子的；
2.支持表级锁，即每次操作是对整个表加锁；
3.采用菲聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。
4.存储表的总行数；
5.一个MYISAM表有三个文件：索引文件、表结构文件、数据文件；

2.InnoDB引擎：

1.支持ACID的事务，支持事务的四种隔离级别；
2.支持行级锁及外键约束：因此可以支持写并发；
3.主键索引采用聚集索引（索引的数据域存储数据文件本身），辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整。
4.不存储总行数；
5.一个InnoDb引擎存储在一个文件空间（共享表空间，表大小不受操作系统控制，一个表可能分布在多个文件里），也有可能为多个（设置为独立表空，表大小受操作系统文件大小限制，一般为2G），受操作系统文件大小的限制；

总结：

MyISAM适用于读密集的设计，InnoDB适用于写密集的设计

@see MySql两种存储引擎的区别https://www.cnblogs.com/wangdake-qq/p/7358322.html

三、性能监控

1.QPS

一台 MySQL 数据库，大致处理能力的极限是，每秒一万条左右的简单 SQL，这里的“简单 SQL”，指的是类似于主键查询这种不需要遍历很多条记录的 SQL。根据服务器的配置高低，可能低端的服务器只能达到每秒几千条，高端的服务器可以达到每秒钟几万条，所以这里给出的一万 TPS 是中位数的经验值。考虑到正常的系统不可能只有简单 SQL，所以实际的 TPS 还要打很多折扣。

我的经验数据，一般一台 MySQL 服务器，平均每秒钟执行的 SQL 数量在几百左右，就已经是非常繁忙了，即使看起来 CPU 利用率和磁盘繁忙程度没那么高，你也需要考虑给数据库“减负”了

2.表大小

你在编写一条查询语句的时候，可以依据你要查询数据表的数据总量，估算一下这条查询大致需要遍历多少行数据。

如果遍历行数在百万以内的，只要不是每秒钟都要执行几十上百次的频繁查询，可以认为是安全的。

遍历数据行数在几百万的，查询时间最少也要几秒钟，你就要仔细考虑有没有优化的办法。

遍历行数达到千万量级和以上的，我只能告诉你，这种查询就不应该出现在你的系统中。当然我们这里说的都是在线交易系统，离线分析类系统另
说。遍历行数在千万左右，是 MySQL 查询的一个坎儿。MySQL 中单个表数据量，也要尽量控制在一千万条以下，最多不要超过二三千万这个量级。原因也很好理解，对一个千万级别的表执行查询，加上几个 WHERE 条件过滤一下，符合条件的数据最多可能在几十万或者百万量级，这还可以接受。但如果再和其他的表做一个联合查询，遍历的数据量很可能就超过千万级别了。

所以，每个表的数据量最好小于千万级别。

三、核心技术

1.常见日期格式

date和datetime类型区别

区别1：

①date类型可用于需要一个日期值而不需要时间部分时；

②datetime类型:可用于需要同时包含日期和时间信息的值。

区别2：

①date：MySQL 以 ‘YYYY-MM-DD’ 格式检索与显示date值；

②datetime:MySQL 以 'YYYY-MM-DD HH:mm:ss’格式检索与显示 DATETIME 类型。

区别3：

①date类型：支持的范围是 ‘1000-01-01’ 到’9999-12-31’；

②datetime类型:支持的范围是’1000-01-0100:00:00’ 到 ‘9999-12-3123:59:59’。

2.mysql驱动版本对比

老版本驱动，一般指5.x版本：

driverClassName: com.mysql.jdbc.Driver
1

新版本驱动，一般指8.x版本：

driverClassName: com.mysql.cj.jdbc.Driver
1

总结：使用mysql数据库，代码中使用的数据库版本要和数据库服务版本保持一致，并且数据库驱动准确无误才可以正常创建连接

SQL关键字的执行顺序

1)Mysql语法顺序，即当sql中存在下面的关键字时，它们要保持这样的顺序：

select[distinct]
from
join（如left join）
on
where
group by
having
union
order by
limit

1
2
3
4
5
6
7
8
9
10
11

2)Mysql执行顺序，即在执行时sql按照下面的顺序进行执行：

from  
on  
join  
where  
group by  
having  
select  
distinct  
union  
order by  
1
2
3
4
5
6
7
8
9
10

@see Mysql 语句执行顺序 https://blog.csdn.net/jintao_ma/article/details/51253356#

where条件顺序

where条件默认是从左往右的，当然mysql引擎默认会先检索索引字段，但是从编码习惯上还是尊从区分度大的字段靠左

mysql中in 和exists 区别。

exists：
exists对外表用loop逐条查询，每次查询都会查看exists的条件语句，当 exists里的条件语句能够返回记录行时(无论记录行是的多少，只要能返回)，条件就为真，返回当前loop到的这条记录，反之如果exists里的条 件语句不能返回记录行，则当前loop到的这条记录被丢弃，exists的条件就像一个bool条件，当能返回结果集则为true，不能返回结果集则为 false

not exists：
not exists与exists相反，也就是当exists条件有结果集返回时，loop到的记录将被丢弃，否则将loop到的记录加入结果集

in
in是把外表和内表做hash连接，先查询内表，再把内表结果与外表匹配，对外表使用索引（外表效率高，可用大表），而内表多大都需要查询，不可避免，故外表大的使用in，可加快效率。

@see MySQL中exists和in的区别及使用场景 https://www.cnblogs.com/xiaoxiong-kankan/p/7928153.html

思考

1.一张自增表里面总共有 7 条数据，删除了最后 2 条数据，重启 MySQL 数据库，又插入了一条数据，此时 ID 是几？

6
一般情况下，我们创建的表的类型是InnoDB，如果新增一条记录（不重启mysql的情况下），这条记录的id是8；但是如果重启（上文中提到的）MySQL的话，这条记录的ID是5。因为InnoDB表只把自增主键的最大ID记录到内存中，所以重启数据库或者对表OPTIMIZE操作，都会使最大ID丢失。
但是，如果我们使用表的类型是MylSAM，那么这条记录的ID就是8。因为MylSAM表会把自增主键的最大ID记录到数据文件里面，重启MYSQL后，自增主键的最大ID也不会丢失。

@see Java面试题（四）：数据库 https://www.jianshu.com/p/4ff8add187a4

2. limit深度分页问题

普通的limit m,n，m表示偏移量，n表示返回条数，当m偏移量很大时，就需要扫描过多的表数据，例如limit 1000000,100，查询就需要扫描1000100条，然后舍弃掉不符合条件的前1000000条，效率自然也就低了。

我们大概有3种方式来解决limit分页慢的问题，请根据自己的数据量和业务需求进行选择：
1、id连续的情况下，直接用where id>500的方式来解决
2、id不连续的情况下，使用select id from test limit 5000000,1来获取limit起始值，但是实际测试发现效果不大
3、id不连续的情况下，新建一个order_no字段用来计算起始值，效率很高，但是需要解决order_no更新的问题
4、限制查询页数

参考：数据量大时limit分页慢的问题https://www.jianshu.com/p/b40852891fb0

3.慢查询优化思想？

在业务系统中，除了使用主键进行的查询，其他的都会在测试库上测试其耗时，慢查询的统计主要由运维在做，会定期将业务中的慢查询反馈给我们。

慢查询的优化首先要搞明白慢的原因是什么？是查询条件没有命中索引？是load了不需要的数据列？还是数据量太大？

所以优化也是针对这三个方向来的。

1.首先分析语句，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。

2.分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。

3.如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表。

参考文献

1.mysql中文官网 https://www.mysqlzh.com/
1.oracle中if/else功能的实现的3种写法 https://www.cnblogs.com/yangzhilong/archive/2013/04/03/2998282.html
2.mysql 概念和逻辑架构 https://www.cnblogs.com/andy6/p/5789254.html
3.MySql两种存储引擎的区别 https://www.cnblogs.com/wangdake-qq/p/7358322.html