Mysql索引的使用

前言

本文是由本人全部手写，因为看了文档，写好了忘了保存，所有全部手写一遍，可能出错，后续还有一些between和in、or之类的操作，想起来就写吧

一、索引类型

引用：mysql中explain的type的解释以及常见索引失效的情况
explain中的type类型，速度由快到慢
system:只有一列数据
constant:通常情况下，如果将一个主键放置到where后面作为条件查询，mysql优化器就能把这次查询优化转化为一个常量。至于如何转化以及何时转化，这个取决于优化器。
ref_eq:使用了主键或者唯一性索引
ref:查找条件列使用了索引而且不为主键和unique
range: between，and以及’>’,’<'外，in和or也是索引范围扫描 有时in会失效
index: 全表的索引搜索
All: 全表搜索，唯一没用索引

二、索引实战

1 表结构（结构介绍）

表结构：

留着代码方便创建用

CREATE TABLE `sys_user` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `username` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户名',
  `nickname` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL 
  `entrance` int NOT NULL COMMENT '账号端口标记(1:商家 2Saas  4 技师  )',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `login_name` (`username`,`entrance`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=172 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='用户信息表';

索引：

一会用到的查询代码：

select id from sys_user where id = '1';
select username from sys_user where id = '1';
select username from sys_user where username ='root' and entrance = '1';
select username from sys_user where username ='root';
select username from sys_user where entrance = '1';
select nickname from sys_user where username ='root';
select username from sys_user where username like 'ro%';
select username from sys_user where username like '%ot';
select username from sys_user where username like '%ro%';
select nickname from sys_user where username like '%ro%';
select nickname from sys_user where username like 'ro%';

2 索引（实战部分）

2.1 主键搜索

type:const

select id from sys_user where id = '1';

2.2 主键搜索 搜索其他列信息

type:const

select username from sys_user where id = '1';

2.3 联合索引

type:ref

select username from sys_user where username ='root' and entrance = '1';

2.4 联合索引第一个key

type:ref

select username from sys_user where username ='root';

2.5 联合索引第二个key

type:index
只用到了第一行username的全部索引

select username from sys_user where entrance = '1';

2.6 联合索引 搜索其他列信息

type:ref
还是用到了第一列username的索引

select nickname from sys_user where username ='root';

2.7 like ‘xxx%’

引言：因为网上看到很多有人对 like % 之类有很多不一样的解释，所以自己试试
type:range
因为%在后面，所有跟网上大部分人说的合理

select username from sys_user where username like 'ro%';

2.8 like ‘%xxx’

type:index
因为百分比在前面，索引无效，跟网上说的大部分一致，但也是有index效果的

select username from sys_user where username like '%ot';

2.9 like ‘%xxx%’

网上有些说 ‘%xxx%’ 能解决 ‘%xxx’ 无法利用索引，我们来验证一下
type:index
结果：无法解决，前百分比就是无法利用索引

select username from sys_user where username like '%ro%';

2.10 like ‘%xxx%’ 搜索其他列信息

type:All
按理说应该也是有引用index的，不知为何一旦引用其他列就无法使用，可能是优化器干脆不用index了，直接改成搜nickname更快

select nickname from sys_user where username like '%ro%';

2.11 like ‘%xxx’ 搜索其他列信息

但这里应该注意，与上文对比，如果是搜索的 ‘%xxx’ 的话，是有范围索引的
type:range

select nickname from sys_user where username like 'ro%';

三、结论

1.事实证明 like ‘%xxx’ 是范围搜索，使用’%xxx%'不能解决’xxx%'导致的索引失效问题