mysql 进阶（二）

优化案例

 

 1.单表优化

索引一旦进行升级优化，一定要将不用的索引删掉，防止干扰

优化过程

索引优化是一个逐步优化的过程

小结：a.保持最佳左前缀原则，保持索引的定义和使用的顺序一致性，b.索引需要逐步优化，c.将含in的范围查询放到where条件的最后防止失效

 

2双表优化

当多表连个查询的时候给那个表那个字段加索引

　　分析小表大表双层循环的类型

以上连个for循环，最终都会循环3000次；但是对于双层循环来说，一般建议  将数据小的循环放外层，数据大的 放内层

 

两表做链接查询： on条件前后  按照上边的方法 where  语句之后的索引就跟以前的加索引方式一样

7 避免索引失效的原则

（1).复合索引

　　a.复合索引，不要跨列或这无序使用（最左前缀原则）

　　b.复合索引，最好使用全索引匹配

 (2).不要在索引上进行任何操作（计算，函数，类型转换），否则索引失效

(3).复合索引不能使用不等于（ !=  <>  ）或 is null (is not null),否则自身以及右侧索引全部失效

但是 有些特殊情况 复合索引中使用 > 或者< 等符号 索引有的时候没有失效，因为mysql服务层中有SQL优化器，可能回影响我们的优化　

 补救：尽量使用索引覆盖（extar : using index）回提高系统性能

8.一些其他的优化方法

exist/in

select .... from table where exist/in

如果主查询数据集大 用in 

如果子查询数据集大 用exist

exist 语法 in和exist的区别：

 2.order by 优化

using filesort 有两种算法：双路排序，单路排序（根据IO次数）

MySql4.1之前 默认使用 双路排序； 双路：扫描两次磁盘（1.从磁盘中读取排序字段，对排序字段进行排序【在 buffer中进行的排序】，2.扫描其他字段）

MySql4.1之后 默认使用 单路排序； 单路：只读取一次（全部字段），在buffer中进行排序。但单路排序会有一定隐患（不一定真的是单路“一次IO”,有可能是多次io）原因：如果数据量特别大，则无法将所有字段的数据一次性读取完毕，因此会进行一次“分片读取”　　

　　注意：单路排序比双路排序会占用更多的buffer

　　单路排序时：如果数据量太大，可以选择调大buffer的容量大小：set max_length_for_sort_data=1024 单位时字节

 

如果 max_length_for_sort_data 值太低，则 mysql 会自动从 单路 切换到  双路（太低：需要排序的列的总大小超过了max_length_for_sort_data定义的字节数）

提高order by 的查询的策略：

　　a.选择使用单路，双路；调整buffer的容量大小。

　　b. 避免 select *

　　c.保证所有的排序字段 排序的一致性（都是生序或者降序）

4.尽量使用覆盖索引