索引概述
每个表至少支持16个索引,总索引长度至少256个字节,大多数存储引擎有更高的限制。
BTREE索引:b+树索引,myisam、innodb的默认索引类型。
hash索引:memory的默认索引类型。
前缀索引:字段的前N部分作为索引字段。
全文本索引(FULLTEXT):全文搜索,限于char、varchar、text, 限于myisam。不支持前缀检索。
空间类型索引:空间列上创建索引。限于myisam。
? create table; --创建表的同时创建索引。
? create index; --单独创建索引的语句。
? alter table; --通过alter table 创建索引。
create index city_idx on city(city(10)); --在city表的city字段上创建一个前缀索引
explain select * from city where city like '北%' \G; --通过执行计划查询索引使用情况
drop index city_idx on city; --删除表city的city_idx索引
show indexes from t2; --查询表t2上的索引
索引的设计原则
- 最适合索引的列应该是出现在where子句上或者连接字段上。
- 使用唯一索引。 或者尽可能在区分度高的列上建立索引。
- 使用短索引。如果一个长字段,如:char(200), 前几个字符的区分度已经很高,就没有对整个列建立索引,而应该使用前缀索引。较小的索引涉及的磁盘IO较少。还可以节省高速索引缓存的空间。
- 利用最左前缀。对于多列索引(起到多个索引的作用),可以利用列集最左边的列来匹配行。
- 不要过度索引。索引加大写数据的负担。加大了mysql查询计划的负担,可能导致查询计划无法匹配到最优索引上。
- innodb的索引特性。innodb的表记录会按照一定的顺序排列。主键顺序>唯一索引>内部列(自动生成),只有在前面不满足的时候,会后面的排列顺序。 故innodb的表建议指定一下主键或者唯一索引,提供检索效率,有多个列都可以作为主键和唯一索引,应该选择最常访问的那一列。 innodb表的普通索引会存放主键的数据, 所以主键应选择短数据类型。
BTREE索引和HASH索引
memory的表默认hash索引,也可以使用btree索引。hash索引有自己的使用范围:
- 只能用于类似于= or <=>的等式操作符,包括in。
- 不能用hash索引来加速order by。
- 不能使用like,必须用整个关键字来检索。
FULLTEXT索引
myisam支持全文索引,可以对大字段上建立全文所用,通过match函数来检索字段。全文索引占用存储偏大(预分词?)
FTREE索引
空间列?