MySql(21)------mysql索引使用

一 概要

索引是数据库中用于提高性能最常用的工具，所有的mysql列类型都可以使用索引，每个表的最大索引数

和索引长度根据表的存储引擎而定，一般情况下每个表至少支持16个索引，索引总长度至少为256个字节，

有些存储引擎的索引限制会更高些。

常见存储引擎支持的索引：

（1）对于MyISAM和InnoDB存储引擎表的默认创建索引为BTREE索引。同时也支持全文索引，该索引

用于全文搜索，目前全文索引只能用于CHAR, VARCHAR以及TEXT类型列，索引针对整个列进行，不支持局部索引。

（2）InnoDB存储引擎默认也是BTREE索引，同时也支持HASH索引的方式。

（3）MEMORY存储引擎默认使用HASH索引，同时也支持BTREE索引方式。

******创建语法：

索引的创建可以随表建立时创建，也可以在表创建完后添加。

创建新索引：

CREATE [UNIQUE\FULLTEXT\SPATIAL\NORMAL] INDEX index_name [USING index_type] ON table_name(index_col_name,...)

其中index_col_name部分的内容：

col_name[(length)][ASC][DESC]

eg:

CREATE UNIQUE INDEX userName_index USING BTREE ON t_user_main (f_userName);

其中如语法所诉，USING BTREE表使用BTREE索引方式，如果不写，就使用表的存储引擎

默认的索引方式，如果不指定UNIQUE，就使用默认NORMAL索引类型。

注意的是[UNIQUE\FULLTEXT\SPATIAL\NORMAL]表示的是索引类型，

[USING index_type]表示的是索引方式，也就是检索方式。

增加索引时使用ALTER TABLE语法增加索引。

******删除索引：

DROP INDEX index_name ON table_name;

eg:

DROP INDEX userName_index ON t_user_main;

二 索引设计原则

索引的创建可以遵循一些千千万万革命同志实践总结出来的原则，对于创建索引提高效率会起到事半功倍的效果。

如果自己一味的去瞎创建索引，可能适得其反，不合理的使用索引对于提高效率起到的只是相反的作用。

设计原则，简单列举几个：

（1） 搜索的索引列，并不一定是选择的列。也就是说，最恰当的索引列应该是在WHERE子句中，

或者是连接子句中连接的列，而不是SELECT后面指定的查询列。简单来说，搜索的索引列是用来

作为查询条件或关联条件的，方便查询时能对条件或关联列进行特殊处理，从而提高查询效率。

（2）使用唯一索引。唯一的含义就是这个作为索引的列存的数据尽可能的不同，存的数据是唯一的，

比如存身份证号码的列上创建一个唯一索引，效果会很好，而对于存性别的列上创建一个唯一索引，

就没有啥效果，因为每次通过男或女去查询，都能筛选出一半数据。

（3）使用短索引。一些列中存放的数据很长，但是可以设计数据的前10个或20个是唯一的，这样创建索引

时可以正对该列的前10个或20个字符进行短索引创建，不用全局匹配，能够提高效率。

（4）尽可能不要过度的创建索引。物极必反，合理使用。

三 BTREE索引和HASH索引

MEMORY引擎表创建的默认索引是HASH索引，但是也可以使用BTREE索引，这两种索引有一定的使用范围。

HASH索引的使用范围：

（1）只用于=或<=>比较等式

（2）优化器不能使用HASH索引加速ORDER BY 操作，相生相克

（3）MySql不能确定在两个值之间有多少行，如果强行将MyISAM引擎表改为HASH索引的MEMORY引擎表，

效率会受到影响。

（4）只能使用与整个关键字搜索一行

BTREE索引的使用范围：

（1）>,<,>=,<=,BETWEEN,!=或<>可以使用BTREE索引

（2）like 'pattern',其中pattern不以通配符开头时，比如like 'kkkkk%'时可以使用BTREE索引，而like '%kkkkk'不能使用BTREE索引。

注意： SQL编写时按照使用原则，让自己创建的索引使用上，否则白创建了