MySQL--性能优化方案
原文链接:https://www.cnblogs.com/liaowenhui/p/12318812.html
性能优化(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) 哪些情况会索引失效?
参考:
索引不起作用