MySql数据库优化篇(七) 关联查询优化、排序分组优化

一. 左外连接(left join)

1. 建表：

CREATE TABLE IF NOT EXISTS `class` (`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,`card` INT(10) UNSIGNED NOT NULL,PRIMARY KEY (`id`));CREATE TABLE IF NOT EXISTS `book` (`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `card` INT(10) UNSIGNED NOT NULL,PRIMARY KEY (`bookid`));

演示代码：

EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

演示结果图：

如何优化？在哪个表上建立索引？

ALTER TABLE `book` ADD INDEX idx_card( `card`)

演示结果图：

结论：

1、在优化关联查询时，只有在被驱动表上建立索引才有效！2、left join 时，左侧的为驱动表，右侧为被驱动表！3、右外连接也是一样的，在对面表加索引

二. 排序分组优化

where条件和on的判断这些过滤条件，作为优先优化的部门，是要被先考虑的！其次，如果有分组和排序，那么也要考虑 grouo by 和 order by。

1. 无过滤不索引：

create index idx_age_deptid_name on emp (age,deptid,name);explain select * from emp where age=40 order by deptid;explain select * from emp order by age,deptid;explain select * from emp order by age,deptid limit 10;

演示结果图：


using filesort 说明进行了文件内排序！原因在于没有 where 作为过滤条件！

结论： 无过滤，不索引。where，limt 都相当于一种过滤条件，所以才能使用上索引！

2. 顺序错，必排序：

explain select * from emp where age=45 order by deptid,name;

explain select * from emp where age=45 order by deptid,empno;

结论：empno字段并没有建立索引，因此也无法用到索引，此字段需要排序！

explain select * from emp where age=45 order by name,deptid;

结论：where 两侧列的顺序可以变换，效果相同，但是 order by 列的顺序不能随便变换！

explain select * from emp where deptid=45 order by age;

结论：deptid 作为过滤条件的字段，无法使用索引，因此排序没法用上索引

3. 方向反，必排序：

explain select * from emp where age=45 order by deptid desc, name desc ;

结论：如果可以用上索引的字段都使用正序或者逆序，实际上是没有任何影响的，无非将结果集调换顺序。

explain select * from emp where age=45 order by deptid asc, name desc ;

结论：如果排序的字段，顺序有差异，就需要将差异的部分，进行一次倒置顺序，因此还是需要手动排序的！

有什么不理解的可以看这一篇：Explain性能分析归纳总结

总结：MySql高级篇更新完毕。