Mysql优化

一  查找分析查询速度慢的原因

1.开启慢查询日志及使用mysqldumpslow工具分析慢查询日志

https://blog.csdn.net/m_nanle_xiaobudiu/article/details/79288257

 

2.使用show profile

服务器上执行的所有语句会检测消耗的时间,存到临时表中

（1）连接mysql数据库

（2）set profiling=1; //开启profiling,可以用来查看query执行的细节，方便优化。

（3）show profiles  //查看临时表

（4）得到空集是因为在开启之前没有进行过任何查询,现在随便查询一下

（5）现在再show profiles,就会得到如下

（6）我们还可以查看每条语句花费的时间都用在了什么地方上。show  profile  for  query  临时表id；

如果关闭，直接set profiling=0；即可

 

3.使用explain  (也可以使用desc,功能一样)

可以帮助我们分析单条sql语句

这样,我们可以查看索引的使用情况,以及扫描的行数,查询的类型等

 

4.show status

show status 会返回一些计数器,  show global status  查看服务器级别的所有计数 , 有时根据这些计数,可以猜测出哪些操作 代价较高或者消耗时间多

 

我们可以通过show status 查看每个任务执行了多少次

 

5.show processlist 

观察是否有大量线程处于不正常的状态或特征

这里只有一个线程,所以show processlist 可以帮助我们观察哪些线程是有问题的

 

 

二、优化

 

1.优化查询过程中的数据访问

 

（1）访问数据太多会导致查询性能下降  

尽量不要直接select *。应该需要什么查什么，不浪费

 

（2）确定应用程序是否在检索大量超过需要的数据

比如本来只需要查name和id，结果把数据表中的全部数据都查了一遍，最后用了其中两个

 

（3）确认Mysql服务器是否在分析大量不必要的数据行

 

（4）定义sql语句应该注意以下几点:

--(1)查询不需要的数据，可以使用limit来解决

--(2) 多表关联尽量不要让它返回全部列，我们要指定列名 （即不浪费，需要什么拿什么，绝不多拿）

--(3) select * 会让优化器无法完成索引覆盖扫描的优化

 

（5）重复查询相同的数据，可以缓存数据，下次直接读取缓存

相应配置和使用可以搜索query cache ,也可以参考 http://blog.csdn.net/permike/article/details/50601080

 

（6）是否在扫描额外的记录

使用explain进行分析，如果发现查询需要扫描大量的数据，但只返回少量的行，可以通过如下技巧去优化：

--(1) 使用索引

--(2) 改变数据库和表的结构, 修改数据表范式

比如我们现在是第三范式,我们需要查询一张表中的内容,但经常关联另一张表的一个字段,这时效率可能会偏低一些,我们可以冗余的把这个字段存到第一个表中,降低范式,用空间换时间,用空间换效率

--(3) 重写sql语句,让优化器以更优的方式去执行查询

 

2  优化长难的查询语句

（1）切分查询

使用尽可能少的查询是好的,但是有时将一个大的查询分解成多个小的查询是很有必要的

--(1)可以方便做缓存

--(2)如果一条sql涉及了大量的数据时，可能会一次性锁住多个表或行，耗费了大量的系统资源，却阻塞了其他很多小的但重要的查询。将一个大的DELETE语句切分为较小的查询时,每次删除数据后，都暂停一会儿再做下一次删除，这样也可以将服务器上原本一次性的压力分散到一个很长的时间段中，就可以大大降低对服务器的影响，还可以大大减少删除时锁的持有时间。

例如，每个月会运行一次的语句，以清理三个月前的数据:

mysql> DELETE FROM messages WHERE dt<DATE_SUB(NOW(), INTERVAL 3 MONTH);

可以用以下的方法来完成这样的任务：

rows_affected = 0
do {
     rows_affected = do_query("DELETE FROM messages WHERE dt<DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000")
}while rows_affected>0

一次删除一万行数据是个比较高效且对服务器影响较小的做法。同时如果每次删除数据时暂停一会，可以将服务器原本的一次性压力分散到一个较长的时间段中，从而减少删除时锁表锁行的时间

--(参考自《高性能MySQL》)

 

（2）分解关联查询

--(1) 可以将一条关联语句分解成多条sql来执行

--(2) 可以让缓存的效率更高

--(3) 执行单个查询减少锁的竞争

--(4) 在应用层做关联可以更容易对数据库进行拆分(分表,分区之类的)

 

（3）优化特定类型的查询语句

--(1) 优化count()查询

count(*) 会忽略所有的列,直接统计所有列数,因此不要使用count(列名)

注:没有where条件下,myisam表的count(*)会非常快,而有where条件,myisam中的count(*)就和其他数据引擎相差不多了

也可以增加汇总表,我们查询总数的时候,直接到汇总表中查询

也可以使用缓存

--(2) 优化关联查询

确定on 或者using 子句的列上有索引

确保group by 和  order by 中只有一个表中的列,这样myisam才可能使用索引

--(3) 优化子查询

尽可能使用关联查询来代替

--(4) 优化 group by 和 distinct

这两种查询均可使用索引来优化

--(5) 使用标识列进行分组的效率会更高(比如使用主键列或者auto_increment 列)

--(6) 进行group by时,如果不需要进行排序,可以使用order by null ,这样,mysql就不会再进行文件排序

--(7) 优化limit分页

limit偏移量大的时候,查询效率会低

--(8) union优化

union all 效率高于union ,虽然union all会将重复的数据也查询出来,但可以在应用层面将数据筛选出来

 

三、总结：你是如何进行mysql优化的

首先定位应用程序内哪些sql语句会比较慢,我们可以通过慢查询日志,show profiles ,show processlist, 然后通过explain来查询这条或这些条sql语句为什么会慢,然后进行一系列的优化。

优化,首先从索引着手,然后从数据访问,长难查询语句,以及特定类型的查询语句来进行优化。