【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优化,完