@TOC sql优化

从网上的答案提出疑问到做出假设

不管假设有没有完全，能让我理解并记住就可以了。每个人的记忆宫殿不一样。

网上查到的优化方法

1.把返回的时候把*号改成对应字段，联表查询的时候字段应该添加前缀减少判断，不要写没必要返回的字段。
2.添加索引，通过索引作为查询条件，同事注意不要以任何形式改变索引字段，改变后就不再是索引，比如函数计算等。
3.杜绝不必要的联表查询。
4.避免语句中出现子查询作为判断条件，应该用left join等方式构成判断添加。
5.合理使用临时表（减少对大表的查询和多联表查询，单线程使用select into比较快，但是会使用锁堵塞其他通道，多线程使用先建表再插数据的方式）。
6.In后面的集合把出现频率高的放前面啊，尽量允许返回值重复，避免判断。
7.让解析器先处理小表关系，减少大表的查询数量。
8.避免数据转换。
9.避免同一条件的同类语句多次执行，一次性插入所有字段，而不是一个个字段插入。

提出问题

1.*号改成具体字段到底影响了哪里？为什么会达到优化的效果？
2.索引是什么机制，为什么总在说用索引优化？
4.子查询是怎么影响查询速度的？
5.为什么先查小表？

理解sql执行的原理

网上有很多答案了，这里只简单说一下：客户端传来sql语句，服务器首先在高速缓存区查找是否有能用的结果，有的话校验完客户端的访问权限直接可以返回数据（最快），如果没有对应的数据，服务器就要重新解析sql语句，最终生成一棵树，然后数据库会有个优化机制改造这棵树，试图让语句执行得更顺利。最后再由折行计划折行这棵树，在数据库缓存区如果有数据，就直接使用了，如果没有就要启动innodb等引擎去文件里面取数据。并在折行计划中完成日志，事务的动作，返回数据到客户端，并缓存在高速缓冲区。（文字描述很难看懂的，建议搞张图。地址：http://dl2.iteye.com/upload/attachment/0102/8513/b643e438-7da3-3127-a6bc-bdd3b57b7e70.png）

缩小目标范围

sql优化的效果一定是出现在sql解析到折行计划这几部里面，那么这几部数据库到底做了什么？按照执行原理来说，sql优化一定是对树的优化，不行你看数据库自带的优化算法，也是生成树后优化树。那么树是什么？

理解指令树

给个网站：https://images2017.cnblogs.com/blog/285763/201708/285763-20170831141810062-1742853233.png
从树上可以看出，原来执行是一个个条件处理，最后再输出结果的。嗯……那如果我是机器，我会怎么去处理这些条件呢？

做出假设

字典

就和solr一样，mysql应该也有它的字典，字典就好像是一本书的目录，你要找什么内容，从目录里面找到范围，直接翻开就能找到了。那么mysql的字典包含了什么内容呢？假设是是库名、表名、表的字段、索引这些内容。

执行过程

先来一段网上找到的语句折行顺序：
SQL语句中的函数、关键字、排序等执行顺序:

1. FROM 子句返回初始结果集。
2. WHERE 子句排除不满足搜索条件的行。
3. GROUP BY 子句将选定的行收集到 GROUP BY 子句中各个唯一值的组中。
4. 选择列表中指定的聚合函数可以计算各组的汇总值。
5. 此外,HAVING 子句排除不满足搜索条件的行。
6. 计算所有的表达式;
7. 使用 order by 对结果集进行排序。
8. 查找你要搜索的字段。

重点假设来了

执行是第一步，找到你需要的数据所在的位置；第二步根据你找到的位置去提取你要的数据，第三部把数据按照sql语句要求的返回格式返回。

那么一开始执行，执行的是fron后面的字句，from后面一般是表名称吧！那就是说，折行from后面的语句时，会打开这个表。然后折行where语句后面的条件，这时候就会根据条件，查询对应的列，每个条件去和指定列的数据逐一对比，成立后就返回改数据所在行。就等于给了个坐标集合，每个元素就告诉你了，（哪个库，哪个表，哪行）的数据是你想要的。
如果没有再复杂的查询了，这时候程序就会根据sql语句中返回需求，将需要返回的类号加到坐标集合中（哪个库，哪个表，哪行，哪列），形成一个输出表样式，输出表样式根据坐标，从表中提取对应位置的数据，形成输出表输出，完成查询。
那如果还有复杂的条件比如join on，group by，having等，则会根据条件所给的条件，先从where条件的结果集合中，找到对应group by对应的条件列的值，再打开其他需要的表，通过上一步得到的条件值去和其他表指定的列逐一对比，得到新的坐标集（哪个库，哪个表，哪行），以此类推，知道完成最后的条件，生成了最终的坐标集。这时候程序就会根据sql语句中返回需求，将需要返回的类号加到坐标集合中（哪个库，哪个表，哪行，哪列），形成一个输出表样式，输出表样式根据坐标，从表中提取对应位置的数据，形成输出表输出，完成查询。

假设完成！

逆向思维问下我的假设是否合理

如果有索引，那么在生成第一步坐标集合（哪个库，哪个表，哪行）的时候，甚至都不用打开表就能完成了，应为索引是在字典里面的。都知道，表其实就是一个文件，我们平时打开个大文件消耗的时间是很明显的呀！还有资料说如果没有直接在条件里面指明列，系统会优先从索引列先完成条件查询。这就证明了索引的查询执行就和打开文件这个步骤应该是无关的，另外一个线程。那么就是所，打开文件过程和查询过程是同步的，那么节省了不少的时间啦。（之前是打开文件再查询，时间是相加关系，现在是取最大的关系）。
那如果是没有索引，而且没有指定列的条件（比如模糊查询），每个条件都要在打开文件后和表中每个数据进行对比，这样消耗的时间不是一般地多了！

同样的，如果没有指定返回的对应列，或者没有明确指明的时候，那么执行第二步生成坐标集合（哪个库，哪个表，哪行，哪列）时，就会多很多的计算，甚至要把更多的内容加载到输出表中，增加了运行时间。（所以说要指明返回的列，而且尽量不要写没必要的返回列）

解答之前提出的问题

1.*号改成具体字段到底影响了哪里？为什么会达到优化的效果？
上一段“逆向思维问下我的假设是否合理”已说明

2.索引是什么机制，为什么总在说用索引优化？
上一段“逆向思维问下我的假设是否合理”已说明

4.子查询是怎么影响查询速度的？
等于都几个sql语句，那么要在解析优化的时候多做一些工作。如果子查询没有默认的索引，又不能通过主查询形成指定列的关系，子查询的查询时间就会很长。（如果子查询有索引的时候，可以根据情况使用子查询，启用子查询的索引）

5.为什么先查小表？
这里说的先查小表代表的是from后面先被打开的表用小表或中间表，小表先打开的说法是网上大多数博客这样写的，从我的假设中也不能解决这个问题。还有说法说现在的数据库已经解决了这方面的问题，因此，现在的表顺序已经不影响速度了。但是还有一种我比较认同的说法，在数据表过大的时候，不能完全加载在内存中（内存小），如果大表在前，就要不断打开大表从中获取数据然后再打开小表，进行对比。重复了很多工作。如果小表在前，小表就会被缓存在内存中，再拿去对比就会快很多。所以这种说法就是说大表要很大才算大表，毕竟服务器的内存怎么也8G以上吧。

最后

以上就是迅速网络为你收集整理的sql优化的理解从网上的答案提出疑问到做出假设的全部内容，希望文章能够帮你解决sql优化的理解从网上的答案提出疑问到做出假设所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。