【云驻共创】常见的MySQL面试题有哪些?

举报
架构师聊技术 发表于 2022/10/30 10:02:00 2022/10/30
【摘要】 本文主要介绍我在公司招聘的时候,主要问应聘者的一些问题,希望对换工作的同行及在使用过程中有些疑问的同行有所帮助

本文主要介绍我在公司招聘的时候,主要问应聘者的一些问题,希望对换工作的同行及在使用过程中有些疑问的同行有所帮助

1. MySQL海量数据优化

优化问题主要从SQL优化、事务级别调整、MySql配置参数调整做起

1.1 SQL优化

  • 避免使用OR
  • 不要使用like '%xx' %在左边时索引失效
  • 使用复合索引时没有遵循最左匹配原则
  • 不要让数据类型出现隐式转化
  • 不要在索引字段上使用not,<>,!=,一样会导致索引失效
  • 分解关联查询
  • 小表驱动大表 即小的数据集驱动大的数据集

1.2 事务级别调整

首先了解下事务的隔离级别,数据库共定义了四种隔离级别:

  • Serializable:可避免脏读、不可重复读、虚读情况的发生。(串行化)
  • Repeatable read:可避免脏读、不可重复读情况的发生。(可重复读)
  • Read committed:可避免脏读情况发生(读已提交)。
  • Read uncommitted:最低级别,以上情况均无法保证。(读未提交)

可以通过 set transaction isolation level 设置事务隔离级别来提高性能

1.3 MySql配置参数

#基础配置

datadir=/data/datafile

socket=/var/lib/mysql/mysql.sock

log-error=/data/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

character_set_server=utf8#允许任意IP访问

bind-address = 0.0.0.0#是否支持符号链接,即数据库或表可以存储在my.cnf中指定datadir之外的分区或目录,为0不开启#symbolic-links=0#支持大小写

lower_case_table_names=1#二进制配置

server-id = 1

log-bin = /data/log/mysql-bin.log

log-bin-index =/data/log/binlog.index

log_bin_trust_function_creators=1

expire_logs_days=7

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION#InnoDB存储数据字典、内部数据结构的缓冲池,16MB已经足够大了。

innodb_additional_mem_pool_size = 16M#InnoDB用于缓存数据、索引、锁、插入缓冲、数据字典等#如果是专用的DB服务器,且以InnoDB引擎为主的场景,通常可设置物理内存的60%#如果是非专用DB服务器,可以先尝试设置成内存的1/4

innodb_buffer_pool_size = 4G#InnoDB的log buffer,通常设置为 64MB 就足够了

innodb_log_buffer_size = 64M#InnoDB redo log大小,通常设置256MB 就足够了

innodb_log_file_size = 256M#InnoDB redo log文件组,通常设置为 2 就足够了

innodb_log_files_in_group = 2

innodb_file_per_table = 1#InnoDB共享表空间初始化大小,默认是 10MB,改成 1GB,并且自动扩展

innodb_data_file_path = ibdata1:1G:autoextend#设置临时表空间最大4G

innodb_temp_data_file_path=ibtmp1:500M:autoextend:max:4096M#启用InnoDB的status file,便于管理员查看以及监控

innodb_status_file = 1#当设置为0,该模式速度最快,但不太安全,mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。#当设置为1,该模式是最安全的,但也是最慢的一种方式。在mysqld 服务崩溃或者服务器主机crash的情况下,binary log 只有可能丢失最多一个语句或者一个事务。#当设置为2,该模式速度较快,也比0安全,只有在操作系统崩溃或者系统断电的情况下,上一秒钟所有事务数据才可能丢失。

innodb_flush_log_at_trx_commit = 1

max_connections=600

max_connect_errors=1000

max_user_connections=400#设置临时表最大值,这是每次连接都会分配,不宜设置过大 max_heap_table_size 和 tmp_table_size 要设置一样大

max_heap_table_size = 100M

tmp_table_size = 100M#每个连接都会分配的一些排序、连接等缓冲,一般设置为 2MB 就足够了

sort_buffer_size = 2M

join_buffer_size = 2M

read_buffer_size = 2M

read_rnd_buffer_size = 2M#建议关闭query cache,有些时候对性能反而是一种损害

query_cache_size = 0

key_buffer_size = 8M

long_query_time = 120

slow_query_log=1 #开启mysql慢sql的日志

log_output=table,File #日志输出会写表,也会写日志文件,为了便于程序去统计,所以最好写表

slow_query_log_file=/data/log/slow.log

innodb_buffer_pool_dump_at_shutdown=1

innodb_buffer_pool_load_at_startup=1#打印deadlock日志

innodb_print_all_deadlocks=1

2. MySql数据表分区如何设计

数据表分区主要是解决监控数据过大造成的单表数据量过大,在存储及查询的过程中对性能造成影响,通过数据表分区,减小数据文件的大小,提高磁盘读写性能。

分区设计

  • 时间阀值需求,指标明细保留90天,趋势数据保留3年
  • 监控的指标数据需要能查询90天内的明细,所以原始明细数据保留90天,指标多及监控间隔时间短,必然产生大量的数据
  • 每天一个分区存储原始数据
  • 统计趋势数据,及删除超过90天的明细数据
  • 数据表分区规划示意图

 

3. 如何启用Mysql查询缓存

数据库优化中,除了Sql本身之外,数据库本身的优化也是很重要的一个环节,缓存就是其中一项。

查看查询缓存情况:

mysql> show variables like '%query_cache%'; (query_cache_type 为 ON 表示已经开启)
+------------------------------+----------+| Variable_name                | Value    |
+------------------------------+----------+
| have_query_cache             | YES      || query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     || query_cache_size             | 20971520 |
| query_cache_type             | ON       || query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+

如果不是ON,修改/etc/my.cnf配置文件以开启查询缓存:

[mysqld]中添加:

query_cache_size = 20M
query_cache_type = ON

重启mysql服务使配置查询缓存配置生效

> service mysql restart

4. MySQL配置文件字符集可以通过指令改吗?

可以的,MySql8.x支持动态修改配置参数

1、可以的,通过 set global 命令直接修改,不用重启mysql
2、要是8.x 可以用 set persist 新特性

示例:

mysql> set persist max_connections=default;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from performance_schema.persisted_variables;
+-----------------+----------------+
| VARIABLE_NAME   | VARIABLE_VALUE |
+-----------------+----------------+
| max_connections | 151            |
+-----------------+----------------+
1 row in set (0.00 sec)
mysql> show variables like '%max_connections%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 151   |
| mysqlx_max_connections | 100   |
+------------------------+-------+
2 rows in set (0.01 sec)

5. join关联有几种类型

Join常用的有3种关联类型。

inner join 内连接,只保留两张表中完全匹配的结果集;

left join 会返回左表所有的行,即使在右表中没有匹配的记录;

right join 会返回右表所有的行,即使在左表中没有匹配的记录;

三种连接如果结果相同,优先使用 inner join,如果使用 left join 左边表尽量小。

6. char varchar2 的区别

  • char的长度是固定的,而varchar2的长度是可以变化的

   比如,存储字符串“101”,对于char(10),表示你存储的字符将占 10 个字节(包括 7 个空字符),在数据库中它是以空格占位的,而同样的varchar2(10)则只占用 3 个字节的长度,10 只是最大值,当你存储的字符小于 10 时,按实际长度存储。

  • char的效率比varchar2的效率稍高
  • 何时用char,何时用varchar2?

   char和varchar2是一对矛盾的统一体,两者是互补的关系,varchar2比char节省空间,在效率上比char会稍微差一点,既想获取效率,就必须牺牲一点空间,这就是我们在数据库设计上常说的 “以空间换效率”。

主要分享低代码、微服务、容器化、SAAS‬、系统架构方面的的‬内容‬‬,希望‬大家‬点赞‬,评论,关注‬。

本文参与华为云社区【内容共创】活动第21期。
https://bbs.huaweicloud.cn/blogs/380503

任务35:常见的MySQL面试题有哪些?

【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。