小菜鸟

排序优化

对于order by关键字进行优化前，首先大家要先知道索引不仅用于检索还用于排序

MySQL支持两种方式的排序，index和filesort，index效率高，可以根据索引本身来完成排序，filesort效率较低

最好在进行explain进行分析时不要出现filesort

使用index

通过有序索引而直接取得有序的数据，这样就可以不需要进行任何排序操作即可满足客户端要求的有序数据

• order by 语句使用索引最左前列
• 使用where 子句和order by子句组合满足索引最左前列

如果使用多字段排序，需要保证排序方向一致，要么就全是ASC要么就全是DESC

in和exists的取舍

之前说过要小表驱动大表，即先遍历小表再遍历大表，接下来看一下in和exists的区别

in

先执行子查询，适合于外表大而内表小的情况

1
2
3
4
5

select * from A where id in (select id from B)

等价于==
先遍历表B select id from B
再遍历表A select * from A where A.id = B.id

MySQL索引

索引就是用来帮助mysql快速查找数据的数据结构，这种数据结构以某种方式指向数据，以此种数据结构的基础上实现高级的查找算法，就可以快速的查找数据

MySQL默认使用的是B-Tree索引(结构为B+树)

执行计划

如果不知道执行计划，那就不可能进行SQL优化，那么执行计划是什么呢？

使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL的，进而分析性能瓶颈

用起来其实很简单，使用explain + sql语句即可，也可以与show WARNINGS来进行组合

1
2
3
4
5

explain select * from user;

-- show WARNINGS 可以解释为什么索引没有使用
explain select * from user;
show WARNINGS;

MySQL锁机制

锁机制是数据库为了保证数据的一致性而使各种共享资源在被并发访问变得有序所设计的一种规则，锁的作用主要是管理共享资源的并发访问，事务在修改数据之前，需要先获得相应的锁；获得锁之后，事务便可以修改数据；该事务操作期间，这部分数据是锁定的，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁

由于Mysql中存在多种存储引擎，而每种存储引擎所应对的场景不同，所以各存储引擎的锁机制也有较大区别，总的来说MySQL中的锁按照类型上分为共享锁(读锁)、独占锁(写锁)、悲观锁(for update)、乐观锁(使用version，来进行CAS操作)；按照锁的粒度分为表锁、行锁和页锁

按照算法可以分为Record Lock(单行记录)、Gap Lock(间隙锁，锁定一个范围，不包括锁定记录，间隙锁主要用于范围查询的时候，锁住查询的范围)、Next-Key Lock(Record Lock+Gap Lock，锁定一个范围，包括记录本身)

按照锁类型

表锁(偏读)

表锁是MySQL各存储引擎中最大粒度的锁定机制，实现逻辑简单，带来的系统负面影响最小，所以获取锁和释放锁的速度很快，且由于表锁一次会将整个表锁定，所以可以很好的避免死锁，但是由于锁粒度大导致锁定资源争用的概率最高，致使并发度很低

总结下来就是：表锁开销小，加锁快；无死锁；锁的粒度大，并发小