mysql计算字段优化_MySQL--性能优化⽅案
性能优化(Optimize)指的是在保证系统正确性的前提下，能够更快速响应请求的⼀种⼿段。⽽且有些性能问题，⽐如慢查询等，如果积累到⼀定的程度或者是遇到急速上升的并发请求之后，会导致严重的后果，轻则造成服务繁忙，重则导致应⽤不可⽤。它对我们来说就像⼀颗即将被引爆的定时⼀样，时刻威胁着我们。因此在上线项⽬之前需要严格的把关，以确保 MySQL 能够以最优的状态进⾏运⾏。
MySQL 的优化⽅案有哪些？
MySQL 数据库常见的优化⼿段分为三个层⾯：SQL 和索引优化、数据库结构优化、系统硬件优化。
⼀、SQL 和索引优化
此优化⽅案指的是通过优化 SQL 语句以及索引来提⾼ MySQL 数据库的运⾏效率
SQL优化
(1) 查询具体的字段⽽⾮全部字段
任何地⽅都不要使⽤select * from t，⽽是查询需要的字段，这样可以提升速度，以及减少⽹络传输的带宽压⼒。
(2) 优化⼦查询
尽量使⽤ Join 语句来替代⼦查询，因为⼦查询是嵌套查询，⽽嵌套查询会新创建⼀张临时表，⽽临时表的创建与销毁会占⽤⼀定的系统资源以及花费⼀定的时间，但 Join 语句并不会创建临时表，因此性能会更⾼。
(3)尽量使⽤⼩表驱动⼤表(注意查询结果集)
我们要尽量使⽤⼩表驱动⼤表的⽅式进⾏查询，也就是如果 B 表的数据⼩于 A 表的数据，那执⾏的顺序就是先查 B 表再查 A 表，具体查询语句如下：紫龙天布袋戏23pili
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，可加快效率；
mysql面试题sql
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 配置⽂件的⽅式进⾏开启，我们需要配置 myf 中的“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 类型
java接口编写
text 类型的查询效率很低，如果必须要使⽤ text 定义字段，可以把此字段分离成⼦表，需要查询此字段时使⽤联合查询，这样可以提⾼主表的查询效率。
④ 适当分表、分库策略
分表和分库⽅案也是我们经常说的垂直分隔(分表)和⽔平分隔(分库)。
分表是指当⼀张表中的字段更多时，可以尝试将⼀张⼤表拆分为多张⼦表，把使⽤⽐较⾼频的主信息放⼊主表中，其他的放⼊⼦表，这样我们⼤部分查询只需要查询字段更少的主表就可以完成了，从⽽有效的提⾼了查询的效率。
分库是指将⼀个数据库分为多个数据库。⽐如我们把⼀个数据库拆分为了多个数据库，⼀个主数据库⽤于写⼊和修改数据，其他的⽤于同步主数据并提供给客户端查询，这样就把⼀个库的读和写的压⼒，分摊给了多个库，从⽽提⾼了数据库整体的运⾏效率。
三、系统硬件优化
MySQL 对硬件的要求主要体现在三个⽅⾯：磁盘、⽹络和内存。
① 磁盘
磁盘应该尽量使⽤有⾼性能读写能⼒的磁盘，⽐如固态硬盘，这样就可以减少 I/O 运⾏的时间，从⽽提⾼了 MySQL 整体的运⾏效率。
磁盘也可以尽量使⽤多个⼩磁盘⽽不是⼀个⼤磁盘，因为磁盘的转速是固定的，有多个⼩磁盘就相当于拥有多个并⾏运⾏的磁盘⼀样。
② ⽹络
保证⽹络带宽的通畅(低延迟)以及够⼤的⽹络带宽是 MySQL 正常运⾏的基本条件，如果条件允许的话也可以设置多个⽹卡，以提⾼⽹络⾼峰期 MySQL 服务器的运⾏效率。
③ 内存
MySQL 服务器的内存越⼤，那么存储和缓存的信息也就越多，⽽内存的性能是⾮常⾼的，从⽽提⾼了整个 MySQL 的运⾏效率。
实战：之前在WKD项⽬中，⽤户中⼼的服务器是2核4G的，多条件查询时，数据量⼀⼤，就内存溢出了，因为只是简单的主表和明细表关联，同时页⾯查询条件太多，⽆法分成单表查询，索引也加了，已经⽆法优化了着，最终采取了将服务器配置提⾼到了4核8G，然后基本就不会出现内存溢出的问题了。
常见⾯试题
(1) MySQL 的优化⽅案有哪些？
(2) 如何排查慢查询？
汇编部是什么(3) 哪些情况会索引失效？
参考:
索引不起作⽤
SQL优化——索引
拉钩教育 -- 性能优化 --

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。