近一年来断断续续地读了两本书,但一直都没做梳理,读的时候感觉是那么回事,读完之后感觉除了开了点眼界什么都没留下。读技术的书不同读小说和文学作品,浅尝辄止无异于什么都不懂,所以这次看这本网易出的mysql技术内幕的时候,我给自己定了个目标,每章必做总结,以公众号发布来做督促。不巧的是上上周在赶一个项目,一不小心搞了个997,上周双休,周六炖了只鸡,周日包了顿饺子,一晃两天又过去了,恍然间两周过去了,读书的进度其实倒没落下,只是没有总结,这种状态让我感觉很不好。收拾心态,决定把两章内容融为一体,写一遍深究逻辑、落实应用的文章。

工欲善其事,必先利其器。要想使用好数据库,数据库的数据类型你是有必要了解的,很多简单的细节也能给数据库带来不少优化。此处不做过多展开,老实讲通过一篇文章你不可能get这个技能,只是给你一个方向。

我们大多数对数据库的优化都是对查询的优化,那么如果你想要优化好一个SQL,必须要对其执行流程清楚。Mysql的查询可以拆分为逻辑查询和物理查询,通俗来讲就是理论查询(逻辑)和实际查询(物理)。所谓逻辑查询就是你写了一条查询SQL语句,他的逻辑执行流程,而物理查询是真正走的SQL语句,那么你们可能会奇怪,为什么真正走的SQL会和我们编写的SQL语句不一样?是的,也许你可以猜到,这里存在优化,不知道大家是否还记得我的上一篇文章,有一张图描绘了mysql的各种组件,其中包括解析器、优化器、插件式存储引擎,其中的解析器和优化器会对你的SQL进行解析优化,所以存在了物理查询,此处大部分工作是数据库底层做的,也不做展开,本文着重介绍逻辑查询。

客户表:

CREATE TABLE `customer` (

`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

`cnum` varchar(30) COLLATE utf8_bin NOT NULL,

`cname` varchar(30) COLLATE utf8_bin NOT NULL,

`create_time` datetime NOT NULL,

`update_time` datetime NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `customer` VALUES ('1', '9527', '李四', '2018-07-18 10:38:34', '2018-07-18 10:38:31');

INSERT INTO `customer` VALUES ('2', '0531', '张三', '2018-07-18 10:39:00', '2018-07-18 10:38:56');

INSERT INTO `customer` VALUES ('3', '007', '王五', '2018-07-18 10:39:23', '2018-07-18 10:39:20');

INSERT INTO `customer` VALUES ('4', '008', '赵柳', '2018-07-18 10:44:54', '2018-07-18 10:44:50');

订单表:

CREATE TABLE `orders` (

`oname` varchar(50) COLLATE utf8_bin NOT NULL,

`cnum` varchar(30) COLLATE utf8_bin NOT NULL,

`onum` varchar(50) COLLATE utf8_bin NOT NULL,

`create_time` datetime NOT NULL,

`update_time` datetime NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `orders` VALUES ('手机', '9527', '1234', '2018-07-18 10:31:32', '2018-07-18 10:31:58');

INSERT INTO `orders` VALUES ('手机', '9527', '12345', '2018-07-18 10:31:20', '2018-07-18 10:31:54');

INSERT INTO `orders` VALUES ('手机', '0531', '', '2018-07-18 10:31:04', '2018-07-18 10:31:29');

INSERT INTO `orders` VALUES ('电视', '0531', '1236', '2018-07-18 10:31:36', '2018-07-18 10:32:03');

INSERT INTO `orders` VALUES ('手机', '0531', '', '2018-07-18 10:31:10', '2018-07-18 10:31:51');

INSERT INTO `orders` VALUES ('冰箱', '007', '2345', '2018-07-18 10:31:47', '2018-07-18 10:32:13');

INSERT INTO `orders` VALUES ('洗衣机', '007', '54321', '2018-07-18 10:31:44', '2018-07-18 10:32:10');

INSERT INTO `orders` VALUES ('电视', '007', '', '2018-07-18 10:31:40', '2018-07-18 10:32:06');

编写SQL:

c.id AS id,

c.cnum AS num,

c.cname AS name,

o.oname AS product,

o.onum AS onum,

COUNT(onum) AS '订单数量',

o.create_time AS '下单时间',

o.update_time AS '更新时间'

FROM customer AS c LEFT JOIN orders AS o

ON c.cnum = o.cnum

WHERE id > 1

GROUP BY num

HAVING COUNT(onum) > 1

ORDER BY c.id ASC

逻辑查询分析:

看到这个图是不是有点惊讶,一个简单的查询SQL竟然有10步,并且每走一步都会产生一个虚拟表,由于图画的很详细了,在此不再赘述,其实了解SQL的逻辑查询对我们编写SQL很有帮助的,不仅可以优化程序,而且也可以对一些常见错误做出解释。

一个常见的错误:

c.cnum AS num,

c.cname AS name,

o.oname AS product,

o.onum AS onum,

COUNT(onum) AS '订单数量',

o.create_time AS '下单时间',

o.update_time AS '更新时间'

FROM customer AS c LEFT JOIN orders AS o

ON c.cnum = o.cnum

WHERE num = 007

GROUP BY num

HAVING COUNT(onum) > 1

ORDER BY c.id ASC

异常信息:

这是一个很简单的问题,就是一个未知列的问题,解决很简单,不让你用别名就用全限定名,即把num改成c.cnum就可以了,这个错误在以前写SQL的时候经常遇到,当时只是会解决,但是并不知道是什么原因,分析完SQL的逻辑查询后有种豁然开朗的感觉,where语句执行在列筛选前,所以别名此时不存在。

一个慢sql优化:

准备一个表里面共有160多万数据:

一个常见的分页慢SQL:

SELECT SQL_NO_CACHE

* FROM lp_liquidator_store LIMIT ,20;

看一下响应时间:

导致这条慢SQL的原因是要去操作这条数据,通过刚才的逻辑查询分析,可知limit语句是最后执行的一个行筛选,而前面的9步每次操作完成之后都会产生一个虚拟表,那么这的数据就会不断被插入,所以导致慢SQL,由分页业务场景可知,前条数据对我们是无用的,我们是否可以直接过滤掉?也即是在执行流程的前几步把条数据干掉,如下优化:

SELECT SQL_NO_CACHE

* FROM lp_liquidator_store WHERE id > LIMIT 20;

看下效果:

由原来的10.36秒降到了0.002秒,效率提高了5180倍,很简单的一个SQL,竟然能导致这么大的性能提升,那么其中是否有缺陷呢?坦白讲有,我们现在只是站在数据库的角度去优化,并没有考虑业务层面,比如我们不用传统的page/pageSize分页,那么业务逻辑需要梳理(本周优化了一次,需要花点心思),同样由于抛弃了传统分页,所以我们不能做到直接跳到尾页(其实可以做,需要花心思),如下的输入跳转也要抛弃。

敲黑板!!!美女来了!!!