mysql 中间点策略_MYSQL 高效索引策略（完成）

忘是亡心i 2023-01-12 01:52 20阅读 0赞

介绍一下如何真正的发挥索引的优势

(1)独立的列

我们通常会看到一些查询不当的使用索引，或者是的MYSQL无法使用已有的索引。如果查询中的列不是独立的，则MYSQL就不会使用索引。“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数

例如下面的的这个查询语句

select student\_id form student where student\_id+1=5;

看一下我们就知道where中的表达式等价于actor\_id=4，但是MYSQL无法自动解析这个方程式，所以我们就要优化一下这个语句，始终把索引列单独放在比较符号的一侧

下面是另一个常见的错误

select …… where to\_days(current\_data)-to\_days(date\_col)<=10

(2)前缀索引和索引的选择性

有时候索引很长的字符列，会让索引变得大且慢，碰到这种情况，一种方法是模拟哈希索引列。单有时候这样子做仍然不够，那么要怎么做呢？

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

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

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

接下来演示一下如何创建前缀索引

alter table student.id add key(id(7))

以上语句的意思就是取id的前几位进行创建前缀索引

前缀索引是一种能使索引更小、更快的有效方法，但是另一方面也有其缺点：MySQL无法使用前缀索引做排序(order by或group by)，也无法使用前缀索引做覆盖扫描

(3)多列索引

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

第一个问题：为每个列创建独立索引

create tablet\{

c1int,

c2int,

c3int,key(c1),key(c2),key(c3)

\};

这种索引策略，一般是因为一句话“把where条件里面的列都建上索引”。实际上这句话是非常错误的。这样一来最好的情况下也只能是一星索引，其性能比起真正最优的索引可能差几个数量级。有时如果无法设计一个三星索引，那么不如忽略掉where子句，集中精力优化索引列的顺序，或者创建一个全覆盖索引

在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。MySQL5.0和耿勋版本引入了索引合并策略，一定程度上可以使用表上的多个单列索引来定位指定的行。更早的MySQL只能使用其中某一个单列索引，然而这个情况下没有哪一个独立的单列索引是非常有效的。

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

(1)当出现服务器对多个索引做相交操作室(通常由多个AND条件)，通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引

(2)当服务器需要对多个索引做联合操作室(通常有多个OR条件)，通常需要好非大量的CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不搞，需要合并扫描返回的大量数据的时候

(3)优化器不会把这些计算到“查询成本”中，优化器只关心随机页面读取。这会使得查询的成本被“低估”，导致该执行计划还不如直接走全表扫描。这样做不但会小号更多的CPU和内存资源，还可能会影响查询的并发性，但如果是单独运行这样的查询则往往会忽略对并发性的影响。

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

(4)选择合适的索引列顺序

索引列顺序实际上是非常重要的。正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好的满足排序和分组的需要(只用于B-Tree索引；哈希或者其他索引存储数据并不是顺序存储)

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

所以多列索引列的顺序至关重要。对于如何选择索引的列顺序有一个经验法则：将选择性最高的索引放在索引的最高列。在某些场景这个经验时非常有用，但是通常不如避免随机IO和排序那么重要，考虑问题需要更全面。

当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化where条件的查找。这种情况下，这样设计的索引确实能够最快的过滤出需要的行，对于在where的子句中只是用了索引部分前缀列的查询来说选择性也更高。然而性能不只是依赖于所有索引列的选择性(整体基数)，也和查询条件的具体值有关，也就是和值的分布有关(需要根据那些运行频率最高的查询来调整索引列的顺序，让这种情况下的索引列的选择性最高)。

以下面的查询为例

select \* from payment where staff\_id=2 and customer\_id=584

是应该创建一个(staff\_id,customer\_id)索引还是应该颠倒一下顺序？可以跑一些查询来确定这个表中值的分布情况，并确定哪个列的选择性最高。先用下面的查询预测一下，看看各个where条件的分支对应的数据基数有多大：

select sum(staff\_id=2),sum(customer\_id=584) from payment

![33bfd1b60b1c2162883387357bfd2710.png][]

根据前面的经验法则，我们应该将索引列customer\_id放在前面，因为对应条件值的customer\_id数量更小。我们再来看看对于这个customer\_id的条件值，对应的staff\_id列的选择性如何

![b151aeaa8b674e8c7e66e7df32c75a01.png][]

这样做有一个地方需要注意，查询的结果非常依赖于选定的具体指。如果按上述方法优化，可能对其他一些条件值的查询不公平， 服务器的整体性能可能变得更早，或者其他某些查询的运行变得不如预期。

如果是从诸如pt-query-digest这样的工具的报告中提起“最差”查询，那么再按上述办法选定的索引顺序往往是非常搞笑的。如果没有类似的具体查询来运行，那么最好还是按经验法则来做，因为经验法则考虑的是全局基数和选择性，而不是某个具体查询：

![4cffecc2cfd2304c29345e9a53a4b397.png][]

从图中我们就可以看出来customer的选择性更高，所以答案是将其作为索引列的第一列

alter table payment add key(customer\_id,staff\_id)

(5)聚簇索引

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

当表有局促索引时，它的数据行实际上存放在索引的叶子页中。术语聚簇表示数据行和相邻的键值紧凑的存储在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

![5fec8204b4a619796cb1a01459f475ed.png][]

如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。InnoDB只聚集在同一个页面中的记录，包括相邻键值的页面可能会相距甚远

聚簇主键可能对性能有帮助，但也可能导致严重的性能问题。所以需要仔细的考虑聚簇索引，尤其是将表的存储引擎从InnoDB改成其他引擎的时候(反过来也一样)。

聚集的数据有一些优点：

(1)可能把相关数据保存在一起。例如实现电子邮箱时，可以根据用户ID来聚集数据，这样子只需要从磁盘中读取少数的数据也技能获取某个用户的全部邮件

(2)数据访问更快。聚簇索引把索引和数据都放在同一个B-Tree中，因此从聚簇索引中获取数据比从非聚簇索引中要快

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

聚簇索引的缺点：

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

(2)插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。但如果不是按照逐渐顺序加载数据，那么在加载完成后最好使用OPTIMIZE TABLE重新组织一下表

(3)更新聚簇索引列的代价很高。因为要强制InnoDB将每个被更新的行移动到新的位置

(4)基于聚簇索引的表在插入新航，或者主键被更新导致移动行的时候，可能面临“页分裂”的问题。当行的主键值要求姜哲一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页来容纳该行，这就是一次页分裂操作，这也意味着这样导致表占用更多的磁盘空间

(5)聚簇索引可能导致权标扫描变慢，尤其是行比较稀疏时，或者由于页分裂导致数据存储不连续的时候

(6)二级索引可能比想想的要更大。因为二级索引的叶子结点包含了引用行的主键列

(7)二级索引访问需要两次索引查找，而不是一次

最后一点让人有些疑惑，为什么二级索引需要二次查找？答案在于二级索引中保存的“行指针”的实质。要记住，二级索引叶子节点保存的不是指向行的物理位置的指针而是行的主键值

这意味着通过二级索引查找行，需要存储引擎找到二级索引的叶子节点获得对应的主键值，然后根据这个值去聚簇索引中查找到对应的行。这里做了重复的工作：两次B-Tree查找而不是一次，

(6)覆盖索引

通常大家都会根据查询的where条件来创建合适的索引，不过这也只是索引优化的一个方面。设计优秀的索引应该考虑到整个查询，而不单单是where条件部分。索引确实是一种查找数据的高效方式，但是MySQL也可以使用索引来直接获取列的数据，这样就不再需要读取数据行。如果索引一个索引包含索引需要查询的字段的值，我们就称其为覆盖索引

覆盖索引的好处如下：

(1)索引条目通常远小于数据行的大小，所以如果只需要读取索引，那么MySQL就会极大的减少数据访问量。这对缓存的负载非常重要，因为这种情况下响应时间大部分花费在数据拷贝下。覆盖索引对于I/O密集型的应用也有帮助，因为索引比数据更小，更容易全部放进去内存

(2)因为索引是按照列值顺序存储的，对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少的多。对于某些存储引擎，例如MyISAM的Percona XtraDB，甚至可以通过POTIMIZE命令是的索引完全顺序排列，这样就可以让简单的范围查询能使用完全排序的索引访问

(3)一些存储引擎如MyISAM在内存中只缓存索引。数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。这可能会导致严重的性能问题，尤其是那些系统调用占了数据访问中的最大开销的场景

(4)由于InnoDB的聚簇索引，覆盖索引对于InnoDB表特别有用。InnoDB的二级索引在叶子节点保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询

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

(7)使用索引扫描来做排序

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

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

MySQL可以使用同一个索引既满足排序，又用于查找行。因此，如果可能，设计索引时应该尽可能的同时满足这两种任务，这样是最好的。

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

有一种情况下order by子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候。如果where 子句或者join子句中这些列指定了常量，就可以弥补索引的不足。

下面是我一些不能使用索引做排序的查询

![dde801dfeb27491e12f0b86090ae4b82.png][]

(8)压缩前缀

MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中，这在某些情况下能极大的提高性能(默认只压缩字符串，但是也可以通过参数设置压缩数字)。

MyISAM压缩每个索引块的方法是，先完全保存索引块中的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分，把这一份存储起来即可。比如，索引块的第一个值是“bea”，第二个是“beautyful”，那么第二个值压缩后就是“4，tyful”。MyISAM对于行指针也有类似的压缩手段

压缩块使用更少的空间，代价是某些操作可能更慢。因为每个值的压缩前缀都依赖前面的值，所以MyISAM查找是无法在索引块使用二分查找只能从头到尾进行扫描。正序的扫描速度还不错，但是如果是倒序扫描(比如order by desc)就不是很好了。所有在快中查找一行的操作平均都需要扫描半个索引块

测试表明，对于CPU密集型引用，因为扫描需要随机扫描，压缩索引使得MyISAM在索引查找上要慢好几倍。压缩索引的倒序扫描就更慢了。压缩索引需要在CPU内存资源与硬盘之间做权衡。压缩索引可能只需要1/10的磁盘空间，如果是I/O密集型应用，对某些查询带来的好处会比成本多很多。

(9)冗余和重复索引

重复索引是指在相同列上按照相同的顺序创建的相同类型的索引。应该避免这样的创建重复索引，发现以后也应该立即移除。比如以下代码

create tabletest\{

idint not null primary key,

aint not null,

bint not null,unique(id)index(id)

\}engine=InnoDB;

一个经验不足的用户可能是想创建一个主键，先加上唯一限制，然后再加上索引以供查询使用。然而唯一限制和主键限制都是通过索引使用，因此上面的写法实际上在相同的列上创建了三个重复的索引。当然了，如果这三个索引是不同类型的，那么就不算是重复索引，比如key(id)鸡儿filltext key(id)就不算是重复索引

冗余索引和重复索引有一些不同，比如：如果创建了索引(A,B)，再创建(A)那就是冗余索引，因为A就是前一个索引的前缀索引。索引(A,B)完全就可以当做A来使用。但是如果创建了索引(B,A)那就不是冗余索引了，索引B也不是。因为B不是索引(A,B)的最左前缀索引。另外，其他不同类型的索引，例如哈希，全文索引也不会是B-Tree的冗余索引。

冗余索引通常发生在为表添加新索引的时候。例如，有人可能会增加一个新的索引(A,B)而不是拓展已有的索引(A)，还有一种情况是将一个索引扩展为(A,ID)，其中的ID是主键，对于InnoDB来说主键列已经包含在二级索引当中了， 所以这也是冗余的

大多数情况下不需要冗余索引，应该尽量扩展已有的索引而不是创建新的索引。但也有时候处于性能方面的考虑需要冗余索引，因为扩展已有的索引会导致其变得太大 ，从而影响其他使用该索引的查询的性能。例如，在一个整数列索引上添加一个很长的varchar列，那性能可能会急剧下降。特别是有索引把这个索引当中覆盖索引时，或者这是MyISAM表并且有很多范围查询的时候

解决冗余索引和重复索引的方法非常简单，删除这些索引就可以。但是首先要做的事找出这样的索引。可以通过歇一歇复杂的访问information\_schema表的查询来找，不过还有两个更简单的方法就是使用Shlomi Noach的common\_schema中的一些视图来定位(common\_schema是一系列可以安装在服务器上的常用的存储和视图)。另外一个方法就是使用Percona Toolkit中的pt\_duplicate-key-checker，该工具通过分析表结构来找出冗余和重复索引。

(10)未使用的索引

除了冗余索引和重复索引，可能还会有一些服务器永远不用的索引。这样的索引完全是累赘，建议直接删除。有两个工具可以帮助定位未使用的索引。最简单的方法是在Percona Server或者Maria DB中先打开userstates服务器变量，然后让服务器正常运行一段时间，再通过查询INFORMATION\_SCHEMA\_STATISTICS就能查到每个索引的使用频率。另外还可以使用Percona Toolkit中的pt-index-usage，该工具可以读取查询日志，并对日志中都每条查询进行EXPLAIN操作，然后打印关于索引和查询的报告

(11)索引和锁

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

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

InnoDB只有在访问行的时候才会对其枷锁，而索引能够减少InnoDB访问的行数，从而减少锁的数量。但这只有当InnoDB在存储引擎层能够过滤掉索引不需要的行时才有效。如果索引无法过滤掉无效的行，那么在InnoDB检索到数据并返回给服务器层以后，MySQL服务器才能应用where子句。这是已经无法避免锁定航了：InnoDB已经锁住了这些行。到适当的时候才释放。

比如以下的查询语句

![099d7ca940c1b6b45dea34e8d7df82d6.png][]

返回结果如下

![2dd57f958d4d09165d8083e60c3b84f9.png][]

从结果上看实际上我们仅需要对2-4之间的行进行加锁，但是实际上获取了1-4之间的行的排它锁。InnoDB会锁定第一行，这是因为MySQL为该查询选择的执行计划是索引的范围扫描：

![f7ceb5fb8210c3ddc9b67630d116bde1.png][]

换句话说，底层存储引擎的操作是：从索引的开头开始获取满足条件actor\_Id<5的记录。服务器并没有告诉InnoDB可以过滤掉第一行的where条件。注意EXPLAIN的EXTRA出现了using where，这表示MySQL服务器将存储引擎返回行以后再引用了where过滤条件

保持第一个链接打开，然后再打开一个链接并执行一下查询

![6d930a874229852e163ebd42c8fd3aa3.png][]

这个查询将会直接挂起，知道第一个事务释放第一行的锁。

以上栗子表明了即使使用索引，InnoDB也会锁住一些不需要的数据。如果没有使用索引查找和锁定行的话问题将会更加糟糕，MySQL会做全表扫描并锁定索引的行，不管需不需要。

[33bfd1b60b1c2162883387357bfd2710.png]: /images/20221119/f36536146f444b99898a47e165e8f774.png
[b151aeaa8b674e8c7e66e7df32c75a01.png]: /images/20221119/6cd7a94e52d9457abdd068655b6ebcc7.png
[4cffecc2cfd2304c29345e9a53a4b397.png]: /images/20221119/7acab486469f4b4bb182de4d62a763af.png
[5fec8204b4a619796cb1a01459f475ed.png]: /images/20221119/830a7139648d4073b11bac39332d81c9.png
[dde801dfeb27491e12f0b86090ae4b82.png]: /images/20221119/f032de7b062e41ebb1a3b3d9cd611ed4.png
[099d7ca940c1b6b45dea34e8d7df82d6.png]: /images/20221119/f365da9243634517b46c4e2921e3db2a.png
[2dd57f958d4d09165d8083e60c3b84f9.png]: /images/20221119/4507f3ca6925496e8c63fd4586a7b1a1.png
[f7ceb5fb8210c3ddc9b67630d116bde1.png]: /images/20221119/b2f4cbb37d1c41bca72f44f5db247b62.png
[6d930a874229852e163ebd42c8fd3aa3.png]: /images/20221119/03db9f8df8de48979baa4d955a3f35e7.png