2-3-2-5、高性能索引的创建策略

最新推荐文章于 2024-09-04 15:29:39 发布

析道者

最新推荐文章于 2024-09-04 15:29:39 发布

阅读量856

点赞数

分类专栏：进阶 MySQL索引基础文章标签：算法数据结构 java MySQL 索引

本文链接：https://blog.csdn.net/weixin_41381248/article/details/129208768

版权

进阶同时被 2 个专栏收录

54 篇文章 0 订阅

订阅专栏

MySQL索引基础

5 篇文章 0 订阅

订阅专栏

正确地创建和使用索引是实现高性能查询的基础

索引列的类型尽量小

在定义表结构的时候要显式的指定列的类型，以整数类型为例，有 TTNYINT、NEDUMNT、INT、BIGTNT 这么几种，它们占用的存储空间依次递增，这里所说的类型大小指的就是该类型表示的数据范围的大小。能表示的整数范围当然也是依次递增，如果想要对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，比如能使用 INT 就不要使用 BIGINT，能使用 NEDIUMINT 就不要使用 INT，这是因为:

数据类型越小，在查询时进行的比较操作越快（CPU 层次)
数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘/0 带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率

这个建议对于表的主键来说更加适用，因为不仅是聚簇索引中会存储主键值，其他所有的二级索引的节点处都会存储一份记录的主键值，如果主键适用更小的数据类型，也就意味着节省更多的存储空间和更高效的 I/0

索引选择性/离散型

创建索引应该选择选择性/离散性高的列。索引的选择性/离散性是指，不重复的索引值（也称为基数，cardinality)和数据表的记录总数（N)的比值，范围从 1/N 到 1 之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的行。唯一索引的选择性是 1，这是最好的索引选择性，性能也是最好的
很差的索引选择性就是列中的数据重复度很高，比如性别字段，不考虑特殊的情况，只有两者可能，男或女。那么在查询时，即使使用这个索引，从概率的角度来说，依然可能查出一半的数据出来
比如下面这个表：

上表中姓名字段最适合作为索引，因为里面的数据没有任何重复，性别字段是最不适合做索引的，因为数据的重复度非常高

计算索引的离散型

去重后的数据量/所有数据量

select COUNT(DISTINCT order_no)/count(*) cnt from order_exp;

select COUNT(DISTINCT order_status)/count(*) cnt from order_exp;

很明显，order_no 列上的索引就比 order_status 列上的索引的选择性就要好，原因很简单，因为 order_status 列中的值只有-1,0,1 三种

模拟哈希索引

order_exp 表中 order_note 字段很长，想把它作为一个索引，可以增加一个 order_not_hash 字段来存储 order_note 的哈希值，然后在 order_not_hash 上建立索引，相对于之前的索引速度会有明显提升，一个是对完整的 order_note 做索引，而后者则是用整数哈希值做索引，显然数字的比较比字符串的匹配要高效得多

缺点

需要额外维护 order_not_hash 字段
哈希算法的选择决定了哈希冲突的概率，不良的哈希算法会导致重复值很多
不支持范围查找

前缀索引

索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。一般情况下需要保证某个列前缀的选择性也是足够高的，以满足查询性能。（尤其对于 BLOB、TEXT 或者很长的 VARCHAR 类型的列，应该使用前缀索引，因为 MySQL 不允许索引这些列的完整长度）
诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间)。前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。换句话说，前缀的“基数”应该接近于完整列的“基数”
为了决定前缀的合适长度，可以找到最常见的值的列表，然后和最常见的前缀列表进行比较
首先找到最常见的值的列表：

SELECT COUNT(*) AS cnt,order_note FROM order_exp GROUP BY order_note ORDER BY cnt DESC LIMIT 20;

通过观察数据的分布，可以大胆的猜测，前 9 个字符的选择性不会太好，从第 10 个开始试一试：

SELECT 
	COUNT(DISTINCT LEFT(order_note, 3))/COUNT(*) AS sel3, 
	COUNT(DISTINCT LEFT(order_note, 4))/COUNT(*) AS sel4, 
	COUNT(DISTINCT LEFT(order_note, 5))/COUNT(*) AS sel5, 
	COUNT(DISTINCT LEFT(order_note, 6))/COUNT(*) As sel6, 
	COUNT(DISTINCT LEFT(order_note, 7))/COUNT(*) As sel7, 
	COUNT(DISTINCT LEFT(order_note, 8))/COUNT(*) As sel8, 
	COUNT(DISTINCT LEFT(order_note, 9))/COUNT(*) As sel9, 
	COUNT(DISTINCT LEFT(order_note, 10))/COUNT(*) As sel10, 
	COUNT(DISTINCT LEFT(order_note, 11))/COUNT(*) As sel11, 
	COUNT(DISTINCT LEFT(order_note, 12))/COUNT(*) As sel12, 
	COUNT(DISTINCT LEFT(order_note, 13))/COUNT(*) As sel13, 
	COUNT(DISTINCT LEFT(order_note, 14))/COUNT(*) As sel14, 
	COUNT(DISTINCT LEFT(order_note, 15))/COUNT(*) As sel15, 
	COUNT(DISTINCT order_note)/COUNT(*) As total 
FROM order_exp;

可以看见，从第 10 个开始选择性的增加值很高，随着前缀字符的越来越多，选择度也在不断上升，但是增长到第 15 时，已经和第 14 没太大差别了，选择性提升的幅度已经很小了，都非常接近整个列的选择性了
针对这个字段做前缀索引的话，从第 13 到第 15 都是不错的选择，甚至第 12 也不是不能考虑。当然不找到最常见的值的列表，直接计算前缀字符选择性也是可以的
在上面的示例中，已经找到了合适的前缀长度，然后进行创建前缀索引:

ALTER TABLE order_exp ADD KEY (order_note(14));

建立前缀索引后查询语句并不需要更改：

select * from order_exp where order_note = 'xxxx' ;

前缀索引是一种能使索引更小、更快的有效办法，但另一方面也有其缺点 MySQL 无法使用前缀索引做 ORDER BY 和 GROUP BY，也无法使用前缀索引做覆盖扫描
有时候后缀索引 (suffix index)也有用途（例如，找到某个域名的所有电子邮件地址)。MySQL 原生并不支持反向索引，但是可以把字符串反转后存储，并基于此建立前缀索引。可以通过触发器或者应用程序自行处理来维护索引

搜索、排序或分组的列创建索引

只为出现在 WHERE 子句中的列、连接子句中的连接列、ORDER BY 或 GROUP BY子句中的列创建索引，而出现在查询列表中的列一般就没必要建立索引了，除非是需要使用覆盖索引
比如：

SELECT * FROM order_exp ORDER BY insert_time, order_status,expire_time;

查询的结果集需要先按照 insert_time 值排序，如果记录的 insert_time 值相同，则需要按照 order_status 来排序，如果 order_status 的值相同，则需要按照 expire_time 排序。回顾一下联合索引的存储结构，u_idx_day_status 索引本身就是按照上述规则排好序的，所以直接从索引中提取数据，然后进行回表操作取出该索引中不包含的列就好了
当然 ORDER BY 的子句后边的列的顺序也必须按照索引列的顺序给出，如果给出 ORDER BY order_status,expire_time, insert_time 的顺序，那也是用不了 B+树索引的

SELECT insert_time, order_status,expire_time,count(*) FROM order_exp GROUP BY insert_time, order_status,expire_time;

这个查询语句相当于做了 3 次分组操作：

先把记录按照 insert_time 值进行分组，所有 insert_time 值相同的记录划分为一组
将每个 insert_time 值相同的分组里的记录再按照 order_status 的值进行分组，将 order_status 值相同的记录放到一个小分组里
再将上一步中产生的小分组按照 expire_time 的值分成更小的分组

然后针对最后的分组进行统计，如果没有索引的话，这个分组过程全部需要在内存里实现，而如果有了索引的话，恰巧这个分组顺序又和的 u_idx_day_status 索引中的索引列的顺序是一致的，而的B+树索引又是按照索引列排好序的，所以可以直接使用 B+树索引进行分组。和使用B+树索引进行排序是一个道理，分组列的顺序也需要和索引列的顺序一致

多列索引

在一个多列 B-Tree 索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。所以，索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的 ORDER BY、GROUP BY 和 DISTINCT 等子句的查询需求
经验法则：将选择性最高的列放到索引最前列。当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化 WHERE 条件的查找。在这种情况下，这样设计的索引确实能够最快地过滤出需要的行，对于在 WHERE 子句中只使用了索引部分前缀列的查询来说选择性也更高
然而，性能不只是依赖于索引列的选择性，也和查询条件的有关。可能需要根据那些运行频率最高的查询来调整索引列的顺序，比如排序和分组，让这种情况下索引的选择性最高
同时，在优化性能的时候，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求

三星索引

三星索引概念

对于一个查询而言，一个三星索引，可能是其最好的索引
如果查询使用三星索引，一次查询通常只需要进行一次磁盘随机读以及一次窄索引片的扫描，因此其相应时间通常比使用一个普通索引的响应时间少几个数量级
三星索引概念是在《Rrelational Database Index Design and the optimizers》一书中提出来的。原文如下：

The index earns one star if it places relevant rows adjacent to each other, a second star if its rows are sorted in the order the query needs, and a final star if it contains all the columns needed for the query.

译文：

索引将相关的记录放到一起则获得一星
如果索引中的数据顺序和查找中的排列顺序一致则获得二星
如果索引中的列包含了查询中需要的全部列则获得三星

一星（相关索引星）

一星按照原文稍微有点难以理解，其实它的意思就是：如果一个查询相关的索引行是相邻的或者至少相距足够靠近的话，必须扫描的索引片宽度就会缩至最短，也就是说，让索引片尽量变窄，也就是我们所说的索引的扫描范围越小越好

二星（排序星）

在满足一星的情况下，当查询需要排序，group by、 order by，如果查询所需的顺序与索引是一致的（索引本身是有序的），是不是就可以不用再另外排序了，一般来说排序可是影响性能的关键因素

三星（宽索引星）

在满足了二星的情况下，如果索引中所包含了这个查询所需的所有列（包括 where 子句和 select 子句中所需的列，也就是覆盖索引），这样一来，查询就不再需要回表了，减少了查询的步骤和 IO 请求次数，性能几乎可以提升一倍

三星索引的优先选择性

第三颗星相对最重要，因为将一个列排除在索引之外可能会导致很多磁盘随机读（回表操作）。第一和第二颗星重要性差不多，可以理解为第三颗星比重是 50%，第一颗星为 27%，第二颗星为 23%，所以在大部分的情况下，会先考虑第一颗星，但会根据业务情况调整这两颗星的优先度

案例

符合三星索引的案例

表DDL语句如下：

create table customer(
  cno int,
  lname varchar(10),
  fname varchar(10),
  sex int,
  weight int,
  city varchar(10)
);
create index idx_cust on customer(city,lname,fname,cno);

对于下面的 SQL 而言，这是个三星索引：

select cno,fname from customer where lname =’xx’ and city =’yy’ order by fname;

第一颗星：所有等值谓词的列，是组合索引的开头的列，可以把索引片缩得很窄，符合
第二颗星：order by 的 fname 字段在组合索引中且是索引自动排序好的，符合
第三颗星：select 中的 cno 字段、fname 字段在组合索引中存在，符合

不符合三星索引的案例

表DDL语句如下：

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(100) DEFAULT NULL,
  `sex` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `c_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

SQL 语句如下：

select user_name,sex,age from test where user_name like 'test%' and sex =1 ORDER BY age;

场景一

如果我们建立索引(user_name,sex,age)：
第三颗星，满足
第一颗星，满足
第二颗星，不满足
user_name 采用了范围匹配，sex 是过滤列，此时 age 列无法保证有序的，此时索引(user_name,sex,age)并不能满足三星索引中的第二颗星（排序）

场景二

修改为索引(sex, age，user_name)：
第一颗星，不满足，只可以匹配到 sex，sex 选择性很差，意味着是一个宽索引片
第二颗星，满足，等值 sex 的情况下，age 是有序的
第三颗星，满足，select 查询的列都在索引列中
对于索引(sex,age，user_name)可以看到，此时无法满足第一颗星，窄索引片的需求
以上 2 个索引，都是无法同时满足三星索引设计中的三个需求的，我们只能尽力满足 2 个。而在多数情况下，能够满足 2 颗星，已经能缩小很大的查询范围了，具体最终要保留那一颗星（排序星 or 窄索引片星），这个就需要看不同的业务场景，没有标准的选择

主键是很少改变的列

行是按照聚集索引物理排序的，如果主键频繁改变(update)，物理顺序会改变，MySQL 要不断调整 B+树，并且中间可能会产生页面的分裂和合并等等，会导致性能会急剧降低

冗余和重复索引

MySQL 允许在相同列上创建多个索引，无论是有意的还是无意的。MySQL 需要单独维护重复的索引，并且优化器在优化查询的时候也需要逐个地进行考虑，这会影响性能。重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免这样创建重复索引，发现以后也应该立即移除
冗余索引和重复索引有一些不同。如果创建了索引(A B)，再创建索引(A)就是冗余索引，因为这只是前一个索引的前缀索引。因此索引(AB)也可以当作索引(A)来使用（这种冗余只是对 B-Tree 索引来说的)。但是如果再创建索引 (B,A)，则不是冗余索引，索引(B)也不是，因为 B 不是索引(A,B)的最左前缀列
已有的索引(A)，扩展为(A，ID)，其中 ID 是主键，对于 InnoDB 来说主键列已经包含在二级索引中了，所以这也是冗余的
解决冗余索引和重复索引的方法很简单，删除这些索引就可以，但首先要做的是找出这样的索引。可以通过写一些复杂的访问 INFORMATION_SCHEMA 表的查询来找