MySQL 索引

好记忆也需烂 2020-06-10

索引

MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度
排好序的快速查找数据结构

mysql常用的索引种类

1. FULLTEXT

即为全文索引,目前只有MyISAM引擎支持。其可以在CREATE TABLE ,ALTER TABLE ,CREATE INDEX 使用,不过目前只有 CHAR、VARCHAR ,TEXT 列上可以创建全文索引。全文索引并不是和MyISAM一起诞生的,它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。

2. HASH

由于HASH的唯一(几乎100%的唯一)及类似键值对的形式,很适合作为索引。HASH索引可以一次定位,不需要像树形索引那样逐层查找,因此具有极高的效率。但是,这种高效是有条件的,即只在“=”和“in”条件下高效,对于范围查询、排序及组合索引仍然效率不高。

3. BTREE

BTREE索引就是一种将索引值按一定的算法,存入一个树形的数据结构中(二叉树),每次查询都是从树的入口root开始,依次遍历node,获取leaf。这是MySQL里默认和最常用的索引类型。

4. RTREE

RTREE在MySQL很少使用,仅支持geometry数据类型,支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。相对于BTREE,RTREE的优势在于范围查找 

索引种类

普通索引:仅加速查询

唯一索引:加速查询 + 列值唯一(可以有null)

主键索引:加速查询 + 列值唯一(不可以有null)+ 表中只有一个

组合索引:多列值组成一个索引,专门用于组合搜索,其效率大于索引合并

全文索引:对文本的内容进行分词,进行搜索

建立索引优缺点

优点:类似大学图书馆建书目索引,提高了检索效率,降低了数据库IO,同时还可以通过索引进行排序,降低数据排序的成本,降低了CPU的消耗

缺点: 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行 insert、update和delete,因为更新表时不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段

mysql索引不会命中的情况

1.如果条件中有 or ,即使其中有条件带索引也不会命中(这也是为什么尽量少用or的原因)
2.like查询是以%开头,如果是int型索引不会命中,字符型的命中’test%‘百分号只有在右边才可以命中
3. 如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引
4. 没有查询条件,或者查询条件没有建立索引
5. 查询条件中,在索引列上使用函数(+, - ,*,/), 这种情况下需建立函数索引
6. 采用 not in, not exist
7. B-tree 索引 is null 不会走, is not null 会走
8.联合索引遵循最左原则,不满足的不会命中

索引的建立与不建

# 哪些情况需要创建索引

	主键自动创建唯一索引

	频繁作为查询条件的字段应该创建索引

	查询中与其它表关联的字段,外键关系建立索引

	查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度

	查询中统计或者分组字段



# 哪些情况不需要建索引

	频繁更新的字段不适合创建索引
	where 条件用不到的字段不适合创建索引
	注意,如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果

小总结

假设index(a,b,c),a,b,c 散列建立了普通索引

	Where语句	索引是否被使用
	where a = 3	Y,使用到a
	where a = 3 and b = 5	Y,使用到a,b
	where a = 3 and b = 5 and c = 4	Y,使用到a,b,c
	where b = 3 或者 where b = 3 and c = 4 或者 where c = 4	N
	where a = 3 and c = 5	使用到a, 但是c不可以,b中间断了
	where a = 3 and b > 4 and c = 5	使用到a和b, c不能用在范围之后,b断了
	where a = 3 and b like ‘kk%‘ and c = 4	Y,使用到a,b,c
	where a = 3 and b like ‘%kk‘ and c = 4	Y,只用到a
	where a = 3 and b like ‘%kk%‘ and c = 4	Y,只用到a
	where a = 3 and b like ‘k%kk%‘ and c = 4	Y,使用到a,b,c

相关推荐