1、平时MySQL主要⽤哪个版本
出现概率: ★★★★
可以说说⾃⼰⽤的MySQL版本, ⽐如我⾃⼰⽤的版本是5.7版本,然后可以也简单聊聊这个版本的⼀些特点:
1)、安全性
在MySQL 5.7中,有不少安全性相关的改进。包括:
MySQL数据库初始化完成以后,会产⽣⼀个 root@localhost ⽤户,从MySQL 5.7开始,root⽤户的密码不再是空,⽽是随机产⽣⼀个密码,这也导致了⽤户安装5.7
时发现的与5.6版本⽐较⼤的⼀个不同点。
MySQL官⽅已经删除了test数据库,默认安装完后是没有test数据库的,就算⽤户创建了test库,也可以对test库进⾏权限控制了 MySQL 5.7版本提供了更为简单SSL安
全访问配置,并且默认连接就采⽤SSL的加密⽅式。
可以为⽤户设置密码过期策略,⼀定时间以后,强制⽤户修改密码。
2)、灵活性
MySQL 5.7的两个全新的功能,即JSON和generate column
CREATE TABLE t1 (jdoc JSON);
INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}');
3)、可⽤性
在线设置复制的过滤规则,不再需要重启MySQL,只需要停⽌SQL thread,修改完成以后,启动SQL thread
这个主要⼤致谈谈你⾃⼰的理解, 也可以根据⾃⼰的理解多延展⼀些, 提⾼⾯试的加分。
2、数据库三⼤范式是什么
出现概率: ★★★
第⼀范式:每个列都不可以再拆分。
第⼆范式:在第⼀范式的基础上,⾮主键列完全依赖于主键,⽽不能是依赖于主键的⼀部分。
第三范式:在第⼆范式的基础上,⾮主键列只依赖于主键,不依赖于其他⾮主键。
在设计数据库结构的时候,要尽量遵守三范式,如果不遵守,必须有⾜够的理由。⽐如性能。事实上我们经常会为了性能⽽妥协数据库的设计。
3、MySQL有关权限的表都有哪⼏个
出现概率: ★★★
MySQL服务器通过权限表来控制⽤户对数据库的访问,权限表存放在mysql数据库⾥,由mysql
install
db脚本初始化。这些权限表
分别 user , db , table_priv , columns_priv 和 host 。下⾯分别介绍⼀下这些表的结构和内容:
user 权限表:记录允许连接到服务器的⽤户帐号信息,⾥⾯的权限是全局级的。
db 权限表:记录各个帐号在各个数据库上的操作权限。
table_priv 权限表:记录数据表级的操作权限。
columns_priv 权限表:记录数据列级的操作权限。
host 权限表:配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。
4、MySQL的binlog有有⼏种录⼊格式?分别有什么区别?
出现概率: ★★★
MySQL的binlog有三种格式: statement,row和mixed。
1)、statement模式下,每⼀条会修改数据的sql都会记录在binlog中。不需要记录每⼀⾏的变化,减少了binlog⽇志量,节约了IO,提⾼性能。由于sql的执⾏是有上
下⽂的,因此在保存的时候需要保存相关的信息,同时还有⼀些使⽤了函数之类的语句⽆法被记录复制。
2)、row级别下,不记录sql语句上下⽂相关信息,仅保存哪条记录被修改。记录单元为每⼀⾏的改动,基本是可以全部记下来但是由于很多操作,会导致⼤量⾏的改动(⽐如
alter table),因此这种模式的⽂件保存的信息太多,⽇志量太⼤。
3)、mixed,⼀种折中的⽅案,普通操作使⽤statement记录,当⽆法使⽤statement的时候使⽤row。
此外,新版的MySQL中对row级别也做了⼀些优化,当表结构发⽣变化的时候,会记录语句⽽不是逐⾏记录。
平时⽤到哪些关系型数据库和⾮关系数据库, 可以谈谈你对它们的理解吗?
出现概率: ★★★★★
主要讲讲你⽤过的关系型数据库⽐如MySQL, ⾮关系数据库(NoSql数据库)⽐如Redis, MongoDB等等。
1)、事务⽅⾯
关系型数据库的最⼤特点就是事务的⼀致性, 所以对于订单模型 对⼀致性要求⽐较⾼的还是建议⽤MySQL。
2)、关系数据库的另⼀个特点就是其具有固定的表结构
其实在业务模型中, 表结构固定反⽽是⼀件好事, 没有约束的模型 更容易出问题。
3)、复杂SQL,特别是多表关联查询
NoSql是不⽀持JOIN 这种查询的。
4)、索引⽅式
关系型数据库:B树、哈希等
NoSql:键值索引
5)、并发⽀持
关系型数据库:通过事务和锁来⽀持并发,⾼并发情况下,执⾏效率较低。
NoSql:打破了传统关系型数据库范式的约束和事务⼀致性,因此并发性能⾼。
当然⾃⼰也可以多延伸看⼀下, 毕竟这个⾯试出现的概率还是蛮⾼的。
5、可以简单说说你对MySQL的逻辑架构了解吗?
出现概率: ★★★
第⼀层是服务器层,主要提供连接处理、授权认证、安全等功能。
第⼆层实现了 MySQL 核⼼服务功能,包括查询解析、分析、优化、缓存以及⽇期和时间等所有内置函数,所有跨存储引擎的功能都在这⼀层实现,例如存储过程、触发
器、视图等。
第三层是存储引擎层,存储引擎负责 MySQL 中数据的存储和提取。服务器通过 API 与存储引擎通信,这些接⼝屏蔽了不同存储引擎的差异,使得差异对上层查询过
程透明。除了会解析外键定义的 InnoDB 外,存储引擎不会解析 SQL,不同存储引擎之间也不会相互通信,只是简单响应上层服务器请求。
6、了解MySQL中的MVCC是什么?
出现概率: ★★★
MVCC 是多版本并发控制,在很多情况下避免加锁,⼤都实现了⾮阻塞的读操作,写操作也只锁定必要的⾏。
InnoDB 的MVCC 通过在每⾏记录后⾯保存两个隐藏的列来实现,这两个列⼀个保存了⾏的创建时间,⼀个保存⾏的过期时间间。不过存储的不是实际的时间值⽽是系
统版本号,每开始⼀个新的事务系统版本号都会⾃动递增,事务开始时刻的系统版本号会作为事务的版本号,⽤来和查询到的每⾏记录的版本号进⾏⽐较。MVCC 只能
在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下⼯作,因为 READ UNCOMMITTED 总是读取最新的数据⾏,⽽不是符合当前事务版本的数据⾏,⽽
SERIALIZABLE 则会对所有读取的⾏都加锁。
7、PostgreSQL相对于MySQL的优势
出现概率: ★★★★
在SQL的标准实现上要⽐MySQL完善,⽽且功能实现⽐较严谨;
存储过程的功能⽀持要⽐MySQL好,具备本地缓存执⾏计划的能⼒;
对表连接⽀持较完整,优化器的功能较完整,⽀持的索引类型很多,复杂查询能⼒较强;
PG主表采⽤堆表存放,MySQL采⽤索引组织表,能够⽀持⽐MySQL更⼤的数据量。
PG的主备复制属于物理复制,相对于MySQL基于binlog的逻辑复制,数据的⼀致性更加可靠,复制性能更⾼,对主机性能的影响也更⼩。
8、PostgreSQL和MySQL的⼀些区别
出现概率: ★★★★
这个其实出现的概率还⽐较⾼, ⾃⼰可以说⼏点就⾏了
MySQL不⽀持地理数据类型。
从9.2开始,PG⽀持json数据类型。相对于MySQL来说,PG对json的⽀持⽐较先进。他有⼀些json指定的操作符和函数,是的搜索json⽂本⾮常⾼效。9.4开始,可以
以⼆进制的格式存储json数据,⽀持在该列上进⾏全⽂索引(GIN索引),从⽽在json⽂档中进⾏快速搜索。
从5.7开始,MySQL⽀持json数据类型,⽐PG晚。也可以在json列上建⽴索引。然⽽对json相关的函数的⽀持⽐较有限。不⽀持在json列上全⽂索引。由于MySQL对SQL
⽀持的限制,在存储和处理json数据⽅⾯,MySQL不是⼀个很好的选择。
⼆、索引
0、概要
1、索引有哪些使⽤场景(重点)
2、索引的数据结构(b树,hash)
3、创建索引的原则是什么?(重中之重)
4、使⽤索引查询⼀定能提⾼查询的性能吗?为什么
5、索引有哪些优缺点?
6、讲⼀讲聚簇索引与⾮聚簇索引?
7、百万级别或以上的数据如何删除
8、什么是最左前缀原则?什么是最左匹配原则
9、数据库为什么使⽤B+树⽽不是B树
10、⾮聚簇索引⼀定会回表查询吗?
11、有哪些情况, 索引会失效, 可以简单说说吗?
1、索引有哪些使⽤场景
出现概率: ★★★★★
1)、应该创建索引的场景
主键应该创建主键索引。
频繁作为查询条件的字段应该创建索引。
查询中需要与其他表进⾏关联的字段应该创建索引。
需要排序的字段应该创建索引。
需要统计或分组的字段应该创建索引。
优先考虑创建复合索引。
2)、不应创建索引的场景
数据记录较少的表。
经常需要增删改操作的字段。
数据记录重复较多且分布平均的字段(如性别、状态等)。
索引的选择性是指索引列中不同值的数⽬与表中记录总数的⽐。
索引的选择性越接近于1,创建索引的价值就越⾼。反之就越低。
2、索引的数据结构(B+树,hash)
出现概率: ★★★★★
从存储结构上来划分:BTree索引(B-Tree或B+Tree索引),Hash索引,full-index全⽂索引,R-Tree索引。这⾥所描述的是索引存储时保存的形式,MySQL默认采
⽤的B+Tree, 这⾥主要讲讲B+树的特点:
1.⾮叶⼦节点不存储data,只存储索引(冗余),可以放更多的索引
2.叶⼦节点包含所有索引字段
3.叶⼦节点⽤指针连接,提⾼区间访问的性能 (快速定位范围查询,例如查询⼤于20,第⼀次io从根节点查询三次定位到20,然后通过后⾯的指针查询⼤于20的数
据,就不⽤再从根节点的重新再查询,提⾼性能,叶⼦节点开始结束节点也是⽤指针连接串起来的)
3、创建索引的原则是什么?
出现概率: ★★★★
1)、选择唯⼀性索引
2)、为经常需要排序、分组和联合操作的字段建⽴索引
3)、为常作为查询条件的字段建⽴索引
4)、限制索引的数⽬
索引的数⽬不是越多越好。每个索引都需要占⽤磁盘空间,索引越多,需要的磁盘空间就越⼤。修改表时,对索引的重构和更新很麻烦。越多的索引,会使更新表变得
很浪费时间。
5)、尽量使⽤数据量少的索引
如果索引的值很⻓,那么查询的速度会受到影响。例如,对⼀个CHAR(100)类型的字段进⾏全⽂检索需要的时间肯定要⽐对CHAR(10)类型的字段需要的时间要多。
6)、尽量使⽤前缀来索引
如果索引字段的值很⻓,最好使⽤值的前缀来索引。例如,TEXT和BLOG类型的字段,进⾏全⽂检索会很浪费时间。如果只检索字段的前⾯的若⼲个字符,这样可以提
⾼检索速度。
7)、最左前缀匹配原则
8)、查询时使⽤计算,会导致索引失效
4、使⽤索引查询⼀定能提⾼查询的性能吗?为什么
出现概率: ★★★★
不是所有的查询使⽤查询都能提⾼性能, ⽐如下⾯⼏个案例
像 like % xxx% 、不满⾜最左匹配原则的情况下并不能使⽤到建好的索引
MySQL 在可以使⽤多个索引的情况下,查询优化器会根据查询范围的数据量估算索引代价,最坏的是估算完毕后,发现这些索引的字段区分度不⾼,还不如扫全
表,于是 Mysql 扫全表了
如果索引的列⽐需要查询的列少,Mysql 会通过聚簇索引回表查询其他字段
如果索引的字段很⼤,每个⻚能存的条⽬就很少,读取时 IO 会消耗更多,⻚ Buffer 轮替的更快
5、索引有哪些优缺点?
出现概率: ★★★
1)、索引的优点
可以⼤⼤加快数据的检索速度,这也是创建索引的最主要的原因。
通过使⽤索引,可以在查询的过程中,使⽤优化隐藏器,提⾼系统的性能。
2)、索引的缺点
时间⽅⾯:创建索引和维护索引要耗费时间,具体地,当对表中的数据进⾏增加、删除和修改的时候,索引也要动态的维护,会降低增/改/删的执⾏效率;
空间⽅⾯:索引需要占物理空间。
6、讲⼀讲聚簇索引与⾮聚簇索引?
出现概率: ★★★★
在 InnoDB ⾥,索引B+Tree的叶⼦节点存储了整⾏数据的是主键索引,也被称之为聚簇索引,即将数据存储与索引放到了⼀块,找到索引也就找到了数据。