Mysql索引知识点总结

索引

概述

索引简介

​ 索引是帮助Mysql进行高效获取数据的数据结构(有序),在数据之外数据可系统还维护着满足特定查找算法的数据结构,这些数据以某种方式指向数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引

索引的作用

​ 如果查询一张表的某一个字段时,在没有索引的情况下,我们需要对=整张表进行查询,一直扫描到最最后一行,进行全表扫描,

​ 但是如果查询字段建立索引后,则就相当于给该字段建立了一个二叉树结构(假设索引是一个二叉树结构),在对该字段进行查询时,就相当于从该二叉树进行查询就会增加数据的查询速度

索引的特点

​ 提高数据检索的效率降低数据的酷的io成本,但是相对应会增加数据库所占用的空间

​ 通过索引对数据进行排序提高数据库进行排序的成本,但是相对应因为使用对应的数据结构降低了更新表的速度,比如对标进行插入删除修改的操作

索引结构

概述

Mysql索引是基于存储引擎来实现的,对于不同的存储引擎有不同的索引结构,主

要包括一下几种,

• B+Tree索引 最常见的索引类型，大部分引擎都支持 B+ 树索引
• Hash索引 底层数据结构是用哈希表实现的, 只有精确匹配索引列的查询才有效, 不支持范围查询
• R-tree(空间索引）空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
• Full-text(全文索引) 是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES

不同存储引擎对索引结构的支持是不一样的具体看下表

索引	InnoDB	MyISAM	Memory
B+tree索引	支持	支持	支持
Hash 索引	不支持	不支持	支持
R-tree 索引	不支持	支持	不支持
Full-text	5.6版本之后支持	支持	不支持

注意,一般所说的索引没有特别指引的就是指B+tree结构组织的索引

详细数据结构知识点等待后续补充,

分类
在mysql数据库中将索引的具体类型主要分为以下几类,

• 主键索引, 自动创建, primary kay
• 唯一索引, 关键字 unique
• 常规索引,
• 全文索引, 关键字 fulltext

在innodb存储引擎中根据索引的存储形式又可以分为以下俩种

分类	含义	特点
聚集索引	将数据存储与索引放在一起,索引结构的节点保存了行数据,	必须有而且只有一个
二级索引	将数据与索引分开存储,索引结构的叶子结点关联的是对应的主键,非叶子节点把存储数据	可以存在多个

聚集索引选取规则:

如果存在主键，主键索引就是聚集索引。如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。

如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

索引语法

• 创建索引 create [ unique | fulltext ] 索引名 on 表名(字段列表)

• 创建索引 alter table 表名 add 索引名(索引字段)

• 查看索引 show index from 表名

• 删除索引 drop index 索引名

• 字段列表为多个字段时该索引为联合索引

• 注意联合索引的重点是第一个索引字段,如果后面的索引字段经常单独作为查询条件进行查询则不宜使用该联合索引,还是应该建立多个单列索引

• 如果你经常要用到多个字段的多条件查询，可以考虑建立联合索引，一般是除第一个字段外的其它字段不经常用于条件筛选情况，比如说a,b 两个字段，如果你经常用a条件或者a+b条件去查询，而很少单独用b条件查询，那么可以建立a,b的联合索引。如果a和b都要分别经常独立的被用作查询条件，那还是建立多个单列索引。

• 联合索引使用场景,

  • 打当俩个列的组合是唯一值时,联合索引是一个不错的选择
  • 使用联合索引可以对第二个简直进行排序 只需要在第一个键值后面添加排序条件即可,不需要使用order by ,因为联合索引在建立索引时已经对该索引进行排序
  • 当查询一行数据时,单列索引与 联合索引发生冲突时,数据库优先选择单列索引,这里小编猜想既然联合索引已经相当与一个单列索引在查询第一个索引字段时,那么是不是在单列与联合索引冲突只保留联合索引就可以了

• 创建联合索引时可以指定字段使用降序还是升序,直接在字段列表后跟排序条件即可

索引的使用

• 最左前缀原则规定了联合索引在何种查询中才能生效，规则如下：
  • 如果想使用联合索引，联合索引的最左边的列必须作为过滤条件，否则联合索引不会生效。
• 范围查询 在使用范围查询时如果要使用联合索引,那么要尽量使用尽可能的使用类似于 >= 或 <= 这类的范围查询，而避免使用 > 或 < ,后者会导致联合查询第二列的索引失效
• 尽量使用覆盖索引,即查询使用了索引时,在需要返回的列中在该索引中能中全部找到,即建好select的使用,不然还需要回查表数据

索引失效情况

• 在索引列上进行运算操作,会导致索引失效

• 查询字符串类型时不给字符串类型添加单引号,会导致索引失效

• 使用模糊查询时如果对头部进行模糊查询则索引失效,但是尾部进行模糊查询不会导致索引失效

• 用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。左右俩测都有索引时,索引才会生效

• 数据分布影响, 如果mysql认为走全表查询更快,则索引不会生效,具体看数据本身,除非是根据主键查询,这时候直接走聚集索引,而不是二级索引

• 当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让

  索引变得很大，查询时，浪费大量的磁盘IO， 影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率

  • 语法 create index idx_xxxx on table_name(column(n)) ;

• SQl提示使用索引

  • use index(索引名) 建议mysql使用一个索引,仅仅是建议,mysql内部还是会再次进行评估

  • ignore index() 忽略指定索引

  • force index() 强制使用某个索引

索引设计原则

• 针对数据量较大,且查询比较频繁的表建立索引
• 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
• 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
• 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引
• 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
• 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
• 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。