SQL复合索引使用和注意事项

单一索引

单一索引是指索引为一列的情况，即新建索引的语句只实施在一列上

复合索引

用户可以在多列上新建索引，这种索引叫复合索引(组合索引)
复合索引在数据库操作期间的开销更小，可以替代多个单一索引
同时有两个概念叫做窄索引和宽索引：

• 窄索引是指索引为1-2列的索引
• 宽索引也就是索引超过2列的索引
  设计索引的一个重要原则就是能用窄索引不用宽索引，因为窄索引往往比组合索引更有效

使用

创建复合索引

CREATE INDEX index_name ON tablel(clo1,clo2,clo3)

查询语句

select * from table where clo1=A and col2=B and col3=C

这时候查询优化器，不在扫描直接从索引中拿数据，因为索引中有这些数据，这叫覆盖试查询，这样的查询速度非常快；

注音事项

1.对于复合索引，在查询使用时，最好将条件顺序按照索引顺序，这样的查询效率最高

 select * from table1 where col1=A AND col2=B AND col3=D 

如果使用

where col2=B AND col1=A

或者

where col2=B

将不会使用索引
2.何时使用复合索引 ？

根据where条件建立索引是及其重要的一个原则；注意不要过多用索引，否则对表的更新效率有很大的影响，因为在操作表的时候要花大量时间在创建索引中
3.复合索引会代替单一索引吗？
如果索引满足窄索引的情况可以建立复合索引，这样可以节约空间和时间
4.备注：
对一张表来说，如果有一个复合索引on(clo1,clo2)就没必要建立单索引on(clo1);
如果查询条件需要可以在已有的单一索引on(clo1)的情况下添加复合索引 on(col1,col2)，对于效率有一定的提高
同时建立多字段(包含5,6个字段）的复合索引没有特别多的好处，相对而言，建立多个窄字段(仅包含1个或顶多2个的字段)的索引可以达到更好的效率和灵活性。

5.例子：

mysql增加索引、删除索引、查看索引

添加索引

有四种方式添数据表的索引

• 添加一个主键索引，这意味着索引值必须是唯一的，且不能为NULL

ALTER TABLE tbl_naem ADD PRIMARY KEY (column_list)

• 这条语句创建索引的值必须是唯一的(除NULL外，NULL可能会出现多次)

ALTER TABLE table_name ADD UNIQUE index_name (column_list);

• 添加普通索引，索引值可以出现多次

ALTER TABLE table_name ADD INDEX index_name (column_list)

• 该语句指定索引为FULLTEXT，用于全文索引

ALTER TABLE table_name ADD FULLTEXT index_name (column_list)

删除索引

• 在ALTER命令中使用DROP子句嘞删除索引

ALTER TABLE table_name DROP INDEX index_name

*注：使用ALTER 命令添加和删除主键。主键只能作用于一个列上，添加主键索引时，你需要确保该主键索引默认不为空(NOT NULL)

实例如下：

ALTER TABLE testalter_tbl MODIFY i INT NOT NULL
ALTER TABLE testalter_tbl ADD PRIMARY KEY (i)

可以使用ALTER命令删除主键：

ALTER TABLE testalter_tbl DROP PRIMARY KEY

删除主键时只需要指定PRIMARY KEY 但在删除索引时，你必须知道索引名

显示索引名

你可以使用SHOW INDEX命令列出表中相关的索引信息。可以添加\G来格式化输出信息。

SHOW INDEX FROM table_name;\G

查看是否使用到索引

explain  SELECT * FROM tb_user

explain 相关字段解析