MySQL的索引，你真的有好好理解过吗？

吾爱主题阅读：163 2024-04-02 18:52:54 评论：0

哈喽大家好!我是小三。今天我们来讲索引。

索引是什么?

「索引的概念」：索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里的所有记录的引用指针。通俗来说就是数据库索引就好像是一本书的目录，能够加快数据库的查询速度。

「索引的作用」：索引存在的目的就是在于提高查询效率，使得原始的随机全表扫描变成了快速顺序锁定数据

常用的索引分类：

1、普通索引：这是最基本的索引，没有任何的限制

2、唯一索引：引列的值必须唯一，但允许有空值(注意和主键不同)

3、组合索引：多个数据列组成的索引，遵守了最左匹配原则

索引高性能保证：

1、把查询过程中的随机事件变成了顺序事件

2、数据保存在磁盘上，而为了提高性能，每次又可以把一部分的数据读入内存来计算，访问磁盘的成本大概是访问内存的十万倍左右。

3、考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一系列的优化，当进行一次IO时，不光把当前磁盘的地址的数据也把相邻的数据也都读取到内存的缓冲区之内。因为局部的预读性原理告诉了我们，当计算机访问一个地址的数据的时候，与它响铃的数据也会很快被访问到。每一次IO读取的数据我们都称之为一页(page)。具体一页会有多大的数据，这跟操作系统有关，一般为4k或者是8k。

那为什么磁盘读取数据会很慢呢?

我们知道磁盘读取时间=寻道时间+旋转时间+传输时间，当需要从磁盘读取到数据的时候，系统会将数据的逻辑地址传给磁盘，磁盘的控制电路按照寻址逻辑将逻辑地址翻译成了物理地址，就确定了要读的数据在哪一个磁道，哪个扇区。为了读取扇区的数据，需要将磁头放到扇区的上方，为了实现这一点，磁头需要移动对准相应的磁道，这个过程叫寻道，在这里所耗费的时间叫做寻道时间，然后磁盘旋转目标扇区旋转到磁头下，这个过程耗费的时间叫做旋转时间。

索引的底层实现方案

我们使用索引的目的，自然是要提高查询的效率。例如像字典，如果要查询"mysql"这个单词，我们首先肯定是要定位到m字母，然后从下往下找到y字母，以此类推。

索引的设计难度

查询要求：等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)

数据量：超过一千万数据通过索引查询，查询性能保证

常见的检索方案分析

顺序检索：最基本的查询算法-复杂度O(n)，数据量大的话这个算法的效率是糟糕的

二叉树查找：O(log2n)，单层节点所能存储数据量较少，需要进行遍历多层才能拿到数据，总结点数k与高度h的关系为k=(2^h)-1

hash索引：无法满足范围查找，但是它的等值检索快，hash值==》物理地址x018，范围检索

B-Tree:每个节点都是一个二元数组：[key,data]，所有的节点都可以存储数据，key为索引key，data为除之外的数据

B+Tree数据结构高性能解析

B-Tree的缺点：插入删除新的数据记录会破坏掉B-Tree的性质，因此在插入删除时，需要对树进行一个分裂、合并、转移等操作来保持B-Tree的性质。在区间查找时可能需要返回上层节点重新，IO操作繁琐。

B+Tree的改进：非叶子节点不存储data，只存储了索引的key，只有叶子节点才存储data

「高性能的保证」：

第一、3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要进行三次IO的话，那么对性能的提高无疑是巨大的，如果没有索引的话，每个数据项都要发生一次IO那么就会有百万次的IO，这显然成本非常非常高。

第二、在B+Tree的每个叶子节点增加一个指向相邻子节点的指针，这样就形成了带有顺序访问指针的B+Tree

第三、B+Tree只在叶子节点来存储数据，所有的叶子节点包含一个链指针，其他内存的非叶子节点只存储索引数据。只利用索引快速的定位数据索引范围，先定位索引再通过索引高效的定位数据。

mysql为什么会选错索引

优化器的逻辑

Mysql Server层的优化器负责的是选择索引，而优化器选择索引的目的就是要找到一个最优的执行方案，并且用最小代价来执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，也就意味着访问的磁盘的数据次数就越小，消耗的CPU就越少。扫描行数并不是唯一的判断标准，优化器还会结合了是否使用临时表、是否排序等等因素来综合判断。

扫描行数是怎么判断的

Mysql在真正开始执行语句之前，并不可以精确的知道满足该查询条件的记录究竟有多少条，只能根据统计的信息来估算记录数。所以这个统计信息就是索引的“区分度”。显然，一个索引上面的值不同得越多，这个索引的区分度就越好。在一个索引上不同值的个数，称为基数。

那么，mysql是怎么样得到索引基数的?在这里mysql采样统计方法，但是为什么要使用采样统计这种方法呢?原因就是因为如果把整张表取出来然后进行一行行的统计，虽然这样能够得到精确的数据，但是代价也太高了，所以的话只能使用采样统计。

#创建表
CREATE TABLE `test` (
`id` int(11) NOT NULL,
`a` int(11) NOT NULL default 0,
`b` int(11) NOT NULL default 0,
PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB;
#添加数据
delimiter ;;
create procedure xddata()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into test values(i, i, i);
set i=i+1;
end while;
end;;
delimiter ;
call xddata();
数据查询
explain select * from test where (a between 1000 and 2000) and (b between 50000 and 100000) order by b limit 1;

「为什么会出现这种结果呢?」

在多个的索引情况下，优化器一般会通过比较了扫描行数、是否需要临时表以及是否需要排序等因素来作为索引的半段依据。

选择了索引b，则就需要在b索引上扫描9W条记录，然后回到主键索引上过滤掉不满足a条件的记录，因为索引有序，所以使用b索引不需要额外排序。

「解决方案」

使用force index a让mysql直接选择a索引来处理此处的查询

select * from test where (a between 1000 and 2000) and (b between 50000 and 100000) order by b limit 1;
select * from test force index(a) where (a between 1000 and 2000) and (b between 50000 and 100000) order by b limit 1;