数据结构-索引-实验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,`salt`=id,`sort`=id ;

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

-- 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_username_password (username,password);

drop INDEX index_user_account_username on user_account ;
drop INDEX index_user_account_username_password on user_account ;

SHOW INDEX FROM user_account;

（二）一般步骤

1、第一，了解各种 SQL 的执行频率

命令：show [session|global] status

show status;
show session status;
show global status;

MySQL 客户端连接成功后，通过【 show [session|global] status 】命令可以提供服务器状态信息，也可以在操作系统上使用 mysqladmin extended-status 命令获得这些消息。

show [session|global] status 可以根据需要加上参数“session”或者“global”来显示 session 级(当前连接)的统计结果和 global 级(自数据库上次启动至今)的统计结果。如果不写，默认使用参数是“session”。

（1）Com_xxx 表示每个 xxx 语句执行的次数，我们通常比较关心的是以下几个统计参数。

参数	说明
Com_select	执行 select 操作的次数，一次查询只累加 1
Com_insert	执行 INSERT 操作的次数，对于批量插入的 INSERT 操作，只累加一次
Com_update	执行 UPDATE 操作的次数
Com_delete	执行 DELETE 操作的次数

上面这些参数对于所有存储引擎的表操作都会进行累计。

（2）下面这几个参数只是针对InnoDB 存储引擎的，累加的算法也略有不同。

参数	说明
Innodb_rows_read	select 查询返回的行数
Innodb_rows_inserted	执行 INSERT 操作插入的行数
Innodb_rows_updated	执行 UPDATE 操作更新的行数
Innodb_rows_deleted	执行 DELETE 操作删除的行数

通过以上几个参数，可以很容易地了解当前数据库的应用是以插入更新为主还是以查询操作为主，以及各种类型的 SQL 大致的执行比例是多少。对于更新操作的计数，是对执行次数的计数，不论提交还是回滚都会进行累加。

（3）对于事务型的应用，通过 Com_commit 和 Com_rollback 可以了解事务提交和回滚的情况，对于回滚操作非常频繁的数据库，可能意味着应用编写存在问题。

参数	说明
Com_commit	事务提交的情况
Com_rollback	事务回滚的情况

（4）此外，以下几个参数便于用户了解数据库的基本情况。

参数	说明
Connections	试图连接 MySQL 服务器的次数
Uptime	服务器工作时间
Slow_queries	慢查询的次数

2、第二，定位执行效率较低的 SQL 语句

可以通过以下两种方式定位执行效率较低的 SQL 语句。

• 事后：通过慢查询日志定位那些执行效率较低的 SQL 语句，用–log-slow-queries[=file_name]选项启动时，mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件。
• 实时：慢查询日志在查询结束