单机存储引擎到mysql的思考一

ゝ一纸荒年。 2023-07-18 14:01 12阅读 0赞

> 大家都知道mysql现在很火，很多招后端工程师的要求其中一项就是  
> 精通`mysql`  
> 但是我们真的了解mysql吗？那么笔者要下面卖膏药了~

<table> 
 <thead> 
  <tr> 
   <th>标题</th> 
   <th>网址</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>单机存储引擎到mysql的思考一</td> 
   <td><a href="https://blog.csdn.net/xk4848123/article/details/105166855" rel="nofollow">https://blog.csdn.net/xk4848123/article/details/105166855</a></td> 
  </tr> 
  <tr> 
   <td>单机存储引擎到mysql的思考二</td> 
   <td><a href="https://blog.csdn.net/xk4848123/article/details/105195921" rel="nofollow">https://blog.csdn.net/xk4848123/article/details/105195921</a></td> 
  </tr> 
  <tr> 
   <td>单机存储引擎到mysql的思考三</td> 
   <td><a href="https://blog.csdn.net/xk4848123/article/details/105207797" rel="nofollow">https://blog.csdn.net/xk4848123/article/details/105207797</a></td> 
  </tr> 
 </tbody> 
</table>

笔者在大规模分布式存储系统笔记三中阐述了三种存储引擎大家还记得吗？哈希存储引擎、B树存储引擎 、LSM树存储引擎。在看到B树存储引擎时突然想到了mysql。

## mysql简史 ##

MySQL的历史可以追溯到1979年，一个名为Monty Widenius的程序员在为TcX的小公司打工，并且用BASIC设计了一个报表工具，使其可以在4MHz主频和16KB内存的计算机上运行。当时，这只是一个很底层的且仅面向报表的存储引擎，名叫Unireg。  
Monty为满足客户的一个项目需求，毅然重新设计整个系统，1995年5月23日MySQL的第一个内部版本发行了，1996年对外公布了官方正式发行版(3.11.1)。  
2005年10月，发布了里程碑的MySQL 5.0版本. 在5.0中加入了游标，存储过程，触发器，视图、查询优化以及分布式事务等，实现了"正常数据库管理系统"应当拥有的一整套功能。至此，MySQL明确地表现出向高性能数据库发展的步伐。  
2009年04月Oracle以74亿美元收购Sun 公司，MySQL 转入Oracle 门下。  
再后来出现了比较有里程碑意义的5.5，到现在已经mysql8了。

## mysql是什么（结构） ##

我们以mysql默认引擎InnoDB来说，独立表空间的情况下每张表在磁盘上都对应一个.frm文件（表结构定义）和一个.ibd文件（数据文件），**一张表数据文件本身就是按 B+Tree 组织的一个索引结构,这棵树的叶点data 域保存了完整的数据记录**。这个索引的 key 是数据表的主键,因此 InnoDB 表数据文件本身就是主索引。mysql建议**使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置**,当一页写满,就会自动开辟一个新的页。这样就会形成一个紧凑的索引结构,近似顺序填满。由于每次插入时也不需要移动已有数据,因此效率很高,也不会增加很多开销在维护索引上。那么单张表mysql到底存放多少数据呢，肯定不是无上限的，因为，**MySQL 为了提高性能，会将表的索引装载到内存中。InnoDB buffer size 足够的情况下，其能完成全加载进内存，查询不会有问题。** 但是，当单表数据库到达某个量级的上限时，导致内存无法存储其索引，使得之后的 SQL 查询会产生磁盘 IO，从而导致性能下降。阿里巴巴《Java 开发手册》提出单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。**那么为什么不用hash结构而采用B+树呢**？大家都都知道hash的效率是O(1),而B+树是O(longdN),d是出度（分叉度），但是O(1)针对的是单条数据精确查询，我们系统常常是要查某个范围的数据，between,<>,>,<,order by 云云，这时候有序的B+树才能使查询更高效。**B+树相对B-树的优点**，**矮胖**（`非叶子节点不存储data，这样一个节点就可以存储更多的key。`）即减少IO次数，所有叶子结点组成一个**单向链表**，方便范围查询。平常我们开发过程中写sql一定要注意利用好索引，where 条件、order by条件以及group by根据实际出现频率考虑是否用索引，还有注意覆盖索引（就是你查的数据正好是索引）的使用，我们来看看为什么查的数据正好索引为什么就快。  
![借用一张图][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hrNDg0ODEyMw_size_16_color_FFFFFF_t_70]  
看看上图中的InnoDB辅助索引（非聚簇索引也就是一般索引非主键），检索过程一目了然，如果查行数据还得再多IO几次，当然就慢了。  
看看下面一个示例  
超大分页查询优化,类似于`select * from table where age > 20 limit 1000000,10`这种查询其实也是有可以优化的余地的.。这条语句需要load1000000数据然后基本上全部丢弃,只取10条当然比较慢。 当时我们可以修改为`select * from table where id in (select id from table where age > 20 limit 1000000,10)`。这样虽然也load了一百万的数据,但是由于索引覆盖,要查询的所有字段都在索引中,所以速度会很快。  
通常我们经常说尽量不要**全表扫描**，那什么是全表扫描呢，还记得之前我们是B+树的叶子节点是单向链表吗，全表扫描，其实就从这个链表中从前往后搜索出符合条件(比如非索引字段age)的记录。

## mysql并发 ##

通常我们一个项目会有一个连接池(dbcp,c3p0,druid,HikariCP)连接着mysql或者多个项目都在连接一个数据库，都在同时操作一个数据库或者一张表，那么为什么**mysql的数据不会乱呢**。ACID？那么背后到底是谁在操纵呢。在思考二中我讲继续阐述，谢谢。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hrNDg0ODEyMw_size_16_color_FFFFFF_t_70]: /images/20230528/83f36be980e24778b339270d33a2113d.png