mysql索引小结——高性能mysql

时间:2022-06-10 19:31:43

1、索引可以包含一个或者多个列的值,如果索引包含多个列的值,列的顺序很重要,mysql只能高效地使用索引的最左列前缀列。

2、索引是在存储引擎层而非服务器层实现的。

3、B-tree索引的限制:

  • 如果不是按照索引的最左列开始查找,则无法使用索引;
  • 不能跳过索引中的列;
  • 如果查询中有某个列的范围查询,则其右边所有列都无法使用索引优化查询。

4、哈希索引的限制:

  • 哈希索引只包含哈希值和行指针,而不存储字段值,所以不能使用索引中的值来避免避免读取行;
  • 哈希索引数据不是按索引值顺序储存的,所以也就无法用于排序;
  • 哈希索引不支持部分索引列匹配查找,因为哈希索引始终是使用索引列的全部内容来计算哈希值的;
  • 哈希索引只支持等值比较查询,包括=、IN()、<=>(注意<>和<=>是不同的操作),也不支持任何范围查询,如where price<50;
  • 访问哈希索引的速度非常快,除非有很多哈希冲突(不同的索引列值 ,却有很多相同的哈希值);
  • 如果哈希冲突很多,一些索引维护操作的代价会很高。

5、索引的优点:

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

6、评价索引的“三星系统”:

  • 一星:索引将相关的记录放到一起;
  • 二星:如果索引中的数据顺序和查找中的排列顺序一致;
  • 三星:如果索引中的列包含了查询中需要的全部列。

7、快速查找除了索引技术,还有分区技术和块级别元数据技术。

8、独立的列

如果查询的列不是独立的,则mysql不会使用索引。“独立的列”指索引列不能是表达式的一部分,也不能是函数的参数。以下查询无法使用列的索引:

select actor_id from actor where actor_id+1=5
select ... where to_days(current_date)-to_days(date_col)<=10

8、前缀索引和索引选择性

索引的选择性指不重复的索引值与数据表的记录总数的比值。前缀索引是一种能使索引更小、更快的有效办法,缺点是:mysql无法使用前缀索引做order by和group by,也无法使用前缀索引做覆盖扫描。

9、多列索引

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

10、选择合适的索引顺序列

经验法则:将选择性最高的列放到索引最前面。有时和值的分布有关。

11、 聚簇索引

聚簇索引不是一种单独的索引类型,而是一种数据存储方式。InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。术语“聚簇”表示数据行和相邻的键值紧凑的存储在一起。

聚簇的数据优点:

  • 可以把相关数据保存在一起;
  • 数据访问更快;
  • 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

聚簇索引缺点:

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

向UUID主键插入行占用更多时间和空间;对于高并发负载,在InnoDB中按主键顺序插入可能会造成明显的争用,主键的上界和AUTO_INCREAMENT锁机制会成为“热点”。

12、覆盖索引

如果一个索引包含所有需要查询的字段的值,我们称之为“覆盖索引”。覆盖索引必须要存储索引列的值,而哈希索引、空间索引和全文索引都不存储索引列的值,所以mysql只能使用B-Tree索引做覆盖索引。

当发起一个索引覆盖查询时,在EXPLAIN的Extra列可以看到“Using index”的信息。

13、使用索引扫描来做排序

  • 如果EXPLAIN出来的type列的值为“index”,则说明mysql使用了索引扫描来做排序;
  • 只有当索引的列排序顺序和order by子句的顺序完全一致,并且所有列的排序方向(倒序或正序)都一样时,mysql才能使用索引来对结果排序;
  • 如果查询需要关联多张表,则只有当order by子句引用的字段全部为第一个表时,才能使用索引做排序;
  • order by子句和查找型查询的限制一样:需要满足索引的最左前缀的要求。

14、压缩(前缀压缩)索引

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

15、重复索引和冗余索引

重复索引:在相同的列上按照相同的顺序创建的相同类型的索引,应当避免和移除,如:

create table test{
id
int not null privary key,
a
int not null,
unique(id),
index(id),
}ENGINE
=InnoDB;

冗余索引和重复索引有一些不同。如果创建了索引(a,b),再创建索引(a)就是冗余索引。

16、未使用的索引

有些索引可能服务器永远也不会用到,建议删除。

17、索引和锁

InnoDB只有在访问行的时候才会对其加锁,而索引能减少InnoDB访问的行数,从而减少锁的数量。

18、选择和编写利用索引的查询时,三个原则:

  • 单行访问很慢;
  • 按顺序访问范围数据很快;
  • 索引覆盖查询很快。