mysql面试题（2）

21.MySQL有哪些日志，分别是什么用处？

mysql日志一般分为5种

错误日志：-log-err (记录启动，运行，停止mysql时出现的信息)

二进制日志：-log-bin （记录所有更改数据的语句，还用于复制，恢复数据库用）

查询日志：-log （记录建立的客户端连接和执行的语句）

慢查询日志: -log-slow-queries （记录所有执行超过long_query_time秒的所有查 询）

更新日志: -log-update （二进制日志已经代替了老的更新日志，更新日志在MySQL5.1 中不再使用）

22.在哪些情况下会发生针对该列创建了索引但是在查询的时候并没有使用呢?

• 使用不等于查询
• 列参与了数学运算或者函数
• 在字符串like时左边是通配符.类似于’%aaa’
• 当mysql分析全表扫描比使用索引快的时候不使用索引
• 当使用联合索引,前面一个条件为范围查询,后面的即使符合最左前缀原则,也无法使用索 引.

23.为什么要尽量设定一个主键?

主键是数据库确保数据行在整张表唯一性的保障,即使业务上本张表没有主键,也建议添加一个 自增长的ID列作为主键.设定了主键之后,在后续的删改查的时候可能更加快速以及确保操作数 据范围安全。

24.主键使用自增ID还是UUID?

推荐使用自增ID,不要使用UUID.

因为在InnoDB存储引擎中,主键索引是作为聚簇索引存在的,也就是说,主键索引的B+树叶子节 点上存储了主键索引以及全部的数据(按照顺序),如果主键索引是自增ID,那么只需要不断向后 排列即可,如果是UUID,由于到来的ID与原来的大小不确定,会造成非常多的数据插入,数据移动, 然后导致产生很多的内存碎片,进而造成插入性能的下降.

总之,在数据量大一些的情况下,用自增主键性能会好一些.

*图片来源于《高性能MySQL》: 其中默认后缀为使用自增ID,_uuid为使用UUID为主键的测 试,测试了插入100w行和300w行的性能

关于主键是聚簇索引,如果没有主键,InnoDB会选择一个唯一键来作为聚簇索引,如果没有唯一 键,会生成一个隐式的主键。

25.字段为什么要求定义为notnull?

MySQL官网这样介绍:

NULL columns require additional space in the rowto record whether their values are NULL. For MyISAM tables, each NULL columntakes one bit extra, rounded up to the nearest byte.

null值会占用更多的字节,且会在程序中造成很多与预期不符的情况。

26.如果要存储用户的密码散列,应该使用什么字段进行存储?

密码散列,盐,用户身份证号等固定长度的字符串应该使用char而不是varchar来存储,这样可以 节省空间且提高检索效率。

27.varchar(10)和int(10)代表什么含义?

varchar的10代表了申请的空间长度,也是可以存储的数据的最大长度,而int的10只是代表了展示的长度,不足10位以0填充.也就是说,int(1)和int(10)所能存储的数字大小以及占用的空间都是相同的,只是在展示时按照长度展示。

28.MySQL的binlog有有几种录入格式?分别有什么区别?

有三种格式,statement,row和mixed.

• statement模式下,记录单元为语句.即每一个sql造成的影响会记录.由于sql的执行是有上 下文的,因此在保存的时候需要保存相关的信息,同时还有一些使用了函数之类的语句无法 被记录复制.
• row级别下,记录单元为每一行的改动,基本是可以全部记下来但是由于很多操作,会导致大 量行的改动(比如alter table),因此这种模式的文件保存的信息太多,日志量太大.
• mixed. 一种折中的方案,普通操作使用statement记录,当无法使用statement的时候使 用row.

此外,新版的MySQL中对row级别也做了一些优化,当表结构发生变化的时候,会记录语句而不是 逐行记录。

29.超大分页怎么处理?

超大的分页一般从两个方向上来解决.

• 数据库层面,这也是我们主要集中关注的(虽然收效没那么大),类似于select from table where age > 20 limit 1000000,10这种查询其实也是有可以优化的余地 的. 这条语句需要load1000000数据然后基本上全部丢弃,只取10条当然比较慢. 当时我们 可以修改为select from table where id in (select id from table where age > 20 limit 1000000,10).这样虽然也load了一百万的数据,但是由于索 引覆盖,要查询的所有字段都在索引中,所以速度会很快. 同时如果ID连续的好,我们还可以 select * from table where id > 1000000 limit 10,效率也是不错的,优化的 可能性有许多种,但是核心思想都一样,就是减少load的数据.
• 从需求的角度减少这种请求….主要是不做类似的需求(直接跳转到几百万页之后的具体某 一页.只允许逐页查看或者按照给定的路线走,这样可预测,可缓存)以及防止ID泄漏且连续 被人恶意攻击.

解决超大分页,其实主要是靠缓存,可预测性的提前查到内容,缓存至redis等k-V数据库中,直接 返回即可.

在阿里巴巴《Java开发手册》中,对超大分页的解决办法是类似于上面提到的第一种。

30.关心过业务系统里面的sql耗时吗?统计过慢查询吗?对慢查询都怎么优化过?

在业务系统中,除了使用主键进行的查询,其他的我都会在测试库上测试其耗时,慢查询的统计主 要由运维在做,会定期将业务中的慢查询反馈给我们。

慢查询的优化首先要搞明白慢的原因是什么? 是查询条件没有命中索引?是load了不需要的数据 列?还是数据量太大? 所以优化也是针对这三个方向来的

首先分析语句,看看是否load了额外的数据,可能是查询了多余的行并且抛弃掉了,可能是 加载了许多结果中并不需要的列,对语句进行分析以及重写。

分析语句的执行计划,然后获得其使用索引的情况,之后修改语句或者修改索引,使得语句 可以尽可能的命中索引。

如果对语句的优化已经无法进行,可以考虑表中的数据量是否太大,如果是的话可以进行横 向或者纵向的分表。

31.什么是存储过程？有哪些优缺点？

存储过程是一些预编译的SQL语句。

1、更加直白的理解：存储过程可以说是一个记录集，它是由一些T-SQL语句组成的代码块， 这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这 个代码块取一个名字，在用到这个功能的时候调用他就行了。

2、存储过程是一个预编译的代码块，执行效率比较高,一个存储过程替代大量T_SQL语句 ， 可以降低网络通信量，提高通信速率,可以一定程度上确保数据安全

但是,在互联网项目中,其实是不太推荐存储过程的,比较出名的就是阿里的《Java开发手册》中 禁止使用存储过程,我个人的理解是,在互联网项目中,迭代太快,项目的生命周期也比较短,人员 流动相比于传统的项目也更加频繁,在这样的情况下,存储过程的管理确实是没有那么方便,同时, 复用性也没有写在服务层那么好。

32.说一说三个范式

第一范式: 每个列都不可以再拆分.

第二范式: 非主键列完全依赖于主键,而不能是依赖于主键的 一部分.

第三范式: 非主键列只依赖于主键,不依赖于其他非主键。

在设计数据库结构的时候,要尽量遵守三范式,如果不遵守,必须有足够的理由.比如性能. 事实上 我们经常会为了性能而妥协数据库的设计。

33.什么情况下应不建或少建索引

1、表记录太少

2、经常插入、删除、修改的表

3、数据重复且分布平均的表字段，假如一个表有10万行记录，有一个字段A只有T和F两种 值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询 速度。

4、经常和主字段一块查询但主字段索引值比较多的表字段

34.什么是表分区？

表分区，是指根据一定规则，将数据库中的一张表分解成多个更小的，容易管理的部分。从逻 辑上看，只有一张表，但是底层却是由多个物理分区组成。

35.表分区与分表的区别

分表：指的是通过一定规则，将一张表分解成多张不同的表。比如将用户订单记录根据时间成 多个表。

分表与分区的区别在于：分区从逻辑上来讲只有一张表，而分表则是将一张表分解成多张表。

36.表分区有什么好处？

1、存储更多数据。分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设 备。和单个磁盘或者文件系统相比，可以存储更多数据

2、优化查询。在where语句中包含分区条件时，可以只扫描一个或多个分区表来提高查询效 率；涉及sum和count语句时，也可以在多个分区上并行处理，最后汇总结果。

3、分区表更容易维护。例如：想批量删除大量数据可以清除整个分区。

4、避免某些特殊的瓶颈，例如InnoDB的单个索引的互斥访问，ext3问价你系统的inode锁竞 争等。

37.MVVC了解过吗

MySQL InnoDB存储引擎，实现的是基于多版本的并发控制协议——MVCC (Multi-Version Concurrency Control)

注：与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control

MVCC最大的好处：读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突是非常 重要的，极大的增加了系统的并发性能，现阶段几乎所有的RDBMS，都支持了MVCC。

1. LBCC：Lock-Based Concurrency Control，基于锁的并发控制

2. MVCC：Multi-Version Concurrency Control

基于多版本的并发控制协议。纯粹基于锁的并发机制并发量低，MVCC是在基于锁的并 发控制上的改进，主要是在读操作上提高了并发量。

38.在MVCC并发控制中，读操作可以分成哪几类？

1. 快照读 (snapshot read)：读取的是记录的可见版本 (有可能是历史版本)，不用加锁 （共享读锁s锁也不加，所以不会阻塞其他事务的写）

2. 当前读 (current read)：读取的是记录的最新版本，并且，当前读返回的记录，都会加 上锁，保证其他事务不会再并发修改这条记录

39.行级锁定的优点

1、当在许多线程中访问不同的行时只存在少量锁定冲突。

2、回滚时只有少量的更改

3、可以长时间锁定单一的行。

40.行级锁定的缺点

1. 比页级或表级锁定占用更多的内存。

2. 当在表的大部分中使用时，比页级或表级锁定速度慢，因为你必须获取更多的锁。

3. 如果你在大部分数据上经常进行GROUP BY操作或者必须经常扫描整个表，比其它锁定 明显慢很多。

4. 用高级别锁定，通过支持不同的类型锁定，你也可以很容易地调节应用程序，因为其锁 成本小于行级锁定。