search
HomeBackend DevelopmentPHP Tutorialphp程序优化求建议,执行速度太慢

本帖最后由 zhuzhaodan 于 2013-06-12 18:06:26 编辑

现有 四级词库4000单词txt文档,格式如下
accent n.口音,腔调;重音 
acceptable a.可接受的,合意的 
acceptance n.接受,验收;承认 
access n.接近;通道,入口 
accessory n.同谋,从犯;附件 
accident n.意外的;事故 
accidental a.偶然的;非本质的 

现要把英汉分离后分别插入mySQL数据库内,代码如下
<?phpheader("Content-type: text/html; charset=utf-8");$file = dirname(__FILE__)."/siji.txt";  //四级词库文件if(!file_exists($file)){	echo 'Not exist';}else {	$a = array();//存放英汉对照对儿的数组	$lines = file($file);//读取txt到数组,一行为一个英汉对照对	foreach ($lines as $k=>$v){		if(preg_match('/[a-zA-Z]/',$v))//有的行是标题之类的,不是英汉对照,判断后不加到数组a里面			$a[$k] = trim($v);//因为有换行符,去掉	}	$b = array();//2维数组,$b[n][0]为英文,$b[n][1]为释义	foreach($a as $k=>$v){//把a数组的英汉分离,填充到b数组的第二维内		preg_match('/([a-zA-Z]*)\s(.*)/',$v,$matches);//正则英汉分离,matches[1]是英文,matches[2]是释义中文		$b[$k][0] = $matches[1];		$b[$k][1] = $matches[2];	}	$dsn = 'mysql:host=localhost;dbname=test1';	$db = new PDO($dsn,'root','',array(PDO::MYSQL_ATTR_INIT_COMMAND => 'set names utf8'));	foreach($b as $k=>$v){//插入数据库		$db->exec("INSERT INTO siji (en,cn) VALUES ('$v[0]','$v[1]')");//数据库3个字段,id,en是英文,cn是中文	}		}


程序执行时间2分钟左右,求优化建议~我自己感觉正则那部分应该优化不了多少,主要感觉插进数据库这里费时太多,各位高人有方法请不吝赐教,现在程序执行完毕需要 120秒,希望最终优化后能到10秒内,谢谢,4级词库一共才4000对词组,120秒太长了

最终效果图:


高人们,因为我比较新手,所以不管从代码上,还是整体思路上都使这个程序运行缓慢,求优化建议,特别是 数据库这里,还有 数组这里,或者有 更好的办法。100分在这里,谢谢!

回复讨论(解决方案)

不一定能满足要求,但肯定比你的快

$ar = array_map('trim', file('siji.txt', FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES ));foreach($ar as $v){  preg_match('/([a-zA-Z]*)\s(.*)/', $v, $r);  if(isset($r[2])) $b[] = "('$r[1]','$r[2]')";}$dsn = 'mysql:host=localhost;dbname=test1';$db = new PDO($dsn,'root','',array(PDO::MYSQL_ATTR_INIT_COMMAND => 'set names utf8'));$db->exec('INSERT INTO siji (en,cn) VALUES ' . join(',', $b));
 

说一说我的想法,
也不知道效率怎么样,
不用数组,也不用循环。
首先用 file_get_contents 将文件读取为字符串,
然后 preg_replace 直接将整个字符串格式化为一个多语句sql,
接着执行一次就行了。

内存表 一次性写入数据库

1、将内容格式化成一个类似#1的插入sql语句,保存成*.sql
2、直接用mysql将上面的*.sql导入,php可以用exec

内存表 一次性写入数据库
求详解

mysql有个多行insert语句, 很多行集成到一个SQL语句, 效率比一行一条SQL语句高很多.
格式如下:
inser into (...) values (...),(...),...

不好意思, 上面insert少了个t
MySQL 的导出语句格式都是用多行insert. 导入速度很快. 一般一条语句2000行记录没问题

如果你的数据文件中的行都符合你示例的格式,那么就可以直接使用 MySQL 指令

LOAD DATA local INFILE '/路径/siji.txt' INTO TABLE siji  FIELDS TERMINATED BY ' ' (en, cn);

不一定能满足要求,但肯定比你的快

$ar = array_map('trim', file('siji.txt', FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES ));foreach($ar as $v){  preg_match('/([a-zA-Z]*)\s(.*)/', $v, $r);  if(isset($r[2])) $b[] = "('$r[1]','$r[2]')";}$dsn = 'mysql:host=localhost;dbname=test1';$db = new PDO($dsn,'root','',array(PDO::MYSQL_ATTR_INIT_COMMAND => 'set names utf8'));$db->exec('INSERT INTO siji (en,cn) VALUES ' . join(',', $b));
 

我考虑了一下,用了预处理语句参数绑定
	$start = microtime(true);	$stmt = $db->prepare('INSERT INTO siji (en,cn) VALUES (:en,:cn)');	$stmt->bindParam(':en',$en);	$stmt->bindParam(':cn',$cn);	foreach($b as $k=>$v){//插入数据库		$en = $v[0];		$cn = $v[1];		$stmt->execute();//数据库3个字段,id,en是英文,cn是中文	}	echo microtime(true)-$start;//127秒

对比原来的代码
$start = microtime(true);	foreach($b as $k=>$v){//插入数据库		$db->exec("INSERT INTO siji (en,cn) VALUES ('$v[0]','$v[1]')");//数据库3个字段,id,en是英文,cn是中文	}	echo microtime(true)-$start;//116秒

居然还慢了11秒,不是说一样类型的语句,编译成sql模板后速度会提升么,为什么这里下降啊??太奇怪!

使用参数绑定时,PDO::quote(转义特殊字符)始终都会被执行,并不理会你的数据实际并不包含特殊字符
多出来的时间就是 PDO::quote 的执行时间

如果你总是单条插入,那么无论如何都不会“高速”运行的

居然还慢了11秒,不是说一样类型的语句,编译成sql模板后速度会提升么,为什么这里下降啊??太奇怪!
预编译的提升原理在于节省了编译本身的时间,你觉得一个简单的insert编译需要时间吗?

内存表 一次性写入数据库

感觉MySQL还是弱,貌似没有类似SQLServer的bulkCopy功能,SQLBulkCopy批量插入几万条数据也是瞬间的事
“内存表 一次性写入数据库”,具体怎么实现,跪求详解啊

连接成一个SQL语句再执行,速度会快很多的

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
PHP's Purpose: Building Dynamic WebsitesPHP's Purpose: Building Dynamic WebsitesApr 15, 2025 am 12:18 AM

PHP is used to build dynamic websites, and its core functions include: 1. Generate dynamic content and generate web pages in real time by connecting with the database; 2. Process user interaction and form submissions, verify inputs and respond to operations; 3. Manage sessions and user authentication to provide a personalized experience; 4. Optimize performance and follow best practices to improve website efficiency and security.

PHP: Handling Databases and Server-Side LogicPHP: Handling Databases and Server-Side LogicApr 15, 2025 am 12:15 AM

PHP uses MySQLi and PDO extensions to interact in database operations and server-side logic processing, and processes server-side logic through functions such as session management. 1) Use MySQLi or PDO to connect to the database and execute SQL queries. 2) Handle HTTP requests and user status through session management and other functions. 3) Use transactions to ensure the atomicity of database operations. 4) Prevent SQL injection, use exception handling and closing connections for debugging. 5) Optimize performance through indexing and cache, write highly readable code and perform error handling.

How do you prevent SQL Injection in PHP? (Prepared statements, PDO)How do you prevent SQL Injection in PHP? (Prepared statements, PDO)Apr 15, 2025 am 12:15 AM

Using preprocessing statements and PDO in PHP can effectively prevent SQL injection attacks. 1) Use PDO to connect to the database and set the error mode. 2) Create preprocessing statements through the prepare method and pass data using placeholders and execute methods. 3) Process query results and ensure the security and performance of the code.

PHP and Python: Code Examples and ComparisonPHP and Python: Code Examples and ComparisonApr 15, 2025 am 12:07 AM

PHP and Python have their own advantages and disadvantages, and the choice depends on project needs and personal preferences. 1.PHP is suitable for rapid development and maintenance of large-scale web applications. 2. Python dominates the field of data science and machine learning.

PHP in Action: Real-World Examples and ApplicationsPHP in Action: Real-World Examples and ApplicationsApr 14, 2025 am 12:19 AM

PHP is widely used in e-commerce, content management systems and API development. 1) E-commerce: used for shopping cart function and payment processing. 2) Content management system: used for dynamic content generation and user management. 3) API development: used for RESTful API development and API security. Through performance optimization and best practices, the efficiency and maintainability of PHP applications are improved.

PHP: Creating Interactive Web Content with EasePHP: Creating Interactive Web Content with EaseApr 14, 2025 am 12:15 AM

PHP makes it easy to create interactive web content. 1) Dynamically generate content by embedding HTML and display it in real time based on user input or database data. 2) Process form submission and generate dynamic output to ensure that htmlspecialchars is used to prevent XSS. 3) Use MySQL to create a user registration system, and use password_hash and preprocessing statements to enhance security. Mastering these techniques will improve the efficiency of web development.

PHP and Python: Comparing Two Popular Programming LanguagesPHP and Python: Comparing Two Popular Programming LanguagesApr 14, 2025 am 12:13 AM

PHP and Python each have their own advantages, and choose according to project requirements. 1.PHP is suitable for web development, especially for rapid development and maintenance of websites. 2. Python is suitable for data science, machine learning and artificial intelligence, with concise syntax and suitable for beginners.

The Enduring Relevance of PHP: Is It Still Alive?The Enduring Relevance of PHP: Is It Still Alive?Apr 14, 2025 am 12:12 AM

PHP is still dynamic and still occupies an important position in the field of modern programming. 1) PHP's simplicity and powerful community support make it widely used in web development; 2) Its flexibility and stability make it outstanding in handling web forms, database operations and file processing; 3) PHP is constantly evolving and optimizing, suitable for beginners and experienced developers.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Best Graphic Settings
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. How to Fix Audio if You Can't Hear Anyone
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
WWE 2K25: How To Unlock Everything In MyRise
1 months agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

MantisBT

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

PhpStorm Mac version

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment