00:00
好,那么我们在讲完这个MYSQ服务器的这个优化措施之后呢,咱们来看一看这个数据库结构的一个优化呢,具体有哪些方案啊,首先在这里提到说一个好的数据库设计方案,对于数据库性能的提升往往起到事半功倍的效果啊,说的很好是吧?那我们看看具体都有哪些措施呢,能让我们达到这个事半功倍呢?啊OK,首先呢,我们看第一个叫做拆分表,冷热数据的分离,哎冷热数据什么意思呀?哎这块呢,我们首先呢,这样范范一说这呢是一张表有好多字段对吧?那结果呢,我们发现呢,有一些字段的话,我们经常呢在SQL语句当中啊去使用,经常查询,而有一些字段的话呢,我们很少去使用啊,就可以分成了叫热数据和冷数据了,对吧?那我们就可以呢,考虑把这个表呢,诶这样拆分一下,一个表变成两个表啊热数据呢构成一张表,冷数据呢构成一张表,当然了,你这个主键的话呢,如果在这儿这个主见呢,在这里边要写一下,在这里边也要写一下。好,那么这样的话呢,诶,你平时呢,要是经常使用热数据呢,是不是直接查询这个表就可以了,冷数据呢,很少用啊,你要用的话呢,就从这,哎这个随时呢从这调一下就行,那么这样做的好处是什么呢?啊第一个说减少了磁盘IO,保证这个热数据的内存啊,缓存的一个命中率啊,就是相当于呢咱们啊原来呢存储这个。
01:11
B加数啊,聚速缩引B加数的叶子节点的时候呢,原来呢,你这些字段全都要存,现在的话呢,是不是只需要存我们这个热数据就可以了啊,那现在的我们这个页里边呢,是不是加载的这个数据量就会大一些,那导致呢,我们往内存中加载的时候呢,诶我们这一个页里边呢,是不是可以存储更多的数据了啊,就减少磁盘IO了,对吧?同时的话呢,我们可以更有效的去利用这个缓存,因为你要把数据都缓存起来的话,有些冷数据不用,那你没必要给他缓存啊,提高了这个缓存的一个利用率是吧。啊,这呢,就我们说这个点下边呢,举了个例子,针对这个会员的这个呢,诶我们把这个数据呢,就分成了热数据和这个冷数据,那热数据呢,比如像ID username password,最近的一次登录时间啊,最近一次登录的这个I啊,这样的是热数据,数据呢,就是它的这个I,这个I是必须的了,是吧?然后telephone和这个description啊,它这个描述信息啊,就这样就可以了。
02:04
好,那如果说呢,我们现在需要查询一个用户的username和他的这个telephone,那怎么办呢?那这时候呢,就进行一个多表的连接就可以了啊,那么这个连接条件呢,就是我们这个表中的这个主键呢,诶都在这两个表里边儿呢,会去出现啊,让这个ID呢相同就可以了。好,这呢就是我们说的第一个措施啊,然后呢,第二个措施呢,叫增加这个中间表啊,增加中间表它呢针对的是我们这个联合查询来讲的啊,比如这一张表,这一张表,然后我们经常性的是从这里边,呃,从这个表里边呢,用这俩字段,这个表里边呢用这俩字段啊老师这样的去照这个操作说,那不妨呢,我们就诶造一张中间表,然后把这个两个字段啊跟这两个字段呢,是不是取出来,然后呢,诶构成一张表,然后呢,这表里没有数据怎么办呢?诶然后你再去隐私这个表啊,把数据呢,从这个里边到这里边再给他复制过来就可以了。那就可以了是吧,说原来呢,进行的这个联合查询,现在我们改成了对中间表进行查询就可以了,提高查询的一个效率。啊,一个效率下边呢,举了一个例子,比如针对于这个,诶这个叫班级表和我们这个student这两个表。
03:06
假设的话呢,我们想查询这个学生啊,他的这个ID啊,然后他的这个name啊,以及呢,他这个班级的这个class name,还有这个班长啊这几个字段,然后我们就可以呢,构建这样的一张表。是吧,哎,构建这样一样的表。哎,OK,然后构建完以后的话呢,我们再往这个表里边是不是添加数据啊,哎,你可以呢,就隐私这样的方式,然后select啊,就是我们上面两个表呢,做一个联合的一个查询。是吧,诶多表的连接啊就可以了,哎,就有数据了,行OK,那么这样呢,完事以后的话呢,后续的问题是什么呀,是不是一定要保证我们这个数据的一个一致性啊啊你比如说我们这个呃,学生表或者我们这个班级表呢,进行了数据的一个修改,那么同步的话呢,我们这张表是不是也要修改,那怎么办呀?啊,那常用的一种方式呢,就是大家把这个一旦呢上面数据修改以后呢,你把这个表上的数据呢,可以都清空掉,然后呢,再重新的做一个insert。啊,这样的一个行为那就可以了,当然了,你不能频繁的去修改是吧,频繁修改呢,每次呢这块呢,好歹清空,然后再加进来,那这个成本也挺高的啊,就是所以针对这种呃,不是特别频繁更新的这种表呢,我们建立这种中间表呢,是比较有效的啊,这个大家注意一下啊。
04:13
好,然后第三个方式呢,叫增加这个冗余字段的这种方式。啊,你看上面这个还有个视图,哎视图呢,大家就诶可以考虑就别用了啊,因为视图呢,本身来讲,其实还是对于我们现有这个表的,呃,SQ语句的一个封装是吧,哎,查询效率上来讲呢,其实没有特别实质性的一个变化啊啊下边这个我们提到叫增加这个冗音的字段。啊,这呢,就是咱们前面讲的这个反范式化的一个意思。啊,这个不用多说了是吧,诶你增加一个容易字段,原来呢,多表的一个查询,现在就不用了是吧?哎,就这样个情况啊好,然后呢,下边呢,提到这个优化数据类型,哎,优化数据类型呢,咱们其实呢,在讲到这个上篇的时候。嗯,这是咱们的记录片吧,对,在咱们讲到这个上面的时候呢,是不是提到过一张叫MYSQL数据类型的一个精讲,对吧,然后在这里边呢,咱们把那个数据类型啊,该怎么去选择,其实呢,也讲过了啊,这块我们在呃针对性的再说一说啊呃,总体的原则呢,就是我们优先选择符合存储需要的最小的数据类型。
05:16
啊,能小就不要大啊,节省空间吧,是吧?啊这里边提到说你要用的这个字段越大的话呢,呃,建立所引所需要的空间呢,也就越大啊这样的话呢,我们这个,嗯,你这一个页当中呢,你放的这个索引的这个数量呢,是不是就越少,便利的时候L的次数就越多,那索引的性能就越差。啊,连锁反应是吧,啊,总感觉好像说性能差就是因为你这个字段呃大造成的啊,其实呢,这只是其中的一个原因而已啊。好呃,那么具体来讲呢,我们该如何呢去选择呢?首先呢,对于这个整形,整形的话呢,我们提到过好几种整形类型,对吧?啊,那么大家如果说你不知道该选哪一种的话呢,默认你就选int就行啊,首先的话呢,它的范围呢,其实也足够大啊,也够我们用是吧?比int类型大的话呢,是不是就只有这个bigt了。
06:00
啊,Big in的话呢,你比如说呃,双11的这种交易量啊,大型门户网站的这个点击量啊,哎,我们可以使用这个big int啊,一般情况下呢,我们用int呢,都足可以成像啊,这是一个点啊,就是你用它呢要放心,呃其次的话呢,就是在一些特殊场景下,你比如说去存储这个人的年龄啊,这个你要用这个T呢,应该也可以是吧,诶同学说,诶老师我会超过127,诶所以这就涉及到下个问题说呢,诶对于这种非负型的这种整形数据来讲,大家一定要加上一个安。啊,相当于这样的话,一下子就把这个负数就砍掉之后呢,是不是全归到正数里了,能够提升我们的存储的数据量嘛,对吧?哎,那我要存储这个人的年龄,我用tin,你要觉得说这个负127正127可能不够,我加一个安散的,这这肯定够了吧。哎,对的是吧,哎,提升我们这个数据的一个容量了,相当于。然后下个呢,就提到这个能用文本类型的,也可以用整数类型的,那用哪个呀?哎,用整数类型的,因为呢,它能够存储的,呃,这个同样的这个数据的话呢,这个整数呢,它占用的空间呢,就会更小一些啊呃,那就能够提升查询的效率。
07:02
对吧,还是提到这个数据页里边,你可以放的就会更多了啊啊比如说呢,我们把这个IP地址呢,转换成整数啊,就是这样的一个选择啊下面呢,提到说尽量避免呢去使用这个test或者是blog这样的数据类型。啊,这两种数据类型呢,我们也谈到过,他们最大的可以支持四个G是吧,你想的话呢,你要把这个数据呢,塞到我们这个表里边儿,这个这个加载的话呢,是不是很崩溃的一个事儿啊。对吧,诶这里边还提到了叫内存的临时表啊,这个临时表的话呢,首先内存级别的不支持啊,你要想用这个临时表的话呢,只能是磁盘的这个临时表。OK,这样的方式是吧,而且对于这种数据呢,还要进行二次的查询,性能呢很差啊,这是一个点,诶另外一个的话呢,就是我们建议呢,就是大家用这个字段的时候呢,一定要把它分离到单独的表当中去存啊,你比如说我们这这是一张表啊,来存储这个用户的信息的啊用户信息的话呢,我们经常用户呢,是不是需要保存一张照片,那照片的话呢,是不是就不要在这存了,那你可以呢,在这存一个是不是ul,然后呢,在另外的表里边专门呢,是不是存他的一个,呃图片是吧?哎,这样的一种方式啊。
08:04
查询的时候呢,呃,如果说你要真的是放了查询的时候呢,大家可不要用C拉星啊,你要把它查出来的话呢,加载到内存中,这个呢成本很高啊,能避开这个test呢就避开它啊,在查询的时候是吧。OK,这是一个点,然后下边呢,提到一个叫哎枚举类型啊,说枚举类型的这个值得修改呢,需要用al,然后呢,Autob效率比较低啊,建议呢,大家用这个T呢,你去替代一下这个啊innu啊或者I是吧。好,下边呢,提到这个叫time,关于这个日期时间类型啊,咱们在讲这个日期时间类型的时候呢,一个是提到它还有一个呢叫time。啊,这两个它占四个字节,Data time呢,占八个字节是吧?啊那么我们如果要存储像这个注册时间呀,像这个商品发布的时间呀,诶等等的,诶我们考虑用这个时间戳你就可以用这个time step啊存储空间比较小是吧。好,然后的话呢,提到这个诶浮点类型啊,浮点类型这块呢,没有什么可商量的啊,就是不要用float和double,就用这个decim啊,因为它的精度呢,不会丢失精度啊,尤其是这个财务相关的这种金融数据的话呢,是不允许有呃这个精度的这种损失的是吧,还有我们用这个戴膜就可以了。
09:11
呃,总结一下的话呢,就是这个遇到数据量大的这种项目,一定要充分了解业务需求的情况下呢,合理的去配置我们的数据类型。啊,就是这样一个概念啊,这个概念行,这个我们就说到这儿,然后下面的话呢,提到这个叫优化插入记录的一个速度啊,隐色的插入,那如果说我们插入一两条的话呢,那就呃就无所谓了是吧?诶你要插入这个数据量呢,比较大的时候呢,我们就要考虑这样一个优化啊,那这里边呢,我们区分出呢,不同的存储引擎啊MYS和这个in DB啊,因为这哥俩呢,它一些特性呢不一样是吧。那对于这个MY来讲的话呢,我们首先第一个啊禁用索引啊,因为我们知道呢,你要打大量的去做这个隐私的数据的时候呢,我们所有的这个表中的索引是不是涉及到啊,你这个字段呢,是不是都要进行一个修改啊,那这就导致我们整个插入速度这个性能就降低了,所以先把索引呢给它静掉啊,等你这个插入完成以后呢,你再把它启用啊就可以了。
10:06
好,下边呢,叫禁用唯一性检查,你想我们要添加数据呢,呃,要保证它的唯一性,每一条数据呢,是不是都要跟现有表中的做呢,相当于做一个校验,当然这个成本是很高的啊,所以我们首先呢,先禁用微信检查,然后等你添加完以后的话呢,你再去单独的去校验啊这样。好,然后关于这个批量的插入啊,批量插入这块的话呢,你看如果呢,你要是这样写的啊,其实每一个SQ语句呢,都得需要做一个解析啊,做一个优化啊,做个执行啊,这个成本也挺高,那不如我们是不是就合成一个SQ语句啊,就相当于解析啊一次是吧就可以了。行,这呢就是我们说的这种方式呢,要优于上面这种方式啊,然后呢,说针对于这个MY这个存储引擎来讲呢,我们还可以呢,使用这个叫load data啊这样一种方式,我们可以批量的去导入数据啊,这种方式呢,比我们这种方式呢还要好。啊,还要好。行,这个呢,是针对叫MY啊。然后的话呢,我们再看一下这个叫印度DB,哎,印度DB这个存储引擎的话呢,诶,我们有这样几个点啊,第一个呢,说叫禁用唯一性检查,哎,跟我们上面说的方案呢是一样的啊,下面呢叫禁用外键检查啊,这个呢是因为这个度DB啊,它支持外键是吧?你想想我们在添加数据的时候呢,还得到那个附表当中去校验一下,说有没有这样的一个字段值,这个成本也很高,所以我们需要把它先关掉。
11:24
然后呢,当你插入完以后的话呢,你再去检查一下,在附表中有没有这样的一些字段啊,再去做判断啊要好一些。好,然后下边呢,提到这个自动提交的问题,主要是因为咱们这个印度DB啊,它是支持事务的是吧,而且默认情况下呢,这个我们说这个DM的增删改,每执行一次是不是都会自动的去提交啊,啊一提交这个时候呢,就得写入这个磁盘,这个呢成本还是比较高的,怎么办呢?我们可以呢,诶在你这个添加数据之前的话呢,先让他呢不要去提交啊,等都插入完成以后,是不是再提交一次。啊,这样的方式OK。行这个呢,因为都比较简单哈,所以咱们就直接这样过了,然后呢,看这个3.6说使用叫非空约束。
12:06
啊,非公约束这个咱们在前面应该是讲到过啊,就是尽量的咱们在设计这个字段的时候呢,大家呃,要给这个字段呢,能加上闹闹的约束呢,就给它加上啊,这个原因还记得是什么呗。啊,一方面的话呢,针对我们这个闹值的话呢,咱们在这个存储空在这个行格式里边,是不是专门呢有这个,诶now值的这个列表去存储是吧?诶这个呢,我们还得专门用空间去存储它,你要用闹闹的话呢,就不用考虑这个存储的问题了。啊,这是一个点啊,从这个存储空间上来讲,然后另外的话呢,就是我们索引的话呢,诶咱们都知道啊意思not not的话呢,诶你要是用这样的一个,诶判断语句啊,这个过滤语句啊,它呢是不能够使用索引的。对吧,嗯,你要是找这个非空的,要是默认大家都飞空了,你就不用它了,所以你就能够起作用了是吧,这是一个,呃,还有一个呢,就是说,呃,你要是有的字段呢,是这种空值,咱们用这个聚合函数,什么count呀等等的,它都不计算这个空值是吧?可能会有这种偏差啊,这样的一些问题啊,哎,整体来讲的话呢,就是能加非空约数就加上。
13:06
好,嗯,最后一个呢,这块提到了关于这个分析表,检查表和这个优化表啊,这是对应的三个操作啊,这个叫analyze,这个叫check,这个叫optimize OK,这块我们看一下啊,说这个MYS呢,提供了这样的几个功能,分析表呢,主要是分析这个关键字的一个分布,哎,检查表呢,主要是检查表呢是否存在这个错误啊,优化表呢,是消除删除和更新造成的这个空间浪费啊,相当于是进行一个碎片的一个整理啊,那首先看第一个啊叫alyze,这叫分析的意思。分析一个表啊,你可以写多个啊,就是这几个都分析了,相当于啊,前面这块有这个参数可加可不加啊,叫local或者呢,叫no right tolo,哎,这两个意思是一样啊,你选一个就可以了,当然也可以不用啊,呃,用上的这个意思是什么呢?哎,这个主要涉及到了,就是我们后边讲的这个叫blo日志了。这个我们进行这个主从复制的时候呢,这个呃,这个从机你要对我们这个主机上做的任何这个修改呢,得同步过去,那就使用这个blog日呢,做一个这个同步了,那这个呢,就主要决定你这个endline这个语句呢,要不要写到这个belong日志当中啊,就这样个原因啊,那你要不希望呢,他写你就加上这样个参数就行啊,事实上的话呢,他也不会对我们这个表产生任何的影响,所以我们可以考虑呢,用上这样个参数啊。
14:19
呃,这块呢,提到说这个table的话呢,在分析表的过程当中,会对这个表呢加一个读啊,那就就不能够去更新或者是插入数据了,对吧,针对DBMY类型的表都是有效的啊,不能够使用视图。行,那这块呢,关于这个underline的话呢,咱们给大家去举个例子啊,举个例子。嗯,举个例子的话呢,咱们就回到这个代码这块啊来看了啊,嗯,这块呢,因为咱们举这个例子呢,相对来说也不复杂,所以提前我就把这个代码写好了,咱们就直接给大家说一下就行,首先呢,我们选了一个数据库啊,ID硅谷DB3,然后呢,我去创建一个表啊,这呢是我这个表中的这几个字段,ID呢是个主件。然后呢,这个name这块呢,有一个普通的一个哎索引是吧,好,那这是我们选中把这个表呢创建一下。
15:05
没问题是吧,好,创建好以后的话呢,我们往这个表里边呢,添加1000条记录啊,添加记录咱们就随机添加了,所以呢,需要用到这个存储函数,还有这个存储过程啊,这两个的话呢,我都已经创建好了,哎,对应的这个函数在这存储过程呢,在这儿都有行,然后接下来的话呢,我直接呢,就调这个call insert into这个user啊,Insert到这个user里边啊,这不就到我们这表里边了啊,添加这个1000条记录是吧,来凑齐。很快哈啊,因为我们这个1000条数据呢,比较少,行,然后的话呢,我们去看一下啊叫show index from一下这个user啊这个咱们都知道,查看这个表查USER1是吧,查看我们这个表里边的这个索引来走起。啊,大家看,然后在我们这个查看这个索引的时候呢,一个呢叫primary,一个呢叫index,普通的一个,呃,这个这个索引是吧,然后呢,大家看这个位置啊,Cardinality啊。Dal。
16:01
这个呢,翻译成中文呢,叫做基数的意思,这个基数这块呢,就能够用于我们这个endless去做这个分析哈,嗯,这个基数的话呢,你看这是什么意思啊,这个是1000,这个是一是吧,哎,我给大家呢先解释一下啊,哎,我们先去呢,Select星from一下,我们就叫USER1,哎这两个表哎我们选中了做一个执行。啊,大家看我刚才呢,去做这index的时候呢,一个呢,呈现的是我们这个主键ID啊,你看这个是123,一直到了1000,它是不是有1000个不同的值,对吧,而这个name这块的话呢,你看我全叫艾特硅谷。啊,如果从这个呃,区分这个重复度上来讲的话呢,这是不是哎只能有只有一个值,因为大家全是重复的,而我们刚才看到这个收引ex这个位置呢,哎,这个1000就表示它有1000个不同的值,而我们这呢,所有的值都一样,所以就是一。这个大家能明白是吧?好,那么接下来的话呢,我去做这样的操作啊,叫update这个USER1,然后set一下呢,这个name等于啊,我叫at硅谷,比如叫零一,然后呢,Where。
17:00
啊,比如ID等于二。能看懂是吧,好,我呢做一个执行。哎,执行完以后的话呢,我们再去执行这个收银ex。哎,大家你看啊,我们现在这个表当中的话呢,是不是应该是有两个不同的这个内值了,一个呢叫阿硅谷,一个是不是叫I硅谷零一,但是你看这个位置是不是还是写的一啊,相当于他没有及时的帮我们做一个更新,对吧,那此时呢,我们就可以使用这个叫诶分析表。Ans是吧,Table叫user。好,你看这时候我们选中呢,做一个执行。哎,执行完以后,看这是一个status是OK的啊,然后我们再去呢,收index来,我们再走起,你看这个位置呢,是不是就变成二了。啊,因为我们这个表里边儿呢,现在你就是有两种不同的值是吧,哎,爱的硅谷是一个,这是一个。啊,这一个啊行,那大家呢,咱们还可以呢,再举个例子,比如说改成三啊,这个叫零三吧,啊现在呢,我们看一下这个一共是不是有两个不同的值啊,我再去做一个执行。可以了,然后呢,我们此时呢,做一个收index。
18:02
啊,这个你看还是二,然后我们去analyze一下啊,走起,然后我们再去收index。哎,你看这时候就变成三了是吧?哎就这个道理啊,那这个的话呢,我们去分析它,它有什么好处呢?哎,就是我们,哎大家注意哈,咱们这个位置呢,看到了它其实是来体现我们这个,呃,相当于这个不同的值,其实你也可以理解成是不是叫区温度,哎,咱们在前面呢,我讲什么时候说过区温度的概念啊。啊,是不是我们提到这个索引的时候是吧?诶所以说呢,我们这个查询液化器啊,他去这个选择这个索引的时候的话呢,诶就是我们相当于是呃,计算不同的索引啊,哪个花的成本高,哪个花的成本低,最后呢,他要选那个成本最低的啊作为我们这个表真正执行的时候呢,用到那个索引是吧?啊那么我们就会考虑这个,呃,这个区分度的一个问题了,那我们通过这个a analysisze的话呢,相当于就可以让他去更新一下这个,诶不同字段这个区分度。啊,就这个情况啊,你比如说呢,咱们要这个ID啊,我这块呢,执行的时候直接出来是1000啊,大家那块的话不一定就是出来了,一一下子就是1000了,有可能你这个位置上写的就是个二。
19:03
啊,那就意味着呢,就是一开始的时候,这个ID呢,他就认为呢,这个区温度啊,就是两个不同的啊,然后呢,当你要进行一下analyze以后呢,哎,他呢一看哎哟是1000,就相当于帮我们进行了一个刷新一样啊那么这个值呢越大。啊,越接近于我们这个表中的这个,呃,数量的这个条目数,那就说明它的区分度就越高,那你要是在这个字段上有索引,是不是就越优先的去考虑这个字段了呀。哎,就是这样个情况啊好,这呢,就是我们说的这叫哎分析表啊分析表。好,那么就说到这儿了,下边的话呢,我们来看第二个,这个操作呢,叫做哎,检查表。嗯,这个叫检查表,这个检查表呢,用的叫check table啊,他也会在这个执行的过程当中呢,给我们这个表呢,加上一个制毒锁啊OK,然后呢,针对这个in DB和这个MY呢,都是有效的啊,都是有效的,具体的操作的话呢,就是check table谁啊这个你也可以写多个表,然后呢,后边有个叫option。啊,这个option的话呢,可以呢,取值是这样的一些。
20:02
啊只呃不扫描行不检查这个错误的连接啊,只检查没有被正确关闭的表啊等等等等等这样的一些检查行为,哎,这个的话呢,只针对于咱们这个my s sum呢,是可以使用这个option的,这个in DB的话呢,就不能用这option了。啊,就这样的啊,嗯,这个话呢,大家做一个了解,其实就行,那咱们这块呢,稍微的写一下吧,哎,这个要还检查。哎,表是吧,哎check这个table啊,比如就用这个USER1吧,这样子啊。哎,凑齐啊state OK啊这个呢,因为咱这个表也没什么问题啊,所以他也看不出来这个报什么这个错误信息了,就啊那有时候呢,我们在这个表呢,操作过程当中呢,一些错误的行为呢,可能导致这个表呢,就出现一些呃,这个损损毁是吧?啊那这个时候的话呢,你要进行这个check操作呢,它就会给我们展示一些这个信息。啊,这是这样一个情况啊,行,这个就过了比较简单,然后再下边这块呢,诶我们可以提一下叫optimize啊,这叫优化的意思。诶,可以优化表,嗯,这个优化表的话呢,它是干什么的呢?就比如说我们这个表中呢,删除了一大部分数据啊,或者呢,针对这一大部分数据呢,进行一些更新,那我们使用这样一个指令的话呢,可以,诶对我们这个整个呢,占据这个空间呢,进行一个碎片的整理啊,就这样个作用啊,但是你注意的话呢,它只是针对于我们叫work blob或者test这样的字段类型的起作用。
21:21
啊,那也很自然,因为这些字段呢,是不是占用的这个空间呢,就会比较大一些是吧,你要整个int呀啥啥的,呃,也没有必要呢,非得进行空间的优化了啊OK啊,因为呢,优化的话呢,这还是需要成本的这个付出的是吧?哎,我们呢,优化完以后的话,你明显感到这个空间呢降低了啊,那针对这个类型呢,比较大啊同时的话呢,你这个数据的话呢,最好呢,是不是操作过删除过或者更新过一大部分的这个数据的场景,我们再去用。对吧,哎,这个意思。呃,这块的话呢,呃,针对这个MYS和这个in DB啊都是有效的啊,那首先的话呢,我们把这个呢来说一下啊,因为在这里边呢,还有一个小的一个区别啊,这个区别的话呢,也导致啊还确实呢进行过这样的一些相关的一些查询资料啊来我们说一下这个区别先这呢我们叫这个呃优化表,嗯优化表首先的话呢,我们创建一个表啊,这个表呢,我们分别呢使用啊MYS和这个in DB。
22:16
啊,不同的这个存储引擎啊,比如这叫T啊,ID是一个int类型的是吧,还有一个这个比如叫name。哎,差类型啊,15个长度,嗯,这样封一下,然后呢,我们指定一下这个in啊,使用这叫by I some。好,这样的话呢,我们去创建一个表来走起。哎,创建了,好,我这块再复制一份,哎,我造一个这个叫T2,这个表使用的是这个inno DB。嗯,好的,然后咱们再选中做一个执行,诶可以了是吧?行,然后在这块的话呢,你看我们使用的叫嗯OPT麦子是吧,诶这个table呢叫T吧,好这时候我们对它呢进行一个优化来走起。
23:01
诶,大家你会看到这个信息呢,说table is already up to date啊,其实就因为我这个表呢,刚创建的,他说你这也没啥需要优化的啊,就是这样一个啊,或者叫已经优化到这个最新的了啊这样个情况行,然后呢,你看啊CTRLC一下,我们去针对这个T2这表主要呢,是除了他俩这个区别啊,你看我们选中呢,再做个执行。那你看这时候呈现跟我们的信息呢,就不一样了,它这里边儿是这样说的啊。说这个table呢,Does not support optimize说呢,你这个表啊,不支持这个optimize不支持优化,说你应该呢是recreit加LY这个instead,说你应该这样去做啊是不是这个意思啊啊这个一开始的时候呢,诶我呢去做这样执行呢,他报这样的信息呢,哎,我就认为说哎呀这个相当于是OPT就不支持了,是吧,但是呢,看到一些资料当中呢,又说呢,他是支持的。那这时候呢,我们该怎么办啊?啊,是不是就得找这个官方的这个文档看看到底是支持还是不支持的啊,我们要确定这个知识点到底是对的还是错的,是吧?啊那么说这个之前啊,我先说一意就是我们这个呢,对于我们这个D呢,其实出来呢,呃,他要是真优化的话呢,它是怎么处理的。
24:13
你比如说我们这个T2的表呢,针对这个字段啊,这个经过了大量的这个删除啊,空间呢,有很多都是碎片的,那我们优化怎么做呢?它呢,先去创建一个临时的表啊,比如我们这个表,我自己起个名叫T3吧。创建个临时表,然后呢,你把你T2表剩下那些数据呢,都copy到我们这个新的表当中。Copy完以后啊,这里边就没有碎片了是吧,然后你把这个有碎片的这个T2表呢,给它删掉,删掉完以后的话呢,你再把这个T3列表呢,改成叫T2啊,这就是进行了一个碎片的整理,哎,它是这样个思路,所以叫recreit啊,你看就是这个意思是吧?啊recre啊,然后进行一个analyze来进行一个分析嘛,是吧。行,那这首先呢,我们说清楚它的一个做法啊,然后的话呢,我们看一看到底我们这个操作呢,有没有去帮我们做这个优化呀,你要看这块儿呢,又来了个OK是吧?哎这块呢,我们是不是就要诶找官网了啊,因为呢,咱们已经没有这个确定的信息了啊找官网,然后这块呢,诶就找到了我们官网中的这样的一个文档。
25:12
诶在8.0当中,针对我们这个奥呢,是不是进行个说明,诶这呢是最权威的啊,所以呢,给大家讲,就是咱们一般情况下呢,其实大家不会说呢,去这个官网当中去学我们这个MYSQL毕竟是英文的啊,这个还是很痛苦的啊,诶那在我们有一些问题解决不了的时候呢,我们去官方文档里边去找这个答案,因为它是标准对吧。好在这里边呢,其实就有说明啊,然后往下走一走。啊,比如说这里边儿呢,就提到了我们这个,呃,Optimize看针对的这个数据类型。是吧,针对这种数据类型,这是一个,然后的话呢,说这个呢,它works for啊,它呢,针对什么存储引擎是有效的,这是不是提到我们常见的这三种存储引擎,是不是就包括了DB和MYS啊,通过这的话呢,我们首先呢,心就放下来了,哦,DB呢,确实是支持的是吧?那它支持的话呢,那为什么你会呈现这样的这个信息呢?靠不靠谱啊。
26:06
啊,明明告诉我说这又不支持,诶这个呢,我们就再往下走。哎,我就呢,哎再往下走,走着走着,诶看到说诶DB这个details的细节是什么来我们就点进去了,一点进去一看,哎哟跟他有共鸣了,他是不是也这样呢?诶说的呀,现在呢,他去啊优化一个这个fo这个表也会出现这样的信息。啊,也有这样这信息,然后上边你去一读啊,就发现了,哦,他原来呢,就是确实也优化了,它优化的这个信息呢,就是长这个样子的。啊,这块呢,我们就放心呢,确实呢,是帮我们做了一个优化啊,啊那要是你还不放心的话呢,诶这块呢,我还特意的还举了个例子。这个例子的话呢,我就呃已经提前演示好了,我就直接呢把这个过程给大家说一下就行,没有必要呢真的去跑一遍了啊行嗯,这个事儿是这样子的啊,首先呢,我创建了一个表,这个表呢叫OUT1这个表,然后这个表里边呢,放这个这个数据类型了,然后呢,添加来的100万条数据啊,然后填完以后的话呢,整个呢这个呃,底层对应的这个ibd这个文件的大小呢是412兆。
27:06
啊,对吧,然后的话呢,我把这个表中的一半的数据呢,给它删掉了。啊,删了一半的这个数据啊,删完以后的话呢,我们再看一下这个,呃,数据目录下的这样一个idbd这个文件啊,还是呈现给我的是412兆,然后呢,我就使用这auto table啊叫AL1这个表,诶使用完它以后的话呢,再去做查看啊,它就变成了336兆了,相当于呢,是不是进行了一个碎片的一个整理。哎,就是这样一个过程。啊,这样一个过程行啊,那通过这个官网也看了,通过我们实际举例呢,也说清楚了,那说明呢,哎,它确实呢有这样优化的效果啊好,那大家呢,根据你自己的情况啊,如果呢,你针对满足我们这里边儿这个场景的。是吧,哎,你就使用我们这个优化啊,节省这个相当于整理一下这个空间。然后呢,除了用我们这个auto之外呢,还有一个命令啊,叫做my check啊,这个命令的话呢,跟我们这个这个效果呀,是完全一样的。
28:00
啊,完全一样的啊,这个咱们要想演示它呢,呃就就不能在这了,咱们在这个命令行这块呢,去说一下啊,我们就使用一下,这叫my circle t是吧,杠oo呢,就是optimize的意思,然后后边呢,比如针对叫爱的硅谷,呃,D be3。对吧。ADB3好,针对这个数据库下载我们叫UCE1这个表啊,进行一个奥啊杠U啊,你写个ru-P啊ABC啊123回车。哎,然后你会看到呢,是不是这块报的信息跟我们,诶刚才看到的其实是类似的是吧,还就这样个意思,现在呢,也也就帮我们做了一个优化啊,这呢也是一种方式啊。行,那么到此的话呢,咱们就把关于这个呃,数据库的这个结构的这一句话呢,咱们就说完了啊里边有一些这个措施,诶大家这块呢,熟悉熟悉啊呃最后一个小节的话呢,咱们是想强调一下啥呢,就是我们这些措施啊呃大家呢,不是说呢,在开发中一定要选的啊,诶你是要量力而行啊,做个权衡啊,根据这个业务需求呢做个权衡,为什么呢?因为很多这个操作方式呢,它是有利有弊的。
29:01
对吧,你比如说我们修改数据类型啊,这个越小越好,那万一要小的时候呢,导致你这个数据呢,都成不下了,这就成了个错误了是吧?啊那这个呢,你要衡量一下啊,再者的话呢,我们增加这个冗余的字段,增加冗余字段话呢,你一定要确保这个数据的一致性啊,啊这个你你别别忽略了啊,这个增加中间表啊,我们这个反范式化是吧?哎,不要忘了这个一致性的问题。再者的话呢,我们把这个冷热数据拆分开了啊,那你这块呢,要新建连接啊,查询一个表中的数据,现在呢,是不是需要从呃多个表中进行一个照的操作了,是不是还增加了这个成本了,是吧?诶所以说整体上来讲呢,都是有利有弊的啊,你要做一个整体的权衡之后啊,来决定你到底要怎么做。好,这是我们关于数据库结构的一个优化。好,那么接下来的话呢,我们来看一看其他的一些优化策略啊,首先呢,提到了叫大表的一个优化啊,说呢,当MYSQL单表的记录数呢过大的时候,导致我们整个数据库的这个c rud的这个性能呢,会明显的下降啊,那这时候怎么办呢?诶我们相关的一些优化策略,呃,首先的话呢,说叫限定查询的范围对吧?那你像我们这记录数已经很多了,这时候你也不加任何的限制啊过滤条件啊去做查询,那肯定这时候呢,查询的性能呢就会很低了。
30:12
啊,你需要把太多的数据呢,都需要检测出来,那我们这里边呢,是不是就要使用一下这个哎过滤条件,然后尽量的还要使用上这个索引,对吧?哎,这是我们说的第一个点,然后第二点的话呢,提到这个读写分离的这种策略啊,这块的话呢,咱们在后边呢,讲到这个主从复制的时候呢,还会去讲这样的问题啊这呢,我们是从这个优化的角度呢,我们来谈一谈,相当于呢,我们在这个呃,单台这种主机上呢,呃,已经针对这个SQ啊表,还有这个SQ语句啊,已经做了最大限度的优化了,包括这个相关的参数配置,对吧?但是现在的话呢,我们面向的就是一个高并发的场景,那该怎么办呢?诶,我们这里边呢,提到一些其他的策略啊,相当于这种集群的呀,还有这种读写分离的呀,还等等这样的一些这个策略去分摊我们单台服务器的这个压力。那这里呢,就提到了一个叫读写分离对吧,我们呢,通过呢叫主从一个复制的啊方式啊,保证呢,主机和从机的数据呢是同步的,然后的话呢,针对于我们这个,呃,针对数据库的这个写请求,我们呢是访问这个master主机,对于读请求的话呢,我们去访问这个slave啊,这个从机啊,这块呢,我演示的是使用了一个数据库的中间键啊,叫cat啊,以它为例呢,我们进行了一个说明啊,这叫一主一从的模式。
31:26
啊,然后呢,我们说呢,诶这一台主机的话呢,是不是也有可能有这个宕机的风险呢,那怎么办呢?诶我们可以有这种双主双重的模式啊,那么在这个当中呢,我们这两个主机呢,是互相这个复制的啊,它也是一个备份的一个情况啊,比如一开始我们使用的这个主机呢,是这个master,那其实呢,这三台呢,都相当于充当这种读的这个这个机器了,然后呢,当我们这台这个服务器呢,如果挂掉的时候呢,我们呃另外的这台master呢,就启用了,诶然后他们其他三个呢,就充当这种读机,诶就这样的一个效果。好啊,这呢,就是我们相当于叫诶主从复制啊,这样的一个,诶主从复制读写分离是吧,这样的一个模式啊,然后的话呢,我们再提到说当我们这个数据量呢,达到这种千万级以上。
32:10
啊达到千万级以上,首先呢,我们并不是说MYS呢,它不支持这个千万级以上的是支持的,只不过呢,就是你这个数据量呢,特别大的时候呢,几千万的时候呢,这个对于我们整个查询啊,对于我们服区的性能来讲呢,它是一个挺大的一个考验的啊属于这样,那么在这种场景下呢,我们可以考虑呢,呃,叫做分库分表啊,分库分表啊,那你比如说呢,我们这个,呃,这个所有的表呢,都原来放在一个这个主机上啊,那我们都来访问这台主机压力肯定很大了,那怎么办呢?诶我们可以考虑,首先呢,咱们讲叫分库吧,是吧,诶我们把这里边呢,有些表呢分在这台主机上啊,有些表呢放在这台主机上啊,这就是一个分库的一个诶策略。垂直分库是吧?诶把这个相关联的表呢,我们部署在同一台这个主机上,诶什么意思啊,就是我们这时候在分的时候呢,比如我们一个项目啊,涉及到了有这个100张表,这个呢,你别随机分啊,诶我们是不是一定要把这个功能按照这个功能模块啊,把这个功能模块相同的这个表呢,我们分在诶相同的这个主机上,对吧?诶这个另外的这个模块呢,我们分配利用在另外的这个主机上。
33:13
哎,避免呢,我们去这个,呃,跨库呢,去做这个账操作。啊,这呢,我们叫这个垂直分库啊,那如果说我们这个表中的这个列比较多的话呢,比如这个列比较多,我们还可以呢,叫垂直分表,然后把它呢,是不是拆分出来,诶这是一张表,这是一张表诶通过这样的方式对吧?诶这个咱们前面也提到过,是不是这个冷热数据分离的这个方式了,对吧?诶这是一个点啊,那么垂直拆分的优点的话呢,就是我们可以使用这个数据呢,变得更小啊,减少这IO啊这个咱们前面其实也都提到过了哈,缺点的话呢,就是我们在原来不需要照样的时候呢,你现在是不是还是需要这个照样这个操作了是吧。好,这是我们说的这个,然后呢,我们还可以呢,进行这个叫水平的一个拆分啊,针对于我们这个单表中的这个数据量啊,哎,横向来看呢,很大啊,几千万条数,那怎么办呢?诶我们可以考虑呢,对这个数据呢进行一个拆分啊拆分呢倒不是说非得呢只拆两份,你也可以拆成更多份,主要呢,就看你这块呢,这个主机呢,有几台,有三台的话呢,那就可以分成这个三份来拆,对吧。
34:12
那这时候拆的话呢,你要考虑这个按照什么样的这个方式呢来拆啊,这个我们把这个拆呢,就成为这分片啊,这个分片的规则啊,有的时候呢,你不太适合呢,按照这种日期来分,你说我们这个诶按照这个时间来分啊,这一部分,这一部分这一部分,然后呢,比如说大家这个呃,购物的历史订单的这个数据是吧?诶然后按照这个时间来分成三段啊平均分的这个放这儿,这个放这儿,这个放这儿,那实际上呢,你会发现呢,呃,用户来讲呢,呃,其实他要查看历史订单的话呢,他也会看他最近的啊,关于这个之前几几年的,他可能也不看。那相当于呢,你接入分完以后的话呢,这台压力还是很大,对吧?啊这这这台压力呢和这台压力呢,就仍然很小,那你说明你这个分片的规则就不是特别合适啊,这里边呢,就涉及到这个分片规则,有这个取模的呀,有这个按照枚举的方式啊,有按照范围的约定啊,啊日期啊等等,它都是不同的这个分片策略啊,这个我们到后边的时候呢,再提大家呢,先知道我们这样一个事儿就行啊,哎,这呢就提到某个属性的维度呢,进行拆分,就是分片策略的意思。
35:11
好呃,那么呃,整体上来讲的话呢,我们就提到这样几个概念啊,就是叫哎这个垂直分库啊还可以呢,叫垂直分表啊还可以呢,水平分表啊这样的一个方式啊去处理啊好,那这块呢,提到一段话哈,说这个水平拆分啊,能够支持非常大的这个数据量的一个存储啊,应用端呢改造也少,但是呢,我们这个分片事物呢难以解决啊,跨节点这个照呢性能还比较差,逻辑呢还挺复杂啊,那么在这个Java工程师修炼之道这个作者呢,就推荐之后尽量呢就不要进行这个数据的分片了啊,会带来相关的这样的一些复杂度。对吧,诶这个这个这每个人有不同的这种见解哈,哎,这个我们要尊重诶而且呢,面对的不同的业务场景的话呢,我们说比较复杂,在不同的场景下呢,确实应该有不同的这个策略去处理啊啊这是我们提到这个点啊说如果实在要分片呢,尽量应该选择呢在客户端呢来进行分片这个架构减少呢使用这种中间件的方式。
36:06
而我们前面呢,图当中呢,像这个ma啊,这也是比较常见的一个,呃,数据库中间件的这样一种方式,进行一个分库分表啊,这样的操作啊,包括呢读写分离是吧?哎,那么还可以呢,使用这种客户端的这种代理的方式。啊,在我们这个应用端啊,把它呢封装在我们这个包当中啊,通过修改或者封装JDBC啊这个层来进行这个实现啊,两种不同的策略啊,这个呢,大家了解一下就可以了。那么除了我们针对这种大表啊进行优化之外呢,诶还有其他一些策略,诶下边这几个策略呢,大家做一个了解就可以了啊,比如说这个8.0当中啊,其实我们这里边提到的都算是这个8.0的这个新特性了,8.0当中呢,可以设置叫服务器语句超时的这个限制啊,语句超时的限制可以达到这个毫秒级别的啊,一旦呢,超出我们这个限制啊,我们通过这样的参数呢,是不是进行这个设置是吧?超出这个限制的时候呢,诶,我们服务器呢,就会终止查询影响不大的这个失误或连接啊,把这个错误呢,再报给我们这个客户端。
37:02
啊,这是它一个比较好的一个参数啊好,另外呢,提到叫创建全局通用表空间。我们呢,可以通过呢叫create table space啊,这是8.0的一个新特性,创建一个叫全局通用的表空间,然后呢,我们可以把表呢,放在我们这个全局通用表空间当中啊,进行一个保存,这样的话呢,可以节省咱们原数据方面的一些内存。啊,这就它的一个好处啊,下边呢,还给大家举了个例子啊,那咱们把这个代码呢,就跑一下就行啊,这个我就不亲自去写了啊,咱们直接呢,就诶体验一下就可以了,诶通过这样的一个create table space啊我们的叫ID硅谷一,然后呢,对应底层我们的I pd文件呢,啊,我叫ID硅谷一啊使用它的这个方式啊,默认的大小呢,16KB,好,我们这块呢跑一下。诶创建好了是吧,诶创建好以后的话呢,我们就可以呢,去创建一个表,创建表的话呢,我们就指定啊你这块呢,使用的叫table space啊是我们这个爱五艺。啊,就非常的灵活是吧。哎,这样的一种方式,这个呢,是咱们在这个创建表的时候呢,我们去指定的这样的一个表空间啊,走起。
38:05
哎,是不是就可以了,那现在呢,我们就把这个呃表是不是就诶回头呢,它占据这个空间呢,就放在这个文件里了,而不是原来我们这个独立表空间是吧。好,那这是我们叫创建表的时候也可以呢,我们在修改表的时候啊,把这个表呢修改啊,改到我们这个共享的这个表空间当中啊,Al table它然后table space啊指定成我们这个。哎,共享的列表空间,它也是OK的。啊没问题是吧,好,然后呢,你就诶操作这个表就可以了,嗯,那你要是想不用这个诶共享的表空间了,我们是不是可以呢去删除啊,哎,比如我们去drop一下,这个叫table space啊咱们叫爱的硅谷一,哎来我们这块呢,选中呢,看看执行一下行不行啊走琦。好,这时候你可以发现呢,他会给我们提示这个错误信息啊,说你这个艾特硅谷一呢是is'not empty啊,你不是空的,因为咱们不是有这个test这样一个表嘛,啊那你需要什么呢?先把这个表啊,如果你确实要释放这个表空间的话呢,需要把这个表呢先做一个删除。
39:04
我们drop有这个table。对吧,哎,删除这个表以后的话呢,你再去删除我们这个共享的表空间啊就可以了。啊就可以了啊行,这块呢,我们给大家说一下这样一个操作,行啊这块呢,大家了解一下,知道我们这个叫全局通用表空间的这样的一个功能啊,然后的话呢,最后呢,再提到这个隐藏索引啊,咱们再讲索引的时候呢,其实给大家讲过隐藏索引,还有这个降序索引,作为我们8.0的新特性啊,我们提到过的啊,你往前翻一翻,我们讲索引的时候呢,有这个内容。啊,这个隐藏索引的话呢,我们说呢,对于我们这个优化来讲呢,也算是一个策略啊,当然只有8.0才能用。说当一个索引呢,被设置成叫invisible的时候呢,这个查询优化器呢,就不再去使用它了啊,那么我们通过这样一个功能,是不是可以查看隐藏个索引以后呢,你看我们这个性能呢,是不是有所下降啊,如果有所下降呢,说明人家这个索引呢就是有用的是吧?啊那如果说呢,你这个隐藏以后的话呢,说明你这个,呃,这个性能呢没有受影响,那说明这个索引呢就没有啥用,你就可以把它删掉了。
40:02
那就这样的一个功能嘛。啊,就像大家呢,你想看一看,说你自己你的价值有多大是吧,你在公司的你价值有多大,那你想象一下,如果公司没有你,呃运转呢,是不是还一点影响都没有啊,甚至说呢,没有你,哎公司这个运行的还更好,那完了,那你尽早是不是早点撤出这家公司是吧?哎,就成这样的情况了啊。啊,这是我们说这个事儿,然后需要大家注意的点是什么呢?就是这个索引呢,如果我们给它隐藏了,诶你一定要注意啊,虽然说呢,我们这个查净化器呢,默认情况下呢,就不能用了,但是呢,它还是实时更新的啊。啊,它还是实时更新的,这个事儿你要注意一下,所以说呢,你要是有个所以呢,长期被隐藏,那就是最恶心了啊,一方面这个所以呢,产品化器呢,默认还用不了,然后其次的话呢,你还实时更新,还浪费着我们整个这个,呃,相当于插入删除和更新的时候呢,还要考虑你的一个更新,还要影响我们的性能,这是最糟糕的是吧,你要长期隐藏的话呢,干脆就删掉得了。啊,然后再者说呢,我们这个主键呢,就不要设置成invisible了,主键呢,你要给它设成invisible了,它就失效了,那时候它就会又选择一个,呃,飞空的唯一约束的这个索引呢,去来当这个主见了不合适是吧?诶这个大家注意一下啊,行,那么这样的话呢,我们关于整个啊,咱们讲到这个,呃,虽然我们这个下篇当中的第二个这个篇章从我们讲这个诶。
41:19
呃,所以呢,这个数据结构啊,一直到我们这一章,呃,这里边呢,我们谈了很多这个东西啊,然后大家呢,做一个总结。然后下面开始呢,我们开始来讲这个事物的内容。
我来说两句