2千万记录的Mysql表操作

最新推荐文章于 2024-09-10 09:04:24 发布

暗夜之雪love

最新推荐文章于 2024-09-10 09:04:24 发布

阅读量465

点赞数

CC 4.0 BY-SA版权

分类专栏： MySql 文章标签： mysql 大数据mysql 索引

本文链接：https://blog.youkuaiyun.com/shangulidexiao/article/details/78761992

MySql 专栏收录该内容

1 篇文章

订阅专栏

本文通过具体案例展示了MySQL数据库中索引的重要性。通过对一个大型数据表进行查询优化，包括删除和重建索引，展示了如何显著提升查询速度。此外，还讨论了在不同场景下索引对性能的影响。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

软银件环境：
操作系统：VMPlay12 CentOS Linux release 7.3.1611 (Core)
MySql版本： 5.5.52-MariaDB
内存大小：4G
CPU型号：I5

数据库表定义语句：

CREATE TABLE `desgin_admin` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `userName` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '用户名',
  `password` VARCHAR(255) DEFAULT '' COMMENT '密码',
  `trueName` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '姓名',
  `photo` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '头像',
  `email` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '邮箱',
  `mobile` CHAR(11) NOT NULL DEFAULT '' COMMENT '电话',
  `lastIp` VARCHAR(16) NOT NULL DEFAULT '0.0.0.0' COMMENT '最后一次登录ip',
  `lastTime` INT  NOT NULL DEFAULT '0' COMMENT '最后一次登录时间'
  `createId` INT  NOT NULL DEFAULT '0' COMMENT '创建者ID',
  `createTime` INT  NOT NULL DEFAULT '0' COMMENT '创建时间',
  `updateTime` INT  NOT NULL DEFAULT '0' COMMENT '更新时间',
  `status` TINYINT NOT NULL DEFAULT '0' COMMENT '状态',
  `adder` INT NOT NULL DEFAULT '0' COMMENT '添加者',
  `orderBy` INT NOT NULL DEFAULT '0' COMMENT '排序',
  `isDel` INT NOT NULL DEFAULT '0' COMMENT '是否删除'
  PRIMARY KEY (`id`),
  KEY `userName` (`userName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表'

用PHP脚本生成虚拟的数据并插入表中

SHOW TABLE STATUS LIKE 'desgin_admin'\G

显示结果下表1：

列名	值	备注
Name:	desgin_admin	表名
Engine:	InnoDB	存储引擎
Version:	10	版本
Row_format:	Compact	行格式
Rows:	20170235	总行数
Avg_row_length:	169	字段平均长度
Data_length:	3422552064	整个表的数据量 3.1875GB
Max_data_length:	0
Index_length:	784334848	索引占用磁盘的空间大小0.73046875.GB
Data_free:	8388608
Auto_increment:	20382601	下一个Auto_increment的值
Create_time:	2017-08-29 10:13:12	表的创建时间
Update_time:	NULL	表的修改时间
Check_time:	NULL	使用 check table 或myisamchk工具检查表的最近时间
Collation:	utf8_general_ci	表的默认字符集和字符排序规则
Checksum:		如果启用，则对整个表的内容计算时的校验和
Create_options:		指表创建时的其他所有选项
Comment:		注释

2. 查询测试
（1）统计表的总数

SELECT count(*) FROM `desgin_admin`;

查询耗时：
统计表的总数
（2）查询一个用户名为q6_9yddvhxqazyms的记录

SELECT * FROM desgin_admin WHERE userName='_p7t6lu7eokqy';

查询耗时：
这里写图片描述

（3）删除userName字段的索引

DROP INDEX `userName` ON `desgin_admin`;

查询耗时：
删除userName字段的索引情况

（4）再次执行查询测试（2）

SELECT * FROM desgin_admin WHERE userName='_p7t6lu7eokqy';

查询耗时：
再次执行查询测试（2）
注：用0.6G换回从2分钟到0秒的查询的速度

（5）把查询测试（3）删除的索引添加回来

CREATE INDEX `userName` ON `desgin_admin`(`userName`);

查询耗时：

增加索引后表1的Index_length变为0.4629GB。

（6）未添加索引时按createTime排序顺序排查找第一个

SELECT * FROM desgin_admin ORDER BY `createTime` limit 1;

查询耗时：等了好久都没有查出来

（7）添加索引时按createTime排序

CREATE INDEX `createTime` ON `desgin_admin`(`createTime`);

SELECT * FROM desgin_admin ORDER BY `createTime` limit 1;

查询耗时：
这里写图片描述
增加索引后表1的Index_length变为0.7234GB。

（8）插入一条记录

INSERT INTO `desgin_admin` (`userName`,'createTime') VALUES ('HANJIAN123',150402942);

插入耗时：
插入一条记录

（9）更新一条记录

UPDATE `desgin_admin` SET userName='wulalala128' WHERE userName='HANJIAN123';

更新耗时：
这里写图片描述