python查询数据库速度慢_Python具有缓慢的数据库查询,但是Perl没有

我使用

python(Django)作为我的网店.

当我测试高负载(db访问)得到有趣的结果：

python 10 process = 200sec / 100% CPU utilisation

perl 10 process = 65sec / 35% CPU utilisation

Centos 6,python 2.6,mysql 5.5,标准库,其他服务器上的mysql-server.

表product_cars有70 000 000条记录.

为什么python程序这么慢？

Python程序：

#!/usr/bin/python

import MySQLdb

import re

from MySQLdb import cursors

import shutil

import datetime

import random

db0 = MySQLdb.connect(user="X", passwd="X", db="parts")

cursor0 = db0.cursor()

cursor0.execute('SET NAMES utf8')

now = datetime.datetime.now()

for x in xrange(1, 100000):

id = random.randint(10, 50000)

cursor0.execute("SELECT * FROM product_cars WHERE car_id=%s LIMIT 500", [id])

cursor0.fetchone()

Perl程序：

#!/usr/bin/perl

use DBI;

my $INSTANCE=$ARGV[0];

my $user = "x";

my $pw = "x";

my $db = DBI->connect( "dbi:mysql:parts", "x", "x");

my $sql= "SELECT * FROM product_cars WHERE car_id=? LIMIT 500";

foreach $_ ( 1 .. 100000 )

{

$random = int(rand(50000));

$cursor = $db->prepare($sql);

$cursor->execute($random) || die $cursor->errstr;

@Data= $cursor->fetchrow_array();

}

$cursor->finish;

$db->disconnect;

UPDATE1

有趣的事情

选择始终为id = 1的行：

唉,MYSQL使用缓存和查询会非常快,但再次缓慢和100％的CPU利用率.但是同样的perl或ruby代码工作很快.

如果在python代码中替换字符串：

# remove "SET NAMES utf8" string - this has no impact

# python-mysql use "%s", but not "?" as parameter marker

id = 1

for x in xrange(1, 100000):

id = 1

cursor0.execute("SELECT * FROM product_cars WHERE car_id=%s LIMIT 500", [id])

cursor0.fetchone()

perl中的相同代码：

foreach $_ ( 1 .. 20000 )

{

$cursor = $db->prepare( "SELECT * FROM product_cars WHERE car_id=? LIMIT 500";);

$cursor->execute(1);

# while (my @Data= $cursor->fetchrow_array())

if ($_ % 1000 == 0) { print "$_\n" };.

@Data= $cursor->fetchrow_array();

# print "$_\n";

}

红宝石代码：

pk=2

20000.times do |i|

if i % 1000 == 0

print i, "\n"

end

res = my.query("SELECT * FROM product_cars WHERE car_id='#{pk}' LIMIT 500")

res.fetch_row

end

更新2

Exec SQL "SELECT * FROM product WHERE id=1" (string without params) 100000 times

Python: ~15 sec 100% CPU 100%

Perl: ~9 sec CPU 70-90%

Ruby: ~6 sec CPU 60-80%

MySQL服务器在其他机器上.

更新3

尝试使用ourql和pymysql – 更糟的结果.