mysql索引部分总结

索引

索引是存储引擎层实现

索引低层实现

• B-Tree索引
• 哈希索引
  hash索引只有在精确匹配时才有效，相当于他会为每一行数据的索引生成一个hash值，这样可以直接通过hash值去找到对应的数据，时间复杂度为O(1)。 只有memory引擎显式的
  支持hash索引，并且支持非唯一hash索引，如果不同的行具有相同的hash code，就会以链表的形式存储。InnoDB有一个自适应哈希索引，这是一个存储引擎自驱的行为，用户无法控制，当引擎发现某些索引为热点索引时，他就会自动的创建一个哈希索引，优化查询效率。哈希索引只存储哈希值和行指针。优缺点不概述。
• 全文索引
• 空间索引

在非常小的表中，用索引没有全表扫描效率高；在中大型表中，索引比较有效；在特大型表中，维护索引的成本远远高于使用它的成本，因此一般用分区技术来查询大数据。

索引优化策略

explain优化索引

https://blog.csdn.net/why15732625998/article/details/80388236?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522165985497316782248519679%2522%252C%2522scm%2522%253A%252220140713.130102334…%2522%257D&request_id=165985497316782248519679&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2_alltop_positive~default-1-80388236-null-null.142^v39pc_rank_v37,185^v2control&utm_term=explain%E7%9A%84%E7%94%A8%E6%B3%95&spm=1018.2226.3001.4187

独立的列

索引列不能时表达式的一部分或者函数的一部分
错误的例子

create table "user"(
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `age` int(5) NOT NULL COMMENT '年龄',
  `name` varchar（20） NOT NULL COMMENT '姓名',
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`),
)ENGINE=InnoDB

#索引列变成了表达式的一部分
select * from 'user' where age-1=5;

#索引成为了函数的参数
select * from 'user' where toAge(age)=10;
1
2
3
4
5
6
7
8
9
10
11
12
13

前缀索引

取某个字段最前面的一部分作为索引，问题是前缀的长度多少合适呢，前缀太长，比较占存储空间，前缀太短，索引的选择性差（选择性是指不重复的索引值和数据表总数之间的比值，其中唯一索引的选择性为1，最好）。
这里的判断依据是前缀索引的选择性接近于索引整个列的选择性，从宏观上考虑平均选择性，同时还要考虑数据分布不均匀带来的影响。

缺点：前缀索引不能用于order by和group by，而且不能做覆盖索引

多列索引列顺序

通常情况下就是把区分度高的列放在前面，区分度指的是

count(distinct(xxx))/count(*)的比值，比值越大说明区分度越好。
1

聚簇索引和非聚簇索引

目前InnoDB存储引擎支持聚簇索引，也就是B+Tree的存储结构
聚簇索引其实指的是存储方式，每一张表只能有一个聚簇索引，一般挂在主键或者唯一索引下，我们一般理解的索引和真实的表数据是两块存储，从索引存储中可以找到表对应行的数据的物理地址，通过物理地址就可以找到行数据。但是在聚簇索引下，索引的叶子节点下面直接挂着真实的行数据，也就是说索引和表数据在同一块存储上。这样的好处访问更加块，同时可以减少磁盘IO的次数。但是我们必须要明白对于一个索引来说他也不是完全连续的，可能也就分块离散存储在磁盘上，每一个行数据也有可能分散在不同的磁盘位置，所以聚簇索引就是将每一个页面的行数据聚集在一起和叶子节点一起存储。
而非聚簇索引下叶子节点指向的不再是对应行的物理地址，而是主键值或者唯一索引，通过主键值或者唯一索引去聚簇索引上查找行数据，这样的话非聚簇索引要回表查询，要找到行数据至少查找二次索引。

聚簇索引的缺点：

• 插入速度完全取决于插入顺序，按主键顺序插入的速度最快，因此一般用于应用的无关的自增主键来做聚簇索引，保证了顺序插入。
• 在插入新行时可能会导致页分裂，从而占用更大的磁盘空间。
• 非聚簇索引由于保存了主键值可能变得很大（如果主键列长度非常长的情况下）

覆盖索引

一个索引包含所需要查询的所有字段， 这就叫覆盖索引查询。

优化排序

mysql生成排序有两种方式，一种是通过排序操作，另一种是使用索引排序，如果能用第二种尽量不要用第一种，我们在使用索引排序时需要注意以下问题，索引列的顺序要和排序的顺序一致，且所有列的排序方式都要一样。不能部分正排部分倒排。
还有一些别的一些情况，遇到了排序优化问题时可以参考书中P176的内容

延迟关联：利用延迟关联或者子查询优化超多分页场景。在使用limit offset,n的关键词时MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写。
反例：这种方式底层的逻辑是如果where命中非聚簇索引，会先去非聚簇索引中找到对应的主键值，然后根据主键值一个个去找到行数据，最后丢弃前100000个数据，取最后一个数据，我们可以发现根据非聚簇索引去聚簇索引中找行数据通常情况下是随机IO，那么这条语句需要查100020条数据，也就是100020次随机IO，耗时非常长，不推荐这么用

SELECT * FROM 表1 where 条件 LIMIT 100000,20
1

正例：先快速定位需要获取的id段，然后再关联，这种方式的底层逻辑是首先去非聚簇索引中找到主键，取前100020后丢弃前100000，这个过程中需要的内存小，而且不需要去聚簇索引中查询因此不会有很大的IO耗时，然后根据20个id值去主表中查，这种方式的查询时间远远小于前者。

SELECT t1.* FROM 表1 as t1, (select id from 表1 where 条件 LIMIT 100000,20 ) as t2 where t1.id=t2.id
1

压缩索引

冗余索引和重复索引

尽量避免冗余索引以及重复索引的出现
重复索引可以理解为为同一列创建了多个不同级别的索引，比如为id列创建了主键、唯一索引以及普通索引三类，那么后两者就是重复索引。
冗余索引指的是在（A，B）已经建立了联合索引的情况下再去建立索引A
注：在（A，B）已经建立了联合索引的情况下再去建立索引（B、A）不算冗余索引，冗余索引在有些特殊情况下也是必须的，因此需要综合考虑

索引和锁

索引的作用是为了让查询锁定更少的行，InnoDB在非聚簇索引上用了共享锁，在聚簇索引上用了排他锁。select for update比select in share mode查询慢得多

减少索引和数据碎片化

使用Optimize table或者导出再导入数据的方式可以重新整理数据，减少碎片化。

MVCC属于多版本并发控制，简单理解就是通过两个隐藏的字段行创建时间和行删除时间（版本）来控制每个事务能看到的数据，当开启事务时，会将系统版本号+1，作为本次事务的版本号，用于增删改的版本号更新。
在Repeatable Read模式下，

• select只能找到行创建版本在事务版本号之前删除版本没定义或者在事务版本号之后的数据。
• insert会新增一条数据，行创建版本为当前事务版本

间隙锁

在mysql的innoDB存储引擎中，如果更新操作是针对一个区间的，那么它会锁住这个区间内所有的记录，比如update xxx where id between a and b那么它会锁住a到b之间所有记录，注意是所有记录，甚至这个记录并不存在也会被锁住，这个时候，如果另外一个连接需要插入一条记录到a到b之间，那么它就必须等到上一个事务结束。