来，我们讨论下如何怎么才能让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

实际上决定快慢主要有如下的因素：

两个表的数据量,以及数据分布;表有没有经过分析;子查询中是否包含NULL值 (很重要);是否存在索引;数据库版本:不同版本的数据库,优化器的工作方式会有差异. 最新版本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||""||blike 右通配符能命中索引 '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语句，分别查询，然后再聚合