数据结构-索引-实验5：索引优化基础

数据结构-索引-实验5：索引优化基础

一、实验目的及要求

1、了解SQL优化的一般步骤

2、了解定期分析、检查、优化表。

3、掌握常用 SQL 的优化。

二、实验环境及相关情况（包含使用软件、实验设备、主要仪器及材料等）

1、实验设备：

（1）微型计算机：i7处理器、2G内存

2、软件系统：

（1）VMware Workstation 15 Player：虚拟机，用于安装Windows 7操作系统。在虚拟机上安装Windows 7，然后再安装MySQL-8.0.13-winx64；

（2）Windows 7操作系统：

（3）MySQL-8.0.13-winx64：

（4）Navicat Premium 12：数据库管理工具。

三、实验内容

1、数据准备。

2、一般步骤 。

3、定期分析、检查、优化表。

4、常用 SQL 的优化。

四、实验步骤及结果（包含简要的实验步骤流程、结论陈述，可附页）

（一）数据准备

1、新建表结构

-- 时间: 0s
SET FOREIGN_KEY_CHECKS=0;

-- 时间: 0s
DROP TABLE IF EXISTS `user_account`;

-- 时间: 0.054s
CREATE TABLE `user_account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT NULL COMMENT '账号',
  `password` varchar(50) DEFAULT NULL COMMENT '密码',
  `salt` int(11) DEFAULT 0 COMMENT '盐值',
  `sort` int(11) DEFAULT 0 COMMENT '排序',
  `state` int(1) DEFAULT '1' COMMENT '状态：0无效1有效',
  `remark` VARCHAR(100) DEFAULT null COMMENT '备注',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT;

-- 时间: 0s
SET FOREIGN_KEY_CHECKS=1;

2、检查

（1）表结构

desc user_account ;

（2）索引

show index from user_account;

（3）表信息

show table status from test where name='user_account';

4、插入基础数据

-- Affected rows: 1 时间: 0.521s 
INSERT INTO `user_account` VALUES (null, '1', '1', 1, 1, 1, '备注');
-- Affected rows: 1 时间: 0.018s
INSERT INTO `user_account` VALUES (null, '2', '2', 2, 2, 1, '备注');
-- Affected rows: 1 时间: 0.016s
INSERT INTO `user_account` VALUES (null, '3', '3', 3, 3, 1, '备注');
-- Affected rows: 1 时间: 0.051s
INSERT INTO `user_account` VALUES (null, '4', '4', 4, 4, 1, '备注');
-- Affected rows: 1 时间: 0.017s
INSERT INTO `user_account` VALUES (null, '5', '5', 5, 5, 0, '备注');

5、插入千万级别数据

-- ------------------ MySQL-8.0
-- Affected rows: 5       时间: 0.066s
-- Affected rows: 10      时间: 0.04s
-- Affected rows: 20      时间: 0.056s
-- Affected rows: 40      时间: 0.025s
-- Affected rows: 80      时间: 0.018s
-- Affected rows: 160     时间: 0.014s
-- Affected rows: 320     时间: 0.095s
-- Affected rows: 640     时间: 0.077s
-- Affected rows: 1280    时间: 0.094s
-- Affected rows: 2560    时间: 0.304s
-- Affected rows: 5120    时间: 0.302s
-- Affected rows: 10240   时间: 0.959s
-- Affected rows: 20480   时间: 0.677s
-- Affected rows: 40960   时间: 1.481s
-- Affected rows: 81920   时间: 4.912s
-- Affected rows: 163840  时间: 6.598s
-- Affected rows: 327680  时间: 7.989s
-- Affected rows: 655360  时间: 11.656s
-- Affected rows: 1310720 时间: 16.586s
-- Affected rows: 2621440 时间: 34.713s
-- Affected rows: 5242880 时间: 86.015s
-- ------------------ MySQL-8.0
-- 第一步：执行21次，数据量大概为10485760，为千万级别
INSERT into user_account SELECT null,t.username,t.`password`,t.salt,t.sort,t.state,t.remark FROM `user_account` t; 

-- MySQL-8.0：数量：10485760   第一次查询时间: 9.837s   第二次查询时间: 5.44s    第三次查询时间: 3.592s 时间: 2.626s 时间: 2.634s 时间: 2.656s
SELECT count(*) FROM user_account t ;

-- 第二步：更新username、password、salt和sort
-- MySQL-8.0：Affected rows: 10485755 时间: 479.257s
update user_account set `username` = id,`password` = id,`s