系列文章目录前言
SQL优化策略
避免不走索引的场景
尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描。尽量避免使用in 和not in，会导致引擎走全表扫描。如下：尽量避免使用 or，会导致数据库引擎放弃索引进行全表扫描。如下尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。查询条件不能用 <> 或者 != SELECt语句其他优化
避免出现select *多表关联查询时使用小表在前用where字句替换HAVINg字句 LRU算法
Buffer Pool缓冲池缓冲池LRU算法read ahead
前言
BATJTMD等大厂的面试难度越来越高，但无论从大厂还是到小公司，一直未变的一个重点就是对SQL优化经验的考察。一提到数据库，先“说一说你对SQL优化的见解吧？”。SQL优化已经成为衡量程序猿优秀与否的硬性指标，甚至在各大厂招聘岗位职能上都有明码标注，如果是你，在这个问题上能吊打面试官还是会被吊打呢
废话不多说直接上图
优化成本：硬件>系统配置>数据库表结构>SQL及索引。优化效果：硬件<系统配置<数据库表结构 我就会打王者荣耀可以吗？ 言归正传，我们一般遵循五个原则 减少数据访问： 设置合理的字段类型，启用压缩，通过索引访问等减少磁盘IO返回更少的数据： 只返回需要的字段和数据分页处理 减少磁盘io及网络io减少交互次数： 批量DML操作，函数存储等减少数据连接次数减少服务器CPU开销： 尽量减少数据库排序操作以及全表查询，减少cpu 内存占用利用更多资源： 使用表分区，可以增加并行操作，更大限度利用cpu资源
总结起来大致归为三点 最大化利用索引；尽可能避免全表扫描；减少无效数据的查询；
SQL优化策略
优化： 如果需求是要在前面使用模糊查询， 如果是子查询，可以用exists代替 优化： 优化：SELECt * FROM t WHERe score = 0 使用索引列作为条件进行查询时，需要避免使用<>或者!=等判断条件。如确实业务需要，使用到不等于符号，需要在重新评估索引建立，避免在此字段上建立索引，改由查询条件中其他索引字段代替。 首先，select * 操作在任何类型数据库中都不是一个好的SQL编写习惯。 使用select * 取出全部列，会让优化器无法完成索引覆盖扫描这类优化，会影响优化器对执行计划的选择，也会增加网络带宽消耗，更会带来额外的I/O,内存和CPU消耗。 建议提出业务实际需要的列数，将指定列名以取代select * 在MySQL中，执行 from 后的表关联查询是从左往右执行的（Oracle相反），第一张表会涉及到全表扫描，所以将小表放在前面，先扫小表，扫描快效率较高，在扫描后面的大表，或许只扫描大表的前100行就符合返回条件并return了。 避免使用HAVINg字句，因为HAVINg只会在检索出所有记录之后才对结果集进行过滤，而where则是在聚合前刷选记录，如果能通过where字句限制记录的数目，那就能减少这方面的开销。HAVINg中的条件一般用于聚合函数的过滤，除此之外，应该将条件写在where字句中 先来张图镇镇场子 缓冲池是缓冲需要被访问的表和索引数据的内存区域。为了加速，缓冲池将频繁访问的数据直接放在内存中处理。在专有服务器（dedicated server）上，通常给缓冲池分配高达80%的总内存。 为了提升大批量读操作的效率，缓冲池被分成了很个页（page），每个页可以容纳多个行。为了提升缓存管理的效率，缓冲池里的块是以链表的形式存放；基于LRU算法的一种变体，很少使用的数据会随着时间被踢出缓存。 如何利用缓冲池将频繁使用的数据留在内存中，是Mysql优化的一个重要方面。 缓冲池是按照LRU（least recently used最近最少使用）算法的一种变体来管理的一个链表。当进入新的数据页需要空间时，最近一段时间内最少使用的页会被踢出，然后新进来的页会放在列表的中间。中间点的选择方案是，把列表分成两个子列表： 列表头，新页子列表，最近访问过的列表尾，旧页子列表，最近很少访问的
默认情况下 其算法规则如下 缓冲池的3/8分给旧页子列表列表的中间点是新页子列表的尾部和旧页子列表的头部的连接处，即新旧子列表的交界点当InnoDB将页读取进缓冲池，最开始是放在中间点。触发读页进缓冲池的操作除了普通的SQL查询，还可以是InnoDB自动执行的read ahead操作，即预读功能 一种异步IO请求机制，预先将一组数据页（一整个区extent，64个页page）放进缓冲池，因为这个区有可能很马上就需要访问，因此提前做好准备。分为线性预读(linear read ahead)和随机预读(random read ahead)。 线性预读 随机预读 访问一个旧页子列表的页会让这个页变‘新’，它会被移动到缓冲池列表的头部，并且是新页列表的头部。如果页的读取是由于被语句需要，则页立即会被标记为已访问并加入新页子列表，如果是由于read ahead机制被预读，则不会立即被标记为已访问（页有可能直到被踢出也都不会被访问） 缓冲池中的页会按照最近一次访问的时间排序，未被访问的页会慢慢移向列表的尾部。从中间点写入页会导致旧页列表里原有的页往尾部移动。最终会把最久未被使用的哪些页挤出缓冲池。 默认情况下，查询读取的页会立刻被移动到了新页子列表，意味着他们会在缓冲池中存留更长时间。全表扫描（例如Mysqldump操作,或者没有where条件的select语句）会将大量数据加载进行缓冲池，同时会踢出等量更旧的数据，即使新数据或许很难被再次使用。类似地，通过预读自动被加载进缓冲池之后，一旦被访问，就会移动到新页子列表头部。这些情况都会使得频繁使用的页被挤到旧页子列表，进而被挤出缓冲池。有一种技术是可以优化这种情况的，这项技术通过innodb_old_blocks_time系统变量设置了一个时间阈值（单位为ms），从第一次访问一个页开始，这个时间窗口范围内的访问不会将这个页移动到新也子列表的头部（暂时放在旧页子列表，这个时间窗口过后若仍然需要访问，才会将这个页移动到新列表的头部）
声明：以下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代替
-- 不走索引
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/10 = 9
-- 走索引
SELECt * FROM T WHERe score = 10*9
查询条件不能用 <> 或者 !=
更多优化建议
LRU算法
OK问题来了，什么是read ahead
read ahead
若前一个区内被顺序访问的数据块数量超过一个固定值（innodb_read_ahead_threshold系统变量），就会预先读取一个下一个区中的所有页（这里读取和访问不是一回事）
一个区内缓存在缓冲池的连续数据页数量一旦超过13，就会将这个区的所有块都预先缓存
