方向
优化成本：硬件>系统配置>数据库表结构>SQL及索引；
优化效果：SQL及索引>数据库表结构>系统配置>硬件；
- 最大化利用索引；
- 尽可能避免全表扫描；
- 减少无效数据的查询。
遵从的原则 :::warning
- 减少数据访问：设置合理的字段类型，启用压缩，通过索引访问等减少磁盘IO；
- 返回更少的数据：只返回需要的字段和数据分页处理，减少磁盘IO和网络IO；
- 减少交互次数：批量DML操作，函数存储等减少数据连接数；
- 减少服务器CPU开销：尽量减少数据排序等操作以及全表查询，减少CPU内存占用；
利用更多资源：使用表分区，可以增加并行操作，更大限度利用CPU资源； :::
SELECT语句
语法顺序：
SELECT
- DISTINCT
（去重字段） - FROM
JOIN - ON
- WHERE
- GROUP BY
- HAVING
- ORDER BY
- LIMIT
执行顺序：
每个操作都会产生一张虚拟表，该虚拟表作为一个处理的输入。这些虚拟表对用户是透明的，只有最后一步生成的虚拟表才会返回给用户。如果没有在查询中指定某一子句，则将跳过相应的步骤。
FROM<表名> # 选取表，将多个表数据通过笛卡尔积变成一个表。
对FROM子句中的左表
ON<筛选条件> # 对笛卡尔积的虚表进行筛选。
对虚拟表VT1应用ON筛选，只有那些符合
JOIN
如果指定了OUTER JOIN （如LEFT OUTER JOIN、RIGHT OUTER JOIN），那么保留表中未匹配的行作为外部行添加到虚拟表VT2中，产生虚拟表VT3。如果FROM子句包含两个以上表，则对上一个连接生成的结果表VT3和下一个表重复执行步骤1-步骤3，直到处理完所有的表为止。
WHERE
对虚拟表VT3应用WHERE过滤条件，只有符合
GROUP BY<分组条件> # 分组
根据GROUP BY子句中的列，对VT4中的记录进行分组操作，产生VT5；
HAVING<分组筛选> # 对分组后的结果进行聚合筛选；
对虚拟表VT5进行应用having过滤器，只有符合
SELECT<返回数据列表> # 返回的单列必须在group by子句中，聚合函数除外
第二次执行SELECT操作，选择指定的列，插入到虚拟表VT7中。
DISTINCT数据除重
去除重复数据，产生虚拟表VT8；
ORDER BY<排序条件> # 排序
将虚拟表VT8中的记录按照
LIMIT<行数限制>
取出指定行的记录，产生虚拟表VT9，返回给查询用户。
慢查询性能优化
查询的生命周期的过程一般是这样的：从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行，最后返回结果给客户端。其中，执行是整个生命周期最重要的阶段，包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组等。
衡量查询开销的三个指标：
- 响应时间；
- 服务时间和排队时间的总和。也就是处理查询的时间加上服务器需要等待某些资源的时间。
- 扫描的行数；
- 返回的行数；
一般来说扫描的行数应该和返回的行数相同，但是实际中会扫描多行而产生少量的返回结果集。
数据访问
查询性能低下最基本的原因是访问的数据太多。某些查询可能不可避免需要筛选大量的数据。大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。对于低效的查询要进行具体分析：
- 确认是否在检索大量超过需要的数据；
- 确认MySQL服务器层是否在分析大量超过需要的数据行。
- 数据库请求了不需要的数据，这时候应该去掉不需要的数据【如去掉返回不需要的列或者是行】；
MySQL扫描了额外的记录，会产生额外的开销。一般需要建立合适的索引来优化查询。
重构查询方式
可以考虑把复杂的查询分成简单的查询；
- 切分查询，把大查询切分成小查询；
-
优化策略
避免不走索引
尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引，进行全表扫描；
- 尽量避免使用in和not in，会导致引擎走全表扫描；
- 一般使用between或者是exists代替；
- 尽量避免使用or，会导致数据库引擎放弃索引进行全表扫描；
- 使用union代替or；
- 尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描；
- 给字段添加默认值0，对0值进行判断；
- 尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描；
- 可以将表达式、函数操作移动到等号的右侧；
- 当数据量大的时候，避免使用where 1=1条件。数据库引擎会放弃索引进行全表扫描。
- 在代码封装的时候，使用
标签，在没有where条件的时候去掉where，有where条件的时候就加and；
- 在代码封装的时候，使用
- 查询条件不能用<>或者是！=；
- 避免在需要使用不等于符合进行查询的字段建立索引。
- where条件包含复合索引非前置列；
- 不符合最左匹配原则，则不会走索引。
- 隐式类型转换造成不使用索引；
- 索引列类型为varchar，但是赋值是数值，涉及到了隐式类型转换，造成索引失效；
order by条件要与where中条件一致，否则order by不会利用索引进行排序。
语句优化
避免出现 select * 情况；
- 使用select * 取出全部列，会让优化器无法完成索引覆盖扫描，会影响优化器对执行计划的选择，会增加网络带宽消耗，带来额外的I/O，内存和CPU消耗；
- 避免出现不确定结果的函数；
- 不确定值的函数，产生的sql语句无法利用query cache；
- 多表关联查询时，小表在前，大表在后；
- mysql中，执行form后的表关联查询是从左往右执行，第一张表会涉及到全表扫描，所以将小表放在前面。先扫描小表，扫描快效率较高，在扫描后面的大表，或许只扫描大表的前100行，就符合返回条件，然后返回结果。
- 使用表的别名
- 当连表查询是多表的时候，使用表的别名，减少解析的时间。
- 使用where字句替换having字句；
- having字句是在查询记录返回之后，对结果集进行过滤，但是where是在聚合前进行刷新记录。如果能通过where字句限制记录的数目，可以减少开销。having的条件一般用于聚合函数的过滤，除此之外，应该要将条件写在where字句中；
- where后面不能使用组函数；
调整where字句中的连接顺序；
对于复杂的查询，可以使用中间临时表暂存数据；
- 优化group by语句
- MySQL会对groupby分组的所有值进行排序，但是如果不需要进行排序，可使用order by null，禁止排序；
- 优化join语句
- 有些情况下，使用join连接代替子查询，会提高效率；不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。
- 优化union查询
- MySQL通过创建并填充临时表的方式来执行union查询，除非是确认要消除重复的行，否则建议使用union all。因为如果没有all关键字，mysql会给临时表加上distinct选项，这会导致对整个临时表的数据做唯一性校验，消耗很大。
- 拆分复杂sql为多个小sql，避免大事务；
- 简单sql容易使用到mysql的query cache；
- 减少锁表时间，特别是使用MyISAM存储引擎的表；
- 可以使用多个CPU；
- 使用truncate代替delete
- 在删除全表中记录时，使用delete语句的操作会被记录到undo块中，删除记录也会记录binlog。当确认需要删除全表时，会产生大量的binlog，并占用大量的undo数据库，效率不高又占用大量的资源；
- 使用truncate替代，不会记录可恢复的信息，数据不能被恢复。操作很少的资源和极短的时间，提高效率又不浪费资源。另外，使用truncate可以回收表的水位，使自增字段值归零。
-
建表优化
在表中建立索引，优先考虑where、order by使用到的字段；
- 尽量使用数据性字段【性别，1：男；2：女】。能使用数值信息尽量不适用字符类型，否则会降低查询和连接性能，并会增加存储开销。因为在引擎中在处理查询和连接的时候，会逐个比较字符串中每一个字符，对于数值型来说只需要比较一次就好。
- 查询数据量大的表，会造成查询缓慢。主要是因为扫描的行数过多。这个时候可以通过程序，分段分页进行查询，循环遍历，将结果进行合并处理再展示。
- 用varchar、nvarchar代替char、nchar
- 变长字段存储空间小，可以节省存储空间。对于查询来说，在一个相对较小的字段内搜索效率显然要高些；
- null在char类型下，会占用空间【占用类型长度大小的空间】，但是在varchar变长字段类型中，null不占用空间。
若有收获，就点个赞吧
0 人点赞
