面试官：Mysql优化你有哪些方面的经验?

硬件和操作系统层面的优化

从硬件层面来说，影响 Mysql 性能的因素有，CPU、可用内存大小、磁盘读写速度、网络带宽从操作系层面来说，应用文件句柄数、操作系统网络的配置都会影响到 Mysql 性能。这部分的优化一般由 DBA 或者运维工程师去完成。在硬件基础资源的优化中，我们重点应该关注服务本身承载的体量，然后提出合理的指标要求，避免出现资源浪费

架构设计层面的优化

MySQL 是一个磁盘 IO 访问量非常频繁的关系型数据库
在高并发和高性能的场景中.MySQL 数据库必然会承受巨大的并发压力，而此时，我们
的优化方式可以分为几个部分。

搭建 Mysql 主从集群，单个 Mysql 服务容易单点故障，一旦服务器宕机，将会导
致依赖 Mysql 数据库的应用全部无法响应。主从集群或者主主集群可以保证服务
的高可用性。
读写分离设计，在读多写少的场景中，通过读写分离的方案，可以避免读写冲突导
致的性能影响
引入分库分表机制，通过分库可以降低单个服务器节点的 IO 压力，通过分表的方
式可以降低单表数据量，从而提升 sql 查询的效率。
针对热点数据，可以引入更为高效的分布式数据库，比如 Redis、MongoDB 等，
他们可以很好的缓解 Mysql 的访问压力，同时还能提升数据检索性能

Mysql程序配置优化

MySQL 是一个经过互联网大厂验证过的生产级别的成熟数据库，对于 Mysql 数据库本身的优化，一般是通过 Mysql 中的配置文件 my.cnf 来完成的，比如。 Mysql5.7 版本默认的最大连接数是 151 个，这个值可以在 my.cnf 中修改。 binlog 日志，默认是不开启缓存池 bufferpoll 的默认大小配置等。由于这些配置一般都和用户安装的硬件环境以及使用场景有关系，因此这些配置官方只会提供一个默认值，具体情况还得由使用者来修改。关于配置项的修改，需要关注两个方面。

配置的作用域，分为会话级别和全局

是否支持热加载因此，针对这两个点，我们需要注意的是：全局参数的设定对于已经存在的会话无法生效

会话参数的设定随着会话的销毁而失效

全局类的统一配置建议配置在默认配置文件中，否则重启服务会导致配置失效

SQL优化

SQL 优化又能分为三步曲

第一、慢 SQL 的定位和排查我们可以通过慢查询日志和慢查询日志分析工具得到有问题的 SQL 列表。

第二、执行计划分析针对慢 SQL，我们可以使用关键字 explain 来查看当前 sql 的执行计划.可以重点关注 type key rows filterd 等字段，从而定位该 SQL 执行慢的根本原因。再有的放矢的进行优化

第三、使用 show profile 工具 Show Profile 是 MySQL 提供的可以用来分析当前会话中，SQL 语句资源消耗情况的工具，可用于 SQL 调优的测量。在当前会话中.默认情况下处于 show profile 是关闭状态，打开之后保存最近 15 次的运行结

针对运行慢的 SQL，通过 profile 工具进行详细分析.可以得到 SQL 执行过程中所有的资源开销情况. 如 IO 开销,CPU 开销,内存开销等.

索引有哪些缺点以及具体有哪些索引类型

索引的优缺点

合理的增加索引，可以提高数据查询的效率，减少查询时间
有一些特殊的索引，可以保证数据的完整性，比如唯一索引

缺点：

创建索引和维护索引需要消耗时间
索引需要额外占用物理空间
对创建了索引的表进行数据的增加、修改、删除时，会同步动态维护索引，会造成性能的影响

索引的类型

主键索引：数据列不允许重复，不允许为NULL，一个表只能由一个主键。
唯一索引：数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引
普通索引：基本的索引类型，没有唯一性的限制，允许为NULL值
全文索引
覆盖索引
组合索引

联合索引的最左匹配原则，在遇到范围查询(如>、<)的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。注意，对于>=、<=、Between、like前缀匹配的范围查询，并不会停止匹配。

防止索引失效

用上了索引并不意味着查询的时候会使用到索引，所以我们心里要清楚有哪些情况会导致索引失效，从而避免写出索引失效的查询语句，否则这样的查询效率是很低的。

我之前写过索引失效的文章，想详细了解的可以去看这篇文章：谁还没碰过索引失效呢?(opens new window)

这里简单说一下，发生索引失效的情况：

当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
当我们在查询条件中对索引列做了计算、函数、类型转换操作，这些情况下都会造成索引失效；
联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

Mysql的四种隔离级别

读未提交在这种隔离级别下，可能会产生脏读、不可重复读、幻读。
读已提交（RC）这种隔离级别下，可能会产生不可重复读和幻读
可重复读（RR）这种隔离级别下，可能会产生幻读
串行化这种隔离级别下，多个并行事务串行化执行，不会产生安全性问题。

这四种隔离级别里面，只有串行化解决了全部的问题，但也意味着这种隔离级别的性能是最低的。

在 Mysql 里面，InnoDB 引擎默认的隔离级别是 RR（可重复读），因为它需要保证事务 ACID 特性中的隔离性特征

这里出一个题目，针对针对下面这条 SQL，你怎么通过索引来提高查询效率呢？

select * from order where status = 1 order by create_time asc

有的同学会认为，单独给 status 建立一个索引就可以了。

但是更好的方式给 status 和 create_time 列建立一个联合索引，因为这样可以避免 MySQL 数据库发生文件排序。

因为在查询时，如果只用到 status 的索引，但是这条语句还要对 create_time 排序，这时就要用文件排序 filesort，也就是在 SQL 执行计划中，Extra 列会出现 Using filesort。

所以，要利用索引的有序性，在 status 和 create_time 列建立联合索引，这样根据 status 筛选后的数据就是按照 create_time 排好序的，避免在文件排序，提高了查询效率

InnoDB 引擎通过什么技术来保证事务的这四个特性的呢？

持久性是通过 redo log （重做日志）来保证的；
原子性是通过 undo log（回滚日志）来保证的；
隔离性是通过 MVCC（多版本并发控制）或锁机制来保证的；
一致性则是通过持久性+原子性+隔离性来保证；

varchar和char的区别是什么？

char是定长的，varchar是变长的。变长字段实际存储的数据的长度(大小)不固定的。

varchar(n) 中 n 最大取值为多少？

我们要清楚一点，MySQL 规定除了 TEXT、BLOBs 这种大对象类型之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过 65535 个字节。

也就是说，一行记录除了 TEXT、BLOBs 类型的列，限制最大为 65535 字节，注意是一行的总长度，不是一列。

知道了这个前提之后，我们再来看看这个问题：「varchar(n) 中 n 最大取值为多少？」

varchar(n) 字段类型的 n 代表的是最多存储的字符数量，并不是字节大小哦。

要算 varchar(n) 最大能允许存储的字节数，还要看数据库表的字符集，因为字符集代表着，1个字符要占用多少字节，比如 ascii 字符集， 1 个字符占用 1 字节，那么 varchar(100) 意味着最大能允许存储 100 字节的数据

数据库id的适用场景、

存储引擎

在MySQL8以前的版本，默认的存储引擎是myisam，而mysql8以后的默认版本，默认的存储引擎是innoDB。

myisam的索引和数据分开存储，而有利于查询，但它不支持事务和外键等功能。

而innodb虽说查询性能，稍微弱一点，但它支持事务和外键等功能，功能更强大

NOT NULL

在创建字段时，需要选择该字段是否允许为NULL。

我们在定义字段时，应该尽可能明确该字段NOT NULL。

为什么呢？

我们主要以innodb存储引擎为例，myisam存储引擎没啥好说的。

主要有以下原因：

在innodb中，需要额外的空间存储null值，需要占用更多的空间。
null值可能会导致索引失效。
null值只能用is null或者is not null判断，用=号判断永远返回false。

因此，建议我们在定义字段时，能定义成NOT NULL，就定义成NOT NULL。

但如果某个字段直接定义成NOT NULL，万一有些地方忘了给该字段写值，就会insert不了数据。

这也算合理的情况。

但有一种情况是，系统有新功能上线，新增了字段。上线时一般会先执行sql脚本，再部署代码。

由于老代码中，不会给新字段赋值，则insert数据时，也会报错。

由此，非常有必要给NOT NULL的字段设置默认值，特别是后面新增的字段。

alter table product_sku add column brand_id int(10) NOT NULL default 0;

create table product_sku(id int(10) primary key auto_increment,spu_id int(10) not null,brand_id int(10) not null,name varchar(15) not null,
)

create table product_sku(id int(10) primary key auto_increment,spu_id int(10) not null,brand_id int(10) not null,name varchar(15) not null,key `ix_spu_id` (`spu_id`) using BTREE,key `ix_brand_id` (`brand_id`) using BTREE
);

时间字段

时间字段的类型，我们可以选择的范围还是比较多的，目前mysql支持：date、datetime、timestamp、varchar等。

varchar类型可能是为了跟接口保持一致，接口中的时间类型是String。

但如果哪天我们要通过时间范围查询数据，效率会非常低，因为这种情况没法走索引。

date类型主要是为了保存日期，比如：2020-08-20，不适合保存日期和时间，比如：2020-08-20 12:12:20。

而datetime和timestamp类型更适合我们保存日期和时间。

但它们有略微区别。

timestamp：用4个字节来保存数据，它的取值范围为1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07。此外，它还跟时区有关。
datetime：用8个字节来保存数据，它的取值范围为1000-01-01 00:00:00 ~ 9999-12-31 23:59:59。它跟时区无关。

优先推荐使用datetime类型保存日期和时间，可以保存的时间范围更大一些。

CREATE TABLE `sys_dept` (`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',`name` varchar(30) NOT NULL COMMENT '名称',`pid` bigint NOT NULL COMMENT '上级部门',`valid_status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '有效状态 1:有效 0:无效',`create_user_id` bigint NOT NULL COMMENT '创建人ID',`create_user_name` varchar(30) NOT NULL COMMENT '创建人名称',`create_time` datetime(3) DEFAULT NULL COMMENT '创建日期',`update_user_id` bigint DEFAULT NULL COMMENT '修改人ID',`update_user_name` varchar(30)  DEFAULT NULL COMMENT '修改人名称',`update_time` datetime(3) DEFAULT NULL COMMENT '修改时间',`is_del` tinyint(1) DEFAULT '0' COMMENT '是否删除 1：已删除 0：未删除',PRIMARY KEY (`id`) USING BTREE,KEY `index_pid` (`pid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='部门';

mysql中in 和exists的区别。

假设表A表示某企业的员工表，表B表示部门表，查询所有部门的所有员工，很容易有以下SQL:

select * from A where deptId in (select deptId from B)

这样写等价于：

先查询部门表B

select deptId from B

再由部门deptId，查询A的员工

select * from A where A.deptId = B.deptId

可以抽象成这样的一个循环：

List<> resultSet ;
for(int i=0;i<B.length;i++) {for(int j=0;j<A.length;j++) {if(A[i].id==B[j].id) {resultSet.add(A[i]);break;}}
}

显然，除了使用in，我们也可以用exists实现一样的查询功能，如下：

select * from A where exists(select 1 from B where A.deptId = B.deptId);

因为exists查询的理解就是，先执行主查询，获得数据后，再放到子查询中做条件验证，根据验证结果（true或者false），来决定主查询的数据结果是否得意保留。

那么，这样写就等价于：

select * from A,先从A表做循环

select * from B where A.deptId = B.deptId,再从B表做循环.

同理，可以抽象成这样一个循环：

List<> resultSet ;for(int i=0;i<A.length;i++) {for(int j=0;j<B.length;j++) {if(A[i].deptId==B[j].deptId) {resultSet.add(A[i]);break;}}
}

数据库最费劲的就是跟程序链接释放。假设链接了两次，每次做上百万次的数据集查询，查完就走，这样就只做了两次；相反建立了上百万次链接，申请链接释放反复重复，这样系统就受不了了。即mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优。

因此，我们要选择最外层循环小的，也就是，如果B的数据量小于A，适合使用in，如果B的数据量大于A，即适合选择exists，这就是in和exists的区别。

数据库存储日期格式时，如何考虑时区转换问题？

datetime类型适合用来记录数据的原始的创建时间，修改记录中其他字段的值，datetime字段的值不会改变，除非手动修改它。
timestamp类型适合用来记录数据的最后修改时间，只要修改了记录中其他字段的值，timestamp字段的值都会被自动更新。

数据库自增主键可能遇到什么问题。

使用自增主键对数据库做分库分表，可能出现诸如主键重复等的问题。解决方案的话，简单点的话可以考虑使用UUID哈
自增主键会产生表锁，从而引发问题
自增主键可能用完问题。

值传递和引用传递

值传递是指在调用函数时将实际参数复制一份传递到函数中，这样在函数中如果对参数进行修改，将不会影响到实际参数。
引用传递是指在调用函数时将实际参数的地址直接传递到函数中，那么在函数中对参数所进行的修改，将影响到实际参数。

三大范式

第一范式（1 NF）：字段不可再拆分。
第二范式（2 NF）：表中任意一个主键或任意一组联合主键，可以确定除该主键外的所有的非主键值。
第三范式（3 NF）：在任一主键都可以确定所有非主键字段值的情况下，不能存在某非主键字段 A 可以获取某非主键字段 B。