MySQL 面试题

1、隔离级别与锁的关系

回答这个问题,可以先阐述四种隔离级别,再阐述它们的实现原理。隔离级别就是依赖锁和 MVCC 实现的。

2、实践中如何优化 MySQL?

最好是按照以下顺序优化:

  1. SQL 语句及索引的优化
  2. 数据库表结构的优化
  3. 系统配置的优化
  4. 硬件的优化

3 优化子查询

  1. 用关联查询替代。
  2. 优化 GROUP BY 和 DISTINCT。
  3. 这两种查询据可以使用索引来优化,是最有效的优化方法。
  4. 关联查询中,使用标识列分组的效率更高。
  5. 如果不需要 ORDER BY,进行 GROUP BY 时加 ORDER BY NULL MySQL 不会再进行文件排序。
  6. WITH ROLLUP 超级聚合,可以挪到应用程序处理。

4.前缀索引

  1. 语法:index(field(10)),使用字段值的前 10 个字符建立索引,默认是使用字段的全部内容建立索引。
  2. 前提:前缀的标识度高。比如密码就适合建立前缀索引,因为密码几乎各不相同。
  3. 实操的难度:在于前缀截取的长度。
  4. 我们可以利用 select count(*)/count(distinct left(password,prefixLen));,通过从调整 prefixLen 的值(从 1 增)查看不同前缀长度的一个平均匹配度,接近 1 时就可以了(表示一个密码的前 prefixLen 个字符几乎能确定唯一一条记录)

5、MySQL 5.6 和 MySQL 5.7 对索引做了哪些优化?

  1. MySQL5.6 引入了索引下推优化,默认是开启的。
  2. 例子:user 表中(a,b,c)构成一个索引。
  3. select * from user where a='23' and b like '%eqw%' and c like 'dasd'。
  4. 解释:如果没有索引下推原则,则 MySQL 会通过 a='23' 先查询出一个对应的数据。然后返回到 MySQL 服务端。MySQL 服务端再基于两个 like 模糊查询来校验and 查询出的数据是否符合条件。这个过程就设计到回表操作。
  5. 如果使用了索引下推技术,则 MySQL 会首先返回返回条件 a='23'的数据的索引,然后根据模糊查询的条件来校验索引行数据是否符合条件,如果符合条件,则直接根据索引来定位对应的数据,如果不符合直接 reject 掉。因此,有了索引下推优化,可以在有 like 条件的情况下,减少回表的次数。

6、MySQL 有关权限的表有哪几个呢?

MySQL 服务器通过权限表来控制用户对数据库的访问,权限表存放在 MySQL 数据库里,由 MySQL_install_db 脚本初始化。这些权限表分别 user,db,table_priv,columns_priv 和 host。

  1. user 权限表:记录允许连接到服务器的用户帐号信息,里面的权限是全局级的。
  2. db 权限表:记录各个帐号在各个数据库上的操作权限。
  3. table_priv 权限表:记录数据表级的操作权限。
  4. columns_priv 权限表:记录数据列级的操作权限。
  5. host 权限表:配合 db 权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受 GRANT 和 REVOKE 语句的影响。