MySQL索引性能测试

news/2025/3/14 21:56:24/

MySQL索引性能测试

 

blog文档结构图:

wpsEBD3.tmp

 

很长一段时间没学习MySQL了,一直致力于oracle的研究,最近得空了就再拾起MySQL看看吧,记得去年发布过的2篇MySQL文章:

    MySQL 5.6.21下载安装之下载篇(一)  : http://blog.itpub.net/26736162/viewspace-1349705/

    MySQL 5.6.21下载安装之安装篇(二): http://blog.itpub.net/26736162/viewspace-1349787/

今天我们就来看看mysql中索引的性能测试:

准备环境

1.1  在数据库中创建测试表test1

[root@rhel6_lhr ~]# mysql -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 14

Server version: 5.6.21-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

 

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

 

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 

mysql> show databases;

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

| Database           |

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

| information_schema |

| db1                |

| db4                |

| lhr_test           |

| mysql              |

| opensource         |

| opesource          |

| performance_schema |

| test               |

| wyzc               |

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

10 rows in set (0.00 sec)

 

mysql> use lhr_test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

Database changed

mysql> create table test1(

    -> id int,

    -> num int,

    -> pass varchar(50)

    -> );

Query OK, 0 rows affected (0.01 sec)

 

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.00 sec)

 

mysql> create table test2(

    ->  id int,

    ->  num int,

    ->  pass varchar(50),

    ->  index idIdx (id)

    ->  );

Query OK, 0 rows affected (0.24 sec)

 

mysql> create table test3(

    ->  id int,

    ->  num int,

    ->  pass varchar(50)

    ->  );

reset query cache;Query OK, 0 rows affected (0.09 sec)

 

 

 

 

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

在操作系统执行命令,生成100W条数据,其中-plhr中的lhr为mysql的密码,lhr_test为数据库名,该命令为一整条命令: for ((i=1;i<=1000000;i++));do `mysql -plhr lhr_test -e "insert into test1 values($i,floor($i+rand()*$i),md5($i));"`; done > /tmp/mysql.txt   2>&1  

 

经过漫长的等待后查看数据,受不了了,直接20W做测试吧:

wpsEBE5.tmp 

wpsEBE6.tmp 

 

 

简单测试索引性能

2.1  在有索引和没有索引的情况下执行查询

 

mysql> select * from test1 limit 10;

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

| id   | num  | pass                             |

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

|    1 |    1 | c4ca4238a0b923820dcc509a6f75849b |

|    2 |    3 | c81e728d9d4c2f636f067f89cc14862c |

|    3 |    4 | eccbc87e4b5ce2fe28308fd9f2a7baf3 |

|    4 |    5 | a87ff679a2f3e71d9181a67b7542122c |

|    5 |    5 | e4da3b7fbbce2345d7772b0674a318d5 |

|    6 |    6 | 1679091c5a880faf6fb5e6087eb1b2dc |

|    7 |   10 | 8f14e45fceea167a5a36dedd4bea2543 |

|    8 |   12 | c9f0f895fb98ab9159f51fd0297e236d |

|    9 |   12 | 45c48cce2e2d7fbdea1afc51c7c6ad26 |

|   10 |   12 | d3d9446802a44259755d38e6d163e820 |

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

10 rows in set (0.00 sec)

 

mysql> reset query cache; ---清空缓存

Query OK, 0 rows affected (0.18 sec)

 

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

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

| num   | pass                             |

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

| 66755 | 1017bfd4673955ffee4641ad3d481b1c |

| 70239 | 334146de1b9346272cb013adf1a35aea |

| 93704 | f67fe69d3660b4d35a731817b538b21d |

| 90594 | 0cce9d48eb96fdf93fbae8640d547b8e |

| 94673 | 171ab172efb24344684eca5b04abffca |

| 51476 | e7beb1dcf073b1d1e700fb02eccaf064 |

| 78604 | 2432fc2efe99899b0ecff8ade0211e7d |

| 62059 | 1cc41f4ab8528178818a29b9ef5fabbb |

| 94777 | 79c3489e2392afd26733d285dee3abd0 |

| 71807 | f0547ecd4e64a31e247c34b64547f812 |

| 62106 | 3e53ae683f8e8c84221db763b30fe907 |

| 74578 | fbbbadb6d1a15c0c924c73b0b0a4b7cb |

| 91069 | 9ba86c2987b9321a45b4dbf1eff6bb4a |

| 71692 | 8ec41a3e649625a55ceafc35f6fa45e8 |

| 72179 | 87b0cba64000c51c883f57274c04519c |

| 96570 | f7c3c4088dfe80933e84ca084fa3524a |

| 70295 | 5a5a84625f44b7e7345b4ea6fde06627 |

| 68758 | 85dbdb1cbb78b9be83ccedd468732e0a |

| 77853 | 16d37a42180158171d57e1cc8122b415 |

| 81193 | c3a8217c9d3a5d9c5e76a77d8f4a8fde |

| 71512 | 4e2598d3fa41ae72d1927b81328dbd51 |

| 87838 | 81b993dae9d5735b0714c325c526aee5 |

| 51719 | d3d2a1a264feb84bd8ba9d0557aafca8 |

| 88469 | d5e390212ea61535b492b740102df78a |

| 92232 | cf50b28ef624912ff106c57ca9be41dc |

| 67030 | a4eacdf08e8fda83c7784c8fd21f7811 |

| 93650 | 9a84af5408986faab11f648a07867d84 |

| 70810 | 31bd7cc9213175d709fcfa2eeb4b202a |

| 77757 | 2d084a4acd512e6314d6e8ae111b8205 |

| 66555 | 2a12b41adeedc754b55ec468d1a41d09 |

| 77727 | 33702a9c691c0f5aaac103d7dd1952eb |

| 92189 | d081111dbdee3c687d1439b444d64004 |

| 90372 | dfdc9e0c03a33349408e99f28d07f899 |

| 91206 | 0610027c7b4268080e7c1c5f04af05a7 |

| 77903 | cb07accc409bbb4c0adc6afb26cf351b |

| 55402 | a1f3a4e959c66a4dd4f330f13ff4d808 |

| 75258 | 9f75e281cbe6072bd91a286e64fb6f0d |

| 88422 | 9bf3f8e2f454487987a4888544f9e1be |

| 76166 | 664a26f366b9ef4988631e95af9b366d |

| 87260 | f19ea2ad04c46f33134d405510650a60 |

| 73856 | 3b4421d0ab0e43c65932c51fb58f593f |

| 89850 | ca355f31b8e517abc70bf477ca77f4ce |

| 84650 | afa8024de2c03966e71d6f94a93b6b93 |

| 63982 | ce9e053a63f6a8aed199bed09f1e498e |

| 92551 | 3cf419e05d85881157b758a01c6ef399 |

| 80764 | af7994b458c40e4a18ec60f5e622e522 |

| 50054 | c3beb22d8bb8a4b874fd7bb8a8914643 |

| 72336 | 5f9f76d679371d223deeda050bdc9d85 |

| 85120 | 218171bd4087237acdcc6d3846b9cda5 |

| 85468 | d38aad5d5676be87eaf6ade964caff4f |

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

50 rows in set (0.15 sec)

 

 

mysql> insert into test2 select * from test1;

Query OK, 225494 rows affected (2.53 sec)

Records: 225494  Duplicates: 0  Warnings: 0

 

mysql>  reset query cache;

Query OK, 0 rows affected (0.00 sec)

 

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

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

| id | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra                 |

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

|  1 | SIMPLE      | test2 | range | idIdx         | idIdx | 5       | NULL |   49 | Using index condition |

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

1 row in set (0.00 sec)

 

mysql>  explain select num,pass   from test1  where id>=50000 and id<50050;

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

| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |

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

|  1 | SIMPLE      | test1 | ALL  | NULL          | NULL | NULL    | NULL | 226851 | Using where |

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

1 row in set (0.00 sec)

 

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                             |

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

| 66755 | 1017bfd4673955ffee4641ad3d481b1c |

| 70239 | 334146de1b9346272cb013adf1a35aea |

| 93704 | f67fe69d3660b4d35a731817b538b21d |

| 90594 | 0cce9d48eb96fdf93fbae8640d547b8e |

| 94673 | 171ab172efb24344684eca5b04abffca |

| 51476 | e7beb1dcf073b1d1e700fb02eccaf064 |

| 78604 | 2432fc2efe99899b0ecff8ade0211e7d |

| 62059 | 1cc41f4ab8528178818a29b9ef5fabbb |

| 94777 | 79c3489e2392afd26733d285dee3abd0 |

| 71807 | f0547ecd4e64a31e247c34b64547f812 |

| 62106 | 3e53ae683f8e8c84221db763b30fe907 |

| 74578 | fbbbadb6d1a15c0c924c73b0b0a4b7cb |

| 91069 | 9ba86c2987b9321a45b4dbf1eff6bb4a |

| 71692 | 8ec41a3e649625a55ceafc35f6fa45e8 |

| 72179 | 87b0cba64000c51c883f57274c04519c |

| 96570 | f7c3c4088dfe80933e84ca084fa3524a |

| 70295 | 5a5a84625f44b7e7345b4ea6fde06627 |

| 68758 | 85dbdb1cbb78b9be83ccedd468732e0a |

| 77853 | 16d37a42180158171d57e1cc8122b415 |

| 81193 | c3a8217c9d3a5d9c5e76a77d8f4a8fde |

| 71512 | 4e2598d3fa41ae72d1927b81328dbd51 |

| 87838 | 81b993dae9d5735b0714c325c526aee5 |

| 51719 | d3d2a1a264feb84bd8ba9d0557aafca8 |

| 88469 | d5e390212ea61535b492b740102df78a |

| 92232 | cf50b28ef624912ff106c57ca9be41dc |

| 67030 | a4eacdf08e8fda83c7784c8fd21f7811 |

| 93650 | 9a84af5408986faab11f648a07867d84 |

| 70810 | 31bd7cc9213175d709fcfa2eeb4b202a |

| 77757 | 2d084a4acd512e6314d6e8ae111b8205 |

| 66555 | 2a12b41adeedc754b55ec468d1a41d09 |

| 77727 | 33702a9c691c0f5aaac103d7dd1952eb |

| 92189 | d081111dbdee3c687d1439b444d64004 |

| 90372 | dfdc9e0c03a33349408e99f28d07f899 |

| 91206 | 0610027c7b4268080e7c1c5f04af05a7 |

| 77903 | cb07accc409bbb4c0adc6afb26cf351b |

| 55402 | a1f3a4e959c66a4dd4f330f13ff4d808 |

| 75258 | 9f75e281cbe6072bd91a286e64fb6f0d |

| 88422 | 9bf3f8e2f454487987a4888544f9e1be |

| 76166 | 664a26f366b9ef4988631e95af9b366d |

| 87260 | f19ea2ad04c46f33134d405510650a60 |

| 73856 | 3b4421d0ab0e43c65932c51fb58f593f |

| 89850 | ca355f31b8e517abc70bf477ca77f4ce |

| 84650 | afa8024de2c03966e71d6f94a93b6b93 |

| 63982 | ce9e053a63f6a8aed199bed09f1e498e |

| 92551 | 3cf419e05d85881157b758a01c6ef399 |

| 80764 | af7994b458c40e4a18ec60f5e622e522 |

| 50054 | c3beb22d8bb8a4b874fd7bb8a8914643 |

| 72336 | 5f9f76d679371d223deeda050bdc9d85 |

| 85120 | 218171bd4087237acdcc6d3846b9cda5 |

| 85468 | d38aad5d5676be87eaf6ade964caff4f |

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

50 rows in set (0.00 sec)

 

 

 

2.2  在有索引和没有索引的情况下新增数据

 

 

 

mysql>  reset query cache;

Query OK, 0 rows affected (0.00 sec)

 

mysql>  insert into test3 select * from test1;

Query OK, 225494 rows affected (1.67 sec)

Records: 225494  Duplicates: 0  Warnings: 0

 

mysql>

 

 

总结

表名

表属性

查询(单位:秒)

插入(单位:秒)

test1

无索引

0.15

 

test2

有索引

0.00

2.53

test3

无索引

 

1.67

 

 

结论:通常情况下,有索引的情况下查询比较快,插入比较慢,所以在大批量的数据导入操作中应该首先删除索引,待数据导入完成后再建立索引,由于深入学习过oracle,感觉在这里说这些似乎是废话,但认真做实验是一种态度,本blog中总有一些你不知道的,o(∩_∩)o 哈哈。

 

 

 

 

 

.............................................................................................................................

本文作者:小麦苗,只专注于数据库的技术,更注重技术的运用

ITPUB BLOG:http://blog.itpub.net/26736162

本文地址: http://blog.itpub.net/26736162/viewspace-1466094/

QQ:642808185 注明:ITPUB的文章标题

.............................................................................................................................

 

 

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


http://www.ppmy.cn/news/178791.html

相关文章

H3C S6520万兆交换机IRF配置指引

第一台交换机的配置 1、 首先进入并关闭万兆堆叠口 注意&#xff1a;S6520系列交换机&#xff0c;每4个端口为一个IRF端口组&#xff0c;所以需要将37-40口都加入IRF端口组&#xff0c;即使只使用两根堆叠线缆&#xff0c;这个很重要&#xff01;&#xff01; [H3C]interface r…

封装axios

前端网络请求封装 前端采用了axios来处理网络请求&#xff0c;为了避免在每次请求时都去判断各种各样的网络情况&#xff0c;比如连接超时、服务器内部错误、权限不足等等不一而足&#xff0c;我对axios进行了简单的封装&#xff0c;这里主要使用了axios中的拦截器功能。 封装后…

行列式和叉积

行列式和叉积 http://www.evernote.com/shard/s146/sh/587e6520-0c63-4171-a249-945cae255c97/83eecfd4d79c74ee06c7d25d41334258

Oracle-PL/SQL中条件顺序判断

目录 一、IF语句 1.1IF-THEN 组合 1.2IF-THEN-ELSE 组合 1.3 IF-THEN-ELSIF 组合 1.4区别/注意 1.4.1IF-THEN-ELSIF 和 IF-THEN-ELSE 的区别 1.4.2注意 1.5嵌套 IF 语句 二、 CASE语句 2.1简单的CASE语句 2.2搜索型CASE语句 2.3嵌套的CASE语句 2.4区别 在 Oracle …

Linux4.6LNMP架构

文章目录 计算机系统5G云计算第五章 LINUX LNMP架构一、安装 Nginx 服务1.关闭防火墙&#xff0c;将安装nginx所需软件包传到/opt目录下2.安装依赖包3.创建运行用户、组4.编译安装Nginx5.添加 Nginx 系统服务6.修改nginx服务配置文件 三、编译安装mysqld 服务1.将安装mysql 所需…

mt9602参数 mt9602电视评测

联发科今日发布了适用于高端智能电视的 MT9602 SoC。该芯片具有更全面的功能&#xff0c;可实现 4K HDR 显示。 该 SoC 拥有 AI-PQ (图像质量)、 AI-AQ (声音质量)、HDMI 2.1a 支持、全局 HDR&#xff0c;AV1 和 AVS2 解码支持等特性&#xff0c;联发科表示能够为实时视频改善图…

【C++】sort函数

std::sort函数是C标准库中的一个算法函数&#xff0c;用于对指定范围内的元素进行排序。它可以对数组、向量&#xff08;std::vector&#xff09;等容器进行排序操作。 std::sort函数的语法如下&#xff1a; template <typename RandomAccessIterator> void sort(Rando…

小米电视2 android版本,小米电视2的配置参数是什么?小米电视2标配有什么?

小米电视2的配置参数是什么 40寸的小米电视2采用全球唯一最先进的十代线原装进口夏普SDP超晶屏&#xff0c;1080p全高清分辨率、5000:1超高静态对比度&#xff0c;拥有世界最顶级画质。40寸的小米电视2采用了顶级硬件配置&#xff0c;性能超强&#xff0c;配置了4核CPU&#xf…