面试的时候经常被问到mysql优化方面的问题，在这里也是整理分享一下。
Mysql优化主要有四个方面：
设计：存储引擎，字段类型，范式与逆范式
功能：索引，缓存，分区分表
架构：主从复制，读写分离，负载均衡
最后需要结合自己的经验，后期的测试来优化sql语句
存储引擎
Innodb ：数据完整性，并发性处理，擅长更新，删除。
myisam：高速查询及插入。擅长插入和查询。
字段类型
字段类型应该要满足需求，尽量要满足以下需求。
尽可能小（占用存储空间少）、尽可能定长（占用存储空间固定）、尽可能使用整数。
范式与逆范式
范式
为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式。
第一范式，具有原子性，确保每列保持原子性。
第二范式，主键列与非主键列遵循完全函数依赖关系，确保表中的每列都和主键相关。
第三范式，非主键列之间没有传递函数依赖关系索引，确保每列都和主键列直接相关,而不是间接相关。
逆范式
逆范式是指打破范式，通过增加冗余或重复的数据来提高数据库的性能。
示例： 假如有一个商品表Goods：
字段有Goods_id(商品表), goods_name(商品名称), cat_id(所属类别的id)。
还有一个分类表Category：
字段有Cat_id(类别id), cat_name(类别名称)。
现在要查询类别id为3的商品的数量，例如分类列表查询：
分类ID 分类名称 商品数量
3 计算机 567
可以使用下列sql语句：
Select c.*, count(g.goods_id) as goods_count from category as c left join goods as g c.cat_id=g.cat_id group by c.cat_id;
但是，假如商品数量较大，那么就比较耗性能了。这时，我们可以考虑重新设计Category表：增加存当前分类下商品数量的字段。
Cat_id, cat_name, goods_count
每当商品改动时，修改对应分类的数量信息。
再查询分类列表时：Select * from category;
此时额外的消耗，出现在维护该字段的正确性上，保证商品的任何更新都正确的处理该数量才可以。
索引
利用关键字，就是记录的部分数据（某个字段，某些字段，某个字段的一部分），建立与记录位置的对应关系，就是索引。索引的关键字一定是排序的。索引本质上是表字段的有序子集，它是提高查询速度最有效的方法。一个没有建立任何索引的表，就相当于一本没有目录的书，在每次查询时就会进行全表扫描，这样会导致查询效率极低、速度也极慢。如果建立索引，那么就好比一本添加的目录，通过目录的指引，迅速翻阅到指定的章节，提升的查询性能，节约了查询资源。
Sql优化
1.对于并发性的SQL
少用（不用）多表操作（子查询，联合查询），而是将复杂的SQL拆分多次执行。如果查询很原子（很小），会增加查询缓存的利用率。
2.大量数据的插入
多条 insert或者Load data into table（从文件里载入数据到表里）
建议，先关闭约束及索引，完成数据插入，再重新生成索引及约束。
针对于myisam，步骤：
Alter table table_name disable keys; 禁用索引约束
大量的插入
Alter table table_name enable keys; 启用
针对innodb，步骤：
Drop index, drop constraint 删除索引及约束，要保留主键
Begin transaction|set autocommit=0; 开启事务，不让他自动提交
[数据本身已经按照主键值排序]
大量的插入
Commit;
Add index, add constraint
3.分页
分页假定Limit offset, size; size = 10;
Limit 的使用，会大大提升无效数据的检索（被跳过），因为是先检索，检索会检索全部，再取得想要的。好的做法是使用条件等过滤方式，将检索到的数据尽可能精确定位到需要的数据上。
4.随机选一些数据，不要使用Order by Rand()
上面的查询，会导致每条记录都执行rand()，成本很高！
建议，通过mt_rand()，先确定的随机主键，再从数据表中获取数据。
网友评论