mysql索引

1 概述

1)mysql支持的索引有BTREE索引,HASH索引,前缀索引,全文本(FULLTEXT)索引.MyISAM和InnoDB默认创建的都是BTREE索引.前缀索引的长度跟存储引擎相关,对于MyISAM存储引擎的表,索引的前缀长度可以达到000字节长,InnoDB的为767字节.FULLTEXT索引可以用于全文搜索,但是mysql5.0只用MyISAM支持全文本索引,并且只限于char,varchar和text列
2)创建索引语法:
create [unique\fulltext\spatlal] index index_name
[using index_type]
on tbl_name (index_col_name,…)

index_col_name:col_nam[(length)] [ASC\DESC]
例:为ctiy字段创建10个字节的前缀索引
create index cityname on city (city(10));

3. 删除索引的语法
   drop index index_name on tbl_name;
   例:drop index cityname on city;

2 设计索引的原则

1)搜索的索引列,不一定是要选择的列,换句话说,最适合索引的列是出现在where子句中的列,或连接子句中指定的列,而不是select关键字后的列
2)使用唯一索引.考虑到列中指的分布,索引的基数越大,索引的效果越好.如记录创建时间的列,具有不同的值很容易区分,记录性别的列只有两个值,因此建立索引用处不大,不管怎么查询都会有一半的行
3)使用短索引.如果对字符串列进行索引,应该指定一个前缀长度,只要有可能就应该这样做.例如有一个CHAR(200)列,如果在前10个或20个字符内,多数值是唯一的,那么就不要对整个列进行索引.对前10个或20个字符进行索引能够节省大量索引空间,可能使查询更快,较小的索引涉及的磁盘IO较少,较短的值比较起来更快,更为重要的是,对于较短的键值,索引高速缓存中的块能容纳更多的键值,因此,MySQL也可以在内存中容纳更多的值,这样就增加了找到行而不用读取索引中较多块的可能性
4)利用最左前缀.在创建一个n列的索引时,实际是创建了MYSQL可利用的n个索引.多列索引可起到几个索引的作用,因为可利用索引中最左边的列集来匹配行.这样的列集称为最左前缀
5)不要过度索引.每个额外的索引都要占用额外的磁盘空间,并降低写操作的性能.在修改表的内容时,索引必须进行更新,有时可能需要重构,因此,索引越多,所化的时间越长,如果有一个索引很少利用或从不使用,那么会不必要的减缓表的修改速度.此外,mysql在生成一个执行计划时,要考虑各个索引,这也要花费时间.创建多余的索引给查询优化带来了更多的工作.索引太多,可能会使MySQL选择不到所要使用的最好索引,只保持所需的索引有利于查询优化
6)对于InnoDB存储引擎的表,记录会按照一定的顺序保存,如果有主键,则按照主键顺序保存,如果没主键,但是有唯一索引,那么就按照唯一索引保存数据,如果既没有主键也没有唯一索引,则自动生成一个内部列,按照这个列的顺序保存.因此InnoDB表尽量自己指定主键,且主键尽可能选择较短的数据类型,可以有效减少索引的磁盘占用.

3 BTREE和HASH索引

3.1 MEMORY存储引擎的表可以选择使用BTREE索引或者HASH索引,两种不同类型的索引各有不同的适用范围,HASH索引有一些重要的特征需要注意,如下:
1)只用于使用=或<=>操作符的等式比较
2)优化器不能使用HASH来加速ORDER BY操作
3)MySQL不能确定在两个值之间大约有多少行.如果将一个MyISAM改为HASH索引的MEMORY,会影响一些查询的执行效率
4)只能使用整个关键字来搜索一行
3.2BTREE索引,当使用<,>,>=,<=,Between,!=或者<>,或者like “pattern”(其中pattern不以通配符开始)操作符时,都可以使用相关列上的索引