MYSQL索引的种类

索引的种类
索引有哪几种类型?
主键索引:数据列不允许重复,不允许为NULL,一个表只有一个主键。
唯一索引:数据列不允许重复,允许为NULL,一个表允许多个列创建唯一索引。
普通索引:基本的索引类型,没有唯一性的限制,允许为NULL值。
全文索引:是目前搜索引擎使用的一种关键技术,对文本的内容进行分词、搜索。
覆盖索引:查询列要被创建的索引覆盖,不必读取数据行。
组合索引:多列值组成一个索引,用于组合搜索,效率大于索引合并。
主键索引和非主键索引的区别?
比如这个表(对k建立非主键索引):

[https://safe2.dbswiso.prd/c/0/i/aHR0cHM6Ly9pbWctYmxvZy5jc2RuaW1nLmNuL2ltZ19jb252ZXJ0LzNmNWQ3ZDAyMmJlZjgwNTIyZTZiZDc3NGIwYzI3OWJhLnBuZw~~

主键索引和非主键索引的示意图如下:

在这里插入图片描述

其中Rx表示的是整行数据。而k索引里面存的是ID。

所以主要的区别就是主键索引的叶子节点存放的是整行数据,而非主键索引的叶子结点存放的是主键的值。

另外,主键索引也成为聚簇索引,而非主键索引被成为非聚簇索引或者二级索引。

另外一个区别是,非主键索引查找到数据之后,需要回表查询数据。

聚簇索引和非聚簇索引的区别?
聚簇索引:索引项的排序方式和表中的数据记录排序方式一直的索引。也就是说聚簇索引的顺序就是数据的物理存储顺序。他会根据聚簇索引键的顺序来存储表中的数据,即对标的数据按照索引键的顺序进行排序,然后存储到磁盘上,因为数据在物理存放时只有一种排列方式,所以一个表只能有一个聚簇索引。(找到索引了就找到需要的数据,那么这个索引就是聚簇索引,所以主键就是聚簇索引。)

非聚簇索引:索引数据和物理存储顺序不同。(索引的存储和数据的存储是分开的,也就是说找到了索引,但是没有找到数据,需要根据索引上的值在回表查询,所以非聚簇索引也叫辅助索引。)

什么是覆盖索引?

select * from T where k between 3 and 5

​ 比如查找k的范围为3-5,这时候因为k有索引,所以先根据k的索引找到对应的ID,然后再去主键索引上面搜索具体的信息,有回表。

select ID from T where k between 3 and 5

这个时候因为ID值已经在K索引树上了,因为可以直接提供查询结果,不需要回表。

使用覆盖索引的好处?
不需要回表 由于覆盖索引可以减少树的搜索次数,显著提升查询性能。

什么是最左前缀原则?
image-20210901154151717在这里插入图片描述

对于该表,如果按照name字段建立索引的话,采用B+树的话,大概索引如下:

image-20210901154229096

如果进行模糊查找的话,查找name以张开头的所有人的ID,select ID from table where name like '张%'.

由于在B+树结构的索引中,叶子节点是一个有序链表,所以当我们定位到ID为100的张一之后,可以直接向右遍历所有张开头的人,直到条件不满足为止。这种定位到最左边,然后向右遍历寻找的方式就是最左前缀原则。

比如:一个(a,b,c)的组合索引:

通过a,b条件查询能不能使用或命中这个索引?-----能
通过b,c条件查询能不能使用或命中这个索引?-----不能
原因:索引文件具有B-Tree 的最左前缀匹配特性,如果左边的值未确定,那么无法使用此索引。
主键索引和唯一索引的区别?
创建主键的时候会自动给主键添加索引,且该索引为唯一索引,即主键一定是唯一索引,但是一张表里可以有多个唯一索引,所以唯一索引不一定是主键。

普通索引和唯一索引的区别?
普通索引:普通索引的任务是加快对数据的访问速度。因此应该只为那些经常出现在查询条件where或者排序条件order by中的数据列创建索引。

唯一索引:普通索引可以包含重复的值。而唯一索引里面不可以包含重复的值,在为这个数据列创建索引的时候就应该用关键字unique把他定义为唯一索引。

定义成为唯一索引的好处:

简化了MySQL对这个索引的管理工作,这个索引也因此比较有效率
MySQL在有新纪录插入数据表的时候,自动检查新纪录的这个字段是否已经在某个记录上出现过,如果是的话,MySQL会拒绝插入的那条新纪录。也就是说唯一索引可以保证数据记录的唯一性。
唯一复合索引?
紧接着上个例子,如果创建一个唯一复合索引的话:

CREATE UNIQUE INDEX uni_user_info_pass ON user_info(name,pass);

执行插入操作:

insert into user_info value(600003. "chen","password",23);

如果数据库中已经存在name和pass一样的数据的话,就会拒绝插入。

换句话说,如果是复合型索引,并且是唯一的,只有当唯一复合索引的字段都相同的时候会进行唯一约束的作用。

什么是组合索引(复合索引)?组合索引和普通索引的区别?
当建立复合索引index(c1,c2,c3)就相当于创建了以下三个索引:

index(c1)

index(c1, c2);

index(c1, c2, c3)顺序无关。

索引可以覆盖多个数据列,如像INDEX(columnA,columnB)索引。这种索引的特点是MySQL可以有选择地使用一个这样的索引。如果查询操作只需要用到columnA数据列上的一个索引,就可以使用复合索引INDEX(columnA,columnB)。不过,这种用法仅适用于在复合索引中排列在前的数据列组合。比如说,INDEX(A,B,C)可以当做A或(A,B)的索引来使用,但不能当做B、C或(B,C)的索引来使用。

最左前缀原则:
顾名思义是最左优先,以最左边的为起点任何连续的索引都能匹配上,
注:如果第一个字段是范围查询需要单独建一个索引
注:在创建联合索引时,要根据业务需求,where子句中使用最频繁的一列放在最左边。这样的话扩展性较好,比如 userid 经常需要作为查询条件,而 mobile 不常常用,则需要把 userid 放在联合索引的第一位置,即最左边

同时存在联合索引和单列索引(字段有重复的),这个时候查询mysql会怎么用索引呢?

这个涉及到mysql本身的查询优化器策略了,当一个表有多条索引可走时, Mysql 根据查询语句的成本来选择走哪条索引;

有人说where查询是按照从左到右的顺序,所以筛选力度大的条件尽量放前面。网上百度过,很多都是这种说法,但是据我研究,mysql执行优化器会对其进行优化,当不考虑索引时,where条件顺序对效率没有影响,真正有影响的是是否用到了索引!如果使用联合索引,那么where条件也要尽量根据联合索引的顺序来,如果不按照顺序来,索引也同样会用到,但是在执行前,SQL优化器也会将条件调整为联合索引的顺序,既然可以直接避免这种情况,就没必要再让SQL优化器去处理,毕竟处理也是有开销的。

联合索引本质:
当创建(a,b,c)联合索引时,相当于创建了(a)单列索引,(a,b)联合索引以及(a,b,c)联合索引
想要索引生效的话,只能使用 a和a,b和a,b,c三种组合;当然,我们上面测试过,a,c组合也可以,但实际上只用到了a的索引,c并没有用到!
注:这个可以结合上边的 通俗理解 来思考!

其他知识点:
1、需要加索引的字段,要在where条件中
2、数据量少的字段不需要加索引;因为建索引有一定开销,如果数据量小则没必要建索引(速度反而慢)
3、如果where条件中是OR关系,联合索引不起作用
4、联合索引比对每个列分别建索引更有优势,因为索引建立得越多就越占磁盘空间,在更新数据的时候速度会更慢。另外建立多列索引时,顺序也是需要注意的,应该将严格的索引放在前面,这样筛选的力度会更大,效率更高。

什么是前缀索引?
前缀索引是面对这样的一个场景,当需要索引一个比较长的字符比如BLOB或者text的时候,这样会使索引很慢。为了解决这种查找比较慢的话,一般有两种做法:第一种是改用hash索引。另外一种就是使用字符串的前几个字符作为索引即前缀索引。

主要思路就是选择足够长的前缀以保证较高的选择性,同时又不能太长(造成空间浪费)

所谓选择性,是指不重复的索引数量除以总记录数,范围是(0,1],唯一索引之所以查询效率高,是因为它的选择性等于1。

缺点:MySQL中无法使用前缀索引进行ORDER BY和GROUP BY,也无法用来进行覆盖扫描

最左前缀原则和最左匹配原则
最左前缀原则:最左优先,以最左边的为起点,任何连续的索引都能匹配上。

如果第一个字段是范围查询需要单独建一个索引
在创建多列索引时,根据业务需求,where子句中最频繁的一列放在最左侧。
比如:

当创建(a,b,c)复合索引时,想要索引生效的话,只能使用 a和ab、ac和abc三种组合!

实例:以下是常见的几个查询:

mysql>SELECT `a`,`b`,`c` FROM A WHERE `a`='a1' ; //索引生效
mysql>SELECT `a`,`b`,`c` FROM A WHERE `b`='b2' AND `c`='c2'; //索引失效
mysql>SELECT `a`,`b`,`c` FROM A WHERE `a`='a3' AND `c`='c3'; //索引生效,实际上值使用了索引a

扩展:想要索引最大化的使用需要至少建几个索引?

答:需要建立复合索引:bc

建立联合索引测试:
image-20210901173245737

联合索引的顺序为:sex,age,name

![SELECT * FROM user where sex="3"; #使用索引
SELECT * FROM user where age="4"; #未使用索引
SELECT * FROM user where name="2"; #未使用索引
SELECT * FROM user where sex="2" and age="3"; #使用索引
SELECT * FROM user where sex="2" and age="3" and name="4"; #使用索引
SELECT * FROM user where age="3" and name="4";  #未使用索引
SELECT * FROM user where sex="2" and name="4";  #使用索引]

#这个在3.2最后边解释,为什么
explain SELECT * FROM index_demo.user where age="2" and sex="3"; #使用索引

值得注意的是,where sex=“2” and name=“4” 这个相当于只有sex使用到了索引的,

如果索引字段有两个 如果索引有两个字段:sex,age
在这里插入图片描述

explain SELECT * FROM index_demo.user where sex="3"; #使用索引
explain SELECT * FROM index_demo.user where age="4"; #未使用索引
explain SELECT * FROM index_demo.user where sex="2" and age="3"; #使用索引
explain SELECT * FROM index_demo.user where age="3" and sex="4";  #使用索引

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值