mysql 计算字段 优化_MySQL--性能优化方案

性能优化(Optimize)指的是在保证系统正确性的前提下，能够更快速响应请求的一种手段。而且有些性能问题，比如慢查询等，如果积累到一定的程度或者是遇到急速上升的并发请求之后，会导致严重的后果，轻则造成服务繁忙，重则导致应用不可用。它对我们来说就像一颗即将被引爆的定时炸弹一样，时刻威胁着我们。因此在上线项目之前需要严格的把关，以确保 MySQL 能够以最优的状态进行运行。

MySQL 的优化方案有哪些？

MySQL 数据库常见的优化手段分为三个层面：SQL 和索引优化、数据库结构优化、系统硬件优化。

一、SQL 和索引优化

此优化方案指的是通过优化 SQL 语句以及索引来提高 MySQL 数据库的运行效率

SQL优化

(1) 查询具体的字段而非全部字段

任何地方都不要使用select * from t，而是查询需要的字段，这样可以提升速度，以及减少网络传输的带宽压力。

(2) 优化子查询

尽量使用 Join 语句来替代子查询，因为子查询是嵌套查询，而嵌套查询会新创建一张临时表，而临时表的创建与销毁会占用一定的系统资源以及花费一定的时间，但 Join 语句并不会创建临时表，因此性能会更高。

(3)尽量使用小表驱动大表(注意查询结果集)

我们要尽量使用小表驱动大表的方式进行查询，也就是如果 B 表的数据小于 A 表的数据，那执行的顺序就是先查 B 表再查 A 表，具体查询语句如下：

select name from A where A.id in (select id from B);

select name from A exists (select name from B where B.Id = A.id);

内表数据大的使用exists，外表数据大的使用in;

exists表示存在，是对外表做loop循环，每次loop循环再对内表(子查询)进行查询，那么因为对内表的查询使用的索引(内表效率高，故可用大表)，而外表有多大都需要遍历，不可避免(尽量用小表)，故内表大的使用exists，可加快效率；

in是把外表和内表做hash连接，先查询内表，再把内表结果与外表匹配，对外表使用索引(外表效率高，可用大表)，而内表多大都需要查询，不可避免，故外表大的使用in，可加快效率。

(4) 适当增加冗余字段

增加冗余字段可以减少大量的连表查询，因为多张表的连表查询性能很低，所有可以适当的增加冗余字段，以减少多张表的关联查询，这是以空间换时间的优化策略。

(5) 当只要一行数据时使用limit 1

查询时如果已知会得到一条数据，这种情况下加上limit 1会增加性能。因为MySQL数据库引擎会在找到一条结果停止搜索，而不是继续查询下一条是否符合标准直到所有记录查询完毕。

(6)对于连续数值，使用BETWEEN不用IN，in可能会索引失效

使用in时，当IN的取值范围较大时可能会导致索引失效，走全表扫描

失效

(7) 排查慢SQL

如何排查慢查询？

慢查询：超过指定时间的SQL语句查询称为“慢查询”。

慢查询通常的排查手段是先使用慢查询日志功能，查询出比较慢的 SQL 语句，然后再通过 explain 来查询 SQL 语句的执行计划，最后分析并定位出问题的根源，再进行处理。

慢查询日志指的是在 MySQL 中可以通过配置来开启慢查询日志的记录功能，超过 long_query_time 值的 SQL 将会被记录在日志中。我们可以通过设置“slow_query_log=1”来开启慢查询，它的开启方式有两种：

(1) 通过 MySQL 命令行的模式进行开启，只需要执行“set global slow_query_log=1”即可，然而这种配置模式再重启 MySQL 服务之后就会失效；

(2) 另一种方式可通过修改 MySQL 配置文件的方式进行开启，我们需要配置 my.cnf 中的“slow_query_log=1”即可，并且可以通过设置“slow_query_log_file=/tmp/mysql_slow.log”来配置慢查询日志的存储目录，但这种方式配置完成之后需要重启 MySQL 服务器才可生效。

需要注意的是，在开启慢日志功能之后，会对 MySQL 的性能造成一定的影响，因此在生产环境中要慎用此功能。

索引优化(避免索引失效)

首先考虑索引加在哪些字段上，然后选择合适的索引类型(普通索引，唯一索引，主键索引，联合索引，全文索引)，最后避免索引失效。

补充：索引的知识可以查看我的另一篇博文MySQL -- 索引

(1) 对于使用like的查询，查询如果是’%a'不会使用到索引 ,而 like 'a%'就会用到索引。最前面不能使用%和_这样的变化值;

(2)应尽量避免在 where 子句中使用 != 或 < > 操作符，为这些操作符会导致查询引擎放弃索引而进行全表扫描。 优化方法： key<>0 改为 key>0 or key<0。

(3) 在索引列上避免使用 IS NULL 或 IS NOT NULL操作，索引是不索引空值的。

补充解释:

Null值不存储在索引中，因此在索引列上带Is null 条件的查询不会使用索引，而是使用Table Access Full (全表扫描)操作解析查询语句。

如果在索引列上改条件为 Is Not Null ，因为索引列的所有非空值都存储在索引中，按道理也是可以走索引的。但是，为了解析查询语句，优化程序需要从索引中读取每一个值，在映射到表中索引返回的行。

在大多数情况下，执行全表扫描比为索引返回的所有值执行索引扫描(相关的Table Access By Index Rowid操作)效率更高。

例如：数字类型，判断大于0(a is not null改为a>0)，字符串类型设置一个默认值，判断是否等于默认值即可。

(4) 不要在列上进行运算操作

不要在列字段上进行算术运算或其他表达式运算，否则可能会导致查询引擎无法正确使用索引，从而影响了查询的效率。

(5) 字符串不加单引号索引失效;

说明:加了单引号后mysql会在底层对其进行隐式的类型转换(可能会自动转换为int型，使索引无效，产生全表扫描)。

(6)组合索引，如果没有使用第一列索引，索引失效；

(7) 在 MySQL 5.0 之前的版本应尽量避免在 where 子句中使用 or 来连接条件,因为当or语句前后没有同时使用索引，该索引失效而进行全表扫描，只有当or左右查询字段均为索引时，才会生效；

可以这样查询，将 or 用 union all 来替换：

select id from t where num=10 union all select id from t where num=20；

说明:在 MySQL 5.0 之前的版本要尽量避免使用 or 查询，可以使用 union 或者子查询来替代，因为早期的 MySQL 版本使用 or 查询可能会导致索引失效，在 MySQL 5.0 之后的版本中引入了索引合并，简单来说就是把多条件查询，比如 or 或 and 查询的结果集进行合并交集或并集的功能，因此就不会导致索引失效的问题了。

二、数据库结构优化

① 表的字段长度尽可能小

一般说来数据库的表越小，那么它的查询速度就越快，因此为了提高表的效率，应该将表的字段设置的尽可能小，比如身份证号，可以设置为 char(18) 就不要设置为 varchar(18)。

② 使用最简单数据类型

能使用 int 类型就不要使用 varchar 类型，因为 int 类型比 varchar 类型的查询效率更高。

③ 尽量少定义 text 类型

text 类型的查询效率很低，如果必须要使用 text 定义字段，可以把此字段分离成子表，需要查询此字段时使用联合查询，这样可以提高主表的查询效率。

④ 适当分表、分库策略

分表和分库方案也是我们经常说的垂直分隔(分表)和水平分隔(分库)。

分表是指当一张表中的字段更多时，可以尝试将一张大表拆分为多张子表，把使用比较高频的主信息放入主表中，其他的放入子表，这样我们大部分查询只需要查询字段更少的主表就可以完成了，从而有效的提高了查询的效率。

分库是指将一个数据库分为多个数据库。比如我们把一个数据库拆分为了多个数据库，一个主数据库用于写入和修改数据，其他的用于同步主数据并提供给客户端查询，这样就把一个库的读和写的压力，分摊给了多个库，从而提高了数据库整体的运行效率。

三、系统硬件优化

MySQL 对硬件的要求主要体现在三个方面：磁盘、网络和内存。

① 磁盘

磁盘应该尽量使用有高性能读写能力的磁盘，比如固态硬盘，这样就可以减少 I/O 运行的时间，从而提高了 MySQL 整体的运行效率。

磁盘也可以尽量使用多个小磁盘而不是一个大磁盘，因为磁盘的转速是固定的，有多个小磁盘就相当于拥有多个并行运行的磁盘一样。

② 网络

保证网络带宽的通畅(低延迟)以及够大的网络带宽是 MySQL 正常运行的基本条件，如果条件允许的话也可以设置多个网卡，以提高网络高峰期 MySQL 服务器的运行效率。

③ 内存

MySQL 服务器的内存越大，那么存储和缓存的信息也就越多，而内存的性能是非常高的，从而提高了整个 MySQL 的运行效率。

实战：之前在WKD项目中，用户中心的服务器是2核4G的，多条件查询时，数据量一大，就内存溢出了，因为只是简单的主表和明细表关联，同时页面查询条件太多，无法分成单表查询，索引也加了，已经无法优化了着，最终采取了将服务器配置提高到了4核8G，然后基本就不会出现内存溢出的问题了。

常见面试题

(1) MySQL 的优化方案有哪些？

(2) 如何排查慢查询？

(3) 哪些情况会索引失效？

参考:

索引不起作用

SQL优化——索引

拉钩教育 -- 性能优化 --