Mysql数据类型及查询浅析实战

Mysql的数据类型

工欲善其事，必先利其器。要想使用好数据库，数据库的数据类型你是有必要了解的，很多简单的细节也能给数据库带来不少优化。此处不做过多展开，老实讲通过一篇文章你不可能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', '123456', '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', '134562', '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', '654321', '2018-07-18 10:31:40', '2018-07-18 10:32:06');

编写SQL：

SELECT

DISTINCT

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

LIMIT 0,3;

逻辑查询分析：

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

一个常见的错误：

SELECT

DISTINCT

c.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 num = 007

GROUP BY num

HAVING COUNT(onum) > 1

ORDER BY c.id ASC

LIMIT 0,3

异常信息：

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

一个慢sql优化：

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

一个常见的分页慢SQL：

SELECT SQL_NO_CACHE

* FROM lp_liquidator_store LIMIT 1500000,20;

看一下响应时间：

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

SELECT SQL_NO_CACHE

* FROM lp_liquidator_store WHERE id > 1500000 LIMIT 20;

看下效果：

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

敲黑板！！！美女来了！！！