关闭

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

标签: 索引HASHBTREE索引设计原则性能优化
348人阅读 评论(0) 收藏 举报
分类:

一 概要

索引是数据库中用于提高性能最常用的工具,所有的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编写时按照使用原则,让自己创建的索引使用上,否则白创建了

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:330387次
    • 积分:6202
    • 等级:
    • 排名:第4181名
    • 原创:334篇
    • 转载:1篇
    • 译文:2篇
    • 评论:16条
    最新评论