mysql性能测试(索引)
2023-09-27 14:25:09 时间
首先,使用Talend随机生成一千万条数据:
数据库表中现在有1千万+的数据:
mysql> select count(*) from zhangchao; +----------+ | count(*) | +----------+ | 10040005 | +----------+ 1 row in set (2.50 sec)
不加索引的情况下,查询一条记录的时间为3.02s
mysql> select * from zhangchao where x = "bq2i2T"; +--------+--------+ | x | y | +--------+--------+ | bq2i2T | DRt5Mx | +--------+--------+ 1 row in set (3.02 sec) mysql> show create table zhangchao; +-----------+------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-----------+------------------------------------------------------------------------------------------------------------------------------------+ | zhangchao | CREATE TABLE `zhangchao` ( `x` varchar(100) DEFAULT NULL, `y` varchar(100) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-----------+------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
给表zhangchao的x列创建索引,耗时45.43s:
mysql> alter table zhangchao add index (x); Query OK, 0 rows affected (45.43 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table zhangchao; +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------+ | zhangchao | CREATE TABLE `zhangchao` ( `x` varchar(100) DEFAULT NULL, `y` varchar(100) DEFAULT NULL, KEY `x` (`x`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
在执行同样的查询,耗时0.09s:
mysql> select * from zhangchao where x = "bq2i2T"; +--------+--------+ | x | y | +--------+--------+ | bq2i2T | DRt5Mx | +--------+--------+ 1 row in set (0.09 sec)
和没有加索引的性能差别:3.02/0.09 = 33.555倍
相关文章
- 面试官问:mysql中时间日期类型和字符串类型的选择
- mysql insert返回主键
- mysql查看表结构
- MYSQL事务的开启与提交
- mysql时间截取函数和实现数据累加
- 自动化测试之获取mysql中的数据
- MySQL性能测试方案设计
- Go 连接 mysql 数据库的简单测试.
- MySQL<数据库的高级操作>
- sysbench对自装MySQL数据库进行基准测试
- MySQL的InnoDB多线程
- 2022-10-08 mysql-使用gunit测试-记录
- 2023-01-03 mysql列存储引擎-load-data和insert-select测试
- Python接口测试之对MySQL/unittest框架/Requests 的操作
- MySQL day7 主从复制
- 常用Mysql查询语句
- 软件测试技能,JMeter压力测试教程,JDBC配置连接mysql数据库(十)
- Mysql 压力测试-sysbench
- xampp集成包如何重置mysql的密码
- linux环境安装mysql,以及mysql基本的终端操作命令
- Mysql创建测试大量测试数据