Effective MySQL之SQL语句最优化学习笔记1

1识别性能问题

       用户报告说你的应用程序太慢。在确定不存在物理系统资源瓶颈之后，应该把注意力转向MySQL数据库。

1.1寻找运行缓慢的SQL语句

         可以在MySQL中运行show full processlist命令，查看一下详细信息：

        如果存在的话，Time会列出时间，Info列中列出花费时间最长的SQL语句。

 

1.2确认低效查询

        发现一个潜在低效查询后，我们首先要做的就是确认是否每次重复执行都很缓慢。

• 运行SQL语句并记录执行时间

          运行刚在Info中列出的SQL语句，执行时间超过10毫秒，MySQL命令行客户端返回的查询结果已足够。

 

          警告：重复运行方法只能用于select语句，因为它不会修改数据。如果是update或者delete语句，那么把它重写为select语句以完成验证。

• 生成一个查询执行计划（QEP）

          当MySQL要执行一个查询的时候，首先会对该SQL语句进行语法检查，然后构造一个QEP，QEP决定了MySQL从底层存储引擎中获取信息的方式。如果想要查看QEP，只需要在select语句前加上EXPLAIN

 

           如果你对阅读QEP一无所知，那么首先看看使用到的索引和受影响行数这两列信息。explain结果找那个key列是使用的索引，任何没有使用索引的查询语句都可以认为是没有被足够调优的SQL查询。

 

2.优化查询

2.1不应该做的事

       当你毫无头绪的时候，可能会想到在这个基于where语句的表上添加一个索引。

mysql>alter table user add index (id);

 

       警告：在没有进一步验证的情况下，千万不要在生产环境中这样做。

 

       决定添加一个索引要考虑众多因素。由于alter语句是阻塞操作，因此所有为表添加和修改数据的额外请求都被阻塞了。根据其他数据操作语言（DML）的执行顺序，此时select语句也会被阻塞而无法完成。如果表更大一些的话，一个alter语句可能需要几个小时或者几天才能完成！另一个需要考虑的是一个表有多个索引的情况下DML语句的性能开销。

2.2确认优化

        添加完索引以后重新执行SQL查询看性能是否得到明显改善。也可以用explain查看修正了的QEP。

 

2.3正确的方式

        在决定添加索引之前，通常应该至少做2项检查。首先验证表现有的结构，然后确认表的大小，可以通过一下SQL命令来获取信息：

 

 

2.4备选的解决方案

       通过添加索引并不是解决查询速度慢的理想方法。如果不做进一步分析，该表将会因存在额外的不必哟的索引而导致开销。