MySQL 模拟10条数据 mysql实验6

文章目录

• 数据结构-索引-实验6：索引优化（MySQL-5.6）

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

• 1、实验设备：
• 2、软件系统：

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

• （一）数据准备

• 1、新建表结构
• 2、检查

• （1）表结构
• （2）索引
• （3）表信息

• 4、插入基础数据
• 5、插入千万级别数据
• 6、索引建立与删除SQL

• （二）概念：基数

• 1、准备
• 2、含义
• 3、测试
• 
  
• 4、小结

• （三）概念：回表
• （四）查看索引的使用情况
• （五）索引优化的规则：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、创建索引时避免以下错误观念

• 五、实验总结（包括心得体会、问题回答及实验改进意见）
• 六、参考

数据结构-索引-实验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 = 1	4	5	8388608/10485760*100%=80%
username = ‘1’	1	5	1/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——索引优化实战