系列文章目录
前言
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），从第一次访问一个页开始，这个时间窗口范围内的访问不会将这个页移动到新也子列表的头部（暂时放在旧页子列表，这个时间窗口过后若仍然需要访问，才会将这个页移动到新列表的头部）
848
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
