mysql索引

最新推荐文章于 2024-10-02 12:42:27 发布

sula.xu

最新推荐文章于 2024-10-02 12:42:27 发布

阅读量146

点赞数

文章标签： mysql b树数据结构

本文链接：https://blog.csdn.net/qq_33286156/article/details/126021460

版权

数据加慢的原因是什么呢？
这个大概很多人都知道，io读取慢。io为什么读取慢，这就是我们下面要讲的问题。

首先mysql存储引擎常用的innoDB，MyISAM。
mysql 索引采用B+树的数据结构
数据结构：

Hash索引
1.采用hash存储的话，需要将所有的数据文件添加到内存，比较耗费内存空间。（内存相比磁盘来说，更珍贵）
2.hash值是无序的，再使用排序及范围查询，比较困难。如果所有的查询为等值查询，那么采用hash确实很快，而实际工作中范围查询更多。
结论：占内存，范围查询慢，mysql估摒弃了此数据结构
二叉树
平衡二叉树

查找>5的数过程
先找到5，经历3次io。然后再找>5,再往回找找到6，右边7，再回来到6 ，再往上到8。。。
所以如果大于5的数很多，导致树很深，则io次数增多，查询效率很慢。
红黑树

无论是二叉树或是红黑树，都会因为树的深度过深，而造成io次数变多，影响数据读取效率。
B树（也称B-树）

B树，解决了二叉树的哪些问题呢？
我们可以看到，二叉树的节点只有一个值，而B树存有2个值。同样是10这个数的查找，二叉树查找3次，而B树找10，则只需两次，因为树的深度变矮了，所以相对查找变快很多。这些只是因为数据量少，看起来并不明显。但是可以想象下如果上万数据呢，效率是不是一下就提上来了呢。
但是B树虽然解决了二叉树高度的问题，但也是无法解决二叉树的回旋查找。>5时，查找二叉树及B树都会存在回旋查找的过程。
B+树

特点：
1.一个节点可以存储2个值，降低树的深度。
2.每个叶子节点增加了单向链表排序，将所以的数据进行链表排序。

链表以上的节点，为非叶子节点只存储key。且非叶子节点也会出现在叶子节点。
而叶子节点存储的则是key及value。
从上图可以看出，B+树，在查找叶子节点时，比如10需要3次，效率没有B树高，但在查找范围的时候，则非常快，>5，查找到5后，>5的数一下就出来了。

实战：
我们先准备一个300万数据的表，以此为例做sql优化

#1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50),
first_name char(10),
last_name char(10)
);

#2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<3000000)do
        insert into s1 values(i,'xboyww','man',concat('xboyww',i,'@qq'),concat('王',i),concat('文',i));
        set i=i+1;
    end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号

#3. 查看存储过程
show create procedure auto_insert1;

#4. 调用存储过程
call auto_insert1();

没有任何索引查询

#没有任何索引查询
SELECT * FROM s1 WHERE last_name = '文20' and email='xboyww20@qq' and name = 'xboyww'

在这里插入图片描述

创建一个联合索引

#创建一个联合索引
ALTER TABLE s1 ADD INDEX idx_last_name_email_name(last_name,email,name);

在这里插入图片描述
再次执行刚刚的查询
可以看到查询效率大大提高，从之前的1.066s变成0.008s

索引创建：
1.单索引
2.联合索引（最佳左前缀索引）

我们来体验一下什么叫做最佳左前缀索引

#尝试最佳左前缀索引
SELECT * FROM s1 WHERE last_name = '文20' and email='xboyww20@qq'
EXPLAIN SELECT * FROM s1 WHERE last_name = '文20' and email='xboyww20@qq'

SELECT * FROM s1 WHERE email='xboyww20@qq' and name = 'xboyww' 
EXPLAIN SELECT * FROM s1 WHERE email='xboyww20@qq' and name = 'xboyww' 

SELECT * FROM s1 WHERE last_name = '文20' and name = 'xboyww' 
EXPLAIN SELECT * FROM s1 WHERE last_name = '文20' and name = 'xboyww' 

SELECT * FROM s1 WHERE last_name = '文20'
EXPLAIN SELECT * FROM s1 WHERE last_name = '文20'

SELECT * FROM s1 WHERE name = 'xboyww' 
EXPLAIN SELECT * FROM s1 WHERE  name = 'xboyww'

在这里插入图片描述

结论：
如果我们创建了(last_name, email,name)的复合索引，那么其实相当于创建了：
(last_name, email,name)，(last_name, email)、(last_name)三个索引，这被称为最佳左前缀原则。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。

1.在mysql中执行查询时，只能使用一个索引，如果我们在last_name, email,name上分别建单列索引,执行查询时，只能使用一个索引，**mysql会选择一个最严格(获得结果集记录数最少)的索引**。
2.加上组合索引后，组合索引起作用，只需查询一条符合结果的数据，效率要比单独索引高，**但是复合索引对于or查询不起作用**