Mysql 面试题
1. CHAR 和 VARCHAR 的区别?
- CHAR 和 VARCHAR 类型在存储和检索方面有所不同。
- CHAR 列长度固定为创建表时声明的长度,长度值范围是 1 到 255。
- 当 CHAR 值被存储时,它们被用空格填充到特定长度,检索 CHAR 值时需删除尾随空格。
2. TIMESTAMP 在 UPDATE CURRENT_TIMESTAMP 数据类型上做什么?
创建表时 TIMESTAMP 列用 Zero 更新。只要表中的其他字段发生更改,UPDATECURRENT_TIMESTAMP 修饰符就将时间戳字段更新为当前时间。
3. 主键和候选键有什么区别?
表格的每一行都由主键唯一标识, 一个表只有一个主键。
主键也是候选键。按照惯例,候选键可以被指定为主键,并且可以用于任何外键引用。
4、数据库隔离级别有哪些,各自的含义是什么,MySQL默认的隔离级别是多少?
隔离级别有四种。
- 未提交读:是最低的隔离级别,其含义是允许一个事务读取另外一个事务没有提交的数据。会出现脏读。
- 读写提交:是指一个事务只能读取另一个事务已经提交的数据,不能读取未提交的数据。克服了脏读,但会出现不可重复读现象。
- 可重复读:克服读写提交中出现的不可重复读现象。但会出现幻读现象。
- 串行化:数据库中最高的隔离级别,她会要求所有的SQL都会按照顺序执行,这样就可以克服上述隔离级别出现的各种问题,所以它能完全保证数据的致性。
MySQL默认的隔离级别是可重复读。
5. MySQL有哪些存储引擎,各自优缺点。
MySQL支持 InnoDB、MyISAM、 MEMORY等存储引擎。
InnoDB引擎(MySQL5.5 以后默认使用) :
- 灾难恢复性好
- 支持事务
- 使用行级锁和表级锁,能支持更多的并发量
- 查询不加锁
- 支持外键关联
- 支持热备份
- 实现缓冲管理
MyISAM引擎:
- 不支持事务
- 使用表级锁,并发性差
- 主机宕机后,MyISAM 表易损坏,灾难恢复性不佳
- 可以配合锁,实现操作系统下的复制备份、迁移
- 只缓存索引
- 数据紧凑存储,因此可获得更小的索引和更快的全表扫描性能
两者主要区别:
- InnoDB支持事务,MyISAM不支持事务处理等高级处理。
- InnoDB支持行级锁,而MyISAM仅支持表级锁。
- MyISAM类型的表强调的是性能,其执行速度比InnoDB类型更快。
- MyISAM适合查询以及插入为主的应用,InnoDB适合频繁修改以及涉及到安全性较高的应用。
- InnoDB支持外键,MyISAM 不支持。
- MyISAM支持全文搜索,而InnoDB 1.2.x版本后才支持。
- 对于自增长的字段,InnoDB中必须包含只有该字段的索引,但是在MyISAM表中可以和其他字段一起建立联合索引。
6. MySQL 数据库服务器性能分析的方法命令有哪些?
Show status 一些值得监控的变量值: Bytesreceived 和 Bytessent 和服务器之间来往的流量。 Com 服务器正在执行的命令。 Created 在查询执行期限间创建的临时表和文件。 Handler 存储引擎操作。 Select 不同类型的联接执行计划。Sort_* 几种排序信息。 Show session status like ‘Select’; Show profiles SET profiling=1; Show profiles\G Show profile;
7. MySQL 中 InnoDB 引擎的行锁是通过加在什么上完成(或称实现)的?
InnoDB 行锁是通过给索引上的索引项加锁来实现的,这一点 MySQL 与Oracle 不同,后者是通过在数据块中对相应数据行加锁来实现的。InnoDB 这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB 才使用行级锁,否则,InnoDB 将使用表锁!
8. 若一张表中只有一个字段 VARCHAR(N)类型,utf8 编码,则 N 最大值为多少(精确到数量级即可)?
由于 utf8 的每个字符最多占用 3 个字节。而 MySQL 定义行的长度不能超过65535,因此 N 的最大值计算方法为:(65535-1-2)/3。减去 1 的原因是实际存储从第二个字节开始,减去 2 的原因是因为要在列表长度存储实际的字符长度,除以 3 是因为 utf8 限制:每个字符最多占用 3 个字节。
9. [SELECT *] 和[SELECT 全部字段]的 2 种写法有何优缺点?
- 前者要解析数据字典,后者不需要
- 结果输出顺序,前者与建表列顺序相同,后者按指定字段顺序。
- 表字段改名,前者不需要修改,后者需要改
- 后者可以建立索引进行优化,前者无法优化
- 后者的可读性比前者要高
10. LIKE 和 REGEXP 操作有什么区别?
LIKE 和 REGEXP 运算符用于表示 ^ 和%。
SELECT * FROM
11. BLOB 和 TEXT 有什么区别?
BLOB 是一个二进制对象,可以容纳可变数量的数据。有四种类型的 BLOB
- TINYBLOB
- BLOB
- MEDIUMBLOB
- LONGBLOB 它们只能在所能容纳价值的最大长度上有所不同。
TEXT 是一个不区分大小写的 BLOB。四种 TEXT 类型
- TINYTEXT
- TEXT
- MEDIUMTEXT
- LONGTEXT 它们对应于四种 BLOB 类型,并具有相同的最大长度和存储要求。
BLOB 和 TEXT 类型之间的唯一区别在于对 BLOB 值进行排序和比较时区分大小写,对 TEXT 值不区分大小写。
12. 数据库的三范式?
第一范式:数据库表的每一个字段都是不可分割的。 第二范式:数据库表中的非主属性只依赖于主键。 第三范式:不存在非主属性对关键字的传递函数依赖关系。
13. MySQL 数据优化。
- 优化数据类型
- 避免使用 NULL,NULL 需要特殊处理, 大多数时候应该使用 NOT NULL,或者使用一个特殊的值,如 0,-1 作为默认值。
- 小心字符集转换
- 客户端或应用程序使用的字符集可能和表本身的字符集不一样,这需要MySQL 在运行过程中隐含地进行转换,此外,要确定字符集如 UTF-8 是否支持多字节字符,因此它们需要更多的存储空间。
- _优化 count(mycol) 和 count(*)
- 优化子查询
- 遇到子查询时,MySQL 查询优化引擎并不是总是最有效的,这就是为什么经常将子查询转换为连接查询的原因了,优化器已经能够正确处理连接查询了,当然要注意的一点是,确保连接表 (第二个表) 的连接列是有索引的,在第一个表上 MySQL 通常会相对于第二个表的查询子集进行一次全表扫描,这是嵌套循环算法的一部分。
- 优化 UNION
- 在跨多个不同的数据库时使用 UNION 是一个有趣的优化方法,UNION 从两个互不关联的表中返回数据,这就意味着不会出现重复的行,同时也必须对数据进行排序,我们知道排序是非常耗费资源的,特别是对大表的排序。
- UNION ALL 可以大大加快速度,如果你已经知道你的数据不会包括重复行,或者你不在乎是否会出现重复的行,在这两种情况下使用UNION ALL 更适合。此外,还可以在应用程序逻辑中采用某些方法避免出现重复的行,这样 UNION ALL 和 UNION 返回的结果都是一样的,但 UNION ALL 不会进行排序。
14. 一张表,里面有 ID 自增主键,当 insert 了 17 条记录之后,删除了第 15,16,17 条记录,再把 Mysql 重启,再 insert 一条记录,这条记录的 ID 是 18 还是 15 ?
- 如果表的类型是 MyISAM,那么是 18因为 MyISAM 表会把自增主键的最大 ID 记录到数据文件里,重启 MySQL 自增主键的最大ID 也不会丢失
- 如果表的类型是 InnoDB,那么是 15 InnoDB 表只是把自增主键的最大 ID 记录到内存中,所以重启数据库或者是对表进行OPTIMIZE 操作,都会导致最大 ID 丢失
15. 如何区分 FLOAT 和 DOUBLE?
浮点数以 8 位精度存储在 FLOAT 中,并且有四个字节。 浮点数存储在 DOUBLE 中,精度为 18 位,有八个字节。
16. 区分 CHAR_LENGTH 和 LENGTH?
CHAR_LENGTH 是字符数,而 LENGTH 是字节数。Latin 字符的这两个数据是相同的,但是对于 Unicode 和其他编码,它们是不同的。
17. Mysql 如何优化 DISTINCT?
DISTINCT 在所有列上转换为 GROUP BY,并与 ORDER BY 子句结合使用。
18. 可以使用多少列创建索引?
任何标准表最多可以创建 16 个索引列。
19. HAVNG 子句 和 WHERE 的异同点?
- 语法上:where 用表中列名,having 用 select 结果别名
- 影响结果范围:where 从表读出数据的行数,having 返回客户端的行数
- 索引:where 可以使用索引,having 不能使用索引,只能在临时结果集操作
- where 后面不能使用聚集函数,having 是专门使用聚集函数的。
20. MySQL 当记录不存在时 insert,当记录存在时 update
INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;