数据结构-索引-实验6:索引优化(MySQL-5.6)

数据结构-索引-实验6:索引优化(MySQL-5.6)

一、实验目的及要求

1、 理解索引优化的相关概念,如:基数、回表;

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

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

1、实验设备:

(1)微型计算机:i7处理器、2G内存
在这里插入图片描述

2、软件系统:

(1)VMware Workstation 15 Player:虚拟机,用于安装Windows 7操作系统。在虚拟机上安装Windows 7,然后再安装MySQL-5.6.35;
在这里插入图片描述

(2)Windows 7操作系统:

(3)MySQL-5.6.35-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(300) 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、插入基础数据

INSERT INTO `user_account` VALUES (null, '1', '1', 1, 1, 1, '备注');
INSERT INTO `user_account` VALUES (null, '2', '2', 2, 2, 1, '备注');
INSERT INTO `user_account` VALUES (null, '3', '3', 3, 3, 1, '备注');
INSERT INTO `user_account` VALUES (null, '4', '4', 4, 4, 1, '备注');
INSERT INTO `user_account` VALUES (null, '5', '5', 5, 5, 0, '备注');

5、插入千万级别数据

-- 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
-- 第一步:执行21次,数据量大概为10485760,为千万级别
INSERT into user_account SELECT null,t.username,t.`password`,t.salt,t.sort,t.state,t.remark FROM `user_account` t; 

-- 数量:10485760   第一次查询时间: 12.48s   第二次查询时间: 1.968s   第三次查询时间: 1.968s
SELECT count(*) FROM user_account t ;

SELECT * FROM user_account t limit 100;

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

-- 第三步:更新remark
-- Affected rows: 943384   时间: 55.905s
update user_account set remark = null where id < 1139965;
-- Affected rows: 1393115  时间: 60.492s
update user_account set remark = null where id > 9485760;

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 index_user_account_remark on user_account ;
drop INDEX index_user_account_username_password on user_account ;

SHOW INDEX FROM user_account;

(二)概念:基数

1、准备

(1)索引准备

-- 时间: 32.383s
ALTER TABLE user_account ADD INDEX index_user_account_username (username);
-- 时间: 55.055s
ALTER TABLE user_account ADD INDEX index_user_account_state (state);

(2)user_account表索引详情:

show index from user_account;

在这里插入图片描述

2、含义

单个列唯一键(distict_keys)的数量叫做基数。执行以下SQL,结果如下图所示:

-- 时间: 90.698s   时间: 26.843s
SELECT COUNT(*),COUNT(DISTINCT username),COUNT(DISTINCT state) FROM user_account;

在这里插入图片描述
user_account表的总行数是10485760,username列的基数是10485760,state列的基数是2,说明state列里面有大量重复值,username列的基数等于总行数,说明 username列没有重复值,相当于主键。

3、测试

(1)先计算数量

-- 时间: 36.815s
SELECT count(*) FROM user_account;
-- 时间: 6.979s
SELECT count(*) FROM user_account t where t.state = 1;
-- 时间: 7.02s
SELECT count(*) FROM user_account t where t.username = '1';

总记录数:10485760

state = 1的记录数:8388608

username = '1’的记录数:1

(2)对应的数据比例

比较项基数总数比例
state = 1458388608/10485760*100%=80%
username = ‘1’151/5*100%=20%

(3)测试点:2个

现在问题来了,假设state和username 列都有索引,那么以下这两个查询

SELECT * FROM user_account t where t.state = 1;
SELECT * FROM user_account t where t.username = '1';

都能命中索引吗?

第一个,where t.state = 1

EXPLAIN extended SELECT * FROM user_account t where t.state = 1;

当数据量很大时,会命中索引。如数据量很大,即使返回数据比例为80%,但还是走了索引。
在这里插入图片描述

当数据量很小时,没有命中索引。如当总记录数为5,where t.state = 1返回4条记录时,没有命中索引。

在这里插入图片描述

注意:

extended:执行Mysql的explain extended的输出会比单纯的explain多一列filtered(MySQL 5.7缺省就会输出filtered),它指返回结果的行占需要读到的行(rows列的值)的百分比。filtered是个非常有用的值。如这里的80指的是80%。

第二个,where t.username = ‘1’

EXPLAIN extended SELECT * FROM user_account t where t.username = '1';

在这里插入图片描述

命中了索引index_user_account_username,因为走索引直接就能找到要查询的记录,所以filtered的值为100。这里的100指的是100%。

4、小结

经测试,返回表中 8.805714%至10.871553% 的数据会走索引,返回超过这个比例数据就使用全表扫描。

估计这个比例只是一个大概的范围,并不是绝对的比例。

详见(五)索引优化的规则:8、范围条件查询可以命中索引

(三)概念:回表

当对一个列创建索引之后,索引会包含该列的键值及键值对应行所在的 rowid。通过索引中记录的 rowid 访问表中的数据就叫回表。

回表次数太多会严重影响 SQL 性能,如果回表次数太多,就不应该走索引扫描,应该直接走全表扫描。

EXPLAIN命令结果中的

using index :使用覆盖索引的时候就会出现。代表从索引中查询。意味着不会回表,通过索引就可以获得主要的数据。

using where:在查找使用索引的情况下,需要回表去查询所需的数据。意味着需要回表取数据。

using index condition:查找使用了索引,但是需要回表查询数据。

using index & using where:查找使用了索引,但是需要的数据都在索引列中能找到,所以不需要回表查询数据

(四)查看索引的使用情况

有些时候虽然数据库有索引,但是并不被优化器选择使用。

SHOW STATUS LIKE 'Handler_read%';

在这里插入图片描述

参数名说明
Handler_read_key如果索引正在工作,Handler_read_key的值将很高。
Handler_read_rnd_next数据文件中读取下一行的请求数。如果正在进行大量的表扫描,值将较高,则说明索引利用不理想。

(五)索引优化的规则:16条

1、返回数据的比例

如果MySQL估计使用索引比全表扫描还慢,则不会使用索引

返回数据的比例是重要的指标,比例越低越容易命中索引。

记住这个范围值——30%,后面所讲的内容都是建立在返回数据的比例在30%以内的基础上。

疑问:比例30%从哪里来?本实验测试的数据表明,大概为8%。

2、前导模糊查询

(1)索引准备

由于当前已经对username建立了索引,故本测试点无需再建立索引。

(2)user_account表索引详情:

show index from user_account;

在这里插入图片描述

(2)测试点:2个

第一,前导模糊查询不能命中索引:

EXPLAIN extended SELECT * FROM user_account t where t.username like '%10000%';

在这里插入图片描述

第二,非前导模糊查询则可以使用索引,可优化为使用非前导模糊查询:

EXPLAIN extended SELECT * FROM user_account t where t.username like '10000%';

在这里插入图片描述

3、隐式转换

数据类型出现隐式转换的时候不会命中索引,特别是当列类型是字符串,一定要将字符常量值用引号引起来

(1)索引准备

由于当前已经对username建立了索引,故本测试点无需再建立索引。

(2)user_account表索引详情:

show index from user_account;

在这里插入图片描述

(2)测试点:2个

第一,出现隐式转换的时候

EXPLAIN extended SELECT * FROM user_account t where t.username  = 1;

在这里插入图片描述

第二,没有出现隐式转换的时候

EXPLAIN extended SELECT * FROM user_account t where t.username  = '1';

在这里插入图片描述

4、最左原则

复合索引的情况下,查询条件不包含索引列最左边部分(不满足最左原则),不会命中符合索引。

(1)索引准备

先暂时删除username和password的索引,接着为username、password列创建复合索引。

-- 时间: 0.088s
drop INDEX index_user_account_username on user_account ;
-- 时间: 0.015s
drop INDEX index_user_account_state on user_account ;

-- 时间: 66.24s
ALTER TABLE user_account ADD INDEX index_user_account_username_password (username,password);

(2)user_account表索引详情:

show index from user_account;

在这里插入图片描述

(3)测试点:3个

第一,根据最左原则,where条件的username,是复合索引index_user_account_username_password中的最左列,所以可以命中:

EXPLAIN extended SELECT * FROM user_account WHERE username='2' AND state=1;

在这里插入图片描述

第二,注意:最左原则并不是说是查询条件的顺序。现在交换一下查询条件中的username和state:

EXPLAIN extended SELECT * FROM user_account WHERE state=1 AND username='2';

在这里插入图片描述

第三,而是查询条件中是否包含索引最左列字段:下面没有包含最左列username,只要password,所以不会命中。

EXPLAIN extended SELECT * FROM user_account WHERE password = '2' AND state=1;

在这里插入图片描述

5、union、in、or

union、in、or 都能够命中索引,建议使用 in。

(1)索引准备

drop INDEX index_user_account_username_password on user_account ;

ALTER TABLE user_account ADD INDEX index_user_account_username (username);
ALTER TABLE user_account ADD INDEX index_user_account_state (state);

(2)user_account表索引详情:

show index from user_account;

在这里插入图片描述

(3)测试点

第一,union:

EXPLAIN extended
SELECT * FROM user_account WHERE username = '1'
UNION ALL
SELECT * FROM user_account WHERE username = '2';

在这里插入图片描述

第二,in:

EXPLAIN extended SELECT * FROM user_account WHERE username IN ('1','2');

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-grQoNEwH-1574875027717)(…\picture\数据结构与算法\数据结构-索引\索引优化-MySQL-5.6\79.png)]

这里看到会走索引。

但是,当数据量很小时,有可能不走索引。因为即使有索引,in后面的值超过一定个数后,就会分析消耗,最后如果判断出消耗时间比走全表扫描还多,则就不走索引。

第三,or:

EXPLAIN extended SELECT * FROM user_account WHERE username ='1' or username ='2';

在这里插入图片描述

查询的CPU消耗:or > in >union

6、or前的条件中列有索引,而后面的列中没有索引

用or分割开的条件,如果or前的条件中列有索引,而后面的列中没有索引,那么涉及到的索引都不会被用到。

(1)索引准备

由于当前已经对username建立了索引,故本测试点无需再建立索引。

(2)user_account表索引详情:

show index from user_account;

在这里插入图片描述

(3)测试点

第一,or前的条件中列有索引,or后面的条件列中没有索引

EXPLAIN extended SELECT * FROM user_account WHERE username ='1' or state = 1;

在这里插入图片描述

因为or后面的条件列中没有索引,那么后面的查询肯定要走全表扫描,

在存在全表扫描的情况下,就没有必要多一次索引扫描增加IO访问。

7、负向条件查询:!=、<>、not in、not exists、not like

负向条件查询不能使用索引,可以优化为 in 查询。

(1)索引准备

由于当前已经对username建立了索引,故本测试点无需再建立索引。

(2)user_account表索引详情:

show index from user_account;

在这里插入图片描述

(3)测试点

负向条件有:!=、<>、not in、not exists、not like 等。

第一,负向条件不能命中缓存:

EXPLAIN extended SELECT * FROM user_account WHERE username !='1' AND username != '2';

在这里插入图片描述

第二,可以优化为 in 查询,但是前提是区分度要高,返回数据的比例在30%以内:

EXPLAIN extended SELECT * FROM user_account WHERE username IN ('1','3','4');

在这里插入图片描述

8、范围条件查询:<、<=、>、>=、between

范围条件查询可以命中索引

(1)索引准备

-- 时间: 0.057s
drop INDEX index_user_account_username on user_account ;
-- 时间: 50.27s
ALTER TABLE user_account ADD INDEX index_user_account_salt (salt);
-- 时间: 51.675s
ALTER TABLE user_account ADD INDEX index_user_account_sort (sort);

(2)user表索引详情:

SHOW INDEX FROM user_account;

在这里插入图片描述

(3)测试点:6个

范围条件有:<、<=、>、>=、between等

第一,范围条件查询可以命中索引:

EXPLAIN extended SELECT * FROM user_account WHERE sort < 1139965;

在这里插入图片描述

第二,但是当数据量达到一定程度时,就不会走索引,而是会全表扫描了。

EXPLAIN extended SELECT * FROM user_account WHERE sort < 1139966;

在这里插入图片描述

第三,使用<=时,数量上又略有不同。

EXPLAIN extended SELECT * FROM user_account WHERE sort <= 1139963;

在这里插入图片描述

第四,但是当数据量达到一定程度时,就不会走索引,而是会全表扫描了。

EXPLAIN extended SELECT * FROM user_account WHERE sort <= 1139964;

在这里插入图片描述

第五,范围列可以用到索引(联合索引必须是最左前缀),但是范围列后面的列无法用到索引,索引最多用于一个范围列,如果查询条件中有两个范围列则无法全用到索引。也就是说,只能用其中一个索引,而且是选择代价最小的作为索引:

sort、salt都是索引。

EXPLAIN extended SELECT * FROM user_account WHERE salt < 100;

在这里插入图片描述

选择代价最小的作为索引,salt返回数量最少,代价最小。

EXPLAIN extended SELECT * FROM user_account WHERE sort < 1139965 AND salt < 100;

在这里插入图片描述

即使交换顺序,也是salt。

EXPLAIN extended SELECT * FROM user_account WHERE salt < 100 AND sort < 1139965;

在这里插入图片描述

第六,如果是范围查询和等值查询同时存在,优先匹配等值查询列的索引:

EXPLAIN extended SELECT * FROM user_account WHERE salt < 100 AND sort = 1139965;

在这里插入图片描述

9、数据库执行计算

(1)索引准备

由于当前已经对sort建立了索引,故本测试点无需再建立索引。

(2)user_account表索引详情:

show index from user_account;

在这里插入图片描述

数据库执行计算不会命中索引

(3)测试点

第一,执行计算:sort < 1000

EXPLAIN extended SELECT * FROM user_account WHERE sort < 1000;

在这里插入图片描述

第二,执行计算:sort+1 < 1000

EXPLAIN extended SELECT * FROM user_account WHERE sort+1 < 1000;

在这里插入图片描述

计算逻辑应该尽量放到业务层处理,节省数据库的 CPU的同时最大限度的命中索引。

10、利用覆盖索引进行查询,避免回表

被查询的列,数据能从索引中取得,而不用通过行定位符 row-locator 再到 row 上获取,即“被查询列要被所建的索引覆盖”,这能够加速查询速度。

(1)索引准备

-- 时间: 0.03s
drop INDEX index_user_account_salt on user_account ;
-- 时间: 0.026s
drop INDEX index_user_account_sort on user_account ;

-- 时间: 65.232s
ALTER TABLE user_account ADD INDEX index_user_account_username (username);

(2)user表索引详情:

SHOW INDEX FROM user_account;

在这里插入图片描述

(3)测试点

第一,因为username字段是索引列,所以直接从索引中就可以获取值,不必回表查询:

EXPLAIN extended SELECT username FROM user_account WHERE username = '1000';

在这里插入图片描述

第二,当查询其他列时,就需要回表查询,这也是为什么要避免SELECT *的原因之一:

EXPLAIN extended SELECT * FROM user_account WHERE username = '1000';

在这里插入图片描述

11、建立索引的列,不允许为 null

(1)索引准备

-- 时间: 0.046s
drop INDEX index_user_account_username on user_account ;

-- 时间: 60.403s
ALTER TABLE user_account ADD INDEX index_user_account_remark (remark);

(2)user表索引详情:

SHOW INDEX FROM user_account;

在这里插入图片描述

(3)测试点

单列索引不存 null 值,复合索引不存全为 null 的值,如果列允许为 null,可能会得到“不符合预期”的结果集,所以,请使用 not null 约束以及默认值。

第一,IS NULL可以命中索引:

EXPLAIN extended SELECT * FROM user_account where remark IS NULL;

在这里插入图片描述

第二,IS NOT NULL不能命中索引:

EXPLAIN extended SELECT * FROM user_account where remark IS NOT NULL;

在这里插入图片描述

虽然IS NULL可以命中索引,但是NULL本身就不是一种好的数据库设计,应该使用NOT NULL 约束以及默认值

12、更新十分频繁的字段上不宜建立索引

因为更新操作会变更B+树,重建索引。这个过程是十分消耗数据库性能的。

13、区分度不大的字段上不宜建立索引

类似于性别这种区分度不大的字段,建立索引的意义不大。因为不能有效过滤数据,性能和全表扫描相当。

另外返回数据的比例在30%以外的情况下,优化器不会选择使用索引。

疑问:比例30%从哪里来?本实验测试的数据表明,大概为8%。

14、业务上具有唯一特性的字段,即使是多个字段的组合,也必须建成唯一索引

虽然唯一索引会影响insert速度,但是对于查询的速度提升是非常明显的。另外,即使在应用层做了非常完善的校验控制,只要没有唯一索引,在并发的情况下,依然有脏数据产生。

15、多表关联时,要保证关联字段上一定有索引

16、创建索引时避免以下错误观念

  • 索引越多越好,认为一个查询就需要建一个索引。
  • 宁缺勿滥,认为索引会消耗空间、严重拖慢更新和新增速度。
  • 抵制唯一索引,认为业务的唯一性一律需要在应用层通过“先查后插”方式解决。
  • 过早优化,在不了解系统的情况下就开始优化。

五、实验总结(包括心得体会、问题回答及实验改进意见)

1、通过本次实验,提高了SQL性能优化的意识。

2、了解了索引对SQL的性能影响很大。

3、学会了使用EXPLAIN命令分析一下SQL

4、理解了基数、回表的概念

5、掌握了索引优化的规则。

六、参考

《深入浅出MySQL》

MySQL——索引优化实战

©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页