MySQL索引

  2019-5-23 


索引类型、实现、以及索引优化

索引

Innodb中索引由B+树实现,①B+树相比AVL更低,查询耗时更少②B+树叶子结点有序,指针串起来成一串,可以做到顺序查找、范围查找

可以将B+树的中间结点调入内存,数据结点依然存放在磁盘中,这样就很好。

索引的优点:①查找快,减少需要扫描的数据量②由于B+树的有序性,索引的优势就是可以让随机IO变成顺序IO

以什么字段属性建立索引,那中间的结点存的就是该字段的值

索引是有序的(对应到B+树的叶子节点),建立索引的时候就会进行排序

B+ Tree索引

没有主键会创建默认主键

主键索引主键就是一种索引,此即主键索引,也叫聚簇索引。使用:设置primary key

主键索引的叶子结点的data域有完整的数据记录。其叶子结点称为数据页。聚类结点的这个特性决定了索引组织表中的数据也是索引的一部分

辅助索引的叶子 结点的data域记录着主键的值,不包含数据。查询到叶子结点,获取到主键索引值之后需要再次查主键索引树来获取数据(回表查询,相当于进行了两次IO)

理解:所以我们一般用个id(默认的)做主键,然后再用辅助索引索引name之类的字段,然后我们通过辅助索引查得name所对应的主键id,再回标去主键索引中查就能很快查出主键索引树对应叶子节点上的数据。(当然这里可以用覆盖索引改善,不用回表查询)。想想如果没有辅助索引只有主键索引,那我们就得去所有数据行中找对应的name,就相当于没有索引了。

非主键索引:也叫辅助索引。非主键索引是要先链接到主键索引上的,只要建立的不是主键就是非主键索引。使用:create index on(由于默认建立主键,所以非主键索引可以不用担心没有链接到主键索引)

非主键索引可以是不唯一的,但主键索引一定是唯一的

B+Tree和B Tree的区别

B+树的叶子节点只存储数据(还有索引值、链表指针),在B+树查找命中的时候,会将叶子结点的data域(一页)读进内存

B+树的一个节点(所有结点)大小=innodb的一页=4个操作系统页(一页4kb)=16kb(系统规定,不用纠结)然后把非叶子结点读入内存,叶子结点只在读取到的时候读入内存

叶子节点只存储数据(索引值和链表指针占不考虑)

非叶子节点存储(索引值+指针(指向下子结点))<=这就是B+树相比B树优秀的地方之一,①非叶子结点只存索引,那当然使得非叶子结点储存的索引量增大,使得中间索引更快,层数更低,查询更有效率!

并且利用外存的“预读取”原则,每次读取的时候,把整个节点的数据读取到内存中,下次读取很有可能还读取结点附近的数据(局部性原理

查询速度更稳定:由于B+Tree非叶子节点不存储数据(data),因此所有的数据都要查询至叶子节点,而叶子节点的高度都是相同的,因此所有数据的查询速度都是一样的。

添加了指向相邻叶节点的指针,形成了带有顺序访问指针的B+Tree,这样做是为了提高区间查找的效率

非主键索引,主键索引都是用B+树结构(但非主键索引的叶子结点存放的是主键值)

补充阅读,可以更详细地了解具体结构:https://blog.csdn.net/qq_24384579/article/details/90902132

联合索引

当建立联合索引(a,b,c)的时候,那么就会先排序a,再在a定的基础上排序b,再c。这样一来我们在select查询的时候就必须遵循最左匹配原则,要使用后面的索引,必须得先使用前面的索引,即只能select a或a b或abc,才会使用能进行快速索引查找,否则就只是全表扫描很慢

联合索引是一棵树

更多见索引优化-2

哈希索引

当某个索引值被使用频繁的时候,InnoDB会在B+ Tree索引之上再创建一个哈希索引,方便快速查找(用短hash在b树中找,这样可以用短键索引长键

其它还有全文索引、空间数据索引

索引优化

  1. 索引选择性:不重复的索引值和记录总数的比值(值越高,区分能力就越高,即选择性越强)

  2. 使用联合索引,且使用的时候将选择性较强的索引列放在前面(这样我们就可以最快检索出想要的行)

    联合索引是将多个索引字段构成了一棵树而不是多颗树!该树的结点上的选择支选择遵循最左匹配原则

    在构造联合索引的B+树时,每个结点先判断左边的索引再判断右边的索引(也就是左边索引字段排好序的情况下,右边索引字段再排序。显而易见为什么必须遵循最左匹配原则)

    在联合索引中,中间的结点就是索引字段的值了

    #建立联合索引
    alter table tb1 add index score_index(NAME,SCORE);
    #建立此联合索引之后,查询的时候查询NAME或NAME,SCORE就会使用联合索引查询Type=ref,即where NAME=或NAME= AND WHERE =查询的时候,其它顺序就无法使用联合索引(全表/全索引扫描 Type=ALL/index)
    #(但实际上mysql的查询优化器会优化SCORE,NAME的情况,使得它也会是通过联合索引查询,但是如果顺序不一致,就不会覆盖索引了)
    #注意:如果不符合最左匹配原则查询name和score,比如直接查询score,依然会使用联合索引,但是type=index,不会采用高效的查询算法了(此时score非有序,得全索引遍历查询)
    
    #查询,这里查询就查询了联合索引了 
    select * from tb1 where NAME='a2dasdasd' and SCORE=102;
  3. 对于长字符串,使用前缀索引 (先计算索引选择性,得出最有选择度的长度比如为4,add key name(4))

  4. 覆盖索引:查询在索引树中就可查找所需数据,无需回表,速度更快。

    常见实现方法:将被查询的字段,建立到联合索引里去

    #基于上面的联合索引
    #用到了覆盖索引,因为name,score联合索引的
    select NAME,SCORE from tb1 where NAME='a2dasdasd' and SCORE=102;
    
    #没有用到覆盖索引,因为查找的字段不止name和score还有其它字段没被联合索引覆盖
    select * from tb1 where NAME='a2dasdasd' and SCORE=102;

    联合索引就将多个索引字段的值放在了一个结点,这样比如我们只查联合索引索引的字段(id,name)的时候,一次性就把id和name查出来了,不用回表查询!

    (explain的extra=using index则表示使用了覆盖索引)

SQL慢的可能

一个 SQL 执行的很慢,我们要分两种情况讨论:

1、大多数情况下很正常,偶尔很慢,则有如下原因

(1)、数据库在刷新脏页,例如 redo log 写满了需要同步到磁盘。

(2)、执行的时候,遇到,如表锁、行锁。

2、这条 SQL 语句一直执行的很慢,则有如下原因。

(1)、没有用上索引:例如该字段没有索引;由于对字段进行运算、函数操作导致无法用索引;没有遵循最左原则选择列

(2)、数据库选错了索引。

3、我们的SQL请求太多/太长

尽量上select *,最好用limit,尽量将大SQL语句拆成小的,以便使用缓存

索引不使用于小表上(索引开销大还不如全表扫描,索引的B+树的新增、更新、删除操作需要分裂旋转等操作)

索引设计原则

MySQL 索引设计原则:

(1)对于经常查询的字段,建议创建索引。

(2)索引不是越多越好,一个表如果有大量索引,不仅占用磁盘空间,而且会影响INSERT,DELETE,UPDATE等语句的性能。

(3)避免对经常更新的表进行过多的索引,因为当表中数据更改的同时,索引也会进行调整和更新,十分消耗系统资源。

(4)数据量小的表建议不要创建索引,数据量小时索引不仅起不到明显的优化效果,对于索引结构的维护反而消耗系统资源。

(5)不要在区分度低的字段建立索引。比如性别字段,只有 “男” 和 “女” ,建a索引完全起不到优化效果。

(6)当唯一性是某字段本身的特征时,指定唯一索引能提高查询速度。

(7)在频繁进行跑排列分组(即进行 group by 或 order by操作)的列上建立索引,如果待排序有多个,可以在这些列上建立组合索引。

参考链接:https://blog.csdn.net/qq_30745307/article/details/81230109

备注

explain sqlsentences #分析
---explain结果---
id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.
select_type: SELECT 查询的类型.
table: 查询的是哪个表
partitions: 匹配的分区
type: 索引类型;常用的有index:全索引扫描;all:全表扫描;ref:联合索引
possible_keys: 此次查询中可能选用的索引
key: 此次查询中确切使用到的索引.
ref: 哪个字段或常数与 key 一起被使用
rows: 显示此查询一共扫描了多少行. 这个是一个估计值.
filtered: 表示此查询条件所过滤的数据的百分比
extra: 额外的信息;using index表示使用了覆盖检索
参考:https://segmentfault.com/a/1190000008131735

show index from tb1 #查看索引

更多阅读:MySQL优化策略大全:https://www.jianshu.com/p/d7665192aaaf


且听风吟