20200103-数据库索引

联合索引

联合索引： 多个字段上建立的索引，能够加速复核查询条件的检索

select uid, login_time from t_user where 
login_name=? and passwd=?

登录业务需求----可以建立(login_name, passwd)的联合索引

• 联合索引能够满足最左侧查询需求
  （最左侧查询需求，并不是指SQL语句的写法必须满足索引的顺序）

例如(a, b, c)三列的联合索引，能够加速a | (a, b) | (a, b, c) 三组查询需求。

• 这也就是为何不建立(passwd, login_name)这样联合索引的原因，业务上几乎没有passwd的单条件查询需求，而有很多login_name的单条件查询需求

SQL语句里，where条件中的and前后的顺序，是不会影响索引的命中

select uid, login_time from t_user where
passwd=? and login_name=?

仍然可以命中(login_name, passwd)这个联合索引

约束主键与唯一索引约束

• PRIMARY KEY and UNIQUE Index Constraints

触发约束检测的时机：insert / update

• 当检测到违反约束时，不同存储引擎的处理动作是不一样的。
• 如果存储引擎支持事务，SQL会自动回滚.

create table t1 (
id int(10) primary key
)engine=innodb;

insert into t1 values(1);
insert into t1 values(1);//第二条insert会因为违反约束，而导致回滚

举例：

• 如果存储引擎不支持事务，SQL的执行会中断
• 此时可能会导致后续有符合条件的行不被操作，出现不符合预期的结果。

举例：

create table t2 (
id int(10) unique
)engine=MyISAM;

insert into t2 values(1);
insert into t2 values(5);
insert into t2 values(6);
insert into t2 values(10);

update t2 set id=id+1;

update执行后===》正确结果输出是：2，5，6，10

• 第一行id=1，加1后，没有违反unique约束，执行成功；
• 第二行id=5，加1后，由于id=6的记录存在，违反uinique约束，SQL终止，修改失败；
• 第三行id=6，第四行id=10便不再执行；

===》update语句，部分执行成功，部分执行失败。

• 使用InnoDB存储引擎，可以有效避免这种情况的发生
• InnoDB在遇到违反约束时，会自动回滚update语句，一行都不会修改成功

===》对于insert的约束冲突： insert … on duplicate key

• 指出在违反主键或唯一索引约束时，需要进行的额外操作

举例：

create table t3 (
id int(10) unique,
flag char(10) default 'true'
)engine=MyISAM;

insert into t3(id) values(1);
insert into t3(id) values(5);
insert into t3(id) values(6);
insert into t3(id) values(10);

insert into t3(id) values(10) on duplicate key update flag='false';

===》插入id=10的记录，会违反unique约束，此时执行update flag=’false’，于是有一行记录被update了

• 相当于 update t3 set flag=‘false’ where id=10;

总结

对于主键与唯一索引约束：

• 执行insert和update时，会触发约束检查
• InnoDB违反约束时，会回滚对应SQL
• MyISAM违反约束时，会中断对应的SQL，可能造成不符合预期的结果集
• 可以使用 insert … on duplicate key 来指定触发约束时的动作
• 通常使用 show warnings; 来查看与调试违反约束的ERROR

类型不匹配，字符编码不匹配，对索引命中产生的影响

第一类：“列类型”与“where值类型”不符

• 不能命中索引，会导致全表扫描(full table scan)

举例：

create table t1 (
cell varchar(3) primary key
)engine=innodb default charset=utf8;

insert into t1(cell) values ('111'),('222'),('333');

（1）cell属性为varchar类型；
（2）cell为主键，即聚簇索引(clustered index)；
（3）t1插入3条测试数据；

explain select * from t1 where cell=111;
explain select * from t1 where cell='111';

（1）第一个语句，where后的值类型是整数（与表cell类型不符）；

（2）第二个语句，where后的值类型是字符串（与表cell类型一致）；

（1）强制类型转换，不能命中索引，需要全表扫描，即3条记录；
（2）类型相同，命中索引，1条记录；

第二类：相join的两个表的字符编码不同

• 不能命中索引，会导致笛卡尔积的循环计算（nested loop）。

举例：

create table t1 (
cell varchar(3) primary key
)engine=innodb default charset=utf8;

insert into t1(cell) values ('111'),('222'),('333');

create table t2 (
cell varchar(3) primary key
)engine=innodb default charset=latin1;

insert into t2(cell) values ('111'),('222'),('333'),('444'),('555'),('666');

create table t3 (
cell varchar(3) primary key
)engine=innodb default charset=utf8;

insert into t3(cell) values ('111'),('222'),('333'),('444'),('555'),('666');

（1）t2和t1字符集不同，插入6条测试数据；
（2）t3和t1字符集相同，也插入6条测试数据；
（3）除此之外，t1，t2，t3表结构完全相同；

explain select * from t1,t2 where t1.cell=t2.cell;
explain select * from t1,t3 where t1.cell=t3.cell;

（1）第一个join，连表t1和t2（字符集不同），关联属性是cell；
（2）第一个join，连表t1和t3（字符集相同），关联属性是cell；

（1）t1和t2字符集不同，存储空间不同；
（2）t1和t2相join时，遍历了t1的所有记录3条，t1的每一条记录又要遍历t2的所有记录6条，实际进行了笛卡尔积循环计算(nested loop)，索引无效；
（3）t1和t3相join时，遍历了t1的所有记录3条，t1的每一条记录使用t3索引，即扫描1行记录；

总结

两类隐蔽的不能利用索引的case：
（1）表列类型，与where值类型，不一致；
（2）join表的字符编码不同；

负向查询 !=，IN，OR，UNION对索引命中的影响

举例：
假设订单业务表结构为：order(oid, date, uid, status, money, time,…) 相关字段上也建立了索引。

假设订单有三种状态：0已下单，1已支付，2已完成
业务需求，查询未完成的订单，哪个SQL更快呢？

(1)select * from order where status!=2
(2)select * from order where status IN(0,1)
(3)select * from order where status=0 or status=1
(4)select * from order where status=0 union all select * from order where status=1

• 负向查询：select * from order where status!=2

负向查询肯定不可以命中索引，无争议。

前导模糊查询，像like '%XX’是同样的道理，无法命中索引；
非前导模糊查询，像like ‘XX%’，则可以命中索引。

• IN查询：select * from order where status IN(0,1)

可以命中索引

• OR查询：select * from order where status=0 or status=1

新版MySQL，可以优化为IN查询，故也是可以命中索引的

• union：select * from order where status=0 union all select * from order where status=1

两个SQL结果集合并，肯定可以命中status索引，无争议。

注意

举例：

create table user (
id int,
name varchar(20),
index(id)
)engine=innodb;

insert into user values(1,'shenjian');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');

id为索引，非唯一(non unique)，允许空(null)。

1. 数据区分度不大的字段，不宜使用索引

例如：
select * from user where sex=1
因为，性别只有男，女，每次过滤掉的数据很少，性能和全表扫描差不多

2. 属性上的计算，不能命中索引

select * from order where YEAR(date) < ='2019'
即使date上建立了索引，也会全表扫描

可优化为值计算：

select * from order where date < =CURDATE()
select * from order where date < ='2019-10-24'

后两者可以命中索引。

3. 负向比较（例如：!=）会引发全表扫描
   explain select * from user where id!=1;
   
   索引字段id上的不等于查询，如上图所示：
   （1）type=ALL，全表扫描；
   （2）rows=3，全表只有3行；

4. 允许空值，不等于(!=)查询，可能导致不符合预期的结果

• 如果允许空值，不等于(!=)的查询，不会将空值行(row)包含进来，此时的结果集往往是不符合预期的，此时往往要加上一个or条件，把空值(is null)结果包含进来
• 建表时加上默认(default)值，这样能避免空值的坑

insert into user(name) values(‘wangwu’);//构造一条id为NULL的数据，共四条记录

• select * from user where id!=1;
  
  结果集只有2条记录，空值记录记录并未出现在结果集里

• select * from user where id!=1 or id is null;
  如果想到得到符合预期的结果集，必须加上一个or条件。
  

5. 某些or条件，又可能导致全表扫描，此时应该优化为union

• explain select * from user where id=1;//索引字段id上的等值查询，能命中索引
  
• explain select * from user where id is null; //索引字段id上的null查询，也能命中索引
  
• explain select * from user where id=1 or id is null;//用or查询，则会全表扫描
  
• explain select * from user where id=1
  union select * from user where id is null;//优化为union查询，又能够命中索引
  
  索引，一文搞定 | 数据库系列：https://mp.weixin.qq.com/s/woz5lkQwyJZNmoiiJZy7NA