索引的类型
主键索引
- 创建表的时候指定主键
-- 创建表
create table t_user(
id varchar(32) not null,
nickname varchar(20) not null,
age tinyint not null default 0,
gender tinyint not null default 2,
birthday date not null default ‘1997-01-01’,
id_card char(18) not null default '',
remark varchar(512) not null default '',
primary key(id)
);
-- 查看t_user表中的索引
show index in t_user;
- 在创建完表之后指定
-- 创建没有指定主键的表
create table t_demo(id varchar(32) not null, name varchar(20) not null);
-- 设置主键
alter table t_demo add primary key(id);
-- 查看
show index in t_demo;
唯一索引
唯一索引的命名一般以uniq作为前缀，下划线风格。
alter table t_user add unique uniq_nickname(nickname);
show index in t_user;
普通索引
普通索引的命名一般以idx作为前缀，下划线风格。
alter table t_user add index idx_age(age);
组合索引
-- 创建身份证号、出生年月、性别的组合索引
alter table t_user add index idx_idcard_birth_gender(id_card, birthday, gender);
全文索引
MyISam才支持全文索引
alter table t_user add fulltext index idx_remark(remark);
索引未命中场景
- 有隐式转换
explain select * from t_user where mobile = 15333333333;
mobile是varchar类型的，但是查询并没有带单引号（''），会导致索引未命中，正确应该如下：
explain select * from t_user where mobile = '15333333333';
- 有负向查询
# 主键id上的操作
explain select * from t_user where id = 1;
explain select * from t_user where id <> 1;
# 在属性age上的操作
explain select * from t_user where age = 23;
explain select * from t_user where age != 23;
执行完之后，我们可以发现主键id上的负向查询用到了索引，不过执行计划中的type是range。
在age上的负向查询就没有命中索引。
- 有前导模糊查询
explain select * from t_user where mobile like '%185';
从执行计划中我们能看到，索引没有命中，是全表扫描的。
但是下面这样的插叙是能命中索引的：
explain select * from t_user where mobile like '185%';
- 查询条件字段上有计算
explain select * from t_user where age - 1 = 22;
explain select * from t_user where pow(age, 2) = 200;
explain select * from t_user where mobile = concat(mobile, 'm');
上面的是不能命中索引的
