聚簇索引与非聚簇索引 覆盖索引 索引下推 最左原则 索引结构 b树、b+树、跳表 回表 日志binlog、redolog、undolog mvcc 锁 查询优化

一、聚簇索引与非聚簇索引

聚簇索引：找到了索引就找到了需要的数据，那么这个索引就是聚簇索引，所以主键就是聚簇索引，修改聚簇索引其实就是修改主键。

非聚簇索引：索引的存储和数据的存储是分离的，也就是说找到了索引但没找到数据，需要根据索引上的值(主键)再次回表查询,非聚簇索引也叫做辅助索引。

二、覆盖索引

覆盖索引：联合索引中，查询的字段可以全部在辅助索引中找到，不需要回表时，就叫覆盖索引。

例如：unikey(a,b,c)，select a,b,c from table where a like “hello%” 这种就直接走辅助索引树遍历。select a,b,c,d from table where a like "hello%"这种就会出现回表查询d字段的情况，就不叫覆盖索引。

三、索引下推

索引下推：联合索引中，击中了辅助索引的前面字段，后面的字段会在存储引擎层就过滤出来。

例如：select * from people where country like ‘中%’ and name = ‘张三’ 联合索引unikey(country,name)，这个sql会用到辅助索引unikey的country字段，在5.6以前没有索引下推，存储引擎会将country like '中%'的数据全部返回给server层由server层来过滤剩下的条件name = ‘张三’，把不符合条件的过滤掉返回给client端，但是在5.6以后有了索引下推icp，这时候会直接在server层就对name = '张三’的数据进行过滤，返回给server端，这就是索引下推。相当于把server层的判断交给存储引擎层。

四、最左原则

最左原则：联合索引中，查询的条件按左到右的顺序击中。

例如：unikey(a,b,c)，select * from table where a = “hello” and b like “a%” 这种就是满足了最左原则，mysql执行器会选用辅助索引。select * from talbe where b = “word” 这种就没有满足最左原则，mysql会直接执行全表扫描。

五、索引结构 b树、b+树、跳表

b树：数据存在每个节点上；mongdb

b+树：数据只是存在叶子节点上；mysql

跳表：单向有序链表+多级索引。redis

六、回表

回表：是指查询的条件击中了辅助索引，但是查询的字段除了辅助索引的字段外，还需要到主键索引树中查找的情况就叫做回表。

七、binlog、redolog、undolog

binlog：记录的是每次修改记录的是数据写入操作。可以简单的理解为记录的每次的insert、update，delete，alter语句。【逻辑日志】binlog日志介绍

redolog：记录事务对数据页做了哪些修改。【物理日志】redolog日志介绍

undolog：记录的是每次修改操作的反向sql语句。【逻辑日志】undolog日志介绍

八、mvcc

mvcc 多版本并发控制，是为了解决并发读在不加锁的情况下，提高读取效率而产生的一种手段。【mysql mvcc介绍】

九、锁

我们这里说的锁基于innodb

1. 表级别锁；

   1.1 表级别读锁s；

   1.2 表级别写锁x；

   1.3 表级别auto-inc锁；

   1.4 意向共享锁IS；

   1.5 意向独占锁IX；

2. 行级别锁。

   2.1 记录锁 Record Locks；

   2.2 间隙锁 Gap Locks；

   2.3 临键锁 Next-Key Locks；

   2.4 隐式锁。

十、查询优化

1. 不要使用select *；

2. 减少连接查询；

3. 查询条件加索引；

4. 对于深分页，使用子查询减少回表次数；

   select * from k_order.order where code > 2000 order by code desc limit 1000000,10

   优化后 ---->

   》select * from k_order.order where id in (select id from k_order.order where code >2000 limit 1000000,10) （版本不支持就用inner join 如下）

   》select * from k_order.order a inner join (select id from k_order.order where code >2000 order by code desc limit 1000000,10)b on a.id = b.id

5. 最左原则，例如，模糊查询like ‘a%’；联合索引 a=3 and b =4；

6. in 代替or；

   6.1.in或or在字段有添加索引的情况下，查询很快，两者查询速度没有什么区别；

   6.2.in或or在字段没有添加索引的情况下,所连接的字段越多(1or2or3or4or…)，or比in的查询效率低很多

7. join 代替in子查询。

   in 子查询会生成临时表，临时表不存在索引，且临时表会占用内存