索引的设计和使用

索引是数据库中用来提高性能的最常用工具。

         根据存储引擎可以定义每个表的最大索引数和最大索引长度,每种存储引擎(MyISAM,InnoDB,BDB,MEMORY等)对每个表至少支持16个索引,总索引长度至少为256字节。

         前缀索引

         要为city表创建了10个字节的前缀索引,语法是:

         create  index  cityname on  city (city_name(10));

         删除索引

DROP INDEX cityname ON city;

 

         设计索引的原则

(1)、搜索的索引列,不一定是所要选择的列。

最适合索引的列出现在WHERE子句中的列,或者连接子句中指定的列,而不是出现在SELECT关键字后的选择列表中的列。

(2)、使用唯一索引。考虑某列中值的分布。索引的列基础越大,索引的效果越好。如区分日期的列效果就比区分性别好

(3)、使用短检索。如果对字符串进行检索,应该指定一个前缀长度。例如:一个CHAR(200)列,如果前10个或20个字符内,多数值是唯一的,那么就不要对整个列进行检索。对前10个或20个字符进行检索能够节省大量索引空间,是查询更快。

(4)、利用最左前缀。在创建一个n列索引时,实际是创建了MySQL可利用的n个索引。多列索引可起几个索引的作用,因为可利用索引最左边的列级来匹配。

(5)、不要过度索引。

 

BTREE索引和HASH索引

         HASH索引注意事项

 

 

MySQL索引(PRIMARY KEY、UNIQUE、INDEX、FULLTEXT等)

 

keyprimary key区别 
CREATE TABLE wh_logrecord ( 
logrecord_id int(11) NOT NULL auto_increment, 
user_name varchar(100) default NULL, 
operation_time datetime default NULL, 
logrecord_operation varchar(100) default NULL, 
PRIMARY KEY (logrecord_id), 
KEY wh_logrecord_user_name (user_name) 

解析: 
KEY wh_logrecord_user_name (user_name)                     //表(字段)
本表的user_name字段与wh_logrecord_user_name表user_name字段建立外键 
括号外是建立外键的对应表,括号内是对应字段 
类似还有 KEY user(userid) 
当然,key未必都是外键 

总结: 
Key是索引约束,对表中字段进行约束索引的,都是通过primary foreign unique等创建的。常见有foreign key,外键关联用的。

key的用途:主要是用来加快查询速度的。 

Primary key的用途:主键,只能有一个。 

UNIQUE KEY的用途:主要是用来防止数据插入的时候重复的。

 

二、KEYINDEX区别 
KEY通常是INDEX同义词。如果关键字属性PRIMARY KEY在列定义中已给定,则PRIMARY KEY也可以只指定为KEY。这么做的目的是与其它数据库系统兼容。 PRIMARY KEY是一个唯一KEY,此时,所有的关键字列必须定义为NOT NULL。如果这些列没有被明确地定义为NOT NULL,MySQL应隐含地定义这些列。一个表只有一个PRIMARY KEY。

 

三、mysqlUNIQUE KEYPRIMARY KEY有什么区别 
1、 Primary key的1个或多个列必须为NOT NULL,如果列为NULL,在增加PRIMARY KEY时,列自动更改为NOT NULL。而UNIQUE KEY 对列没有此要求 
2、一个表只能有一个PRIMARY KEY,但可以有多个UNIQUE KEY 
3、主键和唯一键约束是通过参考索引实施的,如果插入的值均为NULL,则根据索引的原理,全NULL值不被记录在索引上,所以插入全NULL值时,可以有重复的,而其他的则不能插入重复值。 
alter table t add constraint uk_t_1 unique (a,b); 
insert into t (a ,b ) values (null,1);    # 不能重复 
insert into t (a ,b ) values (null,null);#可以重复 

 

 

如果对多列进行索引(组合索引),列的顺序非常重要

MySQL仅能对索引最左边的前缀进行有效的查找

posted on 2015-01-21 10:00  我是齐欢  阅读(205)  评论(0编辑  收藏  举报