手动总结mysql优化 应届面试篇

写在开头

建表

首先mysql的优化应该从建表就开始考虑，应考虑单行数据量不要过大，同时如果数据是定长应选用char 变长选用varchar，尽量避免使用null值可以设定默认值，以及如果字段确实有唯一性可以加唯一约束索引也应为唯一索引。

索引

对应上面建表说给出的建议。首先单行数据不宜过大 因为mysql采取分页存储、索引是使用B+树，每一叶大小为16kb即B+树一个节点是16kb，假设我们每一行数据1kb 采用我自增id bigint类型8字节，就以主键索引为例，那么我们的B+数 算上bigint类型的8kb 以及 指针6kb 一个结点能分出一千多个子节点 那么我们的三层B+树就有100w个子节点 一个子节点能存储十多行数据，那么一个三层b+树就能达到千万行。

同时在我们的索引建立上，尽量选择区分度高的字段 最好是唯一索引，避免null值（因为null值会引发索引失效 （这个什么时候会引发失效什么时候不失效我也搞不通，大佬可以教教我），如果要是使用not null就100%gg了）。索引字段尽可能考虑业务需求 建立覆盖索引。

语句优化

尽量避免select * 因为使用select * mysql执行器自己还要分析一遍都有哪些字段，对资源浪费（程序员没有mysql值钱 dog）

关联查询 大表驱动小表，其次阿里巴巴开发手册云：join 禁止三张表以上，mysql资源相比java来说 极其珍贵 过多的关联查询还是交给java老说比较好。

limit语句 会将起始点以前全部的行都查一遍，比如limtit1000，10其实是查了1010行数据，在查寻行数过多时会引发索引失效，所以尽量要建立一个覆盖索引，再通过子查询或join的方式优化。

gourp by order by也是如此尽量使用覆盖索引，分组查询使用覆盖索引可以通过B+树特性直接返回答案，order by也可以避免uesing filesort的问题。

count 尽量使用count列 （count1和*都是算null值的 count 列不算）5.7版本以后， count列的效率是比较高的。 ps：innodb之所以不再保存表行数是因为mvcc机制引入 行数不断变化的情况保存行数变得没有意义了。

尽量避免数据类型的隐式转换，会引发索引失效

in 也少用 阿里巴巴手册又建议：1000以上不要用in 问题也是会可能索引失效。

分库分表

阿里巴巴手册建议 行数大于500w 或2G考虑分库分表，分表又分为垂直分 和水平分。

水平分：将区分度不高的分表 比如性别 年级。

垂直分：把不常用的分出去。