MySQL优化查询4：24种查询优化的具体方法 | 数据分析学习历程全记录

发布时间：2022-09-13 16:22:32 所属栏目：教程来源：

导读：　　本文包括：

　　SQL优化简介具体的优化技巧其它有关数据分析，机器学习的文章及社群SQL优化简介：

　　在数据库优化上有两个主要方面：即安全与性能。

　　因为数据分析师主要使用SQL进行查询，因此

　　本文包括：

　　SQL优化简介具体的优化技巧其它有关数据分析，机器学习的文章及社群SQL优化简介：

　　在数据库优化上有两个主要方面：即安全与性能。

　　因为数据分析师主要使用SQL进行查询，因此与安全方面的关系不大，主要关注于查询性能方面。

　　从多个方面进行数据库优化：

　　SQL及索引；数据库表结构；系统配置；硬件。

　　一般而言，优化效果从左往右由高变低，成本从左往右由低变高，因此在SQL和索引方面的优化的性价比最高。

　　数据分析师一般关注于SQL和索引方面。

　　SQL优化遵循规则：

　　最大化利用索引；尽可能避免全表扫描；减少无效数据的查询。

　　2. 具体的优化技巧：

　　1. LIMIT

　　当 LIMIT 子句变成 “LIMIT 1000000,10” 时，即使是使用组合索引也会很慢，因为数据库不知道第1000000条记录从什么地方开始，会从第一条记录开始扫描，即使有索引也需要从头计算一次。

　　优化：

　　一般使用LIMIT的同时也会使用ORDER BY，因为相关需求一般为查询按照某一字段从大到小排列后的第几个记录，因此可以利用ORDER BY子句包含的字段中的最大值或最小值作为WHERE后的过滤条件，这样就可以将LIMIT子句改为“LIMIT 10”。

　　2. 子查询

　　将子查询重新写成Join。

　　3. 混合排序

　　所谓混合排序是指Order By后面既有Desc也有Asc。

　　优化：

　　观察数据，如果一个排序的数据的种类很少，比如只有1和0两种，可以通过先选取1对应的记录并进行排列，再选取0对应的记录进行排列，最后Union All。

　　4. 模糊查询

　　Where子句为Like ‘%abc%’会导致数据库引擎放弃索引而用全表扫描。

　　优化：

　　尽量在字段后面使用模糊查询，比如Where子句为Like ‘abc%’。

　　但如果没有办法将字段前模糊查询转换成字段后模糊查询该怎么办呢，这里提供三个搜索方法：

　　第一，使用INSTR（）函数来进行匹配。用法为INSTR（col，expr），col为字段名，expr为要查询的内容，该函数返回需要查询的内容在每一个记录中的开始位置。比如INSTR（’abcde’,’bc’）将返回2，如果找不到将返回0。根据这一特性，可以将WHERE子句改为INSTR（col，expr）>0。INSTR（）函数之所以更快是因为它走索引。

　　第二，建立全文索引，用match against检索。语法为MATCH(col1,col2,…) AGAINST (expr[search_mode])。MATCH中的字段必须已经建立全文索引，search_mode为可选搜索类型。

　　第三，在数据量较大的情况可以使用ElasticSearch。

　　5. in和not in

　　Where子句有in和not in会导致全表扫描。

　　优化：

　　如果需要查询的内容为连续型数据，可以用between代替。

　　如果是子查询，可以用exists代替。

　　6. or

　　Where子句出现or会全表扫描。

　　优化：

　　用Union代替or。

　　7. null值的判断

　　对null值进行判断会导致全表扫描。

　　优化：

　　给可能出现Null的字段添加默认值0，在查询时对0值进行判断。

　　8. Where中的表达式和函数操作

　　如果在Where子句中判断符的左侧进行表达式或函数操作，会导致全表扫描。比如： Where score/10 = 9

　　优化：

　　将表达式或函数操作移动到右侧，查询时将走索引。比如：Where score = 10*9

　　9. 或!=

　　当使用索引作为条件进行查询时，使用或者!=将导致全表扫描。

　　优化：

　　如无法避免，可重新评估在其它字段上建立索引，使用其它字段上的索引进行查询。

　　10. 复合索引

　　比如建立了复合索引（col1, col2, col3），但在查询时Where子句不包含第一列col1，则不会走索引查询（最左前列原则，详细信息可查看这篇文章：舟晓南：MySQL优化查询1：索引及其使用技巧 | 数据分析学习历程全记录）。

　　优化：

　　对于复合索引，遵循最左前列原则。

　　11. 隐式类型转换

　　MySQL会根据需要自动将数字转换为字符串，将字符串转换数字。如果SQL语句中给定的数据类型与索引的数据类型不一致，造成不使用索引。

　　优化：

　　在进行查询前，需要了解索引列的数据类型，并在SQL语句中采用相同的数据类型。

　　12. Order By子句与Where子句

　　如果Order By子句与Where子句条件不一致，Order By不会利用索引进行排序。比如：

　　SELECT * FROM t WHERE height > 100 ORDER BY age。

　　优化：

　　SELECT * FROM t WHERE age > 0 ORDER BY age，将采用索引进行排序。

　　当然对Order By的优化不止这一点，可以根据以下测试进行分析。

　　测试：采用以Date为索引的表。

　　EXPLAIN SELECT * FROM covid ORDER BY DATE;
　　#Using filesort；
　　EXPLAIN SELECT DATE FROM covid ORDER BY DATE;
　　#Using Index；
　　EXPLAIN SELECT DATE FROM covid WHERE countrycode = 'CN' ORDER BY DATE;
　　#Using where；Using filesort
　　EXPLAIN SELECT DATE FROM covid WHERE DATE > '2019-12-16' ORDER BY DATE;
　　#Using where；Using index
　　EXPLAIN SELECT country FROM covid WHERE DATE > '2019-12-16' ORDER BY DATE;
　　#Using where；Using filesort
　　EXPLAIN SELECT DATE FROM covid WHERE DATE > '2019-12-16' AND country = '中国' ORDER BY DATE;
　　#Using where；Using filesort
　　13. 正确使用 hint 优化语句

　　使用hint指定优化器在执行时选择或忽略特定的索引。

　　比如：

　　SELECT col1 FROM table USE INDEX (key1)

　　#提示优化器采用索引key1进行查询，增加使用key1索引的权重。

　　SELECT col1 FROM table IGNORE INDEX (key2)

　　#强制优化器忽略索引key2进行查询。

　　SELECT col1 FROM table FORCE INDEX (key3)

　　#强制优化器使用索引key3进行查询。

　　14. 避免出现select *

　　这就不说了。

　　15. 多表关联

　　多表关联查询时，小表在前，大表在后。

　　之所以这样，与MySQL的执行顺序有关，MySQL会先对左边的表进行全表扫描，所以先扫小表的效率更高。当MySQL开始扫描大表时，由于一些过滤条件，可能扫描到大表的一部分就符合查询条件并返回数据了。

　　16. 使用表的别名

　　当连接多个表时，使用表别名，将别名前缀于每个列名前，减少解析时间。

　　17. Where和Having

　　Having将在检索出所有记录之后才对结果集进行过滤。

　　优化：

　　将Having子句转换成Where子句mysql数据库优化教程，将省下检索后的过滤过程。

　　18. Where子句的顺序

　　MySQL采用从左往右，自上而下的顺序解析Where子句。因此可以将过滤数据多的条件放在前面，可以快速缩小结果集。

　　19. Group By的排序

　　默认情况下，MySQL会对Group By分组的所有值进行排序，相当于多了一个Order By部分。

　　优化：

　　如果不需要对分组的值进行排序，可以在后面添加Order By Null。

　　20. Union

　　当对两表进行Union时，MySQL默认会进行Distinct操作。

　　优化：

　　使用Union All。

　　21. 大事务

　　复杂的SQL不容易利用MySQL的缓存机制。

　　优化：

　　将复杂的SQL拆分为多个小SQL，尽量利用MySQL的SQUERY CACHE功能。

　　所谓MySQL的缓存机制我在这篇文章：舟晓南：MySQL的执行逻辑和表的结构 | 数据分析学习历程全记录中有提到，简单来说，就是对于简单的SQL，MySQL会将其查询语句与结果存储在缓存中，下一次相同的SQL将直接把查询结果从缓存之中释放，不需要进行扫描。

　　22. 临时表

　　当在工作中需要重复引用大表中的一部分时，可以采用临时表的方式将需要的数据进行存储mysql数据库优化教程，接着通过SQL语句查询临时表就可以了，这样可以不再扫描大表，提高查询效率。

　　需要注意的是，在任务完成后，要将临时表删除。

　　23. 函数操作

　　在Where子句中进行函数操作将导致全表扫描。

　　优化：

　　比如：

　　将select country from t Where substring(country,1,2)='Ch' 改为：

　　select country from t Where country Like 'Ch%'

　　24. 数据类型

　　在查询时过滤条件尽量使用数字型字段，这是因为对于字符型字段，在查询时会逐个比较字符串中的每一个字符，但对于数字型而言只需要比较一次就够了。

　　最后：

　　关于SQL的查询优化其实还有很多小技巧可以讲，比如窗口函数的利用可以有效提高效率等等，而窗口函数可以单独写一篇文章讲，这里先按下不表。

　　欢迎关注专栏：

　　MySQL文章：

　　舟晓南：转行数据分析师如何开始学习SQL | 工科生三个月转行数据分析学习心得

　　舟晓南：MySQL的执行逻辑和表的结构 | 数据分析学习历程全记录

　　舟晓南：MySQL优化查询1：索引及其使用技巧 | 数据分析学习历程全记录

　　舟晓南：MySQL优化查询2：如何查找低效率语句 | 数据分析学习历程全记录

　　舟晓南：MySQL优化查询3：explain和show profiles | 数据分析学习历程全记录

　　机器学习模型：

　　舟晓南：统计学习方法 - 感知机模型解读 | 数据分析，机器学习，学习历程全记录

　　舟晓南：统计学习方法 - 感知机模型的收敛性解读 | 数据分析，机器学习，学习历程全记录

　　舟晓南：统计学习方法 - k近邻模型解读 | 数据分析，机器学习，学习历程全记录

　　舟晓南：统计学习方法 - 朴素贝叶斯模型解读 | 数据分析，机器学习，学习历程全记录

　　舟晓南：统计学习方法 - 朴素贝叶斯之后验概率最大化的含义 | 数据分析，机器学习，学习历程全记录

　　关于数据分析的文章：

　　舟晓南：如何转行和学习数据分析 | 工科生三个月成功转行数据分析心得浅谈

　　舟晓南：求职数据分析师岗位，简历应该如何写？｜工科生三个月成功转行数据分析心得浅谈

　　数据分析学习社群正式启动~

　　需要学习资料，想要加入社群均可私信~

　　在这里会分享各种数据分析相关资源，技能学习技巧和经验等等~

　　详情私信，一起进步吧！

（编辑：PHP编程网 - 湛江站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

小白教你电脑无法连上	笔记本电脑开机黑屏进
详解手机进水怎么办开	系统之家官网是哪一个