Mysql查询测试

引用

os:ubuntu 32bit
cpu:model name : Intel(R) Pentium(R) Dual  CPU  E2160  @ 1.80GHz
memory:512M
普通7200 rpm:sata硬盘


BEGIN;
/**
*分组表
*order为了自定义排序
*create_time简化时间排序
*/
CREATE TABLE `contactgroup` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `email` varchar(64)  NOT NULL,
    `name` varchar(32) NOT NULL,
    `create_time` datetime NOT NULL,
    `order` integer NOT NULL
)
;
/**
*
*联系人表,和以前的列基本一至
*某些字段的长度可能需要在导入时发现异常情况后重新商议
*
*/
CREATE TABLE `contact` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `uid` integer  NULL,
    `name` varchar(32) NOT NULL,
    `email` varchar(64) NOT NULL,
    `important` integer  NULL,
    `mobile` varchar(15)  NULL,
    `sinauser` varchar(32)  NULL,
    `uc` integer  NULL,
    `letter` varchar(24)  NULL,
    `birthday` datetime  NULL,
    `homepage` varchar(128)  NULL,
    `email_bak` varchar(64)  NULL,
    `country` varchar(128)  NULL,
    `city` varchar(128)  NULL,
    `home_phone` varchar(15)  NULL,
    `home_addr` varchar(256)  NULL,
    `home_post` varchar(6)  NULL,
    `home_fax` varchar(15)  NULL,
    `company` varchar(128)  NULL,
    `company_title` varchar(128)  NULL,
    `company_addr` varchar(256)  NULL,
    `company_post` varchar(6)  NULL,
    `company_phone` varchar(15)  NULL,
    `company_fax` varchar(15)  NULL,
    `note` varchar(256)  NULL,
    `uic` integer  NULL,
    `sinanick` varchar(32)  NULL
)
;
/**
*
*关联表
*在关联表里面增加了name & email两个冗余字段，是为了提升查询的速度，在应
用逻辑里面会有相应的代码保证数据一至性
*/

CREATE TABLE `contactgroup_contact` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `group_id` integer NOT NULL REFERENCES `contactgroup` (`id`),
    `contact_id` integer NOT NULL REFERENCES `contact` (`id`),
    `name` varchar(32) NOT NULL,
    `email` varchar(64) NOT NULL,
    UNIQUE (`group_id`, `contact_id`)
)
;
COMMIT;
索引另外加

一，准备数据，用python写个脚本，最后只插入了近4000W数据，单表最大1800W记录
root@hanyh-desktop:~# more insert.py
# -*- coding: utf-8 -*-
import MySQLdb
import random

conn = MySQLdb.connect (host = "x.x.x.x",
                   user = "edu",
                   passwd = "edu",
                   db = "addr")
cursor = conn.cursor ()

import datetime
import os
import string

sqls = ("delete from contactgroup_contact","delete from contactgroup","delete from contact")
for sql in sqls:
    cursor.execute(sql)
   
email = ""
c_id = 1
for i in xrange(10000000):
   random.seed() #Set the seed based on the system time.
   j = (i % 10 )
   if j == 0:
       name = ''.join(random.choice(string.letters) for i in xrange(j+3))
       email = name+str(i)+"@sina.cn"
   else:
       name = ''.join(random.choice(string.letters) for i in xrange(j+3))
   g_id = (i+400)
  # print g_id
   g = "insert into contactgroup (id,email,name,create_time,`order`) values (%s,\'%s\',\'%s\',\'%s\',\'%s\')" %
(g_id,email,name,datetime.datetime.now(),i)   
  # print g
   cursor.execute (g)
   for k in xrange(5):
        c_id = c_id+1  
        c_name = ''.join(random.choice(string.letters) for i in xrange(5))
        c_email = name+str(k)+"@sina.com"
        c = "insert into contact(id,name,email) values (%s,\'%s\',\'%s\')" %(c_id,c_name,c_email)
      #  print c
        cursor.execute (c)
       
        r = "insert into contactgroup_contact(id,group_id,contact_id,name,email) values (%s,%s,%s,\'%s\',\'%s\'
)" % (c_id,g_id,c_id,c_name,c_email)

        cursor.execute(r)

  
cursor.close ()
conn.close ()


二，主键查询：
<?php
function microtime_float()
{
    list($usec, $sec) = explode(" ", microtime());
    return ((float)$usec + (float)$sec);
}

for($j=0;$j<10;$j++) {
$time_start = microtime_float();
$link = mysql_connect('x.x.x.x', 'edu', 'edu');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
// make foo the current db
$db_selected = mysql_select_db('addr', $link);
if (!$db_selected) {
    die ('Can\'t use foo : ' . mysql_error());
}

$contacts = array();
for($i=1;$i<10000;$i++) {
    $id = mt_rand(2, 18994235);
    $sql = "select name,email from contact where id=$id";
    $result = mysql_query($sql);
    $row = mysql_fetch_row($result);
    $name = $row[0];
    $email = $row[1];
    $contacts[] = array($name,$email);
}
mysql_close($link);
$fp = fopen("contacts.dat","w");
fwrite($fp,serialize($contacts));
fclose($fp);

$time_end = microtime_float();
$time = $time_end - $time_start;
echo "$time\n";
}
?>
结果：
1W次查询，160s左右，无并发


并发查询，机器负载15+...
$ab -n10000 -c10 -t100 http://localhost/mail_bench/get_email2.php
Time taken for tests:   100.041 seconds
Complete requests:      1659
Failed requests:        1549
   (Connect: 0, Receive: 0, Length: 1549, Exceptions: 0)
Write errors:           0
Total transferred:      411493 bytes
HTML transferred:       24946 bytes
Requests per second:    16.58 [#/sec] (mean)
Time per request:       603.022 [ms] (mean)
Time per request:       60.302 [ms] (mean, across all concurrent requests)
Transfer rate:          4.02 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.2      0       5
Processing:     2  600 584.0    857    2816
Waiting:        1  600 584.0    857    2815
Total:          2  600 584.0    857    2816

Percentage of the requests served within a certain time (ms)
  50%    855
  66%   1059
  75%   1122
  80%   1169
  90%   1275
  95%   1345
  98%   1419
  99%   1474
100%   2816 (longest request)

三，non-index查询：
$contacts = array();

$str = file_get_contents ("contacts.dat");
$contacts = unserialize($str);
print count($contacts);
$i = 1;
foreach($contacts as $c) {
    $mail = $c[1];
    $sql = "select name,email from contact where email='".$mail."'";
    print $i.":".$sql."\n";
    $i += 1;
    $result = mysql_query($sql);
    $row = mysql_fetch_row($result);
}
....
62:select name,email from contact where email='[e-mail]'
63:select name,email from contact where email='[e-mail]'
64:select name,email from contact where email='[e-mail]'
65:select name,email from contact where email='[e-mail]'
66:select name,email from contact where email='[e-mail]'
67:select name,email from contact where email='[e-mail]'
68:select name,email from contact where email='[e-mail]'
69:select name,email from contact where email='[e-mail]'
70:select name,email from contact where email='[e-mail]'
71:select name,email from contact where email='[e-mail]'
72:select name,email from contact where email='[e-mail]'
73:select name,email from contact where email='[e-mail]'
74:select name,email from contact where email='[e-mail]'
75:select name,email from contact where email='[e-mail]'

执行到65个的时候，速度变得极慢，1分钟能出来3个...实在等不了了.

现在给email字段加index,该表的记录数为:18994235
mysql> alter table contact add index c_mail_idx (email);
执行了26分钟，结果还没出来.....
现在ubuntu上面跑的xfce对keyboard&mouse无反应....
只能ssh

继续等着：
查看状态：
mysql> show table status from contact \G
*************************** 1. row ***************************
           Name: contact
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 18994235
Avg_row_length: 55
    Data_length: 1063677100
Max_data_length: 281474976710655
   Index_length: 194926592
      Data_free: 0
Auto_increment: 18994237
    Create_time: 2009-05-22 13:32:49
    Update_time: 2009-05-22 18:35:24
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
Create_options:
        Comment:
*************************** 2. row ***************************
           Name: contactgroup
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 3798847
Avg_row_length: 48
    Data_length: 184490432
Max_data_length: 281474976710655
   Index_length: 65113088
      Data_free: 0
Auto_increment: 3799247
    Create_time: 2009-05-22 14:24:51
    Update_time: 2009-05-22 18:35:24
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
Create_options:
        Comment:
*************************** 3. row ***************************
           Name: contactgroup_contact
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 18994234
Avg_row_length: 41
    Data_length: 797757776
Max_data_length: 281474976710655
   Index_length: 469504000
      Data_free: 0
Auto_increment: 18994236
    Create_time: 2009-05-22 13:32:51
    Update_time: 2009-05-22 18:35:24
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
Create_options:
        Comment:

mysql> select concat(truncate(sum(data_length)/1024/1024,2),'MB') as data_size, concat(truncate(sum(max_data_length)/1024/1024,2),'MB') as max_data_size, concat(truncate(sum(data_free)/1024/1024,2),'MB') as data_free, concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size from information_schema.tables where TABLE_SCHEMA = 'addr';
+-----------+----------------+-----------+------------+
| data_size | max_data_size  | data_free | index_size |
+-----------+----------------+-----------+------------+
| 1951.14MB | 805306367.99MB | 0.00MB    | 695.74MB   |
+-----------+----------------+-----------+------------+

mysql> select concat(truncate(sum(data_length)/1024/1024,2),'MB') as data_size, concat(truncate(sum(max_data_length)/1024/1024,2),'MB') as max_data_size, concat(truncate(sum(data_free)/1024/1024,2),'MB') as data_free, concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size from information_schema.tables where TABLE_NAME = 'contact';
+-----------+----------------+-----------+------------+
| data_size | max_data_size  | data_free | index_size |
+-----------+----------------+-----------+------------+
| 1014.40MB | 536870911.99MB | 0.00MB    | 185.89MB   |
+-----------+----------------+-----------+------------+


mysql> select concat(truncate(sum(data_length)/1024/1024,2),'MB') as data_size, concat(truncate(sum(max_data_length)/1024/1024,2),'MB') as max_data_size, concat(truncate(sum(data_free)/1024/1024,2),'MB') as data_free, concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size from information_schema.tables where TABLE_NAME = 'contactgroup_contact';
+-----------+----------------+-----------+------------+
| data_size | max_data_size  | data_free | index_size |
+-----------+----------------+-----------+------------+
| 760.80MB  | 536870911.99MB | 0.00MB    | 447.75MB   |
+-----------+----------------+-----------+------------+
1 row in set (0.04 sec)