索引使用分析

这里对之前理论的补充：

SQL索引失效_孤人飘雪的博客-CSDN博客_sqlserver索引失效

我在这里已经创建了answer表；

1.查看数据库表中所有索引：

show index from 表名;

show index from answer;

参数说明：

Table ：表示创建索引的数据表名，这里是 answer 数据表。

Non_unique：表示该索引是否是唯一索引。若不是唯一索引，则该列的值为 1；若是唯一索引，则该列的值为 0。

Key_name：表示索引的名称。

Seq_in_index：表示该列在索引中的位置，如果索引是单列的，则该列的值为 1；如果索引是组合索引，则该列的值为每列在索引定义中的顺序。

Column_name：表示定义索引的列字段。
Collation：表示列以何种顺序存储在索引中。在 MySQL 中，升序显示值“A”（升序），若显示为 NULL，则表示无分类。
Cardinality：索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL 使用该索引的机会就越大。
Sub_part：表示列中被编入索引的字符的数量。若列只是部分被编入索引，则该列的值为被编入索引的字符的数目；若整列被编入索引，则该列的值为 NULL。
Packed：指示关键字如何被压缩。若没有被压缩，值为 NULL。
Null：用于显示索引列中是否包含 NULL。若列含有 NULL，该列的值为 YES。若没有，则该列的值为 NO。
Index_type：显示索引使用的类型和方法（BTREE、FULLTEXT、HASH、RTREE）。
Comment：显示评注。

 2.创建和删除索引语句：

1).primary key 主键索引：
--创建主键id
alter table answer add primary key(id);
--创建主键id自增
alter table answer modify id int auto_increment;
删除主键（如果先删除自增会报错）：如果不先删除自增，会报错：

--删除自增长
Alter table answer change id id int(10);
--删除主键
ALTER table answer drop primary key;

 2).unique 唯一索引：

ALTER TABLE 表名 ADD UNIQUE(列名);

--创建唯一索引
ALTER TABLE answer add UNIQUE (id);

3).index 普通索引：

ALTER table 表名 add INDEX 索引名(列名);

ALTER table answer add INDEX index_name(answerselect);

4).组合索引：

ALTER table 表名 add index 索引名(列名,列名,列名);

ALTER table answer add INDEX answerselect_answertitle_isright_index(answerselect,answertitle,isright);

删除语句：

DROP INDEX 索引名 ON 表名；

3.分析索引及失效：

1).--组合索引answerselect,answertitle,isright，只使用answerselect
EXPLAIN select * from answer where answerselect = 'A';

 2).--组合索引answerselect(A),answertitle(B),isright(C)，使用A,B
EXPLAIN select * from answer where answerselect = 'A' and answertitle = '错误';

 3).--组合索引answerselect(A),answertitle(B),isright(C)，使用ABC，全部命中
EXPLAIN select * from answer where answerselect = 'A' and answertitle = '错误' and isright = '1';

 4).--组合索引answerselect(A),answertitle(B),isright(C)，使用A,C,只命中了A
EXPLAIN select * from answer where answerselect = 'A' and isright = '1';

 5).--组合索引answerselect(A),answertitle(B),isright(C)，使用B,C,都没有命中
EXPLAIN select * from answer where answertitle = '错误' and isright = '1';

 6).--组合索引answerselect(A),answertitle(B),isright(C)，使用B,C,A，全部命中
EXPLAIN select * from answer where answertitle = '错误' and  isright = '1' and answerselect = 'A' ;

 7).--组合索引answerselect(A),answertitle(B),isright(C)，使用A,B,C，命中A
EXPLAIN select * from answer where answerselect = 'A' and answertitle like '%误' and isright = '1';

 8).--组合索引answerselect(A),answertitle(B),isright(C)，使用A,B,C，命中A
EXPLAIN select * from answer where answerselect = 'A' and answertitle like '错%' and isright = '1';

 --排序情况(不能出现Using filesort)
1).--组合索引answerselect(A),answertitle(B),isright(C)，使用A查询,B排序
EXPLAIN select * from answer where answerselect = 'A' ORDER BY answertitle;

2).--组合索引answerselect(A),answertitle(B),isright(C)，使用A，B查询,C排序
EXPLAIN select * from answer where answerselect = 'A' and answertitle = '错误' ORDER BY isright;

3).--组合索引answerselect(A),answertitle(B),isright(C)，使用A查询,C排序
EXPLAIN select * from answer where answerselect = 'A' ORDER BY isright;