来，我们讨论下如何怎么才能让Sql执行的快。

发现问题

系统业务性能表现
Mysql慢日志。看这里
Mysql主机 CPU负载过高
RDS等云数据库得监控
增加慢sql层，比如利用Spring AOP重写数据源，增加慢Sql告警
explain命令发现使用where条件而没有命中任何索引，或者是为了得到返回结果用到了太多的行.返回结果中。type对应了查询所使用的类型，比如All代表全表扫描，ref代表索引扫描，还会有范围扫描、唯一索引扫描等等。最好都能够达到ref的级别。

Mysql 整体结构

在解决慢Sql问题之前，先看一下Mysql的整体结构和一个查看Sql的执行过程。

Connection Pool 连接池，可以通过设置max_connections=200增加连接数提高并发性能，但是会增加CPU消耗和增加锁的开销。
Parser 解析器。会把Sql解析成 语法树， 校验语法正确，且 缓存语法树
Optimizer 优化器，重新决定表的顺序，选择索引，缓存命中
Cache & Buffer 缓存。两个部分，查询缓存和索引缓存。innodb_buffer_pool_size 设置索引缓存。基本上越大越好。

查询缓存真的会快么？

很重要的三点：

可以缓存执行计划，省去解析和生成执行计划的过程
可以缓存数据，只有sql完全匹配才会命中缓存
只要相关的表发生了变化，缓存即刻失效
好下面来评价一下它是否会有用。
会缓存执行计划，这个是有用的，这个也是为什么视图会稍微快一点的原因，因为视图是被Parser和Optimizer构造成执行计划缓存的。
完全匹配包含了空格等等都不是完全匹配，有不确定因素不算完全匹配比如含有now()
也就是任何的插入更新都会导致失效
缓存是排它锁的，会成为瓶颈。(缓存更新的时候，查询是阻塞的)
评价缓存的唯一标准，缓存命中率，很低。
总结：基本上不会有人依赖于这个缓存的。

那么Buffer有用么？有，大用，后面说。

一些写Sql的基本原则和误解

写好SQL的唯一原则，就是在脑子中模拟数据库是如何做过滤的，这个前提是需要了解很多的知识，先来看一下一些基本的原则和容易的误解。

用具体的字段代替*么？

测试一下好了。

SELECT * FROM fm_news_news WHERE id=95950;
SELECT id FROM fm_news_news WHERE id=95950;

除了减少了IO外， *还会对应一个字典映射。

越小的条件越在右面么？

SELECT * FROM fm_news_news WHERE create_org = 3 AND date_publish > '2016-08-30 23:59:59';

调换顺序和EXPLAIN之后发现：是一样一样一样的啊。 为什么呢？
因为是全表扫的，每扫一行都是匹配这两项，不管什么位置都是要全表扫。

然后我们换一个带索引的SQL:

EXPLAIN SELECT * FROM fm_news_news WHERE id>95950 AND date_update > '2016-08-30 23:59:59';

调换位置会发现还是一样的，这个也是因为Optimizer发挥了作用,会初步计算会过滤掉的行，然后决定选取的索引，
唯一索引>普通索引，其他的索引是先定义的索引，而不是SQL的位置。

OR 替代 IN么？

实际上Optimizer会把IN自动改成OR的格式，没有必要显示的修改。

hibernate 的 n+1问题。

hibernate设置延迟加载的时候会有N+1查询数据库的问题，
可以强制走左连接。

EXIST代替IN么？

首先应用场景不一样，EXIST仅仅适用于子查询。

SELECT * FROM fm_news_news n WHERE EXISTS (SELECT 1 FROM fm_news_channelnews cn WHERE n.id = cn.news_id AND cn.channel_id=120 AND cn.channel_type = 5)
SELECT * FROM fm_news_news WHERE id IN (SELECT news_id FROM fm_news_channelnews WHERE channel_id=120 AND channel_type = 5);
SELECT * FROM fm_news_news n LEFT JOIN fm_news_channelnews cn ON n.id=cn.news_id AND cn.channel_id=120 AND cn.channel_type = 5;
// 上面这个会先查出一个channel_news的临时表，然后用news_news跟这个临时表LEFT JOIN， 所以很低是正常的
SELECT * FROM fm_news_news n LEFT JOIN fm_news_channelnews cn ON n.id=cn.news_id WHERE  cn.channel_id=120 AND cn.channel_type = 5;

2, 4的性能最好。
都会命中索引，网上有的说IN语句的子查询不会命中索引是错误的。

2比1块为什么？
首先看IN是怎么查的，IN中（）内的子查询只执行一次，返回结果集int[] 然后是便利for(int i : int[]) 在便利中找到主表中满足要求的。
而EXIST呢，先执行A的全表扫描得到结果集News[] ns 然后便利这个ns, 判断每一条在EXIST条件中是否能返回true,所以本质是一个EXIST的全表扫。
选对驱动表， IN是子查询是驱动表，EXIST是外面是驱动表

IN不处理null

SELECT * FROM fm_news_news WHERE brief IN (NULL) LIMIT 10这样查询的结果为空，只能使用IS NULL;
但是有null的列都不入索引，所以is not null或者是is null 语句都不走索引，因此我们插入数据的时候索引字段不能为null

实际上决定快慢主要有如下的因素：
1. 两个表的数据量,以及数据分布;
2. 表有没有经过分析;
3. 子查询中是否包含NULL值 (很重要);
4. 是否存在索引;
5. 数据库版本:不同版本的数据库,优化器的工作方式会有差异. 最新版本IN子查询已经可以走索引了

用NOT EXIST代替NOT IN么？

NOT EXIST 仍然是以左表作为驱动表，做全查询，然后判断条件是否成立的。

SELECT * FROM fm_user_devicechannel dc WHERE dc.channel_id NOT IN (NULL);
 SELECT * FROM fm_news_channelnews cn WHERE NOT EXISTS (SELECT 1 FROM fm_user_defaultchannel df WHERE cn.channel_id = df.channel_id );
SELECT * FROM fm_user_devicechannel dc WHERE dc.channel_id NOT IN (SELECT channel_id FROM fm_user_defaultchannel df);

NOT IN null的这个会返回空。所以只有需要判断子查询的结果中不为null的时候才有意义。

其他的情况下因为NOT EXIST会使用索引， NOT IN 是完全的双表全表扫所以最慢，理论上讲永远不用。

先过滤后GROUP BY，而不是先GROUP BY 再HAVING

尽量使用一个复杂语句而不是多次链接数据库么？

这个越来越不是这样了，特别是随着SOA，微服务的盛行。实际上拆分多个语句会有如下的好处:

让缓存效率更高。如果第一个查询已经执行过，那么就可以跳过。
减少锁竞争
在应用层做关联，可以对数据库进行拆分，获得更好的扩展性
使用IN（）代替关联查询，本身会比关联查询更高效
在应用层可以重用第一次的查询结果，比如做缓存。

下面是索引，单独拿出来说

索引

为什么索引会变快？

是由索引的数据结构决定的。索引是一个二叉树，二叉树的便利过程就是一个二分查找算法。查找的复杂度为logn，n是树的高度。
因为效率跟树的高度有关，所以一般索引都是平衡二叉树，平衡二叉树就是两边的高度尽量一样高。这种再插入的时候会增加左旋和右旋，但是查询效率很高。
再进化就是B+ tree了，如下图的结构：

可见每个节点是多个值，这个主要是因为磁盘IO的读取性质，连续读远远>随即读， B+Tree的一个节点会放在连续的空间，然后通过系统的页加载机制，一次读出多个数据，大大的减少磁盘I/O次数，增加性能。

通过上面的方法保证了有索引的查询会比较快。

索引一定快么？

不一定，首先如果不是很唯一的值就不一定快。
另外因为需要先查找索引页在查找数据页，一般当使用索引仍然需要查询70%以上数据的时候索引就比不用索引还慢了
如果数据特别少也是不用索引比较快。比如在数据少的时候冒泡比二分查找还快。

聚簇索引、非聚簇索引、覆盖索引

聚簇索引：
类似于没有目录的字典，表的数据就是按照索引的顺序来存储的，既叶子节点就是表数据。所以一张表上最多只有一个。更新的性能差。
但是因为物理上的位置就是相邻的，所以呢范围查找会比较快。
还有一个问题是容易出现尾端热点，可能有性能问题。
非聚簇索引：
叶子节点是到真实物理数据的指针和索引字段的内容，类似于目录。
插入的性能会好
范围查询就没有这么大的优势了。
覆盖索引：所有的数据都是索引字段，直接在内存中便利索引就拿到了，不需要。

索引命中的问题

连接列不会用索引 a||""||b
like 右通配符能命中索引 'a%' 左通配符不行'%b'
组合索引，(a, b, c) 查询 a AND b, 或者是a 但是如果是 b AND c的这种就不行
!= 不会使用索引，改为范围
更新少，唯一性高，短列比较适合做索引
对列的函数计算不会用到索引，比如md5(colume1)
函数计算针对常量则没有问题，比如 date_publish > DATE_FORMAT(now(), '')
NULL 字段不会命中索引 IS NULL 和 IS NOT NULL都要少用
索引字段都命中索引，则会变成覆盖索引，效率更高
很重要的一点， LEFT JOIN 的时候ON的语句一定是要有索引的，否则会变成全表查。

承接前面的Buffer,这个就是用来保存索引的，可以用innodb_buffer_pool_size来设定，一般这台机器的所有内存的70-80%就是给他了。

分库分表下的查询优化

全局表。
插入更新的时候会同步到所有的分片。
查询的时候使用一个分片。
ER Join
表分组的思路，子表的存储位置依赖于主表，解决Join的问题。
Share Join
支持两张表，解析SQL语句，分别查询，然后再聚合

时间： 2024-09-07 18:55:38