索引的设计

索引简述

1.mysql所有的列都可以被索引，每种引擎对每个表至少支持16个索引，总索引长度至少为256字节。

2.MyISAM和InnoDB默认都是BTREE索引，支持前缀索引（对索引字段前N个字符创建索引）。MEMORY存储引擎使用HASH索引，也支持BTREE

3.创建索引的语法：

    CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type] on table_name(index_col_name)

4.删除索引

    DROP INDEX index_name ON tbl_name

索引设计原则

1.最适合索引的列是出现在where子句中的列，或连接字句中指定的列，

2.使用唯一索引。考虑某列中值的分布。索引的列的基数越大，索引效果越好。eg：性别 男女 属于基数小，不合适

3.使用短索引，对长字符串，可指定一个前缀长度。例如char(100)，可以指定前10个或20个字符作为索引。可以节省索引空间，磁盘IO较少，更快，索引高速缓存中的块能容纳更多的键值。

4.利用最左前缀

5.不要过度索引。索引不是越多越好，越多维护成本高。

BTREE索引和HASH索引

1.HASH索引一些特征需注意

1.1 只用于使用=或<=>操作符的等式比较

1.2 优化器不能对HASH索引来加速orderby操作

1.3只能使用整个关键字来搜索一行

2.BTREE索引，使用>,<,>=,<=,BETWEEN,!=或者<>,或者LIKE 'pattern'(不能以通配符开头)操作符时，都可以使用相关列上的索引。