MySQL进阶实战 3，mysql索引详解，上篇

发布时间：2022-11-02 15:32:17 所属栏目：MySql教程来源：转载

导读： 目录
一、索引
索引是存储引擎用于快速查找记录的一种数据结构。我觉得数据库中最重要的知识点，就是索引。
存储引擎以不同的方式使用B-Tree索引，性能也各有不同，各有优劣。例如MyISAM使

一、索引

索引是存储引擎用于快速查找记录的一种数据结构。我觉得数据库中最重要的知识点，就是索引。

存储引擎以不同的方式使用B-Tree索引，性能也各有不同，各有优劣。例如MyISAM使用前缀压缩技术使得索引更小，但InnoDB则按照原数据格式进行存储。MyISAM索引通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行。

在这里插入图片描述

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

B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根结点开始进行搜索。根结点的槽中存放了指向子结点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么找到对应的值，要么该记录不存在。

叶子节点比较特别，它们的指针指向的是被索引的数据，而不是其他的节点页。B-Tree对索引列是顺序组织存储的，所有很适合查找范围数据。B-Tree适用于全键值、键值范围或键前缀查找。

因为索引树中的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的order by操作。一般来说，如果B-Tree可以按照某种方式查找到值，那么也可以按照这种方式用于排序。

二、索引的优点索引大大减少了服务器需要扫描的数据量；索引可以帮助服务器避免排序和临时表；索引可以将随机IO变为顺序IO；三、哈希索引

哈希索引是基于哈希表实现的，只有精确匹配索引所有列的查询才会生效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时哈希表中保存指向每个数据行的指针。

因为索引自身只需存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的速度非常快。

哈希索引的缺点：

哈希索引无法用于排序；哈希索引不支持最左前缀原则；哈希索引只支持等值比较，不支持范围查询mysql索引表，比如where salary > 5000；

InnoDB引擎有一个特殊的功能叫“自适应哈希索引”。当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引，这就让B-Tree索引也具有哈希索引的一些优点。这是一个完全自动的、内部的行为，用户无法控制或配置。

四、哈希索引实例

如果一个表需要存储大量的URL，并需要根据URL进行搜索查找，如果使用B-Tree来存储URL，存储的内容就会很大，因为URL本身就很长，正常情况下会有如下查询：

select id,name from url where url = "http://www.baidu.com";

若删除原来URL列上的索引，而新增一个被索引的url-src列，使用SRC32做哈希，就可以使用下面的方式查询：

select id,name from url where url = "http://www.baidu.com" and url_crc = CRC32("http://www.baidu.com");

这样做的话，性能会提高很多，通过url_crc列进行哈希查找。

这样实现的缺点是需要维护哈希值，可以手动维护，也可以使用触发器实现。

五、前缀索引 1、独立的列

独立的列是指索引列不能是表达式的一部分，也不能是函数的参数，必须是等号前单独的列存在。

2、前缀索引和索引选择性

通常可以拿字符串开始的部分字符当索引，这样可以大大节约索引空间，从而提高索引效率。索引的选择性越高则查询效率越高，选择性高的索引可以让MySQL在查找时过滤掉更多的行。

一般情况下，对于blob、text、varchar类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。诀窍在于选择足够长的前缀以保证较高的索引选择性，同时又不能太长，最好使得前缀索引的选择性接近于索引整个列。

为了决定前缀的合适长度，需要找到最常见的值的列表，然后和最常见的前缀列表进行比较。

使用MySQL官方数据库提供的表：

CREATE TABLE city (
  city_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  city VARCHAR(50) NOT NULL,
  country_id SMALLINT UNSIGNED NOT NULL,
  last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (city_id),
  KEY idx_fk_country_id (country_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

在这里插入图片描述

用city列测试一下如何使用前缀索引。

3、通过sql获取city全列的索引选择性

select count(distinct city) / count(*) FROM city;

在这里插入图片描述

可以看到，结果为 0.9968，说明这一列的值是有重复的。

4、截取city列，获取合适的索引选择性

现在通过截取city列，来测试一下他们的索引选择性，使前缀的选择性接近于完整列的选择性就可以了。

SELECT 
	COUNT(DISTINCT LEFT(city,3))/COUNT(*) AS pref3,
	COUNT(DISTINCT LEFT(city,5))/COUNT(*) AS pref5,
	COUNT(DISTINCT LEFT(city,7))/COUNT(*) AS pref7,
	COUNT(DISTINCT LEFT(city,9))/COUNT(*) AS pref9,
	COUNT(DISTINCT LEFT(city,11))/COUNT(*) AS pref11,
	COUNT(DISTINCT LEFT(city,13))/COUNT(*) AS pref13,
	COUNT(DISTINCT LEFT(city,14))/COUNT(*) AS pref14,
	COUNT(DISTINCT LEFT(city,15))/COUNT(*) AS pref15 
FROM city;

在这里插入图片描述

我觉得截取5个的时候就可以了，这个时候再增加前缀长度，选择性的提升已经不明显了。但是如果数据分布很不均匀，可能就会有问题，

5、通过sql测试一下

select count(1) as cnt,left(city,5) as pref from city group by pref order by cnt desc limit 5

在这里插入图片描述

已经找到了合适的前缀长度，下面创建前缀索引：

alter table city add key city_idx(city(5));

在这里插入图片描述

前缀索引是一种能使索引更小、更快的方式，但MySQL中无法使用前缀索引做order by 和group by ，也无法使用前缀索引做覆盖扫描。

6、测试一下是否可以触发索引

explain select city_id,city,last_update from city where city = 'dalian'

在这里插入图片描述

触发了city_idx索引。

六、explain查询结果简介 1、id：SELECT识别符。这是SELECT的查询序列号。 2、select_type：SELECT类型： 3、table：表名 4、type：联接类型 system：表仅有一行(=系统表)。这是const联接类型的一个特例；const：表最多有一个匹配行，它将在查询开始时被读取。因为仅有一行，在这行的列值可被优化器剩余部分认为是常数。const用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时；eq_ref：对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。eq_ref可以用于使用= 操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式；ref：对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY(换句话说，如果联接不能基于关键字选择单个行的话)，则使用ref。如果使用的键仅仅匹配少量行，该联接类型是不错的。

ref可以用于使用=或操作符的带索引的列；ref_or_null：该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化；index_merge：该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素；unique_subquery：该类型替换了下面形式的IN子查询的ref：value IN (SELECT primary_key FROMsingle_table WHERE some_expr);unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高；index_subquery：该联接类型类似于unique_subquery。可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引：value IN (SELECT key_column FROM single_table WHERE some_expr；range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。当使用=、、>、>=、

（编辑：威海站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

什么时候可以考虑利用	try mysql5.5.8
mysql5.7.17 64位rhel	innobackuppex全备与复