mysql索引相关

1) InnoDB的的二级索引的叶子节点存放的是KEY字段加主键值。因此，通过二级索引查询首先查到是主键值，然后InnoDB再根据查到的主键值通过主键索引找到相应的数据块。
2) MyISAM的二级索引叶子节点存放的还是列值与行号的组合，叶子节点中保存的是数据的物理地址。
所以可以看出MYISAM的主键索引和二级索引没有任何区别，主键索引仅仅只是一个叫做PRIMARY的唯一、非空的索引，且MYISAM引擎中可以不设主键

二.覆盖索引：

2.1.0 概念

覆盖索引：一查询使用了索引，并且需要返回的列 在该索引中已经全部能找到，不需要回表等操作。

索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。

覆盖索引是非聚簇索引的一种形式，它包括在查询里的SELECT、JOIN和WHERE子句用到的所有列（即建索引的字段正好是覆盖查询条件中所涉及的字段）。简单说就是，索引列+主键包含 SELECT 到 FROM之间查询的列。

2.1.1 覆盖索引情况下，“不等于”索引生效

没覆盖索引情况下，“不等于”索引失效：

没覆盖索引的情况下，使用“不等于”导致索引失效。因为如果使用索引，则需要依次遍历非聚簇索引B+树里所有叶节点，时间复杂度O(n)，找到记录后还要回表，加在一起效率不如全表扫描，所以查询优化器就选择全表扫描了。

CREATE INDEX index_age_name ON user(phone, nickname);

a)查所有字段，并且使用“不等于”，索引失效

EXPLAIN SELECT * FROM `user` where age<>21;

b)覆盖索引情况下，“不等于”索引生效：

覆盖索引，查的两个字段被联合索引给覆盖了，性能更高。虽然还是需要依次遍历非聚簇索引B+树里所有叶节点，时间复杂度O(n)，但是不需要回表了，整体效率比不用索引更高，查询优化器就又使用索引了。

CREATE INDEX user_index ON user(phone, nickname);

EXPLAIN SELECT id,nickname,age FROM `user` where age<>21;

2.1.2 覆盖索引情况下，左模糊查询索引生效

没覆盖索引的情况下，左模糊查询导致索引失效

CREATE INDEX user_index ON user(phone, nickname);

EXPLAIN SELECT * FROM `user` where nickname like '%哈';

2.1.3覆盖索引情况下，左模糊查询索引生效

主要原因也是因为走非聚簇索引B+树遍历叶节点，不回表，效率会比全表扫描时高，查询优化器选择效率高的方案。

CREATE INDEX user_index ON user(phone, nickname);

EXPLAIN SELECT id,phone,nickname FROM `user` where nickname like '%哈';

2.1.4查询除索引字段外其他字段，索引失效

CREATE INDEX user_index ON user(phone, nickname);

EXPLAIN SELECT id,age,nickname,phone FROM `user` where nickname like '%哈';

2.2 覆盖索引的利弊

好处：

1.避免回表（Innodb表进行索引的二次查询）

Innodb是以聚集索引的顺序来存储的，对于lnnodb来说，二级索引在叶子节点中所保存的是行的主键信息，如果是用二级索引查询数据，在查找到相应的键值后，还需通过主键进行二次查询才能获取我们真实所需要的数据。

在覆盖索引中，二级索引的键值中可以获取所要的数据，避免了对主键的二次查询，减少了IO操作，提升了查询效率。

2.可以把随机IO变成顺序IO加快查询效率

由于覆盖索引是按键值的顺序存储的，对于IO密集型的范围查找来说，对比随机从磁盘读取每一行的数据I0要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的IO 转变成索引查找的顺序IO。

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

弊端:

具体问题要具体分析：

索引字段的维护总是有代价的。因此，在建立几余索引来支持覆盖索引时就需要权衡考虑了。这是业务DBA，或者称为业务数据架构师的工作。

三．前缀索引

3.1 案例

有一张教师表，表定义如下：

create table teacher(

ID bigint unsigned primary key,

email varchar(64),

...

)engine=innodb;

讲师要使用邮箱登录，所以业务代码中一定会出现类似于这样的语句：

mysql> select col1, col2 from teacher where email='xxx';

如果email这个字段上没有索引，那么这个语句就只能做全表扫描。

MySQL是支持前缀索引的。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。

mysql> alter table teacher add index index1(email);

#或

mysql> alter table teacher add index index2(email(6))

这两种不同的定义在数据结构和存储上有什么区别呢？下图就是这两个索引的示意图：

如果使用的是index1（索引包含整个字符串），执行顺序是这样的：

从index1索引树找到满足索引值是’ zhangssxyz@xxx.com’的这条记录，取得ID2的值；

回表到主键上查到主键值是ID2的行，判断email的值是正确的，将这行记录加入结果集；

取index1索引树上刚刚查到的位置的下一条记录，发现已经不满足email=' zhangssxyz@xxx.com ’的条件了，循环结束。

这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。

如果使用的是index2（索引包含字符串前缀email(6)），执行顺序是这样的：

从index2索引树找到满足索引值是’zhangs’的记录，找到的第一个是ID1；

回表到主键上查到主键值是ID1的行，判断出email的值不是’ zhangssxyz@xxx.com ’，这行记录丢弃；

取index2上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出ID2，再到回表到ID索引上取整行然后判断，这次值对了，将这行记录加入结果集；

重复上一步，直到在index2上取到的值不是’zhangs’时，循环结束。

也就是说使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。前面已经讲过区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。

3.2 前缀索引不能用覆盖索引

因为非聚簇索引树查到的数据是前缀和id，前缀不是完整数据，必须要回表到聚簇索引树。

所以使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素。

四.索引下推

4.1.0概念

MySQL的索引下推（Index Condition Pushdown, ICP）是一种优化查询的方式，它可以改善查询性能，特别是对于包含多个条件的查询。在没有索引下推的情况下，MySQL会先从索引中找到满足某一条件的行的指针，然后根据这些指针去表中检索完整的行，最后再应用其他的过滤条件来确定哪些行实际符合查询的要求。

引入索引下推后，MySQL能够在使用索引检索数据时就应用所有相关的过滤条件，从而减少需要从存储引擎中检索的数据行数。这意味着如果一个查询可以部分或完全通过索引来判断数据行是否符合条件，MySQL就会在索引层面就过滤掉那些不符合条件的行，避免了对它们的进一步检查。这样做的结果是减少了IO操作和提高了查询的效率。

举例：

例如，考虑一个简单的表employees，包含字段id（主键），name，和department_id，并且department_id上有一个索引。如果我们执行一个查询来找出特定部门中名字以某个字母开头的员工，如：

SELECT * FROM employees WHERE department_id = 10 AND name LIKE 'A%';

如果没有索引下推，MySQL会首先使用department_id索引找到所有属于部门10的员工，然后逐个检查这些员工的名字是否以’A’开头。如果使用了索引下推，MySQL会在使用department_id索引的同时，检查名字是否以’A’开头，这样就能直接过滤掉更多的不符合条件的行，减少了需要进一步检查的数据量。

简而言之，索引下推让MySQL在尽可能早的阶段就过滤掉不符合条件的数据，从而提升查询性能。

不支持索引下推的联合索引：例如索引(name,age)，查询name like 'z%' and age=？，模糊查询导致age无序。在联合索引树查询时只会查name，后面的age乱序不能直接进行条件判断，必须回表后再判断age。

而支持索引下推的联合索引：例如索引(name,age)，查询name like 'z%' and age and address，在联合索引树查询时不止查name，还会判断后面的age，过滤后再回表判断address。

CREATE INDEX idx_name_age ON student(name,age);

#索引失败；非覆盖索引时，左模糊导致索引失效

EXPLAIN SELECT * FROM student WHERE name like '%bc%' AND age=30;

#索引成功；MySQL5.6引入索引下推，where后面的name和age都在联合索引里，可以又过滤又索引，不用回表，索引生效

EXPLAIN SELECT * FROM student WHERE `name` like 'bc%' AND age=30;

#索引成功；name走索引，age用到索引下推过滤，classid不在联合索引里，需要回表。

EXPLAIN SELECT * FROM student WHERE `name` like 'bc%' AND age=30 AND classid=2;