【MySQL】索引

之前的select所采用的方式是遍历数据表进行查找,这种方式效率是比较低的,尤其是数据表汇总数据量比较大的时候,于是便有了索引,MySQL中的索引的作用就是为了快速获取数据

1.索引简介

索引本身其实是一种数据结构,这些数据结构以某种方式指向数据
索引的优缺点比较明显,优点是提高了检索效率,缺点是索引本身也占据空间,而且因为数据结构和数据对应,所以如果更新数据的话那么索引也要更新

2.索引的数据结构

索引是在引擎层实现的,所以不同的存储引擎有着不同的结构,最常见的索引结构是B+树索引,这个索引大部分的存储引擎都支持,所以B+树索引是接下来的重点,但在这之前我们需要先了解下B-Tree

2.1 B-Tree

B-Tree指的是多路平衡查找树,也属于树形结构,和二叉树锁不同的是,B-Tree的节点里面可以有多个值,节点也可以有多个子节点,B-Tree的最大度数为节点最多存储的值的个数+1
这里推荐一个网站:https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
这个网站可以直观的展示各种数据结构是如何创建的
我们以最大度数为3的B-Tree为例(即一个节点最多存储两个值):
最开始我们插入两个值
在这里插入图片描述

然后再插入一个大于5的值,因为一个节点最多只能存储两个值,所以树要进行分裂,5作为中间的值就存在父节点中
在这里插入图片描述
之后再插入值,比5小的放左边,比5大的放右边
在这里插入图片描述
这个时候如果再插入一个值的话,由于字节点存储的值已经满了,但父节点还有一个存储位置,所以再分裂情况如下
在这里插入图片描述
再插入一个值,树则会分裂成3层
在这里插入图片描述
大致过程就是这样,但还是建议自己去网站里面看看,因为这里只截了最后的结果,中间的分裂过程并没有截取下来
而B+Tree则是基于B-Tree进行变化的,其结构如下:
在这里插入图片描述
我们首先要看的是叶子节点部分,整棵树所存储的值都会出现在叶子节点里面,非叶子节点则是起到一个索引的作用,而且叶子节点的值是用链表连接起来的

而MySQL的B+Tree索引在原来的B+Tree上对其进行了优化,简单来说就是把单向链表变为了双向循环链表

2.2 Hash索引

Hash索引主要使用的就是哈希算法,将key值进行映射到对应位置后进行存储
相较于B+Tree索引,Hash索引的查询效率更高,但它只支持对等比较(=,in),不支持范围查询(between,<等不行),而且无法完成排序操作
所以Hash索引使用的场景是比较少的

3.索引的分类

分类特点关键字
主键索引默认是自动创建,有且仅有一个primary
唯一索引可以有多个unique
常规索引可以有多个
全文索引可以有多个fulltext

全文索引所查找的是文本中的关键词
在InnoDB引擎中,根据索引存储的形式将其分为了聚集索引和二级索引

  • 聚集索引 : 数据和索引放在一起存储,索引结构的叶子节点保存行数据,特点是必须有,而且仅有一个
  • 二级索引 : 数据和索引分开存储,叶子节点存储的是对应的主键值,可以有多个
    聚集索引选取规则:
  1. 存在主键的话主键索引就是聚集索引
  2. 没有主键但是有唯一索引的话,第一个唯一索引就是聚集索引
  3. 二者都没有的话,InnoDB会生成一个rowid作为隐藏的聚集索引
    现在假设有一个数据表,里面id为主键,我输入一条查询语句,我们来看看这个语句的执行过程
select * from user where name='Tom';

首先我们是依据名字来进行查找的,但是主键是id,所以先要在二级索引里面进行查找,在二级索引里面找到名字对应的id之后再来到聚集索引里面查找,最后显示信息
上述的操作过程叫回表查询

4.索引的语法

在索引的语法中我们主要介绍的是创建,查看和删除索引

创建索引:
  create [unique/fulltext] index 索引名称 on 表名(字段1,字段2......);   主键索引默认是自动创建的,索引只有一个列为单列索引,索引包含多个列则是联合索引
查看索引:
  show index from 表名;
删除索引:
  drop index 索引名 on 表名;

一般索引名称为idx_表名_字段名

5.SQL性能分析

SQL性能分析是对SQL语句进行性能优化的前提,清楚哪些SQL语句执行的效率低才能有针对的进行优化

  • 查看SQL语句执行频率
查看服务器状态信息:
  show session/global status;
查看SQL语句执行频率:
  show global status like 'Com_______';   7个下划线
  • 慢查询日志
    这个日志记录了所有执行时间超过指定参数(默认为10秒)的所有SQL语句
查询日志是否开启:
  show variables like 'slow_query_log';  一般默认是off
开启日志:
  set global slow_query_log=1;
修改指定参数的值:
  set long_query_time=; 
查询慢查询日志的路径:
  show variables like '%quer%';  出来的结果里面slow_query_log_file就是

上面这些只是临时配置,想要永久配置的话需要在MySQL的配置文件里面进行配置 slow_query_log=1,指定参数也是一样的

  • profile详情
    这是一条语句: show profiles
    这条语句可以在进行SQL优化时候帮助我们了解耗费的时间去哪了
查看是否支持profile:
  select @@have_profiling;
查看profile状态:
  select @@profiling;    默认是关闭状态
设置profile状态:
  set profiling=1/0;

打开profile之后我们在执行完SQL后输入show profiles就可以查看各个SQL语句的耗时情况,如图
在这里插入图片描述
输入show profile for query Query_ID值 就可以查看单条SQL语句具体是哪个阶段耗时最多
在这里插入图片描述

  • explain执行计划
    explain可以查看MySQL是如何执行select语句的,包括select执行过程中数据表如何连接以及连接顺序
    explain语法是在select语句之前加上关键字explain即可,结果如图
    在这里插入图片描述
    图中字段的含义会逐个介绍

id : select查询的序列号,表示操作表的顺序,id相同顺序是从上到下,不同则是值越大的越先执行
select_type : select的类型,常见的值有simple,primary,union,subquery等
type : 连接类型,性能由好到差依次为null,system,const,eq_ref,ref,range,index,all
possible_keys : 可能引用在这张表上的索引
key : 实际使用的索引
key_len : 索引中使用的字节数,这个值为索引的最大可能长度,非实际长度,在不损失精度的前提下一般是越短越好
ref : 非唯一索引查询
rows : MySQL认为需要执行查询的行数,在InnlDB引擎中这是一个估计值,并非一定准确
Exttra : 额外信息

6.索引使用

6.1 使用原则

  • 最左前缀法则
    这个主要是针对联合索引,如果索引关联了多列,那么查询从索引的最左列开始,否则索引失效,且不跳过中间的列,跳过则索引部分失效(后面的索引字段失效)
create index idx_student_name_age_class on student(name,age,class);

上面的索引关联了三个字段,那么在使用索引进行查询的时候,可以不同全部写在条件里面,但是条件中name必须存在,否则索引失效,如果索引字段填写的是name和class,那么class失效

  • 范围查询
    在联合索引中,如果出现<,>这种范围查询的话,范围查询右侧的列索引失效,但是<=和>=可以
    还是以上面的SQL语句创建的索引为例
select * from student where name='Tom' and age<20 and class=1;

class索引失效

  • SQL提示
    在SQL语句中加入一些人为的提示来达到优化操作
    这里主要介绍三个

use index : 告诉数据库使用指定索引,MySQL不一定接受
ignore index : 告诉数据库不使用指定索引
force index : 强制让数据库使用指定索引

这三个关键字都是加在表名之后

  • 前缀索引
    这个是针对字段数据类型为字符串的处理,特别是长字符串和文本较大的数据,将字符串的前缀拿出来建立索引,这样可以提高索引效率
create index 索引名称 on 表名(字段(n));    n表示提取字符串前n个字符

具体n怎么取就需要看实际的业务场景了

6.2 索引失效的情况

  1. 索引列进行运算操作,失效
  2. 字符串类型的字段使用时未加引号,这会导致隐式类型转换,失效
  3. 头部模糊匹配,失效
  4. 如果or只有一侧有索引而另一侧没有,失效
  5. 如果MySQL评估使用索引比使用select更慢便不会使用索引

6.3 索引设计原则

  1. 针对数据量大,且查询比较频繁是数据表建立索引
  2. 针对经常被作为查询条件,分组,排序操作的字段建立索引
  3. 尽量使用联合索引,联合索引较单列索引避免了回表查询,查询效率更高
  4. 尽量选择区分度高的字段作为索引,尽量建立唯一索引
  5. 如果字符串类型的字段长度较长,可以考虑使用前缀索引
  6. 索引不是越多越好,索引本身也是需要维护的,索引过多会影响数据表增删改的效率

索引到这里就基本结束,下一篇是SQL优化,完

  • 2
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 2
    评论
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

星鸦wyk

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值