大纲：

  1. 主键索引，普通索引
  2. 唯一索引，普通索引
  3. 索引的使用技巧

 

一、主键索引，普通索引

创建测试表

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

表中R1~R5的(ID,k)值分别为(100,1)、(200,2)、(300,3)、(500,5)和(600,6)，两棵索引树的示例示意图如下

三级索引毫秒级寻址_联合索引

1.索引的结构：

innodb索引中最常见的是B+树结构，类似一个N叉树，N大小与数据页（默认16k可以用innodb_page_size设置）大小有关，存储值（long比int占空间大）的类型有关，存储的数据量随层数指数增长

这个B+树由多层，N大概在1000左右，4层可以存10亿数据

主键ID非叶子节点存储的冗余了所有ID值方便进行查找，叶子节点存储这一行所有数据

普通索引k非叶子节点冗余所有k的值，叶子节点存储主键id

 

2.普通索引的回表：

由上面的结构可以看出，当查询使用的是普通索引时，先扫描普通索引树，找到主键ID后再扫描主键索引树找到行记录，这个过程叫“回表”

 

3.索引的插入更新：

B+树为了维护索引有序性（每一层都是有序的），如果插入新的行ID值为700，则只需要在R5的记录后面插入一个新记录。如果新插入的ID值为400，需要逻辑上挪动后面的数据，空出位置。

如果R5所在的数据页已经满了，根据B+树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响。

除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约50%。

因此，为了提高效率与空间利用率尽量创建一个顺序的主键。

 

二、唯一 索引，普通索引

1.普通索引唯一索引查询过程：首先都在内存中查询有没有索引数据所在页，如果有直接返回，如果没有先从硬盘读入内存再返回。区别是唯一索引可以直接返回结果，普通索引找到匹配数据后指针继续后移（索引的有序性）直到不匹配，然后返回结果。因此唯一索引在select性能上的差异微乎其微。

2.普通索引唯一索引更新过程：首先都在内存中查询有没有索引数据所在页，如果有直接返回，如果没有，唯一索引将从硬盘读入数据到内存并判断唯一性然后进行写入操作，普通索引直接写入change buffer后返回。当select时候会将新的数据页读入内存再去change buffer里寻找该页中需要更新的数据进行更新，可以减少磁盘读。而且，频繁刷新内存中的数据页，也会降低内存命中率，从而减少数据库整体的读性能。因此，除非表数据有要求必须一致，否则尽量使用普通索引。

change buffer用的是buffer pool里的内存，可以通过参数innodb_change_buffer_max_size来动态设置。change buffer修改的数据写入内存页操作称为merge，除了上述select会触发merge，还有后台线程定时merge，数据库正常关闭merge。

change buffer与redolog作用区别，change buffer防止每次写操作就从硬盘读新的数据页进来，redolog防止每次写操作就直接写入硬盘，共同点是优化写操作的磁盘IO性能，不同是redolog优化磁盘写，change buffer优化磁盘读。

三级索引毫秒级寻址_三级索引毫秒级寻址_02

 

三、索引使用技巧

1.索引覆盖：

当select语句使用到某个普通索引，且索引包含所用需要查询的列，这时不回表，这种特性叫“索引覆盖”。

 

2.最左前缀

当select语句中where包含多个值，又走到一个联合索引上，匹配的顺序则是从左到右，当联合索引的第一列在where条件中再看第二列是否在where条件中，以此类推。简言之：用到联合索引前n列。

最左前缀同样适用于字符串匹配前n个字符，这就是like 'xxx%'这种后模糊依然走到索引的原因。

 

3.索引下推

从最左前缀可知，如果where包括联合索引的第一、二、四列，而没有用到第三列，其实只有前二列在索引判断的时候有效，第四列只能回表后判断。

因此innodb做了优化，第一、二列满足条件后，即使第三列不在where条件中，还是会判断联合索引后面的列是否在where条件中，如果在的话，也可以进行判断，从而减少回表次数。

 

4.索引区分度

索引上不同的值的个数，称之为“基数”（cardinality）。这个基数越大，索引的区分度越好，扫描行数越低，例：在性别上建索引区分度低，只有男和女，在手机号上建索引区分度高。

优化器选择索引的时候会优先考虑选择基数大的索引，不过基数是优化器的考虑因素之一，其他的还有排序、是否使用临时表等。建索引的时候尽量建基数大的，还可以通过索引的使用情况来分析和优化现有的索引，比如索引的使用率和每次扫描行数。

MySQL使用的是采样统计的方法，InnoDB默认会选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过1/M的时候，会自动触发重新做一次索引统计。参数innodb_stats_persistent设置。也可以通过analyze table t 来手动触发重新统计。

 

5.索引失效的情况

对索引值做计算

select name from t where func(k) = 1; --索引字段做函数操作
select name from t where k+1 = 1;     --索引字段做计算

隐式强转

select name from t where k = '10';--k是int类型

索引列全部为null，这种情况生产种遇到过，生产加了新字段后，查询不走索引，后来发现索引一列全部为null。索引列有值后查询正常。