联合索引失效(未注意最左原则)问题解决

生产上有一个运行了个多月的项目，随着数据越来越多，查询就变得越来越慢，按理说STATUS这个字段也加了索引，为什么会这么慢呢？之前有一篇文章已经讲解过索引的最左原则，今天讲讲为什么联合索引失效了。

我们执行如下语句:SHOW INDEX FROM T_table_name 查看该表的索引情况:

Table:表名称

Non_unique:如果索引不能包括重复词，则为0。如果可以，则为1。

Key_name:索引名称

Seq_in_index:索引中的列序列号，从1开始（最左原则与该顺序挂钩，需注意）

Column_name:列名称

Collation:列以什么方式存储在索引中,在MySQL中，有值‘A’（升序）或NULL（无分类）

Cardinality:索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大

Sub_part:如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL

Packed:指示关键字如何被压缩。如果没有被压缩，则为NULL。

Null:如果列含有NULL，则含有YES,如果没有，则该列含有NO。

Index_type:用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）

我们发现，STATUS字段在seq_in_index中排在了第5位，按照索引的最左原则，直接WHERE STATUS='1'时，索引便会失效：

我们看看type类型，竟然是all，这就意味着进行了全表扫描(索引失效)，那么type是什么意思呢？MySQL的官网解释非常简洁，只用了3个单词：连接类型(the join type)，它描述了找到所需数据使用的扫描方式，也有人称呼它为访问类型。

最为常见的扫描方式有：

system:系统表，少量数据，往往不需要进行磁盘IO；

const:常量连接；

eq_ref:主键索引(primary key)或者非空唯一索引(unique not null)等值扫描；

ref:非主键非唯一索引等值扫描；

range:范围扫描；

index:索引树扫描；

ALL:全表扫描(full table scan)；

上面各类扫描方式由快到慢： system > const > eq_ref > ref > range > index > ALL

我们现在来看一下为什么会索引失效，界面之前查询选择按钮还有行政区划一说，现在取消了行政区划，查得最多的一项，就是按照STATUS进行查询，这就能解释得通为什么数据量变多以后，查询就越来越慢了：

所以，根据最左原则，我们需要重新优化联合索引，我们可以先删除索引，在创建正确索引，删除索引:
删除索引可以使用ALTER TABLE或DROP INDEX语句来实现，DROP INDEX可以在ALTER TABLE内部作为一条语句处理，其格式如下：

drop index index_name on table_name

alter table table_name drop index index_name

alter table table_name drop primary key

其中，在前面的两条语句中，都删除了table_name中的索引index_name。
最后一条语句只在删除PRIMARY KEY索引中使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引

如果从表中删除某列，则索引会受影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

删除索引以后，我们重新创建索引:

1)使用ALTER TABLE语句创建索引
alter table table_name add index index_name (column_list) //普通索引
alter table table_name add unique (column_list) //UNIQUE索引
alter table table_name add primary key (column_list) //PRIMARY KEY索引

2)使用CREATE INDEX语句对表增加索引
能够增加普通索引和UNIQUE索引两种。其格式如下：
create index index_name on table_name (column_list) ;
create unique index index_name on table_name (column_list) ;
说明：不能用CREATE INDEX语句创建PRIMARY KEY索引。

在该项目案例中，我们需要用到的语句如下:

SHOW INDEX FROM T_ECFN_PAY_LIST

EXPLAIN SELECT * FROM T_ECFN_PAY_LIST WHERE STATUS='0'

ALTER TABLE T_ECFN_PAY_LIST DROP INDEX T_ECFN_PAY_LIST_COMM_1  //删除索引 

CREATE INDEX INDEX_NAME ON T_ECFN_PAY_LIST (ADM_DIV_CODE,PAY_LIST_ID,STATUS,VOUCHER_BILL_NO,VOUCHER_NO,PAY_AMT,PAYEE_ACCT_NO) //新创建索引

SELECT * FROM T_ECFN_PAY_LIST WHERE STATUS='0'  //验证查询效率

注:为什么选择ADM_DIV_CODE和PAY_LIST_ID在前，是因为该二者确定唯一，而界面上不能输入该二者，所以无论什么时候用STATUS进行查询，都不会导致索引失效。