「Mysql」Mysql建索引和不建索引的效率对比

1.数据准备

准别好100w+数据

2、不建索引查询执行sql

-- 查看表索引
show index from user_info;
-- 根据用户名等值查询
select * from user_info where username = 'user0335528';
-- 根据用户名等值查询的执行计划
explain select * from user_info where username = 'user0335528';

查询表索引

不建立索引查询效率

可以看到mssql 临时表，目前只有主键索引，接下来看看不建索引时的查询效率

不建立索引的执行计划

从执行计划可以看出，我们的查询进行了全表扫描，扫描的行数特别多，并且是回表查看。先不看这些含义，我们先进行建立索引后的查询对比。

3、建立普通索引查询执行sql

-- 创建普通索引
alter table user_info add index idx_ui_username(username);
-- 根据用户名等值查询
select * from user_info where username = 'user0335528';
-- 根据用户名等值查询的执行计划
explain select * from user_info where username = 'user0335528';

创建普通索引

创建普通索引耗时

普通索引执行计划

4、建立唯一索引查询

我这里创造的数据存在重复数据，唯一索引使用主键索引来代替

执行sql

-- 删除原来的普通索引
alter table user_info drop index idx_ui_username;
-- 创建唯一索引
alter table user_info add unique index idx_ui_username(username);
-- 根据用户名等值查询(如果您的用户名是唯一的话)
select * from user_info where username = 'user0335528';
-- 根据用户名等值查询的执行计划(如果您的用户名是唯一的话)
explain select id, username, nickname from user_info where username like 'user033552%';
-- 根据用户名等值查询(如果您的用户名不是唯一的话，使用主键尝试)
select * from user_info where id = 1000001;
-- 根据用户名等值查询的执行计划(如果您的用户名不是唯一的话，使用主键尝试)
explain select id, username, nickname from user_info where id = 1000001;

因为存在重复数据，使用主键代替演示

5、执行计划说明select_typetypepossible_keys

查询是能用到的索引，并不一定真的会用到

key

查询时真正用到的索引，显示索引名称

rows

查询优化器根据统计信息，估计查询到结果要扫描多少行数据。原则上越少越好

key_len

索引的字节数量

extra6、回表查询与索引覆盖什么是回表查询？

因为辅助索引的叶子节点存储的是主键值和索引字段值，通过辅助索引无法直接定位记录，通常需要扫描两遍索引树。先通过辅助索引定位主键值，再通过聚簇索引定位记录。

辅助索引也叫二级索引，就是跟主键设置一个映射关系。idx_ui_username 就是建立username和id的映射关系，如果需要查询处理id和username，就需要通过username先找到id, 再通过id回表查询到对应的其他字段。

什么是索引覆盖？

只需要在一颗索引树上就能获取SQL所需的所有列数据，无需回表，速度更快，这就叫做索引覆盖。

实现索引覆盖常见的方法就是，将被查询字段，建立成组合索引。

7、哪些情况索引不生效？

mysql在使用like查询时，索引可以被使用到，只有把%写在后面才会使用到索引

-- 创建索引
alter table user_info add index idx_ui_username(username);
explain select * from user_info where username like '%user03355%';  -- 不起作用
explain select * from user_info where username like 'user03355%';  -- 起作用
explain select * from user_info where username like '%user03355';  -- 不起作用

8、总结

MySQL的查询优化主要就是建立索引，当然索引的过滤兄啊过需要比较高，不然100w+数据，通过男女区分，各自50w+数据，通过性别建立的索引将索然无味。

那么索引是越多越好吗？

当然不是，可以看到上面我们创建索引耗时是比较高的，索引对于频繁修改的表，创建索引反而会降低效率，大量查询比较适合创建索引。

那么既然创建索引如此耗时，那么大表数据迁移的时候执行删除部分数据迁移到历史表怎么做呢？

Mysql官方手册告诉我们删除数据的速度和创建索引的数量是成正比的

大表数据迁移/删除方案

1、先删除索引（大概耗时3分钟）

2、删除无用的数据（大概耗时2分钟）

3、删除完成后重新创建索引（数据变少，建索引速度非常快，约10分钟）

（编辑：鹰潭站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!