一条mysql查询语句的畅谈

程序员做个三五年就算是中级或高级了，前后端以及服务器都能应付，独立开发个 项目也 是没问题的。可是一旦涉及了底层优化，拓展等等的就不灵了。这也许就是传说中的瓶颈，遇到了天花板了（可能只是碰到吊着的电灯泡）。说到原因，每个公司的 架构不尽相同，项目都是用了几年甚至十几年的框架了，没有哪个项目经理会有魄力推陈出新，用新的东西去替换掉。因为这会引起很多的连锁问题，哪怕只是升级下php版本，jquery版本，bootstrap版本，更别说换UI,换框架了，所以在同一个公司呆久了，掌握的还是那些东西，无非就是更熟练了。想要获取更多提升，必须要靠自己苦心钻研。

最近我在看mysql 方面的知识，怎么使用内置函数，怎么查询，配置字段这些东西就不提了，没啥意思；至于乐观锁，悲观锁行锁，数据库引擎什么的又有点太深，主要是我真得不知道这些东西在什么应用场景下会需要自己配置。现阶段我能研究的也就是优化查询了，比如如何使用索引。下面就关于查询语句，我谈谈最近学到的知识点。并且，我很负责的说，下面写到的都是经过实践得出结论，绝不仅仅停留在理论层面。

一，MySQL 基础架构分析

1. 连接器 作为身份认证
2. 查询缓存 执行查询语句时，先查询缓存（新版本已弃用）
3. 分析器 如果没有缓存，则分析sql语句的目的，并且判断是否有语法错误
4. 优化器 选择mysql最优化的方案
5. 执行器 执行语句，并通过存储引擎返回数据

上面说的这几条就是mysql执行语句的几个大步，没啥特殊意义。值得玩味的就是第4条，优化器。mysql在执行语句前会以它认为的最优方案来执行。所以咱们想要写一条ok的语句，除了语法正确外，得搞清楚mysql是怎么想问题的。比如说，select name,age from member where age>18 and sex=2 limit 10 这条语句从执行到返回中间发生了什么。

咱们现场还原一下这条语句，某学校参加全国歌舞比赛，学校要挑选10名18岁以上的男生来参加。那么接下来要怎么选呢，我暂且提供3种方案，第一种，全学校18岁以上的学生站出来，然后其中的所有男生再站出来，然后再从左到右数出来10个学生，最后再得到这10名学生的姓名；第二种，全学校18岁以上的男生全站出来，从左到右数出来10个学生再取姓名；第三种，全学校学生，从左到右一个一个查身份，如果18岁以上的男生就登记一个，直到凑够10个人。如此种种，可以有更多种方法，那么哪种更好呢，这里先按下不说。咱们先说说mysql默认会怎么查询。

二、sql查询语句处理步骤

1. 从from开始 选择要查询的表

1. Where 阶段 筛选条件
2. Group by 分组
3. Having 筛选
4. Select 挑选需要的字段
5. Ordery by 排序返回数据

上图来自网络（https://blog.csdn.net/myt0929/article/details/80263596），

通过上面的解释，很容易明白一条查询语句的执行过程了，拿上面的举例来说。Mysql默认会以第三种方案来执行数据库查询。也就是说，如果正好最前面的10名学生就满足条件，那么只需要核对10名学生就得到结果了。换言之，如果直到最后都没有符合条件的，或者正好10名符合条件的学生都排在最后面了，那么想要取得这10名学生，就要把全校都核对一遍。难道不能把这些符合条件的学生做个标记吗，类似于学校平时建立一个档案，给全校18岁的学生组织一个精英小组，给所有男生组织一个英雄小组；更有甚之给18岁的男生组织一个明日之星少年团。现在需要10名18岁以上的男生，直接从之个明日之星少年团选10名学生不就Ok了。事实上，这种想法是没问题，mysql也是这样做的，前提是你要建立数据索引。

三、 为什么索引能提高查询速度

建立索引就是给一本书列目录，每加一个索引就相当于多列一种目录。还拿前面的做例子。学校肯定会给学生建立档案，一般是按年级，班级这样划分的。如果学校要找的10名学生不是18岁以上的男生，而是高二班，16A班的10名学生，肯定就会容易的多，因为在档案册里翻到这个班级的学生信息就行了。同样的道理，如果学校再建一个类似的档案，不以班级划分，而是以年龄和性别来区分。那么找到10名18岁的男生岂不是很简单。但是建索引也不是随便就建的，至少不是越多越好。试想 一下，学校为了便于查询，按年龄，按身高，按血型，按颜值等建了无数个档案，不仅维护起来相当烦锁，每次查询还要翻好几本册子也是复杂，毕竟大多数情况学校只是查找某个班级的某个人名。所以呢，建立索引的规范也要说一下下。

1. 单张表的索引不超过5个
2. 不要给每个字段都建立单独索引
3. 一般需要建立索引的字段包括 where,order by,group by,distinct相关的字段
4. 不要给一个字段建立多种索引

四，关于limit与优化mysql的探讨

1，limit 直的能减少查询压力吗

在web开发中，涉及列表分页时，有两种方案，一种是一次性取到所有数据，再切割成若干页；另一种则是根据当前页码多次获取需要的数据。通常我们会选择后者，因为一次性取出所有数据，肯定会在数据传输上略逊一筹。那么随着网络加速，5g或者更快的通信技术革新这个理由可能不再重要。我们更多考虑的是mysql查询压力，

通过上面图片看出，使用了Limit 直观上看并没有减少mysql遍历行数，但是从响应时间上明显是少很多。所以我个人认为mysql在获取足够条数时，是不会继续遍历的。至于为什么explain给的遍历条数不符合推测，我也不明白，希望有大神给出解释。

1. Limit 0,100与limit 10000,100的区别

从上面两个图可以明显看出，limit 10000,100比limit 0,100要慢很多，也就是说limit 10000,100并不是像前面说到的书目一样直接翻到指定位置，然后数出100条数据就行了，而是从第一条开始往后数出10100条数据，再取出最后100条。这就说明，在开发中做了分页，越是后面的页码响应速度会慢。

1. 怎么优化limit 10000,100

是不是想要从指定节点往后取指定条数，就必须从数据的起点开始遍历呢。当然不是，请看下图。

从上面看得出，在where加入了主键的限制条件，mysql的遍历行数减少了，也就是说它是从指定位置开始查询的。并且从响应时间上看并没有增加。所以根据这个思路，可以把limit 10000,100这种格式转换为主键限制条件加到where里面

Select * from member where user_id>(select user_id from member order by user_id limit 10000,1) limit 100

好了，关于mysql的查询今天就到这儿。欢迎提出质疑和讨论，共同进步。