首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >mysql可以靠索引,而我只能靠打工,加油,打工人!

mysql可以靠索引,而我只能靠打工,加油,打工人!

作者头像
java小杰要加油
发布2021-05-13 15:14:04
4390
发布2021-05-13 15:14:04
举报
文章被收录于专栏:java小杰要加油java小杰要加油

一、 索引数据结构

面试的时候肯定会问这一个问题,mysql为什么会选择b+树作为索引呢?而不选择其他索引,例如b树?hash?

下面说的磁盘IO是指数据从硬盘加载到内存中的操作

  • hash索引的话,不支持范围查询,因为hash就是一个键对应一个值的,没办法范围查询
  • 二叉树的话,它的特点就是左子树小于根节点小于右子树(指的是节点的值),如果根节点取值有问题的话,有可能会退化成链表,就是树不分叉了,树一直往左或者一直往右,这样就不能折半查找从而减少IO次数了,同时也不支持范围查询,要是范围查询的话,每次都要从根部遍历,树也太高了,树越高,IO操作越频繁,浪费资源
  • 平衡二叉树的话,它就没有了二叉树的这种退化成链表的缺点,因为他左右子节点最多相差1层,可是他也不支持范围查找这一点和二叉树的问题一样
  • b树的话,和二叉树比起来树是很矮胖,IO操作减少了,是个多叉树,它每个节点都存了对应的行数据,可是如果这一行的数据的列不断的增加,那么这一页存储的节点就会变少,因为所占的空间不断的变大,树也会越来越高,增加IO操作次数,同时是也不支持范围查找。要是相同大小的空间可以存很多的节点数据的话就更好了,所以就有了下面的b+树
  • b+树 它非叶子节点只存索引的数据,不存整行数据,但是叶子节点是冗余的,冗余了非叶子节点,叶子节点还都用双向链表链接起来,这样有助于顺序查找,b+树和b树比起来,更加矮胖,磁盘IO次数更少

二、 mysql中索引类型

  • 聚簇索引与非聚簇索引

我们可以简单的理解为 聚簇索引就是主键索引,非聚簇索引就是普通索引

本质的区别是

聚簇索引 的叶子节点存储的是整行数据

innodb是通过主键来实现聚簇索引的,如果没有主键的话,那么他就会选择一个唯一非空的索引来实现,如果再没有的话,他就会隐式生成一个主键来实现聚簇索引

非聚簇索引存储的是索引值和主键值

  • 普通索引一张表中可以有多个普通索引,随便一个字段都可以建立的索引,我们平常建立的索引大部分都是普通索引
  • 联合索引好几个字段联合起来建立的索引
  • 唯一索引业务中唯一的字段适合建立唯一索引,一个表中可以有多个唯一索引
  • 主键索引和唯一索引一样,主键索引也是唯一的,不同的就是,一个表只能有一个主键索引

三、关于索引的sql

创建主键索引

ALTER TABLE test add  PRIMARY  KEY (id)

创建唯一索引

ALTER TABLE test add UNIQUE idx_id_card(id_card)

创建普通索引

ALTER TABLE test add INDEX idx_name(name)

创建联合索引

ALTER TABLE test add INDEX idx_age_name(age,name)

修改索引名称 :先删除再添加

删除索引 (两种方式)

ALTER TABLE test DROP INDEX idx_id_card
DROP INDEX idx_id_card on test

 --删除主键索引
DROP PRIMARY key on test  
ALTER TABLE test DROP  PRIMARY key

查看表中索引

SHOW INDEX FROM test

分析索引

EXPLAIN SELECT * from test WHERE name = "xhJaver"

我们先给name字段添加一个索引,索引名字叫做idx_name

ALTER TABLE test add INDEX idx_name(name)

查看test表中的索引

SHOW INDEX FROM test

其中的属性

  • table: 表名
  • Non_unique: 能重复的话为1,不能重复的话为0,我们主键的那里是0,而name那里是1,因为name可以重复,而主键不能重复
  • Key_name: 索引名称
  • Seq_in_index:索引中列的顺序
  • Column_name:列名称
  • Collation:列以什么方式存储的,A升序,null无序
  • Cardinality:数目越大,则使用该索引的可能性越大
  • Sub_part:如果列只是部分的编入索引,则被编入索引的字符数目,如果整列被编入索引,则为null
  • Packed:关键字是否被压缩,null表示没有被压缩
  • Null:如果该列含有null,则为yes,如果没有null,则为no
  • Index_type:索引数据结构
  • Comment:多种评注

四、回表查询

select * from test where  name = "xhJaver"

假如说我们name字段建立了索引,然后当我们运行这一句sql语句的时候,因为建立的是普通索引,所以我们的b+树的叶子节点存储的数据是id,我们会找到name是xhJaver的这条记录的id,再根据这个id,去主键索引的那棵b+树去查询,查询到叶子节点时即查询出这条记录,可见这个过程中,我们从一棵树跑到了另一棵树继续查,这样就叫做“回表查询”,那有没有办法只查一棵树就可以查询出结果呢?

五、覆盖索引

办法当然是有的啦,那就是覆盖索引,我们注意到,刚才这个sql语句时查询出来了所有元素,假如说我们这样写的话

select address from test where  name = "xhJaver"

假如说我们建立的索引是(name,address)那么这个时候(name,address)这棵b+树的叶子节点存储的数据就包括address了,此时就不需要再根据name = "xhJaver"的id去第二棵树查了,这样就避免了回表查询

六、最左匹配原则

假如说现在我们写一个这样的sql语句

select *  from test where  name = "xhJaver" and age =23  and address="京东"

并且我们建立的索引是(name,address,age)这样是会用到(name,address,age)索引的,可是如果要这样写的话

select *  from test where  name = "xhJaver" and age >23  and address="京东"

这样只会用到(name,age)这两个索引,从左边开始匹配,如果要是遇到范围查询的话,则不继续往右匹配索引

七、explain分析索引语句

我们用explain语句解析一下下面这条sql语句

EXPLAIN SELECT * from test WHERE name = "xhJaver"

它的属性有

id: 执行的顺序

  • id相同时,顺序从上到下执行
  • id不同时,id大的先执行

select_type: 查询的类型

  • primary: 最外层的查询被标记为primary
  • simple: 简单查询,没有关联其他表,就一张表
  • subquery: 在where或者select中的子查询
  • derived: 衍生虚拟表 例如from(子查询) t,这个子查询的结果就被放在虚拟表t中

table: 关于哪张表的

partitions: 分区相关(还没搞懂呜呜呜)

type:访问类型

性能由好至坏依次是 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL一般来说,好的sql查询至少达到range级别,最好能达到ref

  • system:表中只有一行数据
  • const:常量查询 通常用于比较主键等于一个常量,用索引查询一次就查到了
  • eq_ref:唯一性索引,每个索引对应一条数据,例如主键索引
  • ref:非唯一索引,每个索引有可能对应多行数据,例如普通索引
  • range: 范围查询,用到了>,<,in,between等查询
  • index:全表扫描,但是是遍历整棵索引树
  • all:全表扫描,没有用到索引

possible_keys:查询的字段上有索引的话,就会显示出来,

key : 具体用到的索引,若用到了覆盖索引,则possible_keys为null,只会显示在key中

key_len:索引中使用的字节数,最大可能长度,并非实际长度,key_len是根据表定义计算而得的,不是通过表内检索出的

ref: 表示使用索引的是哪一个字段

rows:大致估算出所需要读取的行数

filtered:显示了通过条件过滤出的行数的百分比估计值。

Extra:

  • Using filesort : mysql无法利用索引完成的排序被称为文件排序
  • Using temporary: 使用临时表存储了下中间结果,mysql对查询结果排序时是使用了临时表,常见于order by 和 group by
1. EXPLAIN SELECT (select student.id from student WHERE student.`name`="xhJaver") FROM teacher

2. EXPLAIN SELECT * FROM teacher where teacher.id = (select student.id from student WHERE student.`name`="xhJaver") 

我们写几个sql语句实际分析下

1.sql1 SELECT后面有子查询

2.sql2 where后面有子查询

我们就拿后面这个图来实战分析一下,挑几个重要的属性说一下

select_type:

  • 我们最外层的查询是 from teacher 所以table为teacher的那个表的select_type就是primary
  • select/where后面的括号中的查询语句中的表是student,所以table为student的那个表的select_type就是subquery

table: 这条sql查询用到的表

type: 访问类型

  • 第一行const : teacher.id =巴拉巴拉巴拉(这个是常数)主键和常数比较时,这个表最多有一个匹配数据,只读取一次
  • 第二行ref:代表用到了普通索引,就是这个索引name和xhJaver匹配,可能匹配到很多相同的值

possible_key: 代表可能用到的索引,但是不一定会用到

key: 代表用到的索引, 用到了idx_name,PRIMARY索引

ref: 这一列显示了在key列记录的索引中,表查找值所用到的列或常量, 常见的有:const,字段名

extra:

  • using index: 一般是使用了覆盖索引,看我们这个sql语句,
select student.id from student WHERE student.`name`="xhJaver"

name字段有索引,查询的是id,b+树叶子节点存的数据就是id,所以不需要回表查询了,用到了覆盖索引

八、索引失效原因

  1. 遇到范围查询(>,<,like,beetwon),右边的索引列会失效(使用联合索引时)
  2. 索引字段不能有函数操作或者不能是表达式的一部分
  3. 索引字段隐式类型转换 索引字段类型是string,我们传进来个int
  4. 使用时or,is null ,is not null , !=, <>, like "%xxx" 索引会失效

但是用覆盖索引就可以解决 like左模糊查询走不到索引的情况 如果只select索引字段,或者select索引字段和主键,也会走索引的。

本文参与 腾讯云自媒体分享计划,分享自微信公众号。
原始发表:2020-10-25,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 java小杰要加油 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 一、 索引数据结构
  • 二、 mysql中索引类型
  • 三、关于索引的sql
  • 四、回表查询
  • 五、覆盖索引
  • 六、最左匹配原则
  • 七、explain分析索引语句
  • 八、索引失效原因
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档