CTO之瞳-数据库-MySql

​数据,是所有软件业务系统的根本。业务生长于数据之上,最终又归于数据。数据的设计、处理与保存是否合理,决定了系统的稳固性以及可拓展性。因此,我们先从数据库说起。

1.DBMS

数据库,存储数据的仓库。

假如没有数据库,程序还能写的出来吗?当然可以,早期没有专业数据库的时候,程序员通常把数据放在文件中存进存储介质。但程序员需要自己设计数据的增删改查算法与代码,同时需要自己保证数据的安全性与可靠性。而这一切,耗时费力。

数据库的诞生,把以上关于数据操作的活儿都做了,程序员只要集中精力设计数据库表的结构与字段就可以了。关于数据操作的效率、安全等方面,全部交给专业的数据库设计人员去实现。

因此,通常所说的xxx数据库并不准确,应该称其为xxx数据库管理软件(Database Management Software,DBMS)。数据库,其实是一整套数据管理的软件集合。

2.DBMS的分类

通常度娘出来的DBMS分类为两类:关系型数据库、非关系型数据库(NoSql),个人认为这样的分类已经失去意义。通常所说的非关系型数据库(NoSql)里,包含了文档数据库MongoDB,包含了键值对数据库Redis、Memcached,包含了分布式数据库HBase,还包含了时序数据库InfluxDB,这几种类型个人看来都是可以和关系型数据库并列的。

实际上DB-Engines排名中的分类也是如此区分的,各位可以参考。

https://db-engines.com/en/ranking

每一个分类的定义与解释,由于不是科普类文章,这里就不搬砖了。上面的网页点进去后,有专业的名词解释。

3.使用哪些DBMS?

对于一个中小型业务系统,一般使用MySql + MongoDB可以满足大部分的业务需求。

近来年由于IoT的兴起,如果业务与物联网有关的话,再加一个时序数据库InfluxDB。例如,一些工业管网体系有成千上万个监测点,每个监测点的硬件终端按秒级上报监控状态,用以监控管网的运作情况。在这种场景下,使用传统数据库也许可以完成数据的记录,但是等到要按时间维度做多重数据分析的时候,传统数据库便无法胜任了。

随着业务的发展,如果业务量大到数据库成为瓶颈时,可以考虑使用Memcached之类的缓存技术提升数据读写的性能。

所以个人观点,MySql + MongoDB + InfluxDB +Memcached组合,可以支撑大多数中小型业务系统的设计。

4.MySql

接下来,正式开始MySql相关的内容。

由于不是搬砖文,所以关于MySql的安装使用等基础知识,此文并不会涉及,推荐各位直接阅读菜鸟教程https://www.runoob.com/mysql/mysql-tutorial.html以下所有分析基于开发环境:MySql 5.7.22(Windwos版),IDEA 2019,Windows10, i7-9700F, 8G内存, 东芝固态硬盘相关代码访问路径:https://gitlab.com/ctoeyes/mysql

4.1 MySql – DB Engine之InnoDB与MyISAM

先说一说DB Engine(数据库引擎)的概念,看下图。


数据库引擎是数据库管理软件里直接操控数据库实体硬件存储介质的一环,MySql有多种DB Engine可供选择,常用的有MyISAM、InnoDB。创建一张表时,可以指定DB Engine的类型,默认是InnoDB。

为什么要有多种DB Engine类型呢?因为每一种的特点不一样,针对不同业务用途,可以选择不同的类型。

以InnoDB与MyISAM为例,有三个较大的区别

- InnoDB支持事务(ACID),MyISAM不支持。

- InnoDB行级锁,MyISAM表级锁。

- 两者读写性能与存储空间有差异,请看下面的实验结果。

首先,设计两张一样的表,一张使用InnoDB引擎,另一张使用MyISAM引擎。

建表sql如下:

CREATE TABLE `innodb` ( `A` varchar(32) DEFAULT NULL, `B` int(11) NOT NULL, `C` varchar(32) DEFAULT NULL, `D` int(11) DEFAULT NULL, PRIMARY KEY (`B`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `myisam` ( `A` varchar(32) DEFAULT NULL, `B` int(11) NOT NULL, `C` varchar(32) DEFAULT NULL, `D` int(11) DEFAULT NULL, PRIMARY KEY (`B`)) ENGINE=MyISAM DEFAULT CHARSET=utf8

实验一、单线程连续插入5万条数据。

InnoDB耗时约35秒,MyISAM耗时约2秒。

实验二、单线程连续查询一万次,每次以D列作为查询条件。

InnoDB耗时约75秒,MyISAM耗时约34秒。

由此可见,单线程情况下,MyISAM引擎查询与插入的性能均大大优于InnoDB。

扩展题:多线程,同时插入与查询的情况下,哪一种引擎的表现会更好?

实验三、两张表有5万条相同的数据,它们的空间使用情况如下。

MyISAM的数据以及索引总共占用1.5M左右空间,而InnoDB占用了接近10M。

综上,使用MySql数据库,如果不要求事务性,使用MyISAM引擎性能和空间上更占优势。如果业务读多写少,使用MyISAM也更合适。

例如,中小型公司的内部OA、售后等并发量小,对于延迟不敏感的系统均可以使用MyISAM。

4.2 MySql – 索引与B+树

上一节在查询时,使用了非索引列D作为查询条件。在两张表里,列B与列D的数据是完全一样的,列B被定义为主键(非空索引)。如果使用列B作为查询条件,结果是这样的。

同样1w次查询,InnoDB耗时约700ms,MyISAM耗时约500ms。对比之前的75秒与34秒,是不是有种起飞的感觉?这就是索引的作用。

对于新手来说,如果数据查询性能有问题,先看一看代码里的查询条件是否使用了索引。

那是不是把所有能设为索引的列都设为索引呢?不是。第一个原因:索引都要占用一定的存储空间。第二个原因:大量的索引会导致数据表的更新、插入、删除变慢(需要更新索引)。

索引是什么呢?索引是一种数据结构,MySql里使用的是称之为B+树的数据结构。

假设我们有9条数据,每条数据有4列值,分别为Name、ID、Age、Seat No,其中ID为主键,Seat No又设为了索引。



那么在MySql里(InnoDB引擎),这些数据是长成下图这样的,这里就不做具体的解释了,相信程序猿都看得懂。



虽然在日常开发中并不需要知道MySql的底层数据结构长什么样,但理解每种数据库的数据结构,有助于选择合适的数据库,并合理的设计数据表、主键、索引等。

4.3 MySql – 分库分表

随着表数据以及容量的增加,增删改查的效率都会随之下降。那么当表数据量大到什么程度时,效率会低到无法容忍了呢?网上流传的阿里数据库MySql设计规范里(未证实)有这么一句“单表数据量超过500w或数据容量超过10G考虑分表”。

上面这个分表的参考标准,相信是无数开发、测试与DBA经验的总结,在此并无意质疑。但这个标准一定准确吗?个人并不认为。这个标准里并没有承载数据库的硬件参数作为参考值,同样的库表部署在不同的硬件上,性能是会有很大差异的。下图来源于MySql的官方测试结果,可以看到随着CPU内核数的增加,MySql 5.6的性能是逐渐提升的。


所以,经验值是供参考的,应用时得考虑实际情况,不能照本宣科。

不管怎样,随着业务的发展,总有一天需要通过某些方法去提升数据库的性能。

常见的方法有:

分库:最简单有效的方法。一个业务系统的数据放在一个独立的物理数据库里,比如账户信息、交易信息、物流信息都单独存放在一台实体数据库中,性能一定是杠杠的。当然,成本也一定是最高的。

垂直分表:假设一张表有4个字段,如果业务上经常通过ID查询用户名,而年龄和座位号并不常用,那么可以拆为两张表,如下所示。


这样拆分,如果业务上要查询坐在座位20上的人叫什么名,或者有多少个7岁的Tommy时,本来单表查询可以完成的工作就要采用JOIN语句了。

水平分表:按照ID的奇偶数分为两张不同的表,减少单表的容量。


这样拆分,对于很多业务查询需求,需要采用UNION语句对多张表进行处理。

扩展:曾经被问过一个问题,假设有一张交易订单表,按照时间维度进行水平拆分,由于用户通常会访问最近的交易信息,因此大部分的查询还是命中在同一张表里,如何处理?有些低频用户,隔几个月才交易一次,如果查询此类用户的最近10单交易,需要去多张表甚至多个库访问,查询比较耗时,如何处理?

分库分表,提升了数据增删改查的性能。但由于数据的分离,一段业务逻辑需要使用的数据原本从一张表里就可以拿到,现在可能要去不同库的好几张表里获取,增加了代码编写的复杂度。针对这种情况,一类数据库中间件应运而生,代表性的有MyCat。

MyCat可以看做是一个内部服务,业务系统对于数据的操作保持不变,sql语句发到MyCat,由MayCat解析后去不同的数据库和数据表操作,然后把操作结果返回给业务系统。

不过,在这里想说的是,如果业务数据分库分表复杂到需要使用MyCat之类的中间件时,MySql可能就不是一个好的数据库选择去应对业务的发展了。更正确的方向,应该考虑使用一种新的数据库去处理增长的业务数据需求。

4.4 MySql – MySql Router

在分库分表、使用MyCat之前,建议先考虑MySql DBMS中一个原生的轻量级中间件:MySql Router。

使用MySql Router组一个简单的2-3台主从机策略,可以方便快捷的达到以下几个目的

- 提升性能

- 数据备份

- 故障热切换

- 负载均衡

- 读写分离

好了,关于MySql就写到这里,下一篇《数据库-MongoDB》。

写在最后的一些话:以上关于MySql的这些内容并不复杂,基本上是一个初中级的程序员应该掌握的,而在DBA眼里就更ez了。写这些内容,是想说明几个道理,如果想做一个好的程序员并且成长为架构师甚至CTO,需要

- 具备独立思考的能力,而不光是学(chao)习(xi)的能力。技术种类有很多,各类观点也很多,怎样结合实际业务需求,做出准确的判断需要我们独立思考(当然交流也是必须的)。如果我们只是听前辈的言传身教,学到的永远只是皮毛。就好比婴儿一直吃母亲嚼碎的食物,没法具备自己咀嚼的能力。

- 纸上得来终觉浅,绝知此事要躬行。听别人说的,看别人写的,不如自己做几个实验,画几张图理解的更深刻。

- 要想脱离CRUD,需要系统的学习,而不是各大论坛网站东拼西凑。