mysql 索引

追逐记忆

已于 2023-01-29 16:09:28 修改

阅读量100

点赞数

分类专栏： mysql 文章标签：数据库

于 2023-01-27 21:49:12 首次发布

本文链接：https://blog.csdn.net/charsing/article/details/128767614

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

mysql优化

问题导入：几秒到几十秒的慢sql查询如何优化？
加机器，分库分表都不合适
看执行计划，看有没有走索引。
索引提升的幅度很大

1.索引：是帮助mysql高效获取数据的排好序的数据结构

1.1索引的常见的数据结构

二叉树
红黑树
Hash表
B-Tree

2.二叉树

select
如果这条sql语句直接在表中查找不经过索引的话
select * from t where t.col2 = 89;
性能慢
（实际上是把每一行记录从磁盘上面加载出来，然后比对Col2字段。这些数据可能分布在磁盘上随机的任何一个位置，全面扫描，每次扫一条记录，相当于和磁盘做一次io操作，而且需要6次磁盘io）

如果col2字段加上二叉树索引
二叉树每一个节点存一个 字段值-所在行的磁盘文件地址
经过2次io
缺陷：
给排序字段添加索引时
在这里插入图片描述
需要6次io,和全表扫描几乎没有区别,还增加了索引的空间

2.红黑树

本质上是平衡二叉树,深度差为2时就会自旋保持平衡
在这里插入图片描述
缺陷:
大量数据会导致树的高度很高,500万行记录时树的高度20层,从根节点到叶子节点要20次io,高度不可控导致性能不稳定.

3.B-Tree

分配存储索引节点的空间变大,可以放更多的索引元素.树的高度就会降低
在这里插入图片描述

show global status like 'Innodb_page_size';

主键索引的叶子节点

为什么存大量数据的B+Tree的高度却很低?
每个树节点的空间大小为16kb,意味着每次从磁盘中会取出16kb的数据
如果bigint字段作为索引,大小为8B,下一级节点的地址分配6B,
非叶子节点: (索引字段+下一级节点的地址)一个索引节点可以放16384/(8+6)=1170个元素
叶子节点: (索引字段+当前元素所在行的地址值)大概可以放16个元素
高度为3的b+Tree放满可以放2千多万个元素

B+Tree查找元素的方式
把根节点加载到内存中RAM,折半查找.找到下一节点的地址,把下一节点也加载到内存中.然素.mysql非叶子节点在mysql初始化就加载到内存中了

叶子节点从左到右依次递增

4.mysql存储引擎

存储引擎是形容数据库的还是数据库表的?
MyISAM
叶子节点的索引元素和表数据分开的叫非聚集索引
在这里插入图片描述
一张表在磁盘中对应三个文件,分别是.frm(表结构) .MYD(表数据) .MYI(表索引)

InnoDB
主键索引的叶子节点数据和索引元素在一起就是聚集索引
非主键索引的叶子节点data放的是索引所在行的主键
左边是主键索引,右边是非主键索引
一张表在磁盘中对应三个文件,分别是.frm(表结构) .IBD(数据和数据)

为什么建议InoDB表必须建主键,并且推荐使用整型的自增主键?

   不建主键,在一张表的所有字段中找一个最适合做唯一索引的字段当主键索引.找不到默认维护一列row_id,作为主键索引.mysql维护主键索引会浪费性能
   uuid和雪花id不是整型也不自增(分库分表才会用)，折半查找整型更快,数据库一般存在ssd固态硬盘,整型可以节省磁盘空间不自增添加元素会导致索引树的维护会消耗更多的性能

4. HASH

时间复杂度O(1)
在这里插入图片描述
B+Tree叶子节点是全量有序数据所以支持范围查找

5. 联合索引

联合索引的底层存储结构长什么样?
它会按照三个索引字段进行排序，比较的时候如果没有左边的字段，则无法直接比较右边的。
在这里插入图片描述
联合索引最左前缀原理

select * from t where b = 2 and c = 2;//where条件有最左边的字段才会走联合索引
select * from t where c = 2;//不走联合索引

select * from t where f like '%a'//不会走f索引
select * from t where f like 'a%'//会走f索引

范围查找导致索引失效
如果走索引比不走索引慢，则mysql底层就不会走索引

select * from t where b>1;//走索引需要回表，所以全表扫描更快
select b from t;//走联合索引遍历的每个page数据量多，所以会走索引

在这里插入图片描述
此时全表扫描比先查询联合索引再回表查询行记录更快，所以没有走联合索引

索引覆盖

explain select a,b,c,d from t where b>1;

在这里插入图片描述
因为bcd联合索引叶子节点会存a,b,c,d字段数据，所以不需要回表操作，效率更高

*mysql当等号两端的数据类型不一样时，会先转为int 类型再比较，例如 ‘c’=>0 ,‘1’=>1

索引失效的几种情况

索引字段与条件或关联字段类型不一致，隐式类型转换不会走索引
使用like并且左边带%
在where条件里面对索引列使用运算或者函数
使用or且存在非索引列
使用in中的值很大时不会走索引
order by 如果不加limit 或者没有where+索引字段
order by 的字段不满足最左匹配规则

关联查询
原理,一次一行循环的从第一张表(驱动表)中读取行,在这行数据中渠道关联字段,更具关联字段在另一张表(被驱动表)力去除满足条件的行,然后取出两张表的结果合集
结论:使用inner join是mysql会选择数据量笑的表作为驱动表
如果被驱动表的关联字段没有索引,NLJ算法性能比较低
2.BNL
把驱动表的数据读入到join_buffer中,然后扫描被驱动表,把被驱动表每一行取出来,根join_buffer中的数据做对比

buffer_pool