2千万记录的Mysql表操作

软银件环境 ：
操作系统：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='用户表'

1. 用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`;

查询耗时：

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

SELECT * FROM desgin_admin WHERE userName='_p7t6lu7eokqy';

查询耗时：

注：用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';

更新耗时：