mysql 索引

注释:此文章的索引介绍是基于InnoDB存储引擎来介绍的，没特别声明則Mysql默认式InnoDB存储引擎存储。

一、独立的列

独立的列:指索引列不能是表达式的一部份。
　　遵守一个规则：养成简化where条件语句的习惯，始终将索引列单独放到比较符的一侧。

　//表table_1 在clum_1 上建立列B-tree 索引，存储引擎为InnoDB。
　//此时索引clum_1在查询式是没有使用到的。
　mysql> select * from table_1 where clum_1 +1=5;
　//不难发现可以改写为如下，此时clum_1是能够发挥索引的作用的。
　mysql>selct * from table_1 where clum_1 = 4 ;

二、前缀索引和索引选择性

1 碰到长的索引列的解决策略

在需要索引很长的字符列时，会使索引变得大且慢，此时可以通过模拟Memory引擎特有的哈希索引来解决问题。此时有一个缺点就是得通过手动或触发器来实现对哈希值的维护，如下表：

create table imitate_hash(
    id int unsigned neo null auto_increment;
    url varchar(255) not null;
    url_crc int unsigned not null default 0,
    primary key (id),
    key (url_crc，url)
);

创建触发器

delimiter
create trigger imitate_hash_crc_insert before insert on imitate_hash for each row begin set new.url_crc=crc32(new.url);
end;
create trigger imitate_hash_crc_update before update on imitate_hash for each row begin set new.url_crc =crc32(new.url);
end;
delimiter;

查询语句

mysql>select url,url_crc from imitate_hash where url="http://www.baidu.com" and rul_crc="12312312312";

注意点：如果数目过大的话会产生大量的hash 冲突，而且在查询时where 语句中得包含常量值(url),否则单独查询hash值可能会产生多条返回值，此时可以考虑自己实现一个64位的hash函数，得返回整数，例如

mysql>select conv(right(MD5('http://www.mysql.com"),16),16,10) as hash64;

2.选择合适的前缀索引长度

在一般情况下某个列的前缀的选择性也是足够高的，可以满足一般的查询性能要求，对于blob 、text或非常长的varchar类型的列，必须使用前缀索引，因为mysql 不允许索引这些列的完整长度。
　　此时要选择足够长的前缀来保证较高的选择性，同时不能太长，前缀应该足够长，以使得前缀索引的选择性接近于索引的整个列，前缀的基数应该接近于完整列的基数(不重复的索引值)。

create table user (
id int not null auto_increment,
name varchar(20) not null,
comment varchar(300) not null,
primary key(id),
key(comment(20))
mysql> alter table user add index (comment(20));
//或者
mysql>alter table user add key (comment(20));

注释：

选择性=不重复索引值/表的记录总条数；

三、多列索引

1.在使用多个单列索引进行条件查询时，使用explian 来进行查看，如果在extra列中显示有索引合并使用的情况，应该考虑是否将多个单列索引合并成多列索引，此时得根据表的查询需求来确认好的索引顺序。
　2.当mysql在对多个索引做相交操作时(通常会出现多个and 条件)，通常意味着需要一个包含所有相关列的多列索引，而不是建立多个单独的索引。
　3.当服务器对多个索引做联合操作式，比如多个or条件语句，此时需要耗费大量的cpu和内存资源来在算法缓存、合并和排序上面，特别式当索引效率并不高，需要合并大量扫描数据时,此时将or 语句替换为union效率更高一些。

四、选择合适的索引顺序

1.原由

在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排列，其次是第二列，因此，索引可以按照升序或者降序进行扫描，来满足精确符合列顺序的group by 、order by 和　distinct 等句子的查询。

五、聚簇索引

聚簇索引并不是单独的索引，而是一种数据的存储方式，在InnoDB引擎中聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。当表有聚簇索引时，它的数据行实际上是存放到索引的叶子页中。
　”聚簇“表示数据行和相邻的键值紧凑的存储在一起，所以一张表只能有一个聚簇索引，由于是存储引擎负责实现索引，因此不是所有的存储引擎都支持聚簇索引。