高性能Mysql之索引基础

最新推荐文章于 2024-11-28 17:40:31 发布

原创最新推荐文章于 2024-11-28 17:40:31 发布 · 1.5k 阅读

18 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #索引

数据库同时被 3 个专栏收录

2 篇文章

订阅专栏

mysql

1 篇文章

订阅专栏

索引

1 篇文章

订阅专栏

本文探讨了MySQL中索引的重要性，包括减少数据扫描量、避免排序和临时表，以及提升I/O效率。重点讲解了Hash索引的工作原理、优缺点，如Hash冲突的解决方法以及其在InnoDB中的应用。此外，还介绍了如何自定义Hash索引以提高查询效率，以及在何种情况下索引并非最佳解决方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

索引的优点

索引大大减少了服务器需要扫描的数据量。
索引可以帮助服务器避免排序和临时表。
索引可以将随机I/O变为顺序I/O。

Hash索引

仅MEMORY存储引擎支持Hash索引

哈希索引是基于哈希表实现的，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码。哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

创建Hash索引例子如下

create table testhash(
	fname VARCHAR(50) not null,
	lname VARCHAR(50) not null,
	key using hash(fname)
)ENGINE MEMORY;

哈希索引只需要存储对应的hash值，所以索引结构很紧凑，查找速度非常快。

Hash冲突的解决

Hash冲突的解决很一般，利用链表存储hash值相同的行指针。

Hash索引的缺点

Hash索引只保存Hash值和行指针，不存储字段值，不能用索引中的值来避免读取行。
Hash值不是按照索引值顺序存储的，不能排序。
不支持部分索引，Hash索引的Hash值是根据索引列的所有值计算的，比如按照（A，B）建立Hash索引，就不能按照A来索引。
Hash索引只支持比较查询，=、IN、<=>,不支持范围比较，比如WHERE price > 100.
当出现Hash冲突的时候，必须要遍历所有链表中的指针，直到匹配为止。
Hash冲突很多的话，一些维护操作的代价会很高。

InnoDB的Hash索引

InnoDB也有一种基于Hash索引的功能，叫做自适应哈希索引(adaptive hash index)，它是在B-tree索引的上层加一层Hash索引，让B-Tree索引也具备Hash索引的一些优点。

自定义Hash索引

在某些情况下可以自定义一个B-Tree索引和Hash索引相结合的索引，另B-Tree结点的值不是索引列的值，而是索引列值的hash，这样通常能达到很高的效率。下面是例子。

创建一个表pseudohash

create table pseudohash(
	id int unsigned not null AUTO_INCREMENT,
	url varchar(255) not null,
	url_crc int unsigned not null default 0,
	primary key(id)
);

其中的url_crc是为了建立url的Hash索引而添加的额外的列，利用crc函数建立hash值
,查询方式如下。

SELECT id FROM pseudohash WHERE url = 'http://www.baidu.com' AND url_crc = CRC32('http://www.baidu.com');

定义触发器维护url_crc

定义一个触发器，在新增行和更新行的时候，自动出发更新url_crc的操作。

DELIMITER //
# 插入的时候
CREATE TRIGGER pseudohash_crc_ins BEFORE INSERT ON pseudohash FOR EACH ROW BEGIN
SET NEW.url_crc=CRC32(NEW.url);
END;
//
# 更新的时候
CREATE TRIGGER pseudohash_crc_upd BEFORE update ON pseudohash FOR EACH ROW BEGIN
SET NEW.url_crc=CRC32(NEW.url);
END;
//
DELIMITER ;

需要注意: 不要用SHA1()和MD5() 作为哈希函数，这两个函数计算的hash值是很长的字符串，会占用很大的空间，这两个函数的目标是尽量避免哈希冲突，但是性能较差。

因为有很大可能会出现Hash冲突，所以在查询的时候，不能单独使额外的新增列url_crc去查询, 比如下面的语句可能无法查到唯一结果。

select id from psedohash where url_crc=CRC32('http://www.baidu.com');

所以，想要用这种索引方式提高查询效率，查询的时候的WHERE子句必须同时带上索引列的Hash值和索引列的值。

SELECT id FROM pseudohash WHERE url = 'http://www.baidu.com' AND url_crc = CRC32('http://www.baidu.com');

索引不是最好的解决方案

只有当索引确实能够帮助我们缩短查询时间时候，索引才有用。但是对于非常小的表，索引反而没有直接全表扫描的效率高；对于中大型的表，索引非常有用；对于特大的表，建立索引和维护索引的开销会随着表的增大而增大，索引并不能完全解决查询性能问题，这时就要使用数据库的分区。这里不讨论分区。