小白篇(五)：Mysql数据库进阶章-查询优化

 

获取原文

 

简短介绍

---

大家好，先祝大家双节快乐。这是今年最后一个小长假了。小伙伴们可以好好玩玩。后面的日子可不轻松了哦，没有长假了，只能连续上班搞事情。但是玩归玩，学习的时间还是要抽一点点出来。成长不是上班的那8个小时，而是下班后的时间决定的。所以看自己吧。好吧，反正我放假这些天不更新了。当然我得考虑写书的事情^_^。言归正传，今天给小伙伴们讲讲Mysql进阶内容，助小伙伴们走向高薪之路。

 

01-Mysql运行顺序

 

在我们使用工具时，要怎么才能玩得6呢？那就是按照它的规矩来，不去主动触发它的BUG，照它的流程正常走，那不就很顺畅么！查询优化也是类似的道理。那我们先要了解它的流程。也是就是它的执行顺序。当我们知晓执行顺序后，再知晓些原理，就可以编写出它想要的样子，就实现了优化的作用。

• Mysql语句执行顺序如下：

(1) from 查表

(3) join 关联表

(2) on 关联值

(4) where 筛选值

(5) group by 分组统计

(6) avg,sum.... 函数统计

(7) having 统计值过滤

(8) select 查询所需字段

(9) distinct 去除值

(10) order by 排序

(11) limit 获取指定条数

 

• Mysql查询运行原理

小伙伴们可以从下图，了解到实际底层运行原理。我们通过一定的手段，优化其中某些节点位置。从而更高效的读取数据。

Mysql查询流程图

 

 

02-优化方向

 

如果最终的目的是更快的读取到数据。那么除了从Sql查询方面去下功夫优化，我们还可以从表结构，数据存储结构上去动动手脚哦。其实在面试过程中，很多人会问到Sql优化。那么我们不妨将问题看大点。整库的查询优化。那么可回答的点就多了。

 

• 一、数据库表结构设计

先遵循三大范式

一范式：原子性、不可拆分的字段属性

二范式：主外键约束关系

三范式：字段没有冗余(相关联表字段,不要重复出现在多个表中)

合理的数据存储，可以有效的减少需要查询的表，减少关联关系。

 

 

• 二、数据库索引设计

索引可以理解为给书添加目录.有了目录,定位内容就快了。

主键索引: 

-- 表中作为唯一存在的键,值不能为空
ALTER TABLE table_name ADD PRIMARY KEY (字段);

 

唯一索引: 

-- 表中可以存在多个唯一索引字段,允许一个空值.其它值不能为空且需唯一
ALTER TABLE table_name ADD UNIQUE [索引的名字] (字段);

 

单列索引: 

-- 单列索引适合创建在值区分程度高的字段。时间和证件号这些合适。类型和性别字段不合适。因为区分程度太小了，还是会走全表扫描读取。
ALTER TABLE table_name ADD INDEX [索引的名字] (字段);

 

组合索引: 

-- 如果在where中字段1和字段2同时出现.就会采用索引查询.即字段是组合出现的.
-- 当然如果只出现字段1也是可以走上索引的.如果只出现字段2就不行了.
-- 就类似2级目录一样.
ALTER TABLE table_name ADD INDEX [索引的名字] (字段1,字段2);

 

查看表中索引

show index from table_name ;

说明: 因为索引就好比是书中的目录,可以快速定位到书中内容。所以在数据库中，是可以起到查询优化的效果。当然如果索引创建太多也不是什么好事。小伙伴们可以想想，如果书的目录比书还厚，你是什么感受呢？那查阅目录都困难，何况书中内容，可怕！

 

• 三、大表拆分(减少大表访问次数,控制IO和内容)

如果一张订单表,数据量特别大。那么查询是非常费劲的，针对它还不能有过多的访问次数，否则内存和CPU都吃不消。我们可以考虑根据订单产品分类拆成多个表。那么查询某类产品订单的时候，就可以快速查询了。当然，我们还可以根据订单时间拆成多个表。具体拆分规则，可以根据实际情况来。对某张表进行拆分存储，在查询这种表数据时就好比是并发查询。

 

• 四、表定时整理，清理水位线

在业务系统中,通常会有delete的操作。其实数据并发真实的物理删除了。就好比水缸中的水，取走一些后。水缸壁上，还是会留下原先水位的痕迹。delete删除表数据也是类似的道理。我们查询的话，还是会读取到水位线。通常的做法就是：

①  如果全表删除就用truncate，一并清理水位线。

② 手工释放水位线。create table tab2 as select tab1; 删除tab1，将tab2改名为tab1。好麻烦啊。。。试试分区表吧。

 

• 五、SQL语句调优

① 索引字段保持原汁原味(不要对索引字段做任何处理或运算)

-- date为索引字段,推荐
select 字段 from tab1 where date>='2020-10-01' and date<'2020-10-02'; -- 查询10月1号数据
-- 不推荐,索引被破坏
select 字段 from tab1 where substring(date,1,10)='2020-10-01';-- -- 查询10月1号数据

② union all 代替 or(增加sql代码量,换来高效运算率.有舍有得)

-- 推荐
select 字段 from tab1 where name ='a'
union all
select 字段 from tab1 where name ='b'
-- 不推荐
select 字段 from tab1 where name = 'a' or name = 'b'

③ 在数据不重复的情况下，用union all 代替union

④ 尽量减少不必要的排序

⑤ 利用limit 1取得唯一行

select 字段 from tab1 limit 1; -- 确认返回只有1条数据情况下

⑥ 避免Select * 命令,精准查询需要的字段.减少数据对内存的占用

-- 推荐
select 字段 from tab1
-- 不推荐,如果有大字段,哭去吧
select * from tab1

⑦ 尽量用上索引,加快查询性能.(索引不要盲目创建)

⑧ 减少表的访问次数,减少IO读写.(如果大表被重复使用,可处理成临时的小表再查询)

⑨ 多练习，多测试，多验证

 

03-小结

想要快速的查询并获取到数据。优化是必不可少的。当然对数据查询的优化能力，也是慢慢积累的经验。小伙伴们可以在平时编写Sql时多思考下如何提升性能，那么成为优化大师也不会太远了。好吧，Mysql就先告一段落了。后续给大家带来大数据Hive查询使用分享。希望小伙伴们会喜欢。得回家过节了。拜！！