先 LOAD DATA 再加索引(主键)是不是真的好？

If you want to Import data from dat file to Mysql , May be you will choose : Load Data infile;
Normally, you will create a table without index (include primary key) ,then load the data , then create the index ;
But ,I found after load data and create primary key , "select count(*) from T" is so slowly.
[@more@]

#### ENV
OS:RHEL 5U4
MYSQL: 5140
Engine: Innodb


#### Create Table ;
CREATE TABLE `http_auth1` (
`username` varchar(64) NOT NULL,
`pass` varchar(200) DEFAULT NULL,
`comment` varchar(200) DEFAULT NULL,
`uid` int(11) NOT NULL,
`gid` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#### LOAD DATA INFILE
# Attention: Do not load data for "username " after sorted;

root@127.0.0.1 : test 16:47:36> load data infile '/var/smack-data/words.dat' into table http_auth1 FIELDS TERMINATED BY ',';
Query OK, 20000000 rows affected, 65535 warnings (13 min 24.49 sec)
Records: 20000000 Deleted: 0 Skipped: 0 Warnings: 1956829

####CP a table
root@127.0.0.1 : test 17:01:04> create table http_auth1_cp engine=innodb as select * from http_auth1;
Query OK, 20000000 rows affected (12 min 50.35 sec)
Records: 20000000 Duplicates: 0 Warnings: 0

#### 2 Table have same size of datafile;
-rw-rw---- 1 mysql mysql 8690 Nov 3 17:02 http_auth1_cp.frm
-rw-rw---- 1 mysql mysql 8665432064 Nov 3 17:13 http_auth1_cp.ibd
-rw-rw---- 1 mysql mysql 8690 Nov 3 16:47 http_auth1.frm
-rw-rw---- 1 mysql mysql 8665432064 Nov 3 16:59 http_auth1.ibd

root@127.0.0.1 : test 17:16:16> show table status like 'http_auth1'G
*************************** 1. row ***************************
Name: http_auth1
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 20041954
Avg_row_length: 426
Data_length: 8538554368
Max_data_length: 0
Index_length: 0
Data_free: 5242880
Auto_increment: NULL
Create_time: 2010-11-03 16:47:18
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.29 sec)

root@127.0.0.1 : test 17:16:18> show table status like 'http_auth1_cp'G
*************************** 1. row ***************************
Name: http_auth1_cp
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 20107025
Avg_row_length: 424
Data_length: 8538554368
Max_data_length: 0
Index_length: 0
Data_free: 5242880
Auto_increment: NULL
Create_time: 2010-11-03 17:02:09
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.06 sec)

#### Add primary key , need more time because of sort;
root@127.0.0.1 : test 17:17:20> alter table http_auth1_cp add primary key (username);
Query OK, 20000000 rows affected (34 min 30.39 sec)
Records: 20000000 Duplicates: 0 Warnings: 0

#### After Add primary key , need more diskspace because of Index Split;

-rw-rw---- 1 mysql mysql 8690 Nov 3 17:17 http_auth1_cp.frm
-rw-rw---- 1 mysql mysql 13820231680 Nov 3 17:54 http_auth1_cp.ibd
-rw-rw---- 1 mysql mysql 8690 Nov 3 16:47 http_auth1.frm
-rw-rw---- 1 mysql mysql 8665432064 Nov 3 16:59 http_auth1.ibd

#### The most important question ?

select count(*) from http_auth1_cp ;

#### It is so slowly Run above Sql ,Speed about 60 mins ;
# Iostat display the speed of readding from disk just 2000Kb/s ;
# But is so fast for SQL on http_auth1 :
select count(*) from http_auth1 , (have not add primary key) ;

#### But,Create a New table from sorted table (http_auth1_cp), no such problem;
create table http_auth1_cp2 as select * from http_auth1_cp;
alter table http_auth1_cp2 add primary key (username);
select count(*) from http_auth1_cp2;
### Speed is Normal; 30 sec

#### After Optimize slow Table http_auth1_cp , read speed change to Normal

root@127.0.0.1 : test 18:11:23> optimize table http_auth1_cp ;
+--------------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+--------------------+----------+----------+-------------------------------------------------------------------+
| test.http_auth1_cp | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| test.http_auth1_cp | optimize | status | OK |
+--------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (1 hour 6 min 12.96 sec)

-rw-rw---- 1 mysql mysql 8690 Nov 3 18:12 http_auth1_cp.frm
-rw-rw---- 1 mysql mysql 8560574464 Nov 3 19:19 http_auth1_cp.ibd

root@127.0.0.1 : test 19:18:52> select count(*) from http_auth1_cp;
+----------+
| count(*) |
+----------+
| 20000000 |
+----------+
1 row in set (28.62 sec)

#### The result
A table , after create primary key on nonsorted column, select count(*) is very slow; why ?
too much Pages ?
different expalin plan ?

Searching ....

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/703656/viewspace-1040845/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/703656/viewspace-1040845/