MySQL中如何创建高性能索引

索引的弊端

这么说，索引这么好用，我们疯狂创建索引岂不是速度飞起？其实并不是的，在学习如何更好的使用索引之前先要了

解一下使用它的代价，它在空间和时间上都会拖后腿。

空间上的代价

这个是显而易见的，每建立一个索引都要为它建立一棵 B+树，每一棵 B+树 的每一个节点都是一个数据页，一个页默认会占用 16KB 的存储空间，一棵很大 的 B+树由许多数据页组成会占据很多的存储空间。

时间上的代价

每次对表做出任何修改，我们都需要去维护该表下面所有的索引信息。这必然会对性能造成影响。

那么如何建立必要的索引呢？

高性能的索引创建策略

索引类型尽量选小的

正确地创建和使用索引是实现高性能查询的基础。前面我们已经了解了索引 相关的数据结构，各种类型的索引及其对应的优缺点。现在我们一起来看看如何真正地发挥这些索引的优势。

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

• 数据类型越小，在查询时进行的比较操作越快（CPU 层次)
• 数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，并且每一页下面能接多少子节点，也和一页中的记录数密不可分。因此，一个页放更多的索引可以让树更加的矮胖。从而减少磁盘I0 带来的性能损耗。同时，InnoDB读取数据也是以页为单位的，因此可以读取更多的数据到内存中，提高查询命中率。

主键索引更加适用（因为它无处不在）

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

索引的选择性/离散性

创建索引应该选择选择性/离散性高的列。索引的选择性/离散性是指，不重复的索引值（也称为基数，cardinality)和数据表的记录总数（N)的比值，范围从 1/N 到 1 之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的行。唯一索引的选择性是 1，这是最好的索引选择性，性能也是最好的。

很差的索引选择性就是列中的数据重复度很高，比如性别字段，不考虑政治 正确的情况下，只有两者可能，男或女。那么我们在查询时，即使使用这个索引， 从概率的角度来说，依然可能查出一半的数据出来。（那索引存在的意义基本没有了）

比如下面这个表：

哪列做为索引字段最好？当然是姓名字段，因为里面的数据没有任何重复， 性别字段是最不适合做索引的，因为数据的重复度非常高。

如何计算字段在表中的分离度

如果我们自己不好区分字段的分离度，要怎么计算字段的分离度呢？

可以用distinct计算去重后的字段总量进行对比。可以轻易的比较出结果。

如何建立一个特别长的字符串类型索引

MySQL索引长度的计算规则

1. 所有的索引字段，如果没有设置not null，则需要加一个字节。
2. 定长字段,int占4个字节、date占3个字节、char(n)占n个字符。
3. 变长字段,varchar(n)，则有n个字符+两个字节。注：不同的字符集，一个字符占用的字节数不同。latin1编码的，一个字符占用1个字节，gbk编码的，一个字符占用2个字节，utf8编码的，一个字符占用3个字节。 utf8mb4是一个字符占4个字节。

但无论如何，我们知道变长的varcahr类型的索引长度与该字符串长度本身是有紧密关系的。使用explain语句查询到的key_len字段,可以适用于上面的计算规则。

需求描述

假设现在我们要设置下面这张表的order_note作为索引列。

该怎么做呢？直接把整列数据直接索引，这会让索引变得大且慢（造成磁盘页内存放的索引更少，造成更频繁的IO）。一个策略是前面提 到过的模拟哈希索引。

模拟哈希索引

查询效率提升

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

明显缺点

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

前缀索引

还可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。一般情况下我们需要保证某个列前缀的选择性也是足够高的，以满足查询性能。（尤其对于 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 个开始应该还不错。试一试：

第一步：查找不同长度的前缀的分离度

我们用以下sql查找不同前缀的索引分离度。

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 没太大差别了，选择性 提升的幅度已经很

也不是不能考虑。当然不找到最常见的值的列表，直接计算前缀字符选择 性也是可以的。 在上面的示例中，已经找到了合适的前缀长度，如何创建前缀索引:

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

建立前缀索引并不会影响我们的查询语句，也就是我们查询的时候直接where order_note='xxxx'即可。

去重存储+前缀索引

遇到这种前缀重复率极高的值。即时使用前缀索引也未必能有效的缩减索引长度。因此我们可以自己再维护一个列，将前面大量重复的字段进行去重后存储。之后再配合前缀索引进一步缩短索引的长度。

前缀索引的缺点

前缀索引是一种能使索引更小、更快的有效办法，但另一方面也有其缺点 MySQL 无法使用前缀索引做 ORDER BY 和 GROUP BY，也无法使用前缀索引做覆盖扫描。

后缀索引的真面目

有时候后缀索引 (suffix index)也有用途（例如，找到某个域名的所有电子邮 件地址)。MySQL 原生并不支持反向索引，但是可以把字符串反转后存储，并基 于此建立前缀索引。可以通过触发器或者应用程序自行处理来维护索引。

数据库建立索引常用的规则

1. 表的主键、外键必须有索引； 
2. 数据量超过300的表应该有索引； 
3. 经常与其他表进行连接的表，在连接字段上应该建立索引； （被关联的表可以通过关联后快速查找）
4. 用于搜索：经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
5. 用于排序：经常出现<,><=,>=的字段应该用索引；
6. 用于分组：ORDER BY 或 GROUP BY 子句中的列创建索引；(注意：如果是联合索引和order by的索引值完全一致，则order by后面跟的值必须和联合索引顺序保持一致，否则用不上联合索引)。
7. 索引应该建在选择性高的字段上； 
8. 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引； 
9. 复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：
10. 频繁进行数据操作的表，不要建立太多的索引； 
11. 删除无用的索引，避免对执行计划造成负面影响；

以上是一些普遍的建立索引时的判断依据。 

索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。 

因为太多的索引与不充分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。 另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。 

总的来说，小型表肯定不建索引， 

或者数据库记录在亿条数据级以上，还是建议使用非关系型数据库。 

还有些特殊字段的数据库，比如BLOB，CLOB字段肯定也不适合建索引。 

其实这个问题更感觉偏向于做软件项目的一种经验。

联合索引的建立技巧

在一个多列 B-Tree 索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。所以，索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的 ORDER BY、GROUP BY 和 DISTINCT 等子句的查询需求。

对于如何选择索引的列顺序有一个经验法 则：将选择性最高的列放到索引最前列。当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。

这时候索引的作用只是用于优化 WHERE 条件的查找。在这种情况下，这样设计的索引确实能够最快地过滤出需要的行，对于在 WHERE 子句中只使用了索引部分前缀列的查询来说选择性也更高。

然而，性能不只是依赖于索引列的离散性，也和查询条件的有关。可能需要根据那些运行频率最高的查询来调整索引列的顺序，比如排序和分组，让这种情况下就应该根据索引排序/分组优先级来选择。

同时，在优化性能的时候，可能需要使用相同的列但顺序不同的索引来满足 不同类型的查询需求。

如何给我们使用联合索引的查询语句打个分呢？我们这里引出了三星索引的概念。

一个查询使用的索引等级——三星索引

对于一个查询而言，一个三星索引，可能是其最好的索引。

如果查询使用三星索引，一次查询通常只需要进行一次磁盘随机读以及一次窄索引片的扫描，因此其相应时间通常比使用一个普通索引的响应时间少几个数量级。

那么索引的星级是如何定义？

第一颗星（第一个索引分离度高）

与查询相关的索引行是相邻的，也就是where后面的等值谓词，可以匹配索引列顺序。

照我们自己的理解，就是我们联合索引的第一个索引必须保证分离度足够高。这样在我们查询的时候，才可以把需要操作的索引片范围缩的很小。

也就是说第一个索引分离度较差，会导致查出的数据过多，数据就可能存在多个数据页中，导致需要在链表中多次进行IO获取所有数据。这样意义是不大的，我们毕竟还需要消费不小的成本去维护索引。

第二颗星（排序/分组）

在满足一星的情况下，当查询需要排序，group by、 order by，的字段顺序，与联合索引中的顺序是一致的，那么就可以免去我们自己重新排序的时间（因为联合默认排好序了）。

第三颗星（利用覆盖索引）

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

达成三星索引的例子

表结构：

create table customer( 
cno int, l
name varchar(10), 
fname varchar(10), 
sex int, 
weight int, 
city varchar(10));

建立索引 ：

create index idx_cust on customer(lname,city,fname,cno);

三星索引sql：

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

来评估下：

第一颗星：所有等值谓词的列，是组合索引的开头的列，可以把索引片缩得很窄，符合。

第二颗星：order by 的 fname 字段在组合索引中且是索引自动排序好的，符合。

第三颗星：select 中的 cno 字段、fname 字段在组合索引中存在，符合。

达不到三星索引的例子

情况1

现在有表 ：

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;

建立索引 ：

create index idx_test on test(user_name,sex,age)：

SQL 语句如下：

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

来评估下：

第一颗星：符合，第一个索引离散度还行。

第二颗星：不符合。这里一开始有点犯晕。age如果需要排序，必须保证前面的索引都是=查询。如果是范围查询会导致age的顺序无法保证。（因为每一个user_name下面都维护了一个排好序的age，这是无法合并的）。

第三颗星：符合，查询的数据在联合索引中都存在。

情况2

索引修改，sql不变：

create index idx_test on test(sex, age，user_name)

重新分析：

第一颗星，不满足，只可以匹配到 sex，sex 选择性很差，意味着是一个宽索引片。

第二颗星，满足，等值 sex 的情况下，age 是有序的。

第三颗星，满足，select 查询的列都在索引列中

对于索引(sex,age，user_name)我们可以看到，此时无法满足第一颗星，窄索引片的需求。 以上 2 个索引，都是无法同时满足三星索引设计中的三个需求的，我们只能 尽力满足 2 个。而在多数情况下，能够满足 2 颗星，已经能缩小很大的查询范围 了，具体最终要保留哪一颗星，这个就需要看查询者己的重点了，无法给出标准答案。

主键索引禁止修改

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

最关键的是，每一个二级索引，联合索引中都维护了一个主键索引。如果主键一改，所有B+树都要跟着一起变动，这个性能损耗就很夸张了。因此我们在选择主键时，就要选择那种与业务没有强关联的，以防后续修改。

避免一不小心生成的重复，冗余索引

重复索引

有时会在不经意间创建了重复索引，例如下面的代码:

CREATE TABLE test ( 
ID INT NOT NULL PRIMARY KEY, 
A INT NOT NULL, 
B INT NOT NULL, 
UNIQUE(ID), //唯一索引
INDEX(ID) //普通索引（与默认重复）
) ENGINE=InnoDB;

上面的写法实 际上在相同的列上创建了三个重复的索引。通常并没有理由这样做。

联合索引不要创建冗余索引

冗余索引和重复索引有一些不同。如果创建了索引(A B)，再创建索引(A)就是冗余索引，因为这只是前一个索引的前缀索引。因此索引(AB)也可以当作索引 (A)来使用（这种冗余只是对 B-Tree 索引来说的)。但是如果再创建索引 (B,A)， 则不是冗余索引，索引(B)也不是，因为 B 不是索引(A,B)的最左前缀列。

已有的索引(A)，假设还有一个索引(A，ID)，其中 ID 是主键，对于 InnoDB 来说主键列已经包含在二级索引中了，所以这也是冗余的。

解决冗余索引和重复索引的方法很简单，删除这些索引就可以，但首先要做的是找出这样的索引。可以通过写一些复杂的访问 INFORMATION_SCHEMA 表的 查询来找。

删除未使用的索引

除了冗余索引和重复索引，可能还会有一些服务器永远不用的索引。这样的索引完全是累赘，建议考虑删除。