创建高性能的索引

我会带着你远行 2022-09-29 01:51 190阅读 0赞

1.索引基础

索引可以包含一个或多个列的值，创建多个列的索引，顺序很重要，mysql只能高效的使用索引的最左前缀列。

1.1索引类型

B-Tree类型：我们将的索引基本就是这个索引。

这个索引能够加快访问数据的速度，因为存储引擎不需要扫描全表获取数据，而是从索引的根节点开始搜索。这个索引适用于全键值，键值范围或键前缀查找(最根据最左前缀)。

全值匹配是指和索引中所有的列进行匹配。匹配最左前缀是指只使用索引的第一列。匹配列前缀是指：匹配第一列开头的一部分。匹配范围值是指索引第一列中在一定范围中的值。因为索引是有序的，所以索引还可以用于order by操作。

这个索引的限制：如果不是按照索引最左列开始查找，则无法使用索引。不能跳过索引中的列。如果索引中某一列是使用的范围查找，则这一列右边的列都不能使用索引优化查找。

哈希索引基于哈希表的实现，只有精确匹配索引所有列的查询才有效。

哈希索引的限制：

1.哈希索引只包含哈希值和行指针。不存储字段值，所以不能使用索引中的值来避免读取行。

2.哈希索引的不值按照索引值的顺序排序的所以不能用于排序。

3.哈希索引不支持部分列的范围查找。

4.哈希索引只支持等值比较查找。

5.访问哈希索引的数据非常快，除非有很多哈希冲突。

6.如果哈希冲突很多，维护索引消耗很大。

2.索引的优点

1.索引减少了服务器需要扫描的数据量

2.索引可以帮助服务器避免排序和临时表

3.索引可以将随机i/o变为顺序i/o。

3.高性能的索引策略

3.1独立的列

独立的列是指索引不能是表达式的一部分或者函数的参数。

select * from test where age+1=5;//这两种情况是不使用索引的，因为不是独立的列 select * from test where to_days(time)-to_days(oldtime)=10;

3.2前缀索引和索引选择性

索引很长的字符串，索引会变得很大而且很慢。一种方式是使用模拟哈希索引，有时候这样做还不够。通常索引开始的部分字符，而不是索引全部，这样可以节约索引空间，提升效率。但是这样会降低索引的选择性，索引的选择性指：不重复的索引值和数据库记录总数的比值。索引选择性越高则查询效率越高，因为索引选择性越高，过滤的行就越多。

一般情况下，某个列的前缀选择性也是足够高的，足以满足查询需要。对于blob,text,很长的varchar()，必须使用前缀索引。

前缀索引的诀窍在于选择足够长的前缀以保证较高的选择性，同时又不能太长。缺点：前缀索引无法使用order by和group by。也无法使用前缀索引来做覆盖扫描。

3.3 多列索引

在多个列上独立的建立单独的索引大部分情况下不能提高mysql的查询性能。mysql5.0引入一种叫索引合并的策略，一定程度上可以使用表上的多个单独索引来定位指定的行。更早版本只能使用某一个单独的索引，而多列的情况下，没有哪一个单独的索引是高效的。

select * from test where age=12 or name='jack';//在age和name上分别有一个索引。

在老版本上面的语句会使用全表查询。除非改成：

select * from test where age=12 union all select * from test where name='jack' and age<>12

但在mysql5.0后，查询能够同时使用两个单独索引进行扫描，并将结果进行合并。这种算法有三个变种：or的联合，and相交，以及联合和相交两种一起。

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

1.当出现服务器对多个索引进行相交操作时，意味着需要一个包含多个列的索引而不是多个单独的索引。

2.当出现服务器对多个索引进行联合操作时，通常需要消耗大量的cpu和内存资源在算法的缓存，排序，合并操作上。特别是有的索引选择性不高，需要合并扫面返回大量数据的时候。

3.更重要的是，优化器不会把这些计算到查询成本中，优化器只关心随机页面读取。这会使得查询成本被低估，导致执行该计划还不如扫描全表。这样做不仅消耗资源，而且还影响查询的并发性。所以多个列进行联合操作，可以将查询方式写成union格式。

3.4选择合适的索引列顺序

不考虑排序和分组时，将选择性高的列放在前面通常是很好的。这时候索引的作用是用于where条件的查找。但是性能不只是依赖于索引列的选择性，也和查询条件的具体值有关，也就是值的分布有关。

3.5聚簇索引

聚簇索引不是一种单独的索引类型，而是一种数据存储结构。InnoDB的聚簇索引是同一结构中保存b-tree索引和数据行。InnoDB通过主键聚集数据，如果没有定义主键，选择一个非空的索引代替，如果没有这样的索引，会隐式定义一个主键来作为聚簇索引。

聚集的数据有一些优点：

1.可以把相关数据保存在一起。

2.数据访问更快

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

缺点：

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

2.插入速度严重依赖于插入顺序。

3.更新聚簇索引列的代价很高，因为要移动列到新的位置。  
      4.基于聚簇索引的表在插入新行时，或者主键被更新需要移动行的时候，可能面临页分裂问题。

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

6.二级索引可能比想象中的要大。

7.二级索引需要两次索引查找。  
      如果innoDB表中没有什么数据需要聚集，那么可以定义一个代理键作为主键，最简单的方法就是使用auto\_increment.最好避免随机的聚簇索引。

3.6覆盖索引

如果一个索引包含需要查询的值，就叫做覆盖索引。

4.1支持多种过滤条件

如果一个列的选择性低，但是出现的频率很高，可以作为索引列的最左列。没有这个字段的查询也可以绕过，就是使用这个字段in()，例如sex in('m','f')这样写不会过滤任何行。但是in太长就不行了。范围查找的列最好放到后面去。避免多个范围查找

4.2优化排序

使用orderby limit的查询，没有索引很慢。有索引但是在查询后面的数据也是很慢。

select name from test where sex='m' order by r limit 10000,10;
    //这个查询后面的数据很慢，可以改为
    select name from test inner join(
    select id from test where sex='m' order by e limit 10000,10
    ) as x using(id)

5.1找到并修复受损的表

使用check table来看是否表损坏了，使用repair table

5.2更新索引信息

show index from xxx可以查看索引的基数。