关于MySQL——优化与面试

MySQL问题总结

优化

1.定位慢查询

开源工具:Arthas,skywalking

开启MySQL自带的慢日志:在my.cnf(Windows版是my.ini)配置一下:

# 开启MySQL慢日志查询开关
slow_query_log=1  
# 设置慢日志的时间为2秒,SQL语句查询时间超过2秒就会被计入慢查询中
long_query_time=2

配置完毕后慢SQL会记录在localhost-slow.log中。当然在生产环境中是不会开启慢日志查询的,会影响性能

2.分析慢SQL:采用在select前加上EXPLAIN关键字,例如:

possible_keys:可能会使用到的索引

key:当前SQL命中的索引

key_len:索引占用的大小(一般通过key和key_len来判断是否命中索引)一般key_len越小,索引效果越好。

type:SQL的连接类型一般为根据主键const、唯一索引查询eq_ref、索引查询ref、范围查询range(最低要求)的性能还行。index这种索引树扫描all这种全盘扫描就需要优化了。

如何分析慢SQL:

  1. 先通过key和key_len查看是否命中了索引
  2. 再通过type看是不是扫了全索引或全盘

3.索引的概念以及其底层数据结构

索引(index)帮助MySQL高效获取数据的有序的数据结构。MySQL的innodb是维护了B+树算法。索引就是一种以空间换时间的数据结构,可以提高数据检索效率,降低数据库IO成本,降低CPU消耗

为什么不用二叉树或者红黑树:最坏的二叉树时间复杂度为On,相对不太稳定;红黑树虽然时间复杂度比较稳定,但当MySQL数据量过高的时候,红黑树依旧是二叉树,效率就会很低。

因此引入了B树,相对于二叉树,B树可以有多叉并且也像二叉树一样左边比右边小,并且B树引入了指针的概念。

B+树是在B树的基础上进行了优化,InnoDB就是用B+树实现其索引结构。

B+树相较于B树的优点

  • 磁盘读写代价更低:B+树非叶子结点不会存储数据;在B树查询数据的时候,查询节点的同时,节点的数据也会被读出来。因此B+树查询不会有过多的数据读,这样会在分支上腾出更多空间提高查找性能
  • 查找效率更稳定:B+树所有的数据都存在叶子节点上
  • 更便于扫库排序和区间查询:B+树的叶子节点之间通过双向指针连接。也就是说B+树有head和root两个头指针,而B树只有root指针
  • B树的元素个数比分支数少1,而B+树元素个数=分支数

——————2025.01.09新增

回想起上次面试官问我怎么实现B+树,鬼知道。现在我知道了

所有的叶子节点都在同一层,n阶B+树最多有n个分支,非根节点最少得有一半个分支,根节点最少有两个分支

非叶子节点的元素对应着子节点的最大值,B+树的头指针除了指向root的还有一个指向head的。

 

4.聚簇索引(聚集索引)和非聚簇索引(二级索引)

  • 聚簇索引(必须有,且只有一个):将数据和索引存储在了一起,索引结构的叶子节点保存了行数据
    • 如果有主键,那主键就是聚簇索引
    • 如果没有主键,有unique索引,那么第一个unique索引是聚簇索引
    • 如果都没有,那么innodb提供一个隐藏的rowId作为索引
  • 非聚簇索引(可以存在多个):数据与索引分开存储,索引的叶子节点对应数据的主键和当前索引

5.回表查询,覆盖索引,超大分页优化

回表查询:通过二级索引(非聚簇索引)找到对应的主键值,再通过主键值在聚集索引查行数据

覆盖索引:(简单来说就是不用走回表查询)查询使用了索引,并且需要返回的列都在该索引内可以查到

超大分页查询:举例:limit 900000 10,这里我们需要查询出来前900010条记录并排序,在最后仅仅需要900000~900010的记录,而其他记录丢掉了。

优化:通过覆盖索引+子查询(即先通过子查询对ID(主键)进行排序并取出ID,再通过ID查询对应的row)

SELECT T.*
FROM TABLE T,
    (SELECT id FROM TABLE ORDER BY id LIMIT 600000,10) a
WHERE T.id=a.id

6.索引的创建原则

  • 数据量大且查询比较频繁(单表超过10万条数据)
  • 经常用于where,orderBy,groupBy的字段
  • 区分度高的字段(越接近unique越好)
  • 前缀索引:字符串比较长(其实这里使用ES更好)
  • 联合索引:尽量使用覆盖索引,不需要回表查询
  • 要控制索引的数量,别太多
  • 索引列不能存null的时候记得用notnull的约束

7.索引在什么情况下会失效:

  • 联合索引的情况下违反最左前缀法则(联合索引的使用查询要从索引最左列开始,并且不跳过索引中的列,一旦跳过则后续的都不会生效)例如:当查询条件为 name / name and status / name and status and address 时索引生效;而当查询条件为status / status and address时,索引会失效;当查询条件为name and address时,索引只有name会生效(通过explain查询即可得出结论)

  • 范围查询的列的右边的列会不能使用索引,例如 如果 id、age、sex是联合索引,如果age使用了范围查询,那么sex的索引就失效了
    WHERE id=1 AND age>10 AND sex=1
  • 索引列上进行运算操作
  • 字符串类型不加单引号,会发生类型转换导致索引失效
  • 以%开头的模糊查询也会导致索引失效

8.谈谈SQL优化

  1. 表的设计优化:
    1. 设置合适的数据(tinyint,int,bigint)
    2. 设置合适的字符串(char,varchar)
  2. SQL语句优化:
    1. 避免select *;
    2. 避免索引失效;
    3. 避免在where子句对字段进行表达式操作;
    4. join优化:能用inner join就不用left/right join;使用的时候一定要以小表为驱动()
    5. 尽量使用union all代替union(union会比union all多一层过滤)
  3. 主从复制,读写分离
    1. 如果读场景很多,为了避免写影响读,将一个slave设置为只读;将一个master设置为只写;然后将master数据同步进slave。这样可以提高查询效率
  4. 索引优化(见第6条)
  5. 分库分表

事务

1.什么是事务

事务是一组操作的集合;是一个不可分割的工作单位;事务会把所有操作视为一个整体一起向系统提交或撤销;事务所控制的语句要么同时成功,要么同时失败

2.事务的特性ACID

A原子性:事务是不可分割的最小单元,要么一起成功,要么一起失败——由undo log提供

C一致性:事务完成时所有数据必须保持一致状态——由undo log提供

I隔离性:数据库系统提供的隔离机制,保证事务在不受外部并发操作的影响下进行——由MVCC提供

D持久性:事务一旦提交,那么它的效果就是永久的——由redo log提供

3.并发事务问题

脏读:一个事务读到了另一个事务还未提交的数据

不可重复读:一个事务先后读取同一条记录,但结果却不一样

幻读:一个事务按条件查询时并没有查到数据,但在插入数据的时候又发现这个数据已经存在了。

4.MySQL的隔离级别(隔离级别越高,安全性越高,但是性能就越低,一般都是使用默认的可重复读)

  1. 未提交读(啥都不能解决)
  2. 读已提交(能解决脏读)RC
  3. 可重复读(默认)RR可以解决脏读和不可重复读
  4. 串行化(最高)都可以解决,但是效率很低,因为所有事务在这里都是串行进行的

5.undo-log和redo-log

redo-log前置知识:

  缓冲池(buffer pool)主内存中的一个区域,里面缓存了磁盘上经常操作的真实数据。在执行CRUD时先操作缓冲池内数据(如果缓冲池没有数据,那需要从磁盘加载并缓存)再以一定频率刷新到磁盘,从而减少磁盘IO并加快处理速度

  数据页(page)innodb存储引擎磁盘管理的最小单元,默认大小为16KB,页中存储的是行数据

redo-log:是一种物理日志,提交一些update/delete时,为了方便会先把数据commit进buffer pool(内存),然后由pool和page(数据页,位于磁盘,后缀为ibd)交互。但如果突然服务宕机,pool会与page无法交互,导致持久性无法保持,因此redo-log记录数据页的变化,服务宕机后可用于同步数据。实现事务的持久性

undo-log:是一种逻辑日志,回滚日志,用于记录数据被修改前的信息。一般用于事务:提供回滚和MVCC控制。可以认为他会记录和执行结果完全相反的记录,实现了事务的一致性和原子性

6.MVCC

事务如何保持隔离性:

锁:排它锁:如果事务获取了一个数据行的排它锁,其他事务就不能获取该行的排它锁

MVCC:多版本并发控制。指维护一个数据的多个版本,让读写没有冲突

实现原理:

  1. 隐藏字段
    • DB_TRX_ID:记录插入或最后一次修改这条数据的事务的ID
    • DB_ROLL_PTR:回滚指针,指向这条记录的上一个版本,配合undolog使用
    • DB_ROW_ID:隐藏主键,没有主键才会有这个字段
  2. undo log
    • 在insert,update,delete时便于回滚的日志
      • insert:产生的undo log日志只在回滚时使用,事务提交后即可删除
      • update,delete:产生的undo log日志不仅在回滚时需要,MVCC版本访问也需要,不会被立即删除
  3. readview:用于解决事务查询时选择的版本问题
    • 快照读SQL执行时MVCC提供数据的依据,记录并维护系统当前活跃事务(未提交的)ID
      • 当前读:读取的是当前记录的最新版本,读取时还会对当前记录加锁使其保持版本不变
      • 快照读:读取的是记录数据的可见版本,有可能是历史数据,不加锁,是非阻塞读

主从同步原理

MySQL有二进制文件(BINLOG)记录了所有DDL和DML,但不会包括select、show语句

slave会从master的binlog中读取二进制数据,将其写入自己的relay log中,再由自身执行relay log完成数据同步

分库分表

水平分库:将一个库的数据拆分到多个库中,解决海量数据存储和高并发问题。(我实习的公司就是测试的时候是单库,生产的时候用分片技术把数据库分成多个)

水平分表:解决单表存储和性能问题。————————水平分库分表都需要涉及数据存在哪,去哪取的问题;这里一般引用mycat、sharding-sphere作为中间件

垂直分库:根据业务拆分(例如微服务),高并发环境下提高磁盘IO和网络连接数。

垂直分表:数据进行冷热分离,多表互不影响:把一个常用表的不常用字段抽出来,增加查询效率。

 

补充:

1.4399JAVA校招笔试题:常见索引类型都有哪些

主键索引,唯一索引,普通索引,全文索引(innodb5.6+开始支持,提高like模糊查询效率,基于倒排索引类似于搜索引擎),前缀索引,联合索引

2.如果你的系统里已经有了elasticsearch,那还有使用mysql的必要吗?

有的,mysql虽然在全文查找(模糊查找)上效率很慢,但是elasticsearch是不具备事务性质的。这样会导致只使用elasticsearch会导致脏数据的产生,因此elasticsearch不适合存储原始数据。在项目中两个一起使用,mysql能保证数据的质量,elasticsearch能保证全文搜索的功能(这里在补充一点,模糊查询使用%做前缀的时候索引会失效)

3.mysql中的锁机制

一般分为表级锁和行级锁,在innodb中一般都是使用行级锁,很少用表级锁

表级锁:

lock tables table_name read/write 手动指定锁

unlock tables释放锁

行级锁:

共享锁(S锁)读锁for share

排它锁(X锁)写锁for update

4.关于join

left/right join 左右连接,有主从,从查不到对应则为null

inner join 返回两表交集,无主从

inner join 自连接

posted @   天启A  阅读(31)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· .NET 使用 DeepSeek R1 开发智能 AI 客户端
· 10亿数据,如何做迁移?
· 推荐几款开源且免费的 .NET MAUI 组件库
· c# 半导体/led行业 晶圆片WaferMap实现 map图实现入门篇
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
点击右上角即可分享
微信分享提示