文章目录
- 数据结构-索引-实验7:索引优化(MySQL-8.0)
-
- 一、实验目的及要求
- 二、实验环境及相关情况(包含使用软件、实验设备、主要仪器及材料等)
- 三、实验内容
- 四、实验步骤及结果(包含简要的实验步骤流程、结论陈述,可附页)
-
- (一)数据准备
- (二)概念:基数
- (四)查看索引的使用情况
- (五)索引优化的规则:16条
-
- 1、返回数据的比例
- 2、前导模糊查询
- 3、隐式转换
- 4、最左原则
- 5、union、in、or
- 6、or前的条件中列有索引,而后面的列中没有索引
- 7、负向条件查询:!=、<>、not in、not exists、not like
- 8、范围条件查询:<、<=、>、>=、between
- 9、数据库执行计算
- 10、利用覆盖索引进行查询,避免回表
- 11、建立索引的列,不允许为 null
- 12、更新十分频繁的字段上不宜建立索引
- 13、区分度不大的字段上不宜建立索引
- 14、业务上具有唯一特性的字段,即使是多个字段的组合,也必须建成唯一索引
- 15、多表关联时,要保证关联字段上一定有索引
- 16、创建索引时避免以下错误观念
- 五、实验总结(包括心得体会、问题回答及实验改进意见)
- 六、参考
数据结构-索引-实验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