索引测试

 

准备环境

 

在数据库中创建测试表

Create table test1 (

Id int,

num int,

pass varchar(50)

);

 

在系统提示符下执行如下语句创建100万行数据。

for ((i=1;i<1000000;i++));do `mysql-pRings301. xiaowei -e "insert into test1 values($i,floor($i+rand()*$i),md5($i));"`;done

 

创建数据中

spacer.gif

 

简单测试索引性能

分别在有索引和没有索引的情况下执行查询，检验查询速度。

 

分别在有索引和没有索引的情况下新增数据，检验下插入数据的速度。

 

 

 

查看表结构

mysql> desc test1;

+-------+-------------+------+-----+---------+-------+

| Field | Type        | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| id   | int(11)     | YES  |     |NULL    |       |

| num  | int(11)     | YES  |     |NULL    |       |

| pass | varchar(50) | YES  |     | NULL   |       |

+-------+-------------+------+-----+---------+-------+

3 rows in set (0.09 sec)

 

查看表数据

mysql> select * from test1 limit 10;

+------+------+----------------------------------+

| id  | num  | pass                             |

+------+------+----------------------------------+

|   1 |    1 |c4ca4238a0b923820dcc509a6f75849b |

|   2 |    3 |c81e728d9d4c2f636f067f89cc14862c |

|   3 |    3 |eccbc87e4b5ce2fe28308fd9f2a7baf3 |

|   4 |    7 |a87ff679a2f3e71d9181a67b7542122c |

|   5 |    9 | e4da3b7fbbce2345d7772b0674a318d5|

|   6 |    9 |1679091c5a880faf6fb5e6087eb1b2dc |

|   7 |   10 |8f14e45fceea167a5a36dedd4bea2543 |

|   8 |   15 |c9f0f895fb98ab9159f51fd0297e236d |

|   9 |    9 |45c48cce2e2d7fbdea1afc51c7c6ad26 |

|  10 |   14 | d3d9446802a44259755d38e6d163e820|

+------+------+----------------------------------+

10 rows in set (0.01 sec)

 

刷新查询缓存

 

mysql> reset query cache;

Query OK, 0 rows affected (0.00 sec)

 

查询

mysql> select num,pass from test1 whereid>50000 and id<50050;

+-------+----------------------------------+

| num  | pass                            |

+-------+----------------------------------+

| 70329 | 334146de1b9346272cb013adf1a35aea|

| 52951 | f67fe69d3660b4d35a731817b538b21d|

| 78758 | 0cce9d48eb96fdf93fbae8640d547b8e|

| 84868 | 171ab172efb24344684eca5b04abffca|

| 63045 | e7beb1dcf073b1d1e700fb02eccaf064|

| 60560 | 2432fc2efe99899b0ecff8ade0211e7d|

| 88648 | 1cc41f4ab8528178818a29b9ef5fabbb|

| 61476 | 79c3489e2392afd26733d285dee3abd0|

| 66427 | f0547ecd4e64a31e247c34b64547f812|

| 97640 | 3e53ae683f8e8c84221db763b30fe907|

| 63858 | fbbbadb6d1a15c0c924c73b0b0a4b7cb|

| 76325 | 9ba86c2987b9321a45b4dbf1eff6bb4a|

| 65007 | 8ec41a3e649625a55ceafc35f6fa45e8|

| 96003 | 87b0cba64000c51c883f57274c04519c|

| 59918 | f7c3c4088dfe80933e84ca084fa3524a|

| 61535 | 5a5a84625f44b7e7345b4ea6fde06627|

| 52882 | 85dbdb1cbb78b9be83ccedd468732e0a|

| 79749 | 16d37a42180158171d57e1cc8122b415|

| 65024 | c3a8217c9d3a5d9c5e76a77d8f4a8fde|

| 85816 | 4e2598d3fa41ae72d1927b81328dbd51|

| 58924 | 81b993dae9d5735b0714c325c526aee5|

| 87136 | d3d2a1a264feb84bd8ba9d0557aafca8|

| 83818 | d5e390212ea61535b492b740102df78a|

| 57576 | cf50b28ef624912ff106c57ca9be41dc|

| 61423 | a4eacdf08e8fda83c7784c8fd21f7811|

| 84307 | 9a84af5408986faab11f648a07867d84|

| 62162 | 31bd7cc9213175d709fcfa2eeb4b202a|

| 57828 | 2d084a4acd512e6314d6e8ae111b8205|

| 77628 | 2a12b41adeedc754b55ec468d1a41d09|

| 64513 | 33702a9c691c0f5aaac103d7dd1952eb|

| 64652 | d081111dbdee3c687d1439b444d64004|

| 79633 | dfdc9e0c03a33349408e99f28d07f899|

| 79116 | 0610027c7b4268080e7c1c5f04af05a7|

| 56555 | cb07accc409bbb4c0adc6afb26cf351b|

| 70429 | a1f3a4e959c66a4dd4f330f13ff4d808|

| 82355 | 9f75e281cbe6072bd91a286e64fb6f0d|

| 75382 | 9bf3f8e2f454487987a4888544f9e1be|

| 79751 | 664a26f366b9ef4988631e95af9b366d|

| 97539 | f19ea2ad04c46f33134d405510650a60|

| 98267 | 3b4421d0ab0e43c65932c51fb58f593f|

| 73603 | ca355f31b8e517abc70bf477ca77f4ce|

| 73153 | afa8024de2c03966e71d6f94a93b6b93|

| 69880 | ce9e053a63f6a8aed199bed09f1e498e|

| 79839 | 3cf419e05d85881157b758a01c6ef399|

| 64431 | af7994b458c40e4a18ec60f5e622e522|

| 82583 | c3beb22d8bb8a4b874fd7bb8a8914643|

| 94492 | 5f9f76d679371d223deeda050bdc9d85|

| 74510 | 218171bd4087237acdcc6d3846b9cda5|

| 64038 | d38aad5d5676be87eaf6ade964caff4f|

+-------+----------------------------------+

49 rows in set(0.42 sec)

 

用了0.42秒

 

创建一个新表

mysql>create table test2 (

    -> id int,

    -> num int,

    -> pass varchar(50),

    -> index idIdx (id)

    -> );

QueryOK, 0 rows affected (0.19 sec)

 

 

把test1数据导入test2.

mysql>insert into test2 select * from test1;

QueryOK, 189947 rows affected (6.80 sec)

Records:189947  Duplicates: 0  Warnings: 0

 

用了6.8秒

 

刷新查询缓存

 

mysql> reset query cache;

Query OK, 0 rows affected (0.00 sec)

 

 

查询数据

 

mysql>select num,pass from test2 where id>50000 and id<50050;

+-------+----------------------------------+

|num   | pass                             |

+-------+----------------------------------+

|70329 | 334146de1b9346272cb013adf1a35aea |

|52951 | f67fe69d3660b4d35a731817b538b21d |

|78758 | 0cce9d48eb96fdf93fbae8640d547b8e |

|84868 | 171ab172efb24344684eca5b04abffca |

|63045 | e7beb1dcf073b1d1e700fb02eccaf064 |

|60560 | 2432fc2efe99899b0ecff8ade0211e7d |

|88648 | 1cc41f4ab8528178818a29b9ef5fabbb |

|61476 | 79c3489e2392afd26733d285dee3abd0 |

|66427 | f0547ecd4e64a31e247c34b64547f812 |

|97640 | 3e53ae683f8e8c84221db763b30fe907 |

|63858 | fbbbadb6d1a15c0c924c73b0b0a4b7cb |

|76325 | 9ba86c2987b9321a45b4dbf1eff6bb4a |

|65007 | 8ec41a3e649625a55ceafc35f6fa45e8 |

|96003 | 87b0cba64000c51c883f57274c04519c |

|59918 | f7c3c4088dfe80933e84ca084fa3524a |

|61535 | 5a5a84625f44b7e7345b4ea6fde06627 |

|52882 | 85dbdb1cbb78b9be83ccedd468732e0a |

|79749 | 16d37a42180158171d57e1cc8122b415 |

|65024 | c3a8217c9d3a5d9c5e76a77d8f4a8fde |

|85816 | 4e2598d3fa41ae72d1927b81328dbd51 |

|58924 | 81b993dae9d5735b0714c325c526aee5 |

|87136 | d3d2a1a264feb84bd8ba9d0557aafca8 |

|83818 | d5e390212ea61535b492b740102df78a |

|57576 | cf50b28ef624912ff106c57ca9be41dc |

|61423 | a4eacdf08e8fda83c7784c8fd21f7811 |

|84307 | 9a84af5408986faab11f648a07867d84 |

|62162 | 31bd7cc9213175d709fcfa2eeb4b202a |

|57828 | 2d084a4acd512e6314d6e8ae111b8205 |

|77628 | 2a12b41adeedc754b55ec468d1a41d09 |

|64513 | 33702a9c691c0f5aaac103d7dd1952eb |

|64652 | d081111dbdee3c687d1439b444d64004 |

|79633 | dfdc9e0c03a33349408e99f28d07f899 |

|79116 | 0610027c7b4268080e7c1c5f04af05a7 |

|56555 | cb07accc409bbb4c0adc6afb26cf351b |

|70429 | a1f3a4e959c66a4dd4f330f13ff4d808 |

|82355 | 9f75e281cbe6072bd91a286e64fb6f0d |

|75382 | 9bf3f8e2f454487987a4888544f9e1be |

|79751 | 664a26f366b9ef4988631e95af9b366d |

|97539 | f19ea2ad04c46f33134d405510650a60 |

|98267 | 3b4421d0ab0e43c65932c51fb58f593f |

|73603 | ca355f31b8e517abc70bf477ca77f4ce |

|73153 | afa8024de2c03966e71d6f94a93b6b93 |

|69880 | ce9e053a63f6a8aed199bed09f1e498e |

|79839 | 3cf419e05d85881157b758a01c6ef399 |

|64431 | af7994b458c40e4a18ec60f5e622e522 |

|82583 | c3beb22d8bb8a4b874fd7bb8a8914643 |

|94492 | 5f9f76d679371d223deeda050bdc9d85 |

|74510 | 218171bd4087237acdcc6d3846b9cda5 |

|64038 | d38aad5d5676be87eaf6ade964caff4f |

+-------+----------------------------------+

49rows in set (0.11 sec)

 

用了0.11秒             相比0.42秒  索引加速了查询速度。

 

 

1、 先创建一个带有索引的表，导入数据。

2、 先创建一个没有索引的表，导入数据，再创建索引。

 

建议采取第二种方式。原因是先把数据用上。

 

 

 

创建一个新表

 

mysql>create table test3 (

    -> id int,

    -> num int,

    -> pass varchar(50)

    -> );

QueryOK, 0 rows affected (0.04 sec)

 

刷新查询缓存

 

mysql> reset query cache;

Query OK, 0 rows affected (0.00 sec)

 

插入数据

 

mysql>insert into test3 select * from test1;

QueryOK, 189947 rows affected (4.32 sec)

Records:189947  Duplicates: 0  Warnings: 0

 

用了4.32秒  相比6.8秒速度快了不少。

 

原因：向有索引的表里插入数据每增加一条数据就要重写索引。重写索引增加了额外的操作时间。向没有索引的表里只需要增加数据就行了。