MYSQL索引资源-CSDN文库

MYSQL索引

128 浏览量 2021-02-24 14:33:49 上传评论收藏 845KB PDF 举报

资源推荐

资源详情

资源评论

MYSQL索引索引

索引的类型

索引优化应该是对查询性能优化最有效的手段了。

mysql只能高效地使用索引的最左前缀列。

mysql中索引是在存储引擎层而不是服务器层实现的

B-Tree索引

B-Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。

图中根节点没有画出来。

B-Tree对索引列是顺序组织存储的，索引很适合查找范围数据。

B-Tree索引的限制

如果不是按照索引的最左列开始查找，则无法使用索引。

不能跳过索引中的列

如果查询中有某列的范围查询，则其右边所有列都无法使用索引优化查询。

这些限制都和索引列的顺序存储有关系。或者说是索引顺序存储导致了这些限制。

哈希索引（hash index）

哈希索引基于哈希表实现的，只有精确匹配索引所有列的查询才有效。

对于每一行数据，存储引擎都会对所有的索引列计算一个哈希值（hash code），哈希值是一个较小的值，并且不同键值的行

计算出来的哈希值不一样。哈希索引将所有的哈希值存储在索引中，同时保存指向每个数据行的指针，这样就可以根据，索引

中寻找对于哈希值，然后在根据对应指针，返回到数据行。

mysql中只有memory引擎显式支持哈希索引，innodb是隐式支持哈希索引的。

哈希索引限制：

哈希索引只包含哈希值和行指针，不存储字段值，所以不能使用"覆盖索引"的优化方式，去避免读取数据表。

哈希索引数据并不是按照索引值顺序存储的，索引也就无法用于排序

哈希索引页不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容计算哈希值的。

哈希索引只支持等值比较查询，包括=，in(),<=>，不支持任何范围查询。列入where price>100

访问哈希索引的数据非常快，除非有很多哈希冲突（不同的索引列值却有相同的哈希值）

如果哈希冲突很多的话，一些索引维护操作的代价也会很高。

因为这些限制，哈希索引只适用于某些特定的场合。而一旦适合哈希索引，则它带来的性能提升将非常显著。

innodb引擎有一个特殊的功能“自适应哈希索引”，当innodb注意到一些索引值被使用的非常频繁时，且符合哈希特点（如每次

查询的列都一样），它会在内存中基于B-Tree索引之上再创建一个哈希索引。这是一个完全自动的，内部行为。

创建自定义哈希索引，像模拟innodb一样创建哈希索引。

例如只需要很小的索引就可以为超长的键创建索引。

思路：在B-Tree基础上创建一个伪哈希索引。这和真正的哈希索引不是一回事，因为还是使用B-Tree进行查找，但是它使用

哈希值而不是键本身进行索引查找。需要做的就是在查询的where 子句中手动指定使用哈希函数。

例子：

如果需要存储大量的url，并需要根据url进行搜索查找。如果使用B-Tree来存储URL，存储的内容就会很大，因为URL本身都

很长。正常情况下会有如下查询：

mysql> select id from url where url='http://www.mysql.com';

若删除原来url列上的索引，而新增一个被索引的url_crc列，使用crc32做哈希。就可以实现一个伪哈希索引；查询就变成下面

的方式：

mysql> select id from url where url='http://www.mysql.com'

-> and url_crc=crc32("http://www.mysql.com");

这样性能会提高很多。

当然这样实现的缺陷是需要维护哈希值，就是url改变对应哈希值也应该改变。可以手动维护，当然最好是使用触发器实现。

创建URL表

create table URL （

id int unsigned NOT NULL auto_increment,

url varchar(255) NOT NULL,

url_crc int unsigned NOT NULL DEFAULT 0,

PRIMARY KEY (id),

KEY (url_crc)

）;

创建触发器：

delimiter //

create trigger url_hash_crc_ins before insert on URL FOR EACH ROW

BEGIN

SET NEW.url_crc=crc32(NEW.url);

END;

CREATE TRIGGER url_hash_crc_upd BEFORE UPDATE ON URL

FOR EACH ROW BEGIN

SET NEW.url_crc=crc32(NEW.url);

END;

delimiter ;

mysql> select * from URL;

+----+-----------------------+------------+

| id | url | url_crc |

+----+-----------------------+------------+

| 1 | htttp://www.mysql.com | 1727608869 |

+----+-----------------------+------------+

1 row in set (0.00 sec)

mysql> insert into URL(url) values('htttp://www.');

Query OK, 1 row affected (0.00 sec)

mysql> select * from URL;

+----+-----------------------+------------+

| id | url | url_crc |

+----+-----------------------+------------+

| 1 | htttp://www.mysql.com | 1727608869 |

| 2 | htttp://www. | 1196108391 |

+----+-----------------------+------------+

2 rows in set (0.00 sec)

mysql> UPDATE URL SET url='http://www.baidu.com' where id=2;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from URL;

+----+-----------------------+------------+

| id | url | url_crc |

+----+-----------------------+------------+

| 1 | htttp://www.mysql.com | 1727608869 |

| 2 | http://www.baidu.com | 3500265894 |

+----+-----------------------+------------+

2 rows in set (0.00 sec)

如果采用这种方式，不要使用SHA1()和MD5()作为哈希函数，应该这个函数计算出来的哈希值是非常长的字符串，会浪费大量

空间，比较时页回更慢。

而如果数据表非常大，crc32()会出现大量的哈希冲突，而解决哈希冲突，可以在查询中增加url本身，进行进一步排除；

如下面查询就可以解决哈希冲突的问题：

mysql> select id from url where url='http://www.mysql.com'

-> and url_crc=crc32("http://www.mysql.com");

空间数据索引（R-Tree）

myisam 表支持空间索引，可以用作地理数据存储。

全文索引

全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。第7章中会详细介绍

索引的优点

剩余19页未读，继续阅读

评论收藏

内容反馈

weixin_38637805

粉丝: 4
资源: 952

MYSQL索引

快速了解MySQL 索引

Mysql索引详细介绍

MySQL索引类型大汇总

mysql索引优化分享

MYSQL索引知识

MYSQL

mysql 索引的基础操作汇总（四）

MySQL联合索引用法示例

php mysql索引问题

MySQL索引原理

MySql索引优化

浅析mysql索引

MySQL 索引最佳实践

由浅入深探究mysql索引结构原理、性能分析与优化

MySQL索引面试.md

Mysql索引数据结构.pptx

7月6日 MySQL索引篇

mysql_MYSQL

MYSQL笔记_MYSQL_

MySQL索引

MySQL 索引扩展

MySQL索引的缺点以及MySQL索引在实际操作中有哪些事项

最新资源