mysql的索引类型
mysql支持的索引类型为:主键索引(key)、全文索引(FULLTEXT)、普通索引(NORMAL)、空间索引(SPATIAL)、唯一索引(UNIQUE)。无论使用什么方式的索引,都是通过hash或者b-tree来实现索引的存储结构。
-
主键索引:主键索引又称为聚簇索引通过B+tree的方式实现,并把索引和数据保存在一起。
-
全文索引:主要是匹配文本中的关键字,建议谨慎使用,数据量比较大的时候可以使用elasticsearch或者其他的效率更高的搜索引擎。
-
普通索引:又称为二级索引,没有任何限制,主要是加速查询速度。
-
空间索引:空间索引是对空间数据类型的字段建立的索引,MYSQL中的空间数据类型有4种,分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL关键字进行扩展,使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列,必须将其声明为NOT NULL,空间索引只能在存储引擎为MYISAM的表中创建。
-
唯一索引:具有唯一的特点,无论使用任何方式实现索引,都需要保持全局唯一,如果是复合索引,则要保证复合索引的每一列都是唯一值
无论使用哪一种索引类型,都避免不了使用hash或者b-tree作为索引的存储结构。都需要按照以此为作为目标合理的使用索引:
- 减少服务器需要扫描的数据量
- 帮助服务器避免排序和临时表
- 将随机IO变成为顺序IO
innndb索引
innodb中的索引中b-tree存储结构是使用B+TREE的方式实现的:都知道innodb是支持聚簇索引,主键索引和数据保存在一起按照b+tree的方式存储,并且是有序的,无论是查询,还是排序在主键上面速度都比较快,则其他的索引类型在innodb中只存储了被索引的字段和主键。
例如:在一个表(table1)中有四个字段,id、A、B 、C id为主键索引自增,建立复合索引(A-B)
1:查询id=1的所有值 这:select * from table1 where id = 1(只需要直接筛选主键索引就可以了,就可以拿到说需要的数据)
2:查询A=2的,A、B的值 select A,B from tables where A = 2(只需过滤(A-B)索引,获取A、B的值,这样查询就使用了复合索引的最左前缀原则,也使用了覆盖索引的原则,不需要回表即可查询出所需的数据)。
3:查询A=2的所有值 select id、A、B 、C tables where A=2这个时候需要怎么查询的呢,如果 A=2有多条,mysql🈶️是怎么查询的呢,这就需要了解mysql的回表机制,以此来优化sql语句了。
- 在(A-B)索引树上找到A=2的记录,取得 id=x;
- 再到ID索引树查到id=x对应的所有字段的值(此过程叫回表查询);
- 在(A-B)索引树上找到A=2的记录,取得 id=y
- 再到ID索引树查到id=y对应的所有字段的值(此过程叫回表查询);;
- 一直找到没有可满足条件,循环结束。
这样的一次查询需要回表查询n次,这n次回表的需要浪费时间,这个时候就想,既然必须要回表,我可不可以只回表一次,提高查询效率呢?答案是有的就是:延时关联
延时关联:使用普通索引的复合索引(多列索引)延时关联实现,具体的优化写法如下:
select * from tables inner join(select id from tables where A=2) as fuhe using(id)
先根据复合索引查询主键id,拿到主键id一次性会叫查询id对应的值,这就是使用覆盖索引和延时关联,减少回表次数,提高查询效率。
备注:如果数据量比较少不建议这样,有时候数据量比较少的时候,全表扫描比索引快很多。