Skip to content

Latest commit

 

History

History
128 lines (86 loc) · 5.67 KB

sql3.md

File metadata and controls

128 lines (86 loc) · 5.67 KB

sql语句优化第三期

sql语句的优化这已经是第三期了,前两期都是偏点或者理论。 这期准备从日常工作的实例出发,挑几个大家都写过或者碰到过的点,给出优化建议。

告别 select * 吧

一般来讲,数据越大,从磁盘读取花费的时间就会越长。这点相信大家都知道。但是这依然不能阻止大家常常使用select * 。

同样的道理,从数据库里读出越多的数据,那么查询就会变得越慢。 再加上,生产环境的数据库服务器和WEB服务器都是相互独立的服务器集群,数据越大,越增加网络传输的负载,时间越久。

所以,应该养成一个要什么只取什么的好习惯。

示例代码:

//oh ,no
select * from articles where id=1; (注:article 里的 content 可以是上万字)

//nice 
select title from patients where id=1;

利器ENUM

是不是还为sex=1是表示男,还是表示女而苦恼? 或者为 type里1,2,3这种外星语感到无语?想使用 varchar,但是会严重影响后续使用性能,只能碰到一次苦恼一次!! 谁能解救你? 答案是:ENUM

是不是有过担心说 ENUM会影响查询效率?

实际上,其保存的是 TINYINT,但其外表上显示为字符串,ENUM 类型是非常快和紧凑的。这样一来,用这个字段来做一些选项列表 就变得相当的完美。

以后在遇到如“性别”,“民族”,“状态”或“部门”等,你可以明确知道这些字段的取值是有限而且固定的,别再犹豫,直接拿来主义:ENUM。

示例代码:

//外星语
fld_UserSex                       |  tinyint(4)   

//可读代码
sex        | enum(‘male’, ‘female’) 

擅用 LIMIT

相信大家都写过类似下面这种 sql:

示例代码:

$a = select count(*) from user  where age > 60;
if ( $a >0) {
//xxxxx
}
注:示意代码,不要追究语法。

从上面的示例代码可以看出,只是判断有无age >60。但是上面的语句会把所有符合条件的记录都查出来汇总。 在这种情况下,加上 LIMIT 1 可以增加性能。这样一来,MySQL数据库引擎会在找到一条数据后停止搜索,而不是继续往后查找所有符合条件的记录数据。

示例代码:

$a = select count(1) from user  where age > 60 limit 1;
if ( $a >0) {
//xxxxx
}
注:示意代码,不要追究语法。

很明显,这段代码的会比前面的更有效率。(细心的同学会发现,第一条中是Select *,第二条是Select 1。意在强调下“告别 select * ”)

IP的处理

接触过“好大夫在线点评”的朋友,都知道我们的 ip 是用 varchar(15)来存取的。 我相信很多人都认为很合理,没觉得有问题。 可不可以在优化呢? 答案是肯定的, 对于数据库来说,varchar 的性能是弱于 int的。

那能不能使用 int来存储ip呢?必须可以呀。如:24.89.35.27=》408494875

你用整形来存放,只需要4个字节,并且你可以有定长的字段,记得带上unsigned。而且,这会为你带来查询上的优势,尤其是当你需要使用这样的WHERE条件:IP between ip1 and ip2。 使用 mysql时,可以用函数 INET_ATON() 来把一个字符串IP转成一个整形,INET_NTOA() 把一个整形转成一个字符串IP。在PHP中,也有这样的函数 ip2long() 和 long2ip()。

示例代码:

<?php
echo ip2long('114.66.202.152’);
输出:1916979864
?>


mysql> select INET_ATON('114.66.202.152');
+-----------------------------+
| INET_ATON('114.66.202.152') |
+-----------------------------+
|                  1916979864 |
+-----------------------------+
1 row in set (0.02 sec)

拒绝 all in one 的 SQL 吧 批量查询数据相信大家都写过类似功能的脚本。 如果你到生产环境执行一个大的 SQL语句(insert, delete, update, select),你需要非常小心,这样粗暴的操作很有可能就让整站 down掉。特别是 delete & insert , 因为这两个操作是会锁表的,表一锁住了,别的操作都进不来了。 如果你把表锁上一段时间,比如30秒钟,那么对于一个有很高访问量的站点来说,这30秒所积累的访问进程/线程,数据库链接,打开的文件数,可能不仅仅会让你的服务Crash,还可能会让你的整台服务器马上死掉。

所以,如果你有一个大的批量数据处理,你一定要把其拆分,分段处理数据,如使用 LIMIT 条件就是一个好的方法。

示例代码:

$step = 1000;
$iteratescnt = $total / $step;
$start  = 0;
foreach ($n=0; $n < $iteratescnt; $n++){
delete from users limit $start, $step;
//xxxxxx
sleep(2);
}

尽可能的缩小每个字段的大小。

让我 review过 sql的同学,我对 表字段的数据类型是比较关注的。如varchar(255)是在我这是一定不会过的。我也有简单解释过。为什么这么关注数据库的数据类型呢?在这再详细的解释下:

对于大多数的数据库引擎来说,硬盘操作可能是最重大的瓶颈。所以,把你的数据变得紧凑会对这种情况非常有帮助,因为这减少了对硬盘的访问。

基于上面的理由,在建表时,给每个字段的找到合适的类型,并指定大小,是很重要的。

如果一个表只会有几列的数据(比如说字典表,配置表),那么,我们就没有理由使用 INT 来做主键,使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 会更经济一些。当然,你也需要留够足够的扩展空间。所以建议大家一定要抽时间去看看 mysql 数据类型,熟悉这些面孔。让你的建表语句更精准,更专业。