独立的列

“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。

前缀索引和索引选择性

前缀索引介绍参见创建索引

索引选择性

通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。索引的选择性是指，不重复的索引值（也称为基数，cardinality) 和数据表的记录总数(#T) 的比值（count(distinct col)/count(*)），范围从1/#T 到1 之间。
索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL 在查找时过滤掉更多的行。唯一索引的选择性是1, 这是最好的索引选择性，性能也是最好的，而一些状态、性别字段可能在大数据面前区分度就是0。一般情况下某个列前缀的选择性也是足够高的，足以满足查询性能。对于BLOB 、TEXT 或者很长的VARCHAR 类型的列，必须使用前缀索引，因为MySQL 不允许索引这些列的完整长度。
诀窍在千要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）。前缀应该足够长，以使得前缀索引的选择性接近千索引整个列。换句话说，前缀的“基数”应该接近于完整列的“基数”。

如何确定前缀索引的长度？

第一种方法：对比前缀的数量和完整列的数量

这里使用MySQL官方的Demo数据库sakila演示。一句sakila city表创建我们的演示表city_demo，并插入测试数据

  1. *******************1. 创建测试表city_demo*******************
  2. mysql> create table city_demo( city varchar(50) not null);
  3. Query OK, 0 rows affected (0.02 sec)
  5. *******************2. 插入测试数据*******************
  6. mysql> insert into city_demo(city) select city from sakila.city;
  7. Query OK, 600 rows affected (0.01 sec)
  8. Records: 600  Duplicates: 0  Warnings: 0
  10. *******************3. 重复插入更多数据*******************
  11. mysql> insert into city_demo(city) select city from city_demo;
  12. Query OK, 600 rows affected (0.01 sec)
  13. Records: 600  Duplicates: 0  Warnings: 0
  15. mysql> insert into city_demo(city) select city from city_demo;
  16. Query OK, 1200 rows affected (0.00 sec)
  17. Records: 1200  Duplicates: 0  Warnings: 0
  19. mysql> insert into city_demo(city) select city from city_demo;
  20. Query OK, 2400 rows affected (0.01 sec)
  21. Records: 2400  Duplicates: 0  Warnings: 0
  23. mysql> insert into city_demo(city) select city from city_demo;
  24. Query OK, 4800 rows affected (0.01 sec)
  25. Records: 4800  Duplicates: 0  Warnings: 0
  27. mysql> insert into city_demo(city) select city from city_demo;
  28. Query OK, 9600 rows affected (0.05 sec)
  29. Records: 9600  Duplicates: 0  Warnings: 0
  31. *******************4.将表里面的城市名随机打乱*******************
  32. mysql> update city_demo set city = (select city from sakila.city order by rand() limit 1);
  33. Query OK, 19169 rows affected (6.66 sec)
  34. Rows matched: 19200  Changed: 19169  Warnings: 0

首先我们找到最常见的城市列表

mysql> select count(1) as cnt, city from city_demo group by city order by cnt desc limit 10;
+-----+-----------------+
| cnt | city            |
+-----+-----------------+
|  66 | London          |
|  50 | Paarl           |
|  47 | Hsichuh         |
|  46 | Liepaja         |
|  46 | Nantou          |
|  46 | Bradford        |
|  46 | Antofagasta     |
|  46 | Southend-on-Sea |
|  46 | Alessandria     |
|  45 | Jaipur          |
+-----+-----------------+
10 rows in set (0.03 sec)

可以发现每个值都重复45~66次，现在开始查找最频繁出现的城市前缀，从3开始

mysql> select count(1) as cnt, left(city,3) as prefix3 from city_demo group by prefix3 order by cnt desc limit 10;
+-----+---------+
| cnt | prefix3 |
+-----+---------+
| 438 | San     |
| 188 | Cha     |
| 175 | Sou     |
| 151 | Sal     |
| 147 | Tan     |
| 146 | Kam     |
| 140 | Hal     |
| 139 | al-     |
| 138 | Sha     |
| 131 | Shi     |
+-----+---------+
10 rows in set (0.02 sec)

每个前缀都比原来的城市出现的次数更多，因此唯一前缀比唯一城市要少得多。然后我们增加前缀长度，直到这个前缀的选择性接近完整列的选择性。经过实验后发现前缀长度为7 时比较合适，此时，前缀为7的数量与完整列的数量相当：

mysql> select count(1) as cnt, left(city,7) as prefix7 from city_demo group by prefix7 order by cnt desc limit 10;
+-----+---------+
| cnt | prefix7 |
+-----+---------+
|  71 | San Fel |
|  66 | London  |
|  63 | Santiag |
|  61 | Valle d |
|  50 | Paarl   |
|  47 | Hsichuh |
|  46 | Liepaja |
|  46 | Nantou  |
|  46 | Bradfor |
|  46 | Alessan |
+-----+---------+
10 rows in set (0.02 sec)

第二种方法：直接计算选择性

计算合适的前缀长度的另外一个办法就是计算完整列的选择性，井使前缀的选择性接近于完整列的选择性。下面显示如何计算完整列的选择性：

mysql> select count(distinct(city))/count(1) from city_demo;
+--------------------------------+
| count(distinct(city))/count(1) |
+--------------------------------+
|                         0.0312 |
+--------------------------------+
1 row in set (0.01 sec)

然后计算各个前缀的选择性；

mysql> select count(distinct(left(city,3))) / count(1) as prefix3,
    -> count(distinct(left(city,4))) / count(1) as prefix4,
    -> count(distinct(left(city,5))) / count(1) as prefix5,
    -> count(distinct(left(city,6))) / count(1) as prefix6,
    -> count(distinct(left(city,7))) / count(1) as prefix7
    -> from city_demo;
+---------+---------+---------+---------+---------+
| prefix3 | prefix4 | prefix5 | prefix6 | prefix7 |
+---------+---------+---------+---------+---------+
|  0.0239 |  0.0293 |  0.0305 |  0.0309 |  0.0310 |
+---------+---------+---------+---------+---------+
1 row in set (0.04 sec)

查询显示当前缀长度到达7 的时候，再增加前缀长度，选择性提升的幅度已经很小了。只看平均选择性是不够的，也有例外的情况，需要考虑最坏情况下的选择性。平均选择性会让你认为前缀长度为4 或者5 的索引已经足够了，但如果数据分布很不均匀，可能就会有陷阱。如果观察前缀为4 的最常出现城市的次数，可以看到明显不均匀：

mysql> select count(1) as cnt, left(city,4) as prefix4 from city_demo group by prefix4 order by cnt desc limit 10;
+-----+---------+
| cnt | prefix4 |
+-----+---------+
| 202 | San     |
| 174 | Sant    |
| 147 | Sout    |
|  92 | Toul    |
|  91 | Chan    |
|  79 | Hali    |
|  75 | Ranc    |
|  73 | Sain    |
|  71 | Naga    |
|  70 | Kama    |
+-----+---------+
10 rows in set (0.01 sec)

如果前缀是4 个字节，则最常出现的前缀的出现次数比最常出现的城市的出现次数要大很多。即这些值的选择性比平均选择性要低。如果有比这个随机生成的示例更真实的数据，就更有可能看到这种现象。例如在真实的城市名上建一个长度为4 的前缀索引，对于以“San” 和“New” 开头的城市的选择性就会非常糟糕，因为很多城市都以这两个词开头。

前缀索引的缺点

MySQL 无法使用前缀索引做ORDER BY 和GROUP BY, 也无法使用前缀索引做覆盖扫描。

多列索引

在多个列上建立独立的单列索引大部分情况下并不能提高MySQL 的查询性能。MySQL5.0 和更新版本引入了一种叫“索引合并”(index merge) 的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。更早版本的MySQL 只能使用其中某一个单列索引，然而这种情况下没有哪一个独立的单列索引是非常有效的。例如，表film_actor 在字段film_id 和actor_id 上各有一个单列索引。但对于下面这个查询WHERE 条件，这两个单列索引都不是好的选择：

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

老版本的MySQL会对表进行全表扫描，除非改写成Union方式

mysql> select film_id, actor_id from 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> explain select film_id, actor_id from sakila.film_actor where actor_id = 1 or film_id = 1 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_actor
   partitions: NULL
         type: index_merge
possible_keys: PRIMARY,idx_fk_film_id
          key: PRIMARY,idx_fk_film_id
      key_len: 2,2
          ref: NULL
         rows: 29
     filtered: 100.00
        Extra: Using union(PRIMARY,idx_fk_film_id); Using where
1 row in set, 1 warning (0.01 sec)

MySQL 会使用这类技术优化复杂查询，所以在某些语句的Extra 列中还可以看到嵌套操作。
索引合并策略有时候是一种优化的结果，但实际上更多时候说明了表上的索引建得很糟糕：

  • 当出现服务器对多个索引做相交操作时（通常有多个AND 条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
  • 当服务器需要对多个索引做联合操作时（通常有多个OR 条件），通常需要耗费大量CPU 和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合井扫描返回的大扯数据的时候。
  • 更重要的是，优化器不会把这些计算到“查询成本”(cost) 中，优化器只关心随机页面读取。这会使得查询的成本被“低估＂，导致该执行计划还不如直接走全表扫描。这样做不但会消耗更多的CPU 和内存资源，还可能会影响查询的并发性，但如果是单独运行这样的查询则往往会忽略对并发性的影响。通常来说，还不如像在MySQL4.1 或者更早的时代一样，将查询改写成UNION 的方式往往更好。

如果在EXPLAIN 中看到有索引合井，应该好好检查一下查询和表的结构，看是不是已经是最优的。也可以通过参数optimizer _switch 来关闭索引合并功能。也可以使用IGNORE INDEX 提示让优化器忽略掉某些索引。

选择合适的索引顺序

我们遇到的最容易引起困惑的问题就是索引列的顺序。正确的顺序依赖千使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要（顺便说明，本节内容适用于B-Tree 索引；哈希或者其他类型的索引并不会像B-Tree 索引一样按顺序存储数据）。
在一个多列B-Tree 索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。所以，索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY 、GROUP BY 和DISTINCT 等子旬的查询需求。所以多列索引的列顺序至关重要。
对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列。这个建议有用吗？在某些场景可能有帮助，但通常不如避免随机IO 和排序那么重要，考虑问题需要更全面（场景不同则选择不同，没有一个放之四海皆准的法则。这里只是说明，这个经验法则可能没有你想象的重要）。
当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化WHERE 条件的查找。在这种情况下，这样设计的索引确实能够最快地过滤出需要的行，对于在WHERE 子句中只使用了索引部分前缀列的查询来说选择性也更高。然而，性能不只是依赖于所有索引列的选择性（整体基数），也和查询条件的具体值有关，也就是和值的分布有关。这和前面介绍的选择前缀的长度需要考虑的地方一样。可能需要根据那些运行频率最高的查询来调整索引列的顺序，让这种情况下索引的选择性最高。

聚簇索引

聚簇索引注并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖千其实现方式，但InnoDB 的聚簇索引实际上在同一个结构中保存了B-Tree 索引和数据行。当表有聚簇索引时，它的数据行实际上存放在索引的叶子页(leaf page) 中。术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起注。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。因为是存储引擎负责实现索引，因此不是所有的存储引擎都支持聚簇索引。本节我们主要关注InnoDB, 但是这里讨论的原理对于任何支持聚簇索引的存储引擎都是适用的。下图展示了聚簇索引中的记录是如何存放的。注意到，叶子页包含了行的全部数据，但是节点页只包含了索引列。在这个案例中，索引列包含的是整数值。更多聚簇索引信息参考聚簇索引和二级索引
image.png
InnoDB 将通过主键聚集数据，如果没有定义主键， InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，lnnoDB 会隐式定义一个主键来作为聚簇索引。InnoDB 只聚集在同一个页面中的记录。包含相邻键值的页面可能会相距甚远。

优点

聚集的数据有一些重要的优点：

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

  • 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

    缺点

    如果在设计表和查询时能充分利用上面的优点，那就能极大地提升性能。同时，聚簇索引也有一些缺点：

  • 聚簇数据最大限度地提高了I/O 密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没什么优势了。

  • 插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB 表中速度最快的方式。但如果不是按照主键顺序加载数据，那么在加载完成后最好使用OPTIMIZE TABLE 命令重新组织一下表。
  • 更新聚簇索引列的代价很高，因为会强制InnoDB 将每个被更新的行移动到新的位置。
  • 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临＂页分裂(page split) 的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间。
  • 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
  • 二级索引（非聚簇索引）可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列。

  • 二级索引访问需要两次索引查找，而不是一次。二级索引中保存的“行指针”的实质。要记住，二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。这意味着通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后根据这个值去聚簇索引中查找到对应的行。这里做了重复的工作：两次B-Tree查找而不是一次注。对于lnnoDB, 自适应哈希索引能够减少这样的重复工作。

    InnoDB和MyISAM数据分布对比

    聚簇索引和非聚簇索引的数据分布有区别，以及对应的主键索引和二级索引的数据分布也有区别，比如下表：

    *假设该表的主键取值为1 ~ 10000, 按照随机顺序插入井使用OPTIMIZE TABLE 命令做了优化。
*换句话说，数据在磁盘上的存储方式已经最优，但行的顺序是随机的。
*列col2的值是从1 ~ 100 之间随机赋值，所以有很多重复的值。
create table test(
col1 int not null,
col2 int not null,
primary key(col1),
key(col2)
);

    MylSAM的数据分布

    MyISAM 的数据分布非常简单。MyISAM 按照数据插入的顺序存储在磁盘上，如下图（左）所示。在行的旁边显示了行号，从0 开始递增。因为行是定长的，所以MyISAM 可以从表的开头跳过所需的字节找到需要的行(MylSAM 并不总是使用下图中的“行号”，而是根据定长还是变长的行使用不同策略）。这种分布方式很容易创建索引。下面显示的一系列图，隐藏了页的物理细节，只显示索引中的”节点”，索引中的每个叶子节点包含“行号”。下图（中）显示了test表的主键分布。下图（右）显示了col2的分布。
    image.pngimage.pngimage.png
    事实上， MyISAM 中主键索引和其他索引在结构上没有什么不同。主键索引就是一个名为PRIMARY 的唯一非空索引。

    InnoDB的数据分布

    lnnoDB 的数据分布。因为InnoDB 支持聚簇索引，所以使用非常不同的方式存储同样的数据。InnoDB 以下图（左）所示的方式存储数据。
    image.pngimage.png
    上图图显示了整个表，而不是只有索引。因为在InnoDB 中，聚簇索引”就是”表，所以不像MyISAM 那样需要独立的行存储。
    聚簇索引的每一个叶子节点都包含了主键值、事务ID 、用于事务和MVCC的回滚指针以及所有的剩余列（在这个例子中是col2) 。如果主键是一个列前缀索引，InnoDB也会包含完整的主键列和剩下的其他列。
    还有一点和MyISAM 的不同是， InnoDB 的二级索引和聚簇索引很不相同。InnoDB 二级索引的叶子节点中存储的不是“行指针”，而是主键值，井以此作为指向行的”指针”。这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。使用主键值当作指针会让二级索引占用更多的空间，换来的好处是， InnoDB在移动行时无须更新二级索引中的这个”指针”。
    上图（右）显示了示例表的col2 索引。每一个叶子节点都包含了索引列（这里是col2) ，紧接着是主键值(col1) 。同时展示了B-Tree 的叶子节点结构，故意省略了非叶子节点这样的细节。
    InnoDB 的非叶子节点包含了索引列和一个指向下级节点的指针（下一级节点可以是非叶子节点，也可以是叶子节点）。这对聚簇索引和二级索引都适用。
    下图是描述InnoDB 和MyISAM 如何存放表的抽象图。从图中可以很容易看出InnoDB 和MylSAM 保存数据和索引的区别。
    image.png

    在InnoDB中按主键顺序插入行

    如果正在使用InnoDB 表井且没有什么数据需要聚集，那么可以定义一个代理键(surrogate key) 作为主键，这种主键的数据应该和应用无关，最简单的方法是使用AUTO_INCREMENT 自增列。这样可以保证数据行是按顺序写入，对于根据主键做关联操作的性能也会更好。
    最好避免随机的（不连续且值的分布范围非常大）聚簇索引，特别是对千I/O密集型的应用。例如，从性能的角度考虑，使用UUID 来作为聚簇索引则会很糟糕：它使得聚簇索引的插入变得完全随机，这是最坏的情况，使得数据没有任何聚集特性。
    下面分别展示在InnoDB中插入顺序值（auto_increment）和随机值（UUID）的区别。
    image.png
    因为主键的值是顺序的，所以InnoDB把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时(InnoDB 默认的最大填充因子是页大小的15/16, 留出部分空间用千以后修改），下一条记录就会写入新的页中。一且数据按照这种顺序的方式加载，主键页就会近似千被顺序的记录填满，这也正是所期望的结果（然而，二级索引页可能是不一样的）。
    与插入随机值对比：
    因为新行的主键值不一定比之前插入的大，所以lnnoDB 无法简单地总是把新行插入到索引的最后，而是需要为新的行寻找合适的位置——通常是已有数据的中间位置——并且分配空间。这会增加很多的额外作，并导致数据分布不够优化。下面是总结的一些缺点：

  • 写人的目标页可能已经刷到磁盘上井从缓存中移除，或者是还没有被加载到缓存中，InnoDB 在插入之前不得不先找到并从磁盘读取目标页到内存中。这将导致大扯的随机1/0 。

  • 因为写入是乱序的， InnoDB 不得不频繁地做页分裂操作，以便为新的行分配空间。页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个页。
  • 由千频繁的页分裂，页会变得稀疏并被不规则地填充，所以最终数据会有碎片。在把这些随机值载入到聚簇索引以后，也许需要做一次OPTIMIZE TABLE 来重建表井优化页的填充。

从这个案例可以看出，使用InnoDB 时应该尽可能地按主键顺序插入数据，并且尽可能地使用单调增加的聚簇键的值来插入新行。
image.png

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

覆盖索引

通常大家都会根据查询的WHERE条件来创建合适的索引，不过这只是索引优化的一个方面。设计优秀的索引应该考虑到整个查询，而不单单是WHERE 条件部分。索引确实是一种查找数据的高效方式，但是MySQL 也可以使用索引来直接获取列的数据，这样就不再需要读取数据行。如果索引的叶子节点中已经包含要查询的数据，那么还有什么必要再回表查询呢？如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为＂覆盖索引”。
覆盖索引是非常有用的工具，能够极大地提高性能。考虑一下如果查询只需要扫描索引而无须回表，会带来多少好处：

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

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL 只能使用B-Tree 索引做覆盖索引。另外，不同的存储引擎实现覆盖索引的方式也不同，而且不是所有的引擎都支持覆盖索引。
当发起一个被索引覆盖的查询（也叫做索引覆盖查询）时，在EXPLAIN 的Extra 列可以看到“Using index” 的信息 。例如，表sakila. inventory 有一个多列索引(store_id,film_id) 。MySQL 如果只需访问这两列，就可以使用这个索引做覆盖索引，如下所示：

mysql> explain select store_id, film_id from sakila.inventory \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: inventory
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_store_id_film_id
      key_len: 3
          ref: NULL
         rows: 4581
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

MySQL 有两种方式可以生成有序的结果：通过排序操作或者按索引顺序扫描，如果EXPLAIN 出来的type 列的值为“index”, 则说明MySQL 使用了索引扫描来做排序。扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引记录就都回表查询一次对应的行。这基本上都是随机I/O, 因此按索引顺序读取数据的速度通常要比顺序地全
表扫描慢，尤其是在1/0 密集型的工作负载时。
MySQL 可以使用同一个索引既满足排序，又用于查找行。因此，如果可能，设计索引时应该尽可能地同时满足这两种任务，这样是最好的。

  • 只有当索引的列顺序和ORDER BY 子句的顺序完全一致，井且所有列的排序方向（倒序或正序）都一样时， MySQL 才能够使用索引来对结果做排序注。
  • 如果查询需要关联多张表，则只有当ORDER BY 子句引用的字段全部为第一个表时，才能使用索引做排序。
  • ORDER BY 子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求；否则，MySQL 都需要执行排序操作，而无法利用索引排序。
  • 有一种情况下ORDER BY 子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候。如果WHERE 子旬或者JOIN 子旬中对这些列指定了常量，就可以“弥补＂索引的不足。
    mysql> show create table rental \G
*************************** 1. row ***************************
     Table: rental
Create Table: CREATE TABLE `rental` (
`rental_id` int(11) NOT NULL AUTO_INCREMENT,
`rental_date` datetime NOT NULL,
`inventory_id` mediumint(8) unsigned NOT NULL,
`customer_id` smallint(5) unsigned NOT NULL,
`return_date` datetime DEFAULT NULL,
`staff_id` tinyint(3) unsigned NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`rental_id`),
UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
KEY `idx_fk_inventory_id` (`inventory_id`),
KEY `idx_fk_customer_id` (`customer_id`),
KEY `idx_fk_staff_id` (`staff_id`),
CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE,
CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

  • 主键索引（聚簇索引）尽量选择不修改或者修改很少的列。
  • 查询的索引列，不一定是要选择的索引列。换句话说，最适合索引的列出现在where子句的列、或连接子句中指定的列，而不出现select关键字后面的列。
  • 尽量选择区分度高的列作为索引，区分度的公式是，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。
  • 使用短索引。如果对字符串进行索引，就应该制定一个前缀长度，比如char(200)的前10-20个字符多数是唯一值，就可以指定前缀所以，而不用对整列索引。使用短索引能节省大量索引空间，也可能会使查询更快。较小的索引涉及的磁盘I/O较少，较短的值比起来更快。更重要的是，对于较短的键值，索引高速缓存中的块能容纳更多的键值，因此，MySQL在内存中就能容纳更多的值。这样就增加了找到行而不用读取索引中较多快的可能性。
  • 利用最左前缀。最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
  • 不要过度索引。
    • 每个索引都要占额外的磁盘空间，并降低写操作性能。
    • 在修改表内容时，索引必须进行更新，有时可能需要重构，因此索引越多，所花时间越长。
    • 如果一个索引不经常使用或者从不使用，就会不必要减缓表的修改速度。
    • MySQL在生成一个执行计划时，需要考虑各个索引，这需要花费时间，创建多余索引给优化带来更多工作。
    • 索引太多，可能导致选择不到所需要的最好的索引。
  • 对于InnoDB的表，记录默认会按照一定顺序排序，如果有明确主键，则按照主键排序。没有主键，有唯一索引，则按唯一索引排序保存。如果既没有主键和唯一索引，那么表内部会自动生成一个内部列，按照这个顺序保存。按照主键或者内部列访问是最快的，所以InnoDB最好自己定义主键，若有多个唯一的列，都可作为主键时，要选择最常访问列作为主键。InnoDB表的的普通索引都会保存主键的键值，所以主键要选择尽可能短的数据类型，可以有效地减少索引的磁盘占用，提高索引缓存效果。