索引个人理解总结

索引

锁和索引是数据库中两个核心知识点，不管是在日常工作中还是面试中都经常打交道。今天就把我自己的理解分享出来。

索引的作用就是加快检索数据速度。

普通索引、唯一索引、主键索引、全文索引

按功能分类（业务逻辑）

普通索引是基础的索引，没有任何约束，主要用于提高查询效率。唯一索引就是在普通索引的基础上增加了数据唯一性约束。主键索引就是在唯一索引的基础上增加了非空约束。全文索引在大量文本数据检索时比like + %快N倍。一般用的不多，一般使用搜索引擎。这个介绍全文索引的一篇博客https://blog.csdn.net/mrzhouxiaofei/article/details/79940958。

聚簇索引、非聚簇索引

按物理实现分类

聚簇索引也叫主键索引：按照主键来排序存储数据。叶子节点存的是整行数据

非聚簇索引：也称为二级索引或者辅助索引。叶子节点内容是主键的值

单一索引、联合索引

按字段个数分类

create table person(
id int primary key, 
age int not null, 
name varchar(16),
index (age))engine=InnoDB;

id	name	age
100	张三	19
200	李四	20
300	王五	31
400	张牛牛	40

innodb使用B+树索引模型，一个索引对应一棵树。

p1,p2,p3,p4表示整行数据， 右边的age索引100，200，300，400，代表的是这个数据的主键即id。

基于主键索引和普通索引的查询有什么区别？

• 如果语句是 select * from person where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；
• 如果语句是 select * from person where age=40，即普通索引查询方式，则需要先搜索 age索引树，得到 ID 的值为 400，再到 ID 索引树搜索一次。这个过程称为回表[]

覆盖索引

只需要从索引中查，不用从表中读取数据，就叫使用了覆盖索引。即不需要回表。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。

例如查询select ID from person where age between 30 and 50，这时只需要查 ID 的值，而 ID 的值已经在age 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

使用联合索引的目的就是使用覆盖索引，不用回表，提高查询效率。例如一个用户表中有id_card(身份证号)、name(姓名)，有一个需求根据身份证号查询姓名，就可以创建一个联合索引KEY idCard_name_index (id_card, name)。这样就可以走覆盖索引，不需要回表，加快查询速度。

普通索引和唯一索引

这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。建议你尽量选择普通索引。唯一索引用不上 change buffer 的优化机制。在业务上保证唯一性。

如果所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭 change buffer。而在其他情况下，change buffer 都能提升更新性能。

在实际使用中，你会发现，普通索引和 change buffer 的配合使用，对于数据量大的表的更新优化还是很明显的。

特别地，在使用机械硬盘时，change buffer 这个机制的收效是非常显著的。所以，当你有一个类似“历史数据”的库，并且出于成本考虑用的是机械硬盘时，那你应该特别关注这些表里的索引，尽量使用普通索引，然后把 change buffer 尽量开大，以确保这个“历史数据”表的数据写入速度。

MySQL选错索引

选错索引一般是索引信息统计的不对，导致优化器选择了错误的方案。analyze table t 命令，可以用来重新统计索引信息。如果还不行可以通过修改sql语句诱导优化器走别的索引，或者使用force index强制使用索引。例如 select * from t force index(xxx) where xxx;

给字符串字段加索引

MySQL 是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。

索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低。索引选取的越短，区分度越低，重复的键值就越多，效率就底。

指定多长长度合适，即能区分度高又能节省索引所占的空间。这个需要根据字段的内容来设置。

几种解决方式

1. 如果前边的重复比较高，可以倒序存储。在查询的时候使用reverse()函数来把传入的值倒过来。
2. 新建一个字段存入这个字段的hash值。

还有一个问题是使用了前缀索引就不能使用覆盖索引了。因为不是完整的字段值所以还需要回表再次查询。

索引重建

重建二级索引可以直接删除，然后在创建。但是重建主键索引不可以这么操作。不论是删除主键还是创建主键，都会将整个表重建。所以连着执行这两个语句的话，第一个语句就白做了。这两个语句，你可以用这个语句代替 ： alter table T engine=InnoDB。

重建索引可以节省空间，比如日志表定期删除数据，但是索引还在占据着大量的空间。

mysql主键不建议使用过长字段，推荐使用自增主键。