存储数据:可以一个表空间对应一张表的数据,可以一个表表空间对应多个表的数据。
存储格式:CSV以逗号分隔存储
InnoDB以二进制存储
查询修改流程:查询ID为1这条数据,先查看bufferpoll内存空间是否有数据,有数据直接返回,没有去磁盘读取数据,加载到内存中去。下一次读取直接去内存中去读取。
修改流程:先去查看这条数据在内存中是否存在,存在去修改内存中的数据,不存在去把他,查询出来,再到内存中修改数据,修改数据之后,记录到logbuffer暂存起来,再到去redolog里面异步修改到磁盘中去。
机械硬盘读取机制:一次521b
SSD读取机制:一次4KB
操作系统读取机制:一次4KB
InnoDB读取机制:一次16KB
索引机制:现在有99条数据,我要去查询ID为99的这条数据,假设一条记录占5kb,默认一次读取16kb,也就是一次只能读取三条按照正常查询最多可能需要,99/3=33次。随着数据量越大也会越慢,这时候就是使用索引机制,建立一个索引ID,ID索引里面会存数据的一个引用地址(引用地址会指向13或者36这样的数据),用于索引数据存储内容简单也很小,假设一条索引所占空间大小为1KB,这个时候我去查询ID为99的数据,我会先去查询索引,116的索引,这里的索引对的148的数据引用地址,没有在去查询1732的索引,这里的索引对应的是,48到96的数据引用地址,在去查询索引3349,对应数据是,97~144的数据引用地址,然后查询到了索引,在去根据索引查询一次数据。一共最多四次查询,当数据量越来越多,索引列也会越来越多,这时候还会出现多级索引,意思是索引管理索引。查询机制和上面一致
索引数据结构选择为什么是b+tree而不是其他的数据结构:
二叉树:特点左节点一定比根节点小,右节点一定比根结点大,这样的数据结构放在我们的数据库中,会提高查询效率,比如查询ID为5的数据,他顶部根结点为3,判断5比3大去右边节点,在去判断5比4大去到节点5,一共三次查询,比普通查询要快,但是数据极端的情况下会出现,树的层级过高或者只有右结点或者左节点,这样查询数据就又和普通查询一样了。
平衡二叉树:他解决了,二叉树里面树层级过高,他会进行树的旋转,进行数据挪动,这又出现了新的问题,每次添加一个新索引还要进行数据挪动,同时树节点在数据量多的时候还是过高。
btree:他解决了,平衡二叉树的频繁挪动,和树过高,他以数据块作为索引,而不是单个数据了,只有在数据块满了的时候,才会去进行树分裂,这就意味着数据层级减少了和数据挪动次数也减少了。但是存在一个问题是,它是以数据作为key,数据所占用空间会比较大,同时进行范围查询的时候,不方便。
b+tree:他解决了,btree已数据作为key和范围查询的问题,他已索引作为key,这样每个数据块就会存放很多个key去指向多个数据块。数据只存在于叶子节点里面,并且叶子节点是有序的,是一个链表的一个数据结构首尾相连,这样可以很快速的做范围查询。
数据库ACID:一致性,持久性,原子性和隔离性
一致性:事务不能破坏数据的完整性和一致性,一个事务执行前和执行后都要数据一致,不能出现a事务修改成功,b事务失败
持久性:数据库发生意外要保证数据完整
原子性:事务要么都成功要么都失败
隔离性:事务通常都会多个执行,要保证不会相互影响
redolog:当发生了一个修改操作,他会去内存中生成一个redolog日志在通过异步的方式去保存修改到磁盘中去,如果发生断电,或者宕机,也可以通过redolog进行数据恢复,从而保证了持久性和一致性
undolog:进行一个update或者insert的时候会生成一个事务ID,获取表名,与主键信息保证到undolog文件中,没什么问题的时候会拿到事务ID进行commit,异常会拿到事务ID进行rollback。在同一个事务中发生了一次读取,一次修改,又在一次发生读取,undolog可以通过拿到之前的事务ID读取之前的老数据。undolog保证了,原子性要么成功要么失败,保证了隔离性一个事务中,我可以通过事务ID去取到我原来读取的数据
是否走索引:具体情况要结合MySQL版本和数据量。版本太低有些优化机制没有就不会走索引,而高版本中新增了优化机制,数据量大小也会影响索引
最左匹配原则:我们要匹配一个东西的时候,需要从最左边一个个去匹配索引也不例外,当不确定最左边的数据时,mysql会全表扫描。
like语句:百分号在前不走索引(违反最左匹配原则),其他时候走索引
统计函数:走索引
索引列能不能为空:is null走索引,is not null不走索引(mysql执行优化器会认为你建立了索引的列应该很少数据会为空,直接去全表扫描了)
最左前缀原则:三个字段 a,b,c 当a缺失的时候,就不会走索引,b缺失c不会走索引,a范围查询,bc也会不走索引,b范围查询c也不会走索引。
where条件顺序:组合索引的情况下,要遵守最左前缀原则
要不要用union代替or:要
查询字段与字段值类型不匹配,索引就会失效吗:主键不会,其他索引会失效
exists与in:在数据小的时候用in,数据量大的时候用exists
非等于会不会走索引:看情况,在使用统计函数与ID和使用的是主键的时候走索引,其他时候不走
覆盖索引:查询的列是,索引列,不会去回表并且也会走索引
使用表关联还是子查询方式:建议使用表关联
表关联:表关联之间大小表顺序无所谓,MySQL执行优化器会去自动优化
分页优化当page过大会使得查询很慢:看图