共计 4023 个字符,预计需要花费 11 分钟才能阅读完成。
一、为什么用自增列作为主键
1、如果咱们定义了主键(PRIMARY KEY),那么 InnoDB 会抉择主键作为汇集索引。
如果没有显式定义主键,则 InnoDB 会抉择第一个不蕴含有 NULL 值的惟一索引作为主键索引。
如果也没有这样的惟一索引,则 InnoDB 会抉择内置 6 字节长的 ROWID 作为隐含的汇集索引(ROWID 随着行记录的写入而主键递增,这个 ROWID 不像 ORACLE 的 ROWID 那样可援用,是隐含的)。
2、如果表应用自增主键,那么每次插入新的记录,记录就会程序增加到以后索引节点的后续地位(主键插入性能最高,因为是程序的),当一页写满,就会主动开拓一个新的页
3、如果应用非自增主键(如果身份证号或学号等),因为每次插入主键的值近似于随机,因而每次新纪录都要被插到现有索引页得两头某个地位
此时 MySQL 不得不为了将新记录插到适合地位而挪动数据,甚至指标页面可能曾经被回写到磁盘上而从缓存中清掉,此时又要从磁盘上读回来,这减少了很多开销,同时频繁的挪动、分页操作造成了大量的碎片,失去了不够紧凑的索引构造,后续不得不通过 OPTIMIZE TABLE 来重建表并优化填充页面。
二、为什么在字段上增加索引能进步查问效率
1、增加索引的字段的值,是寄存在索引构建的 b +tree 的叶子节点上,并通过排序寄存;
2、如有相干查问进来,会通过索引创立的 b +tree 获取数据所在的数据页(b+tree 与二分查找法配合,只需几次 io 耗费就能够找到对应的数据页);
3、找到数据页后,将页加载到 buffer pool 中,再内存中从数据页中获取具体数据;
三、B+ 树索引和哈希索引的区别
B+ 树是一个均衡的多叉树结构,从根节点到每个叶子节点的高度差值不超过 1,而且同层级的节点间有指针互相链接,是有序的,如下图:
哈希索引就是采纳肯定的哈希算法,把键值换算成新的哈希值,检索时不须要相似 B + 树那样从根节点到叶子节点逐级查找,只需一次哈希算法即可, 是无序的,如下图所示:
四、哈希索引的劣势:
等值查问,哈希索引具备绝对优势(前提是:没有大量反复键值,如果大量反复键值时,哈希索引的效率很低,因为存在所谓的哈希碰撞问题。);MySQL 中在缓冲池中会开启自适应哈希索引。
五、说说你对 MySQL 汇集索引的了解
1、汇集索引的抉择:
会优先选择显示创立的主键作为汇集索引;
如果没有则抉择第一个创立的非空惟一索引作为汇集索引;
如都没有则零碎会创立一个实例级别的 rowid 作为汇集索引。
2、汇集索引的特点:
汇集索引的键值程序决定了表数据行的物理程序;
叶子节点上寄存的是整行数据;
一张表只能创立一个汇集索引。
六、说说 MySQL 如何优化一般索引的写操作
如一个一般索引的插入操作,对于非汇集索引叶子节点的插入不再是程序的了,这时就须要离散地拜访非汇集索引页,因为随机读取的存在而导致了插入操作性能降落。
MySQL 通过 insert buffer(插入缓冲)这个个性,来优化一般索引的写入操作。
对于非汇集索引的插入操作,不是每一次直接插入到索引页中,而是先判断插入的非汇集索引页是否在缓冲池中,若在,则直接插入;若不在,则先放入到一个 Insert Buffer 对象中。而后再以肯定的频率和状况进行 Insert Buffer 和辅助索引页子节点的 merge(合并)操作,这时通常能将多个插入合并到一个操作中(因为在一个索引页中),这就大大提高了对于非汇集索引插入的性能。
七、说说前缀索引应用的注意事项
建前缀索引时,最重要的是定义好长度,把握好度,即可节俭内存应用,又能够缩小额定的查问老本;前缀索引会对笼罩索引产生影响
八、说说索引影响着哪些方面
索引关乎着 MySQL 性能,影响着方方面面;
最次要的有这几点:晋升读性能;缩小锁期待和死锁;主从复制中 sql 线程利用索引进行回放,缩小主从提早。
九、什么状况下应不建或少建索引
首先大家要晓得索引的用途:索引就是用来从大量数据中获取少部分数据
1、常常更新的字段
2、反复值比拟高的字段
3、不常常查问的字段
4、如果表中记录数特地特地少,就不倡议在表中字段上创立索引;然而如果达到万级别以上,还是倡议创立索引
十、如何限度某 ip 段下的某个 ip 用户不容许登录
利用用户 ip 准确匹配的特点;如果原先用户 ergou@’192.168.58.%’,从新创立一个用户,如 ergou@’192.168.58.51’,并从新设置明码;这样就会用户通过用户 ergou 登录,就会匹配到 ergou@’192.168.58.51’。
十一、delete 与 truncate 区别
1、delete 是逻辑删除,按行删除数据,效率低,反对回滚;然而大家要晓得 delete 只是做了一个删除的标记,具体的删除是由 purge 线程实现删除,这才会开释空间
2.truncate 是物理删除,开释空间,速度快,不反对回滚
十二、生产中为什么倡议字段应用 not null 束缚
1、从性能来说,空值会寄存在 b +tree 的右边,造成索引性能降落
2、空值须要更多的存储空间,多 1 个字节(null 列上建设索引后)
3、造成统计后果的不精确,count(*) 会统计容许为 null 的字段,count(某字段)不含 null 值
十三、什么状况下应用不到索引
1、where 条件:
列进行计算:
explain select * from orders where o_custkey=o_custkey+1;
列应用函数:
explain select * from orders where o_custkey=ceil(o_custkey);
列进行隐式转换:
explain select * from emp where ename=007;
2、联结索引:用到范畴查问,只能用到局部索引
3、联表查问:
关联条件字符集不同,不走索引
关联条件的列类型不同,不走索引
4、其余状况:
。select * from emp;
。查问后果集大于数据量的 30%,不走索引
explain select * from emp where empno > 7000;
。索引自身生效
。like ‘%s’
explain select * from emp where ename like ‘%s’;
。not in(111,9999) 一般索引,如果是主键索引,会被优化为范畴查问,能够利用索引
explain select * from emp where empno not in(111, 9999);
。!=
explain select * from emp where empno != 9999;
十六、四种隔离级别
读未提交(read-uncommitted)RU,产生脏读
读已提交(read-committed)RC,不会产生脏读,产生不可反复读;
可反复读(repeatable-read)RR,不会产生脏读,不会产生不可反复读;会产生幻读(然而 innodb 默认会阻止产生幻读,通过锁实现);
可串行化(serializeable),不会产生脏读,不会产生不可反复读;不会产生幻读;完满合乎事务,但性能最低
十七、MVVC
MySQL InnoDB 存储引擎,实现的是基于多版本的并发控制协议——MVCC (Multi-Version Concurrency Control)
MVCC 最大的益处:读不加锁,读写不抵触。在读多写少的 OLTP 利用中,读写不抵触是十分重要的,极大的减少了零碎的并发性能,现阶段简直所有的 RDBMS,都反对了 MVCC。
MVCC 由数据页中的事务 id、回滚指针 +undo 日志,read view 形成
十八、事务两阶段提交
阶段 1:写 redo log,事务处于 prepare
阶段 2:写 binlog,事务处于 commit
写 binlog 胜利有 xid 事件,会将 xid 写入 redo log
十九、binlog 与 redo log 区别
①redo log 是 innodb 存储引擎独有的,binlog 是不辨别存储引擎
②记录内容不同,redo log 是物理逻辑日志,记录页的变动过程;binlog 是逻辑日志,记录事务具体操作的内容
③写入工夫不同,先写入 redo log,再写入 binlog
④redo log 是循环应用文件,binlog 每次新增一个文件
二十、如何疾速将一张大表迁徙到其余数据库实例
二十一、事务是如何实现的
二十二、MySQL 在 RR 隔离级别下如何阻止幻读
二十三、生产如何尽量避免死锁
二十四、不同隔离级别下、不同索引下 innodb 行锁的粒度是什么样的
二十五、mysqldump 备份原理
二十六、innodb 存储引擎行锁抵触问题
二十七、MySQL 在备份时,怎么做到一致性备份
二十八、主从复制原理
二十九、主从复制架构的瓶颈
三十、如何缩小主从复制提早
三十一、过滤复制会产生哪些问题
三十二、主从复制中断如何解决
三十三、1062 或者 1032 谬误,如何解决
三十四、如何将数据库回档到任意的一天
三十五、说说 5.7 和 8.0 在主从复制方面的改良
三十六、DML 变慢的起因