MySQL服务端逻辑架构与执行流程详解-优快云博客

字段有唯一性的，加索引找到之后就不用继续再往后找了，因为是唯一的。
频繁作为where查询条件的字段
经常group by和order by的字段，如果都需要，那么加联合索引效果最佳。
用到distinct去重操作的字段，创建索引之后按一定规则排序了，所以查询到的结果都是挨着的，去重的时候就很快了
连表查询时用于连表的字段。
字段类型所占空间小的比较适合创建索引，因为创建索引也是需要占空间的。
以很长的字符串作为索引的时候最好建立前缀索引（inde（address（12））取前12位作为索引）。根据字符串的区分度来决定取前多少位（怎样让索引又尽可能的短又能尽可能区分不同的字符串）。
区分度高的适合创建索引，如性别就不适合创建。

不适合创建索引的情况

频繁更新的字段，因为更新要连着索引一起更新。
无序的字段不适合，如uuid。索引的叶子结点数据是按顺序排列的，此时插入一个uuid可能插到中间到时页分裂。自增的字段作为索引好一点。

慢查询与慢查询日志

可以设置执行超过多少秒的sql语句是慢查询语句，然后记录到慢查询日志当中，使用mysqldumpslow工具查看日志。

EXPLAIN查看执行计划

使用方法:在要查看的语句前面加上EXPLAIN，这条语句实际上并没有执行，只是查看执行计划。

table：每一行代表一张表，如果使用两个表连表查询那么explain的结果就有两行。

Id：一个查询语句中select的id，id大的先执行，如果如果查询中有一个子查询，那么就会一个id是1一个是2。特殊地查询优化器可能会将子查询优化为连表查询，这时候id就只有一个1了。

Key_len：索引的字节数，越长说明索引使用得越充分

Type：sql语句效率级别

索引的失效

Where 后面的条件有运算。如where studentId + 1 = 123；这样的话它会把先把结果全部+1再去判断是否匹配，如果等号左边用了函数也是一样的道理。
类型转换：where name = 123；用不上索引，应该给123加上引号，因为本身name是字符串。
联合索引的最左连缀原则，特别的where a = 1 and b > 1 and c = 2;这样索引c会失效，因为b用了范围判断。解决方法是在创建索引的时候把c写在b前边。
!= 索引可能失效。
Is null 不失效，is not null失效
Like “%asd”失效，like “asd%”不失效。
or两边的条件必须都有索引，不然还不如直接全表扫描快。

表连接查询

a left join b；大多情况下a是驱动表，b是被驱动表。而内连接时a join b；驱动表与被驱动表的身份是由innodb决定的。有索引的表作为被驱动表效率更高。

索引条件一致的情况下，a left join b；谁作为驱动表是根据结果集大小来判断的，小表驱动大表。

在没有索引的情况下，会将驱动表的数据存入join buffer分批次地和被驱动表作比较。其他条件相等的情况下要查询的字段少的表所占内存小能存入更多条数据到buffer，所以更适合作驱动表。

子查询的优化

子查询尽量使用联合查询来代替，不要使用not in、not exist等字段。

order by优化

order by多个字段的时候必须都是升序或者都是降序否者索引失效。

各种优化都是基于查询成本来的，innodb的优化器很智能。

索引下推

在联合索引中，索引中有这个字段，但是因为某些原因这个字段的索引失效没有用上，却可以用这个字段在索引中进行条件过滤。

如where a > a and b like “%张%”;存在ab联合索引。

%张% 按理说会使索引失效，但是可以从a > a的结果中过滤掉不姓张的，而不是先a > a之后回表查出结果再过滤找出姓张的，减少了回表查询的量。

范式

第一范式

字段不可再拆分，如不宜设一个字段叫“收货地址与电话”，因为这两个属性可能在不同场景需要单独调用。

第二范式

要有主键，且查询非主键的值要完全依赖主键（如主键是一个联合主键（a,b）那么必须告诉我a和b的值我才能确定一条数据）。

第三范式

每列字段都和主键直接相关。如订单表中有字段商品id和商品名称，这就违背了第三范式了，因为商品名称是依赖于商品id的商品id又依赖于订单id，应该将商品名称去除放到商品表当中。

反范式化

根据实际场景违背某些范式以空间换时间提高效率，要记得添加了冗余的字段后更新数据一次要更新所有冗余的字段。如第三范式那个例子，如果经常需要根据订单id查询商品的名称，那么可以违背第三范式设计一个冗余的商品名称的字段。

锁

共享锁与排他锁

只有两个共享锁之间能兼容直接访问，其他都会锁住。

读操作一般加共享锁，写操作update、delete、insert一般加排他锁，因为insert操作之前还没有这条数据都不知道锁哪条记录所以它会加一个隐式锁。

表级的共享锁与排他锁

innodb相较于myisml多了行级锁，一般不使用表级锁。

意向锁

事务A对表a上的一行加了行级排他锁，这时事务B想对表a加表级排他锁，此时应该是要失败的，因为如果事务B加成功了就等于可以操作表a上的任意行数据，事务A的锁就白加了。所以事务B要一行一行去判断表a上是否有行级锁，很麻烦，于是意向锁就诞生了，事务A在加行级锁的时候就会给表加上一个意向锁，然后事务B在加表级锁的时候就会发现已经有意向锁了于是等待，但此时事务B可以加行级锁，两个事务各锁各的行。