mysql核心知识整理

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

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

二、覆盖索引

覆盖索引：联合索引中，查询的字段可以全部在辅助索引中找到，不需要回表时，就叫覆盖索引。
例如：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 子查询会生成临时表，临时表不存在索引，且临时表会占用内存