索引的种类
索引的种类
索引有哪几种类型?
主键索引:数据列不允许重复,不允许为NULL,一个表只有一个主键。
唯一索引:数据列不允许重复,允许为NULL,一个表允许多个列创建唯一索引。
普通索引:基本的索引类型,没有唯一性的限制,允许为NULL值。
全文索引:是目前搜索引擎使用的一种关键技术,对文本的内容进行分词、搜索。
覆盖索引:查询列要被创建的索引覆盖,不必读取数据行。
组合索引:多列值组成一个索引,用于组合搜索,效率大于索引合并。
主键索引和非主键索引的区别?
比如这个表(对k建立非主键索引):
主键索引和非主键索引的示意图如下:
其中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索引树上了,因为可以直接提供查询结果,不需要回表。
使用覆盖索引的好处?
不需要回表 由于覆盖索引可以减少树的搜索次数,显著提升查询性能。
什么是最左前缀原则?
对于该表,如果按照name字段建立索引的话,采用B+树的话,大概索引如下:
如果进行模糊查找的话,查找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
建立联合索引测试:
联合索引的顺序为: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"; #使用索引
参考
mysql聚簇索引 和主键的区别_一分钟明白MySQL聚簇索引和非聚簇索引
MySQL主键索引(聚簇索引)与非主键索引(二级索引)
索引(index)_普通索引、唯一索引和复合索引.索引查询
普通索引和联合索引区别
【mysql索引】之前缀索引
什么是最左前缀原则?什么是最左匹配原则?