1.数据准备

准别好100w+数据

2、不建索引查询

执行sql

-- 查看表索引
show index from user_info;

-- 根据用户名等值查询
select * from user_info where username = 'user0335528';

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

查询表索引

不建立索引查询效率

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

不建立索引的执行计划

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

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_type

• simple 语句不包含子查询或union
• primary 此查询是最外层的查询
• union 表示查询时union的第二个或者或许查询
• dependency union union中的第二个或后续查询使用了外面查询结果
• union result union查询结果
• subquery子查询
• dependency subquery select子查询依赖外层查询结果

type

• ALL 全表扫描 性能最差
• index 表示基于索引全表扫描 先扫描索引再全表扫描
• range 使用索引范围查询
• ref 使用非唯一索引进行等值查询
• eq_ref 多表join，后面只能匹配前面表的一行结果，一对一查询
• const 使用主键或唯一索引等值查询 常量查询
• NULL 不访问表，速度最快

possible_keys

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

key

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

rows

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

key_len

索引的字节数量

extra

• Using Where 通过回表查询数据
• Using Index 通过索引，索引就可以满足结果了。
• Using filesort 查询结果需要额外排序，量小在内存，量大在磁盘，如果有Using filesort建议优化
• Using temprorqry 查询时使用了临时表，一般出现于去重，分组等

6、回表查询与索引覆盖

什么是回表查询？

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

辅助索引也叫二级索引，就是跟主键设置一个映射关系。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分钟）