从mysql底层学习mysql索引

需要了解的数据结构

0、树

1、B树

2、B+树

 

innodb引擎使用b+树的数据结构（多路平衡树）在硬盘上存储数据

每一张表一定会有一个主键。按照主键id生成b+树数据结构，在叶子节点存放表每一行所有的数据。id索引又称为聚簇索引

对表的常用查询列添加索引或者联合索引。将使用该列数据生成一棵B+树，该B+树叶子节点数据区存放的是主键id（不存放数据地址是因为数据增加或删减的时候聚簇索引树会分裂合并，将导致数据地址变更，维护其他索引树将变得困难）。

非聚簇索引叶子节点存主键id后，如果查询命中索引，返回列如果在索引中已经记载，则直接返回。如果没有记载或者*查询将进行回表操作（使用id从聚簇索引树重新查询）

 

三星索引

 

第一颗星：where后面的等值谓词，可以匹配索引列顺序

第二颗星：索引行的顺序与查询语句需求一致，也就是order by 中的排序和索引顺序是否一致

第三颗星：索引行包含查询语句中所有的列

第一颗星 ，建立索引的时候可以选择数据量小尽可能多的字段，查询的时候按照索引建立列顺序写条件

注意：使用了长数据字段将使硬盘每一页存的索引数量变少，会造成频繁的page split 和 page merge ，划不来。所以不能把所有字段都添加到一起做索引。选择数据量小的，查询条件经常用到的做索引

第二颗星，order by排序和索引顺序一致。例如 索引 index（name，sex） on table 查询后order by尽量先按照name，再按照sex排序，因为b+数已经是按照索引顺序去搜索，重排序会影响效率

第三颗星，索引里面包含了返回结果。例如 索引 index（name,sex） on table 。查询的时候返回值如果为 select name,sex from table 。。。 数据库将在查询完辅助索引后直接返回索引的值。如果查询返回列含有其他值，则索引数会在查到结果id后按照结果id重新从聚簇索引查询结果（回表），导致查询效率变慢

 

sql查询常用提高效率技巧

1、查询条件全值匹配索引最好，如果做不到，一定要按照索引顺序挨个写条件。

当条件中出现数据范围（大于，小于什么的）后面的列不再匹配索引。

当条件不是按照索引字段出现，例如索引（no，name，sex），查询条件为 no=？ and sex=？ 则索引效果不好，只命中no不会命中sex

如果查询条件不包含联合索引的第一个字段，索引直接失效。例如索引（no，name，sex），查询条件为 name=？ and sex=？

2、不等 ，空值，or 这些关键词将会导致索引失效（or有可能不失效）

3、索引列不能带函数，索引没计算的时候处于多路平衡搜索树上，计算过后的值不再是搜素树，不满足左节点一定小，右节点一定大的规则。

4、使用覆盖索引时不能用*去查询，需要用返回值在索引列的sql查询才会覆盖查询，否则回表。