标签归档：效率

mysql对大表执行update速度慢时，试试改用insert可能会有意想不到的发现

发表于由
回复

实例：需要根据用户日志的ip地址计算出其地理地址

表结构：

用户日志表（200万条记录），其中address是待填充的字段：

CREATE TABLE `tmp_open_ip` (
  `email` varchar(60) NOT NULL DEFAULT '',
  `address` varchar(50) NOT NULL DEFAULT '',
  `ip` int(10) unsigned NOT NULL DEFAULT '0',
  KEY `email` (`email`),
  KEY `ip` (`ip`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

另ip地址数据库表（44万条记录）

CREATE TABLE `ip` (
`s` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '开始ip',
`e` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '结束ip',
`a` varchar(50) NOT NULL DEFAULT '',
KEY `s` (`s`),
KEY `e` (`e`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

需要根据用户日志表 tmp_open_ip 里的 ip字段到ip地址数据库表里查询出对应的地理地址，将地址填充到address字段。

使用如下update语句执行：

UPDATE tmp_open_ip AS u 
INNER JOIN ip 
ON u.ip BETWEEN ip.s AND ip.e 
SET u.address = ip.a

在笔者的电脑上运行了速度非常之慢，执行了一个多小时（4500s）都没有完，也不知道还要多久。

实在看不过去，于是想到使用insert 是否会快一些，于是重新导一张表 tmp_open_log 与tmp_open_log完全一致。

创建一张表 tmp_open_address，是insert的目标表，为了速度更快，没建索引：

CREATE TABLE `tmp_open_address` (
`email` varchar(60) NOT NULL DEFAULT '',
`address` varchar(50) NOT NULL DEFAULT '',
`ip` int(10) unsigned NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8

执行insert 语句

insert into tmp_open_address (email,address,ip)
select l.email,ip.a,l.ip
 from  tmp_open_log as l inner join ip on l.ip between ip.s and ip.e ;
/* Affected rows: 2,543,124  Found rows: 0  Warnings: 0  Duration for 3 queries: 16.922 sec. */

不到17s！本来还想去倒杯水、稍事休息一下，结果已经执行完毕。

到本文写完时，前面的update语句已经执行了5000s，结束仍是遥遥无期。

所以，对于大数据量执行update时，可以考虑改用insert 语句实现，可能麻烦一些，但高速带来的收益远大于麻烦！

后记：

直接杀死了update进程，去看看update执行了多少：运行

SELECT * FROM `tmp_open_ip` where address!=''

结果只有 11,373 ，照这个速度，要运行N天....

php 通过mssql_xx函数读mssql 2005记录, text/ntext字段非常速度慢

发表于由
回复

使用php写的web程序，从mssql 2005读取一个文章列表，要读取出文章内容，在列表页面里显示出一部分，起先使用：

SELECT top 20 [id],[title],[content], FROM [news] where xxx

这是很自然直接的写法，但是发现运行速度非常的慢，浏览器访问经常要花费好几秒到几十秒才打开页面，慢的时候还会php脚本执行的超时；该表建有必要的索引，数据量只有几万条而已。通过定时追踪发现问题就出在这个语句上，改成如下的形式：

 $sql="SELECT top $pagesize * from 
    (
        SELECT top $top [id],[title]
         ,convert(varchar(30),[updatetime],120) as [updatetime] ,[keyword]
         ,[picture],convert(varchar(500),content) as concent
        FROM [news] ";
 $sql = $sql.$sql_where." 
        order by updatetime desc
    ) as bb order by updatetime asc";

目的是方便分页，不读取出没用的记录。但事实上并没有起色，还是非常慢(这个写法读出来的记录集其实是倒序的，要在读出到数组里后使用array_reverse来反转一下)。而当把sql语句里的content字段去掉不读，就会快如飞一般。就算是改换$sql_where 里的条件也一样的快(这样mssql就不会使用其内部缓存，方便比较语句效率)。

这么看，text型字段实在是mssql里sql语句效率的杀手；可悲的是，这个内容字段是一定要读取的，这是web系统的功能要求，使用缓存是一个方法，但就算使用缓存，在生成缓存时，也一样会非常慢，甚至超时。

几乎是异想天开的念头：拆成多个语句，先读出不带text字段的记录集，再通过php程序遍历id号，然后一条一条的读出text字段，并拼到结果数组里，没想到，居然速度快多了，打开速度维持在0.5秒内，虽然并不够理想。

实在是无法想像，无法理解为什么是这样子！

代码大致如下：

function get_content($id)
{
	global $conn;
	$content='';
	$sql='select convert(varchar(max),content) as content FROM [news] where id='.(int)$id;
	$rs=mssql_query($sql,$conn);
	if($row=mssql_fetch_assoc($rs)){
		$content=$row['content'];
	}
	$content=substr(strip_tags($content),0,500);
	return $content;
}

$top=($page +1)*$pagesize ;
 $sql="SELECT top $pagesize * from (SELECT top $top [id],[title] ,convert(varchar(30),[updatetime],120) as [updatetime] ,[keyword],[picture] FROM [news] "; $sql = $sql.$sql_where." order by updatetime desc) as bb order by updatetime asc";
$rs=mssql_query($sql,$conn) or die($sql);
$data=array();
while($row=mssql_fetch_assoc($rs)){
	$row=$row+array(
		'content' => get_content($row['id'])
		);

	$data[]=$row;
}
$data=array_reverse($data);