浅谈如何设计MySQL索引

标签：浅谈 create MySQL 查询索引 time 排序 name

文章目录

一、索引的代价
二、如何设计索引

1、索引列的类型尽量小
2、索引的选择离散性高的
3、只为用于搜索、排序或分组的列创建索引
4、主键选择少改变的列
5、处理冗余和重复索引
6、删除未使用的索引
7、总结

三、索引设计——三星索引

一、索引的代价

空间上的代价：

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

时间上的代价：

每次对表中的数据进行增、删、改操作时，都需要去修改索引涉及的B+树索引。B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录，还是非叶子内节点中的记录都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收的操作来维护好节点和记录的排序。如果我们建了许多索引，每个索引对应的B+树都要进行相关的维护操作，这必然会对性能造成影响。

一般来说，一张表6-7个索引以下都能够取得比较好的性能权衡。

二、如何设计索引

创建索引的时候可以参考以下策略：

1、索引列的类型尽量小

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

这是因为:

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

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

2、索引的选择离散性高的

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

很差的索引选择性就是列中的数据重复度很高，比如性别字段，正常情况下只有两种可能。那么我们在查询时，即使使用这个索引，依然可能查出一半的数据。

此时我们就可以联想到hash索引，毕竟hash冲突的概率很小

但是hash索引缺陷也很明显：

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

为索引开始的部分字符添加前缀索引

该方式可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。一般情况下我们需要保证某个列前缀的选择性也是足够高的，以满足查询性能。（尤其对于BLOB、TEXT或者很长的VARCHAR类型的列，应该使用前缀索引，因为MySQL不允许索引这些列的完整长度）。

关键在于要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间)。前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。

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

倒序存储数据，用于后缀索引

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

3、只为用于搜索、排序或分组的列创建索引

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

SELECT * FROM ttrd_otc_trade ORDER BY create_time, update_time,trade_time;

查询的结果集需要先按照create_time值排序，如果记录的create_time值相同，则需要按照update_time来排序，如果update_time的值相同，则需要按照trade_time排序。回顾一下联合索引的存储结构，index_create_update_trade索引本身就是按照上述规则排好序的，所以直接从索引中提取数据，然后进行回表操作取出该索引中不包含的列就好了。

当然ORDER BY的子句后边的列的顺序也必须按照索引列的顺序给出，如果给出ORDER BY trade_time,update_time, create_time的顺序，那也是用不了B+树索引的。

SELECT create_time, update_time,trade_time FROM order_exp GROUP BY  create_time, update_time,trade_time;

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

先把记录按照create_time值进行分组，所有create_time值相同的记录划分为一组。
将每个create_time值相同的分组里的记录再按照update_time的值进行分组，将update_time值相同的记录放到一个小分组里。
再将上一步中产生的小分组按照trade_time的值分成更小的分组。
然后针对最后的分组进行统计，如果没有索引的话，这个分组过程全部需要在内存里实现，而如果有了索引的话，恰巧这个分组顺序又和我们的index_create_update_trade索引中的索引列的顺序是一致的，而我们的B+树索引又是按照索引列排好序的，所以可以直接使用B+树索引进行分组。和使用B+树索引进行排序是一个道理，分组列的顺序也需要和索引列的顺序一致。

4、主键选择少改变的列

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

5、处理冗余和重复索引

MySQL允许在相同列上创建多个索引，无论是有意的还是无意的。MySQL需要单独维护重复的索引，并且优化器在优化查询的时候也需要逐个地进行考虑，这会影响性能。重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免这样创建重复索引，发现以后也应该立即移除。

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

CREATE TABLE USER (
ID INT NOT NULL PRIMARY KEY,
NUM INT NOT NULL,
AGE INT NOT NULL,
UNIQUE(ID),
INDEX(ID)
)

这里创建了一个主键（PRIMARY KEY），又加上唯一限制（UNIQUE），然后再加上索引（INDEX）以供查询使用。事实上，MySQL的唯一限制和主键限制都是通过索引实现的，因此，上面的写法实际上在相同的列上创建了三个重复的索引。通常并没有理由这样做，除非是在同一列上创建不同类型的索引来满足不同的查询需求。

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

已有的索引（AGE），扩展为（AGE,ID），其中ID是主键，对于InnoDB来说主键列已经包含在二级索引中了，所以这也是冗余的。

解决冗余索引和重复索引的方法很简单，删除这些索引就可以，但首先要做的是找出这样的索引。

6、删除未使用的索引

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

7、总结

很多人对多列索引的理解都不够。一个常见的错误就是，为每个列创建独立的索引，或者按照错误的顺序创建多列索引。

我们遇到的最容易引起困惑的问题就是索引列的顺序。正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要。反复强调过，在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。所以，索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY、GROUP BY和DISTINCT等子句的查询需求。

所以多列索引的列顺序至关重要。对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列。当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化WHERE条件的查找。在这种情况下，这样设计的索引确实能够最快地过滤出需要的行，对于在WHERE子句中只使用了索引部分前缀列的查询来说选择性也更高。

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

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

三、索引设计——三星索引

三星索引概念

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

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

三星索引概念是在《Rrelational Database Index Design and the optimizers》一书（这本书也是《高性能MySQL》作者强烈推荐的一本书）中提出来的。原文如下：

<!-- 如果索引将相关的记录放到一起则获得一星 -->
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.

一星：

让索引片尽量变窄，where后面的索引列匹配的越多，最终扫描的数据行也是越小。让索引片尽量变窄，即我们所说的索引的扫描范围越小越好。

二星：

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

三星：

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

索引案例1：

create table student(
    `num` int,
    `name` varchar(10),
    `enname` varchar(10),
    `sex` int,
    `age` int,
    `class` varchar(10)
);

create index idx_stu on customer(class,name,enname,num);

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

select num,name from student where name =’xx’ and class =’yy’ order by enname;

第一颗星：num和name字段能够过滤很多数据，符合。
第二颗星：order by的enname字段在组合索引中且是索引自动排序好的，符合。
第三颗星：select中的num字段、enname字段在组合索引中存在，符合。

索引案例2：

现在有表

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

对于下面的SQL和索引而言，达不成三星索引

create index idx_stu on customer(name,sex,age);

select name,sex,age from student where name like 'zhang%' and sex =1 ORDER BY age

第一颗星：查询列根据最左前缀可以过滤很多数据，满足；
第二颗星：不满足，name 采用了范围匹配，sex 是过滤列，此时age 列无法保证有序的；
第三颗星：存在覆盖索引，满足。

此时索引(user_name,sex,age)，不能满足三星索引中的第二颗星（排序）。

索引案例3：