一、分析性能问题

系统中用户访问日志的数据量，随着时间的推移，这张表的数据量会越来越大，因此我们需要根据业务需求，来对

日志查询模块的性能进行优化。

1.分页查询优化

由于在进行日志查询时，是进行分页查询，那也就意味着，在查看时，至少需要查询两次：

A. 查询符合条件的总记录数。--> count 操作

B. 查询符合条件的列表数据。--> 分页查询 limit 操作

通常来说，count() 都需要扫描大量的行（意味着需要访问大量的数据）才能获得精确的结果，因此是很难对该

SQL进行优化操作的。如果需要对count进行优化，可以采用另外一种思路，可以增加汇总表，或者redis缓存来专

门记录该表对应的记录数，这样的话，就可以很轻松的实现汇总数据的查询，而且效率很高，但是这种统计并不能

保证百分之百的准确 。对于数据库的操作，“快速、精确、实现简单”，三者永远只能满足其二，必须舍掉其中一个。

2.条件查询优化

针对于条件查询,需要对查询条件,

及排序字段建立索引。

3.读写分离

通过主从复制集群，来完成读写分离，使写操作走主节点， 而读操作，走从节点。

4. MySQL服务器优化

5.应用优化

二、优化方式

1.优化count

创建一个额外的表来记录总数，所以每次插入数据之后，更新表就行。

2.优化limit

在进行分页时，一般通过创建覆盖索引，能够比较好的提高性能。一个非常常见，而又非常头疼的分页场景就是

"limit 1000000,10" ，此时MySQL需要搜索出前1000010 条记录后，仅仅需要返回第 1000001 到 1000010 条记

录，前1000000 记录会被抛弃，查询代价非常大。当点击比较靠后的页码时，就会出现这个问题，查询效率非常慢。

# 优化前：
select * from info limit 3000000 , 10;

# 优化后：
select * from info a , (select id from info order by id limit 3000000,10) b where a.id = b.id ;

3.索引优化

根据非索引字段查询时，效率很低，耗时长。所以要提高效率，在该字段添加索引。

4. 排序优化

在查询数据时，如果业务需求中需要我们对结果内容进行了排序处理 , 这个时候,我们还需要对排序的字段建立适当

的索引, 来提高排序的效率

5.读写分离优化

在Mysql主从复制的基础上，可以使用读写分离来降低单台Mysql节点的压力，从而来提高访问效率。对于读写分离的实现，可以通过Spring AOP 来进行动态的切换数据源，进行操作 ：

6.应用优化

  • 缓存

可以在业务系统中使用redis来做缓存，缓存一些基础性的数据，来降低关系型数据库的压力，提高访问效率。

  • 全文检索

如果业务系统中的数据量比较大（达到千万级别），这个时候，如果再对数据库进行查询，特别是进行分页查询，

速度将变得很慢（因为在分页时首先需要count求合计数），为了提高访问效率，这个时候，可以考虑加入Solr 或

者 ElasticSearch全文检索服务，来提高访问效率。

  • 非关系数据库

将其他数据（评论数据等）放在MongoDB等数据库中，从而提高效率。

三、优化实例

1.Insert优化

如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式将大大的缩减客户

端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。

# 优化前
insert into tb_test values(1,'Tom'); 
insert into tb_test values(2,'Cat'); 
insert into tb_test values(3,'Jerry');

# 优化后
insert into tb_test values(1,'Tom'),(2,'Cat')，(3,'Jerry'); 

# 事务优化
start transaction;
insert into tb_test values(1,'Tom'); 
insert into tb_test values(2,'Cat'); 
insert into tb_test values(3,'Jerry');
commit;

2.Order by优化

排序方式有两种：

  • 第一种是通过对返回数据进行排序，也就是通常说的 file sort 排序，所有不是通过索引直接返回排序结果的排序
    都叫 File Sort 排序。
  • 第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

了解了MySQL的排序方式，优化目标就清晰了：尽量减少额外的排序，通过索引直接返回有序数据。where 条件

和Order by 使用相同的索引，并且Order By 的顺序和索引顺序相同，

并且Order by 的字段都是升序，或者都是

降序。否则肯定需要额外的操作，这样就会出现File Sort。

通过创建合适的索引，能够减少 Filesort 的出现，但是在某些情况下，条件限制不能让Filesort消失，那就需要加

快 Filesort的排序操作。对于Filesort ， MySQL 有两种排序算法：

  • 两次扫描算法 ：MySQL4.1 之前，使用该方式排序。首先根据条件取出排序字段和行指针信息，然后在排序区

sort buffffer 中排序，如果sort buffffer不够，则在临时表 temporary table 中存储排序结果。完成排序之后，再根据

行指针回表读取记录，该操作可能会导致大量随机I/O操作。

  • 一次扫描算法：一次性取出满足条件的所有字段，然后在排序区 sort buffffer 中排序后直接输出结果集。排序时

内存开销较大，但是排序效率比两次扫描算法要高。

MySQL 通过比较系统变量 max_length_for_sort_data 的大小和Query语句取出的字段总大小， 来判定是否那种排

序算法，如果max_length_for_sort_data 更大，那么使用第二种优化之后的算法；否则使用第一种。可以适当提高 sort_buffffer_size 和 max_length_for_sort_data 系统变量，来增大排序区的大小，提高排序的效率。

3.Group by 优化

由于GROUP BY 实际上也同样会进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分

组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在

GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。

# 如果查询包含 group by 但是用户想要避免排序结果的消耗， 则可以执行order by null 禁止排序。
# 优化前：
explain select age,count(*) from emp group by age;

# 优化后：
explain select age,count(*) from emp group by age order by null;

第一个SQL语句需要进行"filesort"，而第二个SQL由于order by null 不需要进行
"filesort"， 而上文提过Filesort往往非常耗费时间。

4.嵌套查询优化

# 优化前：
explain select * from t_user where id in (select user_id from user_role ); 

# 优化后：
explain select * from t_user u , user_role ur where u.id = ur.user_id;

连接(Join)查询之所以更有效率一些 ，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的

查询工作。

5.OR条件优化

对于包含OR的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引 ， 而且不能使用到复合索引； 如果没有索引，则应该考虑增加索引。

建议使用 union 替换 or ：

# 优化前：
explain select * from emp where id = 1 or id = 10;

# 优化后：
explain select * from emp where id = 1 union select * from emp where id = 10;

我们来比较下重要指标，发现主要差别是 type 和 ref 这两项。type 显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

UNION 语句的 type 值为 ref，OR 语句的 type 值为 range，可以看到这是一个很明显的差距

UNION 语句的 ref 值为 const，OR 语句的 type 值为 null，const 表示是常量值引用，非常快

这两项的差距就说明了 UNION 要优于 OR 。

6.优化分页查询

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是 limit 2000000,10 ，此时需要MySQL排序前2000010 记录，仅仅返回2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大 。

  • 优化思路1：
explain select * from tb_item t,(select id from tb_item order by id limit 200000,10) a where t.id = a.id;
  • 优化思路2：

该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询 。

explain select * from tb_item where id > 100000 limit 10;

7.使用SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

  • USE INDEX

在查询语句中表名的后面，添加 use index 来提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引。

  • IGNORE INDEX

如果用户只是单纯的想让MySQL忽略一个或者多个索引，则可以使用 ignore index 作为 hint 。

  • FORCE INDEX

为强制MySQL使用一个特定的索引，可在查询中使用 force index 作为hint

8.应用优化

  • 使用连接池

对于访问数据库来说，建立连接的代价是比较昂贵的，因为我们频繁的创建关闭连接，是比较耗费资源的，我们有必要建立 数据库连接池，以提高访问的性能。

  • 减少对MySQL的访问

在编写应用代码时，需要能够理清对数据库的访问逻辑。能够一次连接就获取到结果的，就不用两次连接，这样可以大大减少对数据库无用的重复请求

  • 增加cache层

在应用中，我们可以在应用中增加 缓存 层来达到减轻数据库负担的目的。缓存层有很多种，也有很多实现方式，只要能达到降低数据库的负担又能满足应用需求就可以。因此可以部分数据从数据库中抽取出来放到应用端以文本方式存储， 或者使用框架(Mybatis,Hibernate)提供的一级缓存/二级缓存，或者使用redis数据库来缓存数据

  • 负载均衡

负载均衡是应用中使用非常普遍的一种优化方法，它的机制就是利用某种均衡算法，将固定的负载量分布到不同的服务器上， 以此来降低单台服务器的负载，达到优化的效果。

1.利用MySQL复制分流查询：

通过MySQL的主从复制，实现读写分离，使增删改操作走主节点，查询操作走从节点，从而可以降低单台服务器的读写压力。

2.采用分布式数据架构：

分布式数据库架构适合大数据量、负载高的情况，它有良好的拓展性和高可用性。通过在多台服务器之间分布数 据，可以实现在多台服务器之间的负载均衡，提高访问效率

四、优化细节

1.选取最实用的字段属性

MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。

另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOTNULL，这样在将来执行查询的时候，数据库不用去比较NULL值。 对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。

2.使用联合(UNION)来代替手动创建的临时表

MySQL从4.0的版本开始支持union查询，它可以把需要使用临时表的两条或更多的select查询合并的一个查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。使用union来创建查询的时候，我们只需要用UNION作为关键字把多个select语句连接起来就可以了，要注意的是所有select语句中的字段数目要想同。下面的例子就演示了一个使用UNION的查询。