mysql 索引

1 索引常见模型

hash : 适合等值查询，对范围查询无力

有序数组：对于范围查询和等值查询都可以（使用二分法），但是知识后静态存储，如果数据时常发生变化，索引重构的复杂度很高。

b+ tree , 范围查询和等值查询都可以

跳表：适用场景和 b+树类似，redis zset 就是使用的跳表

mysql 中使用的索引是hash 和b+树，innodb 默认使用b+树

2 主键索引和非主键索引

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。

聚簇索引并不是一种单独的所以类型，而是一种索引存储方法，在InnoDB， 聚簇索引值同一个结构中保存了B-tree 树索引以及数据。

InnoDB 和myIsam 区别：

myIsam主键索引使用的并不是聚簇索引 ，叶子节点存储的是对应行记录的指针，对于二级索引也是相同； 而对于InnoDB来说，主键索引的叶子节点存储的是数据，二级索引的叶子节点存储的是主键。

非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

3 索引维护：

插入数据会造成索引页分裂，删除数据会造成索引页的合并。由于B-tree树索引是按照顺序来进行组织的，所以如果主键索引不是按照顺序进行插入的，那么此现象会发生的更频繁，建议表的主键设置为自增列，避免使用UUID来作为聚簇索引，这会导致聚簇索引的插入变得完全随机，这是最坏的情况。

4 回表

创建表：

create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

然后执行

select * from T where k between 3 and 5

具体过程是怎样的的？

1. 在 k 索引树上找到 k=3 的记录，取得 ID = 300；

2. 再到 ID 索引树查到 ID=300 对应的 R3；

3. 在 k 索引树取下一个值 k=5，取得 ID=500；

4. 再回到 ID 索引树查到 ID=500 对应的 R4；

5. 在 k 索引树取下一个值 k=6，不满足条件，循环结束。

5 覆盖索引：

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

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

explain 中如果有 Using index 说明使用到了覆盖索引。

6 最左前缀规则：

我们先创建联合索引（name,age）

可以看到，索引项是按照索引定义里面出现的字段顺序排序的。

当你的逻辑需求是查到所有名字是“张三”的人时，可以快速定位到 ID4，然后向后遍历得到所有需要的结果。

如果你要查的是所有名字第一个字是“张”的人，你的 SQL 语句的条件是"where name like ‘张 %’"。这时，你也能够用上这个索引，查找到第一个符合条件的记录是 ID3，然后向后遍历，直到不满足条件为止。

可以看到，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

7 索引下推

 select * from tuser where name like '张 %' and age=10 and ismale=1;

对于上述语句mysql是怎么执行的？

在 MySQL 5.6 之前，只能从 ID3 开始一个个回表。到主键索引上找出数据行，再对比字段age 是否等于10并且是男孩

而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

对于ID3这条记录，age很明显不等于10，直接可以从索引中判断出来，就不需要进行回表了。

8 索引使用

 

• 选择合适的索引,可以通过select count(distinct field) / count(*) from table 来得出一个字段的区分度，其中count(distinct field) 也可以替换成 其他函数，比如left 等， 区分度越高，说明离散程度越高，比较适合做索引，一般大于0.03就行。如果想要使用前缀索引，可以使用以下方式来进行长度的选择

select count(distinct LEFT(column1 ,5))/count(*)  , count(distinct LEFT(column1 ,6))/count(*) from table 

• 多列索引， 对于or 条件的sql ，如果多个字段上都有索引，此时会使用索引合并的策略，即将多个or 改成union的情况。正常情况下回很有效，但是如果每个条件通过通过索引过滤后的数据很大，那么此时的效率可能还不如直接进行全表索引。
• 对于组合索引来说，尽量将区分度高的字段放在前面，判断条件可使用 

select  count(DISTINCT field1 )/count(*) , count(distinct field2)/count(*) from table

• 索引查看，可以通过 SHOW INDEX FROM table ,查看索引信息 。其中的 cardinality 字段很重要
• 索引扫描与全表扫描。如果索引不能覆盖查询所需要的全部列，那么就不得不没扫描一条记录都回表查询一次对应的行，这基本是随机IO，因此按索引顺序读取数据的速度通常比顺序的扫描全表慢。
• 索引与锁：InnoDB只有在访问的时候才会枷锁，而索引能够减少InnoDB 访问的行数，从而减少锁的数量。但这只有当InnoDB在存储引擎层能够过滤掉所有不需要的行时才有效。如果索引无法过滤掉无效的行，那么InnoDB检索到数据并返回给服务器以后才能应用where 字句。这是已经无法避免锁定行了，InnoDB锁定了这些行，到适当的时候才释放。在5.1以后的版本中，InnoDB在服务端过滤掉行后就释放锁。如果没有使用到索引，那么会锁住整张表。