2024年我又吊打面试官了，凭借MySQL海量数据优化（理论+实战），java面试线程问题

总结：心得体会

既然选择这个行业，选择了做一个程序员，也就明白只有不断学习，积累实战经验才有资格往上走，拿高薪，为自己，为父母，为以后的家能有一定的经济保障。

学习时间都是自己挤出来的，短时间或许很难看到效果，一旦坚持下来了，必然会有所改变。不如好好想想自己为什么想进入这个行业，给自己内心一个答案。

面试大厂，最重要的就是夯实的基础，不然面试官随便一问你就凉了；其次会问一些技术原理，还会看你对知识掌握的广度，最重要的还是你的思路，这是面试官比较看重的。

最后，上面这些大厂面试真题都是非常好的学习资料，通过这些面试真题能够看看自己对技术知识掌握的大概情况，从而能够给自己定一个学习方向。包括上面分享到的学习指南，你都可以从学习指南里理顺学习路线，避免低效学习。

大厂Java架构核心笔记（适合中高级程序员阅读）：

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录

需要这份系统化的资料的朋友，可以点击这里获取

CREATE TABLE t_user (

id int NOT NULL,

user_name varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,

address varchar(255) DEFAULT NULL,

create_time datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DELIMITER ;;

CREATE PROCEDURE user_insert()

BEGIN

DECLARE i INT DEFAULT 0;

WHILE i<1000000

DO

INSERT INTO t_user(id, user_name, address, create_time) VALUES (i, CONCAT(‘mayun’,i), ‘浙江杭州’, now());

SET i=i+1;

END WHILE ;

commit;

END;;

CALL user_insert();

插入完后咱们看看数据条数

二、优化方式

======

1.分页查询优化

OK，咱们看下分页limit到一定值时的耗时是多少

• limit 1000时

• limit 10000时

• limit 100000时

• limit 1000000时

可以看到limit值越大，耗时越长，这还只是一百万数据，要是千万级、亿级呢？

OK不废话，咱们马上进行分页优化

• 子查询优化

可以看到比起之前 limit 1000000时的0.218s 效率提高了很多

• 使用JOIN分页

可以看到比起之前 limit 1000000时的0.218s 效率也同样提高了很多

• 使用前一次查询的最大ID

可以看到这种方法效率最高，但依赖于需要知道最大ID，这种适合点击下一页查询（类似于滚动加载数据）的场景

• 通过伪列对ID进行分页

然后可以开启多个线程去进行最高效率查询语句的批量查询操作 0~10000，10001-20000… 这样子的话可以快速把全量数据查询出来同步至缓存中。

分页优化总结： 使用前一次查询的最大ID进行查询优化是效率最高的方法，但这种方法只适用于下一页点击的这种操作，对于同步全量数据来说建议的方式使用伪列对ID进行分页，然后开启多个线程同时查询，把全量数据加载到缓存，以后面试官问你如何 快速获取海量数据并加载到缓存 你该知道怎么回答了吧。

2.普通索引优化

先来看没索引优化的情况下的查询效率

可以看到这时没用索引的情况，用了0.305S接下来看看加了索引后的结果

• 普通索引优化

只需要0.024S，我们可以EXPLAIN看下

可以看到使用了普通索引后查询效率明显增加

3.复合索引优化

复合索引什么时候用？为什么要用？ 围绕着这两问题，咱们先来说说复合索引什么时候用

1. 单表中查询、条件语句中具有较多个字段

2. 使用索引会影响写的效率，需要研究建立最优秀的索引

我们这里建议一个复合索引

MySQL建立复合索引时实际建立了(user_name)、（user_name,address）、(user_name,address,create_time)三个索引,我们都知道每多一个索引，都会增加写操作的开销和磁盘空间的开销，对于海量数据的表，这可是不小的开销，所以你会发现我们在这里使用复合索引一个顶三个，又能减少写操作的开销和磁盘空间的开销

当我们select user_name,address,create_time from t_user where user_name=xx and address = xxx时，MySQL可以直接通过遍历索引取得数据，无需回表，这减少了很多的随机IO操作。所以，在真正的实际应用中，这就是覆盖索引，是复合索引中主要的提升性能的优化手段之一。

4.SQL查询优化

1.避免使用OR，看看例子

可以看到这条语句没有使用到索引，是因为当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效。

2.不要使用like ‘%xx’ %在左边时索引失效

3.使用复合索引时没有遵循最左匹配原则

ref:这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。没有值说明没有利用最左前缀原则

再来看个使用了最左前缀的例子

4. 不要让数据类型出现隐式转化

可以看以下两个例子

5. 不要在索引字段上使用not，<>，!=，一样会导致索引失效

6. 分解关联查询 例如这条语句

可以分解成

7.小表驱动大表 即小的数据集驱动大的数据集。如：以t_user，t_order两表为例，两表通过 t_user的id字段进行关联。

当 t_order表的数据集小于t_user表时,用 in 优化 exist,使用 in,两表执行顺序是先查 t_order 表,再查t_user表

select * from t_user where id in (select user_id from t_order)

当 t_user 表的数据集小于 t_order 表时，用 exist 优化 in,使用 exists,两表执行顺序是先查 t_user 表,再查 t_order 表

select * from t_user where exists (select 1 from B where t_order.user_id= t_user.id)

5.事务优化

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

1. Serializable：可避免脏读、不可重复读、虚读情况的发生。（串行化）

2. Repeatable read：可避免脏读、不可重复读情况的发生。（可重复读）

3. Read committed：可避免脏读情况发生（读已提交）。

4. Read uncommitted：最低级别，以上情况均无法保证。(读未提交)

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

6.数据库性能优化

开启查询缓存

• 在解析一个查询语句前，如果查询缓存是打开的，那么MySQL会检查这个查询语句是否命中查询缓存中的数据。如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。这种情况下，查询不会被解析，也不会生成执行计划，更不会执行。 MySQL将缓存存放在一个引用表（不要理解成table，可以认为是类似于HashMap的数据结构），通过一个哈希值索引，这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同（例如：空格、注释），都会导致缓存不会命中。

• 如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表，其查询结果都不会被缓存。比如函数NOW()或者CURRENT_DATE()会因为不同的查询时间，返回不同的查询结果，再比如包含CURRENT_USER或者CONNECION_ID()的查询语句会因为不同的用户而返回不同的结果，将这样的查询结果缓存起来没有任何的意义。

• 既然是缓存，就会失效，那查询缓存何时失效呢？MySQL的查询缓存系统会跟踪查询中涉及的每个表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。正因为如此，在任何的写操作时，MySQL必须将对应表的所有缓存都设置为失效。如果查询缓存非常大或者碎片很多，这个操作就可能带来很大的系统消耗，甚至导致系统僵死一会儿。而且查询缓存对系统的额外消耗也不仅仅在写操作，读操作也不例外：

任何的查询语句在开始之前都必须经过检查，即使这条SQL语句永远不会命中缓存

如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗 复制代码

• 基于此，我们要知道并不是什么情况下查询缓存都会提高系统性能，缓存和失效都会带来额外消耗，只有当缓存带来的资源节约大于其本身消耗的资源时，才会给系统带来性能提升。但要如何评估打开缓存是否能够带来性能提升是一件非常困难的事情，也不在本文讨论的范畴内。如果系统确实存在一些性能问题，可以尝试打开查询缓存，并在数据库设计上做一些优化，比如：

. 批量插入代替循环单条插入 　 . 合理控制缓存空间大小，一般来说其大小设置为几十兆比较合适 　 . 可以通过SQL_CACHE和SQL_NO_CACHE来控制某个查询语句是否需要进行缓存 　　最后的忠告是不要轻易打开查询缓存，特别是写密集型应用。如果你实在是忍不住，可以将query_cache_type设置为DEMAND，这时只有加入SQL_CACHE的查询才会走缓存，其他查询则不会，这样可以非常自由地控制哪些查询需要被缓存。 　　当然查询缓存系统本身是非常复杂的，这里讨论的也只是很小的一部分，其他更深入的话题，比如：缓存是如何使用内存的？如何控制内存的碎片化？事务对查询缓存有何影响等等，读者可以自行阅读相关资料，这里权当抛砖引玉吧。

语法解析和预处理

• MySQL通过关键字将SQL语句进行解析，并生成一颗对应的解析树。这个过程解析器主要通过语法规则来验证和解析。比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。预处理则会根据MySQL规则进一步检查解析树是否合法。比如检查要查询的数据表和数据列是否存在等等。

7.系统内核参数优化

#基础配置

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

#支持大小写

最后

总而言之，面试官问来问去，问的那些Redis知识点也就这么多吧，复习的不够到位，知识点掌握不够熟练，所以面试才会卡壳。将这些Redis面试知识解析以及我整理的一些学习笔记分享出来给大家参考学习

还有更多学习笔记面试资料也分享如下：

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录

需要这份系统化的资料的朋友，可以点击这里获取

以存储在my.cnf中指定datadir之外的分区或目录，为0不开启

#symbolic-links=0

#支持大小写

最后

总而言之，面试官问来问去，问的那些Redis知识点也就这么多吧，复习的不够到位，知识点掌握不够熟练，所以面试才会卡壳。将这些Redis面试知识解析以及我整理的一些学习笔记分享出来给大家参考学习

还有更多学习笔记面试资料也分享如下：

[外链图片转存中…(img-kAHMr6Sw-1715064223863)]

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录

需要这份系统化的资料的朋友，可以点击这里获取