MySQL优化以及LRU算法

系列文章目录


前言

BATJTMD等大厂的面试难度越来越高,但无论从大厂还是到小公司,一直未变的一个重点就是对SQL优化经验的考察。一提到数据库,先“说一说你对SQL优化的见解吧?”。SQL优化已经成为衡量程序猿优秀与否的硬性指标,甚至在各大厂招聘岗位职能上都有明码标注,如果是你,在这个问题上能吊打面试官还是会被吊打呢

废话不多说直接上图
在这里插入图片描述

  • 优化成本:硬件>系统配置>数据库表结构>SQL及索引。
  • 优化效果:硬件<系统配置<数据库表结构<SQL及索引
String result = "嗯,不错,";
 
if ("SQL优化经验足") {
    if ("熟悉事务锁") {
        if ("并发场景处理666") {
            if ("会打王者荣耀") {
                result += "明天入职" 
            }
        }
    }
} else {
    result += "先回去等消息吧";
} 
 
Logger.info("面试官:" + result );

我就会打王者荣耀可以吗?
在这里插入图片描述

言归正传,我们一般遵循五个原则

  • 减少数据访问: 设置合理的字段类型,启用压缩,通过索引访问等减少磁盘IO
  • 返回更少的数据: 只返回需要的字段和数据分页处理 减少磁盘io及网络io
  • 减少交互次数: 批量DML操作,函数存储等减少数据连接次数
  • 减少服务器CPU开销: 尽量减少数据库排序操作以及全表查询,减少cpu 内存占用
  • 利用更多资源: 使用表分区,可以增加并行操作,更大限度利用cpu资源

总结起来大致归为三点

  • 最大化利用索引;
  • 尽可能避免全表扫描;
  • 减少无效数据的查询;

SQL优化策略

声明:以下SQL优化策略适用于数据量较大的场景下,如果数据量较小,没必要以此为准,以免画蛇添足。

避免不走索引的场景

尽量避免在字段开头模糊查询,会导致数据库引擎放弃索引进行全表扫描。
SELECT * FROM t WHERE username LIKE '%陈%'

优化:

SELECT * FROM t WHERE username LIKE '陈%'
  • 如果需求是要在前面使用模糊查询,
    使用MySQL内置函数INSTR(str,substr) 来匹配,作用类似于java中的indexOf(),查询字符串出现的角标位置
  • 使用FullText全文索引,用match against 检索
  • 数据量较大的情况,建议引用ElasticSearch、solr,亿级数据量检索速度秒级
  • 当表数据量较少(几千条儿那种),别整花里胡哨的,直接用like ‘%xx%’
尽量避免使用in 和not in,会导致引擎走全表扫描。如下:
SELECT * FROM t WHERE id IN (2,3)
优化:如果是连续数值,可以用between代替

如果是子查询,可以用exists代替

-- 不走索引
select * from A where A.id in (select id from B);
-- 走索引
select * from A where exists (select * from B where B.id = A.id);
尽量避免使用 or,会导致数据库引擎放弃索引进行全表扫描。如下
SELECT * FROM t WHEREid = 1 OR id = 3

优化:

SELECT * FROM t WHERE id = 1
   UNION
SELECT * FROM t WHERE id = 3
尽量避免进行null值的判断,会导致数据库引擎放弃索引进行全表扫描
SELECT * FROM t WHERE score IS NULL

优化:SELECT * FROM t WHERE score = 0

尽量避免在where条件中等号的左侧进行表达式、函数操作,会导致数据库引擎放弃索引进行全表扫描。
-- 全表扫描
SELECT * FROM T WHERE score/10 = 9
-- 走索引
SELECT * FROM T WHERE score = 10*9
查询条件不能用 <> 或者 !=

使用索引列作为条件进行查询时,需要避免使用<>或者!=等判断条件。如确实业务需要,使用到不等于符号,需要在重新评估索引建立,避免在此字段上建立索引,改由查询条件中其他索引字段代替。

SELECT语句其他优化

避免出现select *

首先,select * 操作在任何类型数据库中都不是一个好的SQL编写习惯。

使用select * 取出全部列,会让优化器无法完成索引覆盖扫描这类优化,会影响优化器对执行计划的选择,也会增加网络带宽消耗,更会带来额外的I/O,内存和CPU消耗。

建议提出业务实际需要的列数,将指定列名以取代select *

多表关联查询时使用小表在前

在MySQL中,执行 from 后的表关联查询是从左往右执行的(Oracle相反),第一张表会涉及到全表扫描所以将小表放在前面,先扫小表,扫描快效率较高,在扫描后面的大表,或许只扫描大表的前100行就符合返回条件并return了。

用where字句替换HAVING字句
  • 避免使用HAVING字句,因为HAVING只会在检索出所有记录之后才对结果集进行过滤,而where则是在聚合前刷选记录,如果能通过where字句限制记录的数目,那就能减少这方面的开销。HAVING中的条件一般用于聚合函数的过滤,除此之外,应该将条件写在where字句中
    更多优化建议

LRU算法

先来张图镇镇场子
在这里插入图片描述

Buffer Pool缓冲池

缓冲池是缓冲需要被访问的表和索引数据的内存区域。为了加速,缓冲池将频繁访问的数据直接放在内存中处理。在专有服务器(dedicated server)上,通常给缓冲池分配高达80%的总内存。

为了提升大批量读操作的效率,缓冲池被分成了很个页(page),每个页可以容纳多个行。为了提升缓存管理的效率,缓冲池里的块是以链表的形式存放;基于LRU算法的一种变体,很少使用的数据会随着时间被踢出缓存。

如何利用缓冲池将频繁使用的数据留在内存中,是Mysql优化的一个重要方面。

缓冲池LRU算法

缓冲池是按照LRU(least recently used最近最少使用)算法的一种变体来管理的一个链表。当进入新的数据页需要空间时,最近一段时间内最少使用的页会被踢出,然后新进来的页会放在列表的中间。中间点的选择方案是,把列表分成两个子列表:

  • 列表头,新页子列表,最近访问过的
  • 列表尾,旧页子列表,最近很少访问的

在这里插入图片描述

默认情况下 其算法规则如下

  • 缓冲池的3/8分给旧页子列表
  • 列表的中间点是新页子列表的尾部和旧页子列表的头部的连接处,即新旧子列表的交界点
  • 当InnoDB将页读取进缓冲池,最开始是放在中间点。触发读页进缓冲池的操作除了普通的SQL查询,还可以是InnoDB自动执行的read ahead操作,即预读功能
    OK问题来了,什么是read ahead

read ahead

一种异步IO请求机制,预先将一组数据页(一整个区extent,64个页page)放进缓冲池,因为这个区有可能很马上就需要访问,因此提前做好准备。分为线性预读(linear read ahead)和随机预读(random read ahead)。

  • 线性预读
    若前一个区内被顺序访问的数据块数量超过一个固定值(innodb_read_ahead_threshold系统变量),就会预先读取一个下一个区中的所有页(这里读取和访问不是一回事)

  • 随机预读
    一个区内缓存在缓冲池的连续数据页数量一旦超过13就会将这个区的所有块都预先缓存

  • 访问一个旧页子列表的页会让这个页变‘新’,它会被移动到缓冲池列表的头部,并且是新页列表的头部。如果页的读取是由于被语句需要,则页立即会被标记为已访问并加入新页子列表,如果是由于read ahead机制被预读,则不会立即被标记为已访问(页有可能直到被踢出也都不会被访问)

  • 缓冲池中的页会按照最近一次访问的时间排序,未被访问的页会慢慢移向列表的尾部。从中间点写入页会导致旧页列表里原有的页往尾部移动。最终会把最久未被使用的哪些页挤出缓冲池。

  • 默认情况下,查询读取的页会立刻被移动到了新页子列表,意味着他们会在缓冲池中存留更长时间。全表扫描(例如Mysqldump操作,或者没有where条件的select语句)会将大量数据加载进行缓冲池,同时会踢出等量更旧的数据,即使新数据或许很难被再次使用。类似地,通过预读自动被加载进缓冲池之后,一旦被访问,就会移动到新页子列表头部。这些情况都会使得频繁使用的页被挤到旧页子列表,进而被挤出缓冲池。有一种技术是可以优化这种情况的,这项技术通过innodb_old_blocks_time系统变量设置了一个时间阈值(单位为ms),从第一次访问一个页开始,这个时间窗口范围内的访问不会将这个页移动到新也子列表的头部(暂时放在旧页子列表,这个时间窗口过后若仍然需要访问,才会将这个页移动到新列表的头部)

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值