mysql第三天，优化效率低的sql

优化sql

    在应用的开发过程中，由于初期数据量小，sql语言更注重功能的实现，但是当系统正式上线以后，很多sql就显示出性能问题，这时有问题的sql就成为整个系统的瓶颈，我们有必要对它进行优化。

 

       通常优化sql的步骤是：1查看表的类型（存储引擎）是否选用恰当、2如果存储引擎使用得当，我们要确定执行效率低的sql然后进行优化。

       通过show [global|session] status（global表示整个数据库，session表示当前连接）查看mysql的状态信息，当然我们主要关注的是各种sql的执行次数，因为通过查看各种sql的执行次数，我们才能判断使用哪种存储引擎更为合适。所以我们过滤一下结果使用show global status like 'Com_%'命令。其中主要的参数的意义：

Com_select:执行select语句次数

Com_insert：执行insert语句的次数，对于批量插入只记录一次

Com_update:执行update语句的次数

Com_delete：执行delete语句的次数

以上结果对所有的存储引擎的表都会累计，以下参数只对InnoDB存储引擎的表进行累计

InnoDB_rows_read:select查询返回的行数

InnoDB_rows_inserted:insert操作插入的行数

InnoDB_rows_updated:update操作更新的行数

InnoDB_rows_deleted:delete操作删除的行数

如果查询和插入语句的数量比较多，则采用MyISAM存储引擎，因为MyISAM不支持事务，而且对整个表加锁，InnoDB存储引擎的表之所以插入性能差，主要原因就是每次插入都开启一次事务，对于InnDB我认为提交性能的方法是对多个操作开启一个事务。

如果修改和删除语句的数量比较，则采用InnoDB，因为InnoDB对表加行锁

对于事务性应用通过Com_commit和Com_rollback查看事务提交和回滚的情况，如果数据库的回滚比较频繁，证明应用编写有问题。

Slow_quries慢查询的次数

       如果表的存储引擎选择恰当，我们就应该定位执行效率低的sql，定位sql有两种方法1.通过慢查询日志（以后在说）2.通过show processlist命令查看所有线程的状态，是否锁表和实时查看sql执行的状态

通过以上方法定位了执行效率低的Sql以后，通过mysql（explain命令）分析sql的执行计划优化sql

例如：explain select sum(moneys) from sale a,company b where a.company_id=b.company_id and a.year=2012 \G;

假如sale表的year没有加索引，执行计划的结果key：null表示没有索引，rows:10000表示扫描的行数,从这些信息可以看出由于year没有加索引，对sale进行了全表扫描

我们给表sale创建索引create index idx_test_sale on sale(year);

然后explain select sum(moneys) from sale a,company b where a.company_id=b.company_id and a.year=2012 \G;

结果为key：idx_test_sal,rows=1

 

       顺便介绍一下mysql的索引，以及怎么使用索引。

       mysql里索引只支持两种方法Hash索引和BTree索引，MyISAM和InnoDB都只支持BTree索引，memory/heap存储引擎Hash和BTree索引都支持，但是这两种存储引擎不经常使用，这里不做介绍了。

使用索引的情况：

1.对于联合索引，只有第一列被引用时，才可能使用索引。

2.like关键字，只有通配符%不在第一个字符上，索引才可能被使用。

3.如果column_name is null  column_name是索引列，那么可能会使用索引

为什么上述都用了可能这个词呢？因为有几个特例需要特别注意，很有可能存在索引，但不使用

1.mysql认为使用索引比全表扫描更慢，比如一个表的列的值大多数数据都是1-100之间

比如select * from table where key>1 and key<100;

2.用or连接的条件，如果第一条件里使用的是索引列or后的条件不是索引列，次数所有的索引都不使用。

3.如果列类型是字符串，那么在where条件中把字符串常量用引号引起来，否则的话列有索引也不会被用到，因为在插入的时候mysql会转化字符串类型，然后进行索引。

 

 

      优化sql。

      1.优化group by。mysql默认对group by col1,col2...后面的列进行排序这与显式制定order by col1，col2......类似,索引显式制定排序的列和order by后面的列一样，其实对mysql的性能没有影响，但是大多数情况下，group by后面的列是不必要排序的，所以我们使用在group by语句时显式指定order by null可以提高效率。

       2.优化or条件。mysql内部对or的优化机制是拆分条件，然后做union操作，这种内部优化机制的效率已经很，但是问题转移到每个拆分后的语句上，只有拆分后在or前后的每个列上加索引，才能提高效率。

      以上内容并不完善，以后补充.....