最近有一些朋友问我一些 MySQL 相关的面试题, 有一些比较基础, 有些比较偏. 这里就总结一些常见的 MySQL 面试题吧, 都是自己平时工作的总结以及经验. 大家看完, 能避开很多坑. 而且很多问题, 都是面试中也经常问到! 希望能对大家的面试有一些帮助!!!
比如, 下面这些问题:
1, 为什么一定要设一个主键?
2, 你们主键是用自增还是 UUID?
3, 自增主机用完了怎么办?
4, 主键为什么不推荐有业务含义?
5, 货币字段用什么类型??
6, 时间字段用什么类型?
7, 为什么不直接存储图片, 音频, 视频等大容量内容?
8, 表中有大字段 X(例如: text 类型), 且字段 X 不会经常更新, 以读为为主, 那么是拆成子表好? 还是放一起好?
9, 字段为什么要定义为 NOT NULL?
10,where 执行顺序是怎样的
11, 应该在这些列上创建索引?
12,MySQL 联合索引?
13, 什么是最左前缀原则?
14, 什么情况下应不建或少建索引?
15,MySQL 数据库 CPU 飙升到 100% 的话他怎么处理?
其实要想把这些问题具体的原理原因将清楚, 那每一个问题都可以啰嗦出一篇文章来. 所以, 在这里我可能不会深入的说明 MySQL 底层的原理. 只会把工作中, 我们的经验总结, 有可能你会觉得, 就是这样的做的, 不这么做可饿能就会掉坑里去. 当然, 我给的回答可能并非标准答案, 毕竟是自己的一些工作总结. 各位读者有更好的回答, 也欢迎交流!
1: 为什么要一定要设置主键?
其实这个不是一定的, 有些场景下, 小系统或者没什么用的表, 不设置主键也没关系, MySQL 最好是用自增主键, 主要是以下两个原因: 果定义了主键, 那么 InnoDB 会选择主键作为聚集索引, 如果没有显式定义主键, 则 innodb 会选择第一个不包含有 NULL 值的唯一索引作为主键索引, 如果也没有这样的唯一索引, 则 innodb 会选择内置 6 字节长的 ROWID 作为隐含的聚集索引. 所以, 反正都要生成一个主键, 那你还不如自己指定一个主键, 提高查询效率!
2: 主键是用自增还是 UUID?
最好是用自增主键, 主要是以下两个原因:
1. 如果表使用自增主键, 那么每次插入新的记录, 记录就会顺序添加到当前索引节点的后续位置, 当一页写满, 就会自动开辟一个新的页.
2. 如果使用非自增主键 (如 uuid), 由于每次插入主键的值近似于随机, 因此每次新纪录都要被插到索引页的随机某个位置, 此时 MySQL 为了将新记录插到合适位置而移动数据, 甚至目标页面可能已经被回写到磁盘上而从缓存中清掉, 此时又要从磁盘上读回来, 这增加了很多开销, 同时频繁的移动, 分页操作造成索引碎片, 得到了不够紧凑的索引结构, 后续不得不通过 OPTIMIZE TABLE 来重建表并优化填充页面.
不过, 也不是所有的场景下都得使用自增主键, 可能场景下, 主键必须自己生成, 不在乎那些性能的开销. 那也没有问题.
3: 自增主机用完了怎么办?
在 MySQL 中, Int 整型的范围 (-2147483648~2147483648), 约 20 亿! 因此不用考虑自增 ID 达到最大值这个问题. 而且数据达到千万级的时候就应该考虑分库分表了.
4: 主键为什么不推荐有业务含义?
最好是主键是无意义的自增 ID, 然后另外创建一个业务主键 ID,
因为任何有业务含义的列都有改变的可能性, 主键一旦带上了业务含义, 那么主键就有可能发生变更. 主键一旦发生变更, 该数据在磁盘上的存储位置就会发生变更, 有可能会引发页分裂, 产生空间碎片.
还有就是, 带有业务含义的主键, 不一定是顺序自增的. 那么就会导致数据的插入顺序, 并不能保证后面插入数据的主键一定比前面的数据大. 如果出现了, 后面插入数据的主键比前面的小, 就有可能引发页分裂, 产生空间碎片.
5: 货币字段用什么类型?
货币字段一般都用 Decimal 类型,
float 和 double 是以二进制存储的, 数据大的时候, 可能存在误差. 看下面这个图就明白了:
6: 时间字段用什么类型?
这个看具体情况和实际场景, timestamp ,datatime ,bigint 都行! 把理由讲清楚就行!
timestamp, 该类型是四个字节的整数, 它能表示的时间范围为 1970-01-01 08:00:01 到 2038-01-19 11:14:07.2038 年以后的时间, 是无法用 timestamp 类型存储的.
但是它有一个优势, timestamp 类型是带有时区信息的. 一旦你系统中的时区发生改变, 例如你修改了时区, 该字段的值会自动变更. 这个特性用来做一些国际化大项目, 跨时区的应用时, 特别注意!
datetime, 占用 8 个字节, 它存储的时间范围为 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59. 显然, 存储时间范围更大. 但是它坑的地方在于, 他存储的是时间绝对值, 不带有时区信息. 如果你改变数据库的时区, 该项的值不会自己发生变更!
bigint, 也是 8 个字节, 自己维护一个时间戳, 查询效率高, 不过数据写入, 显示都需要做转换.
7: 为什么不直接存储图片, 音频, 视频等大容量内容?
我们在实际应用中, 都是文件形式存储的. MySQL 中, 只存文件的存放路径. 虽然 MySQL 中 blob 类型可以用来存放大容量文件, 但是, 我们在生产中, 基本不用!
主要有如下几个原因:
1. MySQL 内存临时表不支持 TEXT,BLOB 这样的大数据类型, 如果查询中包含这样的数据, 查询效率会非常慢.
2. 数据库特别大, 内存占用高, 维护也比较麻烦.
3. binlog 太大, 如果是主从同步的架构, 会导致主从同步效率问题!
因此, 不推荐使用 blob 等类型!
8: 表中有大字段 X(例如: text 类型), 且字段 X 不会经常更新, 以读为为主, 那么是拆成子表好? 还是放一起好?
其实各有利弊, 拆开带来的问题: 连接消耗; 不拆可能带来的问题: 查询性能, 所以要看你的实际情况, 如果表数据量比较大, 最好还是拆开为好. 这样查询速度更快.
9: 字段为什么要定义为 NOT NULL?
一般情况, 都会设置一个默认值, 不会出现字段里面有 null, 又有空的情况. 主要有以下几个原:
1. 索引性能不好, MySQL 难以优化引用可空列查询, 它会使索引, 索引统计和值更加复杂. 可空列需要更多的存储空间, 还需要 MySQL 内部进行特殊处理. 可空列被索引后, 每条记录都需要一个额外的字节, 还能导致 MYisam 中固定大小的索引变成可变大小的索引.
2. 如果某列存在 null 的情况, 可能导致 count() 等函数执行不对的情况. 看一下 2 个图就明白了:
3. sql 语句写着也麻烦, 既要判断是否为空, 又要判断是否为 null 等.
10:where 执行顺序是怎样的?
where 条件从左往右执行的, 在数据量小的时候不用考虑, 但数据量多的时候要考虑条件的先后顺序, 此时应遵守一个原则: 排除越多的条件放在第一个.
11: 应该在这些列上创建索引:
在经常需要搜索的列上, 可以加快搜索的速度; 在作为主键的列上, 强制该列的唯一性和组织表中数据的排列结构; 在经常用在连接的列上, 这些列主要是一些外键, 可以加快连接的速度; 在经常需要根据范围进行搜索的列上创建索引, 因为索引已经排序, 其指定的范围是连续的; 在经常需要排序的列上创建索引, 因为索引已经排序, 这样查询可以利用索引的排序, 加快排序查询时间; 在经常使用在 WHERE 子句中的列上面创建索引, 加快条件的判断速度.
12:MySQL 联合索引
联合索引是两个或更多个列上的索引. 对于联合索引: MySQL 从左到右的使用索引中的字段, 一个查询可以只使用索引中的一部份, 但只能是最左侧部分. 例如索引是 key index (a,b,c). 可以支持 a , a,b , a,b,c 3 种组合进行查找, 但不支持 b,c 进行查找 . 当最左侧字段是常量引用时, 索引就十分有效.
利用索引中的附加列, 您可以缩小搜索的范围, 但使用一个具有两列的索引 不同于使用两个单独的索引. 复合索引的结构与电话簿类似, 人名由姓和名构成, 电话簿首先按姓氏对进行排序, 然后按名字对有相同姓氏的人进行排序. 如果您知 道姓, 电话簿将非常有用; 如果您知道姓和名, 电话簿则更为有用, 但如果您只知道名不姓, 电话簿将没有用处.
13: 什么是最左前缀原则?
最左前缀原则指的是, 如果查询的时候查询条件精确匹配索引的左边连续一列或几列, 则此列就可以被用到. 如下:
- select * from user where name=xx and city=xx ; // 可以命中索引
- select * from user where name=xx ; // 可以命中索引
- select * from user where city=xx ; // 无法命中索引
这里需要注意的是, 查询的时候如果两个条件都用上了, 但是顺序不同, 如 city= xx and name =xx, 那么现在的查询引擎会自动优化为匹配联合索引的顺序, 这样是能够命中索引的.
由于最左前缀原则, 在创建联合索引时, 索引字段的顺序需要考虑字段值去重之后的个数, 较多的放前面. ORDER BY 子句也遵循此规则.
14: 什么情况下应不建或少建索引
表记录太少
经常插入, 删除, 修改的表
数据重复且分布平均的表字段, 假如一个表有 10 万行记录, 有一个字段 A 只有 T 和 F 两种值, 且每个值的分布概率大约为 50%, 那么对这种表 A 字段建索引一般不会提高数据库的查询速度.
经常和主字段一块查询但主字段索引值比较多的表字段
15: 问了下 MySQL 数据库 CPU 飙升到 100% 的话他怎么处理?
1. 列出所有进程 show processlist 观察所有进程 多秒没有状态变化的 (干掉)
2. 查看慢查询, 找出执行时间长的 sql;explain 分析 sql 是否走索引, sql 优化;
3. 检查其他子系统是否正常, 是否缓存失效引起, 需要查看 buffer 命中率;
总结
以上就是一些最基础的总结, 希望大家有所收获吧!
来源: https://www.cnblogs.com/zhangweizhong/p/11193080.html