Mysql学习笔记-优化策略

单表优化

• 最佳左前缀：保持和复合索引定义顺序一致性。
• 将含in的范围查询放到where最后，防止如果使用了复合索引，造成复合索引失效。

多表优化

• 小表驱动大表
• 将数据量小的表列放and左边

避免使用索引时效的原则

1. 复合索引，不要跨列使用或无需使用，复合索引左侧使用>也会造成复合索引时效。
2. 对于复合索引，尽量使用全索引匹配
3. 不要在索引上进行任何操作（计算、函数、类型转换），否则索引会失效
4. in可能会造成索引时效（对于in中条件多的情况，Mysql会判断走索引快还是不走索引快）
5. 不能使用不等于，is null,is not null,or
6. like 不要以%开头，否则索引失效,如果必须使用%开头搜索，尽量使用覆盖索引(using index)，即只查询索引字段。

SQL优化是一种概率层面的优化，SQL优化器Optimizer会干扰我们的SQL，至于是否实际使用了我们的优化，需要使用explain查询。

exist和in的使用

• 如果主查询数据集大则使用In，否则使用exist

order by优化

• using filesort有两种算法：双路排序（Mysql4.1之前默认）和单路排序（Mysql4.1后默认）
• 双路排序：扫描2次磁盘（1：从磁盘读取排序字段，对排序字段在buffer中进行排序，再从磁盘中读取其他字段）
• 单路排序：只读一次（全部字段），在buffer中进行排序。如果数据量很大的情况会进行分片排序造成多次IO。
• 单路排序会比双路排序使用更多的buffer，可以通过设置max_length_for_sort_data调整buffer容量。
• 如果需要排序的列总大小超过max_length_for_sort_data，则Mysql会自动从单路排序调整为双路排序。
• 最好保证排序字段排序的一致性（都是升序或降序）