Mysql:常见问题

1、缓冲池

2、查询缓存和缓冲池的区别

查询缓存是把缓存结果缓存起来,也就意味着只有相同的查询操作才会命中查询缓存,而且更新操作就会破坏缓存,因此Mysql的查询缓存命中率不高,在Mysql8.0中已经弃用了查询缓存
缓冲池的作用是把数据页放到缓冲池中,这样在读取或修改数据的时候可以更新或者查找缓冲池中的数据,数据页中包含了很多行数据,即使这次没用到,下次也可能会用到
缓冲池并不等于查询缓存,它们都是通过缓存的机制来提升效率,但缓冲池服务于数据库整体的I/O操作,而查询缓存服务于Sql查询和查询结果集的,因为命中条件苛刻,只要数据表发生变化,查询缓存就会失效

1、innodb_file_per_table

on:每个InnoDB表数据存储在一个以.ibd为后缀的文件中
off:表的数据放在系统共享表空间,也就是跟数据字典放在一起
建议将这个值设置为on,因为,一个表单独存储一个文件更容易管理,而且不需要这个表的时候,通过drop table命令,系统就会直接删除这个文件;而如果是放在共享表空间中,即使表删掉了,空间也是不会回收的

2、delete删表

Innodb只会把删除的数据标记为删除,磁盘文件的大小并不会缩小,如果之后要在这个位置再插入记录,这个记录可能会被复用
Innodb的数据是按页存储的,如果删掉了一个数据页的所有记录,整个数据页就可以被复用了
记录的复用跟数据页的复用是不同的,记录的复用只限于符合范围条件的数据,而整个数据页被摘掉后,可以复用到任何位置
如果用delete命令把整个表的数据删除,结果就是所有的数据页都会标记为可复用,但是磁盘上,文件不会变小,这些可以复用而没有被使用的空间,看起来就像是’空洞’
不仅删除数据会造成空洞,插入数据也会造成空洞,数据如果是随机插入的,就可能造成索引的页分裂
也就是说,经过大量增删改的表,都是可能存在空洞的,如果能把这些空洞去掉,就能达到收缩表空间的目的,而重建表就能达到这样的目的

3、重建表

alter table A engine = InnoDB;
会去除表A中的数据空洞,相当于drop表A,然后再按主键索引的顺序新建表A
Online DDL
允许表A向表B插入数据的过程中,对表A进行增删改

4、optimize table、analyze table、alter table这三种方式新建表的区别?

alter table t engine = InnoDB(也就是recreate)
analyze table t 其实不是重建表,只是对表的索引信息做重新统计,没有修改数据,这个过程加了MDL读锁
optimize table t 等于recreate + analyze

数据库服务器有两种存储介质:硬盘和内存。内存是临时存储,硬盘是永久存储介质,索引只能放在磁盘中,因此磁盘的I/O次数决定了索引数据结构查询性能的好坏
如果使用二叉树或者平衡二叉查找树,树的高度比较大,磁盘I/O操作多,影响查询效率
BTree和B+Tree都可以作为索引结构,也就是平衡的多路搜索树(m叉树),高度要远小于平衡二叉树的高度,两者的区别是:BTree所有的节点都会存储数据,B+Tree只有叶子节点会存储数据,非叶子节点只会存储索引,在进行范围查询的时候,B+Tree可以直接遍历叶子节点,BTree只能进行中序遍历