Java进阶——MySQL之SQL优化上

身在这个行业，免不了写各式各样的SQL以应对日常复杂的业务需求，我记得我刚在58去实习的时候，刚好赶上一条慢SQL同事和领导都焦头烂额，当时没想到我竟然帮到了他们的忙，也因此领导对我颇为照顾，但也终究没能避免不了公司人力调整，可能这就是该有的经历吧。去年年末现在公司也是一堆10来秒的慢SQL啃到最后还是交给了我来啃，最终我还是啃的差不多了，只剩下几条一两秒的业务考虑我放弃了。哈哈，说这么多，只想表达SQL优化很重要，圈起来可能要考。
接下来，我简单分享下我对SQL优化的一些看法，总的来说不外乎下面这些：
1.先瞅一眼，SQL写了多长，看看业务需要的是啥，这么写是不是合理，如果可以尽量分解，在代码层做处理，减轻DB压力。
2.如果业务合理，用上一篇分享的explain工具详解查看当前SQL的执行计划。
3.对于因为没有走索引而造成的简单慢查询，可以根据需求建索引字段。
4.有些没走索引的原因是因为，SQL书写的原因导致无法触发索引，这种就需要对SQL进行合理的优化，促使触发索引列。
5.对于因为单表数据量太大导致的慢，可以根据业务需求做分表或者冷热备份的相关方案解决，具体的方案我将在后面持续分享。
6.对于因为QPS太大导致的慢，这种我看还是考虑集群吧，具体方案同样在后期分享。
总的来说，任何问题都不会太难，如果你觉得很难肯定是你方向出了问题，大道至简这个到理我们要时刻谨记，我们要自己去琢磨一套适合自己的高效方法体系，如遇到中小型企业的问题，欢迎探讨，大型互联网企业毕竟没去过，不敢信口开河。
接下来，做些简单的实战：
方便实战先建张表

CREATE TABLE emp(
id int(11) NOt null AUTO_INCREMENT,
`name` VARCHAR(20) not null DEFAULT '' COMMENT '姓名',
age int(11) not null DEFAULT 0 COMMENT '年龄',
`pos` VARCHAR(20) not null DEFAULT '' COMMENT '职位',
hire_time TIMESTAMP not null DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
PRIMARY KEY (`id`),
KEY `idx_name_age_pos` (name,age,pos) USING BTREE
)ENGINE=INNODB auto_increment=1 DEFAULT charset=utf8 COMMENT='员工表';

给点数据

INSERT INTO emp(name,age,pos,hire_time) VALUES('gfh',23,'dev',NOW());
INSERT into emp(name,age,pos,hire_time) VALUES('dsdg',48,'dev',NOW());
INSERT into emp(name,age,pos,hire_time) VALUES('dg',14,'dev',NOW());

如何建合适的索引列：
加入现在有一个需求需要根据姓名name查询姓名、年龄、职位这三个字段，有人不懂索引的原理，可能会建一个name列的索引字段，然而查询是按如下字段进行的：
1.根据我前面分享的索引原理简介可以知道，name列构建的索引是非主键索引，数据节点存储的是name值和对应的主键值。
2.要想知道其他字段的值，必须先从name索引列查出对应的id值，然后回表查询详细数据
通过分析，可以看到我们要做回表操作，增加了查询代价，因此我们更合理的操作是建立联合索引，即对3个字段建立索引，这样会将三个字段的值都存在name索引列，不需要做回表操作。
值得注意的是，这里并不是适合所有的场合，这你就得知道为什么二级索引不会存储所有数据了。我建议在查询字段不多的情况可以采用这种方式提高效率。
常见SQL优化
分组、排序Order by与Group by的优化：
分组其实在底层也是做了排序操作的，所以这俩问题可以放一起讨论，如果在大数据量的情况下使用using filesort那不用看了，基本没法用。所以我们与排序有关的操作一定要借助索引，因为索引自带了排序功能的，所以会更高效。就现有的这张表，我们来看看什么情况下会触发索引：

EXPLAIN SELECT * FROM emp where name='dsdg' and pos='dev' ORDER BY age

结果很明显这里触发到了索引：我们的索引列idx_name_age_pos是按照姓名name、年龄age、职位pos顺序排序的，也就是在存储的时候，首先根据name的字典序排序，当name相同的时候再根据age排序，然后当age也相同的时候再根据pos排序，所以在执行这个SQL的时候会根据where条件找出name为dsdg的数据，因为age已经有序，直接根据pos过滤出来就好了。

EXPLAIN SELECT * FROM emp where name='dsdg'  ORDER BY pos

一看就知道这条SQL没能触发索引，为什么呢？
原因很简单，好好想想因该很容易想明白，原来三个字段是依次排序的，那么现在不用age的排序很有可能出现按照职位排序因该是第一的，而年龄却很大排在了最后，这里索引排好的序在该场景下根本无用。然后你看我在where后加个age条件，肯定能触发索引：

EXPLAIN SELECT * FROM emp where name='dsdg' and age=23  ORDER BY pos

讲到这里我想其他情况就很简单了吧

EXPLAIN SELECT * FROM emp where name='dsdg'  ORDER BY age,pos

EXPLAIN SELECT * FROM emp where name='dsdg'  ORDER BY pos,age

一些常见的情况就不多说了，再举例几个特殊点的例子：

EXPLAIN SELECT * FROM emp where name='dsdg'and age = 23  ORDER BY pos,age

这条SQL之所以会走索引，是因为执行器做了优化，虽然后面做了age排序，但是age是一个常量，因此后面的排序是直接忽略了。

EXPLAIN SELECT * FROM emp where name='dsdg' ORDER BY age asc,pos desc

这条未能触发的原因是数据库默认的排序是升序的，排序方式不一致会导致，不能触发。
今天的分享就到这里了，希望对大家有帮助。