特点:B+树叶子节点存储行数据
一个表中,必须有一个聚集索引,只能有一个聚集索引,Innodb通常把一个表的主键索引作为聚集索引,如果没有主键InnoDB会选择一个唯一索引代替。如果没有这样的索引,InnoDB会隐式的定义一个主键来作为聚集索引,这个字段为6个字节,类型为长整形。
利用主键索引查找行数据是最快的,建议使用自增主键原因是利于索引树的构建(主键自增写入时新插入的数据不会影响到原有页,插入效率高;但是如果主键是无序的或者随机的,那每次的插入可能会导致原有页频繁的分裂,影响插入效率)
特点:B+树叶子节点存储主键ID
一个表中可以有多个非聚集索引,每个非聚集索引即是一棵B+树
通过非聚集索引查找数据时,需要先在非聚集索引上找到主键ID,再从聚集索引获取行数据,这个过程就称之为回表
B树索引中的B树实际上是B+树,至于为什么使用B+树而不使用B树或者红黑树的原因在另外的文章中有提及。
特点:
特点:类似JDK中的HashMap,但无法支持范围查询
特点:使用的算法仍然是B树索引,不同的就是索引列的值必须唯一
对于普通索引来说,查找到满足条件的第一个记录后,需要查找下一个记录,直到碰到第一个不满足条件的记录。
对于唯一索引来说,由于索引定义了唯一性,查找到第一个满足条件的记录后,就会停止继续检索,提升索引性能
另外插入行时会构建该唯一索引,假如索引值重复将插入失败,适合业务上做唯一性检验
通过建立倒排索引,可以极大的提升检索效率,解决判断字段是否包含的问题,但是业务上一般都不采用这种索引,而是使用ES处理全文搜索需求
仅对某个特定字段建立的索引,如(biz_id)
对多个字段建立的索引,如(biz_id,type)
索引就是为特定的mysql字段进行一些特定的算法排序,比如二叉树的算法和哈希算法,哈希算法是通过建立特征值,然后根据特征值来快速查找。
1.普通索引:(index)最基本的索引,没有任何限制 目的:加快数据的查询速度
2.唯一索引:(unique) 与"普通索引"类似,不同的就是:索引列的值必须唯一,但允许有空值。
3.主键索引(primary key) 它 是一种特殊的唯一索引,不允许有空值。
4.复合索引:index(a,b,c) 为了更多的提高mysql效率可建立组合索引,遵循”最左前缀“原则。
5.全文索引:fulltext 仅可用于 MyISAM 表,针对较大的数据,生成全文索引很耗时耗空间。
第一类是myisam存储引擎使用的叫做b-tree结构,
第二类是innodb存储引擎使用的叫做聚簇结构(也是一种 b-tree)。 如下图:
注意:
1.myisam不需要回行处理
2.innodb不需要回行处理,直接可以获取数据,因为innodb的储存引擎是包含了数据和索引文件的,其主键索引包含了数据,(唯一索引及普通索是没有直接包含数据的)
1、索引列不能参与计算
有索引列参与计算的查询条件对索引不友好(甚至无法使用索引),如from_unixtime(create_time) = '2014-05-29'。
原因很简单,如何在节点中查找到对应key?如果线性扫描,则每次都需要重新计算,成本太高;如果二分查找,则需要针对from_unixtime方法确定大小关系。
因此,索引列不能参与计算。上述from_unixtime(create_time) = '2014-05-29'语句应该写成create_time = unix_timestamp('2014-05-29')。
2、最左前缀匹配
如有索引(a, b, c, d),查询条件a = 1 and b = 2 and c >3 and d = 4,则会在每个节点依次命中a、b、c,无法命中d。也就是最左前缀匹配原则。
3、冗余和重复索引
冗余索引是指在相同的列上按照相同的顺序创建的相同类型的索引,应当尽量避免这种索引,发现后立即删除。比如有一个索引(A,B),再创建索引(A)就是冗余索引。冗余索引经常发生在为表添加新索引时,比如有人新建了索引(A,B),但这个索引不是扩展已有的索引(A)
4、避免多个范围条件
select user.* from user where login_time >'2017-04-01' and age between 18 and 30
比如想查询某个时间段内登录过的用户:它有两个范围条件,login_time列和age列,MySQL可以使用login_time列的索引或者age列的索引,但无法同时使用它们 .
5、覆盖索引 (能扩展就不新建)
如果一个索引包含或者说覆盖所有需要查询的字段的值,那么就没有必要再回表查询,这就称为覆盖索引。覆盖索引是非常有用的工具,可以极大的提高性能,因为查询只需要扫描索引会带来许多好处:
1.索引条目远小于数据行大小,如果只读取索引,极大减少数据访问量2.索引是有按照列值顺序存储的,对于I/O密集型的范围查询要比随机从磁盘读取每一行数据的IO要少的多
6、选择区分度高的列作索引
如,用性别作索引,那么索引仅能将1000w行数据划分为两部分(如500w男,500w女),索引几乎无效。
区分度的公式是count(distinct ) / count(*),表示字段不重复的比例,比例越大区分度越好。唯一键的区分度是1,而一些状态、性别字段可能在大数据面前的区分度趋近于0。
7、删除长期未使用的索引
场景一(覆盖索引 5)
索引应该建在选择性高的字段上(键值唯一的记录数/总记录条数),选择性越高索引的效果越好、价值越大,唯一索引的选择性最高;
组合索引中字段的顺序,选择性越高的字段排在最前面;
where条件中包含两个选择性高的字段时,可以考虑分别创建索引,引擎会同时使用两个索引(在OR条件下,应该说必须分开建索引);
不要重复创建彼此有包含关系的索引,如index1(a,b,c) 、index2(a,b)、index3(a);
组合索引的字段不要过多,如果超过4个字段,一般需要考虑拆分成多个单列索引或更为简单的组合索引;
不要滥用索引。因为过多的索引不仅仅会增加物理存储的开销,对于插入、删除、更新 *** 作也会增加处理上的开销,而且会增加优化器在选择索引时的计算代价。
因此太多的索引与不充分、不正确的索引对性能都是毫无益处的。一言以蔽之,索引的建立必须慎重,对每个索引的必要性都应该经过仔细分析,要有建立的依据。
MySQL目前主要的索引类型有下面几种:
与前面的普通索引类似,但是他的索引列的值必须是唯一的,所以叫唯一索引,但是这个索引字段如果是空值是可以的,具体创建方式如下:
主键索引是一种特殊的索引,一个表只能有一个主键,不允许有空值,一般是创建表的时候创建主键索引,而且一般习惯设置成自增的,因为对弈MySQL的底层B+树存储起来很方便
组合索引指多个字段上创建的索引,只有在查询时候,查询条件中使用了创建索引时的第一个字段,索引才会生效,他使用遵循最左前缀原则.
索引生效情况:
select * from table where name=1
select * from table where name=1 and city=2
select * from table where name=1 and city=2 and age=3
索引不生效情况:
select * from table where name=1 and age=3
select * from table where city=2 and age=3
select * from table where age=3
select * from table where city=2
这个遵循的是最左原则,具体MySQL底层对联合索引的存储以及为什么是最左原则,参考本人另外一篇文章最后一段就能看明白
https://www.jianshu.com/p/99aabf9611a3
全文索引主要是用来查找文本中的关键字,而不是直接与索引中的值相比较.fulltext索引跟其他索引大不相同,他更像是一个搜索引擎,而不是简单的where 语句的参数匹配,fulltext索引配合match(匹配)和against(反对) *** 作使用,而不是一般的where语句加上like,他可以在create table,alter table,create index使用
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)