MySQL学习笔记12——效率和优化

一、对查询语句进行调优

你肯定遇到过这样的情况：SQL语句执行起来特别慢，要等好久才出结果，或者是干脆就"死”在那里，一点反应也没有。一旦遇到这种问题，就要考虑进行优化了。

而要想提高应用的运行效率，我们就必须掌握优化查询的方法。下面就讲一下MySQL的查询分析语句和2种优化查询的方法。

1、查询分析语句

虽然MySQL的查询分析语句并不能直接优化查询，但是却可以帮助我们了解SQL语句的执行计划，有助于分析查询效率低下的原因，进而有针对性地进行优化。查询分析语句的语法结构是：

{EXPLAIN | DESCRIBE | DESC }查询语句;

2、优化查询方法

使用关键字 “ LIKE ”

“LIKE" 经常被用在查询的限定条件中，通过通配符"%"来筛选符合条件的记录。比如:

• WHERE字段LIKE’%aa’, 表示筛选出所有以字段以’aa’结尾的记录;
• WHERE字段LIKE ‘aa%’, 表示筛选出所有以’aa’开始的记录;
• WHERE字段LIKE ‘%aa%’, 表示所有字段中包含’aa’的记录。

这里我们要注意的是，通配符在前面的筛选条件是不能用索引的。也就是说，WHERE字段LIKE ‘%aa’ 和WHERE字段LIKE ‘%aa%’ 都不能使用索引，但是通配符在后面的筛选条件，就可以使用索引。

使用关键字 “ OR ”

关键字"OR"表示“或”的关系，“WHERE 表达式1 OR 表达式2”，就表示表达式1或者表达式2中只要有一个成立，整个WHERE条件就是成立的。

需要注意的是，只有当条件语句中只有关键字"OR"，并且"OR"前后的表达式中的字段都建有索引的时候，查询才能用到索引。

注意
关于优化查询，还有一个值得关注的点，就是子查询。这是MySQL的一项重要的功能，可以帮助我们通过一个SQL语句实现比较复杂的查询。

但是，子查询的执行效率不高。因为MySQL会用临时表把子查询的结果保存起来，然后再使用临时表的内容完成查询。这样一来，查询就多了一个创建临时表的过程，执行效率没有连接查询高。

针对这种情况，建议是把子查询转换成连接查询，这样可以进一步提高查询的效率。

二、改进表设计以提高性能

MySQL在存取数据时，并不是一条条去处理的， 而是会按照固定大小的页进行处理，如果数据记录占用了不必要的存储空间，就会导致一次读入的有效数据很少。那么，无论怎么改写语句，都无法提升这步操作的效率。这个时候，对表的设计进行优化，就是必不可少的了。

所以，这里介绍一下怎么通过优化数据类型、合理增加冗余字段、拆分表和使用非空约束等方法，来改进表的设计，从而提高查询性能。

1、优化数据类型

对整数数据类型进行优化

之前建议遇到整数类型的字段可以用INT型。这样做的理由是，INT 型数据有足够大的取值范围，不用担心数据超出取值范围的问题。

刚开始做项目的时候，首先要保证系统的稳定性，这样设计字段类型是可以的。

但是，随着你的经验越来越丰富，参与的项目越来越大，数据量也越来越多的时候，你就不能只从系统稳定性的角度来思考问题了，还要考虑到系统整体的效率。

这是因为，在数据量很大的时候，数据类型的定义，在很大程度上会影响到系统整体的执行效率。这个时候，就必须同时考虑稳定性和效率。

既可以使用文本类型也可以使用整数类型的字段，要使用整数类型，而不要用文本类型。

跟文本类型数据相比，大整数往往占用更少的存储空间，因此，在存取和比对的时候，可以占用更少的内存。所以，遇到既可以使用文本类型，又可以使用整数类型来定义的字段，尽量使用整数类型，这样可以提高查询的效率。

2、合理增加冗余字段以提高效率

在数据量大，而且需要频繁进行连接的时候，为了提升效率，我们也可以考虑增加冗余字段来减少连接。

3、拆分表

除了优化数据类型与合理增加冗余字段之外，我们还可以通过对大表进行拆分的方法优化查询。

这样做的原因是，这些表中某些字段的操作频率很高，经常要进行查询或者更新操作，而另外一些字段的使用频率却很低，如果放在一个表里面， 每次查询都要读取大记录，会消耗较多的资源。

这个时候，如果把这个大表拆分开，把使用频率高的字段放在一起形成一个表，把剩下的使用频率低的字段放在一起形成一个表, 这样查询操作每次读取的记录比较小，查询效率自然也就提高了。

4、使用非空约束

在设计字段的时候，如果业务允许，建议尽量使用非空约束。这样做的好处是，可以省去判断是否为空的开销，提高存储效率。而且，非空字段也容易创建索引。使用非空约束，甚至可以节省存储空间(每个字段1个比特)。

我们省去了判断空值的开销，还能够节省一些存储空间。

三、如何充分利用系统资源

内存和CPU都是有限的资源，因此，把它们的作用发挥到极致，对提高应用的承载能力来说至关重要。

接下来介绍一下优化系统配置的方法，同时还会讲解系统自带的监控工具，从而帮助我们合理配置系统资源，精准发现系统资源的瓶颈，进一步提升我们处理大并发、大数据的能力。

1、优化系统资源配置

对CPU资源的掌控，关系到系统整体的成败。因为CPU资源是系统最核心的资源，无可替代且获取成本高。

如果应用无法控制CPU的使用率，就有可能是失败的，不管界面多么人性化，功能多么强大。

因此，我们需要管理好系统配置，把资源效率提升到极致。系统参数控制着资源的配置，调整系统参数的值，可以帮助我们提升资源的利用效率。

• 系统参数InnoDB_flush_log_at _trx_commit适用于InnoDB存储引擎。默认的值是1，意思是每次提交事务的时候，都把数据写入日志，并把日志写入磁盘。0表示每隔1秒将数据写入日志，并将日志写入磁盘。2表示每次事务提交的时候，将数据写入日志，但是日志每隔1秒写入磁盘。
• 系统参数InnoDB_buffer_pool_size表示InnoDB存储弓|擎使用多少缓存来存储索引和数据。这个值越大，可以加载到缓存区的索引和数据量就越多，需要的磁盘读写就越少。
• 系统参数InnoDB_buffer_pool_instances的意思是，将InnoDB的缓存区分成几个部分，可以提高系统的并行处理能力。

CPU资源是系统的核心资源，获取成本非常高。CPU的特点就是阻塞，只
要CPU一开始计算，就意味着等待。遇到CPU资源不足的问题，可以从两个思路去解决:

• 疏通拥堵路段,消除瓶颈，让等待的时间更短；
• 开拓新的通道，增加并行处理能力。

2、如何利用系统资源来诊断问题

解决CPU资源不足需要消除瓶颈，而消除瓶颈的第一步就是要发现它。

Performance Schema是一种专门用来监控服务器执行情况的存储引擎，它会把监控服务器执行情况的数据记录在系统自带的数据库performance_ schema中。我们可以利用监控的数据，对服务器中执行查询的问题进行诊断。

如何启用系统监控
系统数据库performance_ schema中的表setup_instruments 和setup_ consumers中的数据，是启用监控的关键。

• setup_instruments 保存的数据，表示哪些对象发生的事件可以被系统捕获。
• setup_consumers保存的数据，用来控制保存哪些事件的信息。

我们可以通过修改这两个表的内容来开启系统监控，并且把监控数据保存到相应的数据表中，方便我们对查询的执行情况进行诊断。

建议

如果我们遇到了因为无法控制CPU的使用率而导致系统崩溃的情况，首先应该想到的是应用本身有缺陷，然后找到自身的问题，并加以解决，而不是增加系统资源的投入，采购功能强大的CPU和扩大内存等。

原因有2个：

• 资源永远是有限的，如何在有限的资源前提下提高系统的承载能力，是我们应该首先考虑的；
• 资源的投入都是经过谨慎评估的，除非有充足的理由确信，同等条件下，我们开发的应用承载能力已经超过了业界最高水平，否则就说明应用本身还有提升的空间。