高性能mysql：创建高性能的索引

本文系阅读《高性能MySQL》，Baron Schwartz等著一书中第五章创建高性能的索引的笔记，索引是存储引擎用于快速找到记录的一种数据结构。

索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对性能的影响愈发重要。在数据量较小且负载较低时，不恰当的索引对性能的影响可能还不明显，但当数据量逐渐增大时，性能则会急剧下降。

索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高几个数量级，最优的索引有时比一个好的索引性能要好两个数量级。

索引基础

在mysql中，存储引擎先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。假如要运行下面的查询：

SELECT item_name FROM cc_item WHERE item_code = 'YP0000000001';

如果在item_code列上建有索引，则mysql将使用该索引找到item_code为YP0000000001的行，也就是说，mysql先在索引上按值进行查找，然后返回所有包含该值的数据行。

索引可以包含一个或多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为mysql只能高效的使用索引的最左前缀列。创建一个包含两个列的索引，和创建两个只包含一列的索引大不相同。

索引的类型

索引有很多种类型，可以为不同的场景提供更好的性能。在mysql中，索引是在存储引擎层而不是服务器层实现的。所以，并没有统一的索引标准：不同存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。

B-Tree索引

当谈论索引时，如果没有指明类型，那多半说的是B-Tree索引，它使用B-Tree数据结构来存储数据，大多数mysql存储引擎都支持这种索引——实际上很多存储引擎使用的是B+Tree，比如InnoDB，B+Tree的每一个叶子结点都包含指向下一个叶子结点的指针，从而方便叶子结点的范围遍历。

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

B-Tree通常意味着所有的值都是按顺序存储，并且每一个叶子页到根的距离相同。下图展示了B-Tree索引的抽象表示，大致反映了InnoDB索引是如何工作的。

B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根结点开始进行搜索。根结点的槽中存放了指向子结点的指针，存储引擎根据这些指针向下层查找。通过比较结点页的值和要查找的值可以找到合适的指针进入下层子结点，这些指针实际上定义了子结点页中值的上限和下限。树的深度和表的大小直接相关，最终存储引擎要么是找到对应的值，要么该记录不存在。

叶子结点比较特别，它们的指针指向的是被索引的数据，而不是其它的结点页（不同引擎的指针类型不同）。

B-Tree对索引列是顺序组织的，所以很适合查找范围数据。例如，在一个基于文本域的索引树上，按字母顺序传递连续的值进行查找是非常合适的，所以像“找出所有以 I 到 K 开头的名字”这样的查找效率会非常高。例如，有以下数据表：

CREATE TABLE people(
last_name VARCHAR(50) NOT NULL,
first_name VARCHAR(50) NOT NULL,
dob DATE NOT NULL,
address VARCHAR(50) NOT NULL,
KEY(last_name,first_name,dob)
);

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

需要注意的是，索引对多个值进行排序的依据是CREATE TABLE语句中定义索引时列的顺序，如上图中最后两条同名同姓的记录，根据出生日期进行排序。

适用

B-Tree索引适用于全键值、键值范围或键前缀查找，其中键前缀查找只适用于根据最左前缀的查找。

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

因为索引树中的结点是有序的，所以除了按值查找之外，索引还可以用于查询中的ORDER BY操作。一般来说，如果B-Tree可以按照某种方式查找到值，那么也可以按照这种方式用于排序。所以，如果ORDER BY子句满足前面列出的几种查询类型，则这个索引也可以满足对应的排序需求。

限制

如果不是按照索引的最左列开始查找，则无法使用索引。例如，示例索引中无法用于查找名字为Bill的人，也无法查找某个特定生日的人，因为这两列都不是最左数据列。类似的，也无法查找姓氏以某个字母结尾的人
不能跳过索引中的列。例如，示例索引无法用于查找姓为Smith并且在某个特定日期出生的人。如果不指定first_name，则mysql只能使用索引的第一列
如果索引中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。例如，示例索引中有如下查询语句WHERE last_name = 'Smith' AND first_name LIKE 'J%' AND dob = '1976-12-23'，此查询只能使用索引的前两列，因为这里LIKE是一个范围条件。如果范围查询列值的数量有限，那么可以通过使用多个等于条件来代替范围条件。

上述限制都和索引列的顺序有关，在优化性能时，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。也有些限制并不是B-Tree本身导致的，而是mysql优化器和存储引擎使用索引的方式导致的，这部分限制在未来的版本中可能就不再是限制。

哈希索引

哈希索引（hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

在mysql中，只有Memory引擎显式支持哈希索引。这也是Memory引擎表的默认索引类型，Memory引擎同时也支持B-Tree索引。Memory引擎支持非唯一哈希索引，如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。

索引的优点

索引可以让服务器快速的定位到表的指定位置，但这并不是索引的唯一作用，根据创建索引的数据结构不同，索引也有一些其它的附加作用。

最常见的B-Tree索引，按照顺序存储数据，所以mysql可以用来做ORDER BY和GROUP BY操作。因为数据是有序的，所以B-Tree也就会将相关的列值都存储在一起。最后，因为索引中存储了实际的列值，所以某些查询只使用索引就能够完成全部查询，总结如下：

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

高性能的索引策略

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

独立的列

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

示例一：索引列不能是表达式的一部分

SELECT item_code FROM cc_item WHERE item_code + 1 = '282005';

mysql无法自动解析方程式：item_code = 282004，上述查询无法使用item_code列的索引。应该养成简化WHERE条件的习惯，始终将索引列单独放在比较符号的一侧，下面的查询能使用item_code列的索引。

SELECT item_code FROM cc_item WHERE item_code = '282004';

示例二：索引列不能是函数的参数

SELECT ... WHERE TO_DAYS(CURRENT_DATE()) - TO_DAYS(date_col) <= '10';

前缀索引和索引选择性

有时候需要索引很长的字符列，这会让索引变的大且慢，一个策略是模拟哈希索引。

通常还可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率，但这样也会降低索引的选择性。索引的选择性是指，不重复的索引值（也称为基数）和数据表的记录总数（T）的比值，范围从1/T到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让mysql在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

一般情况下某个列前缀的选择性也是足够高的，足以满足查询性能。对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为mysql不允许索引这些列的完整长度。

诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）。前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。换句话说，前缀的“基数”应该接近于完整列的“基数”。

为了决定前缀的合适长度，需要找到找到最常见的值的列表，然后和最常见的前缀列表进行比较

mysql> SELECT COUNT(*) AS cnt, last_name FROM people GROUP BY last_name ORDER BY cnt DESC;
+-----+---------------+
| cnt | last_name     |
+-----+---------------+
|   2 | shantianben   |
|   2 | song          |
|   1 | yanzuojing    |
|   1 | sonz          |
|   1 | zhao          |
|   1 | yanlaijiansan |
|   1 | liu           |
|   1 | zhaj          |
|   1 | yanjingjing   |
|   1 | shantianxi    |
|   1 | jitianli      |
+-----+---------------+
11 rows in set (0.00 sec)

现在查找最频繁出现的姓氏前缀，先从3个前缀字母开始：

mysql> SELECT COUNT(*) AS cnt, LEFT(last_name,3) AS pref FROM people GROUP BY pref ORDER BY cnt DESC;
+-----+------+
| cnt | pref |
+-----+------+
|   3 | sha  |
|   3 | son  |
|   3 | yan  |
|   2 | zha  |
|   1 | jit  |
|   1 | liu  |
+-----+------+
6 rows in set (0.00 sec)

每个姓氏都比原来的姓氏出现的次数多，因此唯一前缀比唯一姓氏要少的多。增加前缀长度，直到这个前缀的选择性接近完整列的选择性。

计算合适的前缀长度的另外一个办法就是计算完整列的选择性，并使前缀的选择性接近于完整列的选择性

mysql> SELECT COUNT(DISTINCT last_name)/COUNT(*) FROM people;
+------------------------------------+
| COUNT(DISTINCT last_name)/COUNT(*) |
+------------------------------------+
|                             0.8462 |
+------------------------------------+
1 row in set (0.00 sec)

通常来说，示例中如果前缀的选择性能够接近于0.846，基本上就可用了。可以在一个查询中针对不同前缀长度进行计算，这对于大表非常有用。

mysql> SELECT COUNT(DISTINCT LEFT(last_name, 2))/COUNT(*) AS sel2,
    -> COUNT(DISTINCT LEFT(last_name, 3))/COUNT(*) AS sel3,
    -> COUNT(DISTINCT LEFT(last_name, 4))/COUNT(*) AS sel4,
    -> COUNT(DISTINCT LEFT(last_name, 5))/COUNT(*) AS sel5,
    -> COUNT(DISTINCT LEFT(last_name, 6))/COUNT(*) AS sel6,
    -> COUNT(DISTINCT LEFT(last_name, 7))/COUNT(*) AS sel7,
    -> COUNT(DISTINCT LEFT(last_name, 8))/COUNT(*) AS sel8,
    -> COUNT(DISTINCT LEFT(last_name, 9))/COUNT(*) AS sel9
    -> FROM people;
+--------+--------+--------+--------+--------+--------+--------+--------+
| sel2   | sel3   | sel4   | sel5   | sel6   | sel7   | sel8   | sel9   |
+--------+--------+--------+--------+--------+--------+--------+--------+
| 0.4615 | 0.4615 | 0.7692 | 0.7692 | 0.7692 | 0.7692 | 0.7692 | 0.8462 |
+--------+--------+--------+--------+--------+--------+--------+--------+
1 row in set (0.00 sec)

只看平均选择性是不够的，也有例外的情况，需要考虑最坏情况下的选择性。如果数据分布很不均匀，可能就会有陷阱。

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

多列索引

一个常见的错误是，为每个列创建独立的索引，或者按照错误的顺序创建多列索引。为每个列创建独立索引的策略，一般是听从“把WHERE条件里面的列都建上索引”这种错误建议。

在多个列上建立独立的索引大部分情况下并不能提高mysql的查询性能。mysql 5.0和更新版本引入了一种叫索引合并（index merge）的策略，一定程度上可以使用表上的多个单列索引来定位指定的行，查询能够同时使用这两个单列索引进行扫描，并将结果进行合并，这种算法有三个变种：

OR条件的联合（union）
AND条件的相交（intersection）
组合前两种情况的联合及相交

例如，字段last_name、first_name上各有一个单列索引：

mysql> EXPLAIN SELECT last_name,first_name FROM people WHERE last_name = 'yanzuojing' OR first_name = 'h'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: people
         type: ALL
possible_keys: last_name,first_name
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 13
        Extra: Using where
1 row in set (0.00 sec)

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

当出现服务器对多个索引做相交操作时（通常有多个AND条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引
当服务器需要对多个索引做联合操作时（通常有多个OR条件），通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上
更重要的是，优化器不会把这些计算到查询成本中，优化器只关心随机页面读取。这会使得查询的成本被低估，导致该执行计划还不如直接走全表扫描

选择合适的索引顺序

正确的索引顺序依赖于使用该索引的查询，并且同时需要考虑如何更好的满足排序和分组的需要。

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

至于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列。当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的，这时候索引的作用只是用于优化WHERE条件的查找。然而，性能不只是依赖于所有索引列的选择性（整体基数），也和查询条件的具体值有关，也就是和值的分布有关。

SELECT * FROM people WHERE last_name = 'yanzuojing' AND first_name = 'h';

上述示例是创建一个(last_name, first_name)索引还是应该颠倒顺序？先用下面的查询预测，查看各个WHERE条件的分支对应的数据基数有多大：

mysql> SELECT SUM(last_name = 'zhaj'),SUM(first_name = 'm') FROM people;
+-------------------------+-----------------------+
| SUM(last_name = 'zhaj') | SUM(first_name = 'm') |
+-------------------------+-----------------------+
|                       1 |                     2 |
+-------------------------+-----------------------+
1 row in set (0.00 sec)

根据前述经验法则，应该将last_name放到前面，因为对应值的last_name数量更小，但这样查询的结果非常依赖于选定的具体值。如果没有类似的具体查询来运行，最好还是按经验法则来做，因为经验法则考虑的是全局基数和选择性，而不是某个具体查询：

mysql> SELECT COUNT(DISTINCT last_name)/COUNT(*) AS l_select,
    -> COUNT(DISTINCT first_name)/COUNT(*) AS f_select,
    -> COUNT(*)
    -> FROM people;
+----------+----------+----------+
| l_select | f_select | COUNT(*) |
+----------+----------+----------+
|   0.8462 |   0.9231 |       13 |
+----------+----------+----------+
1 row in set (0.00 sec)

first_name的选择性更高，所以将其作为索引列的第一列。

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行，本小节主要关注InnoDB。

当表有聚簇索引时，它的数据行实际上存放在索引的叶子叶中。“聚簇”表示数据行和相邻的键值紧凑的存储在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。下图展示了聚簇索引中记录的存放，此示例索引列包含的是整数值：

InnoDB通过主键聚集数据，即上图中被索引的列就是主键列。如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。InnoDB只聚集在同一个页面中的记录，包含相邻键值的页面可能会相距甚远。

聚集的数据的优点：

可以把相关数据保存在一起
数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快
使用覆盖索引扫描的查询可以直接使用页结点中的主键值

聚簇主键可能对性能有帮助，但也可能导致严重的性能问题。所以需要仔细的考虑聚簇索引，尤其是将表的存储引擎从InnoDB改成其它引擎时。聚簇索引的缺点：

聚簇数据最大限度的提高了I/O密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序也就没那么重要了，聚簇索引也就没什么优势了
插入速度严重依赖于插入顺序
更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置
基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行时，可能面临页分裂（page split）的问题
聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候
二级索引（非聚簇索引）可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列
二级索引访问需要两次索引查找，而不是一次

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

在InnoDB中，聚簇索引就是表，所以不像MyISAM那样需要独立的行存储。聚簇索引的每一个叶子结点都包含了主键值、事务ID、用于事务和多版本控制(MVVC)的回滚指针以及所有的剩余列。如果主键是一个列前缀索引，InnoDB也会包含完整的主键列和剩下的其它列。如下图所示：

InnoDB按主键顺序插入

如果正在使用InnoDB表并且没有什么数据需要聚集，那么可以定义一个代理键作为主键，这种主键的数据应该和应用无关，最简单的方法是使用AUTO_INCREMENT自增列。这样可以保证数据行是按顺序写入，对于根据主键做关联操作的性能也会更好。

最好避免随机的（不连续且值的分布范围非常大）聚簇索引，特别是对于I/O密集型的应用，它使得聚簇索引的插入变的完全随机，使数据没有任何聚集特性。使用InnoDB时应该尽可能的按主键顺序插入数据，并且尽可能的使用单调增加的聚簇键的值来插入新行。

覆盖索引

如果一个索引包含（或者覆盖）所有需要查询的字段的值，则称之为覆盖索引，优点如下：

索引条目通常远小于数据行大小，所以如果只需要读取索引，mysql就会极大的减少数据访问量
因为索引是按照列值顺序存储（至少在单个页内是如此），所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少的多
一些存储引擎如MyISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用
由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子结点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询

不是所有类型的索引都可以成为覆盖索引，覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以mysql只能使用B-Tree索引做覆盖索引。另外，不同的存储引擎实现覆盖索引的方式也不同，而且不是所有的引擎都支持覆盖索引。

当发起一个被索引覆盖的查询时，在EXPLAIN的Extra列可以看到Using index信息，示例如下，KEY last_name (first_name) USING BTREE：

mysql> EXPLAIN SELECT * FROM people WHERE first_name = 'm' AND last_name LIKE '%zha%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: people
         type: ref
possible_keys: last_name
          key: last_name
      key_len: 152
          ref: const
         rows: 2
        Extra: Using index condition; Using where
1 row in set (0.00 sec)

在first_name、last_name列上添加一个索引KEY last_name(first_name,last_name) USING BTREE，查询如下所示：

mysql> EXPLAIN SELECT * FROM people WHERE first_name = 'm' AND last_name LIKE '%zha%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: people
         type: ref
possible_keys: last_name
          key: last_name
      key_len: 152
          ref: const
         rows: 2
        Extra: Using index condition
1 row in set (0.00 sec)

在大多数存储引擎中，覆盖索引只能覆盖那些只访问索引中部分列的查询，不过可以更进一步优化InnoDB。InnoDB的二级索引的叶子结点都包含了主键的值，这意味着InnoDB的二级索引可以有效的利用这些“额外”的主键列来覆盖查询，示例如下，KEY last_name(last_name) USING BTREE：

mysql> EXPLAIN SELECT first_name,last_name FROM people WHERE last_name LIKE 'zha
%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: people
         type: range
possible_keys: last_name
          key: last_name
      key_len: 152
          ref: NULL
         rows: 2
        Extra: Using index condition
1 row in set (0.00 sec)

虽然上述示例中索引的列不包含first_name，但也能够用于对first_name做覆盖查询。

使用索引扫描做排序

mysql有两种方式可以生成有序的结果：通过排序操作，或者按索引顺序扫描。如果EXPLAIN中的type列的值为index，则表明mysql使用了索引扫描来做排序。

扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引记录就都回表查询一次对应的行。这基本上都是随机I/O，因此按索引顺序读取数据的速度通常要比顺序的全表扫描慢，尤其是在I/O密集型的工作负载时。

mysql可以使用同一个索引既满足排序，又用于查找行，设计索引时应该尽可能的同时满足这两种任务。

只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向都一样时，mysql才能使用索引来对结果做排序。如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。ORDER BY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求，否则，mysql都需要执行排序操作，而无法利用索引排序。

有一种情况下ORDER BY子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候，示例如下，KEY last_name (first_name,last_name) USING BTREE：

mysql> EXPLAIN SELECT dob,address FROM people WHERE first_name = 'm' ORDER BY last_name\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: people
         type: ref
possible_keys: last_name
          key: last_name
      key_len: 152
          ref: const
         rows: 2
        Extra: Using index condition; Using where
1 row in set (0.00 sec)

即时ORDER BY子句不满足索引的最左前缀的要求，也可以用于查询排序，这是因为索引的第一列被指定为一个常数。

冗余和重复索引

mysql允许在相同列上创建多个索引。重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引，应该避免。冗余索引和重复索引有些不同，如果创建了索引(A, B)，再创建索引(A)就是冗余索引，因为这只是前一个索引的前缀索引。

mysql的唯一限制和主键限制都是通过索引实现。

大多数情况下都不需要冗余索引，应该尽量扩展已有的索引而不是创建新索引。但也有时候出于性能方面的考虑需要冗余索引，因为扩展已有的索引会导致其变的太大，从而影响其他使用该索引的查询的性能。

一般来说，增加新索引将会导致INSERT、UPDATE、DELETE等操作的速度变慢，特别是当新增索引后导致达到了内存瓶颈的时候。

索引和锁

索引可以让查询锁定更少的行。如果查询从不访问那些不需要的行，那么就会锁定更少的行，从两个方面来看这对性能都有好处：

首先，虽然InnoDB的行锁效率很高，内存使用也很少，但是锁定行的时候仍然会带来额外开销
其次，锁定超过需要的行会增加锁争用并减少并发性

在mysql 5.1和更新的版本中，InnoDB可以在服务器端过滤掉行后就释放锁，但是在早起的mysql版本中，InnoDB只有在事务提交后才能释放锁。

InnoDB在二级索引上使用共享锁（读锁），但访问主键索引需要排他锁（写锁）。这消除了使用覆盖索引的可能性，并且使得SELECT ... FOR UPDATE比LOCK IN SHARE MODE或非锁定查询要慢的多。

posted @ 2019-02-26 15:30 zhangniuniu 阅读(383) 评论(0) 编辑收藏举报

刷新页面返回顶部

zhangniuniu