mysql第四天

B+树索引适用的条件(左缀原则)

二级索引包含所有索引值和主键,所以当查询值可以覆盖索引值的时候不需要进行回表,这也是尽量不使用select * 的原因,因为我们二级索引都是按照从左到右的顺序排列大小,比如建立联合索引name,birthday,phone_number,索引中的排序如图

image_1dmo2n5c11ij019unpjtpf21tdr9.png-121.1kB

所以当我们排序按照从左到右的顺序或者左边相等,右边排序,就可以使用到索引,例如order by name,birthday phone_number. select name where name=”a” order by birthday.因为字符串一般排序都是按照前边的字母序,比如先比较第一个字母大小,然后再比较第二个以此类推,所以前缀也是可以用到的比如 like “ac%”,group by同样可以使用索引,只要顺序和建立的相同.但是不要随便建立索引,第一是占用空间,第二我们在增删改数据的时候索引同时需要调整,也是很耗时间的.

eg:主键尽量按照顺序插入,不然随机插入可能会造成页分裂.消耗性能,索引列尽可能小,重复度低

MySQL 的数据目录

每个库对应一个文件夹,每次建表会有一个ibd文件和frm文件(8.0之后都到ibd文件里面了),这个文件可以存放到系统表空间或者独立表空间(innodb)

myisam 会有山中文件 frm(表结构) myd(数据) myi(索引)

由于文件和表名一致,所以表名和大小受限于文件命名的最大值和系统的最大存储.\

MySQL系统数据库简介

我们前边提到了MySQL的几个系统数据库,这几个数据库包含了MySQL服务器运行过程中所需的一些信息以及一些运行状态信息,我们现在稍微了解一下。

  • mysql这个数据库贼核心,它存储了MySQL的用户账户和权限信息,一些存储过程、事件的定义信息,一些运行过程中产生的日志信息,一些帮助信息以及时区信息等。
  • information_schema这个数据库保存着MySQL服务器维护的所有其他数据库的信息,比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引吧啦吧啦。这些信息并不是真实的用户数据,而是一些描述性信息,有时候也称之为元数据。
  • performance_schema这个数据库里主要保存MySQL服务器运行过程中的一些状态信息,算是对MySQL服务器的一个性能监控。包括统计最近执行了哪些语句,在执行过程的每个阶段都花费了多长时间,内存的使用情况等等信息。
  • sys这个数据库主要是通过视图的形式把information_schemaperformance_schema结合起来,让程序员可以更方便的了解MySQL服务器的一些性能信息。

Mysql 第三天

B+树索引

昨天主要是单个页面中的查询,首先页的存储是固定的16kb一旦超过了就会有多个页,所以就会产生页目录

image_1caba0afo11fa1cli1nu070m16bg1j.png-119.1kB

于此同时页目录也会有满的时候所以,页目录也会有一个”目录”

image_1cacafpso19vpkik1j5rtrd17cm3a.png-158.1kB

此上就是B+树索引

对于聚簇索引来说我们页面里面都是根据主键排序的,叶子节点里面也有一列里面,所有的数据,但是对于二级索引来说,排序就是按照相关的索引排序,但是有这么一种情况,主键是不能重复的,但是二级索引没有这个限制一旦重复了,我插入一个新的值,应该如何插入那?例如下图,索引值都是橙色的1,我再次插入一个1的时候应该插入到页面5还是页面4那?所以二级索引不光会用索引值排序,一旦索引排序一样,还会用主键排序,这样就可以保持唯一性.

image_1cp9vthl71h9n8091dkdjek16qg1j.png-58.6kB

MyISAM中的索引方案简单介绍

mysiam的索引和数据是分开存放的,通过索引是可以找到对应数据的内存偏移量,然后再找到对应的真实数据,这个和innodb是不一样的,innodb都是通过查找主键,然后再通过聚簇索引的叶子节点找到真正的数据.

Mysql 第二天

InnoDB记录存储结构

InnoDB采取的方式是:将数据划分为若干个页,以页作为磁盘和内存之间交互的基本单位,InnoDB中页的大小一般为 16 KB。也就是在一般情况下,一次最少从磁盘中读取16KB的内容到内存中,一次最少把内存中的16KB内容刷新到磁盘中。

InnoDB行格式

个人理解行格式主要就是记录mysql的数据存储格式,mysql读取到行数据需要知道,每个字段的大小,真实的数据,是否为空,是否删除等信息,这些信息如何存储到,并且可以解析就是行格式的定义

COMPACT行格式
image_1c9g4t114n0j1gkro2r1h8h1d1t16.png-42.4kB

Compact行格式中,把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位,从而形成一个变长字段长度列表,各变长字段数据占用的字节数按照列的顺序逆序存放,我们再次强调一遍,是逆序存放!(之所以逆序存放,是因为我们读取的时候指针会放在真实记录和额外记录中间,所以逆序更容易获取到前边的数据,提高缓存命中率)

比如一个表有c1,c2,c3三个列,新建顺序同样也是1,2,3内容分别是aaaa,bbb,d换成十六进制就是0x04,0x03,0x01,存放如图,逆序放置

image_1c9gbruvo504dlg1qsf19nbeu878.png-37kB

null值列表,用0和1表示是否为null,0时不为null,同时mysql都是整数字节,即即使有3个列,也得申请8个bit空间位,没有的用0填写(只记录可以为null的列,非null的列比如主键,不记录,因为没有意义)

行溢出

mysql的每一页都是由大小的,如果真实数据超过大小,在真实记录数据的地方,有两种操作,一种是记录部分真实信息,剩一部分空间存一个地址,这个地址可以引到真实的信息,另一种是直接都放到引入的页

InnoDB数据页结构

mysql定义一页至少要有两行数据

上边的mysql的行记录里面有一些头信息,我们在页里面需要查询相关数据,我们这个时候就需要知道最大值和最小值,方便二分,同时msyql会自动生成两个伪记录(最大值,最小值记录)主要是为了方便查找

image_1c9qs1mn2t3j1nt344116nk15uf2p.png-119.7kB

槽属于page Directory部分,我们通过这个槽来二分

image_1d6g64af2sgj1816ktl1q22dehp.png-189.1kB

删除某个几率的时候同理,只需要将next_record指向下一个即可,不会立即删除空间,如果有新的记录插入,会将这部分空间重新利用

mysql第一天

字符集和比较规则

1.服务器,库,表,列都可以单独设置字符集和比较规则

2.没有定义的话向上兼容,比如列没有定义字符集继承表的,表没有继承库的,库没有继承服务器的

3.字符集和比价规则是绑定的,如果只改一个会联动的改掉相关的 eg:utf8和gbk的比较规则就是不同的,不可能用gbk的比较规则去比较utf8字符集,反过来同理,这个会影响我们order by的查询或者列的比较

4.从client发送请求到服务端返回给client一共有三个字符集会影响

InnoDB行记录存储结构

1.InnoDB采取的方式是:将数据划分为若干个页,以页作为磁盘和内存之间交互的基本单位,InnoDB中页的大小一般为 16 KB。也就是在一般情况下,一次最少从磁盘中读取16KB的内容到内存中,一次最少把内存中的16KB内容刷新到磁盘中。

2.创建或者修改表的时候可以修改相关的”行模式”

3.一行记录不光有真实信息,还有每一列的存储大小(毕竟varchar这种是变长的,需要真实记录一下占用了多少字符),null值列,头信息(是否删除的标记位等),真实数据,逆序排列比如建表的时候是列A,列B,列C,在变长字段中就是列C长度,列B长度,列A长度

4.我们所有存储最少都是按照一个字节存储,所以一旦M(varchar(M)或者char(M))*L(该列字符集一个字符占用的字节数)>255(1个字节可以表示的大小),那就必须要用两个字节表示,一共占用了多大字节,在大的话mysql是不允许的(比如insert varchar(7000000),mysql会直接报错,建议你使用text或者blog),只能用blog或者text.

5.null列,0代表是非null,1代表null,同样也是整字节不够用0补,同样是逆序. eg: 不是所有的列都会在里面,如果该列是not null的不会再里面

6.溢出页:行的数据比较多16kB已经放不下了,就会溢出,有两种模式一个是在记录部分真实数据的基础上后边在加一个其他页地址,索引到其他页,每一页之间用链表链接,另外一种是真实数据也不记录一部分了,直接只有一个其他页索引,真实的数据都放到索引页中.