数据结构-索引-实验7：索引优化（MySQL-8.0）

数据结构-索引-实验7：索引优化（MySQL-8.0）

一、实验目的及要求

1、 理解索引优化的相关概念，如：基数、回表；

2、 掌握索引优化的规则；

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

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、索引优化的规则。

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

（一）数据准备

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 ;

MySQL-5.6：

说明：

MySQL-8.0：

说明：

（2）索引

show index from user_account;

MySQL-5.6：

MySQL-8.0：

（3）表信息

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

MySQL-5.6：

MySQL-8.0

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-5.6
-- Affected rows: 5        时间: 0.001s   
-- Affected rows: 10       时间: 0s       
-- Affected rows: 20       时间: 0.001s   
-- Affected rows: 40       时间: 0s      
-- Affected rows: 80       时间: 0.001s   
-- Affected rows: 160      时间: 0.001s
-- Affected rows: 320      时间: 0.003s
-- Affected rows: 640      时间: 0.003s
-- Affected rows: 1280     时间: 0.007s
-- Affected rows: 2560     时间: 0.015s
-- Affected rows: 5120     时间: 0.024s
-- Affected rows: 10240    时间: 0.044s
-- Affected rows: 20480    时间: 0.077s
-- Affected rows: 40960    时间: 0.181s
-- Affected rows: 81920    时间: 0.421s
-- Affected rows: 163840   时间: 0.74s
-- Affected rows: 327680   时间: 1.592s
-- Affected rows: 655360   时间: 2.406s
-- Affected rows: 1310720  时间: 5.578s
-- Affected rows: 2621440  时间: 9.642s
-- Affected rows: 5242880  时间: 17.658s
-- ------------------ MySQL-5.6

-- ------------------ 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-5.6：数量：10485760   第一次查询时间: 12.48s   第二次查询时间: 1.968s   第三次查询时间: 1.968s
-- 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-5.6：Affected rows: 10485755 时间: 125.239s
-- MySQL-8.0：Affected rows: 10485755 时间: 479.257s
update user_account set `username` = id,`password` = id,`salt`=id,`sort`=id ;

-- 第三步：更新remark
-- MySQL-5.6：Affected rows: 943384   时间: 55.905s
-- MySQL-8.0：Affected rows: 943384   时间: 35.478s
update user_account set remark = null where id < 1139965;

-- MySQL-5.6：Affected rows: 1393115  时间: 60.492s
-- MySQL-8.0：Affected rows: 1393115  时间: 31.097s
update user_account set remark = null where id > 9485760;

SELECT * FROM user_account t limit 100;

6、索引建立与删除SQL

后面的实验环节会经常用到

ALTER TABLE user_account ADD INDEX index_user_account_username (username);
ALTER TABLE user_account ADD INDEX index_user_account_salt (salt);
ALTER TABLE user_account ADD INDEX index_user_account_sort (sort);
ALTER TABLE user_account ADD INDEX index_user_account_state (state);
ALTER TABLE user_account ADD INDEX index_user_account_remark (remark);
ALTER TABLE user_account ADD INDEX index_user_account_username_password (username,password);

drop INDEX index_user_account_username on user_account ;
drop INDEX index_user_account_salt on user_account ;
drop INDEX index_user_account_sort on user_account ;
drop INDEX index_user_account_state on user_account ;
drop INDEX inde