高性能mysql 读书笔记创建高性能的索引

最新推荐文章于 2022-12-03 13:58:30 发布

q435201823

最新推荐文章于 2022-12-03 13:58:30 发布

阅读量223

点赞数

分类专栏： -------基础------- mysql

本文链接：https://blog.csdn.net/q435201823/article/details/103214935

版权

-------基础------- 同时被 2 个专栏收录

12 篇文章 0 订阅

订阅专栏

mysql

11 篇文章 0 订阅

订阅专栏

1.索引基础

对于一个SQL，MySQL先在索引上按值进行查找，然后返回所有包含该值的数据行。
索引可以包含一个或多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为MySQL只能高效地使用索引的最左前缀列。创建一个包含两个列的索引和创建两个只包含一列的索引是大不相同的，下面将详细介绍。

如果使用的是ORM，是否还需要关心索引？（未读懂）
简而言之：是的，仍然需要理解索引，即使是使用对象关系映射（ORM）工具。

1.1 索引的类型

索引是在存储引擎层实现的而不是服务器层。使用，没有统一的索引标准，不同存储引擎的索引的工作方式并不一样，也不是索引的存储引擎都支持所有类型的索引。即使多个存储引擎都支持同一种类型的索引，底层实现也不同。

1.1.1 B-Tree索引

这一篇文章写的不错，在看B-Tree索引之前可以先复习一下B树。->B树详解

没有特别指明，索引一般指的是B-Tree索引。

不同的存储引擎使用的是不同的存储结构，比如NDB存储引擎内部使用T-Tree，即时名字是BTREE；InnoDB使用的是B+Tree，还有各种数据结构和算法的变种。

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

B-Tree索引

B-Tree能加快数据访问速度。使用B-Tree后存储引擎不需要全表扫描，而是从索引的根节点开始进行搜索。每个非叶子节点都有指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层。

叶子节点比较特别，他们的指针指向的是被索引的数据。

被B-Tree存储的索引都是顺序存储的。

索引对多个值进行排序的依据是create table语句中定义索引时列的顺序。

举个例子：

create table people(
	last_name varchar(50)  not null,
	first_name varchar(50)  not null,
	dob            date             not null,
	gender       enum('m','f') not null,
	key(last_name,first_name,dob)
);

对于表中的每一行数据，索引中包含了last_name、first_name和dob列的值，下图显式了该索引是如何组织数据的存储的。

在这里插入图片描述

B-Tree索引适合的查找类型（最左前缀）

被B-Tree存储的索引都是顺序存储的，所以在查找上的效率是很高的。B-Tree适用于查找以下类型：

全值匹配
全值匹配指的是和索引中的所有列进行匹配，例如前面提到的索引可用于查找姓名为Cuba Allen、出生与1960-01-01的人。
匹配最左前缀
可以查找所有姓为Allen的人，即只使用索引的第一列。
匹配列前缀
值匹配某一个列的开头部分。例如以J开头的姓的人。这里也只使用索引的第一列。
匹配范围值
查找一个范围，可以查找姓在Allen和Barrymore之间的人。这里也只使用索引的第一列。
精确匹配某一列并范围匹配另外一列
可以查找索引姓为Allen，并且名字是K开头的人。即第一列last_name全匹配，第二列first_name范围匹配。
只访问索引的查询
B-Tree通常可以支持“只访问索引的查询”，即查询只需要访问索引，无需访问数据。

可能有的人已经注意到了，索引的查找只能“从头开始”，不能跳过一段到后面去查找。（最左前缀）

关于B-Tree索引的限制

如果不是按照索引的最左列开始查找，则无法使用索引。例如不能找特定生日的人，不能查找姓氏以某字母结尾的人。
不能跳过索引中的列。也就是说，无法查找姓，并且在某个特定生日的人。
如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。

1.1.2 哈希索引

不熟悉哈希表的同学可以先搜索学习一下，也可以看这一篇文章，以前写的，不过是关于C++的哈希表。STL浅谈 hashtable

哈希索引基于哈希表实现，只有精准匹配所有列的查询才有效。存储引擎对所有的索引列计算一个哈希码作为键，所以索引中存储该哈希码，并保存指向该行的指针。
当有一个查询时，先将所需要查询的数据哈希，再在索引中查找是否有对于的哈希码，如果有就给出对应的指针。

在MySQL中，只有Memory引擎显式支持哈希索引，也是Memory引擎表的默认索引类型。

哈希索引针对碰撞的方法是开链法。

哈希索引的限制

哈希索引只保存哈希值和行指针，不存储字段值，所以不能使用索引中的值来避免读取行，不过访问内存中的行的速度很快，索引大部分情况下这一点堆性能影响不大。
哈希索引数据并不是按照索引值顺序存储的，索引无法用于排序。
哈希索引也不支持部分索引列匹配查找。（因为索引里面存储的是哈希码嘛，索引根本就查不了）
哈希索引只支持等值比较查询，包括 =、IN（）、<=>（注意<>和<=>不是同一种操作）。不支持范围查询。
哈希索引访问速度非常快，除非有非常多的哈希冲突（因为要去访问非常长的链表）。
哈希冲突很多的话，某些索引维护操作代价也很高。

创建自定义哈希索引
书中描述了在B-Tree基础上创建伪哈希索引的方法，这里不做阐述。感兴趣的可以自行阅读。

1.1.3 空间数据索引（R-Tree）

MyISAM表支持空间索引，可以用作地理数据存储。和B-Tree索引不同，这类索引无需前缀查询。空间索引会从所有维度来索引数据。查询时可以有效地使用任意维度来组合查询。

1.1.4 全文索引

全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文搜索和其他积累索引的匹配方法完全不同。它有许多需要注意的细节，比如停用词，词干和复数，布尔搜索等。全文索引更类似与搜索引擎做的事情，而不是简单的where条件匹配。

1.1.5 其他索引类别

还有其他很多第三方的存储引擎使用不同类型的数据结构来存储索引。例如TokuDB使用分形树索引。

2 索引的优点

总结下来索引有如下三个优点：

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

索引是最好的解决方案吗？
对于非常小的表，大部分情况下，简单的全表扫描更高效。对于中到大型的表，索引就非常有效。但对于特大型的表，建立和使用索引的代价将随之增长。（索引太多了，扫描索引也很耗时）。
如果表的数量特别多，可以建立一个元数据信息表（元数据就是描述数据的数据，记录数据有扫描特征）。对于TB级的数据，定位单条记录的意义不打，所以进程使用块级别元数据技术来代替索引。

3 高性能的索引策略

3.1 独立的列

如果查询中的列不是独立的，则MySQL就不会使用索引。“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。

如：

select actor_id from sakila.actor_id + 1 = 5;

3.2 前缀索引和索引选择性

索引选择性
我们先解释一下索引选择性。索引选择性是指，不重复的索引和数据表的记录总数的比值。简单的说，就是索引值最好不要重复，重复越少，选择性越高。
索引的选择性越高则查询效率越高，选择性高的索引可以过滤更多行。索引选择性是1是最好的。

前缀索引
就是截取字符串的前面一部分作为索引，比如123456789截取为123作为索引。

因为截取，所以选择性可能会有所降低。比如123456和123789截取前3个字符都是123。导致了重复，于是选择性就降低了。

blob、text或者很长的varchar必须使用前缀索引，MySQL不允许索引这些列的完整长度。（如果允许那开销有多大可以想象一下。）

索引太长空间开销大，时间开销也大。

矛盾就在于保证选择性的同时，又不能索引太长。所以技巧也就是保证足够的选择性同时又不能太长。

MySQL无法使用前缀索引做order by 和group by。

3.3 多列索引

create table t(
	c1 int,
	c2 int,
	c3 int,
	key(c1),
	key(c2),
	key(c3)
)

这种索引策略一般是由于人们听到一些专家诸如“把where条件里面的列都建上索引”这样模糊的建议导致的。这样是错的，性能很差。多个列上建立独立的索引大部分情况下并不能提高MySQL的查询性能。

举个例子：
在旧版本中，对于下面这个查询where条件，这两个单列索引都不是好的选择

select file_id,actor_id from sakila.film_actor
where actor_id = 1 or film_id = 1;

在老版本中，MySQL堆这个查询会使用全表扫描。除非改成如下的两个查询union的方式：

select file_id,actor_id form sakila.film_actor where actor_id = 1
union all
select film_id,actor_id from sakila.film_actor where film_id = 1
    and actor_id <> 1;

MySQL 5.0引入了“索引合并”策略。也就是说，在一定程度上我们可以使用原先的那种蠢办法。查询能够同时使用两个单列索引进行扫描，并将结果进行合并。这种算法有三个变种：or条件的联合（union），and条件的相交（intersection），组合前两种情况的联合及相交。下面的查询就是使用了两个索引扫描的联合，通过EXPLAIN中的Extra列可以看到这点：

在这里插入图片描述
MySQL会使用这类技术优化复杂查询，索引某些语句的Extra列中可以看到嵌套操作。

索引合并策略有时候是一种优化的结果，但实际上更多时候说明了表上的索引建的很糟糕：

当出现服务器对多个索引做相交操作时（通常是有多个and条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立索引。
当服务器需要堆多个索引做联合操作时（通常是有多个or条件），通常需要耗费大量的CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高、需要合并扫描返回的大量数据的时候。
优化器不会把这些计算到查询成本中，优化器只关心随机页面读取，导致可能不如直接走全表扫描。
或许像旧版本一样使用union会更好。

3.4 选择合适的索引列顺序

3.4.1 索引选择性法则

索引选择性法则：将选择性最高的列放到索引最前列。

举个例子

select * from payment where staff_id = 2 and customer_id = 584;

跳过测试步骤，现在可以告诉你customer_id的选择性比staff_id的选择性高。索引应该建立一个（customer_id，staff_id）的索引，而不是（staff_id，customer_id）的索引。

3.4.2 基数法则

基数法则：当列的基数非常大，索引基本上就没什么用，使用全表扫描更好。

举个例子：

select count（distinct threadId）as count_value
from message
where (groupId = 10137) and (userId = 1288826) and (anonymous = 0)
order by priority desc,modifieddate desc

不考虑基数，使用索引是个不错的选择。
在这里插入图片描述
但如上图，在表中的用户数量实际有1288496，所以要在这1288496中建立索引实际上没什么用

3.5 聚簇索引

聚簇索引：首先聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。将数据存储与索引放到了一块，找到索引也就找到了数据。

当表有聚簇索引时，它的数据行实际上存放在索引的叶子页中。术语“聚簇”表示数据行和相邻的键值紧凑存储在一起。因为无法同时吧数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引（覆盖索引可以模拟聚簇索引）。

下图展示了聚簇索引的存储。叶子页包含了行的全部数据，但是节点也只包含了索引列。
在这里插入图片描述

在高性能MySQL这本树写作之时，还没有任何一个MySQL的内建存储引擎支持[允许选择那个索引作为聚簇索引]，只允许将主键作为聚簇索引。如果没有主键会选择一个唯一的非空索引代替。如果还没有这样的索引，那么会隐式定义一个主键作为聚簇索引。

由于行数据和叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中，再次访问的时候，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。

3.5.1 聚簇索引的优点

可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户ID来聚簇数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘I/O。
数据访问更快了。聚簇索引将索引和数据一同保存在B-Tree中，因此从聚簇索引中获取数据通常比非聚簇索引快。
使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

3.5.2 聚簇索引的缺点

聚簇索引最大限度地提高了I/O密集型隐隐痛的性能，但如果数据全部存放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没什么优势。
插入速度严重依赖于插入顺序。按照主键的顺序插入就是加载数据到InnoDB表中速度最快的方式。但是如果不是按照主键顺序加载数据，那么在加载完成后最好使用optimize table命令重新组织一下表。
更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新位置。（索引要排序）
基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行时，可能面临页分裂的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页来存储，这就是一次页分裂操作。页分裂会导致表占用更多磁盘空间。
聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续。
二级索引（非聚簇索引）可能比想象的要更大，因为二级索引的叶子节点包含了引用行的主键列。
二级索引访问需要两次索引查找，而不是一次。（二级索引叶子节点保存的不是指向行的物理位置，而是行的主键。）

3.5.3 InnoDB和MyISAM的数据分布比较

create table layout_test(
	col1 int not null,
	col2 int not null,
	primary key(col1),
	key(col2)
);

假设该表的主键取值为1~10000，按照随机顺序插入并使用optimize table命令做优化，话句话说，数据在磁盘上的存储方式语句是最优的，但行的顺序是随机的。列col2的值是从1 ~ 100之间随机赋值，所以有很多重复。

3.5.3.1 MyISAM的数据分布

MyISAM的数据分布非常简单，MyISAM按照数据插入的顺序存储在磁盘上。
在这里插入图片描述
在行旁边显式了行号，从0开始递增。因为行是定长的，所以MyISAM可以从表的开头跳过所需要的字节找到行。（不定长会使用其他策略。）
第二张图是第一张图的主键col1的分布。

第三张是col2列索引的分布。和其他索引没什么区别。

3.5.3.2 InnoDB的数据分布

在这里插入图片描述
该图是InnoDB的主键分布图。
可以看出不只存储了索引，还存储了整张表。因为在InnoDB中，聚簇索引“就是”表，所以不像MyISAM那样需要独立的行存储。

聚簇索引的每一个叶子节点都包含了主键值、事务ID、用于事务和MVCC的回滚指针以及所有的剩余项（在这个例子中是col2）。如果主键是一个列前缀索引，InnoDB也会包含完整的主键列和剩下的其他列。

InnoDB的二级索引分布
InnoDB的二级索引和聚簇索引很不同。二级索引的叶子节点中存储的不是“行指针”，而是主键值。并以此作为指向行的“指针”（实际上不是指针，只是功能相同。）。这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。使用主键当指针会让二级索引占用更多空间，换来的好处是，InnoDB在移动时无需更新二级索引中的这个“指针”。在这里插入图片描述
上图是InnoDB表中的二级索引分布。
从下图可以很容易看出InnoDB和MyISAM保存数据和索引的区别。

3.5.4 在InnoDB表中按照主键顺序插入行

按照主键顺序插入能够有更好的性能。（因为对磁盘是顺序I/O）

最好避免随机的聚簇索引，特别是I/O密集型的应用。这会使得性能变得糟糕。

如果不是按顺序插入，有如下缺点：

写入的目标页可能已经刷新到磁盘上并从缓存中移除，或者是还没有被加载到缓存中，InnoDB在插入之前不得不先找到并从磁盘读取目标页到内存中。这将导致大量的随机I/O。
因为写入是乱序的，InnoDB不得不频繁地做页分裂操作，以便为新的行分配空间。页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个页。
由于频繁的页分裂，页会变得系数并被不规则地填充，所以最终数据会有碎片。

所以使用InnoDB应该仅可能地按主键顺序插入数据，并且尽可能地使用单调增加的聚簇键的值来插入新行。

3.5.5 顺序的主键什么时候会造成更坏的结果

对于高并发工作负载，在InnoDB中按主键顺序插入可能会造成更明显的争用。主键的上界会成为“热点”。因为所有的插入都发生在这里，所以并发插入可能会导致间隙锁竞争。另一个热点可能是AUTO_INCREMENT锁机制；如果遇到这个问题，则可能需要考虑重新设计表或者应用，或者更改innodb_autoinc_lock_mode配置。如果你的服务器版本还不支持innodb_autoinc_lock_mode参数，可以升级到新版本的InnoDB，可能对这种场景会工作得更好。

3.6 覆盖索引

覆盖索引就是让索引的叶子节点中包含需要查询的数据（而不是地址）。这样就可以直接在索引中读取数据。（和聚簇索引是有区别的哦）
覆盖索引能极大地提高性能，有以下好处：

索引条目通常远小于数据行大小，所以如果只是需要读取索引，那么MySQL就会极大地减少数据访问量。这对缓存的负载非常重要，因为这种情况下响应时间大部分花费在数据拷贝上。覆盖索引对于I/O密集型的应用也很有帮助，因为索引比数据更小，更容易全部放入内存中（这对于MyISAM尤其正确，因为MyISAM能压缩索引以变得更小）。
因为索引是按照列值顺序存储的（至少在单个页内是如此），索引对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少的得多。对于某些存储引擎，例如MyISAM和Percona XtarDB，甚至可以通过OPTIMIZE命令使得索引完全顺序排列，这让简单的范围查询能使用完全顺序的索引访问。
一些存储引擎如MyISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。这可能会导致严重的性能问题，尤其是那些系统调用了占了数据访问中的最大开销的场景。
由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。

**不是所有类型的索引都可以成为覆盖索引。**覆盖索引要存储索引列的值，而哈希索引、空间索引、和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引。

3.7 使用索引扫描来做排序

MySQL有两种方式可以生成有序的结果：

通过排序操作（O（nlogn））
按索引顺序扫描（O（n），索引是已经原先已经排序好的）

所以很明显，在某些情况下使用索引扫描来做排序是非常高效的。
只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一张表时，才能使用索引做排序。ORDER BY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求；否则，MySQL都需要执行排序操作，而无法利用索引排序。

有一种情况下ORDER BY子句可以不满足最左前缀的要求，就是前导列为常量的时候。如果WHERE子句或者JOIN子句中对这些列指定了常量，就可以“弥补”索引的不足。

3.8压缩（前缀压缩）索引

MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中，这在某些情况下能极大地提高性能。默认只压缩字符串，但通过参数设置也可以对整数做压缩。

MyISAM压缩每个索引块的方法是，先完全保存索引块中的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分，把这部分存储起来即可。例如，索引块中的第一个值是“perform”，第二个值是“performance”，那么第二个值的前缀压缩后存储的是类似“7,ance”这样的形式。MyISAM对行指针也采用类似的前缀压缩方式。

压缩块使用更少的空间，代价是某些操作可能更慢。因为每个值的压缩前缀都依赖前面的值，所以MyISAM查找时无法在索引快使用二分查找而只能从头开始扫描。正序的扫描速度还不错，但是如果是倒序扫描——例如ORDER BY DESC ——就不是很好了。所有在块中查找某一行的操作平均都需要扫描半个索引块。