MySQL的查询优化(二)

“ 在昨天的MySQL的查询优化(一)中，我们谈到SQL常用的一些优化方式：给字段增加索引，避免索引失效，替换掉一些不合理的关键词，那么今天我们继续来看SQL如何进行查询优化”

在上一章第一条优化中我们说到在where条件后的字段上增加索引来提高查询效率，在使用where的同时我想小伙伴们order by用的也不少吧。如果order by用的多了我想ground by你也会用到。但是你用的方式对吗？

语句分析

一.order by

对于order by大家都不陌生，但是为什么使用order by之后查询会变慢呢？我们先来看下一条语句

这条语句大家应该比较熟悉，在昨天的文章中，我们写了相似的语句，只不过我们没有加ordery by status desc 。你有没有发现加完之后Extra字段下面出现了Using index condition; Using filesort。Using index condition是什么意思？ 在MySQL5.6以后，Extra出现这句话表明，这条SQL语句优先去过滤索引，过滤完索引后找到所有符合索引条件的数据行。如果WHERE 还存在子句，再随后去过滤这些子句数据行。Using filesort意思是是用来排序，也就是order by语句造成了，出现Using filesort的语句通常会比较慢，这是因为排序的本质是通过一定的算法(耗费cpu 运算,内存,临时文件IO)将结果集变成有序的结果集。对于这类语句，我们应该怎么优化呢？首先我们仍然是要加索引，但是加了索引就会生效吗？

第一种，sort加了索引，但是你觉得索引生效了吗？

explain select * from `xcx_know_tree`  order by sort desc

很遗憾，这条语句仍然使用了Using filesort，也就是说索引并没有生效。

我们上一篇文章中说到过尽量不要使用 select * 。那么我们来改进一下看看。

explain select sort from `xcx_know_tree`  order by sort desc

有效果！这里type变成来index,Extra成了Using index。Using index表示直接访问索引就足够获取到所需要的数据，不需要通过索引回表。也就是说他不用再去排序，效率自然而然要比上一种要高。但是我们须知，select 字段必须在索引中才会有效（不一定是order by后面的字段索引，其他索引也是可行的）。

第二种：如果我们要使用select * 或者 select的字段不包含在索引中，我们要怎么办呢？这个时候，我们需要添加where 条件，但是where 条件后面的字段也需要包含在索引中

explain select * from `xcx_know_tree` where s sort=  1  order by sort desc

我们还要注意一点就是where条件后面不能使用or去连接条件，不然同样会进行排序

下面是我在网上找到一个order by字段索引失效的情况。

SELECT id,comment from form_entity order by name(给name建立索引,comment没有索引) SELECT * from form_entity order by name(给name设置索引) SELECT * from form_entity order by name desc,comment desc(name,comment建立复合索引) SELECT * from form_entity where comment =’123’ order by name desc(name,comment建立复合索引) SELECT name from form_entity where category_id =’123’ order by name desc,comment desc(name,comment建立复合索引,category_id独立索引) 当查询条件使用了与order by不同的索引，但排序字段是另一个联合索引的非连续部分 SELECT comment from form_entity group by name order by name ,comment(name,comment建立复合索引) 返回数据量过大也会不使用索引 排序非驱动表不会走索引 order by 字段使用了表达式

二.group by

使用了order by,我想group by大家也时常会用到。

来看一下：这里我想计算一下，状态为0和为1的数据量有多少。怎么实现呢？

select status,count(name) as num from `xcx_know_tree`  group by status  

效果：

我们再来看一下这条语句的执行计划：

explain select status,count(name) as num from `xcx_know_tree`  group by status 

Extra除了我们上面说的Using filesort还多来Using temporary。Using temporary表明这条语句使用了临时表。使用临时表会消耗更多的内存，降低查询的效率。所以出现这类语句我们也需要调优。

产生临时表的原因，一般有下面几种情况：

1.如果GROUP BY 的列没有索引,产生临时表.

2.如果GROUP BY的列有索引,ORDER BY的列没索引.产生临时表.

3.如果GROUP BY或ORDER BY的列不是来自JOIN语句第一个表.会产生临时表.

根据第一种情况我们把Group by的列增加索引来看一下：

explain select sort,count(name) as num from `xcx_know_tree` 
 group by sort

果然没有再去使用临时表了。所以Group by的字段也需要加索引。

第二种情况

第三种情况

如果你语句产生来临时表，就可以往以上几种情况靠拢，然后进行优化。

三.优化数据库结构

由于个人方向的问题，对于SQL语句的查询优化，自己并不是很精通，只能说遇到加载很慢的时候，我会去排除原因，如果原因出在SQL的问题上面的时候（大多数我觉得都是这上面），我回去看这个请求执行了哪些SQL，如果开启了慢查询就去看慢查询日志，如果没有，把打印的SQL放到工具上执行一下，然后使用explain去看一下SQL的执行计划，最后再进行优化，当然最后的优化才是最重要的。

下面我再说一下另外一中优化方式：优化数据结构

一个好的数据库设计方案对于数据库的性能常常起到事半功倍的效果。

数据库结构的设计需要考虑数据冗余、查询和更新速度、字段的数据类型是否合理等多方面

（1）将字段很多的表拆分成多个表

有时候有些字段使用频率很低或者字段的数据类型比较大，那么可以考虑垂直拆分的方法，把不常用的字段和大字段拆分出去

（2）增加中间表

对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，

然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。

（3）增加冗余字段

设计数据库表时应尽量遵循范式理论，尽可能减少冗余字段，但是现今存储硬件越来越便宜，有时候查询数据的时候需要join多个表

这样在高峰期间会影响查询的效率，我们需要反范式而为之，增加一些必要的冗余字段，以空间换时间

需要这样做会增加开发的工作量和维护量，但是如果能换来可观的性能提升，这样做也是值得的.

今天小程序更新的题库：

1.触发器的作用？

2.什么是存储过程？用什么来调用？

3.存储过程的优缺点？

4.什么叫视图？游标是什么？

5.如何维护数据库的完整性和一致性？