Mysql优化方案和表优化方案

1. 怎么优化sql可以减少内存耗量，cpu计算量，IO吞吐、增加并发能力

一、不要使用SELECT * 
在SELECT中指定你所需要的列，会 减少内存耗费，IO吞吐。

二、统一sql的写法

其实就是大小写不同，查询分析器就认为是两句不同的SQL语句，必须进行两次解析。生成2个执行计划。所以作为程序员，应该保证相同的查询语句在任何地方都一致，多一个空格都不行！减少cpu计算量。

三、不要把sql语句写的太复杂

越简单的SQL语句被重用的可能性越高。而复杂的SQL语句只要有一个字符发生变化就必须重新解析，然后再把这一大堆垃圾塞在内存里。可想而知，数据库的效率会何等低下。减少cpu计算量。

四、采用临时表暂存中间结果

简化SQL语句的重要方法就是采用临时表暂存中间结果，但是，临时表的好处远远不止这些，将临时结果暂存在临时表，后面的查询就在tempdb中了，这可以避免程序中多次扫描主表，也大大减少了程序执行中“共享锁”阻塞“更新锁”，减少了阻塞，提高了并发性能。

五、过滤条件多的放在后面

如果第一个过滤条件过滤掉很多的数据，第二次比较就减少很多，执行循序根据数据库的执行计划而有所不同。减少了cpu的计算量。

六、使用like查询时请注意

关键词%yue%，由于yue前面用到了“%”，因此该查询必然走全表扫描，除非必要，否则不要在关键词前加%，减少cpu的计算量，提高性能

七、使用索引

索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。如果只是查询的表而不是经常修改的，建议用索引，以内存为代价，减少cpu计算量

八、只有在必须的情况下才使用begin tran

有些情况下，我们需要显式声明begin tran，比如做“插、删、改”操作需要同时修改几个表，要求要么几个表都修改成功，要么都不成功。begin tran 可以起到这样的作用，它可以把若干SQL语句套在一起执行，最后再一起commit。好处是保证了数据的一致性，但任何事情都不是完美无缺的。Begin tran付出的代价是在提交之前，所有SQL语句锁住的资源都不能释放，直到commit掉。减少内存耗量，增加并发能力

九、不使用not

我们在查询时经常在where子句使用一些逻辑表达式，如大于、小于、等于以及不等于等等，也可以使用and(与)、or(或)以及not(非)。NOT可用来对任何逻辑运算符号取反。下面是一个NOT子句的例子: ... where not (status ='VALID')

对这个查询，可以改写为不使用NOT: ... where status <>'INVALID'; 减少cpu计算

十、用Where子句替换HAVING子句

避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销，减少cpu运算。

2、从表设计方面减少内存耗量，cpu计算量，IO吞吐、增加并发能力

一、树表设计：

 树状表都是使用ID和IDParent两个字段来表示树关系。对树进行查找只能使用自关联方式，不光写法麻烦而且记录多的时候查询性能会非常差。建议在设计树表的时候可以考虑加入treePath字段，记载到该节点记录需要经历的树路径。虽然会增加Insert和Update的成本。但是对查询树关系非常有帮助。可以避免大部分的自关联查询。  树状表都是使用ID和IDParent两个字段来表示树关系。对树进行查找只能使用自关联方式，不光写法麻烦而且记录多的时候查询性能会非常差。建议在设计树表的时候可以考虑加入treePath字段，记载到该节点记录需要经历的树路径。虽然会增加Insert和Update的成本。但是对查询树关系非常有帮助。可以避免大部分的自关联查询。减少cpu计算，增加硬盘占用。

二、分区表：

记录超过一百万的表要考虑是否需要使用分区表。对于能够明确确定分区字段，并且经常通过分区访问记录的。分区表会提高查询性能。减少IO吞吐，减少cpu计算，内存耗量，增加硬盘占用。

三、冗余字段：

一些关系查询只查询类似Code、Name等很少的字段。可以考虑将频繁需要关联查询的这种字段冗余到主表中。这种表设计会要求同步更新两个字表，对于大部分查询，会减少表的关联，提高查询性能。对于自定义项引用自由项的需求，使用这种冗余设计能保证报表查询的便利，能避免联查表提高了查询效率。减少IO吞吐、减少cpu计算，增加并发能力。