小惠珠哦

这个屌丝很懒，什么也没留下！

热门标签

mysql优化

作者：小惠珠哦 | 2024-08-08 06:16:28

踩

mysql优化

联合索引的最左前缀匹配原则

b+树是按照从左到右的顺序建立搜索树的，检索数据的时候，b+树会优先比较最左侧字段来确定下一步的比较方向，如果不适用最左前缀，就会导致b+树不知道该查哪个节点，从哪里开始寻找，导致没有用到索引。

1、概述

1.1、为什么要优化

一个应用吞吐量瓶颈往往体现在数据库的数据处理速度
随着应用的用户增多和使用过程中数据积累，数据库数据逐渐增多，数据库处理压力变大
关系型数据库是存储在磁盘中，查询时需要经过多次io，导致读写速度较慢

1.2、如何让优化

从表和字段的设计方面，考虑使用更优的存储和计算，例如使用可变长度或者定长字段中较短的，或者使用notnull字段
数据库自身提供的优化功能，例如索引
横向扩展，主从复制，读写分离，负载均衡和高可用
典型的sql语句优化，收效甚微
使用join代替子查询
使用union代替手动创建的临时表
事务处理

2、字段设计

2.1、典型方案

2.1.1、对精度有要求

2.1.2、尽量使用整形表示字符串

2.1.3、尽可能使用not null

2.1.4、定长和非定长的选择

2.1.5、其他

字段数不要过多
字段注释是必要的
字段明明见名思意
预留字段作为备用，以备扩展

2.2、三大范式

第一范式：字段的原子性，关系型数据库固有的属性
第二范式：消除主键的部分依赖，主键可能不只有一个------使用一个和业务无关的字段作为主键
第三范式：消除对主键的传递依赖，高内聚，商品表分为商品简略信息表和商品详情表

3、存储引擎的选择

3.1、功能差异

Innodb支持行级锁，支持事务和外键

3.2、存储差异

存储方式，MyISAM的数据和索引是分开存储
表可移动性 Innodb不可移动
碎片空间 Innodb不产生
有序存储按照主键有序插入

3.3、选择依据

读多写少
读多写多
- 支持事务和外键，保证数据一致性和完整性
- 并发能力强，支持行级锁

4、索引

4.1、什么是索引

对数据进行排序，方便进行查询。

4.2、索引类型

普通索引，唯一索引，主键索引，全文索引

4.3、索引管理语法

查看索引 show create table 表名 、desc 表名
建立索引
删除索引

4.4、执行计划explain

4.5、索引使用场景

where
order by
join
索引覆盖

4.6、语法细节

4.7、索引存储结构

hash索引
b+树索引
- 最左匹配原则

5、查询缓存

6、分区

默认情况下，一张表对应一个存储文件，当存储文件数据量较大时，需要将存储文件岔开，分到多组存储文件，保证单个文件处理效率
partition by 分区函数（分区字段）（分区逻辑）
- hash-分区字段为整形
- key-分区字段为字符串
- range-基于比较，只支持less than
- list-基于状态值
分区管理
- 创建时分区
- 修改表结构
分区字段应该选择常用字段否则分区意义不大

7、水平分割和垂直分割

水平分割，多张表结构相同的表存储同一类型数据，单独一张表要保证id唯一性
垂直分割，分割字段到多张表，这些表记录是一一对应的

8、集群

8.1、主从复制

8.2、读写分离

使用原生的javax.sql.Connection
借助SpringAOP和Aspect实现数据源动态转换

8.3、负载均衡

轮询
加权轮询
根据负载情况

8.4、高可用

为单机服务提供一个冗余机

心跳检测
虚IP
主从复制

9、典型sql

10、慢查询日志

11、profile

12、典型服务器配置

13、压测工具

携程面试准备

作者：牛客461209969号
链接：https://www.nowcoder.com/discuss/622602?type=post&order=time&pos=&page=1&channel=-1&source_id=search_post_nctrack
来源：牛客网

1.项目中为什么会选择redis
2.如果数据库请求量很大，怎么处理(我说redis缓存，他说缓存也不能把所有数据缓存进去，我说缓存热点数据，他说如果我不查热点数据呢，然后引导让我用算法解决，我回答了lru并讲解了lru算法)

数据预热

场景：服务器启动后迅速宕机
大量请求过来，需要在缓存中获取数据，缓存中又没有，从而去数据库找，然后再将数据存入缓存，短时间内高强度操作redis导致出现问题
解决方案：系统启动前，提前将相关的缓存数据直接加载到缓存系统，避免用户请求的时候，先查询数据库，然后再将数据缓存的问题。
将过期时间散列
分布式锁
缓存穿透：黑客查询redis和mysql中不存在的数据，绕过了redis
- 布隆过滤器，多次哈希
- 缓存空数据，需要内存—设置较短的过期时间

数据库中的请求很大

缓存雪崩：

**
大量请求过来，短时间范围内，大量的key集中过期，

瞬间过期数据量太大，导致对数据库服务器造成压力
避免key过期时间集中，可以有效解决雪崩现象（约40%）。
其它策略：
1.限流，降级处理：短时间范围内牺牲一些客户体验，限制一部分请求访问，降低应用服务器压力，待业务低速运转后再逐步放开访问。
2.根据业务数据进行分类错峰，A类90分钟。B类80分钟，C类70分钟。过期时间使用固定时间+随机值的形式，稀释集中到期的key的数量。
3.对mysql严重耗时业务进行优化，对数据库瓶颈进行排查，例如超时查询、耗时较高事务
**
缓存击穿：

**
单个key形成高热数据，但是这个key过期

缓存击穿就是单个高热数据过期的瞬间，数据访问量较大，未命中redis后，发起了大量对同一数据的数据库访问，导致对数据库服务器造成压力。
应对策略：
1.以电商为例，对于主打商品，在活动期间，加大对此类信息key的过期时长。
2.现场调整，监控访问量，对自然流量激增的数据延长过期时间设置为永久性的key。
3.启动定时任务，高峰期来临之前，刷新数据有效器，确保不丢失。
**
缓存穿透：

**
访问不存在的数据，跳过了合法数据的redis数据缓存阶段，每次访问数据库，导致对数据库造成压力，一般是属于黑客攻击造成。
应对策略：
1.缓存null，对查询结果为null的数据进行缓存，长期使用，定期清理，设定短时限，例如30到60秒，最高5分钟
2.白名单策略（效率很低）。
3.使用布隆过滤器，将数据库中所有的查询条件放入布隆过滤器中，当一个查询请求过来时，先经过布隆过滤器筛选，如果判断请求查询值存在则继续查，如果不存在就直接丢弃。

在这里插入图片描述
假设集合里面有3个元素{x, y, z}，哈希函数的个数为3。首先将位数组进行初始化，将里面每个位都设置位0。对于集合里面的每一个元素，将元素依次通过3个哈希函数进行映射，每次映射都会产生一个哈希值，这个值对应位数组上面的一个点，然后将位数组对应的位置标记为1。查询W元素是否存在集合中的时候，同样的方法将W通过哈希映射到位数组上的3个点。如果3个点的其中有一个点不为1，则可以判断该元素一定不存在集合中。反之，如果3个点都为1，则该元素可能存在集合中，这里就不分析存在误判的情况了

redis的伪代码

String get(String key) {
    String value = redis.get(key);     
    if (value  == null) {
        if(!bloomfilter.mightContain(key)){
            return null; 
        }else{
            value = db.get(key); 
            redis.set(key, value); 
        }    
    }
    return value；
}
1
2
3
4
5
6
7
8
9
10
11
12

//应对大量的请求，Nginx限流处理，做负载均衡

3.讲一下springCloud和dubbo的区别
(我回答了两个的特性)

mysql优化

1.MySQL数据库作发布系统的存储，一天五万条以上的增量，预计运维三年,怎么优化？

a. 设计良好的数据库结构，允许部分数据冗余，尽量避免join查询，提高效率。
b. 选择合适的表字段数据类型和存储引擎，适当的添加索引。
c. mysql库主从读写分离。
d. 找规律分表，减少单表中的数据量提高查询速度。
e.添加缓存机制，比如memcached，apc等。
f. 不经常改动的页面，生成静态页面。
g. 书写高效率的SQL。比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE.

2.实践中如何优化MySQL

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

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

3.优化数据库的方法

选取最适用的字段属性，尽可能减少定义字段宽度，尽量把字段设置NOTNULL，例如’省份’、’性别’最好适用ENUM
使用连接(JOIN)来代替子查询
适用联合(UNION)来代替手动创建的临时表
事务处理
锁定表、优化事务处理
适用外键，优化锁定表
建立索引
优化查询语句

4.如何通俗地理解三个范式？

答：

第一范式：1NF是对属性的原子性约束，要求属性具有原子性，不可再分解；

第二范式：2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；

第三范式：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。。

范式化设计优缺点:

优****点:

可以尽量得减少数据冗余，使得更新快，体积小

缺点:对于查询需要多个表进行关联，减少写得效率增加读得效率，更难进行索引优化

反范式化:

优点:可以减少表得关联，可以更好得进行索引优化

缺点:数据冗余以及数据异常，数据得修改需要更多的成本

5.说说对SQL语句优化有哪些方法？（选择几条）

（1）Where子句中：where表之间的连接必须写在其他Where条件之前，那些可以过滤掉最大数量记录的条件必须写在Where子句的末尾.HAVING最后。

（2）用EXISTS替代IN、用NOT EXISTS替代NOT IN。

（3）避免在索引列上使用计算

（4）避免在索引列上使用IS NULL和IS NOT NULL

（5）对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

（6）应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描

（7）应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描\

Myisam和innodb的比较

锁的范围，精细程度上。

Myisam表是表级锁。 Innodb是行级锁。

表级锁：开销小，加锁时间短。

行级锁：相反。

Innodb:数据完整性,并发性好。事务，且是默认表引擎

适合银行转账，等数据安全要求高的应用

Myisam：压缩存储,适合 insert和select多的应用,博客，BBS等
高速并发插入，
压缩：
压缩后的数据
压缩后，不能再对表进行写操作
解压缩

让请求失败变高，面试官问，这种情况怎么处理(这个我说我不知道)

Mysql锁

悲观锁，正如其名，它指的是对数据被外界（包括当前系统的其它事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排它性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）。

锁的优化策略

读写分离
降低锁的持有时间
分段加锁
多个线程尽量以相同的顺序去获取资源，不能将锁的粒度过于细化，不然可能导致加锁释放锁的次数过多，效率反而不好如加一把大锁

第二部分乐观锁

1 概念

1.1 理解方式一（来自网上其它小伙伴的博客）

乐观锁认为一般情况下数据不会造成冲突，所以在数据进行提交更新时才会对数据的冲突与否进行检测。如果没有冲突那就OK；如果出现冲突了，则返回错误信息并让用户决定如何去做。

1.2 理解方式二（来自网上其它小伙伴的博客）

乐观锁的特点是先进行业务操作，不到万不得已不会去拿锁。乐观地认为拿锁多半会是成功的，因此在完成业务操作需要实际更新数据的最后一步再去拿一下锁。

1.3 我的理解

理解一：就是 CAS 操作

理解二：类似于 SVN、GIt 这些版本管理系统，当修改了某个文件需要提交的时候，它会检查文件的当前版本是否与服务器上的一致，如果一致那就可以直接提交，如果不一致，那就必须先更新服务器上的最新代码然后再提交（也就是先将这个文件的版本更新成和服务器一样的版本）

2 如何实现乐观锁呢

首先说明一点的是：乐观锁在数据库上的实现完全是逻辑的，数据库本身不提供支持，而是需要开发者自己来实现。

常见的做法有两种：版本号控制及时间戳控制。

版本号控制的原理：

为表中加一个 version 字段；
当读取数据时，连同这个 version 字段一起读出；
数据每更新一次就将此值加一；
当提交更新时，判断数据库表中对应记录的当前版本号是否与之前取出来的版本号一致，如果一致则可以直接更新，如果不一致则表示是过期数据需要重试或者做其它操作（PS：这完完全全就是 CAS 的实现逻辑呀~）

至于时间戳控制，其原理和版本号控制差不多，也是在表中添加一个 timestamp 的时间戳字段，然后提交更新时判断数据库中对应记录的当前时间戳是否与之前取出来的时间戳一致，一致就更新，不一致就重试。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vhdCV46n-1618927777804)(C:\Users\ZSZ_{_}\AppData\Roaming\Typora\typora-user-images\image-20210405111845822.png)]

1.自我介绍

2.mySQL索引B+Tree的理解，说一下

b+树只有叶子节点存放数据，由于树又宽又矮，可以减小io次数提高效率

3.直接开始码算法

a.删除链表重复元素

双指针

b.判断两颗二叉树是否相同

递归，迭代，深度优先，广度优先

c.字符串中第一次重复出现的字符

三道题都比较简单，a、b秒了，但是输入样例写不出来（还是用不习惯面试平台的问题，大家刷leetcode的一定要抽出时间刷刷牛客这种OJ）

写第二道的时候面试官让我写一下输入样例测试一下，就卡在这了一直没出来，没做第三题，面试官说时间不太够了，就停下了。

4.详细说一下tcp协议？

给说了tcp连接的特点，三次握手四次挥手，为什么需要进行“三次”握手，四次挥手为什么多一次。

5.说一下从浏览器打开一个链接中间发生了什么？

从DNS域名解析说到HTTP说到TCP说到浏览器显示，blabla。。

1.DNS解析
2.建立TCP连接，发送HTTP请求
3.服务端处理请求并返回HTTP响应
4.浏览器解析渲染页面
5.关闭连接

1. DNS解析

回车敲响的那一刻，浏览器检查了输入框，www.didudidudu.com是什么鬼东西？？我需要的可是IP地址呀！万般无奈之下找向了浏览器缓存，让其查找是否有这家伙的记录，结果并没有发现，此时找向系统缓存，主要去查找了系统中的hosts文件，同样没有，此时找向路由器缓存，查看路由器映射表，然而，并没有！于是，计算机将域名发给了本地DNS服务器（提供本地连接的服务商），本地DNS服务器找不到会将域名发送给其他服务器，进行递归过程，首先会发送到根域名服务器去找，返回顶级域名服务器的IP地址，再请求顶级域名服务器IP返回二级域名服务器IP，再请求二级域名服务器IP返回三级域名服务器IP…直到找到对应的IP地址，返回给浏览器。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gn0fXrc0-1618927777805)(mysql优化.assets/20190908184338232.png)]

2. 建立TCP连接

TCP/IP的建议需要经历三次握手

TCP的三次握手还是经常被问到的，这里概述一下

第一次握手：客户端A将标志位SYN置为1,随机产生一个值为seq=J（J的取值范围为=1234567）的数据包到服务器，客户端A进入SYN_SENT状态，等待服务端B确认；

第二次握手：服务端B收到数据包后由标志位SYN=1知道客户端A请求建立连接，服务端B将标志位SYN和ACK都置为1，ack=J+1，随机产生一个值seq=K，并将该数据包发送给客户端A以确认连接请求，服务端B进入SYN_RCVD状态。

第三次握手：客户端A收到确认后，检查ack是否为J+1，ACK是否为1，如果正确则将标志位ACK置为1，ack=K+1，并将该数据包发送给服务端B，服务端B检查ack是否为K+1，ACK是否为1，如果正确则连接建立成功，客户端A和服务端B进入ESTABLISHED状态，完成三次握手，随后客户端A与服务端B之间可以开始传输数据了。

服务端处理请求并返回HTTP响应

服务端拿到Http请求处理后，再返回HttP响应，这里主要涉及到一部分Http的常见知识点，如各种状态码，各种Header的含义。把常见的状态码，常见的Header了解一下就行了。Http相关的知识前面的文章已经介绍过了，就不重复介绍了。

浏览器解析渲染页面

这部分内容偏前端一点。大家可以参考相关博问，后端几乎很少问。

关闭连接

这里要注意的一点是一个TCP连接是可以发送多个Http请求的，不是发送一次Http请求TCP连接就断了。默认情况下建立 TCP 连接不会断开，只有在请求报头中声明 Connection: close 才会在请求完成后关闭连接。这里又涉及到TCP四次挥手

6.反问环节

经典技术栈哈哈，面试官说用的java（果然）。到这面试就基本结束了，总的来说面试官非常好，面试体验也可以，许愿二面啊啊啊啊啊啊啊啊啊

作者：小姬炖蘑菇
链接：https://www.nowcoder.com/discuss/622549?type=post&order=time&pos=&page=1&channel=-1&source_id=search_post_nctrack
来源：牛客网

携程后端开发一面（已过）

1.数据库三大范式，如果某些表在后期维护中违反了三大范式应该怎么处理？

2.jvm结构

3.jvm调参

4.线程池怎么理解，（单线程池，固定数量线程池，缓存线程池）应用场景

5.redis数据结构，持久化方法，过期策略

6.场景题：三本书，每本页码为1，1，1，2，2，2，。。。，。。。三本书共300页，如何分成三本，每本页码为1，2，3，。。。，100

7.场景题：N张车票，求最短路径

最短路径使用

深度优先，选取一个点，找到最短的边，把另一个顶点放入集合中，寻找相连的最小的边，。。。。，n各顶点n-1条边

广度优先

8.序列化的方法，如果有字段不需要被序列化呢？

携程后端开发二面（已过）

1.项目

2.遇到最大的挑战

3.线程池

4.TCP三次握手四次挥手

5.报文超时会怎样（用Java写，没太懂他的意思，不会。。）

6.单例模式

7.手写一个二分查找（一个有重复数组中找到第一个出现的目标数）

8.开放式问题：多种交通方式，找回家路径

9.你的优缺点

10.表单重复提交问题的解决

11.hashset

作者：coco201903192152680
链接：https://www.nowcoder.com/discuss/586465?type=post&order=time&pos=&page=1&channel=-1&source_id=search_post_nctrack
来源：牛客网

一面（11月底）：

1，写一个前缀树，要实现的方法包括put、search、startWith。（基本写出来了）

2，一个很大的无序数组，找出其中最小的k个数。时间复杂度。（只需要说思路，我具体说了用快速排序里面的那种思想）

很大的无序数组，比较排序

3，垃圾收集算法。

复制算法

标记清楚

标记整理

分代垃圾收集

新生代复制算法
老年代标记整理

分区垃圾收集

4，老年代中有对象依赖年轻代中对象，怎么判断年轻代中的对象是否需要被回收。

根路径搜索，root可达性分析

5，B+树作为索引的结构比其他数据结构好在哪，比如二叉树、哈希表、B树。

6，Linux操作系统中的调度算法。

反问：做哪方面的业务？项目是web项目还是app？

一面问题我基本都回答出来了。因为我准备的很充分。

二面（12月初）：
1，自我介绍：问了项目（我没准备项目，所以就说了我研究生论文发表做的项目）
2，怎么想到用fork/join框架去做的，实际开发中都没人用（我项目中用到的，所以问了）
3，多线程安全问题
4，JVM中一个对象从创建到被回收所经历的整个过程
5，平时自己是怎么学习的
6，对于自己的职业生涯规划怎么想的
7，了解哪些设计模式，说一下单例模式
8，事务的隔离级别
9，连接数据库的操作怎么做的
反问：对于后台开发技术栈除了JAVA、数据库等，我还需要学习哪些知识？

面试官都挺好的，对于我不太清楚的都会引导我去说，所以每个问题我基本都能说出一些。但是对于一些后台相关的知识点问题，我说不知道的就不问了。我也不记得是些啥问题了所以没贴。

所以 很多问题都是在特定的环境下提出来的，看面经的时候要甄别，不能看到很多问题貌似都不知道就否定自己。

作者：Mr.Fate
链接：https://www.nowcoder.com/discuss/571226?type=post&order=time&pos=&page=1&channel=-1&source_id=search_post_nctrack
来源：牛客网

1…哪些线程安全的list

Vector CopyAndWriteLinkedList

2.线程不安全的list会导致什么问题

并发操作时，会发生数据不一致

3.什么是深拷贝与浅拷贝

4.用java实现一下

5.项目

6.用过哪些框架

7.jvm的内容

7.算法题，将一个数组中正数放左边，零放中间，负数放右边。利用快排思想排两次

作者：菜鸟程序员小范
链接：https://www.nowcoder.com/discuss/571096?type=post&order=time&pos=&page=1&channel=-1&source_id=search_post_nctrack
来源：牛客网

1）二分查找（mid放错了位置本文内容由网友自发贡献，转载请注明出处：【wpsshop博客】